Gecko [ www.jdcpatala.org ]

Name	Size	Permission
add_foreign_key.result	1.28 KB	-rw-r--r--
alter_crash.result	4.43 KB	-rw-r--r--
alter_foreign_crash.result	751 B	-rw-r--r--
alter_kill.result	2.42 KB	-rw-r--r--
alter_missing_tablespace.resul...	1.46 KB	-rw-r--r--
alter_page_size.result	1.14 KB	-rw-r--r--
alter_rename_existing.result	5.32 KB	-rw-r--r--
alter_rename_files.result	557 B	-rw-r--r--
alter_row_format.result	1.59 KB	-rw-r--r--
alter_table_redundant.result	278 B	-rw-r--r--
alter_table_stage_progress.res...	942 B	-rw-r--r--
analyze_index.result	705 B	-rw-r--r--
analyze_table.result	538 B	-rw-r--r--
attachable_trx.result	2.48 KB	-rw-r--r--
autoinc_debug.result	2.53 KB	-rw-r--r--
blob-crash-16k.result	2.1 KB	-rw-r--r--
blob-crash-4k.result	2.1 KB	-rw-r--r--
blob-crash.result	4.59 KB	-rw-r--r--
blob-update-debug.result	489 B	-rw-r--r--
blob_page_reserve.result	3.81 KB	-rw-r--r--
blob_redo.result	1.05 KB	-rw-r--r--
buf_pool_resize_oom.result	493 B	-rw-r--r--
bulk_create_index_online.resul...	1.21 KB	-rw-r--r--
cascade_lock_wait.result	1.52 KB	-rw-r--r--
check_ibd_filesize_16k.result	1.58 KB	-rw-r--r--
check_sector_size.result	276 B	-rw-r--r--
checksum.result	331 B	-rw-r--r--
cmp_per_index.result	2.96 KB	-rw-r--r--
crc32_endianness.result	540 B	-rw-r--r--
create-index.result	283 B	-rw-r--r--
create_isl_with_direct.result	328 B	-rw-r--r--
create_table.result	1.97 KB	-rw-r--r--
create_tablespace.result	81.15 KB	-rw-r--r--
create_tablespace_16k.result	69.23 KB	-rw-r--r--
create_tablespace_32k.result	7.33 KB	-rw-r--r--
create_tablespace_4k.result	36.13 KB	-rw-r--r--
create_tablespace_64k.result	7.64 KB	-rw-r--r--
create_tablespace_8k.result	50.53 KB	-rw-r--r--
create_tablespace_debug.result	5.02 KB	-rw-r--r--
create_tablespace_notembedded....	692 B	-rw-r--r--
create_tablespace_partition.re...	33.17 KB	-rw-r--r--
create_tablespace_replication....	2.02 KB	-rw-r--r--
deadlock_detect.result	562 B	-rw-r--r--
default_row_format.result	17.56 KB	-rw-r--r--
default_row_format_16k.result	17.91 KB	-rw-r--r--
default_row_format_compatibili...	4.14 KB	-rw-r--r--
default_row_format_tablespace....	8.64 KB	-rw-r--r--
deprecate_part_in_shared_ts.re...	5.87 KB	-rw-r--r--
doublewrite.result	8.1 KB	-rw-r--r--
dropdb.result	222 B	-rw-r--r--
end_range_check.result	3.06 KB	-rw-r--r--
end_range_check_2.result	811 B	-rw-r--r--
events-merge-tmp-path.result	1.33 KB	-rw-r--r--
file_format_defaults.result	2.84 KB	-rw-r--r--
file_format_upgrade_16k.result	420 B	-rw-r--r--
flush-hang.result	1.27 KB	-rw-r--r--
foreign_key.result	9.21 KB	-rw-r--r--
fts_exec_interrupt.result	723 B	-rw-r--r--
help_verbose.result	80 B	-rw-r--r--
high_prio_trx_1.result	505 B	-rw-r--r--
high_prio_trx_2.result	581 B	-rw-r--r--
high_prio_trx_3.result	665 B	-rw-r--r--
high_prio_trx_4.result	711 B	-rw-r--r--
high_prio_trx_5.result	858 B	-rw-r--r--
high_prio_trx_6.result	775 B	-rw-r--r--
high_prio_trx_7.result	5.67 KB	-rw-r--r--
high_prio_trx_commit_crash.res...	1 KB	-rw-r--r--
high_prio_trx_debug.result	1.09 KB	-rw-r--r--
high_prio_trx_fk.result	918 B	-rw-r--r--
high_prio_trx_predicate.result	824 B	-rw-r--r--
high_prio_trx_rpl.result	1.65 KB	-rw-r--r--
i_s_files_debug.result	471 B	-rw-r--r--
ibuf_not_empty.result	969 B	-rw-r--r--
import.result	1.72 KB	-rw-r--r--
import_compress_encrypt.result	5.28 KB	-rw-r--r--
import_compress_encrypt_upgrad...	2.53 KB	-rw-r--r--
import_export_4k.result	724 B	-rw-r--r--
import_tablespace_page_corrupt...	555 B	-rw-r--r--
import_tablespace_schema_missm...	2.11 KB	-rw-r--r--
import_update_stats.result	2.86 KB	-rw-r--r--
index-online-norebuild.result	897 B	-rw-r--r--
index_length.result	1.28 KB	-rw-r--r--
index_merge_threshold.result	54.7 KB	-rw-r--r--
index_tree_operation.result	1.65 KB	-rw-r--r--
innodb-2byte-collation.result	3.28 KB	-rw-r--r--
innodb-ac-non-locking-select.r...	861 B	-rw-r--r--
innodb-alter-autoinc.result	4.27 KB	-rw-r--r--
innodb-alter-debug-wl6554.resu...	12.79 KB	-rw-r--r--
innodb-alter-debug.result	2.64 KB	-rw-r--r--
innodb-alter-nullable.result	1.52 KB	-rw-r--r--
innodb-alter-wl6554.result	3.13 KB	-rw-r--r--
innodb-alter.result	30.33 KB	-rw-r--r--
innodb-analyze.result	58 B	-rw-r--r--
innodb-autoinc-18274.result	805 B	-rw-r--r--
innodb-autoinc-44030.result	732 B	-rw-r--r--
innodb-autoinc-56228.result	847 B	-rw-r--r--
innodb-autoinc-optimize.result	321 B	-rw-r--r--
innodb-autoinc.result	41.48 KB	-rw-r--r--
innodb-blob.result	4.91 KB	-rw-r--r--
innodb-bug-14068765.result	2.81 KB	-rw-r--r--
innodb-bug-14084530.result	701 B	-rw-r--r--
innodb-bug12552164.result	1.73 KB	-rw-r--r--
innodb-bug14219515.result	244 B	-rw-r--r--
innodb-change-buffer-recovery....	1.08 KB	-rw-r--r--
innodb-consistent.result	670 B	-rw-r--r--
innodb-import-partition-rpl.re...	2.46 KB	-rw-r--r--
innodb-import-partition.result	40.01 KB	-rw-r--r--
innodb-index-debug.result	13.64 KB	-rw-r--r--
innodb-index-online-delete.res...	642 B	-rw-r--r--
innodb-index-online-fk.result	26.4 KB	-rw-r--r--
innodb-index-online-purge.resu...	1.2 KB	-rw-r--r--
innodb-index-online.result	13.35 KB	-rw-r--r--
innodb-index.result	67.62 KB	-rw-r--r--
innodb-index_ucs2.result	3.62 KB	-rw-r--r--
innodb-isolation.result	43.25 KB	-rw-r--r--
innodb-large-prefix.result	1.27 KB	-rw-r--r--
innodb-lock-inherit-read_commi...	4.18 KB	-rw-r--r--
innodb-lock.result	3.22 KB	-rw-r--r--
innodb-multiple-tablespaces.re...	14.79 KB	-rw-r--r--
innodb-read-view.result	2.73 KB	-rw-r--r--
innodb-replace-debug.result	309 B	-rw-r--r--
innodb-semi-consistent.result	1.35 KB	-rw-r--r--
innodb-status-output.result	1.3 KB	-rw-r--r--
innodb-system-table-view.resul...	9.07 KB	-rw-r--r--
innodb-table-online.result	13.11 KB	-rw-r--r--
innodb-timeout.result	953 B	-rw-r--r--
innodb-truncate-debug.result	4.46 KB	-rw-r--r--
innodb-truncate.result	2.28 KB	-rw-r--r--
innodb-ucs2.result	15.38 KB	-rw-r--r--
innodb-update-insert.result	1.1 KB	-rw-r--r--
innodb-wl5522-1.result	29.45 KB	-rw-r--r--
innodb-wl5522-debug.result	30.05 KB	-rw-r--r--
innodb-wl5522.result	20.99 KB	-rw-r--r--
innodb-wl5980-alter.result	38.86 KB	-rw-r--r--
innodb-wl5980-debug.result	1.33 KB	-rw-r--r--
innodb-wl5980-discard.result	29.2 KB	-rw-r--r--
innodb-wl5980-linux.result	9.7 KB	-rw-r--r--
innodb-wl5980-windows.result	10.07 KB	-rw-r--r--
innodb-wl6045-1.result	3.72 KB	-rw-r--r--
innodb-wl6045-linux.result	11.11 KB	-rw-r--r--
innodb-wl6445-1.result	69 KB	-rw-r--r--
innodb-wl6445-2.result	3.57 KB	-rw-r--r--
innodb-wl6445.result	1.78 KB	-rw-r--r--
innodb.result	108.76 KB	-rw-r--r--
innodb_32k.result	3.25 KB	-rw-r--r--
innodb_64k.result	3.47 KB	-rw-r--r--
innodb_autoinc_lock_mode_zero....	1.22 KB	-rw-r--r--
innodb_autoinc_reset.result	423 B	-rw-r--r--
innodb_buffer_pool_dump_pct.re...	1.49 KB	-rw-r--r--
innodb_buffer_pool_load.result	2.02 KB	-rw-r--r--
innodb_buffer_pool_load_now.re...	1.6 KB	-rw-r--r--
innodb_buffer_pool_resize.resu...	1.29 KB	-rw-r--r--
innodb_buffer_pool_resize_debu...	3.24 KB	-rw-r--r--
innodb_buffer_pool_resize_with...	1.08 KB	-rw-r--r--
innodb_bug-13628249.result	988 B	-rw-r--r--
innodb_bug11754376.result	180 B	-rw-r--r--
innodb_bug11766634.result	396 B	-rw-r--r--
innodb_bug11789106.result	404 B	-rw-r--r--
innodb_bug11933790.result	135 B	-rw-r--r--
innodb_bug12400341.result	966 B	-rw-r--r--
innodb_bug12429573.result	708 B	-rw-r--r--
innodb_bug12661768.result	524 B	-rw-r--r--
innodb_bug14006907.result	934 B	-rw-r--r--
innodb_bug14007109.result	223 B	-rw-r--r--
innodb_bug14007649.result	1.17 KB	-rw-r--r--
innodb_bug14147491.result	730 B	-rw-r--r--
innodb_bug14169459.result	1.48 KB	-rw-r--r--
innodb_bug14676111.result	1.99 KB	-rw-r--r--
innodb_bug14704286.result	1.12 KB	-rw-r--r--
innodb_bug19164038.result	1023 B	-rw-r--r--
innodb_bug21704.result	2.65 KB	-rw-r--r--
innodb_bug30113362.result	2.95 KB	-rw-r--r--
innodb_bug30423.result	3.79 KB	-rw-r--r--
innodb_bug30594501.result	317 B	-rw-r--r--
innodb_bug30919.result	17.44 KB	-rw-r--r--
innodb_bug31205266.result	798 B	-rw-r--r--
innodb_bug34053.result	35 B	-rw-r--r--
innodb_bug34300.result	439 B	-rw-r--r--
innodb_bug35220.result	35 B	-rw-r--r--
innodb_bug38231.result	35 B	-rw-r--r--
innodb_bug39438.result	35 B	-rw-r--r--
innodb_bug40360.result	274 B	-rw-r--r--
innodb_bug40565.result	311 B	-rw-r--r--
innodb_bug41904.result	195 B	-rw-r--r--
innodb_bug42101-nonzero.result	810 B	-rw-r--r--
innodb_bug42101.result	704 B	-rw-r--r--
innodb_bug42419.result	858 B	-rw-r--r--
innodb_bug44032.result	313 B	-rw-r--r--
innodb_bug44369.result	300 B	-rw-r--r--
innodb_bug44571.result	334 B	-rw-r--r--
innodb_bug45357.result	290 B	-rw-r--r--
innodb_bug46000.result	906 B	-rw-r--r--
innodb_bug46676.result	362 B	-rw-r--r--
innodb_bug47167.result	1.51 KB	-rw-r--r--
innodb_bug47621.result	777 B	-rw-r--r--
innodb_bug47622.result	748 B	-rw-r--r--
innodb_bug47777.result	612 B	-rw-r--r--
innodb_bug48024.result	592 B	-rw-r--r--
innodb_bug49164.result	913 B	-rw-r--r--
innodb_bug51378.result	2.19 KB	-rw-r--r--
innodb_bug51920.result	316 B	-rw-r--r--
innodb_bug52199.result	175 B	-rw-r--r--
innodb_bug52663.result	857 B	-rw-r--r--
innodb_bug53046.result	1016 B	-rw-r--r--
innodb_bug53290.result	771 B	-rw-r--r--
innodb_bug53592.result	1.9 KB	-rw-r--r--
innodb_bug53674.result	234 B	-rw-r--r--
innodb_bug53756.result	3.62 KB	-rw-r--r--
innodb_bug54044.result	678 B	-rw-r--r--
innodb_bug56143.result	73.71 KB	-rw-r--r--
innodb_bug56716.result	168 B	-rw-r--r--
innodb_bug56947.result	406 B	-rw-r--r--
innodb_bug57252.result	89 B	-rw-r--r--
innodb_bug57255.result	501 B	-rw-r--r--
innodb_bug57904.result	1.3 KB	-rw-r--r--
innodb_bug59307.result	483 B	-rw-r--r--
innodb_bug59410.result	529 B	-rw-r--r--
innodb_bug59641.result	927 B	-rw-r--r--
innodb_bug59733.result	822 B	-rw-r--r--
innodb_bug60049.result	258 B	-rw-r--r--
innodb_bug60196.result	2.86 KB	-rw-r--r--
innodb_bug60229.result	955 B	-rw-r--r--
innodb_bug70867.result	126 B	-rw-r--r--
innodb_bulk_create_index.resul...	29.62 KB	-rw-r--r--
innodb_bulk_create_index_debug...	11.38 KB	-rw-r--r--
innodb_bulk_create_index_flush...	1.46 KB	-rw-r--r--
innodb_bulk_create_index_repli...	5.82 KB	-rw-r--r--
innodb_bulk_create_index_small...	4.26 KB	-rw-r--r--
innodb_corrupt_bit.result	2.61 KB	-rw-r--r--
innodb_corrupt_readonly.result	1011 B	-rw-r--r--
innodb_ctype_ldml.result	27.55 KB	-rw-r--r--
innodb_deadlock_with_autoinc.r...	565 B	-rw-r--r--
innodb_file_format.result	3.11 KB	-rw-r--r--
innodb_file_limit_check.result	457 B	-rw-r--r--
innodb_force_recovery.result	3.7 KB	-rw-r--r--
innodb_fts_limit_check.result	1.04 KB	-rw-r--r--
innodb_high_prio_commit_crash....	1.13 KB	-rw-r--r--
innodb_high_prio_rpl.result	1.07 KB	-rw-r--r--
innodb_i_s_innodb_locks.result	4.89 KB	-rw-r--r--
innodb_i_s_innodb_trx.result	2.85 KB	-rw-r--r--
innodb_information_schema_buff...	2.29 KB	-rw-r--r--
innodb_io_pf.result	407 B	-rw-r--r--
innodb_lock_wait_timeout_1.res...	9.04 KB	-rw-r--r--
innodb_misc1.result	29.29 KB	-rw-r--r--
innodb_multi_update.result	1.62 KB	-rw-r--r--
innodb_mysql.result	95.87 KB	-rw-r--r--
innodb_mysql_rbk.result	629 B	-rw-r--r--
innodb_notembedded.result	637 B	-rw-r--r--
innodb_page_size_func.result	1.49 KB	-rw-r--r--
innodb_pagesize_max_recordsize...	14.37 KB	-rw-r--r--
innodb_prefix_index_check.resu...	1.14 KB	-rw-r--r--
innodb_prefix_index_restart_se...	2.69 KB	-rw-r--r--
innodb_rename_index.result	31.12 KB	-rw-r--r--
innodb_rename_index_err.result	1.81 KB	-rw-r--r--
innodb_replace.result	2.7 KB	-rw-r--r--
innodb_row_log_read.result	867 B	-rw-r--r--
innodb_stats.result	11.62 KB	-rw-r--r--
innodb_stats_auto_recalc.resul...	1.33 KB	-rw-r--r--
innodb_stats_auto_recalc_ddl.r...	1.13 KB	-rw-r--r--
innodb_stats_auto_recalc_lots....	2.07 KB	-rw-r--r--
innodb_stats_auto_recalc_on_no...	2.19 KB	-rw-r--r--
innodb_stats_create_on_corrupt...	705 B	-rw-r--r--
innodb_stats_create_table.resu...	1.1 KB	-rw-r--r--
innodb_stats_del_mark.result	2.81 KB	-rw-r--r--
innodb_stats_drop_locked.resul...	1.55 KB	-rw-r--r--
innodb_stats_external_pages.re...	880 B	-rw-r--r--
innodb_stats_fetch.result	3.35 KB	-rw-r--r--
innodb_stats_fetch_corrupted.r...	1.01 KB	-rw-r--r--
innodb_stats_fetch_nonexistent...	980 B	-rw-r--r--
innodb_stats_flag_global_off.r...	7.36 KB	-rw-r--r--
innodb_stats_flag_global_on.re...	7.36 KB	-rw-r--r--
innodb_stats_rename_table.resu...	1.48 KB	-rw-r--r--
innodb_stats_rename_table_if_e...	1.89 KB	-rw-r--r--
innodb_stats_sample_pages.resu...	1023 B	-rw-r--r--
innodb_stats_table_flag_auto_r...	2.97 KB	-rw-r--r--
innodb_stats_table_flag_sample...	4.23 KB	-rw-r--r--
innodb_sys_var_valgrind.result	2.35 KB	-rw-r--r--
innodb_tablespace.result	22.76 KB	-rw-r--r--
innodb_tablespace_zip.result	1.13 KB	-rw-r--r--
innodb_thread_concurrency_debu...	824 B	-rw-r--r--
innodb_timeout_rollback.result	570 B	-rw-r--r--
innodb_trx_weight.result	35 B	-rw-r--r--
innodb_upd_stats_if_needed_not...	466 B	-rw-r--r--
innodb_ut_format_name.result	140 B	-rw-r--r--
innodb_wl6326.result	10.95 KB	-rw-r--r--
innodb_wl6326_big.result	17.67 KB	-rw-r--r--
innodb_wl6469_1.result	1.48 KB	-rw-r--r--
innodb_wl6469_debug.result	1.63 KB	-rw-r--r--
innodb_wl6470.result	3.11 KB	-rw-r--r--
innodb_wl6470_debug.result	1.42 KB	-rw-r--r--
innodb_wl6501_crash_1.result	15.37 KB	-rw-r--r--
innodb_wl6501_crash_2.result	15.37 KB	-rw-r--r--
innodb_wl6501_crash_6.result	13.07 KB	-rw-r--r--
innodb_wl6501_crash_7.result	13.07 KB	-rw-r--r--
innodb_wl6501_crash_8.result	468 B	-rw-r--r--
innodb_wl6560_1.result	7.27 KB	-rw-r--r--
innodb_wl6560_debug.result	3.36 KB	-rw-r--r--
innodb_wl6915.result	3.99 KB	-rw-r--r--
innodb_wl6915_debug.result	2.88 KB	-rw-r--r--
innodb_wl8114.result	1.78 KB	-rw-r--r--
insert_debug.result	956 B	-rw-r--r--
iodku.result	6.34 KB	-rw-r--r--
iodku_debug.result	2.71 KB	-rw-r--r--
log_alter_table.result	521 B	-rw-r--r--
log_corruption.result	843 B	-rw-r--r--
log_file.result	4.84 KB	-rw-r--r--
log_file_name.result	1.55 KB	-rw-r--r--
log_file_name_debug.result	345 B	-rw-r--r--
log_file_size.result	1.09 KB	-rw-r--r--
log_file_size_1.result	4.85 KB	-rw-r--r--
log_file_size_checkpoint.resul...	106 B	-rw-r--r--
missing_tablespaces.result	664 B	-rw-r--r--
monitor.result	23.64 KB	-rw-r--r--
monitor_restart.result	324 B	-rw-r--r--
mysqldump_max_recordsize.resul...	8.78 KB	-rw-r--r--
optimizer_temporary_table.resu...	114.72 KB	-rw-r--r--
page_reorganize.result	638 B	-rw-r--r--
partition-blob.result	1.53 KB	-rw-r--r--
partition-upgrade.result	33.24 KB	-rw-r--r--
partition.result	39.54 KB	-rw-r--r--
readahead.result	398 B	-rw-r--r--
readonly.result	713 B	-rw-r--r--
records_in_range.result	35.31 KB	-rw-r--r--
redo_log_during_checkpoint.res...	827 B	-rw-r--r--
rename_table.result	890 B	-rw-r--r--
row_format_redundant.result	1.71 KB	-rw-r--r--
sp_temp_table.result	5.72 KB	-rw-r--r--
stored_fk.result	3.52 KB	-rw-r--r--
strict_checksum.result	1.02 KB	-rw-r--r--
strict_mode.result	3.54 KB	-rw-r--r--
sys_tablestats.result	571 B	-rw-r--r--
table_compress.result	25.4 KB	-rw-r--r--
table_encrypt_1.result	6.82 KB	-rw-r--r--
table_encrypt_2.result	3.44 KB	-rw-r--r--
table_encrypt_3.result	66.48 KB	-rw-r--r--
table_encrypt_4.result	3.64 KB	-rw-r--r--
table_encrypt_5.result	56.6 KB	-rw-r--r--
table_encrypt_debug.result	3.22 KB	-rw-r--r--
table_encrypt_fts.result	1.7 KB	-rw-r--r--
table_encrypt_kill.result	7.59 KB	-rw-r--r--
table_encryption.result	3.42 KB	-rw-r--r--
tablespace_crash.result	8.16 KB	-rw-r--r--
tablespace_embedded.result	6.89 KB	-rw-r--r--
tablespace_embedded_windows.re...	6.93 KB	-rw-r--r--
tablespace_per_table.result	21.6 KB	-rw-r--r--
tablespace_per_table_not_windo...	9.11 KB	-rw-r--r--
tablespace_per_table_windows.r...	3.12 KB	-rw-r--r--
tablespace_portability.result	24.68 KB	-rw-r--r--
tablespace_portability_windows...	24.41 KB	-rw-r--r--
timestamp.result	790 B	-rw-r--r--
tinytext-groupby.result	1.33 KB	-rw-r--r--
tmpdir.result	1.8 KB	-rw-r--r--
trx_id_future.result	329 B	-rw-r--r--
undo.result	850 B	-rw-r--r--
update-cascade.result	8.04 KB	-rw-r--r--
update_time.result	1.62 KB	-rw-r--r--
update_time_wl6658.result	8.08 KB	-rw-r--r--
virtual_basic.result	53.55 KB	-rw-r--r--
virtual_blob.result	359 B	-rw-r--r--
virtual_debug.result	53.92 KB	-rw-r--r--
virtual_debug_purge.result	3.98 KB	-rw-r--r--
virtual_fk.result	28.34 KB	-rw-r--r--
virtual_fk_restart.result	1.48 KB	-rw-r--r--
virtual_index.result	7.71 KB	-rw-r--r--
virtual_purge.result	3.95 KB	-rw-r--r--
virtual_stats.result	4.84 KB	-rw-r--r--
xa_prepare_lock_release.result	1.21 KB	-rw-r--r--
xa_recovery.result	381 B	-rw-r--r--
xa_recovery_debug.result	528 B	-rw-r--r--

