April 2010 - developers - lists.mariadb.org

Re: [Maria-developers] 5.3 subquery questions
by Sergey Petrunya 07 Apr '10

07 Apr '10

Hi Sanja, On Wed, Apr 07, 2010 at 01:54:25PM +0300, Oleksandr Byelkin wrote: > I found 1 interesting test case: > > create table t1 (a int, b int); > insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6), > (5,6),(4,5); > create table t2 (c int, d int); > insert into t2 values (2,3),(3,4),(5,6); > > select a, b from t1 where b in (select d from t2); > > It crashes but only if test case started with --debug. I assume this is with current lp:~maria-captains/maria/5.3-subqueries? I'll take a look. > Also could you give me example where Item_in_optimizer will be used for > sure (in above example it looks like it is not used). Basically, subquery must not be a semi-join. The easiest way to achieve this is to make it so that the subuqery is not an AND-part of ther WHERE anymore. The following will use Item_in_optimizer: select a, b, b in (select d from t2) as SUBQ from t1 ; select a, b from t1 where b in (select d from t2) or b < 3; If you want to get lots of test coverage fast, include the standard subselect*.test after having done: SET optimizer_switch='semijoin=off,materialization=off'; This will cause Item_in_optimizer to be used for every IN subquery. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

[Maria-developers] Rev 32: * Increased run time to 20 minutes in file:///Users/hakan/work/monty_program/mariadb-tools/
by Hakan Kuecuekyilmaz 07 Apr '10

07 Apr '10

At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 32 revision-id: hakan(a)askmonty.org-20100407121715-2k24p66xyfkgpdpn parent: hakan(a)askmonty.org-20100331181744-71hyuyjjlijrsl68 committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Wed 2010-04-07 14:17:15 +0200 message: * Increased run time to 20 minutes * Increased warm up time to 5 minutes * Added select_random_points.lua and select_random_ranges.lua tests * Added --log-error to mysqld startup options to catch errors and crashes. === modified file 'sysbench/run-sysbench-myisam.sh' --- a/sysbench/run-sysbench-myisam.sh 2010-03-31 18:16:51 +0000 +++ b/sysbench/run-sysbench-myisam.sh 2010-04-07 12:17:15 +0000 @@ -17,6 +17,7 @@ # * oltp_complex_rw.lua 313M sbtest.MYI # * oltp_simple.lua 325M sbtest.MYI # * select.lua 313M sbtest.MYI +# * select_random_points.lua 313M sbtest.MYI # * select_random_ranges.lua 313M sbtest.MYI # * update_index.lua 313M sbtest.MYI # * update_non_index.lua 313M sbtest.MYI @@ -75,6 +76,7 @@ MYSQLD_OPTIONS="--no-defaults \ --datadir=$DATA_DIR \ --language=./sql/share/english \ + --log-error \ --key_buffer_size=32M \ --max_connections=256 \ --query_cache_size=0 \ @@ -93,7 +95,7 @@ TABLE_SIZE=20000000 # The run time we use for sysbench. -RUN_TIME=1800 +RUN_TIME=1200 # Warm up time we use for sysbench. WARM_UP_TIME=300 @@ -110,6 +112,7 @@ oltp_complex_rw.lua \ oltp_simple.lua \ select.lua \ + select_random_points.lua \ select_random_ranges.lua \ update_index.lua \ update_non_index.lua" === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-03-31 18:16:51 +0000 +++ b/sysbench/run-sysbench.sh 2010-04-07 12:17:15 +0000 @@ -62,6 +62,7 @@ MYSQLD_OPTIONS="--no-defaults \ --datadir=$DATA_DIR \ --language=./sql/share/english \ + --log-error \ --max_connections=256 \ --query_cache_size=0 \ --query_cache_type=0 \ @@ -91,10 +92,10 @@ TABLE_SIZE=2000000 # The run time we use for sysbench. -RUN_TIME=300 +RUN_TIME=1200 # Warm up time we use for sysbench. -WARM_UP_TIME=180 +WARM_UP_TIME=300 # How many times we run each test. LOOP_COUNT=3 @@ -108,6 +109,8 @@ oltp_complex_rw.lua \ oltp_simple.lua \ select.lua \ + select_random_points.lua \ + select_random_ranges.lua \ update_index.lua \ update_non_index.lua"

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2841: Removed compiler warnings
by noreply＠launchpad.net 07 Apr '10

07 Apr '10

------------------------------------------------------------ revno: 2841 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Wed 2010-04-07 01:47:08 +0300 message: Removed compiler warnings Removed random failures from test suite added: mysql-test/suite/binlog/t/binlog_index-master.opt mysql-test/suite/pbxt/t/multi_statement-master.opt modified: mysql-test/extra/rpl_tests/rpl_insert_id_pk.test mysql-test/include/maria_empty_logs.inc mysql-test/suite/binlog/r/binlog_index.result mysql-test/suite/binlog/t/binlog_index.test mysql-test/suite/pbxt/r/multi_statement.result sql/events.cc sql/handler.cc sql/log.cc sql/mysqld.cc sql/set_var.cc sql/slave.cc sql/sql_plugin.cc storage/xtradb/include/ut0lst.h support-files/compiler_warnings.supp -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2841)
by Michael Widenius 07 Apr '10

07 Apr '10

