From: Tor Didriksen Date: July 20 2011 7:39am Subject: bzr push into mysql-trunk branch (tor.didriksen:3404 to 3405) Bug#12763207 List-Archive: http://lists.mysql.com/commits/140383 X-Bug: 12763207 Message-Id: <201107200739.p6K7dsNn017186@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3405 Tor Didriksen 2011-07-20 Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER A dbug-assert which was a bit too aggressive. @ mysql-test/include/subquery.inc New test cases. @ mysql-test/r/subquery_all.result New test cases. @ mysql-test/r/subquery_all_jcl6.result New test cases. @ mysql-test/r/subquery_nomat_nosj.result New test cases. @ mysql-test/r/subquery_nomat_nosj_jcl6.result New test cases. @ mysql-test/r/subquery_none.result New test cases. @ mysql-test/r/subquery_none_jcl6.result New test cases. @ sql/item_subselect.cc Only assert if we have a field, not for constants. modified: mysql-test/include/subquery.inc mysql-test/r/subquery_all.result mysql-test/r/subquery_all_jcl6.result mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_nomat_nosj_jcl6.result mysql-test/r/subquery_none.result mysql-test/r/subquery_none_jcl6.result sql/item_subselect.cc 3404 Guilhem Bichot 2011-07-19 Optimizer trace. By me: WL#5257 Create a first API for the Optimizer trace By Jorgen Loland: WL#5594 Add optimizer traces to the range optimizer WL#5741 Ensure optimizer tracing for subqueries works without JSON syntax errors WL#5581 Add --opt-trace-protocol to MTR. Summary below. * This feature produces a trace for any SELECT/INSERT/UPDATE/DELETE/DO/SET/CALL, which contains information about decisions taken by the optimizer during the optimization phase (choice of table access method, various costs, transformations, etc). It should help understand the logic followed by the optimizer for a particular query. Trace is session-local, controlled by the @@optimizer_trace variable and other new variables, and readable by selecting from information_schema.optimizer_trace. See the doxygen comments in opt_trace.h for more info. * By default the tracing code is compiled in, but not enabled at runtime; set @@optimizer_trace to enable it. * Changes to test results: unless otherwise noted they are due to those changes: ** The query printed by EXPLAIN EXTENDED now shows the id of each SELECT, to help interpret the "id" column. ** EXPLAIN EXTENDED now prints triggered conditions as: trigcond_if(outer_field_is_not_null, (`test`.`t2`.`s1`), true) instead of: trigcond((`test`.`t2`.`s1`). * @@optimizer_trace_offset is a signed variable, so I had to add support for this in MySQL (so far all integer system variables were unsigned, it was impossible to set a system variable to a negative value, it would be rounded to 0). @ CMakeLists.txt support -DOPTIMIZER_TRACE=0|1 @ client/mysqltest.cc option --opt-trace-protocol prints the trace of tested queries. @ dbug/dbug.c Fix for bug: EXCLUDE & SUBDIR is 0, so the line was doing nothing. So with this code: DBUG_SET("+d,xx"); // adds to list of dbug keywords (cs->stack->keywords), with "INCLUDE" state DBUG_SET("-d,xx"); // dels from list, now list empty DBUG_SET("-d,xx"); // adds to list, with "EXCLUDE" state DBUG_SET("+d,xx"); // modifies list element, gives it "INCLUDE" state, now element has both "EXCLUDE" _and_ "INCLUDE" state!! The sequence above can happen in repetitions of this pattern: 1) DBUG_SET("+d,simulate_out_of_memory"); 2) Possible but not guaranteed call to my_realloc() or my_malloc(), which if called do DBUG_SET("-d,simulate_out_of_memory"); 3) DBUG_SET("-d,simulate_out_of_memory"); (needed if my_realloc()/malloc was not called in (2)). Such bug was exposed when testing OOM handling of opt trace. @ include/my_sys.h * When free()ing a memroot block, trash it with bad data, it helps getting a crash when this block is wrongly used. * dynamic array (used by optimizer trace) is improved: ** by using void* instead of uchar* some casts can go away, and it's more logical (any data can be pointed by a void*, but not all data is made of uchar-s). ** get_index_dynamic() was not used @ include/mysql/plugin.h comments @ include/mysql/plugin_audit.h.pp just spacing change @ include/mysql/plugin_auth.h.pp just spacing change @ include/mysql/plugin_ftparser.h.pp just spacing change @ mysql-test/collections/default.experimental main.subquery_sj_none_jcl* fail like main.subquery_sj_none. @ mysql-test/include/have_optimizer_trace.inc detect whether opt trace is compiled in @ mysql-test/mysql-test-run.pl * run new suite "optimizer_trace" by default * pass some options to mysqld when using --opt-trace-protocol * comments @ mysql-test/r/information_schema.result new I_S table @ mysql-test/r/information_schema_db.result new I_S table @ mysql-test/r/mysqld--help-notwin.result new opt trace variables @ mysql-test/r/mysqld--help-win.result new opt trace variables @ mysql-test/r/mysqlshow.result new I_S table @ mysql-test/r/shm.result The query printed by EXPLAIN EXTENDED now shows the id of each SELECT, to help interpret the "id" column. @ mysql-test/suite/funcs_1/r/is_columns_is.result new I_S table @ mysql-test/suite/funcs_1/r/is_columns_is_embedded.result new I_S table @ mysql-test/suite/funcs_1/r/is_tables_is.result new I_S table @ mysql-test/suite/optimizer_trace/include/optimizer_trace.inc first test of "general behaviour" of opt trace @ mysql-test/suite/optimizer_trace/include/optimizer_trace2.inc second test of "general behaviour" of opt trace @ mysql-test/suite/optimizer_trace/include/optimizer_trace_bugs.inc test for fixed opt trace bugs @ mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc test for tracing of range optimizer @ mysql-test/suite/optimizer_trace/include/optimizer_trace_security.inc test that trace does not show information forbidden by lack of privileges. @ mysql-test/suite/optimizer_trace/include/optimizer_trace_subquery.inc test of tracing of subqueries @ mysql-test/suite/optimizer_trace/t/optimizer_trace_charset.test test of tracing of queries using non-utf8 charsets @ mysql-test/suite/optimizer_trace/t/optimizer_trace_debug.test test of opt trace, which requires DBUG_EXECUTE_IF support. @ mysql-test/suite/optimizer_trace/validate_json.py utility script which can be used to validate that traces in a ".result" file are JSON-compliant. This isn't needed by the testsuite, isn't installed by "make install". @ mysql-test/suite/sys_vars/r/optimizer_trace_offset_max.result we see that 100/-100 were truncated to 50/-50 according to --maximum-* @ mysql-test/suite/sys_vars/t/optimizer_trace_basic.test test of setting @@optimizer_trace* variables @ mysql-test/suite/sys_vars/t/optimizer_trace_features_basic.test test of setting @@optimizer_trace* variables @ mysql-test/suite/sys_vars/t/optimizer_trace_limit_basic.test test of setting @@optimizer_trace* variables @ mysql-test/suite/sys_vars/t/optimizer_trace_max_mem_size_basic.test test of setting @@optimizer_trace* variables @ mysql-test/suite/sys_vars/t/optimizer_trace_offset_basic.test test of setting @@optimizer_trace* variables @ mysql-test/suite/sys_vars/t/optimizer_trace_offset_max-master.opt test --maximum-* (a feature of my_getopt) @ mysql-test/suite/sys_vars/t/optimizer_trace_offset_max.test test of setting @@optimizer_trace* variables @ mysys/array.c see comment of my_sys.h @ mysys/my_alloc.c * see comment of my_sys.h @ mysys/my_getopt.c reducing code size by using a utility function, max_of_int_range() @ mysys/my_malloc.c simulate OOM in my_realloc(), like already existed in my_malloc() @ sql/item.cc warning about dangerous code @ sql/item_cmpfunc.cc more useful printing of Item_func_trig_cond @ sql/item_cmpfunc.h more useful printing of Item_func_trig_cond @ sql/item_func.cc Adding SHOW_SIGNED_LONG variables. "unsigned_flag" is what makes -1 be displaid as -1 and not a huge positive number in "SELECT @@var". @ sql/item_subselect.cc tracing of subqueries @ sql/item_subselect.h tracing of subqueries @ sql/mysqld.cc print opt trace if crash @ sql/opt_explain.cc The query printed by EXPLAIN EXTENDED now shows the id of each SELECT, to help interpret the "id" column. @ sql/opt_range.cc tracing of range optimizer @ sql/opt_range.h tracing of range optimizer @ sql/opt_trace.cc implementation of optimizer trace module @ sql/opt_trace.h interface of optimizer trace module @ sql/opt_trace2server.cc implementation of the connection between the optimizer trace module and the client code (the rest of the server): functions doing security checks before letting tracing go... @ sql/opt_trace_Doxyfile Doxyfile which can be used to generate only documentation for the opt trace. @ sql/opt_trace_context.h reduced interface of optimizer trace module, for those clients which need only Opt_trace_context (this is about having minimal dependencies and not including the full opt_trace.h when not needed). @ sql/set_var.cc see explanation in commit comment of sql/sys_vars.h @ sql/set_var.h SET should be traced, except if it manipulates @@optimizer_trace. @ sql/sp_head.cc * back() has changed: it now returns the last element, not the one after last * security checks related to optimizer trace * tracing of routine substatements IF, CASE, DECLARE, SET, RETURN (they may have a subquery in parameter and thus are worth tracing). * sp_instr::exec_open_and_lock_tables() is deleted as suggested by Dmitry Lenev. @ sql/sql_array.h Changes to Dynamic_array: * added const variant of at(), which cannot change data, and can operate on const objects. * back() now returns the last element, not the one after last, this is more consistent with how it's in STL. * append() can take const arg, doesn't modify it * added pop() and del() @ sql/sql_base.cc using utility function which takes opt trace into account: non-EXPLAIN statements can still need to postponed cleanup of derived tables, if opt trace is enabled. @ sql/sql_class.h * new variables * THD contains an Opt_trace_context * new flag to distinguish statements which can be opt-traced. @ sql/sql_delete.cc tracing of DELETE (uses range optimizer) @ sql/sql_derived.cc debugging @ sql/sql_help.cc tracing (uses range optimizer) @ sql/sql_parse.cc * Mark statement which can be opt-traced, with a flag * For those statements, turn on tracing when mysql_execute_command() starts @ sql/sql_plugin.h new SHOW_SIGNED_LONG @ sql/sql_prepare.cc start tracing when statement preparation starts @ sql/sql_select.cc * tracing of the optimizer in general, semijoin, subquery transformations... * printing of select#x when printing a SELECT, if QT_SHOW_SELECT_NUMBER; x is the select's id, or "fake" if this is the fake UNION select. @ sql/sql_show.cc the change on ptr->type is not needed today (there are no SHOW_SIGNED_LONG variables in SHOW STATUS) but good for the future. The second change ("case") is what makes -1 be displaid as -1 and not a huge positive number in "SHOW VARIABLES" @ sql/sql_test.cc reworking some optimizer's debug-specific functions so that they also send to the opt trace. @ sql/sql_update.cc * tracing of UPDATE (uses range optimizer) * using utility function which takes opt trace into account @ sql/sql_view.cc * tracing of view merging/materialization * security checks for views when opt trace is enabled @ sql/sys_vars.cc new variables for opt trace @ sql/sys_vars.h Support for _signed_ integer system variables. This makes do_check() more complicated, as code cannot be the same as for unsigned. The third parameter of throw_bounds_warning(), named 'fixed', was here 'var->save_result.ulonglong_value != uv' which tests whether there was rounding due to the allowed range defined with VALID_RANGE() (see sys_vars.cc); that rounding is done by getopt_ull_limit_value(). But there is another 'fixing', which is that a negative value is rounded to 0; this is done earlier with if (var->value->unsigned_flag) ... else uv= (ulonglong) (v < 0 ? 0 : v); The passed value of 'fixed' didn't reflect this rounding, so throw_bounds_warning() had to explicitely detect it with the part after || : if (fixed || (!is_unsigned && v < 0)) By changing the parameter to be var->save_result.ulonglong_value != (ulonglong)v which detects whether any change happened between input value (v) and final value (var->save_result.ulonglong_value) we can just test 'fixed' in throw_bounds_warning(). @ sql/table.cc do security check before view changes security context, if opt trace is enabled @ unittest/gunit/opt_trace-t.cc unit test for opt trace. added: mysql-test/include/have_optimizer_trace.inc mysql-test/suite/optimizer_trace/ mysql-test/suite/optimizer_trace/include/ mysql-test/suite/optimizer_trace/include/optimizer_trace.inc mysql-test/suite/optimizer_trace/include/optimizer_trace2.inc mysql-test/suite/optimizer_trace/include/optimizer_trace_bugs.inc mysql-test/suite/optimizer_trace/include/optimizer_trace_range.inc mysql-test/suite/optimizer_trace/include/optimizer_trace_security.inc mysql-test/suite/optimizer_trace/include/optimizer_trace_subquery.inc mysql-test/suite/optimizer_trace/r/ mysql-test/suite/optimizer_trace/r/optimizer_trace2_no_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace2_ps_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs_no_prot_all.result mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs_no_prot_none.result mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs_ps_prot_all.result mysql-test/suite/optimizer_trace/r/optimizer_trace_bugs_ps_prot_none.result mysql-test/suite/optimizer_trace/r/optimizer_trace_charset.result mysql-test/suite/optimizer_trace/r/optimizer_trace_debug.result mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot_all.result mysql-test/suite/optimizer_trace/r/optimizer_trace_no_prot_none.result mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot_all.result mysql-test/suite/optimizer_trace/r/optimizer_trace_ps_prot_none.result mysql-test/suite/optimizer_trace/r/optimizer_trace_range_no_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_range_ps_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_security_no_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_security_ps_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_subquery_no_prot.result mysql-test/suite/optimizer_trace/r/optimizer_trace_subquery_ps_prot.result mysql-test/suite/optimizer_trace/t/ mysql-test/suite/optimizer_trace/t/optimizer_trace2_no_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace2_ps_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs_no_prot_all.test mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs_no_prot_none.test mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs_ps_prot_all.test mysql-test/suite/optimizer_trace/t/optimizer_trace_bugs_ps_prot_none.test mysql-test/suite/optimizer_trace/t/optimizer_trace_charset.test mysql-test/suite/optimizer_trace/t/optimizer_trace_debug.test mysql-test/suite/optimizer_trace/t/optimizer_trace_no_prot_all.test mysql-test/suite/optimizer_trace/t/optimizer_trace_no_prot_none.test mysql-test/suite/optimizer_trace/t/optimizer_trace_ps_prot_all.test mysql-test/suite/optimizer_trace/t/optimizer_trace_ps_prot_none.test mysql-test/suite/optimizer_trace/t/optimizer_trace_range_no_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace_range_ps_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace_security_no_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace_security_ps_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace_subquery_no_prot.test mysql-test/suite/optimizer_trace/t/optimizer_trace_subquery_ps_prot.test mysql-test/suite/optimizer_trace/validate_json.py mysql-test/suite/sys_vars/r/optimizer_trace_basic.result mysql-test/suite/sys_vars/r/optimizer_trace_features_basic.result mysql-test/suite/sys_vars/r/optimizer_trace_limit_basic.result mysql-test/suite/sys_vars/r/optimizer_trace_max_mem_size_basic.result mysql-test/suite/sys_vars/r/optimizer_trace_offset_basic.result mysql-test/suite/sys_vars/r/optimizer_trace_offset_max.result mysql-test/suite/sys_vars/t/optimizer_trace_basic.test mysql-test/suite/sys_vars/t/optimizer_trace_features_basic.test mysql-test/suite/sys_vars/t/optimizer_trace_limit_basic.test mysql-test/suite/sys_vars/t/optimizer_trace_max_mem_size_basic.test mysql-test/suite/sys_vars/t/optimizer_trace_offset_basic.test mysql-test/suite/sys_vars/t/optimizer_trace_offset_max-master.opt mysql-test/suite/sys_vars/t/optimizer_trace_offset_max.test sql/opt_trace.cc sql/opt_trace.h sql/opt_trace2server.cc sql/opt_trace_Doxyfile sql/opt_trace_context.h unittest/gunit/opt_trace-t.cc modified: .bzrignore CMakeLists.txt client/mysqltest.cc config.h.cmake dbug/dbug.c include/my_getopt.h include/my_sys.h include/mysql/plugin.h include/mysql/plugin_audit.h.pp include/mysql/plugin_auth.h.pp include/mysql/plugin_ftparser.h.pp mysql-test/collections/default.experimental mysql-test/mysql-test-run.pl mysql-test/r/archive_gis.result mysql-test/r/auto_increment.result mysql-test/r/bench_count_distinct.result mysql-test/r/bigint.result mysql-test/r/case.result mysql-test/r/cast.result mysql-test/r/compare.result mysql-test/r/compress.result mysql-test/r/ctype_binary.result mysql-test/r/ctype_collate.result mysql-test/r/ctype_cp1251.result mysql-test/r/ctype_latin1.result mysql-test/r/ctype_ucs.result mysql-test/r/ctype_utf8.result mysql-test/r/date_formats.result mysql-test/r/explain.result mysql-test/r/fulltext.result mysql-test/r/func_compress.result mysql-test/r/func_crypt.result mysql-test/r/func_default.result mysql-test/r/func_encrypt.result mysql-test/r/func_gconcat.result mysql-test/r/func_group.result mysql-test/r/func_if.result mysql-test/r/func_in_all.result mysql-test/r/func_in_icp.result mysql-test/r/func_in_icp_mrr.result mysql-test/r/func_in_mrr.result mysql-test/r/func_in_mrr_cost.result mysql-test/r/func_in_none.result mysql-test/r/func_like.result mysql-test/r/func_math.result mysql-test/r/func_op.result mysql-test/r/func_regexp.result mysql-test/r/func_set.result mysql-test/r/func_str.result mysql-test/r/func_system.result mysql-test/r/func_test.result mysql-test/r/func_time.result mysql-test/r/gis.result mysql-test/r/group_by.result mysql-test/r/group_min_max.result mysql-test/r/having.result mysql-test/r/information_schema.result mysql-test/r/information_schema_db.result mysql-test/r/innodb_explain_non_select_all.result mysql-test/r/innodb_explain_non_select_none.result mysql-test/r/innodb_mrr.result mysql-test/r/innodb_mrr_all.result mysql-test/r/innodb_mrr_cost.result mysql-test/r/innodb_mrr_cost_all.result mysql-test/r/innodb_mrr_cost_icp.result mysql-test/r/innodb_mrr_icp.result mysql-test/r/innodb_mrr_none.result mysql-test/r/insert_update.result mysql-test/r/join.result mysql-test/r/join_nested.result mysql-test/r/join_nested_jcl6.result mysql-test/r/join_outer.result mysql-test/r/join_outer_jcl6.result mysql-test/r/myisam_explain_non_select_all.result mysql-test/r/myisam_explain_non_select_none.result mysql-test/r/myisam_mrr.result mysql-test/r/myisam_mrr_all.result mysql-test/r/myisam_mrr_cost.result mysql-test/r/myisam_mrr_cost_all.result mysql-test/r/myisam_mrr_cost_icp.result mysql-test/r/myisam_mrr_icp.result mysql-test/r/myisam_mrr_none.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/mysqlshow.result mysql-test/r/named_pipe.result mysql-test/r/negation_elimination.result mysql-test/r/null.result mysql-test/r/olap.result mysql-test/r/order_by_all.result mysql-test/r/order_by_icp_mrr.result mysql-test/r/order_by_none.result mysql-test/r/partition.result mysql-test/r/partition_pruning.result mysql-test/r/ps.result mysql-test/r/query_cache.result mysql-test/r/row.result mysql-test/r/select_all.result mysql-test/r/select_all_jcl6.result mysql-test/r/select_icp_mrr.result mysql-test/r/select_icp_mrr_jcl6.result mysql-test/r/select_none.result mysql-test/r/select_none_jcl6.result mysql-test/r/shm.result mysql-test/r/ssl.result mysql-test/r/ssl_compress.result mysql-test/r/subquery_all.result mysql-test/r/subquery_all_jcl6.result mysql-test/r/subquery_mat.result mysql-test/r/subquery_mat_all.result mysql-test/r/subquery_mat_none.result mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_nomat_nosj_jcl6.result mysql-test/r/subquery_none.result mysql-test/r/subquery_none_jcl6.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result mysql-test/r/subselect_innodb.result mysql-test/r/type_blob.result mysql-test/r/type_datetime.result mysql-test/r/udf.result mysql-test/r/union.result mysql-test/r/varbinary.result mysql-test/r/variables.result mysql-test/r/view.result mysql-test/suite/binlog/r/binlog_stm_blackhole.result mysql-test/suite/funcs_1/r/is_columns_is.result mysql-test/suite/funcs_1/r/is_columns_is_embedded.result mysql-test/suite/funcs_1/r/is_tables_is.result mysql-test/suite/innodb/r/innodb_gis.result mysql-test/suite/rpl/r/rpl_get_lock.result mysql-test/suite/rpl/r/rpl_master_pos_wait.result mysql-test/suite/sys_vars/t/all_vars.test mysys/array.c mysys/my_alloc.c mysys/my_getopt.c mysys/my_malloc.c sql/CMakeLists.txt sql/handler.h sql/item.cc sql/item_cmpfunc.cc sql/item_cmpfunc.h sql/item_func.cc sql/item_subselect.cc sql/item_subselect.h sql/mysqld.cc sql/mysqld.h sql/opt_explain.cc sql/opt_range.cc sql/opt_range.h sql/set_var.cc sql/set_var.h sql/sp_head.cc sql/sp_head.h sql/sql_array.h sql/sql_base.cc sql/sql_class.h sql/sql_delete.cc sql/sql_derived.cc sql/sql_help.cc sql/sql_parse.cc sql/sql_plugin.h sql/sql_prepare.cc sql/sql_select.cc sql/sql_select.h sql/sql_show.cc sql/sql_test.cc sql/sql_test.h sql/sql_update.cc sql/sql_view.cc sql/sys_vars.cc sql/sys_vars.h sql/table.cc unittest/gunit/CMakeLists.txt === modified file 'mysql-test/include/subquery.inc' --- a/mysql-test/include/subquery.inc 2011-07-07 14:14:08 +0000 +++ b/mysql-test/include/subquery.inc 2011-07-20 07:39:07 +0000 @@ -4034,6 +4034,30 @@ SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +--echo # +--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## All these are subject to the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); + +SET SESSION sql_mode=@old_sql_mode; + +DROP TABLE t1, t2; + --echo End of 5.0 tests. # === modified file 'mysql-test/r/subquery_all.result' --- a/mysql-test/r/subquery_all.result 2011-07-19 15:11:15 +0000 +++ b/mysql-test/r/subquery_all.result 2011-07-20 07:39:07 +0000 @@ -5159,6 +5159,31 @@ ERROR 21000: Subquery returns more than SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); === modified file 'mysql-test/r/subquery_all_jcl6.result' --- a/mysql-test/r/subquery_all_jcl6.result 2011-07-19 15:11:15 +0000 +++ b/mysql-test/r/subquery_all_jcl6.result 2011-07-20 07:39:07 +0000 @@ -5163,6 +5163,31 @@ ERROR 21000: Subquery returns more than SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); === modified file 'mysql-test/r/subquery_nomat_nosj.result' --- a/mysql-test/r/subquery_nomat_nosj.result 2011-07-19 15:11:15 +0000 +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-07-20 07:39:07 +0000 @@ -5159,6 +5159,31 @@ ERROR 21000: Subquery returns more than SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); === modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result' --- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-07-19 15:11:15 +0000 +++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-07-20 07:39:07 +0000 @@ -5163,6 +5163,31 @@ ERROR 21000: Subquery returns more than SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); === modified file 'mysql-test/r/subquery_none.result' --- a/mysql-test/r/subquery_none.result 2011-07-19 15:11:15 +0000 +++ b/mysql-test/r/subquery_none.result 2011-07-20 07:39:07 +0000 @@ -5158,6 +5158,31 @@ ERROR 21000: Subquery returns more than SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); === modified file 'mysql-test/r/subquery_none_jcl6.result' --- a/mysql-test/r/subquery_none_jcl6.result 2011-07-19 15:11:15 +0000 +++ b/mysql-test/r/subquery_none_jcl6.result 2011-07-20 07:39:07 +0000 @@ -5162,6 +5162,31 @@ ERROR 21000: Subquery returns more than SET SESSION sql_mode=@old_sql_mode; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; End of 5.0 tests. create table t_out (subcase char(3), a1 char(2), b1 char(2), c1 char(2)); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2011-07-19 15:11:15 +0000 +++ b/sql/item_subselect.cc 2011-07-20 07:39:07 +0000 @@ -1205,7 +1205,13 @@ Item_in_subselect::single_value_transfor print_where(item, "rewrite with MIN/MAX", QT_ORDINARY);); if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) { - DBUG_ASSERT(select_lex->non_agg_field_used()); + /* + If the argument is a field, we assume that fix_fields() has + tagged the select_lex with non_agg_field_used. + We reverse that decision after this rewrite with MIN/MAX. + */ + if (item->get_arg(0)->type() == Item::FIELD_ITEM) + DBUG_ASSERT(select_lex->non_agg_field_used()); select_lex->set_non_agg_field_used(false); } No bundle (reason: useless for push emails).