[Commits] b9cd8aa: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 24 Mar '22
by IgorBabaev 24 Mar '22
24 Mar '22
revision-id: b9cd8aa1de578b72e2875f7a6b730d43c1f3fc13 (mariadb-10.3.26-366-gb9cd8aa)
parent(s): bbf02c85ba2e850da546199421cb75c224747475
author: Igor Babaev
committer: Igor Babaev
timestamp: 2022-03-24 12:36:22 -0700
message:
MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
This bug affected queries with IN predicates that contain parameter markers
in the value list. Such queries are executed via prepared statements.
The problem appeared only if the number of elements in the value list
was greater than the set value of the system variable
in_predicate_conversion_threshold.
The patch unconditionally prohibits conversion of an IN predicate to the
equivalent IN predicand if the value list of the IN predicate contains
parameters markers.
---
mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++
mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++
sql/item_cmpfunc.cc | 7 ++++---
sql/item_cmpfunc.h | 2 ++
sql/sql_tvc.cc | 27 ++++++++++++++++++++++++---
5 files changed, 83 insertions(+), 6 deletions(-)
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index a68e70e..02d9096 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -732,3 +732,30 @@ a b
4 4
drop table t1;
SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-27937: Prepared statement with ? in the list if IN predicate
+#
+set in_predicate_conversion_threshold=2;
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+id a b
+1 3 30
+2 7 70
+3 1 10
+deallocate prepare stmt;
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+id a b
+1 3 30
+2 7 70
+3 1 10
+deallocate prepare stmt;
+drop table t1;
+set in_predicate_conversion_threshold=default;
+# End of 10.3 tests
diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test
index e4e8c6d..f8469f2 100644
--- a/mysql-test/main/opt_tvc.test
+++ b/mysql-test/main/opt_tvc.test
@@ -428,3 +428,29 @@ eval $query;
drop table t1;
SET @@in_predicate_conversion_threshold= default;
+--echo #
+--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate
+--echo #
+
+set in_predicate_conversion_threshold=2;
+
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+deallocate prepare stmt;
+
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+deallocate prepare stmt;
+
+drop table t1;
+
+set in_predicate_conversion_threshold=default;
+
+--echo # End of 10.3 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 38f0a28..f41414f 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding)
Query_arena *arena, backup;
arena= thd->activate_stmt_arena_if_needed(&backup);
- if (to_be_transformed_into_in_subq(thd))
+ if (!transform_into_subq_checked)
{
- transform_into_subq= true;
- thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+ if ((transform_into_subq= to_be_transformed_into_in_subq(thd)))
+ thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+ transform_into_subq_checked= true;
}
if (arena)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 4c88f5b..f3d3be4 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg,
SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
Field *field, Item *value);
bool transform_into_subq;
+ bool transform_into_subq_checked;
public:
/// An array of values, created when the bisection lookup method is used
in_vector *array;
@@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg,
Item_func_opt_neg(thd, list),
Predicant_to_list_comparator(thd, arg_count - 1),
transform_into_subq(false),
+ transform_into_subq_checked(false),
array(0), have_null(0),
arg_types_compatible(FALSE), emb_on_expr_nest(0)
{ }
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 3866b7c..13efd97 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
if (!transform_into_subq)
return this;
- transform_into_subq= false;
-
List<List_item> values;
LEX *lex= thd->lex;
@@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr()
bool Item_func_in::to_be_transformed_into_in_subq(THD *thd)
{
+ bool is_row_list= args[1]->type() == Item::ROW_ITEM;
uint values_count= arg_count-1;
- if (args[1]->type() == Item::ROW_ITEM)
+ if (is_row_list)
values_count*= ((Item_row *)(args[1]))->cols();
if (thd->variables.in_subquery_conversion_threshold == 0 ||
thd->variables.in_subquery_conversion_threshold > values_count)
return false;
+ if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE))
+ return true;
+
+ /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */
+ for (uint i=1; i < arg_count; i++)
+ {
+ if (!is_row_list)
+ {
+ if (args[i]->type() == Item::PARAM_ITEM)
+ return false;
+ }
+ else
+ {
+ Item_row *row_list= (Item_row *)(args[i]);
+ for (uint j=0; j < row_list->cols(); j++)
+ {
+ if (row_list->element_index(j)->type() == Item::PARAM_ITEM)
+ return false;
+ }
+ }
+ }
+
return true;
}
1
0
[Commits] 1902fc4: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 24 Mar '22
by IgorBabaev 24 Mar '22
24 Mar '22
revision-id: 1902fc43b74c9dabedbde0da6b567fa184eaf6d1 (mariadb-10.3.26-366-g1902fc4)
parent(s): bbf02c85ba2e850da546199421cb75c224747475
author: Igor Babaev
committer: Igor Babaev
timestamp: 2022-03-24 11:43:17 -0700
message:
MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
This bug affected queries with IN predicates that contain parameter markers
in the value list. Such queries are executed via prepared statements.
The problem appeared only if the number of elements in the value list
was greater than the set value of the system variable
in_predicate_conversion_threshold.
The patch unconditionally prohibits conversion of an IN predicate to the
equivalent IN predicand if the value list of the IN predicate contains
parameters markers.
---
mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++
mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++
sql/item_cmpfunc.cc | 7 ++++---
sql/item_cmpfunc.h | 2 ++
sql/sql_tvc.cc | 27 ++++++++++++++++++++++++---
5 files changed, 83 insertions(+), 6 deletions(-)
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index a68e70e..02d9096 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -732,3 +732,30 @@ a b
4 4
drop table t1;
SET @@in_predicate_conversion_threshold= default;
+#
+# MDEV-27937: Prepared statement with ? in the list if IN predicate
+#
+set in_predicate_conversion_threshold=2;
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+id a b
+1 3 30
+2 7 70
+3 1 10
+deallocate prepare stmt;
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+id a b
+1 3 30
+2 7 70
+3 1 10
+deallocate prepare stmt;
+drop table t1;
+set in_predicate_conversion_threshold=default;
+# End of 10.3 tests
diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test
index e4e8c6d..f8469f2 100644
--- a/mysql-test/main/opt_tvc.test
+++ b/mysql-test/main/opt_tvc.test
@@ -428,3 +428,29 @@ eval $query;
drop table t1;
SET @@in_predicate_conversion_threshold= default;
+--echo #
+--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate
+--echo #
+
+set in_predicate_conversion_threshold=2;
+
+create table t1 (id int, a int, b int);
+insert into t1 values (1,3,30), (2,7,70), (3,1,10);
+
+prepare stmt from "
+select * from t1 where a in (7, ?, 5, 1);
+";
+execute stmt using 3;
+deallocate prepare stmt;
+
+prepare stmt from "
+select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
+";
+execute stmt using 30;
+deallocate prepare stmt;
+
+drop table t1;
+
+set in_predicate_conversion_threshold=default;
+
+--echo # End of 10.3 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 38f0a28..f41414f 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding)
Query_arena *arena, backup;
arena= thd->activate_stmt_arena_if_needed(&backup);
- if (to_be_transformed_into_in_subq(thd))
+ if (!transform_into_subq_checked)
{
- transform_into_subq= true;
- thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+ if ((transform_into_subq= to_be_transformed_into_in_subq(thd)))
+ thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+ transform_into_subq_checked= true;
}
if (arena)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 4c88f5b..f3d3be4 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg,
SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
Field *field, Item *value);
bool transform_into_subq;
+ bool transform_into_subq_checked;
public:
/// An array of values, created when the bisection lookup method is used
in_vector *array;
@@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg,
Item_func_opt_neg(thd, list),
Predicant_to_list_comparator(thd, arg_count - 1),
transform_into_subq(false),
+ transform_into_subq_checked(false),
array(0), have_null(0),
arg_types_compatible(FALSE), emb_on_expr_nest(0)
{ }
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 3866b7c..13efd97 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
if (!transform_into_subq)
return this;
- transform_into_subq= false;
-
List<List_item> values;
LEX *lex= thd->lex;
@@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr()
bool Item_func_in::to_be_transformed_into_in_subq(THD *thd)
{
+ bool is_row_list= args[1]->type() == Item::ROW_ITEM;
uint values_count= arg_count-1;
- if (args[1]->type() == Item::ROW_ITEM)
+ if (is_row_list)
values_count*= ((Item_row *)(args[1]))->cols();
if (thd->variables.in_subquery_conversion_threshold == 0 ||
thd->variables.in_subquery_conversion_threshold > values_count)
return false;
+ if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE))
+ return true;
+
+ /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */
+ for (uint i=1; i < arg_count; i++)
+ {
+ if (!is_row_list)
+ {
+ if (args[i]->type() == Item::PARAM_ITEM)
+ return false;
+ }
+ else
+ {
+ Item_row *row_list= (Item_row *)(args[i]);
+ for (uint j=0; j < row_list->cols(); j++)
+ {
+ if (row_list->element_index(j)->type() == Item::PARAM_ITEM)
+ return false;
+ }
+ }
+ }
+
return true;
}
1
0
[Commits] 8fff088: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
by IgorBabaev 23 Mar '22
by IgorBabaev 23 Mar '22
23 Mar '22
revision-id: 8fff0886ddc95eb5f8dba60b8b859183f42bd494 (mariadb-10.3.26-356-g8fff088)
parent(s): bfed2c7d57a7ca34936d6ef0688af7357592dc40
author: Igor Babaev
committer: Igor Babaev
timestamp: 2022-03-23 12:45:56 -0700
message:
MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
This bug could affect prepared statements for the command CREATE VIEW with
specification that contained unnamed basic constant in select list. If
generation of a valid name for the corresponding view column required
resolution of conflicts with names of other columns that were explicitly
defined then execution of such prepared statement and following deallocation
of this statement led to reading from freed memory.
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/main/view.result | 28 ++++++++++++++++++++++++++++
mysql-test/main/view.test | 26 ++++++++++++++++++++++++++
sql/sql_view.cc | 3 ++-
3 files changed, 56 insertions(+), 1 deletion(-)
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index a410ab7..6483d76 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6839,5 +6839,33 @@ id bar
Drop View v1;
Drop table t1;
#
+# MDEV-24281: Execution of PREPARE from CREATE VIEW statement
+#
+create table t1 (s1 int);
+insert into t1 values (3), (7), (1);
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 's1' AS `My_exp_1_s1`,`t1`.`s1` AS `s1`,1 AS `My_exp_s1` from `t1` latin1 latin1_swedish_ci
+select * from v1;
+My_exp_1_s1 s1 My_exp_s1
+s1 3 1
+s1 7 1
+s1 1 1
+drop view v1;
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+execute stmt;
+ERROR 42S01: Table 'v1' already exists
+deallocate prepare stmt;
+drop view v1;
+drop table t1;
+#
# End of 10.3 tests
#
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 431dfdb..46232b1 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6577,5 +6577,31 @@ Drop View v1;
Drop table t1;
--echo #
+--echo # MDEV-24281: Execution of PREPARE from CREATE VIEW statement
+--echo #
+
+create table t1 (s1 int);
+insert into t1 values (3), (7), (1);
+
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view v1;
+select * from v1;
+drop view v1;
+
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+--error ER_TABLE_EXISTS_ERROR
+execute stmt;
+deallocate prepare stmt;
+drop view v1;
+drop table t1;
+
+--echo #
--echo # End of 10.3 tests
--echo #
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 024bd36..b6787a1 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -96,7 +96,8 @@ static void make_unique_view_field_name(THD *thd, Item *target,
itc.rewind();
}
- target->orig_name= target->name.str;
+ if (!target->orig_name)
+ target->orig_name= target->name.str;
target->set_name(thd, buff, name_len, system_charset_info);
}
1
0
revision-id: 260d13f0fb40822d259fab76c6f9127d0c3e0167 (mariadb-10.6.1-373-g260d13f0fb4)
parent(s): 304a2a6ca6a906afaf60c2650b21c2de7a06df60
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-03-22 10:07:39 +0300
message:
Update test results (8)
---
mysql-test/suite/maria/mrr.result | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result
index 61450c948bf..2047febb92a 100644
--- a/mysql-test/suite/maria/mrr.result
+++ b/mysql-test/suite/maria/mrr.result
@@ -187,7 +187,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 2.86 Using index condition; Rowid-ordered scan
+1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 FLTRD Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
1
0
revision-id: 304a2a6ca6a906afaf60c2650b21c2de7a06df60 (mariadb-10.6.1-372-g304a2a6ca6a)
parent(s): 7ba842d1e8d81ca99887e500187ec6dc6964c858
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-03-22 07:56:44 +0300
message:
Update test results (7)
---
mysql-test/include/mrr_tests.inc | 1 +
mysql-test/main/myisam_mrr.result | 2 +-
mysql-test/main/xtradb_mrr.result | 2 +-
mysql-test/suite/innodb/r/innodb_mysql.result | 2 +-
mysql-test/suite/innodb/t/innodb_mysql.test | 1 +
5 files changed, 5 insertions(+), 3 deletions(-)
diff --git a/mysql-test/include/mrr_tests.inc b/mysql-test/include/mrr_tests.inc
index ad7dff61477..a50cc8c16f0 100644
--- a/mysql-test/include/mrr_tests.inc
+++ b/mysql-test/include/mrr_tests.inc
@@ -89,6 +89,7 @@ insert into t4 (a,b,c,filler)
insert into t4 (a,b,c,filler)
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500;
+--replace_column 11 FLTRD
explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
diff --git a/mysql-test/main/myisam_mrr.result b/mysql-test/main/myisam_mrr.result
index 57e6a443940..37ad7565a1b 100644
--- a/mysql-test/main/myisam_mrr.result
+++ b/mysql-test/main/myisam_mrr.result
@@ -189,7 +189,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 1.79 Using index condition; Rowid-ordered scan
+1 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 FLTRD Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result
index ceb98f2c7d5..015d1e61753 100644
--- a/mysql-test/main/xtradb_mrr.result
+++ b/mysql-test/main/xtradb_mrr.result
@@ -186,7 +186,7 @@ explain
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 2.86 Using index condition; Rowid-ordered scan
+1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 FLTRD Using index condition; Rowid-ordered scan
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1'
or c='no-such-row2');
a b c filler
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 7a2dfa7eec8..0bdf7965b0e 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -3373,7 +3373,7 @@ SELECT v2
FROM t1
WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 NULL ref i,v i 5 const 2 100.00 Using where
+1 SIMPLE t1 NULL ref i,v i 5 const 2 FLTRD Using where
DROP TABLE t1;
#
# Bug#54606 innodb fast alter table + pack_keys=0
diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test
index d495186db25..ae681fc8631 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql.test
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test
@@ -1015,6 +1015,7 @@ WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
--echo
--echo # Should not use index_merge
+--replace_column 11 FLTRD
EXPLAIN
SELECT v2
FROM t1
1
0
[Commits] 390051a: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
by IgorBabaev 22 Mar '22
by IgorBabaev 22 Mar '22
22 Mar '22
revision-id: 390051add0fd3fba3aae9e2f1795ec73833f792a (mariadb-10.3.26-356-g390051a)
parent(s): bfed2c7d57a7ca34936d6ef0688af7357592dc40
author: Igor Babaev
committer: Igor Babaev
timestamp: 2022-03-21 20:00:24 -0700
message:
MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
This bug could affect prepared statements for the command CREATE VIEW with
specification that contained unnamed basic constant in select list. If
generation of a valid name for the corresponding view column required
resolution of conflicts with names of other columns that were explicitly
defined then execution of such prepared statement and following deallocation
of this statement led to reading from freed memory.
---
mysql-test/main/view.result | 28 ++++++++++++++++++++++++++++
mysql-test/main/view.test | 26 ++++++++++++++++++++++++++
sql/sql_view.cc | 3 ++-
3 files changed, 56 insertions(+), 1 deletion(-)
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index a410ab7..6483d76 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6839,5 +6839,33 @@ id bar
Drop View v1;
Drop table t1;
#
+# MDEV-24281: Execution of PREPARE from CREATE VIEW statement
+#
+create table t1 (s1 int);
+insert into t1 values (3), (7), (1);
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 's1' AS `My_exp_1_s1`,`t1`.`s1` AS `s1`,1 AS `My_exp_s1` from `t1` latin1 latin1_swedish_ci
+select * from v1;
+My_exp_1_s1 s1 My_exp_s1
+s1 3 1
+s1 7 1
+s1 1 1
+drop view v1;
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+execute stmt;
+ERROR 42S01: Table 'v1' already exists
+deallocate prepare stmt;
+drop view v1;
+drop table t1;
+#
# End of 10.3 tests
#
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 431dfdb..46232b1 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6577,5 +6577,31 @@ Drop View v1;
Drop table t1;
--echo #
+--echo # MDEV-24281: Execution of PREPARE from CREATE VIEW statement
+--echo #
+
+create table t1 (s1 int);
+insert into t1 values (3), (7), (1);
+
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+deallocate prepare stmt;
+show create view v1;
+select * from v1;
+drop view v1;
+
+prepare stmt from "
+create view v1 as select 's1', s1, 1 as My_exp_s1 from t1;
+";
+execute stmt;
+--error ER_TABLE_EXISTS_ERROR
+execute stmt;
+deallocate prepare stmt;
+drop view v1;
+drop table t1;
+
+--echo #
--echo # End of 10.3 tests
--echo #
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 024bd36..b6787a1 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -96,7 +96,8 @@ static void make_unique_view_field_name(THD *thd, Item *target,
itc.rewind();
}
- target->orig_name= target->name.str;
+ if (!target->orig_name)
+ target->orig_name= target->name.str;
target->set_name(thd, buff, name_len, system_charset_info);
}
1
0
revision-id: 7ba842d1e8d81ca99887e500187ec6dc6964c858 (mariadb-10.6.1-371-g7ba842d1e8d)
parent(s): b6ff2b590519ab141fca0cd597abf0ffbde0ac14
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-03-21 19:41:00 +0300
message:
Update test results (6)
---
mysql-test/main/named_pipe.result | 110 +++++++++++++++++++-------------------
1 file changed, 55 insertions(+), 55 deletions(-)
diff --git a/mysql-test/main/named_pipe.result b/mysql-test/main/named_pipe.result
index e512e2a0f5f..903afc4053a 100644
--- a/mysql-test/main/named_pipe.result
+++ b/mysql-test/main/named_pipe.result
@@ -1360,82 +1360,82 @@ select count(*) from t2 left join t4 using (companynr) where t4.companynr is not
count(*)
1199
explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200
-1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 100.00
+1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 Using where; Not exists
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 Using where; Not exists
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 100.00 Using where; Not exists
select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
companynr companyname
select count(*) from t2 left join t4 using (companynr) where companynr is not null;
count(*)
1200
explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
delete from t2 where fld1=999999;
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
-1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
+1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
-1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
+1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
-1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
+1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 Using where
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 Using where
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 Using where
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 Using where
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where
SET @@optimizer_switch=@local_optimizer_switch;
select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
companynr companynr
37 36
41 40
explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t4 NULL index NULL PRIMARY 1 NULL 12 Using index; Using temporary
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join)
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t4 NULL index NULL PRIMARY 1 NULL 12 100.00 Using index; Using temporary
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (flat, BNL join)
SET @@join_cache_level=@local_join_cache_level;
select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
fld1 companynr fld3 period
@@ -1950,11 +1950,11 @@ select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr =
fld1 sum(price)
038008 234298
explain select fld3 from t2 where 1>2 or 2>3;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
explain select fld3 from t2 where fld1=fld1;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00
select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502;
companynr fld1
34 250501
@@ -2005,8 +2005,8 @@ select count(*) from t3 where companynr=512 and price2=76234234;
count(*)
4181
explain select min(fld1),max(fld1),count(*) from t2;
-id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table partitions type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
select min(fld1),max(fld1),count(*) from t2;
min(fld1) max(fld1) count(*)
0 1232609 1199
1
0
revision-id: b6ff2b590519ab141fca0cd597abf0ffbde0ac14 (mariadb-10.6.1-370-gb6ff2b59051)
parent(s): f00c21ce9785f2ebffd7e500773d29dfe7d45768
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-03-21 18:07:48 +0300
message:
Update test results (5)
---
mysql-test/include/index_merge_ror_cpk.inc | 1 +
mysql-test/main/index_merge_innodb.result | 2 +-
mysql-test/main/index_merge_myisam.result | 2 +-
mysql-test/main/join_cache.result | 286 ++++++++++++++---------------
mysql-test/main/join_cache.test | 64 ++++++-
mysql-test/main/myisam_icp.result | 2 +-
mysql-test/suite/maria/icp.result | 2 +-
7 files changed, 210 insertions(+), 149 deletions(-)
diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc
index c2da93cf383..59c7a6194a3 100644
--- a/mysql-test/include/index_merge_ror_cpk.inc
+++ b/mysql-test/include/index_merge_ror_cpk.inc
@@ -60,6 +60,7 @@ commit;
# Verify that range scan on CPK is ROR
# (use index_intersection because it is impossible to check that for index union)
+--replace_column 11 FLTRD
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
# CPK scan + 1 ROR range scan is a special case
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result
index 0d1e20b6860..4eac7afa1fd 100644
--- a/mysql-test/main/index_merge_innodb.result
+++ b/mysql-test/main/index_merge_innodb.result
@@ -547,7 +547,7 @@ primary key (pk1, pk2)
);
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 10 0.10 Using where
+1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 10 FLTRD Using where
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result
index f9234230b82..1eec57a9834 100644
--- a/mysql-test/main/index_merge_myisam.result
+++ b/mysql-test/main/index_merge_myisam.result
@@ -1379,7 +1379,7 @@ primary key (pk1, pk2)
);
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 7 0.30 Using index condition; Using where
+1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 7 FLTRD Using index condition; Using where
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index 02c87eb8195..3e62b54bf5d 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -58,8 +58,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -88,9 +88,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join)
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join)
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -136,8 +136,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -166,9 +166,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join)
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join)
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (incremental, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -292,8 +292,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -322,9 +322,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (incremental, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -410,8 +410,8 @@ FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.00 Using where
-1 SIMPLE City NULL hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 24 0.59 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 24 FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
@@ -437,8 +437,8 @@ ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.00 Using where
-1 SIMPLE City NULL hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND
@@ -476,8 +476,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -506,9 +506,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join)
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join)
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -554,8 +554,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -584,9 +584,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join)
-1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join)
+1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (incremental, BNL join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -632,8 +632,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -662,9 +662,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -710,8 +710,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where
-1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -740,9 +740,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where
-1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (incremental, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (incremental, BNLH join)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -821,8 +821,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -851,9 +851,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (flat, BNLH join); Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (flat, BNLH join); Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -923,8 +923,8 @@ FROM Country LEFT JOIN CountryLanguage ON
WHERE
Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where
-1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 0.10 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -1021,8 +1021,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -1051,9 +1051,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (incremental, BNLH join); Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (incremental, BNLH join); Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1095,8 +1095,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join)
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -1123,8 +1123,8 @@ FROM Country LEFT JOIN CountryLanguage ON
WHERE
Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where
-1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 0.10 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -1215,8 +1215,8 @@ FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range Name Name 52 NULL # 32.64 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # 0.74 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range Name Name 52 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code ROWS FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
@@ -1243,8 +1243,8 @@ ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range Name Name 52 NULL 17 32.64 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range Name Name 52 NULL 17 FLTRD Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND
@@ -1280,8 +1280,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -1310,9 +1310,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1354,8 +1354,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -1382,8 +1382,8 @@ FROM Country LEFT JOIN CountryLanguage ON
WHERE
Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where
-1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -1477,8 +1477,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -1507,9 +1507,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1551,8 +1551,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -1579,8 +1579,8 @@ FROM Country LEFT JOIN CountryLanguage ON
WHERE
Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where
-1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -1674,8 +1674,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -1704,9 +1704,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1748,8 +1748,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -1776,8 +1776,8 @@ FROM Country LEFT JOIN CountryLanguage ON
WHERE
Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where
-1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -1871,8 +1871,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -1901,9 +1901,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1945,8 +1945,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -1973,8 +1973,8 @@ FROM Country LEFT JOIN CountryLanguage ON
WHERE
Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where
-1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where
+1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
@@ -2072,8 +2072,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -2102,9 +2102,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (flat, BNLH join); Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (flat, BNLH join); Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2146,8 +2146,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -2176,8 +2176,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -2206,9 +2206,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join)
-1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (incremental, BNLH join); Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (incremental, BNLH join); Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2250,8 +2250,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join)
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -2280,8 +2280,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -2310,9 +2310,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2354,8 +2354,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -2384,8 +2384,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -2414,9 +2414,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2458,8 +2458,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -2488,8 +2488,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -2518,9 +2518,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2562,8 +2562,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -2592,8 +2592,8 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
@@ -2622,9 +2622,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2666,8 +2666,8 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan
-1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan
+1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
@@ -2745,8 +2745,8 @@ EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE City NULL range Population,Country Population 4 NULL # 100.00 Using index condition; Rowid-ordered scan
-1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country # 100.00 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+1 SIMPLE City NULL range Population,Country Population 4 NULL ROWS FLTRD Using index condition; Rowid-ordered scan
+1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country ROWS FLTRD Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
Name Name
@@ -2899,8 +2899,8 @@ FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range Name Name 302 NULL 15 32.64 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 24 0.59 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range Name Name 302 NULL 15 FLTRD Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 24 FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -2908,8 +2908,8 @@ ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE Country NULL range Name Name 302 NULL 15 32.64 Using index condition; Using where; Rowid-ordered scan
-1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
+1 SIMPLE Country NULL range Name Name 302 NULL 15 FLTRD Using index condition; Using where; Rowid-ordered scan
+1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set @@use_stat_tables=@save_use_stat_tables;
set @@join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test
index 1bea4cfcbf8..4564b63cd5c 100644
--- a/mysql-test/main/join_cache.test
+++ b/mysql-test/main/join_cache.test
@@ -51,6 +51,7 @@ set join_cache_level=1;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -61,6 +62,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -82,6 +84,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=2;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -92,6 +95,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -148,6 +152,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=4;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -158,6 +163,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -195,6 +201,7 @@ CREATE INDEX City_Name ON City(Name);
ANALYZE TABLE City;
--enable_result_log
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -206,6 +213,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -229,6 +237,7 @@ show variables like 'join_buffer_size';
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -239,6 +248,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -260,6 +270,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=2;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -270,6 +281,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -291,6 +303,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=3;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -301,6 +314,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -322,6 +336,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
set join_cache_level=4;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -332,6 +347,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -374,6 +390,7 @@ show variables like 'join_buffer_size';
set join_cache_level=3;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -384,6 +401,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -412,6 +430,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -429,6 +448,7 @@ show variables like 'join_buffer_size';
set join_cache_level=4;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -439,6 +459,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -457,6 +478,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -466,6 +488,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -480,7 +503,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
Country.Population > 10000000;
---replace_column 10 #
+--replace_column 10 ROWS 11 FLTRD
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -494,6 +517,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population
CREATE INDEX City_Name ON City(Name);
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
@@ -513,6 +537,7 @@ show variables like 'join_buffer_size';
set join_cache_level=5;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -523,6 +548,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -541,6 +567,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -550,6 +577,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -566,6 +594,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
set join_cache_level=6;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -576,6 +605,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -594,6 +624,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -603,6 +634,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -619,6 +651,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
set join_cache_level=7;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -629,6 +662,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -647,6 +681,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -656,6 +691,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -672,6 +708,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P
set join_cache_level=8;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -682,6 +719,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -700,6 +738,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -709,6 +748,7 @@ SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
@@ -728,6 +768,7 @@ show variables like 'join_buffer_size';
set join_cache_level=3;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -738,6 +779,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -756,6 +798,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -768,6 +811,7 @@ SELECT Name FROM City
set join_cache_level=4;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -778,6 +822,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -796,6 +841,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -808,6 +854,7 @@ SELECT Name FROM City
set join_cache_level=5;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -818,6 +865,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -836,6 +884,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -848,6 +897,7 @@ SELECT Name FROM City
set join_cache_level=6;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -858,6 +908,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -876,6 +927,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -888,6 +940,7 @@ SELECT Name FROM City
set join_cache_level=7;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -898,6 +951,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -916,6 +970,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -928,6 +983,7 @@ SELECT Name FROM City
set join_cache_level=8;
show variables like 'join_cache_level';
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
@@ -938,6 +994,7 @@ SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND
Country.Name LIKE 'L%' AND City.Population > 100000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
@@ -956,6 +1013,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language
CountryLanguage.Percentage > 50 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -977,7 +1035,7 @@ SELECT City.Name, Country.Name FROM City,Country
set join_cache_level=8;
set join_buffer_size=384;
---replace_column 10 #
+--replace_column 10 ROWS 11 FLTRD
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
@@ -1041,12 +1099,14 @@ CREATE INDEX City_Name ON City(Name);
ANALYZE TABLE City, Country;
--enable_result_log
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+--replace_column 11 FLTRD
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result
index dba40aa6369..211773621c1 100644
--- a/mysql-test/main/myisam_icp.result
+++ b/mysql-test/main/myisam_icp.result
@@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 NULL range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) 50.00 Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter
+1 SIMPLE t1 NULL range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter
DROP TABLE t1;
#
#
diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result
index 7c0b9c701e0..8117293cd80 100644
--- a/mysql-test/suite/maria/icp.result
+++ b/mysql-test/suite/maria/icp.result
@@ -409,7 +409,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240
ORDER BY c1
LIMIT 1;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 NULL range PRIMARY,k1 PRIMARY 4 NULL 3 50.00 Using index condition; Using where; Rowid-ordered scan; Using filesort
+1 SIMPLE t1 NULL range PRIMARY,k1 PRIMARY 4 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan; Using filesort
DROP TABLE t1;
#
#
1
0
21 Mar '22
revision-id: f00c21ce9785f2ebffd7e500773d29dfe7d45768 (mariadb-10.6.1-369-gf00c21ce978)
parent(s): d5cd8d3d5f6ecf283df9ea2610abe7b4abfd69c7
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-03-21 16:47:32 +0300
message:
Update libmariadb to fix unit.conc_ps_bugs test.
---
libmariadb | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/libmariadb b/libmariadb
index 39d19136f47..06c28696163 160000
--- a/libmariadb
+++ b/libmariadb
@@ -1 +1 @@
-Subproject commit 39d19136f4700b9c756cef637e5156a67cb90114
+Subproject commit 06c28696163900a6eae7a565ac693f4eb3a80d7f
1
0
[Commits] 06c2869: MDEV-27776: Make EXPLAIN show filtered and partitions columns
by psergey 21 Mar '22
by psergey 21 Mar '22
21 Mar '22
revision-id: 06c28696163900a6eae7a565ac693f4eb3a80d7f ()
parent(s): 39d19136f4700b9c756cef637e5156a67cb90114
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2022-03-21 16:44:40 +0300
message:
MDEV-27776: Make EXPLAIN show filtered and partitions columns
Update test_explain_bug() to account that EXPLAIN output may
include "partitions" and "filtered" columns.
---
unittest/libmariadb/ps_bugs.c | 59 +++++++++++++++++++++++++++++++++----------
1 file changed, 45 insertions(+), 14 deletions(-)
diff --git a/unittest/libmariadb/ps_bugs.c b/unittest/libmariadb/ps_bugs.c
index 95ff3e2..052fe14 100644
--- a/unittest/libmariadb/ps_bugs.c
+++ b/unittest/libmariadb/ps_bugs.c
@@ -3462,6 +3462,7 @@ static int test_explain_bug(MYSQL *mysql)
MYSQL_STMT *stmt;
MYSQL_RES *result;
int rc;
+ int fieldno;
if (!is_mariadb)
return SKIP;
@@ -3571,50 +3572,80 @@ static int test_explain_bug(MYSQL *mysql)
result= mysql_stmt_result_metadata(stmt);
FAIL_IF(!result, "Invalid result set");
- FAIL_UNLESS(10 == mysql_num_fields(result), "fields != 10");
-
- if (verify_prepare_field(result, 0, "id", "", MYSQL_TYPE_LONGLONG, "", "", "", 3, 0))
+ FAIL_UNLESS((mysql_num_fields(result) == 10 ||
+ mysql_num_fields(result) == 12), "fields not in (10,12)");
+
+ fieldno= 0;
+ if (verify_prepare_field(result, fieldno++, "id", "", MYSQL_TYPE_LONGLONG, "", "", "", 3, 0))
goto error;
- if (verify_prepare_field(result, 1, "select_type", "", MYSQL_TYPE_VAR_STRING, "", "", "", 19, 0))
+ if (verify_prepare_field(result, fieldno++, "select_type", "", MYSQL_TYPE_VAR_STRING, "", "", "", 19, 0))
goto error;
- if (verify_prepare_field(result, 2, "table", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN, 0))
+ if (verify_prepare_field(result, fieldno++, "table", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN, 0))
goto error;
- if (verify_prepare_field(result, 3, "type", "", MYSQL_TYPE_VAR_STRING, "", "", "", 10, 0))
+ /* The next field can be 'partitions'. */
+ {
+ MYSQL_FIELD *field;
+ FAIL_IF(!(field= mysql_fetch_field_direct(result, fieldno)), "FAILED to get result");
+ if (!strcmp(field->name, "partitions"))
+ {
+ /*
+ The length is 6316032 = MAX_PARTITIONS * (1 + FN_LEN) * 3. See
+ mysql_client_test.c:test_explain_bug()
+ */
+ if (verify_prepare_field(result, fieldno++, "partitions", "",
+ MYSQL_TYPE_MEDIUM_BLOB, "", "", "", 6316032, 0))
+ goto error;
+ }
+ }
+
+ if (verify_prepare_field(result, fieldno++, "type", "", MYSQL_TYPE_VAR_STRING, "", "", "", 10, 0))
goto error;
- if (verify_prepare_field(result, 4, "possible_keys", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN*MAX_KEY, 0))
+ if (verify_prepare_field(result, fieldno++, "possible_keys", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN*MAX_KEY, 0))
goto error;
- if ( verify_prepare_field(result, 5, "key", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN, 0))
+ if ( verify_prepare_field(result, fieldno++, "key", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN, 0))
goto error;
if (mysql_get_server_version(mysql) <= 50000)
{
- if (verify_prepare_field(result, 6, "key_len", "", MYSQL_TYPE_LONGLONG, "", "", "", 3, 0))
+ if (verify_prepare_field(result, fieldno++, "key_len", "", MYSQL_TYPE_LONGLONG, "", "", "", 3, 0))
goto error;
}
else if (mysql_get_server_version(mysql) <= 60000)
{
- if (verify_prepare_field(result, 6, "key_len", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN*MAX_KEY, 0))
+ if (verify_prepare_field(result, fieldno++, "key_len", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN*MAX_KEY, 0))
goto error;
}
else
{
- if (verify_prepare_field(result, 6, "key_len", "", MYSQL_TYPE_VAR_STRING, "", "", "", (MAX_KEY_LENGTH_DECIMAL_WIDTH + 1) * MAX_KEY, 0))
+ if (verify_prepare_field(result, fieldno++, "key_len", "", MYSQL_TYPE_VAR_STRING, "", "", "", (MAX_KEY_LENGTH_DECIMAL_WIDTH + 1) * MAX_KEY, 0))
goto error;
}
- if (verify_prepare_field(result, 7, "ref", "", MYSQL_TYPE_VAR_STRING, "", "", "",
+ if (verify_prepare_field(result, fieldno++, "ref", "", MYSQL_TYPE_VAR_STRING, "", "", "",
NAME_CHAR_LEN*16, 0))
goto error;
- if (verify_prepare_field(result, 8, "rows", "", MYSQL_TYPE_LONGLONG, "", "", "", 10, 0))
+ if (verify_prepare_field(result, fieldno++, "rows", "", MYSQL_TYPE_LONGLONG, "", "", "", 10, 0))
goto error;
- if (verify_prepare_field(result, 9, "Extra", "", MYSQL_TYPE_VAR_STRING, "", "", "", 255, 0))
+ /* The next field can be "filtered" */
+ {
+ MYSQL_FIELD *field;
+ FAIL_IF(!(field= mysql_fetch_field_direct(result, fieldno)), "FAILED to get result");
+ if (!strcmp(field->name, "filtered"))
+ {
+ if (verify_prepare_field(result, fieldno++, "filtered", "",
+ MYSQL_TYPE_DOUBLE, "", "", "", 4, 0))
+ goto error;
+ }
+ }
+
+ if (verify_prepare_field(result, fieldno++, "Extra", "", MYSQL_TYPE_VAR_STRING, "", "", "", 255, 0))
goto error;
mysql_free_result(result);
1
0