From ce985aa36e2fb9cb036ae66376bf3c0dfab433be Mon Sep 17 00:00:00 2001 From: Sergey Glukhov Date: Wed, 24 Dec 2008 19:24:11 +0400 Subject: Bug#40953 SELECT query throws "ERROR 1062 (23000): Duplicate entry..." error Table could be marked dependent because it is either 1) an inner table of an outer join, or 2) it is a part of STRAIGHT_JOIN. In case of STRAIGHT_JOIN table->maybe_null should not be assigned. The fix is to set st_table::maybe_null to 'true' only for those tables which are used in outer join. mysql-test/r/select.result: test result mysql-test/t/select.test: test case sql/sql_select.cc: Table could be marked dependent because it is either 1) an inner table of an outer join, or 2) it is a part of STRAIGHT_JOIN. In case of STRAIGHT_JOIN table->maybe_null should not be assigned. The fix is to set st_table::maybe_null to 'true' only for those tables which are used in outer join. sql/sql_select.h: added comment --- mysql-test/t/select.test | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) (limited to 'mysql-test/t/select.test') diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 1ee87957643..1e8dc7ac2f6 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3701,4 +3701,40 @@ SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3; SHOW STATUS LIKE 'Handler_read%'; DROP TABLE t1, t2; +# +# Bug#40953 SELECT query throws "ERROR 1062 (23000): Duplicate entry..." error +# +CREATE TABLE t1 (f1 bigint(20) NOT NULL default '0', + f2 int(11) NOT NULL default '0', + f3 bigint(20) NOT NULL default '0', + f4 varchar(255) NOT NULL default '', + PRIMARY KEY (f1), + KEY key1 (f4), + KEY key2 (f2)); +CREATE TABLE t2 (f1 int(11) NOT NULL default '0', + f2 enum('A1','A2','A3') NOT NULL default 'A1', + f3 int(11) NOT NULL default '0', + PRIMARY KEY (f1), + KEY key1 (f3)); +CREATE TABLE t3 (f1 bigint(20) NOT NULL default '0', + f2 datetime NOT NULL default '1980-01-01 00:00:00', + PRIMARY KEY (f1)); + +insert into t1 values (1, 1, 1, 'abc'); +insert into t1 values (2, 1, 2, 'def'); +insert into t1 values (3, 1, 2, 'def'); +insert into t2 values (1, 'A1', 1); +insert into t3 values (1, '1980-01-01'); + +SELECT a.f3, cr.f4, count(*) count +FROM t2 a +STRAIGHT_JOIN t1 cr ON cr.f2 = a.f1 +LEFT JOIN +(t1 cr2 + JOIN t3 ae2 ON cr2.f3 = ae2.f1 +) ON a.f1 = cr2.f2 AND ae2.f2 < now() - INTERVAL 7 DAY AND +cr.f4 = cr2.f4 +GROUP BY a.f3, cr.f4; + +drop table t1, t2, t3; --echo End of 5.0 tests -- cgit v1.2.1