[Commits] d4aae0b47b3: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values
by psergey 03 Jan '22
by psergey 03 Jan '22
03 Jan '22
revision-id: d4aae0b47b396a142fd413ff5f5ac5df1ff3c560 (mariadb-10.3.31-70-gd4aae0b47b3)
parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-04 00:01:52 +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.
---
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] f7648d8ef74: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values
by psergey 03 Jan '22
by psergey 03 Jan '22
03 Jan '22
revision-id: f7648d8ef74016a2d07433f38d12e3c22bdef4a9 (mariadb-10.3.31-70-gf7648d8ef74)
parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-01-03 23:53:45 +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.
---
mysql-test/main/derived_split_innodb.result | 88 ++++++++++++++++
mysql-test/main/derived_split_innodb.test | 149 ++++++++++++++++++++++++++++
sql/opt_split.cc | 5 +-
sql/sql_select.cc | 34 +++++++
4 files changed, 275 insertions(+), 1 deletion(-)
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index 7ea3b689f23..fcdba0d7d07 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -234,4 +234,92 @@ 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 on LEFT JOIN on INDEXED values
+#
+CREATE TABLE t1 (
+the_date date NOT NULL
+, PRIMARY KEY ( the_date )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+INSERT INTO t1 VALUES ('2021-08-10'),('2021-08-11'),('2021-08-12'),('2021-08-13');
+CREATE TABLE t2 (
+the_date date NOT NULL,
+ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
+cco_stk_ttl int,
+PRIMARY KEY ( the_date , ptn_id )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+INSERT INTO t2 VALUES
+('2021-08-11','10002',NULL),('2021-08-11','10741',128),
+('2021-08-11','11001',4),('2021-08-11','11003',2048),
+('2021-08-12','10001',4096),('2021-08-12','10002',1),
+('2021-08-12','10429',256),('2021-08-12','10499',16),
+('2021-08-12','10580',8),('2021-08-12','10740',32),
+('2021-08-12','10741',64),('2021-08-12','10771',512),
+('2021-08-12','11001',2),('2021-08-12','11003',1024);
+CREATE TABLE t3 (
+id int NOT NULL AUTO_INCREMENT,
+nsc_id char(5) NOT NULL,
+dept_id char(4) NOT NULL,
+district_id char(3) NOT NULL,
+region_id char(2) NOT NULL,
+PRIMARY KEY ( id ),
+UNIQUE KEY dept_district ( dept_id , district_id ),
+KEY region_id ( dept_id , region_id )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+INSERT INTO t3 VALUES
+(1,'MMD','ADVB','10','1'), (2,'MMD','ADVB','11','1'),
+(3,'MMD','ADVB','21','2'),(4,'MMD','ADVB','22','2');
+CREATE TABLE t4 (
+dept_id char(4) CHARACTER SET utf8mb3 NOT NULL,
+ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL,
+district_id char(3) CHARACTER SET utf8mb3 NOT NULL DEFAULT '0',
+nsc_id char(5) CHARACTER SET utf8mb3 NOT NULL
+, PRIMARY KEY (ptn_id , dept_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+INSERT INTO t4 VALUES
+('ADVB','10001','10','MMD'),('ADVB','10002','10','MMD'),
+('ADVB','10003','10','MMD'),('ADVB','10429','22','MMD'),
+('ADVB','10740','21','MMD'),('ADVB','10741','21','MMD'),
+('ADVB','10771','23','MMD'),('ADVB','11001','11','MMD'),
+('ADVB','11002','11','MMD');
+CREATE TABLE `t10` (
+`the_date` date NOT NULL,
+`dept_id` char(4) CHARACTER SET utf8mb4 ,
+`org_id` varchar(3) CHARACTER SET utf8mb4 ,
+`district_id` char(3) CHARACTER SET utf8mb4 ,
+`region_id` char(2) CHARACTER SET utf8mb4
+);
+insert into t10
+SELECT cal.the_date ,
+org.dept_id ,
+coalesce(org.district_id, org.region_id, 'MMD') AS org_id ,
+org.district_id ,
+org.region_id
+FROM t1 cal
+CROSS JOIN t3 org
+WHERE org.nsc_id = 'MMD'
+ AND org.dept_id IN ('ADVB')
+AND cal.the_date = '2021-08-12'
+GROUP BY cal.the_date,
+org.dept_id,
+org.region_id,
+org.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL
+OR org.dept_id IS NULL);
+explain $q;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY org2 ALL NULL NULL NULL NULL 7
+1 PRIMARY <derived2> ref key0 key0 43 test.org2.the_date,test.org2.dept_id,test.org2.region_id,test.org2.district_id 2 Using where
+2 LATERAL DERIVED sub ref PRIMARY PRIMARY 3 test.org2.the_date 1 Using temporary; Using filesort
+2 LATERAL DERIVED org ref PRIMARY PRIMARY 15 test.sub.ptn_id 1 Using where
+2 LATERAL DERIVED dis eq_ref dept_district,region_id dept_district 28 const,func 1 Using index condition; Using where
+$q;
+the_date org_id dept_id cco_stk_ttl
+2021-08-12 10 ADVB 4097
+2021-08-12 11 ADVB 2
+2021-08-12 1 ADVB 4099
+2021-08-12 21 ADVB 96
+2021-08-12 22 ADVB 256
+2021-08-12 2 ADVB 352
+2021-08-12 MMD ADVB 4451
+drop table t1,t2,t3,t4,t10;
# 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..dc283792c13 100644
--- a/mysql-test/main/derived_split_innodb.test
+++ b/mysql-test/main/derived_split_innodb.test
@@ -186,4 +186,153 @@ 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 on LEFT JOIN on INDEXED values
+--echo #
+
+CREATE TABLE t1 (
+ the_date date NOT NULL
+ , PRIMARY KEY ( the_date )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+INSERT INTO t1 VALUES ('2021-08-10'),('2021-08-11'),('2021-08-12'),('2021-08-13');
+
+CREATE TABLE t2 (
+ the_date date NOT NULL,
+ ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
+ cco_stk_ttl int,
+ PRIMARY KEY ( the_date , ptn_id )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+INSERT INTO t2 VALUES
+('2021-08-11','10002',NULL),('2021-08-11','10741',128),
+('2021-08-11','11001',4),('2021-08-11','11003',2048),
+('2021-08-12','10001',4096),('2021-08-12','10002',1),
+('2021-08-12','10429',256),('2021-08-12','10499',16),
+('2021-08-12','10580',8),('2021-08-12','10740',32),
+('2021-08-12','10741',64),('2021-08-12','10771',512),
+('2021-08-12','11001',2),('2021-08-12','11003',1024);
+
+CREATE TABLE t3 (
+ id int NOT NULL AUTO_INCREMENT,
+ nsc_id char(5) NOT NULL,
+ dept_id char(4) NOT NULL,
+ district_id char(3) NOT NULL,
+ region_id char(2) NOT NULL,
+ PRIMARY KEY ( id ),
+ UNIQUE KEY dept_district ( dept_id , district_id ),
+ KEY region_id ( dept_id , region_id )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+INSERT INTO t3 VALUES
+(1,'MMD','ADVB','10','1'), (2,'MMD','ADVB','11','1'),
+(3,'MMD','ADVB','21','2'),(4,'MMD','ADVB','22','2');
+
+CREATE TABLE t4 (
+ dept_id char(4) CHARACTER SET utf8mb3 NOT NULL,
+ ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL,
+ district_id char(3) CHARACTER SET utf8mb3 NOT NULL DEFAULT '0',
+ nsc_id char(5) CHARACTER SET utf8mb3 NOT NULL
+ , PRIMARY KEY (ptn_id , dept_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+INSERT INTO t4 VALUES
+('ADVB','10001','10','MMD'),('ADVB','10002','10','MMD'),
+('ADVB','10003','10','MMD'),('ADVB','10429','22','MMD'),
+('ADVB','10740','21','MMD'),('ADVB','10741','21','MMD'),
+('ADVB','10771','23','MMD'),('ADVB','11001','11','MMD'),
+('ADVB','11002','11','MMD');
+
+CREATE TABLE `t10` (
+ `the_date` date NOT NULL,
+ `dept_id` char(4) CHARACTER SET utf8mb4 ,
+ `org_id` varchar(3) CHARACTER SET utf8mb4 ,
+ `district_id` char(3) CHARACTER SET utf8mb4 ,
+ `region_id` char(2) CHARACTER SET utf8mb4
+);
+
+insert into t10
+SELECT cal.the_date ,
+ org.dept_id ,
+ coalesce(org.district_id, org.region_id, 'MMD') AS org_id ,
+ org.district_id ,
+ org.region_id
+FROM t1 cal
+CROSS JOIN t3 org
+WHERE org.nsc_id = 'MMD'
+ AND org.dept_id IN ('ADVB')
+ AND cal.the_date = '2021-08-12'
+GROUP BY cal.the_date,
+ org.dept_id,
+ org.region_id,
+ org.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL
+ OR org.dept_id IS NULL);
+
+let $q=
+SELECT sql_no_cache org2.the_date ,
+ org2.org_id ,
+ org2.dept_id ,
+ msr. cco_stk_ttl
+FROM
+ t10 org2
+LEFT JOIN
+ ( SELECT sub.the_date ,
+ dis.dept_id ,
+ dis.region_id ,
+ dis.district_id ,
+ sum(sub.cco_stk_ttl) AS cco_stk_ttl
+ FROM t2 sub
+ JOIN t4 org ON org.ptn_id = sub.ptn_id
+ JOIN t3 dis ON dis.dept_id = org.dept_id
+ AND dis.district_id = org.district_id
+ WHERE dis.nsc_id = 'MMD'
+ AND dis.dept_id IN ('ADVB')
+ GROUP BY sub.the_date,
+ dis.dept_id,
+ dis.region_id,
+ dis.district_id WITH ROLLUP ) msr ON msr.the_date = org2.the_date
+AND msr.dept_id <=> org2.dept_id
+AND msr.region_id <=> org2.region_id
+AND msr.district_id <=> org2.district_id;
+
+evalp explain $q;
+evalp $q;
+
+drop table t1,t2,t3,t4,t10;
+
+--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)
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index fe02e7b44e4..0ea861987bd 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -17047,6 +17047,26 @@ const_expression_in_where(COND *cond, Item *comp_item, Field *comp_field,
Item *right_item= ((Item_func*) cond)->arguments()[1];
if (equal(left_item, comp_item, comp_field))
{
+ /*
+ If this condition
+ 1. Was injected by LATERAL DERIVED optimization,
+ 2. But is not used for ref access
+ then we ingore it. This is the same as what mmake_cond_for_table() does
+ when it is invoked from make_join_select().
+ */
+ if (func->functype() == Item_func::EQ_FUNC)
+ {
+ if (is_eq_cond_injected_for_split_opt((Item_func_eq*)func))
+ {
+ bool used_for_ref= false;
+ if (left_item->type() == Item::FIELD_ITEM &&
+ test_if_ref(func, (Item_field*)left_item, right_item))
+ used_for_ref= true;
+
+ if (!used_for_ref)
+ return 0;
+ }
+ }
if (test_if_equality_guarantees_uniqueness (left_item, right_item))
{
if (*const_item)
@@ -17057,6 +17077,20 @@ const_expression_in_where(COND *cond, Item *comp_item, Field *comp_field,
}
else if (equal(right_item, comp_item, comp_field))
{
+ /* Do the same as above */
+ if (func->functype() == Item_func::EQ_FUNC)
+ {
+ if (is_eq_cond_injected_for_split_opt((Item_func_eq*)func))
+ {
+ bool used_for_ref= false;
+ if (right_item->type() == Item::FIELD_ITEM &&
+ test_if_ref(func, (Item_field*)right_item, left_item))
+ used_for_ref= true;
+
+ if (!used_for_ref)
+ return 0;
+ }
+ }
if (test_if_equality_guarantees_uniqueness (right_item, left_item))
{
if (*const_item)
1
0
revision-id: 0a47e770743d0be073e262b43fd85a1ea5e3856e (mariadb-10.4.22-53-g0a47e770743)
parent(s): 03dff0fcf6ac5e12a76c8c680384ac8abd0c0f1d
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-23 12:03:09 +0300
message:
Fix typos in optimizer trace output
---
sql/sql_select.cc | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 35925c0cd44..cfd1fbb3275 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -11404,7 +11404,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
trace_const_cond.add("condition_on_constant_tables", const_cond);
if (const_cond->is_expensive())
{
- trace_const_cond.add("evalualted", "false")
+ trace_const_cond.add("evaluated", "false")
.add("cause", "expensive cond");
}
else
@@ -11417,7 +11417,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
if (!const_cond_result)
{
DBUG_PRINT("info",("Found impossible WHERE condition"));
- trace_const_cond.add("evalualted", "true")
+ trace_const_cond.add("evaluated", "true")
.add("found", "impossible where");
join->exec_const_cond= NULL;
DBUG_RETURN(1);
1
0
[Commits] 03dff0fcf6a: MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer
by psergey 23 Dec '21
by psergey 23 Dec '21
23 Dec '21
revision-id: 03dff0fcf6ac5e12a76c8c680384ac8abd0c0f1d (mariadb-10.4.22-52-g03dff0fcf6a)
parent(s): 4eec6b99e177a644650057f2c14d2f10ddd0ada4
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-23 12:00:05 +0300
message:
MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer
make_join_select() calls const_cond->val_int(). There are edge cases
where const_cond may have a not-yet optimized subquery.
(The subquery will have used_tables() covered by join->const_tables. It
will still have const_item()==false, so other parts of the optimizer
will not try to evaluate it. We should probably mark such subqueries
as constant but that is outside the scope of this MDEV)
---
mysql-test/main/opt_trace.result | 27 +++++++++++++++++++++------
mysql-test/main/opt_trace.test | 10 ++++++++++
sql/sql_select.cc | 6 +++++-
3 files changed, 36 insertions(+), 7 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 0e4477e1fd9..34683d037ea 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -2393,7 +2393,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a {
"best_join_order": ["t2", "t1"]
},
{
- "condition_on_constant_tables": "1"
+ "condition_on_constant_tables": "1",
+ "computing_condition": []
},
{
"attaching_conditions_to_tables": {
@@ -2550,7 +2551,8 @@ explain select * from t1 left join t2 on t2.a=t1.a {
"best_join_order": ["t1", "t2"]
},
{
- "condition_on_constant_tables": "1"
+ "condition_on_constant_tables": "1",
+ "computing_condition": []
},
{
"attaching_conditions_to_tables": {
@@ -2708,7 +2710,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
"best_join_order": ["t3", "t2", "t1"]
},
{
- "condition_on_constant_tables": "1"
+ "condition_on_constant_tables": "1",
+ "computing_condition": []
},
{
"attaching_conditions_to_tables": {
@@ -3021,7 +3024,8 @@ explain extended select * from t1 where a in (select pk from t10) {
"best_join_order": ["t1", "<subquery2>"]
},
{
- "condition_on_constant_tables": "1"
+ "condition_on_constant_tables": "1",
+ "computing_condition": []
},
{
"attaching_conditions_to_tables": {
@@ -4719,7 +4723,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"best_join_order": ["t1", "<subquery2>"]
},
{
- "condition_on_constant_tables": "1"
+ "condition_on_constant_tables": "1",
+ "computing_condition": []
},
{
"attaching_conditions_to_tables": {
@@ -7365,7 +7370,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
]
},
{
- "condition_on_constant_tables": "1"
+ "condition_on_constant_tables": "1",
+ "computing_condition": []
},
{
"attaching_conditions_to_tables": {
@@ -8632,5 +8638,14 @@ SELECT 'a\0' LIMIT 0 {
}
SET optimizer_trace=DEFAULT;
#
+# MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object
+#
+CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY;
+SET optimizer_trace=1;
+INSERT INTO t1 VALUES (0,0);
+SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d);
+a
+DROP TABLE t1;
+#
# End of 10.4 tests
#
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 9d4794855e0..855ce11aaf5 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -643,6 +643,16 @@ SELECT 'a\0' LIMIT 0;
SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace=DEFAULT;
+--echo #
+--echo # MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object
+--echo #
+
+CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY;
+SET optimizer_trace=1;
+INSERT INTO t1 VALUES (0,0);
+SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d);
+DROP TABLE t1;
+
--echo #
--echo # End of 10.4 tests
--echo #
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 27fc27f242a..35925c0cd44 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -11409,7 +11409,11 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
}
else
{
- const bool const_cond_result = const_cond->val_int() != 0;
+ bool const_cond_result;
+ {
+ Json_writer_array a(thd, "computing_condition");
+ const_cond_result= const_cond->val_int() != 0;
+ }
if (!const_cond_result)
{
DBUG_PRINT("info",("Found impossible WHERE condition"));
1
0
[Commits] 6450aab: MDEV-22846 Server crashes in handler_index_cond_check on SELECT
by IgorBabaev 23 Dec '21
by IgorBabaev 23 Dec '21
23 Dec '21
revision-id: 6450aab26c73d0522da40b2ece0552dc2113f7a4 (mariadb-10.4.11-238-g6450aab)
parent(s): bf2a244406c36cd12bc53f9a30c8a2cab191f235
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-12-22 18:57:19 -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 | 34 ++++++++++++++++++++++++
sql/handler.h | 16 ++++++++++++
sql/item_subselect.cc | 4 +++
4 files changed, 96 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..874f8d3 100644
--- a/mysql-test/main/rowid_filter_innodb.test
+++ b/mysql-test/main/rowid_filter_innodb.test
@@ -534,3 +534,37 @@ 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 #
+
+# set @stats.save= @@innodb_stats_persistent;
+# set global innodb_stats_persistent=on;
+
+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;
+
+# set global innodb_stats_persistent= @stats.save;
+
+--echo # End of 10.4 tests
diff --git a/sql/handler.h b/sql/handler.h
index cdcb117..0a429f1 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -3095,6 +3095,8 @@ 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;
/* true when the pushed rowid filter has been already filled */
bool rowid_filter_is_active;
@@ -3167,6 +3169,7 @@ class handler :public Sql_alloc
pushed_idx_cond(NULL),
pushed_idx_cond_keyno(MAX_KEY),
pushed_rowid_filter(NULL),
+ save_pushed_rowid_filter(NULL),
rowid_filter_is_active(0),
auto_inc_intervals_count(0),
m_psi(NULL), set_top_table_fields(FALSE), top_table(0),
@@ -4224,6 +4227,19 @@ class handler :public Sql_alloc
rowid_filter_is_active= false;
}
+ virtual void disable_pushed_rowid_filter()
+ {
+ save_pushed_rowid_filter= pushed_rowid_filter;
+ pushed_rowid_filter= NULL;
+ rowid_filter_is_active= false;
+ }
+
+ virtual void enable_pushed_rowid_filter()
+ {
+ pushed_rowid_filter= save_pushed_rowid_filter;
+ rowid_filter_is_active= true;
+ }
+
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
22 Dec '21
revision-id: 3027d0d7262159cca2de62d5d415825a7b4e485e (mariadb-10.6.1-260-g3027d0d7262)
parent(s): 3a2eadbcf938f3dd1548e57a381d773b0e24c97a
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-22 17:19:48 +0300
message:
MDEV-26996: Reverse-ordered indexes: improve print-out
When printing a range into optimizer trace, print DESC for columns
that are reverse-ordered, for example:
"(4) <= (key1 DESC) <= (2)"
---
mysql-test/main/desc_index_range.result | 14 +++++++-------
sql/opt_range.cc | 2 ++
2 files changed, 9 insertions(+), 7 deletions(-)
diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result
index feec5dc1720..244659e3f48 100644
--- a/mysql-test/main/desc_index_range.result
+++ b/mysql-test/main/desc_index_range.result
@@ -13,9 +13,9 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(6) <= (a) <= (6)",
- "(4) <= (a) <= (4)",
- "(2) <= (a) <= (2)"
+ "(6) <= (a DESC) <= (6)",
+ "(4) <= (a DESC) <= (4)",
+ "(2) <= (a DESC) <= (2)"
]
]
set optimizer_trace=default;
@@ -57,7 +57,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(8,50) <= (a,b)"
+ "(8,50) <= (a,b DESC)"
]
]
select * from t1 force index(ab) where a>=8 and b<=50;
@@ -104,7 +104,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(2,80) <= (a,b) <= (4,50)"
+ "(2,80) <= (a,b DESC) <= (4,50)"
]
]
select * from t1 where a between 2 and 4 and b between 50 and 80;
@@ -138,7 +138,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(4) <= (a) <= (2)"
+ "(4) <= (a DESC) <= (2)"
]
]
explain
@@ -151,7 +151,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(4,80) <= (a,b) <= (2,50)"
+ "(4,80) <= (a DESC,b DESC) <= (2,50)"
]
]
drop table t2;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index bbb7b3d87fa..7b70dca86a6 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -16634,6 +16634,8 @@ void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
else
out->append(STRING_WITH_LEN(","));
out->append(key_part->field->field_name);
+ if (key_part->key_part_flag & HA_REVERSE_SORT)
+ out->append(STRING_WITH_LEN(" DESC"));
}
else
break;
1
0
[Commits] 3a2eadbcf93: MDEV-26996: Reverse-ordered indexes: remove SEL_ARG::is_ascending
by psergey 22 Dec '21
by psergey 22 Dec '21
22 Dec '21
revision-id: 3a2eadbcf938f3dd1548e57a381d773b0e24c97a (mariadb-10.6.1-259-g3a2eadbcf93)
parent(s): 275b5fccefa3921cb3891e0c23af37c41bbe69a6
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-22 17:05:15 +0300
message:
MDEV-26996: Reverse-ordered indexes: remove SEL_ARG::is_ascending
Instead, Get the "is_ascending" value from the array of KEY_PART
structures that describes the [pseudo-]index that is being analyzed.
---
sql/item_geofunc.cc | 2 +-
sql/opt_range.cc | 76 +++++++++++++++++++++++++---------------------------
sql/opt_range.h | 55 +++++++++++++++++--------------------
sql/opt_range_mrr.cc | 27 ++++++++++---------
4 files changed, 77 insertions(+), 83 deletions(-)
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index a2a99bcdf8f..0fdcf9e94e2 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1084,7 +1084,7 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param,
field->get_key_image(str, key_part->length, key_part->image_type);
SEL_ARG *tree;
- if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str)))
+ if (!(tree= new (param->mem_root) SEL_ARG(field, str, str)))
DBUG_RETURN(0); // out of memory
switch (type) {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ae2b5060625..bbb7b3d87fa 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1879,7 +1879,6 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc()
max_flag=arg.max_flag;
maybe_flag=arg.maybe_flag;
maybe_null=arg.maybe_null;
- is_ascending= arg.is_ascending;
part=arg.part;
field=arg.field;
min_value=arg.min_value;
@@ -1905,10 +1904,9 @@ inline void SEL_ARG::make_root()
use_count=0; elements=1;
}
-SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
+SEL_ARG::SEL_ARG(Field *f, const uchar *min_value_arg,
const uchar *max_value_arg)
:min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()),
- is_ascending(is_asc),
elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg),
max_value((uchar*) max_value_arg), next(0),prev(0),
next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1)
@@ -1917,11 +1915,11 @@ SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
max_part_no= 1;
}
-SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
+SEL_ARG::SEL_ARG(Field *field_,uint8 part_,
uchar *min_value_, uchar *max_value_,
uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_)
:min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_),
- part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_),
+ part(part_),maybe_null(field_->real_maybe_null()),
elements(1),use_count(1),
field(field_), min_value(min_value_), max_value(max_value_),
next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1)
@@ -1941,8 +1939,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
class SEL_ARG_LE: public SEL_ARG
{
public:
- SEL_ARG_LE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_LE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
if (!field->real_maybe_null())
min_flag= NO_MIN_RANGE; // From start
@@ -1962,17 +1960,17 @@ class SEL_ARG_LT: public SEL_ARG_LE
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_LT(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG_LE(key, field, is_asc)
+ SEL_ARG_LT(const uchar *key, Field *field)
+ :SEL_ARG_LE(key, field)
{ max_flag= NEAR_MAX; }
/*
Use this constructor if value->save_in_field() returned success,
but we don't know if rounding or truncation happened
(as some Field::store() do not report minor data changes).
*/
- SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc,
+ SEL_ARG_LT(THD *thd, const uchar *key, Field *field,
Item *value)
- :SEL_ARG_LE(key, field, is_asc)
+ :SEL_ARG_LE(key, field)
{
if (stored_field_cmp_to_item(thd, field, value) == 0)
max_flag= NEAR_MAX;
@@ -1988,7 +1986,7 @@ class SEL_ARG_GT: public SEL_ARG
without any data rounding or truncation.
*/
SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if (!(key_part->flag & HA_PART_KEY_SEG))
@@ -2002,7 +2000,7 @@ class SEL_ARG_GT: public SEL_ARG
*/
SEL_ARG_GT(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2020,8 +2018,8 @@ class SEL_ARG_GE: public SEL_ARG
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_GE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_GE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
max_flag= NO_MAX_RANGE;
}
@@ -2032,7 +2030,7 @@ class SEL_ARG_GE: public SEL_ARG
*/
SEL_ARG_GE(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2063,7 +2061,7 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent,
}
else
{
- if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending,
+ if (!(tmp= new (param->mem_root) SEL_ARG(field, part,
min_value, max_value,
min_flag, max_flag, maybe_flag)))
return 0; // OOM
@@ -3244,6 +3242,7 @@ double records_in_column_ranges(PARAM *param, uint idx,
seq.keyno= idx;
seq.real_keyno= MAX_KEY;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
seq.is_ror_scan= FALSE;
@@ -8669,8 +8668,7 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param,
if (!field->real_maybe_null())
DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL);
SEL_ARG *tree;
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string)))
+ if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string)))
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
{
@@ -8770,8 +8768,7 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
int2store(min_str + maybe_null, min_length);
int2store(max_str + maybe_null, max_length);
}
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str);
+ SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str);
DBUG_RETURN(tree);
}
@@ -9019,19 +9016,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value));
case SCALAR_CMP_GT:
DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value));
case SCALAR_CMP_GE:
DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -9049,19 +9045,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this));
case SCALAR_CMP_GT:
DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this));
case SCALAR_CMP_GE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -11531,6 +11526,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
seq.keyno= idx;
seq.real_keyno= keynr;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
@@ -11785,9 +11781,9 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
int *min_part, int *max_part)
{
DBUG_ASSERT(next_key_part);
- bool asc = next_key_part->is_ascending;
+ const bool asc = !(key[next_key_part->part].flag & HA_REVERSE_SORT);
- if (!get_min_flag())
+ if (!get_min_flag(key))
{
if (asc)
{
@@ -11802,7 +11798,7 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
*cur_min_flag = invert_max_flag(tmp_flag);
}
}
- if (!get_max_flag())
+ if (!get_max_flag(key))
{
if (asc)
{
@@ -11832,7 +11828,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
int min_part= key_tree->part-1, // # of keypart values in min_key buffer
max_part= key_tree->part-1; // # of keypart values in max_key buffer
- SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
+ SEL_ARG *next_tree = asc ? key_tree->left : key_tree->right;
if (next_tree != &null_element)
{
if (get_quick_keys(param,quick,key,next_tree,
@@ -11841,7 +11838,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
uchar *tmp_min_key=min_key,*tmp_max_key=max_key;
- key_tree->store_min_max(key[key_tree->part].store_length,
+ key_tree->store_min_max(key, key[key_tree->part].store_length,
&tmp_min_key, min_key_flag,
&tmp_max_key, max_key_flag,
&min_part, &max_part);
@@ -11864,8 +11861,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
goto end; // Ugly, but efficient
}
{
- uint tmp_min_flag= key_tree->get_min_flag();
- uint tmp_max_flag= key_tree->get_max_flag();
+ uint tmp_min_flag= key_tree->get_min_flag(key);
+ uint tmp_max_flag= key_tree->get_max_flag(key);
key_tree->store_next_min_max_keys(key,
&tmp_min_key, &tmp_min_flag,
@@ -11876,7 +11873,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
else
{
- if (key_tree->is_ascending)
+ if (asc)
{
flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag:
(key_tree->min_flag |
@@ -11948,7 +11945,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
return 1;
end:
- next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left;
+ next_tree= asc ? key_tree->right : key_tree->left;
if (next_tree != &null_element)
return get_quick_keys(param,quick,key,next_tree,
min_key,min_key_flag,
@@ -16559,6 +16556,7 @@ static void trace_ranges(Json_writer_array *range_trace,
uint n_key_parts= param->table->actual_n_key_parts(keyinfo);
DBUG_ASSERT(range_trace->trace_started());
seq.keyno= idx;
+ seq.key_parts= param->key[idx];
seq.real_keyno= param->real_keynr[idx];
seq.param= param;
seq.start= keypart;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6864a5c583a..f3ccd4d8311 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -306,11 +306,6 @@ class SEL_ARG :public Sql_alloc
uint8 min_flag,max_flag,maybe_flag;
uint8 part; // Which key part
uint8 maybe_null;
- /*
- Whether the keypart is ascending or descending.
- See HowRangeOptimizerHandlesDescKeyparts for details.
- */
- uint8 is_ascending;
/*
The ordinal number the least significant component encountered in
the ranges of the SEL_ARG tree (the first component has number 1)
@@ -361,14 +356,14 @@ class SEL_ARG :public Sql_alloc
SEL_ARG() {}
SEL_ARG(SEL_ARG &);
- SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *);
- SEL_ARG(Field *field, uint8 part, bool is_asc,
+ SEL_ARG(Field *, const uchar *, const uchar *);
+ SEL_ARG(Field *field, uint8 part,
uchar *min_value, uchar *max_value,
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
/* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */
SEL_ARG(enum Type type_arg)
- :min_flag(0), is_ascending(false),
+ :min_flag(0),
max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/,
elements(1),use_count(1),left(0),right(0),
next_key_part(0), color(BLACK), type(type_arg), weight(1)
@@ -447,20 +442,20 @@ class SEL_ARG :public Sql_alloc
{
new_max=arg->max_value; flag_max=arg->max_flag;
}
- return new (thd->mem_root) SEL_ARG(field, part, is_ascending,
+ return new (thd->mem_root) SEL_ARG(field, part,
new_min, new_max, flag_min,
flag_max,
MY_TEST(maybe_flag && arg->maybe_flag));
}
SEL_ARG *clone_first(SEL_ARG *arg)
{ // min <= X < arg->min
- return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value,
+ return new SEL_ARG(field, part, min_value, arg->min_value,
min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX,
maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone_last(SEL_ARG *arg)
{ // min <= X <= key_max
- return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value,
+ return new SEL_ARG(field, part, min_value, arg->max_value,
min_flag, arg->max_flag, maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next);
@@ -544,44 +539,45 @@ class SEL_ARG :public Sql_alloc
}
/* Save minimum and maximum, taking index order into account */
- void store_min_max(uint length,
+ void store_min_max(KEY_PART *kp,
+ uint length,
uchar **min_key, uint min_flag,
uchar **max_key, uint max_flag,
int *min_part, int *max_part)
{
- if (is_ascending) {
- *min_part += store_min(length, min_key, min_flag);
- *max_part += store_max(length, max_key, max_flag);
- } else {
+ if (kp[part].flag & HA_REVERSE_SORT) {
*max_part += store_min(length, max_key, min_flag);
*min_part += store_max(length, min_key, max_flag);
+ } else {
+ *min_part += store_min(length, min_key, min_flag);
+ *max_part += store_max(length, max_key, max_flag);
}
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_min_flag()
+ uint get_min_flag(KEY_PART *kp)
{
- return (is_ascending ? min_flag : invert_max_flag(max_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_max_flag(max_flag) : min_flag;
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_max_flag()
+ uint get_max_flag(KEY_PART *kp)
{
- return (is_ascending ? max_flag : invert_min_flag(min_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_min_flag(min_flag) : max_flag ;
}
/* Get the previous interval, taking index order into account */
- inline SEL_ARG* index_order_prev()
+ inline SEL_ARG* index_order_prev(KEY_PART *kp)
{
- return is_ascending? prev: next;
+ return (kp[part].flag & HA_REVERSE_SORT)? next : prev;
}
/* Get the next interval, taking index order into account */
- inline SEL_ARG* index_order_next()
+ inline SEL_ARG* index_order_next(KEY_PART *kp)
{
- return is_ascending? next: prev;
+ return (kp[part].flag & HA_REVERSE_SORT)? prev : next;
}
/*
@@ -621,7 +617,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if (start_key == asc)
{
res+= nkp->store_min_key(key, range_key, range_key_flag, last_part,
@@ -657,7 +653,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if ((!start_key && asc) || (start_key && !asc))
{
res += nkp->store_max_key(key, range_key, range_key_flag, last_part,
@@ -785,9 +781,6 @@ class SEL_ARG :public Sql_alloc
Range Optimizer handles this as follows:
- The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the
- keypart is ascending.
-
Other than that, the SEL_ARG graph is built without any regard to DESC
keyparts.
@@ -799,7 +792,7 @@ class SEL_ARG :public Sql_alloc
kp1 BETWEEN 10 and 20 (RANGE-1)
- the SEL_ARG will have min_value=10, max_value=20, is_ascending=false.
+ the SEL_ARG will have min_value=10, max_value=20
The ordering of key parts is taken into account when SEL_ARG graph is
linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys().
@@ -850,7 +843,7 @@ class SEL_ARG_IMPOSSIBLE: public SEL_ARG
{
public:
SEL_ARG_IMPOSSIBLE(Field *field)
- :SEL_ARG(field, false, 0, 0)
+ :SEL_ARG(field, 0, 0)
{
type= SEL_ARG::IMPOSSIBLE;
}
diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc
index 8877e15d5b5..452a6864f06 100644
--- a/sql/opt_range_mrr.cc
+++ b/sql/opt_range_mrr.cc
@@ -47,6 +47,7 @@ typedef struct st_sel_arg_range_seq
uint keyno; /* index of used tree in SEL_TREE structure */
uint real_keyno; /* Number of the index in tables */
PARAM *param;
+ KEY_PART *key_parts;
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
@@ -106,13 +107,13 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length;
- key_tree->store_min_max(stor_length,
+ key_tree->store_min_max(arg->key_parts, stor_length,
&cur->min_key, prev->min_key_flag,
&cur->max_key, prev->max_key_flag,
&cur->min_key_parts, &cur->max_key_parts);
- cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag();
- cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag();
+ cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(arg->key_parts);
+ cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(arg->key_parts);
if (key_tree->is_null_interval())
cur->min_key_flag |= NULL_RANGE;
@@ -166,12 +167,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
/* Ok, we're at some "full tuple" position in the tree */
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
seq->is_ror_scan= FALSE;
goto walk_right_n_up;
}
@@ -186,12 +188,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= seq->stack[seq->i].key_tree;
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
break;
}
}
@@ -230,11 +233,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= key_tree->next_key_part;
walk_up_n_right:
- while (key_tree->index_order_prev() &&
- key_tree->index_order_prev() != &null_element)
+ while (key_tree->index_order_prev(seq->key_parts) &&
+ key_tree->index_order_prev(seq->key_parts) != &null_element)
{
/* Step up */
- key_tree= key_tree->index_order_prev();
+ key_tree= key_tree->index_order_prev(seq->key_parts);
}
step_down_to(seq, key_tree);
}
1
0
22 Dec '21
revision-id: 6ded957ccc6919261864262a4e9b53574cf7f1da (mariadb-10.6.1-259-g6ded957ccc6)
parent(s): 275b5fccefa3921cb3891e0c23af37c41bbe69a6
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-22 17:04:09 +0300
message:
Reverse-ordered indexes: remove SEL_ARG::is_ascending
Get the information from the array of KEY_PART structures that describes
the [pseudo-]index that is being analyzed.
---
sql/item_geofunc.cc | 2 +-
sql/opt_range.cc | 76 +++++++++++++++++++++++++---------------------------
sql/opt_range.h | 55 +++++++++++++++++--------------------
sql/opt_range_mrr.cc | 27 ++++++++++---------
4 files changed, 77 insertions(+), 83 deletions(-)
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index a2a99bcdf8f..0fdcf9e94e2 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1084,7 +1084,7 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param,
field->get_key_image(str, key_part->length, key_part->image_type);
SEL_ARG *tree;
- if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str)))
+ if (!(tree= new (param->mem_root) SEL_ARG(field, str, str)))
DBUG_RETURN(0); // out of memory
switch (type) {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ae2b5060625..bbb7b3d87fa 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1879,7 +1879,6 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc()
max_flag=arg.max_flag;
maybe_flag=arg.maybe_flag;
maybe_null=arg.maybe_null;
- is_ascending= arg.is_ascending;
part=arg.part;
field=arg.field;
min_value=arg.min_value;
@@ -1905,10 +1904,9 @@ inline void SEL_ARG::make_root()
use_count=0; elements=1;
}
-SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
+SEL_ARG::SEL_ARG(Field *f, const uchar *min_value_arg,
const uchar *max_value_arg)
:min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()),
- is_ascending(is_asc),
elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg),
max_value((uchar*) max_value_arg), next(0),prev(0),
next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1)
@@ -1917,11 +1915,11 @@ SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
max_part_no= 1;
}
-SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
+SEL_ARG::SEL_ARG(Field *field_,uint8 part_,
uchar *min_value_, uchar *max_value_,
uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_)
:min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_),
- part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_),
+ part(part_),maybe_null(field_->real_maybe_null()),
elements(1),use_count(1),
field(field_), min_value(min_value_), max_value(max_value_),
next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1)
@@ -1941,8 +1939,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
class SEL_ARG_LE: public SEL_ARG
{
public:
- SEL_ARG_LE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_LE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
if (!field->real_maybe_null())
min_flag= NO_MIN_RANGE; // From start
@@ -1962,17 +1960,17 @@ class SEL_ARG_LT: public SEL_ARG_LE
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_LT(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG_LE(key, field, is_asc)
+ SEL_ARG_LT(const uchar *key, Field *field)
+ :SEL_ARG_LE(key, field)
{ max_flag= NEAR_MAX; }
/*
Use this constructor if value->save_in_field() returned success,
but we don't know if rounding or truncation happened
(as some Field::store() do not report minor data changes).
*/
- SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc,
+ SEL_ARG_LT(THD *thd, const uchar *key, Field *field,
Item *value)
- :SEL_ARG_LE(key, field, is_asc)
+ :SEL_ARG_LE(key, field)
{
if (stored_field_cmp_to_item(thd, field, value) == 0)
max_flag= NEAR_MAX;
@@ -1988,7 +1986,7 @@ class SEL_ARG_GT: public SEL_ARG
without any data rounding or truncation.
*/
SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if (!(key_part->flag & HA_PART_KEY_SEG))
@@ -2002,7 +2000,7 @@ class SEL_ARG_GT: public SEL_ARG
*/
SEL_ARG_GT(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2020,8 +2018,8 @@ class SEL_ARG_GE: public SEL_ARG
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_GE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_GE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
max_flag= NO_MAX_RANGE;
}
@@ -2032,7 +2030,7 @@ class SEL_ARG_GE: public SEL_ARG
*/
SEL_ARG_GE(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2063,7 +2061,7 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent,
}
else
{
- if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending,
+ if (!(tmp= new (param->mem_root) SEL_ARG(field, part,
min_value, max_value,
min_flag, max_flag, maybe_flag)))
return 0; // OOM
@@ -3244,6 +3242,7 @@ double records_in_column_ranges(PARAM *param, uint idx,
seq.keyno= idx;
seq.real_keyno= MAX_KEY;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
seq.is_ror_scan= FALSE;
@@ -8669,8 +8668,7 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param,
if (!field->real_maybe_null())
DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL);
SEL_ARG *tree;
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string)))
+ if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string)))
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
{
@@ -8770,8 +8768,7 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
int2store(min_str + maybe_null, min_length);
int2store(max_str + maybe_null, max_length);
}
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str);
+ SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str);
DBUG_RETURN(tree);
}
@@ -9019,19 +9016,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value));
case SCALAR_CMP_GT:
DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value));
case SCALAR_CMP_GE:
DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -9049,19 +9045,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this));
case SCALAR_CMP_GT:
DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this));
case SCALAR_CMP_GE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -11531,6 +11526,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
seq.keyno= idx;
seq.real_keyno= keynr;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
@@ -11785,9 +11781,9 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
int *min_part, int *max_part)
{
DBUG_ASSERT(next_key_part);
- bool asc = next_key_part->is_ascending;
+ const bool asc = !(key[next_key_part->part].flag & HA_REVERSE_SORT);
- if (!get_min_flag())
+ if (!get_min_flag(key))
{
if (asc)
{
@@ -11802,7 +11798,7 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
*cur_min_flag = invert_max_flag(tmp_flag);
}
}
- if (!get_max_flag())
+ if (!get_max_flag(key))
{
if (asc)
{
@@ -11832,7 +11828,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
int min_part= key_tree->part-1, // # of keypart values in min_key buffer
max_part= key_tree->part-1; // # of keypart values in max_key buffer
- SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
+ SEL_ARG *next_tree = asc ? key_tree->left : key_tree->right;
if (next_tree != &null_element)
{
if (get_quick_keys(param,quick,key,next_tree,
@@ -11841,7 +11838,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
uchar *tmp_min_key=min_key,*tmp_max_key=max_key;
- key_tree->store_min_max(key[key_tree->part].store_length,
+ key_tree->store_min_max(key, key[key_tree->part].store_length,
&tmp_min_key, min_key_flag,
&tmp_max_key, max_key_flag,
&min_part, &max_part);
@@ -11864,8 +11861,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
goto end; // Ugly, but efficient
}
{
- uint tmp_min_flag= key_tree->get_min_flag();
- uint tmp_max_flag= key_tree->get_max_flag();
+ uint tmp_min_flag= key_tree->get_min_flag(key);
+ uint tmp_max_flag= key_tree->get_max_flag(key);
key_tree->store_next_min_max_keys(key,
&tmp_min_key, &tmp_min_flag,
@@ -11876,7 +11873,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
else
{
- if (key_tree->is_ascending)
+ if (asc)
{
flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag:
(key_tree->min_flag |
@@ -11948,7 +11945,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
return 1;
end:
- next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left;
+ next_tree= asc ? key_tree->right : key_tree->left;
if (next_tree != &null_element)
return get_quick_keys(param,quick,key,next_tree,
min_key,min_key_flag,
@@ -16559,6 +16556,7 @@ static void trace_ranges(Json_writer_array *range_trace,
uint n_key_parts= param->table->actual_n_key_parts(keyinfo);
DBUG_ASSERT(range_trace->trace_started());
seq.keyno= idx;
+ seq.key_parts= param->key[idx];
seq.real_keyno= param->real_keynr[idx];
seq.param= param;
seq.start= keypart;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6864a5c583a..f3ccd4d8311 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -306,11 +306,6 @@ class SEL_ARG :public Sql_alloc
uint8 min_flag,max_flag,maybe_flag;
uint8 part; // Which key part
uint8 maybe_null;
- /*
- Whether the keypart is ascending or descending.
- See HowRangeOptimizerHandlesDescKeyparts for details.
- */
- uint8 is_ascending;
/*
The ordinal number the least significant component encountered in
the ranges of the SEL_ARG tree (the first component has number 1)
@@ -361,14 +356,14 @@ class SEL_ARG :public Sql_alloc
SEL_ARG() {}
SEL_ARG(SEL_ARG &);
- SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *);
- SEL_ARG(Field *field, uint8 part, bool is_asc,
+ SEL_ARG(Field *, const uchar *, const uchar *);
+ SEL_ARG(Field *field, uint8 part,
uchar *min_value, uchar *max_value,
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
/* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */
SEL_ARG(enum Type type_arg)
- :min_flag(0), is_ascending(false),
+ :min_flag(0),
max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/,
elements(1),use_count(1),left(0),right(0),
next_key_part(0), color(BLACK), type(type_arg), weight(1)
@@ -447,20 +442,20 @@ class SEL_ARG :public Sql_alloc
{
new_max=arg->max_value; flag_max=arg->max_flag;
}
- return new (thd->mem_root) SEL_ARG(field, part, is_ascending,
+ return new (thd->mem_root) SEL_ARG(field, part,
new_min, new_max, flag_min,
flag_max,
MY_TEST(maybe_flag && arg->maybe_flag));
}
SEL_ARG *clone_first(SEL_ARG *arg)
{ // min <= X < arg->min
- return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value,
+ return new SEL_ARG(field, part, min_value, arg->min_value,
min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX,
maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone_last(SEL_ARG *arg)
{ // min <= X <= key_max
- return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value,
+ return new SEL_ARG(field, part, min_value, arg->max_value,
min_flag, arg->max_flag, maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next);
@@ -544,44 +539,45 @@ class SEL_ARG :public Sql_alloc
}
/* Save minimum and maximum, taking index order into account */
- void store_min_max(uint length,
+ void store_min_max(KEY_PART *kp,
+ uint length,
uchar **min_key, uint min_flag,
uchar **max_key, uint max_flag,
int *min_part, int *max_part)
{
- if (is_ascending) {
- *min_part += store_min(length, min_key, min_flag);
- *max_part += store_max(length, max_key, max_flag);
- } else {
+ if (kp[part].flag & HA_REVERSE_SORT) {
*max_part += store_min(length, max_key, min_flag);
*min_part += store_max(length, min_key, max_flag);
+ } else {
+ *min_part += store_min(length, min_key, min_flag);
+ *max_part += store_max(length, max_key, max_flag);
}
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_min_flag()
+ uint get_min_flag(KEY_PART *kp)
{
- return (is_ascending ? min_flag : invert_max_flag(max_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_max_flag(max_flag) : min_flag;
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_max_flag()
+ uint get_max_flag(KEY_PART *kp)
{
- return (is_ascending ? max_flag : invert_min_flag(min_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_min_flag(min_flag) : max_flag ;
}
/* Get the previous interval, taking index order into account */
- inline SEL_ARG* index_order_prev()
+ inline SEL_ARG* index_order_prev(KEY_PART *kp)
{
- return is_ascending? prev: next;
+ return (kp[part].flag & HA_REVERSE_SORT)? next : prev;
}
/* Get the next interval, taking index order into account */
- inline SEL_ARG* index_order_next()
+ inline SEL_ARG* index_order_next(KEY_PART *kp)
{
- return is_ascending? next: prev;
+ return (kp[part].flag & HA_REVERSE_SORT)? prev : next;
}
/*
@@ -621,7 +617,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if (start_key == asc)
{
res+= nkp->store_min_key(key, range_key, range_key_flag, last_part,
@@ -657,7 +653,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if ((!start_key && asc) || (start_key && !asc))
{
res += nkp->store_max_key(key, range_key, range_key_flag, last_part,
@@ -785,9 +781,6 @@ class SEL_ARG :public Sql_alloc
Range Optimizer handles this as follows:
- The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the
- keypart is ascending.
-
Other than that, the SEL_ARG graph is built without any regard to DESC
keyparts.
@@ -799,7 +792,7 @@ class SEL_ARG :public Sql_alloc
kp1 BETWEEN 10 and 20 (RANGE-1)
- the SEL_ARG will have min_value=10, max_value=20, is_ascending=false.
+ the SEL_ARG will have min_value=10, max_value=20
The ordering of key parts is taken into account when SEL_ARG graph is
linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys().
@@ -850,7 +843,7 @@ class SEL_ARG_IMPOSSIBLE: public SEL_ARG
{
public:
SEL_ARG_IMPOSSIBLE(Field *field)
- :SEL_ARG(field, false, 0, 0)
+ :SEL_ARG(field, 0, 0)
{
type= SEL_ARG::IMPOSSIBLE;
}
diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc
index 8877e15d5b5..452a6864f06 100644
--- a/sql/opt_range_mrr.cc
+++ b/sql/opt_range_mrr.cc
@@ -47,6 +47,7 @@ typedef struct st_sel_arg_range_seq
uint keyno; /* index of used tree in SEL_TREE structure */
uint real_keyno; /* Number of the index in tables */
PARAM *param;
+ KEY_PART *key_parts;
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
@@ -106,13 +107,13 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length;
- key_tree->store_min_max(stor_length,
+ key_tree->store_min_max(arg->key_parts, stor_length,
&cur->min_key, prev->min_key_flag,
&cur->max_key, prev->max_key_flag,
&cur->min_key_parts, &cur->max_key_parts);
- cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag();
- cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag();
+ cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(arg->key_parts);
+ cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(arg->key_parts);
if (key_tree->is_null_interval())
cur->min_key_flag |= NULL_RANGE;
@@ -166,12 +167,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
/* Ok, we're at some "full tuple" position in the tree */
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
seq->is_ror_scan= FALSE;
goto walk_right_n_up;
}
@@ -186,12 +188,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= seq->stack[seq->i].key_tree;
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
break;
}
}
@@ -230,11 +233,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= key_tree->next_key_part;
walk_up_n_right:
- while (key_tree->index_order_prev() &&
- key_tree->index_order_prev() != &null_element)
+ while (key_tree->index_order_prev(seq->key_parts) &&
+ key_tree->index_order_prev(seq->key_parts) != &null_element)
{
/* Step up */
- key_tree= key_tree->index_order_prev();
+ key_tree= key_tree->index_order_prev(seq->key_parts);
}
step_down_to(seq, key_tree);
}
1
0
[Commits] c6f9a6a: MDEV-27262 Unexpected index intersection with full index scan for an index
by IgorBabaev 21 Dec '21
by IgorBabaev 21 Dec '21
21 Dec '21
revision-id: c6f9a6ac0e4ef94391dc4263af02b078f6fa7960 (mariadb-10.2.31-1286-gc6f9a6a)
parent(s): 8bb55633699612279744c055e22eeca8d4058273
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-12-20 19:55:00 -0800
message:
MDEV-27262 Unexpected index intersection with full index scan for an index
If when extracting a range condition for an index from the WHERE condition
Range Optimizer sees that the range condition covers the whole index then
such condition should be discarded because it cannot be used in any range
scan. In some cases Range Optimizer really does it, but there remained some
conditions for which it was not done. As a result the optimizer could
produce index merge plans with the full index scan for one of the indexes
participating in the index merge.
This could be observed in one of the test cases from index_merge1.inc
where a plan with index_merge_sort_union was produced and in the test case
reported for this bug where a plan with index_merge_sort_intersect was
produced. In both cases one of two index scans participating in index merge
ran over the whole index.
The patch slightly changes the original above mentioned test case from
index_merge1.inc to be able to produce an intended plan employing
index_merge_sort_union. The original query was left to show that index
merge is not used for it anymore.
It should be noted that for the plan with index_merge_sort_intersect could
be chosen for execution only due to a defect in the InnoDB code that
returns wrong estimates for the cardinality of big ranges.
This bug led to serious problems in 10.4+ where the optimization using
Rowid filters is employed (see mdev-26446).
Approved by Sergey Petrunia <sergey(a)mariadb.com>
---
mysql-test/include/index_merge1.inc | 14 ++++-
mysql-test/r/index_merge_myisam.result | 18 +++++-
mysql-test/r/range_innodb.result | 105 +++++++++++++++++++++++++++++++++
mysql-test/t/range_innodb.test | 93 +++++++++++++++++++++++++++++
sql/opt_range.cc | 19 +++++-
5 files changed, 243 insertions(+), 6 deletions(-)
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc
index b168a76..ebadb50 100644
--- a/mysql-test/include/index_merge1.inc
+++ b/mysql-test/include/index_merge1.inc
@@ -150,12 +150,22 @@ explain select * from t0 where
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
explain select * from t0 where
- ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
- ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
+ or
+ ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+ ((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ or
+ ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+ ((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index 5a23092..a3e9e4f 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -173,17 +173,29 @@ or
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
explain select * from t0 where
-((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
-((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
select * from t0 where key1 < 5 or key8 < 4 order by key1;
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
diff --git a/mysql-test/r/range_innodb.result b/mysql-test/r/range_innodb.result
index f2349f2..df111bc 100644
--- a/mysql-test/r/range_innodb.result
+++ b/mysql-test/r/range_innodb.result
@@ -108,3 +108,108 @@ DROP TABLE t0,t1;
SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
# End of 10.1 tests
+#
+# MDEV-27262: Index intersection with full scan over an index
+#
+CREATE TABLE t1 (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+p char(32) DEFAULT NULL,
+es tinyint(3) unsigned NOT NULL DEFAULT 0,
+er tinyint(3) unsigned NOT NULL DEFAULT 0,
+x mediumint(8) unsigned NOT NULL DEFAULT 0,
+PRIMARY KEY (id),
+INDEX es (es),
+INDEX x (x),
+INDEX er (er,x),
+INDEX p (p)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+insert into t1(es,er) select 0, 1 from seq_1_to_45;
+insert into t1(es,er) select 0, 2 from seq_1_to_49;
+insert into t1(es,er) select 0, 3 from seq_1_to_951;
+insert into t1(es,er) select 0, 3 from seq_1_to_1054;
+insert into t1(es,er) select 0, 6 from seq_1_to_25;
+insert into t1(es,er) select 0, 11 from seq_1_to_1;
+insert into t1(es,er) select 1, 1 from seq_1_to_45;
+insert into t1(es,er) select 1, 2 from seq_1_to_16;
+insert into t1(es,er) select 1, 3 from seq_1_to_511;
+insert into t1(es,er) select 1, 4 from seq_1_to_687;
+insert into t1(es,er) select 1, 6 from seq_1_to_50;
+insert into t1(es,er) select 1, 7 from seq_1_to_4;
+insert into t1(es,er) select 1, 11 from seq_1_to_1;
+insert into t1(es,er) select 2, 1 from seq_1_to_82;
+insert into t1(es,er) select 2, 2 from seq_1_to_82;
+insert into t1(es,er) select 2, 3 from seq_1_to_1626;
+insert into t1(es,er) select 2, 4 from seq_1_to_977;
+insert into t1(es,er) select 2, 6 from seq_1_to_33;
+insert into t1(es,er) select 2, 11 from seq_1_to_1;
+insert into t1(es,er) select 3, 1 from seq_1_to_245;
+insert into t1(es,er) select 3, 2 from seq_1_to_81;
+insert into t1(es,er) select 3, 3 from seq_1_to_852;
+insert into t1(es,er) select 3, 4 from seq_1_to_2243;
+insert into t1(es,er) select 3, 6 from seq_1_to_44;
+insert into t1(es,er) select 3, 11 from seq_1_to_1;
+insert into t1(es,er) select 4, 1 from seq_1_to_91;
+insert into t1(es,er) select 4, 2 from seq_1_to_83;
+insert into t1(es,er) select 4, 3 from seq_1_to_297;
+insert into t1(es,er) select 4, 4 from seq_1_to_2456;
+insert into t1(es,er) select 4, 6 from seq_1_to_19;
+insert into t1(es,er) select 4, 11 from seq_1_to_1;
+update t1 set p='foobar';
+update t1 set x=0;
+set @save_isp=@@innodb_stats_persistent;
+set global innodb_stats_persistent= 1;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+set optimizer_switch='index_merge_sort_intersection=on';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14645 foobar 4 4 0
+14646 foobar 4 4 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=off';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14645 foobar 4 4 0
+14646 foobar 4 4 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=on';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14007 foobar 4 2 0
+14008 foobar 4 2 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=off';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14007 foobar 4 2 0
+14008 foobar 4 2 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=default';
+set global innodb_stats_persistent= @save_isp;
+DROP TABLE t1;
+# End of 10.2 tests
diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test
index 428e5c2..b8a6a7c 100644
--- a/mysql-test/t/range_innodb.test
+++ b/mysql-test/t/range_innodb.test
@@ -116,3 +116,96 @@ SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
--echo # End of 10.1 tests
+
+--echo #
+--echo # MDEV-27262: Index intersection with full scan over an index
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (
+ id int(10) unsigned NOT NULL AUTO_INCREMENT,
+ p char(32) DEFAULT NULL,
+ es tinyint(3) unsigned NOT NULL DEFAULT 0,
+ er tinyint(3) unsigned NOT NULL DEFAULT 0,
+ x mediumint(8) unsigned NOT NULL DEFAULT 0,
+ PRIMARY KEY (id),
+ INDEX es (es),
+ INDEX x (x),
+ INDEX er (er,x),
+ INDEX p (p)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+insert into t1(es,er) select 0, 1 from seq_1_to_45;
+insert into t1(es,er) select 0, 2 from seq_1_to_49;
+insert into t1(es,er) select 0, 3 from seq_1_to_951;
+insert into t1(es,er) select 0, 3 from seq_1_to_1054;
+insert into t1(es,er) select 0, 6 from seq_1_to_25;
+insert into t1(es,er) select 0, 11 from seq_1_to_1;
+insert into t1(es,er) select 1, 1 from seq_1_to_45;
+insert into t1(es,er) select 1, 2 from seq_1_to_16;
+insert into t1(es,er) select 1, 3 from seq_1_to_511;
+insert into t1(es,er) select 1, 4 from seq_1_to_687;
+insert into t1(es,er) select 1, 6 from seq_1_to_50;
+insert into t1(es,er) select 1, 7 from seq_1_to_4;
+insert into t1(es,er) select 1, 11 from seq_1_to_1;
+insert into t1(es,er) select 2, 1 from seq_1_to_82;
+insert into t1(es,er) select 2, 2 from seq_1_to_82;
+insert into t1(es,er) select 2, 3 from seq_1_to_1626;
+insert into t1(es,er) select 2, 4 from seq_1_to_977;
+insert into t1(es,er) select 2, 6 from seq_1_to_33;
+insert into t1(es,er) select 2, 11 from seq_1_to_1;
+insert into t1(es,er) select 3, 1 from seq_1_to_245;
+insert into t1(es,er) select 3, 2 from seq_1_to_81;
+insert into t1(es,er) select 3, 3 from seq_1_to_852;
+insert into t1(es,er) select 3, 4 from seq_1_to_2243;
+insert into t1(es,er) select 3, 6 from seq_1_to_44;
+insert into t1(es,er) select 3, 11 from seq_1_to_1;
+insert into t1(es,er) select 4, 1 from seq_1_to_91;
+insert into t1(es,er) select 4, 2 from seq_1_to_83;
+insert into t1(es,er) select 4, 3 from seq_1_to_297;
+insert into t1(es,er) select 4, 4 from seq_1_to_2456;
+insert into t1(es,er) select 4, 6 from seq_1_to_19;
+insert into t1(es,er) select 4, 11 from seq_1_to_1;
+update t1 set p='foobar';
+update t1 set x=0;
+set @save_isp=@@innodb_stats_persistent;
+set global innodb_stats_persistent= 1;
+analyze table t1;
+
+let $q=
+SELECT * FROM t1
+ WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+
+set optimizer_switch='index_merge_sort_intersection=on';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=off';
+# execution of $q and explain for it led to an assertion failure in 10.4
+# (with the optimizer switch rowid_filter set to 'on')
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+let $q=
+SELECT * FROM t1
+ WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+
+set optimizer_switch='index_merge_sort_intersection=on';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=off';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=default';
+
+set global innodb_stats_persistent= @save_isp;
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index f3f1843..89ee8cf 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9329,7 +9329,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
uint max_part_no= MY_MAX(key1->max_part_no, key2->max_part_no);
- for (key2=key2->first(); key2; )
+ for (key2=key2->first(); ; )
{
/*
key1 consists of one or more ranges. tmp is the range currently
@@ -9343,6 +9343,16 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
^
tmp
*/
+ if (key1->min_flag & NO_MIN_RANGE &&
+ key1->max_flag & NO_MAX_RANGE)
+ {
+ if (key1->maybe_flag)
+ return new SEL_ARG(SEL_ARG::MAYBE_KEY);
+ return 0; // Always true OR
+ }
+ if (!key2)
+ break;
+
SEL_ARG *tmp=key1->find_range(key2);
/*
@@ -9413,6 +9423,13 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
key2->copy_min(tmp);
if (!(key1=key1->tree_delete(tmp)))
{ // Only one key in tree
+ if (key2->min_flag & NO_MIN_RANGE &&
+ key2->max_flag & NO_MAX_RANGE)
+ {
+ if (key2->maybe_flag)
+ return new SEL_ARG(SEL_ARG::MAYBE_KEY);
+ return 0; // Always true OR
+ }
key1=key2;
key1->make_root();
key2=key2_next;
1
0
20 Dec '21
revision-id: 59af76ef3434a4beb21c7e7c52800b539be23b7f (mariadb-10.6.1-250-g59af76ef343)
parent(s): de4426358101575648e968b4cdae35527da74b23
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-20 23:51:55 +0300
message:
Reverse-ordered indexes: remove SEL_ARG::is_ascending
Get the information from the array of KEY_PART structures that describes
the [pseudo-]index that is being analyzed.
---
sql/item_geofunc.cc | 2 +-
sql/opt_range.cc | 76 +++++++++++++++++++++++++---------------------------
sql/opt_range.h | 55 +++++++++++++++++--------------------
sql/opt_range_mrr.cc | 27 ++++++++++---------
4 files changed, 77 insertions(+), 83 deletions(-)
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index a2a99bcdf8f..0fdcf9e94e2 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1084,7 +1084,7 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param,
field->get_key_image(str, key_part->length, key_part->image_type);
SEL_ARG *tree;
- if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str)))
+ if (!(tree= new (param->mem_root) SEL_ARG(field, str, str)))
DBUG_RETURN(0); // out of memory
switch (type) {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ae2b5060625..bbb7b3d87fa 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1879,7 +1879,6 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc()
max_flag=arg.max_flag;
maybe_flag=arg.maybe_flag;
maybe_null=arg.maybe_null;
- is_ascending= arg.is_ascending;
part=arg.part;
field=arg.field;
min_value=arg.min_value;
@@ -1905,10 +1904,9 @@ inline void SEL_ARG::make_root()
use_count=0; elements=1;
}
-SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
+SEL_ARG::SEL_ARG(Field *f, const uchar *min_value_arg,
const uchar *max_value_arg)
:min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()),
- is_ascending(is_asc),
elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg),
max_value((uchar*) max_value_arg), next(0),prev(0),
next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1)
@@ -1917,11 +1915,11 @@ SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
max_part_no= 1;
}
-SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
+SEL_ARG::SEL_ARG(Field *field_,uint8 part_,
uchar *min_value_, uchar *max_value_,
uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_)
:min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_),
- part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_),
+ part(part_),maybe_null(field_->real_maybe_null()),
elements(1),use_count(1),
field(field_), min_value(min_value_), max_value(max_value_),
next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1)
@@ -1941,8 +1939,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
class SEL_ARG_LE: public SEL_ARG
{
public:
- SEL_ARG_LE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_LE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
if (!field->real_maybe_null())
min_flag= NO_MIN_RANGE; // From start
@@ -1962,17 +1960,17 @@ class SEL_ARG_LT: public SEL_ARG_LE
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_LT(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG_LE(key, field, is_asc)
+ SEL_ARG_LT(const uchar *key, Field *field)
+ :SEL_ARG_LE(key, field)
{ max_flag= NEAR_MAX; }
/*
Use this constructor if value->save_in_field() returned success,
but we don't know if rounding or truncation happened
(as some Field::store() do not report minor data changes).
*/
- SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc,
+ SEL_ARG_LT(THD *thd, const uchar *key, Field *field,
Item *value)
- :SEL_ARG_LE(key, field, is_asc)
+ :SEL_ARG_LE(key, field)
{
if (stored_field_cmp_to_item(thd, field, value) == 0)
max_flag= NEAR_MAX;
@@ -1988,7 +1986,7 @@ class SEL_ARG_GT: public SEL_ARG
without any data rounding or truncation.
*/
SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if (!(key_part->flag & HA_PART_KEY_SEG))
@@ -2002,7 +2000,7 @@ class SEL_ARG_GT: public SEL_ARG
*/
SEL_ARG_GT(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2020,8 +2018,8 @@ class SEL_ARG_GE: public SEL_ARG
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_GE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_GE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
max_flag= NO_MAX_RANGE;
}
@@ -2032,7 +2030,7 @@ class SEL_ARG_GE: public SEL_ARG
*/
SEL_ARG_GE(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2063,7 +2061,7 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent,
}
else
{
- if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending,
+ if (!(tmp= new (param->mem_root) SEL_ARG(field, part,
min_value, max_value,
min_flag, max_flag, maybe_flag)))
return 0; // OOM
@@ -3244,6 +3242,7 @@ double records_in_column_ranges(PARAM *param, uint idx,
seq.keyno= idx;
seq.real_keyno= MAX_KEY;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
seq.is_ror_scan= FALSE;
@@ -8669,8 +8668,7 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param,
if (!field->real_maybe_null())
DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL);
SEL_ARG *tree;
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string)))
+ if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string)))
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
{
@@ -8770,8 +8768,7 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
int2store(min_str + maybe_null, min_length);
int2store(max_str + maybe_null, max_length);
}
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str);
+ SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str);
DBUG_RETURN(tree);
}
@@ -9019,19 +9016,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value));
case SCALAR_CMP_GT:
DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value));
case SCALAR_CMP_GE:
DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -9049,19 +9045,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this));
case SCALAR_CMP_GT:
DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this));
case SCALAR_CMP_GE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -11531,6 +11526,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
seq.keyno= idx;
seq.real_keyno= keynr;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
@@ -11785,9 +11781,9 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
int *min_part, int *max_part)
{
DBUG_ASSERT(next_key_part);
- bool asc = next_key_part->is_ascending;
+ const bool asc = !(key[next_key_part->part].flag & HA_REVERSE_SORT);
- if (!get_min_flag())
+ if (!get_min_flag(key))
{
if (asc)
{
@@ -11802,7 +11798,7 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
*cur_min_flag = invert_max_flag(tmp_flag);
}
}
- if (!get_max_flag())
+ if (!get_max_flag(key))
{
if (asc)
{
@@ -11832,7 +11828,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
int min_part= key_tree->part-1, // # of keypart values in min_key buffer
max_part= key_tree->part-1; // # of keypart values in max_key buffer
- SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
+ SEL_ARG *next_tree = asc ? key_tree->left : key_tree->right;
if (next_tree != &null_element)
{
if (get_quick_keys(param,quick,key,next_tree,
@@ -11841,7 +11838,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
uchar *tmp_min_key=min_key,*tmp_max_key=max_key;
- key_tree->store_min_max(key[key_tree->part].store_length,
+ key_tree->store_min_max(key, key[key_tree->part].store_length,
&tmp_min_key, min_key_flag,
&tmp_max_key, max_key_flag,
&min_part, &max_part);
@@ -11864,8 +11861,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
goto end; // Ugly, but efficient
}
{
- uint tmp_min_flag= key_tree->get_min_flag();
- uint tmp_max_flag= key_tree->get_max_flag();
+ uint tmp_min_flag= key_tree->get_min_flag(key);
+ uint tmp_max_flag= key_tree->get_max_flag(key);
key_tree->store_next_min_max_keys(key,
&tmp_min_key, &tmp_min_flag,
@@ -11876,7 +11873,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
else
{
- if (key_tree->is_ascending)
+ if (asc)
{
flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag:
(key_tree->min_flag |
@@ -11948,7 +11945,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
return 1;
end:
- next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left;
+ next_tree= asc ? key_tree->right : key_tree->left;
if (next_tree != &null_element)
return get_quick_keys(param,quick,key,next_tree,
min_key,min_key_flag,
@@ -16559,6 +16556,7 @@ static void trace_ranges(Json_writer_array *range_trace,
uint n_key_parts= param->table->actual_n_key_parts(keyinfo);
DBUG_ASSERT(range_trace->trace_started());
seq.keyno= idx;
+ seq.key_parts= param->key[idx];
seq.real_keyno= param->real_keynr[idx];
seq.param= param;
seq.start= keypart;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6864a5c583a..f3ccd4d8311 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -306,11 +306,6 @@ class SEL_ARG :public Sql_alloc
uint8 min_flag,max_flag,maybe_flag;
uint8 part; // Which key part
uint8 maybe_null;
- /*
- Whether the keypart is ascending or descending.
- See HowRangeOptimizerHandlesDescKeyparts for details.
- */
- uint8 is_ascending;
/*
The ordinal number the least significant component encountered in
the ranges of the SEL_ARG tree (the first component has number 1)
@@ -361,14 +356,14 @@ class SEL_ARG :public Sql_alloc
SEL_ARG() {}
SEL_ARG(SEL_ARG &);
- SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *);
- SEL_ARG(Field *field, uint8 part, bool is_asc,
+ SEL_ARG(Field *, const uchar *, const uchar *);
+ SEL_ARG(Field *field, uint8 part,
uchar *min_value, uchar *max_value,
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
/* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */
SEL_ARG(enum Type type_arg)
- :min_flag(0), is_ascending(false),
+ :min_flag(0),
max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/,
elements(1),use_count(1),left(0),right(0),
next_key_part(0), color(BLACK), type(type_arg), weight(1)
@@ -447,20 +442,20 @@ class SEL_ARG :public Sql_alloc
{
new_max=arg->max_value; flag_max=arg->max_flag;
}
- return new (thd->mem_root) SEL_ARG(field, part, is_ascending,
+ return new (thd->mem_root) SEL_ARG(field, part,
new_min, new_max, flag_min,
flag_max,
MY_TEST(maybe_flag && arg->maybe_flag));
}
SEL_ARG *clone_first(SEL_ARG *arg)
{ // min <= X < arg->min
- return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value,
+ return new SEL_ARG(field, part, min_value, arg->min_value,
min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX,
maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone_last(SEL_ARG *arg)
{ // min <= X <= key_max
- return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value,
+ return new SEL_ARG(field, part, min_value, arg->max_value,
min_flag, arg->max_flag, maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next);
@@ -544,44 +539,45 @@ class SEL_ARG :public Sql_alloc
}
/* Save minimum and maximum, taking index order into account */
- void store_min_max(uint length,
+ void store_min_max(KEY_PART *kp,
+ uint length,
uchar **min_key, uint min_flag,
uchar **max_key, uint max_flag,
int *min_part, int *max_part)
{
- if (is_ascending) {
- *min_part += store_min(length, min_key, min_flag);
- *max_part += store_max(length, max_key, max_flag);
- } else {
+ if (kp[part].flag & HA_REVERSE_SORT) {
*max_part += store_min(length, max_key, min_flag);
*min_part += store_max(length, min_key, max_flag);
+ } else {
+ *min_part += store_min(length, min_key, min_flag);
+ *max_part += store_max(length, max_key, max_flag);
}
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_min_flag()
+ uint get_min_flag(KEY_PART *kp)
{
- return (is_ascending ? min_flag : invert_max_flag(max_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_max_flag(max_flag) : min_flag;
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_max_flag()
+ uint get_max_flag(KEY_PART *kp)
{
- return (is_ascending ? max_flag : invert_min_flag(min_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_min_flag(min_flag) : max_flag ;
}
/* Get the previous interval, taking index order into account */
- inline SEL_ARG* index_order_prev()
+ inline SEL_ARG* index_order_prev(KEY_PART *kp)
{
- return is_ascending? prev: next;
+ return (kp[part].flag & HA_REVERSE_SORT)? next : prev;
}
/* Get the next interval, taking index order into account */
- inline SEL_ARG* index_order_next()
+ inline SEL_ARG* index_order_next(KEY_PART *kp)
{
- return is_ascending? next: prev;
+ return (kp[part].flag & HA_REVERSE_SORT)? prev : next;
}
/*
@@ -621,7 +617,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if (start_key == asc)
{
res+= nkp->store_min_key(key, range_key, range_key_flag, last_part,
@@ -657,7 +653,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if ((!start_key && asc) || (start_key && !asc))
{
res += nkp->store_max_key(key, range_key, range_key_flag, last_part,
@@ -785,9 +781,6 @@ class SEL_ARG :public Sql_alloc
Range Optimizer handles this as follows:
- The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the
- keypart is ascending.
-
Other than that, the SEL_ARG graph is built without any regard to DESC
keyparts.
@@ -799,7 +792,7 @@ class SEL_ARG :public Sql_alloc
kp1 BETWEEN 10 and 20 (RANGE-1)
- the SEL_ARG will have min_value=10, max_value=20, is_ascending=false.
+ the SEL_ARG will have min_value=10, max_value=20
The ordering of key parts is taken into account when SEL_ARG graph is
linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys().
@@ -850,7 +843,7 @@ class SEL_ARG_IMPOSSIBLE: public SEL_ARG
{
public:
SEL_ARG_IMPOSSIBLE(Field *field)
- :SEL_ARG(field, false, 0, 0)
+ :SEL_ARG(field, 0, 0)
{
type= SEL_ARG::IMPOSSIBLE;
}
diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc
index 8877e15d5b5..452a6864f06 100644
--- a/sql/opt_range_mrr.cc
+++ b/sql/opt_range_mrr.cc
@@ -47,6 +47,7 @@ typedef struct st_sel_arg_range_seq
uint keyno; /* index of used tree in SEL_TREE structure */
uint real_keyno; /* Number of the index in tables */
PARAM *param;
+ KEY_PART *key_parts;
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
@@ -106,13 +107,13 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length;
- key_tree->store_min_max(stor_length,
+ key_tree->store_min_max(arg->key_parts, stor_length,
&cur->min_key, prev->min_key_flag,
&cur->max_key, prev->max_key_flag,
&cur->min_key_parts, &cur->max_key_parts);
- cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag();
- cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag();
+ cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(arg->key_parts);
+ cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(arg->key_parts);
if (key_tree->is_null_interval())
cur->min_key_flag |= NULL_RANGE;
@@ -166,12 +167,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
/* Ok, we're at some "full tuple" position in the tree */
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
seq->is_ror_scan= FALSE;
goto walk_right_n_up;
}
@@ -186,12 +188,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= seq->stack[seq->i].key_tree;
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
break;
}
}
@@ -230,11 +233,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= key_tree->next_key_part;
walk_up_n_right:
- while (key_tree->index_order_prev() &&
- key_tree->index_order_prev() != &null_element)
+ while (key_tree->index_order_prev(seq->key_parts) &&
+ key_tree->index_order_prev(seq->key_parts) != &null_element)
{
/* Step up */
- key_tree= key_tree->index_order_prev();
+ key_tree= key_tree->index_order_prev(seq->key_parts);
}
step_down_to(seq, key_tree);
}
1
0