- developers - lists.mariadb.org

[Maria-developers] Rev 2776: post-review changes, and merge of the MWL#43 CREATE TABLE options in http://bazaar.launchpad.net/~maria-captains/maria/5.2/
by serg＠askmonty.org 08 Apr '10

08 Apr '10

At http://bazaar.launchpad.net/~maria-captains/maria/5.2/ ------------------------------------------------------------ revno: 2776 [merge] revision-id: sergii(a)pisem.net-20100408085536-qjt3rz3su3oumthu parent: sergii(a)pisem.net-20100406110456-zhkf13tvdcywb9tb parent: sanja(a)askmonty.org-20100406000609-jl8415st523eq5jj committer: Sergei Golubchik <sergii(a)pisem.net> branch nick: 5.2 timestamp: Thu 2010-04-08 10:55:36 +0200 message: post-review changes, and merge of the MWL#43 CREATE TABLE options added: mysql-test/r/create_options.result create_options.resul-20100406150813-x9l18ft2v5asbu6u-1 mysql-test/t/create_options.test create_options.test-20100405221601-8v82yzjsbg8d5t9h-1 sql/create_options.cc create_options.cc-20100405221547-pgejz63vqubo45gk-1 sql/create_options.h create_options.h-20100405221550-zuwt433ekdv7y9u3-1 modified: .bzrignore sp1f-ignore-20001018235455-q4gxfbritt5f42nwix354ufpsvrf5ebj Docs/sp-imp-spec.txt sp1f-spimpspec.txt-20030202164941-cm47byoggfqjr73hhqr24vos7luqh36q include/my_base.h sp1f-my_base.h-19700101030959-3yhq5cta6tatwfxpqmoukzvevlehtxoz include/my_sys.h sp1f-my_sys.h-19700101030959-lyllvna5vzqfcjnmlcrutgqocylhtb54 libmysqld/CMakeLists.txt sp1f-cmakelists.txt-20060403082523-x3vxka3k56u2wpzwcrlpykznlz2akpxd libmysqld/Makefile.am sp1f-makefile.am-20010411110351-26htpk3ynkyh7pkfvnshztqrxx3few4g mysql-test/r/events_bugs.result sp1f-events_bugs.result-20060220225211-jzbz6uk6vlho4vlbzbfymaub54fll3ou mysql-test/r/information_schema.result sp1f-information_schema.r-20041113105544-waoxa2fjjsicturpothmjmi6jc3yrovn mysql-test/r/plugin.result sp1f-plugin.result-20061214230953-dljmjo3wuacc3eox3gwroufqmk3hlne7 mysql-test/r/sp.result sp1f-sp.result-20030117133802-duandg3yzagzyv7zhhbbt2kcomcegpc7 mysql-test/r/system_mysql_db.result sp1f-system_mysql_db.resu-20040310185404-f7br5g4442iqwxireltudlyu5ppbkijo mysql-test/suite/funcs_1/r/is_columns_mysql.result sp1f-is_columns_mysql.res-20080307163304-a2ymkif2vzliwqqqzr22cglbaf75tthe mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result is_columns_mysql_emb-20080616171935-sl04j972o55obadv-7 mysql-test/t/events_bugs.test sp1f-events_bugs.test-20060220225212-ji5acdijogityb45kk4msvs7h5gn4ld3 mysql-test/t/plugin.test sp1f-plugin.test-20061214230953-rdqkovjzpupoeypjzzvefseahkmrdz4f mysql-test/t/sp.test sp1f-sp.test-20030117133803-b6pcfv2yscbqkur5fszep7acmdg7nf5k scripts/mysql_system_tables.sql sp1f-mysql_system_tables.-20070226104923-4n5a67fuifobcyhhicfbacpsv5npohgv scripts/mysql_system_tables_fix.sql sp1f-mysql_fix_privilege_-20030604152848-cz6lnrig5srcrvkt7d5m35bk3wsz4bdc sql/CMakeLists.txt sp1f-cmakelists.txt-20060831175237-esoeu5kpdtwjvehkghwy6fzbleniq2wy sql/Makefile.am sp1f-makefile.am-19700101030959-xsjdiakci3nqcdd4xl4yomwdl5eo2f3q sql/event_db_repository.cc sp1f-event_db_repository.-20060627064838-k6rpjg72omnihtxhbubu6ht7wjvxggb7 sql/field.cc sp1f-field.cc-19700101030959-f4imaofclsea3n4fj4ow5m7havmyxa2r sql/field.h sp1f-field.h-19700101030959-3n6smzxcwkjl7bikm3wg4hfkjn66uvvp sql/ha_partition.cc sp1f-ha_partition.cc-20050718113037-eoky4qluumb5dmdyg5z6n2fvdkgutxms sql/handler.cc sp1f-handler.cc-19700101030959-ta6zfrlbxzucylciyro3musjsdpocrdh sql/handler.h sp1f-handler.h-19700101030959-mumq2hpilkpgxuf22ftyv5kbilysnzvn sql/log_event.h sp1f-log_event.h-19700101030959-clq6ett55tcqbpys2i4cpfrdccq7j4om sql/mysql_priv.h sp1f-mysql_priv.h-19700101030959-4fl65tqpop5zfgxaxkqotu2fa2ree5ci sql/mysqld.cc sp1f-mysqld.cc-19700101030959-zpswdvekpvixxzxf7gdtofzel7nywtfj sql/share/errmsg.txt sp1f-errmsg.txt-20041213212820-do5w642w224ja7ctyqhyl6iihdmpkzv5 sql/sp.cc sp1f-sp.cc-20021212121421-6xwuvxq5bku2b4yv655kp2e5gsvautd5 sql/sp_head.cc sp1f-sp_head.cc-20021208185920-jtgc5wvyqdnu2gvcdus3gazrfhxbofxd sql/sql_class.cc sp1f-sql_class.cc-19700101030959-rpotnweaff2pikkozh3butrf7mv3oero sql/sql_class.h sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s sql/sql_insert.cc sp1f-sql_insert.cc-19700101030959-xgwqe5svnimxudzdcuitauljzz2zjk5g sql/sql_lex.h sp1f-sql_lex.h-19700101030959-sgldb2sooc7twtw5q7pgjx7qzqiaa3sn sql/sql_parse.cc sp1f-sql_parse.cc-19700101030959-ehcre3rwhv5l3mlxqhaxg36ujenxnrcd sql/sql_plugin.cc sp1f-sql_plugin.cc-20051105112032-hrm64p6xfjq33ud6zy3uivpo7azm75a2 sql/sql_show.cc sp1f-sql_show.cc-19700101030959-umlljfnpplg452h7reeyqr4xnbmlkvfj sql/sql_table.cc sp1f-sql_table.cc-19700101030959-tzdkvgigezpuaxnldqh3fx2h7h2ggslu sql/sql_view.cc sp1f-sql_view.cc-20040715221517-nw4p4mja6nzzlvwwhzfgfqb4umxqobe4 sql/sql_yacc.yy sp1f-sql_yacc.yy-19700101030959-wvn4qyy2drpmge7kaq3dysprbhlrv27j sql/structs.h sp1f-structs.h-19700101030959-dqulhwijezc2pwv2x4g32qdggnybj2nc sql/table.cc sp1f-table.cc-19700101030959-nsxtem2adyqzwe6nz4cgrpcmts3o54v7 sql/table.h sp1f-table.h-19700101030959-dv72bajftxj5fbdjuajquappanuv2ija sql/unireg.cc sp1f-unireg.cc-19700101030959-6a4wymwak6cmvk25gch56ctjvadrhu3v storage/example/ha_example.cc sp1f-ha_example.cc-20040331231732-d55r4dr2e7cf5dutte3f74z6h6yxdywb storage/example/ha_example.h sp1f-ha_example.h-20040331231733-ssl4zmhqjrevhtqixivtivdmlwiau2x5 storage/pbxt/src/discover_xt.cc discover_xt.cc-20090326121724-x683v32twzr3fi0y-26 Diff too large for email (3198 lines, the limit is 1000).

