MySQL Forums
Forum List  »  Newbie

Re: Order By in FROM Subquery doesn't work in 5.7
Posted by: zhiren Yip
Date: April 18, 2016 10:43PM

OK ! this is the full query sql.
SELECT 
    t2.*,
    t1.AbnormalCount,
    COUNT(*) AS TotalCount,
    (1 - t1.AbnormalCount / COUNT(*)) AS NormalRate
FROM
    (SELECT 
        *
    FROM
        `FlightOnTime`.`FlightOnTime`
    WHERE
        (`FlightDate` BETWEEN '2016-04-01' AND '2016-04-14')
            AND `DepAP` IN ('ZGSZ' , 'ZGGG')
            AND `Reg` IN ('B6020' , 'B6021')
    ORDER BY `FlightDate` DESC) t2,
    (SELECT 
        FlightNo, DepAP, ArrAP, COUNT(*) AS AbnormalCount
    FROM
        `FlightOnTime`.`FlightOnTime`
    WHERE
        (`FlightDate` BETWEEN '2016-04-01' AND '2016-04-14')
            AND `normalFlag` = - 1
            AND `DepAP` IN ('ZGSZ' , 'ZGGG')
            AND `Reg` IN ('B6020' , 'B6021')
    GROUP BY `FlightNo` , `DepAP`) t1
WHERE
    t2.`FlightNo` = t1.`FlightNo`
        AND t2.`DepAP` = t1.`DepAP`
        AND t2.`ArrAP` = t1.`ArrAP`
GROUP BY t2.`FlightNo` , t2.`DepAP`
ORDER BY `NormalRate` , t2.`FlightNo`

this sql run in mysql 5.6 with EXPLAIN below
	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
	1	PRIMARY	<derived2>	ALL					194552	Using temporary; Using filesort
	1	PRIMARY	<derived3>	ref	<auto_key0>	<auto_key0>	60	t2.FlightNo,t2.DepAP,t2.ArrAP	10	
	3	DERIVED	FlightOnTime	ALL					194552	Using where; Using temporary; Using filesort
	2	DERIVED	FlightOnTime	ALL					194552	Using where; Using filesort

AND run in mysql 5.7 , EXPLAIN result is
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
	1	PRIMARY	FlightOnTime		ALL					187430	2.78	Using where; Using temporary; Using filesort
	1	PRIMARY	<derived3>		ref	<auto_key0>	<auto_key0>	60	flightontime.FlightOnTime.FlightNo,flightontime.FlightOnTime.DepAP,flightontime.FlightOnTime.ArrAP	10	100.00	
	3	DERIVED	FlightOnTime		ALL					187430	0.28	Using where; Using temporary; Using filesort

you can see , the 'ORDER BY `Flightdate` DESC' in mysql 5.7 has been ignored .
but in mysql 5.6 , the t2 has been sort correcttly .

so , the problem is not about my query , but why in 5.7 , the ORDER BY will be ignored and how to slove this problem in mysql 5.7

Options: ReplyQuote


Subject
Written By
Posted
Re: Order By in FROM Subquery doesn't work in 5.7
April 18, 2016 10:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.