Query that violates ONLY_FULL_GROUP by is accepted under derived_merge=on

[Philip Stoev]

The following query is rejected under derived_merge=OFF with error:

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

but is accepted under derived_merge=ON with the following explain:

mysql> explain extended SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 0 | 0.00 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------+
| Note | 1003 | select count(0) AS `COUNT(*)` from `test`.`t1` order by NULL |
+-------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

test case:

DROP TABLE t1,t2;
CREATE TABLE t1 (f1 integer,f2 integer);

set SESSION sql_mode='ONLY_FULL_GROUP_BY';
set SESSION optimizer_switch='derived_merge=on';
SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
set SESSION optimizer_switch='derived_merge=off';
SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;

revision-id: <email address hidden>
date: 2011-10-28 11:23:30 +0400
build-date: 2011-10-28 11:41:04 +0300
revno: 3257
branch-nick: maria-5.3

[Philip Stoev (pstoev-askmonty)]

Same applies for views:

create table t1 (f1 integer, f2 integer);
create view v1 as select * from t1;
select f1,max(f2) from v1;

does not return an error.