[MDEV-20815] ONLY_FULL_GROUP_BY is ignored with derived_merge=on or merge views Created: 2019-10-12  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-20816 Assertions `maybe_null || !null_value... Confirmed

 Description   

ONLY_FULL_GROUP_BY is ignored when a query is reading from a merge view or has a subquery and derived_merge is enabled (as it is by default):

With a view

CREATE TABLE t (a INT);
 
INSERT INTO t VALUES (1),(2);
 
CREATE ALGORITHM=MERGE VIEW v AS SELECT * FROM t;
 
SET sql_mode= 'ONLY_FULL_GROUP_BY';
 
SELECT a, COUNT(*) FROM v;
 
 
 
# Cleanup
 
DROP VIEW v;
 
DROP TABLE t;

10.4 c3394870

 
MariaDB [test]> SET sql_mode= 'ONLY_FULL_GROUP_BY';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> SELECT a, COUNT(*) FROM v;
 
+------+----------+
 
| a    | COUNT(*) |
 
+------+----------+
 
|    1 |        2 |
 
+------+----------+
 
1 row in set (0.001 sec)

With the table, it works as expected:

MariaDB [test]> SELECT a, COUNT(*) FROM t;
 
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

With a subquery

CREATE TABLE t (a INT);
 
INSERT INTO t VALUES (1),(2);
 
 
 
SET sql_mode= 'ONLY_FULL_GROUP_BY';
 
SELECT a, COUNT(*) FROM (SELECT * FROM t) sq;

10.2 5d57e04b

 
SELECT a, COUNT(*) FROM (SELECT * FROM t) sq;
 
a	COUNT(*)
 
1	2

With derived_merge the query causes the expected error:

SET optimizer_switch='derived_merge=off';
 
SELECT a, COUNT(*) FROM (SELECT * FROM t) sq;
 
bug.t2                                   [ fail ]
 
        Test ended at 2022-01-06 18:31:10
 
 
 
CURRENT_TEST: bug.t2
 
mysqltest: At line 7: query 'SELECT a, COUNT(*) FROM (SELECT * FROM t) sq' failed: 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause


Generated at Thu Feb 08 09:02:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.