MySQL Bugs: #83162: ordered result should have been returned

Bug #83162	ordered result should have been returned
Submitted:	27 Sep 2016 8:01	Modified:	27 Sep 2016 11:11
Reporter:	帅 Bang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.15	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
create table t1 (c1 BIGINT(91) ,c2 SMALLINT(23) ,c3 SMALLINT(121) ,c4 BINARY(32) ,c5 NUMERIC(17,0) ,c6 BIGINT(38) ,c7 BINARY(35) ,c8 DECIMAL(18,14) ,c9 VARCHAR(3) ,c10 VARBINARY(33) , primary key(c9,c7,c3,c8,c2,c10))  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ;
INSERT INTO t1 VALUES (643,596,253,"a",19487,1012,"w",6075.24051907988200,"b","x");
INSERT INTO t1 VALUES (963,41,712,"b",22035,234,"w",9108.66705086318700,"nX","q");
INSERT INTO t1 VALUES (643,452,779,"c",52952,46,"w",2389.29887840463200,"p","x");
INSERT INTO t1 VALUES (866,440,68,"d",34358,900,"w",8362.70347230141000,"xc","w");
INSERT INTO t1 VALUES (1000,128,579,"e",51667,985,"q",5484.32852895246500,"k","j");
INSERT INTO t1 VALUES (167,23,781,"f",2539,146,"j",4593.14356251901000,"RM","l");
create table t2 (c1 VARCHAR(78) ,c2 BIGINT(79) ,c3 NUMERIC(7,6) ,c4 SMALLINT(8) ,c5 BINARY(22) ,c6 INT(42) ,c7 BINARY(95) ,c8 VARBINARY(78) ,c9 NUMERIC(36,18) ,c10 BINARY(47) ,c11 VARBINARY(37) ,c12 VARCHAR(33) ,c13 CHAR(92) , primary key(c3,c12,c4,c8))  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin PARTITION BY KEY(c3,c12) PARTITIONS 2;
INSERT INTO t2 VALUES ("g",402,5.897880,401,"kGQh",506,"x","wWcqxlbJDyjXV",10563.428226494696000000,"x","qq","hKvUZoKslavGLh","qq");
INSERT INTO t2 VALUES ("h",306,7.304043,57,"gFTt",602,"x","x",14538.122486415210000000,"x","qq","qq","c");
INSERT INTO t2 VALUES ("i",647,1.477923,865,"rQqpect",24,"op","x",45910.048043011960000000,"x","qq","IMcGkAunJW","w");
INSERT INTO t2 VALUES ("j",294,7.224041,827,"W",471,"po","x",30997.196671321720000000,"qq","lFbPSD","v","pl");
INSERT INTO t2 VALUES ("k",70,4.302767,626,"VV",843,"pYLy","x",48371.949818082650000000,"tLsFmERV","qq","pmrDFcaZHpurr","x");
INSERT INTO t2 VALUES ("l",329,1.676559,724,"JdocpNB",907,"x","qq",55010.026056458610000000,"e","w","LANilWMdm","i");
INSERT INTO t2 VALUES ("m",503,2.280897,860,"uyt",635,"x","x",7248.002639080827000000,"qq","WPUXlW","xw","qa");
INSERT INTO t2 VALUES ("m",402,0.082945,14,"wsc",306,"qq","hKdBnqxAD",24827.847025109500000000,"VeogeQS","ahTdnFIBQHWFhmlf","xd","x");
INSERT INTO t2 VALUES ("m",469,1.983159,667,"xsa",870,"qq","flFQqyvjm",62102.086518122310000000,"az","DII","Z","wd");
INSERT INTO t2 VALUES ("m",702,5.471207,30,"majED",163,"ldRvuu","qq",39625.557098464800000000,"mdCjosuCmPau","r","y","u");
INSERT INTO t2 VALUES ("w",760,6.088293,953,"xvdfer",396,"qq","Jkn",60915.438350086630000000,"knEsqly","y","yu","u");
INSERT INTO t2 VALUES ("y",362,3.478996,807,"dxd",594,"qq","ftgh",17186.252114030650000000,"y","AFwsfa","u","u");
INSERT INTO t2 VALUES ("z",41,9.539645,895,"xxw",963,"qq","qazs",21250.058568115490000000,"y","y","y","y");
INSERT INTO t2 VALUES ("q",420,4.551205,724,"ddfdre",783,"qq","iuyt",36038.972100723135000000,"y","y","y","ee");
INSERT INTO t2 VALUES ("m",144,4.709972,546,"xdfwq",822,"opiy","y",28553.756553125960000000,"dd","mm","ee","ff";

mysql> SELECT AVG(abs(t1_11.c3)), MAX(t2_113.c11), MIN(repeat(t1_11.c7, 0)), MAX(lower(t2_113.c4)), MAX(round(t1_11.c2)), MIN(replace(t2_113.c4, 'a', t2_113.c8)) FROM t1 AS t1_11 LEFT OUTER JOIN t2 AS t2_113 ON t1_11.c4 = 888 AND t1_11.c9 = t2_113.c1 OR t1_11.c7 < 697 AND t1_11.c6 = t2_113.c7   GROUP BY t1_11.c5 ORDER BY 6, 1, 2, 3, 4, 5;

+--------------------+-----------------+--------------------------+-----------------------+----------------------+-----------------------------------------+
| AVG(abs(t1_11.c3)) | MAX(t2_113.c11) | MIN(repeat(t1_11.c7, 0)) | MAX(lower(t2_113.c4)) | MAX(round(t1_11.c2)) | MIN(replace(t2_113.c4, 'a', t2_113.c8)) |
+--------------------+-----------------+--------------------------+-----------------------+----------------------+-----------------------------------------+
|           781.0000 | NULL            |                          | NULL                  |                   23 | NULL                                    |
|           712.0000 | NULL            |                          | NULL                  |                   41 | NULL                                    |
|           579.0000 | NULL            |                          | NULL                  |                  128 | NULL                                    |
|            68.0000 | NULL            |                          | NULL                  |                  440 | NULL                                    |
|           779.0000 | NULL            |                          | NULL                  |                  452 | NULL                                    |
|           253.0000 | NULL            |                          | NULL                  |                  596 | NULL                                    |
+--------------------+-----------------+--------------------------+-----------------------+----------------------+-----------------------------------------+
6 rows in set, 270 warnings (0.00 sec)

To be honest, a little messy here, yeah. Sorry about that. But here comes the bug:

Please take a note on the 'ORDER BY 6, 1, 2, 3, 4, 5' part in the query. It means that the results must be sorted according to the 6th column, then first column , then 2nd column (and so on...)

But, as you can see that,  the stuff in 6th column are all NULLs, while the first column of the results (781 > 712  > 579 > 68 < 779 > 253)  is not sorted  in an ascending order. 

BTW, this bug is not observed with mysql 5.6

How to repeat:
drop table t1,t2;
create table t1 (c1 BIGINT(91) ,c2 SMALLINT(23) ,c3 SMALLINT(121) ,c4 BINARY(32) ,c5 NUMERIC(17,0) ,c6 BIGINT(38) ,c7 BINARY(35) ,c8 DECIMAL(18,14) ,c9 VARCHAR(3) ,c10 VARBINARY(33) , primary key(c9,c7,c3,c8,c2,c10))  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ;
INSERT INTO t1 VALUES (643,596,253,"a",19487,1012,"w",6075.24051907988200,"b","x");
INSERT INTO t1 VALUES (963,41,712,"b",22035,234,"w",9108.66705086318700,"nX","q");
INSERT INTO t1 VALUES (643,452,779,"c",52952,46,"w",2389.29887840463200,"p","x");
INSERT INTO t1 VALUES (866,440,68,"d",34358,900,"w",8362.70347230141000,"xc","w");
INSERT INTO t1 VALUES (1000,128,579,"e",51667,985,"q",5484.32852895246500,"k","j");
INSERT INTO t1 VALUES (167,23,781,"f",2539,146,"j",4593.14356251901000,"RM","l");
create table t2 (c1 VARCHAR(78) ,c2 BIGINT(79) ,c3 NUMERIC(7,6) ,c4 SMALLINT(8) ,c5 BINARY(22) ,c6 INT(42) ,c7 BINARY(95) ,c8 VARBINARY(78) ,c9 NUMERIC(36,18) ,c10 BINARY(47) ,c11 VARBINARY(37) ,c12 VARCHAR(33) ,c13 CHAR(92) , primary key(c3,c12,c4,c8))  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin PARTITION BY KEY(c3,c12) PARTITIONS 2;
INSERT INTO t2 VALUES ("g",402,5.897880,401,"kGQh",506,"x","wWcqxlbJDyjXV",10563.428226494696000000,"x","qq","hKvUZoKslavGLh","qq");
INSERT INTO t2 VALUES ("h",306,7.304043,57,"gFTt",602,"x","x",14538.122486415210000000,"x","qq","qq","c");
INSERT INTO t2 VALUES ("i",647,1.477923,865,"rQqpect",24,"op","x",45910.048043011960000000,"x","qq","IMcGkAunJW","w");
INSERT INTO t2 VALUES ("j",294,7.224041,827,"W",471,"po","x",30997.196671321720000000,"qq","lFbPSD","v","pl");
INSERT INTO t2 VALUES ("k",70,4.302767,626,"VV",843,"pYLy","x",48371.949818082650000000,"tLsFmERV","qq","pmrDFcaZHpurr","x");
INSERT INTO t2 VALUES ("l",329,1.676559,724,"JdocpNB",907,"x","qq",55010.026056458610000000,"e","w","LANilWMdm","i");
INSERT INTO t2 VALUES ("m",503,2.280897,860,"uyt",635,"x","x",7248.002639080827000000,"qq","WPUXlW","xw","qa");
INSERT INTO t2 VALUES ("m",402,0.082945,14,"wsc",306,"qq","hKdBnqxAD",24827.847025109500000000,"VeogeQS","ahTdnFIBQHWFhmlf","xd","x");
INSERT INTO t2 VALUES ("m",469,1.983159,667,"xsa",870,"qq","flFQqyvjm",62102.086518122310000000,"az","DII","Z","wd");
INSERT INTO t2 VALUES ("m",702,5.471207,30,"majED",163,"ldRvuu","qq",39625.557098464800000000,"mdCjosuCmPau","r","y","u");
INSERT INTO t2 VALUES ("w",760,6.088293,953,"xvdfer",396,"qq","Jkn",60915.438350086630000000,"knEsqly","y","yu","u");
INSERT INTO t2 VALUES ("y",362,3.478996,807,"dxd",594,"qq","ftgh",17186.252114030650000000,"y","AFwsfa","u","u");
INSERT INTO t2 VALUES ("z",41,9.539645,895,"xxw",963,"qq","qazs",21250.058568115490000000,"y","y","y","y");
INSERT INTO t2 VALUES ("q",420,4.551205,724,"ddfdre",783,"qq","iuyt",36038.972100723135000000,"y","y","y","ee");
INSERT INTO t2 VALUES ("m",144,4.709972,546,"xdfwq",822,"opiy","y",28553.756553125960000000,"dd","mm","ee","ff";

SELECT AVG(abs(t1_11.c3)), MAX(t2_113.c11), MIN(repeat(t1_11.c7, 0)), MAX(lower(t2_113.c4)), MAX(round(t1_11.c2)), MIN(replace(t2_113.c4, 'a', t2_113.c8)) FROM t1 AS t1_11 LEFT OUTER JOIN t2 AS t2_113 ON t1_11.c4 = 888 AND t1_11.c9 = t2_113.c1 OR t1_11.c7 < 697 AND t1_11.c6 = t2_113.c7   GROUP BY t1_11.c5 ORDER BY 6, 1, 2, 3, 4, 5;

Suggested fix:
+--------------------+-----------------+--------------------------+-----------------------+----------------------+-----------------------------------------+
| AVG(abs(t1_11.c3)) | MAX(t2_113.c11) | MIN(repeat(t1_11.c7, 0)) | MAX(lower(t2_113.c4)) | MAX(round(t1_11.c2)) | MIN(replace(t2_113.c4, 'a', t2_113.c8)) |
+--------------------+-----------------+--------------------------+-----------------------+----------------------+-----------------------------------------+
|            68.0000 | NULL            |                          | NULL                  |                  440 | NULL                                    |
|           253.0000 | NULL            |                          | NULL                  |                  596 | NULL                                    |
|           579.0000 | NULL            |                          | NULL                  |                  128 | NULL                                    |
|           712.0000 | NULL            |                          | NULL                  |                   41 | NULL                                    |
|           779.0000 | NULL            |                          | NULL                  |                  452 | NULL                                    |
|           781.0000 | NULL            |                          | NULL                  |                   23 | NULL                                    |
+--------------------+-----------------+--------------------------+-----------------------+----------------------+-----------------------------------------+
6 rows in set, 450 warnings (0.01 sec)

as mysql5.6 does.

Hello Bang,

Thank you for the report.
Observed this with 5.7.15 build.

Thanks,
Umesh

Posted by developer:
 
I get correct result with 5.7.11 and 8.0.0.  Wrong result with 5.7.14.

Posted by developer:
 
Correction: Error is also present in 5.7.11

This appears to be the same problem as bug#79366, bug#82334 and bug#82572.