At file:///home/psergey/dev/maria-5.3-subqueries-r3/ ------------------------------------------------------------ revno: 2747 revision-id: psergey@askmonty.org-20100117205220-ixee1qyi1epfyo1x parent: psergey@askmonty.org-20100117150159-8twf2i8rdek9kexq committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r3 timestamp: Sun 2010-01-17 23:52:20 +0300 message: Subquery optimizations, backport to 5.3: - Fix valgrind failure - Test result fixes (not finished) === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect.result 2010-01-17 20:52:20 +0000 @@ -3645,7 +3645,7 @@ 2 2 DROP TABLE t1,t2; -CREATE TABLE t1 (a int, b int auto_increment, PRIMARY KEY (b)); +CREATE TABLE t1 (a int, b int, PRIMARY KEY (b)); CREATE TABLE t2 (x int auto_increment, y int, z int, PRIMARY KEY (x), FOREIGN KEY (y) REFERENCES t1 (b)); create table t3 (a int); @@ -4381,7 +4381,229 @@ MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) NULL 0 DROP TABLE t1, st1, st2; +# +# Bug #48709: Assertion failed in sql_select.cc:11782: +# int join_read_key(JOIN_TAB*) +# +CREATE TABLE t1 (pk int PRIMARY KEY, int_key int); +INSERT INTO t1 VALUES (10,1), (14,1); +CREATE TABLE t2 (pk int PRIMARY KEY, int_key int); +INSERT INTO t2 VALUES (3,3), (5,NULL), (7,3); +# should have eq_ref for t1 +EXPLAIN +SELECT * FROM t2 outr +WHERE outr.int_key NOT IN (SELECT t1.pk FROM t1, t2) +ORDER BY outr.pk; +id select_type table type possible_keys key key_len ref rows Extra +x x outr ALL x x x x x x +x x t1 eq_ref x x x x x x +x x t2 index x x x x x x +# should not crash on debug binaries +SELECT * FROM t2 outr +WHERE outr.int_key NOT IN (SELECT t1.pk FROM t1, t2) +ORDER BY outr.pk; +pk int_key +3 3 +7 3 +DROP TABLE t1,t2; End of 5.0 tests. +create table t_out (subcase char(3), +a1 char(2), b1 char(2), c1 char(2)); +create table t_in (a2 char(2), b2 char(2), c2 char(2)); +insert into t_out values ('A.1','2a', NULL, '2a'); +insert into t_out values ('A.3', '2a', NULL, '2a'); +insert into t_out values ('A.4', '2a', NULL, 'xx'); +insert into t_out values ('B.1', '2a', '2a', '2a'); +insert into t_out values ('B.2', '2a', '2a', '2a'); +insert into t_out values ('B.3', '3a', 'xx', '3a'); +insert into t_out values ('B.4', 'xx', '3a', '3a'); +insert into t_in values ('1a', '1a', '1a'); +insert into t_in values ('2a', '2a', '2a'); +insert into t_in values (NULL, '2a', '2a'); +insert into t_in values ('3a', NULL, '3a'); + +Test general IN semantics (not top-level) + +case A.1 +select subcase, +(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in, +(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in +from t_out where subcase = 'A.1'; +subcase pred_in pred_not_in +A.1 0 1 +case A.2 - impossible +case A.3 +select subcase, +(a1, b1, c1) IN (select * from t_in) pred_in, +(a1, b1, c1) NOT IN (select * from t_in) pred_not_in +from t_out where subcase = 'A.3'; +subcase pred_in pred_not_in +A.3 NULL NULL +case A.4 +select subcase, +(a1, b1, c1) IN (select * from t_in) pred_in, +(a1, b1, c1) NOT IN (select * from t_in) pred_not_in +from t_out where subcase = 'A.4'; +subcase pred_in pred_not_in +A.4 0 1 +case B.1 +select subcase, +(a1, b1, c1) IN (select * from t_in where a2 = 'no_match') pred_in, +(a1, b1, c1) NOT IN (select * from t_in where a2 = 'no_match') pred_not_in +from t_out where subcase = 'B.1'; +subcase pred_in pred_not_in +B.1 0 1 +case B.2 +select subcase, +(a1, b1, c1) IN (select * from t_in) pred_in, +(a1, b1, c1) NOT IN (select * from t_in) pred_not_in +from t_out where subcase = 'B.2'; +subcase pred_in pred_not_in +B.2 1 0 +case B.3 +select subcase, +(a1, b1, c1) IN (select * from t_in) pred_in, +(a1, b1, c1) NOT IN (select * from t_in) pred_not_in +from t_out where subcase = 'B.3'; +subcase pred_in pred_not_in +B.3 NULL NULL +case B.4 +select subcase, +(a1, b1, c1) IN (select * from t_in) pred_in, +(a1, b1, c1) NOT IN (select * from t_in) pred_not_in +from t_out where subcase = 'B.4'; +subcase pred_in pred_not_in +B.4 0 1 + +Test IN as top-level predicate, and +as non-top level for cases A.3, B.3 (the only cases with NULL result). + +case A.1 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'A.1' and +(a1, b1, c1) IN (select * from t_in where a1 = 'no_match'); +pred_in +F +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'A.1' and +(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match'); +pred_not_in +T +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'A.1' and +NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match')); +not_pred_in +T +case A.3 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'A.3' and +(a1, b1, c1) IN (select * from t_in); +pred_in +F +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'A.3' and +(a1, b1, c1) NOT IN (select * from t_in); +pred_not_in +F +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'A.3' and +NOT((a1, b1, c1) IN (select * from t_in)); +not_pred_in +F +select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out +where subcase = 'A.3' and +((a1, b1, c1) IN (select * from t_in)) is NULL and +((a1, b1, c1) NOT IN (select * from t_in)) is NULL; +pred_in +N +case A.4 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'A.4' and +(a1, b1, c1) IN (select * from t_in); +pred_in +F +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'A.4' and +(a1, b1, c1) NOT IN (select * from t_in); +pred_not_in +T +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'A.4' and +NOT((a1, b1, c1) IN (select * from t_in)); +not_pred_in +T +case B.1 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'B.1' and +(a1, b1, c1) IN (select * from t_in where a1 = 'no_match'); +pred_in +F +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'B.1' and +(a1, b1, c1) NOT IN (select * from t_in where a1 = 'no_match'); +pred_not_in +T +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'B.1' and +NOT((a1, b1, c1) IN (select * from t_in where a1 = 'no_match')); +not_pred_in +T +case B.2 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'B.2' and +(a1, b1, c1) IN (select * from t_in); +pred_in +T +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'B.2' and +(a1, b1, c1) NOT IN (select * from t_in); +pred_not_in +F +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'B.2' and +NOT((a1, b1, c1) IN (select * from t_in)); +not_pred_in +F +case B.3 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'B.3' and +(a1, b1, c1) IN (select * from t_in); +pred_in +F +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'B.3' and +(a1, b1, c1) NOT IN (select * from t_in); +pred_not_in +F +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'B.3' and +NOT((a1, b1, c1) IN (select * from t_in)); +not_pred_in +F +select case when count(*) > 0 then 'N' else 'wrong result' end as pred_in from t_out +where subcase = 'B.3' and +((a1, b1, c1) IN (select * from t_in)) is NULL and +((a1, b1, c1) NOT IN (select * from t_in)) is NULL; +pred_in +N +case B.4 +select case when count(*) > 0 then 'T' else 'F' end as pred_in from t_out +where subcase = 'B.4' and +(a1, b1, c1) IN (select * from t_in); +pred_in +F +select case when count(*) > 0 then 'T' else 'F' end as pred_not_in from t_out +where subcase = 'B.4' and +(a1, b1, c1) NOT IN (select * from t_in); +pred_not_in +T +select case when count(*) > 0 then 'T' else 'F' end as not_pred_in from t_out +where subcase = 'B.4' and +NOT((a1, b1, c1) IN (select * from t_in)); +not_pred_in +T +drop table t_out; +drop table t_in; CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a; @@ -4496,6 +4718,42 @@ DROP VIEW v1,v2,v3; DROP TABLE t1,t2; # +# BUG#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result +# +create table t1(id integer primary key, g integer, v integer, s char(1)); +create table t2(id integer primary key, g integer, v integer, s char(1)); +insert into t1 values +(10, 10, 10, 'l'), +(20, 20, 20, 'l'), +(40, 40, 40, 'l'), +(41, 40, null, 'l'), +(50, 50, 50, 'l'), +(51, 50, null, 'l'), +(60, 60, 60, 'l'), +(61, 60, null, 'l'), +(70, 70, 70, 'l'), +(90, 90, null, 'l'); +insert into t2 values +(10, 10, 10, 'r'), +(30, 30, 30, 'r'), +(50, 50, 50, 'r'), +(60, 60, 60, 'r'), +(61, 60, null, 'r'), +(70, 70, 70, 'r'), +(71, 70, null, 'r'), +(80, 80, 80, 'r'), +(81, 80, null, 'r'), +(100,100,null, 'r'); +select * +from t1 +where v in(select v +from t2 +where t1.g=t2.g) is unknown; +id g v s +51 50 NULL l +61 60 NULL l +drop table t1, t2; +# # Bug#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result # create table t1(id integer primary key, g integer, v integer, s char(1)); @@ -4531,4 +4789,18 @@ 51 50 NULL l 61 60 NULL l drop table t1, t2; +CREATE TABLE t1 (a ENUM('rainbow')); +INSERT INTO t1 VALUES (),(),(),(),(); +SELECT 1 FROM t1 GROUP BY (SELECT 1 FROM t1 ORDER BY AVG(LAST_INSERT_ID())); +1 +1 +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB); +INSERT INTO t1 SET a = 'aaaa'; +INSERT INTO t1 SET a = 'aaaa'; +SELECT 1 FROM t1 GROUP BY +(SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); +1 +1 +DROP TABLE t1; End of 5.1 tests. === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-01-17 20:52:20 +0000 @@ -4643,7 +4643,6 @@ SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); a b DROP TABLE t1,t2; -End of 5.0 tests. CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a)); INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20); CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a)); @@ -4694,6 +4693,33 @@ a incorrect 1 1 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +CREATE VIEW v1 AS +SELECT t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +UPDATE v1 SET c=1; +CREATE VIEW v2 (a,b) AS +SELECT t2.id, t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +INSERT INTO v2(a,b) VALUES (2,2); +ERROR HY000: CHECK OPTION failed 'test.v2' +SELECT * FROM v1; +c +1 +1 +1 +1 +CREATE VIEW v3 AS +SELECT t2.c AS c FROM t2 +WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +DELETE FROM v3; +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; # # BUG#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result # === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-01-17 20:52:20 +0000 @@ -79,7 +79,7 @@ select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect parameters to procedure 'ANALYSE' +ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -4643,7 +4643,6 @@ SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); a b DROP TABLE t1,t2; -End of 5.0 tests. CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a)); INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20); CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a)); @@ -4694,6 +4693,33 @@ a incorrect 1 1 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +CREATE VIEW v1 AS +SELECT t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +UPDATE v1 SET c=1; +CREATE VIEW v2 (a,b) AS +SELECT t2.id, t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +INSERT INTO v2(a,b) VALUES (2,2); +ERROR HY000: CHECK OPTION failed 'test.v2' +SELECT * FROM v1; +c +1 +1 +1 +1 +CREATE VIEW v3 AS +SELECT t2.c AS c FROM t2 +WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +DELETE FROM v3; +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; # # BUG#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result # === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-01-17 20:52:20 +0000 @@ -4643,7 +4643,6 @@ SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); a b DROP TABLE t1,t2; -End of 5.0 tests. CREATE TABLE t1(pk INT PRIMARY KEY, a INT, INDEX idx(a)); INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20); CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT, INDEX idxa(a)); @@ -4694,6 +4693,33 @@ a incorrect 1 1 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +CREATE VIEW v1 AS +SELECT t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +UPDATE v1 SET c=1; +CREATE VIEW v2 (a,b) AS +SELECT t2.id, t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +INSERT INTO v2(a,b) VALUES (2,2); +ERROR HY000: CHECK OPTION failed 'test.v2' +SELECT * FROM v1; +c +1 +1 +1 +1 +CREATE VIEW v3 AS +SELECT t2.c AS c FROM t2 +WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +DELETE FROM v3; +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; # # BUG#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result # === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2010-01-17 14:51:10 +0000 +++ b/mysql-test/t/subselect.test 2010-01-17 20:52:20 +0000 @@ -3655,8 +3655,6 @@ DROP TABLE t1,t2; ---echo End of 5.0 tests. - # # Bug#38191 Server crash with subquery containing DISTINCT and ORDER BY # @@ -3939,6 +3937,44 @@ # --error ER_PARSE_ERROR # SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v ); # DROP TABLE t1, t2; +# +# Bug#37460 Assertion failed: +# !table->file || table->file->inited == handler::NONE +# +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int); + +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); + +CREATE VIEW v1 AS +SELECT t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +UPDATE v1 SET c=1; + +CREATE VIEW v2 (a,b) AS +SELECT t2.id, t2.c AS c FROM t1, t2 +WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; + +--error ER_VIEW_CHECK_FAILED +INSERT INTO v2(a,b) VALUES (2,2); + +# disabled for now as this refers to old content of t2 +--disable_parsing +INSERT INTO v2(a,b) VALUES (1,2); +--enable_parsing +SELECT * FROM v1; + +CREATE VIEW v3 AS +SELECT t2.c AS c FROM t2 +WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; + +DELETE FROM v3; + +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; --echo # --echo # BUG#37822 Correlated subquery with IN and IS UNKNOWN provides wrong result === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-01-17 15:01:59 +0000 +++ b/sql/sql_select.cc 2010-01-17 20:52:20 +0000 @@ -4663,6 +4663,7 @@ */ bool null_rejecting; bool *cond_guard; /* See KEYUSE::cond_guard */ + uint sj_pred_no; /* See KEYUSE::sj_pred_no */ } KEY_FIELD; /** @@ -4828,6 +4829,52 @@ } +/* + Given a field, return its index in semi-join's select list, or UINT_MAX + + DESCRIPTION + Given a field, we find its table; then see if the table is within a + semi-join nest and if the field was in select list of the subselect. + If it was, we return field's index in the select list. The value is used + by LooseScan strategy. +*/ + +static uint get_semi_join_select_list_index(Field *field) +{ + uint res= UINT_MAX; + TABLE_LIST *emb_sj_nest; + if ((emb_sj_nest= field->table->pos_in_table_list->embedding) && + emb_sj_nest->sj_on_expr) + { + Item_in_subselect *subq_pred= emb_sj_nest->sj_subq_pred; + st_select_lex *subq_lex= subq_pred->unit->first_select(); + if (subq_pred->left_expr->cols() == 1) + { + Item *sel_item= subq_lex->ref_pointer_array[0]; + if (sel_item->type() == Item::FIELD_ITEM && + ((Item_field*)sel_item)->field->eq(field)) + { + res= 0; + } + } + else + { + for (uint i= 0; i < subq_pred->left_expr->cols(); i++) + { + Item *sel_item= subq_lex->ref_pointer_array[i]; + if (sel_item->type() == Item::FIELD_ITEM && + ((Item_field*)sel_item)->field->eq(field)) + { + res= i; + break; + } + } + } + } + return res; +} + + /** Add a possible key to array of possible keys if it's usable as a key @@ -4998,6 +5045,8 @@ (*key_fields)->null_rejecting= false; } (*key_fields)->cond_guard= NULL; + + (*key_fields)->sj_pred_no= get_semi_join_select_list_index(field); (*key_fields)++; } @@ -5334,6 +5383,7 @@ keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; keyuse.null_rejecting= key_field->null_rejecting; keyuse.cond_guard= key_field->cond_guard; + keyuse.sj_pred_no= key_field->sj_pred_no; if (insert_dynamic(keyuse_array,(uchar*) &keyuse)) return TRUE; } @@ -5406,6 +5456,7 @@ keyuse.used_tables=cond_func->key_item()->used_tables(); keyuse.optimize= 0; keyuse.keypart_map= 0; + keyuse.sj_pred_no= UINT_MAX; return insert_dynamic(keyuse_array,(uchar*) &keyuse); }