[Commits] cb07364: MDEV-15035 Wrong results when calling a stored procedure
by IgorBabaev 25 Apr '18
by IgorBabaev 25 Apr '18
25 Apr '18
revision-id: cb07364f1d1ec47febbcaf2397e3ed2b4c3dc06b (mariadb-5.5.59-59-gcb07364)
parent(s): 5e61e1716e763315009318081fba5994b8910242
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-04-24 15:51:49 -0700
message:
MDEV-15035 Wrong results when calling a stored procedure
multiple times with different arguments.
If the ON expression of an outer join is an OR formula with one
of the disjunct being a constant formula then the expression
cannot be null-rejected if the constant formula is true. Otherwise
it can be null-rejected and if so the outer join can be converted
into inner join. This optimization was added in the patch for
mdev-4817. Yet the code had a defect: if the query was used in
a stored procedure with parameters and the constant item contained
some of them then the value of this constant item depended on the
values of the parameters. With some parameters it may be true,
for others not. The validity of conversion to inner join is checked
only once and it happens only for the first call of procedure.
So if the parameters in the first call allowed the conversion it
was done and next calls used the transformed query though there
could be calls whose parameters made the conversion invalid.
Fixed by cheking whether the constant disjunct in the ON expression
originally contained an SP parameter. If so the expression is not
considered as null-rejected. For this check a new item's attribute
was intruduced: Item::with_param. It is calculated for each item
by fix fields() functions.
Also moved the call of optimize_constant_subqueries() in
JOIN::optimize after the call of simplify_joins(). The reason
for this is that after the optimization introduced by the patch
for mdev-4817 simplify_joins() can use the results of execution
of non-expensive constant subqueries and this is not valid.
---
mysql-test/r/sp-innodb.result | 34 ++++++++++++++++++++++++++++++++++
mysql-test/t/sp-innodb.test | 42 ++++++++++++++++++++++++++++++++++++++++++
sql/item.cc | 7 +++++++
sql/item.h | 1 +
sql/item_cmpfunc.cc | 43 +++++++++++++++++++++++++++++++------------
sql/item_func.cc | 1 +
sql/item_func.h | 7 +++++++
sql/item_row.cc | 1 +
sql/item_sum.cc | 3 +++
sql/sql_select.cc | 6 +++---
10 files changed, 130 insertions(+), 15 deletions(-)
diff --git a/mysql-test/r/sp-innodb.result b/mysql-test/r/sp-innodb.result
index b5fe920..9daf2c4 100644
--- a/mysql-test/r/sp-innodb.result
+++ b/mysql-test/r/sp-innodb.result
@@ -138,3 +138,37 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
#
# BUG 16041903: End of test case
#
+#
+# MDEV-15035: SP using query with outer join and a parameter
+# in ON expression
+#
+CREATE TABLE t1 (
+id int NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (
+id int NOT NULL,
+id_foo int NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 1);
+DROP PROCEDURE IF EXISTS test_proc;
+CREATE PROCEDURE test_proc(IN param int)
+LANGUAGE SQL
+READS SQL DATA
+BEGIN
+SELECT DISTINCT f.id
+FROM t1 f
+LEFT OUTER JOIN t2 b ON b.id_foo = f.id
+WHERE (param <> 0 OR b.id IS NOT NULL);
+END|
+CALL test_proc(0);
+id
+1
+CALL test_proc(1);
+id
+1
+2
+DROP PROCEDURE IF EXISTS test_proc;
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/sp-innodb.test b/mysql-test/t/sp-innodb.test
index 2371516..e44a853 100644
--- a/mysql-test/t/sp-innodb.test
+++ b/mysql-test/t/sp-innodb.test
@@ -158,5 +158,47 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
--echo # BUG 16041903: End of test case
--echo #
+--echo #
+--echo # MDEV-15035: SP using query with outer join and a parameter
+--echo # in ON expression
+--echo #
+
+CREATE TABLE t1 (
+ id int NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE TABLE t2 (
+ id int NOT NULL,
+ id_foo int NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1, 1);
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS test_proc;
+--enable_warnings
+
+DELIMITER |;
+CREATE PROCEDURE test_proc(IN param int)
+LANGUAGE SQL
+READS SQL DATA
+BEGIN
+ SELECT DISTINCT f.id
+ FROM t1 f
+ LEFT OUTER JOIN t2 b ON b.id_foo = f.id
+ WHERE (param <> 0 OR b.id IS NOT NULL);
+END|
+DELIMITER ;|
+
+CALL test_proc(0);
+CALL test_proc(1);
+
+DROP PROCEDURE IF EXISTS test_proc;
+DROP TABLE t1, t2;
+
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
diff --git a/sql/item.cc b/sql/item.cc
index 08a0061..c5c6df0 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -504,6 +504,7 @@ Item::Item():
in_rollup= 0;
decimals= 0; max_length= 0;
with_subselect= 0;
+ with_param= 0;
cmp_context= IMPOSSIBLE_RESULT;
/* Initially this item is not attached to any JOIN_TAB. */
join_tab_idx= MAX_TABLES;
@@ -550,6 +551,7 @@ Item::Item(THD *thd, Item *item):
null_value(item->null_value),
unsigned_flag(item->unsigned_flag),
with_sum_func(item->with_sum_func),
+ with_param(item->with_param),
with_field(item->with_field),
fixed(item->fixed),
is_autogenerated_name(item->is_autogenerated_name),
@@ -1486,6 +1488,9 @@ bool Item_sp_variable::fix_fields(THD *thd, Item **)
max_length= it->max_length;
decimals= it->decimals;
unsigned_flag= it->unsigned_flag;
+ with_param= 1;
+ if (thd->lex->current_select->master_unit()->item)
+ thd->lex->current_select->master_unit()->item->with_param= 1;
fixed= 1;
collation.set(it->collation.collation, it->collation.derivation);
@@ -7234,6 +7239,7 @@ void Item_ref::set_properties()
split_sum_func() doesn't try to change the reference.
*/
with_sum_func= (*ref)->with_sum_func;
+ with_param= (*ref)->with_param;
with_field= (*ref)->with_field;
unsigned_flag= (*ref)->unsigned_flag;
fixed= 1;
@@ -7681,6 +7687,7 @@ Item_cache_wrapper::Item_cache_wrapper(Item *item_arg)
decimals= orig_item->decimals;
collation.set(orig_item->collation);
with_sum_func= orig_item->with_sum_func;
+ with_param= orig_item->with_param;
with_field= orig_item->with_field;
unsigned_flag= orig_item->unsigned_flag;
name= item_arg->name;
diff --git a/sql/item.h b/sql/item.h
index 830f8bf..d756cf8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -644,6 +644,7 @@ class Item {
bool null_value; /* if item is null */
bool unsigned_flag;
bool with_sum_func; /* True if item contains a sum func */
+ bool with_param; /* True if contains an SP parameter */
/**
True if any item except Item_sum_func contains a field. Set during parsing.
*/
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 39f497e..6fb650b 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1546,6 +1546,7 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref)
}
eval_not_null_tables(NULL);
with_sum_func= args[0]->with_sum_func;
+ with_param= args[0]->with_param || args[1]->with_param;
with_field= args[0]->with_field;
if ((const_item_cache= args[0]->const_item()))
{
@@ -1587,6 +1588,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref)
with_subselect= 1;
with_sum_func= with_sum_func || args[1]->with_sum_func;
with_field= with_field || args[1]->with_field;
+ with_param= args[0]->with_param || args[1]->with_param;
used_tables_cache|= args[1]->used_tables();
const_item_cache&= args[1]->const_item();
fixed= 1;
@@ -2108,6 +2110,7 @@ void Item_func_interval::fix_length_and_dec()
used_tables_cache|= row->used_tables();
not_null_tables_cache= row->not_null_tables();
with_sum_func= with_sum_func || row->with_sum_func;
+ with_param= with_param || row->with_param;
with_field= with_field || row->with_field;
const_item_cache&= row->const_item();
}
@@ -4335,6 +4338,7 @@ Item_cond::fix_fields(THD *thd, Item **ref)
List_iterator<Item> li(list);
Item *item;
uchar buff[sizeof(char*)]; // Max local vars in function
+ bool is_and_cond= functype() == Item_func::COND_AND_FUNC;
not_null_tables_cache= used_tables_cache= 0;
const_item_cache= 1;
@@ -4396,26 +4400,33 @@ Item_cond::fix_fields(THD *thd, Item **ref)
(item= *li.ref())->check_cols(1))
return TRUE; /* purecov: inspected */
used_tables_cache|= item->used_tables();
- if (item->const_item())
+ if (item->const_item() && !item->with_param &&
+ !item->is_expensive() && !cond_has_datetime_is_null(item))
{
- if (!item->is_expensive() && !cond_has_datetime_is_null(item) &&
- item->val_int() == 0)
+ if (item->val_int() == is_and_cond && top_level())
{
/*
- This is "... OR false_cond OR ..."
+ a. This is "... AND true_cond AND ..."
+ In this case, true_cond has no effect on cond_and->not_null_tables()
+ b. This is "... OR false_cond/null cond OR ..."
In this case, false_cond has no effect on cond_or->not_null_tables()
*/
}
else
{
/*
- This is "... OR const_cond OR ..."
+ a. This is "... AND false_cond/null_cond AND ..."
+ The whole condition is FALSE/UNKNOWN.
+ b. This is "... OR const_cond OR ..."
In this case, cond_or->not_null_tables()=0, because the condition
const_cond might evaluate to true (regardless of whether some tables
were NULL-complemented).
*/
+ not_null_tables_cache= (table_map) 0;
and_tables_cache= (table_map) 0;
}
+ if (thd->is_error())
+ return TRUE;
}
else
{
@@ -4427,6 +4438,7 @@ Item_cond::fix_fields(THD *thd, Item **ref)
}
with_sum_func= with_sum_func || item->with_sum_func;
+ with_param= with_param || item->with_param;
with_field= with_field || item->with_field;
with_subselect|= item->has_subquery();
if (item->maybe_null)
@@ -4443,30 +4455,36 @@ bool
Item_cond::eval_not_null_tables(uchar *opt_arg)
{
Item *item;
+ bool is_and_cond= functype() == Item_func::COND_AND_FUNC;
List_iterator<Item> li(list);
not_null_tables_cache= (table_map) 0;
and_tables_cache= ~(table_map) 0;
while ((item=li++))
{
table_map tmp_table_map;
- if (item->const_item())
+ if (item->const_item() && !item->with_param &&
+ !item->is_expensive() && !cond_has_datetime_is_null(item))
{
- if (!item->is_expensive() && !cond_has_datetime_is_null(item) &&
- item->val_int() == 0)
+ if (item->val_int() == is_and_cond && top_level())
{
/*
- This is "... OR false_cond OR ..."
+ a. This is "... AND true_cond AND ..."
+ In this case, true_cond has no effect on cond_and->not_null_tables()
+ b. This is "... OR false_cond/null cond OR ..."
In this case, false_cond has no effect on cond_or->not_null_tables()
*/
}
else
{
/*
- This is "... OR const_cond OR ..."
+ a. This is "... AND false_cond/null_cond AND ..."
+ The whole condition is FALSE/UNKNOWN.
+ b. This is "... OR const_cond OR ..."
In this case, cond_or->not_null_tables()=0, because the condition
- some_cond_or might be true regardless of what tables are
- NULL-complemented.
+ const_cond might evaluate to true (regardless of whether some tables
+ were NULL-complemented).
*/
+ not_null_tables_cache= (table_map) 0;
and_tables_cache= (table_map) 0;
}
}
@@ -5118,6 +5136,7 @@ Item_func_regex::fix_fields(THD *thd, Item **ref)
args[1]->fix_fields(thd, args + 1)) || args[1]->check_cols(1))
return TRUE; /* purecov: inspected */
with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func;
+ with_param=args[0]->with_param || args[1]->with_param;
with_field= args[0]->with_field || args[1]->with_field;
with_subselect= args[0]->has_subquery() || args[1]->has_subquery();
max_length= 1;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 9e4edfc..8b3c72dd3 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -222,6 +222,7 @@ Item_func::fix_fields(THD *thd, Item **ref)
maybe_null=1;
with_sum_func= with_sum_func || item->with_sum_func;
+ with_param= with_param || item->with_param;
with_field= with_field || item->with_field;
used_tables_cache|= item->used_tables();
const_item_cache&= item->const_item();
diff --git a/sql/item_func.h b/sql/item_func.h
index 5781822..3a609fc 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -83,6 +83,7 @@ class Item_func :public Item_result_field
args= tmp_arg;
args[0]= a;
with_sum_func= a->with_sum_func;
+ with_param= a->with_param;
with_field= a->with_field;
}
Item_func(Item *a,Item *b):
@@ -91,6 +92,7 @@ class Item_func :public Item_result_field
args= tmp_arg;
args[0]= a; args[1]= b;
with_sum_func= a->with_sum_func || b->with_sum_func;
+ with_param= a->with_param || b->with_param;
with_field= a->with_field || b->with_field;
}
Item_func(Item *a,Item *b,Item *c):
@@ -102,6 +104,7 @@ class Item_func :public Item_result_field
arg_count= 3;
args[0]= a; args[1]= b; args[2]= c;
with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func;
+ with_param= a->with_param || b->with_param || c->with_param;
with_field= a->with_field || b->with_field || c->with_field;
}
}
@@ -115,6 +118,8 @@ class Item_func :public Item_result_field
args[0]= a; args[1]= b; args[2]= c; args[3]= d;
with_sum_func= a->with_sum_func || b->with_sum_func ||
c->with_sum_func || d->with_sum_func;
+ with_param= a->with_param || b->with_param ||
+ c->with_param || d->with_param;
with_field= a->with_field || b->with_field ||
c->with_field || d->with_field;
}
@@ -128,6 +133,8 @@ class Item_func :public Item_result_field
args[0]= a; args[1]= b; args[2]= c; args[3]= d; args[4]= e;
with_sum_func= a->with_sum_func || b->with_sum_func ||
c->with_sum_func || d->with_sum_func || e->with_sum_func ;
+ with_param= a->with_param || b->with_param ||
+ c->with_param || d->with_param || e->with_param;
with_field= a->with_field || b->with_field ||
c->with_field || d->with_field || e->with_field;
}
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 9e81c05..9fe34dd 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -125,6 +125,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref)
with_sum_func= with_sum_func || item->with_sum_func;
with_field= with_field || item->with_field;
with_subselect|= item->with_subselect;
+ with_param|= item->with_param;
}
fixed= 1;
return FALSE;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 709c2b6..16334cd 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1164,6 +1164,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref)
return TRUE;
set_if_bigger(decimals, args[i]->decimals);
with_subselect|= args[i]->with_subselect;
+ with_param|= args[i]->with_param;
}
result_field=0;
max_length=float_length(decimals);
@@ -1195,6 +1196,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref)
return TRUE;
decimals=item->decimals;
with_subselect= args[0]->with_subselect;
+ with_param= args[0]->with_param;
switch (hybrid_type= item->result_type()) {
case INT_RESULT:
@@ -3430,6 +3432,7 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref)
args[i]->check_cols(1))
return TRUE;
with_subselect|= args[i]->with_subselect;
+ with_param|= args[i]->with_param;
}
/* skip charset aggregation for order columns */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 90bb536..1e9f1c0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1034,9 +1034,6 @@ JOIN::optimize()
eval_select_list_used_tables();
- if (optimize_constant_subqueries())
- DBUG_RETURN(1);
-
table_count= select_lex->leaf_tables.elements;
if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
@@ -1098,6 +1095,9 @@ JOIN::optimize()
thd->restore_active_arena(arena, &backup);
}
+ if (optimize_constant_subqueries())
+ DBUG_RETURN(1);
+
if (setup_jtbm_semi_joins(this, join_list, &conds))
DBUG_RETURN(1);
2
1
revision-id: aff4dbce5d1e68376798b28e74b48133bb948ea3 (mariadb-10.2.14-61-gaff4dbc)
parent(s): 619dc2b24f26aea29345dc3f3289bed406738025
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-04-24 12:33:56 -0700
message:
Changed the test case for MDEV-15571
It has been done to demonstrate that the fix of this bug is good for 10.3
as well. The previous test case is not good for this purpose because
10.2 and 10.3 use different rules for determining the types of recursive
CTEs.
---
mysql-test/r/cte_recursive.result | 5 ++++-
mysql-test/t/cte_recursive.test | 7 ++++++-
sql/sql_select.cc | 2 ++
3 files changed, 12 insertions(+), 2 deletions(-)
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 6b2db35..70752c7 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -3083,16 +3083,19 @@ set big_tables=default;
#
# MDEV-15571: using recursive cte with big_tables enabled
#
+create table t1 (a bigint);
+insert into t1 values(1);
set big_tables=1;
with recursive qn as
(
-select 1 as a from dual
+select a from t1
union all
select a*2000 from qn where a<10000000000000000000
)
select * from qn;
ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000'
set big_tables=default;
+drop table t1;
#
# MDEV-15556: using recursive cte with big_tables enabled
# when recursive tables are accessed by key
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 50cb39a..332a64b 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2114,12 +2114,15 @@ set big_tables=default;
--echo # MDEV-15571: using recursive cte with big_tables enabled
--echo #
+create table t1 (a bigint);
+insert into t1 values(1);
+
set big_tables=1;
--error ER_DATA_OUT_OF_RANGE
with recursive qn as
(
- select 1 as a from dual
+ select a from t1
union all
select a*2000 from qn where a<10000000000000000000
)
@@ -2127,6 +2130,8 @@ select * from qn;
set big_tables=default;
+drop table t1;
+
--echo #
--echo # MDEV-15556: using recursive cte with big_tables enabled
--echo # when recursive tables are accessed by key
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6450eb0..db97596 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1304,6 +1304,8 @@ JOIN::optimize_inner()
/* Convert all outer joins to inner joins if possible */
conds= simplify_joins(this, join_list, conds, TRUE, FALSE);
+ if (thd->is_error())
+ DBUG_RETURN(1);
if (select_lex->save_leaf_tables(thd))
DBUG_RETURN(1);
build_bitmap_for_nested_joins(join_list, 0);
1
0
[Commits] db3a1ae: MDEV-15035 Wrong results when calling a stored procedure
by IgorBabaev 24 Apr '18
by IgorBabaev 24 Apr '18
24 Apr '18
revision-id: db3a1ae5d6e2796f61e6c8eec3446a81780aeee3 (mariadb-5.5.59-59-gdb3a1ae)
parent(s): 5e61e1716e763315009318081fba5994b8910242
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-04-24 11:34:44 -0700
message:
MDEV-15035 Wrong results when calling a stored procedure
multiple times with different arguments.
If the ON expression of an outer join is an OR formula with one
of the disjunct being a constant formula then the expression
cannot be null-rejected if the constant formula is true. Otherwise
it can be null-rejected and if so the outer join can be converted
into inner join. This optimization was added in the patch for
mdev-4817. Yet the code had a defect: if the query was used in
a stored procedure with parameters and the constant item contained
some of them then the value of this constant item depended on the
values of the parameters. With some parameters it may be true,
for others not. The validity of conversion to inner join is checked
only once and it happens only for the first call of procedure.
So if the parameters in the first call allowed the conversion it
was done and next calls used the transformed query though there
could be calls whose parameters made the conversion invalid.
Fixed by cheking whether the constant disjunct in the ON expression
originally contained an SP parameter. If so the expression is not
considered as null-rejected. For this check a new item's attribute
was intruduced: Item::with_param. It is calculated for each item
by fix fields() functions.
---
mysql-test/r/sp-innodb.result | 34 ++++++++++++++++++++++++++++++++++
mysql-test/t/sp-innodb.test | 42 ++++++++++++++++++++++++++++++++++++++++++
sql/item.cc | 7 +++++++
sql/item.h | 1 +
sql/item_cmpfunc.cc | 43 +++++++++++++++++++++++++++++++------------
sql/item_func.cc | 1 +
sql/item_func.h | 7 +++++++
sql/item_row.cc | 1 +
sql/item_sum.cc | 3 +++
9 files changed, 127 insertions(+), 12 deletions(-)
diff --git a/mysql-test/r/sp-innodb.result b/mysql-test/r/sp-innodb.result
index b5fe920..9daf2c4 100644
--- a/mysql-test/r/sp-innodb.result
+++ b/mysql-test/r/sp-innodb.result
@@ -138,3 +138,37 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
#
# BUG 16041903: End of test case
#
+#
+# MDEV-15035: SP using query with outer join and a parameter
+# in ON expression
+#
+CREATE TABLE t1 (
+id int NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (
+id int NOT NULL,
+id_foo int NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 1);
+DROP PROCEDURE IF EXISTS test_proc;
+CREATE PROCEDURE test_proc(IN param int)
+LANGUAGE SQL
+READS SQL DATA
+BEGIN
+SELECT DISTINCT f.id
+FROM t1 f
+LEFT OUTER JOIN t2 b ON b.id_foo = f.id
+WHERE (param <> 0 OR b.id IS NOT NULL);
+END|
+CALL test_proc(0);
+id
+1
+CALL test_proc(1);
+id
+1
+2
+DROP PROCEDURE IF EXISTS test_proc;
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/sp-innodb.test b/mysql-test/t/sp-innodb.test
index 2371516..e44a853 100644
--- a/mysql-test/t/sp-innodb.test
+++ b/mysql-test/t/sp-innodb.test
@@ -158,5 +158,47 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
--echo # BUG 16041903: End of test case
--echo #
+--echo #
+--echo # MDEV-15035: SP using query with outer join and a parameter
+--echo # in ON expression
+--echo #
+
+CREATE TABLE t1 (
+ id int NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE TABLE t2 (
+ id int NOT NULL,
+ id_foo int NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1, 1);
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS test_proc;
+--enable_warnings
+
+DELIMITER |;
+CREATE PROCEDURE test_proc(IN param int)
+LANGUAGE SQL
+READS SQL DATA
+BEGIN
+ SELECT DISTINCT f.id
+ FROM t1 f
+ LEFT OUTER JOIN t2 b ON b.id_foo = f.id
+ WHERE (param <> 0 OR b.id IS NOT NULL);
+END|
+DELIMITER ;|
+
+CALL test_proc(0);
+CALL test_proc(1);
+
+DROP PROCEDURE IF EXISTS test_proc;
+DROP TABLE t1, t2;
+
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
diff --git a/sql/item.cc b/sql/item.cc
index 08a0061..c5c6df0 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -504,6 +504,7 @@ Item::Item():
in_rollup= 0;
decimals= 0; max_length= 0;
with_subselect= 0;
+ with_param= 0;
cmp_context= IMPOSSIBLE_RESULT;
/* Initially this item is not attached to any JOIN_TAB. */
join_tab_idx= MAX_TABLES;
@@ -550,6 +551,7 @@ Item::Item(THD *thd, Item *item):
null_value(item->null_value),
unsigned_flag(item->unsigned_flag),
with_sum_func(item->with_sum_func),
+ with_param(item->with_param),
with_field(item->with_field),
fixed(item->fixed),
is_autogenerated_name(item->is_autogenerated_name),
@@ -1486,6 +1488,9 @@ bool Item_sp_variable::fix_fields(THD *thd, Item **)
max_length= it->max_length;
decimals= it->decimals;
unsigned_flag= it->unsigned_flag;
+ with_param= 1;
+ if (thd->lex->current_select->master_unit()->item)
+ thd->lex->current_select->master_unit()->item->with_param= 1;
fixed= 1;
collation.set(it->collation.collation, it->collation.derivation);
@@ -7234,6 +7239,7 @@ void Item_ref::set_properties()
split_sum_func() doesn't try to change the reference.
*/
with_sum_func= (*ref)->with_sum_func;
+ with_param= (*ref)->with_param;
with_field= (*ref)->with_field;
unsigned_flag= (*ref)->unsigned_flag;
fixed= 1;
@@ -7681,6 +7687,7 @@ Item_cache_wrapper::Item_cache_wrapper(Item *item_arg)
decimals= orig_item->decimals;
collation.set(orig_item->collation);
with_sum_func= orig_item->with_sum_func;
+ with_param= orig_item->with_param;
with_field= orig_item->with_field;
unsigned_flag= orig_item->unsigned_flag;
name= item_arg->name;
diff --git a/sql/item.h b/sql/item.h
index 830f8bf..d756cf8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -644,6 +644,7 @@ class Item {
bool null_value; /* if item is null */
bool unsigned_flag;
bool with_sum_func; /* True if item contains a sum func */
+ bool with_param; /* True if contains an SP parameter */
/**
True if any item except Item_sum_func contains a field. Set during parsing.
*/
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 39f497e..6fb650b 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1546,6 +1546,7 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref)
}
eval_not_null_tables(NULL);
with_sum_func= args[0]->with_sum_func;
+ with_param= args[0]->with_param || args[1]->with_param;
with_field= args[0]->with_field;
if ((const_item_cache= args[0]->const_item()))
{
@@ -1587,6 +1588,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref)
with_subselect= 1;
with_sum_func= with_sum_func || args[1]->with_sum_func;
with_field= with_field || args[1]->with_field;
+ with_param= args[0]->with_param || args[1]->with_param;
used_tables_cache|= args[1]->used_tables();
const_item_cache&= args[1]->const_item();
fixed= 1;
@@ -2108,6 +2110,7 @@ void Item_func_interval::fix_length_and_dec()
used_tables_cache|= row->used_tables();
not_null_tables_cache= row->not_null_tables();
with_sum_func= with_sum_func || row->with_sum_func;
+ with_param= with_param || row->with_param;
with_field= with_field || row->with_field;
const_item_cache&= row->const_item();
}
@@ -4335,6 +4338,7 @@ Item_cond::fix_fields(THD *thd, Item **ref)
List_iterator<Item> li(list);
Item *item;
uchar buff[sizeof(char*)]; // Max local vars in function
+ bool is_and_cond= functype() == Item_func::COND_AND_FUNC;
not_null_tables_cache= used_tables_cache= 0;
const_item_cache= 1;
@@ -4396,26 +4400,33 @@ Item_cond::fix_fields(THD *thd, Item **ref)
(item= *li.ref())->check_cols(1))
return TRUE; /* purecov: inspected */
used_tables_cache|= item->used_tables();
- if (item->const_item())
+ if (item->const_item() && !item->with_param &&
+ !item->is_expensive() && !cond_has_datetime_is_null(item))
{
- if (!item->is_expensive() && !cond_has_datetime_is_null(item) &&
- item->val_int() == 0)
+ if (item->val_int() == is_and_cond && top_level())
{
/*
- This is "... OR false_cond OR ..."
+ a. This is "... AND true_cond AND ..."
+ In this case, true_cond has no effect on cond_and->not_null_tables()
+ b. This is "... OR false_cond/null cond OR ..."
In this case, false_cond has no effect on cond_or->not_null_tables()
*/
}
else
{
/*
- This is "... OR const_cond OR ..."
+ a. This is "... AND false_cond/null_cond AND ..."
+ The whole condition is FALSE/UNKNOWN.
+ b. This is "... OR const_cond OR ..."
In this case, cond_or->not_null_tables()=0, because the condition
const_cond might evaluate to true (regardless of whether some tables
were NULL-complemented).
*/
+ not_null_tables_cache= (table_map) 0;
and_tables_cache= (table_map) 0;
}
+ if (thd->is_error())
+ return TRUE;
}
else
{
@@ -4427,6 +4438,7 @@ Item_cond::fix_fields(THD *thd, Item **ref)
}
with_sum_func= with_sum_func || item->with_sum_func;
+ with_param= with_param || item->with_param;
with_field= with_field || item->with_field;
with_subselect|= item->has_subquery();
if (item->maybe_null)
@@ -4443,30 +4455,36 @@ bool
Item_cond::eval_not_null_tables(uchar *opt_arg)
{
Item *item;
+ bool is_and_cond= functype() == Item_func::COND_AND_FUNC;
List_iterator<Item> li(list);
not_null_tables_cache= (table_map) 0;
and_tables_cache= ~(table_map) 0;
while ((item=li++))
{
table_map tmp_table_map;
- if (item->const_item())
+ if (item->const_item() && !item->with_param &&
+ !item->is_expensive() && !cond_has_datetime_is_null(item))
{
- if (!item->is_expensive() && !cond_has_datetime_is_null(item) &&
- item->val_int() == 0)
+ if (item->val_int() == is_and_cond && top_level())
{
/*
- This is "... OR false_cond OR ..."
+ a. This is "... AND true_cond AND ..."
+ In this case, true_cond has no effect on cond_and->not_null_tables()
+ b. This is "... OR false_cond/null cond OR ..."
In this case, false_cond has no effect on cond_or->not_null_tables()
*/
}
else
{
/*
- This is "... OR const_cond OR ..."
+ a. This is "... AND false_cond/null_cond AND ..."
+ The whole condition is FALSE/UNKNOWN.
+ b. This is "... OR const_cond OR ..."
In this case, cond_or->not_null_tables()=0, because the condition
- some_cond_or might be true regardless of what tables are
- NULL-complemented.
+ const_cond might evaluate to true (regardless of whether some tables
+ were NULL-complemented).
*/
+ not_null_tables_cache= (table_map) 0;
and_tables_cache= (table_map) 0;
}
}
@@ -5118,6 +5136,7 @@ Item_func_regex::fix_fields(THD *thd, Item **ref)
args[1]->fix_fields(thd, args + 1)) || args[1]->check_cols(1))
return TRUE; /* purecov: inspected */
with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func;
+ with_param=args[0]->with_param || args[1]->with_param;
with_field= args[0]->with_field || args[1]->with_field;
with_subselect= args[0]->has_subquery() || args[1]->has_subquery();
max_length= 1;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 9e4edfc..8b3c72dd3 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -222,6 +222,7 @@ Item_func::fix_fields(THD *thd, Item **ref)
maybe_null=1;
with_sum_func= with_sum_func || item->with_sum_func;
+ with_param= with_param || item->with_param;
with_field= with_field || item->with_field;
used_tables_cache|= item->used_tables();
const_item_cache&= item->const_item();
diff --git a/sql/item_func.h b/sql/item_func.h
index 5781822..3a609fc 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -83,6 +83,7 @@ class Item_func :public Item_result_field
args= tmp_arg;
args[0]= a;
with_sum_func= a->with_sum_func;
+ with_param= a->with_param;
with_field= a->with_field;
}
Item_func(Item *a,Item *b):
@@ -91,6 +92,7 @@ class Item_func :public Item_result_field
args= tmp_arg;
args[0]= a; args[1]= b;
with_sum_func= a->with_sum_func || b->with_sum_func;
+ with_param= a->with_param || b->with_param;
with_field= a->with_field || b->with_field;
}
Item_func(Item *a,Item *b,Item *c):
@@ -102,6 +104,7 @@ class Item_func :public Item_result_field
arg_count= 3;
args[0]= a; args[1]= b; args[2]= c;
with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func;
+ with_param= a->with_param || b->with_param || c->with_param;
with_field= a->with_field || b->with_field || c->with_field;
}
}
@@ -115,6 +118,8 @@ class Item_func :public Item_result_field
args[0]= a; args[1]= b; args[2]= c; args[3]= d;
with_sum_func= a->with_sum_func || b->with_sum_func ||
c->with_sum_func || d->with_sum_func;
+ with_param= a->with_param || b->with_param ||
+ c->with_param || d->with_param;
with_field= a->with_field || b->with_field ||
c->with_field || d->with_field;
}
@@ -128,6 +133,8 @@ class Item_func :public Item_result_field
args[0]= a; args[1]= b; args[2]= c; args[3]= d; args[4]= e;
with_sum_func= a->with_sum_func || b->with_sum_func ||
c->with_sum_func || d->with_sum_func || e->with_sum_func ;
+ with_param= a->with_param || b->with_param ||
+ c->with_param || d->with_param || e->with_param;
with_field= a->with_field || b->with_field ||
c->with_field || d->with_field || e->with_field;
}
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 9e81c05..9fe34dd 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -125,6 +125,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref)
with_sum_func= with_sum_func || item->with_sum_func;
with_field= with_field || item->with_field;
with_subselect|= item->with_subselect;
+ with_param|= item->with_param;
}
fixed= 1;
return FALSE;
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 709c2b6..16334cd 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1164,6 +1164,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref)
return TRUE;
set_if_bigger(decimals, args[i]->decimals);
with_subselect|= args[i]->with_subselect;
+ with_param|= args[i]->with_param;
}
result_field=0;
max_length=float_length(decimals);
@@ -1195,6 +1196,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref)
return TRUE;
decimals=item->decimals;
with_subselect= args[0]->with_subselect;
+ with_param= args[0]->with_param;
switch (hybrid_type= item->result_type()) {
case INT_RESULT:
@@ -3430,6 +3432,7 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref)
args[i]->check_cols(1))
return TRUE;
with_subselect|= args[i]->with_subselect;
+ with_param|= args[i]->with_param;
}
/* skip charset aggregation for order columns */
1
0
revision-id: 64ca3812839824baf0e300d5d11aa2a03c6a2537 (mariadb-10.3.6-44-g64ca3812839)
parent(s): e304f088837682f6186f56ee34bd74e9f33a59c3
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-24 15:17:58 +0200
message:
buildbot finding
---
tests/mysql_client_test.c | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index e574d6b6e78..97ffa5bc8ac 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -20236,8 +20236,6 @@ static void test_proxy_header()
test_proxy_header_ignore();
}
-#endif
-
static void test_bulk_autoinc()
{
@@ -20291,6 +20289,8 @@ static void test_bulk_autoinc()
myquery(rc);
}
+#endif
+
static struct my_tests_st my_tests[]= {
{ "disable_query_logs", disable_query_logs },
{ "test_view_sp_list_fields", test_view_sp_list_fields },
@@ -20576,8 +20576,8 @@ static struct my_tests_st my_tests[]= {
{ "test_mdev14454", test_mdev14454 },
#ifndef EMBEDDED_LIBRARY
{ "test_proxy_header", test_proxy_header},
-#endif
{ "test_bulk_autoinc", test_bulk_autoinc},
+#endif
{ 0, 0 }
};
1
0
[Commits] 48e3b4ca5dd: MDEV-15607: mysqld crashed few after node is being joined with sst
by jan 24 Apr '18
by jan 24 Apr '18
24 Apr '18
revision-id: 48e3b4ca5dd6a6cffbee64381dc301d43c66e036 (mariadb-10.1.32-67-g48e3b4ca5dd)
parent(s): 9c34a4124d67d9e3f70837eaeb11290f35e8f8d0
author: Jan Lindström
committer: Jan Lindström
timestamp: 2018-04-24 14:43:41 +0300
message:
MDEV-15607: mysqld crashed few after node is being joined with sst
This is a typical systemd response where it tries to shutdown the
joiner (due to "timeout") before the joiner manages to complete SST.
wsrep_sst_wait
wsrep_SE_init_wait
While waiting the operation to finish use mysql_cond_timedwait
instead of mysql_cond_wait and if operation is not finished
extend systemd timeout (if needed).
---
sql/wsrep_sst.cc | 21 +++++++++++++++++++--
1 file changed, 19 insertions(+), 2 deletions(-)
diff --git a/sql/wsrep_sst.cc b/sql/wsrep_sst.cc
index 260755d08a8..c1f980bd595 100644
--- a/sql/wsrep_sst.cc
+++ b/sql/wsrep_sst.cc
@@ -30,6 +30,10 @@
#include <cstdio>
#include <cstdlib>
+#if MYSQL_VERSION_ID < 100200
+# include <my_service_manager.h>
+#endif
+
static char wsrep_defaults_file[FN_REFLEN * 2 + 10 + 30 +
sizeof(WSREP_SST_OPT_CONF) +
sizeof(WSREP_SST_OPT_CONF_SUFFIX) +
@@ -186,6 +190,9 @@ bool wsrep_before_SE()
static bool sst_complete = false;
static bool sst_needed = false;
+#define WSREP_EXTEND_TIMEOUT_INTERVAL 30
+#define WSREP_TIMEDWAIT_SECONDS 10
+
void wsrep_sst_grab ()
{
WSREP_INFO("wsrep_sst_grab()");
@@ -197,11 +204,16 @@ void wsrep_sst_grab ()
// Wait for end of SST
bool wsrep_sst_wait ()
{
+ struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0};
if (mysql_mutex_lock (&LOCK_wsrep_sst)) abort();
while (!sst_complete)
{
WSREP_INFO("Waiting for SST to complete.");
- mysql_cond_wait (&COND_wsrep_sst, &LOCK_wsrep_sst);
+ mysql_cond_timedwait (&COND_wsrep_sst, &LOCK_wsrep_sst, &wtime);
+ if (!sst_complete)
+ service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL,
+ "WSREP state transfer ongoing, current seqno: %ld", local_seqno);
+
}
if (local_seqno >= 0)
@@ -1298,9 +1310,14 @@ void wsrep_SE_init_grab()
void wsrep_SE_init_wait()
{
+ struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0};
while (SE_initialized == false)
{
- mysql_cond_wait (&COND_wsrep_sst_init, &LOCK_wsrep_sst_init);
+ mysql_cond_timedwait (&COND_wsrep_sst_init, &LOCK_wsrep_sst_init, &wtime);
+
+ if (!SE_initialized)
+ service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL,
+ "WSREP SE initialization ongoing.");
}
mysql_mutex_unlock (&LOCK_wsrep_sst_init);
}
1
0
[Commits] cb6aade130b: MDEV-13695: INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle
by Oleksandr Byelkin 24 Apr '18
by Oleksandr Byelkin 24 Apr '18
24 Apr '18
revision-id: cb6aade130b75f3dc4b7c3a1dd4d33864adffd90 (mariadb-10.3.6-43-gcb6aade130b)
parent(s): f79c5a658cc33a10d7744a748a4328254e2cbaf7
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-24 12:04:59 +0200
message:
MDEV-13695: INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle
Switch off automatic INTERSECT priority for ORACLE MODE
---
mysql-test/main/intersect.result | 60 ++++++++++++++++++++++++++++++++++++++++
mysql-test/main/intersect.test | 38 +++++++++++++++++++++++++
sql/sql_yacc.yy | 3 +-
3 files changed, 100 insertions(+), 1 deletion(-)
diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result
index b589e8bd17e..66c7addfd36 100644
--- a/mysql-test/main/intersect.result
+++ b/mysql-test/main/intersect.result
@@ -607,6 +607,22 @@ NULL INTERSECT RESULT <intersect2,4> ALL NULL NULL NULL NULL NULL NULL
NULL UNION RESULT <union1,3,5> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`)
+set SQL_MODE=ORACLE;
+(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
+a b
+3 3
+4 4
+explain extended
+(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+2 UNION t2 ALL NULL NULL NULL NULL 2 100.00
+3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00
+4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 (/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") intersect (/* select#3 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") union (/* select#4 */ select 4 AS "4",4 AS "4")
+set SQL_MODE=default;
(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
e f
3 3
@@ -623,6 +639,24 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 (/* select#1 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) intersect (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union (/* select#4 */ select 4 AS `4`,4 AS `4`)
+set SQL_MODE=ORACLE;
+(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
+e f
+3 3
+4 4
+5 5
+6 6
+explain extended
+(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00
+2 INTERSECT t2 ALL NULL NULL NULL NULL 2 100.00
+3 UNION t1 ALL NULL NULL NULL NULL 2 100.00
+4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 (/* select#1 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") intersect (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") union (/* select#3 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#4 */ select 4 AS "4",4 AS "4")
+set SQL_MODE=default;
(/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`);
a b
3 3
@@ -772,4 +806,30 @@ SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
count(*)
14848
drop table t1,t2,t3;
+#
+# MDEV-13695: INTERSECT precedence is not in line with Oracle even
+# in SQL_MODE=Oracle
+#
+create table t12(c1 int);
+insert into t12 values(1);
+insert into t12 values(2);
+create table t13(c1 int);
+insert into t13 values(1);
+insert into t13 values(3);
+create table t234(c1 int);
+insert into t234 values(2);
+insert into t234 values(3);
+insert into t234 values(4);
+set SQL_MODE=oracle;
+select * from t13 union select * from t234 intersect select * from t12;
+c1
+1
+2
+set SQL_MODE=default;
+select * from t13 union select * from t234 intersect select * from t12;
+c1
+1
+2
+3
+drop table t12,t13,t234;
# End of 10.3 tests
diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test
index d9d420c786b..fb5e991a24c 100644
--- a/mysql-test/main/intersect.test
+++ b/mysql-test/main/intersect.test
@@ -147,12 +147,25 @@ insert into t3 values (1,1),(3,3);
(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
explain extended
(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
+set SQL_MODE=ORACLE;
+--sorted_result
+(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
+explain extended
+(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4);
+set SQL_MODE=default;
+
# test result of linear mix operation
--sorted_result
(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
explain extended
(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
+set SQL_MODE=ORACLE;
+--sorted_result
+(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
+explain extended
+(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4);
+set SQL_MODE=default;
--sorted_result
(/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`);
@@ -282,4 +295,29 @@ select count(*) from (
drop table t1,t2,t3;
+--echo #
+--echo # MDEV-13695: INTERSECT precedence is not in line with Oracle even
+--echo # in SQL_MODE=Oracle
+--echo #
+
+create table t12(c1 int);
+insert into t12 values(1);
+insert into t12 values(2);
+create table t13(c1 int);
+insert into t13 values(1);
+insert into t13 values(3);
+create table t234(c1 int);
+insert into t234 values(2);
+insert into t234 values(3);
+insert into t234 values(4);
+
+set SQL_MODE=oracle;
+--sorted_result
+select * from t13 union select * from t234 intersect select * from t12;
+set SQL_MODE=default;
+--sorted_result
+select * from t13 union select * from t234 intersect select * from t12;
+
+drop table t12,t13,t234;
+
--echo # End of 10.3 tests
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 93704cda06d..e1f3d15e514 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -546,7 +546,8 @@ bool LEX::add_select_to_union_list(bool is_union_distinct,
as possible */
if (type == INTERSECT_TYPE &&
(current_select->linkage != INTERSECT_TYPE &&
- current_select != current_select->master_unit()->first_select()))
+ current_select != current_select->master_unit()->first_select())
+ && !(thd->variables.sql_mode & MODE_ORACLE))
{
/*
This and previous SELECTs should go one level down because of
1
0
revision-id: 863e0712906d4e0938fe5afba71b94f1ecaab10a (mariadb-10.3.6-43-g863e0712906)
parent(s): 0d0cc6b0e903192600e8efdefa51129a485afec9
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-24 10:25:07 +0200
message:
fix
---
tests/mysql_client_test.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index f24bb919906..e574d6b6e78 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -20226,6 +20226,7 @@ static void test_proxy_header_ignore()
myquery(rc);
}
+
static void test_proxy_header()
{
test_proxy_header_tcp("192.0.2.1",3333);
1
0
revision-id: 0d0cc6b0e903192600e8efdefa51129a485afec9 (mariadb-10.3.6-42-g0d0cc6b0e90)
parent(s): 459c701b0b472d6bbfd9d5271e244797a2498f8d
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-04-24 10:24:22 +0200
message:
fix by buildbot results
---
tests/mysql_client_test.c | 23 +++++++++++------------
1 file changed, 11 insertions(+), 12 deletions(-)
diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 3188ea882bb..f24bb919906 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -20226,6 +20226,17 @@ static void test_proxy_header_ignore()
myquery(rc);
}
+static void test_proxy_header()
+{
+ test_proxy_header_tcp("192.0.2.1",3333);
+ test_proxy_header_tcp("2001:db8:85a3::8a2e:370:7334",2222);
+ test_proxy_header_tcp("::ffff:192.0.2.1",2222);
+ test_proxy_header_localhost();
+ test_proxy_header_ignore();
+}
+
+#endif
+
static void test_bulk_autoinc()
{
@@ -20279,18 +20290,6 @@ static void test_bulk_autoinc()
myquery(rc);
}
-
-static void test_proxy_header()
-{
- test_proxy_header_tcp("192.0.2.1",3333);
- test_proxy_header_tcp("2001:db8:85a3::8a2e:370:7334",2222);
- test_proxy_header_tcp("::ffff:192.0.2.1",2222);
- test_proxy_header_localhost();
- test_proxy_header_ignore();
-}
-
-#endif
-
static struct my_tests_st my_tests[]= {
{ "disable_query_logs", disable_query_logs },
{ "test_view_sp_list_fields", test_view_sp_list_fields },
1
0
[Commits] 65f79b36f98: MDEV-14820 System versioning is applied incorrectly to CTEs
by sergï¼ mariadb.org 23 Apr '18
by sergï¼ mariadb.org 23 Apr '18
23 Apr '18
revision-id: 65f79b36f9859333678938850d5ff78a59490dfc (10.4-12-g65f79b36f98)
parent(s): ceadd5485a56e272365e201e6c89429202abae3a
author: Sergei Golubchik
committer: Sergei Golubchik
timestamp: 2018-04-23 18:46:47 +0200
message:
MDEV-14820 System versioning is applied incorrectly to CTEs
Make sure that SELECT_LEX_UNIT::derived, behaves as documented
(points to the "TABLE_LIST representing this union in the
embedding select"). For recursive CTE this was not necessarily
the case, it could've pointed to the TABLE_LIST inside the CTE,
not in the embedding select.
To fix:
* don't update unit->derived in mysql_derived_prepare(), pass derived
as an argument to st_select_lex_unit::prepare()
* prefer to set unit->derived in TABLE_LIST::init_derived()
to the TABLE_LIST in the embedding select, not to the recursive
reference. Fail if there are many TABLE_LISTs in the embedding
select with conflicting FOR SYSTEM_TIME clauses.
cleanup:
* remove redundant THD* argument from st_select_lex_unit::prepare()
---
mysql-test/suite/versioning/disabled.def | 1 -
mysql-test/suite/versioning/r/cte.result | 225 ++++++++++++++++++++++++++++---
mysql-test/suite/versioning/t/cte.test | 152 +++++++++++++++++++--
sql/item_subselect.cc | 2 +-
sql/share/errmsg-utf8.txt | 4 +-
sql/sql_cte.cc | 2 +-
sql/sql_derived.cc | 4 +-
sql/sql_lex.h | 3 +-
sql/sql_prepare.cc | 4 +-
sql/sql_select.cc | 3 +-
sql/sql_union.cc | 58 ++++----
sql/sql_view.cc | 2 +-
sql/table.cc | 42 +++++-
sql/table.h | 2 +
14 files changed, 432 insertions(+), 72 deletions(-)
diff --git a/mysql-test/suite/versioning/disabled.def b/mysql-test/suite/versioning/disabled.def
index 11e45360f19..888298bbb09 100644
--- a/mysql-test/suite/versioning/disabled.def
+++ b/mysql-test/suite/versioning/disabled.def
@@ -9,4 +9,3 @@
# Do not use any TAB characters for whitespace.
#
##############################################################################
-cte: MDEV-14820
diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result
index fda5e086be2..6ad09ede5a6 100644
--- a/mysql-test/suite/versioning/r/cte.result
+++ b/mysql-test/suite/versioning/r/cte.result
@@ -1,9 +1,8 @@
set default_storage_engine=innodb;
create or replace table dept (
-dept_id int(10) primary key,
+dept_id int(10) primary key,
name varchar(100)
-)
-with system versioning;
+) with system versioning;
create or replace table emp (
emp_id int(10) primary key,
dept_id int(10) not null,
@@ -18,16 +17,51 @@ constraint `mgr-fk`
foreign key (mgr) references emp (emp_id)
on delete restrict
on update restrict
-)
-with system versioning;
+) with system versioning;
insert into dept (dept_id, name) values (10, "accounting");
-insert into emp (emp_id, name, salary, dept_id, mgr) values
+insert into emp (emp_id, name, salary, dept_id, mgr) values
(1, "bill", 1000, 10, null),
(20, "john", 500, 10, 1),
(30, "jane", 750, 10,1 );
-select max(sys_trx_start) into @ts_1 from emp;
+select row_start into @ts_1 from emp where name="jane";
update emp set mgr=30 where name ="john";
-select sys_trx_start into @ts_2 from emp where name="john";
+explain extended
+with ancestors as (
+select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary from emp as e
+) select * from ancestors for system_time as of @ts_1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 100.00
+2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
+3 UNION e ALL NULL NULL NULL NULL 4 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 with ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
+select row_start into @ts_2 from emp where name="john";
+explain extended /* All report to 'Bill' */
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp for system_time as of timestamp @ts_1 as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp for system_time as of timestamp @ts_1 as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
+2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION <derived2> ref key0 key0 4 test.e.mgr 2 100.00
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
/* All report to 'Bill' */
with recursive
ancestors
@@ -36,7 +70,7 @@ as
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_1 as e
where name = 'bill'
- union
+ union
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_1 as e,
ancestors as a
@@ -47,25 +81,186 @@ emp_id name mgr salary
1 bill NULL 1000
20 john 1 500
30 jane 1 750
-/* Expected 3 rows */
+explain extended with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of timestamp @ts_1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
+2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION <derived2> ref key0 key0 4 test.e.mgr 2 100.00
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
with recursive
ancestors
as
(
select e.emp_id, e.name, e.mgr, e.salary
-from emp for system_time as of timestamp @ts_2 as e
+from emp as e
where name = 'bill'
- union
+ union
select e.emp_id, e.name, e.mgr, e.salary
-from emp for system_time as of timestamp @ts_2 as e,
+from emp as e,
ancestors as a
where e.mgr = a.emp_id
)
-select * from ancestors;
+select * from ancestors for system_time as of timestamp @ts_1;
emp_id name mgr salary
1 bill NULL 1000
+20 john 1 500
30 jane 1 750
-20 john 30 500
+explain extended with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00
+1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join)
+4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 4 100.00
+2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where
+3 RECURSIVE UNION <derived2> ref key0 key0 4 test.e.mgr 2 100.00
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`emp_id` = `ancestors`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 04:14:07.999999'
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
+name
+bill
+john
+jane
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ancestors for system_time as of @ts_2 a2;
+emp_id name mgr salary emp_id name mgr salary
+1 bill NULL 1000 1 bill NULL 1000
+30 jane 1 750 1 bill NULL 1000
+20 john 30 500 1 bill NULL 1000
+1 bill NULL 1000 30 jane 1 750
+30 jane 1 750 30 jane 1 750
+20 john 30 500 30 jane 1 750
+1 bill NULL 1000 20 john 30 500
+30 jane 1 750 20 john 30 500
+20 john 30 500 20 john 30 500
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ancestors for system_time as of now() a2;
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors,
+ancestors for system_time as of @ts_2 a2;
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ancestors a2;
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+with recursive
+ancestors
+as
+(
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e
+where name = 'bill'
+ union
+select e.emp_id, e.name, e.mgr, e.salary
+from emp as e,
+ancestors as a
+where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2
+where emp_id in (select * from ancestors);
+ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
+# SYSTEM_TIME to internal recursive instance is prohibited
+with recursive cte as
+(
+select * from emp
+union all
+select * from cte for system_time as of @ts_1
+)
+select * from cte;
+ERROR HY000: Table `cte` is not system-versioned
create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1);
diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test
index 9df0bb3dfba..68ca53dc045 100644
--- a/mysql-test/suite/versioning/t/cte.test
+++ b/mysql-test/suite/versioning/t/cte.test
@@ -1,10 +1,9 @@
-- source include/have_innodb.inc
set default_storage_engine=innodb;
create or replace table dept (
- dept_id int(10) primary key,
+ dept_id int(10) primary key,
name varchar(100)
-)
-with system versioning;
+) with system versioning;
create or replace table emp (
emp_id int(10) primary key,
@@ -20,21 +19,29 @@ create or replace table emp (
foreign key (mgr) references emp (emp_id)
on delete restrict
on update restrict
-)
-with system versioning;
+) with system versioning;
insert into dept (dept_id, name) values (10, "accounting");
-insert into emp (emp_id, name, salary, dept_id, mgr) values
+insert into emp (emp_id, name, salary, dept_id, mgr) values
(1, "bill", 1000, 10, null),
(20, "john", 500, 10, 1),
(30, "jane", 750, 10,1 );
-select max(sys_trx_start) into @ts_1 from emp;
+select row_start into @ts_1 from emp where name="jane";
update emp set mgr=30 where name ="john";
-select sys_trx_start into @ts_2 from emp where name="john";
+explain extended
+with ancestors as (
+ select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary from emp as e
+) select * from ancestors for system_time as of @ts_1;
+
+select row_start into @ts_2 from emp where name="john";
+
+let $q=
/* All report to 'Bill' */
with recursive
ancestors
@@ -43,7 +50,7 @@ as
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_1 as e
where name = 'bill'
- union
+ union
select e.emp_id, e.name, e.mgr, e.salary
from emp for system_time as of timestamp @ts_1 as e,
ancestors as a
@@ -51,21 +58,138 @@ as
)
select * from ancestors;
-/* Expected 3 rows */
+eval explain extended $q;
+eval $q;
+
+let $q=with recursive
+ancestors
+as
+(
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e
+ where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e,
+ ancestors as a
+ where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of timestamp @ts_1;
+
+eval explain extended $q;
+eval $q;
+
+let $q=with recursive
+ancestors
+as
+(
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e
+ where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e,
+ ancestors as a
+ where e.mgr = a.emp_id
+)
+select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
+
+eval explain extended $q;
+eval $q;
+
with recursive
ancestors
as
(
select e.emp_id, e.name, e.mgr, e.salary
- from emp for system_time as of timestamp @ts_2 as e
+ from emp as e
where name = 'bill'
- union
+ union
select e.emp_id, e.name, e.mgr, e.salary
- from emp for system_time as of timestamp @ts_2 as e,
+ from emp as e,
ancestors as a
where e.mgr = a.emp_id
)
-select * from ancestors;
+select * from ancestors for system_time as of @ts_2,
+ ancestors for system_time as of @ts_2 a2;
+
+--error ER_AMBIGUOUS_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e
+ where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e,
+ ancestors as a
+ where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ ancestors for system_time as of now() a2;
+
+--error ER_AMBIGUOUS_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e
+ where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e,
+ ancestors as a
+ where e.mgr = a.emp_id
+)
+select * from ancestors,
+ ancestors for system_time as of @ts_2 a2;
+
+--error ER_AMBIGUOUS_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e
+ where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e,
+ ancestors as a
+ where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2,
+ ancestors a2;
+
+--error ER_AMBIGUOUS_FOR_SYSTEM_TIME
+with recursive
+ancestors
+as
+(
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e
+ where name = 'bill'
+ union
+ select e.emp_id, e.name, e.mgr, e.salary
+ from emp as e,
+ ancestors as a
+ where e.mgr = a.emp_id
+)
+select * from ancestors for system_time as of @ts_2
+ where emp_id in (select * from ancestors);
+
+--echo # SYSTEM_TIME to internal recursive instance is prohibited
+--error ER_VERS_NOT_VERSIONED
+with recursive cte as
+(
+ select * from emp
+ union all
+ select * from cte for system_time as of @ts_1
+)
+select * from cte;
create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index a5209ba5115..4b31e4b528a 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3674,7 +3674,7 @@ int subselect_single_select_engine::prepare(THD *thd)
int subselect_union_engine::prepare(THD *thd_arg)
{
set_thd(thd_arg);
- return unit->prepare(thd, result, SELECT_NO_UNLOCK);
+ return unit->prepare(unit->derived, result, SELECT_NO_UNLOCK);
}
int subselect_uniquesubquery_engine::prepare(THD *)
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 17192663381..e9a07bc8c59 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7859,8 +7859,8 @@ ER_VERS_ALTER_ENGINE_PROHIBITED
ER_VERS_RANGE_PROHIBITED
eng "SYSTEM_TIME range selector is not allowed"
-ER_UNUSED_26
- eng "You should never see it"
+ER_CONFLICTING_FOR_SYSTEM_TIME
+ eng "Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE"
ER_VERS_TABLE_MUST_HAVE_COLUMNS
eng "Table %`s must have at least one versioned column"
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index a58a9254a82..0cdd664d05c 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -978,7 +978,7 @@ bool With_element::prepare_unreferenced(THD *thd)
thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
if (!spec->prepared &&
- (spec->prepare(thd, 0, 0) ||
+ (spec->prepare(spec->derived, 0, 0) ||
rename_columns_of_derived_unit(thd, spec) ||
check_duplicate_names(thd, first_sl->item_list, 1)))
rc= true;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index ab66384c6cb..6c2242b6ced 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -747,8 +747,6 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
}
}
- unit->derived= derived;
-
/*
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
@@ -766,7 +764,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
// st_select_lex_unit::prepare correctly work for single select
- if ((res= unit->prepare(thd, derived->derived_result, 0)))
+ if ((res= unit->prepare(derived, derived->derived_result, 0)))
goto exit;
if (derived->with &&
(res= derived->with->rename_columns_of_derived_unit(thd, unit)))
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 02c2ffb6a12..6b66670617c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -770,7 +770,8 @@ class st_select_lex_unit: public st_select_lex_node {
bool is_excluded() { return prev == NULL; }
/* UNION methods */
- bool prepare(THD *thd, select_result *result, ulong additional_options);
+ bool prepare(TABLE_LIST *derived_arg, select_result *sel_result,
+ ulong additional_options);
bool optimize();
bool exec();
bool exec_recursive();
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 24f3cc66c6b..5e46a7192d7 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1528,7 +1528,7 @@ static int mysql_test_select(Prepared_statement *stmt,
It is not SELECT COMMAND for sure, so setup_tables will be called as
usual, and we pass 0 as setup_tables_done_option
*/
- if (unit->prepare(thd, 0, 0))
+ if (unit->prepare(unit->derived, 0, 0))
goto error;
if (!lex->describe && !thd->lex->analyze_stmt && !stmt->is_sql_prepare())
{
@@ -1699,7 +1699,7 @@ static bool select_like_stmt_test(Prepared_statement *stmt,
thd->lex->used_tables= 0; // Updated by setup_fields
/* Calls JOIN::prepare */
- DBUG_RETURN(lex->unit.prepare(thd, 0, setup_tables_done_option));
+ DBUG_RETURN(lex->unit.prepare(lex->unit.derived, 0, setup_tables_done_option));
}
/**
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b8fd6bd3da9..45850328f14 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25635,7 +25635,8 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
unit->fake_select_lex->type= unit_operation_text[unit->common_op()];
unit->fake_select_lex->options|= SELECT_DESCRIBE;
}
- if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
+ if (!(res= unit->prepare(unit->derived, result,
+ SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
res= unit->exec();
}
else
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 446e33366f3..d0922347b77 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -36,7 +36,7 @@ bool mysql_union(THD *thd, LEX *lex, select_result *result,
{
DBUG_ENTER("mysql_union");
bool res;
- if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK |
+ if (!(res= unit->prepare(unit->derived, result, SELECT_NO_UNLOCK |
setup_tables_done_option)))
res= unit->exec();
res|= unit->cleanup();
@@ -810,10 +810,11 @@ bool st_select_lex_unit::join_union_item_types(THD *thd_arg,
}
-bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
+bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg,
+ select_result *sel_result,
ulong additional_options)
{
- SELECT_LEX *lex_select_save= thd_arg->lex->current_select;
+ SELECT_LEX *lex_select_save= thd->lex->current_select;
SELECT_LEX *sl, *first_sl= first_select();
bool is_recursive= with_element && with_element->is_recursive;
bool is_rec_result_table_created= false;
@@ -824,7 +825,6 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
bool instantiate_tmp_table= false;
bool single_tvc= !first_sl->next_select() && first_sl->tvc;
DBUG_ENTER("st_select_lex_unit::prepare");
- DBUG_ASSERT(thd == thd_arg);
DBUG_ASSERT(thd == current_thd);
describe= additional_options & SELECT_DESCRIBE;
@@ -876,7 +876,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
prepared= 1;
saved_error= FALSE;
- thd_arg->lex->current_select= sl= first_sl;
+ thd->lex->current_select= sl= first_sl;
found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS;
is_union_select= is_unit_op() || fake_select_lex || single_tvc;
@@ -905,7 +905,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
while (last->next_select())
last= last->next_select();
if (!(tmp_result= union_result=
- new (thd_arg->mem_root) select_union_direct(thd_arg, sel_result,
+ new (thd->mem_root) select_union_direct(thd, sel_result,
last)))
goto err; /* purecov: inspected */
fake_select_lex= NULL;
@@ -914,11 +914,11 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
else
{
if (!is_recursive)
- union_result= new (thd_arg->mem_root) select_unit(thd_arg);
+ union_result= new (thd->mem_root) select_unit(thd);
else
{
with_element->rec_result=
- new (thd_arg->mem_root) select_union_recursive(thd_arg);
+ new (thd->mem_root) select_union_recursive(thd);
union_result= with_element->rec_result;
fake_select_lex= NULL;
}
@@ -936,10 +936,10 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
{
if (sl->tvc)
{
- if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
+ if (sl->tvc->prepare(thd, sl, tmp_result, this))
goto err;
}
- else if (prepare_join(thd_arg, first_sl, tmp_result, additional_options,
+ else if (prepare_join(thd, first_sl, tmp_result, additional_options,
is_union_select))
goto err;
types= first_sl->item_list;
@@ -950,10 +950,10 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
{
if (sl->tvc)
{
- if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
+ if (sl->tvc->prepare(thd, sl, tmp_result, this))
goto err;
}
- else if (prepare_join(thd_arg, sl, tmp_result, additional_options,
+ else if (prepare_join(thd, sl, tmp_result, additional_options,
is_union_select))
goto err;
@@ -973,7 +973,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
{
if (with_element)
{
- if (derived->with->rename_columns_of_derived_unit(thd, this))
+ if (derived_arg->with->rename_columns_of_derived_unit(thd, this))
goto err;
if (check_duplicate_names(thd, sl->item_list, 0))
goto err;
@@ -984,7 +984,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
if (first_sl->item_list.elements != sl->item_list.elements)
{
my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT,
- ER_THD(thd_arg, ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),
+ ER_THD(thd, ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),
MYF(0));
goto err;
}
@@ -993,25 +993,25 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
{
if (!with_element->is_anchor(sl))
sl->uncacheable|= UNCACHEABLE_UNITED;
- if(!is_rec_result_table_created &&
- (!sl->next_select() ||
- sl->next_select() == with_element->first_recursive))
+ if (!is_rec_result_table_created &&
+ (!sl->next_select() ||
+ sl->next_select() == with_element->first_recursive))
{
ulonglong create_options;
- create_options= (first_sl->options | thd_arg->variables.option_bits |
+ create_options= (first_sl->options | thd->variables.option_bits |
TMP_TABLE_ALL_COLUMNS);
// Join data types for all non-recursive parts of a recursive UNION
if (join_union_item_types(thd, types, union_part_count + 1))
goto err;
if (union_result->create_result_table(thd, &types,
MY_TEST(union_distinct),
- create_options, &derived->alias,
- false,
+ create_options,
+ &derived_arg->alias, false,
instantiate_tmp_table, false,
0))
goto err;
- if (!derived->table)
- derived->table= derived->derived_result->table=
+ if (!derived_arg->table)
+ derived_arg->table= derived_arg->derived_result->table=
with_element->rec_result->rec_tables.head();
with_element->mark_as_with_prepared_anchor();
is_rec_result_table_created= true;
@@ -1082,7 +1082,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
}
- create_options= (first_sl->options | thd_arg->variables.option_bits |
+ create_options= (first_sl->options | thd->variables.option_bits |
TMP_TABLE_ALL_COLUMNS);
/*
Force the temporary table to be a MyISAM table if we're going to use
@@ -1110,7 +1110,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
Query_arena *arena, backup_arena;
arena= thd->activate_stmt_arena_if_needed(&backup_arena);
- intersect_mark= new (thd_arg->mem_root) Item_int(thd, 0);
+ intersect_mark= new (thd->mem_root) Item_int(thd, 0);
if (arena)
thd->restore_active_arena(arena, &backup_arena);
@@ -1154,7 +1154,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
result_table_list.maybe_null_exec= save_maybe_null;
}
- thd_arg->lex->current_select= lex_select_save;
+ thd->lex->current_select= lex_select_save;
if (!item_list.elements)
{
Query_arena *arena, backup_arena;
@@ -1194,7 +1194,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
*/
fake_select_lex->item_list= item_list;
- thd_arg->lex->current_select= fake_select_lex;
+ thd->lex->current_select= fake_select_lex;
/*
We need to add up n_sum_items in order to make the correct
@@ -1222,12 +1222,12 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
}
}
- thd_arg->lex->current_select= lex_select_save;
+ thd->lex->current_select= lex_select_save;
- DBUG_RETURN(saved_error || thd_arg->is_fatal_error);
+ DBUG_RETURN(saved_error || thd->is_fatal_error);
err:
- thd_arg->lex->current_select= lex_select_save;
+ thd->lex->current_select= lex_select_save;
(void) cleanup();
DBUG_RETURN(TRUE);
}
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index e910d48c75c..b5700fa5ee1 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -530,7 +530,7 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views,
/* prepare select to resolve all fields */
lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW;
- if (unit->prepare(thd, 0, 0))
+ if (unit->prepare(unit->derived, 0, 0))
{
/*
some errors from prepare are reported to user, if is not then
diff --git a/sql/table.cc b/sql/table.cc
index 577ed20a87e..75c7d69cfa7 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8114,7 +8114,21 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
(first_table && first_table->is_multitable()))
set_multitable();
- unit->derived= this;
+ if (!unit->derived)
+ unit->derived= this;
+ else if (!is_with_table_recursive_reference())
+ {
+ if (unit->derived->is_with_table_recursive_reference())
+ unit->derived= this;
+ else if (vers_conditions.eq(unit->derived->vers_conditions))
+ vers_conditions.empty();
+ else
+ {
+ my_error(ER_CONFLICTING_FOR_SYSTEM_TIME, MYF(0));
+ return TRUE;
+ }
+ }
+
if (init_view && !view)
{
/* This is all what we can do for a derived table for now. */
@@ -8864,6 +8878,26 @@ void vers_select_conds_t::resolve_units(bool timestamps_only)
end.resolve_unit(timestamps_only);
}
+bool vers_select_conds_t::eq(const vers_select_conds_t &conds)
+{
+ if (type != conds.type)
+ return false;
+ switch (type) {
+ case SYSTEM_TIME_UNSPECIFIED:
+ case SYSTEM_TIME_ALL:
+ return true;
+ case SYSTEM_TIME_BEFORE:
+ DBUG_ASSERT(0);
+ case SYSTEM_TIME_AS_OF:
+ return start.eq(conds.start);
+ case SYSTEM_TIME_FROM_TO:
+ case SYSTEM_TIME_BETWEEN:
+ return start.eq(conds.start) && end.eq(conds.end);
+ }
+ DBUG_ASSERT(0);
+ return false;
+}
+
void Vers_history_point::resolve_unit(bool timestamps_only)
{
if (item && unit == VERS_UNDEFINED)
@@ -8885,6 +8919,12 @@ void Vers_history_point::fix_item()
item->decimals= 6;
}
+
+bool Vers_history_point::eq(const vers_history_point_t &point)
+{
+ return unit == point.unit && item->eq(point.item, false);
+}
+
void Vers_history_point::print(String *str, enum_query_type query_type,
const char *prefix, size_t plen)
{
diff --git a/sql/table.h b/sql/table.h
index ac382e5aa94..8f8929de066 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1827,6 +1827,7 @@ class Vers_history_point : public vers_history_point_t
void empty() { unit= VERS_UNDEFINED; item= NULL; }
void print(String *str, enum_query_type, const char *prefix, size_t plen);
void resolve_unit(bool timestamps_only);
+ bool eq(const vers_history_point_t &point);
};
struct vers_select_conds_t
@@ -1876,6 +1877,7 @@ struct vers_select_conds_t
{
return !from_query && type != SYSTEM_TIME_UNSPECIFIED;
}
+ bool eq(const vers_select_conds_t &conds);
};
/*
1
0
[Commits] ccae16d: MDEV-12387 Push conditions into materialized subqueries
by galina.shalyginaï¼ mariadb.com 23 Apr '18
by galina.shalyginaï¼ mariadb.com 23 Apr '18
23 Apr '18
revision-id: ccae16dfc128bfb95421c98a7ce331c38fc747ce (mariadb-10.3.4-60-gccae16d)
parent(s): 283b3275742ca22cd1f46a21b1a794f1b52039a9
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2018-04-23 17:50:11 +0200
message:
MDEV-12387 Push conditions into materialized subqueries
The method join_equalities_after_optimize_cond() changed and renamed.
Some comments changed.
---
mysql-test/r/derived.result | 2 +-
sql/opt_subselect.cc | 401 ++++++++++++++++++++------------------------
sql/opt_subselect.h | 6 +-
sql/sql_select.cc | 7 +-
sql/sql_select.h | 6 +
5 files changed, 198 insertions(+), 224 deletions(-)
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 32184c5..75b5391 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -632,7 +632,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DERIVED t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1276 Field or reference 'sq.f2' of SELECT #3 was resolved in SELECT #1
-Note 1003 /* select#1 */ select 6 AS `f1` from <materialize> (/* select#4 */ select `test`.`t2`.`f3` from `test`.`t2` having `test`.`t2`.`f3` >= 8) semi join (`test`.`t2`) where `test`.`t2`.`f3` = 6 and `<subquery4>`.`f3` = 9
+Note 1003 /* select#1 */ select 6 AS `f1` from <materialize> (/* select#4 */ select `test`.`t2`.`f3` from `test`.`t2` having `test`.`t2`.`f3` >= 8) semi join (`test`.`t2`) where `<subquery4>`.`f3` = 9 and `test`.`t2`.`f3` = 6
DROP TABLE t2,t1;
#
# MDEV-9462: Out of memory using explain on 2 empty tables
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index eac779c..c3a392c 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5429,257 +5429,220 @@ int select_value_catcher::send_data(List<Item> &items)
DBUG_RETURN(0);
}
-/**
- @brief
- Try to add an equality to some multiple equality
-
- @param thd the thread handle
- @param cond_equal the set of multiple equalities
- @param item the item that can be added
-
- @note
- To check this fact check_simple_equality is called
-
- @retval TRUE if the equality can be used to build multiple equalities
- @retval FALSE otherwise
-*/
-bool join_equalities_for_setup_jtbm_semi_joins(THD *thd,
- COND_EQUAL *cond_equal,
- Item *item)
-{
- if (!(item->type() == Item::FUNC_ITEM &&
- ((Item_func *)item)->functype() == Item_func::EQ_FUNC))
- return false;
-
- Item *left_item= ((Item_func *)item)->arguments()[0]->real_item();
- Item *right_item= ((Item_func *)item)->arguments()[1]->real_item();
-
- if (check_simple_equality(thd,
- Item::Context(Item::ANY_SUBST,
- ((Item_func_equal *)item)->compare_type_handler(),
- ((Item_func_equal *)item)->compare_collation()),
- left_item, right_item, cond_equal))
- return true;
-
- return false;
-}
-
/**
- @brief Push down equalities into the internal levels of the condition
- if needed
+ @brief
+ Conjugate conditions after optimize_cond() call
@param thd the thread handle
- @param level the current level number
- @param conds the current level condition
- @param cond_equal the equalities from the upper level that should
- be pushed down
+ @param cond the condition where to attach new conditions
+ @param cond_eq IN/OUT the multiple equalities of cond
+ @param new_conds IN/OUT the list of conditions needed to add
+ @param cond_value the returned value of the condition
@details
- During the setup_jtbm_semi_joins work new equalities can appear.
- These equalities are attached to the top level of the WHERE clause
- but are not attached to the inner levels.
- This procedure pushes equalities down to the inner levels if needed merging
- them with the exist equalities on the this level.
+ The method creates new condition through conjunction of cond and
+ the conditions from new_conds list.
+ The method is called after optimize_cond() for cond. The result
+ of the conjunction should be the same as if it was done before the
+ the optimize_cond() call.
+
+ @retval NULL if an error occurs
+ @retval otherwise the created condition
*/
-Item *search_for_missing_parts_of_equalities(THD *thd,
- uint level,
- Item *conds,
- COND_EQUAL *cond_equal)
-{
- if (conds->type() != Item::COND_ITEM)
- {
- /*
- If current level is top level — nothing to add
- */
- if (level<2)
- return conds;
-
- if (conds->type() == Item::FUNC_ITEM &&
- ((Item_func *)conds)->functype() != Item_func::MULT_EQUAL_FUNC)
- return conds;
- List_iterator<Item_equal> it(cond_equal->current_level);
- Item_equal *item;
- Item_equal *eq= (Item_equal *)conds;
-
- while ((item=it++))
- eq->merge_with_check(thd, item, true);
+Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
+ COND_EQUAL **cond_eq,
+ List<Item> &new_conds,
+ Item::cond_result *cond_value)
+{
+ COND_EQUAL new_cond_equal;
+ Item *item;
+ Item_equal *equality;
+ bool is_simplified_cond= false;
+ List_iterator<Item> li(new_conds);
+ List_iterator_fast<Item_equal> it(new_cond_equal.current_level);
- return eq;
- }
- else
+ /*
+ Creates multiple equalities 'new_cond_equal' from new_conds list
+ equalities. If multiple equality can't be created or the condition
+ from new_conds list isn't an equality the method leaves it in new_conds
+ list.
+
+ The equality can't be converted into the multiple equality if it
+ is a knowingly false or true equality.
+ For example, (3 = 1) equality.
+ */
+ while ((item=li++))
{
- List_iterator_fast<Item> li(*((Item_cond*) conds)->argument_list());
- Item *item;
- level++;
-
- if (((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC)
- cond_equal= &((Item_cond_and *) conds)->m_cond_equal;
-
- while ((item=li++))
- item= search_for_missing_parts_of_equalities(thd, level,
- item, cond_equal);
+ if (item->type() == Item::FUNC_ITEM &&
+ ((Item_func *) item)->functype() == Item_func::EQ_FUNC &&
+ check_simple_equality(thd,
+ Item::Context(Item::ANY_SUBST,
+ ((Item_func_equal *)item)->compare_type_handler(),
+ ((Item_func_equal *)item)->compare_collation()),
+ ((Item_func *)item)->arguments()[0]->real_item(),
+ ((Item_func *)item)->arguments()[1]->real_item(),
+ &new_cond_equal))
+ li.remove();
}
- return conds;
-}
-
-
-/**
- @brief
- Attach the equalities to the WHERE-clause condition
- @param join join where equalities should be attached
- @param eq_list the set of the equalities to add
-
- @details
- The method modifies the condition of the join (the condition of the
- WHERE clause) by adding new equalities from eq_list. It adds new
- equalities to the remain multiple equalities of the WHERE clause condition
- and attaches them to the WHERE clause condition.
-
- First all multiple equalities are disjoined from the WHERE clause condition
- to avoid repetitions.
- Multiple equalities for the on expression of join are merged with the
- equalities from eq_list. For example, MULT_EQ(t1.a, t1.b) and (t1.a = 2)
- will become MULT_EQ(2, t1.a, t1.b)
- Sometimes merge is not possible and in this case the equalities that can't
- be merged are saved to be attached to the condition later. This situation
- can appear after the optimize of the IN subquery predicate if it is
- transformed in the knowingly false equality. For example, (3 = 1) equality.
-
- Finally, a new condition is created. It consists of the old condition from
- which multiple equalities were disjoint, new multiple equalities and the
- equalities from eq_list that weren't merged with the multiple equalities.
-
- @retval TRUE if an error occurs
- @retval FALSE otherwise
-*/
-
-bool join_equalities_after_optimize_cond(JOIN *join,
- List<Item> &eq_list)
-{
- DBUG_ENTER("join_equalities_after_optimize_cond");
- Item *conds= join->conds;
- List<Item> *and_args= NULL;
- COND_EQUAL cond_equal;
- Item_equal *item_equal;
- List<Item_equal> *cond_equal_list=
- (List<Item_equal> *) &join->cond_equal->current_level;
- THD *thd= join->thd;
-
- if (conds && conds->type() == Item::COND_ITEM &&
- ((Item_cond*) conds)->functype() == Item_func::COND_AND_FUNC)
+ it.rewind();
+ if (cond && cond->type() == Item::COND_ITEM &&
+ ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
{
- and_args= ((Item_cond*) conds)->argument_list();
- if (join->cond_equal && join->cond_equal->current_level.elements)
+ /*
+ cond is an AND-condition.
+ The method conjugates the AND-condition cond, created multiple
+ equalities 'new_cond_equal' and remain conditions from new_conds.
+
+ First, the method disjoins multiple equalities of cond and
+ merges 'new_cond_equal' multiple equalities with these equalities.
+ It checks if after the merge the multiple equalities are knowingly
+ true or false equalities.
+ It attaches to cond conditions from new_conds list and the result
+ of the merge of multiple equalities. The multiple equalities are
+ attached only to the upper level of cond AND-condition. So they
+ should be pushed down to the inner levels of cond AND-condition
+ if needed. It is done by propagate_new_equalities().
+ */
+ COND_EQUAL *cond_equal= &((Item_cond_and *) cond)->m_cond_equal;
+ List<Item_equal> *cond_equalities= &cond_equal->current_level;
+ List<Item> *and_args= ((Item_cond_and *)cond)->argument_list();
+ and_args->disjoin((List<Item> *) cond_equalities);
+ and_args->append(&new_conds);
+
+ while ((equality= it++))
{
- and_args->disjoin((List<Item> *) &join->cond_equal->current_level);
- cond_equal.current_level.append(cond_equal_list);
- join->cond_equal->current_level.empty();
+ equality->upper_levels= 0;
+ equality->merge_into_list(thd, cond_equalities, false, false);
}
+ List_iterator_fast<Item_equal> ei(*cond_equalities);
+ while ((equality= ei++))
+ {
+ if (equality->const_item() && !equality->val_int())
+ is_simplified_cond= true;
+ equality->fixed= 0;
+ if (equality->fix_fields(thd, NULL))
+ return NULL;
+ }
+
+ and_args->append((List<Item> *) cond_equalities);
+ *cond_eq= &((Item_cond_and *) cond)->m_cond_equal;
+
+ propagate_new_equalities(thd, cond, cond_equalities,
+ cond_equal->upper_levels,
+ &is_simplified_cond);
+ cond= cond->propagate_equal_fields(thd,
+ Item::Context_boolean(),
+ cond_equal);
}
- else if (join->cond_equal && join->cond_equal->current_level.elements)
+ else
{
/*
- If the condition of the WHERE clause is a multiple equality itself
- it is set to 0.
+ cond isn't AND-condition or is NULL.
+ There can be several cases:
+
+ 1. cond is a multiple equality.
+ In this case cond is merged with the multiple equalities of
+ 'new_cond_equal'.
+ The new condition is created with the conjunction of new_conds
+ list conditions and the result of merge of multiple equalities.
+ 2. cond isn't a multiple equality and isn't NULL
+ In this case new condition is created from cond, remain conditions
+ from new_conds list and created multiple equalities from
+ 'new_cond_equal'.
+ 3. cond is NULL
+ The new condition is created from the conditions from new_conds
+ list and multiple equalities from 'new_cond_equal'.
*/
- if (conds && conds->type() == Item::FUNC_ITEM &&
- ((Item_func*) conds)->functype() == Item_func::MULT_EQUAL_FUNC)
- conds= 0;
+ List<Item> new_conds_list; // List to store new condition elements
+ bool is_mult_eq= (cond && cond->type() == Item::FUNC_ITEM &&
+ ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC);
- cond_equal.current_level.append(cond_equal_list);
- join->cond_equal->current_level.empty();
- }
+ if (cond && !is_mult_eq)
+ new_conds_list.push_back(cond, thd->mem_root);
+ if (new_conds.elements > 0)
+ {
+ li.rewind();
+ while ((item=li++))
+ {
+ if (!item->fixed && item->fix_fields(thd, NULL))
+ return NULL;
+ if (item->const_item() && !item->val_int())
+ is_simplified_cond= true;
+ }
+ if (new_conds.elements > 1)
+ new_conds_list.append(&new_conds);
+ else
+ {
+ li.rewind();
+ item= li++;
+ new_conds_list.push_back(item, thd->mem_root);
+ }
+ }
- /*
- Merges the equalities from the equal_list with the multiple equalities
- of the condition of the WHERE clause. If the equality can't be merged it
- is left in the eq_list list so it can be later added to the WHERE clause.
- */
- List_iterator<Item> li(eq_list);
- Item *item;
- while ((item=li++))
- {
- if (join_equalities_for_setup_jtbm_semi_joins(thd, &cond_equal, item))
- li.remove();
- }
+ if (new_cond_equal.current_level.elements > 0)
+ {
+ if (is_mult_eq)
+ {
+ Item_equal *eq_cond= (Item_equal *)cond;
+ eq_cond->upper_levels= 0;
+ eq_cond->merge_into_list(thd, &new_cond_equal.current_level,
+ false, false);
- if (conds)
- conds= conds->propagate_equal_fields(thd,
- Item::Context_boolean(),
- &cond_equal);
+ while ((equality= it++))
+ {
+ if (equality->const_item() && !equality->val_int())
+ is_simplified_cond= true;
+ }
- /* Fix just created multiple equalities */
- List_iterator_fast<Item_equal> it(cond_equal.current_level);
- while ((item_equal= it++))
- {
- item_equal->set_link_equal_fields(true);
- item_equal->fixed= 0;
- if (item_equal->fix_fields(thd, NULL))
- DBUG_RETURN(TRUE);
- item_equal->update_used_tables();
- set_if_bigger(thd->lex->current_select->max_equal_elems,
- item_equal->n_field_items());
- }
+ if (new_cond_equal.current_level.elements +
+ new_conds_list.elements == 1)
+ {
+ it.rewind();
+ equality= it++;
+ equality->fixed= 0;
+ if (equality->fix_fields(thd, NULL))
+ return NULL;
+ }
+ *cond_eq= &new_cond_equal;
+ }
+ new_conds_list.append((List<Item> *)&new_cond_equal.current_level);
+ }
- /*
- Creates AND-condition for the WITH clause if at least one of these
- conditions is satisfied:
+ if (new_conds_list.elements > 1)
+ {
+ Item_cond_and *and_cond=
+ new (thd->mem_root) Item_cond_and(thd, new_conds_list);
- 1. there are several multiple inequalities
- 2. there remain several equalities in the equality list
- 3. in the {conds, equalities, cond_equal.current_level} set
- there are at least 2 non-empty elements.
- */
- uint mult_eq_cnt= cond_equal.current_level.elements;
- uint eq_cnt= eq_list.elements;
- if (!and_args &&
- ((mult_eq_cnt > 1) ||
- (eq_cnt > 1) ||
- (mult_eq_cnt && eq_cnt) ||
- (mult_eq_cnt && conds) ||
- (conds && eq_cnt)))
- {
- if (!conds)
- conds= new (thd->mem_root) Item_cond_and(thd);
+ and_cond->m_cond_equal.copy(new_cond_equal);
+ cond= (Item *)and_cond;
+ *cond_eq= &((Item_cond_and *)cond)->m_cond_equal;
+ }
else
{
- Item_cond_and *new_conds= new (thd->mem_root) Item_cond_and(thd);
- new_conds->argument_list()->push_back(conds);
- conds= new_conds;
+ List_iterator_fast<Item> iter(new_conds_list);
+ cond= iter++;
}
- and_args= ((Item_cond*) conds)->argument_list();
- }
-
- /* Attaches the remaining equalities from the eq_list to the WHERE clause */
- if (and_args)
- and_args->append(&eq_list);
- else
- conds= new (thd->mem_root) Item_cond_and(thd, eq_list);
- if (conds && !conds->fixed && conds->fix_fields(thd, NULL))
- DBUG_RETURN(TRUE);
+ if (!cond->fixed && cond->fix_fields(thd, NULL))
+ return NULL;
- /* Attaches the multiple equalities to the WHERE clause condition */
- ((Item_cond_and *)conds)->m_cond_equal.copy(cond_equal);
- cond_equal.current_level=
- ((Item_cond_and *)conds)->m_cond_equal.current_level;
- if (and_args)
- {
- and_args->append((List<Item> *)&cond_equal.current_level);
- conds= search_for_missing_parts_of_equalities(thd, 0, conds, &cond_equal);
- }
- else if (mult_eq_cnt == 1)
- {
- it.rewind();
- conds= it++;
+ if (new_cond_equal.current_level.elements > 0)
+ cond= cond->propagate_equal_fields(thd,
+ Item::Context_boolean(),
+ &new_cond_equal);
}
- join->conds= conds;
-
- DBUG_RETURN(FALSE);
+ /*
+ If it was found that some of the created condition parts are knowingly
+ true or false equalities method calls removes_eq_cond() to remove them
+ from the condition and set the cond_value to the appropriate value.
+ */
+ if (is_simplified_cond)
+ cond= cond->remove_eq_conds(thd, cond_value, true);
+ return cond;
}
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index e116fdf..0311182 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -26,8 +26,10 @@ int check_and_do_in_subquery_rewrites(JOIN *join);
bool convert_join_subqueries_to_semijoins(JOIN *join);
int pull_out_semijoin_tables(JOIN *join);
bool optimize_semijoin_nests(JOIN *join, table_map all_table_map);
-bool join_equalities_after_optimize_cond(JOIN *join,
- List<Item> &eq_list);
+Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
+ COND_EQUAL **cond_eq,
+ List<Item> &new_conds,
+ Item::cond_result *cond_value);
bool setup_degenerate_jtbm_semi_joins(JOIN *join,
List<TABLE_LIST> *join_list,
List<Item> &eq_list);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e7bc68e..58f7a0e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1791,9 +1791,12 @@ JOIN::optimize_inner()
if (eq_list.elements != 0)
{
- if (join_equalities_after_optimize_cond(this, eq_list))
+ conds= and_new_conditions_to_optimized_cond(thd, conds, &cond_equal,
+ eq_list, &cond_value);
+
+ if (!conds &&
+ cond_value != Item::COND_FALSE && cond_value != Item::COND_TRUE)
DBUG_RETURN(TRUE);
- conds= conds->remove_eq_conds(thd, &cond_value, true);
}
if (thd->lex->sql_command == SQLCOM_SELECT &&
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 05255e7..56b7874 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1737,6 +1737,7 @@ class JOIN :public Sql_alloc
bool fix_all_splittings_in_plan();
bool transform_in_predicates_into_in_subq(THD *thd);
+ bool add_equalities_to_where_condition(THD *thd, List<Item> &eq_list);
private:
/**
Create a temporary table to be used for processing DISTINCT/ORDER
@@ -2448,4 +2449,9 @@ bool check_simple_equality(THD *thd, const Item::Context &ctx,
Item *left_item, Item *right_item,
COND_EQUAL *cond_equal);
+void propagate_new_equalities(THD *thd, Item *cond,
+ List<Item_equal> *new_equalities,
+ COND_EQUAL *inherited,
+ bool *is_simplifiable_cond);
+
#endif /* SQL_SELECT_INCLUDED */
1
0