Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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 ;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with non-standard GROUP BY + ORDER BY
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
Re: Wrong result with non-standard GROUP BY + ORDER BY
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
Re: Wrong result with non-standard GROUP BY + ORDER BY
The result difference problem only appears with MyISAM as the storage engine, not with InnoDB. That is why I could not repeat.
Re: Wrong result with non-standard GROUP BY + ORDER BY
Fixed in 5.2 and above (left in 5.1).
Launchpad bug id: 694450
Re: Wrong result with non-standard GROUP BY + ORDER BY
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;
--------------------
-------+--------------------
-------+--------------------
-------+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 ;
--------------------
-------+--------------------
-------+--------------------
-------+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).