#At lp:maria based on revid:monty@askmonty.org-20100401094011-hdhc99gn00rhidy5 2841 Michael Widenius 2010-04-07 Removed compiler warnings Removed random failures from test suite added: mysql-test/suite/binlog/t/binlog_index-master.opt mysql-test/suite/pbxt/t/multi_statement-master.opt modified: mysql-test/extra/rpl_tests/rpl_insert_id_pk.test mysql-test/include/maria_empty_logs.inc mysql-test/suite/binlog/r/binlog_index.result mysql-test/suite/binlog/t/binlog_index.test mysql-test/suite/pbxt/r/multi_statement.result sql/events.cc sql/handler.cc sql/log.cc sql/mysqld.cc sql/set_var.cc sql/slave.cc sql/sql_plugin.cc storage/xtradb/include/ut0lst.h support-files/compiler_warnings.supp per-file messages: mysql-test/extra/rpl_tests/rpl_insert_id_pk.test Make test predicatable. mysql-test/include/maria_empty_logs.inc We can't use 'Threads_connected' for syncronization, as the 'check_warnings' thread that just quit may still be counted in 'Threads_connected' Now we just wait until MySQLD answers again, which should be good enough for our purposes mysql-test/suite/binlog/r/binlog_index.result Updated results file mysql-test/suite/binlog/t/binlog_index-master.opt Added option file to not get stack traces in .err file. mysql-test/suite/binlog/t/binlog_index.test Added 'flush tables' to remove warning about crashed suppression file from logs mysql-test/suite/pbxt/r/multi_statement.result Updated results mysql-test/suite/pbxt/t/multi_statement-master.opt Added options so that slow query testing makes sense sql/events.cc Don't write Event Scheduler startup message if warnings are turned off. sql/handler.cc Removed compiler warning sql/log.cc Removed compiler warning sql/mysqld.cc Added option 'test-expect-abort'; If this is set, we don't write message to log in case of 'DBUG_ABORT'. (Gives us smaller, easier to read log files) sql/set_var.cc Removed compiler warning sql/slave.cc Removed compiler warning sql/sql_plugin.cc Don't write warnings about disabled plugin if using --log_warnings=0 storage/xtradb/include/ut0lst.h Removed compiler warning support-files/compiler_warnings.supp Supress warning from xtradb === modified file 'mysql-test/extra/rpl_tests/rpl_insert_id_pk.test' --- a/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test 2007-06-06 17:57:07 +0000 +++ b/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test 2010-04-06 22:47:08 +0000 @@ -82,7 +82,6 @@ SET FOREIGN_KEY_CHECKS=0; # Duplicate Key Errors codes --error 1022, ER_DUP_ENTRY INSERT INTO t1 VALUES (1),(1); -sync_slave_with_master; -connection master; drop table t1; +sync_slave_with_master; # End of 4.1 tests === modified file 'mysql-test/include/maria_empty_logs.inc' --- a/mysql-test/include/maria_empty_logs.inc 2009-10-13 12:47:17 +0000 +++ b/mysql-test/include/maria_empty_logs.inc 2010-04-06 22:47:08 +0000 @@ -11,9 +11,10 @@ connection default; let $default_db=`select database()`; let $MYSQLD_DATADIR= `SELECT @@datadir`; -# it will used at end of test for wait_for_status_var.inc primitive -let $status_var= Threads_connected; -let $status_var_value= query_get_value(SHOW STATUS LIKE 'Threads_connected', Value, 1); + +#it will used at end of test for wait_for_status_var.inc primitive +#let $status_var= Threads_connected; +#let $status_var_value= query_get_value(SHOW STATUS LIKE 'Threads_connected', Value, 1); connection admin; -- echo * shut down mysqld, removed logs, restarted it @@ -77,12 +78,15 @@ append_file $MYSQLTEST_VARDIR/tmp/mysqld restart-maria_empty_logs.inc EOF +connection default; --source include/wait_until_connected_again.inc -connection default; # Make sure that all connections are restored ---source include/wait_for_status_var.inc +# This is disabled as 'Threads_connected' can't be trusted' +# (It may be affected by 'check_testcase()') +# --source include/wait_for_status_var.inc # Restore current database as the effect of "use" was lost after restart + --disable_query_log eval use $default_db; --enable_query_log === modified file 'mysql-test/suite/binlog/r/binlog_index.result' --- a/mysql-test/suite/binlog/r/binlog_index.result 2009-12-16 19:52:56 +0000 +++ b/mysql-test/suite/binlog/r/binlog_index.result 2010-04-06 22:47:08 +0000 @@ -3,6 +3,7 @@ call mtr.add_suppression('MSYQL_BIN_LOG: call mtr.add_suppression('MSYQL_BIN_LOG::open failed to sync the index file'); call mtr.add_suppression('Turning logging off for the whole duration of the MySQL server process.'); call mtr.add_suppression('MSYQL_BIN_LOG::purge_logs failed to clean registers before purging logs.'); +flush tables; flush logs; flush logs; flush logs; === added file 'mysql-test/suite/binlog/t/binlog_index-master.opt' --- a/mysql-test/suite/binlog/t/binlog_index-master.opt 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/binlog/t/binlog_index-master.opt 2010-04-06 22:47:08 +0000 @@ -0,0 +1 @@ +--skip-stack-trace --test-expect-abort --log-warnings=0 === modified file 'mysql-test/suite/binlog/t/binlog_index.test' --- a/mysql-test/suite/binlog/t/binlog_index.test 2009-12-08 16:03:19 +0000 +++ b/mysql-test/suite/binlog/t/binlog_index.test 2010-04-06 22:47:08 +0000 @@ -11,6 +11,8 @@ call mtr.add_suppression('MSYQL_BIN_LOG: call mtr.add_suppression('MSYQL_BIN_LOG::open failed to sync the index file'); call mtr.add_suppression('Turning logging off for the whole duration of the MySQL server process.'); call mtr.add_suppression('MSYQL_BIN_LOG::purge_logs failed to clean registers before purging logs.'); +flush tables; + let $old=`select @@debug`; let $MYSQLD_DATADIR= `select @@datadir`; === modified file 'mysql-test/suite/pbxt/r/multi_statement.result' --- a/mysql-test/suite/pbxt/r/multi_statement.result 2009-04-02 10:03:14 +0000 +++ b/mysql-test/suite/pbxt/r/multi_statement.result 2010-04-06 22:47:08 +0000 @@ -47,5 +47,5 @@ i 3 show status like 'Slow_queries'|||| Variable_name Value -Slow_queries 0 +Slow_queries 3 drop table t1|||| === added file 'mysql-test/suite/pbxt/t/multi_statement-master.opt' --- a/mysql-test/suite/pbxt/t/multi_statement-master.opt 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/pbxt/t/multi_statement-master.opt 2010-04-06 22:47:08 +0000 @@ -0,0 +1,3 @@ +--slow-query-log +--slow-query-log-file=slow.log +--log-queries-not-using-indexes === modified file 'sql/events.cc' --- a/sql/events.cc 2010-03-04 08:03:07 +0000 +++ b/sql/events.cc 2010-04-06 22:47:08 +0000 @@ -1263,8 +1263,9 @@ Events::load_events_from_db(THD *thd) } } } - sql_print_information("Event Scheduler: Loaded %d event%s", - count, (count == 1) ? "" : "s"); + if (global_system_variables.log_warnings) + sql_print_information("Event Scheduler: Loaded %d event%s", + count, (count == 1) ? "" : "s"); ret= FALSE; end: === modified file 'sql/handler.cc' --- a/sql/handler.cc 2010-03-30 12:36:49 +0000 +++ b/sql/handler.cc 2010-04-06 22:47:08 +0000 @@ -4298,6 +4298,8 @@ int handler::index_read_idx_map(uchar * enum ha_rkey_function find_flag) { int error, error1; + LINT_INIT(error1); + error= index_init(index, 0); if (!error) { === modified file 'sql/log.cc' --- a/sql/log.cc 2010-03-24 22:12:39 +0000 +++ b/sql/log.cc 2010-04-06 22:47:08 +0000 @@ -1216,6 +1216,7 @@ void LOGGER::deactivate_log_handler(THD { my_bool *tmp_opt= 0; MYSQL_LOG *file_log; + LINT_INIT(file_log); switch (log_type) { case QUERY_LOG_SLOW: === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-03-30 12:36:49 +0000 +++ b/sql/mysqld.cc 2010-04-06 22:47:08 +0000 @@ -1,4 +1,5 @@ /* Copyright (C) 2000-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc. + 2009-2010 Monty Program Ab This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -382,7 +383,7 @@ static bool volatile select_thread_in_us static bool volatile ready_to_exit; static my_bool opt_debugging= 0, opt_external_locking= 0, opt_console= 0; static my_bool opt_short_log_format= 0; -static my_bool opt_ignore_wrong_options= 0; +static my_bool opt_ignore_wrong_options= 0, opt_expect_abort= 0; static uint kill_cached_threads, wake_thread; ulong thread_created; static ulong max_used_connections; @@ -2530,13 +2531,19 @@ extern "C" sig_handler handle_segfault(i curr_time= my_time(0); localtime_r(&curr_time, &tm); - fprintf(stderr,"\ -%02d%02d%02d %2d:%02d:%02d - mysqld got " SIGNAL_FMT " ;\n\ + fprintf(stderr, "%02d%02d%02d %2d:%02d:%02d ", + tm.tm_year % 100, tm.tm_mon+1, tm.tm_mday, + tm.tm_hour, tm.tm_min, tm.tm_sec); + if (opt_expect_abort && sig == SIGABRT) + { + fprintf(stderr,"[Note] mysqld did an expected abort\n"); + goto end; + } + + fprintf(stderr,"[ERROR] mysqld got " SIGNAL_FMT " ;\n\ This could be because you hit a bug. It is also possible that this binary\n\ or one of the libraries it was linked against is corrupt, improperly built,\n\ or misconfigured. This error can also be caused by malfunctioning hardware.\n", - tm.tm_year % 100, tm.tm_mon+1, tm.tm_mday, - tm.tm_hour, tm.tm_min, tm.tm_sec, sig); fprintf(stderr, "\ We will try our best to scrape up some info that will hopefully help diagnose\n\ @@ -2655,6 +2662,7 @@ bugs.\n"); } #endif +end: #ifndef __WIN__ /* On Windows, do not terminate, but pass control to exception filter */ exit(1); @@ -5888,7 +5896,7 @@ enum options_mysqld OPT_MIN_EXAMINED_ROW_LIMIT, OPT_LOG_SLOW_SLAVE_STATEMENTS, OPT_DEBUG_CRC, OPT_DEBUG_ON, OPT_OLD_MODE, - OPT_TEST_IGNORE_WRONG_OPTIONS, + OPT_TEST_IGNORE_WRONG_OPTIONS, OPT_TEST_RESTART, #if defined(ENABLED_DEBUG_SYNC) OPT_DEBUG_SYNC_TIMEOUT, #endif /* defined(ENABLED_DEBUG_SYNC) */ @@ -6737,6 +6745,10 @@ log and this option does nothing anymore "Ignore wrong enums values in command line arguments. Useful only for test scripts", (uchar**) &opt_ignore_wrong_options, (uchar**) &opt_ignore_wrong_options, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"test-expect-abort", OPT_TEST_RESTART, + "Expect that server aborts with 'abort'; Don't write out server variables on 'abort'. Useful only for test scripts", + (uchar**) &opt_expect_abort, (uchar**) &opt_expect_abort, + 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, {"timed_mutexes", OPT_TIMED_MUTEXES, "Specify whether to time mutexes (only InnoDB mutexes are currently supported)", (uchar**) &timed_mutexes, (uchar**) &timed_mutexes, 0, GET_BOOL, NO_ARG, 0, === modified file 'sql/set_var.cc' --- a/sql/set_var.cc 2010-03-28 18:10:00 +0000 +++ b/sql/set_var.cc 2010-04-06 22:47:08 +0000 @@ -2590,7 +2590,7 @@ static int sys_check_log_path(THD *thd, char path[FN_REFLEN], buff[FN_REFLEN]; MY_STAT f_stat; String str(buff, sizeof(buff), system_charset_info), *res; - const char *log_file_str; + const char *log_file_str= 0; size_t path_length; if (!(res= var->value->val_str(&str))) @@ -2640,7 +2640,7 @@ static int sys_check_log_path(THD *thd, err: my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), var->var->name, - res ? log_file_str : "NULL"); + log_file_str ? log_file_str : "NULL"); return 1; } @@ -2649,7 +2649,7 @@ bool update_sys_var_str_path(THD *thd, s set_var *var, const char *log_ext, bool log_state, uint log_type) { - MYSQL_QUERY_LOG *file_log; + MYSQL_QUERY_LOG *file_log= 0; char buff[FN_REFLEN]; char *res= 0, *old_value=(char *)(var ? var->value->str_value.ptr() : 0); bool result= 0; === modified file 'sql/slave.cc' --- a/sql/slave.cc 2010-03-04 08:03:07 +0000 +++ b/sql/slave.cc 2010-04-06 22:47:08 +0000 @@ -2255,6 +2255,7 @@ static int exec_relay_log_event(THD* thd if (slave_trans_retries) { int temp_err; + LINT_INIT(temp_err); if (exec_res && (temp_err= has_temporary_error(thd))) { const char *errmsg; === modified file 'sql/sql_plugin.cc' --- a/sql/sql_plugin.cc 2010-03-12 19:05:21 +0000 +++ b/sql/sql_plugin.cc 2010-04-06 22:47:08 +0000 @@ -1701,9 +1701,10 @@ bool mysql_install_plugin(THD *thd, cons if (tmp->state == PLUGIN_IS_DISABLED) { - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_CANT_INITIALIZE_UDF, ER(ER_CANT_INITIALIZE_UDF), - name->str, "Plugin is disabled"); + if (global_system_variables.log_warnings) + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_CANT_INITIALIZE_UDF, ER(ER_CANT_INITIALIZE_UDF), + name->str, "Plugin is disabled"); } else { === modified file 'storage/xtradb/include/ut0lst.h' --- a/storage/xtradb/include/ut0lst.h 2010-03-30 12:36:49 +0000 +++ b/storage/xtradb/include/ut0lst.h 2010-04-06 22:47:08 +0000 @@ -158,7 +158,7 @@ Inserts a NODE2 after NODE1 in a list. /** Invalidate the pointers in a list node. @param NAME list name @param N pointer to the node that was removed */ -# define UT_LIST_REMOVE_CLEAR(NAME, N) {} while (0) +# define UT_LIST_REMOVE_CLEAR(NAME, N) do {} while (0) #endif /*******************************************************************//** === modified file 'support-files/compiler_warnings.supp' --- a/support-files/compiler_warnings.supp 2010-04-01 09:40:11 +0000 +++ b/support-files/compiler_warnings.supp 2010-04-06 22:47:08 +0000 @@ -40,6 +40,7 @@ sync/sync0sync\.c: unused parameter sync/sync0sync\.c: unused variable ut/ut0ut\.c: ignoring return value of srv/srv0srv\.c: value computed is not used +buf/buf0buf\.c: warning: .*block_mutex.* might be used uninitialized # # bdb is not critical to keep up to date

