3509 Jorgen Loland 2011-10-18
WL#5953: Optimize away useless subquery clauses
Follow-up patch: Show the transformations in optimizer trace
modified:
mysql-test/suite/opt_trace/include/subquery.inc
mysql-test/suite/opt_trace/r/subquery_no_prot.result
mysql-test/suite/opt_trace/r/subquery_ps_prot.result
sql/sql_select.cc
3508 Tor Didriksen 2011-10-18
Bug#12582849 ASSERTION FAILURE IN __CXA_PURE_VIRTUAL/ITEM_COND::FIX_FIELDS
This is a variant of earlier bugs: 12603457 and 12603141:
Sometimes Item_field objects are wrapped in Item_ref objects.
These must be stripped off when saving conditions in in 'prep_where'.
@ mysql-test/r/ps.result
New test case.
@ mysql-test/t/ps.test
New test case.
@ sql/sql_lex.cc
Fix garbled comment.
modified:
mysql-test/r/ps.result
mysql-test/t/ps.test
sql/item_cmpfunc.cc
sql/sql_lex.cc
=== modified file 'mysql-test/suite/opt_trace/include/subquery.inc'
--- a/mysql-test/suite/opt_trace/include/subquery.inc 2011-08-31 16:48:23 +0000
+++ b/mysql-test/suite/opt_trace/include/subquery.inc 2011-10-18 14:27:48 +0000
@@ -48,6 +48,14 @@ WHERE NOT EXISTS
SELECT * FROM information_schema.OPTIMIZER_TRACE;
--echo
+# Distinct, order and group is removed from subquery. Note: For PS,
+# removal happens during prepare so the only visible effect is that
+# the subquery does not contain those clauses.
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b);
+--echo
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+--echo
+
DROP TABLE t1,t2;
--echo #
=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2011-10-18 10:23:09 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result 2011-10-18 14:27:48 +0000
@@ -669,6 +669,131 @@ WHERE NOT EXISTS
] /* steps */
} 0 0
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b);
+1
+1
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "transformations_to_subquery": [
+ "removed_ordering",
+ "removed_distinct",
+ "removed_grouping"
+ ] /* transformations_to_subquery */
+ },
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where (not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_evaluation": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "database": "test",
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "rows": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* rows_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* subselect_evaluation */,
+ "resulting_condition": null
+ }
+ ] /* steps */
+ } /* condition_processing */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
DROP TABLE t1,t2;
#
# BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
@@ -825,6 +950,11 @@ field4,field5,field6 {
"select#": 3,
"steps": [
{
+ "transformations_to_subquery": [
+ "removed_distinct"
+ ] /* transformations_to_subquery */
+ },
+ {
"expanded_query": "/* select#3 */ select `test`.`c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`test`.`t4` `c_sq1_alias2` left join `test`.`t3` `c_sq1_alias1` on((`test`.`c_sq1_alias2`.`col_int_nokey` = `test`.`c_sq1_alias1`.`pk`))) where (`test`.`c_sq1_alias2`.`col_varchar_key` = `test`.`sq1_alias2`.`col_varchar_nokey`)"
}
] /* steps */
=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2011-10-18 10:23:09 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result 2011-10-18 14:27:48 +0000
@@ -669,6 +669,124 @@ WHERE NOT EXISTS
] /* steps */
} 0 0
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b);
+1
+1
+
+SELECT * FROM information_schema.OPTIMIZER_TRACE;
+QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
+SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t1 GROUP BY a ORDER BY b) {
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 1,
+ "steps": [
+ {
+ "join_preparation": {
+ "select#": 2,
+ "steps": [
+ {
+ "expanded_query": "/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL where (not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+ }
+ ] /* steps */
+ } /* join_preparation */
+ },
+ {
+ "join_optimization": {
+ "select#": 1,
+ "steps": [
+ {
+ "condition_processing": {
+ "condition": "WHERE",
+ "original_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))",
+ "steps": [
+ {
+ "transformation": "equality_propagation",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+ },
+ {
+ "transformation": "constant_propagation",
+ "subselect_evaluation": [
+ ] /* subselect_evaluation */,
+ "resulting_condition": "(not(exists(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1`)))"
+ },
+ {
+ "transformation": "trivial_condition_removal",
+ "subselect_evaluation": [
+ {
+ "subselect_execution": {
+ "select#": 2,
+ "steps": [
+ {
+ "join_optimization": {
+ "select#": 2,
+ "steps": [
+ {
+ "table_dependencies": [
+ {
+ "database": "test",
+ "table": "t1",
+ "row_may_be_null": false,
+ "map_bit": 0,
+ "depends_on_map_bits": [
+ ] /* depends_on_map_bits */
+ }
+ ] /* table_dependencies */
+ },
+ {
+ "rows_estimation": [
+ {
+ "database": "test",
+ "table": "t1",
+ "rows": 1,
+ "cost": 1,
+ "table_type": "system"
+ }
+ ] /* rows_estimation */
+ }
+ ] /* steps */,
+ "empty_result": {
+ "cause": "no matching row in const table"
+ } /* empty_result */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 2,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+ } /* subselect_execution */
+ }
+ ] /* subselect_evaluation */,
+ "resulting_condition": null
+ }
+ ] /* steps */
+ } /* condition_processing */
+ }
+ ] /* steps */
+ } /* join_optimization */
+ },
+ {
+ "join_execution": {
+ "select#": 1,
+ "steps": [
+ ] /* steps */
+ } /* join_execution */
+ }
+ ] /* steps */
+} 0 0
+
DROP TABLE t1,t2;
#
# BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-10-18 10:23:09 +0000
+++ b/sql/sql_select.cc 2011-10-18 14:27:48 +0000
@@ -598,14 +598,27 @@ void remove_redundant_subquery_clauses(s
subq_predicate->substype() == Item_subselect::IN_SUBS ||
subq_predicate->substype() == Item_subselect::ALL_SUBS ||
subq_predicate->substype() == Item_subselect::ANY_SUBS);
+
+ enum change
+ {
+ REMOVE_NONE=0,
+ REMOVE_ORDER= 1 << 0,
+ REMOVE_DISTINCT= 1 << 1,
+ REMOVE_GROUP= 1 << 2
+ };
+
+ uint changelog= 0;
+
if (subq_select_lex->order_list.elements)
{
+ changelog|= REMOVE_ORDER;
subq_select_lex->join->order= NULL;
subq_select_lex->order_list.empty();
}
if (subq_select_lex->options & SELECT_DISTINCT)
{
+ changelog|= REMOVE_DISTINCT;
subq_select_lex->join->select_distinct= false;
subq_select_lex->options&= ~SELECT_DISTINCT;
}
@@ -617,9 +630,26 @@ void remove_redundant_subquery_clauses(s
if (subq_select_lex->group_list.elements &&
!subq_select_lex->with_sum_func && !subq_select_lex->join->having)
{
+ changelog|= REMOVE_GROUP;
subq_select_lex->join->group_list= NULL;
subq_select_lex->group_list.empty();
}
+
+ if (changelog)
+ {
+ Opt_trace_context * trace= &subq_select_lex->join->thd->opt_trace;
+ if (unlikely(trace->is_started()))
+ {
+ Opt_trace_object trace_wrapper(trace);
+ Opt_trace_array trace_changes(trace, "transformations_to_subquery");
+ if (changelog & REMOVE_ORDER)
+ trace_changes.add_alnum("removed_ordering");
+ if (changelog & REMOVE_DISTINCT)
+ trace_changes.add_alnum("removed_distinct");
+ if (changelog & REMOVE_GROUP)
+ trace_changes.add_alnum("removed_grouping");
+ }
+ }
}
/**
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3508 to 3509) WL#5953 | Jorgen Loland | 19 Oct |
