Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.11, 11.4, 11.8, 12.3, 11.4.10
-
Not for Release Notes
Description
Under semantic-equivalent rewriting, extracting one join input into a passthrough view causes MariaDB to reject the rewritten query with `ERROR 1055`.
The source and mutated queries are logically equivalent, but MariaDB accepts the source and rejects the mutated form.
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'; |
|
|
DROP VIEW IF EXISTS V_t2_case07; |
DROP TABLE IF EXISTS t2; |
DROP TABLE IF EXISTS t3; |
|
|
CREATE TABLE t2 ( |
c1 INT, |
c7 INT |
);
|
|
|
CREATE TABLE t3 ( |
c3 INT, |
c12 INT |
);
|
|
|
CREATE VIEW V_t2_case07 AS |
SELECT c1, c7 |
FROM t2; |
|
|
-- Source Original SQL
|
SELECT t2.c7 + t3.c12 AS x |
FROM t2 |
JOIN t3 ON t3.c3 = t2.c1 |
GROUP BY t2.c7 + t3.c12; |
|
|
-- Mutated SQL
|
SELECT V_t2_case07.c7 + t3.c12 AS x |
FROM V_t2_case07 |
JOIN t3 ON t3.c3 = V_t2_case07.c1 |
GROUP BY V_t2_case07.c7 + t3.c12; |
Observed result - Query A (original):
Empty set
Observed result - Query B (mutated):
ERROR 1055 (42000): 'test.t3.c12' isn't in GROUP BY
Attachments
Issue Links
- duplicates
-
MDEV-39725 CREATE VIEW extraction changes row multiplicity for DISTINCT over grouped aggregate results
-
- Confirmed
-