Incorrect "Duplicate entry" error with views and GROUP BY in maria-5.3
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Michael Widenius |
Bug Description
The following query:
SELECT COUNT(*), v2.f3, v2.f10, v2.f1
FROM t1 LEFT JOIN (v2, t3) ON 1
GROUP BY v2.f3, v2.f10, v2.f1 ;
returns the following error:
1062: 'Duplicate entry 'NULL-NULL-0' for key 'group_key'
The "group_key" is not reflected in the EXPLAIN and derived* optimizer switches have no effect.
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
test case:
CREATE TABLE t1 ( f1 int) ;
INSERT INTO t1 VALUES (19),(20);
CREATE TABLE t2 (f1 int not null, f3 int, f10 int) ;
INSERT INTO t2 VALUES (19,1,NULL)
CREATE VIEW v2 AS SELECT * FROM t2;
CREATE TABLE t3 (f1 int);
EXPLAIN SELECT COUNT(*), v2.f3, v2.f10, v2.f1
FROM t1 LEFT JOIN (v2, t3) ON 1
GROUP BY v2.f3, v2.f10, v2.f1 ;
--error 0
SELECT COUNT(*), v2.f3, v2.f10, v2.f1
FROM t1 LEFT JOIN (v2, t3) ON 1
GROUP BY v2.f3, v2.f10, v2.f1 ;
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
description: | updated |
Changed in maria: | |
assignee: | Igor Babaev (igorb-seattle) → Michael Widenius (monty) |
Changed in maria: | |
milestone: | 5.3 → 5.1 |
Changed in maria: | |
importance: | Undecided → High |
status: | New → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
I succeeded to reproduce this bug with the latest 5.1 build using the test case from
the bug #798576:
Server version: 5.1.58- MariaDB- debug Source distribution
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]> CREATE TABLE t1 ( f1 int NOT NULL , f2 int NOT NULL ) ;
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> INSERT INTO t1 VALUES (214,0),(6,6);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]>
MariaDB [test]> CREATE TABLE t2 ( f2 int) ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO t2 VALUES (88),(88);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]>
MariaDB [test]> CREATE ALGORITHM=MERGE VIEW v2 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 'up') ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT f1 , MIN(f2) FROM v2 GROUP BY f1;
ERROR 1062 (23000): Duplicate entry '214' for key 'group_key'
The bug is not reproducible with the latest mysql-5.1.
It should be fixed in maria-5.1.