- commits - lists.mariadb.org

[Commits] 9b425d3: MDEV-27132 Wrong result from query when using split optimization
by IgorBabaev 13 Jan '22

13 Jan '22

revision-id: 9b425d348be8c5af85475608d755d134768d8ee3 (mariadb-10.3.26-308-g9b425d3) parent(s): c04adce8ac34412c1352c77140cf842020d6b4fd author: Igor Babaev committer: Igor Babaev timestamp: 2022-01-13 15:54:59 -0800 message: MDEV-27132 Wrong result from query when using split optimization This bug could affect queries with a grouping derived table containing equalities in the where clause of its specification if the optimizer chose to apply split optimization to access the derived table. In such cases wrong results could be returned from the queries. When the optimizer considers a possibility of using split optimization to a derived table it injects equalities joining the derived table with other tables into the where condition of the derived table. After the join order for the execution using split optimization has been chosen as the cheapest the injected equalities that are not used to access the derived table are removed from the where condition of the derived table. For this removal the optimizer looks through the conjuncts of the where condition of the derived table, fetches the equalities and checks whether they belong to the list of injected equalities. As the injection of the list was performed just by the insertion of it into the list of top level AND condition of the where condition some extra conjuncts from the where condition could be automatically attached to the end of the list of injected equalities. If such attached conjunct happened to be an equality predicate it was removed from the where condition of the derived table and thus lost for checking at the execution phase. The bug has been fixed by injecting of a shallow copy of the list of the pushed equalities rather than the list itself leaving the latter intact. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/derived_cond_pushdown.result | 171 +++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 100 ++++++++++++++++ sql/opt_split.cc | 2 +- 3 files changed, 272 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index d2c1169..6343bdc 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17531,4 +17531,175 @@ id a a id 21 2 2 2 deallocate prepare stmt; drop table t1,t2,t3; +# +# MDEV-MDEV-27132: Splittable derived with equality in WHERE +# +CREATE TABLE t1 ( +id int PRIMARY KEY +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834), +(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844), +(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854), +(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864), +(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874), +(4875),(4876); +CREATE TABLE t2 ( +id int PRIMARY KEY AUTO_INCREMENT, +deleted int(1), +t1_id int, +email varchar(255), +reporting_person int(1), +KEY t1_id (t1_id) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(1,0,2064,'1test(a)test.ee',1),(2,1626095588,2066,'2test(a)test.ee',1), +(3,0,2066,'3test(a)test.ee',1),(4,0,2068,'4test(a)test.ee',1), +(5,0,2068,'5test(a)test.ee',1),(6,0,2069,'6test(a)test.ee',1),(7,0,2070,'',0), +(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0), +(12,0,2072,'',0),(13,0,2072,'13test(a)test.ee',1),(14,0,2073,'14test(a)test.ee',1), +(15,0,2074,'15test(a)test.ee',1),(16,0,2075,'16test(a)test.ee',1),(17,0,2075,'',0), +(18,0,2075,'',0),(19,0,2076,'19test(a)test.ee',1),(20,0,2077,'',0), +(21,0,2078,'21test(a)test.ee',1),(22,0,2078,'22test(a)test.ee',1); +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+10000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+20000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+40000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+80000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+160000, email, reporting_person FROM t2; +CREATE TABLE t3 ( +id int PRIMARY KEY, +deleted int, +t1_id int, +YEAR int(4), +quarter int(1), +KEY t1_id (t1_id,year,quarter) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1), +(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1), +(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2), +(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2), +(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3), +(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3), +(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3), +(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3), +(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3), +(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3), +(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3), +(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3), +(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3), +(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3), +(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3), +(350,0,2304,2020,3),(351,0,3896,2020,3); +ANALYZE TABLE t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +id +EXPLAIN SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range t1_id t1_id 5 NULL 46 Using where; Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.t1_id 1 Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t3.t1_id 2 +2 LATERAL DERIVED t2 ref t1_id t1_id 5 test.t1.id 3 Using index condition; Using where +EXPLAIN FORMAT=JSON SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "range", + "possible_keys": ["t1_id"], + "key": "t1_id", + "key_length": "5", + "used_key_parts": ["t1_id"], + "rows": 46, + "filtered": 100, + "attached_condition": "t3.t1_id between 200 and 100000 and t3.t1_id is not null", + "using_index": true + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.t3.t1_id"], + "rows": 1, + "filtered": 100, + "using_index": true + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["t1_id"], + "ref": ["test.t3.t1_id"], + "rows": 2, + "filtered": 100, + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["t1_id"], + "key": "t1_id", + "key_length": "5", + "used_key_parts": ["t1_id"], + "ref": ["test.t1.id"], + "rows": 3, + "filtered": 100, + "index_condition": "t2.t1_id between 200 and 100000", + "attached_condition": "t2.reporting_person = 1" + } + } + } + } + } +} +set optimizer_switch='split_materialized=off'; +SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +id +set optimizer_switch='split_materialized=default'; +DROP TABLE t1,t2,t3; # End of 10.3 tests diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 9bb9d9b..4f4ffc9 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3611,4 +3611,104 @@ deallocate prepare stmt; drop table t1,t2,t3; +--echo # +--echo # MDEV-MDEV-27132: Splittable derived with equality in WHERE +--echo # + +CREATE TABLE t1 ( + id int PRIMARY KEY +) ENGINE=MyISAM; + +INSERT INTO t1 VALUES +(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834), +(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844), +(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854), +(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864), +(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874), +(4875),(4876); + +CREATE TABLE t2 ( + id int PRIMARY KEY AUTO_INCREMENT, + deleted int(1), + t1_id int, + email varchar(255), + reporting_person int(1), + KEY t1_id (t1_id) +) ENGINE=MyISAM; + +INSERT INTO t2 VALUES +(1,0,2064,'1test(a)test.ee',1),(2,1626095588,2066,'2test(a)test.ee',1), +(3,0,2066,'3test(a)test.ee',1),(4,0,2068,'4test(a)test.ee',1), +(5,0,2068,'5test(a)test.ee',1),(6,0,2069,'6test(a)test.ee',1),(7,0,2070,'',0), +(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0), +(12,0,2072,'',0),(13,0,2072,'13test(a)test.ee',1),(14,0,2073,'14test(a)test.ee',1), +(15,0,2074,'15test(a)test.ee',1),(16,0,2075,'16test(a)test.ee',1),(17,0,2075,'',0), +(18,0,2075,'',0),(19,0,2076,'19test(a)test.ee',1),(20,0,2077,'',0), +(21,0,2078,'21test(a)test.ee',1),(22,0,2078,'22test(a)test.ee',1); + +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+10000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+20000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+40000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+80000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+160000, email, reporting_person FROM t2; + +CREATE TABLE t3 ( + id int PRIMARY KEY, + deleted int, + t1_id int, + YEAR int(4), + quarter int(1), + KEY t1_id (t1_id,year,quarter) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1), +(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1), +(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2), +(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2), +(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3), +(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3), +(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3), +(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3), +(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3), +(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3), +(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3), +(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3), +(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3), +(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3), +(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3), +(350,0,2304,2020,3),(351,0,3896,2020,3); + +ANALYZE TABLE t1,t2,t3; + +let $q= +SELECT t1.id +FROM t1 + JOIN t3 + ON t3.t1_id = t1.id + JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx + ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; + +set optimizer_switch='split_materialized=on'; + +eval $q; +eval EXPLAIN $q; +eval EXPLAIN FORMAT=JSON $q; + +set optimizer_switch='split_materialized=off'; + +eval $q; + +set optimizer_switch='split_materialized=default'; + +DROP TABLE t1,t2,t3; + --echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 8a98509..9dfc8ac 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -1091,7 +1091,7 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables) if (inj_cond) inj_cond->fix_fields(thd,0); - if (inject_cond_into_where(inj_cond)) + if (inject_cond_into_where(inj_cond->copy_andor_structure(thd))) return true; select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;

