diff options
59 files changed, 1027 insertions, 281 deletions
diff --git a/BUILD/SETUP.sh b/BUILD/SETUP.sh index c254304a5a8..c1a05fb1007 100755 --- a/BUILD/SETUP.sh +++ b/BUILD/SETUP.sh @@ -178,7 +178,7 @@ base_configs="--prefix=$prefix --enable-assembler " base_configs="$base_configs --with-extra-charsets=complex " base_configs="$base_configs --enable-thread-safe-client " base_configs="$base_configs --with-big-tables" -base_configs="$base_configs --with-plugin-aria --with-aria-tmp-tables --without-plugin-innodb_plugin" +base_configs="$base_configs --with-plugin-aria --with-aria-tmp-tables" # Compile our client programs with static libraries to allow them to be moved base_configs="$base_configs --with-mysqld-ldflags=-static --with-client-ldflags=-static" diff --git a/configure.in b/configure.in index 227b7ba2da5..66799d13472 100644 --- a/configure.in +++ b/configure.in @@ -13,7 +13,7 @@ dnl When changing the major version number please also check the switch dnl statement in mysqlbinlog::check_master_version(). You may also need dnl to update version.c in ndb. -AC_INIT([MariaDB Server], [5.3.4-MariaDB-rc], [], [mysql]) +AC_INIT([MariaDB Server], [5.3.5-MariaDB-ga], [], [mysql]) AC_CONFIG_SRCDIR([sql/mysqld.cc]) AC_CANONICAL_SYSTEM diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 4d6269044c1..e6ef03d6ad5 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2518,7 +2518,8 @@ sub setup_vardir() { else { # hm, what paths work for debs and for rpms ? - for (<$bindir/lib/mysql/plugin/*.so>, + for (<$bindir/lib64/mysql/plugin/*.so>, + <$bindir/lib/mysql/plugin/*.so>, <$bindir/lib/plugin/*.dll>) { my $pname=basename($_); diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 49d5c409e31..2f0e0bd371b 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1458,14 +1458,14 @@ WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM t3 t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t ref PRIMARY,c c 4 func 2 Using where; Using index +2 DEPENDENT SUBQUERY t eq_ref PRIMARY,c PRIMARY 4 func 1 Using where EXPLAIN SELECT * FROM t1 , t2 WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t3 ref PRIMARY,c c 4 func 2 Using where; Using index +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,c PRIMARY 4 func 1 Using where SELECT * FROM t1 , t2 WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t); b a diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 9660640baaa..f1d3777e097 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1845,7 +1845,7 @@ NULL EXPLAIN EXTENDED SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 1 PRIMARY t1 range a a 4 NULL 4 100.00 Using where; Using index; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 858d31e05c8..3d0502e163c 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2407,7 +2407,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 Using index 2 MATERIALIZED t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index e631c7dbe5a..a397592ae66 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -571,6 +571,17 @@ f1 DROP TABLE t1,t2; End of 5.1 tests # +# LP bug:938518 HAVING does not reject the result of aggregation +# +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0); +SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1; +MIN(t.pk) +NULL +SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10; +MIN(t.pk) +drop table t1; +# # LP bug #791761: MAX over an empty join + HAVING # CREATE TABLE t1 (a int, b int , KEY (b)) ; @@ -594,3 +605,33 @@ SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0 WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ; f DROP TABLE t1,t2,t3; +# +# LP bug:806955 HAVING not observed with aggregate +subquery +# +CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) ); +INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (7); +CREATE TABLE t3 (f3 int); +INSERT INTO t3 VALUES (0),(8); +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=off,materialization=off'; +SELECT MIN( t1.f10 ) AS field1 +FROM t1 , t2 +WHERE t2.f2 IN ( SELECT f3 FROM t3 ) +HAVING field1 < 's'; +field1 +explain extended +SELECT MIN( t1.f10 ) AS field1 +FROM t1 , t2 +WHERE t2.f2 IN ( SELECT f3 FROM t3 ) +HAVING field1 < 's'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t1 index NULL f10 4 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` join `test`.`t2` where <expr_cache><7>(<in_optimizer>(7,<exists>(select `test`.`t3`.`f3` from `test`.`t3` where (<cache>(7) = `test`.`t3`.`f3`)))) having (`field1` < 's') +set optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +End of 5.2 tests diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index 033d9bc53a1..5808d25c50e 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -605,7 +605,7 @@ a b EXPLAIN SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 2 MATERIALIZED s1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED s2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index b1cfd80490d..e196cb8170b 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1275,6 +1275,55 @@ Handler_read_rnd_next 1 DROP TABLE t1, t2; End of 5.1 tests # +# Bug #43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS +# +create table t1(c1 int primary key, c2 char(10)) engine=myisam; +create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +insert into t1 values(1,'a'); +insert into t2 values(1,'a', 1); +insert into t3 values(1,'a', 1); +insert into t3 values(2,'b',2); +insert into t4 values(1,'a', 1); +insert into t4 values(2,'a', 2); +insert into t4 values(3,'a', 3); +insert into t4 values(4,'a', 4); +insert into t1 values(2,'b'); +insert into t1 values(3,'c'); +EXPLAIN +SELECT * +FROM t4 JOIN +(t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) +ON t4.ref_t1=t1.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +EXPLAIN +SELECT STRAIGHT_JOIN * +FROM t4 JOIN +(t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) +ON t4.ref_t1=t1.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +EXPLAIN +SELECT * +FROM t4 STRAIGHT_JOIN +(t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) +ON t4.ref_t1=t1.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 ALL NULL NULL NULL NULL 4 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t4.ref_t1 1 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +drop table t1,t2,t3,t4; +End of 5.2 tests +# # BUG#724275: Crash in JOIN::optimize in maria-5.3 # create table t1 (a int); diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result index 7ea314f5c87..3138701fc76 100644 --- a/mysql-test/r/join_outer_innodb.result +++ b/mysql-test/r/join_outer_innodb.result @@ -25,3 +25,130 @@ WHERE (t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b) AND t2.b = 4 GROUP BY 1; a DROP TABLE t1,t2; +# +Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN +# +CREATE TABLE t1 ( +pk int(11) NOT NULL, +col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,'1'); +CREATE TABLE t2 ( +pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 ( +pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1); +CREATE TABLE t4 ( +pk int(11) NOT NULL, +col_int int(11) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t4 VALUES (1,1,1,'1'); +CREATE TABLE t5 ( +col_int int(11) DEFAULT NULL, +col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t5 VALUES (1,'1'); +CREATE TABLE t6 ( +col_int_key int(11) DEFAULT NULL, +col_varchar_10_latin1_key varchar(10) DEFAULT NULL, +pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t6 VALUES (1,'1',1); +SELECT STRAIGHT_JOIN t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +pk pk +1 NULL +EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +SELECT t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +pk pk +1 NULL +EXPLAIN SELECT t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 3fc4de3da06..72f4b22b06a 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -1,4 +1,5 @@ drop table if exists t1,t2; +drop DATABASE if exists mysqltest_1; create table t1(n int); insert into t1 values (1); lock tables t1 write; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 9e06f2d3841..e25df1c7634 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4821,10 +4821,10 @@ SET SESSION join_buffer_size = 2048; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 1 SIMPLE t2 ALL NULL NULL NULL NULL 12 +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 const 1 +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.f1 1 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 5b36791024f..f2cadea01e1 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -4832,10 +4832,10 @@ SET SESSION join_buffer_size = 2048; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 1 SIMPLE t2 ALL NULL NULL NULL NULL 12 -1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 const 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.f1 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 9e06f2d3841..e25df1c7634 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -4821,10 +4821,10 @@ SET SESSION join_buffer_size = 2048; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 1 SIMPLE t2 ALL NULL NULL NULL NULL 12 +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using where -1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 const 1 +1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.f1 1 1 SIMPLE t5 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index bca4eb677de..327cb5b2563 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2973,7 +2973,7 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cac 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 <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED 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`.`flag` = 'N')) @@ -3563,7 +3563,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); @@ -3575,7 +3575,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary DROP TABLE t1; create table t1( f1 int,f2 int); @@ -4471,14 +4471,14 @@ SET @save_join_cache_level=@@join_cache_level; SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: @@ -5585,7 +5585,7 @@ WHERE col_varchar_nokey IN (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 ref idx_cvk_cik idx_cvk_cik 9 const,const 1 Using where; Using index SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN @@ -5598,7 +5598,7 @@ WHERE (col_varchar_nokey, 'x') IN (SELECT col_varchar_key, col_varchar_key2 FROM it2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED it2 ref idx_cvk_cvk2_cik,idx_cvk_cik idx_cvk_cvk2_cik 8 const,const 1 Using where; Using index SELECT col_int_nokey FROM ot WHERE (col_varchar_nokey, 'x') IN diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 4e3247e9da8..e8822ebf6bd 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1249,7 +1249,7 @@ EXPLAIN SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where @@ -1258,7 +1258,7 @@ c1 c1 c1 EXPLAIN SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t4 index NULL PRIMARY 3 NULL 2 Using index; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where @@ -2141,6 +2141,20 @@ WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3 c a b 10 7 0 drop table t2, t3, t4; +# +# BUG#934597: Assertion `! is_set()' failed in Diagnostics_area::set_ok_status(THD... +# +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('u'),('k'); +CREATE TABLE t2 AS +SELECT a AS field1 FROM t1 +WHERE ( SELECT alias1.a +FROM t1 AS alias1 +) IS NOT NULL; +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t2; +ERROR 42S02: Unknown table 't2' +DROP TABLE t1; set optimizer_switch=@subselect4_tmp; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 4d02e5012e2..b7cc7e8a3ed 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1473,7 +1473,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk @@ -1861,6 +1861,57 @@ WHERE 'condition'='impossible' MIN(a) NULL DROP TABLE t1; +# +# BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT +# +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); +# Should use Materialization: +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +SHOW STATUS LIKE 'Created_tmp_tables'; +Variable_name Value +Created_tmp_tables 3 +DROP TABLE t1,t2,t3; +# +# BUG#939009: Crash with aggregate function in IN subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on,semijoin=on'; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (7,1), (4,2), (7,7); +CREATE TABLE t2 ( c INT ); +INSERT INTO t2 VALUES (4), (7), (6); +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (`test`.`t1`.`b` = 7) and (isnull(`<subquery2>`.`MAX(c)`) or (`<subquery2>`.`MAX(c)` = 7))) +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); +a b +EXPLAIN +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); +a b +SET optimizer_switch=@save_optimizer_switch; +DROP TABLE t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; @@ -1983,7 +2034,7 @@ SET @@optimizer_switch='default,semijoin=on,materialization=on'; EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result index e5f2df1c5c1..900bad0183a 100644 --- a/mysql-test/r/subselect_mat_cost.result +++ b/mysql-test/r/subselect_mat_cost.result @@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)) AND Language IN ('English','Spanish'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan -2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 2 Using index condition; Using where +2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 1 Using index condition; Using where 2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index select count(*) from CountryLanguage diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index e170debd6d2..1167e9483c9 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -2979,7 +2979,7 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optim 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 <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED 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`.`flag` = 'N')) @@ -3569,7 +3569,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); @@ -3581,7 +3581,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary DROP TABLE t1; create table t1( f1 int,f2 int); @@ -4477,14 +4477,14 @@ SET @save_join_cache_level=@@join_cache_level; SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: @@ -5591,7 +5591,7 @@ WHERE col_varchar_nokey IN (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 ref idx_cvk_cik idx_cvk_cik 9 const,const 1 Using where; Using index SELECT col_int_nokey FROM ot WHERE col_varchar_nokey IN @@ -5604,7 +5604,7 @@ WHERE (col_varchar_nokey, 'x') IN (SELECT col_varchar_key, col_varchar_key2 FROM it2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED it2 ref idx_cvk_cvk2_cik,idx_cvk_cik idx_cvk_cvk2_cik 8 const,const 1 Using where; Using index SELECT col_int_nokey FROM ot WHERE (col_varchar_nokey, 'x') IN diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index f92b33ff651..1d0aae275ed 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -802,7 +802,7 @@ INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','f EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); 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 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func,func 1 100.00 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) @@ -1053,8 +1053,8 @@ AND t1.val IN (SELECT t3.val FROM t3 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 <subquery3> eq_ref distinct_key distinct_key 14 func 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 14 func 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func 1 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 5 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where SELECT * @@ -1633,9 +1633,9 @@ select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED C ALL NULL NULL NULL NULL 3 3 MATERIALIZED D ALL NULL NULL NULL NULL 3 drop table t1, t2; @@ -2323,7 +2323,7 @@ explain SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 13 Using where 2 MATERIALIZED t2 ref b b 4 test.t3.a 1 Using index SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); @@ -2443,7 +2443,7 @@ EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 4e9672446a3..1ee01030b4b 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -183,7 +183,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -251,7 +251,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -292,7 +292,7 @@ from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 Using where 2 MATERIALIZED t1 index a a 5 NULL 10 Using where; Using index 2 MATERIALIZED t2 ref a a 5 test.t1.a 1 Using index 2 MATERIALIZED t3 ref a a 5 test.t1.a 1 Using index @@ -721,7 +721,7 @@ The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) @@ -878,7 +878,7 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 1b7d65275ab..e69548dcf5f 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -133,7 +133,7 @@ set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b @@ -195,7 +195,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -263,7 +263,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -304,7 +304,7 @@ from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 Using where 2 MATERIALIZED t1 index a a 5 NULL 10 Using where; Using index 2 MATERIALIZED t2 ref a a 5 test.t1.a 1 Using index 2 MATERIALIZED t3 ref a a 5 test.t1.a 1 Using index @@ -735,7 +735,7 @@ The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) @@ -892,7 +892,7 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t3.a 1 Using where; Using join buffer (flat, BNLH join) 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 @@ -990,7 +990,7 @@ EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); a b @@ -999,7 +999,7 @@ EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY <subquery2> hash_ALL distinct_key #hash#distinct_key 5 test.t1.b 1 Using join buffer (flat, BNLH join) +1 PRIMARY <subquery2> hash_ALL distinct_key #hash#distinct_key 4 test.t1.b 1 Using join buffer (flat, BNLH join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using temporary SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); a b diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 64d1ce2ef74..f6e98a37007 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -185,7 +185,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -253,7 +253,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -294,7 +294,7 @@ from t0 where a in (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 Using where 2 MATERIALIZED t1 index a a 5 NULL 10 Using where; Using index 2 MATERIALIZED t2 ref a a 5 test.t1.a 1 Using index 2 MATERIALIZED t3 ref a a 5 test.t1.a 1 Using index @@ -723,7 +723,7 @@ The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) @@ -880,7 +880,7 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 769373429da..baa2aec8aea 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -815,7 +815,7 @@ INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','f EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); 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 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func,func 1 100.00 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) @@ -1066,8 +1066,8 @@ AND t1.val IN (SELECT t3.val FROM t3 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 <subquery3> eq_ref distinct_key distinct_key 14 func 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 14 func 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 13 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 13 func 1 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 5 Using where 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where SELECT * @@ -1646,9 +1646,9 @@ select * from t1 A, t1 B where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED C ALL NULL NULL NULL NULL 3 3 MATERIALIZED D ALL NULL NULL NULL NULL 3 drop table t1, t2; @@ -1993,7 +1993,7 @@ explain extended SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); 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 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (incremental, BNL join) 2 MATERIALIZED t4 index f2 f2 5 NULL 2 100.00 Using index @@ -2337,7 +2337,7 @@ explain SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 13 Using where 2 MATERIALIZED t2 ref b b 4 test.t3.a 1 Using index SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a); @@ -2457,7 +2457,7 @@ EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); @@ -2773,7 +2773,7 @@ SELECT * FROM t1 LEFT JOIN t2 ON (c = b) WHERE (a, b) IN (SELECT a, b FROM t1 t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 1 PRIMARY t2 ref idx_c idx_c 4 test.t1.b 2 Using where; Using index 2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 SELECT * FROM t1 LEFT JOIN t2 ON (c = b) @@ -2788,7 +2788,7 @@ SELECT * FROM t1 LEFT JOIN t3 ON (c = b) WHERE (a, b) IN (SELECT a, b FROM t1 t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 1 PRIMARY t3 ref idx_c idx_c 4 test.t1.b 2 Using where 2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 SELECT * FROM t1 LEFT JOIN t3 ON (c = b) @@ -2804,7 +2804,7 @@ SELECT * FROM t1 LEFT JOIN t2 ON (c = b) WHERE (a, b) IN (SELECT a, b FROM t1 t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 1 PRIMARY t2 ref idx_c idx_c 4 test.t1.b 2 Using where; Using index 2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 SELECT * FROM t1 LEFT JOIN t2 ON (c = b) @@ -2819,7 +2819,7 @@ SELECT * FROM t1 LEFT JOIN t3 ON (c = b) WHERE (a, b) IN (SELECT a, b FROM t1 t); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 1 PRIMARY t3 ref idx_c idx_c 4 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 SELECT * FROM t1 LEFT JOIN t3 ON (c = b) diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index b21236de7b9..9a238eaaccd 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -47,7 +47,7 @@ explain extended select * from t1 where a1 in (select b1 from t2 where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0')) @@ -59,7 +59,7 @@ explain extended select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0')) @@ -71,7 +71,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0')) @@ -83,7 +83,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) @@ -106,7 +106,7 @@ explain extended select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 # -1 PRIMARY <subquery2> eq_ref distinct_key # 9 # 1 100.00 # +1 PRIMARY <subquery2> eq_ref distinct_key # 8 # 1 100.00 # 2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 # Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`max(b1)` = `test`.`t1i`.`a1`) @@ -153,7 +153,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 2 MATERIALIZED t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`max(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) @@ -165,12 +165,12 @@ prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) execute st1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 2 MATERIALIZED t2i range NULL it2i3 9 NULL 3 Using index for group-by execute st1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 2 MATERIALIZED t2i range NULL it2i3 9 NULL 3 Using index for group-by prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; execute st2; @@ -185,7 +185,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 2 MATERIALIZED t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) @@ -233,7 +233,7 @@ explain extended select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1.a1,test.t1.a2 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) @@ -245,7 +245,7 @@ explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 test.t1i.a1,test.t1i.a2 1 100.00 2 MATERIALIZED t2i index NULL it2i3 18 NULL 5 100.00 Using index Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`)) @@ -299,8 +299,8 @@ where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 16 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where @@ -341,8 +341,8 @@ b2 in (select c2 from t3 where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 100.00 5 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where @@ -367,7 +367,7 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 16 func,func 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 5 MATERIALIZED t3c ALL NULL NULL NULL NULL 4 100.00 Using where 5 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) @@ -436,7 +436,7 @@ where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 4 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where @@ -460,7 +460,7 @@ a1 = c1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 100.00 Using where 4 MATERIALIZED t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where @@ -645,7 +645,7 @@ from t1_16 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 20 func 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 19 func 1 100.00 Using where 2 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` > '0') and (`test`.`t1_16`.`a1` = substr(`test`.`t2_16`.`b1`,1,16))) @@ -675,7 +675,7 @@ from t1_16 where a1 in (select group_concat(b1) from t2_16 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_16.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_16.a1 1 100.00 Using where 2 MATERIALIZED t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -760,7 +760,7 @@ from t1_512 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 517 func 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 516 func 1 100.00 Using where 2 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` > '0') and (`test`.`t1_512`.`a1` = substr(`test`.`t2_512`.`b1`,1,512))) @@ -775,7 +775,7 @@ from t1_512 where a1 in (select group_concat(b1) from t2_512 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where 2 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -789,7 +789,7 @@ from t1_512 where a1 in (select group_concat(b1) from t2_512 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_512.a1 1 100.00 Using where 2 MATERIALIZED t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -870,7 +870,7 @@ from t1_1024 where a1 in (select group_concat(b1) from t2_1024 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where 2 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -884,7 +884,7 @@ from t1_1024 where a1 in (select group_concat(b1) from t2_1024 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1024.a1 1 100.00 Using where 2 MATERIALIZED t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -965,7 +965,7 @@ from t1_1025 where a1 in (select group_concat(b1) from t2_1025 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where 2 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -979,7 +979,7 @@ from t1_1025 where a1 in (select group_concat(b1) from t2_1025 group by b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 260 test.t1_1025.a1 1 100.00 Using where 2 MATERIALIZED t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`) @@ -1001,7 +1001,7 @@ from t1bit where (a1, a2) in (select b1, b2 from t2bit); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t2bit ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` semi join (`test`.`t2bit`) where 1 @@ -1187,7 +1187,7 @@ insert into t1 values (5); explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) @@ -1236,7 +1236,7 @@ insert into t1 values ('aa', 'aaaa'); 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 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 2 func 1 Using where 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 select a,b from t1 where b in (select a from t1); a b @@ -1273,7 +1273,7 @@ GROUP BY t3i ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 const 1 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 const 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using temporary @@ -1510,7 +1510,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk @@ -1878,7 +1878,7 @@ insert into t2 values explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL i_a NULL NULL NULL 8 Using where select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1); a b @@ -1898,6 +1898,58 @@ WHERE 'condition'='impossible' MIN(a) NULL DROP TABLE t1; +# +# BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT +# +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); +# Should use Materialization: +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +SHOW STATUS LIKE 'Created_tmp_tables'; +Variable_name Value +Created_tmp_tables 2 +DROP TABLE t1,t2,t3; +# +# BUG#939009: Crash with aggregate function in IN subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on,semijoin=on'; +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (7,1), (4,2), (7,7); +CREATE TABLE t2 ( c INT ); +INSERT INTO t2 VALUES (4), (7), (6); +EXPLAIN EXTENDED +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (`test`.`t1`.`b` = 7) and (isnull(`<subquery2>`.`MAX(c)`) or (`<subquery2>`.`MAX(c)` = 7))) +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); +a b +EXPLAIN +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * FROM t1 +WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); +a b +SET optimizer_switch=@save_optimizer_switch; +DROP TABLE t1,t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result index 8df74536de7..2a3768c8c50 100644 --- a/mysql-test/r/subselect_sj_nonmerged.result +++ b/mysql-test/r/subselect_sj_nonmerged.result @@ -40,14 +40,14 @@ alter table t3 add primary key(a); # (despite that subquery's join output estimate is 50 rows) explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) # Compare to this which really will have 50 record combinations: explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b, t1.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 50 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 50 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) @@ -57,7 +57,7 @@ SET optimizer_switch='outer_join_with_cache=off'; explain select * from t3 where a in (select max(t2.a) from t1 left join t2 on t1.a=t2.a group by t2.b, t1.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 50 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 50 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 8 <subquery2>.max(t2.a) 1 Using where; Using index 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using temporary 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using where @@ -69,7 +69,7 @@ t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 1 PRIMARY t4 ALL a NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 test.t4.a 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t4.a 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B; @@ -77,8 +77,8 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using where -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) 1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 12 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) @@ -114,7 +114,7 @@ insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; # The following must use non-merged SJ-Materialization: explain select * from t1 X join t0 Y on X.a < Y.a where X.a in (select max(a) from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 1 PRIMARY X ref a a 5 <subquery2>.max(a) 1 Using index 1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 diff --git a/mysql-test/suite/maria/r/maria3.result b/mysql-test/suite/maria/r/maria3.result index 47c02999087..24c1e48f70b 100644 --- a/mysql-test/suite/maria/r/maria3.result +++ b/mysql-test/suite/maria/r/maria3.result @@ -621,3 +621,27 @@ ERROR 23000: Duplicate entry '2' for key 'a' insert into t1 values(3); insert into t2 values(3); drop table t1, t2; +CREATE TABLE t1 ( +a INT PRIMARY KEY, +b CHAR(255), +c VARCHAR(2048), +d VARCHAR(18990), +e CHAR(128), +f CHAR(192) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; +INSERT INTO t1 VALUES +(1,'A','B','C','','D'), +(2,'Abcdefghi','E','F','','G'); +CREATE TABLE t2 ( +g INT PRIMARY KEY, +h CHAR(32), +i CHAR(255), +j TEXT +) ENGINE=MyISAM DEFAULT CHARSET=utf8; +INSERT INTO t2 VALUES (1,'M','','H'), +(2,'N','','H'); +SELECT * FROM t1, t2 WHERE a = g ORDER BY b; +a b c d e f g h i j +1 A B C D 1 M H +2 Abcdefghi E F G 2 N H +drop table t1,t2; diff --git a/mysql-test/suite/maria/t/maria3.test b/mysql-test/suite/maria/t/maria3.test index bc1dc47337f..2db0e23571b 100644 --- a/mysql-test/suite/maria/t/maria3.test +++ b/mysql-test/suite/maria/t/maria3.test @@ -524,6 +524,37 @@ insert into t2 values(3); connection default; drop table t1, t2; +# +# BUG#909635 - MariaDB crashes on a select with long varchar and blob fields +# + +CREATE TABLE t1 ( + a INT PRIMARY KEY, + b CHAR(255), + c VARCHAR(2048), + d VARCHAR(18990), + e CHAR(128), + f CHAR(192) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES + (1,'A','B','C','','D'), + (2,'Abcdefghi','E','F','','G'); + +CREATE TABLE t2 ( + g INT PRIMARY KEY, + h CHAR(32), + i CHAR(255), + j TEXT +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +INSERT INTO t2 VALUES (1,'M','','H'), + (2,'N','','H'); + +SELECT * FROM t1, t2 WHERE a = g ORDER BY b; +drop table t1,t2; + + # End of 5.1 tests --disable_result_log diff --git a/mysql-test/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result index b73b393f9da..15554216056 100644 --- a/mysql-test/suite/pbxt/r/group_min_max.result +++ b/mysql-test/suite/pbxt/r/group_min_max.result @@ -2256,7 +2256,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 Using where +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 1 Using index 2 MATERIALIZED t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 0a065e61edf..96e14cdf785 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -2842,7 +2842,7 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cac 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 <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED 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`.`flag` = 'N')) @@ -3435,7 +3435,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary ALTER TABLE t1 ADD INDEX(a); SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); @@ -3447,7 +3447,7 @@ EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL a NULL NULL NULL 9 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 21 test.t1.a,test.t1.b 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 9 Using temporary DROP TABLE t1; create table t1( f1 int,f2 int); diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 01342fdf5fa..3edf449c92d 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -589,10 +589,18 @@ ORDER BY t1.f1; DROP TABLE t1,t2; - --echo End of 5.1 tests --echo # +--echo # LP bug:938518 HAVING does not reject the result of aggregation +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0); +SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1; +SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10; +drop table t1; + +--echo # --echo # LP bug #791761: MAX over an empty join + HAVING --echo # @@ -616,3 +624,36 @@ SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0 WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ; DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug:806955 HAVING not observed with aggregate +subquery +--echo # + +CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) ); +INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0); + +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (7); + +CREATE TABLE t3 (f3 int); +INSERT INTO t3 VALUES (0),(8); + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=off,materialization=off'; + +SELECT MIN( t1.f10 ) AS field1 +FROM t1 , t2 +WHERE t2.f2 IN ( SELECT f3 FROM t3 ) +HAVING field1 < 's'; + +explain extended +SELECT MIN( t1.f10 ) AS field1 +FROM t1 , t2 +WHERE t2.f2 IN ( SELECT f3 FROM t3 ) +HAVING field1 < 's'; + +set optimizer_switch=@save_optimizer_switch; + +drop table t1,t2,t3; + +--echo End of 5.2 tests diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index bfe5f085e93..51bb72e1239 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -941,7 +941,6 @@ SELECT f1 , MIN(f2) FROM v2 GROUP BY f1; drop table t1,t2; drop view v1,v2; - --echo # --echo # BUG#47217 Lost optimization caused slowdown & wrong result. --echo # @@ -964,6 +963,46 @@ DROP TABLE t1, t2; --echo End of 5.1 tests --echo # +--echo # Bug #43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS +--echo # + +create table t1(c1 int primary key, c2 char(10)) engine=myisam; +create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam; +insert into t1 values(1,'a'); +insert into t2 values(1,'a', 1); +insert into t3 values(1,'a', 1); +insert into t3 values(2,'b',2); +insert into t4 values(1,'a', 1); +insert into t4 values(2,'a', 2); +insert into t4 values(3,'a', 3); +insert into t4 values(4,'a', 4); +insert into t1 values(2,'b'); +insert into t1 values(3,'c'); + +EXPLAIN +SELECT * +FROM t4 JOIN + (t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) + ON t4.ref_t1=t1.c1; + +EXPLAIN +SELECT STRAIGHT_JOIN * +FROM t4 JOIN + (t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) + ON t4.ref_t1=t1.c1; + +EXPLAIN +SELECT * +FROM t4 STRAIGHT_JOIN + (t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1) + ON t4.ref_t1=t1.c1; + +drop table t1,t2,t3,t4; +--echo End of 5.2 tests + +--echo # --echo # BUG#724275: Crash in JOIN::optimize in maria-5.3 --echo # diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test index 565bb72b152..8d6fa71bc8f 100644 --- a/mysql-test/t/join_outer_innodb.test +++ b/mysql-test/t/join_outer_innodb.test @@ -39,3 +39,70 @@ SELECT STRAIGHT_JOIN t1.a FROM t1 RIGHT JOIN t2 ON t1.b = t2.b GROUP BY 1; DROP TABLE t1,t2; + +--echo # +--echo Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN +--echo # + +CREATE TABLE t1 ( + pk int(11) NOT NULL, + col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,'1'); +CREATE TABLE t2 ( + pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 ( + pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1); +CREATE TABLE t4 ( + pk int(11) NOT NULL, + col_int int(11) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t4 VALUES (1,1,1,'1'); +CREATE TABLE t5 ( + col_int int(11) DEFAULT NULL, + col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t5 VALUES (1,'1'); +CREATE TABLE t6 ( + col_int_key int(11) DEFAULT NULL, + col_varchar_10_latin1_key varchar(10) DEFAULT NULL, + pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t6 VALUES (1,'1',1); + +# EXPLAIN of query above (t2 is before t5 in plan) + +let $rest_of_query=t6a.pk, t2.pk +FROM t6 AS t6a + LEFT JOIN + ( + t2 + RIGHT JOIN + ( + (t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) + LEFT JOIN + (t5 JOIN t6 AS t6b + ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) + ON t1.pk = t5.col_int + ) + ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key + AND t5.col_varchar_10_utf8_key = 0 + ) + ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; + +eval SELECT STRAIGHT_JOIN $rest_of_query; +eval EXPLAIN SELECT STRAIGHT_JOIN $rest_of_query; + +# right result (same query, just remove STRAIGHT_JOIN): + +eval SELECT $rest_of_query; +eval EXPLAIN SELECT $rest_of_query; + +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index f05821af5a8..044af931489 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -5,6 +5,7 @@ --disable_warnings drop table if exists t1,t2; +drop DATABASE if exists mysqltest_1; --enable_warnings # Test to see if select will get the lock ahead of low priority update diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index b2e15c457d7..eee0140815b 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1779,6 +1779,22 @@ WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3 drop table t2, t3, t4; +--echo # +--echo # BUG#934597: Assertion `! is_set()' failed in Diagnostics_area::set_ok_status(THD... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('u'),('k'); +--error ER_SUBQUERY_NO_1_ROW +CREATE TABLE t2 AS + SELECT a AS field1 FROM t1 + WHERE ( SELECT alias1.a + FROM t1 AS alias1 + ) IS NOT NULL; +--error ER_BAD_TABLE_ERROR +DROP TABLE t2; +DROP TABLE t1; + + set optimizer_switch=@subselect4_tmp; SET optimizer_switch= @@global.optimizer_switch; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 0d04b3f984a..252fbb525eb 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1559,6 +1559,49 @@ WHERE a IN ( DROP TABLE t1; +--echo # +--echo # BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT +--echo # +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); +--echo # Should use Materialization: +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +SHOW STATUS LIKE 'Created_tmp_tables'; +DROP TABLE t1,t2,t3; + +--echo # +--echo # BUG#939009: Crash with aggregate function in IN subquery +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on,semijoin=on'; + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (7,1), (4,2), (7,7); + +CREATE TABLE t2 ( c INT ); +INSERT INTO t2 VALUES (4), (7), (6); + +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); + +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); +SELECT * FROM t1 + WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b); + +SET optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysys/mf_keycache.c b/mysys/mf_keycache.c index a038061d9d0..e0d495ce8e0 100644 --- a/mysys/mf_keycache.c +++ b/mysys/mf_keycache.c @@ -5945,6 +5945,10 @@ int init_key_cache(KEY_CACHE *keycache, uint key_cache_block_size, ((PARTITIONED_KEY_CACHE_CB *) keycache_cb)->partitions : 0; DBUG_ASSERT(partitions <= MAX_KEY_CACHE_PARTITIONS); + keycache->key_cache_mem_size= + keycache->partitions ? + ((PARTITIONED_KEY_CACHE_CB *) keycache_cb)->key_cache_mem_size : + ((SIMPLE_KEY_CACHE_CB *) keycache_cb)->key_cache_mem_size; if (blocks > 0) keycache->can_be_used= 1; return blocks; @@ -6008,6 +6012,11 @@ int resize_key_cache(KEY_CACHE *keycache, uint key_cache_block_size, ((PARTITIONED_KEY_CACHE_CB *)(keycache->keycache_cb))->partitions; } + keycache->key_cache_mem_size= + keycache->partitions ? + ((PARTITIONED_KEY_CACHE_CB *)(keycache->keycache_cb))->key_cache_mem_size : + ((SIMPLE_KEY_CACHE_CB *)(keycache->keycache_cb))->key_cache_mem_size; + keycache->can_be_used= (blocks >= 0); } return blocks; diff --git a/mysys/thr_lock.c b/mysys/thr_lock.c index 99a498cfdd5..32b38105d1f 100644 --- a/mysys/thr_lock.c +++ b/mysys/thr_lock.c @@ -168,7 +168,8 @@ thr_lock_owner_equal(THR_LOCK_OWNER *rhs, THR_LOCK_OWNER *lhs) static uint found_errors=0; static int check_lock(struct st_lock_list *list, const char* lock_type, - const char *where, my_bool same_owner, my_bool no_cond) + const char *where, my_bool same_owner, my_bool no_cond, + my_bool read_lock) { THR_LOCK_DATA *data,**prev; uint count=0; @@ -181,6 +182,23 @@ static int check_lock(struct st_lock_list *list, const char* lock_type, for (data=list->data; data && count++ < MAX_LOCKS ; data=data->next) { + if (data->type == TL_UNLOCK) + { + fprintf(stderr, + "Warning: Found unlocked lock at %s: %s\n", + lock_type, where); + return 1; + } + if ((read_lock && data->type > TL_READ_NO_INSERT) || + (!read_lock && data->type <= TL_READ_NO_INSERT)) + { + fprintf(stderr, + "Warning: Found %s lock in %s queue at %s: %s\n", + read_lock ? "write" : "read", + read_lock ? "read" : "write", + lock_type, where); + return 1; + } if (data->type != last_lock_type) last_lock_type=TL_IGNORE; if (data->prev != prev) @@ -237,11 +255,14 @@ static void check_locks(THR_LOCK *lock, const char *where, if (found_errors < MAX_FOUND_ERRORS) { - if (check_lock(&lock->write,"write",where,1,1) | - check_lock(&lock->write_wait,"write_wait",where,0,0) | - check_lock(&lock->read,"read",where,0,1) | - check_lock(&lock->read_wait,"read_wait",where,0,0)) + if (check_lock(&lock->write,"write",where,1,1,0) | + check_lock(&lock->write_wait,"write_wait",where,0,0,0) | + check_lock(&lock->read,"read",where,0,1,1) | + check_lock(&lock->read_wait,"read_wait",where,0,0,1)) + { + DBUG_ASSERT(my_assert_on_error == 0); found_errors++; + } if (found_errors < MAX_FOUND_ERRORS) { @@ -592,18 +613,17 @@ wait_for_lock(struct st_lock_list *wait, THR_LOCK_DATA *data, static enum enum_thr_lock_result -thr_lock(THR_LOCK_DATA *data, THR_LOCK_OWNER *owner, - enum thr_lock_type lock_type) +thr_lock(THR_LOCK_DATA *data, THR_LOCK_OWNER *owner) { THR_LOCK *lock=data->lock; enum enum_thr_lock_result result= THR_LOCK_SUCCESS; struct st_lock_list *wait_queue; THR_LOCK_DATA *lock_owner; + enum thr_lock_type lock_type= data->type; DBUG_ENTER("thr_lock"); data->next=0; data->cond=0; /* safety */ - data->type=lock_type; data->owner= owner; /* Must be reset ! */ data->priority&= ~THR_LOCK_LATE_PRIV; VOID(pthread_mutex_lock(&lock->mutex)); @@ -912,9 +932,7 @@ void thr_unlock(THR_LOCK_DATA *data, uint unlock_flags) if (lock_type == TL_READ_NO_INSERT) lock->read_no_write_count--; data->type=TL_UNLOCK; /* Mark unlocked */ - check_locks(lock,"after releasing lock", lock_type, 1); wake_up_waiters(lock); - check_locks(lock,"end of thr_unlock", lock_type, 1); pthread_mutex_unlock(&lock->mutex); DBUG_VOID_RETURN; } @@ -934,6 +952,7 @@ static void wake_up_waiters(THR_LOCK *lock) enum thr_lock_type lock_type; DBUG_ENTER("wake_up_waiters"); + check_locks(lock, "before waking up waiters", TL_UNLOCK, 1); if (!lock->write.data) /* If no active write locks */ { data=lock->write_wait.data; @@ -1087,7 +1106,7 @@ thr_multi_lock(THR_LOCK_DATA **data, uint count, THR_LOCK_OWNER *owner) /* lock everything */ for (pos=data,end=data+count; pos < end ; pos++) { - enum enum_thr_lock_result result= thr_lock(*pos, owner, (*pos)->type); + enum enum_thr_lock_result result= thr_lock(*pos, owner); if (result != THR_LOCK_SUCCESS) { /* Aborted */ thr_multi_unlock(data,(uint) (pos-data), 0); diff --git a/scripts/make_binary_distribution.sh b/scripts/make_binary_distribution.sh index 789f3e3ce2e..070b396a34c 100644 --- a/scripts/make_binary_distribution.sh +++ b/scripts/make_binary_distribution.sh @@ -285,7 +285,8 @@ if [ x"$BASE_SYSTEM" != x"netware" ] ; then pkgplugindir=@pkgplugindir@ \ pkgsuppdir=@pkgsuppdir@ \ mandir=@mandir@ \ - infodir=@infodir@ + infodir=@infodir@ \ + libexecdir=@prefix@/libexec # ---------------------------------------------------------------------- # Rename top directory, and set DEST to the new directory @@ -301,7 +302,10 @@ if [ x"$BASE_SYSTEM" != x"netware" ] ; then if [ -z "$gcclib" ] ; then echo "Warning: Compiler doesn't tell libgcc.a!" elif [ -f "$gcclib" ] ; then + { + mkdir -p $DEST/lib $CP $gcclib $DEST/lib/libmygcc.a + } else echo "Warning: Compiler result '$gcclib' not found / no file!" fi diff --git a/scripts/mysql_config.sh b/scripts/mysql_config.sh index f1d46c0b2c0..ce61c09aa15 100644 --- a/scripts/mysql_config.sh +++ b/scripts/mysql_config.sh @@ -86,7 +86,7 @@ bindir='@bindir@' # If installed, search for the compiled in directory first (might be "lib64") pkglibdir='@pkglibdir@' pkglibdir_rel=`echo $pkglibdir | sed -e "s;^$basedir/;;"` -fix_path pkglibdir $pkglibdir_rel lib/mysql lib +fix_path pkglibdir $pkglibdir_rel lib64/mysql lib64 lib/mysql lib plugindir='@pkgplugindir@' diff --git a/sql/field_conv.cc b/sql/field_conv.cc index ed910c4ef70..c99eb62eb57 100644 --- a/sql/field_conv.cc +++ b/sql/field_conv.cc @@ -117,6 +117,11 @@ static void do_outer_field_to_null_str(Copy_field *copy) int set_field_to_null(Field *field) { + if (field->table->null_catch_flags & CHECK_ROW_FOR_NULLS_TO_REJECT) + { + field->table->null_catch_flags|= REJECT_ROW_DUE_TO_NULL_FIELDS; + return -1; + } if (field->real_maybe_null()) { field->set_null(); @@ -160,6 +165,11 @@ set_field_to_null(Field *field) int set_field_to_null_with_conversions(Field *field, bool no_conversions) { + if (field->table->null_catch_flags & CHECK_ROW_FOR_NULLS_TO_REJECT) + { + field->table->null_catch_flags|= REJECT_ROW_DUE_TO_NULL_FIELDS; + return -1; + } if (field->real_maybe_null()) { field->set_null(); diff --git a/sql/item.h b/sql/item.h index 9891d690798..aeae1b1faf2 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1032,7 +1032,6 @@ public: virtual bool update_table_bitmaps_processor(uchar *arg) { return 0; } virtual bool view_used_tables_processor(uchar *arg) { return 0; } virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; } - virtual bool clear_sum_processor(uchar *opt_arg) { return 0; } virtual bool is_subquery_processor (uchar *opt_arg) { return 0; } virtual bool limit_index_condition_pushdown_processor(uchar *opt_arg) { diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 52a2c2a7d8b..b40467eb21c 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -4180,6 +4180,11 @@ bool subselect_hash_sj_engine::init(List<Item> *tmp_columns, uint subquery_id) memcpy(name, buf, len+1); result_sink->get_tmp_table_param()->materialized_subquery= true; + if (item->substype() == Item_subselect::IN_SUBS && + ((Item_in_subselect*)item)->is_jtbm_merged) + { + result_sink->get_tmp_table_param()->force_not_null_cols= true; + } if (result_sink->create_result_table(thd, tmp_columns, TRUE, tmp_create_options, name, TRUE, TRUE)) diff --git a/sql/item_sum.h b/sql/item_sum.h index 11caf37e6fe..c970dec5082 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -390,7 +390,6 @@ public: { return trace_unsupported_by_check_vcol_func_processor(func_name()); } - bool clear_sum_processor(uchar *arg) { clear(); return 0; } }; diff --git a/sql/lock.cc b/sql/lock.cc index 3f9b3a9e18b..57ced99417b 100644 --- a/sql/lock.cc +++ b/sql/lock.cc @@ -195,31 +195,33 @@ int mysql_lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags) MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, uint flags, bool *need_reopen) { - TABLE *write_lock_used; MYSQL_LOCK *sql_lock; + TABLE *write_lock_used; + int rc; DBUG_ENTER("mysql_lock_tables(tables)"); *need_reopen= FALSE; - if (mysql_lock_tables_check(thd, tables, count, flags)) - DBUG_RETURN(NULL); - if (!(sql_lock= get_lock_data(thd, tables, count, GET_LOCK_STORE_LOCKS, - &write_lock_used)) || - ! sql_lock->table_count) - DBUG_RETURN(sql_lock); + if (mysql_lock_tables_check(thd, tables, count, flags)) + DBUG_RETURN (NULL); - if (mysql_lock_tables(thd, sql_lock, write_lock_used != 0, flags, - need_reopen)) + for (;;) { - /* Clear the lock type of all lock data to avoid reusage. */ - reset_lock_data(sql_lock, 1); + if (!(sql_lock= get_lock_data(thd, tables, count, GET_LOCK_STORE_LOCKS, + &write_lock_used)) || + !sql_lock->table_count) + break; + rc= mysql_lock_tables(thd, sql_lock, write_lock_used != 0, flags, + need_reopen); + if (!rc) + break; // Got lock my_free(sql_lock, MYF(0)); - sql_lock= 0; + if (rc > 0) + DBUG_RETURN(0); // Failed } DBUG_RETURN(sql_lock); } - /** Lock a table based on a MYSQL_LOCK structure. @@ -232,120 +234,150 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, @param need_reopen Out parameter, TRUE if some tables were altered or deleted and should be reopened by caller. - @return 0 ok - @return 1 error + @return 0 ok + @return 1 fatal error + @return -1 retry */ -bool mysql_lock_tables(THD *thd, MYSQL_LOCK *sql_lock, - bool write_lock_used, - uint flags, bool *need_reopen) +int mysql_lock_tables(THD *thd, MYSQL_LOCK *sql_lock, + bool write_lock_used, + uint flags, bool *need_reopen) { + int res= 0; int rc; - bool error= 1; DBUG_ENTER("mysql_lock_tables(sql_lock)"); - *need_reopen= FALSE; - for (;;) + + if (write_lock_used && !(flags & MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK)) { - if (write_lock_used && !(flags & MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK)) + if (global_read_lock) { - if (global_read_lock) + /* + Someone has issued LOCK ALL TABLES FOR READ and we want a write lock + Wait until the lock is gone + */ + if (wait_if_global_read_lock(thd, 1, 1)) { - /* - Someone has issued LOCK ALL TABLES FOR READ and we want a write lock - Wait until the lock is gone - */ - if (wait_if_global_read_lock(thd, 1, 1)) - break; - if (thd->version != refresh_version) - goto retry; + /* Clear the lock type of all lock data to avoid reusage. */ + reset_lock_data(sql_lock, 1); + DBUG_RETURN(1); // Fatal error } - - if (opt_readonly && - !(thd->security_ctx->master_access & SUPER_ACL) && - !thd->slave_thread) + if (thd->version != refresh_version) { - /* - Someone has issued SET GLOBAL READ_ONLY=1 and we want a write lock. - We do not wait for READ_ONLY=0, and fail. - */ - my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only"); - break; + /* Clear the lock type of all lock data to avoid reusage. */ + reset_lock_data(sql_lock, 1); + goto retry; } } - thd_proc_info(thd, "System lock"); - if (lock_external(thd, sql_lock->table, sql_lock->table_count)) - break; - thd_proc_info(thd, "Table lock"); - /* Copy the lock data array. thr_multi_lock() reorders its contens. */ - memcpy(sql_lock->locks + sql_lock->lock_count, sql_lock->locks, - sql_lock->lock_count * sizeof(*sql_lock->locks)); - /* Lock on the copied half of the lock data array. */ - rc= thr_lock_errno_to_mysql[(int) thr_multi_lock(sql_lock->locks + - sql_lock->lock_count, - sql_lock->lock_count, - thd->lock_id)]; - if (rc) /* Locking failed */ + if (opt_readonly && + !(thd->security_ctx->master_access & SUPER_ACL) && + !thd->slave_thread) { + /* + Someone has issued SET GLOBAL READ_ONLY=1 and we want a write lock. + We do not wait for READ_ONLY=0, and fail. + */ + my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only"); + reset_lock_data(sql_lock, 1); + DBUG_RETURN(1); // Fatal error + } + } + + thd_proc_info(thd, "System lock"); + if (lock_external(thd, sql_lock->table, sql_lock->table_count)) + { + /* Clear the lock type of all lock data to avoid reusage. */ + res= 1; // Fatal error + goto end; + } + thd_proc_info(thd, "Table lock"); + DBUG_PRINT("info", ("thd->proc_info %s", thd->proc_info)); + /* Copy the lock data array. thr_multi_lock() reorders its contens. */ + memcpy(sql_lock->locks + sql_lock->lock_count, sql_lock->locks, + sql_lock->lock_count * sizeof(*sql_lock->locks)); + /* Lock on the copied half of the lock data array. */ + rc= thr_lock_errno_to_mysql[(int) thr_multi_lock(sql_lock->locks + + sql_lock->lock_count, + sql_lock->lock_count, + thd->lock_id)]; + if (rc) // Locking failed + { + if (sql_lock->table_count) VOID(unlock_external(thd, sql_lock->table, sql_lock->table_count)); - if (rc > 1) - { - /* a timeout or a deadlock */ - my_error(rc, MYF(0)); - break; - } - /* We where aborted and should try again from upper level*/ - thd->some_tables_deleted= 1; + + /* + reset_lock_data is required here. If thr_multi_lock fails it + resets lock type for tables, which were locked before (and + including) one that caused error. Lock type for other tables + preserved. + */ + reset_lock_data(sql_lock, 0); + + if (rc > 1) + { + my_error(rc, MYF(0)); + DBUG_RETURN(1); } - else + DBUG_ASSERT(rc == 1); // Timeout + thd->some_tables_deleted= 1; // Reopen tables + sql_lock->lock_count= 0; // Locks are already freed + /* Retry */ + } + else + { + /* + Lock worked. Now check that nothing happend while we where waiting + to get the lock that would require us to free it. + */ + if (!thd->some_tables_deleted || (flags & MYSQL_LOCK_IGNORE_FLUSH)) + { + res= 0; + goto end; /* Lock was not aborted. Return to upper level */ + } + if (!thd->open_tables && !(flags & MYSQL_LOCK_NOT_TEMPORARY)) { /* - Lock worked. Now check that nothing happend while we where waiting - to get the lock that would require us to free it. - */ - error= 0; - if (!thd->some_tables_deleted || (flags & MYSQL_LOCK_IGNORE_FLUSH)) - { - /* - Table was not signaled for deletion or we don't care if it was. - Return with table as locked. - */ - break; - } - else if (!thd->open_tables && !(flags & MYSQL_LOCK_NOT_TEMPORARY)) - { - /* - Only using temporary tables, no need to unlock. - We need the flag as open_tables is not enough to distingush if - we are only using temporary tables for tables used trough - the HANDLER interface. + Only using temporary tables, no need to unlock. + We need the flag as open_tables is not enough to distingush if + we are only using temporary tables for tables used trough + the HANDLER interface. - We reset some_tables_deleted as it doesn't make sense to have this - one when we are only using temporary tables. - */ - thd->some_tables_deleted=0; - break; - } - /* some table was altered or deleted. reopen tables marked deleted */ - error= 1; - mysql_unlock_tables(thd, sql_lock, 0); + We reset some_tables_deleted as it doesn't make sense to have this + one when we are only using temporary tables. + */ + thd->some_tables_deleted=0; + goto end; } + /* Free lock and retry */ + } + + /* some table was altered or deleted. reopen tables marked deleted */ + mysql_unlock_tables(thd, sql_lock, 0); retry: - if (flags & MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN) - { - *need_reopen= TRUE; - break; - } - if (wait_for_tables(thd)) - break; // Couldn't open tables - reset_lock_data(sql_lock, 0); // Set org locks and retry + res= -1; // Retry + if (flags & MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN) + { + *need_reopen= TRUE; // Upper level will retry + DBUG_RETURN(1); // Fatal error } + if (wait_for_tables(thd)) + res= 1; // Couldn't open tables +end: thd_proc_info(thd, 0); + if (thd->killed) + { + thd->send_kill_message(); + if (res == 0) + mysql_unlock_tables(thd,sql_lock,0); + else + reset_lock_data(sql_lock, 1); + res= 1; // Fatal + } thd->set_time_after_lock(); - DBUG_RETURN(error); + DBUG_RETURN(res); } diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 71498bb3d41..c27617d56ae 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -2348,9 +2348,9 @@ extern struct st_VioSSLFd * ssl_acceptor_fd; MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count, uint flags, bool *need_reopen); -bool mysql_lock_tables(THD *thd, MYSQL_LOCK *sql_lock, - bool write_lock_used, - uint flags, bool *need_reopen); +int mysql_lock_tables(THD *thd, MYSQL_LOCK *sql_lock, + bool write_lock_used, + uint flags, bool *need_reopen); /* mysql_lock_tables() and open_table() flags bits */ #define MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK 0x0001 diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index d01ef381806..4559b76764e 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -251,8 +251,8 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, Subquery !contains {GROUP BY, ORDER BY [LIMIT], aggregate functions}) && subquery predicate is not under "NOT IN")) - (*) The subquery must be part of a SELECT statement. The current - condition also excludes multi-table update statements. + (*) The subquery must be part of a SELECT or CREATE TABLE ... SELECT statement. + The current condition also excludes multi-table update statements. A note about prepared statements: we want the if-branch to be taken on PREPARE and each EXECUTE. The rewrites are only done once, but we need select_lex->sj_subselects list to be populated for every EXECUTE. @@ -261,7 +261,8 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs, if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) && // 0 !child_select->is_part_of_union() && // 1 parent_unit->first_select()->leaf_tables.elements && // 2 - thd->lex->sql_command == SQLCOM_SELECT && // * + (thd->lex->sql_command == SQLCOM_SELECT || // * + thd->lex->sql_command == SQLCOM_CREATE_TABLE) && // * child_select->outer_select()->leaf_tables.elements && // 2A subquery_types_allow_materialization(in_subs) && (in_subs->is_top_level_item() || //3 @@ -3167,6 +3168,7 @@ bool setup_sj_materialization_part1(JOIN_TAB *sjm_tab) sjm->sjm_table_cols.push_back(*p_item); sjm->sjm_table_param.field_count= subq_select->item_list.elements; + sjm->sjm_table_param.force_not_null_cols= TRUE; if (!(sjm->table= create_tmp_table(thd, &sjm->sjm_table_param, sjm->sjm_table_cols, (ORDER*) 0, diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 11fd5db2020..95a149741de 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -9606,13 +9606,12 @@ open_performance_schema_table(THD *thd, TABLE_LIST *one_table, else { /* - If error in mysql_lock_tables(), open_ltable doesn't close the - table. Thread kill during mysql_lock_tables() is such error. But - open tables cannot be accepted when restoring the open tables - state. + This can happen during a thd->kill or while we are trying to log + data for a stored procedure/trigger and someone causes the table + to be flushed (for example by creating a new trigger for the + table) */ - if (thd->killed) - close_thread_tables(thd); + close_thread_tables(thd); thd->restore_backup_open_tables_state(backup); } diff --git a/sql/sql_class.cc b/sql/sql_class.cc index a0ed1cdc83f..9a72b647261 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -3195,6 +3195,11 @@ int select_materialize_with_stats::send_data(List<Item> &items) if ((res= select_union::send_data(items))) return res; + if (table->null_catch_flags & REJECT_ROW_DUE_TO_NULL_FIELDS) + { + table->null_catch_flags&= ~REJECT_ROW_DUE_TO_NULL_FIELDS; + return 0; + } /* Skip duplicate rows. */ if (write_err == HA_ERR_FOUND_DUPP_KEY || write_err == HA_ERR_FOUND_DUPP_UNIQUE) @@ -3236,6 +3241,7 @@ void TMP_TABLE_PARAM::init() precomputed_group_by= 0; bit_fields_as_long= 0; materialized_subquery= 0; + force_not_null_cols= 0; skip_create_table= 0; DBUG_VOID_RETURN; } diff --git a/sql/sql_class.h b/sql/sql_class.h index c5f500a6b18..20ca9bd47c5 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3081,6 +3081,8 @@ public: bool schema_table; /* TRUE if the temp table is created for subquery materialization. */ bool materialized_subquery; + /* TRUE if all columns of the table are guaranteed to be non-nullable */ + bool force_not_null_cols; /* True if GROUP BY and its aggregate functions are already computed by a table access method (e.g. by loose index scan). In this case @@ -3104,7 +3106,8 @@ public: TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), - schema_table(0), materialized_subquery(0), precomputed_group_by(0), + schema_table(0), materialized_subquery(0), force_not_null_cols(0), + precomputed_group_by(0), force_copy_fields(0), bit_fields_as_long(0), skip_create_table(0) {} ~TMP_TABLE_PARAM() diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc index d789957ffa4..4f1c63930d6 100644 --- a/sql/sql_handler.cc +++ b/sql/sql_handler.cc @@ -676,7 +676,7 @@ retry: /* save open_tables state */ if (handler->lock->lock_count > 0) { - bool lock_error; + int lock_error; handler->lock->locks[0]->type= handler->lock->locks[0]->org_type; lock_error= mysql_lock_tables(thd, handler->lock, 0, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6cf64c4f7c3..c5049e9df31 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -105,7 +105,7 @@ static int return_zero_rows(JOIN *join, select_result *res, List<TABLE_LIST> &tables, List<Item> &fields, bool send_row, ulonglong select_options, const char *info, - Item *having); + Item *having, List<Item> &all_fields); static COND *build_equal_items(THD *thd, COND *cond, COND_EQUAL *inherited, List<TABLE_LIST> *join_list, @@ -2161,6 +2161,15 @@ JOIN::exec() !exec_const_cond->val_int()) zero_result_cause= "Impossible WHERE noticed after reading const tables"; + /* + We've called exec_const_cond->val_int(). This may have caused an error. + */ + if (thd->is_error()) + { + error= thd->is_error(); + DBUG_VOID_RETURN; + } + if (zero_result_cause) { (void) return_zero_rows(this, result, select_lex->leaf_tables, @@ -2168,7 +2177,7 @@ JOIN::exec() send_row_on_empty_set(), select_options, zero_result_cause, - having ? having : tmp_having); + having ? having : tmp_having, all_fields); DBUG_VOID_RETURN; } @@ -3771,15 +3780,18 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, { /* field = expression OR field IS NULL */ old->level= and_level; - old->optimize= KEY_OPTIMIZE_REF_OR_NULL; - /* - Remember the NOT NULL value unless the value does not depend - on other tables. - */ - if (!old->val->used_tables() && old->val->is_null()) - old->val= new_fields->val; - /* The referred expression can be NULL: */ - old->null_rejecting= 0; + if (old->field->maybe_null()) + { + old->optimize= KEY_OPTIMIZE_REF_OR_NULL; + /* + Remember the NOT NULL value unless the value does not depend + on other tables. + */ + if (!old->val->used_tables() && old->val->is_null()) + old->val= new_fields->val; + /* The referred expression can be NULL: */ + old->null_rejecting= 0; + } } else { @@ -10444,7 +10456,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond, static int return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, List<Item> &fields, bool send_row, ulonglong select_options, - const char *info, Item *having) + const char *info, Item *having, List<Item> &all_fields) { DBUG_ENTER("return_zero_rows"); @@ -10474,9 +10486,15 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, if (!table->is_jtbm()) mark_as_null_row(table->table); // All fields are NULL } - if (having && - !having->walk(&Item::clear_sum_processor, FALSE, NULL) && - having->val_int() == 0) + List_iterator_fast<Item> it(all_fields); + Item *item; + /* + Inform all items (especially aggregating) to calculate HAVING correctly, + also we will need it for sending results. + */ + while ((item= it++)) + item->no_rows_in_result(); + if (having && having->val_int() == 0) send_row=0; } if (!(result->send_fields(fields, @@ -10484,13 +10502,7 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables, { bool send_error= FALSE; if (send_row) - { - List_iterator_fast<Item> it(fields); - Item *item; - while ((item= it++)) - item->no_rows_in_result(); send_error= result->send_data(fields) > 0; - } if (!send_error) result->send_eof(); // Should be safe } @@ -12281,6 +12293,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, tbl->table->maybe_null= FALSE; tbl->join_list= table->join_list; repl_list.push_back(tbl); + tbl->dep_tables|= table->dep_tables; } li.replace(repl_list); /* Need to update the name resolution table chain when flattening joins */ @@ -13593,6 +13606,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, table->merge_keys.init(); table->intersect_keys.init(); table->keys_in_use_for_query.init(); + table->no_rows_with_nulls= param->force_not_null_cols; table->s= share; init_tmp_table_share(thd, share, "", 0, tmpname, tmpname); @@ -13672,6 +13686,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, thd->mem_root= mem_root_save; arg= sum_item->set_arg(i, thd, new Item_field(new_field)); thd->mem_root= &table->mem_root; + if (param->force_not_null_cols) + { + new_field->flags|= NOT_NULL_FLAG; + new_field->null_ptr= NULL; + } if (!(new_field->flags & NOT_NULL_FLAG)) { null_count++; @@ -13747,6 +13766,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, agg_item->result_field= new_field; } tmp_from_field++; + if (param->force_not_null_cols) + { + new_field->flags|= NOT_NULL_FLAG; + new_field->null_ptr= NULL; + } reclength+=new_field->pack_length(); if (!(new_field->flags & NOT_NULL_FLAG)) null_count++; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 599dc993483..5c235fd1778 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -57,9 +57,17 @@ int select_union::send_data(List<Item> &values) unit->offset_limit_cnt--; return 0; } + if (table->no_rows_with_nulls) + table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT; fill_record(thd, table->field, values, TRUE, FALSE); if (thd->is_error()) return 1; + if (table->no_rows_with_nulls) + { + table->null_catch_flags&= ~CHECK_ROW_FOR_NULLS_TO_REJECT; + if (table->null_catch_flags) + return 0; + } if ((write_err= table->file->ha_write_tmp_row(table->record[0]))) { diff --git a/sql/table.h b/sql/table.h index 4a1719073b8..9757194f21f 100644 --- a/sql/table.h +++ b/sql/table.h @@ -674,6 +674,9 @@ enum index_hint_type INDEX_HINT_FORCE }; +#define CHECK_ROW_FOR_NULLS_TO_REJECT (1 << 0) +#define REJECT_ROW_DUE_TO_NULL_FIELDS (1 << 1) + struct st_table { st_table() {} /* Remove gcc warning */ @@ -816,6 +819,26 @@ struct st_table { NULL, including columns declared as "not null" (see maybe_null). */ bool null_row; + /* + No rows that contain null values can be placed into this table. + Currently this flag can be set to true only for a temporary table + that used to store the result of materialization of a subquery. + */ + bool no_rows_with_nulls; + /* + This field can contain two bit flags: + CHECK_ROW_FOR_NULLS_TO_REJECT + REJECT_ROW_DUE_TO_NULL_FIELDS + The first flag is set for the dynamic contexts where it is prohibited + to write any null into the table. + The second flag is set only if the first flag is set on. + The informs the outer scope that there was an attept to write null + into a field of the table in the context where it is prohibited. + This flag should be set off as soon as the first flag is set on. + Currently these flags are used only the tables tno_rows_with_nulls set + to true. + */ + uint8 null_catch_flags; /* TODO: Each of the following flags take up 8 bits. They can just as easily diff --git a/storage/innodb_plugin/btr/btr0sea.c b/storage/innodb_plugin/btr/btr0sea.c index 04c273344f3..f10c207b76a 100644 --- a/storage/innodb_plugin/btr/btr0sea.c +++ b/storage/innodb_plugin/btr/btr0sea.c @@ -524,7 +524,7 @@ btr_search_update_hash_ref( { dict_index_t* index; ulint fold; - const rec_t* rec; + rec_t* rec; ut_ad(cursor->flag == BTR_CUR_HASH_FAIL); #ifdef UNIV_SYNC_DEBUG diff --git a/storage/innodb_plugin/ha/ha0ha.c b/storage/innodb_plugin/ha/ha0ha.c index 65046138275..594a10dc431 100644 --- a/storage/innodb_plugin/ha/ha0ha.c +++ b/storage/innodb_plugin/ha/ha0ha.c @@ -106,7 +106,7 @@ ha_insert_for_fold_func( #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG buf_block_t* block, /*!< in: buffer block containing the data */ #endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */ - const rec_t* data) /*!< in: data, must not be NULL */ + rec_t* data) /*!< in: data, must not be NULL */ { hash_cell_t* cell; ha_node_t* node; @@ -238,11 +238,11 @@ ha_search_and_update_if_found_func( /*===============================*/ hash_table_t* table, /*!< in/out: hash table */ ulint fold, /*!< in: folded value of the searched data */ - const rec_t* data, /*!< in: pointer to the data */ + rec_t* data, /*!< in: pointer to the data */ #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG buf_block_t* new_block,/*!< in: block containing new_data */ #endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */ - const rec_t* new_data)/*!< in: new pointer to the data */ + rec_t* new_data)/*!< in: new pointer to the data */ { ha_node_t* node; diff --git a/storage/innodb_plugin/include/ha0ha.h b/storage/innodb_plugin/include/ha0ha.h index 8bba564d153..83a7394123f 100644 --- a/storage/innodb_plugin/include/ha0ha.h +++ b/storage/innodb_plugin/include/ha0ha.h @@ -38,7 +38,7 @@ Looks for an element in a hash table. @return pointer to the data of the first hash table node in chain having the fold number, NULL if not found */ UNIV_INLINE -const rec_t* +rec_t* ha_search_and_get_data( /*===================*/ hash_table_t* table, /*!< in: hash table */ @@ -52,11 +52,11 @@ ha_search_and_update_if_found_func( /*===============================*/ hash_table_t* table, /*!< in/out: hash table */ ulint fold, /*!< in: folded value of the searched data */ - const rec_t* data, /*!< in: pointer to the data */ + rec_t* data, /*!< in: pointer to the data */ #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG buf_block_t* new_block,/*!< in: block containing new_data */ #endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */ - const rec_t* new_data);/*!< in: new pointer to the data */ + rec_t* new_data);/*!< in: new pointer to the data */ #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG /** Looks for an element when we know the pointer to the data and @@ -131,7 +131,7 @@ ha_insert_for_fold_func( #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG buf_block_t* block, /*!< in: buffer block containing the data */ #endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */ - const rec_t* data); /*!< in: data, must not be NULL */ + rec_t* data); /*!< in: data, must not be NULL */ #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG /** @@ -210,7 +210,7 @@ struct ha_node_struct { #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG buf_block_t* block; /*!< buffer block containing the data, or NULL */ #endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */ - const rec_t* data; /*!< pointer to the data */ + rec_t* data; /*!< pointer to the data */ ulint fold; /*!< fold value for the data */ }; diff --git a/storage/innodb_plugin/include/ha0ha.ic b/storage/innodb_plugin/include/ha0ha.ic index 5656e9b7eba..aec28398b5d 100644 --- a/storage/innodb_plugin/include/ha0ha.ic +++ b/storage/innodb_plugin/include/ha0ha.ic @@ -58,7 +58,7 @@ ha_node_set_data_func( #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG buf_block_t* block, /*!< in: buffer block containing the data */ #endif /* UNIV_AHI_DEBUG || UNIV_DEBUG */ - const rec_t* data) /*!< in: pointer to the data */ + rec_t* data) /*!< in: pointer to the data */ { #if defined UNIV_AHI_DEBUG || defined UNIV_DEBUG node->block = block; @@ -111,7 +111,7 @@ Looks for an element in a hash table. @return pointer to the data of the first hash table node in chain having the fold number, NULL if not found */ UNIV_INLINE -const rec_t* +rec_t* ha_search_and_get_data( /*===================*/ hash_table_t* table, /*!< in: hash table */ diff --git a/storage/maria/ma_open.c b/storage/maria/ma_open.c index 0b5ee2feb85..678f74bdda2 100644 --- a/storage/maria/ma_open.c +++ b/storage/maria/ma_open.c @@ -1714,9 +1714,11 @@ my_bool _ma_columndef_write(File file, MARIA_COLUMNDEF *columndef) { uchar buff[MARIA_COLUMNDEF_SIZE]; uchar *ptr=buff; + uint low_offset= (uint) (columndef->offset & 0xffff); + uint high_offset= (uint) (columndef->offset >> 16); mi_int2store(ptr,(ulong) columndef->column_nr); ptr+= 2; - mi_int2store(ptr,(ulong) columndef->offset); ptr+= 2; + mi_int2store(ptr, low_offset); ptr+= 2; mi_int2store(ptr,columndef->type); ptr+= 2; mi_int2store(ptr,columndef->length); ptr+= 2; mi_int2store(ptr,columndef->fill_length); ptr+= 2; @@ -1725,12 +1727,14 @@ my_bool _ma_columndef_write(File file, MARIA_COLUMNDEF *columndef) (*ptr++)= columndef->null_bit; (*ptr++)= columndef->empty_bit; - ptr[0]= ptr[1]= ptr[2]= ptr[3]= 0; ptr+= 4; /* For future */ + mi_int2store(ptr, high_offset); ptr+= 2; + ptr[0]= ptr[1]= 0; ptr+= 2; /* For future */ return my_write(file, buff, (size_t) (ptr-buff), MYF(MY_NABP)) != 0; } uchar *_ma_columndef_read(uchar *ptr, MARIA_COLUMNDEF *columndef) { + uint high_offset; columndef->column_nr= mi_uint2korr(ptr); ptr+= 2; columndef->offset= mi_uint2korr(ptr); ptr+= 2; columndef->type= mi_sint2korr(ptr); ptr+= 2; @@ -1740,7 +1744,9 @@ uchar *_ma_columndef_read(uchar *ptr, MARIA_COLUMNDEF *columndef) columndef->empty_pos= mi_uint2korr(ptr); ptr+= 2; columndef->null_bit= (uint8) *ptr++; columndef->empty_bit= (uint8) *ptr++; - ptr+= 4; + high_offset= mi_uint2korr(ptr); ptr+= 2; + columndef->offset|= ((ulong) high_offset << 16); + ptr+= 2; return ptr; } |