Query that violates ONLY_FULL_GROUP by is accepted under derived_merge=on
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Confirmed
|
Low
|
Timour Katchaounov |
Bug Description
The following query is rejected under derived_merge=OFF with error:
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),
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=
set SESSION optimizer_
SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
set SESSION optimizer_
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
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Low |
assignee: | Igor Babaev (igorb-seattle) → Timour Katchaounov (timour) |
milestone: | 5.3 → 5.2 |
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.