1 0

[Commits] b1d92f72934: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values
by psergey 13 Jan '22

13 Jan '22

revision-id: b1d92f72934afaf82a1c28580f2a2b26cba4fca7 (mariadb-10.3.31-70-gb1d92f72934) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-13 16:41:37 +0300 message: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values Disable LATERAL DERIVED optimization for subqueries that have WITH ROLLUP. This bug could affect queries with grouping derived tables / views / CTEs with ROLLUP. The bug could manifest itself if the corresponding materialized derived tables are subject to split optimization. The current implementation of the split optimization produces rows from the derived table in an arbitrary order. So these rows must be accumulated in another temporary table and sorted according to the used GROUP BY clause in order to be able to generate the additional ROLLUP rows. This patch prohibits to use split optimization for grouping derived tables / views / CTEs with ROLLUP. --- mysql-test/main/derived_split_innodb.result | 42 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 36 +++++++++++++++++++++++++ sql/opt_split.cc | 5 +++- 3 files changed, 82 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 7ea3b689f23..8162bfcdae7 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -234,4 +234,46 @@ id itemid id id 4 2 4 2 drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +# +# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results +# (The testcase is taken from testcase for MDEV-13389 due to it being +# much smaller) +# +create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; +insert into t3 values +(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), +(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), +(5,14,'dd'), (9,12,'ee'); +create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; +insert into t4 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +insert into t4 select a+10, b+10, concat(c,'f') from t4; +analyze table t3,t4; +Table Op Msg_type Msg_text +test.t3 analyze status OK +test.t4 analyze status OK +# This should use a plan with LATERAL DERIVED: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range idx_b idx_b 5 NULL 3 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 +2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 +# ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range idx_b idx_b 5 NULL 3 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 +2 DERIVED t4 ALL NULL NULL NULL NULL 40 Using filesort +drop table t3, t4; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 6f33c71eede..2a3565be36f 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -186,4 +186,40 @@ eval $q; drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +--echo # +--echo # MDEV-26337: subquery with groupby and ROLLUP returns incorrect results +--echo # (The testcase is taken from testcase for MDEV-13389 due to it being +--echo # much smaller) +--echo # + +create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; +insert into t3 values +(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), +(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), +(5,14,'dd'), (9,12,'ee'); +create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; +insert into t4 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +insert into t4 select a+10, b+10, concat(c,'f') from t4; +analyze table t3,t4; + +--echo # This should use a plan with LATERAL DERIVED: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; + +--echo # ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; + +drop table t3, t4; + --echo # End of 10.3 tests diff --git a/sql/opt_split.cc b/sql/opt_split.cc index edf9ae3deff..8a985095f3c 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -310,6 +310,8 @@ struct SplM_field_ext_info: public SplM_field_info occurred also in the select list of this join 9. There are defined some keys usable for ref access of fields from C with available statistics. + 10. The select doesn't use WITH ROLLUP (This limitation can probably be + lifted) @retval true if the answer is positive @@ -326,7 +328,8 @@ bool JOIN::check_for_splittable_materialized() (unit->first_select()->next_select()) || // !(3) (derived->prohibit_cond_pushdown) || // !(4) (derived->is_recursive_with_table()) || // !(5) - (table_count == 0 || const_tables == top_join_tab_count)) // !(6) + (table_count == 0 || const_tables == top_join_tab_count) || // !(6) + rollup.state != ROLLUP::STATE_NONE) // (10) return false; if (group_list) // (7.1) {

