List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 24 2011 2:20pm
Subject:bzr commit into mysql-trunk branch (guilhem.bichot:3392) Bug#12616477
View as plain text  
#At file:///home/mysql_src/bzrrepos_new/opt-back-new/ based on revid:guilhem.bichot@stripped

 3392 Guilhem Bichot	2011-06-24
      Fix for BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
      Don't mark MIN() as constant when optimizing correlated subquery.
     @ mysql-test/r/subquery_all.result
        Before the code fix:
        the third and fifth rows of the testcase had NULL in last column.
     @ sql/opt_sum.cc
        Scenario of the bug, using the simplified testcase.
        
        Subq is executed for each row of "t1f JOIN t3f".
        First subq execution also optimizes the subquery. So we have in total,
        for the subquery, one optimization and four executions.
        The optimization goes into opt_sum_query() for
         SELECT MIN(t3s.col_int_key)
         FROM t3 AS t3s JOIN
         t1 AS t1s ON t1s.col_int_key = 9 and
         t1s.col_varchar_key = 'e'
         WHERE 'e' <> t1f.col_varchar_nokey.
        t1s is irrelevant here.
        opt_sum_query() sees that the WHERE clause does not mention t3s, so it
        reads the minimum of t3s.col_int_key (found through the index)
        (value:4) and marks the MIN() item as being constant with value 4.
        At second execution, row of t1f has t1f.col_varchar_nokey='e',
        so WHERE condition is false, end_send_group() calls
        item->no_rows_in_result() which sets the const item above to NULL.
        At third execution, the MIN() should be set back 4, but it's not,
        because opt_sum_query() marked it constant. So it stays at how the
        second execution left it: NULL. It stays NULL for all further
        executions.
        The explanation above assumed optimizer_join_cache_level=0;
        with higher levels, the order of rows is different (rows
        with value 4 come first); as we run the testsuite with higher levels,
        the testcase has been changed (forcing the join order) so that
        BNL row shuffling put rows in the "right order" to trigger the bug.
        
        The transformation done in opt_sum_query() is ok as long as there
        is a single execution: this single execution will either find rows in
        the JOIN (then the constant value will not be touched, and be
        returned), or find no rows, and end_send_group() will set to NULL.
        But for multiple executions, the transformation done at subquery's
        optimization is wrong, as it offers no way to get "from NULL back to
        4" for a next outer row.
        Fix: don't set MIN() to "constant" if the WHERE depends on outer rows.
        I ran the full testsuite, to see if the fix above makes
        opt_sum_query() not do "constant marking" for some test, and the
        answer is "no" (except for this test's testcase). So it does not look
        too intrusive.
        
        FYI here is the backtrace where t1f.col_varchar_nokey gets the
        OUTER_REF_TABLE_BIT bit, in the subquery:
        
        (top-gdb) p depended_from
        $37 = (st_select_lex *) 0x1e6c580
        (top-gdb) p depended_from->select_number
        $38 = 1 # this is the top-query
        (top-gdb) p *this
        $44 = (Item_field) "t1f.col_varchar_nokey" # field from outer table
        (top-gdb) f
        #2  0x00000000005e8b20 in Item_func::fix_fields at sql/item_func.cc:218
        (top-gdb) bt
        #1  Item_field::used_tables at sql/item.cc:2367
        #2  Item_func::fix_fields at sql/item_func.cc:218
        #3  setup_conds at sql/sql_base.cc:8565
        #4  setup_without_group at sql/sql_select.cc:570
        #5  JOIN::prepare of subquery

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_jcl6.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_jcl6.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_jcl6.result
      sql/opt_sum.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-06-24 13:47:09 +0000
+++ b/mysql-test/include/subquery.inc	2011-06-24 14:20:06 +0000
@@ -5378,3 +5378,41 @@ eval $query;
 eval explain $query;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+--echo #
+
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+
+# At jcl>=1, if join buffering is on t1 bug doesn't happen, so we
+# force join order so that join buffering is rather on t3.
+# Reverse join order if you want to see bug at jcl=0.
+
+let $query=SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+
+eval $query;
+eval explain $query;
+
+DROP TABLE t1,t3;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2011-06-24 13:47:09 +0000
+++ b/mysql-test/r/subquery_all.result	2011-06-24 14:20:06 +0000
@@ -6555,4 +6555,55 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Using where; Using index for group-by; Using temporary
 DROP TABLE t1,t2;
+#
+# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+col_int_key	col_varchar_key	col_varchar_nokey	col_int_key	col_varchar_key	(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+224	p	p	4	p	4
+9	e	e	4	p	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	10	a	4
+9	e	e	10	a	NULL
+explain SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1f	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3f	index	NULL	col_varchar_key	9	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using index condition; Using where
+2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result	2011-06-24 13:47:09 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result	2011-06-24 14:20:06 +0000
@@ -6559,5 +6559,56 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Using where; Using index for group-by; Using temporary
 DROP TABLE t1,t2;
+#
+# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+col_int_key	col_varchar_key	col_varchar_nokey	col_int_key	col_varchar_key	(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+224	p	p	4	p	4
+9	e	e	4	p	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	10	a	4
+9	e	e	10	a	NULL
+explain SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1f	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3f	index	NULL	col_varchar_key	9	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using index condition; Using where
+2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-06-24 13:47:09 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-06-24 14:20:06 +0000
@@ -6555,4 +6555,55 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Using where; Using index for group-by; Using temporary
 DROP TABLE t1,t2;
+#
+# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+col_int_key	col_varchar_key	col_varchar_nokey	col_int_key	col_varchar_key	(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+224	p	p	4	p	4
+9	e	e	4	p	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	10	a	4
+9	e	e	10	a	NULL
+explain SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1f	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3f	index	NULL	col_varchar_key	9	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using index condition; Using where
+2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result	2011-06-24 13:47:09 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result	2011-06-24 14:20:06 +0000
@@ -6559,5 +6559,56 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Using where; Using index for group-by; Using temporary
 DROP TABLE t1,t2;
+#
+# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+col_int_key	col_varchar_key	col_varchar_nokey	col_int_key	col_varchar_key	(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+224	p	p	4	p	4
+9	e	e	4	p	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	10	a	4
+9	e	e	10	a	NULL
+explain SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1f	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3f	index	NULL	col_varchar_key	9	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using index condition; Using where
+2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-06-24 13:47:09 +0000
+++ b/mysql-test/r/subquery_none.result	2011-06-24 14:20:06 +0000
@@ -6554,4 +6554,55 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Using where; Using index for group-by; Using temporary
 DROP TABLE t1,t2;
+#
+# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+col_int_key	col_varchar_key	col_varchar_nokey	col_int_key	col_varchar_key	(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+224	p	p	4	p	4
+9	e	e	4	p	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	10	a	4
+9	e	e	10	a	NULL
+explain SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1f	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3f	index	NULL	col_varchar_key	9	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using where
+2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result	2011-06-24 13:47:09 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result	2011-06-24 14:20:06 +0000
@@ -6558,5 +6558,56 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t2	range	f1_key	f1_key	4	NULL	6	Using where; Using index for group-by; Using temporary
 DROP TABLE t1,t2;
+#
+# BUG#12616477 - 0 VS NULL DIFFERENCES WITH OUTER JOIN, SUBQUERY
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY (col_int_key)
+);
+INSERT INTO t1 VALUES (224,'p','p'),(9,'e','e');
+CREATE TABLE t3 (
+col_int_key int,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key))
+;
+INSERT INTO t3 VALUES (4,'p'),(8,'e'),(10,'a');
+SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+col_int_key	col_varchar_key	col_varchar_nokey	col_int_key	col_varchar_key	(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+224	p	p	4	p	4
+9	e	e	4	p	NULL
+224	p	p	8	e	4
+9	e	e	8	e	NULL
+224	p	p	10	a	4
+9	e	e	10	a	NULL
+explain SELECT t1f.*,t3f.*,(
+SELECT MIN(t3s.col_int_key)
+FROM t3 AS t3s JOIN
+t1 AS t1s ON t1s.col_int_key = 9 and
+t1s.col_varchar_key = 'e'
+WHERE 'e' <> t1f.col_varchar_nokey )
+FROM
+t1 AS t1f STRAIGHT_JOIN t3 AS t3f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1f	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3f	index	NULL	col_varchar_key	9	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t1s	ref	col_int_key	col_int_key	5	const	1	Using where
+2	DEPENDENT SUBQUERY	t3s	index	NULL	col_int_key	5	NULL	3	Using index; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2011-04-15 08:11:49 +0000
+++ b/sql/opt_sum.cc	2011-06-24 14:20:06 +0000
@@ -243,14 +243,21 @@ int opt_sum_query(THD *thd,
   ulonglong count= 1;
   bool is_exact_count= TRUE, maybe_exact_count= TRUE;
   table_map removed_tables= 0, outer_tables= 0, used_tables= 0;
-  table_map where_tables= 0;
   Item *item;
   int error;
 
   DBUG_ENTER("opt_sum_query");
 
-  if (conds)
-    where_tables= conds->used_tables();
+  const table_map where_tables= conds ? conds->used_tables() : 0;
+  /*
+    opt_sum_query() happens at optimization. A subquery is optimized once but
+    executed possibly multiple times.
+    If the value of the set function depends on the join's emptiness (like
+    MIN() does), and the join's emptiness depends on the outer row, we cannot
+    mark the set function as constant:
+   */
+  if (where_tables & OUTER_REF_TABLE_BIT)
+    DBUG_RETURN(0);
 
   /*
     Analyze outer join dependencies, and, if possible, compute the number


Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110624142006-ai9nocndvladfgj3.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3392) Bug#12616477Guilhem Bichot25 Jun