- developers - lists.mariadb.org

[Maria-developers] Rev 2779: 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: 2779 revision-id: sanja(a)askmonty.org-20100406000609-swbb13c0biged8iv parent: sergii(a)pisem.net-20100407150509-l5lrdmanv1flfsi7 author: sanja(a)askmonty.org committer: Sergei Golubchik <sergii(a)pisem.net> branch nick: work-maria-5.2-createoptions5 timestamp: Tue 2010-04-06 03:06:09 +0300 message: MWL#43 CREATE TABLE options Diff too large for email (3333 lines, the limit is 1000).

1 0

[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 07 Apr '10

07 Apr '10

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

1 0

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

07 Apr '10

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

1 0

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

06 Apr '10

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

1 0