1 0

[Commits] bbc41062268: MDEV-27382: OFFSET is ignored when combined with DISTINCT
by psergey 13 Jan '22

13 Jan '22

revision-id: bbc410622680ac2dbb543104b67c2cecbc5782a1 (mariadb-10.5.13-47-gbbc41062268) parent(s): 6831b3f2a0fd656fb41dd9df5f141431988448f1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-13 15:53:44 +0300 message: MDEV-27382: OFFSET is ignored when combined with DISTINCT A query in form SELECT DISTINCT expr_that_is_inferred_to_be_const LIMIT 0 OFFSET n produces one row when it should produce none. The issue was in JOIN_TAB::remove_duplicates() in the piece of logic that tried to avoid duplicate removal for such cases but didn't account for possible "LIMIT 0". Fixed in two places: - Make JOIN::optimize_inner be able to infer "Zero limit" for "LIMIT 0 OFFSET some_non_zero_value" (in addition to just "LIMIT 0") - Make JOIN_TAB::remove_duplicates not apply its optimization for cases with non-zero OFFSET clause. --- mysql-test/main/distinct.result | 36 ++++++++++++++++++++++++++++++++++++ mysql-test/main/distinct.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 20 ++++++++++++++++---- 3 files changed, 90 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 2062ff0091d..0e31a174be7 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -1070,3 +1070,39 @@ UNION 1 drop table t1; End of 5.5 tests +# +# MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN +# +CREATE TABLE t1 ( +id int(7) NOT NULL AUTO_INCREMENT, +name varchar(50) DEFAULT NULL, +primary key (id) +); +INSERT INTO t1 VALUES (1, 'Reed'), (10, 'no-child'); +CREATE TABLE t2 ( +id int(11) NOT NULL AUTO_INCREMENT, +parent_id int(7) NOT NULL, +name varchar(100) DEFAULT NULL, +primary key (id), +key(parent_id) +); +INSERT INTO t2 VALUES (1, 1,'John'), (2, 2,'no-parent'); +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0; +id +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0 offset 5; +id +# Test the second part of the fix: just check that "LIMIT 0 OFFSET n" is +# handled in the same way as "LIMIT 0" +explain select * from t1 limit 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +explain select * from t1 limit 0 offset 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +drop table t1, t2; diff --git a/mysql-test/main/distinct.test b/mysql-test/main/distinct.test index da12c7273b2..32e189da98a 100644 --- a/mysql-test/main/distinct.test +++ b/mysql-test/main/distinct.test @@ -818,3 +818,41 @@ UNION drop table t1; --echo End of 5.5 tests + +--echo # +--echo # MDEV-27382: OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN +--echo # +CREATE TABLE t1 ( + id int(7) NOT NULL AUTO_INCREMENT, + name varchar(50) DEFAULT NULL, + primary key (id) +); +INSERT INTO t1 VALUES (1, 'Reed'), (10, 'no-child'); + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + parent_id int(7) NOT NULL, + name varchar(100) DEFAULT NULL, + primary key (id), + key(parent_id) +); + +INSERT INTO t2 VALUES (1, 1,'John'), (2, 2,'no-parent'); + +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0; + +SELECT DISTINCT p.id +FROM t1 p LEFT JOIN t2 c ON p.id = c.parent_id +WHERE p.id=1 +LIMIT 0 offset 5; + +--echo # Test the second part of the fix: just check that "LIMIT 0 OFFSET n" is +--echo # handled in the same way as "LIMIT 0" + +explain select * from t1 limit 0; +explain select * from t1 limit 0 offset 10; + +drop table t1, t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cb391314603..66879a39e95 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2149,11 +2149,14 @@ JOIN::optimize_inner() if (sel->having != having && having_value == Item::COND_OK) thd->change_item_tree(&sel->having, having); } + bool zero_limit= !unit->lim.get_select_limit() || + (unit->lim.get_select_limit() == + unit->lim.get_offset_limit()); + if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || - (!unit->lim.get_select_limit() && - !(select_options & OPTION_FOUND_ROWS))) + (zero_limit && !(select_options & OPTION_FOUND_ROWS))) { /* Impossible cond */ - if (unit->lim.get_select_limit()) + if (!zero_limit) { DBUG_PRINT("info", (having_value == Item::COND_FALSE ? "Impossible HAVING" : "Impossible WHERE")); @@ -24304,7 +24307,16 @@ JOIN_TAB::remove_duplicates() field_count++; } - if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having) + /* + If the select list does not have any non-constant items, then all rows + are identical. Adjust the LIMIT to just produce the first row. + Exceptions to this are: + - SQL_CALC_FOUND_ROWS + - when HAVING caluse is present (we'll need to check it) + - when there's a non-zero OFFSET clause + */ + if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having && + !join->unit->lim.get_offset_limit()) { // only const items with no OPTION_FOUND_ROWS join->unit->lim.set_single_row(); // Only send first row DBUG_RETURN(false);

