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

[Philip Stoev]

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 ;

[Igor Babaev (igorb-seattle)]

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.

[Michael Widenius (monty)]

Fix committed, with test case, to 5.1