diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2023-01-13 10:18:30 +0200 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2023-01-13 10:18:30 +0200 |
| commit | 73ecab3d26c055928673a2629f4513aa7008dbf0 (patch) | |
| tree | b04760b3369cd1d8fc03c7dec204f5a371335b29 /mysql-test/main | |
| parent | 7d1df207c4ded0ac8aa61f0d35bcae7eda974c54 (diff) | |
| parent | 71e8e4934db06c02db1b51716e9d4b3992505161 (diff) | |
| download | mariadb-git-73ecab3d26c055928673a2629f4513aa7008dbf0.tar.gz | |
Merge 10.4 into 10.5
Diffstat (limited to 'mysql-test/main')
30 files changed, 997 insertions, 310 deletions
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 # diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index 3af5aeb7330..49e60dd9bcc 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -2031,6 +2031,17 @@ 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 074195a331d..db8638ebe54 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -1893,6 +1893,19 @@ CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAI 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/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 @@ -2170,5 +2170,19 @@ 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/func_group.result b/mysql-test/main/func_group.result index aa2bcfef123..244fdb2856f 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'; @@ -2567,5 +2566,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 ac4ca32e7f5..e5ae33f7208 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'; @@ -1802,5 +1798,13 @@ 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 # diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 0c87ede0f31..b0a7cb4988c 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1291,6 +1291,164 @@ SELECT JSON_OBJECTAGG(a) FROM t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') FROM t1' at line 1 DROP TABLE t1; # +# 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_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"]}] +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 316d0b0f954..7e7b83c720d 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -817,6 +817,107 @@ SELECT JSON_OBJECTAGG(a) FROM t1; DROP TABLE t1; --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_PRETTY(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 # 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 # diff --git a/mysql-test/main/join_nested.result b/mysql-test/main/join_nested.result index b323190d8d5..816fe4b5e35 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 cc1bd327031..8ac6b5a1d6b 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 41b47bfa868..6ac2a291ed1 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)); diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index fcffeb6d161..6bcaee31e54 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; @@ -2825,5 +2826,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 721989c3368..3d73ebdc9ba 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; @@ -2832,5 +2833,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; diff --git a/mysql-test/main/olap.result b/mysql-test/main/olap.result index 93eda747d83..b837aeaf1db 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'; @@ -696,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; @@ -859,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; @@ -869,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; @@ -894,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; # 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/opt_trace.result b/mysql-test/main/opt_trace.result index 13b8a66ca51..f365041b28a 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -7421,17 +7421,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, @@ -7445,8 +7441,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; @@ -7455,17 +7450,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, @@ -7479,8 +7470,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t0,t1; @@ -7492,17 +7482,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, @@ -7516,8 +7502,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1,one_k; @@ -7537,17 +7522,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, @@ -7561,8 +7542,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table ten,t1; @@ -7576,17 +7556,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, @@ -7600,8 +7576,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; @@ -7610,17 +7585,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, @@ -7634,8 +7605,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7652,17 +7622,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, @@ -7676,8 +7642,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; @@ -7687,17 +7652,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, @@ -7711,8 +7672,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; @@ -7722,17 +7682,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, @@ -7746,8 +7702,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7760,17 +7715,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, @@ -7784,8 +7735,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7801,17 +7751,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, @@ -7825,8 +7771,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7840,17 +7785,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, @@ -7864,8 +7805,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1; @@ -7896,17 +7836,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, @@ -7920,8 +7856,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] drop table t1, t0, one_k; @@ -7960,19 +7895,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, @@ -7992,18 +7923,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 4.017089844, "rest_of_plan": [ - { "plan_prefix": - [ - "A" - ], + ["A"], "table": "B", "best_access_path": { "considered_access_paths": [ - { "access_type": "scan", "resulting_rows": 804.6875, @@ -8025,17 +7952,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.6875, @@ -8066,19 +7990,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, @@ -8098,18 +8018,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 4.017089844, "rest_of_plan": [ - { "plan_prefix": - [ - "A" - ], + ["A"], "table": "B", "best_access_path": { "considered_access_paths": [ - { "access_type": "ref", "index": "b", @@ -8120,7 +8036,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20.00585794, "chosen": true }, - { "access_type": "scan", "resulting_rows": 804.6875, @@ -8143,17 +8058,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.6875, @@ -8189,17 +8101,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, @@ -8213,8 +8121,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "too few roworder scans" }, "analyzing_index_merge_union": - [ - ] + [] } ] DROP TABLE t1; @@ -8236,7 +8143,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": [ @@ -8257,15 +8163,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, @@ -8291,19 +8193,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, @@ -8323,18 +8221,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 4.021972656, "rest_of_plan": [ - { "plan_prefix": - [ - "t1" - ], + ["t1"], "table": "t2", "best_access_path": { "considered_access_paths": [ - { "access_type": "ref", "index": "a", @@ -8345,7 +8239,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 20.00585794, "chosen": true }, - { "access_type": "scan", "resulting_rows": 100, @@ -8368,17 +8261,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, @@ -8399,18 +8289,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost_for_plan": 22.21972656, "rest_of_plan": [ - { "plan_prefix": - [ - "t2" - ], + ["t2"], "table": "t1", "best_access_path": { "considered_access_paths": [ - { "access_type": "ref", "index": "a", @@ -8421,7 +8307,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "cost": 200.0585794, "chosen": true }, - { "access_type": "scan", "resulting_rows": 10, @@ -8465,24 +8350,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.046875 }, - { "column_name": "b", "ranges": - [ - "NULL < b <= 5" - ], + ["NULL < b <= 5"], "selectivity_from_histogram": 0.046875 } ] @@ -8495,9 +8373,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": @@ -8517,15 +8393,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.15625 } ] @@ -8543,15 +8415,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, @@ -8737,7 +8605,6 @@ select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) from information_schema.optimizer_trace; JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) [ - { "members": [ @@ -8763,19 +8630,16 @@ select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) [ - { "item": "t0.a in (1,2,3,4,5,6)", "conversion": [ - { "join_preparation": { "select_id": 2, "steps": [ - { "derived": { @@ -8784,7 +8648,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) "algorithm": "materialized" } }, - { "transformation": { @@ -8795,7 +8658,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) "possible": true } }, - { "transformation": { @@ -8805,7 +8667,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) "chosen": true } }, - { "expanded_query": "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0" } @@ -8822,7 +8683,6 @@ select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) [ - { "item": "t0.a in (1,2,3,4,5,t0.a + 1)", "done": false, @@ -8836,7 +8696,6 @@ select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.in_to_subquery_conversion')) [ - { "item": "t0.a in ('1','2','3','4','5','6')", "done": false, diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 3b79a7a44e8..2b5d4cbaae5 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": 2700.058937, "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": 10.31393703, @@ -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": 10.31393703, @@ -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": 10.31393703, @@ -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": 457.058937, "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": 457.058937 }, - { "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": 457.058937, "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": 10.31393703, @@ -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": 10.31393703, @@ -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": 10.31393703, @@ -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": 10.31393703, @@ -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 5de1fc5c3e9..8eb3ec521fa 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/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 # diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 2be21fe261d..7cd6484d7ff 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5718,6 +5718,24 @@ 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; +# +# End of 10.3 tests +# # # MDEV-19263: Server crashes in mysql_handle_single_derived # upon 2nd execution of PS diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 97624925519..e260803a8d3 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -5109,6 +5109,45 @@ 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; + +--echo # +--echo # End of 10.3 tests +--echo # + --echo # --echo # MDEV-19263: Server crashes in mysql_handle_single_derived --echo # upon 2nd execution of PS diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 6d2f73cd3f9..e1bcc7463d5 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -20,13 +20,11 @@ select json_detailed(JSON_EXTRACT(trace, '$**.ranges')) from information_schema.optimizer_trace; json_detailed(JSON_EXTRACT(trace, '$**.ranges')) [ - [ "(1) <= (key1) <= (1)", "(2) <= (key1) <= (2)", "(3) <= (key1) <= (3)" ], - [ "(1) <= (key1) <= (1)", "(2) <= (key1) <= (2)", @@ -71,9 +69,7 @@ from information_schema.optimizer_trace); select left(@json, 500); left(@json, 500) [ - [ - { "index": "key1", "ranges": @@ -85,7 +81,7 @@ left(@json, 500) "(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)", "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", - " + "(1,1,8) <= (kp ## Repeat the above with low max_weight: set @tmp9750_weight=@@optimizer_max_sel_arg_weight; set optimizer_max_sel_arg_weight=20; @@ -102,9 +98,7 @@ set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); select left(@json, 500); left(@json, 500) [ - [ - { "index": "key1", "ranges": @@ -119,14 +113,13 @@ left(@json, 500) "(8) <= (kp1) <= (8)", "(9) <= (kp1) <= (9)", "(10) <= (kp1) <= (10)" - + ], + set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); select left(@json, 2500); left(@json, 2500) [ - [ - { "sel_arg_weight_heuristic": { @@ -136,7 +129,6 @@ left(@json, 2500) "key2_weight": 10 } }, - { "sel_arg_weight_heuristic": { @@ -146,7 +138,6 @@ left(@json, 2500) "key2_weight": 10 } }, - { "sel_arg_weight_heuristic": { @@ -173,9 +164,7 @@ from information_schema.optimizer_trace); select left(@json, 1500); left(@json, 1500) [ - [ - { "index": "key1", "ranges": @@ -209,7 +198,8 @@ left(@json, 1500) "(3,7) <= (kp1,kp2) <= (3,7)", "(3,8) <= (kp1,kp2) <= (3,8)", "(3,9) <= (kp1,kp2) <= (3,9)", - "(3,10) <= (kp1,kp2 + "(3,10) <= (kp1,kp2) <= (3,10)", + set optimizer_max_sel_arg_weight= @tmp9750_weight; set optimizer_trace=@tmp_9750; drop table t1; diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result index abd777f3520..8af3e224c8a 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'; @@ -1145,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 27d03bd888c..cbc3c67bf64 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'; @@ -789,5 +785,22 @@ 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 # |