Incorrect "Duplicate entry" error with views and GROUP BY in maria-5.3

Bug #798597 reported by Philip Stoev
6
This bug affects 1 person
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),(20,5,0);
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)
Revision history for this message
Igor Babaev (igorb-seattle) wrote :

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.

description: updated
Michael Widenius (monty)
Changed in maria:
assignee: Igor Babaev (igorb-seattle) → Michael Widenius (monty)
Michael Widenius (monty)
Changed in maria:
milestone: 5.3 → 5.1
Michael Widenius (monty)
Changed in maria:
importance: Undecided → High
status: New → Fix Committed
Revision history for this message
Michael Widenius (monty) wrote :

Fix committed, with test case, to 5.1

Michael Widenius (monty)
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.