diff options
| author | igor@rurik.mysql.com <> | 2006-10-19 23:05:53 -0700 |
|---|---|---|
| committer | igor@rurik.mysql.com <> | 2006-10-19 23:05:53 -0700 |
| commit | d8b6f46a390951d3da038bd366b38760ae5a38c9 (patch) | |
| tree | a33320fcd924119dcdb40bf6cde9be47f5b4840a | |
| parent | 1dacdd4c85685a144615b22374b1dbb86ac1ead9 (diff) | |
| download | mariadb-git-d8b6f46a390951d3da038bd366b38760ae5a38c9.tar.gz | |
Fixed bug #23478.
If elements a not top-level IN subquery were accessed by an index and
the subquery result set included a NULL value then the quantified
predicate that contained the subquery was evaluated to NULL when
it should return a non-null value.
| -rw-r--r-- | mysql-test/r/subselect.result | 15 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 17 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 3 |
3 files changed, 35 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ad847b5f156..28fbfc86657 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2982,3 +2982,18 @@ field1 field2 1 1 1 3 DROP TABLE t1, t2; +CREATE TABLE t1(a int, INDEX (a)); +INSERT INTO t1 VALUES (1), (3), (5), (7); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2(a int); +INSERT INTO t2 VALUES (1),(2),(3); +EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index +SELECT a, a IN (SELECT a FROM t1) FROM t2; +a a IN (SELECT a FROM t1) +1 1 +2 NULL +3 1 +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 6defa8b16a5..ac035c72d18 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1948,4 +1948,21 @@ SELECT field1, field2 DROP TABLE t1, t2; +# +# Bug #23478: not top-level IN subquery returning a non-empty result set +# with possible NULL values by index access from the outer query +# + +CREATE TABLE t1(a int, INDEX (a)); +INSERT INTO t1 VALUES (1), (3), (5), (7); +INSERT INTO t1 VALUES (NULL); + +CREATE TABLE t2(a int); +INSERT INTO t2 VALUES (1),(2),(3); + +EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; +SELECT a, a IN (SELECT a FROM t1) FROM t2; + +DROP TABLE t1,t2; + # End of 4.1 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index f3be0663af8..1ab81d1862d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -610,6 +610,7 @@ double Item_in_subselect::val() */ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); + null_value= 0; if (exec()) { reset(); @@ -625,6 +626,7 @@ double Item_in_subselect::val() longlong Item_in_subselect::val_int() { DBUG_ASSERT(fixed == 1); + null_value= 0; if (exec()) { reset(); @@ -645,6 +647,7 @@ String *Item_in_subselect::val_str(String *str) */ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); + null_value= 0; if (exec()) { reset(); |