Hi!
On Tue, Dec 16, 2008 at 05:38:18PM +0000, Sergey Glukhov wrote:
> #At file:///home/gluh/MySQL/mysql-5.0-bug-40953/ based on
> revid:sergey.glukhov@stripped
>
> 2744 Sergey Glukhov 2008-12-16
> Bug#40953 SELECT query throws "ERROR 1062 (23000): Duplicate entry..." error
> There are two instances of t1 (cr, cr2). cr2 should have
The fact that the same table is used twice in the query is irrelevant to the
problem (and so does not need to be mentioned). You can create a copy of table
t1, make the query refer to a table only once and it will still fail with the
same error.
The real issue was that a 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, while the code that assigns table->maybe_null atrributes
assumed the only the possibility of #1 (and not #2).
Please fix the comment to reflect that.
> maybe_null set in the st_table instance, cr should not.
> Because cr is considered to be dependent on t2,
> the code sets st_table::maybe_null for cr into 'true'.
> The fix is to set st_table::maybe_null to 'true' only
> for those tables which are used in outer join.
> modified:
> mysql-test/r/select.result
> mysql-test/t/select.test
> sql/sql_select.cc
>
> per-file messages:
> mysql-test/r/select.result
> test result
> mysql-test/t/select.test
> test case
> sql/sql_select.cc
> There are two instances of t1 (cr, cr2). cr2 should have
> maybe_null set in the st_table instance, cr should not.
> Because cr is considered to be dependent on t2,
> the code sets st_table::maybe_null for cr into 'true'.
> The fix is to set st_table::maybe_null to 'true' only
> for those tables which are used in outer join.
> === modified file 'mysql-test/r/select.result'
> --- a/mysql-test/r/select.result 2008-02-18 15:18:44 +0000
> +++ b/mysql-test/r/select.result 2008-12-16 18:39:23 +0000
> @@ -4355,4 +4355,37 @@ Handler_read_prev 0
> Handler_read_rnd 0
> Handler_read_rnd_next 6
> DROP TABLE t1, t2;
> +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;
> +f3 f4 count
> +1 abc 1
> +1 def 2
> +drop table t1, t2, t3;
> End of 5.0 tests
>
> === modified file 'mysql-test/t/select.test'
> --- a/mysql-test/t/select.test 2008-02-18 15:18:44 +0000
> +++ b/mysql-test/t/select.test 2008-12-16 18:39:23 +0000
> @@ -3701,4 +3701,40 @@ SELECT DISTINCT b FROM t1 LEFT JOIN t2 U
> 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
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2008-12-10 14:13:11 +0000
> +++ b/sql/sql_select.cc 2008-12-16 18:39:23 +0000
> @@ -2489,7 +2489,7 @@ make_join_statistics(JOIN *join, TABLE_L
> if (s->dependent & table->map)
> s->dependent |= table->reginfo.join_tab->dependent;
> }
> - if (s->dependent)
> + if (outer_join & s->table->map)
> s->table->maybe_null= 1;
> }
> /* Catch illegal cross references for outer joins */
Please also add a comment fod definition of JOIN::outer_join.
BR
Sergey
--
Sergey Petrunia, Lead Software Engineer
MySQL AB, www.mysql.com
Office: N/A
Blog: http://s.petrunia.net/blog