1 0

[Maria-developers] Rev 33: Added: in file:///Users/hakan/work/monty_program/mariadb-tools/
by Hakan Kuecuekyilmaz 08 Apr '10

08 Apr '10

At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 33 revision-id: hakan(a)askmonty.org-20100408002114-36ml405ax1h61x60 parent: hakan(a)askmonty.org-20100407121715-2k24p66xyfkgpdpn committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Thu 2010-04-08 02:21:14 +0200 message: Added: * IO utilization with iostat * CPU utilization with mpstat * Simple queries/second monitoring per test with mysqladmin --sleep 10 status * Tests can have options now ** select_random_points has option for number of points ** select_random_ragnes has option for number of ranges and delta of ranges. === modified file 'sysbench/run-sysbench-myisam.sh' --- a/sysbench/run-sysbench-myisam.sh 2010-04-07 12:17:15 +0000 +++ b/sysbench/run-sysbench-myisam.sh 2010-04-08 00:21:14 +0000 @@ -106,16 +106,26 @@ # We need at least 1 GB disk space in our $WORK_DIR. SPACE_LIMIT=1000000 -SYSBENCH_TESTS="delete.lua \ - insert.lua \ - oltp_complex_ro.lua \ - oltp_complex_rw.lua \ - oltp_simple.lua \ - select.lua \ - select_random_points.lua \ - select_random_ranges.lua \ - update_index.lua \ - update_non_index.lua" +# Interval in seconds for monitoring system status like disk IO, +# CPU utilization, and such. +MONITOR_INTERVAL=10 + +SYSBENCH_TESTS[0]="delete.lua" +SYSBENCH_TESTS[1]="insert.lua" +SYSBENCH_TESTS[2]="oltp_complex_ro.lua" +SYSBENCH_TESTS[3]="oltp_complex_rw.lua" +SYSBENCH_TESTS[4]="oltp_simple.lua" +SYSBENCH_TESTS[5]="select.lua" + +# Default option is --random-points=10. +SYSBENCH_TESTS[6]="select_random_points.lua" + +# Default options are +# --number-of-ranges=10 +# --random-ranges-delta=5. +SYSBENCH_TESTS[7]="select_random_ranges.lua" +SYSBENCH_TESTS[8]="update_index.lua" +SYSBENCH_TESTS[9]="update_non_index.lua" # # Note: myisam-max-rows has to match or exceed oltp-table-size @@ -277,7 +287,7 @@ } # -# Write out configurations used for future refernce. +# Write out configurations used for future reference. # echo $MYSQLD_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt @@ -285,8 +295,17 @@ echo "Warm up time is: $WARM_UP_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo "Run time is: $RUN_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt -for SYSBENCH_TEST in $SYSBENCH_TESTS +# +# Kill possibly left over monitoring processes. +# +killall -9 iostat +killall -9 mpstat +killall -9 mysqladmin + +for (( i = 0 ; i < ${#SYSBENCH_TESTS[@]} ; i++ )) do + # Get rid of any options of given sysbench test. + SYSBENCH_TEST=$(echo "${SYSBENCH_TESTS[$i]}" | awk '{ print $1 }') mkdir ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST} kill_mysqld @@ -299,8 +318,8 @@ exit 1 fi - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for $SYSBENCH_TEST." - SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TEST}" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for ${SYSBENCH_TESTS[$i]}." + SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TESTS[$i]}" $SYSBENCH $SYSBENCH_OPTIONS --max-time=$RUN_TIME prepare $MYSQLADMIN $MYSQLADMIN_OPTIONS shutdown @@ -308,14 +327,14 @@ rm -rf ${SYSBENCH_DB_BACKUP} mkdir ${SYSBENCH_DB_BACKUP} - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of $SYSBENCH_TEST for later usage." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of ${SYSBENCH_TESTS[$i]} for later usage." cp -a ${DATA_DIR}/* ${SYSBENCH_DB_BACKUP}/ for THREADS in $NUM_THREADS do THIS_RESULT_DIR="${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}" mkdir $THIS_RESULT_DIR - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt echo '' >> ${THIS_RESULT_DIR}/results.txt SYSBENCH_OPTIONS_WARM_UP="${SYSBENCH_OPTIONS} --num-threads=3 --max-time=$WARM_UP_TIME" @@ -325,7 +344,7 @@ while [ $k -lt $LOOP_COUNT ] do echo '' - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for $SYSBENCH_TEST." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for ${SYSBENCH_TESTS[$i]}." kill_mysqld cp -a ${SYSBENCH_DB_BACKUP}/* ${DATA_DIR} @@ -334,7 +353,7 @@ sync echo 3 | $SUDO tee /proc/sys/vm/drop_caches - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" start_mysqld sync @@ -346,8 +365,25 @@ echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished warm up." echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting actual sysbench run." + + $IOSTAT -d -k $IOSTAT_DEVICE $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/iostat${k}.txt 2>&1 & + IOSTAT_PID=$! + + $MPSTAT -u $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/cpustat${k}.txt 2>&1 & + MPSTAT_PID=$! + + $MYSQLADMIN $MYSQLADMIN_OPTIONS --sleep $MONITOR_INTERVAL status > ${THIS_RESULT_DIR}/server_status${k}.txt 2>&1 & + SERVER_STATUS_PID=$! + $SYSBENCH $SYSBENCH_OPTIONS_RUN run > ${THIS_RESULT_DIR}/result${k}.txt 2>&1 + sync; sync; sync + sleep 1 + + kill -9 $SERVER_STATUS_PID + kill -9 $MPSTAT_PID + kill -9 $IOSTAT_PID + grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt echo 'SELECT * FROM INFORMATION_SCHEMA.KEY_CACHES' | $MYSQL $MYSQL_OPTIONS > ${THIS_RESULT_DIR}/key_cache_stats${k}.txt @@ -356,7 +392,7 @@ done echo '' >> ${THIS_RESULT_DIR}/results.txt - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt done done === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-04-07 12:17:15 +0000 +++ b/sysbench/run-sysbench.sh 2010-04-08 00:21:14 +0000 @@ -103,16 +103,26 @@ # We need at least 1 GB disk space in our $WORK_DIR. SPACE_LIMIT=1000000 -SYSBENCH_TESTS="delete.lua \ - insert.lua \ - oltp_complex_ro.lua \ - oltp_complex_rw.lua \ - oltp_simple.lua \ - select.lua \ - select_random_points.lua \ - select_random_ranges.lua \ - update_index.lua \ - update_non_index.lua" +# Interval in seconds for monitoring system status like disk IO, +# CPU utilization, and such. +MONITOR_INTERVAL=10 + +SYSBENCH_TESTS[0]="delete.lua" +SYSBENCH_TESTS[1]="insert.lua" +SYSBENCH_TESTS[2]="oltp_complex_ro.lua" +SYSBENCH_TESTS[3]="oltp_complex_rw.lua" +SYSBENCH_TESTS[4]="oltp_simple.lua" +SYSBENCH_TESTS[5]="select.lua" + +# Default option is --random-points=10. +SYSBENCH_TESTS[6]="select_random_points.lua" + +# Default options are +# --number-of-ranges=10 +# --random-ranges-delta=5. +SYSBENCH_TESTS[7]="select_random_ranges.lua" +SYSBENCH_TESTS[8]="update_index.lua" +SYSBENCH_TESTS[9]="update_non_index.lua" SYSBENCH_OPTIONS="--oltp-table-size=$TABLE_SIZE \ --max-requests=0 \ @@ -269,7 +279,7 @@ } # -# Write out configurations used for future refernce. +# Write out configurations used for future reference. # echo $MYSQLD_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt @@ -277,8 +287,17 @@ echo "Warm up time is: $WARM_UP_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo "Run time is: $RUN_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt -for SYSBENCH_TEST in $SYSBENCH_TESTS +# +# Kill possibly left over monitoring processes. +# +killall -9 iostat +killall -9 mpstat +killall -9 mysqladmin + +for (( i = 0 ; i < ${#SYSBENCH_TESTS[@]} ; i++ )) do + # Get rid of any options of given sysbench test. + SYSBENCH_TEST=$(echo "${SYSBENCH_TESTS[$i]}" | awk '{ print $1 }') mkdir ${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST} kill_mysqld @@ -291,8 +310,8 @@ exit 1 fi - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for $SYSBENCH_TEST." - SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TEST}" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Preparing and loading data for ${SYSBENCH_TESTS[$i]}." + SYSBENCH_OPTIONS="${SYSBENCH_OPTIONS} --test=${TEST_DIR}/${SYSBENCH_TESTS[$i]}" $SYSBENCH $SYSBENCH_OPTIONS --max-time=$RUN_TIME prepare $MYSQLADMIN $MYSQLADMIN_OPTIONS shutdown @@ -300,14 +319,14 @@ rm -rf ${SYSBENCH_DB_BACKUP} mkdir ${SYSBENCH_DB_BACKUP} - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of $SYSBENCH_TEST for later usage." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Copying $DATA_DIR of ${SYSBENCH_TESTS[$i]} for later usage." cp -a ${DATA_DIR}/* ${SYSBENCH_DB_BACKUP}/ for THREADS in $NUM_THREADS do THIS_RESULT_DIR="${RESULT_DIR}/${TODAY}/${PRODUCT}/${SYSBENCH_TEST}/${THREADS}" mkdir $THIS_RESULT_DIR - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee ${THIS_RESULT_DIR}/results.txt echo '' >> ${THIS_RESULT_DIR}/results.txt SYSBENCH_OPTIONS_WARM_UP="${SYSBENCH_OPTIONS} --num-threads=1 --max-time=$WARM_UP_TIME" @@ -317,7 +336,7 @@ while [ $k -lt $LOOP_COUNT ] do echo '' - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for $SYSBENCH_TEST." + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Killing mysqld and copying back $DATA_DIR for ${SYSBENCH_TESTS[$i]}." kill_mysqld cp -a ${SYSBENCH_DB_BACKUP}/* ${DATA_DIR} @@ -326,7 +345,7 @@ sync echo 3 | $SUDO tee /proc/sys/vm/drop_caches - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting mysqld for running ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" start_mysqld sync @@ -337,15 +356,32 @@ echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished warm up." echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting actual sysbench run." + + $IOSTAT -d -k $IOSTAT_DEVICE $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/iostat${k}.txt 2>&1 & + IOSTAT_PID=$! + + $MPSTAT -u $MONITOR_INTERVAL > ${THIS_RESULT_DIR}/cpustat${k}.txt 2>&1 & + MPSTAT_PID=$! + + $MYSQLADMIN $MYSQLADMIN_OPTIONS --sleep $MONITOR_INTERVAL status > ${THIS_RESULT_DIR}/server_status${k}.txt 2>&1 & + SERVER_STATUS_PID=$! + $SYSBENCH $SYSBENCH_OPTIONS_RUN run > ${THIS_RESULT_DIR}/result${k}.txt 2>&1 - + + sync; sync; sync + sleep 1 + + kill -9 $SERVER_STATUS_PID + kill -9 $MPSTAT_PID + kill -9 $IOSTAT_PID + grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt k=$(($k + 1)) done echo '' >> ${THIS_RESULT_DIR}/results.txt - echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished $SYSBENCH_TEST with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt + echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished ${SYSBENCH_TESTS[$i]} with $THREADS threads and $LOOP_COUNT iterations for $PRODUCT" | tee -a ${THIS_RESULT_DIR}/results.txt done done

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2842: Fixed suppression of compiler/test warnings
by noreply＠launchpad.net 07 Apr '10

