Hi Sanja, Ok to push.
On Wed, Nov 23, 2011 at 01:46:22AM +0200, [email protected] wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-lpb747278/ > > ------------------------------------------------------------ > revno: 3308 > revision-id: [email protected] > parent: [email protected] > committer: [email protected] > 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 > [email protected] > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
