[Commits] Rev 3147: Fix bug lp:817384 in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Tue Aug 9 10:29:04 EEST 2011


At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------
revno: 3147
revision-id: timour at askmonty.org-20110809072857-jejqbtsvmg8fz762
parent: igor at askmonty.org-20110809050210-fpt9djersdhxmnn4
fixes bug(s): https://launchpad.net/bugs/817384
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Tue 2011-08-09 10:28:57 +0300
message:
  Fix bug lp:817384
  
  This bug is a special case of lp:813447.
  
  Analysis:
  Constant optimization finds that the condition t2.a = 1
  can be used to access the primary key of table 't2'. As
  a result both outer table t1,t2 are considered as constant
  when we reach the execution phase. At the same time, during
  constant optimization, the IN predicate is not evaluated
  because it is expensive.
  
  When execution of the outer query reaches do_select(),
  control flow enter the branch:
  if (join->table_count == join->const_tables)
  { ... }
  This branch checks only the WHERE and HAVING clauses,
  but doesn't check the ON clauses of the query. Since the
  IN predicate was not evaluated during optimization, it is
  not evaluated at all, thus execution doesn't detect that
  the ON clause is FALSE.
  
  Solution:
  Similar to the patch for bug lp:813447, exclude system
  tables from constant substitution based on unique key
  lookups if there is an expensive ON condition on the
  inner table.
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-07-21 12:50:25 +0000
+++ b/mysql-test/r/join_outer.result	2011-08-09 07:28:57 +0000
@@ -1561,3 +1561,35 @@ id	select_type	table	type	possible_keys
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
 DROP TABLE t1,t2,t3;
+#
+# LP bug #817384 Wrong result with outer join + subquery in ON
+# clause +unique key
+#
+CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ;
+INSERT INTO t1 VALUES (1,'b');
+CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (1,'a');
+create table t3 (c1 char(1), c2 char(2));
+insert into t3 values ('c','d');
+insert into t3 values ('c','d');
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t2      eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+b
+NULL
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t2      const   PRIMARY PRIMARY 4       const   1       Using where
+2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+b
+NULL
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2011-07-21 12:50:25 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2011-08-09 07:28:57 +0000
@@ -1570,6 +1570,38 @@ id	select_type	table	type	possible_keys
 Warnings:
 Note    1003    select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
 DROP TABLE t1,t2,t3;
+#
+# LP bug #817384 Wrong result with outer join + subquery in ON
+# clause +unique key
+#
+CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ;
+INSERT INTO t1 VALUES (1,'b');
+CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (1,'a');
+create table t3 (c1 char(1), c2 char(2));
+insert into t3 values ('c','d');
+insert into t3 values ('c','d');
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t2      eq_ref  PRIMARY PRIMARY 4       const   1       Using where
+2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+b
+NULL
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      system  NULL    NULL    NULL    NULL    1       
+1       PRIMARY t2      const   PRIMARY PRIMARY 4       const   1       Using where
+2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+b
+NULL
+DROP TABLE t1,t2,t3;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name   Value

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/subselect.result	2011-08-09 07:28:57 +0000
@@ -2960,7 +2960,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -2972,7 +2972,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2011-08-09 07:28:57 +0000
@@ -2965,7 +2965,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -2977,7 +2977,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2011-08-09 07:28:57 +0000
@@ -2961,7 +2961,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -2973,7 +2973,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2011-08-09 07:28:57 +0000
@@ -2961,7 +2961,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -2973,7 +2973,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/r/subselect_scache.result'
--- a/mysql-test/r/subselect_scache.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/subselect_scache.result	2011-08-09 07:28:57 +0000
@@ -2964,7 +2964,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
              ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'
@@ -2976,7 +2976,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
             ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      system  PRIMARY NULL    NULL    NULL    1       
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    2       Using index condition
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- a/mysql-test/suite/pbxt/r/subselect.result	2011-07-23 04:39:55 +0000
+++ b/mysql-test/suite/pbxt/r/subselect.result	2011-08-09 07:28:57 +0000
@@ -2954,7 +2954,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
 ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       Using index
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    1       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
@@ -2966,7 +2966,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c
 ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      const   PRIMARY PRIMARY 4       const   1       Using index
-1       PRIMARY r       const   PRIMARY PRIMARY 4       const   1       
+1       PRIMARY r       eq_ref  PRIMARY PRIMARY 4       const   1       Using where
 2       DEPENDENT SUBQUERY      t2      range   b       b       40      NULL    1       Using where
 SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
 ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2011-07-21 07:43:37 +0000
+++ b/mysql-test/t/join_outer.test	2011-08-09 07:28:57 +0000
@@ -1127,3 +1127,30 @@ SELECT t2.a FROM t1 LEFT JOIN t2 ON (6)
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # LP bug #817384 Wrong result with outer join + subquery in ON
+--echo # clause +unique key
+--echo #
+
+CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ;
+INSERT INTO t1 VALUES (1,'b');
+
+CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (1,'a');
+
+create table t3 (c1 char(1), c2 char(2));
+insert into t3 values ('c','d');
+insert into t3 values ('c','d');
+
+
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+
+DROP TABLE t1,t2,t3;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-08 21:37:06 +0000
+++ b/sql/sql_select.cc	2011-08-09 07:28:57 +0000
@@ -3330,7 +3330,9 @@ make_join_statistics(JOIN *join, List<TA
           {
             if (table->key_info[key].flags & HA_NOSAME)
             {
-              if (const_ref == eq_part)
+              if (const_ref == eq_part &&
+                  !((outer_join & table->map) &&
+                    (*s->on_expr_ref)->is_expensive()))
               {                                 // Found everything for ref.
                 int tmp;
                 ref_changed = 1;



More information about the commits mailing list