Wrong result with non-standard GROUP BY + ORDER BY

[Philip Stoev]

The following query:

SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ORDER BY alias1.f2 , field2;

is non-standard in a sense that the ORDER BY contains a column not specified in the SELECT list or GROUP BY. Yet, ONLY_FULL_GROUP_BY SQL mode does not generate an error, and the query returns a wrong result.

It seems that this bug is present in all MariaDB and MySQL version. It would be nice to fix it because it causes various false positives when testing 5.3 optimizations.

Test case:

SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY';
CREATE TABLE t1 (
f1 int(11), f2 int(11), f3 datetime, f4 varchar(1), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES ('1','9','2004-10-11 18:13','x'),('2','5','2004-03-07 14:02','g'),('3','1','2004-04-09 09:38','o'),('4','0','1900-01-01 00:00','g'),('5','1','2009-02-19 02:05','v');

# This must return an error, but instead returns 1 row
SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ORDER BY alias1.f2 , field2;

# This returns several rows
SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ;

[Kristian Nielsen (knielsen)]

Philip, just to clarify, are there really two bugs here?

First, from a quick test, it looks to me as if ONLY_FULL_GROUP_BY simply is not taken into account for ORDER BY. Even trivial examples show this. Maybe it makes sense to report this to MySQL.

Second, you say that the two queries return different result sets (one row vs. several). This seems to be a regression compared to MariaDB 5.1.51, where I get the same set of rows from both queries:

MariaDB [test]> SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ORDER BY alias1.f2 , field2;
+---------------------+--------+
| field1 | field2 |
+---------------------+--------+
| 2004-10-11 18:13:00 | 1 |
| 2009-02-19 02:05:00 | 5 |
+---------------------+--------+
2 rows in set (0.00 sec)

MariaDB [test]> SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ;
+---------------------+--------+
| field1 | field2 |
+---------------------+--------+
| 2004-10-11 18:13:00 | 1 |
| 2009-02-19 02:05:00 | 5 |
+---------------------+--------+
2 rows in set (0.00 sec)

(Since you say "this bug is present in all MariaDB and MySQL versions", I was not sure if you meant just the ignoring of ONLY_FULL_GROUP_BY in ORDER BY, or also the result difference).

[Philip Stoev (philip-stoev)]

Yes, there are two distinct issues:

A. ONLY_FULL_GROUP_BY does not prevent a query with sub-standard ORDER BY from running. I think this bug has been there since the dawn of time. I will report it to MySQL when I have a chance.

B. Wrong result from the query. Here are the results I am getting:
- mysql-5.1.53 from binary - bug is present
- mysql-5.5.8 from binary - bug is present, also generates extra warnings
- maria-5.3 ./BUILD/compile-pentium-debug-max-no-ndb - bug is present
- maria-5.2 ./BUILD/compile-pentium-debug-max-no-ndb - bug is present
- maria-5.1 ./BUILD/compile-pentium-debug-max-no-ndb - bug is present

[Philip Stoev (philip-stoev)]

with the mariadb-5.1.53-Linux-i686 binary I get different results with and without ORDER By. The EXPLAIN plans are as follows:

with order by:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 ALL NULL NULL NULL NULL 5 Using where
1 SIMPLE alias2 eq_ref PRIMARY PRIMARY 4 test.alias1.f2 1

without order by:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort
1 SIMPLE alias2 eq_ref PRIMARY PRIMARY 4 test.alias1.f2 1

[Kristian Nielsen (knielsen)]

The result difference problem only appears with MyISAM as the storage engine, not with InnoDB. That is why I could not repeat.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Fixed in 5.2 and above (left in 5.1).