1 0

[Commits] 21a810253c6: Update test results after the two last csets
by psergey 12 Jan '22

12 Jan '22

revision-id: 21a810253c6d120e75c421267afa913b5c21be3b (mariadb-10.6.1-351-g21a810253c6) parent(s): e9541a81a1383cdf64a19c71791bc83f7ef25fcc author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-12 19:49:39 +0300 message: Update test results after the two last csets --- mysql-test/main/selectivity_innodb.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 6e6517f7e08..8db83c6bcaf 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1257,8 +1257,8 @@ EXPLAIN EXTENDED SELECT * FROM language, country, continent WHERE country_group = lang_group AND lang_group IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE country ALL NULL NULL NULL NULL 2 0.00 Using where -1 SIMPLE language ALL NULL NULL NULL NULL 6 0.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE country ALL NULL NULL NULL NULL 2 50.00 Using where +1 SIMPLE language ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (flat, BNL join) 1 SIMPLE continent ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`language`.`lang_group` = `test`.`country`.`country_group` and `test`.`country`.`country_group` is null

1 0

[Commits] e9541a81a13: Updates for the prev cset:
by psergey 12 Jan '22

12 Jan '22

revision-id: e9541a81a1383cdf64a19c71791bc83f7ef25fcc (mariadb-10.6.1-350-ge9541a81a13) parent(s): 9a86900b3534d2d2bf112c32d7a7c0a3d3a1e46e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-12 18:21:59 +0300 message: Updates for the prev cset: - Fix compilation on windows. - selectivity.result update, caused by "don't return selectivity of 0.0" logic in records_in_column_ranges. --- mysql-test/main/selectivity.result | 4 ++-- sql/opt_range.cc | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index d559d3c39f7..003b91a6a28 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1245,8 +1245,8 @@ EXPLAIN EXTENDED SELECT * FROM language, country, continent WHERE country_group = lang_group AND lang_group IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE country ALL NULL NULL NULL NULL 2 0.00 Using where -1 SIMPLE language ALL NULL NULL NULL NULL 6 0.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE country ALL NULL NULL NULL NULL 2 50.00 Using where +1 SIMPLE language ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (flat, BNL join) 1 SIMPLE continent ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`language`.`lang_group` = `test`.`country`.`country_group` and `test`.`country`.`country_group` is null diff --git a/sql/opt_range.cc b/sql/opt_range.cc index bb5043eecd7..37cf054dfb0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3279,7 +3279,7 @@ double records_in_column_ranges(PARAM *param, uint idx, total_rows += rows; } if (total_rows == 0) - total_rows= MY_MIN(1, param->table->stat_records()); + total_rows= MY_MIN(1, rows2double(param->table->stat_records())); return total_rows; }

1 0

[Commits] 9a86900b353: MDEV-26901: Estimation for filtered rows less precise ... #4
by psergey 11 Jan '22

11 Jan '22

revision-id: 9a86900b3534d2d2bf112c32d7a7c0a3d3a1e46e (mariadb-10.6.1-349-g9a86900b353) parent(s): bdb90055c02a348e224574bab8113e76c5105a65 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-11 17:09:55 +0300 message: MDEV-26901: Estimation for filtered rows less precise ... #4 In Histogram_json_hb::point_selectivity(), do return selectivity of 0.0 when the histogram says so. The logic of "Do not return 0.0 estimate as it causes a multiply-by-zero meltdown in cost and cardinality calculations" is moved into records_in_column_ranges() where it is one *once* per column pair (as opposed to doing once per range, which can cause the error to add-up to large number when there are many ranges) --- mysql-test/main/statistics_json.result | 20 ++++++++++++++++++++ mysql-test/main/statistics_json.test | 16 ++++++++++++++++ sql/opt_histogram_json.cc | 2 +- 3 files changed, 37 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 600b2ddd4ee..9eb8cf87c44 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -8264,3 +8264,23 @@ ANALYZE SELECT * FROM t1 WHERE f > '00:01:00'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 50.00 50.00 Using where drop table t1; +# +# MDEV-26901: Estimation for filtered rows less precise ... #4 +# +create table t1 (f int); +insert into t1 values +(7),(5),(0),(5),(112),(9),(9),(7),(5),(9), +(1),(7),(0),(6),(6),(2),(1),(6),(169),(7); +select f from t1 where f in (77, 1, 144, 73, 14, 12); +f +1 +1 +set histogram_type= JSON_HB; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 10.00 10.00 Using where +drop table t1; diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index 93a526dbfb0..0a1b886f8fd 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -429,3 +429,19 @@ ANALYZE TABLE t1 PERSISTENT FOR ALL; ANALYZE SELECT * FROM t1 WHERE f > '00:01:00'; drop table t1; +--echo # +--echo # MDEV-26901: Estimation for filtered rows less precise ... #4 +--echo # +create table t1 (f int); +insert into t1 values + (7),(5),(0),(5),(112),(9),(9),(7),(5),(9), + (1),(7),(0),(6),(6),(2),(1),(6),(169),(7); + +select f from t1 where f in (77, 1, 144, 73, 14, 12); + +set histogram_type= JSON_HB; +analyze table t1 persistent for all; + +analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); +drop table t1; + diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 4305737df1c..73284025511 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -921,7 +921,7 @@ double Histogram_json_hb::point_selectivity(Field *field, key_range *endpoint, The bucket has a single value and it doesn't match! Return a very small value. */ - sel= 1.0 / total_rows; + sel= 0.0; } else {

