maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04469
Re: [Commits] Rev 3308: Fixed LP BUG#747278 in file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/
Hi Sanja,
Ok to push.
On Wed, Nov 23, 2011 at 01:46:22AM +0200, sanja@xxxxxxxxxxxx wrote:
> At file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/
>
> ------------------------------------------------------------
> revno: 3308
> revision-id: sanja@xxxxxxxxxxxx-20111122234621-b1t47i7xgzsg1hwo
> parent: holyfoot@xxxxxxxxxxxx-20111122135733-vgsg077x9rbn08r6
> committer: sanja@xxxxxxxxxxxx
> branch nick: work-maria-5.3-lpb747278
> timestamp: Wed 2011-11-23 01:46:21 +0200
> message:
> Fixed LP BUG#747278
>
> The problem was that when we have single row subquery with no rows
> Item_cache(es) which represent result row was not null and being
> requested via element_index() returned random value.
>
> The fix is setting all Item_cache(es) in NULL before executing the
> query (reset() method) which guaranty NULL value of whole query
> or its elements requested in any way if no rows was found.
>
> set_null() method was added to Item_cache to guaranty correct NULL
> value in case of reseting the cache.
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2011-11-22 10:06:46 +0000
> +++ b/mysql-test/r/subselect.result 2011-11-22 23:46:21 +0000
> @@ -5681,4 +5681,85 @@ select a from t1 where a in (select a fr
> a
> 1
> drop table t1;
> +#
> +# LP BUG#747278 incorrect values of the NULL (no rows) single
> +# row subquery requested via element_index() interface
> +#
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int default 1, f3b int default 2);
> +INSERT INTO t3 VALUES (1,1),(2,2);
> +set @old_optimizer_switch = @@session.optimizer_switch;
> +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +set @@session.optimizer_switch=@old_optimizer_switch;
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +select (null, null) = (null, null);
> +(null, null) = (null, null)
> +NULL
> +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0);
> +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0)
> +NULL
> +drop tables t1,t2,t3;
> +# return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
>
> === modified file 'mysql-test/r/subselect_no_mat.result'
> --- a/mysql-test/r/subselect_no_mat.result 2011-11-22 10:06:46 +0000
> +++ b/mysql-test/r/subselect_no_mat.result 2011-11-22 23:46:21 +0000
> @@ -5680,6 +5680,87 @@ select a from t1 where a in (select a fr
> a
> 1
> drop table t1;
> +#
> +# LP BUG#747278 incorrect values of the NULL (no rows) single
> +# row subquery requested via element_index() interface
> +#
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int default 1, f3b int default 2);
> +INSERT INTO t3 VALUES (1,1),(2,2);
> +set @old_optimizer_switch = @@session.optimizer_switch;
> +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +set @@session.optimizer_switch=@old_optimizer_switch;
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +select (null, null) = (null, null);
> +(null, null) = (null, null)
> +NULL
> +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0);
> +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0)
> +NULL
> +drop tables t1,t2,t3;
> +# return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set optimizer_switch=default;
> select @@optimizer_switch like '%materialization=on%';
>
> === modified file 'mysql-test/r/subselect_no_opts.result'
> --- a/mysql-test/r/subselect_no_opts.result 2011-11-21 20:01:47 +0000
> +++ b/mysql-test/r/subselect_no_opts.result 2011-11-22 23:46:21 +0000
> @@ -5676,5 +5676,86 @@ select a from t1 where a in (select a fr
> a
> 1
> drop table t1;
> +#
> +# LP BUG#747278 incorrect values of the NULL (no rows) single
> +# row subquery requested via element_index() interface
> +#
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int default 1, f3b int default 2);
> +INSERT INTO t3 VALUES (1,1),(2,2);
> +set @old_optimizer_switch = @@session.optimizer_switch;
> +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +set @@session.optimizer_switch=@old_optimizer_switch;
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +select (null, null) = (null, null);
> +(null, null) = (null, null)
> +NULL
> +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0);
> +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0)
> +NULL
> +drop tables t1,t2,t3;
> +# return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set @optimizer_switch_for_subselect_test=null;
>
> === modified file 'mysql-test/r/subselect_no_scache.result'
> --- a/mysql-test/r/subselect_no_scache.result 2011-11-22 10:06:46 +0000
> +++ b/mysql-test/r/subselect_no_scache.result 2011-11-22 23:46:21 +0000
> @@ -5685,6 +5685,87 @@ select a from t1 where a in (select a fr
> a
> 1
> drop table t1;
> +#
> +# LP BUG#747278 incorrect values of the NULL (no rows) single
> +# row subquery requested via element_index() interface
> +#
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int default 1, f3b int default 2);
> +INSERT INTO t3 VALUES (1,1),(2,2);
> +set @old_optimizer_switch = @@session.optimizer_switch;
> +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +set @@session.optimizer_switch=@old_optimizer_switch;
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +select (null, null) = (null, null);
> +(null, null) = (null, null)
> +NULL
> +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0);
> +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0)
> +NULL
> +drop tables t1,t2,t3;
> +# return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set optimizer_switch=default;
> select @@optimizer_switch like '%subquery_cache=on%';
>
> === modified file 'mysql-test/r/subselect_no_semijoin.result'
> --- a/mysql-test/r/subselect_no_semijoin.result 2011-11-22 10:06:46 +0000
> +++ b/mysql-test/r/subselect_no_semijoin.result 2011-11-22 23:46:21 +0000
> @@ -5676,5 +5676,86 @@ select a from t1 where a in (select a fr
> a
> 1
> drop table t1;
> +#
> +# LP BUG#747278 incorrect values of the NULL (no rows) single
> +# row subquery requested via element_index() interface
> +#
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int default 1, f3b int default 2);
> +INSERT INTO t3 VALUES (1,1),(2,2);
> +set @old_optimizer_switch = @@session.optimizer_switch;
> +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +set @@session.optimizer_switch=@old_optimizer_switch;
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1)
> +NULL
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +(SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +(SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +NULL
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +(SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1)
> +NULL
> +select (null, null) = (null, null);
> +(null, null) = (null, null)
> +NULL
> +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0);
> +(SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0)
> +NULL
> +drop tables t1,t2,t3;
> +# return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
> set @optimizer_switch_for_subselect_test=null;
>
> === modified file 'mysql-test/t/subselect.test'
> --- a/mysql-test/t/subselect.test 2011-11-21 20:16:01 +0000
> +++ b/mysql-test/t/subselect.test 2011-11-22 23:46:21 +0000
> @@ -4778,4 +4778,47 @@ set @@optimizer_switch='in_to_exists=on,
> select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))));
> drop table t1;
>
> +--echo #
> +--echo # LP BUG#747278 incorrect values of the NULL (no rows) single
> +--echo # row subquery requested via element_index() interface
> +--echo #
> +
> +CREATE TABLE t1 (f1a int, f1b int) ;
> +INSERT IGNORE INTO t1 VALUES (1,1),(2,2);
> +CREATE TABLE t2 ( f2 int);
> +INSERT IGNORE INTO t2 VALUES (3),(4);
> +CREATE TABLE t3 (f3a int default 1, f3b int default 2);
> +INSERT INTO t3 VALUES (1,1),(2,2);
> +
> +# check different IN with switches where the bug was found
> +set @old_optimizer_switch = @@session.optimizer_switch;
> +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
> +
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +
> +set @@session.optimizer_switch=@old_optimizer_switch;
> +
> +# check different IN with default switches
> +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1);
> +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2;
> +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1);
> +
> +# other row operation with NULL single row subquery also should work
> +select (null, null) = (null, null);
> +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0);
> +
> +drop tables t1,t2,t3;
> +--echo # return optimizer switch changed in the beginning of this test
> set optimizer_switch=@subselect_tmp;
>
> === modified file 'sql/item.cc'
> --- a/sql/item.cc 2011-11-02 08:05:07 +0000
> +++ b/sql/item.cc 2011-11-22 23:46:21 +0000
> @@ -8201,6 +8201,20 @@ void Item_cache::print(String *str, enum
> str->append(')');
> }
>
> +/**
> + Assign to this cache NULL value if it is possible
> +*/
> +
> +void Item_cache::set_null()
> +{
> + if (maybe_null)
> + {
> + null_value= TRUE;
> + value_cached= TRUE;
> + }
> +}
> +
> +
> bool Item_cache_int::cache_value()
> {
> if (!example)
> @@ -8671,6 +8685,20 @@ void Item_cache_row::bring_value()
> }
>
>
> +/**
> + Assign to this cache NULL value if it is possible
> +*/
> +
> +void Item_cache_row::set_null()
> +{
> + Item_cache::set_null();
> + if (!values)
> + return;
> + for (uint i= 0; i < item_count; i++)
> + values[i]->set_null();
> +};
> +
> +
> Item_type_holder::Item_type_holder(THD *thd, Item *item)
> :Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item))
> {
>
> === modified file 'sql/item.h'
> --- a/sql/item.h 2011-11-14 17:24:36 +0000
> +++ b/sql/item.h 2011-11-22 23:46:21 +0000
> @@ -3651,6 +3651,7 @@ public:
> return false;
> return example->is_expensive_processor(arg);
> }
> + virtual void set_null();
> };
>
>
> @@ -3825,6 +3826,7 @@ public:
> DBUG_VOID_RETURN;
> }
> bool cache_value();
> + virtual void set_null();
> };
>
>
>
> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc 2011-11-21 14:56:32 +0000
> +++ b/sql/item_subselect.cc 2011-11-22 23:46:21 +0000
> @@ -893,7 +893,10 @@ void Item_singlerow_subselect::reset()
> {
> Item_subselect::reset();
> if (value)
> - value->null_value= TRUE;
> + {
> + for(uint i= 0; i < engine->cols(); i++)
> + row[i]->set_null();
> + }
> }
>
>
> @@ -1004,6 +1007,11 @@ void Item_singlerow_subselect::fix_lengt
> */
> if (engine->no_tables())
> maybe_null= engine->may_be_null();
> + else
> + {
> + for (uint i= 0; i < max_columns; i++)
> + row[i]->maybe_null= TRUE;
> + }
> }
>
>
>
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
