Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.3.9, 10.0.22
-
None
-
None
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
|