[Commits] f4a080c: Fixes of MDEV-30538 and MDEV-30586 for 10.4 adjusted for 11.0.
by IgorBabaev 10 Feb '23
by IgorBabaev 10 Feb '23
10 Feb '23
revision-id: f4a080c50bab40b8c14f085a09b8448520925a28 (mariadb-10.11.1-31-gf4a080c)
parent(s): 834e8d6c4616611b8b7ea91abbca761a1ebabff5
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-02-09 20:45:04 -0800
message:
Fixes of MDEV-30538 and MDEV-30586 for 10.4 adjusted for 11.0.
The commits for MDEV-30538 and MDEV-30586 could not be cherry-picked into
11.0 separately.
---
mysql-test/main/delete.result | 39 +++++++++-
mysql-test/main/delete.test | 41 ++++++++++-
mysql-test/main/multi_update.result | 120 +++++++++++++++++++++++++++++++
mysql-test/main/multi_update.test | 70 ++++++++++++++++++
mysql-test/main/update_use_source.result | 18 +++--
sql/item_subselect.cc | 6 +-
sql/sql_yacc.yy | 5 +-
7 files changed, 285 insertions(+), 14 deletions(-)
diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index 51257d2..694c604 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -525,7 +525,38 @@ DELETE v2 FROM v2;
ERROR HY000: Can not delete from join view 'test.v2'
DROP VIEW v2, v1;
DROP TABLE t1, t2;
-End of 10.10 tests
+End of 5.5 tests
+#
+# MDEV-30586: DELETE with WHERE containing nested subquery
+# with set function aggregated in outer subquery
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+7
+delete from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+update t1 set t1.a=t1.a+10
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+drop table t1,t2,t3;
+End of 10.4 tests
#
# MDEV-29428: DELETE with ORDER BY without LIMIT clause
#
@@ -545,7 +576,8 @@ explain delete from t1
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a ALL NULL NULL NULL NULL 8
delete from t1
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
select *from t1;
@@ -561,7 +593,8 @@ where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
order by c2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a ALL NULL NULL NULL NULL 8
delete from t1
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
order by c2;
diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test
index 1e3848f..ff9da9b 100644
--- a/mysql-test/main/delete.test
+++ b/mysql-test/main/delete.test
@@ -583,7 +583,46 @@ DELETE v2 FROM v2;
DROP VIEW v2, v1;
DROP TABLE t1, t2;
---echo End of 10.10 tests
+--echo End of 5.5 tests
+
+--echo #
+--echo # MDEV-30586: DELETE with WHERE containing nested subquery
+--echo # with set function aggregated in outer subquery
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+
+let $c=
+ t1.a in (select t3.a from t3 group by t3.a
+ having t3.a > any (select t2.b from t2
+ where t2.b*10 < sum(t3.b)));
+
+eval
+select * from t1
+ where $c;
+
+eval
+delete from t1
+ where $c;
+
+eval
+select * from t1
+ where $c;
+
+eval
+update t1 set t1.a=t1.a+10
+ where $c;
+
+drop table t1,t2,t3;
+
+--echo End of 10.4 tests
--echo #
--echo # MDEV-29428: DELETE with ORDER BY without LIMIT clause
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index ae661fa..de36182 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -1267,3 +1267,123 @@ EXPLAIN
}
}
DROP TABLES t1, t2;
+# End of 10.3 tests
+#
+# MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in
+#
+create table t1 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t1 values
+(1,1,1),(3,2,2),(1,3,3),
+(2,1,4),(2,2,5),(4,3,6),
+(2,4,7),(2,5,8);
+create table t2 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t2 values
+(1,7,1),(1,8,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+create table t3 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t3 values
+(1,1,1),(1,2,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+insert into t3 select c1+1, c2+2, c3 from t3;
+insert into t3 select c1, c2+2, c3 from t3;
+analyze table t1,t2,t3 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+test.t3 analyze status Engine-independent statistics collected
+test.t3 analyze status OK
+explain select * from t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t3 ref idx idx 5 test.t1.c2 3
+2 MATERIALIZED t2 range idx idx 5 NULL 3 Using index condition; Using where
+explain delete from t1 using t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index
+2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where
+explain update t1,t3 set t1.c1 = t1.c1+10
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index
+2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where
+create table t as select * from t1;
+select * from t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+c1 c2 c3 c1 c2 c3
+2 1 4 1 1 1
+2 1 4 2 1 4
+2 2 5 1 2 2
+2 2 5 2 2 5
+2 4 7 2 4 7
+2 4 7 2 4 2
+2 4 7 3 4 5
+2 4 7 1 4 2
+2 4 7 2 4 5
+2 5 8 2 5 8
+2 5 8 2 5 3
+2 5 8 3 5 6
+2 5 8 1 5 3
+2 5 8 2 5 6
+2 5 8 2 5 1
+2 5 8 3 5 4
+select * from t1;
+c1 c2 c3
+1 1 1
+3 2 2
+1 3 3
+2 1 4
+2 2 5
+4 3 6
+2 4 7
+2 5 8
+delete from t1 using t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+select * from t1;
+c1 c2 c3
+1 1 1
+3 2 2
+1 3 3
+4 3 6
+truncate table t1;
+insert into t1 select * from t;
+analyze table t1 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status Table is already up to date
+update t1,t3 set t1.c1 = t1.c1+10
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+select * from t1;
+c1 c2 c3
+1 1 1
+3 2 2
+1 3 3
+12 1 4
+12 2 5
+4 3 6
+12 4 7
+12 5 8
+drop table t1,t2,t3,t;
+# End of 10.4 tests
diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test
index 63648dc..39c9f41 100644
--- a/mysql-test/main/multi_update.test
+++ b/mysql-test/main/multi_update.test
@@ -1128,3 +1128,73 @@ EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=2 WHERE t2.part=1 AND
EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=2 AND t1.part=2;
DROP TABLES t1, t2;
+
+--echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-30538: multi-table UPDATE/DELETE with possible exists-to-in
+--echo #
+
+create table t1 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t1 values
+(1,1,1),(3,2,2),(1,3,3),
+(2,1,4),(2,2,5),(4,3,6),
+(2,4,7),(2,5,8);
+
+create table t2 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t2 values
+(1,7,1),(1,8,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+
+create table t3 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t3 values
+(1,1,1),(1,2,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+insert into t3 select c1+1, c2+2, c3 from t3;
+insert into t3 select c1, c2+2, c3 from t3;
+
+analyze table t1,t2,t3 persistent for all;
+
+let $c=
+ t1.c2 = t3.c2 and
+ t1.c1 > 1 and
+ exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+
+let $q1=
+select * from t1,t3
+where $c;
+
+eval explain $q1;
+
+let $q2=
+delete from t1 using t1,t3
+where $c;
+
+eval explain $q2;
+
+let $q3=
+update t1,t3 set t1.c1 = t1.c1+10
+where $c;
+
+eval explain $q3;
+
+create table t as select * from t1;
+
+eval $q1;
+select * from t1;
+
+eval $q2;
+select * from t1;
+
+truncate table t1;
+insert into t1 select * from t;
+analyze table t1 persistent for all;
+
+eval $q3;
+select * from t1;
+
+drop table t1,t2,t3,t;
+
+--echo # End of 10.4 tests
diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result
index 320f5b6..e4ba0e9 100644
--- a/mysql-test/main/update_use_source.result
+++ b/mysql-test/main/update_use_source.result
@@ -76,7 +76,8 @@ rollback;
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a ALL NULL NULL NULL NULL 8
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
@@ -316,8 +317,9 @@ rollback;
#
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where
-2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 4 Using index
+1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
@@ -557,8 +559,9 @@ rollback;
#
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where
-2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index
+1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
@@ -799,8 +802,9 @@ rollback;
#
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition; Using where
-2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index
+1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using index condition
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 9b4e60b..b704de0 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -2906,7 +2906,11 @@ bool Item_exists_subselect::select_prepare_to_be_in()
bool trans_res= FALSE;
DBUG_ENTER("Item_exists_subselect::select_prepare_to_be_in");
if (!optimizer &&
- thd->lex->sql_command == SQLCOM_SELECT &&
+ (thd->lex->sql_command == SQLCOM_SELECT ||
+ thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
+ thd->lex->sql_command == SQLCOM_DELETE_MULTI ||
+ thd->lex->sql_command == SQLCOM_UPDATE ||
+ thd->lex->sql_command == SQLCOM_DELETE) &&
!unit->first_select()->is_part_of_union() &&
optimizer_flag(thd, OPTIMIZER_SWITCH_EXISTS_TO_IN) &&
(is_top_level_item() ||
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index affd8f7..5d86041 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13377,12 +13377,13 @@ delete_part2:
{
LEX *lex= Lex;
lex->last_table()->vers_conditions= lex->vers_conditions;
- lex->pop_select(); //main select
lex->sql_command= SQLCOM_DELETE;
if (!(lex->m_sql_cmd=
new (thd->mem_root) Sql_cmd_delete(false)))
MYSQL_YYABORT;
}
+ stmt_end
+ {}
;
delete_single_table:
@@ -13433,12 +13434,12 @@ single_multi:
LEX *lex= Lex;
if ($3)
Select->order_list= *($3);
- lex->pop_select(); //main select
lex->sql_command= SQLCOM_DELETE;
if (!(lex->m_sql_cmd=
new (thd->mem_root) Sql_cmd_delete(false)))
MYSQL_YYABORT;
}
+ stmt_end {}
| table_alias_ref_list
{
LEX *lex= Lex;
1
0
[Commits] 45b0658: MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error
by IgorBabaev 09 Feb '23
by IgorBabaev 09 Feb '23
09 Feb '23
revision-id: 45b06584f0e26414fbb41c8d20cc082fd178461c (mariadb-10.4.25-178-g45b0658)
parent(s): 40adf52d1c26b398cbf47339895e38220bad7641
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-02-08 19:24:15 -0800
message:
MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error
The parser code for single-table DELETE missed the call of the function
LEX::check_main_unit_semantics(). As a result the the field nested level
of SELECT_LEX structures remained set 0 for all non-top level selects.
This could lead to different kind of problems. In particular this did not
allow to determine properly the selects where set functions had to be
aggregated when they were used in inner subqueries.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/delete.result | 32 ++++++++++++++++++++++++++++++++
mysql-test/main/delete.test | 41 +++++++++++++++++++++++++++++++++++++++++
sql/sql_yacc.yy | 4 ++++
sql/sql_yacc_ora.yy | 4 ++++
4 files changed, 81 insertions(+)
diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index ed3683d..7a9963a 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -525,3 +525,35 @@ DELETE v2 FROM v2;
ERROR HY000: Can not delete from join view 'test.v2'
DROP VIEW v2, v1;
DROP TABLE t1, t2;
+End of 5.5 tests
+#
+# MDEV-30586: DELETE with WHERE containing nested subquery
+# with set function aggregated in outer subquery
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+7
+delete from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+update t1 set t1.a=t1.a+10
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+drop table t1,t2,t3;
+End of 10.4 tests
diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test
index c824206..6d898ec 100644
--- a/mysql-test/main/delete.test
+++ b/mysql-test/main/delete.test
@@ -582,3 +582,44 @@ DELETE v2 FROM v2;
DROP VIEW v2, v1;
DROP TABLE t1, t2;
+
+--echo End of 5.5 tests
+
+--echo #
+--echo # MDEV-30586: DELETE with WHERE containing nested subquery
+--echo # with set function aggregated in outer subquery
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+
+let $c=
+ t1.a in (select t3.a from t3 group by t3.a
+ having t3.a > any (select t2.b from t2
+ where t2.b*10 < sum(t3.b)));
+
+eval
+select * from t1
+ where $c;
+
+eval
+delete from t1
+ where $c;
+
+eval
+select * from t1
+ where $c;
+
+eval
+update t1 set t1.a=t1.a+10
+ where $c;
+
+drop table t1,t2,t3;
+
+--echo End of 10.4 tests
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0221e4e..902b7d1 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -14032,6 +14032,8 @@ delete_part2:
{
Lex->last_table()->vers_conditions= Lex->vers_conditions;
Lex->pop_select(); //main select
+ if (Lex->check_main_unit_semantics())
+ MYSQL_YYABORT;
}
;
@@ -14068,6 +14070,8 @@ single_multi:
if ($3)
Select->order_list= *($3);
Lex->pop_select(); //main select
+ if (Lex->check_main_unit_semantics())
+ MYSQL_YYABORT;
}
| table_wild_list
{
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index f6cda75..1e1af94 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -14162,6 +14162,8 @@ delete_part2:
{
Lex->last_table()->vers_conditions= Lex->vers_conditions;
Lex->pop_select(); //main select
+ if (Lex->check_main_unit_semantics())
+ MYSQL_YYABORT;
}
;
@@ -14198,6 +14200,8 @@ single_multi:
if ($3)
Select->order_list= *($3);
Lex->pop_select(); //main select
+ if (Lex->check_main_unit_semantics())
+ MYSQL_YYABORT;
}
| table_wild_list
{
1
0
[Commits] f270eb0: MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error
by IgorBabaev 07 Feb '23
by IgorBabaev 07 Feb '23
07 Feb '23
revision-id: f270eb0d1ed3459b672a42f55a717191d7346ea6 (mariadb-10.4.25-178-gf270eb0)
parent(s): 40adf52d1c26b398cbf47339895e38220bad7641
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-02-06 22:35:48 -0800
message:
MDEV-30586 DELETE with aggregation in subquery of WHERE returns bogus error
The parser code for single-table DELETE missed the call of the function
LEX::check_main_unit_semantics(). As a result the the field nested level
of SELECT_LEX structures remained set 0 for all non-top level selects.
This could lead to different kind of problems. In particular this did not
allow to determine properly the selects where set functions had to be
aggregated when they were used in inner subqueries.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/delete.result | 32 ++++++++++++++++++++++++++++++++
mysql-test/main/delete.test | 41 +++++++++++++++++++++++++++++++++++++++++
sql/sql_yacc.yy | 4 ++++
3 files changed, 77 insertions(+)
diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index ed3683d..7a9963a 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -525,3 +525,35 @@ DELETE v2 FROM v2;
ERROR HY000: Can not delete from join view 'test.v2'
DROP VIEW v2, v1;
DROP TABLE t1, t2;
+End of 5.5 tests
+#
+# MDEV-30586: DELETE with WHERE containing nested subquery
+# with set function aggregated in outer subquery
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+7
+delete from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+select * from t1
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+a
+update t1 set t1.a=t1.a+10
+where t1.a in (select t3.a from t3 group by t3.a
+having t3.a > any (select t2.b from t2
+where t2.b*10 < sum(t3.b)));
+drop table t1,t2,t3;
+End of 10.4 tests
diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test
index c824206..6d898ec 100644
--- a/mysql-test/main/delete.test
+++ b/mysql-test/main/delete.test
@@ -582,3 +582,44 @@ DELETE v2 FROM v2;
DROP VIEW v2, v1;
DROP TABLE t1, t2;
+
+--echo End of 5.5 tests
+
+--echo #
+--echo # MDEV-30586: DELETE with WHERE containing nested subquery
+--echo # with set function aggregated in outer subquery
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+create table t2 (b int);
+insert into t2 values (2), (1), (4), (7);
+
+create table t3 (a int, b int);
+insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40);
+
+let $c=
+ t1.a in (select t3.a from t3 group by t3.a
+ having t3.a > any (select t2.b from t2
+ where t2.b*10 < sum(t3.b)));
+
+eval
+select * from t1
+ where $c;
+
+eval
+delete from t1
+ where $c;
+
+eval
+select * from t1
+ where $c;
+
+eval
+update t1 set t1.a=t1.a+10
+ where $c;
+
+drop table t1,t2,t3;
+
+--echo End of 10.4 tests
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0221e4e..902b7d1 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -14032,6 +14032,8 @@ delete_part2:
{
Lex->last_table()->vers_conditions= Lex->vers_conditions;
Lex->pop_select(); //main select
+ if (Lex->check_main_unit_semantics())
+ MYSQL_YYABORT;
}
;
@@ -14068,6 +14070,8 @@ single_multi:
if ($3)
Select->order_list= *($3);
Lex->pop_select(); //main select
+ if (Lex->check_main_unit_semantics())
+ MYSQL_YYABORT;
}
| table_wild_list
{
1
0
[Commits] 85b0a9c: MDEV-30538 Plans for SELECT and multi-table UPDATE/DELETE unexpectedly differ
by IgorBabaev 03 Feb '23
by IgorBabaev 03 Feb '23
03 Feb '23
revision-id: 85b0a9cdf92bddbb4084024c9eb851466ecb8fce (mariadb-10.4.27-70-g85b0a9c)
parent(s): b05218e08f45a17f46d1b73cbb9dcb2969dc04cd
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-02-02 22:38:32 -0800
message:
MDEV-30538 Plans for SELECT and multi-table UPDATE/DELETE unexpectedly differ
This patch allowed transformation of EXISTS subqueries into equivalent
IN predicands at the top level of WHERE conditions for multi-table UPDATE
and DELETE statements. There was no reason to prohibit the transformation
for such statements. The transformation provides more opportunities of
using semi-join optimizations.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/multi_update.result | 120 +++++++++++++++++++++++++++++++
mysql-test/main/multi_update.test | 70 ++++++++++++++++++
mysql-test/main/update_use_source.result | 12 ++--
sql/item_subselect.cc | 4 +-
4 files changed, 201 insertions(+), 5 deletions(-)
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index 61e04c3..d6cf9ba 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -1251,3 +1251,123 @@ EXPLAIN
}
}
DROP TABLES t1, t2;
+# End of 10.3 tests
+#
+# MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in
+#
+create table t1 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t1 values
+(1,1,1),(3,2,2),(1,3,3),
+(2,1,4),(2,2,5),(4,3,6),
+(2,4,7),(2,5,8);
+create table t2 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t2 values
+(1,7,1),(1,8,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+create table t3 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t3 values
+(1,1,1),(1,2,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+insert into t3 select c1+1, c2+2, c3 from t3;
+insert into t3 select c1, c2+2, c3 from t3;
+analyze table t1,t2,t3 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status OK
+test.t3 analyze status Engine-independent statistics collected
+test.t3 analyze status OK
+explain select * from t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t3 ref idx idx 5 test.t1.c2 3
+2 MATERIALIZED t2 range idx idx 5 NULL 3 Using index condition; Using where
+explain delete from t1 using t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index
+2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where
+explain update t1,t3 set t1.c1 = t1.c1+10
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index
+2 MATERIALIZED t2 range idx idx 5 NULL 3 Using where
+create table t as select * from t1;
+select * from t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+c1 c2 c3 c1 c2 c3
+2 1 4 1 1 1
+2 1 4 2 1 4
+2 2 5 1 2 2
+2 2 5 2 2 5
+2 4 7 2 4 7
+2 4 7 2 4 2
+2 4 7 3 4 5
+2 4 7 1 4 2
+2 4 7 2 4 5
+2 5 8 2 5 8
+2 5 8 2 5 3
+2 5 8 3 5 6
+2 5 8 1 5 3
+2 5 8 2 5 6
+2 5 8 2 5 1
+2 5 8 3 5 4
+select * from t1;
+c1 c2 c3
+1 1 1
+3 2 2
+1 3 3
+2 1 4
+2 2 5
+4 3 6
+2 4 7
+2 5 8
+delete from t1 using t1,t3
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+select * from t1;
+c1 c2 c3
+1 1 1
+3 2 2
+1 3 3
+4 3 6
+truncate table t1;
+insert into t1 select * from t;
+analyze table t1 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status Table is already up to date
+update t1,t3 set t1.c1 = t1.c1+10
+where t1.c2 = t3.c2 and
+t1.c1 > 1 and
+exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+select * from t1;
+c1 c2 c3
+1 1 1
+3 2 2
+1 3 3
+12 1 4
+12 2 5
+4 3 6
+12 4 7
+12 5 8
+drop table t1,t2,t3,t;
+# End of 10.4 tests
diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test
index 54c6491..48e6250 100644
--- a/mysql-test/main/multi_update.test
+++ b/mysql-test/main/multi_update.test
@@ -1130,3 +1130,73 @@ EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=2 WHERE t2.part=1 AND
EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=2 AND t1.part=2;
DROP TABLES t1, t2;
+
+--echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in
+--echo #
+
+create table t1 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t1 values
+(1,1,1),(3,2,2),(1,3,3),
+(2,1,4),(2,2,5),(4,3,6),
+(2,4,7),(2,5,8);
+
+create table t2 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t2 values
+(1,7,1),(1,8,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+
+create table t3 (c1 int, c2 int, c3 int, index idx(c2));
+insert into t3 values
+(1,1,1),(1,2,2),(1,3,3),
+(2,1,4),(2,2,5),(2,3,6),
+(2,4,7),(2,5,8);
+insert into t3 select c1+1, c2+2, c3 from t3;
+insert into t3 select c1, c2+2, c3 from t3;
+
+analyze table t1,t2,t3 persistent for all;
+
+let $c=
+ t1.c2 = t3.c2 and
+ t1.c1 > 1 and
+ exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4);
+
+let $q1=
+select * from t1,t3
+where $c;
+
+eval explain $q1;
+
+let $q2=
+delete from t1 using t1,t3
+where $c;
+
+eval explain $q2;
+
+let $q3=
+update t1,t3 set t1.c1 = t1.c1+10
+where $c;
+
+eval explain $q3;
+
+create table t as select * from t1;
+
+eval $q1;
+select * from t1;
+
+eval $q2;
+select * from t1;
+
+truncate table t1;
+insert into t1 select * from t;
+analyze table t1 persistent for all;
+
+eval $q3;
+select * from t1;
+
+drop table t1,t2,t3,t;
+
+--echo # End of 10.4 tests
diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result
index 9e43b54..5778767 100644
--- a/mysql-test/main/update_use_source.result
+++ b/mysql-test/main/update_use_source.result
@@ -76,7 +76,8 @@ rollback;
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a ALL NULL NULL NULL NULL 8
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
@@ -317,7 +318,8 @@ rollback;
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where
-2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 4 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
@@ -558,7 +560,8 @@ rollback;
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where
-2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
@@ -800,7 +803,8 @@ rollback;
explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where
-2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+2 MATERIALIZED a range t1_c2 t1_c2 5 NULL 2 Using where; Using index
start transaction;
update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
affected rows: 4
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 461bd9f..1454073 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -2825,7 +2825,9 @@ bool Item_exists_subselect::select_prepare_to_be_in()
bool trans_res= FALSE;
DBUG_ENTER("Item_exists_subselect::select_prepare_to_be_in");
if (!optimizer &&
- thd->lex->sql_command == SQLCOM_SELECT &&
+ (thd->lex->sql_command == SQLCOM_SELECT ||
+ thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
+ thd->lex->sql_command == SQLCOM_DELETE_MULTI) &&
!unit->first_select()->is_part_of_union() &&
optimizer_flag(thd, OPTIMIZER_SWITCH_EXISTS_TO_IN) &&
(is_top_level_item() ||
1
0
31 Jan '23
revision-id: c4fa6c3c4eaacefd9bf50b5c88c2c72474a15bc5 (mariadb-10.4.27-71-gc4fa6c3)
parent(s): c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-01-31 13:14:53 -0800
message:
MDEV-30218 Incorrect optimization for rowid_filtering
Correction over the last patch for this MDEV.
---
mysql-test/main/join_nested_jcl6.result | 2 +-
mysql-test/main/opt_trace.result | 13 +-
.../main/opt_trace_index_merge_innodb.result | 1 -
mysql-test/main/range.result | 2 +-
mysql-test/main/rowid_filter.result | 189 ++++++++++++---------
mysql-test/main/rowid_filter_innodb.result | 102 ++++++++---
mysql-test/main/select.result | 2 +-
mysql-test/main/select_jcl6.result | 2 +-
mysql-test/main/select_pkeycache.result | 2 +-
mysql-test/main/selectivity.result | 2 +-
sql/sql_select.cc | 132 +++++++-------
11 files changed, 251 insertions(+), 198 deletions(-)
diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result
index 7226a5d..91fe367 100644
--- a/mysql-test/main/join_nested_jcl6.result
+++ b/mysql-test/main/join_nested_jcl6.result
@@ -2085,7 +2085,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t5 ALL NULL NULL NULL NULL 3
-1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
1 SIMPLE t6 range PRIMARY,b_i PRIMARY 4 NULL 3 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join)
1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 2eef0da..92cd3eb 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1016,7 +1016,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 200,
"chosen": true
@@ -1073,7 +1072,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 200,
"chosen": true
@@ -2120,7 +2118,6 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"access_type": "ref",
"index": "a_c",
"used_range_estimates": true,
- "rowid_filter_skipped": "worst/max seeks clipping",
"rows": 180,
"cost": 92,
"chosen": true
@@ -3346,7 +3343,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"access_type": "ref",
"index": "pk",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 2,
"chosen": true
@@ -3355,7 +3351,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"access_type": "ref",
"index": "pk_a",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 2,
"chosen": false,
@@ -3365,7 +3360,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"access_type": "ref",
"index": "pk_a_b",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 1.0043,
"chosen": true
@@ -3974,6 +3968,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"best_access_path": {
"considered_access_paths": [
{
+ "rowid_filter_skipped": "cost_factor <= 0",
"access_type": "range",
"resulting_rows": 3,
"cost": 1.407,
@@ -4000,7 +3995,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"index": "a",
"used_range_estimates": false,
"cause": "not better than ref estimates",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 3.007,
"chosen": true
@@ -4030,6 +4024,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"best_access_path": {
"considered_access_paths": [
{
+ "rowid_filter_skipped": "cost_factor <= 0",
"access_type": "range",
"resulting_rows": 3,
"cost": 1.407,
@@ -4056,7 +4051,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"index": "a",
"used_range_estimates": false,
"cause": "not better than ref estimates",
- "rowid_filter_skipped": "worst/max seeks clipping",
"rows": 2,
"cost": 3.014,
"chosen": true
@@ -8069,7 +8063,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"index": "b",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 20,
"chosen": true
@@ -8273,7 +8266,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 20,
"chosen": true
@@ -8341,7 +8333,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 200,
"chosen": true
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 82f09df..5786f74 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -208,7 +208,6 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"access_type": "ref",
"index": "key1",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 2,
"chosen": true
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index a3ce10f..0e728d7 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -281,7 +281,7 @@ INSERT INTO t1 VALUES
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where
+1 SIMPLE t1 ref|filter a,b b|a 5|5 const 15 (5%) Using where; Using rowid filter
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
a b
DROP TABLE t1;
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index b35021b..efe1fe1 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -335,8 +335,8 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
o_totalprice between 200000 and 230000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition
-1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using rowid filter
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) Using where; Using rowid filter
set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
@@ -346,40 +346,40 @@ EXPLAIN
"query_block": {
"select_id": 1,
"table": {
- "table_name": "lineitem",
+ "table_name": "orders",
"access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
+ "rows": 69,
+ "filtered": 100,
+ "index_condition": "orders.o_totalprice between 200000 and 230000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
"possible_keys": [
"PRIMARY",
"i_l_shipdate",
"i_l_orderkey",
"i_l_orderkey_quantity"
],
- "key": "i_l_shipdate",
- "key_length": "4",
- "used_key_parts": ["l_shipDATE"],
- "rows": 98,
- "filtered": 100,
- "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
- },
- "table": {
- "table_name": "orders",
- "access_type": "eq_ref",
- "possible_keys": ["PRIMARY", "i_o_totalprice"],
- "key": "PRIMARY",
+ "key": "i_l_orderkey",
"key_length": "4",
- "used_key_parts": ["o_orderkey"],
- "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
"rowid_filter": {
"range": {
- "key": "i_o_totalprice",
- "used_key_parts": ["o_totalprice"]
+ "key": "i_l_shipdate",
+ "used_key_parts": ["l_shipDATE"]
},
- "rows": 69,
- "selectivity_pct": 4.6
+ "rows": 98,
+ "selectivity_pct": 1.632
},
- "rows": 1,
- "filtered": 4.6,
- "attached_condition": "orders.o_totalprice between 200000 and 230000"
+ "rows": 4,
+ "filtered": 1.632,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
}
}
}
@@ -388,8 +388,8 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
o_totalprice between 200000 and 230000;
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
-1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition
-1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) 0.11 (10%) 4.60 100.00 Using where; Using rowid filter
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) 0.15 (2%) 1.63 100.00 Using where; Using rowid filter
set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
@@ -401,53 +401,53 @@ ANALYZE
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
- "table_name": "lineitem",
+ "table_name": "orders",
"access_type": "range",
- "possible_keys": [
- "PRIMARY",
- "i_l_shipdate",
- "i_l_orderkey",
- "i_l_orderkey_quantity"
- ],
- "key": "i_l_shipdate",
- "key_length": "4",
- "used_key_parts": ["l_shipDATE"],
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
"r_loops": 1,
- "rows": 98,
- "r_rows": 98,
+ "rows": 69,
+ "r_rows": 71,
"r_total_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100,
- "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
+ "index_condition": "orders.o_totalprice between 200000 and 230000"
},
"table": {
- "table_name": "orders",
- "access_type": "eq_ref",
- "possible_keys": ["PRIMARY", "i_o_totalprice"],
- "key": "PRIMARY",
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_orderkey",
"key_length": "4",
- "used_key_parts": ["o_orderkey"],
- "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
"rowid_filter": {
"range": {
- "key": "i_o_totalprice",
- "used_key_parts": ["o_totalprice"]
+ "key": "i_l_shipdate",
+ "used_key_parts": ["l_shipDATE"]
},
- "rows": 69,
- "selectivity_pct": 4.6,
- "r_rows": 71,
- "r_lookups": 96,
- "r_selectivity_pct": 10.417,
+ "rows": 98,
+ "selectivity_pct": 1.632,
+ "r_rows": 98,
+ "r_lookups": 476,
+ "r_selectivity_pct": 2.3109,
"r_buffer_size": "REPLACED",
"r_filling_time_ms": "REPLACED"
},
- "r_loops": 98,
- "rows": 1,
- "r_rows": 0.1122,
+ "r_loops": 71,
+ "rows": 4,
+ "r_rows": 0.1549,
"r_total_time_ms": "REPLACED",
- "filtered": 4.6,
+ "filtered": 1.632,
"r_filtered": 100,
- "attached_condition": "orders.o_totalprice between 200000 and 230000"
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
}
}
}
@@ -2072,7 +2072,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where
-1 PRIMARY t1 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where
+1 PRIMARY t1 ref|filter a1,b1 a1|b1 5|4 test.t2.a2 36 (29%) 28.75 Using where; Using rowid filter
2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2
@@ -2097,6 +2097,14 @@ EXPLAIN
"key_length": "5",
"used_key_parts": ["a1"],
"ref": ["test.t2.a2"],
+ "rowid_filter": {
+ "range": {
+ "key": "b1",
+ "used_key_parts": ["b1"]
+ },
+ "rows": 115,
+ "selectivity_pct": 28.75
+ },
"rows": 36,
"filtered": 28.75,
"attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2"
@@ -2179,7 +2187,7 @@ test.t1 analyze status OK
explain
SELECT * FROM t1 WHERE a > 0 AND b=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 5 const 151 Using where
+1 SIMPLE t1 ref|filter a,b b|a 5|5 const 151 (17%) Using where; Using rowid filter
SELECT * FROM t1 WHERE a > 0 AND b=0;
a b
1 0
@@ -2514,19 +2522,32 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 44,
+ "r_lookups": 1000,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
- "r_rows": 44,
+ "rows": 921,
+ "r_rows": 0,
"r_total_time_ms": "REPLACED",
- "filtered": 9.21,
- "r_filtered": 0,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "filtered": 0.44,
+ "r_filtered": 100,
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
@@ -2559,19 +2580,31 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 0,
+ "r_lookups": 0,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
+ "rows": 911,
"r_rows": 0,
- "r_total_time_ms": "REPLACED",
- "filtered": 9.11,
+ "filtered": 0.44,
"r_filtered": 100,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 1bf63d9..d7f1fe4 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -1997,7 +1997,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where
-1 PRIMARY t1 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where
+1 PRIMARY t1 ref|filter a1,b1 a1|b1 5|4 test.t2.a2 36 (29%) 28.75 Using where; Using rowid filter
2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2
@@ -2022,6 +2022,14 @@ EXPLAIN
"key_length": "5",
"used_key_parts": ["a1"],
"ref": ["test.t2.a2"],
+ "rowid_filter": {
+ "range": {
+ "key": "b1",
+ "used_key_parts": ["b1"]
+ },
+ "rows": 115,
+ "selectivity_pct": 28.75
+ },
"rows": 36,
"filtered": 28.75,
"attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2"
@@ -2104,7 +2112,7 @@ test.t1 analyze status OK
explain
SELECT * FROM t1 WHERE a > 0 AND b=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 5 const 128 Using where
+1 SIMPLE t1 ref|filter a,b b|a 5|5 const 128 (14%) Using where; Using rowid filter
SELECT * FROM t1 WHERE a > 0 AND b=0;
a b
1 0
@@ -2439,19 +2447,32 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 44,
+ "r_lookups": 1000,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
- "r_rows": 44,
+ "rows": 921,
+ "r_rows": 0,
"r_total_time_ms": "REPLACED",
- "filtered": 9.21,
- "r_filtered": 0,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "filtered": 0.44,
+ "r_filtered": 100,
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
@@ -2484,19 +2505,31 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 0,
+ "r_lookups": 0,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
+ "rows": 911,
"r_rows": 0,
- "r_total_time_ms": "REPLACED",
- "filtered": 9.11,
+ "filtered": 0.44,
"r_filtered": 100,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
@@ -2671,7 +2704,7 @@ count(*)
5
explain extended select count(*) from t1 where a between 21 and 30 and b=2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref b,a b 5 const 24 9.60 Using where
+1 SIMPLE t1 ref|filter b,a b|a 5|5 const 24 (10%) 9.60 Using where; Using rowid filter
Warnings:
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` between 21 and 30
select * from t1 where a between 21 and 30 and b=2;
@@ -3133,7 +3166,7 @@ WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
timestamp >= DATE_ADD('2017-01-30 08:24:51', INTERVAL -1 MONTH)
ORDER BY timestamp DESC;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain 98 const 40 33.33 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp 98|4 const 40 (33%) 33.33 Using index condition; Using where; Using filesort; Using rowid filter
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_stree
t2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test
`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>('2017-01-30 08:24:51' + interval -1 month) order by `test`.`t1`.`timestamp` desc
SET optimizer_switch=@save_optimizer_switch;
@@ -3382,7 +3415,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index
1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where
-1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 24 14.46 Using where
+1 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 24 (14%) 14.46 Using where; Using rowid filter
Warnings:
Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
@@ -3498,7 +3531,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index
1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where; Using join buffer (flat, BKA join); Rowid-ordered scan
-1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 24 14.46 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan
+1 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 24 (14%) 14.46 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter
Warnings:
Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
@@ -3616,9 +3649,22 @@ ANALYZE
"key_length": "8",
"used_key_parts": ["aceid"],
"ref": ["test.a.id"],
+ "rowid_filter": {
+ "range": {
+ "key": "filt_fh",
+ "used_key_parts": ["fh"]
+ },
+ "rows": 81,
+ "selectivity_pct": 14.464,
+ "r_rows": 80,
+ "r_lookups": 80,
+ "r_selectivity_pct": 40,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
"rows": 24,
- "r_rows": 80,
+ "r_rows": 32,
"r_total_time_ms": "REPLACED",
"filtered": 14.464,
"r_filtered": 100
@@ -3628,7 +3674,7 @@ ANALYZE
"join_type": "BKA",
"mrr_type": "Rowid-ordered scan",
"attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)",
- "r_filtered": 40
+ "r_filtered": 100
}
}
}
@@ -3732,7 +3778,7 @@ WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 60 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ref c1,i1 i1 5 test.t1.pk 20 100.00 Using index condition; Using where
+2 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 38 (25%) 25.00 Using where; Full scan on NULL key; Using rowid filter
2 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 60 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
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index fc3a290..6652b1a 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1
WHERE ID_better=1 AND ID1_with_null IS NULL AND
(ID2_with_null=1 OR ID2_with_null=2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where
+1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter
DROP TABLE t1;
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index e7c2e3e..cef6e0f 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3755,7 +3755,7 @@ EXPLAIN SELECT * FROM t1
WHERE ID_better=1 AND ID1_with_null IS NULL AND
(ID2_with_null=1 OR ID2_with_null=2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where
+1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter
DROP TABLE t1;
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index fc3a290..6652b1a 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1
WHERE ID_better=1 AND ID1_with_null IS NULL AND
(ID2_with_null=1 OR ID2_with_null=2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where
+1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter
DROP TABLE t1;
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 4113779..7e32023 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1661,7 +1661,7 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
# gives selectivity data
explain extended select * from t1 where a in (17,51,5) and b=2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref b,a b 5 const 58 2.90 Using where
+1 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5)
drop table t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0f8ead4..4bdfb65 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7557,7 +7557,6 @@ best_access_path(JOIN *join,
rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
Json_writer_object trace_access_idx(thd);
- double eq_ref_rows= 0;
/*
full text keys require special treatment
*/
@@ -7596,8 +7595,7 @@ best_access_path(JOIN *join,
type= JT_EQ_REF;
trace_access_idx.add("access_type", join_type_str[type])
.add("index", keyinfo->name);
- eq_ref_rows= tmp = prev_record_reads(join_positions, idx,
- found_ref);
+ tmp = prev_record_reads(join_positions, idx, found_ref);
records=1.0;
}
else
@@ -7904,28 +7902,7 @@ best_access_path(JOIN *join,
(s->table->file->index_flags(start_key->key,0,1) &
HA_DO_RANGE_FILTER_PUSHDOWN))
{
- double rows;
- if (type == JT_EQ_REF)
- {
- /*
- Treat EQ_REF access in a special way:
- 1. We have no cost for index-only read. Assume its cost is 50% of
- the cost of the full read.
-
- 2. A regular ref access will do #record_count lookups, but eq_ref
- has "lookup cache" which reduces the number of lookups made.
- The estimation code uses prev_record_reads() call to estimate:
-
- tmp = prev_record_reads(join_positions, idx, found_ref);
-
- Set the effective number of rows from "tmp" here.
- */
- keyread_tmp= COST_ADD(eq_ref_rows / 2, s->startup_cost);
- rows= eq_ref_rows;
- }
- else
- rows= record_count * records;
-
+ double rows= record_count * records;
/*
If we use filter F with selectivity s the the cost of fetching data
by key using this filter will be
@@ -7947,46 +7924,53 @@ best_access_path(JOIN *join,
cost_of_fetching_1_row = tmp/rows
cost_of_fetching_1_key_tuple = keyread_tmp/rows
- access_cost_factor is the gain we expect for using rowid filter.
- An access_cost_factor of 1.0 means that keyread_tmp is 0
- (using key read is infinitely fast) and the gain for each row when
- using filter is great.
- An access_cost_factor if 0.0 means that using keyread has the
- same cost as reading rows, so there is no gain to get with
- filter.
- access_cost_factor should never be bigger than 1.0 (if all
- calculations are correct) as the cost of keyread should always be
- smaller than the cost of fetching the same number of keys + rows.
- access_cost_factor should also never be smaller than 0.0.
- The one exception is if number of records is 1 (eq_ref), then
- because we are comparing rows to cost of keyread_tmp, keyread_tmp
- is higher by 1.0. This is a big that will be fixed in a later
- version.
-
- If we have limited the cost (=tmp) of reading rows with 'worst_seek'
- we cannot use filters as the cost calculation below would cause
- tmp to become negative. The future resultion is to not limit
- cost with worst_seek.
+ Here's a more detailed explanation that uses the formulas behind
+ the function the call filter->get_adjusted_gain(). The function
+ takes as a parameter the number of probes/look-ups into the filter
+ that is equal to the number of fetched key entries that is equal to
+ the number of row fetches when no filter is used (assuming no
+ index condition pushdown is employed for the used key access).
+ Let this number be N. Then the total gain from using the filter is
+ N*a_adj - b where b is the cost of building the filter and
+ a_adj is calcilated as follows:
+ a - (1-access_cost_factor)*(1-s) =
+ (1+1_cond_eval_cost)*(1-s)-1_probe_cost - (1-access_cost_factor)*(1-s)
+ = (1-s)*(1_cond_eval_cost+access_cost_factor) - 1_probe_cost.
+ Here ((1-s)*(1_cond_eval_cost) * N is the gain from checking less
+ conditions pushed into the table, 1_probe_cost*N is the cost of the
+ probes and (1*s) * access_cost_factor * N must be the gain from
+ accessing less rows.
+ It does not matter how we calculate the cost of N full row fetches
+ cost_of_fetching_N_rows or
+ how we calculate the cost of fetching N key entries
+ cost_of_fetching_N_key_entries
+ the gain from less row fetches will be
+ (cost_of_fetching_N_rows - cost_of_fetching_N_key_entries) * (1-s)
+ and this should be equal to (1*s) * access_cost_factor * N.
+ Thus access_cost_factor must be calculated as
+ (cost_of_fetching_N_rows - cost_of_fetching_N_key_entries) / N.
+
+ For safety we clip cost_of_fetching_N_key_entries by the value
+ of cost_of_fetching_N_row though formally it's not necessary.
*/
- double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0);
- if (!(records < s->worst_seeks &&
- records <= thd->variables.max_seeks_for_key))
- trace_access_idx.add("rowid_filter_skipped", "worst/max seeks clipping");
- else if (access_cost_factor <= 0.0)
- trace_access_idx.add("rowid_filter_skipped", "cost_factor <= 0");
- else
+ /*
+ For eq_ref access we assume that the cost of fetching N key entries
+ is equal to the half of fetching N rows
+ */
+ double key_access_cost=
+ type == JT_EQ_REF ? 0.5 * tmp : MY_MIN(tmp, keyread_tmp);
+ double access_cost_factor= MY_MIN((tmp - key_access_cost) / rows, 1.0);
+
+ filter=
+ table->best_range_rowid_filter_for_partial_join(start_key->key,
+ rows,
+ access_cost_factor);
+ if (filter)
{
- filter=
- table->best_range_rowid_filter_for_partial_join(start_key->key,
- rows,
- access_cost_factor);
- if (filter)
- {
- tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows);
- DBUG_ASSERT(tmp >= 0);
- trace_access_idx.add("rowid_filter_key",
+ tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows);
+ DBUG_ASSERT(tmp >= 0);
+ trace_access_idx.add("rowid_filter_key",
s->table->key_info[filter->key_no].name);
- }
}
}
trace_access_idx.add("rows", records).add("cost", tmp);
@@ -8139,19 +8123,19 @@ best_access_path(JOIN *join,
uint key_no= s->quick->index;
/* See the comment concerning using rowid filter for with ref access */
- keyread_tmp= s->table->quick_index_only_costs[key_no] * record_count;
- double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0);
- if (access_cost_factor > 0.0)
+ double row_access_cost= s->quick->read_time * record_count;
+ double key_access_cost=
+ MY_MIN(row_access_cost,
+ s->table->quick_index_only_costs[key_no] * record_count);
+ double access_cost_factor= MY_MIN((row_access_cost - key_access_cost) /
+ rows, 1.0);
+ filter=
+ s->table->best_range_rowid_filter_for_partial_join(key_no, rows,
+ access_cost_factor);
+ if (filter)
{
- filter=
- s->table->
- best_range_rowid_filter_for_partial_join(key_no, rows,
- access_cost_factor);
- if (filter)
- {
- tmp-= filter->get_adjusted_gain(rows);
- DBUG_ASSERT(tmp >= 0);
- }
+ tmp-= filter->get_adjusted_gain(rows);
+ DBUG_ASSERT(tmp >= 0);
}
else
trace_access_scan.add("rowid_filter_skipped", "cost_factor <= 0");
1
0
31 Jan '23
revision-id: 595b916272e377bb00298e603abd9541dede9bf4 (mariadb-10.4.27-71-g595b916)
parent(s): c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-01-31 00:10:41 -0800
message:
MDEV-30218 Incorrect optimization for rowid_filtering
Correction over ths last patch for this MDEV.
---
mysql-test/main/join_nested_jcl6.result | 2 +-
mysql-test/main/opt_trace.result | 13 +-
.../main/opt_trace_index_merge_innodb.result | 1 -
mysql-test/main/range.result | 2 +-
mysql-test/main/rowid_filter.result | 189 ++++++++++++---------
mysql-test/main/rowid_filter_innodb.result | 102 ++++++++---
mysql-test/main/select.result | 2 +-
mysql-test/main/select_jcl6.result | 2 +-
mysql-test/main/select_pkeycache.result | 2 +-
mysql-test/main/selectivity.result | 2 +-
sql/sql_select.cc | 132 +++++++-------
11 files changed, 251 insertions(+), 198 deletions(-)
diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result
index 7226a5d..91fe367 100644
--- a/mysql-test/main/join_nested_jcl6.result
+++ b/mysql-test/main/join_nested_jcl6.result
@@ -2085,7 +2085,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t5 ALL NULL NULL NULL NULL 3
-1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
1 SIMPLE t6 range PRIMARY,b_i PRIMARY 4 NULL 3 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join)
1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 2eef0da..92cd3eb 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1016,7 +1016,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 200,
"chosen": true
@@ -1073,7 +1072,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 200,
"chosen": true
@@ -2120,7 +2118,6 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 {
"access_type": "ref",
"index": "a_c",
"used_range_estimates": true,
- "rowid_filter_skipped": "worst/max seeks clipping",
"rows": 180,
"cost": 92,
"chosen": true
@@ -3346,7 +3343,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"access_type": "ref",
"index": "pk",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 2,
"chosen": true
@@ -3355,7 +3351,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"access_type": "ref",
"index": "pk_a",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 2,
"chosen": false,
@@ -3365,7 +3360,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 {
"access_type": "ref",
"index": "pk_a_b",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 1.0043,
"chosen": true
@@ -3974,6 +3968,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"best_access_path": {
"considered_access_paths": [
{
+ "rowid_filter_skipped": "cost_factor <= 0",
"access_type": "range",
"resulting_rows": 3,
"cost": 1.407,
@@ -4000,7 +3995,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"index": "a",
"used_range_estimates": false,
"cause": "not better than ref estimates",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 3.007,
"chosen": true
@@ -4030,6 +4024,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"best_access_path": {
"considered_access_paths": [
{
+ "rowid_filter_skipped": "cost_factor <= 0",
"access_type": "range",
"resulting_rows": 3,
"cost": 1.407,
@@ -4056,7 +4051,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"index": "a",
"used_range_estimates": false,
"cause": "not better than ref estimates",
- "rowid_filter_skipped": "worst/max seeks clipping",
"rows": 2,
"cost": 3.014,
"chosen": true
@@ -8069,7 +8063,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"index": "b",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 20,
"chosen": true
@@ -8273,7 +8266,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 20,
"chosen": true
@@ -8341,7 +8333,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"index": "a",
"used_range_estimates": false,
"cause": "not available",
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 200,
"chosen": true
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 82f09df..5786f74 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -208,7 +208,6 @@ explain select * from t1 where pk1 != 0 and key1 = 1 {
"access_type": "ref",
"index": "key1",
"used_range_estimates": true,
- "rowid_filter_skipped": "cost_factor <= 0",
"rows": 1,
"cost": 2,
"chosen": true
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index a3ce10f..0e728d7 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -281,7 +281,7 @@ INSERT INTO t1 VALUES
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where
+1 SIMPLE t1 ref|filter a,b b|a 5|5 const 15 (5%) Using where; Using rowid filter
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
a b
DROP TABLE t1;
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index b35021b..efe1fe1 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -335,8 +335,8 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
o_totalprice between 200000 and 230000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition
-1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using rowid filter
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) Using where; Using rowid filter
set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
@@ -346,40 +346,40 @@ EXPLAIN
"query_block": {
"select_id": 1,
"table": {
- "table_name": "lineitem",
+ "table_name": "orders",
"access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
+ "rows": 69,
+ "filtered": 100,
+ "index_condition": "orders.o_totalprice between 200000 and 230000"
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
"possible_keys": [
"PRIMARY",
"i_l_shipdate",
"i_l_orderkey",
"i_l_orderkey_quantity"
],
- "key": "i_l_shipdate",
- "key_length": "4",
- "used_key_parts": ["l_shipDATE"],
- "rows": 98,
- "filtered": 100,
- "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
- },
- "table": {
- "table_name": "orders",
- "access_type": "eq_ref",
- "possible_keys": ["PRIMARY", "i_o_totalprice"],
- "key": "PRIMARY",
+ "key": "i_l_orderkey",
"key_length": "4",
- "used_key_parts": ["o_orderkey"],
- "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
"rowid_filter": {
"range": {
- "key": "i_o_totalprice",
- "used_key_parts": ["o_totalprice"]
+ "key": "i_l_shipdate",
+ "used_key_parts": ["l_shipDATE"]
},
- "rows": 69,
- "selectivity_pct": 4.6
+ "rows": 98,
+ "selectivity_pct": 1.632
},
- "rows": 1,
- "filtered": 4.6,
- "attached_condition": "orders.o_totalprice between 200000 and 230000"
+ "rows": 4,
+ "filtered": 1.632,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
}
}
}
@@ -388,8 +388,8 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
o_totalprice between 200000 and 230000;
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
-1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition
-1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) 0.11 (10%) 4.60 100.00 Using where; Using rowid filter
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition
+1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) 0.15 (2%) 1.63 100.00 Using where; Using rowid filter
set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
FROM orders JOIN lineitem ON o_orderkey=l_orderkey
WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
@@ -401,53 +401,53 @@ ANALYZE
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
- "table_name": "lineitem",
+ "table_name": "orders",
"access_type": "range",
- "possible_keys": [
- "PRIMARY",
- "i_l_shipdate",
- "i_l_orderkey",
- "i_l_orderkey_quantity"
- ],
- "key": "i_l_shipdate",
- "key_length": "4",
- "used_key_parts": ["l_shipDATE"],
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
"r_loops": 1,
- "rows": 98,
- "r_rows": 98,
+ "rows": 69,
+ "r_rows": 71,
"r_total_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100,
- "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
+ "index_condition": "orders.o_totalprice between 200000 and 230000"
},
"table": {
- "table_name": "orders",
- "access_type": "eq_ref",
- "possible_keys": ["PRIMARY", "i_o_totalprice"],
- "key": "PRIMARY",
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_orderkey",
"key_length": "4",
- "used_key_parts": ["o_orderkey"],
- "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
"rowid_filter": {
"range": {
- "key": "i_o_totalprice",
- "used_key_parts": ["o_totalprice"]
+ "key": "i_l_shipdate",
+ "used_key_parts": ["l_shipDATE"]
},
- "rows": 69,
- "selectivity_pct": 4.6,
- "r_rows": 71,
- "r_lookups": 96,
- "r_selectivity_pct": 10.417,
+ "rows": 98,
+ "selectivity_pct": 1.632,
+ "r_rows": 98,
+ "r_lookups": 476,
+ "r_selectivity_pct": 2.3109,
"r_buffer_size": "REPLACED",
"r_filling_time_ms": "REPLACED"
},
- "r_loops": 98,
- "rows": 1,
- "r_rows": 0.1122,
+ "r_loops": 71,
+ "rows": 4,
+ "r_rows": 0.1549,
"r_total_time_ms": "REPLACED",
- "filtered": 4.6,
+ "filtered": 1.632,
"r_filtered": 100,
- "attached_condition": "orders.o_totalprice between 200000 and 230000"
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'"
}
}
}
@@ -2072,7 +2072,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where
-1 PRIMARY t1 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where
+1 PRIMARY t1 ref|filter a1,b1 a1|b1 5|4 test.t2.a2 36 (29%) 28.75 Using where; Using rowid filter
2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2
@@ -2097,6 +2097,14 @@ EXPLAIN
"key_length": "5",
"used_key_parts": ["a1"],
"ref": ["test.t2.a2"],
+ "rowid_filter": {
+ "range": {
+ "key": "b1",
+ "used_key_parts": ["b1"]
+ },
+ "rows": 115,
+ "selectivity_pct": 28.75
+ },
"rows": 36,
"filtered": 28.75,
"attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2"
@@ -2179,7 +2187,7 @@ test.t1 analyze status OK
explain
SELECT * FROM t1 WHERE a > 0 AND b=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 5 const 151 Using where
+1 SIMPLE t1 ref|filter a,b b|a 5|5 const 151 (17%) Using where; Using rowid filter
SELECT * FROM t1 WHERE a > 0 AND b=0;
a b
1 0
@@ -2514,19 +2522,32 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 44,
+ "r_lookups": 1000,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
- "r_rows": 44,
+ "rows": 921,
+ "r_rows": 0,
"r_total_time_ms": "REPLACED",
- "filtered": 9.21,
- "r_filtered": 0,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "filtered": 0.44,
+ "r_filtered": 100,
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
@@ -2559,19 +2580,31 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 0,
+ "r_lookups": 0,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
+ "rows": 911,
"r_rows": 0,
- "r_total_time_ms": "REPLACED",
- "filtered": 9.11,
+ "filtered": 0.44,
"r_filtered": 100,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 1bf63d9..d7f1fe4 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -1997,7 +1997,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 )
WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where
-1 PRIMARY t1 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where
+1 PRIMARY t1 ref|filter a1,b1 a1|b1 5|4 test.t2.a2 36 (29%) 28.75 Using where; Using rowid filter
2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2
@@ -2022,6 +2022,14 @@ EXPLAIN
"key_length": "5",
"used_key_parts": ["a1"],
"ref": ["test.t2.a2"],
+ "rowid_filter": {
+ "range": {
+ "key": "b1",
+ "used_key_parts": ["b1"]
+ },
+ "rows": 115,
+ "selectivity_pct": 28.75
+ },
"rows": 36,
"filtered": 28.75,
"attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2"
@@ -2104,7 +2112,7 @@ test.t1 analyze status OK
explain
SELECT * FROM t1 WHERE a > 0 AND b=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b b 5 const 128 Using where
+1 SIMPLE t1 ref|filter a,b b|a 5|5 const 128 (14%) Using where; Using rowid filter
SELECT * FROM t1 WHERE a > 0 AND b=0;
a b
1 0
@@ -2439,19 +2447,32 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 44,
+ "r_lookups": 1000,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
- "r_rows": 44,
+ "rows": 921,
+ "r_rows": 0,
"r_total_time_ms": "REPLACED",
- "filtered": 9.21,
- "r_filtered": 0,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "filtered": 0.44,
+ "r_filtered": 100,
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
@@ -2484,19 +2505,31 @@ ANALYZE
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
- "access_type": "range",
+ "access_type": "ref",
"possible_keys": ["idx1", "idx2"],
- "key": "idx1",
- "key_length": "35",
- "used_key_parts": ["nm"],
+ "key": "idx2",
+ "key_length": "5",
+ "used_key_parts": ["fl2"],
+ "ref": ["const"],
+ "rowid_filter": {
+ "range": {
+ "key": "idx1",
+ "used_key_parts": ["nm"]
+ },
+ "rows": 44,
+ "selectivity_pct": 0.44,
+ "r_rows": 0,
+ "r_lookups": 0,
+ "r_selectivity_pct": 0,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
- "rows": 44,
+ "rows": 911,
"r_rows": 0,
- "r_total_time_ms": "REPLACED",
- "filtered": 9.11,
+ "filtered": 0.44,
"r_filtered": 100,
- "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'",
- "attached_condition": "t1.fl2 = 0"
+ "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'"
}
}
}
@@ -2671,7 +2704,7 @@ count(*)
5
explain extended select count(*) from t1 where a between 21 and 30 and b=2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref b,a b 5 const 24 9.60 Using where
+1 SIMPLE t1 ref|filter b,a b|a 5|5 const 24 (10%) 9.60 Using where; Using rowid filter
Warnings:
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` between 21 and 30
select * from t1 where a between 21 and 30 and b=2;
@@ -3133,7 +3166,7 @@ WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND
timestamp >= DATE_ADD('2017-01-30 08:24:51', INTERVAL -1 MONTH)
ORDER BY timestamp DESC;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain 98 const 40 33.33 Using index condition; Using where; Using filesort
+1 SIMPLE t1 ref|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp 98|4 const 40 (33%) 33.33 Using index condition; Using where; Using filesort; Using rowid filter
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_stree
t2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test
`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>('2017-01-30 08:24:51' + interval -1 month) order by `test`.`t1`.`timestamp` desc
SET optimizer_switch=@save_optimizer_switch;
@@ -3382,7 +3415,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index
1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where
-1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 24 14.46 Using where
+1 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 24 (14%) 14.46 Using where; Using rowid filter
Warnings:
Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
@@ -3498,7 +3531,7 @@ fi.fh in (6311439873746261694,-397087483897438286,
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index
1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where; Using join buffer (flat, BKA join); Rowid-ordered scan
-1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 24 14.46 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan
+1 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 24 (14%) 14.46 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter
Warnings:
Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
@@ -3616,9 +3649,22 @@ ANALYZE
"key_length": "8",
"used_key_parts": ["aceid"],
"ref": ["test.a.id"],
+ "rowid_filter": {
+ "range": {
+ "key": "filt_fh",
+ "used_key_parts": ["fh"]
+ },
+ "rows": 81,
+ "selectivity_pct": 14.464,
+ "r_rows": 80,
+ "r_lookups": 80,
+ "r_selectivity_pct": 40,
+ "r_buffer_size": "REPLACED",
+ "r_filling_time_ms": "REPLACED"
+ },
"r_loops": 1,
"rows": 24,
- "r_rows": 80,
+ "r_rows": 32,
"r_total_time_ms": "REPLACED",
"filtered": 14.464,
"r_filtered": 100
@@ -3628,7 +3674,7 @@ ANALYZE
"join_type": "BKA",
"mrr_type": "Rowid-ordered scan",
"attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)",
- "r_filtered": 40
+ "r_filtered": 100
}
}
}
@@ -3732,7 +3778,7 @@ WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 60 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ref c1,i1 i1 5 test.t1.pk 20 100.00 Using index condition; Using where
+2 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 38 (25%) 25.00 Using where; Full scan on NULL key; Using rowid filter
2 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 60 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
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index fc3a290..6652b1a 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1
WHERE ID_better=1 AND ID1_with_null IS NULL AND
(ID2_with_null=1 OR ID2_with_null=2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where
+1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter
DROP TABLE t1;
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index e7c2e3e..cef6e0f 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -3755,7 +3755,7 @@ EXPLAIN SELECT * FROM t1
WHERE ID_better=1 AND ID1_with_null IS NULL AND
(ID2_with_null=1 OR ID2_with_null=2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where
+1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter
DROP TABLE t1;
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index fc3a290..6652b1a 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1
WHERE ID_better=1 AND ID1_with_null IS NULL AND
(ID2_with_null=1 OR ID2_with_null=2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where
+1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter
DROP TABLE t1;
CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts));
INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 4113779..7e32023 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1661,7 +1661,7 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
# gives selectivity data
explain extended select * from t1 where a in (17,51,5) and b=2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref b,a b 5 const 58 2.90 Using where
+1 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5)
drop table t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0f8ead4..350d43f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7557,7 +7557,6 @@ best_access_path(JOIN *join,
rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
Json_writer_object trace_access_idx(thd);
- double eq_ref_rows= 0;
/*
full text keys require special treatment
*/
@@ -7596,8 +7595,7 @@ best_access_path(JOIN *join,
type= JT_EQ_REF;
trace_access_idx.add("access_type", join_type_str[type])
.add("index", keyinfo->name);
- eq_ref_rows= tmp = prev_record_reads(join_positions, idx,
- found_ref);
+ tmp = prev_record_reads(join_positions, idx, found_ref);
records=1.0;
}
else
@@ -7904,28 +7902,7 @@ best_access_path(JOIN *join,
(s->table->file->index_flags(start_key->key,0,1) &
HA_DO_RANGE_FILTER_PUSHDOWN))
{
- double rows;
- if (type == JT_EQ_REF)
- {
- /*
- Treat EQ_REF access in a special way:
- 1. We have no cost for index-only read. Assume its cost is 50% of
- the cost of the full read.
-
- 2. A regular ref access will do #record_count lookups, but eq_ref
- has "lookup cache" which reduces the number of lookups made.
- The estimation code uses prev_record_reads() call to estimate:
-
- tmp = prev_record_reads(join_positions, idx, found_ref);
-
- Set the effective number of rows from "tmp" here.
- */
- keyread_tmp= COST_ADD(eq_ref_rows / 2, s->startup_cost);
- rows= eq_ref_rows;
- }
- else
- rows= record_count * records;
-
+ double rows= record_count * records;
/*
If we use filter F with selectivity s the the cost of fetching data
by key using this filter will be
@@ -7947,46 +7924,53 @@ best_access_path(JOIN *join,
cost_of_fetching_1_row = tmp/rows
cost_of_fetching_1_key_tuple = keyread_tmp/rows
- access_cost_factor is the gain we expect for using rowid filter.
- An access_cost_factor of 1.0 means that keyread_tmp is 0
- (using key read is infinitely fast) and the gain for each row when
- using filter is great.
- An access_cost_factor if 0.0 means that using keyread has the
- same cost as reading rows, so there is no gain to get with
- filter.
- access_cost_factor should never be bigger than 1.0 (if all
- calculations are correct) as the cost of keyread should always be
- smaller than the cost of fetching the same number of keys + rows.
- access_cost_factor should also never be smaller than 0.0.
- The one exception is if number of records is 1 (eq_ref), then
- because we are comparing rows to cost of keyread_tmp, keyread_tmp
- is higher by 1.0. This is a big that will be fixed in a later
- version.
-
- If we have limited the cost (=tmp) of reading rows with 'worst_seek'
- we cannot use filters as the cost calculation below would cause
- tmp to become negative. The future resultion is to not limit
- cost with worst_seek.
+ Here's a more detailed explanation that uses the formulas behind
+ the function the call filter->get_adjusted_gain(). The function
+ takes as a parameter the number of probes/look-ups into the filter
+ that is equal to the number of fetched key entries that is equal to
+ the number of row fetches when no filter is used (assuming no
+ index condition pushdown is employed for the used key access).
+ Let this number be N. Then the total gain from using the filter is
+ N*a_adj - b where b is the cost of building the filter and
+ a_adj is calcilated as follows:
+ a - (1-access_cost_factor)*(1-s) =
+ (1+1_cond_eval_cost)*(1-s)-1_probe_cost - (1-access_cost_factor)*(1-s)
+ = (1-s)*(1_cond_eval_cost+access_cost_factor) - 1_probe_cost.
+ Here ((1-s)*(1_cond_eval_cost) * N is the gain from checking less
+ conditions pushed into the table, 1_probe_cost*N is the cost of the
+ probes and (1*s) * access_cost_factor * N must be the gain from
+ accessing less rows.
+ It does not matter how we calculate the cost of N full row fetches
+ cost_of_fetching_N_rows or
+ how we calculate the cost of fetching N key entries
+ cost_of_fetching_N_key_entries
+ the gain from less row fetches will be
+ (cost_of_fetching_N_rows - cost_of_fetching_N_key_entries) * (1-s)
+ and this should be equal to (1*s) * access_cost_factor * N.
+ Thus access_cost_factor must be calculated as
+ (cost_of_fetching_N_rows - cost_of_fetching_N_key_entries) / N.
+
+ For safety we clip cost_of_fetching_N_key_entries by the value
+ of cost_of_fethching_N_row though formally it's not necessary.
*/
- double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0);
- if (!(records < s->worst_seeks &&
- records <= thd->variables.max_seeks_for_key))
- trace_access_idx.add("rowid_filter_skipped", "worst/max seeks clipping");
- else if (access_cost_factor <= 0.0)
- trace_access_idx.add("rowid_filter_skipped", "cost_factor <= 0");
- else
+ /*
+ For eq_ref access we assume that the cost of fetching N key entries
+ is equal to the half of fetching N rows
+ */
+ double key_access_cost=
+ type == JT_EQ_REF ? 0.5 * tmp : MY_MIN(tmp, keyread_tmp);
+ double access_cost_factor= MY_MIN((tmp - key_access_cost) / rows, 1.0);
+
+ filter=
+ table->best_range_rowid_filter_for_partial_join(start_key->key,
+ rows,
+ access_cost_factor);
+ if (filter)
{
- filter=
- table->best_range_rowid_filter_for_partial_join(start_key->key,
- rows,
- access_cost_factor);
- if (filter)
- {
- tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows);
- DBUG_ASSERT(tmp >= 0);
- trace_access_idx.add("rowid_filter_key",
+ tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows);
+ DBUG_ASSERT(tmp >= 0);
+ trace_access_idx.add("rowid_filter_key",
s->table->key_info[filter->key_no].name);
- }
}
}
trace_access_idx.add("rows", records).add("cost", tmp);
@@ -8139,19 +8123,19 @@ best_access_path(JOIN *join,
uint key_no= s->quick->index;
/* See the comment concerning using rowid filter for with ref access */
- keyread_tmp= s->table->quick_index_only_costs[key_no] * record_count;
- double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0);
- if (access_cost_factor > 0.0)
+ double row_access_cost= s->quick->read_time * record_count;
+ double key_access_cost=
+ MY_MIN(row_access_cost,
+ s->table->quick_index_only_costs[key_no] * record_count);
+ double access_cost_factor= MY_MIN((row_access_cost - key_access_cost) /
+ rows, 1.0);
+ filter=
+ s->table->best_range_rowid_filter_for_partial_join(key_no, rows,
+ access_cost_factor);
+ if (filter)
{
- filter=
- s->table->
- best_range_rowid_filter_for_partial_join(key_no, rows,
- access_cost_factor);
- if (filter)
- {
- tmp-= filter->get_adjusted_gain(rows);
- DBUG_ASSERT(tmp >= 0);
- }
+ tmp-= filter->get_adjusted_gain(rows);
+ DBUG_ASSERT(tmp >= 0);
}
else
trace_access_scan.add("rowid_filter_skipped", "cost_factor <= 0");
1
0
[Commits] 98922da: Merge branch 'bb-10.4-release' of github.com:MariaDB/server into bb-10.4-release
by IgorBabaev 31 Jan '23
by IgorBabaev 31 Jan '23
31 Jan '23
revision-id: 98922dabb74bb05cd97003e4a0c5f3b2a878ccf0 (mariadb-10.4.27-70-g98922da)
parent(s): c8f9bb2718c4ed7b464504c54df961bfeb2cccca c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-01-30 21:02:35 -0800
message:
Merge branch 'bb-10.4-release' of github.com:MariaDB/server into bb-10.4-release
# Conflicts:
# sql/sql_select.cc
.../r/binlog_recovery_after_checksum_change.result | 8 ----
.../binlog/r/innodb_rc_insert_before_delete.result | 52 ++++++++++++++++++++++
sql/sql_select.cc | 8 ++--
3 files changed, 57 insertions(+), 11 deletions(-)
1
0
[Commits] b6e958e: MDEV-28958 Crash when checking whether condition can be pushed into view
by IgorBabaev 31 Jan '23
by IgorBabaev 31 Jan '23
31 Jan '23
revision-id: b6e958edc5fb147fcfeb4a3837d14daacfa2c93e (mariadb-10.7.4-105-gb6e958e)
parent(s): cea50896d2ea0d18924d92d62a7ec1607d55e509
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-01-30 19:42:27 -0800
message:
MDEV-28958 Crash when checking whether condition can be pushed into view
Do not set any flags in the items for constant subformulas TRUE/FALSE when
checking pushability of a formula into a view. Occurrences of these
subformulas can be ignored when checking pushability of the formula.
At the same time the items used for these constants became immutable
starting from version 10.7.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/derived_cond_pushdown.result | 17 +++++++++++++++++
mysql-test/main/derived_cond_pushdown.test | 21 +++++++++++++++++++++
sql/item.h | 23 ++++++++++++++++-------
3 files changed, 54 insertions(+), 7 deletions(-)
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 5b0cc4a..b1fefd2 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -18186,3 +18186,20 @@ id select_type table type possible_keys key key_len ref rows Extra
drop view v1;
drop table t1;
# End of 10.4 tests
+#
+# MDEV-28958: condition pushable into view after simplification
+# contains constant TRUE/FALSE as subformula
+#
+create table t1 (c1 int);
+insert into t1 values (3), (7), (1), (3), (1), (3);
+create table t2 (c2 int);
+insert into t2 values (3), (5), (7), (3);
+create view v1 as select * from t1 group by c1;
+create view v2 as select c1 as a, c2 as b from v1,t2 where c1=c2;
+select * from v2 group by a,b having a=b or b > a+10;
+a b
+3 3
+7 7
+drop view v1,v2;
+drop table t1,t2;
+# End of 10.7 tests
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 39e8221..8fdc54d 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3895,3 +3895,24 @@ drop view v1;
drop table t1;
--echo # End of 10.4 tests
+
+--echo #
+--echo # MDEV-28958: condition pushable into view after simplification
+--echo # contains constant TRUE/FALSE as subformula
+--echo #
+
+create table t1 (c1 int);
+insert into t1 values (3), (7), (1), (3), (1), (3);
+
+create table t2 (c2 int);
+insert into t2 values (3), (5), (7), (3);
+
+create view v1 as select * from t1 group by c1;
+create view v2 as select c1 as a, c2 as b from v1,t2 where c1=c2;
+
+select * from v2 group by a,b having a=b or b > a+10;
+
+drop view v1,v2;
+drop table t1,t2;
+
+--echo # End of 10.7 tests
diff --git a/sql/item.h b/sql/item.h
index d197ebb..59d395a 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2681,18 +2681,27 @@ class Item :public Value_source,
void register_in(THD *thd);
bool depends_only_on(table_map view_map)
- { return marker & MARKER_FULL_EXTRACTION; }
- int get_extraction_flag() const
- { return marker & MARKER_EXTRACTION_MASK; }
+ { return get_extraction_flag() & MARKER_FULL_EXTRACTION; }
+ int get_extraction_flag() const
+ {
+ if (basic_const_item())
+ return MARKER_FULL_EXTRACTION;
+ else
+ return marker & MARKER_EXTRACTION_MASK;
+ }
void set_extraction_flag(int16 flags)
{
- marker &= ~MARKER_EXTRACTION_MASK;
- marker|= flags;
+ if (!basic_const_item())
+ {
+ marker= marker & ~MARKER_EXTRACTION_MASK;
+ marker|= flags;
+ }
}
void clear_extraction_flag()
{
- marker &= ~MARKER_EXTRACTION_MASK;
- }
+ if (!basic_const_item())
+ marker= marker & ~MARKER_EXTRACTION_MASK;
+ }
void check_pushable_cond(Pushdown_checker excl_dep_func, uchar *arg);
bool pushable_cond_checker_for_derived(uchar *arg)
{
1
0
[Commits] 2530783: MDEV-28616 Crash when using derived table over union with order by clause
by IgorBabaev 25 Jan '23
by IgorBabaev 25 Jan '23
25 Jan '23
revision-id: 253078337ca6019badfaf359d9302c8a479746ca (mariadb-10.3.35-379-g2530783)
parent(s): f513d715382a63415c9342f1cae75be75b441f98
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-01-25 11:46:28 -0800
message:
MDEV-28616 Crash when using derived table over union with order by clause
This bug manifested itself when the server processed a query containing
a derived table over union whose ORDER BY clause included a subquery
with unresolvable column reference. For such a query the server crashed
when trying to resolve column references in the ORDER BY clause used by
union.
For any union with ORDER BY clause an extra SELECT_LEX structure is created
and it is attached to SELECT_LEX_UNIT structure of the union via the field
fake_select_lex. The outer context for fake_select_lex must be the same as
for other selects of the union. If the union is used in the FROM list of
a derived table then the outer context for fake_select_lex must be set to
NULL in line with other selects of the union. It was not done and it
caused a crash when searching for possible resolution of an unresolvable
column reference occurred in a subquery used in the ORDER BY clause.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/derived.result | 23 +++++++++++++++++++++++
mysql-test/main/derived.test | 30 ++++++++++++++++++++++++++++++
sql/sql_derived.cc | 3 +++
3 files changed, 56 insertions(+)
diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result
index 2761fdf..0cb029f 100644
--- a/mysql-test/main/derived.result
+++ b/mysql-test/main/derived.result
@@ -1327,5 +1327,28 @@ a b
DROP VIEW v1;
DROP TABLE t1;
#
+# MDEV-28616: derived table over union with order by clause that
+# contains subquery with unresolvable column reference
+#
+SELECT 1 FROM (
+SELECT 1 UNION SELECT 2 ORDER BY (SELECT 1 FROM DUAL WHERE xxx = 0)
+) dt;
+ERROR 42S22: Unknown column 'xxx' in 'where clause'
+create table t1 (a int, b int);
+insert into t1 values (3,8), (7,2), (1,4), (5,9);
+create table t2 (a int, b int);
+insert into t2 values (9,1), (7,3), (2,6);
+create table t3 (c int, d int);
+insert into t3 values (7,8), (1,2), (3,8);
+select * from
+(
+select a,b from t1 where t1.a > 3
+union
+select a,b from t2 where t2.b < 6
+order by (a - b / (select a + max(c) from t3 where d = x))
+) dt;
+ERROR 42S22: Unknown column 'x' in 'where clause'
+drop table t1,t2,t3;
+#
# End of 10.3 tests
#
diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test
index 6a83100..dca7243 100644
--- a/mysql-test/main/derived.test
+++ b/mysql-test/main/derived.test
@@ -1138,5 +1138,35 @@ DROP VIEW v1;
DROP TABLE t1;
--echo #
+--echo # MDEV-28616: derived table over union with order by clause that
+--echo # contains subquery with unresolvable column reference
+--echo #
+
+--error ER_BAD_FIELD_ERROR
+SELECT 1 FROM (
+ SELECT 1 UNION SELECT 2 ORDER BY (SELECT 1 FROM DUAL WHERE xxx = 0)
+) dt;
+
+create table t1 (a int, b int);
+insert into t1 values (3,8), (7,2), (1,4), (5,9);
+
+create table t2 (a int, b int);
+insert into t2 values (9,1), (7,3), (2,6);
+
+create table t3 (c int, d int);
+insert into t3 values (7,8), (1,2), (3,8);
+
+--error ER_BAD_FIELD_ERROR
+select * from
+(
+ select a,b from t1 where t1.a > 3
+ union
+ select a,b from t2 where t2.b < 6
+ order by (a - b / (select a + max(c) from t3 where d = x))
+) dt;
+
+drop table t1,t2,t3;
+
+--echo #
--echo # End of 10.3 tests
--echo #
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 93dc628..8177ee2 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -771,6 +771,9 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
cursor->outer_join|= JOIN_TYPE_OUTER;
}
}
+ // Prevent it for possible ORDER BY clause
+ if (unit->fake_select_lex)
+ unit->fake_select_lex->context.outer_context= 0;
/*
Above cascade call of prepare is important for PS protocol, but after it
1
0
[Commits] db2eeaf: MDEV-30081 Crash with splitting from constant mergeable derived table
by IgorBabaev 24 Jan '23
by IgorBabaev 24 Jan '23
24 Jan '23
revision-id: db2eeafc20de584d430ce9c0903478aeb8782d66 (mariadb-10.3.35-375-gdb2eeaf)
parent(s): 074bef4dcaad15dba40013e9d2ddf0011b7744a1
author: Igor Babaev
committer: Igor Babaev
timestamp: 2023-01-23 15:54:49 -0800
message:
MDEV-30081 Crash with splitting from constant mergeable derived table
This bug manifested itself in very rare situations when splitting
optimization was applied to a materialized derived table with group clause
by key over a constant meargeable derived table that was in inner part of
an outer join. In this case the used tables for the key to access the
split table incorrectly was evaluated to a not empty table map.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/derived_cond_pushdown.result | 58 ++++++++++++++++++++++++++++
mysql-test/main/derived_cond_pushdown.test | 49 +++++++++++++++++++++++
sql/item.cc | 2 +-
3 files changed, 108 insertions(+), 1 deletion(-)
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index ebe0c229..88f20bc 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -18131,4 +18131,62 @@ DROP TABLE transaction_items;
DROP TABLE transactions;
DROP TABLE charges;
DROP TABLE ledgers;
+#
+# MDEV-30081: Splitting from a constant mergeable derived table
+# used in inner part of an outer join.
+#
+CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(4),(7);
+CREATE TABLE t2 (
+id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,4,6),(7,7,7);
+CREATE TABLE t3 (
+wid int, wtid int, otid int, oid int,
+PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
+CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t5 (
+id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
+) ENGINE=MyISAM ;
+INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+ANALYZE TABLE t1,t2,t3,t4,t5;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+test.t3 analyze status OK
+test.t4 analyze status OK
+test.t5 analyze status OK
+CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
+SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+t1, t2, t3
+LEFT JOIN
+(SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ON t3.oid = dt.id AND t3.otid = 14
+LEFT JOIN v1
+ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+wid wtid otid oid t1_id t2_id id a id1
+7 17 7 7 7 7 NULL NULL NULL
+EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+t1, t2, t3
+LEFT JOIN
+(SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ON t3.oid = dt.id AND t3.otid = 14
+LEFT JOIN v1
+ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 const PRIMARY,oid PRIMARY 4 const 1
+1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t2 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t4 const PRIMARY,a NULL NULL NULL 1 Impossible ON condition
+1 PRIMARY <derived3> ref key0 key0 5 const 0 Using where
+3 LATERAL DERIVED t5 ref id1 id1 5 const 0 Using index
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4,t5;
# End of 10.3 tests
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 619d104..e039d1f 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3853,4 +3853,53 @@ DROP TABLE transactions;
DROP TABLE charges;
DROP TABLE ledgers;
+
+--echo #
+--echo # MDEV-30081: Splitting from a constant mergeable derived table
+--echo # used in inner part of an outer join.
+--echo #
+
+ CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(4),(7);
+
+CREATE TABLE t2 (
+ id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,4,6),(7,7,7);
+
+CREATE TABLE t3 (
+ wid int, wtid int, otid int, oid int,
+ PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
+
+CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t5 (
+ id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
+) ENGINE=MyISAM ;
+INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+ANALYZE TABLE t1,t2,t3,t4,t5;
+
+CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
+
+let $q=
+SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+ t1, t2, t3
+ LEFT JOIN
+ (SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ ON t3.oid = dt.id AND t3.otid = 14
+ LEFT JOIN v1
+ ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+
+eval $q;
+eval EXPLAIN $q;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4,t5;
+
--echo # End of 10.3 tests
diff --git a/sql/item.cc b/sql/item.cc
index 02220fd..9ecbefa 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -10838,7 +10838,7 @@ table_map Item_direct_view_ref::used_tables() const
table_map used= (*ref)->used_tables();
return (used ?
used :
- ((null_ref_table != NO_NULL_TABLE) ?
+ (null_ref_table != NO_NULL_TABLE && !null_ref_table->const_table ?
null_ref_table->map :
(table_map)0 ));
}
1
0