1 0

[Commits] bdb90055c02: MDEV-27229: Estimation for filtered rows less precise ... #5
by psergey 11 Jan '22

11 Jan '22

revision-id: bdb90055c02a348e224574bab8113e76c5105a65 (mariadb-10.6.1-348-gbdb90055c02) parent(s): bd3930075b34d576dcebe14037d1d654c9fa8232 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-11 16:58:51 +0300 message: MDEV-27229: Estimation for filtered rows less precise ... #5 Followup: remove this line from get_column_range_cardinality() set_if_bigger(res, col_stats->get_avg_frequency()); and make sure it is only used with the binary histograms. For JSON histograms, it makes the estimates unnecessarily imprecise. --- mysql-test/main/statistics_json.result | 17 +++++++++++++---- mysql-test/main/statistics_json.test | 6 +++--- sql/opt_histogram_json.cc | 2 +- sql/opt_histogram_json.h | 2 +- sql/opt_range.cc | 5 ++++- sql/sql_statistics.cc | 13 +++++++++---- sql/sql_statistics.h | 4 ++-- 7 files changed, 33 insertions(+), 16 deletions(-) diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index 5de350fb21d..600b2ddd4ee 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -4644,7 +4644,7 @@ Warnings: Note 1003 select `test`.`t1_json`.`a` AS `a` from `test`.`t1_json` where `test`.`t1_json`.`a` < 'b-1a' analyze select * from t1_json where a > 'zzzzzzzzz'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 10.00 0.00 Using where +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 0.00 0.00 Using where drop table ten; UPDATE mysql.column_stats SET histogram='["not-what-you-expect"]' WHERE table_name='t1_json'; @@ -4752,7 +4752,7 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f 1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 1.98 1.98 Using where explain extended select * from t2 where city < 'Lagos'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 101 50.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 1.98 Using where Warnings: Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` < 'Lagos' drop table t1_bin; @@ -8164,7 +8164,7 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 33.00 33.00 Using where analyze select * from t1 where a < 0; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 1.47 0.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 1.00 0.00 Using where analyze select * from t1 where a > 0; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 67.00 67.00 Using where @@ -8189,7 +8189,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK analyze select * from t2 where a < 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 1011 1011.00 8.33 0.10 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1011 1011.00 0.10 0.10 Using where analyze select * from t2 where a =100; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 1011 1011.00 0.10 0.10 Using where @@ -8237,6 +8237,15 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f analyze select COUNT(*) FROM t1 WHERE a >='bar'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 100.00 100.00 Using where +analyze select COUNT(*) FROM t1 WHERE a < 'aaa'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 0.50 0.00 Using where +analyze select COUNT(*) FROM t1 WHERE a <='aaa'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 0.50 0.00 Using where +analyze select COUNT(*) FROM t1 WHERE a < 'bar'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 0.50 0.00 Using where analyze select COUNT(*) FROM t1 WHERE a <='bar'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 50.00 50.00 Using where diff --git a/mysql-test/main/statistics_json.test b/mysql-test/main/statistics_json.test index 7ec6c691a99..93a526dbfb0 100644 --- a/mysql-test/main/statistics_json.test +++ b/mysql-test/main/statistics_json.test @@ -409,9 +409,9 @@ analyze select COUNT(*) FROM t1 WHERE a > 'bar'; analyze select COUNT(*) FROM t1 WHERE a >='bar'; # Can enable these after get_avg_frequency issue is resolved: -# analyze select COUNT(*) FROM t1 WHERE a < 'aaa'; -# analyze select COUNT(*) FROM t1 WHERE a <='aaa'; -# analyze select COUNT(*) FROM t1 WHERE a < 'bar'; +analyze select COUNT(*) FROM t1 WHERE a < 'aaa'; +analyze select COUNT(*) FROM t1 WHERE a <='aaa'; +analyze select COUNT(*) FROM t1 WHERE a < 'bar'; analyze select COUNT(*) FROM t1 WHERE a <='bar'; diff --git a/sql/opt_histogram_json.cc b/sql/opt_histogram_json.cc index 7c037183f41..4305737df1c 100644 --- a/sql/opt_histogram_json.cc +++ b/sql/opt_histogram_json.cc @@ -962,7 +962,7 @@ std::string& Histogram_json_hb::get_end_value(int idx) */ double Histogram_json_hb::range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp) + key_range *max_endp, double avg_sel) { double min, max; diff --git a/sql/opt_histogram_json.h b/sql/opt_histogram_json.h index 9e32e70f7fc..48e9a29c8a5 100644 --- a/sql/opt_histogram_json.h +++ b/sql/opt_histogram_json.h @@ -129,7 +129,7 @@ class Histogram_json_hb : public Histogram_base double avg_selection, double total_rows) override; double range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp) override; + key_range *max_endp, double avg_sel) override; void set_json_text(ulonglong sz, const char *json_text_arg, size_t json_text_len) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 06063cb9ae1..bb5043eecd7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3277,7 +3277,10 @@ double records_in_column_ranges(PARAM *param, uint idx, break; } total_rows += rows; - } + } + if (total_rows == 0) + total_rows= MY_MIN(1, param->table->stat_records()); + return total_rows; } diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 6b3ea834628..fc8e38173c7 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3910,10 +3910,13 @@ double get_column_range_cardinality(Field *field, if (col_stats->min_max_values_are_provided()) { Histogram_base *hist= col_stats->histogram; + double avg_frequency= col_stats->get_avg_frequency(); double sel; if (hist && hist->is_usable(thd)) { - sel= hist->range_selectivity(field, min_endp, max_endp); + sel= hist->range_selectivity(field, min_endp, max_endp, + avg_frequency / col_non_nulls); + res= col_non_nulls * sel; } else { @@ -3938,9 +3941,9 @@ double get_column_range_cardinality(Field *field, max_mp_pos= 1.0; sel = (max_mp_pos - min_mp_pos); + res= col_non_nulls * sel; + set_if_bigger(res, avg_frequency); } - res= col_non_nulls * sel; - set_if_bigger(res, col_stats->get_avg_frequency()); } else res= col_non_nulls; @@ -4076,7 +4079,8 @@ double Histogram_binary::point_selectivity(Field *field, key_range *endpoint, double Histogram_binary::range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp) + key_range *max_endp, + double avg_sel) { double sel, min_mp_pos, max_mp_pos; Column_statistics *col_stats= field->read_stats; @@ -4105,6 +4109,7 @@ double Histogram_binary::range_selectivity(Field *field, uint max= find_bucket(max_mp_pos, FALSE); sel= bucket_sel * (max - min + 1); + set_if_bigger(sel, avg_sel); return sel; } diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 14877417072..1950f3268a7 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -191,7 +191,7 @@ class Histogram_base double avg_selectivity, double total_rows)=0; virtual double range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp)=0; + key_range *max_endp, double avg_sel)=0; /* Legacy: return the size of the histogram on disk. @@ -353,7 +353,7 @@ class Histogram_binary : public Histogram_base } double range_selectivity(Field *field, key_range *min_endp, - key_range *max_endp) override; + key_range *max_endp, double avg_sel) override; /* Estimate selectivity of "col=const" using a histogram

