Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.7(EOL)
-
None
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 |
Attachments
Issue Links
- relates to
-
-
- Confirmed
-