diff options
| -rw-r--r-- | mysql-test/r/subselect3.result | 31 | ||||
| -rw-r--r-- | mysql-test/t/subselect3.test | 25 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 6 | ||||
| -rw-r--r-- | sql/sql_select.h | 7 |
4 files changed, 67 insertions, 2 deletions
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index f52249db8a1..9bbfdc6c5f9 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -711,3 +711,34 @@ a 1 4 DROP TABLE t1,t2; +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int PRIMARY KEY); +CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10)); +INSERT INTO t1 VALUES (2), (NULL), (3), (1); +INSERT INTO t2 VALUES (234), (345), (457); +INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa'); +EXPLAIN +SELECT * FROM t1 +WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 +WHERE t3.name='xxx' AND t2.id=t3.id); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key +SELECT * FROM t1 +WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 +WHERE t3.name='xxx' AND t2.id=t3.id); +id +2 +NULL +3 +1 +SELECT (t1.id IN (SELECT t2.id FROM t2,t3 +WHERE t3.name='xxx' AND t2.id=t3.id)) AS x +FROM t1; +x +0 +0 +0 +0 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index dfe09968fa2..65556012588 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -546,3 +546,28 @@ SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1)); SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4)); DROP TABLE t1,t2; + +# +# Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL +# + +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int PRIMARY KEY); +CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10)); +INSERT INTO t1 VALUES (2), (NULL), (3), (1); +INSERT INTO t2 VALUES (234), (345), (457); +INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa'); + +EXPLAIN +SELECT * FROM t1 + WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 + WHERE t3.name='xxx' AND t2.id=t3.id); +SELECT * FROM t1 + WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3 + WHERE t3.name='xxx' AND t2.id=t3.id); + +SELECT (t1.id IN (SELECT t2.id FROM t2,t3 + WHERE t3.name='xxx' AND t2.id=t3.id)) AS x + FROM t1; + +DROP TABLE t1,t2,t3; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index ccd361dba99..48b82e3cde6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1829,6 +1829,8 @@ int subselect_single_select_engine::exec() if (cond_guard && !*cond_guard) { /* Change the access method to full table scan */ + tab->save_read_first_record= tab->read_first_record; + tab->save_read_record= tab->read_record.read_record; tab->read_first_record= init_read_record_seq; tab->read_record.record= tab->table->record[0]; tab->read_record.thd= join->thd; @@ -1849,8 +1851,8 @@ int subselect_single_select_engine::exec() JOIN_TAB *tab= *ptab; tab->read_record.record= 0; tab->read_record.ref_length= 0; - tab->read_first_record= join_read_always_key_or_null; - tab->read_record.read_record= join_read_next_same_or_null; + tab->read_first_record= tab->save_read_first_record; + tab->read_record.read_record= tab->save_read_record; } executed= 1; thd->where= save_where; diff --git a/sql/sql_select.h b/sql/sql_select.h index 5081366c10b..3cdd265df9a 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -159,6 +159,13 @@ typedef struct st_join_table { Read_record_func read_first_record; Next_select_func next_select; READ_RECORD read_record; + /* + Currently the following two fields are used only for a [NOT] IN subquery + if it is executed by an alternative full table scan when the left operand of + the subquery predicate is evaluated to NULL. + */ + Read_record_func save_read_first_record;/* to save read_first_record */ + int (*save_read_record) (READ_RECORD *);/* to save read_record.read_record */ double worst_seeks; key_map const_keys; /* Keys with constant part */ key_map checked_keys; /* Keys checked in find_best */ |