diff options
author | unknown <timour@askmonty.org> | 2011-02-22 12:44:58 +0200 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2011-02-22 12:44:58 +0200 |
commit | 759d71eba1842bbde7b44fc42e1098590b136320 (patch) | |
tree | 36f218066133a216bf706cca7e619b9667c24d40 /mysql-test/r | |
parent | 96efe1cab3861bedc9d1f30812b1d6557d8f1585 (diff) | |
download | mariadb-git-759d71eba1842bbde7b44fc42e1098590b136320.tar.gz |
MWL#89
Split the tests for MWL#89 into two parts - one for bugs
(currently active), and one for functionality tets
(currently in progress, and thus disabled).
Disable the test for LP BUG#718593.
Diffstat (limited to 'mysql-test/r')
-rw-r--r-- | mysql-test/r/subselect_mat_cost.result | 231 | ||||
-rw-r--r-- | mysql-test/r/subselect_mat_cost_bugs.result | 234 |
2 files changed, 234 insertions, 231 deletions
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result index c714be078cf..9d6986cf092 100644 --- a/mysql-test/r/subselect_mat_cost.result +++ b/mysql-test/r/subselect_mat_cost.result @@ -3769,234 +3769,3 @@ drop procedure delete_materialization_data; drop procedure set_all_columns_not_null; drop procedure set_all_columns_nullable; drop table t1, t2, t1_1024, t2_1024; -# -# LP BUG#643424 valgrind warning in choose_subquery_plan() -# -CREATE TABLE t1 ( -pk int(11) NOT NULL AUTO_INCREMENT, -c1 int(11) DEFAULT NULL, -c2 int(11) DEFAULT NULL, -PRIMARY KEY (pk), -KEY c2 (c2)); -INSERT INTO t1 VALUES (1,NULL,2); -INSERT INTO t1 VALUES (2,7,9); -INSERT INTO t1 VALUES (9,NULL,8); -CREATE TABLE t2 ( -pk int(11) NOT NULL AUTO_INCREMENT, -c1 int(11) DEFAULT NULL, -c2 int(11) DEFAULT NULL, -PRIMARY KEY (pk), -KEY c2 (c2)); -INSERT INTO t2 VALUES (1,1,7); -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; -SELECT pk FROM t1 WHERE (c2, c1) IN (SELECT c2, c2 FROM t2); -pk -set session optimizer_switch=@save_optimizer_switch; -drop table t1, t2; -# -# LP BUG#652727 Crash in create_ref_for_key() -# -CREATE TABLE t2 ( -pk int(11) NOT NULL AUTO_INCREMENT, -c1 int(11) DEFAULT NULL, -PRIMARY KEY (pk)); -INSERT INTO t2 VALUES (10,7); -INSERT INTO t2 VALUES (11,1); -INSERT INTO t2 VALUES (17,NULL); -CREATE TABLE t1 ( -pk int(11) NOT NULL AUTO_INCREMENT, -c1 int(11) DEFAULT NULL, -PRIMARY KEY (pk)); -INSERT INTO t1 VALUES (15,1); -INSERT INTO t1 VALUES (19,NULL); -CREATE TABLE t3 (c2 int(11) DEFAULT NULL, KEY c2 (c2)); -INSERT INTO t3 VALUES (1); -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; -SELECT c2 -FROM t3 -WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FROM t1 STRAIGHT_JOIN t2 ON t2.pk = t1.pk); -c2 -set session optimizer_switch=@save_optimizer_switch; -drop table t1, t2, t3; -# -# LP BUG#641245 Crash in Item_equal::contains -# -CREATE TABLE t1 ( -pk int(11) NOT NULL AUTO_INCREMENT, -c1 int(11) DEFAULT NULL, -c2 int(11) DEFAULT NULL, -c3 varchar(1) DEFAULT NULL, -c4 varchar(1) DEFAULT NULL, -PRIMARY KEY (pk), -KEY c2 (c2), -KEY c3 (c3,c2)); -INSERT INTO t1 VALUES (10,7,8,'v','v'); -INSERT INTO t1 VALUES (11,1,9,'r','r'); -INSERT INTO t1 VALUES (12,5,9,'a','a'); -create table t1a like t1; -insert into t1a select * from t1; -create table t1b like t1; -insert into t1b select * from t1; -CREATE TABLE t2 ( -pk int(11) NOT NULL AUTO_INCREMENT, -c1 int(11) DEFAULT NULL, -c2 int(11) DEFAULT NULL, -c3 varchar(1) DEFAULT NULL, -c4 varchar(1) DEFAULT NULL, -PRIMARY KEY (pk), -KEY c2 (c2), -KEY c3 (c3,c2)); -INSERT INTO t2 VALUES (1,NULL,2,'w','w'); -INSERT INTO t2 VALUES (2,7,9,'m','m'); -set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; -EXPLAIN EXTENDED SELECT pk -FROM t1 -WHERE c1 IN -(SELECT t1a.c1 -FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN -t1a ON (t1a.c2 = t1b.pk AND 2) -WHERE t1.pk) ; -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 -2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using index -2 DEPENDENT SUBQUERY t1b ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 2 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,(`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where (`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))))) -SELECT pk -FROM t1 -WHERE c1 IN -(SELECT t1a.c1 -FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN -t1a ON (t1a.c2 = t1b.pk AND 2) -WHERE t1.pk) ; -pk -DROP TABLE t1, t1a, t1b, t2; -# -# LP BUG#714808 Assertion `outer_lookup_keys <= outer_record_count' -# failed with materialization -CREATE TABLE t1 ( pk int(11), PRIMARY KEY (pk)) ; -CREATE TABLE t2 ( f2 int(11)) ; -CREATE TABLE t3 ( f1 int(11), f3 varchar(1), KEY (f1)) ; -INSERT INTO t3 VALUES (7,'f'); -set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; -EXPLAIN -SELECT t1.* -FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 -WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -SELECT t1.* -FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 -WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); -pk -drop table t1,t2,t3; -# -# LP BUG#714999 Second crash in select_describe() with nested subqueries -# -CREATE TABLE t1 ( pk int(11)) ; -INSERT INTO t1 VALUES (29); -CREATE TABLE t2 ( f1 varchar(1)) ; -INSERT INTO t2 VALUES ('f'),('d'); -CREATE TABLE t3 ( f2 varchar(1)) ; -EXPLAIN SELECT f2 FROM t3 WHERE ( -SELECT MAX( pk ) FROM t1 -WHERE EXISTS ( -SELECT DISTINCT f1 -FROM t2 -) -) IS NULL ; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY t1 system NULL NULL NULL NULL 1 -3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary -drop table t1, t2; -# -# LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed -# -CREATE TABLE t2 ( f2 int(11)) ; -CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; -INSERT INTO t1 VALUES (6),(4); -EXPLAIN -SELECT * FROM (SELECT * FROM t2) AS a2 -WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index -2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -insert into t2 values (1),(2); -EXPLAIN -SELECT * FROM (SELECT * FROM t2) AS a2 -WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index -2 DERIVED t2 ALL NULL NULL NULL NULL 2 -drop table t1,t2; -# -# LP BUG#715027 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed -# -CREATE TABLE t1 ( f1 int(11), PRIMARY KEY (f1)) ; -INSERT INTO t1 VALUES (28),(29); -CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; -INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); -EXPLAIN -SELECT alias2.f2 AS field1 -FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 -WHERE ( -SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 -) -ORDER BY field1 ; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where; Using filesort -1 PRIMARY alias1 eq_ref PRIMARY PRIMARY 4 alias2.f3 1 Using index -3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 -3 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) -2 DERIVED t2 ALL NULL NULL NULL NULL 2 -SELECT alias2.f2 AS field1 -FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 -WHERE ( -SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 -) -ORDER BY field1 ; -field1 -drop table t1,t2; -# -# LP BUG#718578 Yet another Assertion `!table || -# (!table->read_set || bitmap_is_set(table->read_set, field_index))' -CREATE TABLE t1 ( f1 int(11), f2 int(11), f3 int(11)) ; -INSERT IGNORE INTO t1 VALUES (28,5,6),(29,NULL,4); -CREATE TABLE t2 ( f10 varchar(1) ); -INSERT IGNORE INTO t2 VALUES (NULL); -SELECT f1 AS field1 -FROM ( SELECT * FROM t1 ) AS alias1 -WHERE (SELECT t1.f1 -FROM t2 JOIN t1 ON t1.f2 -WHERE alias1.f3 AND t1.f3) AND f2 -ORDER BY field1; -field1 -28 -drop table t1,t2; -# -# LP BUG#601124 Bug in eliminate_item_equal -# leads to crash in Item_func::Item_func -CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; -INSERT INTO t1 VALUES (5,'m'),(NULL,'c'); -CREATE TABLE t2 ( f2 int(11), f3 varchar(1)) ; -INSERT INTO t2 VALUES (6,'f'),(2,'d'); -CREATE TABLE t3 ( f2 int(11), f3 varchar(1)) ; -INSERT INTO t3 VALUES (6,'f'),(2,'d'); -SELECT * FROM t3 -WHERE ( f2 ) IN (SELECT t1.f1 -FROM t1 STRAIGHT_JOIN t2 ON t2.f3 = t1.f3 -WHERE t2.f3 = 'c'); -f2 f3 -drop table t1,t2,t3; diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result new file mode 100644 index 00000000000..a3abda164fb --- /dev/null +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -0,0 +1,234 @@ +# +# LP BUG#643424 valgrind warning in choose_subquery_plan() +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2)); +INSERT INTO t1 VALUES (1,NULL,2); +INSERT INTO t1 VALUES (2,7,9); +INSERT INTO t1 VALUES (9,NULL,8); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2)); +INSERT INTO t2 VALUES (1,1,7); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; +SELECT pk FROM t1 WHERE (c2, c1) IN (SELECT c2, c2 FROM t2); +pk +set session optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# LP BUG#652727 Crash in create_ref_for_key() +# +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +PRIMARY KEY (pk)); +INSERT INTO t2 VALUES (10,7); +INSERT INTO t2 VALUES (11,1); +INSERT INTO t2 VALUES (17,NULL); +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +PRIMARY KEY (pk)); +INSERT INTO t1 VALUES (15,1); +INSERT INTO t1 VALUES (19,NULL); +CREATE TABLE t3 (c2 int(11) DEFAULT NULL, KEY c2 (c2)); +INSERT INTO t3 VALUES (1); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; +SELECT c2 +FROM t3 +WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FROM t1 STRAIGHT_JOIN t2 ON t2.pk = t1.pk); +c2 +set session optimizer_switch=@save_optimizer_switch; +drop table t1, t2, t3; +# +# LP BUG#641245 Crash in Item_equal::contains +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +c3 varchar(1) DEFAULT NULL, +c4 varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2), +KEY c3 (c3,c2)); +INSERT INTO t1 VALUES (10,7,8,'v','v'); +INSERT INTO t1 VALUES (11,1,9,'r','r'); +INSERT INTO t1 VALUES (12,5,9,'a','a'); +create table t1a like t1; +insert into t1a select * from t1; +create table t1b like t1; +insert into t1b select * from t1; +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +c3 varchar(1) DEFAULT NULL, +c4 varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2), +KEY c3 (c3,c2)); +INSERT INTO t2 VALUES (1,NULL,2,'w','w'); +INSERT INTO t2 VALUES (2,7,9,'m','m'); +set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN EXTENDED SELECT pk +FROM t1 +WHERE c1 IN +(SELECT t1a.c1 +FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN +t1a ON (t1a.c2 = t1b.pk AND 2) +WHERE t1.pk) ; +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 +2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t1b ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where (`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))))) +SELECT pk +FROM t1 +WHERE c1 IN +(SELECT t1a.c1 +FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN +t1a ON (t1a.c2 = t1b.pk AND 2) +WHERE t1.pk) ; +pk +DROP TABLE t1, t1a, t1b, t2; +# +# LP BUG#714808 Assertion `outer_lookup_keys <= outer_record_count' +# failed with materialization +CREATE TABLE t1 ( pk int(11), PRIMARY KEY (pk)) ; +CREATE TABLE t2 ( f2 int(11)) ; +CREATE TABLE t3 ( f1 int(11), f3 varchar(1), KEY (f1)) ; +INSERT INTO t3 VALUES (7,'f'); +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT t1.* +FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 +WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT t1.* +FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 +WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); +pk +drop table t1,t2,t3; +# +# LP BUG#714999 Second crash in select_describe() with nested subqueries +# +CREATE TABLE t1 ( pk int(11)) ; +INSERT INTO t1 VALUES (29); +CREATE TABLE t2 ( f1 varchar(1)) ; +INSERT INTO t2 VALUES ('f'),('d'); +CREATE TABLE t3 ( f2 varchar(1)) ; +EXPLAIN SELECT f2 FROM t3 WHERE ( +SELECT MAX( pk ) FROM t1 +WHERE EXISTS ( +SELECT DISTINCT f1 +FROM t2 +) +) IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 system NULL NULL NULL NULL 1 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary +drop table t1, t2, t3; +# +# LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed +# +CREATE TABLE t2 ( f2 int(11)) ; +CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; +INSERT INTO t1 VALUES (6),(4); +EXPLAIN +SELECT * FROM (SELECT * FROM t2) AS a2 +WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +insert into t2 values (1),(2); +EXPLAIN +SELECT * FROM (SELECT * FROM t2) AS a2 +WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +drop table t1,t2; +# +# LP BUG#715027 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed +# +CREATE TABLE t1 ( f1 int(11), PRIMARY KEY (f1)) ; +INSERT INTO t1 VALUES (28),(29); +CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; +INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); +EXPLAIN +SELECT alias2.f2 AS field1 +FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 +WHERE ( +SELECT t2.f2 +FROM t2 JOIN t1 ON t1.f1 +WHERE t1.f1 AND alias2.f10 +) +ORDER BY field1 ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where; Using filesort +1 PRIMARY alias1 eq_ref PRIMARY PRIMARY 4 alias2.f3 1 Using index +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 +3 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +SELECT alias2.f2 AS field1 +FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 +WHERE ( +SELECT t2.f2 +FROM t2 JOIN t1 ON t1.f1 +WHERE t1.f1 AND alias2.f10 +) +ORDER BY field1 ; +field1 +drop table t1,t2; +# +# LP BUG#718578 Yet another Assertion `!table || +# (!table->read_set || bitmap_is_set(table->read_set, field_index))' +CREATE TABLE t1 ( f1 int(11), f2 int(11), f3 int(11)) ; +INSERT IGNORE INTO t1 VALUES (28,5,6),(29,NULL,4); +CREATE TABLE t2 ( f10 varchar(1) ); +INSERT IGNORE INTO t2 VALUES (NULL); +SELECT f1 AS field1 +FROM ( SELECT * FROM t1 ) AS alias1 +WHERE (SELECT t1.f1 +FROM t2 JOIN t1 ON t1.f2 +WHERE alias1.f3 AND t1.f3) AND f2 +ORDER BY field1; +field1 +28 +drop table t1,t2; +# +# LP BUG#601124 Bug in eliminate_item_equal +# leads to crash in Item_func::Item_func +CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; +INSERT INTO t1 VALUES (5,'m'),(NULL,'c'); +CREATE TABLE t2 ( f2 int(11), f3 varchar(1)) ; +INSERT INTO t2 VALUES (6,'f'),(2,'d'); +CREATE TABLE t3 ( f2 int(11), f3 varchar(1)) ; +INSERT INTO t3 VALUES (6,'f'),(2,'d'); +SELECT * FROM t3 +WHERE ( f2 ) IN (SELECT t1.f1 +FROM t1 STRAIGHT_JOIN t2 ON t2.f3 = t1.f3 +WHERE t2.f3 = 'c'); +f2 f3 +drop table t1,t2,t3; +# +# LP BUG#718593 Crash in substitute_for_best_equal_field -> +# eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains |