- commits - lists.mariadb.org

[Commits] 496d32b0714: MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294
by psergey 10 Feb '22

10 Feb '22

revision-id: 496d32b071424a4b119fa3902b9f5fd6184dad09 (mariadb-10.2.42-2-g496d32b0714) parent(s): 941bc7053616d5ca8c9e6538828c4a65802e8c3d author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-02-10 14:23:20 +0300 message: MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294 The asserion failure was caused by this query select /*id=1*/ from t1 where col= ( select /*id=2*/ from ... where corr_cond1 union select /*id=4*/ from ... where corr_cond2) Here, - select with id=2 was correlated due to corr_cond1. - select with id=4 was initially correlated due to corr_cond2, but then the optimizer optimized away the correlation, making the select with id=4 uncorrelated. However, since select with id=2 remained correlated, the execution had to re-compute the whole UNION. When it tried to execute select with id=4, it hit an assertion (join buffer already free'd). This is because select with id=4 has freed its execution structures after it has been executed once. The select is uncorrelated, so it did not expect it would select --- mysql-test/r/subselect4.result | 4 +++- mysql-test/r/union_innodb.result | 34 ++++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 2 ++ mysql-test/t/union_innodb.test | 45 ++++++++++++++++++++++++++++++++++++++++ sql/sql_lex.cc | 16 +++++++++++++- 5 files changed, 99 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index b1db309ec18..2657977dae7 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1358,6 +1358,8 @@ INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0); CREATE TABLE t3 (f4 int,KEY (f4)) ; INSERT IGNORE INTO t3 VALUES (0),(0); set @@optimizer_switch='semijoin=off'; +# NOTE: the following should have 'SUBQUERY', not 'DEPENDENT SUBQUERY' +# for line with id=2, see MDEV-27794. EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 @@ -1367,7 +1369,7 @@ FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary 3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE diff --git a/mysql-test/r/union_innodb.result b/mysql-test/r/union_innodb.result new file mode 100644 index 00000000000..876897fa865 --- /dev/null +++ b/mysql-test/r/union_innodb.result @@ -0,0 +1,34 @@ +# +# MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294 +# +CREATE TABLE t1 (i1 int)engine=innodb; +INSERT INTO `t1` VALUES (62),(66); +CREATE TABLE t2 (i1 int) engine=innodb; +SELECT 1 FROM t1 +WHERE t1.i1 =( SELECT t1.i1 FROM t2 +UNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2) +window w1 as (partition by t1.i1)); +1 +drop table t1,t2; +# Another testcase +CREATE TABLE t1 (i3 int NOT NULL, i1 int , i2 int , i4 int , PRIMARY key(i2)); +INSERT INTO t1 VALUES (6,72,98,98),(46,1,6952,0); +SELECT i1 FROM t1 +WHERE t1.i3 = +(SELECT ref_4.i2 FROM t1 AS ref_4 +WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4) +UNION +SELECT ref_6.i2 +FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON ((ref_6.i1 > ref_6.i2) OR (ref_5.i4 < ref_5.i4))) +WHERE (t1.i2 >= t1.i2)); +i1 +drop table t1; +# +# MDEV-25761: Assertion `aggr != __null' failed in sub_select_postjoin_aggr +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY) engine=innodb; +INSERT INTO t1 VALUES (0),(4),(31); +CREATE TABLE t2 (i int) engine=innodb; +DELETE FROM t1 WHERE t1.a = +(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a); +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index bd1e20cb5d6..93389571c5c 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1039,6 +1039,8 @@ INSERT IGNORE INTO t3 VALUES (0),(0); set @@optimizer_switch='semijoin=off'; +--echo # NOTE: the following should have 'SUBQUERY', not 'DEPENDENT SUBQUERY' +--echo # for line with id=2, see MDEV-27794. EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 diff --git a/mysql-test/t/union_innodb.test b/mysql-test/t/union_innodb.test new file mode 100644 index 00000000000..cb805a30bb4 --- /dev/null +++ b/mysql-test/t/union_innodb.test @@ -0,0 +1,45 @@ +--source include/have_innodb.inc + +--echo # +--echo # MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294 +--echo # + +CREATE TABLE t1 (i1 int)engine=innodb; +INSERT INTO `t1` VALUES (62),(66); +CREATE TABLE t2 (i1 int) engine=innodb; + +SELECT 1 FROM t1 +WHERE t1.i1 =( SELECT t1.i1 FROM t2 + UNION SELECT i1 FROM (t1 AS dt1 natural JOIN t2) + window w1 as (partition by t1.i1)); + +drop table t1,t2; + +--echo # Another testcase +CREATE TABLE t1 (i3 int NOT NULL, i1 int , i2 int , i4 int , PRIMARY key(i2)); +INSERT INTO t1 VALUES (6,72,98,98),(46,1,6952,0); + +SELECT i1 FROM t1 +WHERE t1.i3 = + (SELECT ref_4.i2 FROM t1 AS ref_4 + WHERE t1.i2 > (SELECT i3 FROM t1 ORDER BY i3 LIMIT 1 OFFSET 4) + UNION + SELECT ref_6.i2 + FROM (t1 AS ref_5 JOIN t1 AS ref_6 ON ((ref_6.i1 > ref_6.i2) OR (ref_5.i4 < ref_5.i4))) + WHERE (t1.i2 >= t1.i2)); + +drop table t1; + +--echo # +--echo # MDEV-25761: Assertion `aggr != __null' failed in sub_select_postjoin_aggr +--echo # + +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY) engine=innodb; +INSERT INTO t1 VALUES (0),(4),(31); + +CREATE TABLE t2 (i int) engine=innodb; + +DELETE FROM t1 WHERE t1.a = + (SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a); + +DROP TABLE t1,t2; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 125bbfe1bfd..47ff2836aba 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3898,7 +3898,21 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) } if (empty_union_result) subquery_predicate->no_rows_in_result(); - if (!is_correlated_unit) + + if (is_correlated_unit) + { + /* + Some parts of UNION are not correlated. This means we will need to + re-execute the whole UNION every time. Mark all parts of the UNION + as correlated so that they are prepared to be executed multiple + times (if we don't do that, some part of the UNION may free its + execution data at the end of first execution and crash on the second + execution) + */ + for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select()) + sl->uncacheable |= UNCACHEABLE_DEPENDENT; + } + else un->uncacheable&= ~UNCACHEABLE_DEPENDENT; subquery_predicate->is_correlated= is_correlated_unit; }

1 0

[Commits] 8cf192c034f: MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
by psergey 07 Feb '22

07 Feb '22

revision-id: 8cf192c034f1359ae716e3f363dc2856a85e06ff (mariadb-10.2.40-236-g8cf192c034f) parent(s): c1d7b4575e67bd0ef458457859cdf7de32b3d4f9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-02-07 17:09:04 +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 ++++++++++++++++++++++ .../suite/encryption/r/tempfiles_encrypted.result | 32 ++++++++++++++++++++++ mysql-test/t/win.test | 27 ++++++++++++++++++ sql/item_windowfunc.cc | 3 ++ sql/sql_yacc.yy | 3 -- 5 files changed, 94 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/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index f66a5fd0b25..aaba6bb044a 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -4204,6 +4204,38 @@ 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

[Commits] 458e91054ea: MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
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

[Commits] 5714599: Stabilizing the test case for MDEV-22846.
by IgorBabaev 02 Feb '22

02 Feb '22

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

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

[Commits] 9149fcb8d50: Fix Item_func::is_simplifed_cond_processor
by psergey 27 Jan '22

27 Jan '22

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

[Commits] 768b5f70b59: Fix Item_func::is_simplifed_cond_processor
by psergey 27 Jan '22

27 Jan '22

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

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

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

[Commits] fa7a67ff499: MDEV-27149: Add rocksdb_ignore_datadic_errors
by psergey 21 Jan '22

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