Code Editor : optimizer_temporary_table.result

drop table if exists t1, t2;
Warnings:
Note	1051	Unknown table 'test.t1'
Note	1051	Unknown table 'test.t2'
create  table t1 (a int, b varchar(20));
create  table t2 (a int, b varchar(20));
insert into t1 values(1, 'a'), (2, 'b'), (3, 'c'), (3, 'c'), (4, 'c');
insert into t2 values(2, 'd'), (3, 'e'), (4, 'f'), (4, 'f'), (5, 'e');
drop function if exists func1;
Warnings:
Note	1305	FUNCTION test.func1 does not exist
create function func1(x int) returns int deterministic
begin
declare z1, z2 int;
set z1 = x;
set z2 = z1 + 2;
return z2;
end|
drop table if exists t3;
Warnings:
Note	1051	Unknown table 'test.t3'
create  table t3 like t1;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
insert into t3 select 5 + 10000 * rand(), '5' from t3;
drop table if exists t4;
Warnings:
Note	1051	Unknown table 'test.t4'
create  table t4 (a int, b int, c int, d char, e blob);
insert into t4 values (1, 2, 3, '4', '5');
drop table if exists t5;
Warnings:
Note	1051	Unknown table 'test.t5'
create  table t5 (a int, b int primary key);
insert into t5 values (1,7), (1,8);
drop table if exists t6;
Warnings:
Note	1051	Unknown table 'test.t6'
create  table t6 (a int, b int, c int, d char);
insert into t6 values(1, 2, 3, '4');
create index t6_indx1 on t6(a);
drop table if exists t7;
Warnings:
Note	1051	Unknown table 'test.t7'
create  table t7 (a int, b varchar(20));
insert into t7 values(1, 'a'), (2, 'b'), (3, 'c'), (3, 'c'), (4, 'c');
create index indx7 on t7(b);
drop table if exists t8;
Warnings:
Note	1051	Unknown table 'test.t8'
create  table t8 (
pk integer auto_increment, col_int_key integer,
col_varchar_key varchar(1), primary key (pk),
key (col_int_key), key (col_varchar_key, col_int_key)) engine = innodb;
insert into t8 values (1, 2, 'v'), (2, 150, 'v');
drop table if exists t9, t10;
Warnings:
Note	1051	Unknown table 'test.t9'
Note	1051	Unknown table 'test.t10'
create  table t9 (
pk int(11) not null auto_increment,
col_int_nokey int(11) default null,
col_int_key int(11) default null,
col_date_key date default null,
col_date_nokey date default null,
primary key (pk),
key col_int_key (col_int_key),
key col_date_key (col_date_key),
key col_varchar_key (col_int_key)
) engine=innodb;
Warnings:
Warning	1831	Duplicate index 'col_varchar_key' defined on the table 'test.t9'. This is deprecated and will be disallowed in a future release.
create  table t10 (
pk int(11) not null auto_increment,
col_int_nokey int(11) default null,
col_int_key int(11) default null,
col_date_key date default null,
col_date_nokey date default null,
primary key (pk),
key col_int_key (col_int_key),
key col_date_key (col_date_key),
key col_varchar_key (col_int_key)
) engine=innodb;
Warnings:
Warning	1831	Duplicate index 'col_varchar_key' defined on the table 'test.t10'. This is deprecated and will be disallowed in a future release.
insert into t9 values
(1, 2, 4, '2008-12-05', '2008-12-05'),
(2, 150, 62, '2005-03-27', '2005-03-27'),
(3, NULL, 7, '2004-04-09', '2004-04-09'),
(4, 2, 1, '2006-05-13', '2006-05-13'),
(5, 5, 0, '2001-05-06', '2001-05-06'),
(6, 3, 7, '2006-03-03', '2006-03-03'),
(7, 1, 7, '2007-12-28', '2007-12-28'),
(8, 4, 1, '2004-10-20', '2004-10-20'),
(9, NULL, 7, '2008-04-09', '2008-04-09'),
(10, 2, 1, '2005-12-25', '2005-12-25'),
(11, 6, 5, '1900-01-01', '1900-01-01'),
(12, 6, 2, NULL, NULL),
(13, 8, 0, '1900-01-01', '1900-01-01'),
(14, 2, 1, '2001-01-16', '2001-01-16'),
(15, 6, 8, '1900-01-01', '1900-01-01'),
(16, 8, 1, '2001-11-23', '2001-11-23'),
(17, 3, 1, '2004-11-04', '2004-11-04'),
(18, 3, 9, '2003-03-12', '2003-03-12'),
(19, 9, 1, '2002-06-22', '2002-06-22'),
(20, 6, 5, '2004-10-10', '2004-10-10');
insert into t10 values
(1, 8, 4, '2002-01-03', '2002-01-03'),
(2, 3, 5, '2007-07-08', '2007-07-08'),
(3, 3, 8, '2000-08-02', '2000-08-02'),
(4, NULL, 4, '2000-03-06', '2000-03-06'),
(5, 7, 8, '2002-06-05', '2002-06-05'),
(6, 4, 2, '2009-08-09', '2009-08-09'),
(7, 7, 9, '2001-03-22', '2001-03-22'),
(8, 7, 6, '2002-10-08', '2002-10-08'),
(9, 8, NULL, NULL, NULL),
(10, 6, NULL, '2007-09-13', '2007-09-13'),
(11, 3, 48, '2003-12-08', '2003-12-08'),
(12, 210, 228, '2006-06-01', '2006-06-01'),
(13, 1, 3, '2001-10-04', '2001-10-04'),
(14, 2, 5, '2004-11-16', '2004-11-16'),
(15, 251, 39, NULL, NULL),
(16, 4, 6, '2005-06-22', '2005-06-22'),
(17, 4, 8, '2003-04-19', '2003-04-19'),
(18, 9, 3, '2006-03-23', '2006-03-23'),
(19, 4, NULL, NULL, NULL),
(20, NULL, 2, '2008-10-22', '2008-10-22'),
(21, 4, 6, '2009-04-04', '2009-04-04'),
(22, NULL, 3, NULL, NULL),
(23, 1, 1, '2001-07-15', '2001-07-15'),
(24, 6, 4, '2004-09-25', '2004-09-25'),
(25, 2, 3, '2001-02-20', '2001-02-20'),
(26, NULL, 1, '1900-01-01', '1900-01-01'),
(27, 4, NULL, '2004-08-02', '2004-08-02'),
(28, 248, 97, '2009-02-26', '2009-02-26'),
(29, 4, 0, '2002-11-23', '2002-11-23'),
(30, 8, 0, '2008-12-17', '2008-12-17'),
(31, 4, 9, '2005-08-26', '2005-08-26'),
(32, 5, 5, '2001-03-10', '2001-03-10'),
(33, 9, 9, '1900-01-01', '1900-01-01'),
(34, 2, 0, '2003-08-04', '2003-08-04'),
(35, 4, 2, '2007-11-06', '2007-11-06'),
(36, 211, 172, '2009-04-23', '2009-04-23'),
(37, 2, NULL, '2002-10-06', '2002-10-06'),
(38, 4, 5, '2008-02-12', '2008-02-12'),
(39, 125, 119, '2007-09-18', '2007-09-18'),
(40, 9, 1, '2007-06-26', '2007-06-26'),
(41, 4, 4, NULL, NULL),
(42, 8, 8, '2009-05-05', '2009-05-05'),
(43, 8, NULL, '2003-05-04', '2003-05-04'),
(44, NULL, 6, '2003-11-10', '2003-11-10'),
(45, 8, 5, '2009-02-19', '2009-02-19'),
(46, 4, 5, '2006-11-17', '2006-11-17'),
(47, 8, 1, '2000-02-23', '2000-02-23'),
(48, NULL, 7, '1900-01-01', '1900-01-01'),
(49, 1, 2, '2003-04-08', '2003-04-08'),
(50, 3, 8, '2006-07-08', '2006-07-08'),
(51, 5, 9, '2001-05-21', '2001-05-21'),
(52, 8, NULL, NULL, NULL),
(53, 7, NULL, '2009-01-05', '2009-01-05'),
(54, 2, 3, '2001-08-05', '2001-08-05'),
(55, NULL, 172, '2000-11-01', '2000-11-01'),
(56, 3, NULL, '2004-12-16', '2004-12-16'),
(57, NULL, 6, '2003-02-06', '2003-02-06'),
(58, 9, 6, '2008-04-23', '2008-04-23'),
(59, 1, 5, '2005-12-20', '2005-12-20'),
(60, 0, 4, '2002-03-13', '2002-03-13'),
(61, 0, 3, NULL, NULL),
(62, 8, 2, '2006-07-20', '2006-07-20'),
(63, NULL, 7, '2002-12-19', '2002-12-19'),
(64, 9, 4, '2001-07-09', '2001-07-09'),
(65, 1, 6, '2006-05-27', '2006-05-27'),
(66, 9, 0, '2007-02-26', '2007-02-26'),
(67, 7, 8, NULL, NULL),
(68, 2, 5, '2007-02-07', '2007-02-07'),
(69, 1, 8, '2005-01-22', '2005-01-22'),
(70, 9, 2, '2006-04-25', '2006-04-25'),
(71, 5, 9, '2002-11-13', '2002-11-13'),
(72, 4, 7, '2007-10-26', '2007-10-26'),
(73, 6, 5, '2003-06-16', '2003-06-16'),
(74, 5, 7, '2006-01-18', '2006-01-18'),
(75, 2, 0, '1900-01-01', '1900-01-01'),
(76, 4, 4, '2000-09-13', '2000-09-13'),
(77, 0, 3, '2003-01-26', '2003-01-26'),
(78, 3, 1, '2002-09-09', '2002-09-09'),
(79, 0, 0, '2001-09-06', '2001-09-06'),
(80, 6, 6, '2006-02-23', '2006-02-23'),
(81, 1, 2, '2004-06-21', '2004-06-21'),
(82, 9, NULL, '2006-12-02', '2006-12-02'),
(83, 4, 8, '2005-05-17', '2005-05-17'),
(84, 9, NULL, '2001-12-27', '2001-12-27'),
(85, 4, NULL, '2008-04-11', '2008-04-11'),
(86, 1, NULL, '2000-09-24', '2000-09-24'),
(87, 2, 3, '2004-04-20', '2004-04-20'),
(88, 8, 7, '2008-07-10', '2008-07-10'),
(89, 1, 3, '2007-08-14', '2007-08-14'),
(90, 0, 5, '2008-06-08', '2008-06-08'),
(91, 2, 5, '2001-07-26', '2001-07-26'),
(92, 0, 1, '2008-09-17', '2008-09-17'),
(93, 1, 2, NULL, NULL),
(94, 2, 1, '2004-02-25', '2004-02-25'),
(95, NULL, 7, '2009-11-02', '2009-11-02'),
(96, 3, 1, '2001-12-14', '2001-12-14'),
(97, 8, 9, NULL, NULL),
(98, 4, 9, '1900-01-01', '1900-01-01'),
(99, 4, 8, '2002-04-12', '2002-04-12'),
(100, NULL, 3, '2000-02-09', '2000-02-09');
create  table t11 (
col_date_key date default null,
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 default null,
col_varchar_10_latin1 varchar(10) default null,
col_varchar_10_latin1_key varchar(10) default null,
col_datetime datetime default null,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 default null,
col_varchar_1024_latin1_key varchar(1024) default null,
col_varchar_1024_latin1 varchar(1024) default null,
col_varchar_1024_utf8 varchar(1024) CHARACTER SET utf8 default null,
col_int_key int(11) default null,
col_date date default null,
col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 default null,
col_int int(11) default null,
pk int(11) not null auto_increment,
col_datetime_key datetime default null,
primary key (pk),
key col_date_key (col_date_key),
key col_varchar_10_latin1_key (col_varchar_10_latin1_key),
key col_varchar_10_utf8_key (col_varchar_10_utf8_key),
key col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000)),
key col_int_key (col_int_key),
key col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333)),
key col_datetime_key (col_datetime_key)
) engine=myisam auto_increment=76 default charset=latin1;
insert ignore into t11 values
('2005-03-10','have','o','gryvuwdllyhymuwyypoiuyeqbnaanbur','2004','going','h','well','f',1140785152,'2003','YTLDF',NULL,NULL,'20050908213850.042201'),
('2003', 'g', 'ELTFL', 'got', '20:07:16.048352', 'ryvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnvspyaftisgdbkzlevczyrlossygtnatwcobbtsxqhjrdykmthpyzmdgyirxwlidiknxwsufkyexucekyhivscjdcouswuiyltrpmrngriwvrxgovfyewdsvlwirfzkwexxzoccufvuvhsjfvuwjsrrzguntudxxhblzbescayrbbrijnvucxxmbyltlojlgcweddzrfxsmwnxxewigapgrowtmrlqsknuaegzfvdwfdnnbfyubwckwfdrwmrymagyswwvvyeqhaaskgxogthhrzsdlsyqoeuvhwxquwbnivpowtybzehvbsoppuefqngkqohtdeylosjyvtxuziotnfpbqxkpxvzcjbgxokjzitakfevxduhtudsslluhzgcwgrcrtpnczgxchnmmgnubjzvvuklklfhiupbsjzhcqyadhskxtjzabzesulhgpykga', 'she', 'have', 'PYJKD', 9, '2008-06-10', 'yvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnv', NULL, NULL, '2008-01-09');
create  table t12 (t text, c char(10), b blob, d varbinary(10)) engine=innodb;
insert into t12 values (NULL, NULL, NULL,NULL);
insert into t12 values ("", "", "", "");
insert into t12 values ("hello", "hello", "hello", "hello");
insert into t12 values ("HELLO", "HELLO","HELLO", "HELLO");
insert into t12 values ("HELLO MY", "HELLO MY", "HELLO MY", "HELLO MY");
insert into t12 values ("a", "a", "a", "a");
insert into t12 values (1,1,1,1);
insert into t12 values (NULL,NULL,NULL,NULL);
create  table t13 (
pk int(11) not null auto_increment,
col_int_nokey int(11) default null,
col_int_key int(11) default null,
col_date_key date default null,
col_time_key time default null,
col_varchar_key varchar(1) default null,
col_varchar_nokey varchar(1) default null,
primary key (pk),
key col_int_key (col_int_key),
key col_date_key (col_date_key),
key col_time_key (col_time_key),
key col_varchar_key (col_varchar_key, col_int_key)
) engine=myisam auto_increment=21;
insert into t13 values
(2, 150, 62, '2005-03-27', '14:26:02', 'v', 'v'),
(3, NULL, 7, '2004-04-09', '14:03:03', 'c', 'c'),
(4, 2, 1, '2006-05-13', '01:46:09', NULL, NULL),
(5, 5, 0, '2001-05-06', '16:21:18', 'x', 'x'),
(6, 3, 7, '2006-03-03', '18:56:33', 'i', 'i'),
(7, 1, 7, '2007-12-28', NULL, 'e', 'e'),
(8, 4, 1, '2004-10-20', '09:29:08', 'p', 'p'),
(9, NULL, 7, '2008-04-09', '19:11:10', 's', 's'),
(10, 2, 1, '2005-12-25', '11:57:26', 'j', 'j'),
(11, 6, 5, '1900-01-01', '00:39:46', 'z', 'z'),
(12, 6, 2, NULL, '03:28:15', 'c', 'c'),
(13, 8, 0, '1900-01-01', '06:44:18', 'a', 'a'),
(14, 2, 1, '2001-01-16', '14:36:39', 'q', 'q'),
(15, 6, 8, '1900-01-01', '18:42:45', 'y', 'y'),
(16, 8, 1, '2001-11-23', '02:57:29', NULL, NULL),
(17, 3, 1, '2004-11-04', '16:46:13', 'r', 'r'),
(18, 3, 9, '2003-03-12', '19:39:02', 'v', 'v'),
(19, 9, 1, '2002-06-22', NULL, NULL, NULL),
(20, 6, 5, '2004-10-10', '20:58:33', 'r', 'r');
create  table t14 (
pk int(11) not null auto_increment,
col_int_nokey int(11) default null,
col_int_key int(11) default null,
col_date_key date default null,
col_time_key time default null,
col_varchar_key varchar(1) default null,
col_varchar_nokey varchar(1) default null,
primary key (pk),
key col_int_key (col_int_key),
key col_date_key (col_date_key),
key col_time_key (col_time_key),
key col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=myisam auto_increment=30;
insert into t14 values
(10, NULL, 8, '2000-12-03', '22:55:23', 'x', 'x'),
(11, 8, 7, '2008-05-03', '10:19:31', 'd', 'd'),
(12, 1, 1, '2005-12-06', '14:40:36', 'r', 'r'),
(13, 9, 7, '2000-04-10', '04:37:47', 'f', 'f'),
(14, 4, 9, '2002-11-05', '19:34:06', 'y', 'y'),
(15, 3, NULL, '2000-09-06', '20:35:33', 'u', 'u'),
(16, 2, 1, NULL, NULL, 'm', 'm'),
(17, NULL, 9, '2007-06-14', '14:43:37', NULL, NULL),
(18, 2, 2, '2007-11-17', '02:23:09', 'o', 'o'),
(19, NULL, 9, '2009-02-23', '01:22:45', 'w', 'w'),
(20, 6, 2, '2007-01-08', '00:00:00', 'm', 'm'),
(21, 7, 4, '2008-06-10', '00:13:25', 'q', 'q'),
(22, 2, 0, '2002-10-20', '03:47:16', NULL, NULL),
(23, 5, 4, '2008-09-12', '01:41:48', 'd', 'd'),
(24, 7, 8, '2006-06-16', '00:00:00', 'g', 'g'),
(25, 6, NULL, '2004-09-18', '22:32:04', 'x', 'x'),
(26, 6, NULL, '1900-01-01', '16:44:14', 'f', 'f'),
(27, 2, 0, '2005-09-13', '17:38:37', 'p', 'p'),
(28, 9, NULL, '2007-04-09', '08:46:48', 'j', 'j'),
(29, 6, 8, '2000-09-20', '14:11:27', 'c', 'c');
analyze table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
test.t4	analyze	status	OK
test.t5	analyze	status	OK
test.t6	analyze	status	OK
test.t7	analyze	status	OK
test.t8	analyze	status	OK
test.t9	analyze	status	OK
test.t10	analyze	status	OK
test.t11	analyze	status	OK
test.t12	analyze	status	OK
test.t13	analyze	status	OK
test.t14	analyze	status	OK
# restart: --big-tables=1
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";
explain format = json select sql_buffer_result * from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "buffer_result": {
      "using_temporary_table": true,
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
select sql_buffer_result * from t1;
a	b
1	a
2	b
3	c
3	c
4	c
explain format = json select sql_buffer_result * from t2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "buffer_result": {
      "using_temporary_table": true,
      "table": {
        "table_name": "t2",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select sql_buffer_result `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
select sql_buffer_result * from t2;
a	b
2	d
3	e
4	f
4	f
5	e
explain format = json select * from t1 left join t2 on t1.b = t2.b order by t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "25.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 25,
            "filtered": "100.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "5.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "800"
            },
            "used_columns": [
              "a",
              "b"
            ],
            "attached_condition": "<if>(is_not_null_compl(t2), (`test`.`t2`.`b` = `test`.`t1`.`b`), true)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where 1 order by `test`.`t1`.`a`
select * from t1 left join t2 on t1.b = t2.b order by t1.a;
a	b	a	b
1	a	NULL	NULL
2	b	NULL	NULL
3	c	NULL	NULL
3	c	NULL	NULL
4	c	NULL	NULL
explain format = json select * from t1 order by func1(a);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `func1`(`test`.`t1`.`a`)
select * from t1 order by func1(a);
a	b
1	a
2	b
3	c
3	c
4	c
explain format = json select a from t1 group by b order by sum(a);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b` order by sum(`test`.`t1`.`a`)
select a from t1 group by b order by sum(a);
a
1
2
3
explain format = json select a from t7 group by b order by sum(a);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "grouping_operation": {
        "using_filesort": false,
        "table": {
          "table_name": "t7",
          "access_type": "index",
          "possible_keys": [
            "indx7"
          ],
          "key": "indx7",
          "used_key_parts": [
            "b"
          ],
          "key_length": "23",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t7`.`a` AS `a` from `test`.`t7` group by `test`.`t7`.`b` order by sum(`test`.`t7`.`a`)
select a from t7 group by b order by sum(a);
a
1
2
3
explain format = json select sql_big_result distinct * from t1, t2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "buffer_result": {
        "using_temporary_table": true,
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          },
          {
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 25,
              "filtered": "100.00",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "5.00",
                "prefix_cost": "8.00",
                "data_read_per_join": "800"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2`
select sql_big_result distinct * from t1, t2;
a	b	a	b
1	a	2	d
1	a	3	e
1	a	4	f
1	a	5	e
2	b	2	d
2	b	3	e
2	b	4	f
2	b	5	e
3	c	2	d
3	c	3	e
3	c	4	f
3	c	5	e
4	c	2	d
4	c	3	e
4	c	4	f
4	c	5	e
explain format = json select sql_big_result t1.b from t1, t2 group by t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "25.00"
      },
      "buffer_result": {
        "using_temporary_table": true,
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "b"
              ]
            }
          },
          {
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 25,
              "filtered": "100.00",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "5.00",
                "prefix_cost": "8.00",
                "data_read_per_join": "800"
              }
            }
          }
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select sql_big_result `test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`b`
select sql_big_result t1.b from t1, t2 group by t1.b;
b
a
b
c
explain format = json select sql_big_result distinct t1.a from t1, t2 group by t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "25.00"
        },
        "buffer_result": {
          "using_temporary_table": true,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "a",
                  "b"
                ]
              }
            },
            {
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 25,
                "filtered": "100.00",
                "distinct": true,
                "using_join_buffer": "Block Nested Loop",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "5.00",
                  "prefix_cost": "8.00",
                  "data_read_per_join": "800"
                }
              }
            }
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`b`
select sql_big_result distinct t1.a from t1, t2 group by t1.b;
a
1
2
3
explain format = json select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t2`.`a`
select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
a	b
2	b
3	c
3	c
4	c
4	c
explain format = json select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t1`.`a`
select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
a	b
2	b
3	c
3	c
4	c
4	c
explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t1`.`a`
select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
a	b
2	b
3	c
4	c
explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t2`.`a`
select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
a	b
2	b
3	c
4	c
explain format = json select a from t1 group by a order by a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "5.00"
        },
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` order by `test`.`t1`.`a`
select a from t1 group by a order by a;
a
1
2
3
4
explain format = json select b from t1 group by b order by a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` group by `test`.`t1`.`b` order by `test`.`t1`.`a`
select b from t1 group by b order by a;
b
a
b
c
explain format = json select distinct t1.a from t1, t2 where  t1.b = t2.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "distinct": true,
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "b"
            ],
            "attached_condition": "(`test`.`t2`.`b` = `test`.`t1`.`b`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
select distinct t1.a from t1, t2 where  t1.b = t2.b;
a
explain format = json select distinct a from t1 group by b,a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "5.00"
        },
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b`,`test`.`t1`.`a`
select distinct a from t1 group by b,a;
a
1
2
3
4
set @old_sort_buffer_size = @@sort_buffer_size;
set @@sort_buffer_size = 32804;
explain format = json select distinct b from t3 group by a having count(*) > 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.20"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "1.00"
        },
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t3`.`b` AS `b` from `test`.`t3` group by `test`.`t3`.`a` having (count(0) > 1)
select distinct b from t3 group by a having count(*) > 1;
b
select distinct b from t3 group by a;
b
set @@sort_buffer_size = @old_sort_buffer_size;
explain format = json select distinct t4.a,e from t4, t1 order by t4.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.26"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "duplicates_removal": {
        "using_temporary_table": true,
        "using_filesort": false,
        "cost_info": {
          "sort_cost": "5.00"
        },
        "nested_loop": [
          {
            "table": {
              "table_name": "t4",
              "access_type": "ALL",
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.20",
                "prefix_cost": "1.20",
                "data_read_per_join": "32"
              },
              "used_columns": [
                "a",
                "b",
                "e"
              ]
            }
          },
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "distinct": true,
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.06",
                "eval_cost": "1.00",
                "prefix_cost": "3.26",
                "data_read_per_join": "160"
              }
            }
          }
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t4`.`a` AS `a`,`test`.`t4`.`e` AS `e` from `test`.`t4` join `test`.`t1` order by `test`.`t4`.`b`
select distinct t4.a,e from t4, t1 order by t4.b;
a	e
1	5
explain format = json select * from t1 union all select * from t2;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "using_temporary_table": false,
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
select * from t1 union all select * from t2;
a	b
1	a
2	b
3	c
3	c
4	c
2	d
3	e
4	f
4	f
5	e
explain format = json select * from t1 union select * from t2 order by b;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `b`
select * from t1 union select * from t2 order by b;
a	b
1	a
2	b
3	c
4	c
2	d
3	e
5	e
4	f
explain format = json select tt.* from (select * from t1) as tt;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.00"
    },
    "table": {
      "table_name": "tt",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2.00",
        "eval_cost": "1.00",
        "prefix_cost": "3.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "a",
        "b"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "2.00"
          },
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `tt`.`a` AS `a`,`tt`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `tt`
select tt.* from (select * from t1) as tt;
a	b
1	a
2	b
3	c
3	c
4	c
explain format = json select b from (select b from t5 where b > 5) tt;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.40"
    },
    "table": {
      "table_name": "tt",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2.00",
        "eval_cost": "0.40",
        "prefix_cost": "2.40",
        "data_read_per_join": "32"
      },
      "used_columns": [
        "b"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "1.40"
          },
          "table": {
            "table_name": "t5",
            "access_type": "index",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "b"
            ],
            "key_length": "4",
            "rows_examined_per_scan": 2,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "0.40",
              "prefix_cost": "1.40",
              "data_read_per_join": "32"
            },
            "used_columns": [
              "b"
            ],
            "attached_condition": "(`test`.`t5`.`b` > 5)"
          }
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `tt`.`b` AS `b` from (/* select#2 */ select `test`.`t5`.`b` AS `b` from `test`.`t5` where (`test`.`t5`.`b` > 5)) `tt`
select b from (select b from t5 where b > 5) tt;
b
7
8
explain format = json select * from (select b from t1) as t1, (select b from t2) as t2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.00",
            "eval_cost": "1.00",
            "prefix_cost": "3.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "b"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "2.00"
              },
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "b"
                ]
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 25,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "2.00",
            "eval_cost": "5.00",
            "prefix_cost": "10.00",
            "data_read_per_join": "800"
          },
          "used_columns": [
            "b"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 3,
              "cost_info": {
                "query_cost": "2.00"
              },
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "b"
                ]
              }
            }
          }
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select `t1`.`b` AS `b`,`t2`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`b` AS `b` from `test`.`t1`) `t1` join (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t2`
select * from (select b from t1) as t1, (select b from t2) as t2;
b	b
a	d
b	d
c	d
c	d
c	d
a	e
b	e
c	e
c	e
c	e
a	f
b	f
c	f
c	f
c	f
a	f
b	f
c	f
c	f
c	f
a	e
b	e
c	e
c	e
c	e
explain format = json select * from t1, (select * from t6) as t where t.a =5;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.20"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t",
          "access_type": "ref",
          "possible_keys": [
            "<auto_key0>"
          ],
          "key": "<auto_key0>",
          "used_key_parts": [
            "a"
          ],
          "key_length": "5",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "a",
            "b",
            "c",
            "d"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "1.20"
              },
              "table": {
                "table_name": "t6",
                "access_type": "ALL",
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 1,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "0.20",
                  "prefix_cost": "1.20",
                  "data_read_per_join": "16"
                },
                "used_columns": [
                  "a",
                  "b",
                  "c",
                  "d"
                ]
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "3.20",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `test`.`t1` join (/* select#2 */ select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t6`.`c` AS `c`,`test`.`t6`.`d` AS `d` from `test`.`t6`) `t` where (`t`.`a` = 5)
select * from t1, (select * from t6) as t where t.a =5;
a	b	a	b	c	d
explain format = json select * from t1 where t1.a in (select a from t2) ;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ],
          "attached_condition": "(`test`.`t1`.`a` is not null)"
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "eq_ref",
          "key": "<auto_key>",
          "key_length": "5",
          "ref": [
            "test.t1.a"
          ],
          "rows_examined_per_scan": 1,
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "query_block": {
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "a"
                ]
              }
            }
          }
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`<subquery2>`.`a` = `test`.`t1`.`a`)
select * from t1 where t1.a in (select a from t2) ;
a	b
2	b
3	c
3	c
4	c
set optimizer_switch='materialization=off';
set optimizer_switch='firstmatch=off';
set optimizer_switch='loosescan=off';
explain format = json select * from t1 where t1.a in (select a from t2) ;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "12.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
select * from t1 where t1.a in (select a from t2) ;
a	b
2	b
3	c
3	c
4	c
set optimizer_switch='default,derived_merge=off';
explain format = json update t1, t2 set t1.a = 9, t2.a = 10 where t1.b = 'a';
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4.00"
    },
    "nested_loop": [
      {
        "table": {
          "update": true,
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 1,
          "filtered": "20.00",
          "cost_info": {
            "read_cost": "1.80",
            "eval_cost": "0.20",
            "prefix_cost": "2.00",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "a",
            "b"
          ],
          "attached_condition": "(`test`.`t1`.`b` = 'a')"
        }
      },
      {
        "table": {
          "update": true,
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "4.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a"
          ]
        }
      }
    ]
  }
}
update t1, t2 set t1.a = 9, t2.a = 10 where t1.b = 'a';
explain format = json select count(distinct a) from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.00",
        "prefix_cost": "2.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "a"
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t1`.`a`) AS `count(distinct a)` from `test`.`t1`
select count(distinct a) from t1;
count(distinct a)
4
explain format = json select b, group_concat(a) from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.00",
        "prefix_cost": "2.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "a",
        "b"
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b`,group_concat(`test`.`t1`.`a` separator ',') AS `group_concat(a)` from `test`.`t1`
select b, group_concat(a) from t1;
b	group_concat(a)
a	9,2,3,3,4
select * from t1;
a	b
9	a
2	b
3	c
3	c
4	c
explain format = json select b, count(a), sum(a), avg(a), std(a),
min(a), max(a), bit_and(a), bit_or(a) from t1 group by b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b`,count(`test`.`t1`.`a`) AS `count(a)`,sum(`test`.`t1`.`a`) AS `sum(a)`,avg(`test`.`t1`.`a`) AS `avg(a)`,std(`test`.`t1`.`a`) AS `std(a)`,min(`test`.`t1`.`a`) AS `min(a)`,max(`test`.`t1`.`a`) AS `max(a)`,bit_and(`test`.`t1`.`a`) AS `bit_and(a)`,bit_or(`test`.`t1`.`a`) AS `bit_or(a)` from `test`.`t1` group by `test`.`t1`.`b`
select b, count(a), sum(a), avg(a), std(a), min(a),
max(a), bit_and(a), bit_or(a) from t1 group by b;
b	count(a)	sum(a)	avg(a)	std(a)	min(a)	max(a)	bit_and(a)	bit_or(a)
a	1	9	9.0000	0	9	9	9	9
b	1	2	2.0000	0	2	2	2	2
c	3	10	3.3333	0.4714045207910316	3	4	0	7
select sql_big_result * from information_schema.global_variables where variable_name like 'internal_tmp_disk_storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
INTERNAL_TMP_DISK_STORAGE_ENGINE	InnoDB
select 1 union distinct select 1 union all select 1;
1
1
1
select alias1.`col_int_key` AS field1 from (t8 as alias1) where (5) in
(select 7 union select 1) order by alias1.`col_varchar_key` asc;
field1
select count(table2.col_date_key) as field1 from
(t10 as table1 inner join
((t10 as table2 right join
t9 as table3 on
((table3.pk = table2.pk) or
(table3.pk = table2.col_int_key))))
on ((table3.pk < table2.col_int_nokey)))
order by field1 ASC;
field1
2200
select distinct table1.col_varchar_1024_utf8_key as field1
from t11 as table1 left join t11 as table2 on
table1.col_varchar_1024_utf8 = table2.col_varchar_1024_latin1_key
order by field1;
field1
YTLDF
yvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnv
select t from t12 group by c;
t
NULL

1
a
hello
HELLO MY
select count(outr.col_varchar_nokey) AS x
from t13 as outr2 left join
(select * from t13) as outr
on (outr2.col_time_key > outr.col_date_key)
where outr.pk in
(select innr.col_int_nokey AS y from
(select * from t14) as innr2 left join
(select * from t14) AS innr
ON (innr2.col_int_nokey = innr.col_int_key)
where innr.col_varchar_nokey > innr.col_varchar_key
or not outr.pk > 9)
or outr.col_varchar_key is null;
x
102
SET @@optimizer_switch=@optimizer_switch_saved;
# restart: --innodb-read-only --big-tables=1
SET @optimizer_switch_saved= @@optimizer_switch;
SET @@optimizer_switch="derived_merge=off";
explain format = json select sql_buffer_result * from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "buffer_result": {
      "using_temporary_table": true,
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
select sql_buffer_result * from t1;
a	b
9	a
2	b
3	c
3	c
4	c
explain format = json select sql_buffer_result * from t2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "buffer_result": {
      "using_temporary_table": true,
      "table": {
        "table_name": "t2",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select sql_buffer_result `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
