MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:December 24 2008 3:24pm
Subject:bzr commit into mysql-5.0-bugteam branch (Sergey.Glukhov:2724)
Bug#40953
View as plain text  
#At file:///home/gluh/MySQL/mysql-5.0-bugteam/ based on revid:sergey.glukhov@stripped

 2724 Sergey Glukhov	2008-12-24
      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.
modified:
  mysql-test/r/select.result
  mysql-test/t/select.test
  sql/sql_select.cc
  sql/sql_select.h

per-file messages:
  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
=== 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-24 15:24:11 +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-24 15:24:11 +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-24 15:24:11 +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 */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2008-10-24 02:16:22 +0000
+++ b/sql/sql_select.h	2008-12-24 15:24:11 +0000
@@ -235,7 +235,11 @@ public:
     fetching data from a cursor
   */
   bool     resume_nested_loop;
-  table_map const_table_map,found_const_table_map,outer_join;
+  table_map const_table_map,found_const_table_map;
+  /*
+     Bitmap of all inner tables from outer joins
+  */
+  table_map outer_join;
   ha_rows  send_records,found_records,examined_rows,row_limit, select_limit;
   /*
     Used to fetch no more than given amount of rows per one

Thread
bzr commit into mysql-5.0-bugteam branch (Sergey.Glukhov:2724)Bug#40953Sergey Glukhov24 Dec