diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 25 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 21 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 5 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 13 |
4 files changed, 52 insertions, 12 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 0735f133e6f..b264018866c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1425,7 +1425,7 @@ Note 1003 (select test.t1.s1 AS `s1` from test.t1) s1 tttt drop table t1; -create table t1 (s1 char(5), index s1(s1)); +create table t1 (s1 char(5) not null, index s1(s1)); create table t2 (s1 char(5), index s1(s1)); insert into t1 values ('a1'),('a2'),('a3'); insert into t2 values ('a1'),('a2'); @@ -1451,25 +1451,25 @@ a2 1 a3 1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +1 PRIMARY t1 index NULL s1 5 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index Warnings: Note 1003 select test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from test.t1 explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +1 PRIMARY t1 index NULL s1 5 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index Warnings: Note 1003 select test.t1.s1 AS `s1`,<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from test.t1 explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +1 PRIMARY t1 index NULL s1 5 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index Warnings: Note 1003 select test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from test.t1 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL s1 6 NULL 3 Using index +1 PRIMARY t1 index NULL s1 5 NULL 3 Using index 2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 1 Using index; Using where Warnings: Note 1003 select test.t1.s1 AS `s1`,not(<in_optimizer>(test.t1.s1,<exists>(<index_lookup>(<cache>(test.t1.s1) in t2 on s1 chicking NULL where (test.t2.s1 < _latin1'a2'))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from test.t1 @@ -2125,3 +2125,18 @@ SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 c Oceania drop table t1; +CREATE TABLE t1 ( f1 BIGINT ); +INSERT INTO t1 SET f1= NULL; +INSERT INTO t1 SET f1= 1; +CREATE TABLE t2 ( f1 BIGINT ); +SELECT f1 FROM t1 +WHERE f1 <> ALL ( SELECT f1 FROM t2 ); +f1 +NULL +1 +INSERT INTO t2 VALUES (1), (2); +SELECT f1 FROM t1 +WHERE f1 <> ALL ( SELECT f1 FROM t2 WHERE f1 > 2 ); +f1 +NULL +1 diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index f59851fa722..6f7daa66897 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -889,7 +889,7 @@ drop table t1; # # IN optimisation test results # -create table t1 (s1 char(5), index s1(s1)); +create table t1 (s1 char(5) not null, index s1(s1)); create table t2 (s1 char(5), index s1(s1)); insert into t1 values ('a1'),('a2'),('a3'); insert into t2 values ('a1'),('a2'); @@ -1386,3 +1386,22 @@ INSERT INTO t1 VALUES ('UMI','United States Minor Outlying Islands','Oceania','M /*!40000 ALTER TABLE t1 ENABLE KEYS */; SELECT DISTINCT Continent AS c FROM t1 WHERE Code <> SOME ( SELECT Code FROM t1 WHERE Continent = c AND Population < 200); drop table t1; + +# +# Test cases for bug #7351: +# quantified predicate with subquery returning empty result set +# + +CREATE TABLE t1 ( f1 BIGINT ); +INSERT INTO t1 SET f1= NULL; +INSERT INTO t1 SET f1= 1; +CREATE TABLE t2 ( f1 BIGINT ); + +SELECT f1 FROM t1 + WHERE f1 <> ALL ( SELECT f1 FROM t2 ); + +INSERT INTO t2 VALUES (1), (2); + +SELECT f1 FROM t1 + WHERE f1 <> ALL ( SELECT f1 FROM t2 WHERE f1 > 2 ); + diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index a135f08ae45..3d79c16b5d0 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -636,12 +636,13 @@ longlong Item_in_optimizer::val_int() { DBUG_ASSERT(fixed == 1); cache->store(args[0]); + longlong tmp= args[1]->val_int_result(); if (cache->null_value) { - null_value= 1; + if (tmp) + null_value= 1; return 0; } - longlong tmp= args[1]->val_int_result(); null_value= args[1]->null_value; return tmp; } diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 1d0f46fd196..e1a80941a52 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -824,6 +824,8 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->ref_pointer_array, (char *)"<ref>", this->full_name())); + if (!abort_on_null && left_expr->maybe_null) + item= new Item_cond_or(new Item_func_isnull(left_expr), item); /* AND and comparison functions can't be changed during fix_fields() we can assign select_lex->having here, and pass 0 as last @@ -869,6 +871,8 @@ Item_in_subselect::single_value_transformer(JOIN *join, select_lex->having_fix_field= 0; item= new Item_cond_or(item, new Item_func_isnull(orig_item)); + if (left_expr->maybe_null) + item= new Item_cond_or(new Item_func_isnull(left_expr), item); } item->name= (char *)in_additional_cond; /* @@ -889,12 +893,13 @@ Item_in_subselect::single_value_transformer(JOIN *join, we can assign select_lex->having here, and pass 0 as last argument (reference) to fix_fields() */ - select_lex->having= - join->having= - func->create(expr, - new Item_null_helper(this, item, + item= func->create(expr, + new Item_null_helper(this, item, (char *)"<no matter>", (char *)"<result>")); + if (!abort_on_null && left_expr->maybe_null) + item= new Item_cond_or(new Item_func_isnull(left_expr), item); + select_lex->having= join->having= item; select_lex->having_fix_field= 1; if (join->having->fix_fields(thd, join->tables_list, 0)) |