select sql_buffer_result * from t2;
a	b
10	d
10	e
10	f
10	f
10	e
explain format = json select * from t1 left join t2 on t1.b = t2.b order by t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "25.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 25,
            "filtered": "100.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "5.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "800"
            },
            "used_columns": [
              "a",
              "b"
            ],
            "attached_condition": "<if>(is_not_null_compl(t2), (`test`.`t2`.`b` = `test`.`t1`.`b`), true)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t1`.`b`)) where 1 order by `test`.`t1`.`a`
select * from t1 left join t2 on t1.b = t2.b order by t1.a;
a	b	a	b
2	b	NULL	NULL
3	c	NULL	NULL
3	c	NULL	NULL
4	c	NULL	NULL
9	a	NULL	NULL
explain format = json select * from t1 order by func1(a);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `func1`(`test`.`t1`.`a`)
select * from t1 order by func1(a);
a	b
2	b
3	c
3	c
4	c
9	a
explain format = json select a from t1 group by b order by sum(a);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b` order by sum(`test`.`t1`.`a`)
select a from t1 group by b order by sum(a);
a
2
9
3
explain format = json select a from t7 group by b order by sum(a);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "grouping_operation": {
        "using_filesort": false,
        "table": {
          "table_name": "t7",
          "access_type": "index",
          "possible_keys": [
            "indx7"
          ],
          "key": "indx7",
          "used_key_parts": [
            "b"
          ],
          "key_length": "23",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t7`.`a` AS `a` from `test`.`t7` group by `test`.`t7`.`b` order by sum(`test`.`t7`.`a`)
