From 37a316c01d778a62a056d5d20110ef18bb55975e Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Fri, 9 Dec 2022 21:10:25 +0700 Subject: MDEV-29988: Major performance regression with 10.6.11 The idea is to put Item_direct_ref_to_item as a transparent and permanent wrapper before a string which require conversion. So that Item_direct_ref_to_item would be the only place where the pointer to the string item is stored, this pointer can be changed and restored during PS execution as needed. And if any permanent (subquery) optimization would need a pointer to the item, it'll use a pointer to the Item_direct_ref_to_item - which is a permanent item and won't go away. --- mysql-test/main/ps.result | 15 +++++++++++++++ mysql-test/main/ps.test | 35 +++++++++++++++++++++++++++++++++++ 2 files changed, 50 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 26c41526389..16700fd83d3 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5700,3 +5700,18 @@ EXECUTE stmt USING 'd'; EXECUTE stmt USING 'd'; 300 DROP TABLE t1, t2, t3; +set @@max_session_mem_used=default; +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = ?"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = 'a'"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 2ccfac3c119..87cae7211a4 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -5116,3 +5116,38 @@ EXECUTE stmt USING 'b'; EXECUTE stmt USING 'd'; EXECUTE stmt USING 'd'; DROP TABLE t1, t2, t3; + +set @@max_session_mem_used=default; +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = ?"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +let $run= 1000; +disable_result_log; +disable_query_log; +while ($run) { + execute stmt using repeat('x',10000); + dec $run; +} +enable_result_log; +enable_query_log; +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; + +create table t (a varchar(10)) character set utf8; +insert into t values (''); +prepare stmt from "select 1 from t where a = 'a'"; +set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id()); +let $run= 1000; +disable_result_log; +disable_query_log; +while ($run) { + execute stmt; + dec $run; +} +enable_result_log; +enable_query_log; +deallocate prepare stmt; +drop table t; +set @@max_session_mem_used=default; -- cgit v1.2.1 From 21223c0461455b4175bae91aee7e3525a51c6d70 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sun, 18 Dec 2022 16:30:26 +0100 Subject: MDEV-29988 group by fix --- mysql-test/main/func_group.result | 11 ++++++++++- mysql-test/main/func_group.test | 12 ++++++++---- 2 files changed, 18 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 0f80d14b603..6617d21a197 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3,t4,t5,t6; set @sav_dpi= @@div_precision_increment; set div_precision_increment= 5; show variables like 'div_precision_increment'; @@ -2566,5 +2565,15 @@ stddev_samp(i) stddev_pop(i) stddev(i) std(i) drop view v1; drop table t1; # +# MDEV-29988: Major performance regression with 10.6.11 +# +create table t1 (a varchar(10) charset utf8mb4, b int, c int); +insert t1 values (1,2,3),(4,5,6),(1,7,8); +select concat(a,":",group_concat(b)) from t1 group by a; +concat(a,":",group_concat(b)) +1:2,7 +4:5 +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 6b3a15fd45e..5693940dda6 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -2,10 +2,6 @@ # simple test of all group functions # ---disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6; ---enable_warnings - set @sav_dpi= @@div_precision_increment; set div_precision_increment= 5; show variables like 'div_precision_increment'; @@ -1800,6 +1796,14 @@ select * from v1; drop view v1; drop table t1; +--echo # +--echo # MDEV-29988: Major performance regression with 10.6.11 +--echo # +create table t1 (a varchar(10) charset utf8mb4, b int, c int); +insert t1 values (1,2,3),(4,5,6),(1,7,8); +select concat(a,":",group_concat(b)) from t1 group by a; +drop table t1; + --echo # --echo # End of 10.3 tests --echo # -- cgit v1.2.1 From 111a752b968561b34a88f33052519cb989a8a90f Mon Sep 17 00:00:00 2001 From: Weijun-H Date: Wed, 4 Jan 2023 18:44:03 +0000 Subject: MDEV-19160 JSON_DETAILED output unnecessarily verbose --- mysql-test/main/func_json.result | 126 ++++++++++++++++ mysql-test/main/func_json.test | 100 ++++++++++++ mysql-test/main/opt_trace.result | 218 ++++++--------------------- mysql-test/main/opt_trace_index_merge.result | 102 +++---------- mysql-test/main/opt_trace_ucs2.result | 9 +- mysql-test/main/range_notembedded.result | 1 - mysql-test/suite/plugins/r/multiauth.result | 3 - 7 files changed, 289 insertions(+), 270 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index b6605df547f..6ea8c78b2b6 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1133,3 +1133,129 @@ DROP TABLE t1; # # End of 10.4 tests # +# +# MDEV-19160 JSON_DETAILED output unnecessarily verbose +# +create table t200 (a text); +insert into t200 values +('{ + "steps": [ + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 2e308 + }, + "potential_range_indexes": [ + { + "index": "a_b", + "usable": true, + "key_parts": ["a", "b"] + } + ], + "best_covering_index_scan": { + "index": "a_b", + "cost": 52.195, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a_b", + "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [], + "test_one_line_array":["123"] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a_b", + "rows": 1, + "ranges": ["2 <= a <= 2 AND 4 <= b <= 4"] + }, + "rows_for_plan": 1, + "cost_for_plan": 1.1752, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a_b", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.001 + } + ] + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +}'); +select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; +JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) +[ + { + "range_scan_alternatives": + [ + { + "index": "a_b", + "ranges": + [ + "2 <= a <= 2 AND 4 <= b <= 4", + "123" + ], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": + { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": + [], + "test_one_line_array": + ["123"] + } +] +select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; +JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) +[{"range_scan_alternatives": [{"index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true}], "analyzing_roworder_intersect": {"cause": "too few roworder scans"}, "analyzing_index_merge_union": [], "test_one_line_array": ["123"]}] diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 0987af80b79..6cfcefbee3d 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -718,3 +718,103 @@ DROP TABLE t1; --echo # --echo # End of 10.4 tests --echo # + + +--echo # +--echo # MDEV-19160 JSON_DETAILED output unnecessarily verbose +--echo # + +create table t200 (a text); + +insert into t200 values +('{ + "steps": [ + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 2e308 + }, + "potential_range_indexes": [ + { + "index": "a_b", + "usable": true, + "key_parts": ["a", "b"] + } + ], + "best_covering_index_scan": { + "index": "a_b", + "cost": 52.195, + "chosen": true + }, + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a_b", + "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [], + "test_one_line_array":["123"] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a_b", + "rows": 1, + "ranges": ["2 <= a <= 2 AND 4 <= b <= 4"] + }, + "rows_for_plan": 1, + "cost_for_plan": 1.1752, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a_b", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.001 + } + ] + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +}'); + + +select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; +select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 79201d49474..2eef0da62bb 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -7459,17 +7459,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "a_b", "ranges": - [ - "(2,4) <= (a,b) <= (2,4)" - ], + ["(2,4) <= (a,b) <= (2,4)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, @@ -7483,8 +7479,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] explain select * from t1 where a >= 900 and b between 10 and 20; @@ -7493,17 +7488,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "a_b", "ranges": - [ - "(900,10) <= (a,b)" - ], + ["(900,10) <= (a,b)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, @@ -7517,8 +7508,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t0,t1; @@ -7530,17 +7520,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "start_date", "ranges": - [ - "(2019-02-10,NULL) < (start_date,end_date)" - ], + ["(2019-02-10,NULL) < (start_date,end_date)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, @@ -7554,8 +7540,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1,one_k; @@ -7575,17 +7560,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "a_b_c", "ranges": - [ - "(1) <= (a,b) < (4,50)" - ], + ["(1) <= (a,b) < (4,50)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, @@ -7599,8 +7580,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table ten,t1; @@ -7614,17 +7594,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)" - ], + ["(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7638,8 +7614,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] EXPLAIN SELECT * FROM t1 WHERE b IS NULL; @@ -7648,17 +7623,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(NULL) <= (b) <= (NULL)" - ], + ["(NULL) <= (b) <= (NULL)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7672,8 +7643,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7690,17 +7660,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(ab\x0A) <= (b) <= (ab\x0A)" - ], + ["(ab\x0A) <= (b) <= (ab\x0A)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7714,8 +7680,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] ALTER TABLE t1 modify column b BINARY(10) AFTER i; @@ -7725,17 +7690,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)" - ], + ["(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7749,8 +7710,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] ALTER TABLE t1 modify column b VARBINARY(10) AFTER i; @@ -7760,17 +7720,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(ab\x0A) <= (b) <= (ab\x0A)" - ], + ["(ab\x0A) <= (b) <= (ab\x0A)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7784,8 +7740,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7798,17 +7753,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(ab\n) <= (b) <= (ab\n)" - ], + ["(ab\n) <= (b) <= (ab\n)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7822,8 +7773,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7839,17 +7789,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(ab\x0A) <= (b) <= (ab\x0A)" - ], + ["(ab\x0A) <= (b) <= (ab\x0A)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, @@ -7863,8 +7809,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7878,17 +7823,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "i_b", "ranges": - [ - "(ab\n) <= (b) <= (ab\n)" - ], + ["(ab\n) <= (b) <= (ab\n)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -7902,8 +7843,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7934,17 +7874,13 @@ EXPLAIN select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "start_date", "ranges": - [ - "(2019-02-10,NULL) < (start_date,end_date)" - ], + ["(2019-02-10,NULL) < (start_date,end_date)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, @@ -7958,8 +7894,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1, t0, one_k; @@ -7998,19 +7933,15 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ - [ - { "plan_prefix": - [ - ], + [], "table": "A", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 5.9375, @@ -8030,18 +7961,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 4.0171, "rest_of_plan": [ - { "plan_prefix": - [ - "A" - ], + ["A"], "table": "B", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 804.69, @@ -8063,17 +7990,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) } ] }, - { "plan_prefix": - [ - ], + [], "table": "B", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 804.69, @@ -8104,19 +8028,15 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ - [ - { "plan_prefix": - [ - ], + [], "table": "A", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 10, @@ -8136,18 +8056,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 4.0171, "rest_of_plan": [ - { "plan_prefix": - [ - "A" - ], + ["A"], "table": "B", "best_access_path": { "considered_access_paths": [ - { "access_type": "ref", "index": "b", @@ -8158,7 +8074,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20, "chosen": true }, - { "access_type": "scan", "resulting_rows": 804.69, @@ -8181,17 +8096,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) } ] }, - { "plan_prefix": - [ - ], + [], "table": "B", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 804.69, @@ -8227,17 +8139,13 @@ a select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "a", "ranges": - [ - "() <= (a) <= ()" - ], + ["() <= (a) <= ()"], "rowid_ordered": false, "using_mrr": false, "index_only": false, @@ -8251,8 +8159,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] DROP TABLE t1; @@ -8274,7 +8181,6 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) [ - { "pulled_out_tables": [ @@ -8295,15 +8201,11 @@ kp1 kp2 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) [ - [ - { "index": "kp1", "ranges": - [ - "(2,4) <= (kp1,kp2) <= (2)" - ], + ["(2,4) <= (kp1,kp2) <= (2)"], "rowid_ordered": false, "using_mrr": false, "index_only": true, @@ -8330,19 +8232,15 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) [ - [ - { "plan_prefix": - [ - ], + [], "table": "t1", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 10, @@ -8362,18 +8260,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 4.022, "rest_of_plan": [ - { "plan_prefix": - [ - "t1" - ], + ["t1"], "table": "t2", "best_access_path": { "considered_access_paths": [ - { "access_type": "ref", "index": "a", @@ -8384,7 +8278,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20, "chosen": true }, - { "access_type": "scan", "resulting_rows": 100, @@ -8407,17 +8300,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) } ] }, - { "plan_prefix": - [ - ], + [], "table": "t2", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 100, @@ -8438,18 +8328,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 22.22, "rest_of_plan": [ - { "plan_prefix": - [ - "t2" - ], + ["t2"], "table": "t1", "best_access_path": { "considered_access_paths": [ - { "access_type": "ref", "index": "a", @@ -8460,7 +8346,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 200, "chosen": true }, - { "access_type": "scan", "resulting_rows": 10, @@ -8504,24 +8389,17 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) [ - [ - { "column_name": "a", "ranges": - [ - "1 <= a <= 5" - ], + ["1 <= a <= 5"], "selectivity_from_histogram": 0.0469 }, - { "column_name": "b", "ranges": - [ - "NULL < b <= 5" - ], + ["NULL < b <= 5"], "selectivity_from_histogram": 0.0469 } ] @@ -8534,9 +8412,7 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) [ - [ - { "column_name": "a", "ranges": @@ -8556,15 +8432,11 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) [ - [ - { "column_name": "b", "ranges": - [ - "10 <= b < 25" - ], + ["10 <= b < 25"], "selectivity_from_histogram": 0.1562 } ] @@ -8582,15 +8454,11 @@ UPDATE t1 SET b=10 WHERE a=1; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) [ - [ - { "index": "PRIMARY", "ranges": - [ - "(1) <= (a) <= (1)" - ], + ["(1) <= (a) <= (1)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index ed5ddfd69f4..554ddde66a9 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -307,17 +307,13 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "key1", "ranges": - [ - "(100) <= (key1) <= (100)" - ], + ["(100) <= (key1) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -325,13 +321,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cost": 2844.1, "chosen": true }, - { "index": "key2", "ranges": - [ - "(100) <= (key2) <= (100)" - ], + ["(100) <= (key2) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -340,13 +333,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "chosen": false, "cause": "cost" }, - { "index": "key3", "ranges": - [ - "(100) <= (key3) <= (100)" - ], + ["(100) <= (key3) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, @@ -360,7 +350,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "intersecting_indexes": [ - { "index": "key1", "index_scan_cost": 58.252, @@ -372,7 +361,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "intersect_covering_with_this_index": false, "chosen": true }, - { "index": "key2", "index_scan_cost": 58.252, @@ -384,7 +372,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "intersect_covering_with_this_index": false, "chosen": true }, - { "index": "key3", "index_scan_cost": 58.252, @@ -408,14 +395,12 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "chosen": true }, "analyzing_index_merge_union": - [ - ] + [] } ] select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) [ - { "range_access_plan": { @@ -426,35 +411,26 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) "clustered_pk_scan": false, "intersect_of": [ - { "type": "range_scan", "index": "key1", "rows": 2243, "ranges": - [ - "(100) <= (key1) <= (100)" - ] + ["(100) <= (key1) <= (100)"] }, - { "type": "range_scan", "index": "key2", "rows": 2243, "ranges": - [ - "(100) <= (key2) <= (100)" - ] + ["(100) <= (key2) <= (100)"] }, - { "type": "range_scan", "index": "key3", "rows": 2243, "ranges": - [ - "(100) <= (key3) <= (100)" - ] + ["(100) <= (key3) <= (100)"] } ] }, @@ -470,32 +446,25 @@ id select_type table type possible_keys key key_len ref rows Extra select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": - [ - ], + [], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [ - { "indexes_to_merge": [ - { "range_scan_alternatives": [ - { "index": "key1", "ranges": - [ - "(100) <= (key1) <= (100)" - ], + ["(100) <= (key1) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, @@ -503,13 +472,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cost": 152.53, "chosen": true }, - { "index": "key2", "ranges": - [ - "(100) <= (key2) <= (100)" - ], + ["(100) <= (key2) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, @@ -522,17 +488,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "index_to_merge": "key1", "cumulated_cost": 152.53 }, - { "range_scan_alternatives": [ - { "index": "key3", "ranges": - [ - "(100) <= (key3) <= (100)" - ], + ["(100) <= (key3) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, @@ -540,13 +502,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cost": 152.53, "chosen": true }, - { "index": "key4", "ranges": - [ - "(100) <= (key4) <= (100)" - ], + ["(100) <= (key4) <= (100)"], "rowid_ordered": true, "using_mrr": false, "index_only": true, @@ -565,20 +524,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": [ - { "type": "range_scan", "index": "key1", "rows": 2243, "ranges": - [ - "(100) <= (key1) <= (100)" - ], + ["(100) <= (key1) <= (100)"], "analyzing_roworder_intersect": { "intersecting_indexes": [ - { "index": "key1", "index_scan_cost": 58.252, @@ -590,7 +545,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "intersect_covering_with_this_index": false, "chosen": true }, - { "index": "key2", "index_scan_cost": 58.252, @@ -614,20 +568,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "chosen": true } }, - { "type": "range_scan", "index": "key3", "rows": 2243, "ranges": - [ - "(100) <= (key3) <= (100)" - ], + ["(100) <= (key3) <= (100)"], "analyzing_roworder_intersect": { "intersecting_indexes": [ - { "index": "key3", "index_scan_cost": 58.252, @@ -639,7 +589,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "intersect_covering_with_this_index": false, "chosen": true }, - { "index": "key4", "index_scan_cost": 58.252, @@ -674,14 +623,12 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) [ - { "range_access_plan": { "type": "index_roworder_union", "union_of": [ - { "type": "index_roworder_intersect", "rows": 77, @@ -690,29 +637,22 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) "clustered_pk_scan": false, "intersect_of": [ - { "type": "range_scan", "index": "key1", "rows": 2243, "ranges": - [ - "(100) <= (key1) <= (100)" - ] + ["(100) <= (key1) <= (100)"] }, - { "type": "range_scan", "index": "key2", "rows": 2243, "ranges": - [ - "(100) <= (key2) <= (100)" - ] + ["(100) <= (key2) <= (100)"] } ] }, - { "type": "index_roworder_intersect", "rows": 77, @@ -721,25 +661,19 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) "clustered_pk_scan": false, "intersect_of": [ - { "type": "range_scan", "index": "key3", "rows": 2243, "ranges": - [ - "(100) <= (key3) <= (100)" - ] + ["(100) <= (key3) <= (100)"] }, - { "type": "range_scan", "index": "key4", "rows": 2243, "ranges": - [ - "(100) <= (key4) <= (100)" - ] + ["(100) <= (key4) <= (100)"] } ] } diff --git a/mysql-test/main/opt_trace_ucs2.result b/mysql-test/main/opt_trace_ucs2.result index 306fdbf94ad..945392d0ac1 100644 --- a/mysql-test/main/opt_trace_ucs2.result +++ b/mysql-test/main/opt_trace_ucs2.result @@ -23,17 +23,13 @@ EXPLAIN select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ - { "range_scan_alternatives": [ - { "index": "col1", "ranges": - [ - "(a) <= (col1)" - ], + ["(a) <= (col1)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, @@ -47,8 +43,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 7084e0ca7a0..e17f6341c6e 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -20,7 +20,6 @@ select json_detailed(JSON_EXTRACT(trace, '$**.ranges')) from information_schema.optimizer_trace; json_detailed(JSON_EXTRACT(trace, '$**.ranges')) [ - [ "(1) <= (key1) <= (1)", "(2) <= (key1) <= (2)", diff --git a/mysql-test/suite/plugins/r/multiauth.result b/mysql-test/suite/plugins/r/multiauth.result index 75b604555a7..9651dac6740 100644 --- a/mysql-test/suite/plugins/r/multiauth.result +++ b/mysql-test/suite/plugins/r/multiauth.result @@ -110,16 +110,13 @@ json_detailed(priv) "authentication_string": "*7D8C3DF236D9163B6C274A9D47704BC496988460", "auth_or": [ - { "plugin": "ed25519", "authentication_string": "F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc" }, - { "plugin": "unix_socket" }, - { } ], -- cgit v1.2.1 From b21832ef1525e37f0dda4e17853bcca9e4e20f2b Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 26 Oct 2022 22:08:32 -0700 Subject: MDEV-27624 Wrong result for nested left join using not_exists optimization This bug affected queries with nested left joins having the same last inner table such that not_exists optimization could be applied to the most inner outer join when optimizer chose to use join buffers. The bug could lead to producing wrong a result set. If the WHERE condition a query contains a conjunctive IS NULL predicate over a non-nullable column of an inner table of a not nested outer join then not_exists optimization can be applied to tho the outer join. With this optimization when looking for matches for a certain record from the outer table of the join the records of the inner table can be ignored right after the first match satisfying the ON condition is found. In the case of nested outer joins having the same last inner table this optimization still can be applied but only if all ON conditions of the embedding outer joins are satisfied. Such check was missing in the code that tried to apply not_exists optimization when join buffers were used for outer join operations. This problem has been already fixed in the patch for bug MDEV-7992. Yet there it was resolved only for the cases when join buffers were not used for outer joins. Approved by Oleksandr Byelkin --- mysql-test/main/join_nested.result | 52 +++++++++++++++++++++++++++++++++ mysql-test/main/join_nested.test | 38 ++++++++++++++++++++++++ mysql-test/main/join_nested_jcl6.result | 52 +++++++++++++++++++++++++++++++++ 3 files changed, 142 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/join_nested.result b/mysql-test/main/join_nested.result index 5ab94a6b568..1efb2e64116 100644 --- a/mysql-test/main/join_nested.result +++ b/mysql-test/main/join_nested.result @@ -1999,3 +1999,55 @@ Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.` DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-27624: Nested left joins with not_exists optimization +# for most inner left join +# +set @save_join_cache_level= @@join_cache_level; +CREATE TABLE t1 (a INT NOT NULL, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1); +CREATE TABLE t2(a INT NOT NULL); +INSERT INTO t2 VALUES (1), (2); +CREATE TABLE t3(a INT not null, b INT); +INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1); +set join_cache_level = 0; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +set join_cache_level = 2; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists; Using join buffer (incremental, BNL join) +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +DROP TABLE t1, t2, t3; +set join_cache_level= @save_join_cache_level; +# end of 10.3 tests diff --git a/mysql-test/main/join_nested.test b/mysql-test/main/join_nested.test index eaf754fb0ca..6b2ae9a457f 100644 --- a/mysql-test/main/join_nested.test +++ b/mysql-test/main/join_nested.test @@ -1419,3 +1419,41 @@ DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # MDEV-27624: Nested left joins with not_exists optimization +--echo # for most inner left join +--echo # + +set @save_join_cache_level= @@join_cache_level; + +CREATE TABLE t1 (a INT NOT NULL, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1); + +CREATE TABLE t2(a INT NOT NULL); +INSERT INTO t2 VALUES (1), (2); + +CREATE TABLE t3(a INT not null, b INT); +INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1); + +let $q= +SELECT * +FROM t1 + LEFT JOIN + ( t2 LEFT JOIN t3 ON t2.a = t3.b ) + ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; + +set join_cache_level = 0; +eval EXPLAIN $q; +eval $q; + +set join_cache_level = 2; +eval EXPLAIN $q; +eval $q; + +DROP TABLE t1, t2, t3; + +set join_cache_level= @save_join_cache_level; + +--echo # end of 10.3 tests diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 541cd47bf04..338705c0a29 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -2008,6 +2008,58 @@ Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.` DROP TABLE t1,t2,t3; set join_cache_level= @save_join_cache_level; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-27624: Nested left joins with not_exists optimization +# for most inner left join +# +set @save_join_cache_level= @@join_cache_level; +CREATE TABLE t1 (a INT NOT NULL, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1); +CREATE TABLE t2(a INT NOT NULL); +INSERT INTO t2 VALUES (1), (2); +CREATE TABLE t3(a INT not null, b INT); +INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1); +set join_cache_level = 0; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +set join_cache_level = 2; +EXPLAIN SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Not exists; Using join buffer (incremental, BNL join) +SELECT * +FROM t1 +LEFT JOIN +( t2 LEFT JOIN t3 ON t2.a = t3.b ) +ON t2.a = 1 AND (t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL) +WHERE t1.c = 1 AND t3.a is NULL; +a b c a a b +1 3 1 NULL NULL NULL +DROP TABLE t1, t2, t3; +set join_cache_level= @save_join_cache_level; +# end of 10.3 tests CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); -- cgit v1.2.1 From d7f447915c96681c85abf00d22de589a5332a6da Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Sun, 8 Jan 2023 09:53:09 +1100 Subject: MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error regression from MDEV-29540 / 8c389393695. INSERT SELECT errors needed to be unconditionally ignored. As this touches the CREATE .. SELECT functionality, show the equalivent test there. --- mysql-test/main/create.result | 11 +++++++++++ mysql-test/main/create.test | 13 +++++++++++++ mysql-test/main/insert_select.result | 13 +++++++++++++ mysql-test/main/insert_select.test | 17 +++++++++++++++++ 4 files changed, 54 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index b7ed4388db7..290f293325d 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -2121,5 +2121,16 @@ Warnings: Warning 1280 Name 'foo' ignored for PRIMARY key. DROP TABLE t1; # +# MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +# +create table t1(c1 varchar(1)); +insert into t1(c1) values('#'); +select @@sql_mode like '%strict_all_tables%'; +@@sql_mode like '%strict_all_tables%' +0 +create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1; +ERROR 22007: Truncated incorrect DECIMAL value: '#' +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/create.test b/mysql-test/main/create.test index f1dfba65b4b..83c894dd8dc 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -1978,6 +1978,19 @@ create table t1 (c int(10) unsigned) engine=memory transactional=0; CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); DROP TABLE t1; +--echo # +--echo # MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +--echo # + +create table t1(c1 varchar(1)); +insert into t1(c1) values('#'); + +select @@sql_mode like '%strict_all_tables%'; +--error ER_TRUNCATED_WRONG_VALUE +create table t2 as select if(c1 = '#', c1 = 0, c1) as c1 from t1; + +drop table t1; + --echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index 6d939d0ed8e..29618c6ddd4 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -1042,4 +1042,17 @@ select * from t1; a deallocate prepare stmt; drop table t1,t2,t3; +# +# MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +# +create table t1(c1 varchar(1)); +create table t2(c1 varchar(1)); +insert into t1(c1) values('#'); +select @@sql_mode like '%strict_all_tables%'; +@@sql_mode like '%strict_all_tables%' +0 +insert into t2(c1) select if(c1 = '#', c1 = 0, c1) as c1 from t1; +drop table t1, t2; +# # End of 10.3 test +# diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 6baa7e43c34..a3604e38f34 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -595,4 +595,21 @@ deallocate prepare stmt; drop table t1,t2,t3; + +--echo # +--echo # MDEV-30342 Wrong "Truncated incorrect DECIMAL value" warning/error +--echo # + +create table t1(c1 varchar(1)); +create table t2(c1 varchar(1)); + +insert into t1(c1) values('#'); + +select @@sql_mode like '%strict_all_tables%'; +insert into t2(c1) select if(c1 = '#', c1 = 0, c1) as c1 from t1; + +drop table t1, t2; + +--echo # --echo # End of 10.3 test +--echo # -- cgit v1.2.1 From 3d95737e5596e3f719bbdfb68669e2870c5dca3f Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 23 Dec 2022 23:58:56 +0100 Subject: galera.galera_wsrep_new_cluster: fix bad merge --- .../suite/galera/r/galera_wsrep_new_cluster.result | 8 ------- .../suite/galera/t/galera_wsrep_new_cluster.test | 28 ---------------------- 2 files changed, 36 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result b/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result index 87f61e2be62..143dee3e6e4 100644 --- a/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result +++ b/mysql-test/suite/galera/r/galera_wsrep_new_cluster.result @@ -9,14 +9,6 @@ connection node_2; Cleaning grastate.dat file ... Starting server ... connection node_1; -connection node_2; -connection node_2; -Shutting down server ... -connection node_1; -connection node_2; -Cleaning grastate.dat file ... -Starting server ... -connection node_1; SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; VARIABLE_VALUE Primary diff --git a/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test b/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test index 3fff51fa26d..94ea008cb16 100644 --- a/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test +++ b/mysql-test/suite/galera/t/galera_wsrep_new_cluster.test @@ -34,34 +34,6 @@ --source include/start_mysqld.inc --source include/wait_until_ready.inc -# Save original auto_increment_offset values. ---let $node_1=node_1 ---let $node_2=node_2 ---source include/auto_increment_offset_save.inc - ---connection node_2 ---echo Shutting down server ... ---source include/shutdown_mysqld.inc - ---connection node_1 - ---let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' ---source include/wait_condition.inc - ---connection node_2 - -# -# Delete grastate.dat with safe_to_bootstrap: 0 -# ---echo Cleaning grastate.dat file ... ---remove_file $MYSQLTEST_VARDIR/mysqld.2/data/grastate.dat - ---echo Starting server ... ---let $restart_noprint=2 ---let $start_mysqld_params="--wsrep-new-cluster" ---source include/start_mysqld.inc ---source include/wait_until_ready.inc - --connection node_1 --let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; -- cgit v1.2.1 From ad27e95d542dfb2ad3a31e4fdf1063cc0685d1f2 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sat, 24 Dec 2022 13:45:06 +0100 Subject: disable hanging galera test --- mysql-test/suite/galera/disabled.def | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index bff8e472539..ce0e8d986a4 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -16,5 +16,6 @@ galera_sst_encrypted : MDEV-29876 Galera test failure on galera_sst_encrypted galera.MW-284 : MDEV-29861 Galera test case hangs galera.galera_binlog_checksum : MDEV-29861 Galera test case hangs galera_var_notify_ssl_ipv6 : MDEV-29861 Galera test case hangs +galera_var_notify_cmd: MDEV-29861 Galera test case hangs galera_var_node_address : MDEV-20485 Galera test failure MDEV-26575 : MDEV-29878 Galera test failure on MDEV-26575 -- cgit v1.2.1 From 610cea3ddae5aa7756a3ec1409466c9e0c5fd5b3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 17 Nov 2022 17:30:59 +0100 Subject: cleanup Helper class to swicth to relaxed checks during field copy. Temporarily. --- mysql-test/main/olap.result | 1 - mysql-test/main/olap.test | 4 ---- mysql-test/main/type_date.result | 1 - mysql-test/main/type_date.test | 4 ---- 4 files changed, 10 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/olap.result b/mysql-test/main/olap.result index 93eda747d83..cc261c92603 100644 --- a/mysql-test/main/olap.result +++ b/mysql-test/main/olap.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2; set @sav_dpi= @@div_precision_increment; set div_precision_increment= 5; show variables like 'div_precision_increment'; diff --git a/mysql-test/main/olap.test b/mysql-test/main/olap.test index 0c990300b68..6f4048bf120 100644 --- a/mysql-test/main/olap.test +++ b/mysql-test/main/olap.test @@ -1,7 +1,3 @@ ---disable_warnings -drop table if exists t1,t2; ---enable_warnings - set @sav_dpi= @@div_precision_increment; set div_precision_increment= 5; show variables like 'div_precision_increment'; diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result index e22b15c88cf..0cf78458817 100644 --- a/mysql-test/main/type_date.result +++ b/mysql-test/main/type_date.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2; create table t1 (a char(16), b date, c datetime); insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01'; select * from t1 where c = '2000-01-01'; diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test index 27d03bd888c..74997aa0ef7 100644 --- a/mysql-test/main/type_date.test +++ b/mysql-test/main/type_date.test @@ -1,10 +1,6 @@ # # test of problem with date fields # ---disable_warnings -drop table if exists t1,t2; ---enable_warnings - create table t1 (a char(16), b date, c datetime); insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01'; select * from t1 where c = '2000-01-01'; -- cgit v1.2.1 From 32f09df2b86ed4d2b9b63b26b5c95f8555bc1db3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 17 Nov 2022 17:33:34 +0100 Subject: MDEV-29890 Update with inner join false row count result when creating a temp table field from an actual table field, these two fields are supposed to be mostly identical (except for BIT field storage), in particular, temp field should have the same default as the orig field, even if the sql_mode has been changed meanwhile (e.g. to include NO_ZERO_DATE) --- mysql-test/main/type_date.result | 18 ++++++++++++++++++ mysql-test/main/type_date.test | 17 +++++++++++++++++ 2 files changed, 35 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result index 0cf78458817..d1ea71dae68 100644 --- a/mysql-test/main/type_date.result +++ b/mysql-test/main/type_date.result @@ -1144,5 +1144,23 @@ t2 CREATE TABLE `t2` ( DROP TABLE t2; DROP TABLE t1; # +# MDEV-29890 Update with inner join false row count result +# +set sql_mode='NO_ZERO_DATE'; +create table t1 (a1 bigint primary key, a2 date not null, a3 bigint not null); +create table t2 (b1 bigint primary key); +insert into t2 (b1) values (1); +insert into t1 (a1, a2, a3) values (1, current_date, 1),( 2, current_date, 1); +update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day; +select row_count(); +row_count() +2 +set sql_mode=''; +alter table t1 modify a2 date not null default '0000-00-00'; +set sql_mode='NO_ZERO_DATE'; +update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day; +drop table t1, t2; +set sql_mode=default; +# # End of 10.4 tests # diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test index 74997aa0ef7..cbc3c67bf64 100644 --- a/mysql-test/main/type_date.test +++ b/mysql-test/main/type_date.test @@ -784,6 +784,23 @@ SHOW CREATE TABLE t2; DROP TABLE t2; DROP TABLE t1; +--echo # +--echo # MDEV-29890 Update with inner join false row count result +--echo # +set sql_mode='NO_ZERO_DATE'; +create table t1 (a1 bigint primary key, a2 date not null, a3 bigint not null); +create table t2 (b1 bigint primary key); +insert into t2 (b1) values (1); +insert into t1 (a1, a2, a3) values (1, current_date, 1),( 2, current_date, 1); +update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day; +select row_count(); +set sql_mode=''; +alter table t1 modify a2 date not null default '0000-00-00'; +set sql_mode='NO_ZERO_DATE'; +update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day; +drop table t1, t2; +set sql_mode=default; + --echo # --echo # End of 10.4 tests --echo # -- cgit v1.2.1 From 1e6ad0ce13c5d2e4125df8006316d9dfe0f8d649 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 9 Jan 2023 12:49:37 +0100 Subject: don't set default value in temp table if NO_DEFAULT_VALUE_FLAG when an internal temporary table field is created from a real field, a new temp field should only copy a default from the source field when the latter has it --- mysql-test/main/default.result | 21 +++++++++++++++++++++ mysql-test/main/default.test | 14 ++++++++++++++ mysql-test/main/olap.result | 10 +++++----- 3 files changed, 40 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result index 100a2fe6a6f..ae835940af3 100644 --- a/mysql-test/main/default.result +++ b/mysql-test/main/default.result @@ -3463,5 +3463,26 @@ SELECT 1 FROM t1 GROUP BY DEFAULT(pk); 1 DROP TABLE t1; # +# MDEV-29890 Update with inner join false row count result +# +create table t1 (a int not null); +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='t1'; +column_name column_default has_default is_nullable +a NULL 0 NO +create or replace view v1 as select * from t1; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +column_name column_default has_default is_nullable +a NULL 0 NO +create or replace view v1 as select * from t1 group by a; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +column_name column_default has_default is_nullable +a NULL 0 NO +create or replace view v1 as select * from t1 group by a with rollup; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +column_name column_default has_default is_nullable +a NULL 1 YES +drop view v1; +drop table t1; +# # End of 10.4 test # diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test index 3064209a4a2..13f611246c9 100644 --- a/mysql-test/main/default.test +++ b/mysql-test/main/default.test @@ -2169,6 +2169,20 @@ INSERT INTO t1 VALUES (),(); SELECT 1 FROM t1 GROUP BY DEFAULT(pk); DROP TABLE t1; +--echo # +--echo # MDEV-29890 Update with inner join false row count result +--echo # +create table t1 (a int not null); +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='t1'; +create or replace view v1 as select * from t1; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +create or replace view v1 as select * from t1 group by a; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +create or replace view v1 as select * from t1 group by a with rollup; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +drop view v1; +drop table t1; + --echo # --echo # End of 10.4 test --echo # diff --git a/mysql-test/main/olap.result b/mysql-test/main/olap.result index cc261c92603..b837aeaf1db 100644 --- a/mysql-test/main/olap.result +++ b/mysql-test/main/olap.result @@ -695,7 +695,7 @@ CREATE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; DESC v1; Field Type Null Key Default Extra -a int(11) YES 0 +a int(11) YES NULL LENGTH(a) int(10) YES NULL COUNT(*) bigint(21) NO 0 SELECT * FROM v1; @@ -858,7 +858,7 @@ INSERT INTO t1 VALUES (1),(2); CREATE OR REPLACE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; DESCRIBE v1; Field Type Null Key Default Extra -a int(11) YES 0 +a int(11) YES NULL LENGTH(a) int(10) YES NULL COUNT(*) bigint(21) NO 0 DROP VIEW v1; @@ -868,7 +868,7 @@ INSERT INTO t1 VALUES (1),(2); CREATE OR REPLACE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; DESCRIBE v1; Field Type Null Key Default Extra -a bigint(20) YES 0 +a bigint(20) YES NULL LENGTH(a) int(10) YES NULL COUNT(*) bigint(21) NO 0 DROP VIEW v1; @@ -893,8 +893,8 @@ DROP VIEW v1; CREATE VIEW v1 AS SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP; DESC v1; Field Type Null Key Default Extra -a int(11) YES 0 -b int(20) YES 0 +a int(11) YES NULL +b int(20) YES NULL DROP VIEW v1; DROP TABLE t1; # -- cgit v1.2.1 From 6cb84346e1bde63ec79dd5e3a7d80f69bb106ead Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 17 Nov 2022 19:23:08 +0100 Subject: MDEV-17869 AddressSanitizer: use-after-poison in Item_change_list::rollback_item_tree_changes it's incorrect to use change_item_tree() to replace arguments of top-level AND/OR, because they (arguments) are stored in a List, so a pointer to an argument is in the list_node, and individual list_node's of top-level AND/OR can be deleted in Item_cond::build_equal_items(). In that case rollback_item_tree_changes() will modify the deleted object. Luckily, it's not needed to use change_item_tree() for top-level AND/OR, because the whole top-level item is copied and preserved in prep_where and prep_on, and restored from there. So, just don't. Additionally to the test case in the commit it fixes * ASAN failure of main.opt_tvc --ps * ASAN failure of main.having_cond_pushdown --ps --- mysql-test/main/prepare.result | 16 ++++++++++++++++ mysql-test/main/prepare.test | 17 +++++++++++++++++ 2 files changed, 33 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/prepare.result b/mysql-test/main/prepare.result index cfe6603dbbe..7c730bff0c5 100644 --- a/mysql-test/main/prepare.result +++ b/mysql-test/main/prepare.result @@ -64,3 +64,19 @@ SQRT(?) is not null # # End of 10.3 tests # +# +# MDEV-17869 AddressSanitizer: use-after-poison in Item_change_list::rollback_item_tree_changes +# +create table t1 (pk int, v1 varchar(1)); +insert t1 values (1,'v'),(2,'v'),(3,'c'); +create table t2 (pk int, v1 varchar(1)); +insert t2 values (1,'x'); +create table t3 (pk int, i1 int, v1 varchar(1)); +insert t3 values (10,8,9); +execute immediate 'select straight_join 1 from (t1 join t2 on (t1.v1 = t2.v1)) +where (3, 6) in (select tc.pk, t3.i1 from (t3 join t1 as tc on (tc.v1 = t3.v1)) having tc.pk > 1 );'; +1 +drop table t1, t2, t3; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/prepare.test b/mysql-test/main/prepare.test index 4d1573eb0c8..bf37f6dc8d1 100644 --- a/mysql-test/main/prepare.test +++ b/mysql-test/main/prepare.test @@ -52,3 +52,20 @@ execute p1 using 17864960750176564435; --echo # --echo # End of 10.3 tests --echo # + +--echo # +--echo # MDEV-17869 AddressSanitizer: use-after-poison in Item_change_list::rollback_item_tree_changes +--echo # +create table t1 (pk int, v1 varchar(1)); +insert t1 values (1,'v'),(2,'v'),(3,'c'); +create table t2 (pk int, v1 varchar(1)); +insert t2 values (1,'x'); +create table t3 (pk int, i1 int, v1 varchar(1)); +insert t3 values (10,8,9); +execute immediate 'select straight_join 1 from (t1 join t2 on (t1.v1 = t2.v1)) +where (3, 6) in (select tc.pk, t3.i1 from (t3 join t1 as tc on (tc.v1 = t3.v1)) having tc.pk > 1 );'; +drop table t1, t2, t3; + +--echo # +--echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From b928c849d220f47de0bdfd6a78b8eb584b3e5ae6 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 4 Jan 2023 13:01:47 +0300 Subject: MDEV-28602 Wrong result with outer join, merged derived table and view (Variant 3, initial variant was by Rex Jonston) A LEFT JOIN with a constant as a column of the inner table produced wrong query result if the optimizer had to write the inner table column into a temp table. Query pattern: SELECT ... FROM (SELECT /*non-mergeable select*/ FROM t1 LEFT JOIN (SELECT 'Y' as Val) t2 ON ...) as tbl Fixed this by adding Item_direct_view_ref::save_in_field() which follows the pattern of Item_direct_view_ref's save_org_in_field(), save_in_result_field() and val_XXX() functions: * call check_null_ref() and handle NULL value * if we didn't get a NULL-complemented row, call Item_direct_ref's function. --- mysql-test/main/join_outer.result | 85 ++++++++++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 85 ++++++++++++++++++++++++++++++++++ mysql-test/main/join_outer_jcl6.result | 85 ++++++++++++++++++++++++++++++++++ 3 files changed, 255 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 758d1eda7ae..ae0aabeabc5 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -1,4 +1,5 @@ drop table if exists t0,t1,t2,t3,t4,t5; +drop view if exists v0,v1,v2,v3; SET @org_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); set join_cache_level=1; @@ -2822,5 +2823,89 @@ WHERE t3.pk IN (2); 1 drop view v4; drop table t1,t2,t3,t4; +# +# MDEV-28602 Wrong result with outer join, merged derived table and view +# +create table t1 ( +Election int(10) unsigned NOT NULL +); +insert into t1 (Election) values (1); +create table t2 ( +VoteID int(10), +ElectionID int(10), +UserID int(10) +); +insert into t2 (ElectionID, UserID) values (2, 30), (3, 30); +create view v1 as select * from t1 +left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T +on T.ElectionID = t1.Election +limit 9; +select * from v1; +Election Voted ElectionID +1 NULL NULL +drop table t1, t2; +drop view v1; +# +# and another contrived example showing a bit of heirarchy +# +create table t10 (a int); +create table t20 (b int); +insert into t10 values (1),(2); +insert into t20 values (1),(3); +create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3; +create table t30 (c int); +insert into t30 values (1),(3); +create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6; +select * from v20 limit 9; +c x u y b +1 X U Y 1 +3 NULL NULL NULL NULL +drop view v10, v20; +drop table t10, t20, t30; +# +# More complex testcase +# +create table t2 (b int); +insert into t2 values (3),(7),(1); +create table t3 (c int); +insert into t3 values (3),(1); +create table t1 (a int); +insert into t1 values (1),(2),(7),(1); +select * from +( +select * from +(select 'Z' as z, t1.a from t1) dt1 +left join +(select 'Y' as y, t2.b from t2) dt2 +left join +(select 'X' as x, t3.c from t3) dt3 +on dt2.b=dt3.c +on dt1.a=dt2.b +limit 9 +) dt; +z a y b x c +Z 1 Y 1 X 1 +Z 2 NULL NULL NULL NULL +Z 7 Y 7 NULL NULL +Z 1 Y 1 X 1 +create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3; +create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2; +create view v0(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c; +create view v1 as select 'Z' as z, t1.a, v0.* from t1 left join v0 on t1.a=v0.b limit 9; +select * from v1; +z a y b x c +Z 1 Y 1 X 1 +Z 2 NULL NULL NULL NULL +Z 7 Y 7 NULL NULL +Z 1 Y 1 X 1 +set statement join_cache_level=0 for +select * from v1; +z a y b x c +Z 1 Y 1 X 1 +Z 2 NULL NULL NULL NULL +Z 7 Y 7 NULL NULL +Z 1 Y 1 X 1 +drop view v0, v1, v2, v3; +drop table t1, t2, t3; # end of 10.3 tests SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index ff74e5280e7..5e1e83e4049 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -6,6 +6,7 @@ --disable_warnings drop table if exists t0,t1,t2,t3,t4,t5; +drop view if exists v0,v1,v2,v3; --enable_warnings SET @org_optimizer_switch=@@optimizer_switch; @@ -2341,6 +2342,90 @@ WHERE t3.pk IN (2); drop view v4; drop table t1,t2,t3,t4; +--echo # +--echo # MDEV-28602 Wrong result with outer join, merged derived table and view +--echo # + +create table t1 ( + Election int(10) unsigned NOT NULL +); + +insert into t1 (Election) values (1); + +create table t2 ( + VoteID int(10), + ElectionID int(10), + UserID int(10) +); + +insert into t2 (ElectionID, UserID) values (2, 30), (3, 30); +create view v1 as select * from t1 + left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T + on T.ElectionID = t1.Election +limit 9; +# limit X causes merge algorithm select as opposed to temp table +select * from v1; +drop table t1, t2; +drop view v1; + +--echo # +--echo # and another contrived example showing a bit of heirarchy +--echo # +create table t10 (a int); +create table t20 (b int); +insert into t10 values (1),(2); +insert into t20 values (1),(3); +create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3; +create table t30 (c int); +insert into t30 values (1),(3); +create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6; +select * from v20 limit 9; +drop view v10, v20; +drop table t10, t20, t30; + +--echo # +--echo # More complex testcase +--echo # +create table t2 (b int); +insert into t2 values (3),(7),(1); +create table t3 (c int); +insert into t3 values (3),(1); +create table t1 (a int); +insert into t1 values (1),(2),(7),(1); + +select * from +( + select * from + (select 'Z' as z, t1.a from t1) dt1 + left join + (select 'Y' as y, t2.b from t2) dt2 + left join + (select 'X' as x, t3.c from t3) dt3 + on dt2.b=dt3.c + on dt1.a=dt2.b + limit 9 +) dt; + +## Same as dt3 above +create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3; + +## Same as dt2 above +create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2; + +## Same as (...) in the "... dt1 left join (...)" above +create view v0(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c; + +# Same as above select statement +create view v1 as select 'Z' as z, t1.a, v0.* from t1 left join v0 on t1.a=v0.b limit 9; + +select * from v1; + +set statement join_cache_level=0 for +select * from v1; + +drop view v0, v1, v2, v3; +drop table t1, t2, t3; + --echo # end of 10.3 tests SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 71579ae7b38..4e03ffb51cf 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -6,6 +6,7 @@ set @@join_cache_level=6; set @optimizer_switch_for_join_outer_test=@@optimizer_switch; set @join_cache_level_for_join_outer_test=@@join_cache_level; drop table if exists t0,t1,t2,t3,t4,t5; +drop view if exists v0,v1,v2,v3; SET @org_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); set join_cache_level=@join_cache_level_for_join_outer_test; @@ -2829,5 +2830,89 @@ WHERE t3.pk IN (2); 1 drop view v4; drop table t1,t2,t3,t4; +# +# MDEV-28602 Wrong result with outer join, merged derived table and view +# +create table t1 ( +Election int(10) unsigned NOT NULL +); +insert into t1 (Election) values (1); +create table t2 ( +VoteID int(10), +ElectionID int(10), +UserID int(10) +); +insert into t2 (ElectionID, UserID) values (2, 30), (3, 30); +create view v1 as select * from t1 +left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T +on T.ElectionID = t1.Election +limit 9; +select * from v1; +Election Voted ElectionID +1 NULL NULL +drop table t1, t2; +drop view v1; +# +# and another contrived example showing a bit of heirarchy +# +create table t10 (a int); +create table t20 (b int); +insert into t10 values (1),(2); +insert into t20 values (1),(3); +create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3; +create table t30 (c int); +insert into t30 values (1),(3); +create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6; +select * from v20 limit 9; +c x u y b +1 X U Y 1 +3 NULL NULL NULL NULL +drop view v10, v20; +drop table t10, t20, t30; +# +# More complex testcase +# +create table t2 (b int); +insert into t2 values (3),(7),(1); +create table t3 (c int); +insert into t3 values (3),(1); +create table t1 (a int); +insert into t1 values (1),(2),(7),(1); +select * from +( +select * from +(select 'Z' as z, t1.a from t1) dt1 +left join +(select 'Y' as y, t2.b from t2) dt2 +left join +(select 'X' as x, t3.c from t3) dt3 +on dt2.b=dt3.c +on dt1.a=dt2.b +limit 9 +) dt; +z a y b x c +Z 1 Y 1 X 1 +Z 1 Y 1 X 1 +Z 7 Y 7 NULL NULL +Z 2 NULL NULL NULL NULL +create view v3(x,c) as select * from (select 'X' as x, t3.c from t3) dt3; +create view v2(y,b) as select * from (select 'Y' as y, t2.b from t2) dt2; +create view v0(y,b,x,c) as select * from v2 left join v3 on v2.b=v3.c; +create view v1 as select 'Z' as z, t1.a, v0.* from t1 left join v0 on t1.a=v0.b limit 9; +select * from v1; +z a y b x c +Z 1 Y 1 X 1 +Z 1 Y 1 X 1 +Z 7 Y 7 NULL NULL +Z 2 NULL NULL NULL NULL +set statement join_cache_level=0 for +select * from v1; +z a y b x c +Z 1 Y 1 X 1 +Z 2 NULL NULL NULL NULL +Z 7 Y 7 NULL NULL +Z 1 Y 1 X 1 +drop view v0, v1, v2, v3; +drop table t1, t2, t3; # end of 10.3 tests SET optimizer_switch=@org_optimizer_switch; -- cgit v1.2.1 From 7d1df207c4ded0ac8aa61f0d35bcae7eda974c54 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 11 Jan 2023 18:12:40 +0200 Subject: MDEV-30373 Wrong result with range access This issue was caused by the bug fix for MDEV-30325 Wrong result upon range query using index condition The bug could happen in the case of several overlapping key ranges with OR --- mysql-test/main/range.result | 11 +++++++++++ mysql-test/main/range.test | 9 +++++++++ mysql-test/main/range_aria_dbt3.result | 16 ++++++++++++++-- mysql-test/main/range_aria_dbt3.test | 11 ++++++++++- mysql-test/main/range_mrr_icp.result | 11 +++++++++++ 5 files changed, 55 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 145464896b3..31777773240 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3655,6 +3655,17 @@ b SET @@optimizer_switch=@save_optimizer_switch; drop table t1,t2; # +# MDEV-30373 Wrong result with range access +# +CREATE TABLE t1 (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,3),(2,6),(3,9); +SELECT * FROM t1 WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); +pk a +1 3 +2 6 +3 9 +DROP TABLE t1; +# # End of 10.5 tests # set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 8f9af563dc3..ba6f2942a9b 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2520,6 +2520,15 @@ SELECT t1.b FROM t1 LEFT JOIN t2 ON t1.a = t2.pk WHERE t1.a IS NULL ORDER BY t1. SET @@optimizer_switch=@save_optimizer_switch; drop table t1,t2; +--echo # +--echo # MDEV-30373 Wrong result with range access +--echo # + +CREATE TABLE t1 (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,3),(2,6),(3,9); +SELECT * FROM t1 WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); +DROP TABLE t1; + --echo # --echo # End of 10.5 tests --echo # diff --git a/mysql-test/main/range_aria_dbt3.result b/mysql-test/main/range_aria_dbt3.result index ae5a2e1329f..f08a1b244f8 100644 --- a/mysql-test/main/range_aria_dbt3.result +++ b/mysql-test/main/range_aria_dbt3.result @@ -6,8 +6,20 @@ use dbt3_s001; # SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); COUNT(*) -5056 +5658 +SELECT COUNT(*) FROM lineitem ignore index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); +COUNT(*) +5658 # -# End of 10.5 tests +# MDEV-30373 Wrong result with range access # +explain SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 8 NULL 506 Using where; Using index +SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); +COUNT(*) +293 DROP DATABASE dbt3_s001; +# +# End of 10.5 tests +# diff --git a/mysql-test/main/range_aria_dbt3.test b/mysql-test/main/range_aria_dbt3.test index 89328280987..141bf43885b 100644 --- a/mysql-test/main/range_aria_dbt3.test +++ b/mysql-test/main/range_aria_dbt3.test @@ -16,9 +16,18 @@ use dbt3_s001; --echo # SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); +SELECT COUNT(*) FROM lineitem ignore index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); --echo # ---echo # End of 10.5 tests +--echo # MDEV-30373 Wrong result with range access --echo # +explain SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); + +SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND 672 AND l_linenumber BETWEEN 4 AND 9 ); + DROP DATABASE dbt3_s001; + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index e68af8545cf..6817edd30cd 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3644,6 +3644,17 @@ b SET @@optimizer_switch=@save_optimizer_switch; drop table t1,t2; # +# MDEV-30373 Wrong result with range access +# +CREATE TABLE t1 (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,3),(2,6),(3,9); +SELECT * FROM t1 WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); +pk a +1 3 +2 6 +3 9 +DROP TABLE t1; +# # End of 10.5 tests # set global innodb_stats_persistent= @innodb_stats_persistent_save; -- cgit v1.2.1 From b194c83b7bd2f4e861fdce041a9ea3de18f4d227 Mon Sep 17 00:00:00 2001 From: Brandon Nesterenko Date: Thu, 3 Jun 2021 11:24:34 -0600 Subject: MDEV-25277: mysqlbinlog --verbose cannot read row events with compressed columns: Don't know how to handle column type: 140 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Problem: ======= Mysqlbinlog cannot show the type of a compressed column when two levels of verbosity is provided. Solution: ======== Extend the log event printing logic to handle and tag compressed types. Behavioral Changes: ================== Old: When mysqlbinlog is called in verbose mode and the database uses compressed columns, an error is returned to the user. New: The output will append “ COMPRESSED” on the type of compressed columns Reviewed By =========== Andrei Elkin --- .../r/binlog_verbose_compressed_fields.result | 15 +++++ .../binlog/t/binlog_verbose_compressed_fields.test | 70 ++++++++++++++++++++++ 2 files changed, 85 insertions(+) create mode 100644 mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result create mode 100644 mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test (limited to 'mysql-test') diff --git a/mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result b/mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result new file mode 100644 index 00000000000..6ee11754935 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result @@ -0,0 +1,15 @@ +CREATE TABLE t1 (a TEXT, ac TEXT COMPRESSED, b TINYTEXT, bc TINYTEXT COMPRESSED, c MEDIUMTEXT, cc MEDIUMTEXT COMPRESSED, d LONGTEXT, dc LONGTEXT COMPRESSED, e VARCHAR(10), ec VARCHAR(10) COMPRESSED); +# Isolate row event into its own binary log +FLUSH BINARY LOGS; +INSERT INTO t1 VALUES ('mya', 'myac', 'myb', 'mybc', 'myc', 'mycc', 'myd', 'mydc', 'mye', 'myec'); +FLUSH BINARY LOGS; +# MYSQLBINLOG --base64-output=decode-rows -vv datadir/binlog_file --result-file=result_binlog +include/assert_grep.inc [Ensure compressed TEXT fields are annotated correctly] +include/assert_grep.inc [Ensure compressed TINYTEXT fields are annotated correctly] +include/assert_grep.inc [Ensure compressed MEDIUMTEXT fields are annotated correctly] +include/assert_grep.inc [Ensure compressed LONGTEXT fields are annotated correctly] +include/assert_grep.inc [Ensure compressed VARSTRING fields are annotated correctly] +include/assert_grep.inc [Ensure COMPRESSED only shows up for corresponding fields] +include/assert_grep.inc [Ensure non-compressed TEXT fields are annotated correctly] +include/assert_grep.inc [Ensure non-compressed VARSTRING fields are annotated correctly] +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test b/mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test new file mode 100644 index 00000000000..8cbcdbef601 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test @@ -0,0 +1,70 @@ +# +# Purpose: +# This test validates that mysqlbinlog is able to annotate compressed column +# types with two levels of verbosity. +# +# Methodology: +# Validate that the output from mysqlbinlog -vv after creating and inserting +# into a table with compressed and uncompressed fields correctly annotates +# which columns are compressed +# +# References: +# MDEV-25277: mysqlbinlog --verbose cannot read row events with compressed +# columns: Don't know how to handle column type: 140 +# +--source include/have_binlog_format_row.inc + +CREATE TABLE t1 (a TEXT, ac TEXT COMPRESSED, b TINYTEXT, bc TINYTEXT COMPRESSED, c MEDIUMTEXT, cc MEDIUMTEXT COMPRESSED, d LONGTEXT, dc LONGTEXT COMPRESSED, e VARCHAR(10), ec VARCHAR(10) COMPRESSED); + +--echo # Isolate row event into its own binary log +FLUSH BINARY LOGS; +INSERT INTO t1 VALUES ('mya', 'myac', 'myb', 'mybc', 'myc', 'mycc', 'myd', 'mydc', 'mye', 'myec'); +FLUSH BINARY LOGS; + +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 2) +--let $datadir= `SELECT @@datadir` +--let $result_binlog= $MYSQLTEST_VARDIR/tmp/$binlog_file + +--echo # MYSQLBINLOG --base64-output=decode-rows -vv datadir/binlog_file --result-file=result_binlog +--exec $MYSQL_BINLOG --base64-output=decode-rows -vv $datadir/$binlog_file --result-file=$result_binlog + +--let $assert_file= $result_binlog +--let $assert_count= 1 + +--let $assert_text= Ensure compressed TEXT fields are annotated correctly +--let $assert_select=\WTEXT COMPRESSED +--source include/assert_grep.inc + +--let $assert_text= Ensure compressed TINYTEXT fields are annotated correctly +--let $assert_select=\WTINYTEXT COMPRESSED +--source include/assert_grep.inc + +--let $assert_text= Ensure compressed MEDIUMTEXT fields are annotated correctly +--let $assert_select=\WMEDIUMTEXT COMPRESSED +--source include/assert_grep.inc + +--let $assert_text= Ensure compressed LONGTEXT fields are annotated correctly +--let $assert_select=\WLONGTEXT COMPRESSED +--source include/assert_grep.inc + +--let $assert_text= Ensure compressed VARSTRING fields are annotated correctly +--let $assert_select=\WVARSTRING\(\d+\) COMPRESSED +--source include/assert_grep.inc + +--let $assert_text= Ensure COMPRESSED only shows up for corresponding fields +--let $assert_count= 5 +--let $assert_select= COMPRESSED +--source include/assert_grep.inc + +--let $assert_text= Ensure non-compressed TEXT fields are annotated correctly +--let $assert_count= 8 +--let $assert_select=/*.*TEXT +--source include/assert_grep.inc + +--let $assert_text= Ensure non-compressed VARSTRING fields are annotated correctly +--let $assert_count= 2 +--let $assert_select=/*.*VARSTRING +--source include/assert_grep.inc + +# Cleanup +DROP TABLE t1; -- cgit v1.2.1 From f3d8a546e7476ddec8386eacd5c58bf1c1519377 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 11 Jan 2023 20:15:26 +0200 Subject: MDEV-30345 DML does not find rows it is supposed to This only happens with 'timestamp_column IN (select ...) The reason was a missing assignment in Item_cache_timestamp::cache_value() --- mysql-test/main/cache_temporal_4265.result | 18 ++++++++++++++++++ mysql-test/main/cache_temporal_4265.test | 18 ++++++++++++++++++ 2 files changed, 36 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/cache_temporal_4265.result b/mysql-test/main/cache_temporal_4265.result index 1cda7004a6f..5c648c8de53 100644 --- a/mysql-test/main/cache_temporal_4265.result +++ b/mysql-test/main/cache_temporal_4265.result @@ -20,3 +20,21 @@ select (select max(m2.ut) from t1 m2 where m1.id <> 0) from t1 m1; 2001-01-01 00:00:00.200000 2001-01-01 00:00:00.200000 drop table t1; +# +# MDEV-30345 DML does not find rows it is supposed to +# +CREATE TABLE t1 (f timestamp); +INSERT INTO t1 VALUES ('2022-01-01 00:00:00'),('2022-12-12 12:12:12'); +CREATE TABLE t2 (f timestamp); +INSERT INTO t2 VALUES ('2022-01-01 00:00:00'),('2022-12-12 12:12:12'); +SELECT * FROM t2 WHERE f IN ( SELECT f FROM t1 ) ; +f +2022-01-01 00:00:00 +2022-12-12 12:12:12 +DELETE FROM t2 WHERE f IN ( SELECT f FROM t1 ) ; +SELECT * FROM t2 WHERE f IN ( SELECT f FROM t1 ) ; +f +DROP TABLE t1,t2; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/cache_temporal_4265.test b/mysql-test/main/cache_temporal_4265.test index 1af683c617b..1dfd57b0c8d 100644 --- a/mysql-test/main/cache_temporal_4265.test +++ b/mysql-test/main/cache_temporal_4265.test @@ -20,3 +20,21 @@ select * from t1; select (select max(m2.ut) from t1 m2 where m1.id <> 0) from t1 m1; drop table t1; +--echo # +--echo # MDEV-30345 DML does not find rows it is supposed to +--echo # + +CREATE TABLE t1 (f timestamp); +INSERT INTO t1 VALUES ('2022-01-01 00:00:00'),('2022-12-12 12:12:12'); + +CREATE TABLE t2 (f timestamp); +INSERT INTO t2 VALUES ('2022-01-01 00:00:00'),('2022-12-12 12:12:12'); + +SELECT * FROM t2 WHERE f IN ( SELECT f FROM t1 ) ; +DELETE FROM t2 WHERE f IN ( SELECT f FROM t1 ) ; +SELECT * FROM t2 WHERE f IN ( SELECT f FROM t1 ) ; +DROP TABLE t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From c08dba7b2864913bbf6fc36307837498f7def44a Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 12 Jan 2023 02:48:33 +0200 Subject: Fixed failing test main.func_json --- mysql-test/main/func_json.result | 1 + mysql-test/main/func_json.test | 1 + 2 files changed, 2 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 6ea8c78b2b6..a8f97d087e8 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1259,3 +1259,4 @@ JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) [{"range_scan_alternatives": [{"index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true}], "analyzing_roworder_intersect": {"cause": "too few roworder scans"}, "analyzing_index_merge_union": [], "test_one_line_array": ["123"]}] +drop table t200; diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 6cfcefbee3d..ffe7575310f 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -818,3 +818,4 @@ insert into t200 values select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; +drop table t200; -- cgit v1.2.1 From 17920291a661d3d2b02a9261293caebc7158d38a Mon Sep 17 00:00:00 2001 From: Rucha Deodhar Date: Thu, 12 Jan 2023 20:11:19 +0530 Subject: fixup for MDEV-19160 --- mysql-test/main/func_json.result | 6 +++--- mysql-test/main/func_json.test | 9 ++++----- 2 files changed, 7 insertions(+), 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index a8f97d087e8..400ecdbfc36 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1131,9 +1131,6 @@ SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{" a DROP TABLE t1; # -# End of 10.4 tests -# -# # MDEV-19160 JSON_DETAILED output unnecessarily verbose # create table t200 (a text); @@ -1260,3 +1257,6 @@ select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200 JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) [{"range_scan_alternatives": [{"index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true}], "analyzing_roworder_intersect": {"cause": "too few roworder scans"}, "analyzing_index_merge_union": [], "test_one_line_array": ["123"]}] drop table t200; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index ffe7575310f..743f97fedc1 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -715,11 +715,6 @@ SELECT * FROM t1 WHERE CASE WHEN JSON_OBJECT('x', (SELECT MAX(a=2) FROM t1))='{" DROP TABLE t1; ---echo # ---echo # End of 10.4 tests ---echo # - - --echo # --echo # MDEV-19160 JSON_DETAILED output unnecessarily verbose --echo # @@ -819,3 +814,7 @@ insert into t200 values select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; drop table t200; + +--echo # +--echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From 12618cfb28cb2843dc74bb3a176dae76acdb698a Mon Sep 17 00:00:00 2001 From: Weijun-H Date: Sun, 8 Jan 2023 08:43:02 +0000 Subject: MDEV-19160 json_pretty() alias for json_detailed() --- mysql-test/main/func_json.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/func_json.test | 1 + 2 files changed, 32 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 400ecdbfc36..1e59256b517 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1253,6 +1253,37 @@ JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) ["123"] } ] +select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; +JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) +[ + { + "range_scan_alternatives": + [ + { + "index": "a_b", + "ranges": + [ + "2 <= a <= 2 AND 4 <= b <= 4", + "123" + ], + "rowid_ordered": true, + "using_mrr": false, + "index_only": true, + "rows": 1, + "cost": 1.1752, + "chosen": true + } + ], + "analyzing_roworder_intersect": + { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": + [], + "test_one_line_array": + ["123"] + } +] select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) [{"range_scan_alternatives": [{"index": "a_b", "ranges": ["2 <= a <= 2 AND 4 <= b <= 4", "123"], "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1, "cost": 1.1752, "chosen": true}], "analyzing_roworder_intersect": {"cause": "too few roworder scans"}, "analyzing_index_merge_union": [], "test_one_line_array": ["123"]}] diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 743f97fedc1..b3e19f76972 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -812,6 +812,7 @@ insert into t200 values select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; +select JSON_PRETTY(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; select JSON_LOOSE(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200; drop table t200; -- cgit v1.2.1 From 7a98d232e42b66efc759d584b05214e91681c346 Mon Sep 17 00:00:00 2001 From: Nikita Malyavin Date: Wed, 11 Jan 2023 18:57:44 +0300 Subject: MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT constraint node->is_delete was incorrectly set to NO_DELETE for a set of operations. In general we shouldn't rely on sql_command and look for more abstract ways to control the behavior. trg_event_map seems to be a suitable way. To mind replica nodes, it is ORed with slave_fk_event_map, which stores trg_event_map when replica has triggers disabled. --- mysql-test/suite/versioning/r/foreign.result | 39 +++++++++++++++++++++++ mysql-test/suite/versioning/t/foreign.test | 46 ++++++++++++++++++++++++++++ 2 files changed, 85 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 3eb968f1a33..9b5946e5262 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -493,3 +493,42 @@ set foreign_key_checks= on; delete history from t1; delete from t1; drop table t1; +# +# MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT +# constraint +# +create table t0 (pk integer primary key) with system versioning engine=innodb; +create table t1 (pk integer primary key, +foreign key(pk) references t0(pk) +on delete restrict on update cascade) engine=innodb; +create table t2 (pk integer); +insert into t0 (pk) values (1); +insert into t1 (pk) values (1); +insert into t2 (pk) values (1); +delete from t0; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +replace t0 values (1); +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +select * into outfile 'load_t0' from t0 ; +load data infile 'load_t0' replace into table t0; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +delete t0, t2 from t0 join t2; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE) +select pk from t0; +pk +1 +# Cleanup +drop table t1, t0, t2; +# create_select for a temporary table didn't set up pos_in_locked_tables. +create table t (a int unique) engine=innodb +replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +a b +1 3 +drop table t; +create temporary table t (a int unique) engine=innodb +replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +a b +1 3 +drop table t; diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index f4e4fa7a354..85b0c3e92e4 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -527,4 +527,50 @@ delete from t1; # cleanup drop table t1; +--echo # +--echo # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT +--echo # constraint +--echo # +create table t0 (pk integer primary key) with system versioning engine=innodb; +create table t1 (pk integer primary key, + foreign key(pk) references t0(pk) + on delete restrict on update cascade) engine=innodb; +create table t2 (pk integer); + +insert into t0 (pk) values (1); +insert into t1 (pk) values (1); +insert into t2 (pk) values (1); + +--error ER_ROW_IS_REFERENCED_2 +delete from t0; + +--error ER_ROW_IS_REFERENCED_2 +replace t0 values (1); + +select * into outfile 'load_t0' from t0 ; +--error ER_ROW_IS_REFERENCED_2 +load data infile 'load_t0' replace into table t0; + +--error ER_ROW_IS_REFERENCED_2 +delete t0, t2 from t0 join t2; + +select pk from t0; + +--echo # Cleanup +drop table t1, t0, t2; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load_t0 + + +--echo # create_select for a temporary table didn't set up pos_in_locked_tables. +create table t (a int unique) engine=innodb + replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +drop table t; + +create temporary table t (a int unique) engine=innodb + replace select 1 as a, 2 as b union select 1 as a, 3 as c; +select * from t; +drop table t; + --source suite/versioning/common_finish.inc -- cgit v1.2.1