Details
Minor
Description
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 882981
Re: Query that violates ONLY_FULL_GROUP by is accepted under derived_merge=on
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.