[MDEV-3580] LP:694450 - Wrong result with non-standard GROUP BY + ORDER BY Created: 2010-12-26  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug694450.xml    

 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');

  1. 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;
  1. 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 ;

 Comments   
Comment by Kristian Nielsen [ 2010-12-27 ]

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;
---------------------------+

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).

Comment by Philip Stoev (Inactive) [ 2010-12-27 ]

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
Comment by Philip Stoev (Inactive) [ 2010-12-27 ]

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

Comment by Kristian Nielsen [ 2010-12-27 ]

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.

Comment by Oleksandr Byelkin [ 2012-03-14 ]

Re: Wrong result with non-standard GROUP BY + ORDER BY
Fixed in 5.2 and above (left in 5.1).

Comment by Rasmus Johansson (Inactive) [ 2012-03-14 ]

Launchpad bug id: 694450

Generated at Thu Feb 08 06:49:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.