1 0

[Maria-developers] Rev 2793: Always refer to materialized table as "SUBSELECT#%d" where %d is select number in file:///home/psergey/dev/maria-5.3-subqueries-r12/
by Sergey Petrunya 06 Apr '10

06 Apr '10

At file:///home/psergey/dev/maria-5.3-subqueries-r12/ ------------------------------------------------------------ revno: 2793 revision-id: psergey(a)askmonty.org-20100406212909-r07zximlfpwmyxhm parent: psergey(a)askmonty.org-20100406103533-0upq6g7sse9sjstz committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r12 timestamp: Wed 2010-04-07 01:29:09 +0400 message: Always refer to materialized table as "SUBSELECT#%d" where %d is select number - for Item-based materialization, it was "materialized subselect" - for SJ-Materialization it was "subquery%d" === modified file 'mysql-test/r/group_min_max.result' --- a/mysql-test/r/group_min_max.result 2010-04-06 08:10:58 +0000 +++ b/mysql-test/r/group_min_max.result 2010-04-06 21:29:09 +0000 @@ -2256,7 +2256,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 8 -1 PRIMARY t1_outer ref a a 5 materialized subselect.max(b) 2 Using index +1 PRIMARY t1_outer ref a a 5 SUBQUERY#2.max(b) 2 Using index 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect.result 2010-04-06 21:29:09 +0000 @@ -4328,14 +4328,14 @@ 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 1) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect3.result 2010-04-06 21:29:09 +0000 @@ -1017,7 +1017,7 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where @@ -1035,7 +1035,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +2 DEPENDENT SUBQUERY SUBQUERY#3 eq_ref unique_key unique_key 5 func 1 3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq @@ -1158,7 +1158,7 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); @@ -1207,7 +1207,7 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); @@ -1241,14 +1241,14 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where 2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; @@ -1274,14 +1274,14 @@ explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=default; @@ -1320,7 +1320,7 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 15 func 1 2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer 2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer @@ -1393,7 +1393,7 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-04-06 21:29:09 +0000 @@ -1021,7 +1021,7 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 8 Using temporary; Using filesort 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; Using join buffer 2 SUBQUERY t11 ALL NULL NULL NULL NULL 8 Using where @@ -1040,7 +1040,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY subselect3 eq_ref unique_key unique_key 5 func 1 +2 DEPENDENT SUBQUERY SUBQUERY#3 eq_ref unique_key unique_key 5 func 1 3 SUBQUERY Z ALL NULL NULL NULL NULL 2 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq @@ -1163,7 +1163,7 @@ insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where a in (select a from t2); @@ -1212,7 +1212,7 @@ explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where explain select straight_join * from t1 A, t1 B where A.a in (select a from t2); @@ -1246,14 +1246,14 @@ explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where create table t4 as select a as x, a as y from t1; explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 system NULL NULL NULL NULL 1 -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer 2 SUBQUERY t4 ALL NULL NULL NULL NULL 10 Using where drop table t0,t1,t2,t3,t4; @@ -1279,14 +1279,14 @@ explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 -1 PRIMARY subselect2 eq_ref unique_key unique_key 10 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 10 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 100 set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=default; @@ -1325,7 +1325,7 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 15 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 15 func 1 2 SUBQUERY X ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY Y ALL NULL NULL NULL NULL 6 Using join buffer 2 SUBQUERY Z ALL NULL NULL NULL NULL 6 Using join buffer @@ -1398,7 +1398,7 @@ WHERE cona.postalStripped='T2H3B2' ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 1.00 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1.00 1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer 2 SUBQUERY cona ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 Using join buffer === modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect4.result 2010-04-06 21:29:09 +0000 @@ -216,7 +216,7 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME @@ -231,13 +231,13 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +1 SIMPLE SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; @@ -254,7 +254,7 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME @@ -269,13 +269,13 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +1 SIMPLE SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; @@ -291,7 +291,7 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer PREPARE stmt FROM "EXPLAIN SELECT EMPNAME @@ -306,13 +306,13 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE subselect2 eq_ref unique_key unique_key 3 func 1 +1 SIMPLE SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer DEALLOCATE PREPARE stmt; === modified file 'mysql-test/r/subselect_mat.result' --- a/mysql-test/r/subselect_mat.result 2010-04-06 10:35:33 +0000 +++ b/mysql-test/r/subselect_mat.result 2010-04-06 21:29:09 +0000 @@ -42,7 +42,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where (`materialized subselect`.`b1` = `test`.`t1`.`a1`) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`) select * from t1 where a1 in (select b1 from t2 where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -54,7 +54,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`materialized subselect`.`b1` = `test`.`t1`.`a1`) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`) select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -66,7 +66,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`b2` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`SUBQUERY#2`.`b2` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); a1 a2 1 - 01 2 - 01 @@ -78,7 +78,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`materialized subselect`.`min(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`SUBQUERY#2`.`min(b2)` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -90,7 +90,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1i.a1 1 100.00 2 SUBQUERY t2i index it2i1,it2i3 it2i1 9 NULL 5 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where (`materialized subselect`.`b1` = `test`.`t1i`.`a1`) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where (`SUBQUERY#2`.`b1` = `test`.`t1i`.`a1`) select * from t1i where a1 in (select b1 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -102,7 +102,7 @@ 1 PRIMARY t1i ref it1i1,it1i3 # 9 # 1 100.00 # 2 SUBQUERY t2i range it2i1,it2i3 # 9 # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `materialized subselect`.`b1`) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`) select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -114,7 +114,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00 2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`materialized subselect`.`b2` = `test`.`t1i`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1i`.`a1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`SUBQUERY#2`.`b2` = `test`.`t1i`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1i`.`a1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); a1 a2 1 - 01 2 - 01 @@ -126,7 +126,7 @@ 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 2 SUBQUERY t2i range it2i1,it2i3 # # # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `SUBQUERY#2`.`b2`) and (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); a1 a2 1 - 01 2 - 01 @@ -138,7 +138,7 @@ 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 2 SUBQUERY t2i range it2i1,it2i3 # # # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `SUBQUERY#2`.`min(b2)`) and (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -150,7 +150,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`materialized subselect`.`max(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`SUBQUERY#2`.`max(b2)` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); a1 a2 1 - 01 2 - 01 @@ -182,7 +182,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`materialized subselect`.`min(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`SUBQUERY#2`.`min(b2)` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -223,7 +223,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`b2` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`SUBQUERY#2`.`b2` = `test`.`t1`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1`.`a1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -235,7 +235,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00 2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`materialized subselect`.`b2` = `test`.`t1i`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1i`.`a1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`SUBQUERY#2`.`b2` = `test`.`t1i`.`a2`) and (`SUBQUERY#2`.`b1` = `test`.`t1i`.`a1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); a1 a2 1 - 01 2 - 01 @@ -286,14 +286,14 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00 -1 PRIMARY SUBQUERY#3 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00 +1 PRIMARY SUBQUERY#3 eq_ref distinct_key distinct_key 18 SUBQUERY#2.b1,SUBQUERY#2.b2 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 3 SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00 4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#4`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#4`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where ((`SUBQUERY#3`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#3`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and (a1, a2) in (select c1, c2 from t3 @@ -315,7 +315,7 @@ 4 SUBQUERY t2i index it2i2 # # # 5 100.00 # 2 SUBQUERY t2i index it2i1,it2i3 # # # 5 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`materialized subselect`.`b2` = `test`.`t3i`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`SUBQUERY#4`.`b2` = `test`.`t3i`.`c2`) and (`SUBQUERY#4`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`SUBQUERY#3`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1i`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#3`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1i`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and (a1, a2) in (select c1, c2 from t3i @@ -332,7 +332,7 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00 -1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00 +1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 SUBQUERY#2.b1,SUBQUERY#2.b2 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 5 SUBQUERY SUBQUERY#6 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00 @@ -341,7 +341,7 @@ 4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>( `test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))))) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#6`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#6`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#3`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table > on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`))))))) join `test`.`t1` where ((`SUBQUERY#5`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#5`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1`.`a1` = `SUBQUERY#2`.`b1`)) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -368,7 +368,7 @@ 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` `t3c` where ((`materialized subselect`.`b2` = `test`.`t3c`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3c`.`c1`))) join `test`.`t1` where ((`materialized subselect`.`c2` = `test`.`t1`.`a2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` fr om `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` `t3c` where ((`SUBQUERY#6`.`b2` = `test`.`t3c`.`c2`) and (`SUBQUERY#6`.`b1` = `test`.`t3c`.`c1`))) join `test`.`t1` where ((`SUBQUERY#5`.`c2` = `test`.`t1`.`a2`) and (`SUBQUERY#5`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` li ke '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 t3a where c1 = a1) or @@ -410,7 +410,7 @@ 8 SUBQUERY t2i index it2i1,it2i3 # # # 5 100.00 # NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # Warnings: -Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup> (`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`materialized subselect`.`b2` = `test`.`t3i`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i ` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`))) +Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#6`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#6`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#3`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary tabl e> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`SUBQUERY#5`.`c2` = `SUBQUERY#2`.`b2`) and (`test`.`t1`.`a2` = `SUBQUERY#2`.`b2`) and (`SUBQUERY#5`.`c1` = `SUBQUERY#2`.`b1`) and (`test`.`t1`.`a1` = `SUBQUERY#2`.`b1`))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`SUBQUERY#10`.`b2` = `test`.`t3i`.`c2`) and (`SUBQUERY#10`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`SUBQUERY#9`.`c2` = `SUBQUERY#8`.`b2`) and (`test`.`t1i`.`a2` = `SUBQUERY#8`.`b2`) and (`SUBQUERY#9`.`c1` = `SUBQUERY#8`.`b1`) and ( `test`.`t1i`.`a1` = `SUBQUERY#8`.`b1`))) (select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -441,7 +441,7 @@ 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` where ((`materialized subselect`.`c2` = `test`.`t1`.`a2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`t est`.`t1`.`a2`) = `test`.`t2`.`b2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#5`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#5`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` where ((`SUBQUERY#4`.`c2` = `test`.`t1`.`a2`) and (`SUBQUERY#4`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) select * from t1 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (a1, a2) in (select c1, c2 from t3 @@ -466,7 +466,7 @@ 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c2` = `materialized subselect`.`c2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from ` test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`SUBQUERY#5`.`b2` = `test`.`t3`.`c2`) and (`SUBQUERY#5`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c2` = `SUBQUERY#4`.`c2`) and (`SUBQUERY#4`.`c1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and ( <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) select * from t1, t3 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (c1, c2) in (select c1, c2 from t3 @@ -512,7 +512,7 @@ Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (<i n_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `SUBQUERY#4`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (<in_optimizer> ((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`)) and (<cache>(`test`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and (<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`))))) explain extended select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -648,7 +648,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 20 test.t1_16.a1 1 100.00 Using where 2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0')) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `materialized subselect`.`substring(b1,1,16)`) +Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0')) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `SUBQUERY#2`.`substring(b1,1,16)`) select left(a1,7), left(a2,7) from t1_16 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); @@ -678,7 +678,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_16.a1 1 100.00 Using where 2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_16 where a1 in (select group_concat(b1) from t2_16 group by b2); @@ -701,7 +701,7 @@ 3 DEPENDENT SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 9 test.t2.b1 1 100.00 Using where 4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 AS `Not_used` from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0')) join `test`.`t2_16` join `test`.`t2` where ((`materialized subselect`.`c1` = `test`.`t2`.`b1`) and (`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 AS `Not_used` from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0')) join `test`.`t2_16` join `test`.`t2` where ((`SUBQUERY#4`.`c1` = `test`.`t2`.`b1`) and (`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8))))) drop table t1_16, t2_16, t3_16; set @blob_len = 512; set @suffix_len = @blob_len - @prefix_len; @@ -764,7 +764,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 517 test.t1_512.a1 1 100.00 Using where 2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0')) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`substring(b1,1,512)`) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0')) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `SUBQUERY#2`.`substring(b1,1,512)`) select left(a1,7), left(a2,7) from t1_512 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); @@ -779,7 +779,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where 2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_512 where a1 in (select group_concat(b1) from t2_512 group by b2); @@ -793,7 +793,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where 2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_512 where a1 in (select group_concat(b1) from t2_512 group by b2); @@ -874,7 +874,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where 2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_1024 where a1 in (select group_concat(b1) from t2_1024 group by b2); @@ -888,7 +888,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where 2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_1024 where a1 in (select group_concat(b1) from t2_1024 group by b2); @@ -969,7 +969,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where 2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_1025 where a1 in (select group_concat(b1) from t2_1025 group by b2); @@ -983,7 +983,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where 2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`) +Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `SUBQUERY#2`.`group_concat(b1)`) select left(a1,7), left(a2,7) from t1_1025 where a1 in (select group_concat(b1) from t2_1025 group by b2); @@ -1006,7 +1006,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 10 test.t1bit.a1,test.t1bit.a2 1 100.00 Using where 2 SUBQUERY t2bit ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit`) join `test`.`t1bit` where ((`test`.`t1bit`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1bit`.`a2` = `materialized subselect`.`b2`)) +Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit`) join `test`.`t1bit` where ((`test`.`t1bit`.`a1` = `SUBQUERY#2`.`b1`) and (`test`.`t1bit`.`a2` = `SUBQUERY#2`.`b2`)) select bin(a1), bin(a2) from t1bit where (a1, a2) in (select b1, b2 from t2bit); @@ -1078,7 +1078,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`) +Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`SUBQUERY#2`.`c` = `test`.`t1`.`a`) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1093,7 +1093,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`) +Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`SUBQUERY#2`.`c` = `test`.`t1`.`a`) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1108,7 +1108,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`) +Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`SUBQUERY#2`.`c` = `test`.`t1`.`a`) select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1121,7 +1121,7 @@ 1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `SUBQUERY#2`.`c`))))) select a from t1 group by a having a in (select c from t2 where d >= 20); a 2 @@ -1133,7 +1133,7 @@ 1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `SUBQUERY#2`.`c`))))) select a from t1 group by a having a in (select c from t2 where d >= 20); a 2 @@ -1253,7 +1253,7 @@ SET @@optimizer_switch='default,semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 2 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 2 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); @@ -1275,7 +1275,7 @@ EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); pk === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-04-05 20:16:45 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-04-06 21:29:09 +0000 @@ -1303,7 +1303,7 @@ 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer 2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`test`.`t2`.`a` = `materialized subselect`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`test`.`t2`.`a` = `SUBQUERY#2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1314,7 +1314,7 @@ 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`test`.`t2`.`a` = `materialized subselect`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`test`.`t2`.`a` = `SUBQUERY#2`.`a`) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1326,7 +1326,7 @@ 2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) drop table t1, t2, t3; create table t1 (a int, b int, index a (a,b)); create table t2 (a int, index a (a)); @@ -1349,7 +1349,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a 2 @@ -1360,7 +1360,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); a 2 @@ -1372,7 +1372,7 @@ 2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index 2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) insert into t1 values (3,31); select * from t2 where t2.a in (select a from t1 where t1.b <> 30); a @@ -1389,7 +1389,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00 2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`) +Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`SUBQUERY#2`.`a` = `test`.`t2`.`a`) drop table t0, t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); @@ -2844,7 +2844,7 @@ 1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 10 test.t1.one,test.t1.two 1 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N')) join `test`.`t1` where ((`materialized subselect`.`two` = `test`.`t1`.`two`) and (`materialized subselect`.`one` = `test`.`t1`.`one`)) +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N')) join `test`.`t1` where ((`SUBQUERY#2`.`two` = `test`.`t1`.`two`) and (`SUBQUERY#2`.`one` = `test`.`t1`.`one`)) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 @@ -4343,14 +4343,14 @@ 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 1) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`SUBQUERY#2`.`1` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-04-06 21:29:09 +0000 @@ -848,7 +848,7 @@ EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 13 func 1 1.00 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) @@ -1017,7 +1017,7 @@ WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 8 func 1 1.00 2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) === modified file 'mysql-test/r/subselect_sj2.result' --- a/mysql-test/r/subselect_sj2.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj2.result 2010-04-06 21:29:09 +0000 @@ -32,7 +32,7 @@ 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -74,7 +74,7 @@ from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -101,7 +101,7 @@ explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -129,7 +129,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -163,7 +163,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -197,7 +197,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -231,7 +231,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -348,7 +348,7 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -692,7 +692,7 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) === modified file 'mysql-test/r/subselect_sj2_jcl6.result' --- a/mysql-test/r/subselect_sj2_jcl6.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj2_jcl6.result 2010-04-06 21:29:09 +0000 @@ -36,7 +36,7 @@ 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -78,7 +78,7 @@ from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -105,7 +105,7 @@ explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -133,7 +133,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -167,7 +167,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -201,7 +201,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -235,7 +235,7 @@ from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -352,7 +352,7 @@ SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -698,7 +698,7 @@ explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-04-06 21:29:09 +0000 @@ -852,7 +852,7 @@ EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 13 func 1 1.00 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0)) @@ -1021,7 +1021,7 @@ WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 -1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 8 func 1 1.00 2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`)) === modified file 'mysql-test/suite/pbxt/r/group_min_max.result' --- a/mysql-test/suite/pbxt/r/group_min_max.result 2010-04-06 08:10:58 +0000 +++ b/mysql-test/suite/pbxt/r/group_min_max.result 2010-04-06 21:29:09 +0000 @@ -2257,7 +2257,7 @@ a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 15 -1 PRIMARY t1_outer ref a a 5 materialized subselect.max(b) 1 Using index +1 PRIMARY t1_outer ref a a 5 SUBQUERY#2.max(b) 1 Using index 2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); === modified file 'mysql-test/suite/pbxt/r/subselect.result' --- a/mysql-test/suite/pbxt/r/subselect.result 2010-04-06 08:10:58 +0000 +++ b/mysql-test/suite/pbxt/r/subselect.result 2010-04-06 21:29:09 +0000 @@ -3433,7 +3433,7 @@ SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 9 -1 PRIMARY t1 ref a a 8 materialized subselect.a 1 Using where +1 PRIMARY t1 ref a a 8 SUBQUERY#2.a 1 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort DROP TABLE t1; create table t1( f1 int,f2 int); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-04-05 20:16:45 +0000 +++ b/sql/item_subselect.cc 2010-04-06 21:29:09 +0000 @@ -2116,7 +2116,8 @@ if (!(new_engine= new subselect_hash_sj_engine(thd, this, old_engine)) || - new_engine->init_permanent(unit->get_unit_column_types())) + new_engine->init_permanent(unit->get_unit_column_types(), + old_engine->get_identifier())) { Item_subselect::trans_res trans_res; /* @@ -3662,6 +3663,7 @@ reexecution. @param tmp_columns the items that produce the data for the temp table + @param subquery_id subquery's identifier (for temptable name) @details - Create a temporary table to store the result of the IN subquery. The @@ -3677,7 +3679,8 @@ @retval FALSE otherwise */ -bool subselect_hash_sj_engine::init_permanent(List<Item> *tmp_columns) +bool subselect_hash_sj_engine::init_permanent(List<Item> *tmp_columns, + uint subquery_id) { /* Options to create_tmp_table. */ ulonglong tmp_create_options= thd->options | TMP_TABLE_ALL_COLUMNS; @@ -3712,12 +3715,19 @@ DBUG_RETURN(TRUE); } */ + char buf[32]; + uint len= my_snprintf(buf, sizeof(buf), "SUBQUERY#%d", subquery_id); + char *name; + if (!(name= (char*)thd->alloc(len + 1))) + DBUG_RETURN(TRUE); + memcpy(name, buf, len+1); + if (!(result= new select_materialize_with_stats)) DBUG_RETURN(TRUE); if (((select_union*) result)->create_result_table( thd, tmp_columns, TRUE, tmp_create_options, - "materialized subselect", TRUE)) + name, TRUE)) DBUG_RETURN(TRUE); tmp_table= ((select_union*) result)->table; @@ -3798,7 +3808,7 @@ if (!(tmp_table_ref= (TABLE_LIST*) thd->alloc(sizeof(TABLE_LIST)))) DBUG_RETURN(TRUE); - tmp_table_ref->init_one_table("", "materialized subselect", TL_READ); + tmp_table_ref->init_one_table("", tmp_table->alias, TL_READ); tmp_table_ref->table= tmp_table; context= new Name_resolution_context; === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-04-05 20:16:45 +0000 +++ b/sql/item_subselect.h 2010-04-06 21:29:09 +0000 @@ -814,7 +814,7 @@ } ~subselect_hash_sj_engine(); - bool init_permanent(List<Item> *tmp_columns); + bool init_permanent(List<Item> *tmp_columns, uint subquery_id); bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-04-05 20:16:45 +0000 +++ b/sql/sql_select.cc 2010-04-06 21:29:09 +0000 @@ -18004,7 +18004,7 @@ /* table */ int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, - "subselect%d", + "SUBQUERY#%d", tab->emb_sj_nest->sj_subq_pred->get_identifier()); item_list.push_back(new Item_string(table_name_buffer, len, cs)); /* partitions */