1 0

[Commits] 5101514e269: MDEV-27426: Wrong result upon query using index_merge with DESC key
by psergey 10 Jan '22

10 Jan '22

revision-id: 5101514e2697625c90ba053b8ae9635a32c86177 (mariadb-10.6.1-270-g5101514e269) parent(s): 49b38c82a8d6b1e745704938bdf5fda82bbb0916 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-10 16:19:03 +0300 message: MDEV-27426: Wrong result upon query using index_merge with DESC key Make QUICK_RANGE_SELECT::cmp_next() aware of reverse-ordered key parts. (QUICK_RANGE_SELECT::cmp_prev() uses key_cmp() and so it already works correctly) --- mysql-test/main/desc_index_range.result | 10 ++++++++++ mysql-test/main/desc_index_range.test | 10 ++++++++++ sql/opt_range.cc | 9 +++++---- 3 files changed, 25 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result index 244659e3f48..edf13010829 100644 --- a/mysql-test/main/desc_index_range.result +++ b/mysql-test/main/desc_index_range.result @@ -179,3 +179,13 @@ json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) ] drop table t1; set optimizer_trace=default; +# +# MDEV-27426: Wrong result upon query using index_merge with DESC key +# +CREATE OR REPLACE TABLE t1 (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); +SELECT * FROM t1 WHERE pk > 10 OR a > 0; +pk a b +1 4 5 +2 9 6 +DROP TABLE t1; diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test index bcb9ce83318..2f3d36c8305 100644 --- a/mysql-test/main/desc_index_range.test +++ b/mysql-test/main/desc_index_range.test @@ -86,3 +86,13 @@ from information_schema.optimizer_trace; drop table t1; set optimizer_trace=default; + +--echo # +--echo # MDEV-27426: Wrong result upon query using index_merge with DESC key +--echo # + +CREATE OR REPLACE TABLE t1 (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); + +SELECT * FROM t1 WHERE pk > 10 OR a > 0; +DROP TABLE t1; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 7b70dca86a6..ab734c6ed11 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -13076,24 +13076,25 @@ int QUICK_RANGE_SELECT::cmp_next(QUICK_RANGE *range_arg) key+= store_length, key_part++) { int cmp; + bool reverse= MY_TEST(key_part->flag & HA_REVERSE_SORT); store_length= key_part->store_length; if (key_part->null_bit) { if (*key) { if (!key_part->field->is_null()) - return 1; + return reverse ? 0 : 1; continue; } else if (key_part->field->is_null()) - return 0; + return reverse ? 1 : 0; key++; // Skip null byte store_length--; } if ((cmp=key_part->field->key_cmp(key, key_part->length)) < 0) - return 0; + return reverse ? 1 : 0; if (cmp > 0) - return 1; + return reverse ? 0 : 1; } return (range_arg->flag & NEAR_MAX) ? 1 : 0; // Exact match }

1 0

[Commits] 8265d6d: MDEV-22846 Server crashes in handler_index_cond_check on SELECT
by IgorBabaev 07 Jan '22

07 Jan '22

