[MDEV-737] LP:882981 - Query that violates ONLY_FULL_GROUP by is accepted under derived_merge=on Created: 2011-10-28  Updated: 2020-05-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.9, 10.0.22
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug882981.xml    

 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



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-11-17 ]

Launchpad bug id: 882981

Comment by Philip Stoev (Inactive) [ 2011-11-17 ]

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.

Comment by Daniel Black [ 2015-11-13 ]

Still affected 10.0, fixed in 10.1 something < 10.1.9

MariaDB [test]> set SESSION sql_mode='ONLY_FULL_GROUP_BY';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> set SESSION optimizer_switch='derived_merge=on';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
 
+----------+
 
| COUNT(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> set SESSION optimizer_switch='derived_merge=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
 
+----------+
 
| COUNT(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+------------------------------+
 
| @@version                    |
 
+------------------------------+
 
| 10.0.22-MariaDB-1~jessie-log |
 
+------------------------------+
 
1 row in set (0.00 sec)
 

MariaDB [test]> CREATE TABLE t1 (f1 integer,f2 integer);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> set SESSION sql_mode='ONLY_FULL_GROUP_BY';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> set SESSION optimizer_switch='derived_merge=on';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
 
+----------+
 
| COUNT(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> set SESSION optimizer_switch='derived_merge=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT COUNT(*) FROM (SELECT * FROM t1) AS a1 ORDER BY f2;
 
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
 
MariaDB [test]> select @@version;
 
+--------------------+
 
| @@version          |
 
+--------------------+
 
| 10.1.9-MariaDB-log |

Comment by Elena Stepanova [ 2016-02-29 ]

danblack,
From the output above it looks like the problem is not fixed in 10.1, as the initial complaint was that the query is allowed with derived_merge=on, and it's still allowed.
I'm not sure why you were able to execute the query with derived_merge=off on 10.0, my guess is maybe you had query cache on?

Generated at Thu Feb 08 06:30:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.