22 Dec '21
revision-id: 3027d0d7262159cca2de62d5d415825a7b4e485e (mariadb-10.6.1-260-g3027d0d7262)
parent(s): 3a2eadbcf938f3dd1548e57a381d773b0e24c97a
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-22 17:19:48 +0300
message:
MDEV-26996: Reverse-ordered indexes: improve print-out
When printing a range into optimizer trace, print DESC for columns
that are reverse-ordered, for example:
"(4) <= (key1 DESC) <= (2)"
---
mysql-test/main/desc_index_range.result | 14 +++++++-------
sql/opt_range.cc | 2 ++
2 files changed, 9 insertions(+), 7 deletions(-)
diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result
index feec5dc1720..244659e3f48 100644
--- a/mysql-test/main/desc_index_range.result
+++ b/mysql-test/main/desc_index_range.result
@@ -13,9 +13,9 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(6) <= (a) <= (6)",
- "(4) <= (a) <= (4)",
- "(2) <= (a) <= (2)"
+ "(6) <= (a DESC) <= (6)",
+ "(4) <= (a DESC) <= (4)",
+ "(2) <= (a DESC) <= (2)"
]
]
set optimizer_trace=default;
@@ -57,7 +57,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(8,50) <= (a,b)"
+ "(8,50) <= (a,b DESC)"
]
]
select * from t1 force index(ab) where a>=8 and b<=50;
@@ -104,7 +104,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(2,80) <= (a,b) <= (4,50)"
+ "(2,80) <= (a,b DESC) <= (4,50)"
]
]
select * from t1 where a between 2 and 4 and b between 50 and 80;
@@ -138,7 +138,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(4) <= (a) <= (2)"
+ "(4) <= (a DESC) <= (2)"
]
]
explain
@@ -151,7 +151,7 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges'))
[
[
- "(4,80) <= (a,b) <= (2,50)"
+ "(4,80) <= (a DESC,b DESC) <= (2,50)"
]
]
drop table t2;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index bbb7b3d87fa..7b70dca86a6 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -16634,6 +16634,8 @@ void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
else
out->append(STRING_WITH_LEN(","));
out->append(key_part->field->field_name);
+ if (key_part->key_part_flag & HA_REVERSE_SORT)
+ out->append(STRING_WITH_LEN(" DESC"));
}
else
break;
1
0
[Commits] 3a2eadbcf93: MDEV-26996: Reverse-ordered indexes: remove SEL_ARG::is_ascending
by psergey 22 Dec '21
by psergey 22 Dec '21
22 Dec '21
revision-id: 3a2eadbcf938f3dd1548e57a381d773b0e24c97a (mariadb-10.6.1-259-g3a2eadbcf93)
parent(s): 275b5fccefa3921cb3891e0c23af37c41bbe69a6
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-22 17:05:15 +0300
message:
MDEV-26996: Reverse-ordered indexes: remove SEL_ARG::is_ascending
Instead, Get the "is_ascending" value from the array of KEY_PART
structures that describes the [pseudo-]index that is being analyzed.
---
sql/item_geofunc.cc | 2 +-
sql/opt_range.cc | 76 +++++++++++++++++++++++++---------------------------
sql/opt_range.h | 55 +++++++++++++++++--------------------
sql/opt_range_mrr.cc | 27 ++++++++++---------
4 files changed, 77 insertions(+), 83 deletions(-)
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index a2a99bcdf8f..0fdcf9e94e2 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1084,7 +1084,7 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param,
field->get_key_image(str, key_part->length, key_part->image_type);
SEL_ARG *tree;
- if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str)))
+ if (!(tree= new (param->mem_root) SEL_ARG(field, str, str)))
DBUG_RETURN(0); // out of memory
switch (type) {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ae2b5060625..bbb7b3d87fa 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1879,7 +1879,6 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc()
max_flag=arg.max_flag;
maybe_flag=arg.maybe_flag;
maybe_null=arg.maybe_null;
- is_ascending= arg.is_ascending;
part=arg.part;
field=arg.field;
min_value=arg.min_value;
@@ -1905,10 +1904,9 @@ inline void SEL_ARG::make_root()
use_count=0; elements=1;
}
-SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
+SEL_ARG::SEL_ARG(Field *f, const uchar *min_value_arg,
const uchar *max_value_arg)
:min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()),
- is_ascending(is_asc),
elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg),
max_value((uchar*) max_value_arg), next(0),prev(0),
next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1)
@@ -1917,11 +1915,11 @@ SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
max_part_no= 1;
}
-SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
+SEL_ARG::SEL_ARG(Field *field_,uint8 part_,
uchar *min_value_, uchar *max_value_,
uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_)
:min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_),
- part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_),
+ part(part_),maybe_null(field_->real_maybe_null()),
elements(1),use_count(1),
field(field_), min_value(min_value_), max_value(max_value_),
next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1)
@@ -1941,8 +1939,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
class SEL_ARG_LE: public SEL_ARG
{
public:
- SEL_ARG_LE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_LE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
if (!field->real_maybe_null())
min_flag= NO_MIN_RANGE; // From start
@@ -1962,17 +1960,17 @@ class SEL_ARG_LT: public SEL_ARG_LE
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_LT(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG_LE(key, field, is_asc)
+ SEL_ARG_LT(const uchar *key, Field *field)
+ :SEL_ARG_LE(key, field)
{ max_flag= NEAR_MAX; }
/*
Use this constructor if value->save_in_field() returned success,
but we don't know if rounding or truncation happened
(as some Field::store() do not report minor data changes).
*/
- SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc,
+ SEL_ARG_LT(THD *thd, const uchar *key, Field *field,
Item *value)
- :SEL_ARG_LE(key, field, is_asc)
+ :SEL_ARG_LE(key, field)
{
if (stored_field_cmp_to_item(thd, field, value) == 0)
max_flag= NEAR_MAX;
@@ -1988,7 +1986,7 @@ class SEL_ARG_GT: public SEL_ARG
without any data rounding or truncation.
*/
SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if (!(key_part->flag & HA_PART_KEY_SEG))
@@ -2002,7 +2000,7 @@ class SEL_ARG_GT: public SEL_ARG
*/
SEL_ARG_GT(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2020,8 +2018,8 @@ class SEL_ARG_GE: public SEL_ARG
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_GE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_GE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
max_flag= NO_MAX_RANGE;
}
@@ -2032,7 +2030,7 @@ class SEL_ARG_GE: public SEL_ARG
*/
SEL_ARG_GE(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2063,7 +2061,7 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent,
}
else
{
- if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending,
+ if (!(tmp= new (param->mem_root) SEL_ARG(field, part,
min_value, max_value,
min_flag, max_flag, maybe_flag)))
return 0; // OOM
@@ -3244,6 +3242,7 @@ double records_in_column_ranges(PARAM *param, uint idx,
seq.keyno= idx;
seq.real_keyno= MAX_KEY;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
seq.is_ror_scan= FALSE;
@@ -8669,8 +8668,7 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param,
if (!field->real_maybe_null())
DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL);
SEL_ARG *tree;
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string)))
+ if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string)))
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
{
@@ -8770,8 +8768,7 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
int2store(min_str + maybe_null, min_length);
int2store(max_str + maybe_null, max_length);
}
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str);
+ SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str);
DBUG_RETURN(tree);
}
@@ -9019,19 +9016,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value));
case SCALAR_CMP_GT:
DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value));
case SCALAR_CMP_GE:
DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -9049,19 +9045,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this));
case SCALAR_CMP_GT:
DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this));
case SCALAR_CMP_GE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -11531,6 +11526,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
seq.keyno= idx;
seq.real_keyno= keynr;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
@@ -11785,9 +11781,9 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
int *min_part, int *max_part)
{
DBUG_ASSERT(next_key_part);
- bool asc = next_key_part->is_ascending;
+ const bool asc = !(key[next_key_part->part].flag & HA_REVERSE_SORT);
- if (!get_min_flag())
+ if (!get_min_flag(key))
{
if (asc)
{
@@ -11802,7 +11798,7 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
*cur_min_flag = invert_max_flag(tmp_flag);
}
}
- if (!get_max_flag())
+ if (!get_max_flag(key))
{
if (asc)
{
@@ -11832,7 +11828,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
int min_part= key_tree->part-1, // # of keypart values in min_key buffer
max_part= key_tree->part-1; // # of keypart values in max_key buffer
- SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
+ SEL_ARG *next_tree = asc ? key_tree->left : key_tree->right;
if (next_tree != &null_element)
{
if (get_quick_keys(param,quick,key,next_tree,
@@ -11841,7 +11838,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
uchar *tmp_min_key=min_key,*tmp_max_key=max_key;
- key_tree->store_min_max(key[key_tree->part].store_length,
+ key_tree->store_min_max(key, key[key_tree->part].store_length,
&tmp_min_key, min_key_flag,
&tmp_max_key, max_key_flag,
&min_part, &max_part);
@@ -11864,8 +11861,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
goto end; // Ugly, but efficient
}
{
- uint tmp_min_flag= key_tree->get_min_flag();
- uint tmp_max_flag= key_tree->get_max_flag();
+ uint tmp_min_flag= key_tree->get_min_flag(key);
+ uint tmp_max_flag= key_tree->get_max_flag(key);
key_tree->store_next_min_max_keys(key,
&tmp_min_key, &tmp_min_flag,
@@ -11876,7 +11873,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
else
{
- if (key_tree->is_ascending)
+ if (asc)
{
flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag:
(key_tree->min_flag |
@@ -11948,7 +11945,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
return 1;
end:
- next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left;
+ next_tree= asc ? key_tree->right : key_tree->left;
if (next_tree != &null_element)
return get_quick_keys(param,quick,key,next_tree,
min_key,min_key_flag,
@@ -16559,6 +16556,7 @@ static void trace_ranges(Json_writer_array *range_trace,
uint n_key_parts= param->table->actual_n_key_parts(keyinfo);
DBUG_ASSERT(range_trace->trace_started());
seq.keyno= idx;
+ seq.key_parts= param->key[idx];
seq.real_keyno= param->real_keynr[idx];
seq.param= param;
seq.start= keypart;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6864a5c583a..f3ccd4d8311 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -306,11 +306,6 @@ class SEL_ARG :public Sql_alloc
uint8 min_flag,max_flag,maybe_flag;
uint8 part; // Which key part
uint8 maybe_null;
- /*
- Whether the keypart is ascending or descending.
- See HowRangeOptimizerHandlesDescKeyparts for details.
- */
- uint8 is_ascending;
/*
The ordinal number the least significant component encountered in
the ranges of the SEL_ARG tree (the first component has number 1)
@@ -361,14 +356,14 @@ class SEL_ARG :public Sql_alloc
SEL_ARG() {}
SEL_ARG(SEL_ARG &);
- SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *);
- SEL_ARG(Field *field, uint8 part, bool is_asc,
+ SEL_ARG(Field *, const uchar *, const uchar *);
+ SEL_ARG(Field *field, uint8 part,
uchar *min_value, uchar *max_value,
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
/* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */
SEL_ARG(enum Type type_arg)
- :min_flag(0), is_ascending(false),
+ :min_flag(0),
max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/,
elements(1),use_count(1),left(0),right(0),
next_key_part(0), color(BLACK), type(type_arg), weight(1)
@@ -447,20 +442,20 @@ class SEL_ARG :public Sql_alloc
{
new_max=arg->max_value; flag_max=arg->max_flag;
}
- return new (thd->mem_root) SEL_ARG(field, part, is_ascending,
+ return new (thd->mem_root) SEL_ARG(field, part,
new_min, new_max, flag_min,
flag_max,
MY_TEST(maybe_flag && arg->maybe_flag));
}
SEL_ARG *clone_first(SEL_ARG *arg)
{ // min <= X < arg->min
- return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value,
+ return new SEL_ARG(field, part, min_value, arg->min_value,
min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX,
maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone_last(SEL_ARG *arg)
{ // min <= X <= key_max
- return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value,
+ return new SEL_ARG(field, part, min_value, arg->max_value,
min_flag, arg->max_flag, maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next);
@@ -544,44 +539,45 @@ class SEL_ARG :public Sql_alloc
}
/* Save minimum and maximum, taking index order into account */
- void store_min_max(uint length,
+ void store_min_max(KEY_PART *kp,
+ uint length,
uchar **min_key, uint min_flag,
uchar **max_key, uint max_flag,
int *min_part, int *max_part)
{
- if (is_ascending) {
- *min_part += store_min(length, min_key, min_flag);
- *max_part += store_max(length, max_key, max_flag);
- } else {
+ if (kp[part].flag & HA_REVERSE_SORT) {
*max_part += store_min(length, max_key, min_flag);
*min_part += store_max(length, min_key, max_flag);
+ } else {
+ *min_part += store_min(length, min_key, min_flag);
+ *max_part += store_max(length, max_key, max_flag);
}
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_min_flag()
+ uint get_min_flag(KEY_PART *kp)
{
- return (is_ascending ? min_flag : invert_max_flag(max_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_max_flag(max_flag) : min_flag;
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_max_flag()
+ uint get_max_flag(KEY_PART *kp)
{
- return (is_ascending ? max_flag : invert_min_flag(min_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_min_flag(min_flag) : max_flag ;
}
/* Get the previous interval, taking index order into account */
- inline SEL_ARG* index_order_prev()
+ inline SEL_ARG* index_order_prev(KEY_PART *kp)
{
- return is_ascending? prev: next;
+ return (kp[part].flag & HA_REVERSE_SORT)? next : prev;
}
/* Get the next interval, taking index order into account */
- inline SEL_ARG* index_order_next()
+ inline SEL_ARG* index_order_next(KEY_PART *kp)
{
- return is_ascending? next: prev;
+ return (kp[part].flag & HA_REVERSE_SORT)? prev : next;
}
/*
@@ -621,7 +617,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if (start_key == asc)
{
res+= nkp->store_min_key(key, range_key, range_key_flag, last_part,
@@ -657,7 +653,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if ((!start_key && asc) || (start_key && !asc))
{
res += nkp->store_max_key(key, range_key, range_key_flag, last_part,
@@ -785,9 +781,6 @@ class SEL_ARG :public Sql_alloc
Range Optimizer handles this as follows:
- The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the
- keypart is ascending.
-
Other than that, the SEL_ARG graph is built without any regard to DESC
keyparts.
@@ -799,7 +792,7 @@ class SEL_ARG :public Sql_alloc
kp1 BETWEEN 10 and 20 (RANGE-1)
- the SEL_ARG will have min_value=10, max_value=20, is_ascending=false.
+ the SEL_ARG will have min_value=10, max_value=20
The ordering of key parts is taken into account when SEL_ARG graph is
linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys().
@@ -850,7 +843,7 @@ class SEL_ARG_IMPOSSIBLE: public SEL_ARG
{
public:
SEL_ARG_IMPOSSIBLE(Field *field)
- :SEL_ARG(field, false, 0, 0)
+ :SEL_ARG(field, 0, 0)
{
type= SEL_ARG::IMPOSSIBLE;
}
diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc
index 8877e15d5b5..452a6864f06 100644
--- a/sql/opt_range_mrr.cc
+++ b/sql/opt_range_mrr.cc
@@ -47,6 +47,7 @@ typedef struct st_sel_arg_range_seq
uint keyno; /* index of used tree in SEL_TREE structure */
uint real_keyno; /* Number of the index in tables */
PARAM *param;
+ KEY_PART *key_parts;
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
@@ -106,13 +107,13 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length;
- key_tree->store_min_max(stor_length,
+ key_tree->store_min_max(arg->key_parts, stor_length,
&cur->min_key, prev->min_key_flag,
&cur->max_key, prev->max_key_flag,
&cur->min_key_parts, &cur->max_key_parts);
- cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag();
- cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag();
+ cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(arg->key_parts);
+ cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(arg->key_parts);
if (key_tree->is_null_interval())
cur->min_key_flag |= NULL_RANGE;
@@ -166,12 +167,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
/* Ok, we're at some "full tuple" position in the tree */
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
seq->is_ror_scan= FALSE;
goto walk_right_n_up;
}
@@ -186,12 +188,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= seq->stack[seq->i].key_tree;
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
break;
}
}
@@ -230,11 +233,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= key_tree->next_key_part;
walk_up_n_right:
- while (key_tree->index_order_prev() &&
- key_tree->index_order_prev() != &null_element)
+ while (key_tree->index_order_prev(seq->key_parts) &&
+ key_tree->index_order_prev(seq->key_parts) != &null_element)
{
/* Step up */
- key_tree= key_tree->index_order_prev();
+ key_tree= key_tree->index_order_prev(seq->key_parts);
}
step_down_to(seq, key_tree);
}
1
0
22 Dec '21
revision-id: 6ded957ccc6919261864262a4e9b53574cf7f1da (mariadb-10.6.1-259-g6ded957ccc6)
parent(s): 275b5fccefa3921cb3891e0c23af37c41bbe69a6
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-22 17:04:09 +0300
message:
Reverse-ordered indexes: remove SEL_ARG::is_ascending
Get the information from the array of KEY_PART structures that describes
the [pseudo-]index that is being analyzed.
---
sql/item_geofunc.cc | 2 +-
sql/opt_range.cc | 76 +++++++++++++++++++++++++---------------------------
sql/opt_range.h | 55 +++++++++++++++++--------------------
sql/opt_range_mrr.cc | 27 ++++++++++---------
4 files changed, 77 insertions(+), 83 deletions(-)
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index a2a99bcdf8f..0fdcf9e94e2 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1084,7 +1084,7 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param,
field->get_key_image(str, key_part->length, key_part->image_type);
SEL_ARG *tree;
- if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str)))
+ if (!(tree= new (param->mem_root) SEL_ARG(field, str, str)))
DBUG_RETURN(0); // out of memory
switch (type) {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ae2b5060625..bbb7b3d87fa 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1879,7 +1879,6 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc()
max_flag=arg.max_flag;
maybe_flag=arg.maybe_flag;
maybe_null=arg.maybe_null;
- is_ascending= arg.is_ascending;
part=arg.part;
field=arg.field;
min_value=arg.min_value;
@@ -1905,10 +1904,9 @@ inline void SEL_ARG::make_root()
use_count=0; elements=1;
}
-SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
+SEL_ARG::SEL_ARG(Field *f, const uchar *min_value_arg,
const uchar *max_value_arg)
:min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()),
- is_ascending(is_asc),
elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg),
max_value((uchar*) max_value_arg), next(0),prev(0),
next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1)
@@ -1917,11 +1915,11 @@ SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
max_part_no= 1;
}
-SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
+SEL_ARG::SEL_ARG(Field *field_,uint8 part_,
uchar *min_value_, uchar *max_value_,
uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_)
:min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_),
- part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_),
+ part(part_),maybe_null(field_->real_maybe_null()),
elements(1),use_count(1),
field(field_), min_value(min_value_), max_value(max_value_),
next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1)
@@ -1941,8 +1939,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
class SEL_ARG_LE: public SEL_ARG
{
public:
- SEL_ARG_LE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_LE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
if (!field->real_maybe_null())
min_flag= NO_MIN_RANGE; // From start
@@ -1962,17 +1960,17 @@ class SEL_ARG_LT: public SEL_ARG_LE
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_LT(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG_LE(key, field, is_asc)
+ SEL_ARG_LT(const uchar *key, Field *field)
+ :SEL_ARG_LE(key, field)
{ max_flag= NEAR_MAX; }
/*
Use this constructor if value->save_in_field() returned success,
but we don't know if rounding or truncation happened
(as some Field::store() do not report minor data changes).
*/
- SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc,
+ SEL_ARG_LT(THD *thd, const uchar *key, Field *field,
Item *value)
- :SEL_ARG_LE(key, field, is_asc)
+ :SEL_ARG_LE(key, field)
{
if (stored_field_cmp_to_item(thd, field, value) == 0)
max_flag= NEAR_MAX;
@@ -1988,7 +1986,7 @@ class SEL_ARG_GT: public SEL_ARG
without any data rounding or truncation.
*/
SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if (!(key_part->flag & HA_PART_KEY_SEG))
@@ -2002,7 +2000,7 @@ class SEL_ARG_GT: public SEL_ARG
*/
SEL_ARG_GT(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2020,8 +2018,8 @@ class SEL_ARG_GE: public SEL_ARG
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_GE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_GE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
max_flag= NO_MAX_RANGE;
}
@@ -2032,7 +2030,7 @@ class SEL_ARG_GE: public SEL_ARG
*/
SEL_ARG_GE(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2063,7 +2061,7 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent,
}
else
{
- if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending,
+ if (!(tmp= new (param->mem_root) SEL_ARG(field, part,
min_value, max_value,
min_flag, max_flag, maybe_flag)))
return 0; // OOM
@@ -3244,6 +3242,7 @@ double records_in_column_ranges(PARAM *param, uint idx,
seq.keyno= idx;
seq.real_keyno= MAX_KEY;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
seq.is_ror_scan= FALSE;
@@ -8669,8 +8668,7 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param,
if (!field->real_maybe_null())
DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL);
SEL_ARG *tree;
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string)))
+ if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string)))
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
{
@@ -8770,8 +8768,7 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
int2store(min_str + maybe_null, min_length);
int2store(max_str + maybe_null, max_length);
}
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str);
+ SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str);
DBUG_RETURN(tree);
}
@@ -9019,19 +9016,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value));
case SCALAR_CMP_GT:
DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value));
case SCALAR_CMP_GE:
DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -9049,19 +9045,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this));
case SCALAR_CMP_GT:
DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this));
case SCALAR_CMP_GE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -11531,6 +11526,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
seq.keyno= idx;
seq.real_keyno= keynr;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
@@ -11785,9 +11781,9 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
int *min_part, int *max_part)
{
DBUG_ASSERT(next_key_part);
- bool asc = next_key_part->is_ascending;
+ const bool asc = !(key[next_key_part->part].flag & HA_REVERSE_SORT);
- if (!get_min_flag())
+ if (!get_min_flag(key))
{
if (asc)
{
@@ -11802,7 +11798,7 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
*cur_min_flag = invert_max_flag(tmp_flag);
}
}
- if (!get_max_flag())
+ if (!get_max_flag(key))
{
if (asc)
{
@@ -11832,7 +11828,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
int min_part= key_tree->part-1, // # of keypart values in min_key buffer
max_part= key_tree->part-1; // # of keypart values in max_key buffer
- SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
+ SEL_ARG *next_tree = asc ? key_tree->left : key_tree->right;
if (next_tree != &null_element)
{
if (get_quick_keys(param,quick,key,next_tree,
@@ -11841,7 +11838,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
uchar *tmp_min_key=min_key,*tmp_max_key=max_key;
- key_tree->store_min_max(key[key_tree->part].store_length,
+ key_tree->store_min_max(key, key[key_tree->part].store_length,
&tmp_min_key, min_key_flag,
&tmp_max_key, max_key_flag,
&min_part, &max_part);
@@ -11864,8 +11861,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
goto end; // Ugly, but efficient
}
{
- uint tmp_min_flag= key_tree->get_min_flag();
- uint tmp_max_flag= key_tree->get_max_flag();
+ uint tmp_min_flag= key_tree->get_min_flag(key);
+ uint tmp_max_flag= key_tree->get_max_flag(key);
key_tree->store_next_min_max_keys(key,
&tmp_min_key, &tmp_min_flag,
@@ -11876,7 +11873,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
else
{
- if (key_tree->is_ascending)
+ if (asc)
{
flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag:
(key_tree->min_flag |
@@ -11948,7 +11945,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
return 1;
end:
- next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left;
+ next_tree= asc ? key_tree->right : key_tree->left;
if (next_tree != &null_element)
return get_quick_keys(param,quick,key,next_tree,
min_key,min_key_flag,
@@ -16559,6 +16556,7 @@ static void trace_ranges(Json_writer_array *range_trace,
uint n_key_parts= param->table->actual_n_key_parts(keyinfo);
DBUG_ASSERT(range_trace->trace_started());
seq.keyno= idx;
+ seq.key_parts= param->key[idx];
seq.real_keyno= param->real_keynr[idx];
seq.param= param;
seq.start= keypart;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6864a5c583a..f3ccd4d8311 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -306,11 +306,6 @@ class SEL_ARG :public Sql_alloc
uint8 min_flag,max_flag,maybe_flag;
uint8 part; // Which key part
uint8 maybe_null;
- /*
- Whether the keypart is ascending or descending.
- See HowRangeOptimizerHandlesDescKeyparts for details.
- */
- uint8 is_ascending;
/*
The ordinal number the least significant component encountered in
the ranges of the SEL_ARG tree (the first component has number 1)
@@ -361,14 +356,14 @@ class SEL_ARG :public Sql_alloc
SEL_ARG() {}
SEL_ARG(SEL_ARG &);
- SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *);
- SEL_ARG(Field *field, uint8 part, bool is_asc,
+ SEL_ARG(Field *, const uchar *, const uchar *);
+ SEL_ARG(Field *field, uint8 part,
uchar *min_value, uchar *max_value,
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
/* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */
SEL_ARG(enum Type type_arg)
- :min_flag(0), is_ascending(false),
+ :min_flag(0),
max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/,
elements(1),use_count(1),left(0),right(0),
next_key_part(0), color(BLACK), type(type_arg), weight(1)
@@ -447,20 +442,20 @@ class SEL_ARG :public Sql_alloc
{
new_max=arg->max_value; flag_max=arg->max_flag;
}
- return new (thd->mem_root) SEL_ARG(field, part, is_ascending,
+ return new (thd->mem_root) SEL_ARG(field, part,
new_min, new_max, flag_min,
flag_max,
MY_TEST(maybe_flag && arg->maybe_flag));
}
SEL_ARG *clone_first(SEL_ARG *arg)
{ // min <= X < arg->min
- return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value,
+ return new SEL_ARG(field, part, min_value, arg->min_value,
min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX,
maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone_last(SEL_ARG *arg)
{ // min <= X <= key_max
- return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value,
+ return new SEL_ARG(field, part, min_value, arg->max_value,
min_flag, arg->max_flag, maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next);
@@ -544,44 +539,45 @@ class SEL_ARG :public Sql_alloc
}
/* Save minimum and maximum, taking index order into account */
- void store_min_max(uint length,
+ void store_min_max(KEY_PART *kp,
+ uint length,
uchar **min_key, uint min_flag,
uchar **max_key, uint max_flag,
int *min_part, int *max_part)
{
- if (is_ascending) {
- *min_part += store_min(length, min_key, min_flag);
- *max_part += store_max(length, max_key, max_flag);
- } else {
+ if (kp[part].flag & HA_REVERSE_SORT) {
*max_part += store_min(length, max_key, min_flag);
*min_part += store_max(length, min_key, max_flag);
+ } else {
+ *min_part += store_min(length, min_key, min_flag);
+ *max_part += store_max(length, max_key, max_flag);
}
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_min_flag()
+ uint get_min_flag(KEY_PART *kp)
{
- return (is_ascending ? min_flag : invert_max_flag(max_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_max_flag(max_flag) : min_flag;
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_max_flag()
+ uint get_max_flag(KEY_PART *kp)
{
- return (is_ascending ? max_flag : invert_min_flag(min_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_min_flag(min_flag) : max_flag ;
}
/* Get the previous interval, taking index order into account */
- inline SEL_ARG* index_order_prev()
+ inline SEL_ARG* index_order_prev(KEY_PART *kp)
{
- return is_ascending? prev: next;
+ return (kp[part].flag & HA_REVERSE_SORT)? next : prev;
}
/* Get the next interval, taking index order into account */
- inline SEL_ARG* index_order_next()
+ inline SEL_ARG* index_order_next(KEY_PART *kp)
{
- return is_ascending? next: prev;
+ return (kp[part].flag & HA_REVERSE_SORT)? prev : next;
}
/*
@@ -621,7 +617,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if (start_key == asc)
{
res+= nkp->store_min_key(key, range_key, range_key_flag, last_part,
@@ -657,7 +653,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if ((!start_key && asc) || (start_key && !asc))
{
res += nkp->store_max_key(key, range_key, range_key_flag, last_part,
@@ -785,9 +781,6 @@ class SEL_ARG :public Sql_alloc
Range Optimizer handles this as follows:
- The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the
- keypart is ascending.
-
Other than that, the SEL_ARG graph is built without any regard to DESC
keyparts.
@@ -799,7 +792,7 @@ class SEL_ARG :public Sql_alloc
kp1 BETWEEN 10 and 20 (RANGE-1)
- the SEL_ARG will have min_value=10, max_value=20, is_ascending=false.
+ the SEL_ARG will have min_value=10, max_value=20
The ordering of key parts is taken into account when SEL_ARG graph is
linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys().
@@ -850,7 +843,7 @@ class SEL_ARG_IMPOSSIBLE: public SEL_ARG
{
public:
SEL_ARG_IMPOSSIBLE(Field *field)
- :SEL_ARG(field, false, 0, 0)
+ :SEL_ARG(field, 0, 0)
{
type= SEL_ARG::IMPOSSIBLE;
}
diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc
index 8877e15d5b5..452a6864f06 100644
--- a/sql/opt_range_mrr.cc
+++ b/sql/opt_range_mrr.cc
@@ -47,6 +47,7 @@ typedef struct st_sel_arg_range_seq
uint keyno; /* index of used tree in SEL_TREE structure */
uint real_keyno; /* Number of the index in tables */
PARAM *param;
+ KEY_PART *key_parts;
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
@@ -106,13 +107,13 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length;
- key_tree->store_min_max(stor_length,
+ key_tree->store_min_max(arg->key_parts, stor_length,
&cur->min_key, prev->min_key_flag,
&cur->max_key, prev->max_key_flag,
&cur->min_key_parts, &cur->max_key_parts);
- cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag();
- cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag();
+ cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(arg->key_parts);
+ cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(arg->key_parts);
if (key_tree->is_null_interval())
cur->min_key_flag |= NULL_RANGE;
@@ -166,12 +167,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
/* Ok, we're at some "full tuple" position in the tree */
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
seq->is_ror_scan= FALSE;
goto walk_right_n_up;
}
@@ -186,12 +188,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= seq->stack[seq->i].key_tree;
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
break;
}
}
@@ -230,11 +233,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= key_tree->next_key_part;
walk_up_n_right:
- while (key_tree->index_order_prev() &&
- key_tree->index_order_prev() != &null_element)
+ while (key_tree->index_order_prev(seq->key_parts) &&
+ key_tree->index_order_prev(seq->key_parts) != &null_element)
{
/* Step up */
- key_tree= key_tree->index_order_prev();
+ key_tree= key_tree->index_order_prev(seq->key_parts);
}
step_down_to(seq, key_tree);
}
1
0
[Commits] c6f9a6a: MDEV-27262 Unexpected index intersection with full index scan for an index
by IgorBabaev 21 Dec '21
by IgorBabaev 21 Dec '21
21 Dec '21
revision-id: c6f9a6ac0e4ef94391dc4263af02b078f6fa7960 (mariadb-10.2.31-1286-gc6f9a6a)
parent(s): 8bb55633699612279744c055e22eeca8d4058273
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-12-20 19:55:00 -0800
message:
MDEV-27262 Unexpected index intersection with full index scan for an index
If when extracting a range condition for an index from the WHERE condition
Range Optimizer sees that the range condition covers the whole index then
such condition should be discarded because it cannot be used in any range
scan. In some cases Range Optimizer really does it, but there remained some
conditions for which it was not done. As a result the optimizer could
produce index merge plans with the full index scan for one of the indexes
participating in the index merge.
This could be observed in one of the test cases from index_merge1.inc
where a plan with index_merge_sort_union was produced and in the test case
reported for this bug where a plan with index_merge_sort_intersect was
produced. In both cases one of two index scans participating in index merge
ran over the whole index.
The patch slightly changes the original above mentioned test case from
index_merge1.inc to be able to produce an intended plan employing
index_merge_sort_union. The original query was left to show that index
merge is not used for it anymore.
It should be noted that for the plan with index_merge_sort_intersect could
be chosen for execution only due to a defect in the InnoDB code that
returns wrong estimates for the cardinality of big ranges.
This bug led to serious problems in 10.4+ where the optimization using
Rowid filters is employed (see mdev-26446).
Approved by Sergey Petrunia <sergey(a)mariadb.com>
---
mysql-test/include/index_merge1.inc | 14 ++++-
mysql-test/r/index_merge_myisam.result | 18 +++++-
mysql-test/r/range_innodb.result | 105 +++++++++++++++++++++++++++++++++
mysql-test/t/range_innodb.test | 93 +++++++++++++++++++++++++++++
sql/opt_range.cc | 19 +++++-
5 files changed, 243 insertions(+), 6 deletions(-)
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc
index b168a76..ebadb50 100644
--- a/mysql-test/include/index_merge1.inc
+++ b/mysql-test/include/index_merge1.inc
@@ -150,12 +150,22 @@ explain select * from t0 where
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
explain select * from t0 where
- ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
- ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
+ or
+ ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+ ((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ or
+ ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+ ((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index 5a23092..a3e9e4f 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -173,17 +173,29 @@ or
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
explain select * from t0 where
-((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
-((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
select * from t0 where key1 < 5 or key8 < 4 order by key1;
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
diff --git a/mysql-test/r/range_innodb.result b/mysql-test/r/range_innodb.result
index f2349f2..df111bc 100644
--- a/mysql-test/r/range_innodb.result
+++ b/mysql-test/r/range_innodb.result
@@ -108,3 +108,108 @@ DROP TABLE t0,t1;
SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
# End of 10.1 tests
+#
+# MDEV-27262: Index intersection with full scan over an index
+#
+CREATE TABLE t1 (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+p char(32) DEFAULT NULL,
+es tinyint(3) unsigned NOT NULL DEFAULT 0,
+er tinyint(3) unsigned NOT NULL DEFAULT 0,
+x mediumint(8) unsigned NOT NULL DEFAULT 0,
+PRIMARY KEY (id),
+INDEX es (es),
+INDEX x (x),
+INDEX er (er,x),
+INDEX p (p)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+insert into t1(es,er) select 0, 1 from seq_1_to_45;
+insert into t1(es,er) select 0, 2 from seq_1_to_49;
+insert into t1(es,er) select 0, 3 from seq_1_to_951;
+insert into t1(es,er) select 0, 3 from seq_1_to_1054;
+insert into t1(es,er) select 0, 6 from seq_1_to_25;
+insert into t1(es,er) select 0, 11 from seq_1_to_1;
+insert into t1(es,er) select 1, 1 from seq_1_to_45;
+insert into t1(es,er) select 1, 2 from seq_1_to_16;
+insert into t1(es,er) select 1, 3 from seq_1_to_511;
+insert into t1(es,er) select 1, 4 from seq_1_to_687;
+insert into t1(es,er) select 1, 6 from seq_1_to_50;
+insert into t1(es,er) select 1, 7 from seq_1_to_4;
+insert into t1(es,er) select 1, 11 from seq_1_to_1;
+insert into t1(es,er) select 2, 1 from seq_1_to_82;
+insert into t1(es,er) select 2, 2 from seq_1_to_82;
+insert into t1(es,er) select 2, 3 from seq_1_to_1626;
+insert into t1(es,er) select 2, 4 from seq_1_to_977;
+insert into t1(es,er) select 2, 6 from seq_1_to_33;
+insert into t1(es,er) select 2, 11 from seq_1_to_1;
+insert into t1(es,er) select 3, 1 from seq_1_to_245;
+insert into t1(es,er) select 3, 2 from seq_1_to_81;
+insert into t1(es,er) select 3, 3 from seq_1_to_852;
+insert into t1(es,er) select 3, 4 from seq_1_to_2243;
+insert into t1(es,er) select 3, 6 from seq_1_to_44;
+insert into t1(es,er) select 3, 11 from seq_1_to_1;
+insert into t1(es,er) select 4, 1 from seq_1_to_91;
+insert into t1(es,er) select 4, 2 from seq_1_to_83;
+insert into t1(es,er) select 4, 3 from seq_1_to_297;
+insert into t1(es,er) select 4, 4 from seq_1_to_2456;
+insert into t1(es,er) select 4, 6 from seq_1_to_19;
+insert into t1(es,er) select 4, 11 from seq_1_to_1;
+update t1 set p='foobar';
+update t1 set x=0;
+set @save_isp=@@innodb_stats_persistent;
+set global innodb_stats_persistent= 1;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+set optimizer_switch='index_merge_sort_intersection=on';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14645 foobar 4 4 0
+14646 foobar 4 4 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=off';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14645 foobar 4 4 0
+14646 foobar 4 4 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=on';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14007 foobar 4 2 0
+14008 foobar 4 2 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=off';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14007 foobar 4 2 0
+14008 foobar 4 2 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=default';
+set global innodb_stats_persistent= @save_isp;
+DROP TABLE t1;
+# End of 10.2 tests
diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test
index 428e5c2..b8a6a7c 100644
--- a/mysql-test/t/range_innodb.test
+++ b/mysql-test/t/range_innodb.test
@@ -116,3 +116,96 @@ SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
--echo # End of 10.1 tests
+
+--echo #
+--echo # MDEV-27262: Index intersection with full scan over an index
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (
+ id int(10) unsigned NOT NULL AUTO_INCREMENT,
+ p char(32) DEFAULT NULL,
+ es tinyint(3) unsigned NOT NULL DEFAULT 0,
+ er tinyint(3) unsigned NOT NULL DEFAULT 0,
+ x mediumint(8) unsigned NOT NULL DEFAULT 0,
+ PRIMARY KEY (id),
+ INDEX es (es),
+ INDEX x (x),
+ INDEX er (er,x),
+ INDEX p (p)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+insert into t1(es,er) select 0, 1 from seq_1_to_45;
+insert into t1(es,er) select 0, 2 from seq_1_to_49;
+insert into t1(es,er) select 0, 3 from seq_1_to_951;
+insert into t1(es,er) select 0, 3 from seq_1_to_1054;
+insert into t1(es,er) select 0, 6 from seq_1_to_25;
+insert into t1(es,er) select 0, 11 from seq_1_to_1;
+insert into t1(es,er) select 1, 1 from seq_1_to_45;
+insert into t1(es,er) select 1, 2 from seq_1_to_16;
+insert into t1(es,er) select 1, 3 from seq_1_to_511;
+insert into t1(es,er) select 1, 4 from seq_1_to_687;
+insert into t1(es,er) select 1, 6 from seq_1_to_50;
+insert into t1(es,er) select 1, 7 from seq_1_to_4;
+insert into t1(es,er) select 1, 11 from seq_1_to_1;
+insert into t1(es,er) select 2, 1 from seq_1_to_82;
+insert into t1(es,er) select 2, 2 from seq_1_to_82;
+insert into t1(es,er) select 2, 3 from seq_1_to_1626;
+insert into t1(es,er) select 2, 4 from seq_1_to_977;
+insert into t1(es,er) select 2, 6 from seq_1_to_33;
+insert into t1(es,er) select 2, 11 from seq_1_to_1;
+insert into t1(es,er) select 3, 1 from seq_1_to_245;
+insert into t1(es,er) select 3, 2 from seq_1_to_81;
+insert into t1(es,er) select 3, 3 from seq_1_to_852;
+insert into t1(es,er) select 3, 4 from seq_1_to_2243;
+insert into t1(es,er) select 3, 6 from seq_1_to_44;
+insert into t1(es,er) select 3, 11 from seq_1_to_1;
+insert into t1(es,er) select 4, 1 from seq_1_to_91;
+insert into t1(es,er) select 4, 2 from seq_1_to_83;
+insert into t1(es,er) select 4, 3 from seq_1_to_297;
+insert into t1(es,er) select 4, 4 from seq_1_to_2456;
+insert into t1(es,er) select 4, 6 from seq_1_to_19;
+insert into t1(es,er) select 4, 11 from seq_1_to_1;
+update t1 set p='foobar';
+update t1 set x=0;
+set @save_isp=@@innodb_stats_persistent;
+set global innodb_stats_persistent= 1;
+analyze table t1;
+
+let $q=
+SELECT * FROM t1
+ WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+
+set optimizer_switch='index_merge_sort_intersection=on';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=off';
+# execution of $q and explain for it led to an assertion failure in 10.4
+# (with the optimizer switch rowid_filter set to 'on')
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+let $q=
+SELECT * FROM t1
+ WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2;
+
+set optimizer_switch='index_merge_sort_intersection=on';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=off';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=default';
+
+set global innodb_stats_persistent= @save_isp;
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index f3f1843..89ee8cf 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9329,7 +9329,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
uint max_part_no= MY_MAX(key1->max_part_no, key2->max_part_no);
- for (key2=key2->first(); key2; )
+ for (key2=key2->first(); ; )
{
/*
key1 consists of one or more ranges. tmp is the range currently
@@ -9343,6 +9343,16 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
^
tmp
*/
+ if (key1->min_flag & NO_MIN_RANGE &&
+ key1->max_flag & NO_MAX_RANGE)
+ {
+ if (key1->maybe_flag)
+ return new SEL_ARG(SEL_ARG::MAYBE_KEY);
+ return 0; // Always true OR
+ }
+ if (!key2)
+ break;
+
SEL_ARG *tmp=key1->find_range(key2);
/*
@@ -9413,6 +9423,13 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
key2->copy_min(tmp);
if (!(key1=key1->tree_delete(tmp)))
{ // Only one key in tree
+ if (key2->min_flag & NO_MIN_RANGE &&
+ key2->max_flag & NO_MAX_RANGE)
+ {
+ if (key2->maybe_flag)
+ return new SEL_ARG(SEL_ARG::MAYBE_KEY);
+ return 0; // Always true OR
+ }
key1=key2;
key1->make_root();
key2=key2_next;
1
0
20 Dec '21
revision-id: 59af76ef3434a4beb21c7e7c52800b539be23b7f (mariadb-10.6.1-250-g59af76ef343)
parent(s): de4426358101575648e968b4cdae35527da74b23
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-20 23:51:55 +0300
message:
Reverse-ordered indexes: remove SEL_ARG::is_ascending
Get the information from the array of KEY_PART structures that describes
the [pseudo-]index that is being analyzed.
---
sql/item_geofunc.cc | 2 +-
sql/opt_range.cc | 76 +++++++++++++++++++++++++---------------------------
sql/opt_range.h | 55 +++++++++++++++++--------------------
sql/opt_range_mrr.cc | 27 ++++++++++---------
4 files changed, 77 insertions(+), 83 deletions(-)
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index a2a99bcdf8f..0fdcf9e94e2 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1084,7 +1084,7 @@ Item_func_spatial_rel::get_mm_leaf(RANGE_OPT_PARAM *param,
field->get_key_image(str, key_part->length, key_part->image_type);
SEL_ARG *tree;
- if (!(tree= new (param->mem_root) SEL_ARG(field, true, str, str)))
+ if (!(tree= new (param->mem_root) SEL_ARG(field, str, str)))
DBUG_RETURN(0); // out of memory
switch (type) {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ae2b5060625..bbb7b3d87fa 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1879,7 +1879,6 @@ SEL_ARG::SEL_ARG(SEL_ARG &arg) :Sql_alloc()
max_flag=arg.max_flag;
maybe_flag=arg.maybe_flag;
maybe_null=arg.maybe_null;
- is_ascending= arg.is_ascending;
part=arg.part;
field=arg.field;
min_value=arg.min_value;
@@ -1905,10 +1904,9 @@ inline void SEL_ARG::make_root()
use_count=0; elements=1;
}
-SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
+SEL_ARG::SEL_ARG(Field *f, const uchar *min_value_arg,
const uchar *max_value_arg)
:min_flag(0), max_flag(0), maybe_flag(0), maybe_null(f->real_maybe_null()),
- is_ascending(is_asc),
elements(1), use_count(1), field(f), min_value((uchar*) min_value_arg),
max_value((uchar*) max_value_arg), next(0),prev(0),
next_key_part(0), color(BLACK), type(KEY_RANGE), weight(1)
@@ -1917,11 +1915,11 @@ SEL_ARG::SEL_ARG(Field *f, bool is_asc, const uchar *min_value_arg,
max_part_no= 1;
}
-SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
+SEL_ARG::SEL_ARG(Field *field_,uint8 part_,
uchar *min_value_, uchar *max_value_,
uint8 min_flag_,uint8 max_flag_,uint8 maybe_flag_)
:min_flag(min_flag_),max_flag(max_flag_),maybe_flag(maybe_flag_),
- part(part_),maybe_null(field_->real_maybe_null()), is_ascending(is_asc_),
+ part(part_),maybe_null(field_->real_maybe_null()),
elements(1),use_count(1),
field(field_), min_value(min_value_), max_value(max_value_),
next(0),prev(0),next_key_part(0),color(BLACK),type(KEY_RANGE), weight(1)
@@ -1941,8 +1939,8 @@ SEL_ARG::SEL_ARG(Field *field_,uint8 part_, bool is_asc_,
class SEL_ARG_LE: public SEL_ARG
{
public:
- SEL_ARG_LE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_LE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
if (!field->real_maybe_null())
min_flag= NO_MIN_RANGE; // From start
@@ -1962,17 +1960,17 @@ class SEL_ARG_LT: public SEL_ARG_LE
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_LT(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG_LE(key, field, is_asc)
+ SEL_ARG_LT(const uchar *key, Field *field)
+ :SEL_ARG_LE(key, field)
{ max_flag= NEAR_MAX; }
/*
Use this constructor if value->save_in_field() returned success,
but we don't know if rounding or truncation happened
(as some Field::store() do not report minor data changes).
*/
- SEL_ARG_LT(THD *thd, const uchar *key, Field *field, bool is_asc,
+ SEL_ARG_LT(THD *thd, const uchar *key, Field *field,
Item *value)
- :SEL_ARG_LE(key, field, is_asc)
+ :SEL_ARG_LE(key, field)
{
if (stored_field_cmp_to_item(thd, field, value) == 0)
max_flag= NEAR_MAX;
@@ -1988,7 +1986,7 @@ class SEL_ARG_GT: public SEL_ARG
without any data rounding or truncation.
*/
SEL_ARG_GT(const uchar *key, const KEY_PART *key_part, Field *field)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if (!(key_part->flag & HA_PART_KEY_SEG))
@@ -2002,7 +2000,7 @@ class SEL_ARG_GT: public SEL_ARG
*/
SEL_ARG_GT(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2020,8 +2018,8 @@ class SEL_ARG_GE: public SEL_ARG
Use this constructor if value->save_in_field() went precisely,
without any data rounding or truncation.
*/
- SEL_ARG_GE(const uchar *key, Field *field, bool is_asc)
- :SEL_ARG(field, is_asc, key, key)
+ SEL_ARG_GE(const uchar *key, Field *field)
+ :SEL_ARG(field, key, key)
{
max_flag= NO_MAX_RANGE;
}
@@ -2032,7 +2030,7 @@ class SEL_ARG_GE: public SEL_ARG
*/
SEL_ARG_GE(THD *thd, const uchar *key,
const KEY_PART *key_part, Field *field, Item *value)
- :SEL_ARG(field, !(key_part->flag & HA_REVERSE_SORT), key, key)
+ :SEL_ARG(field, key, key)
{
// Don't use open ranges for partial key_segments
if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
@@ -2063,7 +2061,7 @@ SEL_ARG *SEL_ARG::clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent,
}
else
{
- if (!(tmp= new (param->mem_root) SEL_ARG(field, part, is_ascending,
+ if (!(tmp= new (param->mem_root) SEL_ARG(field, part,
min_value, max_value,
min_flag, max_flag, maybe_flag)))
return 0; // OOM
@@ -3244,6 +3242,7 @@ double records_in_column_ranges(PARAM *param, uint idx,
seq.keyno= idx;
seq.real_keyno= MAX_KEY;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
seq.is_ror_scan= FALSE;
@@ -8669,8 +8668,7 @@ Item_func_null_predicate::get_mm_leaf(RANGE_OPT_PARAM *param,
if (!field->real_maybe_null())
DBUG_RETURN(type == ISNULL_FUNC ? &null_element : NULL);
SEL_ARG *tree;
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- if (!(tree= new (alloc) SEL_ARG(field, is_asc, is_null_string, is_null_string)))
+ if (!(tree= new (alloc) SEL_ARG(field, is_null_string, is_null_string)))
DBUG_RETURN(0);
if (type == Item_func::ISNOTNULL_FUNC)
{
@@ -8770,8 +8768,7 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
int2store(min_str + maybe_null, min_length);
int2store(max_str + maybe_null, max_length);
}
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
- SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, is_asc, min_str, max_str);
+ SEL_ARG *tree= new (param->mem_root) SEL_ARG(field, min_str, max_str);
DBUG_RETURN(tree);
}
@@ -9019,19 +9016,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, is_asc, value));
+ DBUG_RETURN(new (mem_root) SEL_ARG_LT(thd, str, this, value));
case SCALAR_CMP_GT:
DBUG_RETURN(new (mem_root) SEL_ARG_GT(thd, str, key_part, this, value));
case SCALAR_CMP_GE:
DBUG_RETURN(new (mem_root) SEL_ARG_GE(thd, str, key_part, this, value));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -9049,19 +9045,18 @@ SEL_ARG *Field::stored_field_make_mm_leaf_exact(RANGE_OPT_PARAM *param,
if (!(str= make_key_image(param->mem_root, key_part)))
DBUG_RETURN(0);
- bool is_asc= !(key_part->flag & HA_REVERSE_SORT);
switch (op) {
case SCALAR_CMP_LE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LE(str, this));
case SCALAR_CMP_LT:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_LT(str, this));
case SCALAR_CMP_GT:
DBUG_RETURN(new (param->mem_root) SEL_ARG_GT(str, key_part, this));
case SCALAR_CMP_GE:
- DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this, is_asc));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG_GE(str, this));
case SCALAR_CMP_EQ:
case SCALAR_CMP_EQUAL:
- DBUG_RETURN(new (param->mem_root) SEL_ARG(this, is_asc, str, str));
+ DBUG_RETURN(new (param->mem_root) SEL_ARG(this, str, str));
break;
}
DBUG_ASSERT(0);
@@ -11531,6 +11526,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
seq.keyno= idx;
seq.real_keyno= keynr;
+ seq.key_parts= param->key[idx];
seq.param= param;
seq.start= tree;
@@ -11785,9 +11781,9 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
int *min_part, int *max_part)
{
DBUG_ASSERT(next_key_part);
- bool asc = next_key_part->is_ascending;
+ const bool asc = !(key[next_key_part->part].flag & HA_REVERSE_SORT);
- if (!get_min_flag())
+ if (!get_min_flag(key))
{
if (asc)
{
@@ -11802,7 +11798,7 @@ void SEL_ARG::store_next_min_max_keys(KEY_PART *key,
*cur_min_flag = invert_max_flag(tmp_flag);
}
}
- if (!get_max_flag())
+ if (!get_max_flag(key))
{
if (asc)
{
@@ -11832,7 +11828,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
int min_part= key_tree->part-1, // # of keypart values in min_key buffer
max_part= key_tree->part-1; // # of keypart values in max_key buffer
- SEL_ARG *next_tree = key_tree->is_ascending ? key_tree->left : key_tree->right;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
+ SEL_ARG *next_tree = asc ? key_tree->left : key_tree->right;
if (next_tree != &null_element)
{
if (get_quick_keys(param,quick,key,next_tree,
@@ -11841,7 +11838,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
uchar *tmp_min_key=min_key,*tmp_max_key=max_key;
- key_tree->store_min_max(key[key_tree->part].store_length,
+ key_tree->store_min_max(key, key[key_tree->part].store_length,
&tmp_min_key, min_key_flag,
&tmp_max_key, max_key_flag,
&min_part, &max_part);
@@ -11864,8 +11861,8 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
goto end; // Ugly, but efficient
}
{
- uint tmp_min_flag= key_tree->get_min_flag();
- uint tmp_max_flag= key_tree->get_max_flag();
+ uint tmp_min_flag= key_tree->get_min_flag(key);
+ uint tmp_max_flag= key_tree->get_max_flag(key);
key_tree->store_next_min_max_keys(key,
&tmp_min_key, &tmp_min_flag,
@@ -11876,7 +11873,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
}
else
{
- if (key_tree->is_ascending)
+ if (asc)
{
flag= (key_tree->min_flag & GEOM_FLAG) ? key_tree->min_flag:
(key_tree->min_flag |
@@ -11948,7 +11945,7 @@ get_quick_keys(PARAM *param,QUICK_RANGE_SELECT *quick,KEY_PART *key,
return 1;
end:
- next_tree = key_tree->is_ascending ? key_tree->right : key_tree->left;
+ next_tree= asc ? key_tree->right : key_tree->left;
if (next_tree != &null_element)
return get_quick_keys(param,quick,key,next_tree,
min_key,min_key_flag,
@@ -16559,6 +16556,7 @@ static void trace_ranges(Json_writer_array *range_trace,
uint n_key_parts= param->table->actual_n_key_parts(keyinfo);
DBUG_ASSERT(range_trace->trace_started());
seq.keyno= idx;
+ seq.key_parts= param->key[idx];
seq.real_keyno= param->real_keynr[idx];
seq.param= param;
seq.start= keypart;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6864a5c583a..f3ccd4d8311 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -306,11 +306,6 @@ class SEL_ARG :public Sql_alloc
uint8 min_flag,max_flag,maybe_flag;
uint8 part; // Which key part
uint8 maybe_null;
- /*
- Whether the keypart is ascending or descending.
- See HowRangeOptimizerHandlesDescKeyparts for details.
- */
- uint8 is_ascending;
/*
The ordinal number the least significant component encountered in
the ranges of the SEL_ARG tree (the first component has number 1)
@@ -361,14 +356,14 @@ class SEL_ARG :public Sql_alloc
SEL_ARG() {}
SEL_ARG(SEL_ARG &);
- SEL_ARG(Field *, bool is_asc, const uchar *, const uchar *);
- SEL_ARG(Field *field, uint8 part, bool is_asc,
+ SEL_ARG(Field *, const uchar *, const uchar *);
+ SEL_ARG(Field *field, uint8 part,
uchar *min_value, uchar *max_value,
uint8 min_flag, uint8 max_flag, uint8 maybe_flag);
/* This is used to construct degenerate SEL_ARGS like ALWAYS, IMPOSSIBLE, etc */
SEL_ARG(enum Type type_arg)
- :min_flag(0), is_ascending(false),
+ :min_flag(0),
max_part_no(0) /* first key part means 1. 0 mean 'no parts'*/,
elements(1),use_count(1),left(0),right(0),
next_key_part(0), color(BLACK), type(type_arg), weight(1)
@@ -447,20 +442,20 @@ class SEL_ARG :public Sql_alloc
{
new_max=arg->max_value; flag_max=arg->max_flag;
}
- return new (thd->mem_root) SEL_ARG(field, part, is_ascending,
+ return new (thd->mem_root) SEL_ARG(field, part,
new_min, new_max, flag_min,
flag_max,
MY_TEST(maybe_flag && arg->maybe_flag));
}
SEL_ARG *clone_first(SEL_ARG *arg)
{ // min <= X < arg->min
- return new SEL_ARG(field, part, is_ascending, min_value, arg->min_value,
+ return new SEL_ARG(field, part, min_value, arg->min_value,
min_flag, arg->min_flag & NEAR_MIN ? 0 : NEAR_MAX,
maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone_last(SEL_ARG *arg)
{ // min <= X <= key_max
- return new SEL_ARG(field, part, is_ascending, min_value, arg->max_value,
+ return new SEL_ARG(field, part, min_value, arg->max_value,
min_flag, arg->max_flag, maybe_flag | arg->maybe_flag);
}
SEL_ARG *clone(RANGE_OPT_PARAM *param, SEL_ARG *new_parent, SEL_ARG **next);
@@ -544,44 +539,45 @@ class SEL_ARG :public Sql_alloc
}
/* Save minimum and maximum, taking index order into account */
- void store_min_max(uint length,
+ void store_min_max(KEY_PART *kp,
+ uint length,
uchar **min_key, uint min_flag,
uchar **max_key, uint max_flag,
int *min_part, int *max_part)
{
- if (is_ascending) {
- *min_part += store_min(length, min_key, min_flag);
- *max_part += store_max(length, max_key, max_flag);
- } else {
+ if (kp[part].flag & HA_REVERSE_SORT) {
*max_part += store_min(length, max_key, min_flag);
*min_part += store_max(length, min_key, max_flag);
+ } else {
+ *min_part += store_min(length, min_key, min_flag);
+ *max_part += store_max(length, max_key, max_flag);
}
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_min_flag()
+ uint get_min_flag(KEY_PART *kp)
{
- return (is_ascending ? min_flag : invert_max_flag(max_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_max_flag(max_flag) : min_flag;
}
/*
Get the flag for range's starting endpoint, taking index order into
account.
*/
- uint get_max_flag()
+ uint get_max_flag(KEY_PART *kp)
{
- return (is_ascending ? max_flag : invert_min_flag(min_flag));
+ return (kp[part].flag & HA_REVERSE_SORT)? invert_min_flag(min_flag) : max_flag ;
}
/* Get the previous interval, taking index order into account */
- inline SEL_ARG* index_order_prev()
+ inline SEL_ARG* index_order_prev(KEY_PART *kp)
{
- return is_ascending? prev: next;
+ return (kp[part].flag & HA_REVERSE_SORT)? next : prev;
}
/* Get the next interval, taking index order into account */
- inline SEL_ARG* index_order_next()
+ inline SEL_ARG* index_order_next(KEY_PART *kp)
{
- return is_ascending? next: prev;
+ return (kp[part].flag & HA_REVERSE_SORT)? prev : next;
}
/*
@@ -621,7 +617,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MIN_RANGE | NEAR_MIN)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if (start_key == asc)
{
res+= nkp->store_min_key(key, range_key, range_key_flag, last_part,
@@ -657,7 +653,7 @@ class SEL_ARG :public Sql_alloc
nkp->part == key_tree->part+1 &&
!(*range_key_flag & (NO_MAX_RANGE | NEAR_MAX)))
{
- const bool asc = nkp->is_ascending;
+ const bool asc = !(key[key_tree->part].flag & HA_REVERSE_SORT);
if ((!start_key && asc) || (start_key && !asc))
{
res += nkp->store_max_key(key, range_key, range_key_flag, last_part,
@@ -785,9 +781,6 @@ class SEL_ARG :public Sql_alloc
Range Optimizer handles this as follows:
- The SEL_ARG object has SEL_ARG::is_ascending which specifies whether the
- keypart is ascending.
-
Other than that, the SEL_ARG graph is built without any regard to DESC
keyparts.
@@ -799,7 +792,7 @@ class SEL_ARG :public Sql_alloc
kp1 BETWEEN 10 and 20 (RANGE-1)
- the SEL_ARG will have min_value=10, max_value=20, is_ascending=false.
+ the SEL_ARG will have min_value=10, max_value=20
The ordering of key parts is taken into account when SEL_ARG graph is
linearized to ranges, in sel_arg_range_seq_next() and get_quick_keys().
@@ -850,7 +843,7 @@ class SEL_ARG_IMPOSSIBLE: public SEL_ARG
{
public:
SEL_ARG_IMPOSSIBLE(Field *field)
- :SEL_ARG(field, false, 0, 0)
+ :SEL_ARG(field, 0, 0)
{
type= SEL_ARG::IMPOSSIBLE;
}
diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc
index 8877e15d5b5..452a6864f06 100644
--- a/sql/opt_range_mrr.cc
+++ b/sql/opt_range_mrr.cc
@@ -47,6 +47,7 @@ typedef struct st_sel_arg_range_seq
uint keyno; /* index of used tree in SEL_TREE structure */
uint real_keyno; /* Number of the index in tables */
PARAM *param;
+ KEY_PART *key_parts;
SEL_ARG *start; /* Root node of the traversed SEL_ARG* graph */
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
@@ -106,13 +107,13 @@ static void step_down_to(SEL_ARG_RANGE_SEQ *arg, SEL_ARG *key_tree)
uint16 stor_length= arg->param->key[arg->keyno][key_tree->part].store_length;
- key_tree->store_min_max(stor_length,
+ key_tree->store_min_max(arg->key_parts, stor_length,
&cur->min_key, prev->min_key_flag,
&cur->max_key, prev->max_key_flag,
&cur->min_key_parts, &cur->max_key_parts);
- cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag();
- cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag();
+ cur->min_key_flag= prev->min_key_flag | key_tree->get_min_flag(arg->key_parts);
+ cur->max_key_flag= prev->max_key_flag | key_tree->get_max_flag(arg->key_parts);
if (key_tree->is_null_interval())
cur->min_key_flag |= NULL_RANGE;
@@ -166,12 +167,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
/* Ok, we're at some "full tuple" position in the tree */
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
//step down; (update the tuple, we'll step right and stay there)
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
seq->is_ror_scan= FALSE;
goto walk_right_n_up;
}
@@ -186,12 +188,13 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= seq->stack[seq->i].key_tree;
/* Step down if we can */
- if (key_tree->index_order_next() && key_tree->index_order_next() != &null_element)
+ if (key_tree->index_order_next(seq->key_parts) &&
+ key_tree->index_order_next(seq->key_parts) != &null_element)
{
// Step down; update the tuple
seq->i--;
- step_down_to(seq, key_tree->index_order_next());
- key_tree= key_tree->index_order_next();
+ step_down_to(seq, key_tree->index_order_next(seq->key_parts));
+ key_tree= key_tree->index_order_next(seq->key_parts);
break;
}
}
@@ -230,11 +233,11 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range)
key_tree= key_tree->next_key_part;
walk_up_n_right:
- while (key_tree->index_order_prev() &&
- key_tree->index_order_prev() != &null_element)
+ while (key_tree->index_order_prev(seq->key_parts) &&
+ key_tree->index_order_prev(seq->key_parts) != &null_element)
{
/* Step up */
- key_tree= key_tree->index_order_prev();
+ key_tree= key_tree->index_order_prev(seq->key_parts);
}
step_down_to(seq, key_tree);
}
1
0
[Commits] 677643a: MDEV-27262 Unexpected index intersection with full index scan for an index
by IgorBabaev 20 Dec '21
by IgorBabaev 20 Dec '21
20 Dec '21
revision-id: 677643a80986107491b7886441f2828384f0494b (mariadb-10.2.31-1286-g677643a)
parent(s): 8bb55633699612279744c055e22eeca8d4058273
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-12-17 14:11:39 -0800
message:
MDEV-27262 Unexpected index intersection with full index scan for an index
If when extracting a range condition foran index from the WHERE condition
Range Optimizer sees that the range condition covers the whole index then
such condition should be discarded because cannot it be used in any range
scan. In some cases Range Optimizer really does it, but there remained some
conditions for which it was not done. As a result the optimizer could
produce index merge plans with the full index scan for one of the indexes
participating in the index merge.
This could be observed in one of the test cases from index_merge1.inc
where a plan with index_merge_sort_union was produced and in the test case
reported for this bug where a plan with index_merge_sort_intersect was
produced. In both cases one of two index scans participating in index merge
ran over the whole index.
The patch slightly changes the original above mentioned test case from
index_merge1.inc to be able to produce an intended plan employing
index_merge_sort_union. The original query was left to show that index
merge is not used for it anymore.
It should be noted that for the plan with index_merge_sort_intersect could
be chosen for execution only due to a defect in the InnoDB code that
returns wrong estimates for the cardinality of big ranges.
This bug led to serious problems in 10.4+ where the optimization using
Rowid filters is employed (see mdev-26446).
Approved by Oleksandr Byelkin <sanja(a)mariadb.com>
---
mysql-test/include/index_merge1.inc | 8 +++-
mysql-test/r/index_merge_myisam.result | 12 +++--
mysql-test/r/range_innodb.result | 81 ++++++++++++++++++++++++++++++++++
mysql-test/t/range_innodb.test | 78 ++++++++++++++++++++++++++++++++
sql/opt_range.cc | 7 +++
5 files changed, 181 insertions(+), 5 deletions(-)
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc
index b168a76..440f1f7 100644
--- a/mysql-test/include/index_merge1.inc
+++ b/mysql-test/include/index_merge1.inc
@@ -150,15 +150,19 @@ explain select * from t0 where
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
explain select * from t0 where
- ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
- ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ ((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+ ((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4))
+ or
+ ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
# 8. Verify that "order by" after index merge uses filesort
select * from t0 where key1 < 5 or key8 < 4 order by key1;
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index 5a23092..a096c34 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -173,17 +173,23 @@ or
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
explain select * from t0 where
-((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
-((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
+((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4))
or
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where
+explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
+((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4))
+or
+((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
select * from t0 where key1 < 5 or key8 < 4 order by key1;
key1 key2 key3 key4 key5 key6 key7 key8
1 1 1 1 1 1 1 1023
diff --git a/mysql-test/r/range_innodb.result b/mysql-test/r/range_innodb.result
index f2349f2..ccb6da3 100644
--- a/mysql-test/r/range_innodb.result
+++ b/mysql-test/r/range_innodb.result
@@ -108,3 +108,84 @@ DROP TABLE t0,t1;
SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
# End of 10.1 tests
+#
+# MDEV-27262: Index intersection with full scan over an index
+#
+CREATE TABLE t1 (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+p char(32) DEFAULT NULL,
+es tinyint(3) unsigned NOT NULL DEFAULT 0,
+er tinyint(3) unsigned NOT NULL DEFAULT 0,
+x mediumint(8) unsigned NOT NULL DEFAULT 0,
+PRIMARY KEY (id),
+INDEX es (es),
+INDEX x (x),
+INDEX er (er,x),
+INDEX p (p)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+insert into t1(es,er) select 0, 1 from seq_1_to_45;
+insert into t1(es,er) select 0, 2 from seq_1_to_49;
+insert into t1(es,er) select 0, 3 from seq_1_to_951;
+insert into t1(es,er) select 0, 3 from seq_1_to_1054;
+insert into t1(es,er) select 0, 6 from seq_1_to_25;
+insert into t1(es,er) select 0, 11 from seq_1_to_1;
+insert into t1(es,er) select 1, 1 from seq_1_to_45;
+insert into t1(es,er) select 1, 2 from seq_1_to_16;
+insert into t1(es,er) select 1, 3 from seq_1_to_511;
+insert into t1(es,er) select 1, 4 from seq_1_to_687;
+insert into t1(es,er) select 1, 6 from seq_1_to_50;
+insert into t1(es,er) select 1, 7 from seq_1_to_4;
+insert into t1(es,er) select 1, 11 from seq_1_to_1;
+insert into t1(es,er) select 2, 1 from seq_1_to_82;
+insert into t1(es,er) select 2, 2 from seq_1_to_82;
+insert into t1(es,er) select 2, 3 from seq_1_to_1626;
+insert into t1(es,er) select 2, 4 from seq_1_to_977;
+insert into t1(es,er) select 2, 6 from seq_1_to_33;
+insert into t1(es,er) select 2, 11 from seq_1_to_1;
+insert into t1(es,er) select 3, 1 from seq_1_to_245;
+insert into t1(es,er) select 3, 2 from seq_1_to_81;
+insert into t1(es,er) select 3, 3 from seq_1_to_852;
+insert into t1(es,er) select 3, 4 from seq_1_to_2243;
+insert into t1(es,er) select 3, 6 from seq_1_to_44;
+insert into t1(es,er) select 3, 11 from seq_1_to_1;
+insert into t1(es,er) select 4, 1 from seq_1_to_91;
+insert into t1(es,er) select 4, 2 from seq_1_to_83;
+insert into t1(es,er) select 4, 3 from seq_1_to_297;
+insert into t1(es,er) select 4, 4 from seq_1_to_2456;
+insert into t1(es,er) select 4, 6 from seq_1_to_19;
+insert into t1(es,er) select 4, 11 from seq_1_to_1;
+update t1 set p='foobar';
+update t1 set x=0;
+set @save_isp=@@innodb_stats_persistent;
+set global innodb_stats_persistent= 1;
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+set optimizer_switch='index_merge_sort_intersection=on';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14645 foobar 4 4 0
+14646 foobar 4 4 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=off';
+SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id p es er x
+14645 foobar 4 4 0
+14646 foobar 4 4 0
+EXPLAIN EXTENDED SELECT * FROM t1
+WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where
+Warnings:
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2
+set optimizer_switch='index_merge_sort_intersection=default';
+set global innodb_stats_persistent= @save_isp;
+DROP TABLE t1;
+# End of 10.2 tests
diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test
index 428e5c2..7420e72 100644
--- a/mysql-test/t/range_innodb.test
+++ b/mysql-test/t/range_innodb.test
@@ -116,3 +116,81 @@ SET @@GLOBAL.debug_dbug = @saved_dbug;
set @@optimizer_switch= @optimizer_switch_save;
--echo # End of 10.1 tests
+
+--echo #
+--echo # MDEV-27262: Index intersection with full scan over an index
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (
+ id int(10) unsigned NOT NULL AUTO_INCREMENT,
+ p char(32) DEFAULT NULL,
+ es tinyint(3) unsigned NOT NULL DEFAULT 0,
+ er tinyint(3) unsigned NOT NULL DEFAULT 0,
+ x mediumint(8) unsigned NOT NULL DEFAULT 0,
+ PRIMARY KEY (id),
+ INDEX es (es),
+ INDEX x (x),
+ INDEX er (er,x),
+ INDEX p (p)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+insert into t1(es,er) select 0, 1 from seq_1_to_45;
+insert into t1(es,er) select 0, 2 from seq_1_to_49;
+insert into t1(es,er) select 0, 3 from seq_1_to_951;
+insert into t1(es,er) select 0, 3 from seq_1_to_1054;
+insert into t1(es,er) select 0, 6 from seq_1_to_25;
+insert into t1(es,er) select 0, 11 from seq_1_to_1;
+insert into t1(es,er) select 1, 1 from seq_1_to_45;
+insert into t1(es,er) select 1, 2 from seq_1_to_16;
+insert into t1(es,er) select 1, 3 from seq_1_to_511;
+insert into t1(es,er) select 1, 4 from seq_1_to_687;
+insert into t1(es,er) select 1, 6 from seq_1_to_50;
+insert into t1(es,er) select 1, 7 from seq_1_to_4;
+insert into t1(es,er) select 1, 11 from seq_1_to_1;
+insert into t1(es,er) select 2, 1 from seq_1_to_82;
+insert into t1(es,er) select 2, 2 from seq_1_to_82;
+insert into t1(es,er) select 2, 3 from seq_1_to_1626;
+insert into t1(es,er) select 2, 4 from seq_1_to_977;
+insert into t1(es,er) select 2, 6 from seq_1_to_33;
+insert into t1(es,er) select 2, 11 from seq_1_to_1;
+insert into t1(es,er) select 3, 1 from seq_1_to_245;
+insert into t1(es,er) select 3, 2 from seq_1_to_81;
+insert into t1(es,er) select 3, 3 from seq_1_to_852;
+insert into t1(es,er) select 3, 4 from seq_1_to_2243;
+insert into t1(es,er) select 3, 6 from seq_1_to_44;
+insert into t1(es,er) select 3, 11 from seq_1_to_1;
+insert into t1(es,er) select 4, 1 from seq_1_to_91;
+insert into t1(es,er) select 4, 2 from seq_1_to_83;
+insert into t1(es,er) select 4, 3 from seq_1_to_297;
+insert into t1(es,er) select 4, 4 from seq_1_to_2456;
+insert into t1(es,er) select 4, 6 from seq_1_to_19;
+insert into t1(es,er) select 4, 11 from seq_1_to_1;
+update t1 set p='foobar';
+update t1 set x=0;
+set @save_isp=@@innodb_stats_persistent;
+set global innodb_stats_persistent= 1;
+analyze table t1;
+
+let $q=
+SELECT * FROM t1
+ WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2;
+
+set optimizer_switch='index_merge_sort_intersection=on';
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+
+set optimizer_switch='index_merge_sort_intersection=off';
+# execution of $q and explain for it led to an assertion failure in 10.4
+# (with the optimizer switch rowid_filter set to 'on')
+eval $q;
+--replace_column 9 #
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch='index_merge_sort_intersection=default';
+
+set global innodb_stats_persistent= @save_isp;
+DROP TABLE t1;
+
+--echo # End of 10.2 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index f3f1843..2e05b88 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9413,6 +9413,13 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
key2->copy_min(tmp);
if (!(key1=key1->tree_delete(tmp)))
{ // Only one key in tree
+ if (key2->min_flag & NO_MIN_RANGE &&
+ key2->max_flag & NO_MAX_RANGE)
+ {
+ if (key2->maybe_flag)
+ return new SEL_ARG(SEL_ARG::MAYBE_KEY);
+ return 0; // Always true OR
+ }
key1=key2;
key1->make_root();
key2=key2_next;
2
1
[Commits] 32692140e1a: MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
by psergey 19 Dec '21
by psergey 19 Dec '21
19 Dec '21
revision-id: 32692140e1a4f9aa87359f3ef6efe4a615119e71 (mariadb-10.6.5-39-g32692140e1a)
parent(s): 946dafb260fc5683e1ec1410a801f2235ba2313a
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-19 17:19:02 +0300
message:
MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
In mysql_execute_command(), move optimizer trace initialization to be
after run_set_statement_if_requested() call.
Unfortunately, mysql_execute_command() code uses "goto error" a lot, and
this means optimizer trace code cannot use RAII objects. Work this around
by:
- Make Opt_trace_start a non-RAII object, add init() method.
- Move the code that writes the top-level object and array into
Opt_trace_start::init().
---
mysql-test/main/opt_trace.result | 17 +++++++++++++++++
mysql-test/main/opt_trace.test | 8 ++++++++
sql/opt_trace.cc | 20 ++++++++++++++------
sql/opt_trace.h | 18 +++++++++++-------
sql/sp_head.cc | 7 +++----
sql/sql_parse.cc | 11 +++++------
sql/sql_prepare.cc | 6 +++---
7 files changed, 61 insertions(+), 26 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index b0c2a9ca4d9..477c1f31095 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -9248,5 +9248,22 @@ json_detailed(json_extract(trace, '$**.best_join_order'))
]
]
DROP TABLE t1;
+#
+# MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
+#
+set optimizer_trace=0;
+set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2;
+seq
+1
+# The trace must not be empty:
+select left(trace, 100) from information_schema.optimizer_trace;
+left(trace, 100)
+{
+ "steps": [
+ {
+ "join_preparation": {
+ "select_id": 1,
+ "steps": [
+
# End of 10.6 tests
set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 402caf2a165..9a7aa017cd4 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -865,5 +865,13 @@ select json_detailed(json_extract(trace, '$**.best_join_order'))
from information_schema.OPTIMIZER_TRACE;
DROP TABLE t1;
+--echo #
+--echo # MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
+--echo #
+set optimizer_trace=0;
+set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2;
+--echo # The trace must not be empty:
+select left(trace, 100) from information_schema.optimizer_trace;
+
--echo # End of 10.6 tests
set optimizer_trace='enabled=off';
diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc
index ba9220cac44..4bc493940fb 100644
--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -471,12 +471,14 @@ void Opt_trace_context::end()
current_trace= NULL;
}
-Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl,
- enum enum_sql_command sql_command,
- List<set_var_base> *set_vars,
- const char *query,
- size_t query_length,
- const CHARSET_INFO *query_charset):ctx(&thd->opt_trace)
+
+void Opt_trace_start::init(THD *thd,
+ TABLE_LIST *tbl,
+ enum enum_sql_command sql_command,
+ List<set_var_base> *set_vars,
+ const char *query,
+ size_t query_length,
+ const CHARSET_INFO *query_charset)
{
/*
if optimizer trace is enabled and the statment we have is traceable,
@@ -496,6 +498,9 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl,
ctx->set_query(query, query_length, query_charset);
traceable= TRUE;
opt_trace_disable_if_no_tables_access(thd, tbl);
+ Json_writer *w= ctx->get_current_json();
+ w->start_object();
+ w->add_member("steps").start_array();
}
}
@@ -503,6 +508,9 @@ Opt_trace_start::~Opt_trace_start()
{
if (traceable)
{
+ Json_writer *w= ctx->get_current_json();
+ w->end_array();
+ w->end_object();
ctx->end();
traceable= FALSE;
}
diff --git a/sql/opt_trace.h b/sql/opt_trace.h
index 101fb5f707e..1ee23a33591 100644
--- a/sql/opt_trace.h
+++ b/sql/opt_trace.h
@@ -72,14 +72,18 @@ struct Opt_trace_info
*/
-class Opt_trace_start {
+class Opt_trace_start
+{
public:
- Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl,
- enum enum_sql_command sql_command,
- List<set_var_base> *set_vars,
- const char *query,
- size_t query_length,
- const CHARSET_INFO *query_charset);
+ Opt_trace_start(THD *thd_arg): ctx(&thd_arg->opt_trace), traceable(false) {}
+
+ void init(THD *thd, TABLE_LIST *tbl,
+ enum enum_sql_command sql_command,
+ List<set_var_base> *set_vars,
+ const char *query,
+ size_t query_length,
+ const CHARSET_INFO *query_charset);
+
~Opt_trace_start();
private:
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 641c88de826..97905f6a5b4 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -3489,10 +3489,9 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
thd->lex->safe_to_cache_query= 0;
#endif
- Opt_trace_start ots(thd, m_lex->query_tables,
- SQLCOM_SELECT, &m_lex->var_list,
- NULL, 0,
- thd->variables.character_set_client);
+ Opt_trace_start ots(thd);
+ ots.init(thd, m_lex->query_tables, SQLCOM_SELECT, &m_lex->var_list,
+ NULL, 0, thd->variables.character_set_client);
Json_writer_object trace_command(thd);
Json_writer_array trace_command_steps(thd, "steps");
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 2a48c8fb1ce..0886fc85151 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3644,12 +3644,7 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt)
#ifdef HAVE_REPLICATION
} /* endif unlikely slave */
#endif
- Opt_trace_start ots(thd, all_tables, lex->sql_command, &lex->var_list,
- thd->query(), thd->query_length(),
- thd->variables.character_set_client);
-
- Json_writer_object trace_command(thd);
- Json_writer_array trace_command_steps(thd, "steps");
+ Opt_trace_start ots(thd);
/* store old value of binlog format */
enum_binlog_format orig_binlog_format,orig_current_stmt_binlog_format;
@@ -3715,6 +3710,10 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt)
if (run_set_statement_if_requested(thd, lex))
goto error;
+ /* After SET STATEMENT is done, we can initialize the Optimizer Trace: */
+ ots.init(thd, all_tables, lex->sql_command, &lex->var_list, thd->query(),
+ thd->query_length(), thd->variables.character_set_client);
+
if (thd->lex->mi.connection_name.str == NULL)
thd->lex->mi.connection_name= thd->variables.default_master_connection;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 4ead77c225f..67032142591 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2437,9 +2437,9 @@ static bool check_prepared_statement(Prepared_statement *stmt)
For the optimizer trace, this is the symmetric, for statement preparation,
of what is done at statement execution (in mysql_execute_command()).
*/
- Opt_trace_start ots(thd, tables, lex->sql_command, &lex->var_list,
- thd->query(), thd->query_length(),
- thd->variables.character_set_client);
+ Opt_trace_start ots(thd);
+ ots.init(thd, tables, lex->sql_command, &lex->var_list, thd->query(),
+ thd->query_length(), thd->variables.character_set_client);
Json_writer_object trace_command(thd);
Json_writer_array trace_command_steps(thd, "steps");
1
0
[Commits] c2feba8ecc8: MDEV-27188: Suppress optimizer output when executing prepare
by psergey 17 Dec '21
by psergey 17 Dec '21
17 Dec '21
revision-id: c2feba8ecc8e8ab167efb4f4298469f345c359fb (mariadb-10.7.1-3-gc2feba8ecc8)
parent(s): 06988bdcaa2d1af2c178c199b7f65dbafda45a2c
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-17 20:29:41 +0300
message:
MDEV-27188: Suppress optimizer output when executing prepare
- Do not write anything into Optimizer Trace at Prepare phase
- When the query gets an error at Prepare phase, make sure there
is no trace written, either. This is important as we need to
produce the same trace for "mtr --ps-protocol" and regular mtr run.
- For other kinds of errors, trace is still produced as it might be
valuable.
---
mysql-test/main/opt_trace,ps.rdiff | 92 ---
mysql-test/main/opt_trace.result | 621 +++------------------
mysql-test/main/opt_trace_index_merge.result | 11 +-
.../main/opt_trace_index_merge_innodb.result | 11 +-
mysql-test/main/opt_trace_security.result | 38 +-
sql/my_json_writer.h | 8 +
sql/opt_subselect.cc | 13 +-
sql/opt_trace.cc | 60 ++
sql/opt_trace.h | 37 +-
sql/opt_trace_context.h | 6 +
sql/sql_derived.cc | 30 +-
sql/sql_parse.cc | 4 +
sql/sql_prepare.cc | 11 -
sql/sql_select.cc | 17 +-
14 files changed, 235 insertions(+), 724 deletions(-)
diff --git a/mysql-test/main/opt_trace,ps.rdiff b/mysql-test/main/opt_trace,ps.rdiff
deleted file mode 100644
index 3e2218de673..00000000000
--- a/mysql-test/main/opt_trace,ps.rdiff
+++ /dev/null
@@ -1,92 +0,0 @@
---- /Users/shulga/projects/mariadb/server-10.6/mysql-test/main/opt_trace.result 2021-07-21 19:17:11.000000000 +0700
-+++ /Users/shulga/projects/mariadb/server-10.6/mysql-test/main/opt_trace.reject 2021-07-21 19:17:48.000000000 +0700
-@@ -2829,14 +2829,6 @@
- }
- },
- {
-- "transformation": {
-- "select_id": 2,
-- "from": "IN (SELECT)",
-- "to": "semijoin",
-- "chosen": true
-- }
-- },
-- {
- "expanded_query": "/* select#2 */ select t10.pk from t10"
- }
- ]
-@@ -4402,14 +4394,6 @@
- }
- },
- {
-- "transformation": {
-- "select_id": 2,
-- "from": "IN (SELECT)",
-- "to": "semijoin",
-- "chosen": true
-- }
-- },
-- {
- "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2"
- }
- ]
-@@ -4852,14 +4836,6 @@
- }
- },
- {
-- "transformation": {
-- "select_id": 2,
-- "from": "IN (SELECT)",
-- "to": "semijoin",
-- "chosen": true
-- }
-- },
-- {
- "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
- }
- ]
-@@ -4879,14 +4855,6 @@
- }
- },
- {
-- "transformation": {
-- "select_id": 3,
-- "from": "IN (SELECT)",
-- "to": "semijoin",
-- "chosen": true
-- }
-- },
-- {
- "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
- }
- ]
-@@ -6432,14 +6400,6 @@
- }
- },
- {
-- "transformation": {
-- "select_id": 2,
-- "from": "IN (SELECT)",
-- "to": "semijoin",
-- "chosen": true
-- }
-- },
-- {
- "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
- }
- ]
-@@ -6459,14 +6419,6 @@
- }
- },
- {
-- "transformation": {
-- "select_id": 3,
-- "from": "IN (SELECT)",
-- "to": "semijoin",
-- "chosen": true
-- }
-- },
-- {
- "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
- }
- ]
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index b0c2a9ca4d9..18ef904633b 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -38,36 +38,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select * from v1 {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1",
"steps": [
{
"view": {
"table": "v1",
"select_id": 2,
- "algorithm": "merged"
+ "algorithm": "merged",
+ "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1"
}
},
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1"
- }
- ]
- }
- },
- {
- "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"condition_processing": {
"condition": "WHERE",
@@ -189,36 +171,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select * from (select * from t1 where t1.a=1)q {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q",
"steps": [
{
"derived": {
"table": "q",
"select_id": 2,
- "algorithm": "merged"
- }
- },
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1"
- }
- ]
+ "algorithm": "merged",
+ "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1"
}
},
- {
- "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"condition_processing": {
"condition": "WHERE",
@@ -340,40 +304,15 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select * from v2 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "view": {
- "table": "v2",
- "select_id": 2,
- "algorithm": "materialized"
- }
- },
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b"
- }
- ]
- }
- },
- {
- "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2",
"steps": [
{
"join_optimization": {
"select_id": 2,
+ "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b",
"steps": [
{
"condition_processing": {
@@ -590,36 +529,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from v2 {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t2.a AS a from v2",
"steps": [
{
"view": {
"table": "v2",
"select_id": 2,
- "algorithm": "merged"
+ "algorithm": "merged",
+ "expanded_query": "/* select#2 */ select t2.a AS a from t2"
}
},
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select t2.a AS a from t2"
- }
- ]
- }
- },
- {
- "expanded_query": "/* select#1 */ select t2.a AS a from v2"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"table_dependencies": [
{
@@ -702,40 +623,15 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from v1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "view": {
- "table": "v1",
- "select_id": 2,
- "algorithm": "materialized"
- }
- },
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b"
- }
- ]
- }
- },
- {
- "expanded_query": "/* select#1 */ select v1.a AS a from v1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select v1.a AS a from v1",
"steps": [
{
"join_optimization": {
"select_id": 2,
+ "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b",
"steps": [
{
"table_dependencies": [
@@ -911,19 +807,10 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b",
"steps": [
{
"condition_processing": {
@@ -1162,19 +1049,10 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT DISTINCT a FROM t1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select distinct t1.a AS a from t1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select distinct t1.a AS a from t1",
"steps": [
{
"table_dependencies": [
@@ -1327,19 +1205,10 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a",
"steps": [
{
"condition_processing": {
@@ -1526,19 +1395,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`",
"steps": [
{
"condition_processing": {
@@ -1714,19 +1574,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`",
"steps": [
{
"condition_processing": {
@@ -1929,19 +1780,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1 where a=1 and b=2 order by c limit 1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1",
"steps": [
{
"condition_processing": {
@@ -2305,19 +2147,10 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain
select t1.a from t1 left join t2 on t1.a=t2.a {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))",
"steps": [
{
"build_equal_items": {
@@ -2442,19 +2275,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1 left join t2 on t2.a=t1.a {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))",
"steps": [
{
"build_equal_items": {
@@ -2621,19 +2445,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))",
"steps": [
{
"build_equal_items": {
@@ -2812,46 +2627,19 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain extended select * from t1 where a in (select pk from t10) {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)",
"steps": [
{
- "join_preparation": {
+ "transformation": {
"select_id": 2,
- "steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
- {
- "expanded_query": "/* select#2 */ select t10.pk from t10"
- }
- ]
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
}
},
- {
- "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"transformation": {
"select_id": 2,
@@ -3135,19 +2923,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1 where pk = 2 and a=5 and b=1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1",
"steps": [
{
"condition_processing": {
@@ -3496,19 +3275,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select f1(a) from t1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select f1(t1.a) AS `f1(a)` from t1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select f1(t1.a) AS `f1(a)` from t1",
"steps": [
{
"table_dependencies": [
@@ -3593,19 +3363,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select f2(a) from t1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select f2(t1.a) AS `f2(a)` from t1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select f2(t1.a) AS `f2(a)` from t1",
"steps": [
{
"table_dependencies": [
@@ -3697,7 +3458,7 @@ a
2
select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
length(trace)
-2141
+2012
set optimizer_trace_max_mem_size=100;
select * from t1;
a
@@ -3708,10 +3469,9 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select * from t1 {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
- "steps": [
- 2041 0
+ "expanded_query": 1912 0
set optimizer_trace_max_mem_size=0;
select * from t1;
a
@@ -3719,7 +3479,7 @@ a
2
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
-select * from t1 2141 0
+select * from t1 2012 0
drop table t1;
set optimizer_trace='enabled=off';
set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size;
@@ -3809,19 +3569,10 @@ select * from information_schema.optimizer_trace;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3",
"steps": [
{
"condition_processing": {
@@ -4175,36 +3926,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from (select rand() from t1)q {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "derived": {
- "table": "q",
- "select_id": 2,
- "algorithm": "merged"
- }
- },
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1"
- }
- ]
- }
- },
- {
- "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q",
"steps": [
{
"derived": {
@@ -4217,6 +3942,7 @@ explain select * from (select rand() from t1)q {
{
"join_optimization": {
"select_id": 2,
+ "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1",
"steps": [
{
"table_dependencies": [
@@ -4385,46 +4111,19 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2) {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)",
"steps": [
{
- "join_preparation": {
+ "transformation": {
"select_id": 2,
- "steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
- {
- "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2"
- }
- ]
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
}
},
- {
- "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"transformation": {
"select_id": 2,
@@ -4835,73 +4534,19 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_
t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)",
"steps": [
{
- "join_preparation": {
+ "transformation": {
"select_id": 2,
- "steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
- {
- "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
- }
- ]
- }
- },
- {
- "join_preparation": {
- "select_id": 3,
- "steps": [
- {
- "transformation": {
- "select_id": 3,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
- {
- "transformation": {
- "select_id": 3,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
- {
- "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
- }
- ]
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
}
},
- {
- "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"transformation": {
"select_id": 2,
@@ -4910,6 +4555,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"converted_to_semi_join": true
}
},
+ {
+ "transformation": {
+ "select_id": 3,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 3,
@@ -6415,73 +6069,19 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_
t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)",
"steps": [
{
- "join_preparation": {
+ "transformation": {
"select_id": 2,
- "steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
- {
- "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
- }
- ]
- }
- },
- {
- "join_preparation": {
- "select_id": 3,
- "steps": [
- {
- "transformation": {
- "select_id": 3,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
- {
- "transformation": {
- "select_id": 3,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
- {
- "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
- }
- ]
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
}
},
- {
- "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"transformation": {
"select_id": 2,
@@ -6490,6 +6090,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"converted_to_semi_join": true
}
},
+ {
+ "transformation": {
+ "select_id": 3,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 3,
@@ -8677,19 +8286,10 @@ SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
query trace
SELECT 'a\0' LIMIT 0 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select 'a\0' AS `a\x00` limit 0"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select 'a\0' AS `a\x00` limit 0",
"steps": []
}
},
@@ -8716,19 +8316,10 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select count(*) from seq_1_to_10000000 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select count(0) AS `count(*)` from seq_1_to_10000000"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select count(0) AS `count(*)` from seq_1_to_10000000",
"steps": [
{
"table_dependencies": [
@@ -8855,50 +8446,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
"item": "t0.a in (1,2,3,4,5,6)",
"conversion":
[
-
- {
- "join_preparation":
- {
- "select_id": 2,
- "steps":
- [
-
- {
- "derived":
- {
- "table": "tvc_0",
- "select_id": 3,
- "algorithm": "materialized"
- }
- },
-
- {
- "transformation":
- {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
-
- {
- "transformation":
- {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "semijoin",
- "chosen": true
- }
- },
-
- {
- "expanded_query": "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0"
- }
- ]
- }
- }
]
}
]
@@ -8954,7 +8501,7 @@ set @path= (select json_search(@trace, 'one', 'no predicate for first keypart'))
set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2);
select @sub_path;
@sub_path
-$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0]
+$.steps[0].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0]
select
json_detailed(json_extract(
@trace,
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index f1e13586eda..b319639e9fa 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -19,19 +19,10 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1 where a=1 or b=1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 or t1.b = 1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 or t1.b = 1",
"steps": [
{
"condition_processing": {
diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result
index 0ddaaeae89d..bc063015e6d 100644
--- a/mysql-test/main/opt_trace_index_merge_innodb.result
+++ b/mysql-test/main/opt_trace_index_merge_innodb.result
@@ -27,19 +27,10 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select * from t1 where pk1 != 0 and key1 = 1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select t1.pk1 AS pk1,t1.pk2 AS pk2,t1.key1 AS key1,t1.key2 AS key2 from t1 where t1.pk1 <> 0 and t1.key1 = 1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select t1.pk1 AS pk1,t1.pk2 AS pk2,t1.key1 AS key1,t1.key2 AS key2 from t1 where t1.pk1 <> 0 and t1.key1 = 1",
"steps": [
{
"condition_processing": {
diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result
index e1937e744a4..9223f6c0a28 100644
--- a/mysql-test/main/opt_trace_security.result
+++ b/mysql-test/main/opt_trace_security.result
@@ -16,7 +16,6 @@ select * from db1.t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
- 0 1
set optimizer_trace="enabled=off";
grant select(a) on db1.t1 to 'foo'@'%';
set optimizer_trace="enabled=on";
@@ -50,19 +49,10 @@ select * from information_schema.OPTIMIZER_TRACE;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select * from db1.t1 {
"steps": [
- {
- "join_preparation": {
- "select_id": 1,
- "steps": [
- {
- "expanded_query": "select db1.t1.a AS a from t1"
- }
- ]
- }
- },
{
"join_optimization": {
"select_id": 1,
+ "expanded_query": "select db1.t1.a AS a from t1",
"steps": [
{
"table_dependencies": [
@@ -156,36 +146,18 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
select * from db1.v1 {
"steps": [
{
- "join_preparation": {
+ "join_optimization": {
"select_id": 1,
+ "expanded_query": "/* select#1 */ select db1.t1.a AS a from v1",
"steps": [
{
"view": {
"table": "v1",
"select_id": 2,
- "algorithm": "merged"
- }
- },
- {
- "join_preparation": {
- "select_id": 2,
- "steps": [
- {
- "expanded_query": "/* select#2 */ select db1.t1.a AS a from t1"
- }
- ]
+ "algorithm": "merged",
+ "expanded_query": "/* select#2 */ select db1.t1.a AS a from t1"
}
},
- {
- "expanded_query": "/* select#1 */ select db1.t1.a AS a from v1"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select_id": 1,
- "steps": [
{
"table_dependencies": [
{
diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h
index d82313f996f..20f479dc6d0 100644
--- a/sql/my_json_writer.h
+++ b/sql/my_json_writer.h
@@ -224,6 +224,14 @@ class Json_writer
size_t get_truncated_bytes() { return output.get_truncated_bytes(); }
+ /*
+ Note: this may not return exact value due to pretty-printer doing
+ buffering
+ */
+ size_t get_written_size() {
+ return output.length() + output.get_truncated_bytes();
+ }
+
Json_writer() :
indent_level(0), document_start(true), element_started(false),
first_child(true)
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index d91557c5be2..953f2af0be8 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -549,6 +549,12 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
and, depending on the rewrite, either do it, or record it to be done at a
later phase.
+ NOTE
+ * This function called at Prepare phase. It should NOT do any rewrites.
+ It only collects information that's used for doing the rewrites at the
+ optimization phase.
+ * Optimizer trace is NOT yet enabled when this function is called.
+
RETURN
0 - OK
Other - Some sort of query error
@@ -703,8 +709,6 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
{
DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
- (void)subquery_types_allow_materialization(thd, in_subs);
-
in_subs->is_flattenable_semijoin= TRUE;
/* Register the subquery for further processing in flatten_subqueries() */
@@ -717,10 +721,6 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
if (arena)
thd->restore_active_arena(arena, &backup);
in_subs->is_registered_semijoin= TRUE;
- OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform,
- select_lex->select_number,
- "IN (SELECT)", "semijoin");
- trace_transform.add("chosen", true);
}
}
else
@@ -1262,6 +1262,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
while ((in_subq= li++))
{
bool remove_item= TRUE;
+ (void)subquery_types_allow_materialization(thd, in_subq);
/* Stop processing if we've reached a subquery that's attached to the ON clause */
if (in_subq->do_not_convert_to_sj)
diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc
index ba9220cac44..2227519d991 100644
--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -106,6 +106,27 @@ inline bool sql_command_can_be_traced(enum enum_sql_command sql_command)
sql_command == SQLCOM_UPDATE_MULTI;
}
+
+void opt_trace_print_expanded_union(THD *thd, SELECT_LEX_UNIT *unit,
+ Json_writer_object *writer)
+{
+ DBUG_ASSERT(thd->trace_started());
+
+ StringBuffer<1024> str(system_charset_info);
+ ulonglong save_option_bits= thd->variables.option_bits;
+ thd->variables.option_bits &= ~OPTION_QUOTE_SHOW_CREATE;
+ unit->print(&str, enum_query_type(QT_TO_SYSTEM_CHARSET |
+ QT_SHOW_SELECT_NUMBER |
+ QT_ITEM_IDENT_SKIP_DB_NAMES |
+ QT_VIEW_INTERNAL));
+ thd->variables.option_bits= save_option_bits;
+ /*
+ The output is not very pretty lots of back-ticks, the output
+ is as the one in explain extended , lets try to improved it here.
+ */
+ writer->add("expanded_query", str.c_ptr_safe(), str.length());
+}
+
void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex,
Json_writer_object *writer)
@@ -499,10 +520,49 @@ Opt_trace_start::Opt_trace_start(THD *thd, TABLE_LIST *tbl,
}
}
+
+/*
+ @brief
+ See "Handing Query Errors" section of comment for Opt_trace_start
+*/
+
+void Opt_trace_start::trace_heading_done()
+{
+ Json_writer *w;
+ if (traceable && (w= ctx->get_current_json()))
+ trace_heading_size= w->get_written_size();
+ else
+ trace_heading_size= 0;
+}
+
+
+/*
+ @brief
+ See "Handing Query Errors" section of comment for Opt_trace_start
+
+ @detail
+ We can't delete the trace right now, because some final writes (e.g.
+ the top-level closing '}' will still be made to it. Just set clean_me=true
+ so that it is deleted instead of saving it.
+*/
+
+void Opt_trace_start::clean_empty_trace()
+{
+ Json_writer *w;
+ if (traceable && (w= ctx->get_current_json()))
+ {
+ if (w->get_written_size() == trace_heading_size)
+ clean_me= true;
+ }
+}
+
+
Opt_trace_start::~Opt_trace_start()
{
if (traceable)
{
+ if (clean_me)
+ ctx->abort_trace();
ctx->end();
traceable= FALSE;
}
diff --git a/sql/opt_trace.h b/sql/opt_trace.h
index 101fb5f707e..180ecd7bdd3 100644
--- a/sql/opt_trace.h
+++ b/sql/opt_trace.h
@@ -69,10 +69,33 @@ struct Opt_trace_info
@param query query
@param length query's length
@param charset charset which was used to encode this query
+
+ @detail
+ == Lifecycle ==
+ The trace is created before the Name Resolution phase. Reasons:
+ 1. This way, we can have one place where we start the trace for all kinds of
+ queries. If we tried to start tracing right before query optimization
+ starts, we would have to construct Opt_trace_start object in many
+ places: one for SELECT, for UPDATE, for DELETE, etc.
+
+ 2. Privilege checking code may notify the trace (which must exist already)
+ that the user doesn't have enough permissions to perform tracing. See
+ missing_privilege() and the opt_trace_disable_if_*** functions below.
+
+ == Handling Query Errors ==
+ The trace is kept when query error occurs, except for the case when
+ nothing [meaningful] was traced. The second part is necessary for mtr to
+ produce the same output with and without --ps-protocol. If there is an
+ error on prepare phase, then:
+ - In --ps-protocol: PREPARE command produces no trace. The EXECUTE
+ command is not run. The trace is not generated at all.
+ - Regular SQL query: should also NOT produce any trace to match the above.
+ This is handled by trace_heading_done() and clean_empty_trace().
*/
-class Opt_trace_start {
+class Opt_trace_start
+{
public:
Opt_trace_start(THD *thd_arg, TABLE_LIST *tbl,
enum enum_sql_command sql_command,
@@ -82,8 +105,17 @@ class Opt_trace_start {
const CHARSET_INFO *query_charset);
~Opt_trace_start();
+ void trace_heading_done();
+ void clean_empty_trace();
private:
Opt_trace_context *const ctx;
+
+ /* Number of bytes written to the trace after the heading was written/ */
+ size_t trace_heading_size;
+
+ /* If true, trace should be removed (See Handling Query Errors above) */
+ bool clean_me= false;
+
/*
True: the query will be traced
False: otherwise
@@ -102,6 +134,9 @@ class Opt_trace_start {
void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex,
Json_writer_object *trace_object);
+void opt_trace_print_expanded_union(THD *thd, SELECT_LEX_UNIT *unit,
+ Json_writer_object *writer);
+
void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab);
void trace_plan_prefix(JOIN *join, uint idx, table_map join_tables);
void print_final_join_order(JOIN *join);
diff --git a/sql/opt_trace_context.h b/sql/opt_trace_context.h
index f578a0c67ec..ae77c94fdc2 100644
--- a/sql/opt_trace_context.h
+++ b/sql/opt_trace_context.h
@@ -114,6 +114,12 @@ class Opt_trace_context
bool is_enabled();
+ void abort_trace()
+ {
+ delete current_trace;
+ current_trace= NULL;
+ }
+
void missing_privilege();
static const char *flag_names[];
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 579ea34b8e4..cf878ad29c0 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -420,6 +420,24 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
goto exit_merge;
}
+ if (unlikely(thd->trace_started()))
+ {
+ /*
+ Add to optimizer trace whether a derived table/view
+ is merged into the parent select or not.
+ */
+ OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived,
+ derived->is_derived() ? "derived" : "view",
+ derived->alias.str ? derived->alias.str : "<NULL>",
+ derived->get_unit()->first_select()->select_number,
+ derived->is_merged_derived() ? "merged" : "materialized");
+ if (derived->is_merged_derived())
+ {
+ opt_trace_print_expanded_union(thd, derived->get_unit(),
+ &trace_derived);
+ }
+ }
+
/*
exclude select lex so it doesn't show up in explain.
do this only for derived table as for views this is already done.
@@ -822,18 +840,6 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
}
}
- if (unlikely(thd->trace_started()))
- {
- /*
- Add to optimizer trace whether a derived table/view
- is merged into the parent select or not.
- */
- OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived,
- derived->is_derived() ? "derived" : "view",
- derived->alias.str ? derived->alias.str : "<NULL>",
- derived->get_unit()->first_select()->select_number,
- derived->is_merged_derived() ? "merged" : "materialized");
- }
/*
Above cascade call of prepare is important for PS protocol, but after it
is called we can check if we really need prepare for this derived
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index b9d3eec5a60..47cf801d74c 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3648,6 +3648,7 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt)
Json_writer_object trace_command(thd);
Json_writer_array trace_command_steps(thd, "steps");
+ ots.trace_heading_done();
/* store old value of binlog format */
enum_binlog_format orig_binlog_format,orig_current_stmt_binlog_format;
@@ -6150,6 +6151,9 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt)
wsrep_commit_empty(thd, true);
}
+ if (res || thd->is_error())
+ ots.clean_empty_trace();
+
/* assume PA safety for next transaction */
thd->wsrep_PA_safe= true;
#endif /* WITH_WSREP */
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index cc6f572ea64..b6bc9eff50e 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2435,17 +2435,6 @@ static bool check_prepared_statement(Prepared_statement *stmt)
lex->first_select_lex()->context.resolve_in_table_list_only(select_lex->
get_table_list());
- /*
- For the optimizer trace, this is the symmetric, for statement preparation,
- of what is done at statement execution (in mysql_execute_command()).
- */
- Opt_trace_start ots(thd, tables, lex->sql_command, &lex->var_list,
- thd->query(), thd->query_length(),
- thd->variables.character_set_client);
-
- Json_writer_object trace_command(thd);
- Json_writer_array trace_command_steps(thd, "steps");
-
/* Reset warning count for each query that uses tables */
if (tables)
thd->get_stmt_da()->opt_clear_warning_info(thd->query_id);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a357d4f8c8a..45d8f54e264 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1287,11 +1287,6 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num,
join_list= &select_lex->top_join_list;
union_part= unit_arg->is_unit_op();
- Json_writer_object trace_wrapper(thd);
- Json_writer_object trace_prepare(thd, "join_preparation");
- trace_prepare.add_select_number(select_lex->select_number);
- Json_writer_array trace_steps(thd, "steps");
-
// simple check that we got usable conds
dbug_print_item(conds);
@@ -1675,12 +1670,6 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num,
}
}
- if (thd->trace_started())
- {
- Json_writer_object trace_wrapper(thd);
- opt_trace_print_expanded_query(thd, select_lex, &trace_wrapper);
- }
-
if (!procedure && result && result->prepare(fields_list, unit_arg))
goto err; /* purecov: inspected */
@@ -1985,7 +1974,11 @@ JOIN::optimize_inner()
Json_writer_object trace_wrapper(thd);
Json_writer_object trace_prepare(thd, "join_optimization");
- trace_prepare.add_select_number(select_lex->select_number);
+ if (thd->trace_started())
+ {
+ trace_prepare.add_select_number(select_lex->select_number);
+ opt_trace_print_expanded_query(thd, select_lex, &trace_prepare);
+ }
Json_writer_array trace_steps(thd, "steps");
/*
1
0
[Commits] 1b21020e493: Code cleanup: don't call subquery_types_allow_materialization() on prepare
by psergey 15 Dec '21
by psergey 15 Dec '21
15 Dec '21
revision-id: 1b21020e4938d2e778dbdf5c3f77bd995f38be12 (mariadb-10.6.1-243-g1b21020e493)
parent(s): c88e37ff857a83387c4d86829fbaf2e277e4cf9f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-15 23:06:06 +0300
message:
Code cleanup: don't call subquery_types_allow_materialization() on prepare
For subqueries that are processed as semi-joins.
---
mysql-test/main/opt_trace.result | 119 ++++++++++++++++++---------------------
sql/opt_subselect.cc | 3 +-
2 files changed, 56 insertions(+), 66 deletions(-)
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index f7f5476ab23..e1b42a60986 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -2819,15 +2819,6 @@ explain extended select * from t1 where a in (select pk from t10) {
"join_preparation": {
"select_id": 2,
"steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
{
"transformation": {
"select_id": 2,
@@ -2852,6 +2843,15 @@ explain extended select * from t1 where a in (select pk from t10) {
"join_optimization": {
"select_id": 1,
"steps": [
+ {
+ "transformation": {
+ "select_id": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 2,
@@ -4392,15 +4392,6 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"join_preparation": {
"select_id": 2,
"steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
{
"transformation": {
"select_id": 2,
@@ -4425,6 +4416,15 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"join_optimization": {
"select_id": 1,
"steps": [
+ {
+ "transformation": {
+ "select_id": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 2,
@@ -4842,15 +4842,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"join_preparation": {
"select_id": 2,
"steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
{
"transformation": {
"select_id": 2,
@@ -4869,15 +4860,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"join_preparation": {
"select_id": 3,
"steps": [
- {
- "transformation": {
- "select_id": 3,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
{
"transformation": {
"select_id": 3,
@@ -4902,6 +4884,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"join_optimization": {
"select_id": 1,
"steps": [
+ {
+ "transformation": {
+ "select_id": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 2,
@@ -4910,6 +4901,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"converted_to_semi_join": true
}
},
+ {
+ "transformation": {
+ "select_id": 3,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 3,
@@ -6422,15 +6422,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"join_preparation": {
"select_id": 2,
"steps": [
- {
- "transformation": {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
{
"transformation": {
"select_id": 2,
@@ -6449,15 +6440,6 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"join_preparation": {
"select_id": 3,
"steps": [
- {
- "transformation": {
- "select_id": 3,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
{
"transformation": {
"select_id": 3,
@@ -6482,6 +6464,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"join_optimization": {
"select_id": 1,
"steps": [
+ {
+ "transformation": {
+ "select_id": 2,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 2,
@@ -6490,6 +6481,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"converted_to_semi_join": true
}
},
+ {
+ "transformation": {
+ "select_id": 3,
+ "from": "IN (SELECT)",
+ "to": "materialization",
+ "sjm_scan_allowed": true,
+ "possible": true
+ }
+ },
{
"transformation": {
"select_id": 3,
@@ -8876,17 +8876,6 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
}
},
- {
- "transformation":
- {
- "select_id": 2,
- "from": "IN (SELECT)",
- "to": "materialization",
- "sjm_scan_allowed": true,
- "possible": true
- }
- },
-
{
"transformation":
{
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 596b5169659..f2d395a9ee2 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -703,7 +703,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
{
DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
- (void)subquery_types_allow_materialization(thd, in_subs);
+ //(void)subquery_types_allow_materialization(thd, in_subs);
in_subs->is_flattenable_semijoin= TRUE;
@@ -1271,6 +1271,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
while ((in_subq= li++))
{
bool remove_item= TRUE;
+ subquery_types_allow_materialization(thd, in_subq);
/* Stop processing if we've reached a subquery that's attached to the ON clause */
if (in_subq->do_not_convert_to_sj)
1
0
[Commits] 3c9b7393563: MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
by psergey 15 Dec '21
by psergey 15 Dec '21
15 Dec '21
revision-id: 3c9b73935632aaacc941b1600c5046d57dec0ff6 (mariadb-10.2.40-139-g3c9b7393563)
parent(s): 026984c360ce27c62072ed6ce798ec855952c974
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-12-15 15:12:06 +0300
message:
MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
Followup to fix for MDEV-25858: When test_if_skip_sort_order() decides
to use an index to satisfy ORDER BY ... LIMIT clause, it should
disable "Range Checked for Each Record" optimization.
Do this in all cases.
---
mysql-test/r/order_by_innodb.result | 23 +++++++++++++++++++++++
mysql-test/t/order_by_innodb.test | 22 ++++++++++++++++++++++
sql/sql_select.cc | 8 ++++++++
3 files changed, 53 insertions(+)
diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result
index 14b9b861a14..28922ef65f2 100644
--- a/mysql-test/r/order_by_innodb.result
+++ b/mysql-test/r/order_by_innodb.result
@@ -198,5 +198,28 @@ id id
1 NULL
2 1
3 3
+#
+# MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
+#
+# This must NOT have "Range checked for each record" without any
+# provisions to produce rows in the required ordering:
+explain
+select
+t1.id,t2.id
+from
+t1 left join
+t2 on t2.id2 = t1.id and
+t2.id = (select dd.id
+from t2 dd
+where
+dd.id2 = t1.id and
+d1 > '2019-02-06 00:00:00'
+ order by
+dd.d1, dd.d2, dd.id limit 1
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index
+1 PRIMARY t2 eq_ref PRIMARY,id2 PRIMARY 4 func # Using where
+2 DEPENDENT SUBQUERY dd range id2,for_latest_sort for_latest_sort 6 NULL # Using where
drop table t1,t2;
# End of 10.2 tests
diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test
index 97c043b8dbc..af12644c073 100644
--- a/mysql-test/t/order_by_innodb.test
+++ b/mysql-test/t/order_by_innodb.test
@@ -184,6 +184,28 @@ from
order by
dd.d1 desc, dd.d2 desc, dd.id desc limit 1
);
+
+--echo #
+--echo # MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
+--echo #
+
+--echo # This must NOT have "Range checked for each record" without any
+--echo # provisions to produce rows in the required ordering:
+--replace_column 9 #
+explain
+select
+ t1.id,t2.id
+from
+ t1 left join
+ t2 on t2.id2 = t1.id and
+ t2.id = (select dd.id
+ from t2 dd
+ where
+ dd.id2 = t1.id and
+ d1 > '2019-02-06 00:00:00'
+ order by
+ dd.d1, dd.d2, dd.id limit 1
+ );
drop table t1,t2;
--echo # End of 10.2 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 54a2facfe9f..1ab0c295e73 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21980,7 +21980,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
}
}
else if (select && select->quick)
+ {
+ /* Cancel "Range checked for each record" */
+ if (tab->use_quick == 2)
+ {
+ tab->use_quick= 1;
+ tab->read_first_record= join_init_read_record;
+ }
select->quick->need_sorted_output();
+ }
tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ?
join_read_key_unlock_row : rr_unlock_row;
1
0