select a from t7 group by b order by sum(a);
a
1
2
3
explain format = json select sql_big_result distinct * from t1, t2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "buffer_result": {
        "using_temporary_table": true,
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          },
          {
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 25,
              "filtered": "100.00",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "5.00",
                "prefix_cost": "8.00",
                "data_read_per_join": "800"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2`
select sql_big_result distinct * from t1, t2;
a	b	a	b
2	b	10	d
2	b	10	e
2	b	10	f
3	c	10	d
3	c	10	e
3	c	10	f
4	c	10	d
4	c	10	e
4	c	10	f
9	a	10	d
9	a	10	e
9	a	10	f
explain format = json select sql_big_result t1.b from t1, t2 group by t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "25.00"
      },
      "buffer_result": {
        "using_temporary_table": true,
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "b"
              ]
            }
          },
          {
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 25,
              "filtered": "100.00",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "5.00",
                "prefix_cost": "8.00",
                "data_read_per_join": "800"
              }
            }
          }
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select sql_big_result `test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`b`
select sql_big_result t1.b from t1, t2 group by t1.b;
b
a
b
c
explain format = json select sql_big_result distinct t1.a from t1, t2 group by t1.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "25.00"
        },
        "buffer_result": {
          "using_temporary_table": true,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "a",
                  "b"
                ]
              }
            },
            {
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 25,
                "filtered": "100.00",
                "distinct": true,
                "using_join_buffer": "Block Nested Loop",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "5.00",
                  "prefix_cost": "8.00",
                  "data_read_per_join": "800"
                }
              }
            }
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct sql_big_result `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`b`
select sql_big_result distinct t1.a from t1, t2 group by t1.b;
a
9
2
3
explain format = json select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t2`.`a`
select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
a	b
explain format = json select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) order by `test`.`t1`.`a`
select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
a	b
explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t1`.`a`
select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
a	b
explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "13.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t2`.`a`
select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
a	b
explain format = json select a from t1 group by a order by a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "5.00"
        },
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` order by `test`.`t1`.`a`
select a from t1 group by a order by a;
a
2
3
4
9
explain format = json select b from t1 group by b order by a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` group by `test`.`t1`.`b` order by `test`.`t1`.`a`
select b from t1 group by b order by a;
b
b
c
a
explain format = json select distinct t1.a from t1, t2 where  t1.b = t2.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "distinct": true,
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "8.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "b"
            ],
            "attached_condition": "(`test`.`t2`.`b` = `test`.`t1`.`b`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
