summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result21
-rw-r--r--mysql-test/r/subselect_no_mat.result20
-rw-r--r--mysql-test/r/subselect_no_opts.result21
-rw-r--r--mysql-test/r/subselect_no_scache.result21
-rw-r--r--mysql-test/r/subselect_no_semijoin.result21
-rw-r--r--mysql-test/t/subselect.test23
-rw-r--r--sql/sql_select.cc9
7 files changed, 133 insertions, 3 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f775336299b..da0271b3f6a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6847,6 +6847,27 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
#
CREATE TABLE t1 (a INT, KEY(a));
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index e72d25fdafa..4e0f0e491b6 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6845,6 +6845,26 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
#
CREATE TABLE t1 (a INT, KEY(a));
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 9030265356b..756b96e2fae 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6842,6 +6842,27 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
#
CREATE TABLE t1 (a INT, KEY(a));
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index e68f5990c08..f648385f67a 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6853,6 +6853,27 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
#
CREATE TABLE t1 (a INT, KEY(a));
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index f0ce541294a..89d8f640312 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6842,6 +6842,27 @@ id select_type table type possible_keys key key_len ref rows Extra
2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index
DROP TABLE t1,t2;
#
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
#
CREATE TABLE t1 (a INT, KEY(a));
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9f35ecc43f1..11172b8bf52 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5757,6 +5757,29 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B
DROP TABLE t1,t2;
--echo #
+--echo # MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+--echo #
+
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+
+drop table t1, t2, t3;
+
+
+--echo #
--echo # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
--echo #
CREATE TABLE t1 (a INT, KEY(a));
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b4489ddc81e..13c1f499edb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3876,8 +3876,10 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
new_fields->null_rejecting);
}
else if (old->eq_func && new_fields->eq_func &&
- ((old->val->const_item() && old->val->is_null()) ||
- new_fields->val->is_null()))
+ ((old->val->const_item() && !old->val->is_expensive() &&
+ old->val->is_null()) ||
+ (!new_fields->val->is_expensive() &&
+ new_fields->val->is_null())))
{
/* field = expression OR field IS NULL */
old->level= and_level;
@@ -3891,7 +3893,8 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
Remember the NOT NULL value unless the value does not depend
on other tables.
*/
- if (!old->val->used_tables() && old->val->is_null())
+ if (!old->val->used_tables() && !old->val->is_expensive() &&
+ old->val->is_null())
old->val= new_fields->val;
}
else
View Lorry Controller Status