1 0

[Maria-developers] bzr commit into file:///home/tsk/mprog/src/5.3-subqueries/ branch (timour:2790)
by timour＠askmonty.org 06 Apr '10

06 Apr '10

#At file:///home/tsk/mprog/src/5.3-subqueries/ based on revid:timour@askmonty.org-20100405211515-istsgehaz7zafg0l 2790 timour(a)askmonty.org 2010-04-06 Fixed an error in the creation of REF access method for materialized semi-join, where the the REF buffer format was mistaken to be in record format instead of key format. The error was that the null byte for all fields of the record was in the front of the buffer, and not before each field data. modified: sql/opt_subselect.cc === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-03-15 19:52:58 +0000 +++ b/sql/opt_subselect.cc 2010-04-06 19:56:20 +0000 @@ -2296,7 +2296,7 @@ bool setup_sj_materialization(JOIN_TAB * use that information instead. */ cur_ref_buff + null_count, - null_count ? tab_ref->key_buff : 0, + null_count ? cur_ref_buff : 0, cur_key_part->length, tab_ref->items[i]); cur_ref_buff+= cur_key_part->store_length; }

1 0

[Maria-developers] Progress (by Knielsen): Merge OQGraph into MariaDB (112)
by worklog-noreply＠askmonty.org 06 Apr '10