select distinct t1.a from t1, t2 where  t1.b = t2.b;
a
explain format = json select distinct a from t1 group by b,a;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "5.00"
        },
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`b`,`test`.`t1`.`a`
select distinct a from t1 group by b,a;
a
9
2
3
4
set @old_sort_buffer_size = @@sort_buffer_size;
set @@sort_buffer_size = 32804;
explain format = json select distinct b from t3 group by a having count(*) > 1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.20"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "1.00"
        },
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t3`.`b` AS `b` from `test`.`t3` group by `test`.`t3`.`a` having (count(0) > 1)
select distinct b from t3 group by a having count(*) > 1;
b
select distinct b from t3 group by a;
b
set @@sort_buffer_size = @old_sort_buffer_size;
explain format = json select distinct t4.a,e from t4, t1 order by t4.b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.26"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "duplicates_removal": {
        "using_temporary_table": true,
        "using_filesort": false,
        "cost_info": {
          "sort_cost": "5.00"
        },
        "nested_loop": [
          {
            "table": {
              "table_name": "t4",
              "access_type": "ALL",
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.20",
                "prefix_cost": "1.20",
                "data_read_per_join": "32"
              },
              "used_columns": [
                "a",
                "b",
                "e"
              ]
            }
          },
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "distinct": true,
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.06",
                "eval_cost": "1.00",
                "prefix_cost": "3.26",
                "data_read_per_join": "160"
              }
            }
          }
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select distinct `test`.`t4`.`a` AS `a`,`test`.`t4`.`e` AS `e` from `test`.`t4` join `test`.`t1` order by `test`.`t4`.`b`
select distinct t4.a,e from t4, t1 order by t4.b;
a	e
1	5
explain format = json select * from t1 union all select * from t2;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "using_temporary_table": false,
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union all /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
select * from t1 union all select * from t2;
a	b
9	a
2	b
3	c
3	c
4	c
10	d
10	e
10	f
10	f
10	e
explain format = json select * from t1 union select * from t2 order by b;
EXPLAIN
{
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "cost_info": {
              "query_cost": "2.00"
            },
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 5,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "1.00",
                "prefix_cost": "2.00",
                "data_read_per_join": "160"
              },
              "used_columns": [
                "a",
                "b"
              ]
            }
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `b`
select * from t1 union select * from t2 order by b;
a	b
9	a
2	b
3	c
4	c
10	d
10	e
10	f
explain format = json select tt.* from (select * from t1) as tt;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.00"
    },
    "table": {
      "table_name": "tt",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2.00",
        "eval_cost": "1.00",
        "prefix_cost": "3.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "a",
        "b"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "2.00"
          },
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `tt`.`a` AS `a`,`tt`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) `tt`
select tt.* from (select * from t1) as tt;
a	b
9	a
2	b
3	c
3	c
4	c
explain format = json select b from (select b from t5 where b > 5) tt;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.40"
    },
    "table": {
      "table_name": "tt",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2.00",
        "eval_cost": "0.40",
        "prefix_cost": "2.40",
        "data_read_per_join": "32"
      },
      "used_columns": [
        "b"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "1.40"
          },
          "table": {
            "table_name": "t5",
            "access_type": "index",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "b"
            ],
            "key_length": "4",
            "rows_examined_per_scan": 2,
            "rows_produced_per_join": 2,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "0.40",
              "prefix_cost": "1.40",
              "data_read_per_join": "32"
            },
            "used_columns": [
              "b"
            ],
            "attached_condition": "(`test`.`t5`.`b` > 5)"
          }
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `tt`.`b` AS `b` from (/* select#2 */ select `test`.`t5`.`b` AS `b` from `test`.`t5` where (`test`.`t5`.`b` > 5)) `tt`
select b from (select b from t5 where b > 5) tt;
b
7
8
explain format = json select * from (select b from t1) as t1, (select b from t2) as t2;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.00",
            "eval_cost": "1.00",
            "prefix_cost": "3.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "b"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "2.00"
              },
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "b"
                ]
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 25,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "2.00",
            "eval_cost": "5.00",
            "prefix_cost": "10.00",
            "data_read_per_join": "800"
          },
          "used_columns": [
            "b"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 3,
              "cost_info": {
                "query_cost": "2.00"
              },
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "b"
                ]
              }
            }
          }
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select `t1`.`b` AS `b`,`t2`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`b` AS `b` from `test`.`t1`) `t1` join (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t2`
select * from (select b from t1) as t1, (select b from t2) as t2;
b	b
a	d
b	d
c	d
c	d
c	d
a	e
b	e
c	e
c	e
c	e
a	f
b	f
c	f
c	f
c	f
a	f
b	f
c	f
c	f
c	f
a	e
b	e
c	e
c	e
c	e
explain format = json select * from t1, (select * from t6) as t where t.a =5;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.20"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t",
          "access_type": "ref",
          "possible_keys": [
            "<auto_key0>"
          ],
          "key": "<auto_key0>",
          "used_key_parts": [
            "a"
          ],
          "key_length": "5",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "24"
          },
          "used_columns": [
            "a",
            "b",
            "c",
            "d"
          ],
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "dependent": false,
            "cacheable": true,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "1.20"
              },
              "table": {
                "table_name": "t6",
                "access_type": "ALL",
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 1,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "0.20",
                  "prefix_cost": "1.20",
                  "data_read_per_join": "16"
                },
                "used_columns": [
                  "a",
                  "b",
                  "c",
                  "d"
                ]
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "3.20",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ]
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c`,`t`.`d` AS `d` from `test`.`t1` join (/* select#2 */ select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t6`.`c` AS `c`,`test`.`t6`.`d` AS `d` from `test`.`t6`) `t` where (`t`.`a` = 5)
select * from t1, (select * from t6) as t where t.a =5;
a	b	a	b	c	d
explain format = json select * from t1 where t1.a in (select a from t2) ;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "160"
          },
          "used_columns": [
            "a",
            "b"
          ],
          "attached_condition": "(`test`.`t1`.`a` is not null)"
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "eq_ref",
          "key": "<auto_key>",
          "key_length": "5",
          "ref": [
            "test.t1.a"
          ],
          "rows_examined_per_scan": 1,
          "materialized_from_subquery": {
            "using_temporary_table": true,
            "query_block": {
              "table": {
                "table_name": "t2",
                "access_type": "ALL",
                "rows_examined_per_scan": 5,
                "rows_produced_per_join": 5,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "1.00",
                  "eval_cost": "1.00",
                  "prefix_cost": "2.00",
                  "data_read_per_join": "160"
                },
                "used_columns": [
                  "a"
                ]
              }
            }
          }
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`<subquery2>`.`a` = `test`.`t1`.`a`)
select * from t1 where t1.a in (select a from t2) ;
a	b
set optimizer_switch='materialization=off';
set optimizer_switch='firstmatch=off';
set optimizer_switch='loosescan=off';
explain format = json select * from t1 where t1.a in (select a from t2) ;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.00"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "2.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a",
              "b"
            ]
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 5,
            "filtered": "20.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "1.00",
              "prefix_cost": "12.00",
              "data_read_per_join": "160"
            },
            "used_columns": [
              "a"
            ],
            "attached_condition": "(`test`.`t2`.`a` = `test`.`t1`.`a`)"
          }
        }
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
select * from t1 where t1.a in (select a from t2) ;
a	b
set optimizer_switch='default,derived_merge=off';
explain format = json select count(distinct a) from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.00",
        "prefix_cost": "2.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "a"
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select count(distinct `test`.`t1`.`a`) AS `count(distinct a)` from `test`.`t1`
select count(distinct a) from t1;
count(distinct a)
4
explain format = json select b, group_concat(a) from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.00"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows_examined_per_scan": 5,
      "rows_produced_per_join": 5,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "1.00",
        "prefix_cost": "2.00",
        "data_read_per_join": "160"
      },
      "used_columns": [
        "a",
        "b"
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b`,group_concat(`test`.`t1`.`a` separator ',') AS `group_concat(a)` from `test`.`t1`
select b, group_concat(a) from t1;
b	group_concat(a)
a	9,2,3,3,4
select * from t1;
a	b
9	a
2	b
3	c
3	c
4	c
explain format = json select b, count(a), sum(a), avg(a), std(a),
min(a), max(a), bit_and(a), bit_or(a) from t1 group by b;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "5.00"
      },
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 5,
        "rows_produced_per_join": 5,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "1.00",
          "prefix_cost": "2.00",
          "data_read_per_join": "160"
        },
        "used_columns": [
          "a",
          "b"
        ]
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b`,count(`test`.`t1`.`a`) AS `count(a)`,sum(`test`.`t1`.`a`) AS `sum(a)`,avg(`test`.`t1`.`a`) AS `avg(a)`,std(`test`.`t1`.`a`) AS `std(a)`,min(`test`.`t1`.`a`) AS `min(a)`,max(`test`.`t1`.`a`) AS `max(a)`,bit_and(`test`.`t1`.`a`) AS `bit_and(a)`,bit_or(`test`.`t1`.`a`) AS `bit_or(a)` from `test`.`t1` group by `test`.`t1`.`b`
select b, count(a), sum(a), avg(a), std(a), min(a),
max(a), bit_and(a), bit_or(a) from t1 group by b;
b	count(a)	sum(a)	avg(a)	std(a)	min(a)	max(a)	bit_and(a)	bit_or(a)
a	1	9	9.0000	0	9	9	9	9
b	1	2	2.0000	0	2	2	2	2
c	3	10	3.3333	0.4714045207910316	3	4	0	7
select sql_big_result * from information_schema.global_variables where variable_name like 'internal_tmp_disk_storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
INTERNAL_TMP_DISK_STORAGE_ENGINE	InnoDB
select 1 union distinct select 1 union all select 1;
1
1
1
select alias1.`col_int_key` AS field1 from (t8 as alias1) where (5) in
(select 7 union select 1) order by alias1.`col_varchar_key` asc;
field1
select count(table2.col_date_key) as field1 from
(t10 as table1 inner join
((t10 as table2 right join
t9 as table3 on
((table3.pk = table2.pk) or
(table3.pk = table2.col_int_key))))
on ((table3.pk < table2.col_int_nokey)))
order by field1 ASC;
field1
2200
select distinct table1.col_varchar_1024_utf8_key as field1
from t11 as table1 left join t11 as table2 on
table1.col_varchar_1024_utf8 = table2.col_varchar_1024_latin1_key
order by field1;
field1
YTLDF
yvuwdllyhymuwyypoiuyeqbnaanbursgddnqhyvxnnpadsjiqdkajofferjfslatroorycttbiuhlubvmoikwwnalqyewgthdmfvsedatazrflwenzeiwdxtheqppbmqfydsawvanehgvfvbsqxzkqzhtjkybvikmpexipoddmoulsnzfchusfgkdazecevrpuakfioamyyqyribcnydfxmmzsxcpkydyczmeajaebsaskneaqwxjbpjptiwgiizoxfygnbjojpifpzldsdhrukdbwpzribxgafunywmmcfpjugozduylotsugqocbadhcnxjqlugncbebwsbobhxgghyyphxfqqnpliazusgjswynfotwdonpbkllfdbuoqceirksegypasuuxnrjumrkaryhkgkbdfgoqbdmrdrmpvwitqzswgossxltimkbtrxitnmigcxgfaqmrnv
select t from t12 group by c;
t
NULL

${this.title}

Code Editor : optimizer_temporary_table.result