summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived_opt.result27
-rw-r--r--mysql-test/t/derived_opt.test33
-rw-r--r--sql/item.cc3
-rw-r--r--sql/sql_lex.cc10
-rw-r--r--sql/sql_lex.h3
-rw-r--r--sql/sql_select.cc8
6 files changed, 77 insertions, 7 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result
index 0e8b49d5714..069b0aa65c9 100644
--- a/mysql-test/r/derived_opt.result
+++ b/mysql-test/r/derived_opt.result
@@ -537,4 +537,31 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
set join_cache_level=default;
DROP TABLE t1,t2;
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch="derived_merge=on";
+CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
+insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
+CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
+insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
+prepare stmt from "
+SELECT * from
+ (SELECT min(d2) AS d2, min(d1) AS d1 FROM
+ (SELECT t1.d2 AS d2, (SELECT t2.d1
+ FROM t2 WHERE t1.id1 = t2.id1
+ ORDER BY t2.id DESC LIMIT 1) AS d1
+ FROM t1
+ ) dt2
+ ) ca
+ ORDER BY ca.d2;";
+execute stmt;
+d2 d1
+2020-01-01 10:10:10 2020-01-01 10:10:10
+execute stmt;
+d2 d1
+2020-01-01 10:10:10 2020-01-01 10:10:10
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+#
+# End of 10.3 tests
+#
set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test
index aab95f69f26..305cac120a0 100644
--- a/mysql-test/t/derived_opt.test
+++ b/mysql-test/t/derived_opt.test
@@ -401,5 +401,38 @@ set join_cache_level=default;
DROP TABLE t1,t2;
+#
+# MDEV-25182: Complex query in Store procedure corrupts results
+#
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch="derived_merge=on";
+
+CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
+insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
+
+CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
+insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
+
+prepare stmt from "
+SELECT * from
+ (SELECT min(d2) AS d2, min(d1) AS d1 FROM
+ (SELECT t1.d2 AS d2, (SELECT t2.d1
+ FROM t2 WHERE t1.id1 = t2.id1
+ ORDER BY t2.id DESC LIMIT 1) AS d1
+ FROM t1
+ ) dt2
+ ) ca
+ ORDER BY ca.d2;";
+
+execute stmt;
+execute stmt;
+
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
# The following command must be the last one the file
set optimizer_switch=@exit_optimizer_switch;
diff --git a/sql/item.cc b/sql/item.cc
index 55d135c660e..42272fe0148 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4678,6 +4678,9 @@ static bool mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
bool suppress_warning_output)
{
DBUG_ENTER("mark_as_dependent");
+ DBUG_PRINT("info", ("current select: %d (%p) last: %d (%p)",
+ current->select_number, current,
+ (last ? last->select_number : 0), last));
/* store pointer on SELECT_LEX from which item is dependent */
if (mark_item && mark_item->can_be_depended)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 5059e4f656e..bfe773b2c00 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2470,7 +2470,7 @@ void st_select_lex_unit::exclude_tree()
*/
bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last,
- Item *dependency)
+ Item_ident *dependency)
{
DBUG_ASSERT(this != last);
@@ -2478,10 +2478,14 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last,
/*
Mark all selects from resolved to 1 before select where was
found table as depended (of select where was found table)
+
+ We move by name resolution context, bacause during merge can some select
+ be excleded from SELECT tree
*/
- SELECT_LEX *s= this;
+ Name_resolution_context *c= &this->context;
do
{
+ SELECT_LEX *s= c->select_lex;
if (!(s->uncacheable & UNCACHEABLE_DEPENDENT_GENERATED))
{
// Select is dependent of outer select
@@ -2503,7 +2507,7 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last,
if (subquery_expr && subquery_expr->mark_as_dependent(thd, last,
dependency))
return TRUE;
- } while ((s= s->outer_select()) != last && s != 0);
+ } while ((c= c->outer_context) != NULL && (c->select_lex != last));
is_correlated= TRUE;
this->master_unit()->item->is_correlated= TRUE;
return FALSE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index f733f783d0e..31751a16471 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1004,7 +1004,8 @@ public:
}
inline bool is_subquery_function() { return master_unit()->item != 0; }
- bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency);
+ bool mark_as_dependent(THD *thd, st_select_lex *last,
+ Item_ident *dependency);
void set_braces(bool value)
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7bfbf719017..90c071803a1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12097,10 +12097,12 @@ ha_rows JOIN_TAB::get_examined_rows()
bool JOIN_TAB::preread_init()
{
TABLE_LIST *derived= table->pos_in_table_list;
+ DBUG_ENTER("JOIN_TAB::preread_init");
+
if (!derived || !derived->is_materialized_derived())
{
preread_init_done= TRUE;
- return FALSE;
+ DBUG_RETURN(FALSE);
}
/* Materialize derived table/view. */
@@ -12108,7 +12110,7 @@ bool JOIN_TAB::preread_init()
derived->is_recursive_with_table()) &&
mysql_handle_single_derived(join->thd->lex,
derived, DT_CREATE | DT_FILL))
- return TRUE;
+ DBUG_RETURN(TRUE);
preread_init_done= TRUE;
if (select && select->quick)
@@ -12125,7 +12127,7 @@ bool JOIN_TAB::preread_init()
if (table->fulltext_searched)
init_ftfuncs(join->thd, join->select_lex, MY_TEST(join->order));
- return FALSE;
+ DBUG_RETURN(FALSE);
}
View Lorry Controller Status