diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-09-11 21:31:03 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-09-11 21:31:03 +0300 |
| commit | 1bf3e8ab43e982953d7534db902020d321364afb (patch) | |
| tree | 7de4aaac9f947d4302cd4edbb5677da8917ade71 /mysql-test | |
| parent | f5bebaf1d6a0ff2fca9b342ed5910b9d34a276bb (diff) | |
| parent | 4901f31c13f91e130f077f2f77b32c40b0036e32 (diff) | |
| download | mariadb-git-1bf3e8ab43e982953d7534db902020d321364afb.tar.gz | |
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test')
79 files changed, 3123 insertions, 330 deletions
diff --git a/mysql-test/collections/10.0-compatible.list b/mysql-test/collections/10.0-compatible.list new file mode 100644 index 00000000000..415c74ab00c --- /dev/null +++ b/mysql-test/collections/10.0-compatible.list @@ -0,0 +1,591 @@ +main.1st +main.adddate_454 +main.almost_full +main.alter_table_autoinc-5574 +main.alter_table_errors +main.alter_table_mdev539_maria +main.alter_table_mdev539_myisam +main.alter_table_online +main.alter_table_trans +main.analyze +main.analyze_stmt +main.analyze_stmt_orderby +main.analyze_stmt_slow_query_log +main.ansi +main.assign_key_cache +main.auth_rpl +main.auto_increment +main.auto_increment_ranges_innodb +main.auto_increment_ranges_myisam +main.bad_frm_crash_5029 +main.bench_count_distinct +main.bigint +main.binary +main.binary_to_hex +main.blackhole +main.blackhole_plugin +main.bool +main.bootstrap +main.bug12427262 +main.bug13633383 +main.bug46760 +main.bug47671 +main.bulk_replace +main.case +main.change_user +main.check_constraint_show +main.client_xml +main.comment_column +main.comment_column2 +main.comment_index +main.comments +main.comment_table +main.commit_1innodb +main.compare +main.compound +main.contributors +main.count_distinct +main.count_distinct2 +main.create_drop_db +main.create_drop_event +main.create_drop_function +main.create_drop_index +main.create_drop_procedure +main.create_drop_server +main.create_drop_trigger +main.create_drop_user +main.create_drop_view +main.create_not_windows +main.create_select_tmp +main.create-uca +main.create_user +main.create_w_max_indexes_64 +main.ctype_ascii +main.ctype_big5 +main.ctype_binary +main.ctype_collate +main.ctype_cp1250_ch +main.ctype_cp1251 +main.ctype_cp850 +main.ctype_cp932 +main.ctype_cp932_binlog_row +main.ctype_cp932_binlog_stm +main.ctype_create +main.ctype_eucjpms +main.ctype_euckr +main.ctype_filename +main.ctype_filesystem +main.ctype_gb2312 +main.ctype_gbk +main.ctype_gbk_binlog +main.ctype_gbk_export_import +main.ctype_hebrew +main.ctype_latin1 +main.ctype_latin1_de +main.ctype_latin2 +main.ctype_latin2_ch +main.ctype_ldml +main.ctype_many +main.ctype_mb +main.ctype_nopad_8bit +main.ctype_partitions +main.ctype_recoding +main.ctype_sjis +main.ctype_swe7 +main.ctype_tis620 +main.ctype_uca +main.ctype_uca_innodb +main.ctype_uca_partitions +main.ctype_ucs +main.ctype_ucs2_def +main.ctype_ucs2_query_cache +main.ctype_ucs2_uca +main.ctype_ujis +main.ctype_ujis_ucs2 +main.ctype_upgrade +main.ctype_utf16 +main.ctype_utf16_def +main.ctype_utf16le +main.ctype_utf16_uca +main.ctype_utf32 +main.ctype_utf32_uca +main.ctype_utf8 +main.ctype_utf8mb4 +main.ctype_utf8mb4_heap +main.ctype_utf8mb4_innodb +main.ctype_utf8mb4_myisam +main.ctype_utf8mb4_uca +main.ctype_utf8_uca +main.date_formats +main.datetime_456 +main.default_storage_engine +main.delete +main.delete_returning +main.deprecated_features +main.derived_cond_pushdown +main.derived_opt +main.derived_view +main.distinct +main.drop-no_root +main.dyncol +main.empty_server_name-8224 +main.empty_table +main.endspace +main.enforce_storage_engine_opt +main.errors +main.events_2 +main.events_logs_tests +main.events_microsec +main.events_restart +main.events_scheduling +main.events_slowlog +main.events_trans +main.execution_constants +main.explain +main.explain_json +main.explain_json_format_partitions +main.explain_json_innodb +main.explain_non_select +main.ext_key_noPK_6794 +main.fast_prefix_index_fetch_innodb +main.features +main.filesort_bad_i_s-7585 +main.flush2 +main.foreign_key +main.frm_bad_row_type-7333 +main.fulltext +main.fulltext2 +main.fulltext3 +main.fulltext_cache +main.fulltext_charsets +main.fulltext_derived_4257 +main.fulltext_derived_4316 +main.fulltext_distinct +main.fulltext_left_join +main.fulltext_multi +main.fulltext_order_by +main.fulltext_update +main.fulltext_var +main.func_analyse +main.func_concat +main.func_crypt +main.func_date_add +main.func_default +main.func_des_encrypt +main.func_digest +main.func_encrypt +main.func_encrypt_ucs2 +main.func_equal +main.func_gconcat +main.func_group_innodb +main.func_hybrid_type +main.func_if +main.func_in +main.func_isnull +main.func_like +main.func_math +main.func_op +main.func_regexp +main.func_regexp_pcre +main.func_rollback +main.func_sapdb +main.func_set +main.func_system +main.func_test +main.func_time +main.func_time_hires +main.func_timestamp +main.function_defaults +main.function_defaults_innodb +main.gcc296 +main.get_diagnostics +main.gis +main.gis2 +main.gis-alter_table_online +main.gis-precise +main.gis-rt-precise +main.gis-rtree +main.grant_4332 +main.greedy_optimizer +main.group_by +main.group_by_innodb +main.group_by_null +main.group_min_max +main.group_min_max_innodb +main.handler_read_last +main.handlersocket +main.having +main.help +main.host_cache_size_functionality +main.huge_frm-6224 +main.implicit_char_to_num_conversion +main.implicit_commit +main.in_datetime_241 +main.index_intersect +main.index_intersect_innodb +main.index_merge_innodb +main.index_merge_myisam +main.information_schema2 +main.information_schema_all_engines +main.information_schema_chmod +main.information_schema_inno +main.information_schema_parameters +main.information_schema_part +main.information_schema_routines +main.information_schema_stats +main.init_file +main.init_file_longline_3816 +main.init_file_set_password-7656 +main.innodb_bug878769 +main.innodb_ext_key +main.innodb_group +main.innodb_icp +main.innodb_ignore_builtin +main.innodb_mrr_cpk +main.innodb_utf8 +main.insert +main.insert_innodb +main.insert_select +main.insert_update +main.insert_update_autoinc-7150 +main.join +main.join_cache +main.join_crash +main.join_nested +main.join_nested_jcl6 +main.join_optimizer +main.join_outer +main.join_outer_innodb +main.join_outer_jcl6 +main.key +main.key_cache +main.key_diff +main.key_primary +main.keyread +main.keywords +main.last_value +main.limit +main.limit_rows_examined +main.loaddata_autocom_innodb +main.locale +main.log_errchk +main.log_slow +main.log_state_bug33693 +main.log_tables_upgrade +main.long_tmpdir +main.lowercase_mixed_tmpdir +main.lowercase_table +main.lowercase_table5 +main.lowercase_table_grant +main.lowercase_table_qcache +main.lowercase_utf8 +main.lowercase_view +main.mdev13607 +main.mdev_14586 +main.mdev316 +main.mdl +main.merge_innodb +main.merge_mmap +main.metadata +main.mix2_myisam_ucs2 +main.mrr_derived_crash_4610 +main.mrr_icp_extra +main.multi_statement +main.multi_update2 +main.multi_update_innodb +main.multi_update_tiny_hash +main.myisam-blob +main.myisam_enable_keys-10506 +main.myisam_explain_non_select_all +main.myisam_icp +main.myisam_mrr +main.myisampack +main.myisam-system +main.mysql +main.mysql5613mysql +main.mysql57_virtual +main.mysqladmin +main.mysql_binary_mode +main.mysqlcheck +main.mysql_comments +main.mysql_cp932 +main.mysqld--defaults-file +main.mysqld--help +main.mysqld_help_crash-9183 +main.mysqld_option_err +main.mysqldump-compat +main.mysqldump-nl +main.mysqldump-no-binlog +main.mysqldump_restore +main.mysql_not_windows +main.mysql_protocols +main.mysqlshow +main.mysqlslap +main.mysqltest_256 +main.mysqltest_cont_on_error +main.mysql_tzinfo_to_sql_symlink +main.mysql_upgrade_noengine +main.mysql_upgrade_no_innodb +main.mysql_upgrade_ssl +main.mysql_upgrade_view +main.negation_elimination +main.no_binlog +main.no_password_column-mdev-11170 +main.null +main.null_key +main.odbc +main.olap +main.old-mode +main.order_by +main.order_by_innodb +main.order_by-mdev-10122 +main.order_by_optimizer +main.order_by_optimizer_innodb +main.order_by_sortkey +main.order_by_zerolength-4285 +main.order_fill_sortbuf +main.outfile_loaddata +main.parser +main.parser_bug21114_innodb +main.parser_precedence +main.parser_stack +main.partition +main.partition_binlog +main.partition_binlog_stmt +main.partition_blackhole +main.partition_bug18198 +main.partition_cache_innodb +main.partition_cache_myisam +main.partition_charset +main.partition_column +main.partition_column_prune +main.partition_datatype +main.partition_disabled +main.partition_error +main.partition_example +main.partition_exchange +main.partition_explicit_prune +main.partition_hash +main.partition_key_cache +main.partition_list +main.partition_mgm +main.partition_mgm_err +main.partition_mgm_err2 +main.partition_myisam +main.partition_not_blackhole +main.partition_not_windows +main.partition_order +main.partition_pruning +main.partition_range +main.partition_rename_longfilename +main.partition_truncate +main.partition_utf8 +main.perror +main.plugin +main.plugin_auth_qa +main.plugin_auth_qa_2 +main.plugin_auth_qa_3 +main.plugin_innodb +main.plugin_load +main.plugin_loaderr +main.plugin_load_option +main.plugin_maturity +main.preload +main.profiling +main.progress_976225 +main.ps_10nestset +main.ps_11bugs +main.ps_1general +main.ps_2myisam +main.ps_3innodb +main.ps_4heap +main.ps_5merge +main.ps_change_master +main.ps_ddl1 +main.ps_max_subselect-5113 +main.ps_not_windows +main.query_cache +main.query_cache_innodb +main.query_cache_merge +main.query_cache_with_views +main.range +main.range_innodb +main.range_mrr_icp +main.range_vs_index_merge +main.range_vs_index_merge_innodb +main.renamedb +main.reopen_temp_table +main.repair +main.repair_symlink-5543 +main.replace +main.rollback +main.round +main.row +main.row-checksum +main.row-checksum-old +main.rowid_order_innodb +main.rpl_mysqldump_slave +main.second_frac-9175 +main.select +main.select_found +main.selectivity +main.selectivity_innodb +main.selectivity_no_engine +main.select_jcl6 +main.select_pkeycache +main.select_safe +main.servers +main.set_password +main.set_statement_notembedded +main.set_statement_notembedded_binlog +main.show +main.show_bad_definer-5553 +main.show_create_user +main.show_function_with_pad_char_to_full_length +main.show_profile +main.show_row_order-9226 +main.sighup-6580 +main.signal +main.signal_demo1 +main.signal_demo2 +main.signal_demo3 +main.signal_sqlmode +main.single_delete_update +main.single_delete_update_innodb +main.skip_grants +main.skip_log_bin +main.sp-big +main.sp-bugs +main.sp-bugs2 +main.sp-destruct +main.sp-dynamic +main.sp-error +main.sp-fib +main.sp_gis +main.sp-group +main.sp_missing_4665 +main.sp-no-code +main.sp-prelocking +main.sp_stress_case +main.sp_trans +main.sp_trans_log +main.sp-ucs2 +main.sp-vars +main.ssl_7937 +main.ssl_8k_key +main.ssl_and_innodb +main.ssl_ca +main.ssl_cert_verify +main.ssl_connect +main.ssl_crl_clients +main.stack-crash +main.statistics +main.statistics_index_crash-7362 +main.stat_tables +main.stat_tables_disabled +main.stat_tables_innodb +main.stat_tables_partition +main.stat_tables_repl +main.strict +main.strict_autoinc_1myisam +main.strict_autoinc_2innodb +main.strict_autoinc_3heap +main.str_to_datetime_457 +main.subselect2 +main.subselect3 +main.subselect3_jcl6 +main.subselect4 +main.subselect_cache +main.subselect-crash_15755 +main.subselect_exists2in +main.subselect_exists2in_costmat +main.subselect_extra +main.subselect_extra_no_semijoin +main.subselect_gis +main.subselect_innodb +main.subselect_mat +main.subselect_mat_cost +main.subselect_mat_cost_bugs +main.subselect_notembedded +main.subselect_nulls +main.subselect_partial_match +main.subselect_sj2 +main.subselect_sj2_jcl6 +main.subselect_sj2_mat +main.subselect_sj_aria +main.subselect_sj_mat +main.subselect_sj_nonmerged +main.sum_distinct +main.sysdate_is_now +main.system_mysql_db +main.system_mysql_db_refs +main.table_elim +main.table_elim_debug +main.table_keyinfo-6838 +main.tablelock +main.table_options +main.table_options-5867 +main.temporal_literal +main.temporal_scale_4283 +main.temp_table_frm +main.timezone +main.timezone2 +main.timezone3 +main.timezone4 +main.tmp_table_count-7586 +main.trigger_no_defaults-11698 +main.trigger_null-8605 +main.truncate +main.truncate_badse +main.truncate-stale-6500 +main.type_binary +main.type_bit +main.type_bit_innodb +main.type_blob +main.type_datetime_hires +main.type_decimal +main.type_enum +main.type_float +main.type_int +main.type_nchar +main.type_newdecimal +main.type_num +main.type_num_innodb +main.type_ranges +main.type_set +main.type_temporal_innodb +main.type_temporal_mysql56 +main.type_time +main.type_time_6065 +main.type_time_hires +main.type_timestamp_hires +main.type_uint +main.type_varchar +main.type_year +main.update_ignore_216 +main.update_innodb +main.upgrade +main.user_var +main.varbinary +main.variables_community +main.view_alias +main.warnings_engine_disabled +main.win_avg +main.win_big +main.win_big-mdev-10092 +main.win_big-mdev-11697 +main.win_bit +main.win_empty_over +main.win_first_last_value +main.win_insert_select +main.win_i_s +main.win_lead_lag +main.win_min_max +main.win_nth_value +main.win_orderby +main.win_percent_cume +main.win_rank +main.win_sum +main.xa_binlog +main.xml +main.xtradb_mrr diff --git a/mysql-test/include/search_pattern_in_file.inc b/mysql-test/include/search_pattern_in_file.inc index 21192b55efb..6bead628fb0 100644 --- a/mysql-test/include/search_pattern_in_file.inc +++ b/mysql-test/include/search_pattern_in_file.inc @@ -18,6 +18,11 @@ # Optionally, SEARCH_ABORT can be set to "FOUND" or "NOT FOUND" and this # will abort if the search result doesn't match the requested one. # +# Optionally, SEARCH_OUTPUT can be set to control the format of output. +# Supported formats: +# - (default) : "FOUND n /pattern/ in FILE " or "NOT FOUND ..." +# - "matches" : Each match is printed, on a separate line +# # In case of # - SEARCH_FILE and/or SEARCH_PATTERN is not set # - SEARCH_FILE cannot be opened @@ -75,7 +80,14 @@ perl; my @matches=($content =~ m/$search_pattern/gs); my $res=@matches ? "FOUND " . scalar(@matches) : "NOT FOUND"; $ENV{SEARCH_FILE} =~ s{^.*?([^/\\]+)$}{$1}; - print "$res /$search_pattern/ in $ENV{SEARCH_FILE}\n"; + + if ($ENV{SEARCH_OUTPUT} eq "matches") { + foreach (@matches) { + print $_ . "\n"; + } + } else { + print "$res /$search_pattern/ in $ENV{SEARCH_FILE}\n"; + } die "$ENV{SEARCH_ABORT}\n" - if $ENV{SEARCH_ABORT} && $res =~ /^$ENV{SEARCH_ABORT}/; + if $ENV{SEARCH_ABORT} && $res =~ /^$ENV{SEARCH_ABORT}/; EOF diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 2c983e3de3e..a1120ba6de7 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16027,6 +16027,374 @@ a aa DROP FUNCTION f1; # +# MDEV-17011: condition pushdown into materialized derived used +# in INSERT SELECT, multi-table UPDATE and DELETE +# +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES +(3), (7), (1), (4), (1); +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; +EXPLAIN FORMAT=JSON INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a <= 2", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a <= 2" + } + } + } + } + } + } + } +} +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +SELECT * FROM t3; +a b +1 2 +2 2 +EXPLAIN FORMAT=JSON UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.a is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "8", + "used_key_parts": ["c"], + "ref": ["test.t2.a"], + "rows": 2, + "filtered": 100, + "attached_condition": "t2.a = t.c and t.a >= 3", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a >= 3" + } + } + } + } + } + } + } +} +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +SELECT * FROM t2; +a +3 +7 +11 +4 +11 +EXPLAIN FORMAT=JSON DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a = 2 and t2.a = t.c + 9", + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + } + } + } +} +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +SELECT * FROM t2; +a +3 +7 +4 +DROP TABLE t1,t2,t3; +# +# MDEV-16765: pushdown condition with the CASE structure +# defined with Item_cond item +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 3 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a = 2) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a = 1 or max_a = 2) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a > 2 and tab2.max_a < 4) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a = 1 or max_a > 2 and max_a < 4) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 3 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a > 1 and (tab2.max_a = 2 or tab2.max_a > 2)) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a > 1 and (max_a = 2 or max_a > 2)) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.b = 2 or tab2.b = 4) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (t1.b = 2 or t1.b = 4) then 1 else 0 end = 1" + } + } + } + } + } + } + } +} +DROP TABLE t1; +# +# MDEV-16803: pushdown condition with IN predicate in the derived table +# defined with several SELECT statements +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,2),(1,1); +SELECT * FROM +( +SELECT a,b,1 as c +FROM t1 +UNION ALL +SELECT a,b,2 as c +FROM t1 +) AS tab +WHERE ((a,b) IN ((1,2),(3,2))); +a b c +1 2 1 +3 2 1 +1 2 2 +3 2 2 +DROP TABLE t1; +# Start of 10.3 tests +# # MDEV-16801: splittable materialized derived/views with # one grouping field from table without keys # diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index b75c56e9ff2..61541d2cdf5 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2988,6 +2988,130 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT DROP FUNCTION f1; --echo # +--echo # MDEV-17011: condition pushdown into materialized derived used +--echo # in INSERT SELECT, multi-table UPDATE and DELETE +--echo # + +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); + +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES + (3), (7), (1), (4), (1); + +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; + +let $q1= +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; + +eval EXPLAIN FORMAT=JSON $q1; +eval $q1; + +SELECT * FROM t3; + +let $q2= +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 + WHERE t2.a= t.c and t.a>=3; + +eval EXPLAIN FORMAT=JSON $q2; +eval $q2; + +SELECT * FROM t2; + +let $q3= +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t + WHERE t2.a= t.c+9 and t.a=2; + +eval EXPLAIN FORMAT=JSON $q3; +eval $q3; + +SELECT * FROM t2; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-16765: pushdown condition with the CASE structure +--echo # defined with Item_cond item +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1; + +--echo # +--echo # MDEV-16803: pushdown condition with IN predicate in the derived table +--echo # defined with several SELECT statements +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,2),(1,1); + +SELECT * FROM +( + SELECT a,b,1 as c + FROM t1 + UNION ALL + SELECT a,b,2 as c + FROM t1 +) AS tab +WHERE ((a,b) IN ((1,2),(3,2))); + +DROP TABLE t1; + +--echo # Start of 10.3 tests + +--echo # --echo # MDEV-16801: splittable materialized derived/views with --echo # one grouping field from table without keys --echo # diff --git a/mysql-test/main/func_isnull.result b/mysql-test/main/func_isnull.result index 15d87997e29..8cbd528f2de 100644 --- a/mysql-test/main/func_isnull.result +++ b/mysql-test/main/func_isnull.result @@ -106,5 +106,25 @@ Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.` DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-15475: Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' +# failed on EXPLAIN EXTENDED with constant table and view +# +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select /*always not null*/ 1 is null AS `ISNULL(pk)` from dual +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual +DROP VIEW v1; +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/main/func_isnull.test b/mysql-test/main/func_isnull.test index 4c59fa3cbe8..7d1a7e83a1a 100644 --- a/mysql-test/main/func_isnull.test +++ b/mysql-test/main/func_isnull.test @@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15475: Assertion `!table || (!table->read_set || +--echo # bitmap_is_set(table->read_set, field_index))' +--echo # failed on EXPLAIN EXTENDED with constant table and view +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1); +EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1; +EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1; +# Cleanup +DROP VIEW v1; +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index b6f9ca8e628..840db182fe7 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -2805,6 +2805,11 @@ SEC_TO_TIME(MAKEDATE(0,RAND(~0))) 838:59:59 Warnings: Warning 1292 Truncated incorrect seconds value: '20000101' +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); +PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')) +24257 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '-3S\xFA\xDE?\x00\x00\xCA\xB3\xEEE\xA4\xD1\xC1\xA8' # # End of 5.5 tests # @@ -3578,6 +3583,7 @@ DROP TABLE t1; # # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) # +set timestamp=unix_timestamp('2018-08-02 10:10:10'); SELECT LAST_DAY(TIME'00:00:00') AS c1, CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index b03c14fb8ba..aebe3df761f 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -1705,6 +1705,10 @@ DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +# +# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str +# +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); --echo # --echo # End of 5.5 tests @@ -2158,6 +2162,7 @@ DROP TABLE t1; --echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) --echo # +set timestamp=unix_timestamp('2018-08-02 10:10:10'); SELECT LAST_DAY(TIME'00:00:00') AS c1, CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 36a44b05817..b3b660c4170 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -3893,5 +3893,22 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index a a 13 NULL 2 Using where; Using index drop table t1; # +# MDEV-15433: Optimizer does not use group by optimization with distinct +# +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +EXPLAIN SELECT DISTINCT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by +SELECT DISTINCT a FROM t1; +a +1 +2 +3 +4 +drop table t1; +# # End of 10.1 tests # diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index b0bc42d7f8c..adad9073235 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1609,5 +1609,32 @@ explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2 drop table t1; --echo # +--echo # MDEV-15433: Optimizer does not use group by optimization with distinct +--echo # + +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +--disable_query_log +INSERT INTO t1(a) VALUES (1), (2), (3), (4); +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +--enable_query_log +OPTIMIZE TABLE t1; +EXPLAIN SELECT DISTINCT a FROM t1; +SELECT DISTINCT a FROM t1; +drop table t1; + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/main/information_schema-big.result b/mysql-test/main/information_schema-big.result index cbd8191bc54..d4aa6deb2e2 100644 --- a/mysql-test/main/information_schema-big.result +++ b/mysql-test/main/information_schema-big.result @@ -20,6 +20,7 @@ table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE CHARACTER_SETS CHARACTER_SET_NAME +CHECK_CONSTRAINTS CONSTRAINT_SCHEMA CLIENT_STATISTICS CLIENT COLLATIONS COLLATION_NAME COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME @@ -77,6 +78,7 @@ table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE CHARACTER_SETS CHARACTER_SET_NAME +CHECK_CONSTRAINTS CONSTRAINT_SCHEMA CLIENT_STATISTICS CLIENT COLLATIONS COLLATION_NAME COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 6e987dd3266..b6e331a0382 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -51,6 +51,7 @@ c ALL_PLUGINS APPLICABLE_ROLES CHARACTER_SETS +CHECK_CONSTRAINTS CLIENT_STATISTICS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY @@ -937,6 +938,7 @@ connection user10261; SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%'; TABLE_NAME COLUMN_NAME PRIVILEGES +CHECK_CONSTRAINTS TABLE_NAME select COLUMNS TABLE_NAME select COLUMN_PRIVILEGES TABLE_NAME select FILES TABLE_NAME select diff --git a/mysql-test/main/information_schema_all_engines.result b/mysql-test/main/information_schema_all_engines.result index 126a6f4bccd..0ce9f15f753 100644 --- a/mysql-test/main/information_schema_all_engines.result +++ b/mysql-test/main/information_schema_all_engines.result @@ -4,6 +4,7 @@ Tables_in_information_schema ALL_PLUGINS APPLICABLE_ROLES CHARACTER_SETS +CHECK_CONSTRAINTS CLIENT_STATISTICS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY @@ -83,6 +84,7 @@ table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE CHARACTER_SETS CHARACTER_SET_NAME +CHECK_CONSTRAINTS CONSTRAINT_SCHEMA CLIENT_STATISTICS CLIENT COLLATIONS COLLATION_NAME COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME @@ -162,6 +164,7 @@ table_name column_name ALL_PLUGINS PLUGIN_NAME APPLICABLE_ROLES GRANTEE CHARACTER_SETS CHARACTER_SET_NAME +CHECK_CONSTRAINTS CONSTRAINT_SCHEMA CLIENT_STATISTICS CLIENT COLLATIONS COLLATION_NAME COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME @@ -247,6 +250,7 @@ table_name group_concat(t.table_schema, '.', t.table_name) num1 ALL_PLUGINS information_schema.ALL_PLUGINS 1 APPLICABLE_ROLES information_schema.APPLICABLE_ROLES 1 CHARACTER_SETS information_schema.CHARACTER_SETS 1 +CHECK_CONSTRAINTS information_schema.CHECK_CONSTRAINTS 1 CLIENT_STATISTICS information_schema.CLIENT_STATISTICS 1 COLLATIONS information_schema.COLLATIONS 1 COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1 @@ -315,6 +319,7 @@ Database: information_schema | ALL_PLUGINS | | APPLICABLE_ROLES | | CHARACTER_SETS | +| CHECK_CONSTRAINTS | | CLIENT_STATISTICS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | @@ -384,6 +389,7 @@ Database: INFORMATION_SCHEMA | ALL_PLUGINS | | APPLICABLE_ROLES | | CHARACTER_SETS | +| CHECK_CONSTRAINTS | | CLIENT_STATISTICS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | @@ -453,5 +459,5 @@ Wildcard: inf_rmation_schema | information_schema | SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 64 +information_schema 65 mysql 30 diff --git a/mysql-test/main/mysql.result b/mysql-test/main/mysql.result index 09f014da627..06ef7e95bc9 100644 --- a/mysql-test/main/mysql.result +++ b/mysql-test/main/mysql.result @@ -538,8 +538,6 @@ a # # End of 10.1 tests # -ERROR 1300 (HY000): Invalid utf8 character string: 'test\xF0\x9F\x98\x81 ' -ERROR 1300 (HY000): Invalid binary character string: 'test\xF0\x9F\x98\x81 ' ERROR 1300 (HY000) at line 2: Invalid utf8 character string: 'test\xF0\x9F\x98\x81' set GLOBAL sql_mode=default; diff --git a/mysql-test/main/mysql.test b/mysql-test/main/mysql.test index 01953ba2112..c65860eb822 100644 --- a/mysql-test/main/mysql.test +++ b/mysql-test/main/mysql.test @@ -638,10 +638,7 @@ EOF --echo # End of 10.1 tests --echo # ---error 1 ---exec $MYSQL --default-character-set=utf8 -e "select 1" "test😁 " 2>&1 ---error 1 ---exec $MYSQL --default-character-set=binary -e "select 1" "test😁 " 2>&1 + --write_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql SET NAMES utf8; USE test😁 ; diff --git a/mysql-test/main/mysql_not_windows.result b/mysql-test/main/mysql_not_windows.result index 1df62d9a12d..96210a366a6 100644 --- a/mysql-test/main/mysql_not_windows.result +++ b/mysql-test/main/mysql_not_windows.result @@ -9,3 +9,5 @@ End of tests 2 X 3 +ERROR 1300 (HY000): Invalid utf8 character string: 'test\xF0\x9F\x98\x81 ' +ERROR 1300 (HY000): Invalid binary character string: 'test\xF0\x9F\x98\x81 ' diff --git a/mysql-test/main/mysql_not_windows.test b/mysql-test/main/mysql_not_windows.test index 591de74cbbf..816160c4f3e 100644 --- a/mysql-test/main/mysql_not_windows.test +++ b/mysql-test/main/mysql_not_windows.test @@ -22,3 +22,10 @@ exec $MYSQL test -e "select let $query = select 3 as X; exec $MYSQL test -e "$query"; + +# Not ran on Windows, since non-ASCII does not work on command line. +# (MDEV-16220) +--error 1 +--exec $MYSQL --default-character-set=utf8 -e "select 1" "test😁 " 2>&1 +--error 1 +--exec $MYSQL --default-character-set=binary -e "select 1" "test😁 " 2>&1 diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index ac490afe229..8032702ffbb 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -231,6 +231,11 @@ The following specify which files/extra groups are read (specified before remain cache, etc) --enforce-storage-engine=name Force the use of a storage engine for new tables + --eq-range-index-dive-limit=# + The optimizer will use existing index statistics instead + of doing index dives for equality ranges if the number of + equality ranges for the index is larger than or equal to + this number. If set to 0, index dives are always used. --event-scheduler[=name] Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely @@ -1404,6 +1409,7 @@ encrypt-binlog FALSE encrypt-tmp-disk-tables FALSE encrypt-tmp-files FALSE enforce-storage-engine (No default value) +eq-range-index-dive-limit 0 event-scheduler OFF expensive-subquery-limit 100 expire-logs-days 0 diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5bf157..fdbd932a433 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -486,34 +486,32 @@ a b deallocate prepare stmt; # use inside out access from tvc rows set @@in_predicate_conversion_threshold= default; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 range idx idx 5 NULL 5 100.00 Using index condition +1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10) +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4) set @@in_predicate_conversion_threshold= 2; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -540,9 +538,9 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) @@ -570,7 +568,7 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -578,7 +576,7 @@ explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -592,7 +590,7 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -608,7 +606,7 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) @@ -632,7 +630,7 @@ i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index d5c9a5cbd3d..2d06a0a0b20 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -255,7 +255,7 @@ deallocate prepare stmt; --echo # use inside out access from tvc rows -let $query= select * from t3 where a in (1,4,10); +let $query= select * from t3 where a in (1,4); set @@in_predicate_conversion_threshold= default; eval $query; eval explain extended $query; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index e2996b963bc..464c0bf031a 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1048,30 +1048,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 drop table t1,t2,t3; -create table t1 (a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, key(a)); -insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; -set @a="select * from t2 force index (a) where a NOT IN(0"; -select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; -count(*) -1000 -set @a=concat(@a, ')'); -insert into t2 values (11),(13),(15); -set @b= concat("explain ", @a); -prepare stmt1 from @b; -execute stmt1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -prepare stmt1 from @a; -execute stmt1; -a -11 -13 -15 -drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', b int NOT NULL DEFAULT '0', @@ -3005,5 +2981,46 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 36e0e32b28b..bd2299bac5f 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -863,30 +863,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b'; drop table t1,t2,t3; # -# BUG#21282 -# -create table t1 (a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, key(a)); -insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; - -set @a="select * from t2 force index (a) where a NOT IN(0"; -select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; -set @a=concat(@a, ')'); - -insert into t2 values (11),(13),(15); - -set @b= concat("explain ", @a); - -prepare stmt1 from @b; -execute stmt1; - -prepare stmt1 from @a; -execute stmt1; - -drop table t1, t2; - -# # Bug #18165: range access for BETWEEN with a constant for the first argument # @@ -2042,5 +2018,38 @@ set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; --echo # +--echo # MDEV-16934: using system variable eq_range_index_dive_limit +--echo # to reduce the number of index dives +--echo # + +create table t1 (a int, b varchar(31), index idx(a)); + +insert into t1 values + (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), + (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; + +analyze table t1; + +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; + +let $q= +select * from t1 where a in (8, 15, 31, 1, 9); + +set eq_range_index_dive_limit=0; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=2; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=default; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/range_debug.result b/mysql-test/main/range_debug.result new file mode 100644 index 00000000000..5597671071e --- /dev/null +++ b/mysql-test/main/range_debug.result @@ -0,0 +1,24 @@ +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, key(a)); +insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; +set @a="select * from t2 force index (a) where a NOT IN(0"; +select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; +count(*) +1000 +set @a=concat(@a, ')'); +insert into t2 values (11),(13),(15); +set @b= concat("explain ", @a); +prepare stmt1 from @b; +execute stmt1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index +prepare stmt1 from @a; +execute stmt1; +a +11 +13 +15 +set in_predicate_conversion_threshold= default; +drop table t1, t2; diff --git a/mysql-test/main/range_debug.test b/mysql-test/main/range_debug.test new file mode 100644 index 00000000000..ef331cdc84f --- /dev/null +++ b/mysql-test/main/range_debug.test @@ -0,0 +1,30 @@ +source include/have_debug.inc; +# +# BUG#21282 +# +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, key(a)); +insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; + +set in_predicate_conversion_threshold= 2000; + +set @a="select * from t2 force index (a) where a NOT IN(0"; +select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; +set @a=concat(@a, ')'); + +insert into t2 values (11),(13),(15); + +set @b= concat("explain ", @a); + +prepare stmt1 from @b; +execute stmt1; + +prepare stmt1 from @a; +execute stmt1; + +set in_predicate_conversion_threshold= default; + +drop table t1, t2; + + diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 629d183bee7..b132e63b732 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1050,30 +1050,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b'; a hex(filler) a 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 drop table t1,t2,t3; -create table t1 (a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, key(a)); -insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; -set @a="select * from t2 force index (a) where a NOT IN(0"; -select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; -count(*) -1000 -set @a=concat(@a, ')'); -insert into t2 values (11),(13),(15); -set @b= concat("explain ", @a); -prepare stmt1 from @b; -execute stmt1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -prepare stmt1 from @a; -execute stmt1; -a -11 -13 -15 -drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', b int NOT NULL DEFAULT '0', @@ -3017,6 +2993,47 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result index fc8d5759945..ec9ddb0452e 100644 --- a/mysql-test/main/subselect_extra_no_semijoin.result +++ b/mysql-test/main/subselect_extra_no_semijoin.result @@ -482,3 +482,25 @@ DROP TABLE t1,t2; set optimizer_switch= @tmp_subselect_extra_derived; set optimizer_switch= @subselect_extra_no_sj_tmp; set @optimizer_switch_for_subselect_extra_test=null; +# +# MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +# +connect con1,localhost,root,,; +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +a +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +create table t2 (b int); +insert into t2 values (1),(2),(3); +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; +disconnect con1; +connection default; diff --git a/mysql-test/main/subselect_extra_no_semijoin.test b/mysql-test/main/subselect_extra_no_semijoin.test index 4340f4165e7..681f92bd8c4 100644 --- a/mysql-test/main/subselect_extra_no_semijoin.test +++ b/mysql-test/main/subselect_extra_no_semijoin.test @@ -6,4 +6,33 @@ set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo set optimizer_switch= @subselect_extra_no_sj_tmp; -set @optimizer_switch_for_subselect_extra_test=null;
\ No newline at end of file +set @optimizer_switch_for_subselect_extra_test=null; + +--echo # +--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +--echo # + +## Using a separate client connection is easier than restoring state +connect(con1,localhost,root,,); + +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); + +create table t2 (b int); +insert into t2 values (1),(2),(3); + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); + +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; +disconnect con1; +connection default; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index e208dfe74c1..010ba03153b 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -499,7 +499,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null))) +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -509,7 +509,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null))) +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1925,7 +1925,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(`<subquery2>`.`MAX(c)` is null) or `<subquery2>`.`MAX(c)` = 7) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(/*always not null*/ 1 is null) or `<subquery2>`.`MAX(c)` = 7) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 7630b0b9c0f..983c6c31c65 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -520,7 +520,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null))) +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); a1 a2 1 - 01 2 - 01 @@ -530,7 +530,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null))) +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null))) select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); a1 a2 1 - 01 2 - 01 @@ -1963,7 +1963,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(`<subquery2>`.`MAX(c)` is null) or `<subquery2>`.`MAX(c)` = 7) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(/*always not null*/ 1 is null) or `<subquery2>`.`MAX(c)` = 7) SELECT * FROM t1 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b); a b diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9129fd0a23b..454356bb5fb 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2096,3 +2096,95 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-17017: TVC in derived table +# +create table t1 (a int); +insert into t1 values (9), (3), (2); +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +8 +1 +explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +1 11 +1 11 +7 77 +3 31 +4 42 +explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +select * from (values (7), (5), (8), (1) union select * from t1) t; +7 +7 +5 +8 +1 +9 +3 +2 +explain select * from (values (7), (5), (8), (1) union select * from t1) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; +# +# MDEV-16930: expression in the first row of TVC specifying derived table +# +SELECT 1 + 1, 2, "abc"; +1 + 1 2 abc +2 2 abc +SELECT * FROM (SELECT 1 + 1, 2, "abc") t; +1 + 1 2 abc +2 2 abc +WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte; +1 + 1 2 abc +2 2 abc +SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc"; +1 + 1 2 abc +2 2 abc +7 3 abc +CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc"; +SELECT * FROM v1; +1 + 1 2 abc +2 2 abc +DROP VIEW v1; +VALUES(1 + 1,2,"abc"); +1 + 1 2 abc +2 2 abc +SELECT * FROM (VALUES(1 + 1,2,"abc")) t; +1 + 1 2 abc +2 2 abc +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +DEALLOCATE PREPARE stmt; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59f829..0dd0a7a04b0 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,51 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-17017: TVC in derived table +--echo # + +create table t1 (a int); +insert into t1 values (9), (3), (2); + +let $q1= +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +eval $q1; +eval explain $q1; + +let $q2= +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +eval $q2; +eval explain $q2; + +let $q3= +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +eval $q3; +eval explain $q3; + +let $q4= +select * from (values (7), (5), (8), (1) union select * from t1) t; +eval $q4; +eval explain $q4; + +drop table t1; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # + +SELECT 1 + 1, 2, "abc"; +SELECT * FROM (SELECT 1 + 1, 2, "abc") t; +WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte; +SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc"; +CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc"; +SELECT * FROM v1; +DROP VIEW v1; + +VALUES(1 + 1,2,"abc"); +SELECT * FROM (VALUES(1 + 1,2,"abc")) t; +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index fd0fbefdcc5..97801b5e285 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -3219,8 +3219,8 @@ DROP TABLE fv_test, fv_result; # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (0),(1),(2); -SELECT LEAD(a) OVER (PARTITION BY a) as lead, -a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead, +a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part FROM t1; lead a_and_lead_part NULL 0 diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index cc16595fcd4..f038316ce98 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2000,8 +2000,8 @@ DROP TABLE fv_test, fv_result; CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (0),(1),(2); -SELECT LEAD(a) OVER (PARTITION BY a) as lead, - a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part +SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead, + a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part FROM t1; SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order diff --git a/mysql-test/main/win_lead_lag.result b/mysql-test/main/win_lead_lag.result index 5ead58fa11a..f3c0b8f7ef2 100644 --- a/mysql-test/main/win_lead_lag.result +++ b/mysql-test/main/win_lead_lag.result @@ -226,4 +226,15 @@ pk a b a+b lag(a + b) over (partition by a order by pk) + pk 9 2 2 4 12 10 2 0 2 14 11 2 10 12 13 +# +# MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause +# +select pk, +lag(pk, 1) over () +from t1; +ERROR HY000: No order list in window specification for 'lag' +select pk, +lead(pk, 1) over () +from t1; +ERROR HY000: No order list in window specification for 'lead' drop table t1; diff --git a/mysql-test/main/win_lead_lag.test b/mysql-test/main/win_lead_lag.test index 2824f83789c..d154244ecd0 100644 --- a/mysql-test/main/win_lead_lag.test +++ b/mysql-test/main/win_lead_lag.test @@ -107,4 +107,17 @@ select pk, a, b, a+b, from t1 order by pk asc; +--echo # +--echo # MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause +--echo # +--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC +select pk, + lag(pk, 1) over () +from t1; + +--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC +select pk, + lead(pk, 1) over () +from t1; + drop table t1; diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index 31dcecfdb18..18fce086f6e 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -2099,3 +2099,87 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-17017: TVC in derived table +# +create table t1 (a int); +insert into t1 values (9), (3), (2); +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +8 +1 +explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +1 11 +1 11 +7 77 +3 31 +4 42 +explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +select * from (values (7), (5), (8), (1) union select * from t1) t; +7 +7 +5 +8 +1 +9 +3 +2 +explain select * from (values (7), (5), (8), (1) union select * from t1) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; +# +# MDEV-16930: expression in the first row of TVC specifying derived table +# +SELECT 1 + 1, 2, 'abc'; +1 + 1 2 abc +2 2 abc +SELECT * FROM (SELECT 1 + 1, 2, 'abc') t; +1 + 1 2 abc +2 2 abc +WITH cte AS (SELECT 1 + 1, 2, 'abc') SELECT * FROM cte; +1 + 1 2 abc +2 2 abc +SELECT 1 + 1, 2, 'abc' UNION SELECT 3+4, 3, 'abc'; +1 + 1 2 abc +2 2 abc +7 3 abc +CREATE VIEW v1 AS SELECT 1 + 1, 2, 'abc'; +SELECT * FROM v1; +1 + 1 2 abc +2 2 abc +DROP VIEW v1; +VALUES(1 + 1,2,'abc'); +1 + 1 2 abc +2 2 abc +SELECT * FROM (VALUES(1 + 1,2,'abc')) t; +1 + 1 2 abc +2 2 abc diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test index 66519e93a36..37d25218664 100644 --- a/mysql-test/suite/compat/oracle/t/table_value_constr.test +++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test @@ -1081,3 +1081,47 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-17017: TVC in derived table +--echo # + +create table t1 (a int); +insert into t1 values (9), (3), (2); + +let $q1= +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +eval $q1; +eval explain $q1; + +let $q2= +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +eval $q2; +eval explain $q2; + +let $q3= +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +eval $q3; +eval explain $q3; + +let $q4= +select * from (values (7), (5), (8), (1) union select * from t1) t; +eval $q4; +eval explain $q4; + +drop table t1; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # + +SELECT 1 + 1, 2, 'abc'; +SELECT * FROM (SELECT 1 + 1, 2, 'abc') t; +WITH cte AS (SELECT 1 + 1, 2, 'abc') SELECT * FROM cte; +SELECT 1 + 1, 2, 'abc' UNION SELECT 3+4, 3, 'abc'; +CREATE VIEW v1 AS SELECT 1 + 1, 2, 'abc'; +SELECT * FROM v1; +DROP VIEW v1; + +VALUES(1 + 1,2,'abc'); +SELECT * FROM (VALUES(1 + 1,2,'abc')) t; diff --git a/mysql-test/suite/funcs_1/r/is_check_constraints.result b/mysql-test/suite/funcs_1/r/is_check_constraints.result new file mode 100644 index 00000000000..678cfb8db2f --- /dev/null +++ b/mysql-test/suite/funcs_1/r/is_check_constraints.result @@ -0,0 +1,144 @@ +# +# MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS +# +set check_constraint_checks=1; +use test; +create table t0 +( +t int, check (t>32) # table constraint +) ENGINE=myisam; +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +ALTER TABLE t0 +ADD CONSTRAINT CHK_t0_t CHECK(t<100); +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CHK_t0_t +TABLE_NAME t0 +CHECK_CLAUSE `t` < 100 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +ALTER TABLE t0 +DROP CONSTRAINT CHK_t0_t; +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +CREATE TABLE t1 +( t int CHECK(t>2), # field constraint +tt int, CONSTRAINT CHK_tt CHECK(tt<100) # table constraint +) ENGINE=InnoDB; +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CHK_tt +TABLE_NAME t1 +CHECK_CLAUSE `tt` < 100 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME t +TABLE_NAME t1 +CHECK_CLAUSE `t` > 2 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +ALTER TABLE t1 +DROP CONSTRAINT CHK_tt; +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME t +TABLE_NAME t1 +CHECK_CLAUSE `t` > 2 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +create table t2 +( +name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint +start_date DATE, +end_date DATE, +CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint +)ENGINE=Innodb; +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME name +TABLE_NAME t2 +CHECK_CLAUSE char_length(`name`) > 2 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CHK_dates +TABLE_NAME t2 +CHECK_CLAUSE `start_date` is null +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME t +TABLE_NAME t1 +CHECK_CLAUSE `t` > 2 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +ALTER TABLE t1 +ADD CONSTRAINT CHK_new_ CHECK(t>tt); +SELECT * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME name +TABLE_NAME t2 +CHECK_CLAUSE char_length(`name`) > 2 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CHK_dates +TABLE_NAME t2 +CHECK_CLAUSE `start_date` is null +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME t +TABLE_NAME t1 +CHECK_CLAUSE `t` > 2 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CONSTRAINT_1 +TABLE_NAME t0 +CHECK_CLAUSE `t` > 32 +CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA test +CONSTRAINT_NAME CHK_new_ +TABLE_NAME t1 +CHECK_CLAUSE `t` > `tt` +create table t3 +( +a int, +b int check (b>0), # field constraint named 'b' +CONSTRAINT b check (b>10) # table constraint +) ENGINE=InnoDB; +select * from information_schema.check_constraints order by check_clause; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME CHECK_CLAUSE +def test name t2 char_length(`name`) > 2 +def test b t3 `b` > 0 +def test b t3 `b` > 10 +def test CHK_dates t2 `start_date` is null +def test t t1 `t` > 2 +def test CONSTRAINT_1 t0 `t` > 32 +def test CHK_new_ t1 `t` > `tt` +drop table t0; +drop table t1; +drop table t2; +drop table t3; diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index d76efef3e08..dfaa5d75137 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -24,6 +24,11 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) select NEVER NULL def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) select NEVER NULL def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 3 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL +def information_schema CHECK_CONSTRAINTS TABLE_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL def information_schema CLIENT_STATISTICS ACCESS_DENIED 22 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL def information_schema CLIENT_STATISTICS BINLOG_BYTES_WRITTEN 9 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL def information_schema CLIENT_STATISTICS BUSY_TIME 5 0 NO double NULL NULL 21 NULL NULL NULL NULL double select NEVER NULL @@ -557,6 +562,11 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME varchar 32 96 utf8 utf8_general_ci varchar(32) 3.0000 information_schema CHARACTER_SETS DESCRIPTION varchar 60 180 utf8 utf8_general_ci varchar(60) NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint(3) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index b0d3c177580..96fb1e286c0 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -24,6 +24,11 @@ def information_schema CHARACTER_SETS CHARACTER_SET_NAME 1 '' NO varchar 32 96 N def information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME 2 '' NO varchar 32 96 NULL NULL NULL utf8 utf8_general_ci varchar(32) NEVER NULL def information_schema CHARACTER_SETS DESCRIPTION 3 '' NO varchar 60 180 NULL NULL NULL utf8 utf8_general_ci varchar(60) NEVER NULL def information_schema CHARACTER_SETS MAXLEN 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(3) NEVER NULL +def information_schema CHECK_CONSTRAINTS CHECK_CLAUSE 5 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG 1 '' NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME 3 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA 2 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL +def information_schema CHECK_CONSTRAINTS TABLE_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL def information_schema CLIENT_STATISTICS ACCESS_DENIED 22 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL def information_schema CLIENT_STATISTICS BINLOG_BYTES_WRITTEN 9 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL def information_schema CLIENT_STATISTICS BUSY_TIME 5 0 NO double NULL NULL 21 NULL NULL NULL NULL double NEVER NULL @@ -557,6 +562,11 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 information_schema CHARACTER_SETS DEFAULT_COLLATE_NAME varchar 32 96 utf8 utf8_general_ci varchar(32) 3.0000 information_schema CHARACTER_SETS DESCRIPTION varchar 60 180 utf8 utf8_general_ci varchar(60) NULL information_schema CHARACTER_SETS MAXLEN bigint NULL NULL NULL NULL bigint(3) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) +3.0000 information_schema CHECK_CONSTRAINTS CHECK_CLAUSE varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema CLIENT_STATISTICS CLIENT varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema CLIENT_STATISTICS TOTAL_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) NULL information_schema CLIENT_STATISTICS CONCURRENT_CONNECTIONS bigint NULL NULL NULL NULL bigint(21) diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result index e0e5a82a265..5fee1e0050a 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result @@ -89,6 +89,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY @@ -1105,6 +1130,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result index e0e5a82a265..5fee1e0050a 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result @@ -89,6 +89,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY @@ -1105,6 +1130,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME CHECK_CONSTRAINTS +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME CLIENT_STATISTICS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY diff --git a/mysql-test/suite/funcs_1/t/is_check_constraints.test b/mysql-test/suite/funcs_1/t/is_check_constraints.test new file mode 100644 index 00000000000..b39abdc1b24 --- /dev/null +++ b/mysql-test/suite/funcs_1/t/is_check_constraints.test @@ -0,0 +1,69 @@ +--source include/have_innodb.inc +--echo # +--echo # MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS +--echo # + +set check_constraint_checks=1; + +use test; +create table t0 +( + t int, check (t>32) # table constraint +) ENGINE=myisam; + +--vertical_results +SELECT * from information_schema.check_constraints order by check_clause; + +ALTER TABLE t0 +ADD CONSTRAINT CHK_t0_t CHECK(t<100); + +SELECT * from information_schema.check_constraints order by check_clause; + +ALTER TABLE t0 +DROP CONSTRAINT CHK_t0_t; + +SELECT * from information_schema.check_constraints order by check_clause; + +CREATE TABLE t1 +( t int CHECK(t>2), # field constraint + tt int, CONSTRAINT CHK_tt CHECK(tt<100) # table constraint +) ENGINE=InnoDB; + +SELECT * from information_schema.check_constraints order by check_clause; + +ALTER TABLE t1 +DROP CONSTRAINT CHK_tt; + +SELECT * from information_schema.check_constraints order by check_clause; + +create table t2 +( +name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint +start_date DATE, +end_date DATE, +CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint +)ENGINE=Innodb; + +SELECT * from information_schema.check_constraints order by check_clause; + +ALTER TABLE t1 +ADD CONSTRAINT CHK_new_ CHECK(t>tt); + +SELECT * from information_schema.check_constraints order by check_clause; + + +# Create table with same field and table check constraint name +create table t3 +( +a int, +b int check (b>0), # field constraint named 'b' +CONSTRAINT b check (b>10) # table constraint +) ENGINE=InnoDB; + +--horizontal_results +select * from information_schema.check_constraints order by check_clause; + +drop table t0; +drop table t1; +drop table t2; +drop table t3; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index c3b5e070158..28fc908c237 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -37,3 +37,10 @@ galera_ist_progress: MDEV-15236 galera_ist_progress fails when trying to read tr galera_concurrent_ctas : MDEV-15845 Test failure on galera.galera_concurrent_ctas pxc-421: Lock timeout exceeded galera_sst_mysqldump_with_key : MDEV-16890 Galera test failure +galera_sst_xtrabackup-v2-options : Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script +MW-328C : Timeouts +galera_gcs_fc_limit : Timeouts +pool_of_threads: WSREP has not yet prepared node for application use +galera_var_innodb_disallow_writes : Timeout +galera.galera_kill_ddl : MDEV-17108 Test failure on galera.galera_kill_ddl +MW-336 : nondeterministic wsrep_thread_count diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result index 0bf8d9d3909..1b5d2984e39 100644 --- a/mysql-test/suite/galera/r/MW-336.result +++ b/mysql-test/suite/galera/r/MW-336.result @@ -2,39 +2,99 @@ CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; connection node_1; SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 1; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); +COUNT(*) +11 +SHOW STATUS LIKE 'wsrep_thread_count'; +Variable_name Value +wsrep_thread_count 11 connection node_2; INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); connection node_1; SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 20; SET GLOBAL wsrep_slave_threads = 1; connection node_2; INSERT INTO t1 VALUES (1); -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (3); -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (5); -INSERT INTO t1 VALUES (6); -INSERT INTO t1 VALUES (7); -INSERT INTO t1 VALUES (8); -INSERT INTO t1 VALUES (9); -connection node_1; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 0; Warnings: Warning 1292 Truncated incorrect wsrep_slave_threads value: '0' connection node_2; -INSERT INTO t1 VALUES (10); -INSERT INTO t1 VALUES (11); -INSERT INTO t1 VALUES (12); -INSERT INTO t1 VALUES (13); -INSERT INTO t1 VALUES (14); -INSERT INTO t1 VALUES (15); -INSERT INTO t1 VALUES (16); -INSERT INTO t1 VALUES (17); -INSERT INTO t1 VALUES (18); -INSERT INTO t1 VALUES (19); -INSERT INTO t1 VALUES (20); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); connection node_1; SET GLOBAL wsrep_slave_threads = 1; +connection node_2; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (1); +connection node_1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MW-44.result b/mysql-test/suite/galera/r/MW-44.result index 459a61030a4..a1e55318422 100644 --- a/mysql-test/suite/galera/r/MW-44.result +++ b/mysql-test/suite/galera/r/MW-44.result @@ -1,21 +1,10 @@ SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -COUNT(*) -0 -SELECT * FROM mysql.general_log; -event_time user_host thread_id server_id command_type argument SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -COUNT(*) -0 -SELECT * FROM mysql.general_log; -event_time user_host thread_id server_id command_type argument SET GLOBAL general_log='ON'; -SELECT COUNT(*) from mysql.general_log; -COUNT(*) -1 +SELECT argument from mysql.general_log WHERE argument NOT LIKE 'SELECT%'; +argument SET SESSION wsrep_osu_method=TOI; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_osu_method=RSU; diff --git a/mysql-test/suite/galera/r/galera_sst_rsync_data_dir.result b/mysql-test/suite/galera/r/galera_sst_rsync_data_dir.result new file mode 100644 index 00000000000..d5c6a11f61f --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sst_rsync_data_dir.result @@ -0,0 +1,396 @@ +connection node_1; +connection node_2; +Performing State Transfer on a server that has been shut down cleanly and restarted +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +connection node_2; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Shutting down server ... +connection node_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_shutdown_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Starting server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_shutdown_slave; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that starts from a clean var directory +This is accomplished by shutting down node #2 and removing its var directory before restarting it +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +connection node_2; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Shutting down server ... +connection node_1; +Cleaning var directory ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_clean_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Starting server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_clean_slave; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that has been killed and restarted +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +connection node_2; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Killing server ... +connection node_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_kill_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Performing --wsrep-recover ... +Starting server ... +Using --wsrep-start-position when starting mysqld ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_kill_slave; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that has been killed and restarted +while a DDL was in progress on it +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +connection node_2; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; +connection node_1; +ALTER TABLE t1 ADD COLUMN f2 INTEGER; +connection node_2; +SET wsrep_sync_wait = 0; +Killing server ... +connection node_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 (f1) VALUES ('node1_committed_during'); +INSERT INTO t1 (f1) VALUES ('node1_committed_during'); +INSERT INTO t1 (f1) VALUES ('node1_committed_during'); +INSERT INTO t1 (f1) VALUES ('node1_committed_during'); +INSERT INTO t1 (f1) VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_kill_slave_ddl, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Performing --wsrep-recover ... +connection node_2; +Starting server ... +Using --wsrep-start-position when starting mysqld ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 (f1) VALUES ('node2_committed_after'); +INSERT INTO t1 (f1) VALUES ('node2_committed_after'); +INSERT INTO t1 (f1) VALUES ('node2_committed_after'); +INSERT INTO t1 (f1) VALUES ('node2_committed_after'); +INSERT INTO t1 (f1) VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 (f1) VALUES ('node1_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_committed_after'); +INSERT INTO t1 (f1) VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_kill_slave_ddl; +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; +COUNT(*) = 2 +1 +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; +COUNT(*) = 2 +1 +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_var_desync_on.result b/mysql-test/suite/galera/r/galera_var_desync_on.result index a26acbd4d6b..26798e51926 100644 --- a/mysql-test/suite/galera/r/galera_var_desync_on.result +++ b/mysql-test/suite/galera/r/galera_var_desync_on.result @@ -2,7 +2,6 @@ CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); connection node_2; SET GLOBAL wsrep_provider_options = 'gcs.fc_limit=1'; -SET GLOBAL wsrep_desync = TRUE; FLUSH TABLES WITH READ LOCK; connection node_1; INSERT INTO t1 VALUES (2); @@ -19,7 +18,6 @@ SET SESSION wsrep_sync_wait = 0; SELECT COUNT(*) = 1 FROM t1; COUNT(*) = 1 1 -SET GLOBAL wsrep_desync = FALSE; UNLOCK TABLES; SET SESSION wsrep_sync_wait = 1; SELECT COUNT(*) = 10 FROM t1; diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test index 8cd363aa019..4bdbfb2ecff 100644 --- a/mysql-test/suite/galera/t/MW-336.test +++ b/mysql-test/suite/galera/t/MW-336.test @@ -8,15 +8,28 @@ CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; --connection node_1 + SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 1; + +--let $wait_timeout=600 --let $wait_condition = SELECT COUNT(*) = 11 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); --source include/wait_condition.inc +SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); +SHOW STATUS LIKE 'wsrep_thread_count'; + --connection node_2 -INSERT INTO t1 VALUES (1); +# Generate 11 replication events +--let $count = 11 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} --connection node_1 + SET GLOBAL wsrep_slave_threads = 10; --let $wait_condition = SELECT COUNT(*) = 11 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); --source include/wait_condition.inc @@ -28,40 +41,40 @@ SET GLOBAL wsrep_slave_threads = 20; SET GLOBAL wsrep_slave_threads = 1; --connection node_2 -INSERT INTO t1 VALUES (1); -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (3); -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (5); -INSERT INTO t1 VALUES (6); -INSERT INTO t1 VALUES (7); -INSERT INTO t1 VALUES (8); -INSERT INTO t1 VALUES (9); - - ---connection node_1 ---let $wait_condition = SELECT COUNT(*) = 12 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); ---source include/wait_condition.inc +# Generate 21 replication events +--let $count = 21 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 0; --connection node_2 -INSERT INTO t1 VALUES (10); -INSERT INTO t1 VALUES (11); -INSERT INTO t1 VALUES (12); -INSERT INTO t1 VALUES (13); -INSERT INTO t1 VALUES (14); -INSERT INTO t1 VALUES (15); -INSERT INTO t1 VALUES (16); -INSERT INTO t1 VALUES (17); -INSERT INTO t1 VALUES (18); -INSERT INTO t1 VALUES (19); -INSERT INTO t1 VALUES (20); +# Generate 21 replication events +--let $count = 21 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} --connection node_1 --let $wait_condition = SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); --source include/wait_condition.inc SET GLOBAL wsrep_slave_threads = 1; + +--connection node_2 +# Generate 21 replication events +--let $count = 21 +while ($count) +{ + INSERT INTO t1 VALUES (1); + --dec $count +} + +--connection node_1 DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-44.test b/mysql-test/suite/galera/t/MW-44.test index 7c988a29548..e8caa28c80e 100644 --- a/mysql-test/suite/galera/t/MW-44.test +++ b/mysql-test/suite/galera/t/MW-44.test @@ -8,20 +8,21 @@ --connection node_1 SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -SELECT * FROM mysql.general_log; +--let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; +--source include/wait_condition.inc --sleep 1 --connection node_2 SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -SELECT * FROM mysql.general_log; +--let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; +--source include/wait_condition.inc --sleep 1 --connection node_1 SET GLOBAL general_log='ON'; -SELECT COUNT(*) from mysql.general_log; +SELECT argument from mysql.general_log WHERE argument NOT LIKE 'SELECT%'; + SET SESSION wsrep_osu_method=TOI; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_osu_method=RSU; diff --git a/mysql-test/suite/galera/t/galera_sst_rsync_data_dir.cnf b/mysql-test/suite/galera/t/galera_sst_rsync_data_dir.cnf new file mode 100644 index 00000000000..afe9796a11a --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sst_rsync_data_dir.cnf @@ -0,0 +1,11 @@ +!include ../galera_2nodes.cnf + +[mysqld] +wsrep_sst_method=rsync + +[mysqld.1] +wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.size=1;pc.ignore_sb=true' + +[mysqld.2] +innodb_data_home_dir=@ENV.MYSQL_TMP_DIR/rsync_test_2 +wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.size=1;pc.ignore_sb=true' diff --git a/mysql-test/suite/galera/t/galera_sst_rsync_data_dir.test b/mysql-test/suite/galera/t/galera_sst_rsync_data_dir.test new file mode 100644 index 00000000000..68aa1068f75 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sst_rsync_data_dir.test @@ -0,0 +1,16 @@ +--source include/big_test.inc +--source include/galera_cluster.inc + +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +--source suite/galera/include/galera_st_shutdown_slave.inc +--source suite/galera/include/galera_st_clean_slave.inc + +--source suite/galera/include/galera_st_kill_slave.inc +--source suite/galera/include/galera_st_kill_slave_ddl.inc +--source include/auto_increment_offset_restore.inc + +# cleanup temporary database files: +--remove_files_wildcard $MYSQL_TMP_DIR/rsync_test_2 * diff --git a/mysql-test/suite/galera/t/galera_var_desync_on.test b/mysql-test/suite/galera/t/galera_var_desync_on.test index 06c5d30a769..fbf660d3ab5 100644 --- a/mysql-test/suite/galera/t/galera_var_desync_on.test +++ b/mysql-test/suite/galera/t/galera_var_desync_on.test @@ -1,5 +1,7 @@ # -# Test wsrep_desync = ON . Node should temporarily not participate in flow control +# Desync will be done once the global read lock is acquired and resync will be done when +# it is released. +# Node should temporarily not participate in flow control # so even if fc_limit has been reached, the master should be able to continue to # commit transactions. # @@ -13,7 +15,6 @@ INSERT INTO t1 VALUES (1); --connection node_2 --let $wsrep_provider_options_orig = `SELECT @@wsrep_provider_options` SET GLOBAL wsrep_provider_options = 'gcs.fc_limit=1'; -SET GLOBAL wsrep_desync = TRUE; # Block the slave applier thread FLUSH TABLES WITH READ LOCK; @@ -37,8 +38,6 @@ SET SESSION wsrep_sync_wait = 0; # No updates have arrived after the FLUSH TABLES SELECT COUNT(*) = 1 FROM t1; -# Resync the slave -SET GLOBAL wsrep_desync = FALSE; --disable_query_log --eval SET GLOBAL wsrep_provider_options = '$wsrep_provider_options_orig'; --enable_query_log diff --git a/mysql-test/suite/maria/concurrent.result b/mysql-test/suite/maria/concurrent.result new file mode 100644 index 00000000000..caea6fd1fb9 --- /dev/null +++ b/mysql-test/suite/maria/concurrent.result @@ -0,0 +1,33 @@ +CREATE TABLE t1 (a INT, b CHAR(12), c INT, FULLTEXT KEY(b), KEY (c)) ENGINE=Aria; +CREATE TABLE t2 (a INT, b CHAR(12), c INT) ENGINE=Aria; +INSERT INTO t2 VALUES (1,'foo',8), (2,'bar',9); +connect con1,localhost,root,,test; +INSERT INTO t1 SELECT * FROM t2; +connection default; +select 1; +1 +1 +select 1; +1 +1 +select 1; +1 +1 +select 1; +1 +1 +select 1; +1 +1 +select 1; +1 +1 +select 1; +1 +1 +SELECT * FROM t1 WHERE a = ( SELECT 1 FROM non_existing_table2 ); +ERROR 42S02: Table 'test.non_existing_table2' doesn't exist +connection con1; +disconnect con1; +connection default; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/maria/concurrent.test b/mysql-test/suite/maria/concurrent.test new file mode 100644 index 00000000000..42adb082d40 --- /dev/null +++ b/mysql-test/suite/maria/concurrent.test @@ -0,0 +1,28 @@ +# +# MDEV-15797 Assertion `thd->killed != 0' failed in ha_maria::enable_indexes +# + +CREATE TABLE t1 (a INT, b CHAR(12), c INT, FULLTEXT KEY(b), KEY (c)) ENGINE=Aria; +CREATE TABLE t2 (a INT, b CHAR(12), c INT) ENGINE=Aria; +INSERT INTO t2 VALUES (1,'foo',8), (2,'bar',9); + +--connect (con1,localhost,root,,test) +--send + INSERT INTO t1 SELECT * FROM t2; +--connection default +select 1; +select 1; +select 1; +select 1; +select 1; +select 1; +select 1; +--error ER_NO_SUCH_TABLE +SELECT * FROM t1 WHERE a = ( SELECT 1 FROM non_existing_table2 ); +--connection con1 +--reap + +# Cleanup +--disconnect con1 +--connection default +DROP TABLE t1, t2; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 765a417e4f2..c17ae47322e 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -2227,9 +2227,9 @@ Block_size: 8192 Recordlength: 99 Table description: -Key Start Len Index Type -1 2 30 multip. varchar -2 33 30 multip. char NULL +Key Start Len Index Type +1 2 30 multip. varchar +2 33 30 multip. char NULL DROP TABLE t1; create table t1 (n int not null, c char(1)) transactional=1; show create table t1; diff --git a/mysql-test/suite/mariabackup/disabled.def b/mysql-test/suite/mariabackup/disabled.def deleted file mode 100644 index a7be04bfc16..00000000000 --- a/mysql-test/suite/mariabackup/disabled.def +++ /dev/null @@ -1 +0,0 @@ -unsupported_redo : MDEV-16791 allows optimized redo
\ No newline at end of file diff --git a/mysql-test/suite/mariabackup/skip_innodb.opt b/mysql-test/suite/mariabackup/skip_innodb.opt new file mode 100644 index 00000000000..213331f5575 --- /dev/null +++ b/mysql-test/suite/mariabackup/skip_innodb.opt @@ -0,0 +1 @@ +--loose-skip-innodb
\ No newline at end of file diff --git a/mysql-test/suite/mariabackup/skip_innodb.result b/mysql-test/suite/mariabackup/skip_innodb.result new file mode 100644 index 00000000000..0d56b55bf4b --- /dev/null +++ b/mysql-test/suite/mariabackup/skip_innodb.result @@ -0,0 +1,10 @@ +CREATE TABLE t(i int); +INSERT INTO t VALUES(1); +# shutdown server +# remove datadir +# xtrabackup move back +# restart server +SELECT * from t; +i +1 +DROP TABLE t; diff --git a/mysql-test/suite/mariabackup/skip_innodb.test b/mysql-test/suite/mariabackup/skip_innodb.test new file mode 100644 index 00000000000..14e4bc007f6 --- /dev/null +++ b/mysql-test/suite/mariabackup/skip_innodb.test @@ -0,0 +1,12 @@ +let $targetdir=$MYSQLTEST_VARDIR/tmp/backup; +CREATE TABLE t(i int); +INSERT INTO t VALUES(1); +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir; +exec $XTRABACKUP --prepare --target-dir=$targetdir; +-- source include/restart_and_restore.inc +--enable_result_log +SELECT * from t; +DROP TABLE t; + +rmdir $targetdir;
\ No newline at end of file diff --git a/mysql-test/suite/mariabackup/truncate_during_backup.result b/mysql-test/suite/mariabackup/truncate_during_backup.result new file mode 100644 index 00000000000..f9d5c785749 --- /dev/null +++ b/mysql-test/suite/mariabackup/truncate_during_backup.result @@ -0,0 +1,4 @@ +CREATE TABLE t1 ENGINE=InnoDB SELECT 1; +DROP TABLE t1; +SET GLOBAL innodb_log_checkpoint_now=1; +SET GLOBAL innodb_log_checkpoint_now=DEFAULT; diff --git a/mysql-test/suite/mariabackup/truncate_during_backup.test b/mysql-test/suite/mariabackup/truncate_during_backup.test new file mode 100644 index 00000000000..c01a74588e7 --- /dev/null +++ b/mysql-test/suite/mariabackup/truncate_during_backup.test @@ -0,0 +1,19 @@ +--source include/have_debug.inc +let $targetdir=$MYSQLTEST_VARDIR/tmp/backup; +mkdir $targetdir; + +CREATE TABLE t1 ENGINE=InnoDB SELECT 1; + +--let after_load_tablespaces=TRUNCATE test.t1 + +--disable_result_log +--error 1 +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir --dbug=+d,mariabackup_events; +--enable_result_log + +--let after_load_tablespaces= + +DROP TABLE t1; +SET GLOBAL innodb_log_checkpoint_now=1; +SET GLOBAL innodb_log_checkpoint_now=DEFAULT; +rmdir $targetdir; diff --git a/mysql-test/suite/mariabackup/unsupported_redo.result b/mysql-test/suite/mariabackup/unsupported_redo.result index a1f95c099cd..fbad89be0ac 100644 --- a/mysql-test/suite/mariabackup/unsupported_redo.result +++ b/mysql-test/suite/mariabackup/unsupported_redo.result @@ -7,7 +7,7 @@ call mtr.add_suppression("InnoDB: Cannot open datafile for read-only: "); call mtr.add_suppression("Table .* in the InnoDB data dictionary has tablespace id .*, but tablespace with that id or name does not exist"); CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB; ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; -# Fails during full backup +# No longer fails during full backup DROP TABLE t1; CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB; INSERT INTO t1(a) select 1 union select 2 union select 3; diff --git a/mysql-test/suite/mariabackup/unsupported_redo.test b/mysql-test/suite/mariabackup/unsupported_redo.test index 319ee2c7571..b9456751b9c 100644 --- a/mysql-test/suite/mariabackup/unsupported_redo.test +++ b/mysql-test/suite/mariabackup/unsupported_redo.test @@ -11,33 +11,15 @@ let $basedir=$MYSQLTEST_VARDIR/tmp/backup; let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1; CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB; ---source ../../suite/innodb/include/no_checkpoint_start.inc ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; -# Below mariabackup operation may complete successfully if checkpoint happens -# after the alter table command. - -echo # Fails during full backup; +echo # No longer fails during full backup; --disable_result_log ---error 0,1 exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir; --enable_result_log DROP TABLE t1; ---let MYSQLD_DATADIR=$basedir/ -perl; -open(OUT, ">$ENV{MYSQLTEST_VARDIR}/log/check.txt") || die; -print OUT ' ---let no_checkpoint_end=1 ---let CLEANUP_IF_CHECKPOINT=rmdir $basedir; ---source ../../suite/innodb/include/no_checkpoint_end.inc ---exit Backup failed to fail despite MLOG_INDEX_LOAD record -' if (-f "$ENV{MYSQLD_DATADIR}/xtrabackup_info"); -close(OUT); -EOF ---source $MYSQLTEST_VARDIR/log/check.txt ---remove_file $MYSQLTEST_VARDIR/log/check.txt rmdir $basedir; CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB; @@ -50,29 +32,14 @@ INSERT INTO t1(a) select 1 union select 2 union select 3; exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir; --enable_result_log ---source ../../suite/innodb/include/no_checkpoint_start.inc ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; --disable_result_log ---error 0,1 exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$incremental_dir --incremental-basedir=$basedir; --enable_result_log DROP TABLE t1; ---let MYSQLD_DATADIR=$incremental_dir/ -perl; -open(OUT, ">$ENV{MYSQLTEST_VARDIR}/log/check.txt") || die; -print OUT ' ---let no_checkpoint_end=1 ---let CLEANUP_IF_CHECKPOINT=rmdir $basedir;rmdir $incremental_dir; ---source ../../suite/innodb/include/no_checkpoint_end.inc ---exit Backup failed to fail despite MLOG_INDEX_LOAD record -' if (-f "$ENV{MYSQLD_DATADIR}/xtrabackup_info"); -close(OUT); -EOF ---source $MYSQLTEST_VARDIR/log/check.txt ---remove_file $MYSQLTEST_VARDIR/log/check.txt rmdir $basedir;rmdir $incremental_dir; CREATE TABLE t1(i INT) ENGINE INNODB; diff --git a/mysql-test/suite/plugins/r/auth_ed25519.result b/mysql-test/suite/plugins/r/auth_ed25519.result index ee9320bbc6c..4785bef3ef7 100644 --- a/mysql-test/suite/plugins/r/auth_ed25519.result +++ b/mysql-test/suite/plugins/r/auth_ed25519.result @@ -33,7 +33,7 @@ PLUGIN_DESCRIPTION Elliptic curve ED25519 based authentication PLUGIN_LICENSE GPL LOAD_OPTION ON PLUGIN_MATURITY Stable -PLUGIN_AUTH_VERSION 1.0-alpha +PLUGIN_AUTH_VERSION 1.0 create user test1@localhost identified via ed25519 using 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY'; show grants for test1@localhost; Grants for test1@localhost diff --git a/mysql-test/suite/rpl/r/rpl_row_spatial.result b/mysql-test/suite/rpl/r/rpl_row_spatial.result new file mode 100644 index 00000000000..86bac89a174 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_spatial.result @@ -0,0 +1,16 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (g POINT NOT NULL, SPATIAL INDEX(g)); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 2)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 2)')); +DELETE FROM t1 where MBREqual(g, ST_GEOMFROMTEXT('Point(1 2)')); +connection slave; +select count(*) from t1; +count(*) +3 +connection master; +DELETE FROM t1; +drop table t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_spatial.test b/mysql-test/suite/rpl/t/rpl_row_spatial.test new file mode 100644 index 00000000000..00c3dd7c54d --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_spatial.test @@ -0,0 +1,17 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +CREATE TABLE t1 (g POINT NOT NULL, SPATIAL INDEX(g)); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 1)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(1 2)')); +INSERT INTO t1 VALUES (ST_GEOMFROMTEXT('Point(2 2)')); +DELETE FROM t1 where MBREqual(g, ST_GEOMFROMTEXT('Point(1 2)')); + +--sync_slave_with_master +select count(*) from t1; + +--connection master +DELETE FROM t1; +drop table t1; +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/auto_increment.result b/mysql-test/suite/sql_sequence/auto_increment.result new file mode 100644 index 00000000000..bf0a04bbaf1 --- /dev/null +++ b/mysql-test/suite/sql_sequence/auto_increment.result @@ -0,0 +1,30 @@ +set global auto_increment_increment= 2, auto_increment_offset= 2; +create sequence s start with -3 minvalue= -1000 increment 0; +select nextval(s); +nextval(s) +-2 +select nextval(s); +nextval(s) +0 +flush tables; +select nextval(s); +nextval(s) +1998 +drop sequence s; +set global auto_increment_increment= 2, auto_increment_offset= 1; +create sequence s start with -3 minvalue= -1000 increment 0; +select nextval(s); +nextval(s) +-3 +select nextval(s); +nextval(s) +-1 +select nextval(s); +nextval(s) +1 +flush tables; +select nextval(s); +nextval(s) +1997 +drop sequence s; +set global auto_increment_increment= default, auto_increment_offset= default; diff --git a/mysql-test/suite/sql_sequence/auto_increment.test b/mysql-test/suite/sql_sequence/auto_increment.test new file mode 100644 index 00000000000..335bef7ea37 --- /dev/null +++ b/mysql-test/suite/sql_sequence/auto_increment.test @@ -0,0 +1,30 @@ +--source include/have_sequence.inc + +# +# tests with auto_increment_increment and auto_increment_offset +# + +set global auto_increment_increment= 2, auto_increment_offset= 2; + +create sequence s start with -3 minvalue= -1000 increment 0; + +select nextval(s); +select nextval(s); +flush tables; +select nextval(s); +drop sequence s; + +set global auto_increment_increment= 2, auto_increment_offset= 1; + +create sequence s start with -3 minvalue= -1000 increment 0; + +select nextval(s); +select nextval(s); +select nextval(s); +flush tables; +select nextval(s); +drop sequence s; + +# Clean up + +set global auto_increment_increment= default, auto_increment_offset= default; diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index d138c342c9d..b1b1bb69306 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -519,3 +519,18 @@ create temporary table tmp (i int); select next value for tmp; ERROR 42S02: 'test.tmp' is not a SEQUENCE drop table tmp; +# +# Test negative numbers +# +create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1; +select next value for s; +next value for s +1 +select next value for s; +next value for s +0 +flush tables; +select next value for s; +next value for s +-999 +drop sequence s; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index 79249002d8f..a1f91bd7823 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -269,3 +269,14 @@ create temporary table tmp (i int); --error ER_NOT_SEQUENCE select next value for tmp; drop table tmp; + +--echo # +--echo # Test negative numbers +--echo # + +create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1; +select next value for s; +select next value for s; +flush tables; +select next value for s; +drop sequence s; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 01c426b117f..e5a684e71fe 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -880,6 +880,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 9f9513867a9..08214e3e888 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -894,6 +894,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/suite/vcol/r/index.result b/mysql-test/suite/vcol/r/index.result index 8860a728bd1..6ab9aa5eb29 100644 --- a/mysql-test/suite/vcol/r/index.result +++ b/mysql-test/suite/vcol/r/index.result @@ -89,3 +89,13 @@ pk left(c, 10) length(c) i 1 bar bar ba 60000 11 drop table t1; disconnect c1; +CREATE TABLE t1 (b BLOB, vb TEXT AS (b) PERSISTENT, KEY(vb(64))) ENGINE=InnoDB; +INSERT INTO t1 (b) VALUES ('foo'); +connect con1,localhost,root,,test; +CREATE TABLE t2 LIKE t1; +connection default; +DELETE FROM t1; +connection con1; +disconnect con1; +connection default; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/vcol/t/index.test b/mysql-test/suite/vcol/t/index.test index 72eed0a8a40..b93c337fab6 100644 --- a/mysql-test/suite/vcol/t/index.test +++ b/mysql-test/suite/vcol/t/index.test @@ -79,3 +79,19 @@ commit; select pk, left(c, 10), length(c), i from t1; drop table t1; disconnect c1; + +# +# MDEV-16961 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed upon concurrent DELETE and DDL with virtual blob column +# + +CREATE TABLE t1 (b BLOB, vb TEXT AS (b) PERSISTENT, KEY(vb(64))) ENGINE=InnoDB; +INSERT INTO t1 (b) VALUES ('foo'); +--connect (con1,localhost,root,,test) +--send CREATE TABLE t2 LIKE t1 +--connection default +DELETE FROM t1; +--connection con1 +--reap +--disconnect con1 +--connection default +DROP TABLE t1, t2; diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index 308501915dc..d38b9eed162 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -100,5 +100,11 @@ ERROR 42S02: 'v' is a view unlock tables; drop view v; drop table t; +create table t1 (i int) with system versioning; +create procedure pr() delete history from t1 before system_time now(); +call pr; +call pr; +drop procedure pr; +drop table t1; drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/truncate.test index 4e039a76567..1727c7787ef 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/truncate.test @@ -107,5 +107,15 @@ unlock tables; drop view v; drop table t; +# +# MDEV-16783 Assertion `!conds' failed in mysql_delete upon 2nd execution of SP with DELETE HISTORY +# +create table t1 (i int) with system versioning; +create procedure pr() delete history from t1 before system_time now(); +call pr; +call pr; +drop procedure pr; +drop table t1; + drop database test; create database test; diff --git a/mysql-test/suite/wsrep/t/variables.test b/mysql-test/suite/wsrep/t/variables.test index 1315f090d5c..40b869422ff 100644 --- a/mysql-test/suite/wsrep/t/variables.test +++ b/mysql-test/suite/wsrep/t/variables.test @@ -101,8 +101,10 @@ SHOW STATUS LIKE 'wsrep_thread_count'; --echo # Setting wsrep_cluster_address triggers the creation of --echo # applier/rollbacker threads. SET GLOBAL wsrep_cluster_address= 'gcomm://'; + --echo # Wait for applier threads to get created. -sleep 3; +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; +--source include/wait_condition.inc --replace_regex /.*libgalera_smm.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; @@ -113,8 +115,11 @@ SHOW STATUS LIKE 'wsrep_thread_count'; SET @wsrep_slave_threads_saved= @@global.wsrep_slave_threads; SET GLOBAL wsrep_slave_threads= 10; + --echo # Wait for applier threads to get created. -sleep 3; +--let $wait_condition = SELECT VARIABLE_VALUE = 11 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; +--source include/wait_condition.inc + SHOW STATUS LIKE 'threads_connected'; SHOW STATUS LIKE 'wsrep_thread_count'; diff --git a/mysql-test/unstable-tests b/mysql-test/unstable-tests index d97792355b4..2c28c9a663b 100644 --- a/mysql-test/unstable-tests +++ b/mysql-test/unstable-tests @@ -23,7 +23,7 @@ # ############################################################################## -# Based on bb-10.3-release 36e59752e7fc70bc5179a3d730b5ce3ee58e4e30 +# Based on bb-10.3-release fac3e575b203e8d6a522a475f9aab4ec5041b146 #----------------------------------------------------------------------- @@ -32,12 +32,17 @@ archive.archive_symlink : MDEV-12170 archive.discover : MDEV-10510 - Table is marked as crashed archive.mysqlhotcopy_archive : MDEV-10995 - Hang on debug +archive-test_sql_discovery.discover : MDEV-16817 - Table marked as crashed + #----------------------------------------------------------------------- binlog.binlog_commit_wait : MDEV-10150 - Mismatch binlog.binlog_flush_binlogs_delete_domain : MDEV-14431 - Wrong exit code binlog.binlog_killed : MDEV-12925 - Wrong result +binlog.binlog_tmp_table_row : Added in 10.1.35, 10.2.17 binlog.binlog_xa_recover : MDEV-8517 - Extra checkpoint +binlog.load_data_stm_view : MDEV-16948 - Wrong result +binlog_tmp_table_row : Added in 10.3.9 #----------------------------------------------------------------------- @@ -47,6 +52,8 @@ binlog_encryption.encrypted_master_switch_to_unencrypted : MDEV-14190 binlog_encryption.encrypted_slave : Modified in 10.3.8 binlog_encryption.encryption_combo : MDEV-14199 - Table is marked as crashed binlog_encryption.rpl_binlog_errors : MDEV-12742 - Crash +binlog_encryption.rpl_checksum : MDEV-16951 - Wrong result +binlog_encryption.rpl_gtid_basic : MDEV-16947 - Server failed to start binlog_encryption.rpl_loadfile : MDEV-16645 - Timeout in include binlog_encryption.rpl_parallel : MDEV-10653 - Timeout in include binlog_encryption.rpl_relayrotate : MDEV-15194 - Timeout @@ -55,17 +62,13 @@ binlog_encryption.rpl_skip_replication : MDEV-13571 binlog_encryption.rpl_ssl : MDEV-14507 - Timeouts binlog_encryption.rpl_stm_relay_ign_space : MDEV-13278 - Wrong result (test assertion) binlog_encryption.rpl_sync : MDEV-13830 - Assertion failure +binlog_encryption.rpl_typeconv : MDEV-14362 - Lost connection to MySQL server during query #----------------------------------------------------------------------- -compat/oracle.column_compression : Added in 10.3.7 -compat/oracle.func_concat : Modified in 10.3.7 -compat/oracle.gis : Added in 10.3.7 +compat/oracle.events : Added in 10.3.9 compat/oracle.parser : Modified in 10.3.8 compat/oracle.sp-cursor-rowtype : Modified in 10.3.8 -compat/oracle.table_value_constr : Added in 10.3.7 -compat/oracle.versioning : Added in 10.3.7 -compat/oracle.win : Added in 10.3.7 #----------------------------------------------------------------------- @@ -79,7 +82,7 @@ encryption.create_or_replace : MDEV-12694 encryption.debug_key_management : MDEV-13841 - Timeout encryption.encrypt_and_grep : MDEV-13765 - Wrong result encryption.innochecksum : MDEV-13644 - Assertion failure -encryption.innodb-checksum-algorithm : MDEV-12898 - Deadlock of threads +encryption.innodb-checksum-algorithm : MDEV-12898 - Deadlock of threads; MDEV-16896 - Server crash encryption.innodb-compressed-blob : MDEV-14728 - Unable to get certificate encryption.innodb-discard-import : Modified in 10.3.8 encryption.innodb-encryption-alter : MDEV-13566 - Lock wait timeout @@ -97,6 +100,7 @@ encryption.innodb_encryption_discard_import : MDEV-16116 encryption.innodb_encryption_filekeys : MDEV-15673 - Timeout encryption.innodb_encryption_row_compressed : MDEV-16113 - Crash encryption.innodb_first_page : MDEV-10689 - Crash +encryption.innodb_lotoftables : MDEV-16111 - Wrong result encryption.innodb_scrub : MDEV-8139 - scrubbing tests need fixing encryption.innodb_scrub_background : MDEV-8139 - scrubbing tests need fixing encryption.innodb_scrub_compressed : MDEV-8139 - scrubbing tests need fixing @@ -109,18 +113,17 @@ engines/rr_trx.* : MDEV-10998 #----------------------------------------------------------------------- -federated.assisted_discovery : Modified in 10.3.7 +federated.assisted_discovery : Include file modified in 10.0.36 federated.federated_bug_35333 : MDEV-13410 - Wrong result federated.federated_bug_585688 : MDEV-14805 - Server crash, MDEV-12907 - Valgrind federated.federated_innodb : MDEV-10617 - Wrong checksum federated.federated_partition : MDEV-10417 - Fails on Mips federated.federated_transactions : MDEV-10617 - Wrong checksum federated.federatedx : MDEV-10617 - Wrong checksum -federated.federatedx_versioning : Added in 10.3.7 -federated.timestamps : Added in 10.3.7 #----------------------------------------------------------------------- +funcs_1.is_engines_federated : Include file modified in 10.0.36 funcs_1.memory_views : MDEV-11773 - timeout funcs_1.processlist_val_no_prot : MDEV-11223 - Wrong result funcs_1.processlist_val_ps : MDEV-12175 - Wrong plan @@ -153,63 +156,73 @@ galera_3nodes.* : Suite is no #----------------------------------------------------------------------- +gcol.gcol_rollback : MDEV-16954 - Unknown storage engine 'InnoDB' +gcol.gcol_update : Include file modified in 10.2.17, 10.3.9 +gcol.innodb_virtual_basic : MDEV-16950 - Failing assertion gcol.innodb_virtual_debug : MDEV-14134 - Crash, assertion failure; modified in 10.3.8 -gcol.innodb_virtual_index : Modified in 10.3.7 +gcol.innodb_virtual_debug_purge : MDEV-16952 - Wrong result; modified in 10.2.17, 10.3.9 +gcol.innodb_virtual_index : Include file modified in 10.2.17, 10.3.9 +gcol.innodb_virtual_purge : Include file modified in 10.3.9 #----------------------------------------------------------------------- -handler.heap : Modified in 10.3.7 -handler.innodb : Modified in 10.3.7 -handler.interface : Modified in 10.3.7 handler.ps : Added in 10.3.8 #----------------------------------------------------------------------- -heap.heap_auto_increment : MDEV-16652 - Out of range, modified in 10.3.8 +heap.heap_auto_increment : Modified in 10.3.8 #----------------------------------------------------------------------- innodb.101_compatibility : MDEV-13891 - Wrong result innodb.alter_copy : MDEV-16181 - Assertion failure -innodb.alter_foreign_crash : Added in 10.3.7 -innodb.alter_kill : MDEV-16273 - Unknown storage engine 'InnoDB'; added in 10.3.7 -innodb.alter_non_null : Added in 10.3.8 -innodb.alter_non_null_debug : Added in 10.3.8 -innodb.alter_not_null : Modified in 10.3.8 -innodb.alter_partitioned : Added in 10.3.7 -innodb.alter_rename_files : Added in 10.3.7 +innodb.alter_crash : MDEV-16944 - The process cannot access the file +innodb.alter_foreign_crash : Added in 10.2.16 +innodb.alter_kill : MDEV-16273 - Unknown storage engine 'InnoDB', MDEV-16946 - Wrong result +innodb.alter_not_null : Modified in 10.3.9 +innodb.alter_not_null_debug : Added in 10.3.9 +innodb.alter_partitioned_xa : Added in 10.0.36 +innodb.alter_rename_files : Added in 10.2.16 innodb.alter_sql_mode : Combinations added in 10.3.8 -innodb.analyze_table : Added in 10.3.7 +innodb.analyze_table : Added in 10.2.16 innodb.autoinc_persist : MDEV-15282 - Assertion failure innodb.binlog_consistent : MDEV-10618 - Server fails to start +innodb.dml_purge : Include file modified in 10.3.9 innodb.doublewrite : MDEV-12905 - Server crash innodb.group_commit_crash : MDEV-14191 - InnoDB registration failed innodb.group_commit_crash_no_optimize_thread : MDEV-13830 - Assertion failure +innodb.index_merge_threshold : Include files modified in 10.2.17, 10.3.9 +innodb.innodb-16k : Modified in 10.3.9 +innodb.innodb-32k-crash : MDEV-16953 - Corrupt log record found innodb.innodb-64k-crash : MDEV-13872 - Failure and crash on startup +innodb.innodb-alter : Modified in 10.0.36 innodb.innodb-alter-debug : MDEV-13182 - InnoDB: adjusting FSP_SPACE_FLAGS innodb.innodb-alter-table : MDEV-10619 - Testcase timeout innodb.innodb-alter-tempfile : MDEV-15285 - Table already exists innodb.innodb-alter-timestamp : Modified in 10.3.8 innodb.innodb-autoinc : Modified in 10.3.8 innodb.innodb-blob : Modified in 10.3.8 +innodb.innodb-corrupted-table : Modified in 10.3.9 innodb.innodb-fk : MDEV-13832 - Assertion failure on shutdown innodb.innodb-get-fk : MDEV-13276 - Server crash innodb.innodb-index-online : MDEV-14809 - Cannot save statistics +innodb.innodb-mdev-7513 : Modified in 10.3.9 innodb.innodb-mdev7046 : Modified in 10.3.8 -innodb.innodb-online-alter-gis : Modified in 10.3.7 +innodb.innodb-online-alter-gis : Modified in 10.2.16 innodb.innodb-page_compression_default : MDEV-13644 - Assertion failure; modified in 10.3.8 innodb.innodb-page_compression_lzma : MDEV-14353 - Wrong result innodb.innodb-page_compression_snappy : /MDEV-13644 - Assertion failure; modified in 10.3.8 innodb.innodb-page_compression_zip : MDEV-10641 - mutex problem innodb.innodb-table-online : MDEV-13894 - Wrong result; modified in 10.3.8 -innodb.innodb-wl5522 : MDEV-13644 - Assertion failure +innodb.innodb-wl5522 : MDEV-13644 - Assertion failure; modified in 10.3.9 innodb.innodb-wl5522-debug : MDEV-14200 - Wrong errno -innodb.innodb_bug13510739 : Modified in 10.3.7 innodb.innodb_bug14147491 : MDEV-11808 - Index is corrupt innodb.innodb_bug30423 : MDEV-7311 - Wrong result innodb.innodb_bug48024 : MDEV-14352 - Assertion failure -innodb.innodb_bug54044 : Modified in 10.3.7 +innodb.innodb_bug54044 : Modified in 10.2.16 innodb.innodb_bug59641 : MDEV-13830 - Assertion failure +innodb.innodb_buffer_pool_resize : MDEV-16964 - Assertion failure +innodb.innodb_buffer_pool_resize_with_chunks : MDEV-16964 - Assertion failure innodb.innodb_bulk_create_index_replication : MDEV-15273 - Slave failed to start innodb.innodb_defrag_stats_many_tables : MDEV-14198 - Table is full innodb.innodb_defragment_small : Modified in 10.3.8 @@ -219,49 +232,60 @@ innodb.innodb_max_recordsize_64k : MDEV-15203 innodb.innodb_monitor : MDEV-10939 - Testcase timeout innodb.innodb_query_cache : Added in 10.3.8 innodb.innodb_stats : MDEV-10682 - wrong result +innodb.innodb_stats_persistent : Include file modified in 10.2.17, 10.3.9 innodb.innodb_stats_persistent_debug : MDEV-14801 - Operation failed innodb.innodb_sys_semaphore_waits : MDEV-10331 - Semaphore wait innodb.innodb_zip_innochecksum2 : MDEV-13882 - Extra warnings innodb.innodb_zip_innochecksum3 : MDEV-14486 - Resource temporarily unavailable +innodb.instant_alter : Modified in 10.3.9 +innodb.instant_alter_crash : Include file modified in 10.3.9 +innodb.instant_alter_debug : Modified in 10.3.9 +innodb.instant_alter_rollback : Include file modified in 10.3.9 innodb.log_corruption : MDEV-13251 - Wrong result innodb.log_data_file_size : MDEV-14204 - Server failed to start innodb.log_file_name : MDEV-14193 - Exception innodb.log_file_size : MDEV-15668 - Not found pattern -innodb.monitor : MDEV-16179 - Wrong result -innodb.purge_secondary : MDEV-15681 - Wrong result +innodb.monitor : MDEV-16179 - Wrong result; modified in 10.3.9 +innodb.purge_secondary : MDEV-15681 - Wrong result; include file modified in 10.3.9 innodb.purge_thread_shutdown : MDEV-13792 - Wrong result innodb.read_only_recovery : MDEV-13886 - Server crash innodb.recovery_shutdown : MDEV-15671 - Checksum mismatch in datafile innodb.rename_table : Modified in 10.3.8 innodb.row_format_redundant : MDEV-15192 - Trying to access missing tablespace innodb.sp_temp_table : MDEV-16647 - Could not remove temporary table +innodb.strict_mode : Modified in 10.3.9 innodb.table_definition_cache_debug : MDEV-14206 - Extra warning innodb.table_flags : MDEV-13572 - Wrong result innodb.temp_table_savepoint : MDEV-16182 - Wrong result -innodb.temporary_table : MDEV-13265 - Wrong result; modified in 10.3.7 -innodb.tmpdir : Modified in 10.3.7 +innodb.temporary_table : MDEV-13265 - Wrong result; modified in 10.3.9 +innodb.truncate_purge_debug : Include file modified in 10.2.17, 10.3.9 innodb.update_time : MDEV-14804 - Wrong result +innodb.undo_log : Include file modified in 10.2.17, 10.3.9 innodb.xa_recovery : MDEV-15279 - mysqld got exception #----------------------------------------------------------------------- -innodb_fts.fulltext2 : Modified in 10.3.7 -innodb_fts.fulltext_var : Modified in 10.3.7 +innodb_fts.basic : Added in 10.0.36 +innodb_fts.fts_kill_query : Added in 10.1.35, 10.2.17, 10.3.9 innodb_fts.innodb-fts-fic : MDEV-14154 - Assertion failure -innodb_fts.innodb_fts_misc : Modified in 10.3.7 innodb_fts.innodb_fts_misc_debug : MDEV-14156 - Unexpected warning +innodb_fts.innodb_fts_multiple_index : Modified in 10.3.9 innodb_fts.innodb_fts_plugin : MDEV-13888 - Errors in server log innodb_fts.innodb_fts_stopword_charset : MDEV-13259 - Table crashed innodb_fts.sync : MDEV-14808 - Wrong result +innodb_fts.sync_ddl : Added in 10.1.35, 10.2.17, 10.3.9 #----------------------------------------------------------------------- +innodb_gis.create_spatial_index : Modified in 10.2.17, 10.3.9 +innodb_gis.kill_server : MDEV-16941 - Checksum mismatch +innodb_gis.rtree_compress : Include file modified in 10.2.17, 10.3.9 innodb_gis.rtree_compress2 : MDEV-16269 - Wrong result innodb_gis.rtree_concurrent_srch : MDEV-15284 - Wrong result with embedded -innodb_gis.rtree_purge : MDEV-15275 - Timeout +innodb_gis.rtree_purge : MDEV-15275 - Timeout; include file modified in 10.3.9 innodb_gis.rtree_recovery : MDEV-15274 - Error on check innodb_gis.rtree_split : MDEV-14208 - Too many arguments -innodb_gis.rtree_undo : MDEV-14456 - Timeout in include file +innodb_gis.rtree_undo : MDEV-14456 - Timeout in include file; include file modified in 10.3.9 innodb_gis.types : MDEV-15679 - Table is marked as crashed #----------------------------------------------------------------------- @@ -276,51 +300,70 @@ innodb_zip.wl6501_scale_1 : MDEV-13254 #----------------------------------------------------------------------- main.alter_table : Modified in 10.3.8 -main.alter_table_errors : Added in 10.3.7 main.alter_table_trans : MDEV-12084 - timeout main.analyze_stmt_slow_query_log : MDEV-12237 - Wrong result -main.ansi : Modified in 10.3.7 +main.assign_key_cache : Added in 10.0.36 +main.assign_key_cache_debug : Added in 10.0.36 main.auth_named_pipe : MDEV-14724 - System error 2 -main.auto_increment : MDEV-16652 - Out of range, modified in 10.3.8 +main.auto_increment : Modified in 10.3.8 +main.bootstrap : Modified in 10.1.35, 10.2.17, 10.3.9 +main.check_constraint : Modified in 10.2.17, 10.3.9 main.check : Modified in 10.3.8 -main.check_constraint : Modified in 10.3.7 -main.connect : MDEV-16270 - Wrong result +main.column_compression : Modified in 10.3.9 main.connect2 : MDEV-13885 - Server crash +main.connect_debug : Added in 10.0.36 +main.connect : MDEV-16270 - Wrong result main.count_distinct2 : MDEV-11768 - timeout main.create_delayed : MDEV-10605 - failed with timeout main.create_drop_event : MDEV-16271 - Wrong result -main.create_or_replace : Modified in 10.3.7 +main.create_or_replace : Modified in 10.2.16 +main.create_replace_tmp : Added in 10.2.17, 10.3.9 main.cte_nonrecursive : Modified in 10.3.8 -main.cte_recursive : Modified in 10.3.7 -main.ctype_utf16le : MDEV-10675: timeout or extra warnings -main.custom_aggregate_functions : Modified in 10.3.7 +main.cte_recursive : Modified in 10.2.17, 10.3.9 +main.cte_recursive_not_embedded : Added in 10.2.17, 10.3.9 +main.ctype_binary : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_eucjpms : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_euckr : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_gbk : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_latin1 : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_ucs : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_ujis : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_upgrade : MDEV-16945 - Error upon mysql_upgrade +main.ctype_utf16le : MDEV-10675: timeout or extra warnings; modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_utf16 : MDEV-10675: timeout or extra warnings; modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_utf32 : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_utf8mb4 : Modified in 10.1.35, 10.2.17, 10.3.9 +main.ctype_utf8 : Modified in 10.1.35, 10.2.17, 10.3.9 main.debug_sync : MDEV-10607 - internal error -main.derived_cond_pushdown : Modified in 10.3.8 +main.derived_cond_pushdown : Modified in 10.2.17, 10.3.9 +main.derived : Modified in 10.1.35, 10.2.17, 10.3.9 main.derived_opt : MDEV-11768 - timeout main.derived_view : Modified in 10.3.8 -main.distinct : MDEV-14194 - Crash; modified in 10.3.7 +main.distinct : MDEV-14194 - Crash main.drop_bad_db_type : MDEV-15676 - Wrong result main.events_2 : MDEV-13277 - Crash main.events_bugs : MDEV-12892 - Crash main.events_restart : MDEV-12236 - Server shutdown problem main.events_slowlog : MDEV-12821 - Wrong result -main.explain_slowquerylog : Modified in 10.3.7 -main.func_json : Modified in 10.3.8 +main.explain_slowquerylog : Modified in 10.2.16 +main.func_json : Modified in 10.3.9 main.func_misc : Modified in 10.3.8 -main.func_time : Modified in 10.3.8 main.func_time_hires : Modified in 10.3.8 +main.func_time : Modified in 10.3.9 main.gis : MDEV-13411 - wrong result on P8 -main.grant : Modified in 10.3.8 main.grant2 : Modified in 10.3.8 -main.grant_not_windows : Added in 10.3.7 +main.grant : Modified in 10.3.9 +main.grant_not_windows : Added in 10.2.16 main.having : Modified in 10.3.8 main.host_cache_size_functionality : MDEV-10606 - sporadic failure on shutdown main.index_intersect_innodb : MDEV-10643 - failed with timeout main.index_merge_innodb : MDEV-7142 - Plan mismatch main.innodb_mysql_lock : MDEV-7861 - Wrong result -main.insert_select : Modified in 10.3.7 -main.invisible_field_grant_completely : Added in 10.3.7 -main.invisible_field_grant_system : Added in 10.3.7 +main.insert_select : Modified in 10.2.16 +main.invisible_field_debug : Modified in 10.3.9 +main.join : Modified in 10.1.35, 10.2.17, 10.3.9 +main.join_cache : Modified in 10.1.35, 10.2.17, 10.3.9 +main.join_outer : Modified in 10.1.35, 10.2.17, 10.3.9 main.kill-2 : MDEV-13257 - Wrong result main.kill_processlist-6619 : MDEV-10793 - Wrong result main.limit : Modified in 10.3.8 @@ -328,39 +371,34 @@ main.lock : Modified in main.log_slow : MDEV-13263 - Wrong result main.log_tables-big : MDEV-13408 - wrong result main.max_statement_time : Modified in 10.3.8 +main.mdev375 : MDEV-10607 - sporadic "can't connect" main.mdev-504 : MDEV-15171 - warning main.merge : MDEV-10607 - sporadic "can't connect" -main.multi_update : Modified in 10.3.7 -main.mysql : Modified in 10.3.7 -main.mysql_client_test_comp : MDEV-16641 - exec failed +main.myisam : Modified in 10.0.36 +main.mysql_client_test_comp : MDEV-16641 - Error in exec main.mysql_client_test_nonblock : CONC-208 - Error on Power; MDEV-15096 - exec failed -main.mysql_cp932 : Modified in 10.3.7 -main.mysql_upgrade_noengine : MDEV-14355 - Wrong result -main.mysql_upgrade_ssl : MDEV-13492 - Unknown SSL error +main.mysql_cp932 : Modified in 10.2.16 main.mysqld_option_err : MDEV-12747 - Timeout -main.mysqldump : MDEV-14800 - Stack smashing detected; modified in 10.3.8 +main.mysqldump : MDEV-14800 - Stack smashing detected; modified in 10.2.16, 10.3.8 main.mysqlhotcopy_myisam : MDEV-10995 - Hang on debug +main.mysql : Modified in 10.2.16 main.mysqlslap : Modified in 10.3.8 main.mysqltest : MDEV-13887 - Wrong result -main.mysqltest_tracking_info : Added in 10.3.8 +main.mysqltest_tracking_info : Added in 10.2.17, 10.3.8 +main.mysql_upgrade_noengine : MDEV-14355 - Wrong result +main.mysql_upgrade_ssl : MDEV-13492 - Unknown SSL error main.olap : Modified in 10.3.8 main.openssl_1 : MDEV-13492 - Unknown SSL error main.order_by_optimizer_innodb : MDEV-10683 - Wrong result -main.parser : Modified in 10.3.7 main.partition_debug_sync : MDEV-15669 - Deadlock found when trying to get lock -main.partition_innodb : Modified in 10.3.7 main.partition_innodb_plugin : MDEV-12901 - Valgrind warnings -main.partition_open_files_limit : Modified in 10.3.8 -main.ps : MDEV-11017 - Wrong result; modified in 10.3.8 -main.query_cache : MDEV-16180 - Wrong result +main.ps : MDEV-11017 - sporadic wrong Prepared_stmt_count; modified in 10.1.34 main.query_cache_debug : MDEV-15281 - Query cache is disabled +main.query_cache : MDEV-16180 - Wrong result main.range_vs_index_merge_innodb : MDEV-15283 - Server has gone away main.read_only_innodb : Modified in 10.3.8 -main.rename : Modified in 10.3.8 +main.rename : Modified in 10.3.9 main.reset_connection : Added in 10.3.8 -main.select : MDEV-15430 - Wrong result with clang-4 (compiler bug) -main.select_jcl6 : MDEV-15430 - Wrong result with clang-4 (compiler bug) -main.select_pkeycache : MDEV-15430 - Wrong result with clang-4 (compiler bug) main.selectivity : Modified in 10.3.8 main.session_tracker_last_gtid : Added in 10.3.8 main.set_password : Added in 10.3.8 @@ -368,55 +406,55 @@ main.set_statement : MDEV-13183 main.shm : MDEV-12727 - Mismatch, ERROR 2013 main.show_explain : MDEV-10674 - Wrong result code main.show_grants_with_plugin-7985 : Modified in 10.3.8 -main.sp : MDEV-7866 - Mismatch; modified in 10.3.8 -main.sp-code : Modified in 10.3.7 main.sp-condition-handler : Added in 10.3.8 main.sp-cursor : Modified in 10.3.8 +main.sp-innodb : Modified in 10.0.36 +main.sp : MDEV-7866 - Mismatch; modified in 10.3.8 +main.sp_notembedded : MDEV-10607 - internal error main.sp-row : Modified in 10.3.8 main.sp-security : Modified in 10.3.8 -main.sp_notembedded : MDEV-10607 - internal error main.sql_mode : Modified in 10.3.8 main.ssl_ca : MDEV-10895 - SSL connection error on Power main.ssl_cert_verify : MDEV-13735 - Server crash main.ssl_connect : MDEV-13492 - Unknown SSL error main.ssl_timeout : MDEV-11244 - Crash -main.stat_tables_par : MDEV-13266 - Wrong result -main.stat_tables_par_innodb : MDEV-14155 - Wrong rounding -main.statistics : Modified in 10.3.8 main.statistics_close : Added in 10.3.8 +main.statistics : Modified in 10.3.8 +main.stat_tables : Modified in 10.1.35, 10.2.17, 10.3.9 +main.stat_tables_par_innodb : MDEV-14155 - Wrong rounding +main.stat_tables_par : MDEV-13266 - Wrong result main.status : MDEV-13255 - Wrong result -main.subselect-crash_15755 : Added in 10.3.7 +main.subselect4 : Modified in 10.0.36 main.subselect_innodb : MDEV-10614 - Wrong result -main.subselect_sj2_mat : Modified in 10.3.8 -main.symlink-aria-11902 : MDEV-15098 - error 40 from storage engine -main.symlink-myisam-11902 : MDEV-15098 - Error 40 from storage engine +main.subselect : Modified in 10.1.35, 10.2.17, 10.3.9 +main.subselect_sj2_mat : Modified in 10.1.35, 10.2.17, 10.3.9 +main.subselect_sj_mat : Modified in 10.1.35, 10.2.17, 10.3.9 +main.subselect_sj : Modified in 10.0.36 main.tc_heuristic_recover : MDEV-14189 - Wrong result main.trigger : Modified in 10.3.8 main.type_bit : Modified in 10.3.8 main.type_blob : MDEV-15195 - Wrong result; modified in 10.3.8 main.type_datetime : MDEV-14322 - wrong result -main.type_datetime_hires : MDEV-15430 - Wrong result with clang-4 (compiler bug) main.type_decimal : Modified in 10.3.8 -main.type_float : MDEV-15430 - Wrong result with clang-4 (compiler bug) main.type_int : Modified in 10.3.8 -main.type_time_hires : MDEV-15430 - Wrong result with clang-4 (compiler bug) -main.type_timestamp_hires : MDEV-15430 - Wrong result with clang-4 (compiler bug) +main.union : Modified in 10.1.35, 10.2.17, 10.3.9 main.userstat : MDEV-12904 - SSL errors main.win : Modified in 10.3.8 -main.xa : MDEV-11769 - lock wait timeout +main.xa : MDEV-11769 - lock wait timeout; modified in 10.3.9 + #----------------------------------------------------------------------- -maria.alter : Modified in 10.3.7 +maria.alter : Modified in 10.2.16 maria.insert_select : MDEV-12757 - Timeout maria.insert_select-7314 : MDEV-16492 - Timeout maria.lock : Modified in 10.3.8 maria.maria : MDEV-14430 - Extra warning; modified in 10.3.8 -maria.maria-autoinc : MDEV-16652 - Out of range; added in 10.3.8 +maria.maria-autoinc : Added in 10.3.8 #----------------------------------------------------------------------- -mariabackup.absolute_ibdata_paths : MDEV-16642 - Wrong result +mariabackup.absolute_ibdata_paths : MDEV-16571 - Wrong result mariabackup.apply-log-only : MDEV-14192 - Assertion failure mariabackup.apply-log-only-incr : MDEV-14192 - Assertion failure; modified in 10.3.8 mariabackup.backup_ssl : MDEV-14192 - Assertion failure @@ -424,34 +462,43 @@ mariabackup.data_directory : MDEV-15270 mariabackup.full_backup : MDEV-16571 - Wrong result mariabackup.huge_lsn : MDEV-15662 - Sequence number is in the future mariabackup.incremental_backup : MDEV-14192 - Assertion failure -mariabackup.incremental_encrypted : MDEV-14188 - Wrong result, MDEV-15667 - timeout +mariabackup.incremental_encrypted : MDEV-15667 - timeout; modified in 10.3.9 +mariabackup.innodb_log_optimize_ddl : Added in 10.2.17, 10.3.9 +mariabackup.log_checksum_mismatch : MDEV-16571 - Wrong result mariabackup.lock_ddl_per_table : Modified in 10.3.8 mariabackup.mdev-14447 : MDEV-15201 - Timeout; modified in 10.3.8 mariabackup.partial_exclude : MDEV-15270 - Error on exec mariabackup.partition_datadir : Modified in 10.3.8 mariabackup.rename_during_mdl_lock : Added in 10.3.8 +mariabackup.unsupported_redo : MDEV-14192 - Crash; modified in 10.2.17, 10.3.9 mariabackup.xb_aws_key_management : MDEV-15680 - Error: xtrabackup_copy_logfile() failed mariabackup.xb_compressed_encrypted : MDEV-14812 - Segmentation fault -mariabackup.xb_file_key_management : MDEV-16650 - Wrong result +mariabackup.xb_file_key_management : MDEV-16571 - Wrong result mariabackup.xb_history : MDEV-16268 - Error on exec mariabackup.xb_page_compress : MDEV-14810 - status: 1, errno: 11 -mariabackup.xb_partition : MDEV-16643 - Table does not exist mariabackup.xb_rocksdb : Added in 10.3.8 mariabackup.xb_rocksdb_datadir : Added in 10.3.8 mariabackup.xb_rocksdb_datadir_debug : Added in 10.3.8 +mariabackup.xb_partition : MDEV-14192 - Crash mariabackup.xbstream : MDEV-14192 - Crash #----------------------------------------------------------------------- -mroonga/storage.* : MDEV-16127 - Wrong result - mroonga/storage.column_datetime_32bit_2038 : Wrong result on Alpha mroonga/storage.column_datetime_32bit_before_unix_epoch : Wrong result on Alpha mroonga/storage.column_datetime_32bit_max : Wrong result on Alpha mroonga/storage.column_datetime_32bit_out_of_range : Wrong result on Alpha +mroonga/storage.index_multiple_column_range_all_used_less_than : MDEV-16127 - Wrong result with GCC 8 +mroonga/storage.index_multiple_column_range_all_used_less_than_or_equal : MDEV-16127 - Wrong result with GCC 8 +mroonga/storage.index_multiple_column_range_partially_used_have_prefix_less_than : MDEV-16127 - Wrong result with GCC 8 +mroonga/storage.index_multiple_column_range_partially_used_have_prefix_less_than_or_equal : MDEV-16127 - Wrong result with GCC 8 +mroonga/storage.index_multiple_column_range_partially_used_no_prefix_less_than : MDEV-16127 - Wrong result with GCC 8 +mroonga/storage.index_multiple_column_range_partially_used_no_prefix_less_than_or_equal : MDEV-16127 - Wrong result with GCC 8 mroonga/storage.index_multiple_column_unique_date_32bit_equal : Wrong result on Alpha mroonga/storage.index_multiple_column_unique_date_order_32bit_desc : Wrong result on Alpha mroonga/storage.index_multiple_column_unique_datetime_index_read : MDEV-8643 - Valgrind +mroonga/storage.optimization_order_limit_optimized_datetime_less_than : MDEV-16127 - Wrong result with GCC 8 +mroonga/storage.optimization_order_limit_optimized_datetime_less_than_or_equal : MDEV-16127 - Wrong result with GCC 8 mroonga/storage.repair_table_no_index_file : MDEV-9364 - wrong result, MDEV-14807 - wrong error message mroonga/wrapper.repair_table_no_index_file : MDEV-14807 - Wrong error message @@ -474,7 +521,8 @@ parts.partition_auto_increment_maria : MDEV-14430 parts.partition_debug_innodb : MDEV-10891 - Can't create UNIX socket; MDEV-15095 - Table doesn't exist parts.partition_exch_qa_10 : MDEV-11765 - wrong result parts.partition_innodb_status_file : MDEV-12901 - Valgrind -parts.show_create : Added in 10.3.7 +parts.partition_special_innodb : MDEV-16942 - Timeout +parts.truncate_locked : Added in 10.1.35, 10.2.17, 10.3.9 #----------------------------------------------------------------------- @@ -494,6 +542,7 @@ perfschema.hostcache_ipv6_addrinfo_bad_allow : MDEV-13260 perfschema.hostcache_ipv6_ssl : MDEV-10696 - Crash perfschema.partition : Added in 10.3.8 perfschema.privilege_table_io : MDEV-13184 - Extra lines +perfschema.rpl_gtid_func : MDEV-16897 - Wrong result perfschema.socket_connect : MDEV-15677 - Wrong result perfschema.socket_summary_by_event_name_func : MDEV-10622 - Wrong result perfschema.stage_mdl_global : MDEV-11803 - wrong result on slow builders @@ -520,20 +569,19 @@ rocksdb.2pc_group_commit : MDEV-14455 rocksdb.add_index_inplace : MDEV-16648 - Crash rocksdb.allow_no_primary_key : MDEV-16634 - Crash rocksdb.allow_no_primary_key_with_sk : MDEV-16639 - Crash -rocksdb.analyze_table : Modified in 10.3.7 +rocksdb.analyze_table : Modified in 10.2.16 rocksdb.autoinc_crash_safe : MDEV-16637 - Crash rocksdb.autoinc_crash_safe_partition : MDEV-16639 - Crash -rocksdb.autoinc_debug : MDEV-16203 - Wrong result rocksdb.autoinc_secondary : MDEV-16638 - Crash rocksdb.autoinc_vars_thread : MDEV-16573 - Debug sync timed out rocksdb.bloomfilter2 : MDEV-16564 - Wrong result rocksdb.bloomfilter4 : MDEV-16649 - Crash -rocksdb.bulk_load_errors : MDEV-16575 - Wrong result -rocksdb.check_ignore_unknown_options : MDEV-16310 - Non-portable commands; modified in 10.3.8 +rocksdb.check_ignore_unknown_options : Modified in 10.3.8 rocksdb.deadlock : MDEV-16033 - Timeout rocksdb.drop_index_inplace : MDEV-14162 - Crash on shutdown rocksdb.drop_table : MDEV-14308 - Timeout rocksdb.drop_table2 : MDEV-16631 - Crash +rocksdb.drop_table3 : MDEV-16949 - Server crash rocksdb.issue255 : MDEV-16577 - Wrong plan; modified in 10.3.8 rocksdb.locking_issues : MDEV-14464 - Wrong result rocksdb.mariadb_ignore_dirs : MDEV-16639 - Crash @@ -546,9 +594,12 @@ rocksdb.rocksdb_parts : MDEV-13843 rocksdb.singledelete : MDEV-16633 - Crash rocksdb.truncate_table3 : MDEV-14506 - Lost connection to server rocksdb.ttl_primary_read_filtering : MDEV-16560 - Wrong result +rocksdb.ttl_secondary : MDEV-16943 - Timeout rocksdb.ttl_secondary_read_filtering : MDEV-16560 - Wrong result rocksdb.unique_check : MDEV-16576 - Wrong errno rocksdb.use_direct_reads_writes : MDEV-16646 - Crash +rocksdb.validate_datadic : MDEV-12445 - Memory leak +rocksdb.write_sync : MDEV-16965 - Wrong result #----------------------------------------------------------------------- @@ -557,7 +608,10 @@ rocksdb_rpl.rpl_binlog_xid_count : MDEV-16644 #----------------------------------------------------------------------- +rocksdb_sys_vars.rocksdb_rate_limiter_bytes_per_sec_basic : MDEV-16639 - Crash rocksdb_sys_vars.rocksdb_remove_mariabackup_checkpoint_basic : Added in 10.3.8 +rocksdb_sys_vars.rocksdb_update_cf_options_basic : MDEV-16955 - Bytes lost +rocksdb_sys_vars.rocksdb_update_cf_options : MDEV-16955 - Bytes lost #----------------------------------------------------------------------- @@ -570,8 +624,11 @@ roles.set_default_role_ps-6960 : Modified in rpl-tokudb.* : MDEV-14354 - Tests fail with tcmalloc +rpl-tokudb.rpl_tokudb_commit_after_flush : MDEV-16966 - Server crash + rpl.last_insert_id : MDEV-10625 - warnings in error log rpl.rename : Added in 10.3.8 +rpl.rpl_15867 : Added in 10.2.17, 10.3.9 rpl.rpl_auto_increment : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_bug45679 : MDEV-10417 - Fails on Mips rpl.rpl_auto_increment_update_failure : MDEV-10625 - warnings in error log @@ -583,6 +640,7 @@ rpl.rpl_ddl : MDEV-10417 rpl.rpl_do_grant : Modified in 10.3.8 rpl.rpl_domain_id_filter_io_crash : MDEV-12729 - Timeout in include file, MDEV-13677 - Server crash rpl.rpl_domain_id_filter_restart : MDEV-10684 - Wrong result +rpl.rpl_drop_db_fail : MDEV-16898 - Slave fails to start rpl.rpl_extra_col_master_innodb : MDEV-16570 - Extra warning rpl.rpl_extra_col_master_myisam : MDEV-14203 - Extra warning rpl.rpl_gtid_basic : MDEV-10681 - server startup problem @@ -602,9 +660,8 @@ rpl.rpl_insert_id_pk : MDEV-16567 rpl.rpl_insert_ignore : MDEV-14365 - Lost connection to MySQL server during query rpl.rpl_invoked_features : MDEV-10417 - Fails on Mips rpl.rpl_mariadb_slave_capability : MDEV-11018 - Extra lines in binlog -rpl.rpl_mdev12179 : Modified in 10.3.7 -rpl.rpl_mdev382 : Modified in 10.3.7 rpl.rpl_mdev6020 : MDEV-15272 - Server crash +rpl.rpl_mixed_implicit_commit_binlog : Included file modified in 10.0.36 rpl.rpl_mixed_mixing_engines : MDEV-14489 - Sync slave with master failed rpl.rpl_non_direct_mixed_mixing_engines : MDEV-14489 - Sync slave with master failed rpl.rpl_non_direct_row_mixing_engines : MDEV-16561 - Timeout in master_pos_wait @@ -619,13 +676,14 @@ rpl.rpl_parallel_retry : MDEV-11119 rpl.rpl_parallel_temptable : MDEV-10356 - Crash rpl.rpl_partition_innodb : MDEV-10417 - Fails on Mips rpl.rpl_password_boundaries : MDEV-11534 - Slave IO warnings -rpl.rpl_row_001 : MDEV-16653 - MTR's internal check fails +rpl.rpl_row_001 : MDEV-16653 - MTR's internal check fails; modified in 10.3.9 rpl.rpl_row_basic_11bugs : MDEV-12171 - Server failed to start rpl.rpl_row_basic_2myisam : MDEV-13875 - command "diff_files" failed rpl.rpl_row_drop_create_temp_table : MDEV-14487 - Wrong result rpl.rpl_row_img_blobs : MDEV-13875 - command "diff_files" failed rpl.rpl_row_img_eng_min : MDEV-13875 - diff_files failed rpl.rpl_row_img_eng_noblob : MDEV-13875 - command "diff_files" failed +rpl.rpl_row_implicit_commit_binlog : Included file modified in 10.0.36 rpl.rpl_row_index_choice : MDEV-15196 - Slave crash rpl.rpl_row_sp001 : MDEV-9329 - Fails on Ubuntu/s390x rpl.rpl_row_until : MDEV-14052 - Master will not send events with checksum @@ -633,6 +691,7 @@ rpl.rpl_semi_sync : MDEV-11220 rpl.rpl_semi_sync_after_sync : MDEV-14366 - Wrong result rpl.rpl_semi_sync_after_sync_row : MDEV-14366 - Wrong result rpl.rpl_semi_sync_event_after_sync : MDEV-11806 - warnings +rpl.rpl_semi_sync_master_shutdown : Added in 10.3.9 rpl.rpl_semi_sync_uninstall_plugin : MDEV-7140 - Assorted failures rpl.rpl_semi_sync_wait_point : MDEV-11807 - timeout in wait condition rpl.rpl_semisync_ali_issues : MDEV-16272 - Wrong result @@ -644,7 +703,8 @@ rpl.rpl_slave_load_tmpdir_not_exist : MDEV-14203 rpl.rpl_slow_query_log : MDEV-13250 - Test abort rpl.rpl_sp_effects : MDEV-13249 - Crash rpl.rpl_start_stop_slave : MDEV-13567 - Sync slave timeout -rpl.rpl_stm_000001 : MDEV-16274 - Connection attributes were truncated +rpl.rpl_stm_000001 : MDEV-16274 - Connection attributes were truncated; modified in 10.3.9 +rpl.rpl_stm_implicit_commit_binlog : Included file modified in 10.0.36 rpl.rpl_stm_mixing_engines : MDEV-14489 - Sync slave with master failed rpl.rpl_stm_multi_query : MDEV-9501 - Failed registering on master rpl.rpl_stm_relay_ign_space : MDEV-14360 - Test assertion @@ -669,6 +729,7 @@ sphinx.union-5539 : MDEV-10986 spider.* : MDEV-9329 - tests are too memory-consuming spider.basic_sql : MDEV-11186 - Internal check fails spider.spider_fixes_part : Modified in 10.3.8 +spider.timestamp : Added in 10.3.9 spider/bg.direct_aggregate : MDEV-7098 - Packets out of order spider/bg.direct_aggregate_part : MDEV-7098 - Trying to unlock mutex that wasn't locked @@ -699,7 +760,7 @@ stress.ddl_innodb : MDEV-10635 sys_vars.autocommit_func2 : MDEV-9329 - Fails on Ubuntu/s390x sys_vars.innodb_buffer_pool_dump_at_shutdown_basic : MDEV-14280 - Unexpected error -sys_vars.innodb_stats_include_delete_marked_basic : Modified in 10.3.7 +sys_vars.innodb_log_optimize_ddl_basic : Added in 10.2.17, 10.3.9 sys_vars.keep_files_on_create_basic : MDEV-10676 - timeout sys_vars.log_slow_admin_statements_func : MDEV-12235 - Server crash sys_vars.maximum_basic : Modified in 10.3.8 @@ -748,6 +809,7 @@ tokudb_bugs.frm_store : MDEV-12823 tokudb_bugs.frm_store2 : MDEV-12823 - Valgrind tokudb_bugs.frm_store3 : MDEV-12823 - Valgrind tokudb_bugs.xa : MDEV-11804 - Lock wait timeout +tokudb_bugs.xa-3 : MDEV-16953 - Corrupt log record found #----------------------------------------------------------------------- @@ -783,16 +845,13 @@ vcol.vcol_misc : MDEV-16651 #----------------------------------------------------------------------- versioning.alter : Modified in 10.3.8 -versioning.partition : Modified in 10.3.7 versioning.replace : Modified in 10.3.8 versioning.select : Modified in 10.3.8 -versioning.truncate : Modified in 10.3.7 versioning.trx_id : Modified in 10.3.8 versioning.trx_id_versioning_attribute_persistence : Added in 10.3.8 #----------------------------------------------------------------------- -wsrep.binlog_format : MDEV-11532 - Could not execute check-testcase wsrep.foreign_key : MDEV-14725 - WSREP has not yet prepared node wsrep.mdev_6832 : MDEV-14195 - Check testcase failed wsrep.pool_of_threads : MDEV-12234 - GLIBCXX_3.4.20 not found |