| 2011-06-23 08:03:19 |
Philip Stoev |
description |
Not repeatable in maria-5.2, mysql-5.1. 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 ; |
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 ; |
|
