Gecko [ www.jdcpatala.org ]

Name	Size	Permission
1st.result	597 B	-rw-r--r--
acl_tables_errors_debug.result	8.93 KB	-rw-r--r--
alias.result	13.58 KB	-rw-r--r--
almost_full.result	1.11 KB	-rw-r--r--
alter_table-big.result	5.45 KB	-rw-r--r--
alter_table.result	154.41 KB	-rw-r--r--
analyze.result	3.69 KB	-rw-r--r--
ansi.result	1.92 KB	-rw-r--r--
archive-big.result	616 B	-rw-r--r--
archive.result	525.18 KB	-rw-r--r--
archive_bitfield.result	4.26 KB	-rw-r--r--
archive_debug.result	355 B	-rw-r--r--
archive_gis.result	25.98 KB	-rw-r--r--
archive_no_symlink.result	882 B	-rw-r--r--
archive_plugin.result	531 B	-rw-r--r--
archive_symlink.result	2.3 KB	-rw-r--r--
auth_rpl.result	1.85 KB	-rw-r--r--
auto_increment.result	12.11 KB	-rw-r--r--
backup.result	4.29 KB	-rw-r--r--
bench_count_distinct.result	557 B	-rw-r--r--
big_test.require	17 B	-rw-r--r--
bigint.result	23.33 KB	-rw-r--r--
binary.result	6.09 KB	-rw-r--r--
binary_to_hex.result	5.87 KB	-rw-r--r--
binlog_tx_isolation.result	1.71 KB	-rw-r--r--
blackhole.result	613 B	-rw-r--r--
blackhole_plugin.result	565 B	-rw-r--r--
bool.result	2.67 KB	-rw-r--r--
bootstrap.result	525 B	-rw-r--r--
bug12368203.result	2.77 KB	-rw-r--r--
bug12427262.result	1.86 KB	-rw-r--r--
bug12969156.result	952 B	-rw-r--r--
bug17076131.result	322 B	-rw-r--r--
bug33509.result	2.31 KB	-rw-r--r--
bug39022.result	1.2 KB	-rw-r--r--
bug46080.result	669 B	-rw-r--r--
bug46261.result	353 B	-rw-r--r--
bug46760.result	1.26 KB	-rw-r--r--
bug47671.result	287 B	-rw-r--r--
bug58669.result	480 B	-rw-r--r--
bulk_replace.result	281 B	-rw-r--r--
cache_innodb.result	6.96 KB	-rw-r--r--
case.result	10.63 KB	-rw-r--r--
case_insensitive_file_system.r...	46 B	-rw-r--r--
case_sensitive_file_system.req...	47 B	-rw-r--r--
cast.result	16.27 KB	-rw-r--r--
change_user.result	2.82 KB	-rw-r--r--
charset.result	2.14 KB	-rw-r--r--
check.result	2.05 KB	-rw-r--r--
check_auto_permission.result	261 B	-rw-r--r--
check_var_limit.require	8 B	-rw-r--r--
client_xml.result	2.38 KB	-rw-r--r--
comment_column.result	141.21 KB	-rw-r--r--
comment_column2.result	355.68 KB	-rw-r--r--
comment_index.result	173.32 KB	-rw-r--r--
comment_table.result	28.95 KB	-rw-r--r--
comments.result	2.34 KB	-rw-r--r--
commit.result	14.83 KB	-rw-r--r--
commit_1innodb.result	19.59 KB	-rw-r--r--
compare.result	5.07 KB	-rw-r--r--
compress.result	66.24 KB	-rw-r--r--
concurrent_innodb_safelog.resu...	32.01 KB	-rw-r--r--
concurrent_innodb_unsafelog.re...	31.74 KB	-rw-r--r--
condition_filter.result	2.17 KB	-rw-r--r--
connect.result	11.02 KB	-rw-r--r--
connect_debug.result	1.46 KB	-rw-r--r--
consistent_snapshot.result	1.14 KB	-rw-r--r--
constraints.result	2.56 KB	-rw-r--r--
count_distinct.result	8.04 KB	-rw-r--r--
count_distinct2.result	2.21 KB	-rw-r--r--
count_distinct3.result	278 B	-rw-r--r--
create-big.result	7.6 KB	-rw-r--r--
create.result	71.42 KB	-rw-r--r--
create_not_windows.result	961 B	-rw-r--r--
create_select_tmp.result	846 B	-rw-r--r--
create_w_max_indexes_64.result	84.46 KB	-rw-r--r--
csv.result	59.08 KB	-rw-r--r--
csv_alter_table.result	1.17 KB	-rw-r--r--
csv_not_null.result	1.93 KB	-rw-r--r--
ctype_ascii.result	2.5 KB	-rw-r--r--
ctype_big5.result	34.28 KB	-rw-r--r--
ctype_binary.result	82.78 KB	-rw-r--r--
ctype_collate.result	18.3 KB	-rw-r--r--
ctype_cp1250_ch.result	22.29 KB	-rw-r--r--
ctype_cp1251.result	96.48 KB	-rw-r--r--
ctype_cp932.result	996 B	-rw-r--r--
ctype_cp932_binlog_row.result	734 B	-rw-r--r--
ctype_cp932_binlog_stm.result	251.36 KB	-rw-r--r--
ctype_create.result	3.12 KB	-rw-r--r--
ctype_errors.result	1.66 KB	-rw-r--r--
ctype_eucjpms.result	400.48 KB	-rw-r--r--
ctype_euckr.result	435.54 KB	-rw-r--r--
ctype_filename.result	324 B	-rw-r--r--
ctype_filesystem.result	341 B	-rw-r--r--
ctype_gb18030.result	69.33 KB	-rw-r--r--
ctype_gb18030_binlog.result	909 B	-rw-r--r--
ctype_gb18030_conversion.resul...	1.17 MB	-rw-r--r--
ctype_gb18030_encoding_cn.resu...	41.39 KB	-rw-r--r--
ctype_gb18030_encoding_utf8.re...	40.64 KB	-rw-r--r--
ctype_gb18030_ligatures.result	24.81 KB	-rw-r--r--
ctype_gb2312.result	33.73 KB	-rw-r--r--
ctype_gbk.result	50.07 KB	-rw-r--r--
ctype_gbk_binlog.result	633 B	-rw-r--r--
ctype_hebrew.result	254 B	-rw-r--r--
ctype_latin1.result	119.29 KB	-rw-r--r--
ctype_latin1_de.result	15.74 KB	-rw-r--r--
ctype_latin2.result	13.71 KB	-rw-r--r--
ctype_latin2_ch.result	25.11 KB	-rw-r--r--
ctype_ldml.result	28.13 KB	-rw-r--r--
ctype_like_range.result	80.89 KB	-rw-r--r--
ctype_many.result	41.87 KB	-rw-r--r--
ctype_mb.result	2.13 KB	-rw-r--r--
ctype_recoding.result	9.6 KB	-rw-r--r--
ctype_sjis.result	195.81 KB	-rw-r--r--
ctype_tis620.result	101.02 KB	-rw-r--r--
ctype_uca.result	155.4 KB	-rw-r--r--
ctype_ucs.result	143.96 KB	-rw-r--r--
ctype_ucs2_def.result	1.05 KB	-rw-r--r--
ctype_ujis.result	365.3 KB	-rw-r--r--
ctype_ujis_ucs2.result	548.6 KB	-rw-r--r--
ctype_utf16.result	47.3 KB	-rw-r--r--
ctype_utf16_def.result	273 B	-rw-r--r--
ctype_utf16_uca.result	72.47 KB	-rw-r--r--
ctype_utf16le.result	52.96 KB	-rw-r--r--
ctype_utf32.result	50.55 KB	-rw-r--r--
ctype_utf32_uca.result	75.29 KB	-rw-r--r--
ctype_utf8.result	169.69 KB	-rw-r--r--
ctype_utf8mb4.result	83.57 KB	-rw-r--r--
ctype_utf8mb4_heap.result	75.01 KB	-rw-r--r--
ctype_utf8mb4_innodb.result	77.85 KB	-rw-r--r--
ctype_utf8mb4_myisam.result	77.43 KB	-rw-r--r--
ctype_utf8mb4_uca.result	38.82 KB	-rw-r--r--
daemonize_opt.result	14 B	-rw-r--r--
datadir_permission.result	1.09 KB	-rw-r--r--
date_formats.result	28.69 KB	-rw-r--r--
ddl_i18n_koi8r.result	123.56 KB	-rw-r--r--
ddl_i18n_utf8.result	126.58 KB	-rw-r--r--
deadlock_innodb.result	2.35 KB	-rw-r--r--
debug_sync.result	12.18 KB	-rw-r--r--
debug_sync2.result	1.15 KB	-rw-r--r--
default.result	8.17 KB	-rw-r--r--
delete.result	22.7 KB	-rw-r--r--
delete_all_rows.result	2.26 KB	-rw-r--r--
deprecate_eof.result	8 KB	-rw-r--r--
deprecated_features.result	2.03 KB	-rw-r--r--
derived.result	172.18 KB	-rw-r--r--
dirty_close.result	345 B	-rw-r--r--
disabled_replication.result	4.82 KB	-rw-r--r--
disabled_storage_engines.resul...	3.28 KB	-rw-r--r--
disconnect_on_expired_password...	1.42 KB	-rw-r--r--
disconnect_on_expired_password...	1.49 KB	-rw-r--r--
distinct.result	80.43 KB	-rw-r--r--
drop-no_root.result	454 B	-rw-r--r--
drop.result	6.71 KB	-rw-r--r--
drop_debug.result	485 B	-rw-r--r--
ds_mrr-big.result	2.93 KB	-rw-r--r--
dynamic_tracing.result	813 B	-rw-r--r--
empty_table.result	229 B	-rw-r--r--
enable_cleartext_plugin.result	1.4 KB	-rw-r--r--
endspace.result	7.25 KB	-rw-r--r--
eq_range_idx_stat.result	6.56 KB	-rw-r--r--
error_simulation.result	3.17 KB	-rw-r--r--
errors.result	6.11 KB	-rw-r--r--
events_1.result	28.3 KB	-rw-r--r--
events_2.result	17.79 KB	-rw-r--r--
events_and_binlog.result	297 B	-rw-r--r--
events_bugs.result	40.94 KB	-rw-r--r--
events_embedded.result	86 B	-rw-r--r--
events_grant.result	10.33 KB	-rw-r--r--
events_logs_tests.result	2.37 KB	-rw-r--r--
events_microsec.result	810 B	-rw-r--r--
events_restart.result	4.36 KB	-rw-r--r--
events_scheduling.result	3.11 KB	-rw-r--r--
events_stress.result	2.39 KB	-rw-r--r--
events_time_zone.result	5.4 KB	-rw-r--r--
events_trans.result	3.61 KB	-rw-r--r--
events_trans_notembedded.resul...	1.88 KB	-rw-r--r--
examined_rows.result	6.73 KB	-rw-r--r--
execution_constants.result	1.03 KB	-rw-r--r--
explain.result	39.39 KB	-rw-r--r--
explain_for_connection_crash.r...	1004 B	-rw-r--r--
explain_for_connection_rqg_jso...	87.48 KB	-rw-r--r--
explain_for_connection_rqg_tra...	35.98 KB	-rw-r--r--
explain_for_connection_small_j...	25.46 KB	-rw-r--r--
explain_for_connection_small_t...	10.3 KB	-rw-r--r--
explain_json_all.result	98.36 KB	-rw-r--r--
explain_json_none.result	102.18 KB	-rw-r--r--
explain_other.result	42.23 KB	-rw-r--r--
file_contents.result	181 B	-rw-r--r--
filesort_debug.result	4.59 KB	-rw-r--r--
filesort_merge.result	2.02 KB	-rw-r--r--
filesort_pack.result	675 B	-rw-r--r--
filter_single_col_idx_big.resu...	38.05 KB	-rw-r--r--
filter_single_col_idx_small.re...	42.99 KB	-rw-r--r--
fix_priv_tables.result	1.96 KB	-rw-r--r--
flush.result	13.78 KB	-rw-r--r--
flush2.result	1.06 KB	-rw-r--r--
flush_block_commit.result	1.47 KB	-rw-r--r--
flush_block_commit_notembedded...	872 B	-rw-r--r--
flush_read_lock.result	73.94 KB	-rw-r--r--
flush_read_lock_kill.result	1.12 KB	-rw-r--r--
flush_table.result	12.33 KB	-rw-r--r--
foreign_key.result	4.84 KB	-rw-r--r--
fulltext.result	32.93 KB	-rw-r--r--
fulltext2.result	7.54 KB	-rw-r--r--
fulltext3.result	815 B	-rw-r--r--
fulltext_cache.result	2.67 KB	-rw-r--r--
fulltext_distinct.result	1.45 KB	-rw-r--r--
fulltext_left_join.result	4 KB	-rw-r--r--
fulltext_multi.result	766 B	-rw-r--r--
fulltext_order_by.result	6.05 KB	-rw-r--r--
fulltext_plugin.result	229 B	-rw-r--r--
fulltext_update.result	1.11 KB	-rw-r--r--
fulltext_var.result	1.3 KB	-rw-r--r--
func_aes.result	12.97 KB	-rw-r--r--
func_aes_cfb1.result	4.5 KB	-rw-r--r--
func_aes_cfb128.result	4.57 KB	-rw-r--r--
func_aes_cfb8.result	4.5 KB	-rw-r--r--
func_aes_kdf_hkdf.result	3.94 KB	-rw-r--r--
func_aes_kdf_pbkdf2_hmac.resul...	6.79 KB	-rw-r--r--
func_aes_misc.result	8.46 KB	-rw-r--r--
func_aes_ofb.result	4.47 KB	-rw-r--r--
func_analyse.result	23.09 KB	-rw-r--r--
func_bitwise_ops.result	80.6 KB	-rw-r--r--
func_compress.result	5.46 KB	-rw-r--r--
func_concat.result	4.37 KB	-rw-r--r--
func_crypt.result	2.05 KB	-rw-r--r--
func_date_add.result	3.74 KB	-rw-r--r--
func_default.result	1.45 KB	-rw-r--r--
func_des_encrypt.result	2.68 KB	-rw-r--r--
func_digest.result	122.35 KB	-rw-r--r--
func_encrypt.result	19.82 KB	-rw-r--r--
func_encrypt_nossl.result	7.75 KB	-rw-r--r--
func_encrypt_ucs2.result	1.21 KB	-rw-r--r--
func_equal.result	1.27 KB	-rw-r--r--
func_found_rows.result	285 B	-rw-r--r--
func_gconcat.result	49.9 KB	-rw-r--r--
func_group.result	66.46 KB	-rw-r--r--
func_group_innodb.result	6.07 KB	-rw-r--r--
func_group_innodb_16k.result	693 B	-rw-r--r--
func_if.result	6.29 KB	-rw-r--r--
func_in_all.result	49.27 KB	-rw-r--r--
func_in_icp.result	48.91 KB	-rw-r--r--
func_in_icp_mrr.result	49.21 KB	-rw-r--r--
func_in_mrr.result	48.93 KB	-rw-r--r--
func_in_mrr_cost.result	48.65 KB	-rw-r--r--
func_in_none.result	48.6 KB	-rw-r--r--
func_isnull.result	827 B	-rw-r--r--
func_like.result	7.78 KB	-rw-r--r--
func_math.result	42.22 KB	-rw-r--r--
func_misc.result	36.8 KB	-rw-r--r--
func_op.result	2.12 KB	-rw-r--r--
func_regexp.result	4.47 KB	-rw-r--r--
func_rollback.result	13.02 KB	-rw-r--r--
func_sapdb.result	12.49 KB	-rw-r--r--
func_set.result	8.9 KB	-rw-r--r--
func_str.result	174.94 KB	-rw-r--r--
func_str_debug.result	349 B	-rw-r--r--
func_str_no_ps.result	1.3 KB	-rw-r--r--
func_system.result	2.9 KB	-rw-r--r--
func_test.result	14.47 KB	-rw-r--r--
func_time.result	71.47 KB	-rw-r--r--
func_timestamp.result	518 B	-rw-r--r--
func_weight_string.result	11.56 KB	-rw-r--r--
function_defaults.result	109.58 KB	-rw-r--r--
gcc296.result	528 B	-rw-r--r--
get_diagnostics.result	28.42 KB	-rw-r--r--
get_table_share.result	20.56 KB	-rw-r--r--
gis-debug.result	10.1 KB	-rw-r--r--
gis-precise.result	162.17 KB	-rw-r--r--
gis-rt-precise.result	2.42 KB	-rw-r--r--
gis-rtree.result	102.08 KB	-rw-r--r--
gis.result	131.8 KB	-rw-r--r--
grant.result	121.17 KB	-rw-r--r--
grant2.result	36.52 KB	-rw-r--r--
grant3.result	8.41 KB	-rw-r--r--
grant4.result	9.36 KB	-rw-r--r--
grant_alter_user.result	40.96 KB	-rw-r--r--
grant_alter_user_qa.result	98.38 KB	-rw-r--r--
grant_cache.result	6.71 KB	-rw-r--r--
grant_debug.result	8.5 KB	-rw-r--r--
grant_explain_non_select.resul...	10.27 KB	-rw-r--r--
grant_lowercase_fs.result	1.2 KB	-rw-r--r--
grant_user_lock.result	16.9 KB	-rw-r--r--
grant_user_lock_qa.result	41.4 KB	-rw-r--r--
greedy_optimizer.result	632.59 KB	-rw-r--r--
greedy_search.result	31.05 KB	-rw-r--r--
group_by.result	120.07 KB	-rw-r--r--
group_by_fd_no_prot.result	81.15 KB	-rw-r--r--
group_by_fd_ps_prot.result	61.28 KB	-rw-r--r--
group_min_max.result	189.52 KB	-rw-r--r--
group_min_max_innodb.result	25.09 KB	-rw-r--r--
gtid_next_xa_binlog_off.result	30.9 KB	-rw-r--r--
gtids_anonymous_trxs_violation...	687 B	-rw-r--r--
handler_innodb.result	41.58 KB	-rw-r--r--
handler_myisam.result	45.48 KB	-rw-r--r--
handler_read_last.result	1.15 KB	-rw-r--r--
have_big5.require	79 B	-rw-r--r--
have_binlog_format_mixed.requi...	40 B	-rw-r--r--
have_binlog_format_row.require	38 B	-rw-r--r--
have_binlog_format_statement.r...	44 B	-rw-r--r--
have_compress.require	38 B	-rw-r--r--
have_cp1250_ch.require	79 B	-rw-r--r--
have_cp1251.require	84 B	-rw-r--r--
have_cp866.require	82 B	-rw-r--r--
have_cp932.require	83 B	-rw-r--r--
have_crypt.require	35 B	-rw-r--r--
have_debug.require	8 B	-rw-r--r--
have_debug_sync.require	13 B	-rw-r--r--
have_eucjpms.require	87 B	-rw-r--r--
have_euckr.require	81 B	-rw-r--r--
have_gb18030.require	87 B	-rw-r--r--
have_gb2312.require	84 B	-rw-r--r--
have_gbk.require	78 B	-rw-r--r--
have_geometry.require	38 B	-rw-r--r--
have_koi8r.require	81 B	-rw-r--r--
have_latin2_ch.require	78 B	-rw-r--r--
have_local_infile.require	36 B	-rw-r--r--
have_log_bin.require	31 B	-rw-r--r--
have_met_timezone.require	43 B	-rw-r--r--
have_moscow_leap_timezone.requ...	46 B	-rw-r--r--
have_mysql_upgrade.result	21 B	-rw-r--r--
have_ndb_debug.require	17 B	-rw-r--r--
have_ndb_extra.require	14 B	-rw-r--r--
have_ndbapi_examples.require	19 B	-rw-r--r--
have_nodebug.require	8 B	-rw-r--r--
have_optimizer_switch.require	19 B	-rw-r--r--
have_outfile.require	55 B	-rw-r--r--
have_partition.require	42 B	-rw-r--r--
have_perror.require	14 B	-rw-r--r--
have_profiling.require	39 B	-rw-r--r--
have_query_cache.require	41 B	-rw-r--r--
have_sjis.require	81 B	-rw-r--r--
have_ssl.require	33 B	-rw-r--r--
have_ssl_is_yes_or_disabled_on...	43 B	-rw-r--r--
have_statement_timeout.require	47 B	-rw-r--r--
have_symlink.require	37 B	-rw-r--r--
have_tis620.require	81 B	-rw-r--r--
have_ucs2.require	80 B	-rw-r--r--
have_ujis.require	81 B	-rw-r--r--
have_utf16.require	82 B	-rw-r--r--
have_utf32.require	82 B	-rw-r--r--
have_utf8.require	80 B	-rw-r--r--
have_utf8mb4.require	86 B	-rw-r--r--
having.result	24.34 KB	-rw-r--r--
heap.result	24.75 KB	-rw-r--r--
heap_auto_increment.result	894 B	-rw-r--r--
heap_btree.result	11.76 KB	-rw-r--r--
heap_hash.result	20.83 KB	-rw-r--r--
help.result	6.64 KB	-rw-r--r--
help_verbose.result	326 B	-rw-r--r--
host_cache_size_functionality....	3.32 KB	-rw-r--r--
ignore_strict.result	30.82 KB	-rw-r--r--
implicit_char_to_num_conversio...	6.44 KB	-rw-r--r--
implicit_commit.result	18.33 KB	-rw-r--r--
import_schema_mismatch.result	478 B	-rw-r--r--
index_merge_delete.result	25.04 KB	-rw-r--r--
index_merge_innodb.result	124.56 KB	-rw-r--r--
index_merge_insert-and-replace...	14.54 KB	-rw-r--r--
index_merge_intersect_dml.resu...	5.85 KB	-rw-r--r--
index_merge_myisam.result	144.52 KB	-rw-r--r--
index_merge_update.result	17.22 KB	-rw-r--r--
information_schema-big.result	2.81 KB	-rw-r--r--
information_schema.result	107.92 KB	-rw-r--r--
information_schema_chmod.resul...	182 B	-rw-r--r--
information_schema_db.result	9.98 KB	-rw-r--r--
information_schema_inno.result	4.33 KB	-rw-r--r--
information_schema_parameters....	22.49 KB	-rw-r--r--
information_schema_part.result	11.97 KB	-rw-r--r--
information_schema_routines.re...	30.81 KB	-rw-r--r--
init_connect.result	3.57 KB	-rw-r--r--
init_file.result	323 B	-rw-r--r--
initialize-bug20350099.result	1.04 KB	-rw-r--r--
initialize-bug20504142.result	1.17 KB	-rw-r--r--
initialize-bug21335821.result	895 B	-rw-r--r--
initialize-errors.result	578 B	-rw-r--r--
initialize-ignore-db.result	742 B	-rw-r--r--
initialize-init-acls.result	1.12 KB	-rw-r--r--
initialize-sha256.result	1.09 KB	-rw-r--r--
initialize.result	3.89 KB	-rw-r--r--
initialize_gtid.result	465 B	-rw-r--r--
innodb_deadlock.result	1.25 KB	-rw-r--r--
innodb_disabled.result	208 B	-rw-r--r--
innodb_explain_json_non_select...	293.06 KB	-rw-r--r--
innodb_explain_json_non_select...	299.29 KB	-rw-r--r--
innodb_explain_non_select_all....	136.52 KB	-rw-r--r--
innodb_explain_non_select_none...	136.37 KB	-rw-r--r--
innodb_icp.result	32.81 KB	-rw-r--r--
innodb_icp_all.result	32.88 KB	-rw-r--r--
innodb_icp_none.result	32.72 KB	-rw-r--r--
innodb_ignore_builtin.result	354 B	-rw-r--r--
innodb_log_file_size_functiona...	2.79 KB	-rw-r--r--
innodb_mrr.result	25.74 KB	-rw-r--r--
innodb_mrr_all.result	25.88 KB	-rw-r--r--
innodb_mrr_cost.result	25.71 KB	-rw-r--r--
innodb_mrr_cost_all.result	25.85 KB	-rw-r--r--
innodb_mrr_cost_icp.result	25.82 KB	-rw-r--r--
innodb_mrr_icp.result	25.85 KB	-rw-r--r--
innodb_mrr_none.result	25.66 KB	-rw-r--r--
innodb_mysql_lock.result	4.05 KB	-rw-r--r--
innodb_mysql_lock2.result	21.4 KB	-rw-r--r--
innodb_mysql_sync.result	25.87 KB	-rw-r--r--
innodb_pk_extension_off.result	20.33 KB	-rw-r--r--
innodb_pk_extension_on.result	20.58 KB	-rw-r--r--
innodb_recovery_with_upper_cas...	1.21 KB	-rw-r--r--
insert.result	30.75 KB	-rw-r--r--
insert_debug.result	1.29 KB	-rw-r--r--
insert_notembedded.result	5.58 KB	-rw-r--r--
insert_select.result	15.34 KB	-rw-r--r--
insert_update.result	18.89 KB	-rw-r--r--
installdb-bad-cipher.result	128 B	-rw-r--r--
internal_tmp_disk_storage_engi...	146.97 KB	-rw-r--r--
ipv4_as_ipv6.result	23.83 KB	-rw-r--r--
ipv6.result	13.89 KB	-rw-r--r--
is_debug_build.require	32 B	-rw-r--r--
is_deprecation.result	558 B	-rw-r--r--
is_embedded.require	16 B	-rw-r--r--
isam.result	15.65 KB	-rw-r--r--
join.result	55.68 KB	-rw-r--r--
join_cache_bka.result	108.89 KB	-rw-r--r--
join_cache_bka_nixbnl.result	108.17 KB	-rw-r--r--
join_cache_bkaunique.result	109.32 KB	-rw-r--r--
join_cache_bnl.result	107.23 KB	-rw-r--r--
join_cache_nojb.result	106.25 KB	-rw-r--r--
join_crash.result	4.53 KB	-rw-r--r--
join_file_handler.result	1.04 KB	-rw-r--r--
join_nested.result	86.66 KB	-rw-r--r--
join_nested_bka.result	92.75 KB	-rw-r--r--
join_nested_bka_nixbnl.result	89.71 KB	-rw-r--r--
join_optimizer.result	2.54 KB	-rw-r--r--
join_outer.result	96.52 KB	-rw-r--r--
join_outer_bka.result	96.89 KB	-rw-r--r--
join_outer_bka_nixbnl.result	94.69 KB	-rw-r--r--
join_outer_innodb.result	3.27 KB	-rw-r--r--
key.result	22.58 KB	-rw-r--r--
key_cache.result	11.88 KB	-rw-r--r--
key_diff.result	1.15 KB	-rw-r--r--
key_primary.result	890 B	-rw-r--r--
keywords.result	6.63 KB	-rw-r--r--
kill.result	8.88 KB	-rw-r--r--
kill_debug.result	1.58 KB	-rw-r--r--
limit.result	8.31 KB	-rw-r--r--
loaddata.result	20.8 KB	-rw-r--r--
loaddata_autocom_innodb.result	651 B	-rw-r--r--
loadxml.result	2.7 KB	-rw-r--r--
locale.result	5.59 KB	-rw-r--r--
lock.result	14.09 KB	-rw-r--r--
lock_multi.result	63.2 KB	-rw-r--r--
lock_multi_bug38499.result	964 B	-rw-r--r--
lock_multi_bug38691.result	812 B	-rw-r--r--
lock_sync.result	27.44 KB	-rw-r--r--
lock_tables_lost_commit.result	150 B	-rw-r--r--
locking_service.result	31.09 KB	-rw-r--r--
log_errchk.result	663 B	-rw-r--r--
log_state.result	9.66 KB	-rw-r--r--
log_state_bug33693.result	111 B	-rw-r--r--
log_tables-big.result	996 B	-rw-r--r--
log_tables.result	41.81 KB	-rw-r--r--
log_tables_debug.result	770 B	-rw-r--r--
log_tables_upgrade.result	2.35 KB	-rw-r--r--
log_timestamps.result	71 B	-rw-r--r--
long_tmpdir.result	99 B	-rw-r--r--
lowercase0.require	45 B	-rw-r--r--
lowercase1.require	45 B	-rw-r--r--
lowercase2.require	45 B	-rw-r--r--
lowercase_fs_off.result	3.46 KB	-rw-r--r--
lowercase_fs_on.result	75 B	-rw-r--r--
lowercase_mixed_tmpdir.result	162 B	-rw-r--r--
lowercase_mixed_tmpdir_innodb....	190 B	-rw-r--r--
lowercase_table.result	3.24 KB	-rw-r--r--
lowercase_table2.result	8.66 KB	-rw-r--r--
lowercase_table4.result	4.63 KB	-rw-r--r--
lowercase_table5.result	673 B	-rw-r--r--
lowercase_table_grant.result	2.01 KB	-rw-r--r--
lowercase_table_qcache.result	847 B	-rw-r--r--
lowercase_utf8.result	173 B	-rw-r--r--
lowercase_view.result	10.21 KB	-rw-r--r--
m_i_db.result	15.5 KB	-rw-r--r--
max_statement_time.result	12.81 KB	-rw-r--r--
mdl_sync.result	113.92 KB	-rw-r--r--
mdl_tablespace.result	34.72 KB	-rw-r--r--
merge-big.result	728 B	-rw-r--r--
merge.result	100.14 KB	-rw-r--r--
merge_innodb.result	1.31 KB	-rw-r--r--
merge_mmap.result	3.33 KB	-rw-r--r--
metadata.result	17.03 KB	-rw-r--r--
mix2_myisam.result	66.36 KB	-rw-r--r--
mix2_myisam_ucs2.result	15.35 KB	-rw-r--r--
multi_plugin_load.result	347 B	-rw-r--r--
multi_plugin_load_add.result	455 B	-rw-r--r--
multi_plugin_load_add2.result	455 B	-rw-r--r--
multi_statement.result	563 B	-rw-r--r--
multi_update.result	26.47 KB	-rw-r--r--
multi_update2.result	1016 B	-rw-r--r--
multi_update_innodb.result	4.52 KB	-rw-r--r--
multi_update_tiny_hash.result	1.5 KB	-rw-r--r--
myisam-blob.result	1.41 KB	-rw-r--r--
myisam-system.result	394 B	-rw-r--r--
myisam.result	96.84 KB	-rw-r--r--
myisam_crash_before_flush_keys...	1.15 KB	-rw-r--r--