06 Apr '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Merge OQGraph into MariaDB CREATION DATE..: Mon, 29 Mar 2010, 18:00 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: CATEGORY.......: Server-Sprint TASK ID........: 112 (http://askmonty.org/worklog/?tid=112) VERSION........: Server-5.2 STATUS.........: Code-Review PRIORITY.......: 60 WORKED HOURS...: 13 ESTIMATE.......: 2 (hours remain) ORIG. ESTIMATE.: 15 PROGRESS NOTES: -=-=(Knielsen - Tue, 06 Apr 2010, 15:28)=-=- Fixed all issues from first code review. Implement packaging for OQGraph in bakery. Set up buildbot hosts for including OQGraph, including binary packaging. Worked 13 hours and estimate 2 hours remain (original estimate unchanged). -=-=(Knielsen - Wed, 31 Mar 2010, 13:38)=-=- Status updated. --- /tmp/wklog.112.old.12166 2010-03-31 13:38:25.000000000 +0000 +++ /tmp/wklog.112.new.12166 2010-03-31 13:38:25.000000000 +0000 @@ -1 +1 @@ -Assigned +Code-Review -=-=(Knielsen - Wed, 31 Mar 2010, 13:38)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.12070 2010-03-31 13:38:08.000000000 +0000 +++ /tmp/wklog.112.new.12070 2010-03-31 13:38:08.000000000 +0000 @@ -15,3 +15,5 @@ Fix OQGraph plug.in to detect boost version >= 1.40.0, and only enable OQGraph if such boost is found. +Update the packaging in ourdelta/bakery to include the oqgraph_engine.so and +link with g++ rather than gcc. -=-=(Knielsen - Mon, 29 Mar 2010, 21:46)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.31142 2010-03-29 21:46:11.000000000 +0000 +++ /tmp/wklog.112.new.31142 2010-03-29 21:46:11.000000000 +0000 @@ -1,28 +1,17 @@ Tasks: -Find the latest version of OQGraph to base this on (there should be a -Launchpad branch somewhere, match it up with what is in the OQGraph patch for -MySQL 5.0 in the ourdelta stuff). - -Extract the correct version of Boost from the MySQL 5.0 ourdelta patch. This -is a patched version of Boost fixing a bug that is supposedly fatal for -OQGraph (details are not known at the time of writing). +Base work on the Launchpad branch lp:~knielsen/maria/mariadb-5.1-oqgraph -Document in OQGraph README the need for boost of a specific version, and point -to where it can be obtained. Also include the patch for boost if the correct -base version of boost to do this against can be determined. +OQGraph requires Boost >= 1.40.0 (earlier versions have a bug that affects +OQGraph). -Install the patched boost in /usr/local/ on the build machines (release builds -and selected Buildbot slaves). +Document in OQGraph README the need for boost of a specific version, and point +to where it can be obtained. -Fix OQGraph plug.in to detect correct version of OQGraph that makes the build -not break. Check which version in Ubuntu starts working (I think it was -Jaunty), and require at least that version. - -Setup some repository or source tarball of the patched boost -somewhere. Preferably a Launchpad branch or similar (if upstream project can -be found). +Install the patched boost in /usr/local/include/boost on the build machines +(release builds and selected Buildbot slaves). G++ seems to by default look in +/usr/local/include, so that is sufficient to find it. -Setup in plug.in or /configure.in appropriate --with-boost=xxx. Or in a pinch, -we can make do with CFLAGS=-Ixxx, or even default look in /usr/local/. +Fix OQGraph plug.in to detect boost version >= 1.40.0, and only enable OQGraph +if such boost is found. -=-=(Knielsen - Mon, 29 Mar 2010, 18:09)=-=- High-Level Specification modified. --- /tmp/wklog.112.old.23061 2010-03-29 18:09:27.000000000 +0000 +++ /tmp/wklog.112.new.23061 2010-03-29 18:09:27.000000000 +0000 @@ -1 +1,28 @@ +Tasks: + +Find the latest version of OQGraph to base this on (there should be a +Launchpad branch somewhere, match it up with what is in the OQGraph patch for +MySQL 5.0 in the ourdelta stuff). + +Extract the correct version of Boost from the MySQL 5.0 ourdelta patch. This +is a patched version of Boost fixing a bug that is supposedly fatal for +OQGraph (details are not known at the time of writing). + +Document in OQGraph README the need for boost of a specific version, and point +to where it can be obtained. Also include the patch for boost if the correct +base version of boost to do this against can be determined. + +Install the patched boost in /usr/local/ on the build machines (release builds +and selected Buildbot slaves). + +Fix OQGraph plug.in to detect correct version of OQGraph that makes the build +not break. Check which version in Ubuntu starts working (I think it was +Jaunty), and require at least that version. + +Setup some repository or source tarball of the patched boost +somewhere. Preferably a Launchpad branch or similar (if upstream project can +be found). + +Setup in plug.in or /configure.in appropriate --with-boost=xxx. Or in a pinch, +we can make do with CFLAGS=-Ixxx, or even default look in /usr/local/. DESCRIPTION: Get the OQGraph storage engine merged into MariaDB, fixing the remaining problems blocking the merge. HIGH-LEVEL SPECIFICATION: Tasks: Base work on the Launchpad branch lp:~knielsen/maria/mariadb-5.1-oqgraph OQGraph requires Boost >= 1.40.0 (earlier versions have a bug that affects OQGraph). Document in OQGraph README the need for boost of a specific version, and point to where it can be obtained. Install the patched boost in /usr/local/include/boost on the build machines (release builds and selected Buildbot slaves). G++ seems to by default look in /usr/local/include, so that is sufficient to find it. Fix OQGraph plug.in to detect boost version >= 1.40.0, and only enable OQGraph if such boost is found. Update the packaging in ourdelta/bakery to include the oqgraph_engine.so and link with g++ rather than gcc. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 06 Apr '10