revision-id: 8265d6d9f632add97f0d13cdfca7188164ba8f2c (mariadb-10.4.22-60-g8265d6d) parent(s): c18896f9c1ce6e4b9a8519a2d5155698d82ae45a author: Igor Babaev committer: Igor Babaev timestamp: 2022-01-07 11:52:25 -0800 message: MDEV-22846 Server crashes in handler_index_cond_check on SELECT If the optimizer decides to rewrites a NOT IN predicand of the form outer_expr IN (SELECT inner_col FROM ... WHERE subquery_where) into the EXISTS subquery EXISTS (SELECT 1 FROM ... WHERE subquery_where AND (outer_expr=inner_col OR inner_col IS NULL)) then the pushed equality predicate outer_expr=inner_col can be used for ref[or_null] access if inner_col is a reference to an indexed column. In this case if there is a selective range condition over this column then a Rowid filter may be employed coupled the with ref[or_null] access. The filter is 'pushed' into the engine and in InnoDB currently it cannot be used with index look-ups by primary key. The ref[or_null] access can be used only when outer_expr is not NULL. Otherwise the original predicand is evaluated to TRUE only if the result set returned by the query SELECT 1 FROM ... WHERE subquery_where is empty. When performing this evaluation the executor switches to the table scan by primary key. Before this patch the pushed filter still remained marked as active and the engine tried to apply the filter. This was incorrect and in InnoDB this attempt to use the filter led to an assertion failure. This patch fixes the problem by disabling usage of the filter when outer_expr is evaluated to NULL. --- mysql-test/main/rowid_filter_innodb.result | 42 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 30 +++++++++++++++++++++ sql/handler.h | 26 ++++++++++++++++++ sql/item_subselect.cc | 4 +++ 4 files changed, 102 insertions(+) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index af7ff32..faa9714 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2919,3 +2919,45 @@ set optimizer_switch=@save_optimizer_switch; set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; +# +# MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +# +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'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 +WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); +pk c1 +15 o +16 x +19 t +35 k +36 h +43 h +53 l +62 a +71 NULL +128 y +129 NULL +133 NULL +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 +WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 15 100.00 Using where +2 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 6 (33%) 33.33 Using where; Full scan on NULL key; Using rowid filter +2 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 15 100.00 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`,`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; +# End of 10.4 tests diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 74349b8..d121405 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -534,3 +534,33 @@ set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; + +--echo # +--echo # MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +--echo # + + +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'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); + +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; + +let $q= +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 + WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2; + +--echo # End of 10.4 tests diff --git a/sql/handler.h b/sql/handler.h index 815641b..71b30ab 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3147,6 +3147,9 @@ class handler :public Sql_alloc Rowid_filter *pushed_rowid_filter; /* true when the pushed rowid filter has been already filled */ bool rowid_filter_is_active; + /* Used for disabling/enabling pushed_rowid_filter */ + Rowid_filter *save_pushed_rowid_filter; + bool save_rowid_filter_is_active; Discrete_interval auto_inc_interval_for_cur_row; /** @@ -3214,6 +3217,8 @@ class handler :public Sql_alloc pushed_idx_cond_keyno(MAX_KEY), pushed_rowid_filter(NULL), rowid_filter_is_active(0), + save_pushed_rowid_filter(NULL), + save_rowid_filter_is_active(false), auto_inc_intervals_count(0), m_psi(NULL), set_top_table_fields(FALSE), top_table(0), top_table_field(0), top_table_fields(0), @@ -4258,6 +4263,27 @@ class handler :public Sql_alloc rowid_filter_is_active= false; } + virtual void disable_pushed_rowid_filter() + { + DBUG_ASSERT(pushed_rowid_filter != NULL && + save_pushed_rowid_filter == NULL); + save_pushed_rowid_filter= pushed_rowid_filter; + if (rowid_filter_is_active) + save_rowid_filter_is_active= rowid_filter_is_active; + pushed_rowid_filter= NULL; + rowid_filter_is_active= false; + } + + virtual void enable_pushed_rowid_filter() + { + DBUG_ASSERT(save_pushed_rowid_filter != NULL && + pushed_rowid_filter == NULL); + pushed_rowid_filter= save_pushed_rowid_filter; + if (save_rowid_filter_is_active) + rowid_filter_is_active= true; + save_pushed_rowid_filter= NULL; + } + virtual bool rowid_filter_push(Rowid_filter *rowid_filter) { return true; } /* Needed for partition / spider */ diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 82b4096..56ab0f6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -4026,6 +4026,8 @@ int subselect_single_select_engine::exec() tab->save_read_record= tab->read_record.read_record_func; tab->read_record.read_record_func= rr_sequential; tab->read_first_record= read_first_record_seq; + if (tab->rowid_filter) + tab->table->file->disable_pushed_rowid_filter(); tab->read_record.thd= join->thd; tab->read_record.ref_length= tab->table->file->ref_length; tab->read_record.unlock_row= rr_unlock_row; @@ -4046,6 +4048,8 @@ int subselect_single_select_engine::exec() tab->read_record.ref_length= 0; tab->read_first_record= tab->save_read_first_record; tab->read_record.read_record_func= tab->save_read_record; + if (tab->rowid_filter) + tab->table->file->enable_pushed_rowid_filter(); } executed= 1; if (!(uncacheable() & ~UNCACHEABLE_EXPLAIN) &&

1 0

[Commits] 7a49bf0: MDEV-22846 Server crashes in handler_index_cond_check on SELECT
by IgorBabaev 07 Jan '22

