diff options
| -rw-r--r-- | mysql-test/r/subselect4.result | 57 | ||||
| -rw-r--r-- | mysql-test/t/subselect4.test | 36 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 38 | ||||
| -rw-r--r-- | sql/sql_lex.h | 2 | ||||
| -rw-r--r-- | sql/sql_select.cc | 7 | ||||
| -rw-r--r-- | sql/sql_union.cc | 48 |
6 files changed, 166 insertions, 22 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index a4fd1123227..262170851af 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2632,7 +2632,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY A ALL NULL NULL NULL NULL 2 3 UNION B ALL NULL NULL NULL NULL 2 -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Using filesort SELECT t1.a FROM t1 WHERE EXISTS (SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); a 1 @@ -2643,6 +2643,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT UNION B ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); a 1 @@ -2656,4 +2657,58 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); ERROR 42S22: Unknown column 's.id' in 'group statement' DROP TABLE t1; +# +# MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT +# or on EXPLAIN EXTENDED +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); +a +1 +2 +3 +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); +a +1 +2 +3 +PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a))'; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +DEALLOCATE PREPARE stmt; +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT id)); +ERROR 42S22: Unknown column 'id' in 'field list' +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT id)); +ERROR 42S22: Unknown column 'id' in 'field list' +DROP TABLE t1; # end of 10.1 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 03929517126..8bd43661dbc 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2173,4 +2173,40 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); DROP TABLE t1; +--echo # +--echo # MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT +--echo # or on EXPLAIN EXTENDED +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a))'; + +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT id)); + +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT id)); + +DROP TABLE t1; + --echo # end of 10.1 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index e863308159e..853b4d8cc1a 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3170,7 +3170,41 @@ bool st_select_lex_unit::union_needs_tmp_table() global_parameters()->order_list.elements != 0 || thd->lex->sql_command == SQLCOM_INSERT_SELECT || thd->lex->sql_command == SQLCOM_REPLACE_SELECT; -} +} + + +/* + @brief + Check if the ORDER BY clause is redundant or empty + + @retval + TRUE order by is redundant or empty + FALSE OTHERWISE +*/ + +bool st_select_lex_unit::is_order_by_clause_redundant() +{ + if (global_parameters()->order_list.elements == 0) + return true; + if (is_union_op_inside_in_predicate()) + return true; + return false; +} + + +/* + @brief + Check if UNION is in side an IN/ALL/ANY subquery + + @retval + TRUE UNION inside in an IN/ALL/ANY subquery + FALSE Otherwise +*/ +bool st_select_lex_unit::is_union_op_inside_in_predicate() +{ + return is_union() && item && item->is_in_predicate(); +} + /** @brief Set the initial purpose of this TABLE_LIST object in the list of used @@ -4678,7 +4712,7 @@ int st_select_lex_unit::save_union_explain(Explain_query *output) eu->add_select(sl->select_number); eu->fake_select_type= "UNION RESULT"; - eu->using_filesort= MY_TEST(global_parameters()->order_list.first); + eu->using_filesort= MY_TEST(!is_order_by_clause_redundant()); eu->using_tmp= union_needs_tmp_table(); // Save the UNION node diff --git a/sql/sql_lex.h b/sql/sql_lex.h index cdf80daa928..f18c9f72cce 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -699,6 +699,8 @@ public: void set_thd(THD *thd_arg) { thd= thd_arg; } inline bool is_union (); bool union_needs_tmp_table(); + bool is_order_by_clause_redundant(); + bool is_union_op_inside_in_predicate(); void set_unique_exclude(); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7a1a7baaa1c..912f28d7168 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -590,6 +590,13 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) DBUG_PRINT("info", ("DISTINCT removed")); } + if (subq_select_lex->master_unit()->is_union_op_inside_in_predicate() && + subq_select_lex->master_unit()->fake_select_lex == subq_select_lex && + subq_select_lex->order_list.elements) + { + subq_select_lex->join->order= NULL; + } + /* Remove GROUP BY if there are no aggregate functions and no HAVING clause diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 0e623777ef0..3f03baf73ec 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -331,6 +331,35 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg, (*order->item)->walk(&Item::set_fake_select_as_master_processor, 0, (uchar*) fake_select_lex); } + + /* + If we are reading UNION output and the UNION is in the + IN/ANY/ALL subquery, then ORDER BY is redundant and hence should + be removed. + Example: + select ... col IN (select col2 FROM t1 union select col3 from t2 ORDER BY 1) + + (as for ORDER BY ... LIMIT, it currently not supported inside + IN/ALL/ANY subqueries) + ORDER BY LIMIT is allowed in EXISTS subqueries, so we don't remove + the ORDER BY clause from EXISTS subqueries + (For non-UNION this removal of ORDER BY clause is done in + check_and_do_in_subquery_rewrites()) + @see remove_redundant_subquery_clauses + */ + if (is_union_op_inside_in_predicate()) + { + for (ORDER *ord= global_parameters()->order_list.first; ord; ord= ord->next) + { + /* + The subqueries inside the ORDER BY clause are marked as eliminated. + This is done to avoid adding the subqueries as children of the + fake_select_lex in the EXPLAIN structures. + */ + (*ord->item)->walk(&Item::mark_as_eliminated_processor, FALSE, NULL); + } + } + } @@ -388,25 +417,6 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS; is_union_select= is_union() || fake_select_lex; - /* - If we are reading UNION output and the UNION is in the - IN/ANY/ALL/EXISTS subquery, then ORDER BY is redundant and hence should - be removed. - Example: - select ... col IN (select col2 FROM t1 union select col3 from t2 ORDER BY 1) - - (as for ORDER BY ... LIMIT, it currently not supported inside - IN/ALL/ANY subqueries) - (For non-UNION this removal of ORDER BY clause is done in - check_and_do_in_subquery_rewrites()) - */ - if (is_union() && item && - (item->is_in_predicate() || item->is_exists_predicate())) - { - global_parameters()->order_list.first= NULL; - global_parameters()->order_list.elements= 0; - } - /* Global option */ if (is_union_select) |