MySQL Bugs: #41345: Order by clause is dropping rows

Bug #41345	Order by clause is dropping rows
Submitted:	9 Dec 2008 23:55	Modified:	4 Apr 2009 19:12
Reporter:	Paul Rolfe	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.0.72, 5.1.30	OS:	Linux (Fedora core6)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	regression

Description:
From my understanding of SQL, the order by clase should neve filter records, but this query.

I have a query which uses a right string function inside a BIT_AND that makes MYSQL return too fewer rows.  Removing the "order by" makes the server return the correct amount of rows. 

How to repeat:
Please find attached table backup from mysql administrator.

SELECT * FROM test_drop where test_number = 30 order by BIT_AND(right(test_string,3));

returns one row only from the test data.

Other oddball results show up when you keep the order by and drop the where.

I have done this on both an innodb and myisam table.  Bad results on both.

Test data set to reproduce problem

Attachment: mybug 20081210 1056.sql (text/plain), 13.08 KiB.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67, and inform about the results.

Sorry, no need to try with 5.0.67. The problem is repeatable with 5.0.72.

5.1.30 is also affected.

MySQL 4.1.x gives proper error message in this case:

mysql> SELECT * FROM test_drop where test_number = 30 order by BIT_AND(right(tes
t_string,3));
ERROR 1111 (HY000): Invalid use of group function
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 4.1.22-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

This is not a bug but an extension of SQL syntax: you can mix groping columns and aggregate functions with not grouping columns like "SELECT c1, SUM(c2) FROM t1".

In our case the server treats the query:

  SELECT * FROM test_drop
  where test_number = 30 
  order by BIT_AND(right(test_string,3));

in the same was as:

  SELECT BIT_AND(right(test_string,3)), test_drop.* FROM test_drop
  where test_number = 30 
  order by 1;

I.e.
1) the server selects random value of each table column,
2) it calculates an aggregate function BIT_AND(right(test_string,3)),
3) it "sorts" (result set contains only one row!) a result set by the value of this aggregate function.

So, single row result set is a valid behavior.

Note:
Processing of GROUP BY columns mixed with other column depends of @@sql_mode system variable state: if a @@sql_mode value contains the ONLY_FULL_GROUP_BY flag, such a query should fail with an error:
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
However, it doesn't, that is an error. After investigation I've found, that any query containing mixed aggregate function and wild cards (not only queries with ORDER BY) ignores ONLY_FULL_GROUP_BY mode and doesn't fail as expected.
See new bug #44090 for details.