06 Apr '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 47 ESTIMATE.......: 8 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress -=-=(Knielsen - Mon, 29 Mar 2010, 10:59)=-=- Status updated. --- /tmp/wklog.47.old.27790 2010-03-29 10:59:53.000000000 +0000 +++ /tmp/wklog.47.new.27790 2010-03-29 10:59:53.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Alexi - Thu, 18 Feb 2010, 19:29)=-=- Worked 20 hours (alexi) Worked 20 hours and estimate 15 hours remain (original estimate unchanged). -=-=(Serg - Fri, 05 Feb 2010, 14:04)=-=- Observers changed: Knielsen,Serg -=-=(Guest - Fri, 05 Feb 2010, 13:40)=-=- Category updated. --- /tmp/wklog.47.old.9197 2010-02-05 13:40:36.000000000 +0200 +++ /tmp/wklog.47.new.9197 2010-02-05 13:40:36.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Guest - Fri, 05 Feb 2010, 13:40)=-=- Status updated. --- /tmp/wklog.47.old.9197 2010-02-05 13:40:36.000000000 +0200 +++ /tmp/wklog.47.new.9197 2010-02-05 13:40:36.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +In-Progress -=-=(Alexi - Thu, 04 Feb 2010, 09:54)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16174 2010-02-04 09:54:13.000000000 +0200 +++ /tmp/wklog.47.new.16174 2010-02-04 09:54:13.000000000 +0200 @@ -171,35 +171,20 @@ 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -When requesting an event, the slave should inform the master whether -it should send Annotate_rows events or not. To that end we add a new -BINLOG_SEND_ANNOTATE_ROWS_EVENT flag used when requesting an event: +If the replicate-annotate-rows-events option is not set on a slave, there +is no need for master to send Annotate_rows events to this slave. The slave +(or mysqlbinlog in remote case), before requesting binlog dump via the +COM_BINLOG_DUMP command, informs the master whether it should send these +events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server +command: + + case COM_BINLOG_DUMP_OPTIONS_EXT: + thd->binlog_dump_flags_ext= packet[0]; + my_ok(thd); + break; - #define BINLOG_DUMP_NON_BLOCK 1 - #define BINLOG_SEND_ANNOTATE_ROWS_EVENT 2 - - pthread_handler_t handle_slave_io(void *arg) - { ... - request_dump(mysql, ...); - ... - } - - int request_dump(MYSQL* mysql, ...) - { ... - if (opt_log_slave_updates && - mi->io_thd->variables.binlog_annotate_rows_events) - binlog_flags|= BINLOG_SEND_ANNOTATE_ROWS_EVENT; - ... - int2store(buf + 4, binlog_flags); - ... - simple_command(mysql, COM_BINLOG_DUMP, buf, ...); - ... - } - -NOTE. mysqlbinlog, when remotely requesting BINLOG_DUMP by calling the -simple_command() function, should also use this flag if it wants (in case -of the --print-annotate-rows-events option set) to recieve Annotate_rows -events. +Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible +conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -338,10 +323,4 @@ to internals(a)lists.mysql.com about this and suggesting to reserve the event number. -Also we should notice the introduction of the BINLOG_SEND_ANNOTATE_ROWS_EVENT -flag taking into account that MySQL/Sun may also introduce a flag with the -same value to be used in the request_dump-mysql_binlog_send interface. -But this is mainly the question of merging: if a conflict concerning this -flag occur, we may simply change the BINLOG_SEND_ANNOTATE_ROWS_EVENT value -(this does not require additional changes in the code). -=-=(Alexi - Sun, 20 Dec 2009, 16:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.19667 2009-12-20 14:00:56.000000000 +0000 +++ /tmp/wklog.47.new.19667 2009-12-20 14:00:56.000000000 +0000 @@ -196,6 +196,11 @@ ... } +NOTE. mysqlbinlog, when remotely requesting BINLOG_DUMP by calling the +simple_command() function, should also use this flag if it wants (in case +of the --print-annotate-rows-events option set) to recieve Annotate_rows +events. + 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -212,8 +217,7 @@ Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || - flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT || - thd->server_id == 0 /* slave == mysqlbinlog */ ) + flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } -=-=(Alexi - Sun, 20 Dec 2009, 13:14)=-=- Low Level Design modified. --- /tmp/wklog.47.old.11350 2009-12-20 13:14:04.000000000 +0200 +++ /tmp/wklog.47.new.11350 2009-12-20 13:14:04.000000000 +0200 @@ -282,23 +282,18 @@ Annotate_rows_log_event* m_annotate_event; }; -When the saved Annotate_rows object may be deleted? When all corresponding -Rows events will be processed, i.e. before processing the first non-Rows -event (note that Annotate_rows object resides in the binary log *after* -the (possible) 'BEGIN' Query event which accompanies the rows events; note -also that this deletion is adjusted with the case when some or all -corresponding Rows events are filtered out by replicate filter rules): +The saved Annotate_rows object should be deleted when all corresponding +Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... - if (rli->get_annotate_event() && !IS_RBR_EVENT_TYPE(ev->get_type_code())) - rli->free_annotate_event(); - apply_event_and_update_pos(ev, ...); - if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) + if (rli->get_annotate_event() && is_last_rows_event(ev)) + rli->free_annotate_event(); + else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; @@ -307,10 +302,21 @@ where - #define IS_RBR_EVENT_TYPE(type) ( (type) == TABLE_MAP_EVENT || \ - (type) == WRITE_ROWS_EVENT || \ + bool is_last_rows_event(Log_event* ev) + { + Log_event_type type= ev->get_type_code(); + if (IS_ROWS_EVENT_TYPE(type)) + { + Rows_log_event* rows= (Rows_log_event*)ev; + return rows->get_flags(Rows_log_event::STMT_END_F); + } + + return 0; + } + + #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ - (type) == DELETE_ROWS_EVENT ) + (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ ------------------------------------------------------------ -=-=(View All Progress Notes, 27 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 06 Apr '10

06 Apr '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 47 ESTIMATE.......: 8 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress -=-=(Knielsen - Mon, 29 Mar 2010, 10:59)=-=- Status updated. --- /tmp/wklog.47.old.27790 2010-03-29 10:59:53.000000000 +0000 +++ /tmp/wklog.47.new.27790 2010-03-29 10:59:53.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Alexi - Thu, 18 Feb 2010, 19:29)=-=- Worked 20 hours (alexi) Worked 20 hours and estimate 15 hours remain (original estimate unchanged). -=-=(Serg - Fri, 05 Feb 2010, 14:04)=-=- Observers changed: Knielsen,Serg -=-=(Guest - Fri, 05 Feb 2010, 13:40)=-=- Category updated. --- /tmp/wklog.47.old.9197 2010-02-05 13:40:36.000000000 +0200 +++ /tmp/wklog.47.new.9197 2010-02-05 13:40:36.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Guest - Fri, 05 Feb 2010, 13:40)=-=- Status updated. --- /tmp/wklog.47.old.9197 2010-02-05 13:40:36.000000000 +0200 +++ /tmp/wklog.47.new.9197 2010-02-05 13:40:36.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +In-Progress -=-=(Alexi - Thu, 04 Feb 2010, 09:54)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16174 2010-02-04 09:54:13.000000000 +0200 +++ /tmp/wklog.47.new.16174 2010-02-04 09:54:13.000000000 +0200 @@ -171,35 +171,20 @@ 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -When requesting an event, the slave should inform the master whether -it should send Annotate_rows events or not. To that end we add a new -BINLOG_SEND_ANNOTATE_ROWS_EVENT flag used when requesting an event: +If the replicate-annotate-rows-events option is not set on a slave, there +is no need for master to send Annotate_rows events to this slave. The slave +(or mysqlbinlog in remote case), before requesting binlog dump via the +COM_BINLOG_DUMP command, informs the master whether it should send these +events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server +command: + + case COM_BINLOG_DUMP_OPTIONS_EXT: + thd->binlog_dump_flags_ext= packet[0]; + my_ok(thd); + break; - #define BINLOG_DUMP_NON_BLOCK 1 - #define BINLOG_SEND_ANNOTATE_ROWS_EVENT 2 - - pthread_handler_t handle_slave_io(void *arg) - { ... - request_dump(mysql, ...); - ... - } - - int request_dump(MYSQL* mysql, ...) - { ... - if (opt_log_slave_updates && - mi->io_thd->variables.binlog_annotate_rows_events) - binlog_flags|= BINLOG_SEND_ANNOTATE_ROWS_EVENT; - ... - int2store(buf + 4, binlog_flags); - ... - simple_command(mysql, COM_BINLOG_DUMP, buf, ...); - ... - } - -NOTE. mysqlbinlog, when remotely requesting BINLOG_DUMP by calling the -simple_command() function, should also use this flag if it wants (in case -of the --print-annotate-rows-events option set) to recieve Annotate_rows -events. +Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible +conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -338,10 +323,4 @@ to internals(a)lists.mysql.com about this and suggesting to reserve the event number. -Also we should notice the introduction of the BINLOG_SEND_ANNOTATE_ROWS_EVENT -flag taking into account that MySQL/Sun may also introduce a flag with the -same value to be used in the request_dump-mysql_binlog_send interface. -But this is mainly the question of merging: if a conflict concerning this -flag occur, we may simply change the BINLOG_SEND_ANNOTATE_ROWS_EVENT value -(this does not require additional changes in the code). -=-=(Alexi - Sun, 20 Dec 2009, 16:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.19667 2009-12-20 14:00:56.000000000 +0000 +++ /tmp/wklog.47.new.19667 2009-12-20 14:00:56.000000000 +0000 @@ -196,6 +196,11 @@ ... } +NOTE. mysqlbinlog, when remotely requesting BINLOG_DUMP by calling the +simple_command() function, should also use this flag if it wants (in case +of the --print-annotate-rows-events option set) to recieve Annotate_rows +events. + 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -212,8 +217,7 @@ Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || - flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT || - thd->server_id == 0 /* slave == mysqlbinlog */ ) + flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } -=-=(Alexi - Sun, 20 Dec 2009, 13:14)=-=- Low Level Design modified. --- /tmp/wklog.47.old.11350 2009-12-20 13:14:04.000000000 +0200 +++ /tmp/wklog.47.new.11350 2009-12-20 13:14:04.000000000 +0200 @@ -282,23 +282,18 @@ Annotate_rows_log_event* m_annotate_event; }; -When the saved Annotate_rows object may be deleted? When all corresponding -Rows events will be processed, i.e. before processing the first non-Rows -event (note that Annotate_rows object resides in the binary log *after* -the (possible) 'BEGIN' Query event which accompanies the rows events; note -also that this deletion is adjusted with the case when some or all -corresponding Rows events are filtered out by replicate filter rules): +The saved Annotate_rows object should be deleted when all corresponding +Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... - if (rli->get_annotate_event() && !IS_RBR_EVENT_TYPE(ev->get_type_code())) - rli->free_annotate_event(); - apply_event_and_update_pos(ev, ...); - if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) + if (rli->get_annotate_event() && is_last_rows_event(ev)) + rli->free_annotate_event(); + else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; @@ -307,10 +302,21 @@ where - #define IS_RBR_EVENT_TYPE(type) ( (type) == TABLE_MAP_EVENT || \ - (type) == WRITE_ROWS_EVENT || \ + bool is_last_rows_event(Log_event* ev) + { + Log_event_type type= ev->get_type_code(); + if (IS_ROWS_EVENT_TYPE(type)) + { + Rows_log_event* rows= (Rows_log_event*)ev; + return rows->get_flags(Rows_log_event::STMT_END_F); + } + + return 0; + } + + #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ - (type) == DELETE_ROWS_EVENT ) + (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ ------------------------------------------------------------ -=-=(View All Progress Notes, 27 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): Store in binlog text of statements that caused RBR events (47)
by worklog-noreply＠askmonty.org 06 Apr '10

