summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result25
-rw-r--r--mysql-test/r/subselect_no_mat.result25
-rw-r--r--mysql-test/r/subselect_no_opts.result25
-rw-r--r--mysql-test/r/subselect_no_scache.result25
-rw-r--r--mysql-test/r/subselect_no_semijoin.result25
-rw-r--r--mysql-test/t/subselect.test18
-rw-r--r--sql/item_subselect.cc36
-rw-r--r--sql/item_subselect.h4
8 files changed, 177 insertions, 6 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 70096a68a34..024dca5d1bb 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6112,5 +6112,30 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7;
f1 f2
drop table t1,t2;
+#
+# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
+# WHERE and UNION in HAVING
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(7);
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+min_a a
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+min_a a
+drop table t1;
# return optimizer switch changed in the beginning of this test
set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 671a5df213b..e8c084f9700 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6111,6 +6111,31 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7;
f1 f2
drop table t1,t2;
+#
+# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
+# WHERE and UNION in HAVING
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(7);
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+min_a a
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+min_a a
+drop table t1;
# return optimizer switch changed in the beginning of this test
set optimizer_switch=@subselect_tmp;
set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index e70c07a5894..ace1f198853 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6107,6 +6107,31 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7;
f1 f2
drop table t1,t2;
+#
+# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
+# WHERE and UNION in HAVING
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(7);
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+min_a a
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+min_a a
+drop table t1;
# return optimizer switch changed in the beginning of this test
set optimizer_switch=@subselect_tmp;
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 253be05cc5b..32f24f9cf14 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6118,6 +6118,31 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7;
f1 f2
drop table t1,t2;
+#
+# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
+# WHERE and UNION in HAVING
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(7);
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+min_a a
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+min_a a
+drop table t1;
# return optimizer switch changed in the beginning of this test
set optimizer_switch=@subselect_tmp;
set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index a6e87097c66..3860bb9fb18 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6107,6 +6107,31 @@ NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7;
f1 f2
drop table t1,t2;
+#
+# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
+# WHERE and UNION in HAVING
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(7);
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+min_a a
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+min_a a
+drop table t1;
# return optimizer switch changed in the beginning of this test
set optimizer_switch=@subselect_tmp;
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index e2d829996b7..a476551ff08 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5190,5 +5190,23 @@ SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELE
drop table t1,t2;
+--echo #
+--echo # LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible
+--echo # WHERE and UNION in HAVING
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(7);
+
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1;
+
+EXPLAIN
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
+
+drop table t1;
+
--echo # return optimizer switch changed in the beginning of this test
set optimizer_switch=@subselect_tmp;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 5af8eb9ebc9..16f754575f5 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -892,7 +892,14 @@ void Item_maxmin_subselect::print(String *str, enum_query_type query_type)
void Item_maxmin_subselect::no_rows_in_result()
{
- if (const_item())
+ /*
+ Subquery predicates outside of the SELECT list must be evaluated in order
+ to possibly filter the special result row generated for implicit grouping
+ if the subquery is in the HAVING clause.
+ If the predicate is constant, we need its actual value in the only result
+ row for queries with implicit grouping.
+ */
+ if (parsing_place != SELECT_LIST || const_item())
return;
value= Item_cache::get_cache(new Item_null());
null_value= 0;
@@ -903,7 +910,14 @@ void Item_maxmin_subselect::no_rows_in_result()
void Item_singlerow_subselect::no_rows_in_result()
{
- if (const_item())
+ /*
+ Subquery predicates outside of the SELECT list must be evaluated in order
+ to possibly filter the special result row generated for implicit grouping
+ if the subquery is in the HAVING clause.
+ If the predicate is constant, we need its actual value in the only result
+ row for queries with implicit grouping.
+ */
+ if (parsing_place != SELECT_LIST || const_item())
return;
value= Item_cache::get_cache(new Item_null());
reset();
@@ -1367,7 +1381,14 @@ Item* Item_exists_subselect::expr_cache_insert_transformer(uchar *thd_arg)
void Item_exists_subselect::no_rows_in_result()
{
- if (const_item())
+ /*
+ Subquery predicates outside of the SELECT list must be evaluated in order
+ to possibly filter the special result row generated for implicit grouping
+ if the subquery is in the HAVING clause.
+ If the predicate is constant, we need its actual value in the only result
+ row for queries with implicit grouping.
+ */
+ if (parsing_place != SELECT_LIST || const_item())
return;
value= 0;
null_value= 0;
@@ -2713,7 +2734,14 @@ void Item_allany_subselect::print(String *str, enum_query_type query_type)
void Item_allany_subselect::no_rows_in_result()
{
- if (const_item())
+ /*
+ Subquery predicates outside of the SELECT list must be evaluated in order
+ to possibly filter the special result row generated for implicit grouping
+ if the subquery is in the HAVING clause.
+ If the predicate is constant, we need its actual value in the only result
+ row for queries with implicit grouping.
+ */
+ if (parsing_place != SELECT_LIST || const_item())
return;
value= 0;
null_value= 0;
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 7f2aa857459..0735df2fb5c 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -147,8 +147,8 @@ public:
null_value= 1;
}
/**
- Set the subquery result to the default value for the predicate when the
- subquery is known to produce an empty result.
+ Set the subquery result to a default value consistent with the semantics of
+ the result row produced for queries with implicit grouping.
*/
void no_rows_in_result()= 0;
virtual bool select_transformer(JOIN *join);
View Lorry Controller Status