Code Editor : index_merge_myisam.result

#---------------- Index merge test 1 -------------------------------------------
# (Start of test file: index_merge1.inc)
SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
drop table if exists t0, t1, t2, t3, t4;
# Create and fill a table with simple keys
create table t0
(
key1 int not null,
key2 int not null,
key3 int not null,
key4 int not null,
key5 int not null,
key6 int not null,
key7 int not null,
key8 int not null,
INDEX i1(key1),
INDEX i2(key2),
INDEX i3(key3),
INDEX i4(key4),
INDEX i5(key5),
INDEX i6(key6),
INDEX i7(key7),
INDEX i8(key8)
);
analyze table t0;
Table	Op	Msg_type	Msg_text
test.t0	analyze	status	OK
# 1. One index
explain select * from t0 where key1 < 3 or key1 > 1020;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	range	i1	i1	4	NULL	78	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key1` > 1020))
# 2. Simple cases
explain
select * from t0 where key1 < 3 or key2 > 1020;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	78	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` > 1020))
select * from t0 where key1 < 3 or key2 > 1020;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
1021	1021	1021	1021	1021	1021	1021	3
1022	1022	1022	1022	1022	1022	1022	2
1023	1023	1023	1023	1023	1023	1023	1
1024	1024	1024	1024	1024	1024	1024	0
explain select * from t0 where key1 < 2 or key2 <3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	5	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 3))
explain
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	11	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` > 30) and (`test`.`t0`.`key1` < 35)) or ((`test`.`t0`.`key2` > 32) and (`test`.`t0`.`key2` < 40)))
# Bug#21277: InnoDB, wrong result set, index_merge strategy,
#            second index not evaluated
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
key1	key2	key3	key4	key5	key6	key7	key8
31	31	31	31	31	31	31	993
32	32	32	32	32	32	32	992
33	33	33	33	33	33	33	991
34	34	34	34	34	34	34	990
35	35	35	35	35	35	35	989
36	36	36	36	36	36	36	988
37	37	37	37	37	37	37	987
38	38	38	38	38	38	38	986
39	39	39	39	39	39	39	985
# 3. Check that index_merge doesn't break "ignore/force/use index"
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ALL	i1	NULL	NULL	NULL	1024	55.55	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` IGNORE INDEX (`i2`) where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4))
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ref	i1,i2,i3	i3	4	const	1	55.55	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key3` = 50) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4)))
explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	5	1.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` USE INDEX (`i2`) USE INDEX (`i1`) where ((`test`.`t0`.`key3` = 50) and ((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 3)))
explain select * from t0 where (key1 > 1 or key2  > 2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ALL	i1,i2	NULL	NULL	NULL	1024	55.55	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` > 1) or (`test`.`t0`.`key2` > 2))
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2  > 2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	1024	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` FORCE INDEX (`i2`) FORCE INDEX (`i1`) where ((`test`.`t0`.`key1` > 1) or (`test`.`t0`.`key2` > 2))
# 4. Check if conjuncts are grouped by keyuse
explain
select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or
(key1>10 and key1<12) or (key2>100 and key2<102);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	8	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 3) or ((`test`.`t0`.`key1` > 5) and (`test`.`t0`.`key1` < 7)) or ((`test`.`t0`.`key1` > 10) and (`test`.`t0`.`key1` < 12)) or ((`test`.`t0`.`key2` > 100) and (`test`.`t0`.`key2` < 102)))
# 5. Check index_merge with conjuncts that are always true/false
#    verify fallback to "range" if there is only one non-confluent condition
explain select * from t0 where key2 = 45 or key1 <=> null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	range	i1,i2	i2	4	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 45) or (`test`.`t0`.`key1` <=> NULL))
explain select * from t0 where key2 = 45 or key1 is not null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ALL	i1,i2	NULL	NULL	NULL	1024	90.01	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 45) or (`test`.`t0`.`key1` is not null))
explain select * from t0 where key2 = 45 or key1 is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ref	i2	i2	4	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (`test`.`t0`.`key2` = 45)
#   the last conj. is always false and will be discarded
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i2,i3,i4	i2,i3	4,4	NULL	2	100.00	Using union(i2,i3); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 10) or (`test`.`t0`.`key3` = 3) or (`test`.`t0`.`key4` <=> NULL))
#   the last conj. is always true and will cause 'all' scan
explain select * from t0 where key2=10 or key3=3 or key4 is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i2,i3	i2,i3	4,4	NULL	2	100.00	Using union(i2,i3); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key2` = 10) or (`test`.`t0`.`key3` = 3))
#   some more complicated cases
explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or
(key3=10) or (key4 <=> null);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i4	i2,i3	4,4	NULL	3	100.00	Using sort_union(i2,i3); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1` from `test`.`t0` where ((`test`.`t0`.`key1` <=> NULL) or (`test`.`t0`.`key2` < 2) or (`test`.`t0`.`key3` = 10) or (`test`.`t0`.`key4` <=> NULL))
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
(key3=10) or (key4 <=> null);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i3,i4	i1,i3	4,4	NULL	6	100.00	Using sort_union(i1,i3); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1` from `test`.`t0` where ((`test`.`t0`.`key1` <=> NULL) or (`test`.`t0`.`key1` < 5) or (`test`.`t0`.`key3` = 10) or (`test`.`t0`.`key4` <=> NULL))
# 6.Several ways to do index_merge, (ignored) index_merge vs. range
explain select * from t0 where
(key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i4,i5,i6	i1,i2	4,4	NULL	4	30.86	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 2)) and ((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key4` < 3)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 5)))
explain
select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3	i1,i2	4,4	NULL	6	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 4)) and ((`test`.`t0`.`key1` < 5) or (`test`.`t0`.`key3` < 3)))
select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
3	3	3	3	3	3	3	1021
explain select * from t0 where
(key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i4,i5,i6	i5,i6	4,4	NULL	4	30.86	Using sort_union(i5,i6); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 2)) and ((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key4` < 3)) and ((`test`.`t0`.`key5` < 2) or (`test`.`t0`.`key6` < 2)))
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 70);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3	i1,i2	4,4	NULL	6	6.25	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3)) and (`test`.`t0`.`key3` < 70))
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 1000);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3	i1,i2	4,4	NULL	6	89.16	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where (((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3)) and (`test`.`t0`.`key3` < 1000))
# 7. Complex cases
#   tree_or(List<SEL_IMERGE>, range SEL_TREE).
explain select * from t0 where
((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3))
or
key2 > 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ALL	i1,i2,i3	NULL	NULL	NULL	1024	53.90	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3)) and ((`test`.`t0`.`key2` < 4) or (`test`.`t0`.`key3` < 3))) or (`test`.`t0`.`key2` > 4))
explain select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3))
or
key1 < 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3	i1,i2	4,4	NULL	9	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key1` < 4) or (`test`.`t0`.`key2` < 4)) and ((`test`.`t0`.`key2` < 4) or (`test`.`t0`.`key3` < 3))) or (`test`.`t0`.`key1` < 5))
select * from t0 where
((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3))
or
key1 < 5;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
3	3	3	3	3	3	3	1021
4	4	4	4	4	4	4	1020
#   tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
explain select * from t0 where
((key1 < 2 or key2 < 2) and (key3 <4 or key5 < 3))
or
((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i5,i6,i7,i8	i1,i2,i5,i6	4,4,4,4	NULL	10	100.00	Using sort_union(i1,i2,i5,i6); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key1` < 2) or (`test`.`t0`.`key2` < 2)) and ((`test`.`t0`.`key3` < 4) or (`test`.`t0`.`key5` < 3))) or (((`test`.`t0`.`key5` < 3) or (`test`.`t0`.`key6` < 3)) and ((`test`.`t0`.`key7` < 3) or (`test`.`t0`.`key8` < 3))))
explain select * from t0 where
((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3))
or
((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i5,i6,i7,i8	i3,i5,i7,i8	4,4,4,4	NULL	16	100.00	Using sort_union(i3,i5,i7,i8); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3))) or (((`test`.`t0`.`key7` < 5) or (`test`.`t0`.`key8` < 3)) and ((`test`.`t0`.`key5` < 4) or (`test`.`t0`.`key6` < 4))))
explain select * from t0 where
((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4))
or
((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i5,i6	i3,i5	4,4	NULL	8	100.00	Using sort_union(i3,i5); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4))) or (((`test`.`t0`.`key3` < 4) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 3))))
explain select * from t0 where
((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3))
or
(((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i5,i6,i7	i3,i5	4,4	NULL	8	100.00	Using sort_union(i3,i5); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 4) or (`test`.`t0`.`key5` < 3)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3))) or ((((`test`.`t0`.`key3` < 5) and (`test`.`t0`.`key7` < 5)) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 4) or (`test`.`t0`.`key6` < 4))))
explain select * from t0 where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ALL	i1,i2,i3,i5,i6	NULL	NULL	NULL	1024	52.20	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((((`test`.`t0`.`key3` < 5) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 4) or (`test`.`t0`.`key2` < 4))) or (((`test`.`t0`.`key3` > 5) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 6))))
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3))
or
((key3 >4 or key5 < 2) and (key5 < 5 or key6 < 4));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2,i3,i5,i6	i3,i5	4,4	NULL	1024	100.00	Using sort_union(i3,i5); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` FORCE INDEX (`i6`) FORCE INDEX (`i5`) FORCE INDEX (`i4`) FORCE INDEX (`i3`) FORCE INDEX (`i2`) FORCE INDEX (`i1`) where ((((`test`.`t0`.`key3` < 3) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 3))) or (((`test`.`t0`.`key3` > 4) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 4))))
# Can't merge any indexes here (predicate on key3 is always true)
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ALL	i1,i2,i3,i5,i6	NULL	NULL	NULL	1024	52.20	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` FORCE INDEX (`i6`) FORCE INDEX (`i5`) FORCE INDEX (`i4`) FORCE INDEX (`i3`) FORCE INDEX (`i2`) FORCE INDEX (`i1`) where ((((`test`.`t0`.`key3` < 5) or (`test`.`t0`.`key5` < 4)) and ((`test`.`t0`.`key1` < 4) or (`test`.`t0`.`key2` < 4))) or (((`test`.`t0`.`key3` >= 5) or (`test`.`t0`.`key5` < 2)) and ((`test`.`t0`.`key5` < 5) or (`test`.`t0`.`key6` < 6))))
# 8. Verify that "order by" after index merge uses filesort
select * from t0 where key1 < 3 or key8 < 2 order by key1;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
1023	1023	1023	1023	1023	1023	1023	1
1024	1024	1024	1024	1024	1024	1024	0
explain
select * from t0 where key1 < 3 or key8 < 2 order by key1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i8	i1,i8	4,4	NULL	6	100.00	Using sort_union(i1,i8); Using where; Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key8` < 2)) order by `test`.`t0`.`key1`
# 9. Check that index_merge cost is compared to 'index' where possible
create table t2 like t0;
insert into t2 select * from t0;
alter table t2 add index i1_3(key1, key3);
alter table t2 add index i2_3(key2, key3);
alter table t2 drop index i1;
alter table t2 drop index i2;
alter table t2 add index i321(key3, key2, key1);
#   index_merge vs 'index', index_merge is better.
explain select key3 from t2 where key1 = 100 or key2 = 100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index_merge	i1_3,i2_3	i1_3,i2_3	4,4	NULL	2	100.00	Using sort_union(i1_3,i2_3); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1` = 100) or (`test`.`t2`.`key2` = 100))
#   index_merge vs 'index', 'index' is better.
explain select key3 from t2 where key1 <100 or key2 < 100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	i1_3,i2_3	i321	12	NULL	1024	55.55	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`key3` AS `key3` from `test`.`t2` where ((`test`.`t2`.`key1` < 100) or (`test`.`t2`.`key2` < 100))
#   index_merge vs 'all', index_merge is better.
explain select key7 from t2 where key1 <100 or key2 < 100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	i1_3,i2_3	NULL	NULL	NULL	1024	55.55	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`key7` AS `key7` from `test`.`t2` where ((`test`.`t2`.`key1` < 100) or (`test`.`t2`.`key2` < 100))
# 10. Multipart keys.
create table t4 (
key1a int not null,
key1b int not null,
key2  int not null,
key2_1 int not null,
key2_2 int not null,
key3  int not null,
index i1a (key1a, key1b),
index i1b (key1b, key1a),
index i2_1(key2, key2_1),
index i2_2(key2, key2_1)
);
Warnings:
Warning	1831	Duplicate index 'i2_2' defined on the table 'test.t4'. This is deprecated and will be disallowed in a future release.
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
#   the following will be handled by index_merge:
select * from t4 where key1a = 3 or key1b = 4;
key1a	key1b	key2	key2_1	key2_2	key3
3	3	0	3	3	3
4	4	0	4	4	4
explain select * from t4 where key1a = 3 or key1b = 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	NULL	index_merge	i1a,i1b	i1a,i1b	4,4	NULL	2	100.00	Using sort_union(i1a,i1b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key1a` = 3) or (`test`.`t4`.`key1b` = 4))
#   and the following will not
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	NULL	ref	i2_1,i2_2	i2_1	4	const	10	19.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key2` = 1) and ((`test`.`t4`.`key2_1` = 1) or (`test`.`t4`.`key3` = 5)))
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	NULL	ref	i2_1,i2_2	i2_1	4	const	10	19.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key2` = 1) and ((`test`.`t4`.`key2_1` = 1) or (`test`.`t4`.`key2_2` = 5)))
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1024	19.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`key1a` AS `key1a`,`test`.`t4`.`key1b` AS `key1b`,`test`.`t4`.`key2` AS `key2`,`test`.`t4`.`key2_1` AS `key2_1`,`test`.`t4`.`key2_2` AS `key2_2`,`test`.`t4`.`key3` AS `key3` from `test`.`t4` where ((`test`.`t4`.`key2_1` = 1) or (`test`.`t4`.`key2_2` = 5))
# 11. Multitable selects
create table t1 like t0;
insert into t1 select * from t0;
#  index_merge on first table in join
explain select * from t0 left join t1 on (t0.key1=t1.key1)
where t0.key1=3 or t0.key2=4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	2	100.00	Using union(i1,i2); Using where
1	SIMPLE	t1	NULL	ref	i1	i1	4	test.t0.key1	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` left join `test`.`t1` on((`test`.`t1`.`key1` = `test`.`t0`.`key1`)) where ((`test`.`t0`.`key1` = 3) or (`test`.`t0`.`key2` = 4))
select * from t0 left join t1 on (t0.key1=t1.key1)
where t0.key1=3 or t0.key2=4;
key1	key2	key3	key4	key5	key6	key7	key8	key1	key2	key3	key4	key5	key6	key7	key8
3	3	3	3	3	3	3	1021	3	3	3	3	3	3	3	1021
4	4	4	4	4	4	4	1020	4	4	4	4	4	4	4	1020
explain
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	2	100.00	Using union(i1,i2); Using where
1	SIMPLE	t1	NULL	ref	i1	i1	4	test.t0.key1	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t1`.`key1` = `test`.`t0`.`key1`) and ((`test`.`t0`.`key1` = 3) or (`test`.`t0`.`key2` = 4)))
#  index_merge vs. ref
explain
select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2=4) and t1.key1<200;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	2	100.00	Using union(i1,i2); Using where
1	SIMPLE	t1	NULL	ref	i1	i1	4	test.t0.key1	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t1`.`key1` = `test`.`t0`.`key1`) and ((`test`.`t0`.`key1` = 3) or (`test`.`t0`.`key2` = 4)) and (`test`.`t0`.`key1` < 200))
#  index_merge vs. ref
explain
select * from t0,t1 where (t0.key1=t1.key1) and
(t0.key1=3 or t0.key2<4) and t1.key1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ref	i1,i2	i1	4	const	1	5.00	Using where
1	SIMPLE	t1	NULL	ref	i1	i1	4	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t0`.`key1` = 2) and (`test`.`t1`.`key1` = 2) and (`test`.`t0`.`key2` < 4))
#  index_merge on second table in join
explain select * from t0,t1 where t0.key1 = 5 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	ref	i1	i1	4	const	1	100.00	NULL
1	SIMPLE	t1	NULL	index_merge	i1,i8	i1,i8	4,4	NULL	2	100.00	Using union(i1,i8); Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t0`.`key1` = 5) and ((`test`.`t1`.`key1` = 5) or (`test`.`t1`.`key8` = 5)))
# Fix for bug#1974
explain select * from t0,t1 where t0.key1 < 3 and
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	range	i1	i1	4	NULL	3	100.00	Using index condition
1	SIMPLE	t1	NULL	ALL	i1,i8	NULL	NULL	NULL	1024	0.20	Range checked for each record (index map: 0x81)
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t0` join `test`.`t1` where ((`test`.`t0`.`key1` < 3) and ((`test`.`t1`.`key1` = `test`.`t0`.`key1`) or (`test`.`t1`.`key8` = `test`.`t0`.`key1`)))
#  index_merge inside union
explain select * from t1 where key1=3 or key2=4
union select * from t1 where key1<4 or key3=5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	2	100.00	Using union(i1,i2); Using where
2	UNION	t1	NULL	index_merge	i1,i3	i1,i3	4,4	NULL	5	100.00	Using sort_union(i1,i3); Using where
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` = 3) or (`test`.`t1`.`key2` = 4)) union /* select#2 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key1` < 4) or (`test`.`t1`.`key3` = 5))
#  index merge in subselect
explain select * from (select * from t1 where key1 = 3 or key2 =3) as z where key8 >5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i2,i8	i1,i2	4,4	NULL	2	99.51	Using union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`key5` AS `key5`,`test`.`t1`.`key6` AS `key6`,`test`.`t1`.`key7` AS `key7`,`test`.`t1`.`key8` AS `key8` from `test`.`t1` where ((`test`.`t1`.`key8` > 5) and ((`test`.`t1`.`key1` = 3) or (`test`.`t1`.`key2` = 3)))
# 12. check for long index_merges.
create table t3 like t0;
insert into t3 select * from t0;
alter table t3 add key9 int not null, add index i9(key9);
alter table t3 add keyA int not null, add index iA(keyA);
alter table t3 add keyB int not null, add index iB(keyB);
alter table t3 add keyC int not null, add index iC(keyC);
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
explain select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	index_merge	i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC	i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC	4,4,4,4,4,4,4,4,4,4,4,4	NULL	12	100.00	Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`key1` AS `key1`,`test`.`t3`.`key2` AS `key2`,`test`.`t3`.`key3` AS `key3`,`test`.`t3`.`key4` AS `key4`,`test`.`t3`.`key5` AS `key5`,`test`.`t3`.`key6` AS `key6`,`test`.`t3`.`key7` AS `key7`,`test`.`t3`.`key8` AS `key8`,`test`.`t3`.`key9` AS `key9`,`test`.`t3`.`keyA` AS `keyA`,`test`.`t3`.`keyB` AS `keyB`,`test`.`t3`.`keyC` AS `keyC` from `test`.`t3` where ((`test`.`t3`.`key1` = 1) or (`test`.`t3`.`key2` = 2) or (`test`.`t3`.`key3` = 3) or (`test`.`t3`.`key4` = 4) or (`test`.`t3`.`key5` = 5) or (`test`.`t3`.`key6` = 6) or (`test`.`t3`.`key7` = 7) or (`test`.`t3`.`key8` = 8) or (`test`.`t3`.`key9` = 9) or (`test`.`t3`.`keyA` = 10) or (`test`.`t3`.`keyB` = 11) or (`test`.`t3`.`keyC` = 12))
select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
key1	key2	key3	key4	key5	key6	key7	key8	key9	keyA	keyB	keyC
1	1	1	1	1	1	1	1023	1	1	1	1
2	2	2	2	2	2	2	1022	2	2	2	2
3	3	3	3	3	3	3	1021	3	3	3	3
4	4	4	4	4	4	4	1020	4	4	4	4
5	5	5	5	5	5	5	1019	5	5	5	5
6	6	6	6	6	6	6	1018	6	6	6	6
7	7	7	7	7	7	7	1017	7	7	7	7
9	9	9	9	9	9	9	1015	9	9	9	9
10	10	10	10	10	10	10	1014	10	10	10	10
11	11	11	11	11	11	11	1013	11	11	11	11
12	12	12	12	12	12	12	1012	12	12	12	12
1016	1016	1016	1016	1016	1016	1016	8	1016	1016	1016	1016
# Test for Bug#3183
explain select * from t0 where key1 < 3 or key2 < 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	7	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`key1` AS `key1`,`test`.`t0`.`key2` AS `key2`,`test`.`t0`.`key3` AS `key3`,`test`.`t0`.`key4` AS `key4`,`test`.`t0`.`key5` AS `key5`,`test`.`t0`.`key6` AS `key6`,`test`.`t0`.`key7` AS `key7`,`test`.`t0`.`key8` AS `key8` from `test`.`t0` where ((`test`.`t0`.`key1` < 3) or (`test`.`t0`.`key2` < 4))
# Bug#21277: InnoDB, wrong result set, index_merge strategy,
#            second index not evaluated
select * from t0 where key1 < 3 or key2 < 4;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	1023
2	2	2	2	2	2	2	1022
3	3	3	3	3	3	3	1021
update t0 set key8=123 where key1 < 3 or key2 < 4;
# Bug#21277: InnoDB, wrong result set, index_merge strategy,
#            second index not evaluated
select * from t0 where key1 < 3 or key2 < 4;
key1	key2	key3	key4	key5	key6	key7	key8
1	1	1	1	1	1	1	123
2	2	2	2	2	2	2	123
3	3	3	3	3	3	3	123
delete from t0 where key1 < 3 or key2 < 4;
select * from t0 where key1 < 3 or key2 < 4;
key1	key2	key3	key4	key5	key6	key7	key8
select count(*) from t0;
count(*)
1021
# Test for BUG#4177
drop table t4;
create table t4 (a int);
insert into t4 values (1),(4),(3);
set @save_join_buffer_size=@@join_buffer_size;
set join_buffer_size= 4096;
explain select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
where (a.key1 < 500000 or a.key2 < 3)
and   (b.key1 < 500000 or b.key2 < 3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	1013	100.00	Using sort_union(i1,i2); Using where
1	SIMPLE	b	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	1013	100.00	Using sort_union(i1,i2); Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select max((((((((((`test`.`a`.`key1` + `test`.`b`.`key1`) + `test`.`a`.`key2`) + `test`.`b`.`key2`) + `test`.`a`.`key3`) + `test`.`b`.`key3`) + `test`.`a`.`key4`) + `test`.`b`.`key4`) + `test`.`a`.`key5`) + `test`.`b`.`key5`)) AS `max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)` from `test`.`t0` `a` FORCE INDEX (`i2`) FORCE INDEX (`i1`) join `test`.`t0` `b` FORCE INDEX (`i2`) FORCE INDEX (`i1`) where (((`test`.`a`.`key1` < 500000) or (`test`.`a`.`key2` < 3)) and ((`test`.`b`.`key1` < 500000) or (`test`.`b`.`key2` < 3)))
select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
where (a.key1 < 500000 or a.key2 < 3)
and   (b.key1 < 500000 or b.key2 < 3);
max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
10240
update t0 set key1=1;
explain select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
where (a.key1 = 1 or a.key2 = 1)
and   (b.key1 = 1 or b.key2 = 1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	1020	100.00	Using union(i1,i2); Using where
1	SIMPLE	b	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	1020	100.00	Using union(i1,i2); Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select max((((((((((`test`.`a`.`key1` + `test`.`b`.`key1`) + `test`.`a`.`key2`) + `test`.`b`.`key2`) + `test`.`a`.`key3`) + `test`.`b`.`key3`) + `test`.`a`.`key4`) + `test`.`b`.`key4`) + `test`.`a`.`key5`) + `test`.`b`.`key5`)) AS `max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)` from `test`.`t0` `a` FORCE INDEX (`i2`) FORCE INDEX (`i1`) join `test`.`t0` `b` FORCE INDEX (`i2`) FORCE INDEX (`i1`) where (((`test`.`a`.`key1` = 1) or (`test`.`a`.`key2` = 1)) and ((`test`.`b`.`key1` = 1) or (`test`.`b`.`key2` = 1)))
select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
from t0 as a force index(i1,i2), t0 as b force index (i1,i2)
where (a.key1 = 1 or a.key2 = 1)
and   (b.key1 = 1 or b.key2 = 1);
max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
8194
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
# The next query will not use index i7 in intersection if the OS doesn't
# support file sizes > 2GB. (ha_myisam::ref_length depends on this and index
# scan cost estimates depend on ha_myisam::ref_length)
explain select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
from t0 as a, t0 as b
where (a.key1 = 1 and a.key2 = 1 and a.key3 = 1 and a.key4=1 and a.key5=1 and a.key6=1 and a.key7 = 1 or a.key8=1)
and (b.key1 = 1 and b.key2 = 1 and b.key3 = 1 and b.key4=1 and b.key5=1 and b.key6=1 and b.key7 = 1 or b.key8=1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
1	SIMPLE	b	NULL	index_merge	i1,i2,i3,i4,i5,i6,i7?,i8	i2,i3,i4,i5,i6,i7?,i8	X	NULL	#	#	Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select max((((((((((`test`.`a`.`key1` + `test`.`b`.`key1`) + `test`.`a`.`key2`) + `test`.`b`.`key2`) + `test`.`a`.`key3`) + `test`.`b`.`key3`) + `test`.`a`.`key4`) + `test`.`b`.`key4`) + `test`.`a`.`key5`) + `test`.`b`.`key5`)) AS `max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)` from `test`.`t0` `a` join `test`.`t0` `b` where ((((`test`.`a`.`key7` = 1) and (`test`.`a`.`key6` = 1) and (`test`.`a`.`key5` = 1) and (`test`.`a`.`key4` = 1) and (`test`.`a`.`key3` = 1) and (`test`.`a`.`key2` = 1) and (`test`.`a`.`key1` = 1)) or (`test`.`a`.`key8` = 1)) and (((`test`.`b`.`key7` = 1) and (`test`.`b`.`key6` = 1) and (`test`.`b`.`key5` = 1) and (`test`.`b`.`key4` = 1) and (`test`.`b`.`key3` = 1) and (`test`.`b`.`key2` = 1) and (`test`.`b`.`key1` = 1)) or (`test`.`b`.`key8` = 1)))
select max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
from t0 as a, t0 as b
where (a.key1 = 1 and a.key2 = 1 and a.key3 = 1 and a.key4=1 and a.key5=1 and a.key6=1 and a.key7 = 1 or a.key8=1)
and (b.key1 = 1 and b.key2 = 1 and b.key3 = 1 and b.key4=1 and b.key5=1 and b.key6=1 and b.key7 = 1 or b.key8=1);
max(a.key1 + b.key1 + a.key2 + b.key2 + a.key3 + b.key3 + a.key4 + b.key4 + a.key5 + b.key5)
8186
set join_buffer_size= @save_join_buffer_size;
# Test for BUG#4177 ends
drop table t0, t1, t2, t3, t4;
# BUG#16166
CREATE TABLE t1 (
cola char(3) not null, colb char(3) not null,  filler char(200),
key(cola), key(colb)
);
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
select count(*) from t1;
count(*)
8704
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	cola,colb	cola,colb	3,3	NULL	32	100.00	Using intersect(cola,colb); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	cola,colb	cola,colb	3,3	NULL	32	100.00	Using intersect(cola,colb); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`cola` AS `cola`,`test`.`t1`.`colb` AS `colb`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` FORCE INDEX (`colb`) FORCE INDEX (`cola`) where ((`test`.`t1`.`colb` = 'bar') and (`test`.`t1`.`cola` = 'foo'))
drop table t1;
#
# BUG#17314: Index_merge/intersection not choosen by the
#            optimizer for MERGE tables
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int, b int,
filler1 char(200), filler2 char(200),
key(a),key(b)
);
insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
create table t2 like t1;
create table t3 (
a int, b int,
filler1 char(200), filler2 char(200),
key(a),key(b)
) engine=merge union=(t1,t2);
explain select * from t1 where a=1 and b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	#	100.00	Using intersect(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (`test`.`t1`.`a` = 1))
explain select * from t3 where a=1 and b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	index_merge	a,b	a,b	5,5	NULL	#	100.00	Using intersect(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler1` AS `filler1`,`test`.`t3`.`filler2` AS `filler2` from `test`.`t3` where ((`test`.`t3`.`b` = 1) and (`test`.`t3`.`a` = 1))
drop table t3;
drop table t0, t1, t2;
#
# BUG#20256 - LOCK WRITE - MyISAM
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
INSERT INTO t2(a,b) VALUES
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(1,2);
LOCK TABLES t1 WRITE, t2 WRITE;
INSERT INTO t2(a,b) VALUES(1,2);
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
a
1
1
UNLOCK TABLES;
DROP TABLE t1, t2;
#
# BUG#29740: HA_KEY_SCAN_NOT_ROR wasn't set for HEAP engine
# 
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`filler` char(200) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
insert into t1 values
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 
(18, 'filler', 18), (19, 'filler', 19), (4, '5      ', 0), (5, '4      ', 0), 
(4, '4      ', 0), (4, 'qq     ', 5), (5, 'qq     ', 4), (4, 'zz     ', 4);
create table t2(
`a` int(11) DEFAULT NULL,
`filler` char(200) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY USING BTREE (`a`),
KEY USING BTREE (`b`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
insert into t2 select * from t1;
must use sort-union rather than union:
explain select * from t1 where a=4 or b=4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	#	100.00	Using sort_union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`filler` AS `filler`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 4) or (`test`.`t1`.`b` = 4))
select * from t1 where a=4 or b=4;
a	filler	b
4	4	0
4	5	0
4	filler	4
4	filler	4
4	qq	5
4	zz	4
5	qq	4
select * from t1 ignore index(a,b) where a=4 or b=4;
a	filler	b
4	4	0
4	5	0
4	filler	4
4	filler	4
4	qq	5
4	zz	4
5	qq	4
must use union, not sort-union:
explain select * from t2 where a=4 or b=4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index_merge	a,b	a,b	5,5	NULL	#	100.00	Using union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`filler` AS `filler`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`a` = 4) or (`test`.`t2`.`b` = 4))
select * from t2 where a=4 or b=4;
a	filler	b
4	4	0
4	5	0
4	filler	4
4	filler	4
4	qq	5
4	zz	4
5	qq	4
drop table t1, t2;
#
# Bug #37943: Reproducible mysqld crash/sigsegv in sel_trees_can_be_ored
#
CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
KEY b(b), KEY a(a));
INSERT INTO t1 VALUES ('y',''), ('z','');
SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR 
(a='pure-S') OR (a='DE80337a') OR (a='DE80799');
b	a
	y
	z
DROP TABLE t1;
#
# BUG#40974: Incorrect query results when using clause
#            evaluated using range check
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int);
insert into t1 values (1),(2);
create table t2(a int, b int);
insert into t2 values (1,1), (2, 1000);
create table t3 (a int, b int, filler char(100), key(a), key(b));
insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
insert into t3 values (1,1,'data');
insert into t3 values (1,1,'data');
The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
explain select * from t1 
where exists (select 1 from t2, t3 
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
2	DEPENDENT SUBQUERY	t3	NULL	ALL	a,b	NULL	NULL	NULL	1002	77.50	Range checked for each record (index map: 0x3)
Warnings:
Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(/* select#2 */ select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t3`.`a` = `test`.`t2`.`b`) or (`test`.`t3`.`b` = `test`.`t2`.`b`) or (`test`.`t3`.`b` = (`test`.`t2`.`b` + 1)))))
select * from t1 
where exists (select 1 from t2, t3 
where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
a
1
2
drop table t0, t1, t2, t3;
#
# BUG#44810: index merge and order by with low sort_buffer_size 
# crashes server!
#
CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128));
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
SET SESSION sort_buffer_size=1;
Warnings:
Warning	1292	Truncated incorrect sort_buffer_size value: '1'
EXPLAIN 
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 
ORDER BY a,b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	131,131	NULL	64	100.00	Using sort_union(a,b); Using where; Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`b`) FORCE INDEX (`a`) where ((`test`.`t1`.`a` like 'a%') or (`test`.`t1`.`b` like 'b%')) order by `test`.`t1`.`a`,`test`.`t1`.`b`
# we don't actually care about the result : we're checking if it crashes
SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 
ORDER BY a,b;
SET SESSION sort_buffer_size=DEFAULT;
DROP TABLE t1;
End of 5.0 tests
#---------------- ROR-index_merge tests -----------------------
# (Start of test file: index_merge_ror.inc)
SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
drop table if exists  t0,t1,t2;
create table t1
(
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
st_a int not null default 0,
swt1a int not null default 0,
swt2a int not null default 0,
st_b int not null default 0,
swt1b int not null default 0,
swt2b int not null default 0,
/* fields/keys for row retrieval tests */
key1 int,
key2 int,
key3 int,
key4 int,
/* make rows much bigger then keys */
filler1 char (200),
filler2 char (200),
filler3 char (200),
filler4 char (200),
filler5 char (200),
filler6 char (200),
/* order of keys is important */
key sta_swt12a(st_a,swt1a,swt2a),
key sta_swt1a(st_a,swt1a),
key sta_swt2a(st_a,swt2a),
key sta_swt21a(st_a,swt2a,swt1a),
key st_a(st_a),
key stb_swt1a_2b(st_b,swt1b,swt2a),
key stb_swt1b(st_b,swt1b),
key st_b(st_b),
key(key1),
key(key2),
key(key3),
key(key4)
) ;
create table t0 as select * from t1;
# Printing of many insert into t0 values (....) disabled.
alter table t1 disable keys;
# Printing of many insert into t1 select .... from t0 disabled.
# Printing of many insert into t1 (...) values (....) disabled.
alter table t1 enable keys;
select count(*) from t1;
count(*)
64801
# One row results tests for cases where a single row matches all conditions
explain select key1,key2 from t1 where key1=100 and key2=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2	key1,key2	5,5	NULL	77	100.00	Using intersect(key1,key2); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
select key1,key2 from t1 where key1=100 and key2=100;
key1	key2
100	100
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key1,key2,key3,key4	5,5,5,5	NULL	154	100.00	Using union(intersect(key1,key2),intersect(key3,key4)); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
explain format=json select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "411.69"
    },
    "table": {
      "table_name": "t1",
      "access_type": "index_merge",
      "possible_keys": [
        "key1",
        "key2",
        "key3",
        "key4"
      ],
      "key": "union(intersect(key1,key2),intersect(key3,key4))",
      "key_length": "5,5,5,5",
      "rows_examined_per_scan": 154,
      "rows_produced_per_join": 154,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "380.90",
        "eval_cost": "30.80",
        "prefix_cost": "411.70",
        "data_read_per_join": "187K"
      },
      "used_columns": [
        "key1",
        "key2",
        "key3",
        "key4",
        "filler1"
      ],
      "attached_condition": "(((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))"
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1	key2	key3	key4	filler1
100	100	100	100	key1-key2-key3-key4
# Several-rows results
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
#  ROR-intersection, not covering
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2	key1,key2	5,5	NULL	77	100.00	Using intersect(key1,key2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
select key1,key2,filler1 from t1 where key1=100 and key2=100;
key1	key2	filler1
100	100	key1-key2-key3-key4
100	100	key1-key2
#  ROR-intersection, covering
explain select key1,key2 from t1 where key1=100 and key2=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2	key1,key2	5,5	NULL	77	100.00	Using intersect(key1,key2); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
select key1,key2 from t1 where key1=100 and key2=100;
key1	key2
100	100
100	100
#  ROR-union of ROR-intersections
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key1,key2,key3,key4	5,5,5,5	NULL	154	100.00	Using union(intersect(key1,key2),intersect(key3,key4)); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1	key2	key3	key4
100	100	100	100
100	100	-1	-1
-1	-1	100	100
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key1,key2,key3,key4	5,5,5,5	NULL	154	100.00	Using union(intersect(key1,key2),intersect(key3,key4)); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1	key2	key3	key4	filler1
100	100	100	100	key1-key2-key3-key4
100	100	-1	-1	key1-key2
-1	-1	100	100	key4-key3
#  3-way ROR-intersection
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3	key1,key2,key3	5,5,5	NULL	2	100.00	Using intersect(key1,key2,key3); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3` from `test`.`t1` where ((`test`.`t1`.`key3` = 100) and (`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
key1	key2	key3
100	100	100
#  ROR-union(ROR-intersection, ROR-range)
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3	key1,key2,key3	5,5,5	NULL	83	100.00	Using union(intersect(key1,key2),key3); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or (`test`.`t1`.`key3` = 101))
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
key1	key2	key3	key4	filler1
100	100	100	100	key1-key2-key3-key4
100	100	-1	-1	key1-key2
101	101	101	101	key1234-101
# Run some ROR updates/deletes
select key1,key2, filler1 from t1 where key1=100 and key2=100;
key1	key2	filler1
100	100	key1-key2-key3-key4
100	100	key1-key2
update t1 set filler1='to be deleted' where key1=100 and key2=100;
update t1 set key1=200,key2=200 where key1=100 and key2=100;
delete from t1 where key1=200 and key2=200;
select key1,key2,filler1 from t1 where key2=100 and key2=200;
key1	key2	filler1
# ROR-union(ROR-intersection) with one of ROR-intersection giving empty
# results
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key1,key2,key3,key4	5,5,5,5	NULL	152	100.00	Using union(intersect(key1,key2),intersect(key3,key4)); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1	key2	key3	key4	filler1
-1	-1	100	100	key4-key3
delete from t1 where key3=100 and key4=100;
# ROR-union with all ROR-intersections giving empty results
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key1,key2,key3,key4	5,5,5,5	NULL	152	100.00	Using union(intersect(key1,key2),intersect(key3,key4)); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
key1	key2	key3	key4	filler1
# ROR-intersection with empty result
explain select key1,key2 from t1 where key1=100 and key2=100;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2	key1,key2	5,5	NULL	76	100.00	Using intersect(key1,key2); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100))
select key1,key2 from t1 where key1=100 and key2=100;
key1	key2
# ROR-union tests with various cases.
#  All scans returning duplicate rows:
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key3,key1,key2,key4	5,5,5,5	NULL	136	100.00	Using union(key3,intersect(key1,key2),key4); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where ((`test`.`t1`.`key3` = 200) or ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or (`test`.`t1`.`key4` = 200))
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
key1	key2	key3	key4	filler1
100	100	200	200	key1-key2-key3-key4-3
100	100	200	200	key1-key2-key3-key4-2
100	100	200	200	key1-key2-key3-key4-1
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key3,key1,key2,key4	5,5,5,5	NULL	146	100.00	Using union(key3,intersect(key1,key2),key4); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where ((`test`.`t1`.`key3` = 200) or ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or (`test`.`t1`.`key4` = 200))
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
key1	key2	key3	key4	filler1
100	100	200	200	key1-key2-key3-key4-3
100	100	200	200	key1-key2-key3-key4-2
100	100	200	200	key1-key2-key3-key4-1
-1	-1	-1	200	key4
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2,key3,key4	key3,key1,key2,key4	5,5,5,5	NULL	156	100.00	Using union(key3,intersect(key1,key2),key4); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where ((`test`.`t1`.`key3` = 200) or ((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or (`test`.`t1`.`key4` = 200))
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
key1	key2	key3	key4	filler1
100	100	200	200	key1-key2-key3-key4-3
100	100	200	200	key1-key2-key3-key4-2
100	100	200	200	key1-key2-key3-key4-1
-1	-1	-1	200	key4
-1	-1	200	-1	key3
##
## Optimizer tests
##
# Check that the shortest key is used for ROR-intersection, covering and non-covering.
explain select * from t1 where st_a=1 and st_b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	st_a,st_b	4,4	NULL	3515	100.00	Using intersect(st_a,st_b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` where ((`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`st_a` = 1))
explain select st_a,st_b from t1 where st_a=1 and st_b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	st_a,st_b	4,4	NULL	3515	100.00	Using intersect(st_a,st_b); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`st_b` AS `st_b` from `test`.`t1` where ((`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`st_a` = 1))
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b	st_b	4	const	15093	24.02	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a` from `test`.`t1` IGNORE INDEX (`st_a`) where ((`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`st_a` = 1))
# Do many tests
# Check that keys that don't improve selectivity are skipped.
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a	sta_swt21a	12	const,const,const	971	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` where ((`test`.`t1`.`swt2a` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	stb_swt1a_2b,stb_swt1b,st_b	stb_swt1a_2b	8	const,const	3879	10.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` where ((`test`.`t1`.`swt2b` = 1) and (`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1))
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt12a,stb_swt1a_2b	12,12	NULL	58	10.00	Using intersect(sta_swt12a,stb_swt1a_2b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` where ((`test`.`t1`.`swt2b` = 1) and (`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt2a` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b	sta_swt12a,stb_swt1b	12,8	NULL	58	10.00	Using intersect(sta_swt12a,stb_swt1b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` IGNORE INDEX (`stb_swt1a_2b`) IGNORE INDEX (`sta_swt21a`) where ((`test`.`t1`.`swt2b` = 1) and (`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt2a` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b	sta_swt1a,stb_swt1b,sta_swt2a	8,8,8	NULL	57	10.00	Using intersect(sta_swt1a,stb_swt1b,sta_swt2a); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` IGNORE INDEX (`stb_swt1a_2b`) IGNORE INDEX (`sta_swt12a`) IGNORE INDEX (`sta_swt21a`) where ((`test`.`t1`.`swt2b` = 1) and (`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt2a` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt1a,sta_swt2a,st_a,st_b	sta_swt1a,sta_swt2a,st_b	8,8,4	NULL	223	1.00	Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` IGNORE INDEX (`stb_swt1b`) IGNORE INDEX (`stb_swt1a_2b`) IGNORE INDEX (`sta_swt12a`) IGNORE INDEX (`sta_swt21a`) where ((`test`.`t1`.`swt2b` = 1) and (`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt2a` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select * from t1
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt12a,stb_swt1a_2b	12,12	NULL	58	100.00	Using intersect(sta_swt12a,stb_swt1a_2b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` where ((`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt2a` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select * from t1
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	232	100.00	Using intersect(sta_swt1a,stb_swt1b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a`,`test`.`t1`.`swt1a` AS `swt1a`,`test`.`t1`.`swt2a` AS `swt2a`,`test`.`t1`.`st_b` AS `st_b`,`test`.`t1`.`swt1b` AS `swt1b`,`test`.`t1`.`swt2b` AS `swt2b`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2`,`test`.`t1`.`filler3` AS `filler3`,`test`.`t1`.`filler4` AS `filler4`,`test`.`t1`.`filler5` AS `filler5`,`test`.`t1`.`filler6` AS `filler6` from `test`.`t1` where ((`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select st_a from t1
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	232	100.00	Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a` from `test`.`t1` where ((`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
explain select st_a from t1
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b	sta_swt1a,stb_swt1b	8,8	NULL	232	100.00	Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`st_a` AS `st_a` from `test`.`t1` where ((`test`.`t1`.`swt1b` = 1) and (`test`.`t1`.`st_b` = 1) and (`test`.`t1`.`swt1a` = 1) and (`test`.`t1`.`st_a` = 1))
drop table t0,t1;
# 'Partially' covered fields test
create table t2 (
a char(10),
b char(10),
filler1 char(255),
filler2 char(255),
key(a(5)),
key(b(5))
);
select count(a) from t2 where a='BBBBBBBB';
count(a)
4
select count(a) from t2 where b='BBBBBBBB';
count(a)
4
# BUG#1:
expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
id	select_type	ta_or_ba_or_ble	pa_or_brtitions	type	possia_or_ble_keys	key	key_len	ref	rows	filtered	Extra_or_b
1	SIMPLE	t2	NULL	ref	a_or_b,a_or_b	a_or_b	6	const	4	12.50	Using where
Warnings:
Note	1003	/* select#1 */ select count(`test`.`t2`.`a_or_b`) AS `count(a_or_b)` from `test`.`t2` where ((`test`.`t2`.`a_or_b` = 'AAAAAAAA') a_or_bnd (`test`.`t2`.`a_or_b` = 'AAAAAAAA'))
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
count(a)
4
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
count(a)
4
insert into t2 values ('ab', 'ab', 'uh', 'oh');
explain select a from t2 where a='ab';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ref	a	a	6	const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a` = 'ab')
drop table t2;
#
# BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI';
#             try to repair it
#
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
KEY(c1), KEY(c2), KEY(c3));
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
INSERT INTO t1 VALUES(0,0,0);
CREATE TABLE t2(c1 int);
INSERT INTO t2 VALUES(1);
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
SELECT * FROM t1;
c1	c2	c3
DROP TABLE t1,t2;
#---------------- Index merge test 2 -------------------------------------------
# (Start of test file: index_merge2.inc)
SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
drop table if exists t1,t2;
create table t1
(
key1 int not null,
key2 int not null,
INDEX i1(key1),
INDEX i2(key2)
);
# No primary key
explain select * from t1 where key1 < 5 or key2 > 197;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	10	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key1` < 5) or (`test`.`t1`.`key2` > 197))
select * from t1 where key1 < 5 or key2 > 197;
key1	key2
0	200
1	199
2	198
3	197
4	196
explain select * from t1 where key1 < 3 or key2 > 195;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	10	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 195))
select * from t1 where key1 < 3 or key2 > 195;
key1	key2
0	200
1	199
2	198
3	197
4	196
# Primary key as case-sensitive string with \0s.
# also make primary key be longer then max. index length of MyISAM.
alter table t1 add str1 char (255) not null,
add zeroval int not null default 0,
add str2 char (255) not null,
add str3 char (255) not null;
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
alter table t1 add primary key (str1, zeroval, str2, str3);
explain select * from t1 where key1 < 5 or key2 > 197;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	10	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`str1` AS `str1`,`test`.`t1`.`zeroval` AS `zeroval`,`test`.`t1`.`str2` AS `str2`,`test`.`t1`.`str3` AS `str3` from `test`.`t1` where ((`test`.`t1`.`key1` < 5) or (`test`.`t1`.`key2` > 197))
select * from t1 where key1 < 5 or key2 > 197;
key1	key2	str1	zeroval	str2	str3
0	200	aaa	0	bbb	200-0_a
1	199	aaa	0	bbb	199-0_A
2	198	aaa	0	bbb	198-1_a
3	197	aaa	0	bbb	197-1_A
4	196	aaa	0	bbb	196-2_a
explain select * from t1 where key1 < 3 or key2 > 195;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i2	i1,i2	4,4	NULL	10	100.00	Using sort_union(i1,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`str1` AS `str1`,`test`.`t1`.`zeroval` AS `zeroval`,`test`.`t1`.`str2` AS `str2`,`test`.`t1`.`str3` AS `str3` from `test`.`t1` where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` > 195))
select * from t1 where key1 < 3 or key2 > 195;
key1	key2	str1	zeroval	str2	str3
0	200	aaa	0	bbb	200-0_a
1	199	aaa	0	bbb	199-0_A
2	198	aaa	0	bbb	198-1_a
3	197	aaa	0	bbb	197-1_A
4	196	aaa	0	bbb	196-2_a
# Test for BUG#5401
drop table t1;
create table t1 (
pk    integer not null auto_increment primary key,
key1  integer,
key2  integer not null,
filler char  (200),
index (key1),
index (key2)
);
show warnings;
Level	Code	Message
explain select pk from t1 where key1 = 1 and key2 = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1,key2	key1	5	const	4	26.67	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where ((`test`.`t1`.`key2` = 1) and (`test`.`t1`.`key1` = 1))
select pk from t1 where key2 = 1 and key1 = 1;
pk
26
27
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
pk
26
27
# More tests for BUG#5401.
drop table t1;
create table t1 (
pk int primary key auto_increment,
key1a  int,
key2a  int,
key1b  int,
key2b  int,
dummy1 int,
dummy2 int,
dummy3 int,
dummy4 int,
key3a  int,
key3b  int,
filler1 char (200),
index i1(key1a, key1b),
index i2(key2a, key2b),
index i3(key3a, key3b)
);
create table t2 (a int);
insert into t2 values (0),(1),(2),(3),(4),(NULL);
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select count(*) from t1;
count(*)
5184
explain select count(*) from t1 where
key1a = 2 and key1b is null and  key2a = 2 and key2b is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i2	i1,i2	10,10	NULL	2	100.00	Using intersect(i1,i2); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`key2a` = 2) and (`test`.`t1`.`key1a` = 2) and isnull(`test`.`t1`.`key1b`) and isnull(`test`.`t1`.`key2b`))
select count(*) from t1 where
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
count(*)
4
explain select count(*) from t1 where
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i1,i3	i1,i3	10,10	NULL	2	100.00	Using intersect(i1,i3); Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where ((`test`.`t1`.`key3a` = 2) and (`test`.`t1`.`key1a` = 2) and isnull(`test`.`t1`.`key1b`) and isnull(`test`.`t1`.`key3b`))
select count(*) from t1 where
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
count(*)
4
drop table t1,t2;
# Test for BUG#8441
create table t1 (
id1 int,
id2 date ,
index idx2 (id1,id2),
index idx1 (id2)
);
insert into t1 values(1,'20040101'), (2,'20040102');
select * from t1  where id1 = 1  and id2= '20040101';
id1	id2
1	2004-01-01
drop table t1;
# Test for BUG#12720
drop view if exists v1;
CREATE TABLE t1 (
`oid` int(11) unsigned NOT NULL auto_increment,
`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
`fk_wochentag` int(11) unsigned NOT NULL,
`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
`geloescht` tinyint(4) NOT NULL,
`version` int(5) NOT NULL,
PRIMARY KEY  (`oid`),
KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
KEY `fk_wochentag` (`fk_wochentag`),
KEY `ix_version` (`version`)
) DEFAULT CHARSET=latin1;
insert  into t1 values
(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
create view v1 as
select
zeit1.oid AS oid,
zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
zeit1.fk_wochentag AS fk_wochentag,
zeit1.uhrzeit_von AS uhrzeit_von,
zeit1.uhrzeit_bis AS uhrzeit_bis,
zeit1.geloescht AS geloescht,
zeit1.version AS version
from
t1 zeit1
where
(zeit1.version =
(select max(zeit2.version) AS `max(version)`
   from t1 zeit2
where
((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
(zeit1.fk_wochentag = zeit2.fk_wochentag) and
(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
)
)
)
and (zeit1.geloescht = 0);
select * from v1 where oid = 21;
oid	fk_bbk_niederlassung	fk_wochentag	uhrzeit_von	uhrzeit_bis	geloescht	version
21	7	1	08:00:00	13:00:00	0	1
drop view v1;
drop table t1;
CREATE TABLE t1(
t_cpac varchar(2) NOT NULL,
t_vers varchar(4) NOT NULL,
t_rele varchar(2) NOT NULL,
t_cust varchar(4) NOT NULL,
filler1 char(250) default NULL,
filler2 char(250) default NULL,
PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
KEY IX_5 (t_vers,t_rele,t_cust)
);
insert into t1 values
('tm','2.5 ','a ','    ','',''), ('tm','2.5U','a ','stnd','',''),
('da','3.3 ','b ','    ','',''), ('da','3.3U','b ','stnd','',''),
('tl','7.6 ','a ','    ','',''), ('tt','7.6 ','a ','    ','',''),
('bc','B61 ','a ','    ','',''), ('bp','B61 ','a ','    ','',''),
('ca','B61 ','a ','    ','',''), ('ci','B61 ','a ','    ','',''),
('cp','B61 ','a ','    ','',''), ('dm','B61 ','a ','    ','',''),
('ec','B61 ','a ','    ','',''), ('ed','B61 ','a ','    ','',''),
('fm','B61 ','a ','    ','',''), ('nt','B61 ','a ','    ','',''),
('qm','B61 ','a ','    ','',''), ('tc','B61 ','a ','    ','',''),
('td','B61 ','a ','    ','',''), ('tf','B61 ','a ','    ','',''),
('tg','B61 ','a ','    ','',''), ('ti','B61 ','a ','    ','',''),
('tp','B61 ','a ','    ','',''), ('ts','B61 ','a ','    ','',''),
('wh','B61 ','a ','    ','',''), ('bc','B61U','a ','stnd','',''),
('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
('wh','B61U','a ','stnd','','');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t_cpac` varchar(2) NOT NULL,
  `t_vers` varchar(4) NOT NULL,
  `t_rele` varchar(2) NOT NULL,
  `t_cust` varchar(4) NOT NULL,
  `filler1` char(250) DEFAULT NULL,
  `filler2` char(250) DEFAULT NULL,
  PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
  UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
  KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
t_vers	t_rele	t_cust	filler1
7.6 	a 	    	
7.6 	a 	    	
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
  and t_rele='a' and t_cust = ' ';
t_vers	t_rele	t_cust	filler1
7.6 	a 	    	
7.6 	a 	    	
drop table t1;
# BUG#19021: Crash in index_merge/ROR-intersection optimizer under
# specific circumstances.
create table t1 (
pk int(11) not null auto_increment,
a int(11) not null default '0',
b int(11) not null default '0',
c int(11) not null default '0',
filler1 datetime, filler2 varchar(15),
filler3 longtext,
kp1 varchar(4), kp2 varchar(7),
kp3 varchar(2), kp4 varchar(4),
kp5 varchar(7),
filler4 char(1),
primary key (pk),
key idx1(a,b,c),
key idx2(c),
key idx3(kp1,kp2,kp3,kp4,kp5)
) default charset=latin1;
set @fill=NULL;
SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND  kp5 = 'R        ';
COUNT(*)
1
drop table t1;
# BUG#21277: Index Merge/sort_union: wrong query results
create table t1
(
key1 int not null, 
key2 int not null default 0,
key3 int not null default 0
);
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
set @d=8;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
insert into t1 (key1) select key1+@d from t1;
set @d=@d*2;
alter table t1 add index i2(key2);
alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	i2,i3	i3,i2	4,4	NULL	11	100.00	Using sort_union(i3,i2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3` from `test`.`t1` where (((`test`.`t1`.`key3` > 30) and (`test`.`t1`.`key3` < 35)) or ((`test`.`t1`.`key2` > 32) and (`test`.`t1`.`key2` < 40)))
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
key1	key2	key3
31	31	31
32	32	32
33	33	33
34	34	34
35	35	35
36	36	36
37	37	37
38	38	38
39	39	39
drop table t1;
#
# Bug#56423: Different count with SELECT and CREATE SELECT queries
#
CREATE TABLE t1 (
a INT,
b INT,
c INT,
d INT,
PRIMARY KEY (a),
KEY (c),
KEY bd (b,d)
);
INSERT INTO t1 VALUES
(1, 0, 1, 0),
(2, 1, 1, 1),
(3, 1, 1, 1),
(4, 0, 1, 1);
EXPLAIN
SELECT a
FROM t1
WHERE c = 1 AND b = 1 AND d = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	c,bd	bd	10	const,const	2	75.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`d` = 1) and (`test`.`t1`.`b` = 1) and (`test`.`t1`.`c` = 1))
CREATE TABLE t2 ( a INT )
SELECT a
FROM t1
WHERE c = 1 AND b = 1 AND d = 1;
SELECT * FROM t2;
a
2
3
DROP TABLE t1, t2;
CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
a	b
1	2
1	2
1	2
1	2
DROP TABLE t1;
# Code coverage of fix.
CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
INSERT INTO t1 (b) VALUES (1);
UPDATE t1 SET b = 2 WHERE a = 1;
SELECT * FROM t1;
a	b
1	2
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
INSERT INTO t2 (b) VALUES ('a');
UPDATE t2 SET b = 'b' WHERE a = 1;
SELECT * FROM t2;
a	b
1	b
DROP TABLE t1, t2;
#
# BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN
#               HANDLER::MULTI_RANGE_READ_INFO_CONST
#
CREATE TABLE t1 (
pk INT NOT NULL,
col_int_key INT NOT NULL,
col_varchar_key VARCHAR(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b');
EXPLAIN
SELECT col_int_key
FROM t1
WHERE col_varchar_key >= 'l' OR 
(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') 
AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	PRIMARY,col_int_key,col_varchar_key	NULL	NULL	NULL	2	78.12	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`col_int_key` AS `col_int_key` from `test`.`t1` where ((`test`.`t1`.`col_varchar_key` >= 'l') or (((`test`.`t1`.`pk` between 141 and 141) or (`test`.`t1`.`col_varchar_key` <> 'l')) and ((`test`.`t1`.`pk` between 141 and 141) or (`test`.`t1`.`col_int_key` > 141))))
SELECT col_int_key
FROM t1
WHERE col_varchar_key >= 'l' OR 
(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') 
AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141)));
col_int_key
DROP TABLE t1;
#---------------- 2-sweeps read Index merge test 2 -------------------------------
# (Start of test file: index_merge_2sweeps.inc)
SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
drop table if exists t1;
create table t1 (
pk int primary key,
key1 int,
key2 int,
filler char(200),
filler2 char(200),
index(key1),
index(key2)
);
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
pk	key1	key2	filler	filler2
10	10	10	filler-data	filler-data-2
9	9	9	filler-data	filler-data-2
8	8	8	filler-data	filler-data-2
7	7	7	filler-data	filler-data-2
6	6	6	filler-data	filler-data-2
5	5	5	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
set @maxv=1000;
select * from t1 where
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
or key1=18 or key1=60;
pk	key1	key2	filler	filler2
1000	1000	1000	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
60	60	60	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
18	18	18	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
1	1	1	filler-data	filler-data-2
select * from t1 where
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
or key1 < 3 or key1 > @maxv-11;
pk	key1	key2	filler	filler2
1000	1000	1000	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
990	990	990	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
1	1	1	filler-data	filler-data-2
select * from t1 where
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
or
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
pk	key1	key2	filler	filler2
1000	1000	1000	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
1	1	1	filler-data	filler-data-2
select * from t1 where
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
or
(key1 < 5) or (key1 > @maxv-10);
pk	key1	key2	filler	filler2
1000	1000	1000	filler-data	filler-data-2
999	999	999	filler-data	filler-data-2
998	998	998	filler-data	filler-data-2
997	997	997	filler-data	filler-data-2
996	996	996	filler-data	filler-data-2
995	995	995	filler-data	filler-data-2
994	994	994	filler-data	filler-data-2
993	993	993	filler-data	filler-data-2
992	992	992	filler-data	filler-data-2
991	991	991	filler-data	filler-data-2
54	54	54	filler-data	filler-data-2
53	53	53	filler-data	filler-data-2
52	52	52	filler-data	filler-data-2
51	51	51	filler-data	filler-data-2
50	50	50	filler-data	filler-data-2
14	14	14	filler-data	filler-data-2
13	13	13	filler-data	filler-data-2
12	12	12	filler-data	filler-data-2
11	11	11	filler-data	filler-data-2
4	4	4	filler-data	filler-data-2
3	3	3	filler-data	filler-data-2
2	2	2	filler-data	filler-data-2
1	1	1	filler-data	filler-data-2
drop table t1;
#---------------- Clustered PK ROR-index_merge tests -----------------------------
# (Start of test file: index_merge_ror_cpk.inc)
SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
drop table if exists  t1;
create table t1
(
pk1 int not null,
pk2 int not null,
key1 int not null,
key2 int not null,
pktail1ok  int not null,
pktail2ok  int not null,
pktail3bad int not null,
pktail4bad int not null,
pktail5bad int not null,
pk2copy int not null,
badkey  int not null,
filler1 char (200),
filler2 char (200),
key (key1),
key (key2),
/* keys with tails from CPK members */
key (pktail1ok, pk1),
key (pktail2ok, pk1, pk2),
key (pktail3bad, pk2, pk1),
key (pktail4bad, pk1, pk2copy),
key (pktail5bad, pk1, pk2, pk2copy),
primary key (pk1, pk2)
);
# Verify that range scan on CPK is ROR
# (use index_intersection because it is impossible to check that for index union)
# Column 9, rows, can change depending on innodb-page-size.
explain select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,key1	PRIMARY	8	NULL	ROWS	#	Using index condition; Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 0) and (`test`.`t1`.`pk1` = 1) and (`test`.`t1`.`pk2` < 80))
# CPK scan + 1 ROR range scan is a special case
select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
pk1	pk2	key1	key2	pktail1ok	pktail2ok	pktail3bad	pktail4bad	pktail5bad	pk2copy	badkey	filler1	filler2
1	10	0	0	0	0	0	0	0	10	0	filler-data-10	filler2
1	11	0	0	0	0	0	0	0	11	0	filler-data-11	filler2
1	12	0	0	0	0	0	0	0	12	0	filler-data-12	filler2
1	13	0	0	0	0	0	0	0	13	0	filler-data-13	filler2
1	14	0	0	0	0	0	0	0	14	0	filler-data-14	filler2
1	15	0	0	0	0	0	0	0	15	0	filler-data-15	filler2
1	16	0	0	0	0	0	0	0	16	0	filler-data-16	filler2
1	17	0	0	0	0	0	0	0	17	0	filler-data-17	filler2
1	18	0	0	0	0	0	0	0	18	0	filler-data-18	filler2
1	19	0	0	0	0	0	0	0	19	0	filler-data-19	filler2
# Verify that CPK fields are considered to be covered by index scans
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2	key1,key2	4,4	NULL	1	100.00	Using intersect(key1,key2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) and (`test`.`t1`.`key1` = 10) and (((2 * `test`.`t1`.`pk1`) + 1) < <cache>(((2 * 96) + 1))))
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
pk1	pk2
95	59
95	58
95	57
95	56
95	55
95	54
95	53
95	52
95	51
95	50
# Verify that CPK is always used for index intersection scans
# (this is because it is used as a filter, not for retrieval)
# The expected number of rows can vary depending on page size
explain select * from t1 where badkey=1 and key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1	key1	4	const	ROWS	10.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`badkey` = 1))
# The expected number of rows can vary depending on page size
explain select * from t1 where pk1 < 7500 and key1 = 10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	PRIMARY,key1	key1	4	const	ROWS	99.99	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pk1` < 7500))
# Verify that keys with 'tails' of PK members are ok.
explain select * from t1 where pktail1ok=1 and key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1,pktail1ok	pktail1ok	4	const	76	0.91	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail1ok` = 1))
explain select * from t1 where pktail2ok=1 and key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1,pktail2ok	pktail2ok	4	const	82	0.91	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail2ok` = 1))
# Note: The following is actually a deficiency, it uses sort_union currently.
#       This comment refers to InnoDB and is probably not valid for other engines.
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	PRIMARY,key1,pktail2ok	pktail2ok,key1	8,4	NULL	ROWS	100.00	Using sort_union(pktail2ok,key1); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where (((`test`.`t1`.`pktail2ok` = 1) and (`test`.`t1`.`pk1` < 50000)) or (`test`.`t1`.`key1` = 10))
explain select * from t1 where pktail3bad=1 and key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1,pktail3bad	EITHER_KEY	4	const	ROWS	0.91	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail3bad` = 1))
explain select * from t1 where pktail4bad=1 and key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1,pktail4bad	pktail4bad	4	const	ROWS	0.91	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail4bad` = 1))
explain select * from t1 where pktail5bad=1 and key1=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	key1,pktail5bad	pktail5bad	4	const	ROWS	0.91	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`pktail1ok` AS `pktail1ok`,`test`.`t1`.`pktail2ok` AS `pktail2ok`,`test`.`t1`.`pktail3bad` AS `pktail3bad`,`test`.`t1`.`pktail4bad` AS `pktail4bad`,`test`.`t1`.`pktail5bad` AS `pktail5bad`,`test`.`t1`.`pk2copy` AS `pk2copy`,`test`.`t1`.`badkey` AS `badkey`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`key1` = 10) and (`test`.`t1`.`pktail5bad` = 1))
# Test for problem with innodb key values prefetch buffer:
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	key1,key2	key1,key2	4,4	NULL	1	100.00	Using intersect(key1,key2); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` where ((`test`.`t1`.`key2` = 10) and (`test`.`t1`.`key1` = 10)) limit 10
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
pk1	pk2	key1	key2
104	49	10	10
104	48	10	10
104	47	10	10
104	46	10	10
104	45	10	10
104	44	10	10
104	43	10	10
104	42	10	10
104	41	10	10
104	40	10	10
drop table t1;
# Testcase for BUG#4984
create table t1
(
RUNID varchar(22),
SUBMITNR varchar(5),
ORDERNR char(1),
PROGRAMM varchar(8),
TESTID varchar(4),
UCCHECK char(1),
ETEXT varchar(80),
ETEXT_TYPE char(1),
INFO char(1),
SEVERITY tinyint(3),
TADIRFLAG char(1),
PRIMARY KEY  (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
KEY `TVERM~KEY`  (PROGRAMM,TESTID,UCCHECK)
) DEFAULT CHARSET=latin1;
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
WHERE
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
`TESTID`='' AND `UCCHECK`='';
drop table t1;
#
# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
#
CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
PRIMARY KEY (f1), KEY (f2), KEY (f3) );
INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
SELECT t1.f1 FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
f1
2
EXPLAIN SELECT t1.f1 FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	system	PRIMARY	NULL	NULL	NULL	1	100.00	NULL
2	DEPENDENT SUBQUERY	t2	NULL	ref	f2,f3	f2	5	const	1	50.00	Using where
Warnings:
Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select '2' AS `f1` from dual where (((/* select#2 */ select count(0) from `test`.`t2` where ((`test`.`t2`.`f2` = '2') and (`test`.`t2`.`f3` = 'h'))) = 0))
DROP TABLE t1,t2;
#
# Bug#19055268 ASSERT `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET,...) FAILED
#
SET sql_mode = '';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) NOT NULL,
col_date_key date NOT NULL,
col_date_nokey date NOT NULL,
col_time_key time NOT NULL,
col_time_nokey time NOT NULL,
col_datetime_key datetime NOT NULL,
col_datetime_nokey datetime NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT 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_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t1 VALUES
(1,2,1,'0000-00-00','0000-00-00','00:00:07','00:00:25','0000-00-00 00:00:00','0000-00-00 00:00:00','2','2'),
(109,25,1,'0000-00-00','0000-00-00','00:00:07','00:00:25','0000-00-00 00:00:00','0000-00-00 00:00:00','1','7');
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) NOT NULL,
col_date_key date NOT NULL,
col_date_nokey date NOT NULL,
col_time_key time NOT NULL,
col_time_nokey time NOT NULL,
col_datetime_key datetime NOT NULL,
col_datetime_nokey datetime NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT 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_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t2 VALUES
(10,2,4,'2000-04-16','2000-04-16','17:02:34','17:02:34','2009-08-04 02:49:10','2009-08-04 02:49:10','u','u'),
(11,6,5,'1900-01-01','1900-01-01','12:31:32','12:31:32','2004-05-24 22:22:06','2004-05-24 22:22:06','b','b'),
(12,6,0,'2005-05-14','2005-05-14','16:49:31','16:49:31','1900-01-01 00:00:00','1900-01-01 00:00:00','c','c'),
(13,94,140,'2001-05-23','2001-05-23','00:00:00','00:00:00','2003-07-28 07:31:49','2003-07-28 07:31:49','f','f'),
(14,10,230,'2000-06-04','2000-06-04','20:23:27','20:23:27','2000-02-04 21:03:36','2000-02-04 21:03:36','g','g'),
(15,8,2,'1900-01-01','1900-01-01','10:31:09','10:31:09','2005-05-22 11:48:12','2005-05-22 11:48:12','q','q'),
(16,43,110,'2008-03-18','2008-03-18','09:58:35','09:58:35','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'),
(17,1,8,'2007-02-25','2007-02-25','05:39:28','05:39:28','2005-05-06 00:53:37','2005-05-06 00:53:37','c','c'),
(18,6,5,'2001-11-22','2001-11-22','00:00:00','00:00:00','2002-08-08 23:43:46','2002-08-08 23:43:46','x','x'),
(19,5,8,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-10-26 04:07:22','2009-10-26 04:07:22','n','n'),
(20,1,5,'2001-08-07','2001-08-07','14:06:09','14:06:09','2005-03-12 22:55:23','2005-03-12 22:55:23','r','r'),
(21,195,70,'2000-08-03','2000-08-03','00:00:00','00:00:00','2008-05-03 22:34:19','2008-05-03 22:34:19','j','j'),
(22,1,0,'2007-04-21','2007-04-21','17:43:27','17:43:27','2001-10-12 13:30:14','2001-10-12 13:30:14','l','l'),
(23,0,9,'2000-06-13','2000-06-13','12:23:15','12:23:15','2007-10-07 03:53:06','2007-10-07 03:53:06','h','h'),
(24,7,3,'0000-00-00','0000-00-00','19:35:06','19:35:06','2005-12-27 20:58:56','2005-12-27 20:58:56','z','z'),
(25,1,9,'2004-07-08','2004-07-08','10:16:08','10:16:08','2009-05-16 18:44:21','2009-05-16 18:44:21','z','z'),
(26,4,3,'2003-06-01','2003-06-01','00:00:00','00:00:00','2008-10-02 00:00:00','2008-10-02 00:00:00','d','d'),
(27,76,22,'0000-00-00','0000-00-00','16:51:15','16:51:15','2001-03-20 00:00:00','2001-03-20 00:00:00','l','l'),
(28,6,1,'1900-01-01','1900-01-01','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','s','s'),
(29,7,3,'2007-01-22','2007-01-22','00:00:00','00:00:00','2000-01-10 23:19:37','2000-01-10 23:19:37','k','k'),
(30,6,3,'2006-10-19','2006-10-19','21:46:20','21:46:20','2008-06-21 16:56:37','2008-06-21 16:56:37','l','l'),
(31,6,8,'2005-02-27','2005-02-27','00:00:00','00:00:00','2002-03-01 00:34:37','2002-03-01 00:34:37','t','t'),
(32,1,4,'2001-09-10','2001-09-10','03:34:16','03:34:16','2003-02-05 18:41:00','2003-02-05 18:41:00','n','n'),
(33,5,1,'2002-09-06','2002-09-06','11:25:31','11:25:31','2004-01-18 13:28:24','2004-01-18 13:28:24','z','z'),
(34,2,3,'2005-01-15','2005-01-15','17:00:53','17:00:53','2002-12-03 01:06:27','2002-12-03 01:06:27','f','f'),
(35,1,3,'1900-01-01','1900-01-01','23:32:47','23:32:47','2006-03-03 02:58:59','2006-03-03 02:58:59','q','q'),
(36,185,63,'2006-12-20','2006-12-20','00:00:00','00:00:00','2002-07-01 10:13:39','2002-07-01 10:13:39','l','l'),
(37,3,4,'2002-04-17','2002-04-17','12:15:05','12:15:05','2009-01-13 20:47:01','2009-01-13 20:47:01','a','a'),
(38,7,8,'2004-01-10','2004-01-10','22:26:38','22:26:38','2008-07-18 13:35:29','2008-07-18 13:35:29','c','c'),
(39,8,6,'2008-03-24','2008-03-24','00:00:00','00:00:00','2001-03-15 02:44:57','2001-03-15 02:44:57','c','c'),
(40,1,4,'2007-08-18','2007-08-18','00:00:00','00:00:00','2000-06-25 14:03:55','2000-06-25 14:03:55','i','i'),
(41,3,9,'2005-07-22','2005-07-22','00:00:00','00:00:00','2005-12-13 21:32:03','2005-12-13 21:32:03','r','r'),
(42,8,2,'2008-07-12','2008-07-12','08:59:37','08:59:37','2009-03-18 19:27:29','2009-03-18 19:27:29','g','g'),
(43,159,118,'2003-08-18','2003-08-18','07:19:50','07:19:50','2004-04-10 02:50:59','2004-04-10 02:50:59','l','l'),
(44,112,10,'2002-02-26','2002-02-26','03:51:46','03:51:46','2008-09-27 06:49:19','2008-09-27 06:49:19','w','w'),
(45,0,9,'2007-07-26','2007-07-26','15:57:32','15:57:32','2006-02-04 15:10:41','2006-02-04 15:10:41','b','b'),
(46,2,9,'1900-01-01','1900-01-01','02:57:08','02:57:08','2004-01-04 18:45:14','2004-01-04 18:45:14','i','i'),
(47,1,1,'2000-07-26','2000-07-26','00:21:15','00:21:15','2009-04-04 02:52:09','2009-04-04 02:52:09','o','o'),
(48,7,6,'2005-03-06','2005-03-06','23:35:02','23:35:02','2001-03-20 12:50:17','2001-03-20 12:50:17','f','f'),
(49,5,2,'2005-04-16','2005-04-16','09:21:51','09:21:51','2001-11-15 10:51:46','2001-11-15 10:51:46','a','a'),
(50,2,2,'2004-04-09','2004-04-09','19:21:02','19:21:02','2007-08-13 00:00:00','2007-08-13 00:00:00','j','j'),
(51,5,3,'2003-05-13','2003-05-13','03:16:22','03:16:22','1900-01-01 00:00:00','1900-01-01 00:00:00','k','k'),
(52,5,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2008-01-24 08:37:15','2008-01-24 08:37:15','x','x'),
(53,2,8,'2000-05-27','2000-05-27','18:19:51','18:19:51','2003-03-02 10:18:02','2003-03-02 10:18:02','r','r'),
(54,2,5,'2004-07-04','2004-07-04','01:42:17','01:42:17','2003-04-25 07:35:13','2003-04-25 07:35:13','t','t'),
(55,9,0,'2006-03-07','2006-03-07','00:00:00','00:00:00','2006-07-11 23:31:13','2006-07-11 23:31:13','p','p'),
(56,3,1,'2002-08-17','2002-08-17','20:32:51','20:32:51','2009-08-08 16:55:59','2009-08-08 16:55:59','g','g'),
(57,2,9,'2003-06-08','2003-06-08','10:04:19','10:04:19','1900-01-01 00:00:00','1900-01-01 00:00:00','q','q'),
(58,7,4,'1900-01-01','1900-01-01','06:39:26','06:39:26','2000-12-22 10:27:41','2000-12-22 10:27:41','m','m'),
(59,4,7,'2003-10-09','2003-10-09','15:48:26','15:48:26','2002-07-20 00:00:00','2002-07-20 00:00:00','l','l'),
(60,5,6,'2007-08-12','2007-08-12','02:17:23','02:17:23','2007-09-24 00:12:11','2007-09-24 00:12:11','d','d'),
(61,3,3,'2008-12-13','2008-12-13','07:48:47','07:48:47','2009-01-05 06:13:21','2009-01-05 06:13:21','n','n'),
(62,0,8,'0000-00-00','0000-00-00','09:09:14','09:09:14','2008-11-23 18:13:35','2008-11-23 18:13:35','l','l'),
(63,1,2,'2003-07-27','2003-07-27','22:46:23','22:46:23','2002-12-19 00:00:00','2002-12-19 00:00:00','c','c'),
(64,5,5,'2003-04-13','2003-04-13','19:42:42','19:42:42','2003-04-03 16:16:35','2003-04-03 16:16:35','v','v'),
(65,0,3,'2007-04-25','2007-04-25','15:15:32','15:15:32','2009-04-13 13:44:41','2009-04-13 13:44:41','z','z'),
(66,4,4,'2000-03-04','2000-03-04','02:50:26','02:50:26','2007-04-19 20:49:24','2007-04-19 20:49:24','h','h'),
(67,0,9,'2001-08-03','2001-08-03','14:44:31','14:44:31','2002-11-15 23:36:04','2002-11-15 23:36:04','y','y'),
(68,2,0,'2006-04-13','2006-04-13','00:00:00','00:00:00','2000-05-16 00:00:00','2000-05-16 00:00:00','o','o'),
(69,2,1,'2006-04-22','2006-04-22','07:31:27','07:31:27','2002-06-08 06:51:27','2002-06-08 06:51:27','h','h'),
(70,8,5,'2004-08-26','2004-08-26','10:48:57','10:48:57','2006-12-07 01:49:26','2006-12-07 01:49:26','h','h'),
(71,8,6,'2002-11-26','2002-11-26','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','c','c'),
(72,5,2,'2002-01-07','2002-01-07','23:06:00','23:06:00','2000-09-27 16:26:23','2000-09-27 16:26:23','m','m'),
(73,1,0,'2005-05-07','2005-05-07','05:56:02','05:56:02','1900-01-01 00:00:00','1900-01-01 00:00:00','s','s'),
(74,0,0,'2001-04-21','2001-04-21','10:44:22','10:44:22','2008-02-13 04:28:14','2008-02-13 04:28:14','t','t'),
(75,7,9,'2000-09-01','2000-09-01','00:00:00','00:00:00','2000-03-10 06:17:52','2000-03-10 06:17:52','r','r'),
(76,6,8,'2004-07-06','2004-07-06','01:44:26','01:44:26','2003-05-23 00:00:00','2003-05-23 00:00:00','u','u'),
(77,0,6,'0000-00-00','0000-00-00','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),
(78,6,0,'2001-02-21','2001-02-21','17:01:49','17:01:49','2003-10-27 12:45:52','2003-10-27 12:45:52','p','p'),
(79,9,0,'2004-01-28','2004-01-28','16:27:14','16:27:14','2004-08-06 00:00:00','2004-08-06 00:00:00','z','z'),
(80,5,3,'2008-10-07','2008-10-07','00:00:00','00:00:00','2001-10-05 05:13:39','2001-10-05 05:13:39','h','h'),
(81,5,5,'0000-00-00','0000-00-00','10:34:22','10:34:22','2001-03-25 04:24:51','2001-03-25 04:24:51','q','q'),
(82,6,0,'2008-03-04','2008-03-04','10:48:51','10:48:51','2006-08-20 11:54:04','2006-08-20 11:54:04','l','l'),
(83,1,0,'2009-05-08','2009-05-08','16:55:06','16:55:06','2003-05-16 18:14:52','2003-05-16 18:14:52','p','p'),
(84,1,1,'2005-03-19','2005-03-19','14:46:32','14:46:32','2009-10-24 13:39:35','2009-10-24 13:39:35','t','t'),
(85,9,8,'2007-09-14','2007-09-14','16:26:50','16:26:50','2001-08-10 00:00:00','2001-08-10 00:00:00','o','o'),
(86,249,190,'2007-02-10','2007-02-10','06:35:32','06:35:32','2001-09-06 10:15:08','2001-09-06 10:15:08','o','o'),
(87,3,0,'2000-06-12','2000-06-12','02:28:38','02:28:38','2006-09-27 00:00:00','2006-09-27 00:00:00','p','p'),
(88,6,4,'2002-07-06','2002-07-06','00:00:00','00:00:00','2002-02-27 20:12:29','2002-02-27 20:12:29','e','e'),
(89,7,2,'2001-02-25','2001-02-25','04:52:29','04:52:29','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),
(90,5,8,'2000-04-27','2000-04-27','00:00:00','00:00:00','2005-08-18 00:00:00','2005-08-18 00:00:00','z','z'),
(91,4,0,'2005-08-23','2005-08-23','09:11:04','09:11:04','2000-10-15 15:52:54','2000-10-15 15:52:54','j','j'),
(92,24,137,'2006-01-07','2006-01-07','22:38:18','22:38:18','2007-11-22 13:52:22','2007-11-22 13:52:22','e','e'),
(93,3,7,'2007-07-08','2007-07-08','13:23:13','13:23:13','2005-09-20 09:09:48','2005-09-20 09:09:48','k','k'),
(94,179,153,'2000-01-06','2000-01-06','15:33:10','15:33:10','2008-01-11 17:35:34','2008-01-11 17:35:34','d','d'),
(95,5,7,'0000-00-00','0000-00-00','09:20:10','09:20:10','2002-11-01 00:00:00','2002-11-01 00:00:00','q','q'),
(96,7,9,'2002-02-22','2002-02-22','11:19:10','11:19:10','2009-06-27 00:00:00','2009-06-27 00:00:00','j','j'),
(97,7,2,'2001-04-17','2001-04-17','08:08:41','08:08:41','2000-09-25 14:11:58','2000-09-25 14:11:58','j','j'),
(98,1,0,'2005-06-26','2005-06-26','10:17:36','10:17:36','2004-01-05 04:49:39','2004-01-05 04:49:39','e','e'),
(99,5,6,'2008-09-14','2008-09-14','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','f','f'),
(100,7,6,'0000-00-00','0000-00-00','01:05:02','01:05:02','2001-01-17 23:16:24','2001-01-17 23:16:24','a','a'),
(101,9,2,'2001-12-20','2001-12-20','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','o','o'),
(102,5,9,'2001-11-05','2001-11-05','00:00:00','00:00:00','2008-07-15 00:00:00','2008-07-15 00:00:00','y','y'),
(103,4,4,'1900-01-01','1900-01-01','15:32:04','15:32:04','2001-03-06 10:00:37','2001-03-06 10:00:37','d','d'),
(104,8,7,'2009-02-13','2009-02-13','20:25:36','20:25:36','2004-08-15 00:00:00','2004-08-15 00:00:00','z','z'),
(105,8,6,'2008-03-25','2008-03-25','21:33:53','21:33:53','2006-10-12 06:13:06','2006-10-12 06:13:06','s','s');
analyze table t1;
analyze table t2;
EXPLAIN UPDATE t1 SET col_time_nokey = 25
WHERE (col_varchar_key, col_varchar_nokey) IN (
SELECT col_int_key, col_int_key
FROM t2
WHERE (col_time_key, col_int_key, col_int_key) IN (
('s','a', 'm'), (0, 'n', 7)
)
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	t2	NULL	index_merge	col_int_key,col_time_key	col_int_key,col_time_key	4,3	NULL	3	100.00	Using intersect(col_int_key,col_time_key); Using where; Using index
UPDATE t1 SET col_time_nokey = 25
WHERE (col_varchar_key, col_varchar_nokey) IN (
SELECT col_int_key, col_int_key
FROM t2
WHERE (col_time_key, col_int_key, col_int_key) IN (
('s','a', 'm'), (0, 'n', 7)
)
);
DROP TABLE t1, t2;
SET sql_mode = default;
#
# Generic @@optimizer_switch tests (move those into a separate file if
#  we get another @@optimizer_switch user)
#
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
set optimizer_switch='index_merge=off,index_merge_union=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
set optimizer_switch='index_merge_union=on';
select @@optimizer_switch;
@@optimizer_switch
index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
set optimizer_switch='default,index_merge_sort_union=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
set optimizer_switch=4;
set optimizer_switch=NULL;
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL'
set optimizer_switch='default,index_merge';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge'
set optimizer_switch='index_merge=index_merge';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=index_merge'
set optimizer_switch='index_merge=on,but...';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'but...'
set optimizer_switch='index_merge=';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge='
set optimizer_switch='index_merge';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge'
set optimizer_switch='on';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'on'
set optimizer_switch='index_merge=on,index_merge=off';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off'
set optimizer_switch='index_merge_union=on,index_merge_union=default';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge_union=default'
set optimizer_switch='default,index_merge=on,index_merge=off,default';
ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default'
set optimizer_switch=default;
set optimizer_switch='index_merge=off,index_merge_union=off,default';
select @@optimizer_switch;
@@optimizer_switch
index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
set optimizer_switch=default;
select @@global.optimizer_switch;
@@global.optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
set @@global.optimizer_switch=default;
select @@global.optimizer_switch;
@@global.optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
#
# Check index_merge's @@optimizer_switch flags
#
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int, filler char(100), 
key(a), key(b), key(c));
insert into t1 select 
A.a * B.a*10 + C.a*100, 
A.a * B.a*10 + C.a*100,
A.a, 
'filler'
from t0 A, t0 B, t0 C;
This should use union:
explain select * from t1 where a=1 or b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	2	100.00	Using union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`b` = 1))
This should use ALL:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a=1 or b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	a,b	NULL	NULL	NULL	1000	1.40	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`b` = 1))
This should use sort-union:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=1 or b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	2	100.00	Using sort_union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`b` = 1))
This will use sort-union:
set optimizer_switch=default;
explain select * from t1 where a<1 or b <1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	38	100.00	Using sort_union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` < 1) or (`test`.`t1`.`b` < 1))
This should use ALL:
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where a<1 or b <1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	a,b	NULL	NULL	NULL	1000	55.55	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` < 1) or (`test`.`t1`.`b` < 1))
This should use ALL:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a<1 or b <1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	a,b	NULL	NULL	NULL	1000	55.55	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` < 1) or (`test`.`t1`.`b` < 1))
This will use sort-union:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a<1 or b <1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	38	100.00	Using sort_union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler` from `test`.`t1` where ((`test`.`t1`.`a` < 1) or (`test`.`t1`.`b` < 1))
alter table t1 add d int, add key(d);
update t1 set d=a;
This will use sort_union:
set optimizer_switch=default;
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b,c,d	a,b	5,5	NULL	3	10.09	Using sort_union(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` = 3) or (`test`.`t1`.`b` in (1,2))) and ((`test`.`t1`.`c` = 3) or (`test`.`t1`.`d` = 4)))
And if we disable sort_union, union:
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b,c,d	c,d	5,5	NULL	100	20.56	Using union(c,d); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler` AS `filler`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (((`test`.`t1`.`a` = 3) or (`test`.`t1`.`b` in (1,2))) and ((`test`.`t1`.`c` = 3) or (`test`.`t1`.`d` = 4)))
drop table t1;
create table t1 (
a int, b int, c int,
filler1 char(200), filler2 char(200), 
key(a),key(b),key(c)
);
insert into t1 
select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' 
from t0 A, t0 B, t0 C, t0 D where D.a<5;
This should be intersect:
set optimizer_switch=default;
explain select * from t1 where a=10 and b=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	1	100.00	Using intersect(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10))
No intersect when index_merge is disabled:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a=10 and b=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a,b	a	5	const	49	0.98	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10))
No intersect if it is disabled:
set optimizer_switch='default,index_merge_intersection=off';
explain select * from t1 where a=10 and b=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a,b	a	5	const	49	0.98	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10))
Do intersect when union was disabled
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=10 and b=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	1	100.00	Using intersect(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10))
Do intersect when sort_union was disabled
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where a=10 and b=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b	a,b	5,5	NULL	1	100.00	Using intersect(a,b); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where ((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10))
This will use intersection inside a union:
set optimizer_switch=default;
explain select * from t1 where a=10 and b=10 or c=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b,c	a,b,c	5,5,5	NULL	6	100.00	Using union(intersect(a,b),c); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where (((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10)) or (`test`.`t1`.`c` = 10))
Should be only union left:
set optimizer_switch='default,index_merge_intersection=off';
explain select * from t1 where a=10 and b=10 or c=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b,c	a,c	5,5	NULL	54	100.00	Using union(a,c); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where (((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10)) or (`test`.`t1`.`c` = 10))
This will switch to sort-union (intersection will be gone, too,
thats a known limitation:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=10 and b=10 or c=10;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	a,b,c	a,c	5,5	NULL	54	100.00	Using sort_union(a,c); Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`filler1` AS `filler1`,`test`.`t1`.`filler2` AS `filler2` from `test`.`t1` where (((`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` = 10)) or (`test`.`t1`.`c` = 10))
set optimizer_switch=default;
show variables like 'optimizer_switch';
Variable_name	Value
optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
drop table t0, t1;

${this.title}

Code Editor : index_merge_myisam.result