MySQL Bugs: #97149: S.O.S order by in sub query not working

Bug #97149	S.O.S order by in sub query not working
Submitted:	8 Oct 2019 23:30	Modified:	9 Oct 2019 12:12
Reporter:	musef habra	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, order by, SUB QUERY

Description:
SELECT *
FROM (
  SELECT ID,IDPatient,ADate
  FROM abc i
  Where IDPatient=100033241
  ORDER BY ADate DESC                     <========= This Line do nothing
) AS ii
GROUP BY IDPatient;      

In Older ver i think 
it was working and make Desc sorting then group by gave me first value from the sorting list
Now it gave me rubsh values and i don't know from where !!!!!!

How to repeat:
DROP TABLE IF EXISTS `care`.`abc`;
CREATE TABLE  `care`.`abc` (
  `ID` int(11) unsigned NOT NULL DEFAULT '0',
  `IDPatient` int(4) unsigned NOT NULL DEFAULT '0',
  `ADate` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into table abc(ID,IDPatient,ADate)
Values 
(148664, 100033241, '2019-10-12 10:20:00'),
(148667, 100033241, '2019-10-09 16:30:00'),
(148661, 100033241, '2019-10-08 17:00:00'),
(148662, 100033241, '2019-10-07 20:30:00'),
(148665, 100033241, '2019-10-06 17:00:00');

To see the table :
SELECT ID,IDPatient,ADate
  FROM abc i
  Where IDPatient=100033241
  ORDER BY ADate DESC;

Now run this SQL :

SELECT *
FROM (
  SELECT ID,IDPatient,ADate
  FROM abc i
  Where IDPatient=100033241
  ORDER BY ADate DESC
) AS ii
GROUP BY IDPatient;

the result should be like this :
ID    , IDPatient,  ADate
148664, 100033241, '2019-10-12 10:20:00'

But in fact the result was so no matter how I tried to change the "order By" 
 by adding "Desc" or not or remove all the line "Order by" :
ID    , IDPatient,  ADate
148661, 100033241, '2019-10-08 17:00:00'

Suggested fix:
subquery must be sensitive by order by

when i add this  =========>
,(Select @qqqq:=0) q
it working !!!!!!!

SELECT *
FROM (
  SELECT ID,IDPatient,ADate
  FROM abc i,(Select @qqqq:=0) q
  Where IDPatient=100033241
  ORDER BY ADate DESC
) AS ii
GROUP BY IDPatient;

Hi Mr. habra,

Thank you for your bug report.

However, this is not a bug.  

We simply follow SQL standard on this issue. Order of the results from the nested query are irrelevant, since only the outermost node should be ordered, if specified so. However, it is not specified in the upper node. This is all described in our Reference Manual.

Not a bug.

Regarding your latest comment, it could be a bug, but in order to check it, you have to test it on the latest release of 5.7.