From: Jorgen Loland Date: October 18 2011 2:28pm Subject: bzr push into mysql-trunk branch (jorgen.loland:3508 to 3509) WL#5953 List-Archive: http://lists.mysql.com/commits/141496 Message-Id: <20111018142805.57CDA1D9@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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).