Looks very good. Approved.
--
Øystein
On 31/05/2011 14:10, Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting/ based on
> revid:tor.didriksen@stripped
>
> 3379 Guilhem Bichot 2011-05-31
> Fix for Bug#12546542 "MISSING ROW WHEN USING
> OPTIMIZER_JOIN_CACHE_LEVEL>=3"
> Join cache code forgot to reset JOIN_TAB::first_unmatched in some cases.
> @ sql/sql_join_cache.cc
> Without this fix, the final SELECT in main.subquery_sj_mat_nosj
> main.subquery_sj_none_jcl6 main.subquery_sj_none_jcl7 and main.subquery_sj_none shows only
> "v", misses "we".
>
> In the test's scenario, we had
> "SELECT FROM t1 WHERE t1's field IN (SELECT ... FROM t2 LEFT JOIN t3
> ...)".
> When semijoin=off, IN is converted to EXISTS. This conversion adds an
> implicit
> LIMIT=1 clause to the now-dependent subquery (finding one row suffices
> to calculate the value of EXISTS()).
> At optimizer_join_cache_level>=3, join buffering applies to outer joins,
> so t3 does join buffering (block nested loop join).
> We have two rows in t1 ('v' and 'we'); we evaluate the subquery twice,
> one for each row of t1.
> The first evaluation, for t1's row 'v', leads
> to generating a NULL-complemented row of t2 and t3. This generation
> happens in JOIN_CACHE::join_records() which calls join_null_complements(),
> like this:
>
> /* Prepare for generation of null complementing extensions */
> for (tab= join_tab->first_inner; tab<= join_tab->last_inner;
> tab++)
> tab->first_unmatched= join_tab->first_inner;
> }
> }
> if (join_tab->first_unmatched)
> {
> ...
> rc= join_null_complements(skip_last);
> if (rc != NESTED_LOOP_OK&& rc != NESTED_LOOP_NO_MORE_ROWS)
> goto finish;
> }
> ....
> if (outer_join_first_inner)
> {
> /*
> All null complemented rows have been already generated for all
> outer records from join buffer. Restore the state of the
> first_unmatched values to 0 to avoid another null complementing.
> */
> for (tab= join_tab->first_inner; tab<= join_tab->last_inner;
> tab++)
> tab->first_unmatched= 0;
> }
> finish:
> some irrelevant cleanup code;
>
> The return value 'rc' of join_null_complements() is here
> NESTED_LOOP_QUERY_LIMIT because the subquery had LIMIT=1:
> one row has been found in the subquery's result, no need
> to look for more rows (see end_send()). So we "goto finish", and thus
> forget to reset join_tab->first_unmatched to NULL.
>
> Then we have the second evaluation of the subquery, for t1's row 'we'.
> Because of the non-reset join_tab->first_unmatched, in the subquery's
> evaluation, in JOIN_CACHE::join_records(), we don't even try
> to find a match for t2's row in t3 (missing a row this way),
> we directly jump to NULL-complementing.
> The fix is the following:
> as JOIN_CACHE::join_records() sets first_unmatched and resets it,
> make it reset it in all cases: move the "reset" code to the "finish" label.
>
> modified:
> mysql-test/include/subquery_sj.inc
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> sql/sql_join_cache.cc
> === modified file 'mysql-test/include/subquery_sj.inc'
> --- a/mysql-test/include/subquery_sj.inc 2011-03-17 08:00:10 +0000
> +++ b/mysql-test/include/subquery_sj.inc 2011-05-31 12:10:01 +0000
> @@ -3540,3 +3540,27 @@ ORDER BY a;
>
> DROP TABLE t1;
> DROP VIEW v1;
> +
> +--echo #
> +--echo # Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +--echo #
> +CREATE TABLE t1 (
> + f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> + col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> + col_int_key int(11) DEFAULT NULL,
> + col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> + col_int_key int(11) DEFAULT NULL,
> + col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +DROP TABLE t1,t2,t3;
>
> === modified file 'mysql-test/r/subquery_sj_all.result'
> --- a/mysql-test/r/subquery_sj_all.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_all.result 2011-05-31 12:10:01 +0000
> @@ -5429,4 +5429,29 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
> --- a/mysql-test/r/subquery_sj_all_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-05-31 12:10:01 +0000
> @@ -5433,5 +5433,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
> --- a/mysql-test/r/subquery_sj_all_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-05-31 12:10:01 +0000
> @@ -5433,5 +5433,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +v
> +we
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed.result'
> --- a/mysql-test/r/subquery_sj_dupsweed.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-05-31 12:10:01 +0000
> @@ -5428,4 +5428,29 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-05-31 12:10:01 +0000
> @@ -5432,5 +5432,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-05-31 12:10:01 +0000
> @@ -5432,5 +5432,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch.result'
> --- a/mysql-test/r/subquery_sj_firstmatch.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-05-31 12:10:01 +0000
> @@ -5430,6 +5430,31 @@ a
> DROP TABLE t1;
> DROP VIEW v1;
> #
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> +#
> # Bug#51457 Firstmatch semijoin strategy gives wrong results for
> # certain query plans
> #
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-05-31 12:10:01 +0000
> @@ -5434,6 +5434,31 @@ a
> DROP TABLE t1;
> DROP VIEW v1;
> #
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> +#
> # Bug#51457 Firstmatch semijoin strategy gives wrong results for
> # certain query plans
> #
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-05-31 12:10:01 +0000
> @@ -5434,6 +5434,31 @@ a
> DROP TABLE t1;
> DROP VIEW v1;
> #
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> +#
> # Bug#51457 Firstmatch semijoin strategy gives wrong results for
> # certain query plans
> #
>
> === modified file 'mysql-test/r/subquery_sj_loosescan.result'
> --- a/mysql-test/r/subquery_sj_loosescan.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan.result 2011-05-31 12:10:01 +0000
> @@ -5429,4 +5429,29 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-05-31 12:10:01 +0000
> @@ -5433,5 +5433,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-05-31 12:10:01 +0000
> @@ -5433,5 +5433,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat.result'
> --- a/mysql-test/r/subquery_sj_mat.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat.result 2011-05-31 12:10:01 +0000
> @@ -5429,4 +5429,29 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-05-31 12:10:01 +0000
> @@ -5433,5 +5433,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +we
> +v
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-05-31 12:10:01 +0000
> @@ -5433,5 +5433,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +v
> +we
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
> --- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-05-31 12:10:01 +0000
> @@ -5651,4 +5651,29 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +v
> +we
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none.result'
> --- a/mysql-test/r/subquery_sj_none.result 2011-03-17 08:00:10 +0000
> +++ b/mysql-test/r/subquery_sj_none.result 2011-05-31 12:10:01 +0000
> @@ -5574,4 +5574,29 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +v
> +we
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
> --- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-05-31 12:10:01 +0000
> @@ -5578,5 +5578,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +v
> +we
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
> --- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-03-17 11:23:06 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-05-31 12:10:01 +0000
> @@ -5578,5 +5578,30 @@ a
> 0
> DROP TABLE t1;
> DROP VIEW v1;
> +#
> +# Bug#12546542 MISSING ROW WHEN USING OPTIMIZER_JOIN_CACHE_LEVEL>=3
> +#
> +CREATE TABLE t1 (
> +f2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t1 VALUES ('v'),('we');
> +CREATE TABLE t2 (
> +col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t2 VALUES ('we',4,NULL),('v',1305673728,6);
> +CREATE TABLE t3 (
> +col_int_key int(11) DEFAULT NULL,
> +col_int int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> +INSERT INTO t3 VALUES (4,4);
> +SELECT * FROM t1 WHERE f2 IN (SELECT a1.col_varchar_1024_utf8 AS f2 FROM t2
> +AS a1 LEFT JOIN t3 AS a2 ON a1.col_int_key = a2.col_int_key WHERE a1.col_int
> +BETWEEN 1 AND 10 OR a2.col_int IS NOT NULL);
> +f2
> +v
> +we
> +DROP TABLE t1,t2,t3;
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
>
> === modified file 'sql/sql_join_cache.cc'
> --- a/sql/sql_join_cache.cc 2011-04-26 08:49:10 +0000
> +++ b/sql/sql_join_cache.cc 2011-05-31 12:10:01 +0000
> @@ -1697,28 +1697,28 @@ enum_nested_loop_state JOIN_CACHE::join_
> if (rc != NESTED_LOOP_OK&& rc != NESTED_LOOP_NO_MORE_ROWS)
> goto finish;
> }
> - if (outer_join_first_inner)
> - {
> - /*
> - All null complemented rows have been already generated for all
> - outer records from join buffer. Restore the state of the
> - first_unmatched values to 0 to avoid another null complementing.
> - */
> - for (tab= join_tab->first_inner; tab<= join_tab->last_inner; tab++)
> - tab->first_unmatched= 0;
> - }
> -
> +
> if (skip_last)
> {
> DBUG_ASSERT(!is_key_access());
> /*
> Restore the last record from the join buffer to generate
> - all extentions for it.
> + all extensions for it.
> */
> get_record();
> }
>
> finish:
> + if (outer_join_first_inner)
> + {
> + /*
> + All null complemented rows have been already generated for all
> + outer records from join buffer. Restore the state of the
> + first_unmatched values to 0 to avoid another null complementing.
> + */
> + for (tab= join_tab->first_inner; tab<= join_tab->last_inner; tab++)
> + tab->first_unmatched= 0;
> + }
> restore_last_record();
> reset(TRUE);
> DBUG_RETURN(rc);
>
| Thread |
|---|
| • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3379) Bug#12546542 | Øystein Grøvlen | 8 Jun |
