Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

window function query fails to execute ('lost connection to server..') #9498

Closed
morgo opened this issue Feb 27, 2019 · 8 comments
Closed

window function query fails to execute ('lost connection to server..') #9498

morgo opened this issue Feb 27, 2019 · 8 comments
Assignees
@alivxxx
Labels
type/bug The issue is confirmed as a bug.

Comments

@morgo
Copy link
Contributor

morgo commented Feb 27, 2019

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
CREATE TABLE `ontime` (
  `Year` year DEFAULT NULL,
  `Quarter` tinyint(4) DEFAULT NULL,
  `Month` tinyint(4) DEFAULT NULL,
  `DayofMonth` tinyint(4) DEFAULT NULL,
  `DayOfWeek` tinyint(4) DEFAULT NULL,
  `FlightDate` date DEFAULT NULL,
  `UniqueCarrier` char(7) DEFAULT NULL,
  `AirlineID` int(11) DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `TailNum` varchar(50) DEFAULT NULL,
  `FlightNum` varchar(10) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginAirportSeqID` int(11) DEFAULT NULL,
  `OriginCityMarketID` int(11) DEFAULT NULL,
  `Origin` char(5) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `OriginStateFips` varchar(10) DEFAULT NULL,
  `OriginStateName` varchar(100) DEFAULT NULL,
  `OriginWac` int(11) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestAirportSeqID` int(11) DEFAULT NULL,
  `DestCityMarketID` int(11) DEFAULT NULL,
  `Dest` char(5) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DestStateFips` varchar(10) DEFAULT NULL,
  `DestStateName` varchar(100) DEFAULT NULL,
  `DestWac` int(11) DEFAULT NULL,
  `CRSDepTime` int(11) DEFAULT NULL,
  `DepTime` int(11) DEFAULT NULL,
  `DepDelay` int(11) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `DepDel15` int(11) DEFAULT NULL,
  `DepartureDelayGroups` int(11) DEFAULT NULL,
  `DepTimeBlk` varchar(20) DEFAULT NULL,
  `TaxiOut` int(11) DEFAULT NULL,
  `WheelsOff` int(11) DEFAULT NULL,
  `WheelsOn` int(11) DEFAULT NULL,
  `TaxiIn` int(11) DEFAULT NULL,
  `CRSArrTime` int(11) DEFAULT NULL,
  `ArrTime` int(11) DEFAULT NULL,
  `ArrDelay` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `ArrDel15` int(11) DEFAULT NULL,
  `ArrivalDelayGroups` int(11) DEFAULT NULL,
  `ArrTimeBlk` varchar(20) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  `CRSElapsedTime` int(11) DEFAULT NULL,
  `ActualElapsedTime` int(11) DEFAULT NULL,
  `AirTime` int(11) DEFAULT NULL,
  `Flights` int(11) DEFAULT NULL,
  `Distance` int(11) DEFAULT NULL,
  `DistanceGroup` tinyint(4) DEFAULT NULL,
  `CarrierDelay` int(11) DEFAULT NULL,
  `WeatherDelay` int(11) DEFAULT NULL,
  `NASDelay` int(11) DEFAULT NULL,
  `SecurityDelay` int(11) DEFAULT NULL,
  `LateAircraftDelay` int(11) DEFAULT NULL,
  `FirstDepTime` varchar(10) DEFAULT NULL,
  `TotalAddGTime` varchar(10) DEFAULT NULL,
  `LongestAddGTime` varchar(10) DEFAULT NULL,
  `DivAirportLandings` varchar(10) DEFAULT NULL,
  `DivReachedDest` varchar(10) DEFAULT NULL,
  `DivActualElapsedTime` varchar(10) DEFAULT NULL,
  `DivArrDelay` varchar(10) DEFAULT NULL,
  `DivDistance` varchar(10) DEFAULT NULL,
  `Div1Airport` varchar(10) DEFAULT NULL,
  `Div1AirportID` int(11) DEFAULT NULL,
  `Div1AirportSeqID` int(11) DEFAULT NULL,
  `Div1WheelsOn` varchar(10) DEFAULT NULL,
  `Div1TotalGTime` varchar(10) DEFAULT NULL,
  `Div1LongestGTime` varchar(10) DEFAULT NULL,
  `Div1WheelsOff` varchar(10) DEFAULT NULL,
  `Div1TailNum` varchar(10) DEFAULT NULL,
  `Div2Airport` varchar(10) DEFAULT NULL,
  `Div2AirportID` int(11) DEFAULT NULL,
  `Div2AirportSeqID` int(11) DEFAULT NULL,
  `Div2WheelsOn` varchar(10) DEFAULT NULL,
  `Div2TotalGTime` varchar(10) DEFAULT NULL,
  `Div2LongestGTime` varchar(10) DEFAULT NULL,
  `Div2WheelsOff` varchar(10) DEFAULT NULL,
  `Div2TailNum` varchar(10) DEFAULT NULL,
  `Div3Airport` varchar(10) DEFAULT NULL,
  `Div3AirportID` int(11) DEFAULT NULL,
  `Div3AirportSeqID` int(11) DEFAULT NULL,
  `Div3WheelsOn` varchar(10) DEFAULT NULL,
  `Div3TotalGTime` varchar(10) DEFAULT NULL,
  `Div3LongestGTime` varchar(10) DEFAULT NULL,
  `Div3WheelsOff` varchar(10) DEFAULT NULL,
  `Div3TailNum` varchar(10) DEFAULT NULL,
  `Div4Airport` varchar(10) DEFAULT NULL,
  `Div4AirportID` int(11) DEFAULT NULL,
  `Div4AirportSeqID` int(11) DEFAULT NULL,
  `Div4WheelsOn` varchar(10) DEFAULT NULL,
  `Div4TotalGTime` varchar(10) DEFAULT NULL,
  `Div4LongestGTime` varchar(10) DEFAULT NULL,
  `Div4WheelsOff` varchar(10) DEFAULT NULL,
  `Div4TailNum` varchar(10) DEFAULT NULL,
  `Div5Airport` varchar(10) DEFAULT NULL,
  `Div5AirportID` int(11) DEFAULT NULL,
  `Div5AirportSeqID` int(11) DEFAULT NULL,
  `Div5WheelsOn` varchar(10) DEFAULT NULL,
  `Div5TotalGTime` varchar(10) DEFAULT NULL,
  `Div5LongestGTime` varchar(10) DEFAULT NULL,
  `Div5WheelsOff` varchar(10) DEFAULT NULL,
  `Div5TailNum` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin ;
SET SESSION tidb_enable_window_function = 1;
SELECT
 year,
 UniqueCarrier,
 COUNT(*) as Flights,
 AVG(ArrDelay) as avgArrDelay,
 lag(AVG(ArrDelay), 1) OVER (order by year) as prevAvgArrDelay
FROM ontime
GROUP BY UniqueCarrier, year
ORDER BY year, UniqueCarrier;
  1. What did you expect to see?

Query results.

  1. What did you see instead?
mysql> SELECT
    ->  year,
    ->  UniqueCarrier,
    ->  COUNT(*) as Flights,
    ->  AVG(ArrDelay) as avgArrDelay,
    ->  lag(AVG(ArrDelay), 1) OVER (order by year) as prevAvgArrDelay
    -> FROM ontime
    -> GROUP BY UniqueCarrier, year
    -> ORDER BY year, UniqueCarrier;
ERROR 2013 (HY000): Lost connection to MySQL server during query
  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.0-beta-118-g9afbda98d
Git Commit Hash: 9afbda98d0c52a964c4a920bc259e7b5b25bc7a4
Git Branch: master
UTC Build Time: 2019-02-27 03:46:36
GoVersion: go version go1.11.3 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.01 sec)
@morgo morgo added the type/bug The issue is confirmed as a bug. label Feb 27, 2019
@morgo
Copy link
Contributor Author

morgo commented Feb 27, 2019

PTAL @lamxTyler

@alivxxx
Copy link
Contributor

alivxxx commented Feb 28, 2019

@morgo The function lag is not yet supported, it will be done in future PR.

@zz-jason zz-jason changed the title lost connection executing window function query window function lag is not supported Feb 28, 2019
@morgo
Copy link
Contributor Author

morgo commented Feb 28, 2019

Sounds good. We can close this if you like, it is now covered in #9499

@alivxxx alivxxx closed this as completed Feb 28, 2019
@morgo
Copy link
Contributor Author

morgo commented Mar 14, 2019

I'm still getting an abort on this specific query:

SELECT
 year,
 UniqueCarrier,
 COUNT(*) AS Flights,
 AVG(ArrDelay) AS avgArrDelay,
 lag(AVG(ArrDelay), 1)
  OVER (PARTITION BY UniqueCarrier ORDER BY year)
  AS prevAvgArrDelay,
 lag(AVG(ArrDelay), 1)
  OVER (PARTITION BY UniqueCarrier ORDER BY year)-AVG(ArrDelay)
  AS improvement
FROM ontime
WHERE UniqueCarrier IN ('AA', 'UA', 'DL')
GROUP BY UniqueCarrier, year
ORDER BY year, UniqueCarrier;

Other queries are working well though. For example this one was 96 rows in set (1 min 26.98 sec) for TiDB, and 96 rows in set (6 min 24.27 sec) for MySQL:

CREATE OR REPLACE VIEW stats AS
SELECT
 year,
 UniqueCarrier,
 COUNT(*) AS Flights,
 COUNT(CASE WHEN ArrDelay>=30 THEN 1 END) as AtLeast30Delayed
FROM ontime
WHERE UniqueCarrier IN ('AA', 'UA', 'DL')
GROUP BY UniqueCarrier, year;

SELECT
  year,
  UniqueCarrier,
  flights,
  AtLeast30Delayed,
  AtLeast30Delayed/Flights as PctDelayed,
  LAG(AtLeast30Delayed) OVER w/LAG(Flights) OVER w
    AS LastYearPctDelayed
FROM stats
WINDOW w AS (PARTITION BY UniqueCarrier ORDER BY year)
ORDER BY year, UniqueCarrier;

@morgo morgo reopened this Mar 14, 2019
@morgo
Copy link
Contributor Author

morgo commented Mar 14, 2019

@lamxTyler PTAL

@morgo morgo changed the title window function lag is not supported window function query fails to execute ('lost connection to server..') Mar 14, 2019
@alivxxx
Copy link
Contributor

alivxxx commented Mar 15, 2019

@morgo It is the same problem with #9591. It will be fixed soon.

@alivxxx
Copy link
Contributor

alivxxx commented Mar 15, 2019

@morgo You could try the latest master. It is already fixed.

@morgo
Copy link
Contributor Author

morgo commented Mar 15, 2019

Thanks! confirming that it is fixed.

@morgo morgo closed this as completed Mar 15, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@alivxxx alivxxx

Labels
type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@morgo @alivxxx