[Commits] 458e91054ea: MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
by psergey 07 Feb '22
by psergey 07 Feb '22
07 Feb '22
revision-id: 458e91054ea32fc62d5546e3655bb30eaeedb4b0 (mariadb-10.2.40-236-g458e91054ea)
parent(s): c1d7b4575e67bd0ef458457859cdf7de32b3d4f9
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-02-07 12:10:18 +0300
message:
MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
(Backport Varun Gupta's patch + edit the commit comment)
Name resolution code produced errors for valid queries with window
functions (but not for queries which used aggregate functions as
window functions).
Name resolution code worked incorrectly, because window function
objects had is_window_func_sum_expr()=false. This was so, because
mark_as_window_func_sum_expr() was only called for aggregate functions
used as window functions.
The fix is to call it for any window function.
---
mysql-test/r/win.result | 32 ++++++++++++++++++++++++++++++++
mysql-test/t/win.test | 27 +++++++++++++++++++++++++++
sql/item_windowfunc.cc | 3 +++
sql/sql_yacc.yy | 3 ---
4 files changed, 62 insertions(+), 3 deletions(-)
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index bc017ea70a3..30650f29555 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -4198,5 +4198,37 @@ drop procedure sp7;
drop view v1,v2;
drop table t1;
#
+# MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
+#
+CREATE TABLE t1(a VARCHAR(10), b int);
+INSERT INTO t1 VALUES
+('Maths', 60),('Maths', 60),
+('Maths', 70),('Maths', 55),
+('Biology', 60), ('Biology', 70);
+SET @save_sql_mode= @@sql_mode;
+SET sql_mode = 'ONLY_FULL_GROUP_BY';
+SELECT
+RANK() OVER (PARTITION BY a ORDER BY b) AS rank,
+a, b FROM t1 ORDER BY a, b DESC;
+rank a b
+2 Biology 70
+1 Biology 60
+4 Maths 70
+2 Maths 60
+2 Maths 60
+1 Maths 55
+SET sql_mode= @save_sql_mode;
+DROP TABLE t1;
+CREATE TABLE t1(i int,j int);
+INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4);
+INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4);
+SELECT i, LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) FROM t1 GROUP BY i;
+i LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j)
+1 6
+2 4
+3 2
+4 2
+DROP TABLE t1;
+#
# End of 10.2 tests
#
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 72e789dff3f..126ed735c88 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -2703,6 +2703,33 @@ drop procedure sp7;
drop view v1,v2;
drop table t1;
+--echo #
+--echo # MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
+--echo #
+
+CREATE TABLE t1(a VARCHAR(10), b int);
+
+INSERT INTO t1 VALUES
+('Maths', 60),('Maths', 60),
+('Maths', 70),('Maths', 55),
+('Biology', 60), ('Biology', 70);
+
+SET @save_sql_mode= @@sql_mode;
+SET sql_mode = 'ONLY_FULL_GROUP_BY';
+
+SELECT
+ RANK() OVER (PARTITION BY a ORDER BY b) AS rank,
+ a, b FROM t1 ORDER BY a, b DESC;
+
+SET sql_mode= @save_sql_mode;
+DROP TABLE t1;
+
+CREATE TABLE t1(i int,j int);
+INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4);
+INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4);
+SELECT i, LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) FROM t1 GROUP BY i;
+DROP TABLE t1;
+
--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc
index bb4a8a9f3af..03f99540771 100644
--- a/sql/item_windowfunc.cc
+++ b/sql/item_windowfunc.cc
@@ -93,6 +93,9 @@ Item_window_func::fix_fields(THD *thd, Item **ref)
my_error(ER_NO_ORDER_LIST_IN_WINDOW_SPEC, MYF(0), window_func()->func_name());
return true;
}
+
+ window_func()->mark_as_window_func_sum_expr();
+
/*
TODO: why the last parameter is 'ref' in this call? What if window_func
decides to substitute itself for something else and does *ref=.... ?
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a4b105862f3..6f3274aced5 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -10557,9 +10557,6 @@ window_func:
simple_window_func
|
sum_expr
- {
- ((Item_sum *) $1)->mark_as_window_func_sum_expr();
- }
;
simple_window_func:
1
0
revision-id: 57145997436a8c4f509a40b351fccf8806c13c5f (mariadb-10.6.5-85-g5714599)
parent(s): 9853e407e8e5aa512b7cd3913ac01417f0bfb41c
author: Igor Babaev
committer: Igor Babaev
timestamp: 2022-02-02 14:31:21 -0800
message:
Stabilizing the test case for MDEV-22846.
---
mysql-test/main/rowid_filter_innodb.result | 3 +++
mysql-test/main/rowid_filter_innodb.test | 4 ++++
2 files changed, 7 insertions(+)
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index eab6d12..49e74d1 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2974,6 +2974,8 @@ set global innodb_stats_persistent= @stats.save;
#
# MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter
#
+set @stats.save= @@innodb_stats_persistent;
+set global innodb_stats_persistent=0;
CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb;
INSERT INTO t1 VALUES
(1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'),
@@ -3012,4 +3014,5 @@ Warnings:
Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t1` `a1` where `test`.`t2`.`i1` = `test`.`t1`.`pk` and `test`.`t2`.`i1` is not null and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`))))
DROP TABLE t1,t2;
+set global innodb_stats_persistent= @stats.save;
# End of 10.4 tests
diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test
index c878eb8..97b9aa0 100644
--- a/mysql-test/main/rowid_filter_innodb.test
+++ b/mysql-test/main/rowid_filter_innodb.test
@@ -545,6 +545,8 @@ set global innodb_stats_persistent= @stats.save;
--echo # MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter
--echo #
+set @stats.save= @@innodb_stats_persistent;
+set global innodb_stats_persistent=0;
CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb;
INSERT INTO t1 VALUES
@@ -569,4 +571,6 @@ eval EXPLAIN EXTENDED $q;
DROP TABLE t1,t2;
+set global innodb_stats_persistent= @stats.save;
+
--echo # End of 10.4 tests
1
0
[Commits] 21f4d8674a6: MDEV-22377: Subquery in an UPDATE query uses full scan instead of range
by psergey 31 Jan '22
by psergey 31 Jan '22
31 Jan '22
revision-id: 21f4d8674a68728874a20690b564c5f91eeee19b (mariadb-10.4.22-82-g21f4d8674a6)
parent(s): b915f79e4e004fde4f6ac8f341afee980e11792b
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-31 12:27:02 +0300
message:
MDEV-22377: Subquery in an UPDATE query uses full scan instead of range
Item_in_subselect::inject_in_to_exists_cond injects the IN-equality
into the subquery's WHERE. A SELECT's WHERE clause has certain invariants
which must hold in order for other optimization to work. However,
inject_in_to_exists_cond breaks them which disables further optimizations.
Details: The invariant is: If the subquery is an Item_cond_and and the WHERE
clause has multiple equalities, then
1. The end of Item_cond_and's argument is the list of WHERE's multiple
equalities.
2. join->conds.m_cond_equal should describe WHERE's multiple equalities.
The code in Item_in_subselect::inject_in_to_exists_cond maintained #1 but
failed to maintain #2.
The patch makes the function to use and_new_conditions_to_optimized_cond()
which properly maintains all the invariants.
Note that and_new_conditions_to_optimized_cond() can infer that the resulting
condition is always false. The patch adds handing for this case.
---
mysql-test/main/subselect.result | 4 +-
mysql-test/main/subselect2.result | 2 +-
mysql-test/main/subselect3.result | 4 +-
mysql-test/main/subselect3_jcl6.result | 4 +-
mysql-test/main/subselect4.result | 4 +-
mysql-test/main/subselect_extra_no_semijoin.result | 2 +-
mysql-test/main/subselect_innodb.result | 54 ++++++++++++++++++++++
mysql-test/main/subselect_innodb.test | 53 +++++++++++++++++++++
mysql-test/main/subselect_mat_cost_bugs.result | 4 +-
mysql-test/main/subselect_no_exists_to_in.result | 4 +-
mysql-test/main/subselect_no_mat.result | 4 +-
mysql-test/main/subselect_no_opts.result | 4 +-
mysql-test/main/subselect_no_scache.result | 4 +-
mysql-test/main/subselect_no_semijoin.result | 4 +-
mysql-test/main/subselect_sj.result | 2 +-
mysql-test/main/subselect_sj2_mat.result | 4 +-
mysql-test/main/subselect_sj_jcl6.result | 2 +-
sql/item_subselect.cc | 49 +++++++-------------
18 files changed, 149 insertions(+), 59 deletions(-)
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index 15ebaa33474..ca33ca6404e 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -1327,7 +1327,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1337,7 +1337,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
index a3d7fda7abc..c54d635230f 100644
--- a/mysql-test/main/subselect2.result
+++ b/mysql-test/main/subselect2.result
@@ -262,7 +262,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2c ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) and `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` having trigcond(`test`.`t2a`.`c2` is null))))
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` and (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) having trigcond(`test`.`t2a`.`c2` is null))))
DROP TABLE t1,t2;
#
# MDEV-614, also MDEV-536, also LP:1050806:
diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result
index 5c4544a1b05..0034f61ac23 100644
--- a/mysql-test/main/subselect3.result
+++ b/mysql-test/main/subselect3.result
@@ -169,7 +169,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3`
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
insert into t1 values
@@ -197,7 +197,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3`
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
drop table t1,t2,t3;
create table t1 (oref int, grp int);
insert into t1 (oref, grp) values
diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result
index 4260676cc37..b7b18bf80e0 100644
--- a/mysql-test/main/subselect3_jcl6.result
+++ b/mysql-test/main/subselect3_jcl6.result
@@ -172,7 +172,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3`
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
insert into t1 values
@@ -200,7 +200,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3`
+Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
drop table t1,t2,t3;
create table t1 (oref int, grp int);
insert into t1 (oref, grp) values
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index e4e9a5c8917..8b35131b9b3 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -1741,7 +1741,7 @@ SET @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);
a1 a2
set @@optimizer_switch=@save_optimizer_switch;
@@ -2349,7 +2349,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`b` or `test`.`t1`.`b` is null) and `test`.`t1`.`b` = `test`.`t1`.`a` having trigcond(`test`.`t1`.`b` is null))))
+Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where `test`.`t1`.`b` = `test`.`t1`.`a` and (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`b` is null))))
SELECT * FROM t2
WHERE f NOT IN (SELECT b FROM t1
WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result
index faeaf75c590..9507670d6bf 100644
--- a/mysql-test/main/subselect_extra_no_semijoin.result
+++ b/mysql-test/main/subselect_extra_no_semijoin.result
@@ -454,7 +454,7 @@ WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
-2 DEPENDENT SUBQUERY <derived3> ref key1 key1 8 const,const 0 Using where
+2 DEPENDENT SUBQUERY <derived3> ref key1 key1 8 const,const 0
3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where
SELECT * FROM t3
WHERE t3.b IN (SELECT v1.b FROM v1, t2
diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result
index f1e07ce09fe..8ab5e662fbd 100644
--- a/mysql-test/main/subselect_innodb.result
+++ b/mysql-test/main/subselect_innodb.result
@@ -663,4 +663,58 @@ a b
execute stmt;
a b
drop table t1,t2;
+#
+# MDEV-22377: Subquery in an UPDATE query uses full scan instead of range
+#
+CREATE TABLE t1 (
+key1 varchar(30) NOT NULL,
+col1 int(11) NOT NULL,
+filler char(100)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+insert into t1
+select
+seq, seq, seq
+from seq_1_to_100000;
+CREATE TABLE t10 (
+key1 varchar(30) NOT NULL,
+col1 int,
+filler char(100),
+PRIMARY KEY (key1)
+) ENGINE=InnoDB CHARSET=utf8;
+insert into t10
+select
+seq, seq, seq from seq_1_to_100000;
+CREATE TABLE t11 (
+key1 varchar(30) NOT NULL,
+filler char(100),
+PRIMARY KEY (key1)
+) ENGINE=InnoDB CHARSET=utf8;
+insert into t11
+select
+seq, seq from seq_1_to_100000;
+explain
+select * from t1 hist
+where
+hist.col1 NOT IN (SELECT tn.col1
+FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1
+WHERE
+tn.key1 IN ('1','2','3','4','5','6','7','8','9','10')
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY hist ALL NULL NULL NULL NULL 100112 Using where
+2 MATERIALIZED tms range PRIMARY PRIMARY 92 NULL 10 Using where; Using index
+2 MATERIALIZED tn eq_ref PRIMARY PRIMARY 92 test.tms.key1 1
+explain update t1 hist
+set filler='aaa'
+WHERE
+key1 IN ('1','2','3','4','5','6','7','8','9','10') AND
+hist.col1 NOT IN (SELECT tn.col1
+FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1
+WHERE tn.key1 IN ('1','2','3','4','5','6','7','8','9','10')
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY hist ALL NULL NULL NULL NULL 100112 Using where
+2 DEPENDENT SUBQUERY tms range PRIMARY PRIMARY 92 NULL 10 Using where; Using index
+2 DEPENDENT SUBQUERY tn eq_ref PRIMARY PRIMARY 92 test.tms.key1 1 Using where
+drop table t1, t10, t11;
# End of 10.4 tests
diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test
index 37f8f40200e..d175fd87234 100644
--- a/mysql-test/main/subselect_innodb.test
+++ b/mysql-test/main/subselect_innodb.test
@@ -661,4 +661,57 @@ execute stmt;
drop table t1,t2;
+--echo #
+--echo # MDEV-22377: Subquery in an UPDATE query uses full scan instead of range
+--echo #
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (
+ key1 varchar(30) NOT NULL,
+ col1 int(11) NOT NULL,
+ filler char(100)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+insert into t1
+select
+ seq, seq, seq
+from seq_1_to_100000;
+
+CREATE TABLE t10 (
+ key1 varchar(30) NOT NULL,
+ col1 int,
+ filler char(100),
+ PRIMARY KEY (key1)
+) ENGINE=InnoDB CHARSET=utf8;
+
+insert into t10
+select
+ seq, seq, seq from seq_1_to_100000;
+CREATE TABLE t11 (
+ key1 varchar(30) NOT NULL,
+ filler char(100),
+ PRIMARY KEY (key1)
+) ENGINE=InnoDB CHARSET=utf8;
+
+insert into t11
+select
+ seq, seq from seq_1_to_100000;
+explain
+select * from t1 hist
+where
+ hist.col1 NOT IN (SELECT tn.col1
+ FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1
+ WHERE
+ tn.key1 IN ('1','2','3','4','5','6','7','8','9','10')
+ );
+explain update t1 hist
+set filler='aaa'
+WHERE
+ key1 IN ('1','2','3','4','5','6','7','8','9','10') AND
+ hist.col1 NOT IN (SELECT tn.col1
+ FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1
+ WHERE tn.key1 IN ('1','2','3','4','5','6','7','8','9','10')
+ );
+drop table t1, t10, t11;
+
--echo # End of 10.4 tests
diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result
index ecceac27b2d..0ccac8d3dd5 100644
--- a/mysql-test/main/subselect_mat_cost_bugs.result
+++ b/mysql-test/main/subselect_mat_cost_bugs.result
@@ -100,7 +100,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1` and `test`.`t2`.`c3` = `test`.`t1b`.`c4`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t2`.`c3` = `test`.`t1b`.`c4` and `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`)))
SELECT pk
FROM t1
WHERE c1 IN
@@ -364,7 +364,7 @@ AND a = SOME (SELECT b FROM t5));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t5 index c c 10 NULL 2 Using where; Using index; Start temporary
-2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using index condition; Using where; End temporary
+2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using where; End temporary
SELECT *
FROM t3
WHERE t3.b > ALL (
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index b1432cf0979..abf53854c55 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -1331,7 +1331,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1341,7 +1341,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index da60f98bf00..345a70169af 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -1334,7 +1334,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1344,7 +1344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index fb99e237a1c..21078bd5959 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -1330,7 +1330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1340,7 +1340,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 4b910009248..752556fdff3 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -1333,7 +1333,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1343,7 +1343,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index f2230718754..0031a1376fa 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -1330,7 +1330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1340,7 +1340,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index f3519e1d846..29e2d01215e 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -3212,7 +3212,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1
+Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where `test`.`t4`.`c4` = 1)))) where 1
# mdev-12820
SELECT *
FROM t1
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result
index e2c9adbfc37..72ddb59fe47 100644
--- a/mysql-test/main/subselect_sj2_mat.result
+++ b/mysql-test/main/subselect_sj2_mat.result
@@ -1646,10 +1646,10 @@ EXPLAIN EXTENDED
SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
2 DEPENDENT SUBQUERY t3 hash_ALL NULL #hash#$hj 5 func 3 100.00 Using where; Using join buffer (flat, BNLH join)
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where <expr_cache><`test`.`t1`.`i1`>(<in_optimizer>(`test`.`t1`.`i1`,<exists>(/* select#2 */ select `test`.`t3`.`i3` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and <cache>(`test`.`t1`.`i1`) = `test`.`t3`.`i3`)))
+Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where <expr_cache><`test`.`t1`.`i1`>(<in_optimizer>(`test`.`t1`.`i1`,<exists>(/* select#2 */ select `test`.`t3`.`i3` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and <cache>(`test`.`t1`.`i1`) = `test`.`t2`.`i2`)))
SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2);
i1
7
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index 02e9a833db6..8874d85681f 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -3223,7 +3223,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1
+Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where `test`.`t4`.`c4` = 1)))) where 1
# mdev-12820
SELECT *
FROM t1
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 56ab0f648ee..c341655a499 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -2777,43 +2777,26 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg)
if (where_item)
{
- List<Item> *and_args= NULL;
- /*
- If the top-level Item of the WHERE clause is an AND, detach the multiple
- equality list that was attached to the end of the AND argument list by
- build_equal_items_for_cond(). The multiple equalities must be detached
- because fix_fields merges lower level AND arguments into the upper AND.
- As a result, the arguments from lower-level ANDs are concatenated after
- the multiple equalities. When the multiple equality list is treated as
- such, it turns out that it contains non-Item_equal object which is wrong.
- */
- if (join_arg->conds && join_arg->conds->type() == Item::COND_ITEM &&
- ((Item_cond*) join_arg->conds)->functype() == Item_func::COND_AND_FUNC)
- {
- and_args= ((Item_cond*) join_arg->conds)->argument_list();
- if (join_arg->cond_equal)
- and_args->disjoin((List<Item> *) &join_arg->cond_equal->current_level);
- }
+ List<Item> list1;
+ list1.push_back(where_item);
+ where_item= and_new_conditions_to_optimized_cond(thd, join_arg->conds,
+ &join_arg->cond_equal,
+ list1,
+ &join_arg->cond_value);
- where_item= and_items(thd, join_arg->conds, where_item);
- if (where_item->fix_fields_if_needed(thd, 0))
- DBUG_RETURN(true);
- // TIMOUR TODO: call optimize_cond() for the new where clause
thd->change_item_tree(&select_lex->where, where_item);
- select_lex->where->top_level_item();
- join_arg->conds= select_lex->where;
+ if (where_item)
+ {
+ select_lex->where->top_level_item();
+ join_arg->conds= select_lex->where;
+ }
+ else
+ join_arg->conds= NULL;
- /* Attach back the list of multiple equalities to the new top-level AND. */
- if (and_args && join_arg->cond_equal)
+ if (join_arg->cond_value == Item::COND_FALSE)
{
- /* The argument list of the top-level AND may change after fix fields. */
- and_args= ((Item_cond*) join_arg->conds)->argument_list();
- List_iterator<Item_equal> li(join_arg->cond_equal->current_level);
- Item_equal *elem;
- while ((elem= li++))
- {
- and_args->push_back(elem, thd->mem_root);
- }
+ join_arg->zero_result_cause= "Impossible WHERE noticed after reading const tables";
+ join_arg->conds= new (thd->mem_root) Item_int(thd, 0);
}
}
1
0
revision-id: 9149fcb8d504d7c1b027024df63c8120a65a7751 (mariadb-10.4.22-82-g9149fcb8d50)
parent(s): b915f79e4e004fde4f6ac8f341afee980e11792b
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-27 22:58:11 +0300
message:
Fix Item_func::is_simplifed_cond_processor
The comment for that function says:
TRUE if the function is knowingly TRUE or FALSE.
I don't get why one would need to check for "!val_int()" in this case.
---
mysql-test/main/having_cond_pushdown.result | 6 ------
sql/item_func.h | 2 +-
2 files changed, 1 insertion(+), 7 deletions(-)
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index 9b124296e3d..aade383a71f 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -3194,7 +3194,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3536,7 +3535,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1 and 1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3663,7 +3661,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3791,7 +3788,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"filesort": {
"sort_key": "t1.c",
"temporary_table": {
@@ -3859,7 +3855,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1 and 1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3977,7 +3972,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"table": {
"table_name": "t3",
"access_type": "ALL",
diff --git a/sql/item_func.h b/sql/item_func.h
index b368e5872fe..2b02971d940 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -399,7 +399,7 @@ class Item_func :public Item_func_or_sum,
With_sum_func_cache* get_with_sum_func_cache() { return this; }
Item_func *get_item_func() { return this; }
bool is_simplified_cond_processor(void *arg)
- { return const_item() && !val_int(); }
+ { return const_item() /*&& !val_int();*/; }
};
1
0
revision-id: 768b5f70b59366925b2b4ff22d1d5afcdd8ab6ee (mariadb-10.4.22-82-g768b5f70b59)
parent(s): b915f79e4e004fde4f6ac8f341afee980e11792b
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-27 22:55:16 +0300
message:
Fix Item_func::is_simplifed_cond_processor
The comment for that function says:
TRUE if the function is knowingly TRUE or FALSE.
I don't get why one would need to check for "!val_int()" in this case.
---
mysql-test/main/having_cond_pushdown.result | 6 ------
sql/item_func.h | 2 +-
2 files changed, 1 insertion(+), 7 deletions(-)
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index 9b124296e3d..aade383a71f 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -3194,7 +3194,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3536,7 +3535,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1 and 1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3663,7 +3661,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3791,7 +3788,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"filesort": {
"sort_key": "t1.c",
"temporary_table": {
@@ -3859,7 +3855,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1 and 1",
"table": {
"table_name": "t1",
"access_type": "ALL",
@@ -3977,7 +3972,6 @@ EXPLAIN
{
"query_block": {
"select_id": 1,
- "const_condition": "1",
"table": {
"table_name": "t3",
"access_type": "ALL",
diff --git a/sql/item_func.h b/sql/item_func.h
index b368e5872fe..2b02971d940 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -399,7 +399,7 @@ class Item_func :public Item_func_or_sum,
With_sum_func_cache* get_with_sum_func_cache() { return this; }
Item_func *get_item_func() { return this; }
bool is_simplified_cond_processor(void *arg)
- { return const_item() && !val_int(); }
+ { return const_item() /*&& !val_int();*/; }
};
1
0
[Commits] 628d4c7: MDEV-27510 Query returns wrong result when using split optimization
by IgorBabaev 25 Jan '22
by IgorBabaev 25 Jan '22
25 Jan '22
revision-id: 628d4c76c3f163732b5cb7fa5dd6ef7c9e25866c (mariadb-10.3.26-316-g628d4c7)
parent(s): 97425f740faf83ac2d16585084476470f3f898ce
author: Igor Babaev
committer: Igor Babaev
timestamp: 2022-01-24 23:14:46 -0800
message:
MDEV-27510 Query returns wrong result when using split optimization
This bug may affect the queries that uses a grouping derived table with
grouping list containing references to columns from different tables if
the optimizer decides to employ the split optimization for the derived
table. In some very specific cases it may affect queries with a grouping
derived table that refers only one base table.
This bug was caused by an improper fix for the bug MDEV-25128. The fix
tried to get rid of the equality conditions pushed into the where clause
of the grouping derived table T to which the split optimization had been
applied. The fix erroneously assumed that only those pushed equalities
that were used for ref access of the tables referenced by T were needed.
In fact the function remove_const() that figures out what columns from the
group list can be removed if the split optimization is applied can uses
other pushed equalities as well.
This patch actually provides a proper fix for MDEV-25128. Rather than
trying to remove invalid pushed equalities referencing the fields of SJM
tables with a look-up access the patch attempts not to push such equalities.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/derived_cond_pushdown.result | 439 ++++++++++++++++++++++++++-
mysql-test/main/derived_cond_pushdown.test | 142 +++++++++
sql/opt_split.cc | 64 +++-
sql/sql_select.cc | 18 +-
sql/sql_select.h | 1 +
5 files changed, 635 insertions(+), 29 deletions(-)
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 7631348..e5cf14f 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -17439,7 +17439,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1
1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3
-2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using index
+2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using where; Using index
explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3
where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2);
EXPLAIN
@@ -17512,6 +17512,7 @@ EXPLAIN
"ref": ["test.t1.a"],
"rows": 1,
"filtered": 100,
+ "attached_condition": "cp2.a = t3.`id`",
"using_index": true
}
}
@@ -17683,7 +17684,7 @@ EXPLAIN
"ref": ["test.t1.id"],
"rows": 3,
"filtered": 100,
- "index_condition": "t2.t1_id between 200 and 100000",
+ "index_condition": "t2.t1_id between 200 and 100000 and t2.t1_id = t3.t1_id",
"attached_condition": "t2.reporting_person = 1"
}
}
@@ -17702,4 +17703,438 @@ WHERE t1.id BETWEEN 200 AND 100000;
id
set optimizer_switch='split_materialized=default';
DROP TABLE t1,t2,t3;
+#
+# MDEV-27510: Splittable derived with grouping over two tables
+#
+CREATE TABLE ledgers (
+id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+name VARCHAR(32)
+) ENGINE=MyISAM;
+CREATE TABLE charges (
+id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+from_ledger_id BIGINT UNSIGNED NOT NULL,
+to_ledger_id BIGINT UNSIGNED NOT NULL,
+amount INT NOT NULL,
+KEY fk_charge_from_ledger (from_ledger_id),
+KEY fk_charge_to_ledger (to_ledger_id)
+) ENGINE=MyISAM;
+CREATE TABLE transactions (
+id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ledger_id BIGINT UNSIGNED NOT NULL,
+KEY fk_transactions_ledger (ledger_id)
+) ENGINE=MyISAM;
+CREATE TABLE transaction_items (
+id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+transaction_id BIGINT UNSIGNED NOT NULL,
+charge_id BIGINT UNSIGNED,
+amount INT NOT NULL,
+KEY fk_items_transaction (transaction_id),
+KEY fk_items_charge (charge_id)
+) ENGINE=MyISAM;
+INSERT INTO ledgers (id, name) VALUES
+(1, 'Anna'), (2, 'John'), (3, 'Fred');
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(1, 2, 1, 200), (2, 1, 2, 330), (3, 1, 2, 640), (4, 3, 1, 640), (5, 3, 2, 1000),
+(6, 3, 1, 660), (7, 2, 3, 650), (8, 3, 2, 160), (9, 2, 1, 740), (10, 3, 2, 310),
+(11, 2, 1, 640), (12, 3, 2, 240), (13, 3, 2, 340), (14, 2, 1, 720),
+(15, 2, 3, 100),
+(16, 2, 3, 980), (17, 2, 1, 80), (18, 1, 2, 760), (19, 2, 3, 740),
+(20, 2, 1, 990);
+INSERT INTO transactions (id, ledger_id) VALUES
+(2, 1), (3, 1), (5, 1), (8, 1), (12, 1), (18, 1), (22, 1), (28, 1),
+(34, 1), (35, 1),
+(40, 1), (1, 2), (4, 2), (6, 2), (10, 2), (13, 2), (16, 2), (17, 2),
+(20, 2), (21, 2),
+(24, 2), (26, 2), (27, 2), (29, 2), (31, 2), (33, 2), (36, 2), (37, 2),
+(39, 2), (7, 3),
+(9, 3), (11, 3), (14, 3), (15, 3), (19, 3), (23, 3), (25, 3), (30, 3),
+(32, 3), (38, 3);
+INSERT INTO transaction_items (id, transaction_id, charge_id, amount) VALUES
+(1, 1, 1, -200), (2, 2, 1, 200), (3, 3, 2, -330), (4, 4, 2, 330),
+(5, 5, 3, -640),
+(6, 6, 3, 640), (7, 7, 4, -640), (8, 8, 4, 640), (9, 9, 5, -1000),
+(10, 10, 5, 1000),
+(11, 11, 6, -660), (12, 12, 6, 660), (13, 13, 7, -650), (14, 14, 7, 650),
+(15, 15, 8, -160),
+(16, 16, 8, 160), (17, 17, 9, -740), (18, 18, 9, 740), (19, 19, 10, -310),
+(20, 20, 10, 310),
+(21, 21, 11, -640), (22, 22, 11, 640), (23, 23, 12, -240), (24, 24, 12, 240),
+(25, 25, 13, -340),
+(26, 26, 13, 340), (27, 27, 14, -720), (28, 28, 14, 720), (29, 29, 15, -100),
+(30, 30, 15, 100),
+(31, 31, 16, -980), (32, 32, 16, 980), (33, 33, 17, -80), (34, 34, 17, 80),
+(35, 35, 18, -760),
+(36, 36, 18, 760), (37, 37, 19, -740), (38, 38, 19, 740), (39, 39, 20, -990),
+(40, 40, 20, 990);
+ANALYZE TABLE ledgers, charges, transactions, transaction_items;
+Table Op Msg_type Msg_text
+test.ledgers analyze status OK
+test.charges analyze status OK
+test.transactions analyze status OK
+test.transaction_items analyze status OK
+set optimizer_switch='split_materialized=on';
+SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id from_ledger_id to_ledger_id from_num_rows
+2 1 2 1
+3 1 2 1
+5 3 2 1
+8 3 2 1
+10 3 2 1
+12 3 2 1
+13 3 2 1
+18 1 2 1
+EXPLAIN SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY charges ref PRIMARY,fk_charge_from_ledger,fk_charge_to_ledger fk_charge_to_ledger 8 const 7
+1 PRIMARY <derived2> ref key0 key0 17 test.charges.from_ledger_id,test.charges.id 2
+2 LATERAL DERIVED transaction_items ref fk_items_transaction,fk_items_charge fk_items_charge 9 test.charges.id 2
+2 LATERAL DERIVED transactions eq_ref PRIMARY,fk_transactions_ledger PRIMARY 8 test.transaction_items.transaction_id 1 Using where
+EXPLAIN FORMAT=JSON SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "charges",
+ "access_type": "ref",
+ "possible_keys": ["PRIMARY", "fk_charge_from_ledger", "fk_charge_to_ledger"],
+ "key": "fk_charge_to_ledger",
+ "key_length": "8",
+ "used_key_parts": ["to_ledger_id"],
+ "ref": ["const"],
+ "rows": 7,
+ "filtered": 100
+ },
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "17",
+ "used_key_parts": ["ledger_id", "charge_id"],
+ "ref": ["test.charges.from_ledger_id", "test.charges.id"],
+ "rows": 2,
+ "filtered": 100,
+ "materialized": {
+ "lateral": 1,
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "transaction_items",
+ "access_type": "ref",
+ "possible_keys": ["fk_items_transaction", "fk_items_charge"],
+ "key": "fk_items_charge",
+ "key_length": "9",
+ "used_key_parts": ["charge_id"],
+ "ref": ["test.charges.id"],
+ "rows": 2,
+ "filtered": 100
+ },
+ "table": {
+ "table_name": "transactions",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "fk_transactions_ledger"],
+ "key": "PRIMARY",
+ "key_length": "8",
+ "used_key_parts": ["id"],
+ "ref": ["test.transaction_items.transaction_id"],
+ "rows": 1,
+ "filtered": 100,
+ "attached_condition": "transactions.ledger_id = charges.from_ledger_id"
+ }
+ }
+ }
+ }
+ }
+}
+set optimizer_switch='split_materialized=off';
+SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id from_ledger_id to_ledger_id from_num_rows
+2 1 2 1
+3 1 2 1
+5 3 2 1
+8 3 2 1
+10 3 2 1
+12 3 2 1
+13 3 2 1
+18 1 2 1
+EXPLAIN SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY charges ref PRIMARY,fk_charge_from_ledger,fk_charge_to_ledger fk_charge_to_ledger 8 const 7
+1 PRIMARY <derived2> ref key0 key0 17 test.charges.from_ledger_id,test.charges.id 4
+2 DERIVED transaction_items ALL fk_items_transaction NULL NULL NULL 40 Using temporary; Using filesort
+2 DERIVED transactions eq_ref PRIMARY PRIMARY 8 test.transaction_items.transaction_id 1
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(101, 4, 2, 100), (102, 7, 2, 200);
+set optimizer_switch='split_materialized=on';
+SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id from_ledger_id to_ledger_id from_num_rows
+2 1 2 1
+3 1 2 1
+5 3 2 1
+8 3 2 1
+10 3 2 1
+12 3 2 1
+13 3 2 1
+18 1 2 1
+101 4 2 NULL
+102 7 2 NULL
+EXPLAIN SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY charges ref fk_charge_to_ledger fk_charge_to_ledger 8 const 9
+1 PRIMARY <derived2> ref key0 key0 18 test.charges.from_ledger_id,test.charges.id 2
+2 LATERAL DERIVED transaction_items ref fk_items_transaction,fk_items_charge fk_items_charge 9 test.charges.id 2
+2 LATERAL DERIVED transactions eq_ref PRIMARY,fk_transactions_ledger PRIMARY 8 test.transaction_items.transaction_id 1 Using where
+EXPLAIN FORMAT=JSON SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "charges",
+ "access_type": "ref",
+ "possible_keys": ["fk_charge_to_ledger"],
+ "key": "fk_charge_to_ledger",
+ "key_length": "8",
+ "used_key_parts": ["to_ledger_id"],
+ "ref": ["const"],
+ "rows": 9,
+ "filtered": 100
+ },
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "18",
+ "used_key_parts": ["ledger_id", "charge_id"],
+ "ref": ["test.charges.from_ledger_id", "test.charges.id"],
+ "rows": 2,
+ "filtered": 100,
+ "materialized": {
+ "lateral": 1,
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "transaction_items",
+ "access_type": "ref",
+ "possible_keys": ["fk_items_transaction", "fk_items_charge"],
+ "key": "fk_items_charge",
+ "key_length": "9",
+ "used_key_parts": ["charge_id"],
+ "ref": ["test.charges.id"],
+ "rows": 2,
+ "filtered": 100
+ },
+ "table": {
+ "table_name": "transactions",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "fk_transactions_ledger"],
+ "key": "PRIMARY",
+ "key_length": "8",
+ "used_key_parts": ["id"],
+ "ref": ["test.transaction_items.transaction_id"],
+ "rows": 1,
+ "filtered": 100,
+ "attached_condition": "transactions.ledger_id = charges.from_ledger_id"
+ }
+ }
+ }
+ }
+ }
+}
+set optimizer_switch='split_materialized=off';
+SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id from_ledger_id to_ledger_id from_num_rows
+2 1 2 1
+3 1 2 1
+5 3 2 1
+8 3 2 1
+10 3 2 1
+12 3 2 1
+13 3 2 1
+18 1 2 1
+101 4 2 NULL
+102 7 2 NULL
+EXPLAIN SELECT
+charges.id,
+charges.from_ledger_id,
+charges.to_ledger_id,
+from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+SELECT
+transactions.ledger_id,
+transaction_items.charge_id,
+count(*) as num_rows
+FROM transaction_items
+INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY charges ref fk_charge_to_ledger fk_charge_to_ledger 8 const 9
+1 PRIMARY <derived2> ref key0 key0 18 test.charges.from_ledger_id,test.charges.id 4
+2 DERIVED transaction_items ALL fk_items_transaction NULL NULL NULL 40 Using temporary; Using filesort
+2 DERIVED transactions eq_ref PRIMARY PRIMARY 8 test.transaction_items.transaction_id 1
+set optimizer_switch='split_materialized=default';
+DROP TABLE transaction_items;
+DROP TABLE transactions;
+DROP TABLE charges;
+DROP TABLE ledgers;
# End of 10.3 tests
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 4f4ffc9..619d104 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3711,4 +3711,146 @@ set optimizer_switch='split_materialized=default';
DROP TABLE t1,t2,t3;
+--echo #
+--echo # MDEV-27510: Splittable derived with grouping over two tables
+--echo #
+
+CREATE TABLE ledgers (
+ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ name VARCHAR(32)
+) ENGINE=MyISAM;
+
+CREATE TABLE charges (
+ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ from_ledger_id BIGINT UNSIGNED NOT NULL,
+ to_ledger_id BIGINT UNSIGNED NOT NULL,
+ amount INT NOT NULL,
+ KEY fk_charge_from_ledger (from_ledger_id),
+ KEY fk_charge_to_ledger (to_ledger_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE transactions (
+ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ ledger_id BIGINT UNSIGNED NOT NULL,
+ KEY fk_transactions_ledger (ledger_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE transaction_items (
+ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ transaction_id BIGINT UNSIGNED NOT NULL,
+ charge_id BIGINT UNSIGNED,
+ amount INT NOT NULL,
+ KEY fk_items_transaction (transaction_id),
+ KEY fk_items_charge (charge_id)
+) ENGINE=MyISAM;
+
+INSERT INTO ledgers (id, name) VALUES
+(1, 'Anna'), (2, 'John'), (3, 'Fred');
+
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(1, 2, 1, 200), (2, 1, 2, 330), (3, 1, 2, 640), (4, 3, 1, 640), (5, 3, 2, 1000),
+(6, 3, 1, 660), (7, 2, 3, 650), (8, 3, 2, 160), (9, 2, 1, 740), (10, 3, 2, 310),
+(11, 2, 1, 640), (12, 3, 2, 240), (13, 3, 2, 340), (14, 2, 1, 720),
+(15, 2, 3, 100),
+(16, 2, 3, 980), (17, 2, 1, 80), (18, 1, 2, 760), (19, 2, 3, 740),
+(20, 2, 1, 990);
+
+INSERT INTO transactions (id, ledger_id) VALUES
+(2, 1), (3, 1), (5, 1), (8, 1), (12, 1), (18, 1), (22, 1), (28, 1),
+(34, 1), (35, 1),
+(40, 1), (1, 2), (4, 2), (6, 2), (10, 2), (13, 2), (16, 2), (17, 2),
+(20, 2), (21, 2),
+(24, 2), (26, 2), (27, 2), (29, 2), (31, 2), (33, 2), (36, 2), (37, 2),
+(39, 2), (7, 3),
+(9, 3), (11, 3), (14, 3), (15, 3), (19, 3), (23, 3), (25, 3), (30, 3),
+(32, 3), (38, 3);
+
+INSERT INTO transaction_items (id, transaction_id, charge_id, amount) VALUES
+(1, 1, 1, -200), (2, 2, 1, 200), (3, 3, 2, -330), (4, 4, 2, 330),
+(5, 5, 3, -640),
+(6, 6, 3, 640), (7, 7, 4, -640), (8, 8, 4, 640), (9, 9, 5, -1000),
+(10, 10, 5, 1000),
+(11, 11, 6, -660), (12, 12, 6, 660), (13, 13, 7, -650), (14, 14, 7, 650),
+(15, 15, 8, -160),
+(16, 16, 8, 160), (17, 17, 9, -740), (18, 18, 9, 740), (19, 19, 10, -310),
+(20, 20, 10, 310),
+(21, 21, 11, -640), (22, 22, 11, 640), (23, 23, 12, -240), (24, 24, 12, 240),
+(25, 25, 13, -340),
+(26, 26, 13, 340), (27, 27, 14, -720), (28, 28, 14, 720), (29, 29, 15, -100),
+(30, 30, 15, 100),
+(31, 31, 16, -980), (32, 32, 16, 980), (33, 33, 17, -80), (34, 34, 17, 80),
+(35, 35, 18, -760),
+(36, 36, 18, 760), (37, 37, 19, -740), (38, 38, 19, 740), (39, 39, 20, -990),
+(40, 40, 20, 990);
+
+ANALYZE TABLE ledgers, charges, transactions, transaction_items;
+
+let $q=
+SELECT
+ charges.id,
+ charges.from_ledger_id,
+ charges.to_ledger_id,
+ from_agg_items.num_rows AS from_num_rows
+FROM charges
+INNER JOIN (
+ SELECT
+ transactions.ledger_id,
+ transaction_items.charge_id,
+ count(*) as num_rows
+ FROM transaction_items
+ INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+ GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+ from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+
+set optimizer_switch='split_materialized=on';
+eval $q;
+eval EXPLAIN $q;
+eval EXPLAIN FORMAT=JSON $q;
+
+set optimizer_switch='split_materialized=off';
+eval $q;
+eval EXPLAIN $q;
+
+INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES
+(101, 4, 2, 100), (102, 7, 2, 200);
+
+let $q1=
+SELECT
+ charges.id,
+ charges.from_ledger_id,
+ charges.to_ledger_id,
+ from_agg_items.num_rows AS from_num_rows
+FROM charges
+LEFT JOIN (
+ SELECT
+ transactions.ledger_id,
+ transaction_items.charge_id,
+ count(*) as num_rows
+ FROM transaction_items
+ INNER JOIN transactions ON transaction_items.transaction_id = transactions.id
+ GROUP BY transactions.ledger_id, transaction_items.charge_id
+) AS from_agg_items
+ON from_agg_items.charge_id = charges.id AND
+ from_agg_items.ledger_id = charges.from_ledger_id
+WHERE charges.to_ledger_id = 2;
+
+set optimizer_switch='split_materialized=on';
+eval $q1;
+eval EXPLAIN $q1;
+eval EXPLAIN FORMAT=JSON $q1;
+
+set optimizer_switch='split_materialized=off';
+eval $q1;
+eval EXPLAIN $q1;
+
+set optimizer_switch='split_materialized=default';
+
+DROP TABLE transaction_items;
+DROP TABLE transactions;
+DROP TABLE charges;
+DROP TABLE ledgers;
+
--echo # End of 10.3 tests
diff --git a/sql/opt_split.cc b/sql/opt_split.cc
index 9dfc8ac..875b2e1 100644
--- a/sql/opt_split.cc
+++ b/sql/opt_split.cc
@@ -1048,16 +1048,16 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
Inject equalities for splitting used by the materialization join
@param
- remaining_tables used to filter out the equalities that cannot
+ excluded_tables used to filter out the equalities that cannot
be pushed.
@details
- This function is called by JOIN_TAB::fix_splitting that is used
- to fix the chosen splitting of a splittable materialized table T
- in the final query execution plan. In this plan the table T
- is joined just before the 'remaining_tables'. So all equalities
- usable for splitting whose right parts do not depend on any of
- remaining tables can be pushed into join for T.
+ This function injects equalities pushed into a derived table T for which
+ the split optimization has been chosen by the optimizer. The function
+ is called by JOIN::inject_splitting_cond_for_all_tables_with_split_op().
+ All equalities usable for splitting T whose right parts do not depend on
+ any of the 'excluded_tables' can be pushed into the where clause of the
+ derived table T.
The function also marks the select that specifies T as
UNCACHEABLE_DEPENDENT_INJECTED.
@@ -1066,7 +1066,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
true on failure
*/
-bool JOIN::inject_best_splitting_cond(table_map remaining_tables)
+bool JOIN::inject_best_splitting_cond(table_map excluded_tables)
{
Item *inj_cond= 0;
List<Item> *inj_cond_list= &spl_opt_info->inj_cond_list;
@@ -1074,7 +1074,7 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables)
KEY_FIELD *added_key_field;
while ((added_key_field= li++))
{
- if (remaining_tables & added_key_field->val->used_tables())
+ if (excluded_tables & added_key_field->val->used_tables())
continue;
if (inj_cond_list->push_back(added_key_field->cond, thd->mem_root))
return true;
@@ -1168,8 +1168,6 @@ bool JOIN_TAB::fix_splitting(SplM_plan_info *spl_plan,
memcpy((char *) md_join->best_positions,
(char *) spl_plan->best_positions,
sizeof(POSITION) * md_join->table_count);
- if (md_join->inject_best_splitting_cond(remaining_tables))
- return true;
/*
This is called for a proper work of JOIN::get_best_combination()
called for the join that materializes T
@@ -1213,7 +1211,8 @@ bool JOIN::fix_all_splittings_in_plan()
if (tab->table->is_splittable())
{
SplM_plan_info *spl_plan= cur_pos->spl_plan;
- if (tab->fix_splitting(spl_plan, all_tables & ~prev_tables,
+ if (tab->fix_splitting(spl_plan,
+ all_tables & ~prev_tables,
tablenr < const_tables ))
return true;
}
@@ -1221,3 +1220,44 @@ bool JOIN::fix_all_splittings_in_plan()
}
return false;
}
+
+
+/**
+ @brief
+ Inject splitting conditions into WHERE of split derived
+
+ @details
+ The function calls JOIN_TAB::inject_best_splitting_cond() for each
+ materialized derived table T used in this join for which the split
+ optimization has been chosen by the optimizer. It is done in order to
+ inject equalities pushed into the where clause of the specification
+ of T that would be helpful to employ the splitting technique.
+
+ @retval
+ false on success
+ true on failure
+*/
+
+bool JOIN::inject_splitting_cond_for_all_tables_with_split_opt()
+{
+ table_map prev_tables= 0;
+ table_map all_tables= (table_map(1) << table_count) - 1;
+ for (uint tablenr= 0; tablenr < table_count; tablenr++)
+ {
+ POSITION *cur_pos= &best_positions[tablenr];
+ JOIN_TAB *tab= cur_pos->table;
+ prev_tables|= tab->table->map;
+ if (!(tab->table->is_splittable() && cur_pos->spl_plan))
+ continue;
+ SplM_opt_info *spl_opt_info= tab->table->spl_opt_info;
+ JOIN *join= spl_opt_info->join;
+ /*
+ Currently the equalities referencing columns of SJM tables with
+ look-up access cannot be pushed into materialized derived.
+ */
+ if (join->inject_best_splitting_cond((all_tables & ~prev_tables) |
+ sjm_lookup_tables))
+ return true;
+ }
+ return false;
+}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 40ecc45..1586b7f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9747,6 +9747,9 @@ bool JOIN::get_best_combination()
hash_join= FALSE;
fix_semijoin_strategies_for_picked_join_order(this);
+
+ if (inject_splitting_cond_for_all_tables_with_split_opt())
+ DBUG_RETURN(TRUE);
JOIN_TAB_RANGE *root_range;
if (!(root_range= new (thd->mem_root) JOIN_TAB_RANGE))
@@ -21848,21 +21851,6 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
cond->marker=3; // Checked when read
return (COND*) 0;
}
- /*
- If cond is an equality injected for split optimization then
- a. when retain_ref_cond == false : cond is removed unconditionally
- (cond that supports ref access is removed by the preceding code)
- b. when retain_ref_cond == true : cond is removed if it does not
- support ref access
- */
- if (left_item->type() == Item::FIELD_ITEM &&
- is_eq_cond_injected_for_split_opt((Item_func_eq *) cond) &&
- (!retain_ref_cond ||
- !test_if_ref(root_cond, (Item_field*) left_item,right_item)))
- {
- cond->marker=3;
- return (COND*) 0;
- }
}
cond->marker=2;
cond->set_join_tab_idx(join_tab_idx_arg);
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 1efb247..412efe5 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1764,6 +1764,7 @@ class JOIN :public Sql_alloc
void add_keyuses_for_splitting();
bool inject_best_splitting_cond(table_map remaining_tables);
bool fix_all_splittings_in_plan();
+ bool inject_splitting_cond_for_all_tables_with_split_opt();
bool transform_in_predicates_into_in_subq(THD *thd);
private:
1
0
[Commits] 88ca6562b48: MDEV-25447: MyRocks use "tmpdir" rather than using "rocksdb_tmpdir"
by psergey 21 Jan '22
by psergey 21 Jan '22
21 Jan '22
revision-id: 88ca6562b48d701128372a51287d2adb13b61db1 (mariadb-10.2.40-236-g88ca6562b48)
parent(s): c1d7b4575e67bd0ef458457859cdf7de32b3d4f9
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-21 19:47:24 +0300
message:
MDEV-25447: MyRocks use "tmpdir" rather than using "rocksdb_tmpdir"
- Move mysql_tmpfile_path() from inside InnoDB onto the SQL layer.
- Make MyRocks use it, like its upstream does.
---
sql/handler.h | 7 +++++++
sql/sql_class.cc | 28 ++++++++++++++++++++++++++++
storage/innobase/handler/ha_innodb.cc | 27 ---------------------------
storage/rocksdb/rdb_index_merge.cc | 4 ----
4 files changed, 35 insertions(+), 31 deletions(-)
diff --git a/sql/handler.h b/sql/handler.h
index 02a4a76c6c1..40c84cf51e3 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -4410,6 +4410,13 @@ void print_keydup_error(TABLE *table, KEY *key, myf errflag);
int del_global_index_stat(THD *thd, TABLE* table, KEY* key_info);
int del_global_table_stat(THD *thd, LEX_STRING *db, LEX_STRING *table);
+
+/*
+ TODO: Starting with next release, make this a part of Plugin API in
+ include/mysql/plugin.h
+*/
+extern "C" int mysql_tmpfile_path(const char *path, const char *prefix);
+
#ifndef DBUG_OFF
/** Converts XID to string.
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 479578679f1..17932fe7d6f 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -340,6 +340,34 @@ extern "C" int mysql_tmpfile(const char *prefix)
}
+extern "C" int mysql_tmpfile_path(const char *path, const char *prefix)
+{
+ DBUG_ASSERT(path != NULL);
+ DBUG_ASSERT((strlen(path) + strlen(prefix)) <= FN_REFLEN);
+
+ char filename[FN_REFLEN];
+ File fd = create_temp_file(filename, path, prefix,
+#ifdef __WIN__
+ O_BINARY | O_TRUNC | O_SEQUENTIAL |
+ O_SHORT_LIVED |
+#endif /* __WIN__ */
+ O_CREAT | O_EXCL | O_RDWR | O_TEMPORARY,
+ MYF(MY_WME));
+ if (fd >= 0) {
+#ifndef __WIN__
+ /*
+ This can be removed once the following bug is fixed:
+ Bug #28903 create_temp_file() doesn't honor O_TEMPORARY option
+ (file not removed) (Unix)
+ */
+ unlink(filename);
+#endif /* !__WIN__ */
+ }
+
+ return fd;
+}
+
+
extern "C"
int thd_in_lock_tables(const THD *thd)
{
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 889aee0d47e..01244514744 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -2391,33 +2391,6 @@ static bool is_mysql_datadir_path(const char *path)
TRUE));
}
-static int mysql_tmpfile_path(const char *path, const char *prefix)
-{
- DBUG_ASSERT(path != NULL);
- DBUG_ASSERT((strlen(path) + strlen(prefix)) <= FN_REFLEN);
-
- char filename[FN_REFLEN];
- File fd = create_temp_file(filename, path, prefix,
-#ifdef __WIN__
- O_BINARY | O_TRUNC | O_SEQUENTIAL |
- O_SHORT_LIVED |
-#endif /* __WIN__ */
- O_CREAT | O_EXCL | O_RDWR | O_TEMPORARY,
- MYF(MY_WME));
- if (fd >= 0) {
-#ifndef __WIN__
- /*
- This can be removed once the following bug is fixed:
- Bug #28903 create_temp_file() doesn't honor O_TEMPORARY option
- (file not removed) (Unix)
- */
- unlink(filename);
-#endif /* !__WIN__ */
- }
-
- return fd;
-}
-
/** Creates a temporary file in the location specified by the parameter
path. If the path is NULL, then it will be created in tmpdir.
@param[in] path location for creating temporary file
diff --git a/storage/rocksdb/rdb_index_merge.cc b/storage/rocksdb/rdb_index_merge.cc
index 424a998548a..c2742d482ee 100644
--- a/storage/rocksdb/rdb_index_merge.cc
+++ b/storage/rocksdb/rdb_index_merge.cc
@@ -107,16 +107,12 @@ int Rdb_index_merge::merge_file_create() {
DBUG_ASSERT(m_merge_file.m_fd == -1);
int fd;
-#ifdef MARIAROCKS_NOT_YET // mysql_tmpfile_path use
/* If no path set for tmpfile, use mysql_tmpdir by default */
if (m_tmpfile_path == nullptr) {
fd = mysql_tmpfile("myrocks");
} else {
fd = mysql_tmpfile_path(m_tmpfile_path, "myrocks");
}
-#else
- fd = mysql_tmpfile("myrocks");
-#endif
if (fd < 0) {
// NO_LINT_DEBUG
sql_print_error("Failed to create temp file during fast index creation.");
1
0
21 Jan '22
revision-id: fa7a67ff499582fad6e4f1ff8198689325dee0dd (mariadb-10.2.40-234-gfa7a67ff499)
parent(s): ad88c428c50e86cd78da2a9ecd027add2f9d6ff9
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-21 09:31:16 +0300
message:
MDEV-27149: Add rocksdb_ignore_datadic_errors
Add a --rocksdb_ignore_datadic_errors plugin option for MyRocks.
The default is 0, and this means MyRocks will call abort() if it detects
a DDL mismatch.
Setting rocksdb_ignore_datadic_errors=1 makes MyRocks to try to ignore the
errors and allow to start the server for repairs.
---
storage/rocksdb/ha_rocksdb.cc | 50 ++++++++++++++++++++--
storage/rocksdb/ha_rocksdb.h | 2 +
.../rocksdb/mysql-test/rocksdb/r/rocksdb.result | 1 +
.../r/rocksdb_ignore_datadic_errors_basic.result | 7 +++
.../t/rocksdb_ignore_datadic_errors_basic.test | 6 +++
storage/rocksdb/rdb_datadic.cc | 6 +++
6 files changed, 68 insertions(+), 4 deletions(-)
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index 1cb1a3517c5..b39db830323 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -637,6 +637,8 @@ static my_bool rocksdb_large_prefix = 0;
static my_bool rocksdb_allow_to_start_after_corruption = 0;
static char* rocksdb_git_hash;
+uint32_t rocksdb_ignore_datadic_errors = 0;
+
char *compression_types_val=
const_cast<char*>(get_rocksdb_supported_compression_types());
static unsigned long rocksdb_write_policy =
@@ -1907,6 +1909,15 @@ static MYSQL_SYSVAR_UINT(
nullptr, nullptr, 1 /* default value */, 0 /* min value */,
2 /* max value */, 0);
+static MYSQL_SYSVAR_UINT(
+ ignore_datadic_errors, rocksdb_ignore_datadic_errors,
+ PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
+ "Ignore MyRocks' data directory errors. "
+ "(CAUTION: Use only to start the server and perform repairs. Do NOT use "
+ "for regular operation)",
+ nullptr, nullptr, 0 /* default value */, 0 /* min value */,
+ 1 /* max value */, 0);
+
static MYSQL_SYSVAR_STR(datadir, rocksdb_datadir,
PLUGIN_VAR_OPCMDARG | PLUGIN_VAR_READONLY,
"RocksDB data directory", nullptr, nullptr,
@@ -2142,6 +2153,8 @@ static struct st_mysql_sys_var *rocksdb_system_variables[] = {
MYSQL_SYSVAR(rollback_on_timeout),
MYSQL_SYSVAR(enable_insert_with_update_caching),
+
+ MYSQL_SYSVAR(ignore_datadic_errors),
nullptr};
static rocksdb::WriteOptions rdb_get_rocksdb_write_options(
@@ -5176,6 +5189,13 @@ static int rocksdb_init_func(void *const p) {
DBUG_RETURN(1);
}
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_information(
+ "CAUTION: Running with rocksdb_ignore_datadic_errors=1. "
+ " This should only be used to perform repairs");
+ }
+
if (rdb_check_rocksdb_corruption()) {
// NO_LINT_DEBUG
sql_print_error(
@@ -5607,7 +5627,14 @@ static int rocksdb_init_func(void *const p) {
if (ddl_manager.init(&dict_manager, &cf_manager, rocksdb_validate_tables)) {
// NO_LINT_DEBUG
sql_print_error("RocksDB: Failed to initialize DDL manager.");
- DBUG_RETURN(HA_EXIT_FAILURE);
+
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("RocksDB: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ }
+ else
+ DBUG_RETURN(HA_EXIT_FAILURE);
}
Rdb_sst_info::init(rdb);
@@ -6674,9 +6701,18 @@ int ha_rocksdb::open(const char *const name, int mode, uint test_if_locked) {
"MyRocks has %u (%s hidden pk)",
table->s->keys, m_tbl_def->m_key_count,
has_hidden_pk(table)? "1" : "no");
- my_error(ER_INTERNAL_ERROR, MYF(0),
- "MyRocks: DDL mismatch. Check the error log for details");
- DBUG_RETURN(HA_ERR_ROCKSDB_INVALID_TABLE);
+
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("MyRocks: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ }
+ else
+ {
+ my_error(ER_INTERNAL_ERROR, MYF(0),
+ "MyRocks: DDL mismatch. Check the error log for details");
+ DBUG_RETURN(HA_ERR_ROCKSDB_INVALID_TABLE);
+ }
}
@@ -11558,6 +11594,12 @@ void Rdb_drop_index_thread::run() {
"from cf id %u. MyRocks data dictionary may "
"get corrupted.",
d.cf_id);
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("RocksDB: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ continue;
+ }
abort();
}
rocksdb::ColumnFamilyHandle *cfh = cf_manager.get_cf(d.cf_id);
diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h
index 4a379cd638a..b53ac851f4f 100644
--- a/storage/rocksdb/ha_rocksdb.h
+++ b/storage/rocksdb/ha_rocksdb.h
@@ -1059,6 +1059,8 @@ const int MYROCKS_MARIADB_PLUGIN_MATURITY_LEVEL= MariaDB_PLUGIN_MATURITY_STABLE;
extern bool prevent_myrocks_loading;
+extern uint32_t rocksdb_ignore_datadic_errors;
+
void sql_print_verbose_info(const char *format, ...);
} // namespace myrocks
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result
index 11cffac070f..71ec4d2344d 100644
--- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result
+++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result
@@ -932,6 +932,7 @@ rocksdb_force_flush_memtable_now OFF
rocksdb_force_index_records_in_range 0
rocksdb_git_hash #
rocksdb_hash_index_allow_collision ON
+rocksdb_ignore_datadic_errors 0
rocksdb_ignore_unknown_options ON
rocksdb_index_type kBinarySearch
rocksdb_info_log_level error_level
diff --git a/storage/rocksdb/mysql-test/rocksdb_sys_vars/r/rocksdb_ignore_datadic_errors_basic.result b/storage/rocksdb/mysql-test/rocksdb_sys_vars/r/rocksdb_ignore_datadic_errors_basic.result
new file mode 100644
index 00000000000..daa70a80683
--- /dev/null
+++ b/storage/rocksdb/mysql-test/rocksdb_sys_vars/r/rocksdb_ignore_datadic_errors_basic.result
@@ -0,0 +1,7 @@
+SET @start_global_value = @@global.ROCKSDB_IGNORE_DATADIC_ERRORS;
+SELECT @start_global_value;
+@start_global_value
+0
+"Trying to set variable @@global.ROCKSDB_IGNORE_DATADIC_ERRORS to 444. It should fail because it is readonly."
+SET @@global.ROCKSDB_IGNORE_DATADIC_ERRORS = 444;
+ERROR HY000: Variable 'rocksdb_ignore_datadic_errors' is a read only variable
diff --git a/storage/rocksdb/mysql-test/rocksdb_sys_vars/t/rocksdb_ignore_datadic_errors_basic.test b/storage/rocksdb/mysql-test/rocksdb_sys_vars/t/rocksdb_ignore_datadic_errors_basic.test
new file mode 100644
index 00000000000..b412a018869
--- /dev/null
+++ b/storage/rocksdb/mysql-test/rocksdb_sys_vars/t/rocksdb_ignore_datadic_errors_basic.test
@@ -0,0 +1,6 @@
+--source include/have_rocksdb.inc
+
+--let $sys_var=ROCKSDB_IGNORE_DATADIC_ERRORS
+--let $read_only=1
+--let $session=0
+--source include/rocksdb_sys_var.inc
diff --git a/storage/rocksdb/rdb_datadic.cc b/storage/rocksdb/rdb_datadic.cc
index 31bc40b1df9..45bb665654c 100644
--- a/storage/rocksdb/rdb_datadic.cc
+++ b/storage/rocksdb/rdb_datadic.cc
@@ -5240,6 +5240,12 @@ void Rdb_dict_manager::log_start_drop_index(GL_INDEX_ID gl_index_id,
"from index id (%u,%u). MyRocks data dictionary may "
"get corrupted.",
gl_index_id.cf_id, gl_index_id.index_id);
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("RocksDB: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ return;
+ }
abort();
}
}
1
0
revision-id: a2d1ca0264ac93982ba1aca45877562e4c2cc00c (mariadb-10.2.40-235-ga2d1ca0264a)
parent(s): aa2a6b2f9bccbac97830aa30f0db13bd2cbe211c
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-20 22:57:02 +0300
message:
Make Explain_node::children protected
1
0
20 Jan '22
revision-id: aa2a6b2f9bccbac97830aa30f0db13bd2cbe211c (mariadb-10.2.40-234-gaa2a6b2f9bc)
parent(s): ad88c428c50e86cd78da2a9ecd027add2f9d6ff9
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-20 22:52:41 +0300
message:
MDEV-27149: Add rocksdb_ignore_datadic_errors
Add a --rocksdb_ignore_datadic_errors plugin option for MyRocks.
The default is 0, and this means MyRocks will call abort() if it detects
a DDL mismatch.
Setting rocksdb_ignore_datadic_errors=1 makes MyRocks to try to ignore the
errors and allow to start the server for repairs.
---
storage/rocksdb/ha_rocksdb.cc | 50 ++++++++++++++++++++--
storage/rocksdb/ha_rocksdb.h | 2 +
.../rocksdb/mysql-test/rocksdb/r/rocksdb.result | 1 +
.../r/rocksdb_ignore_datadic_errors.result | 7 +++
.../t/rocksdb_ignore_datadic_errors.test | 6 +++
storage/rocksdb/rdb_datadic.cc | 6 +++
6 files changed, 68 insertions(+), 4 deletions(-)
diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc
index 1cb1a3517c5..b39db830323 100644
--- a/storage/rocksdb/ha_rocksdb.cc
+++ b/storage/rocksdb/ha_rocksdb.cc
@@ -637,6 +637,8 @@ static my_bool rocksdb_large_prefix = 0;
static my_bool rocksdb_allow_to_start_after_corruption = 0;
static char* rocksdb_git_hash;
+uint32_t rocksdb_ignore_datadic_errors = 0;
+
char *compression_types_val=
const_cast<char*>(get_rocksdb_supported_compression_types());
static unsigned long rocksdb_write_policy =
@@ -1907,6 +1909,15 @@ static MYSQL_SYSVAR_UINT(
nullptr, nullptr, 1 /* default value */, 0 /* min value */,
2 /* max value */, 0);
+static MYSQL_SYSVAR_UINT(
+ ignore_datadic_errors, rocksdb_ignore_datadic_errors,
+ PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
+ "Ignore MyRocks' data directory errors. "
+ "(CAUTION: Use only to start the server and perform repairs. Do NOT use "
+ "for regular operation)",
+ nullptr, nullptr, 0 /* default value */, 0 /* min value */,
+ 1 /* max value */, 0);
+
static MYSQL_SYSVAR_STR(datadir, rocksdb_datadir,
PLUGIN_VAR_OPCMDARG | PLUGIN_VAR_READONLY,
"RocksDB data directory", nullptr, nullptr,
@@ -2142,6 +2153,8 @@ static struct st_mysql_sys_var *rocksdb_system_variables[] = {
MYSQL_SYSVAR(rollback_on_timeout),
MYSQL_SYSVAR(enable_insert_with_update_caching),
+
+ MYSQL_SYSVAR(ignore_datadic_errors),
nullptr};
static rocksdb::WriteOptions rdb_get_rocksdb_write_options(
@@ -5176,6 +5189,13 @@ static int rocksdb_init_func(void *const p) {
DBUG_RETURN(1);
}
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_information(
+ "CAUTION: Running with rocksdb_ignore_datadic_errors=1. "
+ " This should only be used to perform repairs");
+ }
+
if (rdb_check_rocksdb_corruption()) {
// NO_LINT_DEBUG
sql_print_error(
@@ -5607,7 +5627,14 @@ static int rocksdb_init_func(void *const p) {
if (ddl_manager.init(&dict_manager, &cf_manager, rocksdb_validate_tables)) {
// NO_LINT_DEBUG
sql_print_error("RocksDB: Failed to initialize DDL manager.");
- DBUG_RETURN(HA_EXIT_FAILURE);
+
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("RocksDB: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ }
+ else
+ DBUG_RETURN(HA_EXIT_FAILURE);
}
Rdb_sst_info::init(rdb);
@@ -6674,9 +6701,18 @@ int ha_rocksdb::open(const char *const name, int mode, uint test_if_locked) {
"MyRocks has %u (%s hidden pk)",
table->s->keys, m_tbl_def->m_key_count,
has_hidden_pk(table)? "1" : "no");
- my_error(ER_INTERNAL_ERROR, MYF(0),
- "MyRocks: DDL mismatch. Check the error log for details");
- DBUG_RETURN(HA_ERR_ROCKSDB_INVALID_TABLE);
+
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("MyRocks: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ }
+ else
+ {
+ my_error(ER_INTERNAL_ERROR, MYF(0),
+ "MyRocks: DDL mismatch. Check the error log for details");
+ DBUG_RETURN(HA_ERR_ROCKSDB_INVALID_TABLE);
+ }
}
@@ -11558,6 +11594,12 @@ void Rdb_drop_index_thread::run() {
"from cf id %u. MyRocks data dictionary may "
"get corrupted.",
d.cf_id);
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("RocksDB: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ continue;
+ }
abort();
}
rocksdb::ColumnFamilyHandle *cfh = cf_manager.get_cf(d.cf_id);
diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h
index 4a379cd638a..b53ac851f4f 100644
--- a/storage/rocksdb/ha_rocksdb.h
+++ b/storage/rocksdb/ha_rocksdb.h
@@ -1059,6 +1059,8 @@ const int MYROCKS_MARIADB_PLUGIN_MATURITY_LEVEL= MariaDB_PLUGIN_MATURITY_STABLE;
extern bool prevent_myrocks_loading;
+extern uint32_t rocksdb_ignore_datadic_errors;
+
void sql_print_verbose_info(const char *format, ...);
} // namespace myrocks
diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result
index 11cffac070f..71ec4d2344d 100644
--- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result
+++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result
@@ -932,6 +932,7 @@ rocksdb_force_flush_memtable_now OFF
rocksdb_force_index_records_in_range 0
rocksdb_git_hash #
rocksdb_hash_index_allow_collision ON
+rocksdb_ignore_datadic_errors 0
rocksdb_ignore_unknown_options ON
rocksdb_index_type kBinarySearch
rocksdb_info_log_level error_level
diff --git a/storage/rocksdb/mysql-test/rocksdb_sys_vars/r/rocksdb_ignore_datadic_errors.result b/storage/rocksdb/mysql-test/rocksdb_sys_vars/r/rocksdb_ignore_datadic_errors.result
new file mode 100644
index 00000000000..daa70a80683
--- /dev/null
+++ b/storage/rocksdb/mysql-test/rocksdb_sys_vars/r/rocksdb_ignore_datadic_errors.result
@@ -0,0 +1,7 @@
+SET @start_global_value = @@global.ROCKSDB_IGNORE_DATADIC_ERRORS;
+SELECT @start_global_value;
+@start_global_value
+0
+"Trying to set variable @@global.ROCKSDB_IGNORE_DATADIC_ERRORS to 444. It should fail because it is readonly."
+SET @@global.ROCKSDB_IGNORE_DATADIC_ERRORS = 444;
+ERROR HY000: Variable 'rocksdb_ignore_datadic_errors' is a read only variable
diff --git a/storage/rocksdb/mysql-test/rocksdb_sys_vars/t/rocksdb_ignore_datadic_errors.test b/storage/rocksdb/mysql-test/rocksdb_sys_vars/t/rocksdb_ignore_datadic_errors.test
new file mode 100644
index 00000000000..b412a018869
--- /dev/null
+++ b/storage/rocksdb/mysql-test/rocksdb_sys_vars/t/rocksdb_ignore_datadic_errors.test
@@ -0,0 +1,6 @@
+--source include/have_rocksdb.inc
+
+--let $sys_var=ROCKSDB_IGNORE_DATADIC_ERRORS
+--let $read_only=1
+--let $session=0
+--source include/rocksdb_sys_var.inc
diff --git a/storage/rocksdb/rdb_datadic.cc b/storage/rocksdb/rdb_datadic.cc
index 31bc40b1df9..45bb665654c 100644
--- a/storage/rocksdb/rdb_datadic.cc
+++ b/storage/rocksdb/rdb_datadic.cc
@@ -5240,6 +5240,12 @@ void Rdb_dict_manager::log_start_drop_index(GL_INDEX_ID gl_index_id,
"from index id (%u,%u). MyRocks data dictionary may "
"get corrupted.",
gl_index_id.cf_id, gl_index_id.index_id);
+ if (rocksdb_ignore_datadic_errors)
+ {
+ sql_print_error("RocksDB: rocksdb_ignore_datadic_errors=1, "
+ "trying to continue");
+ return;
+ }
abort();
}
}
1
0