07 Apr '10

------------------------------------------------------------ revno: 2842 committer: Michael Widenius <monty(a)askmonty.org> branch nick: maria-5.1 timestamp: Wed 2010-04-07 18:26:30 +0300 message: Fixed suppression of compiler/test warnings Fixed some timing issues in test suite modified: mysql-test/mysql-test-run.pl mysql-test/suite/funcs_1/datadict/processlist_priv.inc mysql-test/suite/pbxt/t/status.test 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:2842)
by Michael Widenius 07 Apr '10

07 Apr '10

#At lp:maria based on revid:monty@askmonty.org-20100406224708-i62wvtw3nl14fhv4 2842 Michael Widenius 2010-04-07 Fixed suppression of compiler/test warnings Fixed some timing issues in test suite modified: mysql-test/mysql-test-run.pl mysql-test/suite/funcs_1/datadict/processlist_priv.inc mysql-test/suite/pbxt/t/status.test support-files/compiler_warnings.supp per-file messages: mysql-test/mysql-test-run.pl Added suppression of errors that comes when master is killed before slave has connect (like with rpl_udf.test) mysql-test/suite/funcs_1/datadict/processlist_priv.inc Wait for check thread to end mysql-test/suite/pbxt/t/status.test Added longer sleep to avoid timing issue support-files/compiler_warnings.supp Fixed wrong suppression === modified file 'mysql-test/mysql-test-run.pl' --- a/mysql-test/mysql-test-run.pl 2010-03-10 10:32:14 +0000 +++ b/mysql-test/mysql-test-run.pl 2010-04-07 15:26:30 +0000 @@ -4015,7 +4015,10 @@ sub extract_warning_lines ($) { qr/Slave I\/O: Get master COLLATION_SERVER failed with error:.*/, qr/Slave I\/O: Get master TIME_ZONE failed with error:.*/, qr/Slave I\/O: error reconnecting to master '.*' - retry-time: [1-3] retries/, + qr/Slave I\/0: Master command COM_BINLOG_DUMP failed/, qr/Error reading packet/, + qr/Lost connection to MySQL server at 'reading initial communication packet'/, + qr/Failed on request_dump/, qr/Slave: Can't drop database.* database doesn't exist/, qr/Slave: Operation DROP USER failed for 'create_rout_db'/, qr|Checking table: '\./mtr/test_suppressions'|, === modified file 'mysql-test/suite/funcs_1/datadict/processlist_priv.inc' --- a/mysql-test/suite/funcs_1/datadict/processlist_priv.inc 2009-10-10 09:59:06 +0000 +++ b/mysql-test/suite/funcs_1/datadict/processlist_priv.inc 2010-04-07 15:26:30 +0000 @@ -89,18 +89,38 @@ USE information_schema; --echo 1 Prepare test. --echo connection default (user=root) --echo #################################################################################### -if (`SELECT COUNT(*) <> 1 FROM processlist`) + +# Check that we have only one connection around. +# If there is more, it may be the check() connection that has not yet ended, +# so we wait for it for up to 10 seconds. + +let $counter=100; +while ($counter) { - --echo This test expects one connection to the server. - --echo Expectation: USER HOST DB COMMAND STATE INFO - --echo Expectation: root localhost information_schema Query executing SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID - --echo But we found in the moment: - SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID; - --echo Maybe - --echo - the base configuration (no of parallel auxiliary sessions) of the server has changed - --echo - a parallel test intended for another server accidently connected to our current one - --echo We cannot proceed in this situation. Abort - exit; + dec $counter; + let $res= `SELECT COUNT(*) <> 1 FROM processlist`; + if (!$res) + { + # Success; Abort while loop + let $counter=0; + } + if ($res) + { + --sleep 0.1 + if (!$counter) + { + --echo This test expects one connection to the server. + --echo Expectation: USER HOST DB COMMAND STATE INFO + --echo Expectation: root localhost information_schema Query executing SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID + --echo But we found in the moment: + SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID; + --echo Maybe + --echo - the base configuration (no of parallel auxiliary sessions) of the server has changed + --echo - a parallel test intended for another server accidently connected to our current one + --echo We cannot proceed in this situation. Abort + exit; + } + } } --echo #################################################################################### === modified file 'mysql-test/suite/pbxt/t/status.test' --- a/mysql-test/suite/pbxt/t/status.test 2009-04-02 10:03:14 +0000 +++ b/mysql-test/suite/pbxt/t/status.test 2010-04-07 15:26:30 +0000 @@ -30,7 +30,7 @@ connection con1; --send update t1 set n = 3; connection con2; -sleep 0.5; +sleep 5; unlock tables; connection con1; reap; === modified file 'support-files/compiler_warnings.supp' --- a/support-files/compiler_warnings.supp 2010-04-06 22:47:08 +0000 +++ b/support-files/compiler_warnings.supp 2010-04-07 15:26:30 +0000 @@ -40,7 +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 +buf/buf0buf\.c: .*block_mutex.* might be used uninitialized # # bdb is not critical to keep up to date

1 0

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 06 Apr '10

06 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 06 Apr '10

06 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