summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2023-01-13 10:45:41 +0200
committerMarko Mäkelä <marko.makela@mariadb.com>2023-01-13 10:45:41 +0200
commit3386b309756adba6b9633ad18c4e0575c7304cfe (patch)
tree094ff72f23d15731b16b2606494f69342052b194 /mysql-test
parent5aa58a0d39e802d8dc0fda2a974713ee69961596 (diff)
parent73ecab3d26c055928673a2629f4513aa7008dbf0 (diff)
downloadmariadb-git-3386b309756adba6b9633ad18c4e0575c7304cfe.tar.gz
Merge 10.5 into 10.6
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/cache_temporal_4265.result18
-rw-r--r--mysql-test/main/cache_temporal_4265.test18
-rw-r--r--mysql-test/main/create.result11
-rw-r--r--mysql-test/main/create.test13
-rw-r--r--mysql-test/main/default.result21
-rw-r--r--mysql-test/main/default.test14
-rw-r--r--mysql-test/main/func_group.result11
-rw-r--r--mysql-test/main/func_group.test12
-rw-r--r--mysql-test/main/func_json.result158
-rw-r--r--mysql-test/main/func_json.test101
-rw-r--r--mysql-test/main/insert_select.result13
-rw-r--r--mysql-test/main/insert_select.test17
-rw-r--r--mysql-test/main/join_nested.result52
-rw-r--r--mysql-test/main/join_nested.test38
-rw-r--r--mysql-test/main/join_nested_jcl6.result52
-rw-r--r--mysql-test/main/join_outer.result85
-rw-r--r--mysql-test/main/join_outer.test85
-rw-r--r--mysql-test/main/join_outer_jcl6.result85
-rw-r--r--mysql-test/main/olap.result11
-rw-r--r--mysql-test/main/olap.test4
-rw-r--r--mysql-test/main/opt_trace.result249
-rw-r--r--mysql-test/main/opt_trace_index_merge.result102
-rw-r--r--mysql-test/main/opt_trace_ucs2.result9
-rw-r--r--mysql-test/main/prepare.result16
-rw-r--r--mysql-test/main/prepare.test17
-rw-r--r--mysql-test/main/ps.result18
-rw-r--r--mysql-test/main/ps.test39
-rw-r--r--mysql-test/main/range.result11
-rw-r--r--mysql-test/main/range.test9
-rw-r--r--mysql-test/main/range_aria_dbt3.result16
-rw-r--r--mysql-test/main/range_aria_dbt3.test11
-rw-r--r--mysql-test/main/range_mrr_icp.result11
-rw-r--r--mysql-test/main/range_notembedded.result20
-rw-r--r--mysql-test/main/type_date.result19
-rw-r--r--mysql-test/main/type_date.test21
-rw-r--r--mysql-test/suite/binlog/r/binlog_verbose_compressed_fields.result15
-rw-r--r--mysql-test/suite/binlog/t/binlog_verbose_compressed_fields.test70
-rw-r--r--mysql-test/suite/galera/disabled.def1
-rw-r--r--mysql-test/suite/galera/r/galera_wsrep_new_cluster.result8
-rw-r--r--mysql-test/suite/galera/t/galera_wsrep_new_cluster.test28
-rw-r--r--mysql-test/suite/plugins/r/multiauth.result3
-rw-r--r--mysql-test/suite/versioning/r/foreign.result44
-rw-r--r--mysql-test/suite/versioning/t/foreign.test52
43 files changed, 1229 insertions, 379 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 12de9bca44e..9ab1db89029 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 0e3422ff1c3..335f7e7d607 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 4ceb6dc53fa..e17ed45180b 100644
--- a/mysql-test/main/func_group.test
+++ b/mysql-test/main/func_group.test
@@ -6,10 +6,6 @@ if (`SELECT $PS_PROTOCOL != 0`)
--skip Test temporarily disabled for ps-protocol
}
---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';
@@ -1808,5 +1804,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 9c5d66b7915..3e92ef272e4 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 ff8df5d7152..985a330108e 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 5f26b03e0d1..4db15f2f5a8 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 221a3118b8a..26081382368 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 c88d3e1202e..59c30da52ca 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 3ac9ad0f498..6a6032e5271 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -7499,17 +7499,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,
@@ -7523,8 +7519,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;
@@ -7533,17 +7528,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,
@@ -7557,8 +7548,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t0,t1;
@@ -7570,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": "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,
@@ -7594,8 +7580,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1,one_k;
@@ -7615,17 +7600,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,
@@ -7639,8 +7620,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table ten,t1;
@@ -7654,17 +7634,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,
@@ -7678,8 +7654,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;
@@ -7688,17 +7663,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,
@@ -7712,8 +7683,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1;
@@ -7730,17 +7700,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,
@@ -7754,8 +7720,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;
@@ -7765,17 +7730,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,
@@ -7789,8 +7750,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;
@@ -7800,17 +7760,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,
@@ -7824,8 +7780,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1;
@@ -7838,17 +7793,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,
@@ -7862,8 +7813,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1;
@@ -7879,17 +7829,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,
@@ -7903,8 +7849,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1;
@@ -7918,17 +7863,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,
@@ -7942,8 +7883,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1;
@@ -7974,17 +7914,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,
@@ -7998,8 +7934,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
drop table t1, t0, one_k;
@@ -8038,19 +7973,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,
@@ -8070,18 +8001,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,
@@ -8102,17 +8029,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,
@@ -8143,19 +8067,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,
@@ -8175,18 +8095,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",
@@ -8197,7 +8113,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 20.00585794,
"chosen": true
},
-
{
"access_type": "scan",
"resulting_rows": 804.6875,
@@ -8220,17 +8135,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,
@@ -8266,17 +8178,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,
@@ -8290,8 +8198,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
- [
- ]
+ []
}
]
DROP TABLE t1;
@@ -8313,7 +8220,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":
[
@@ -8334,15 +8240,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,
@@ -8368,19 +8270,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,
@@ -8400,18 +8298,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",
@@ -8422,7 +8316,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 20.00585794,
"chosen": true
},
-
{
"access_type": "scan",
"resulting_rows": 100,
@@ -8445,17 +8338,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,
@@ -8476,18 +8366,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",
@@ -8498,7 +8384,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"cost": 200.0585794,
"chosen": true
},
-
{
"access_type": "scan",
"resulting_rows": 10,
@@ -8542,24 +8427,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
}
]
@@ -8572,9 +8450,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":
@@ -8594,15 +8470,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
}
]
@@ -8620,15 +8492,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,
@@ -8812,7 +8680,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":
[
@@ -8838,19 +8705,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":
{
@@ -8859,7 +8723,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
"algorithm": "materialized"
}
},
-
{
"transformation":
{
@@ -8870,7 +8733,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
"possible": true
}
},
-
{
"transformation":
{
@@ -8880,7 +8742,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"
}
@@ -8897,7 +8758,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,
@@ -8911,7 +8771,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,
@@ -8983,12 +8842,10 @@ from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
[
-
{
"condition": "WHERE",
"resulting_condition": "t1.a = 3"
},
-
{
"condition": "HAVING",
"resulting_condition": "t1.a + t1.b < 10"
@@ -9010,12 +8867,10 @@ from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
[
-
{
"condition": "WHERE",
"resulting_condition": "t1.b > 5555"
},
-
{
"condition": "ON expr",
"attached_to": "t2",
@@ -9039,12 +8894,10 @@ from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
[
-
{
"condition": "WHERE",
"resulting_condition": "t1.b > 5555"
},
-
{
"condition": "ON expr",
"attached_to": "t2",
@@ -9059,13 +8912,11 @@ from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.condition_processing'))
[
-
{
"condition": "WHERE",
"original_condition": "t1.b > 5555",
"steps":
[
-
{
"build_equal_items":
{
@@ -9074,17 +8925,14 @@ json_detailed(json_extract(trace, '$**.condition_processing'))
"resulting_condition": "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)"
}
},
-
{
"transformation": "equality_propagation",
"resulting_condition": "t1.b > 5555"
},
-
{
"transformation": "constant_propagation",
"resulting_condition": "t1.b > 5555"
},
-
{
"transformation": "trivial_condition_removal",
"resulting_condition": "t1.b > 5555"
@@ -9134,23 +8982,18 @@ from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.choose_best_splitting'))
[
-
[
-
{
"considered_execution_plans":
[
-
{
"plan_prefix":
- [
- ],
+ [],
"table": "t2",
"best_access_path":
{
"considered_access_paths":
[
-
{
"access_type": "ref",
"index": "idx_a",
@@ -9160,7 +9003,6 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting'))
"cost": 2.000585794,
"chosen": true
},
-
{
"type": "scan",
"chosen": false,
@@ -9181,7 +9023,6 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting'))
}
]
},
-
{
"best_splitting":
{
@@ -9200,7 +9041,6 @@ from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.lateral_derived'))
[
-
{
"startup_cost": 9.955783677,
"splitting_cost": 2.488945919,
@@ -9228,7 +9068,6 @@ select json_detailed(json_extract(trace, '$**.best_join_order'))
from information_schema.OPTIMIZER_TRACE;
json_detailed(json_extract(trace, '$**.best_join_order'))
[
-
[
"t1",
"<subquery2>"
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 335e408bddd..1ef7565690e 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -311,17 +311,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,
@@ -329,13 +325,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,
@@ -344,13 +337,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,
@@ -364,7 +354,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
{
"intersecting_indexes":
[
-
{
"index": "key1",
"index_scan_cost": 10.31393703,
@@ -376,7 +365,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"intersect_covering_with_this_index": false,
"chosen": true
},
-
{
"index": "key2",
"index_scan_cost": 10.31393703,
@@ -388,7 +376,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"intersect_covering_with_this_index": false,
"chosen": true
},
-
{
"index": "key3",
"index_scan_cost": 10.31393703,
@@ -412,14 +399,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":
{
@@ -430,35 +415,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)"]
}
]
},
@@ -474,32 +450,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,
@@ -507,13 +476,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,
@@ -526,17 +492,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,
@@ -544,13 +506,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,
@@ -569,20 +528,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,
@@ -594,7 +549,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"intersect_covering_with_this_index": false,
"chosen": true
},
-
{
"index": "key2",
"index_scan_cost": 10.31393703,
@@ -618,20 +572,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,
@@ -643,7 +593,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
"intersect_covering_with_this_index": false,
"chosen": true
},
-
{
"index": "key4",
"index_scan_cost": 10.31393703,
@@ -678,14 +627,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,
@@ -694,29 +641,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,
@@ -725,25 +665,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 c95d8033734..9dc2c8dfded 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5712,6 +5712,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 7c9dc9dd37d..bb6ce7d4048 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -5110,6 +5110,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.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 50ca94430e4..c42670c1ed1 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2524,6 +2524,15 @@ 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;
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 #
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;
diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index ce82a1edd88..3b88851808b 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -17,6 +17,7 @@ galera_sst_encrypted : MDEV-29876 Galera test failure on galera_sst_encrypted
MW-284 : MDEV-29861 Galera test case hangs
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
galera_bf_abort_shutdown : MDEV-29918 Assertion failure on galera_bf_abort_shutdown
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';
diff --git a/mysql-test/suite/plugins/r/multiauth.result b/mysql-test/suite/plugins/r/multiauth.result
index 73241619008..d1ecd600d84 100644
--- a/mysql-test/suite/plugins/r/multiauth.result
+++ b/mysql-test/suite/plugins/r/multiauth.result
@@ -111,16 +111,13 @@ json_detailed(priv)
"authentication_string": "*7D8C3DF236D9163B6C274A9D47704BC496988460",
"auth_or":
[
-
{
"plugin": "ed25519",
"authentication_string": "F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc"
},
-
{
"plugin": "unix_socket"
},
-
{
}
],
diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result
index 8a779be760a..b17deba1c1e 100644
--- a/mysql-test/suite/versioning/r/foreign.result
+++ b/mysql-test/suite/versioning/r/foreign.result
@@ -496,11 +496,48 @@ 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;
+#
# MDEV-20729 Fix REFERENCES constraint in column definition
#
-create or replace table t1(
-id int
-);
+create table t1(id int);
# system fields can't be foreign keys:
create or replace table t2(
x int,
@@ -533,3 +570,4 @@ foreign key (sys_end) references t1(id)
) engine innodb with system versioning;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
drop table t1;
+# End of 10.5 tests
diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test
index 4aa6b21a039..c94cff540c6 100644
--- a/mysql-test/suite/versioning/t/foreign.test
+++ b/mysql-test/suite/versioning/t/foreign.test
@@ -530,11 +530,55 @@ delete from t1;
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;
+
+--echo #
--echo # MDEV-20729 Fix REFERENCES constraint in column definition
--echo #
-create or replace table t1(
- id int
-);
+create table t1(id int);
--echo # system fields can't be foreign keys:
--replace_result $sys_datatype_expl SYS_DATATYPE
--error ER_PARSE_ERROR,ER_PARSE_ERROR
@@ -572,4 +616,6 @@ eval create or replace table t2(
) engine innodb with system versioning;
drop table t1;
+--echo # End of 10.5 tests
+
--source suite/versioning/common_finish.inc