06 Apr '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Store in binlog text of statements that caused RBR events CREATION DATE..: Sat, 15 Aug 2009, 23:48 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: Knielsen, Serg CATEGORY.......: Server-Sprint TASK ID........: 47 (http://askmonty.org/worklog/?tid=47) VERSION........: Server-9.x STATUS.........: In-Progress PRIORITY.......: 60 WORKED HOURS...: 47 ESTIMATE.......: 8 (hours remain) ORIG. ESTIMATE.: 35 PROGRESS NOTES: -=-=(Knielsen - Tue, 06 Apr 2010, 15:26)=-=- Code review (mailed to maria-developers@). Worked 7 hours and estimate 8 hours remain (original estimate unchanged). -=-=(Knielsen - Tue, 06 Apr 2010, 15:25)=-=- Status updated. --- /tmp/wklog.47.old.12734 2010-04-06 15:25:54.000000000 +0000 +++ /tmp/wklog.47.new.12734 2010-04-06 15:25:54.000000000 +0000 @@ -1 +1 @@ -Code-Review +In-Progress -=-=(Knielsen - Mon, 29 Mar 2010, 10:59)=-=- Status updated. --- /tmp/wklog.47.old.27790 2010-03-29 10:59:53.000000000 +0000 +++ /tmp/wklog.47.new.27790 2010-03-29 10:59:53.000000000 +0000 @@ -1 +1 @@ -In-Progress +Code-Review -=-=(Alexi - Thu, 18 Feb 2010, 19:29)=-=- Worked 20 hours (alexi) Worked 20 hours and estimate 15 hours remain (original estimate unchanged). -=-=(Serg - Fri, 05 Feb 2010, 14:04)=-=- Observers changed: Knielsen,Serg -=-=(Guest - Fri, 05 Feb 2010, 13:40)=-=- Category updated. --- /tmp/wklog.47.old.9197 2010-02-05 13:40:36.000000000 +0200 +++ /tmp/wklog.47.new.9197 2010-02-05 13:40:36.000000000 +0200 @@ -1 +1 @@ -Server-RawIdeaBin +Server-Sprint -=-=(Guest - Fri, 05 Feb 2010, 13:40)=-=- Status updated. --- /tmp/wklog.47.old.9197 2010-02-05 13:40:36.000000000 +0200 +++ /tmp/wklog.47.new.9197 2010-02-05 13:40:36.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +In-Progress -=-=(Alexi - Thu, 04 Feb 2010, 09:54)=-=- Low Level Design modified. --- /tmp/wklog.47.old.16174 2010-02-04 09:54:13.000000000 +0200 +++ /tmp/wklog.47.new.16174 2010-02-04 09:54:13.000000000 +0200 @@ -171,35 +171,20 @@ 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -When requesting an event, the slave should inform the master whether -it should send Annotate_rows events or not. To that end we add a new -BINLOG_SEND_ANNOTATE_ROWS_EVENT flag used when requesting an event: +If the replicate-annotate-rows-events option is not set on a slave, there +is no need for master to send Annotate_rows events to this slave. The slave +(or mysqlbinlog in remote case), before requesting binlog dump via the +COM_BINLOG_DUMP command, informs the master whether it should send these +events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server +command: + + case COM_BINLOG_DUMP_OPTIONS_EXT: + thd->binlog_dump_flags_ext= packet[0]; + my_ok(thd); + break; - #define BINLOG_DUMP_NON_BLOCK 1 - #define BINLOG_SEND_ANNOTATE_ROWS_EVENT 2 - - pthread_handler_t handle_slave_io(void *arg) - { ... - request_dump(mysql, ...); - ... - } - - int request_dump(MYSQL* mysql, ...) - { ... - if (opt_log_slave_updates && - mi->io_thd->variables.binlog_annotate_rows_events) - binlog_flags|= BINLOG_SEND_ANNOTATE_ROWS_EVENT; - ... - int2store(buf + 4, binlog_flags); - ... - simple_command(mysql, COM_BINLOG_DUMP, buf, ...); - ... - } - -NOTE. mysqlbinlog, when remotely requesting BINLOG_DUMP by calling the -simple_command() function, should also use this flag if it wants (in case -of the --print-annotate-rows-events option set) to recieve Annotate_rows -events. +Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible +conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -338,10 +323,4 @@ to internals(a)lists.mysql.com about this and suggesting to reserve the event number. -Also we should notice the introduction of the BINLOG_SEND_ANNOTATE_ROWS_EVENT -flag taking into account that MySQL/Sun may also introduce a flag with the -same value to be used in the request_dump-mysql_binlog_send interface. -But this is mainly the question of merging: if a conflict concerning this -flag occur, we may simply change the BINLOG_SEND_ANNOTATE_ROWS_EVENT value -(this does not require additional changes in the code). -=-=(Alexi - Sun, 20 Dec 2009, 16:00)=-=- Low Level Design modified. --- /tmp/wklog.47.old.19667 2009-12-20 14:00:56.000000000 +0000 +++ /tmp/wklog.47.new.19667 2009-12-20 14:00:56.000000000 +0000 @@ -196,6 +196,11 @@ ... } +NOTE. mysqlbinlog, when remotely requesting BINLOG_DUMP by calling the +simple_command() function, should also use this flag if it wants (in case +of the --print-annotate-rows-events option set) to recieve Annotate_rows +events. + 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -212,8 +217,7 @@ Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || - flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT || - thd->server_id == 0 /* slave == mysqlbinlog */ ) + flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } -=-=(Alexi - Sun, 20 Dec 2009, 13:14)=-=- Low Level Design modified. --- /tmp/wklog.47.old.11350 2009-12-20 13:14:04.000000000 +0200 +++ /tmp/wklog.47.new.11350 2009-12-20 13:14:04.000000000 +0200 @@ -282,23 +282,18 @@ Annotate_rows_log_event* m_annotate_event; }; -When the saved Annotate_rows object may be deleted? When all corresponding -Rows events will be processed, i.e. before processing the first non-Rows -event (note that Annotate_rows object resides in the binary log *after* -the (possible) 'BEGIN' Query event which accompanies the rows events; note -also that this deletion is adjusted with the case when some or all -corresponding Rows events are filtered out by replicate filter rules): +The saved Annotate_rows object should be deleted when all corresponding +Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... - if (rli->get_annotate_event() && !IS_RBR_EVENT_TYPE(ev->get_type_code())) - rli->free_annotate_event(); - apply_event_and_update_pos(ev, ...); - if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) + if (rli->get_annotate_event() && is_last_rows_event(ev)) + rli->free_annotate_event(); + else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; @@ -307,10 +302,21 @@ where - #define IS_RBR_EVENT_TYPE(type) ( (type) == TABLE_MAP_EVENT || \ - (type) == WRITE_ROWS_EVENT || \ + bool is_last_rows_event(Log_event* ev) + { + Log_event_type type= ev->get_type_code(); + if (IS_ROWS_EVENT_TYPE(type)) + { + Rows_log_event* rows= (Rows_log_event*)ev; + return rows->get_flags(Rows_log_event::STMT_END_F); + } + + return 0; + } + + #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ - (type) == DELETE_ROWS_EVENT ) + (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ ------------------------------------------------------------ -=-=(View All Progress Notes, 27 total)=-=- http://askmonty.org/worklog/index.pl?tid=47&nolimit=1 DESCRIPTION: Store in binlog (and show in mysqlbinlog output) texts of statements that caused RBR events This is needed for (list from Monty): - Easier to understand why updates happened - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement. The cost of this can be that the binlog will be approximately 2x in size (especially insert of big blob's would be a bit painful), so this should be an optional feature. HIGH-LEVEL SPECIFICATION: Content ~~~~~~~ 1. Annotate_rows_log_event 2. Server option: --binlog-annotate-rows-events 3. Server option: --replicate-annotate-rows-events 4. mysqlbinlog option: --print-annotate-rows-events 5. mysqlbinlog output 1. Annotate_rows_log_event [ ANNOTATE_ROWS_EVENT ] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Describes the query which caused the corresponding rows events. Has empty post-header and contains the query text in its data part. Example: ************************ ANNOTATE_ROWS_EVENT ************************ 00000220 | B6 A0 2C 4B | time_when = 1261215926 00000224 | 33 | event_type = 51 00000225 | 64 00 00 00 | server_id = 100 00000229 | 36 00 00 00 | event_len = 54 0000022D | 56 02 00 00 | log_pos = 00000256 00000231 | 00 00 | flags = <none> ------------------------ 00000233 | 49 4E 53 45 | query = "INSERT INTO t1 VALUES (1), (2), (3)" 00000237 | 52 54 20 49 | 0000023B | 4E 54 4F 20 | 0000023F | 74 31 20 56 | 00000243 | 41 4C 55 45 | 00000247 | 53 20 28 31 | 0000024B | 29 2C 20 28 | 0000024F | 32 29 2C 20 | 00000253 | 28 33 29 | ************************ In binary log, Annotate_rows event follows the (possible) 'BEGIN' Query event and precedes the first of Table map events which accompany the corresponding rows events. (See example in the "mysqlbinlog output" section below.) 2. Server option: --binlog-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the master to write Annotate_rows events to the binary log. * Variable Name: binlog_annotate_rows_events * Scope: Global & Session * Access Type: Dynamic * Data Type: bool * Default Value: OFF NOTE. Session values allows to annotate only some selected statements: ... SET SESSION binlog_annotate_rows_events=ON; ... statements to be annotated ... SET SESSION binlog_annotate_rows_events=OFF; ... statements not to be annotated ... 3. Server option: --replicate-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Tells the slave to reproduce Annotate_rows events recieved from the master in its own binary log (sensible only in pair with log-slave-updates option). * Variable Name: replicate_annotate_rows_events * Scope: Global * Access Type: Read only * Data Type: bool * Default Value: OFF NOTE. Why do we additionally need this 'replicate' option? Why not to make the slave to reproduce this events when its binlog-annotate-rows-events global value is ON? Well, because, for example, we may want to configure the slave which should reproduce Annotate_rows events but has global binlog-annotate-rows-events = OFF meaning this to be the default value for the client threads (see also "How slave treats replicate-annotate-rows-events option" in LLD part). 4. mysqlbinlog option: --print-annotate-rows-events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With this option, mysqlbinlog prints the content of Annotate_rows events (if the binary log does contain them). Without this option (i.e. by default), mysqlbinlog skips Annotate_rows events. 5. mysqlbinlog output ~~~~~~~~~~~~~~~~~~~~~ With --print-annotate-rows-events, mysqlbinlog outputs Annotate_rows events in a form like this: ... # at 1646 #091219 12:45:26 server id 100 end_log_pos 1714 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1261215926/*!*/; BEGIN /*!*/; # at 1714 # at 1812 # at 1853 # at 1894 # at 1938 #091219 12:45:26 server id 100 end_log_pos 1812 Query: `DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a` #091219 12:45:26 server id 100 end_log_pos 1853 Table_map: `test`.`t1` mapped to number 16 #091219 12:45:26 server id 100 end_log_pos 1894 Table_map: `test`.`t2` mapped to number 17 #091219 12:45:26 server id 100 end_log_pos 1938 Delete_rows: table id 16 #091219 12:45:26 server id 100 end_log_pos 1982 Delete_rows: table id 17 flags: STMT_END_F ... LOW-LEVEL DESIGN: Content ~~~~~~~ 1. Annotate_rows event number 2. Outline of Annotate_rows event behavior 3. How Master writes Annotate_rows events to the binary log 4. How slave treats replicate-annotate-rows-events option 5. How slave IO thread requests Annotate_rows events 6. How master executes the request 7. How slave SQL thread processes Annotate_rows events 8. General remarks 1. Annotate_rows event number ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To avoid possible event numbers conflict with MySQL/Sun, we leave a gap between the last MySQL event number and the Annotate_rows event number: enum Log_event_type { ... INCIDENT_EVENT= 26, // New MySQL event numbers are to be added here MYSQL_EVENTS_END, MARIA_EVENTS_BEGIN= 51, // New Maria event numbers start from here ANNOTATE_ROWS_EVENT= 51, ENUM_END_EVENT }; together with the corresponding extension of 'post_header_len' array in the Format description event. (This extension does not affect the compatibility of the binary log). Here is how Format description event looks like with this extension: ************************ FORMAT_DESCRIPTION_EVENT ************************ 00000004 | A1 A0 2C 4B | time_when = 1261215905 00000008 | 0F | event_type = 15 00000009 | 64 00 00 00 | server_id = 100 0000000D | 7F 00 00 00 | event_len = 127 00000011 | 83 00 00 00 | log_pos = 00000083 00000015 | 01 00 | flags = LOG_EVENT_BINLOG_IN_USE_F ------------------------ 00000017 | 04 00 | binlog_ver = 4 00000019 | 35 2E 32 2E | server_ver = 5.2.0-MariaDB-alpha-debug-log ..... ... 0000004B | A1 A0 2C 4B | time_created = 1261215905 0000004F | 13 | common_header_len = 19 ------------------------ post_header_len ------------------------ 00000050 | 38 | 56 - START_EVENT_V3 [1] ..... ... 00000069 | 02 | 2 - INCIDENT_EVENT [26] 0000006A | 00 | 0 - RESERVED [27] ..... ... 00000081 | 00 | 0 - RESERVED [50] 00000082 | 00 | 0 - ANNOTATE_ROWS_EVENT [51] ************************ 2. Outline of Annotate_rows event behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Each Annotate_rows_log_event object has two private members describing the corresponding query: char *m_query_txt; uint m_query_len; When the object is created for writing to a binary log, this query is taken from 'thd' (for short, below we omit the 'Annotate_rows_log_event::' prefix as well as other implementation details): Annotate_rows_log_event(THD *thd) { m_query_txt = thd->query(); m_query_len = thd->query_length(); } When the object is read from a binary log, the query is taken from the buffer containing the binary log representation of the event (this buffer is allocated in Log_event object from which all Log events are derived): Annotate_rows_log_event(char *buf, uint event_len, Format_description_log_event *desc) { m_query_len = event_len - desc->common_header_len; m_query_txt = buf + desc->common_header_len; } The events are written to the binary log by the Log_event::write() member which calls virtual write_data_header() and write_data_body() members ("data header" and "post header" are synonym in replication terminology). In our case, data header is empty and data body is just the query: bool write_data_body(IO_CACHE *file) { return my_b_safe_write(file, (uchar*) m_query_txt, m_query_len); } Printing the event is just printing the query: void Annotate_rows_log_event::print(FILE *file, PRINT_EVENT_INFO *pinfo) { my_b_printf(&pinfo->head_cache, "\tQuery: `%s`\n", m_query_txt); } 3. How Master writes Annotate_rows events to the binary log ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The event is written to the binary log just before the group of Table_map events which precede corresponding Rows events (one query may generate several Table map events in the binary log, but the corresponding Annotate_rows event must be written only once before the first Table map event; hence the boolean variable 'with_annotate' below): int write_locked_table_maps(THD *thd) { ... bool with_annotate= thd->variables.binlog_annotate_rows_events; ... for (uint i= 0; i < ... <number of tables> ...; ++i) { ... thd->binlog_write_table_map(table, ..., with_annotate); with_annotate= 0; // write Annotate_event not more than once ... } ... } int THD::binlog_write_table_map(TABLE *table, ..., bool with_annotate) { ... Table_map_log_event the_event(...); ... if (with_annotate) { Annotate_rows_log_event anno(this); mysql_bin_log.write(&anno); } mysql_bin_log.write(&the_event); ... } 4. How slave treats replicate-annotate-rows-events option ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The replicate-annotate-rows-events option is treated just as the session value of the binlog_annotate_rows_events variable for the slave IO and SQL threads. This setting is done during initialization of these threads: pthread_handler_t handle_slave_io(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_IO); ... } pthread_handler_t handle_slave_sql(void *arg) { THD *thd= new THD; ... init_slave_thread(thd, SLAVE_THD_SQL); ... } int init_slave_thread(THD* thd, SLAVE_THD_TYPE thd_type) { ... thd->variables.binlog_annotate_rows_events= opt_replicate_annotate_rows_events; ... } 5. How slave IO thread requests Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the replicate-annotate-rows-events option is not set on a slave, there is no need for master to send Annotate_rows events to this slave. The slave (or mysqlbinlog in remote case), before requesting binlog dump via the COM_BINLOG_DUMP command, informs the master whether it should send these events by executing the newly added COM_BINLOG_DUMP_OPTIONS_EXT server command: case COM_BINLOG_DUMP_OPTIONS_EXT: thd->binlog_dump_flags_ext= packet[0]; my_ok(thd); break; Note. We add this new command and don't use COM_BINLOG_DUMP to avoid possible conflicts with MySQL/Sun. 6. How master executes the request ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ case COM_BINLOG_DUMP: { ... flags= uint2korr(packet + 4); ... mysql_binlog_send(thd, ..., flags); ... } void mysql_binlog_send(THD* thd, ..., ushort flags) { ... Log_event::read_log_event(&log, packet, ...); ... if ((*packet)[EVENT_TYPE_OFFSET + 1] != ANNOTATE_ROWS_EVENT || flags & BINLOG_SEND_ANNOTATE_ROWS_EVENT) { my_net_write(net, packet->ptr(), packet->length()); } ... } 7. How slave SQL thread processes Annotate_rows events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The slave processes each recieved event by "applying" it, i.e. by calling the Log_event::apply_event() function which in turn calls the virtual do_apply_event() member specific for each type of the event. int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev = next_event(rli); ... apply_event_and_update_pos(ev, ...); if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } int apply_event_and_update_pos(Log_event *ev, ...) { ... ev->apply_event(...); ... } int Log_event::apply_event(...) { return do_apply_event(...); } What does it mean to "apply" an Annotate_rows event? It means to set current thd query to that of the described by the event, i.e. to the query which caused the subsequent Rows events (see "How Master writes Annotate_rows events to the binary log" to follow what happens further when the subsequent Rows events are applied): int Annotate_rows_log_event::do_apply_event(...) { thd->set_query(m_query_txt, m_query_len); } NOTE. I am not sure, but possibly current values of thd->query and thd->query_length should be saved before calling set_query() and to be restored on the Annotate_rows_log_event object deletion. Is it really needed ? After calling this do_apply_event() function we may not delete the Annotate_rows_log_event object immediatedly (see exec_relay_log_event() above) because thd->query now points to the string inside this object. We may keep the pointer to this object in the Relay_log_info: class Relay_log_info { public: ... void set_annotate_event(Annotate_rows_log_event*); Annotate_rows_log_event* get_annotate_event(); void free_annotate_event(); ... private: Annotate_rows_log_event* m_annotate_event; }; The saved Annotate_rows object should be deleted when all corresponding Rows events will be processed: int exec_relay_log_event(THD* thd, Relay_log_info* rli) { ... Log_event *ev= next_event(rli); ... apply_event_and_update_pos(ev, ...); if (rli->get_annotate_event() && is_last_rows_event(ev)) rli->free_annotate_event(); else if (ev->get_type_code() == ANNOTATE_ROWS_EVENT) rli->set_annotate_event((Annotate_rows_log_event*) ev); else if (ev->get_type_code() != FORMAT_DESCRIPTION_EVENT) delete ev; ... } where bool is_last_rows_event(Log_event* ev) { Log_event_type type= ev->get_type_code(); if (IS_ROWS_EVENT_TYPE(type)) { Rows_log_event* rows= (Rows_log_event*)ev; return rows->get_flags(Rows_log_event::STMT_END_F); } return 0; } #define IS_ROWS_EVENT_TYPE(type) ((type) == WRITE_ROWS_EVENT || \ (type) == UPDATE_ROWS_EVENT || \ (type) == DELETE_ROWS_EVENT) 8. General remarks ~~~~~~~~~~~~~~~~~~ Kristian noticed that introducing new log event type should be coordinated somehow with MySQL/Sun: Kristian: The numeric code for this event must be assigned carefully. It should be coordinated with MySQL/Sun, otherwise we can get into a situation where MySQL uses the same numeric code for one event that MariaDB uses for ANNOTATE_ROWS_EVENT, which would make merging the two impossible. Alex: I reserved about 20 numbers not to have possible conflicts with MySQL. Kristian: Still, I think it would be appropriate to send a polite email to internals(a)lists.mysql.com about this and suggesting to reserve the event number. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0