07 Jan '22

revision-id: 7a49bf04fb9aa4fc364ace4a6d71e4b68a99d9ce (mariadb-10.4.11-238-g7a49bf0) parent(s): bf2a244406c36cd12bc53f9a30c8a2cab191f235 author: Igor Babaev committer: Igor Babaev timestamp: 2022-01-06 21:59:34 -0800 message: MDEV-22846 Server crashes in handler_index_cond_check on SELECT If the optimizer decides to rewrites a NOT IN predicand of the form outer_expr IN (SELECT inner_col FROM ... WHERE subquery_where) into the EXISTS subquery EXISTS (SELECT 1 FROM ... WHERE subquery_where AND (outer_expr=inner_col OR inner_col IS NULL)) then the pushed equality predicate outer_expr=inner_col can be used for ref[or_null] access if inner_col is a reference to an indexed column. In this case if there is a selective range condition over this column then a Rowid filter may be employed coupled the with ref[or_null] access. The filter is 'pushed' into the engine and in InnoDB currently it cannot be used with index look-ups by primary key. The ref[or_null] access can be used only when outer_expr is not NULL. Otherwise the original predicand is evaluated to TRUE only if the result set returned by the query SELECT 1 FROM ... WHERE subquery_where is empty. When performing this evaluation the executor switches to the table scan by primary key. Before this patch the pushed filter still remained marked as active and the engine tried to apply the filter. This was incorrect and in InnoDB this attempt to use the filter led to an assertion failure. This patch fixes the problem by disabling usage of the filter when outer_expr is evaluated to NULL. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/rowid_filter_innodb.result | 42 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 30 +++++++++++++++++++++ sql/handler.h | 26 ++++++++++++++++++ sql/item_subselect.cc | 4 +++ 4 files changed, 102 insertions(+) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 05d97b7..fcdaa94 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2913,3 +2913,45 @@ set optimizer_switch=@save_optimizer_switch; set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; +# +# MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +# +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'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 +WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); +pk c1 +15 o +16 x +19 t +35 k +36 h +43 h +53 l +62 a +71 NULL +128 y +129 NULL +133 NULL +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 +WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 15 100.00 Using where +2 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 6 (33%) 33.33 Using where; Full scan on NULL key; Using rowid filter +2 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 15 100.00 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`,`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; +# End of 10.4 tests diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 74349b8..d121405 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -534,3 +534,33 @@ set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; + +--echo # +--echo # MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +--echo # + + +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'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); + +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; + +let $q= +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 + WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2; + +--echo # End of 10.4 tests diff --git a/sql/handler.h b/sql/handler.h index cdcb117..02e33af 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3095,6 +3095,9 @@ class handler :public Sql_alloc /* Rowid filter pushed into the engine */ Rowid_filter *pushed_rowid_filter; + /* Used for disabling/enabling pushed_rowid_filter */ + Rowid_filter *save_pushed_rowid_filter; + bool save_rowid_filter_is_active; /* true when the pushed rowid filter has been already filled */ bool rowid_filter_is_active; @@ -3167,6 +3170,8 @@ class handler :public Sql_alloc pushed_idx_cond(NULL), pushed_idx_cond_keyno(MAX_KEY), pushed_rowid_filter(NULL), + save_pushed_rowid_filter(NULL), + save_rowid_filter_is_active(false), rowid_filter_is_active(0), auto_inc_intervals_count(0), m_psi(NULL), set_top_table_fields(FALSE), top_table(0), @@ -4224,6 +4229,27 @@ class handler :public Sql_alloc rowid_filter_is_active= false; } + virtual void disable_pushed_rowid_filter() + { + DBUG_ASSERT(pushed_rowid_filter != NULL && + save_pushed_rowid_filter == NULL); + save_pushed_rowid_filter= pushed_rowid_filter; + if (rowid_filter_is_active) + save_rowid_filter_is_active= rowid_filter_is_active; + pushed_rowid_filter= NULL; + rowid_filter_is_active= false; + } + + virtual void enable_pushed_rowid_filter() + { + DBUG_ASSERT(save_pushed_rowid_filter != NULL && + pushed_rowid_filter == NULL); + pushed_rowid_filter= save_pushed_rowid_filter; + if (save_rowid_filter_is_active) + rowid_filter_is_active= true; + save_pushed_rowid_filter= NULL; + } + virtual bool rowid_filter_push(Rowid_filter *rowid_filter) { return true; } /* Needed for partition / spider */ diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c50f87c..8204586 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3924,6 +3924,8 @@ int subselect_single_select_engine::exec() tab->save_read_record= tab->read_record.read_record_func; tab->read_record.read_record_func= rr_sequential; tab->read_first_record= read_first_record_seq; + if (tab->rowid_filter) + tab->table->file->disable_pushed_rowid_filter(); tab->read_record.thd= join->thd; tab->read_record.ref_length= tab->table->file->ref_length; tab->read_record.unlock_row= rr_unlock_row; @@ -3944,6 +3946,8 @@ int subselect_single_select_engine::exec() tab->read_record.ref_length= 0; tab->read_first_record= tab->save_read_first_record; tab->read_record.read_record_func= tab->save_read_record; + if (tab->rowid_filter) + tab->table->file->enable_pushed_rowid_filter(); } executed= 1; if (!(uncacheable() & ~UNCACHEABLE_EXPLAIN) &&

1 0