maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #03704
Re: [Commits] Rev 2845: Fixed LP bug #664594 and other bugs leading to invalid execution in file:///home/igor/maria/maria-5.3-mwl128/
Hello Igor,
Ok to push.
On Wed, Nov 03, 2010 at 12:26:19PM -0700, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-mwl128/
>
> ------------------------------------------------------------
> revno: 2845
> revision-id: igor@xxxxxxxxxxxx-20101103192618-17ii8dyn1h2qzdy8
> parent: igor@xxxxxxxxxxxx-20101102235032-vh451jmuugv1gsr2
> committer: Igor Babaev <igor@xxxxxxxxxxxx>
> branch nick: maria-5.3-mwl128
> timestamp: Wed 2010-11-03 12:26:18 -0700
> message:
> Fixed LP bug #664594 and other bugs leading to invalid execution
> plans or wrong results due to the fact that JOIN_CACHE functions
> ignored the possibility of interleaving materialized semijoin
> tables with tables whose records were stored in join buffers.
> This fixes would become mostly unnecessary if the new code of
> mwl 90 was merged into 5.3 right now.
> Yet the fix the code of optimize_wo_join_buffering was needed
> in any case.
> === modified file 'mysql-test/r/explain.result'
> --- a/mysql-test/r/explain.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/explain.result 2010-11-03 19:26:18 +0000
> @@ -195,16 +195,16 @@
> flush tables;
> EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR)
> +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
> flush tables;
> SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
> dt
> flush tables;
> EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR)
> +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
> flush tables;
> SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
> dt
>
> === modified file 'mysql-test/r/group_by.result'
> --- a/mysql-test/r/group_by.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/group_by.result 2010-11-03 19:26:18 +0000
> @@ -1543,7 +1543,8 @@
> (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
> CREATE TABLE t2 (a INT, b INT, KEY(a));
> INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
> EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
>
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/subselect.result 2010-11-03 19:26:18 +0000
> @@ -2831,9 +2831,10 @@
> explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 1.00
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
> Warnings:
> -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
> +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N'))
> explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
> @@ -4203,8 +4204,8 @@
> CREATE INDEX I2 ON t1 (b);
> EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
> +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
> +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> a b
> CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
> @@ -4213,15 +4214,15 @@
> CREATE INDEX I2 ON t2 (b);
> EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
> +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where
> +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2)
> SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
> a b
> EXPLAIN
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
> +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
> +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
> a b
> DROP TABLE t1,t2;
>
> === modified file 'mysql-test/r/subselect3.result'
> --- a/mysql-test/r/subselect3.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/subselect3.result 2010-11-03 19:26:18 +0000
> @@ -103,7 +103,7 @@
> 1 1
> show status like '%Handler_read_rnd_next';
> Variable_name Value
> -Handler_read_rnd_next 5
> +Handler_read_rnd_next 11
> delete from t2;
> insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
> set optimizer_switch='subquery_cache=off';
> @@ -1112,7 +1112,8 @@
> explain select * from (select a from t0) X where a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>)
> +1 PRIMARY subselect3 eq_ref unique_key unique_key 5 func 1
> +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20
> 2 DERIVED t0 ALL NULL NULL NULL NULL 11
> drop table t0, t1;
> create table t0 (a int);
> @@ -1124,16 +1125,18 @@
> insert into t3 select A.a + 10*B.a from t0 A, t0 B;
> explain select * from t3 where a in (select kp1 from t1 where kp1<20);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index
> create table t4 (pk int primary key);
> insert into t4 select a from t3;
> explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
> and t4.pk=t1.c);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
> -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR
> +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index
> drop table t1, t3, t4;
> create table t1 (a int) as select * from t0 where a < 5;
> set @save_max_heap_table_size=@@max_heap_table_size;
> @@ -1261,12 +1264,14 @@
> create table t2 as select * from t1;
> explain select * from t2 where a in (select b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
> explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1
> +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
> drop table t1,t2;
> create table t1 (a int, b int);
> insert into t1 select a,a from t0;
> @@ -1295,7 +1300,8 @@
> explain select * from t0 where a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t0 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
> select * from t0 where a in (select a from t1);
> a
> 10.24
> @@ -1308,7 +1314,8 @@
> explain select * from t0 where a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t0 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
> select * from t0 where a in (select a from t1);
> a
> 2008-01-01
>
> === modified file 'mysql-test/r/subselect3_jcl6.result'
> --- a/mysql-test/r/subselect3_jcl6.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/subselect3_jcl6.result 2010-11-03 19:26:18 +0000
> @@ -110,7 +110,7 @@
> 1 1
> show status like '%Handler_read_rnd_next';
> Variable_name Value
> -Handler_read_rnd_next 5
> +Handler_read_rnd_next 11
> delete from t2;
> insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
> set optimizer_switch='subquery_cache=off';
> @@ -1031,7 +1031,7 @@
> t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort
> -1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (incremental, BNL join)
> +1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join)
> 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (incremental, BNL join)
> 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where
> 2 SUBQUERY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join)
> @@ -1039,7 +1039,6 @@
> t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
> a b c
> 256 67 NULL
> -256 67 NULL
> drop table t1, t11, t12, t21, t22;
> create table t1(a int);
> insert into t1 values (0),(1);
> @@ -1120,7 +1119,8 @@
> explain select * from (select a from t0) X where a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join)
> +1 PRIMARY subselect3 eq_ref unique_key unique_key 5 func 1
> +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20
> 2 DERIVED t0 ALL NULL NULL NULL NULL 11
> drop table t0, t1;
> create table t0 (a int);
> @@ -1132,16 +1132,18 @@
> insert into t3 select A.a + 10*B.a from t0 A, t0 B;
> explain select * from t3 where a in (select kp1 from t1 where kp1<20);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index
> create table t4 (pk int primary key);
> insert into t4 select a from t3;
> explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
> and t4.pk=t1.c);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan
> -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR
> +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index
> drop table t1, t3, t4;
> create table t1 (a int) as select * from t0 where a < 5;
> set @save_max_heap_table_size=@@max_heap_table_size;
> @@ -1269,12 +1271,14 @@
> create table t2 as select * from t1;
> explain select * from t2 where a in (select b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
> explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1
> +2 SUBQUERY t1 ref a a 10 const,test.t2.a 8 Using index
> drop table t1,t2;
> create table t1 (a int, b int);
> insert into t1 select a,a from t0;
> @@ -1303,7 +1307,8 @@
> explain select * from t0 where a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t0 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
> select * from t0 where a in (select a from t1);
> a
> 10.24
> @@ -1316,7 +1321,8 @@
> explain select * from t0 where a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t0 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 4 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4
> select * from t0 where a in (select a from t1);
> a
> 2008-01-01
> @@ -1404,7 +1410,7 @@
> );
> id select_type table type possible_keys key key_len ref rows filtered Extra
> 1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00
> -1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
> +1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join)
> 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where
> 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer (flat, BKA join)
> Warnings:
>
> === modified file 'mysql-test/r/subselect_no_mat.result'
> --- a/mysql-test/r/subselect_no_mat.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/subselect_no_mat.result 2010-11-03 19:26:18 +0000
> @@ -4207,8 +4207,8 @@
> CREATE INDEX I2 ON t1 (b);
> EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
> +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
> +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
> a b
> CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
> @@ -4217,15 +4217,15 @@
> CREATE INDEX I2 ON t2 (b);
> EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
> +1 PRIMARY t2 ALL I2 NULL NULL NULL 2 Using where
> +1 PRIMARY t2 ref I1 I1 4 test.t2.b 2 Using where; Using index; FirstMatch(t2)
> SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
> a b
> EXPLAIN
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
> -1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
> +1 PRIMARY t1 ALL I2 NULL NULL NULL 2 Using where
> +1 PRIMARY t1 ref I1 I1 2 test.t1.b 2 Using where; Using index; FirstMatch(t1)
> SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
> a b
> DROP TABLE t1,t2;
>
> === modified file 'mysql-test/r/subselect_sj.result'
> --- a/mysql-test/r/subselect_sj.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/subselect_sj.result 2010-11-03 19:26:18 +0000
> @@ -1062,8 +1062,10 @@
> WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 5
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1)
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3)
> +1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where
> SELECT *
> FROM t1
> WHERE t1.val IN (SELECT t2.val FROM t2
>
> === modified file 'mysql-test/r/subselect_sj2.result'
> --- a/mysql-test/r/subselect_sj2.result 2010-10-18 20:33:05 +0000
> +++ b/mysql-test/r/subselect_sj2.result 2010-11-03 19:26:18 +0000
> @@ -52,7 +52,8 @@
> explain select * from t3 where b in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 ALL b NULL NULL NULL 10
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
> select * from t3 where b in (select a from t1);
> a b pk1 pk2 pk3
> 1 1 1 1 1
>
> === modified file 'mysql-test/r/subselect_sj2_jcl6.result'
> --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-10-18 20:33:05 +0000
> +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-11-03 19:26:18 +0000
> @@ -59,7 +59,8 @@
> explain select * from t3 where b in (select a from t1);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 ALL b NULL NULL NULL 10
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer (flat, BNL join)
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1
> +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
> select * from t3 where b in (select a from t1);
> a b pk1 pk2 pk3
> 1 1 1 1 1
>
> === modified file 'mysql-test/r/subselect_sj_jcl6.result'
> --- a/mysql-test/r/subselect_sj_jcl6.result 2010-10-27 23:31:22 +0000
> +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-11-03 19:26:18 +0000
> @@ -1069,8 +1069,10 @@
> WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL 5
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
> +1 PRIMARY subselect3 eq_ref unique_key unique_key 14 func 1
> +1 PRIMARY subselect2 eq_ref unique_key unique_key 14 func 1
> +3 SUBQUERY t3 ALL NULL NULL NULL NULL 5 Using where
> +2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where
> SELECT *
> FROM t1
> WHERE t1.val IN (SELECT t2.val FROM t2
>
> === modified file 'mysql-test/r/type_datetime.result'
> --- a/mysql-test/r/type_datetime.result 2010-06-26 10:05:41 +0000
> +++ b/mysql-test/r/type_datetime.result 2010-11-03 19:26:18 +0000
> @@ -537,8 +537,8 @@
> select * from t1
> where id in (select id from t1 as x1 where (t1.cur_date is null));
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
> -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
> +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
> Warnings:
> Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
> Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0))
> @@ -549,8 +549,8 @@
> select * from t2
> where id in (select id from t2 as x1 where (t2.cur_date is null));
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
> -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2)
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary
> +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
> Warnings:
> Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
> Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0))
>
> === modified file 'sql/sql_join_cache.cc'
> --- a/sql/sql_join_cache.cc 2010-10-27 23:37:33 +0000
> +++ b/sql/sql_join_cache.cc 2010-11-03 19:26:18 +0000
> @@ -137,7 +137,44 @@
> *descr_ptr= copy_ptr;
> return len;
> }
> -
> +
> +/*
> + Get the next table whose records are stored in the join buffer of this cache
> +
> + SYNOPSIS
> + get_next_table()
> + tab the table for which the next table is to be returned
> +
> + DESCRIPTION
> + For a given table whose records are stored in this cache the function
> + returns the next such table if there is any.
> + The function takes into account that the tables whose records are
> + are stored in the same cache now can interleave with tables from
> + materialized semijoin subqueries.
> +
> + TODO
> + This function should be modified/simplified after the new code for
> + materialized semijoins is merged.
> +
> + RETURN
> + The next join table whose records are stored in the buffer of this cache
> + if such table exists, 0 - otherwise
> +*/
> +
> +JOIN_TAB *JOIN_CACHE::get_next_table(JOIN_TAB *tab)
> +{
> +
> + if (++tab == join_tab)
> + return NULL;
> + if (join_tab->first_sjm_sibling)
> + return tab;
> + uint i= tab-join->join_tab;
> + while (sj_is_materialize_strategy(join->best_positions[i].sj_strategy) &&
> + i < join->tables)
> + i+= join->best_positions[i].n_sj_tables;
> + return join->join_tab+i < join_tab ? join->join_tab+i : NULL;
> +}
> +
>
> /*
> Determine different counters of fields associated with a record in the cache
> @@ -159,7 +196,9 @@
> void JOIN_CACHE::calc_record_fields()
> {
> JOIN_TAB *tab = prev_cache ? prev_cache->join_tab :
> - join->join_tab+join->const_tables;
> + (join_tab->first_sjm_sibling ?
> + join_tab->first_sjm_sibling :
> + join->join_tab+join->const_tables);
> tables= join_tab-tab;
>
> fields= 0;
> @@ -169,7 +208,7 @@
> data_field_ptr_count= 0;
> referenced_fields= 0;
>
> - for ( ; tab < join_tab ; tab++)
> + for ( ; tab ; tab= get_next_table(tab))
> {
> tab->calc_used_field_length(FALSE);
> flag_fields+= test(tab->used_null_fields || tab->used_uneven_bit_fields);
> @@ -222,7 +261,8 @@
> cache= this;
> do
> {
> - for (tab= cache->join_tab-cache->tables; tab < cache->join_tab ; tab++)
> + for (tab= cache->join_tab-cache->tables; tab ;
> + tab= cache->get_next_table(tab))
> {
> uint key_args;
> bitmap_clear_all(&tab->table->tmp_set);
> @@ -338,7 +378,7 @@
> ©);
>
> /* Create fields for all null bitmaps and null row flags that are needed */
> - for (tab= join_tab-tables; tab < join_tab; tab++)
> + for (tab= join_tab-tables; tab; tab= get_next_table(tab))
> {
> TABLE *table= tab->table;
>
> @@ -425,7 +465,8 @@
> while (ext_key_arg_cnt)
> {
> cache= cache->prev_cache;
> - for (tab= cache->join_tab-cache->tables; tab < cache->join_tab ; tab++)
> + for (tab= cache->join_tab-cache->tables; tab;
> + tab= cache->get_next_table(tab))
> {
> CACHE_FIELD *copy_end;
> MY_BITMAP *key_read_set= &tab->table->tmp_set;
> @@ -475,7 +516,7 @@
>
> /* Now create local fields that are used to build ref for this key access */
> copy= field_descr+flag_fields;
> - for (tab= join_tab-tables; tab < join_tab ; tab++)
> + for (tab= join_tab-tables; tab; tab= get_next_table(tab))
> {
> length+= add_table_data_fields_to_join_cache(tab, &tab->table->tmp_set,
> &data_field_count, ©,
> @@ -531,7 +572,7 @@
> CACHE_FIELD *copy= field_descr+flag_fields+data_field_count;
> CACHE_FIELD **copy_ptr= blob_ptr+data_field_ptr_count;
>
> - for (tab= join_tab-tables; tab < join_tab; tab++)
> + for (tab= join_tab-tables; tab; tab= get_next_table(tab))
> {
> MY_BITMAP *rem_field_set;
> TABLE *table= tab->table;
> @@ -1341,6 +1382,7 @@
> end_pos= pos= cp;
> *is_full= last_record;
>
> + last_written_is_null_compl= 0;
> if (!join_tab->first_unmatched && join_tab->on_precond)
> {
> join_tab->found= 0;
> @@ -1351,8 +1393,6 @@
> last_written_is_null_compl= 1;
> }
> }
> - else
> - last_written_is_null_compl= 0;
>
> return (uint) (cp-init_pos);
> }
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-10-30 22:14:36 +0000
> +++ b/sql/sql_select.cc 2010-11-03 19:26:18 +0000
> @@ -7441,6 +7441,7 @@
> join join for which the check is performed
> options options of the join
> no_jbuf_after don't use join buffering after table with this number
> + prev_tab previous join table
> icp_other_tables_ok OUT TRUE if condition pushdown supports
> other tables presence
> idx_cond_fact_out OUT TRUE if condition pushed to the index is factored
> @@ -7568,6 +7569,7 @@
> uint check_join_cache_usage(JOIN_TAB *tab,
> JOIN *join, ulonglong options,
> uint no_jbuf_after,
> + JOIN_TAB *prev_tab,
> bool *icp_other_tables_ok,
> bool *idx_cond_fact_out)
> {
> @@ -7587,7 +7589,7 @@
>
> *icp_other_tables_ok= TRUE;
> *idx_cond_fact_out= TRUE;
> - if (cache_level == 0 || i == join->const_tables)
> + if (cache_level == 0 || i == join->const_tables || !prev_tab)
> return 0;
>
> if (options & SELECT_NO_JOIN_CACHE)
> @@ -7633,7 +7635,7 @@
> if (tab->first_sj_inner_tab && tab->first_sj_inner_tab != tab &&
> !tab->first_sj_inner_tab->use_join_cache)
> goto no_join_cache;
> - if (!tab[-1].use_join_cache)
> + if (!prev_tab->use_join_cache)
> {
> /*
> Check whether table tab and the previous one belong to the same nest of
> @@ -7655,7 +7657,7 @@
> }
>
> if (!force_unlinked_cache)
> - prev_cache= tab[-1].cache;
> + prev_cache= prev_tab->cache;
>
> switch (tab->type) {
> case JT_ALL:
> @@ -7807,6 +7809,12 @@
> return TRUE; /* purecov: inspected */
> tab->sorted= TRUE;
> }
> +
> + /*
> + SJ-Materialization
> + */
> + if (!(i >= first_sjm_table && i < last_sjm_table))
> + tab->first_sjm_sibling= NULL;
> if (sj_is_materialize_strategy(join->best_positions[i].sj_strategy))
> {
> /* This is a start of semi-join nest */
> @@ -7819,23 +7827,52 @@
>
> if (setup_sj_materialization(tab))
> return TRUE;
> + for (uint j= first_sjm_table; j != last_sjm_table; j++)
> + join->join_tab[j].first_sjm_sibling= join->join_tab + first_sjm_table;
> }
> table->status=STATUS_NO_RECORD;
> pick_table_access_method (tab);
>
> + /*
> + This loop currently can be executed only once as the function
> + check_join_cache_usage does not change the value of tab->type.
> + It won't be true for the future code.
> + */
> + for ( ; ; )
> + {
> + enum join_type tab_type= tab->type;
> + switch (tab->type) {
> + case JT_SYSTEM:
> + case JT_CONST:
> + case JT_EQ_REF:
> + case JT_REF:
> + case JT_REF_OR_NULL:
> + case JT_ALL:
> + if ((jcl= check_join_cache_usage(tab, join, options,
> + no_jbuf_after,
> + i == last_sjm_table ?
> + join->join_tab+first_sjm_table :
> + tab-1,
> + &icp_other_tables_ok,
> + &idx_cond_fact_out)))
> + {
> + tab->use_join_cache= TRUE;
> + tab[-1].next_select=sub_select_cache;
> + }
> + break;
> + default:
> + ;
> + }
> + if (tab->type == tab_type)
> + break;
> + }
> +
> switch (tab->type) {
> case JT_SYSTEM: // Only happens with left join
> case JT_CONST: // Only happens with left join
> /* Only happens with outer joins */
> tab->read_first_record= tab->type == JT_SYSTEM ?
> join_read_system :join_read_const;
> - if ((jcl= check_join_cache_usage(tab, join, options,
> - no_jbuf_after, &icp_other_tables_ok,
> - &idx_cond_fact_out)))
> - {
> - tab->use_join_cache= TRUE;
> - tab[-1].next_select=sub_select_cache;
> - }
> if (table->covering_keys.is_set(tab->ref.key) &&
> !table->no_keyread)
> {
> @@ -7849,13 +7886,6 @@
> case JT_EQ_REF:
> tab->read_record.unlock_row= join_read_key_unlock_row;
> /* fall through */
> - if ((jcl= check_join_cache_usage(tab, join, options,
> - no_jbuf_after, &icp_other_tables_ok,
> - &idx_cond_fact_out)))
> - {
> - tab->use_join_cache= TRUE;
> - tab[-1].next_select=sub_select_cache;
> - }
> if (table->covering_keys.is_set(tab->ref.key) &&
> !table->no_keyread)
> {
> @@ -7875,13 +7905,6 @@
> }
> delete tab->quick;
> tab->quick=0;
> - if ((jcl= check_join_cache_usage(tab, join, options,
> - no_jbuf_after, &icp_other_tables_ok,
> - &idx_cond_fact_out)))
> - {
> - tab->use_join_cache= TRUE;
> - tab[-1].next_select=sub_select_cache;
> - }
> if (table->covering_keys.is_set(tab->ref.key) &&
> !table->no_keyread)
> table->enable_keyread();
> @@ -7896,12 +7919,6 @@
> Also don't use cache if this is the first table in semi-join
> materialization nest.
> */
> - if (check_join_cache_usage(tab, join, options, no_jbuf_after,
> - &icp_other_tables_ok, &idx_cond_fact_out))
> - {
> - tab->use_join_cache= TRUE;
> - tab[-1].next_select=sub_select_cache;
> - }
> /* These init changes read_record */
> if (tab->use_quick == 2)
> {
> @@ -9563,6 +9580,11 @@
> Item_equal *upper= item_field->find_item_equal(upper_levels);
> Item_field *item= item_field;
> TABLE_LIST *field_sjm= embedding_sjm(item_field);
> + if (!field_sjm)
> + {
> + current_sjm= NULL;
> + current_sjm_head= NULL;
> + }
>
> /*
> Check if "item_field=head" equality is already guaranteed to be true
> @@ -10629,7 +10651,7 @@
> {
> /* Find the best access method that would not use join buffering */
> best_access_path(join, rs, reopt_remaining_tables, i,
> - test(i < no_jbuf_before), rec_count,
> + TRUE, rec_count,
> &pos, &loose_scan_pos);
> }
> else
>
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h 2010-10-30 22:14:36 +0000
> +++ b/sql/sql_select.h 2010-11-03 19:26:18 +0000
> @@ -306,6 +306,8 @@
> */
> uint sj_strategy;
>
> + struct st_join_table *first_sjm_sibling;
> +
> void cleanup();
> inline bool is_using_loose_index_scan()
> {
> @@ -1035,6 +1037,8 @@
> buff= 0;
> }
>
> + JOIN_TAB *get_next_table(JOIN_TAB *tab);
> +
> friend class JOIN_CACHE_HASHED;
> friend class JOIN_CACHE_BNL;
> friend class JOIN_CACHE_BKA;
>
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
