summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_exists2in.result37
-rw-r--r--mysql-test/t/subselect_exists2in.test40
-rw-r--r--sql/item.cc25
-rw-r--r--sql/item.h14
-rw-r--r--sql/item_cmpfunc.cc25
-rw-r--r--sql/item_cmpfunc.h10
-rw-r--r--sql/item_func.cc5
-rw-r--r--sql/item_func.h2
-rw-r--r--sql/item_row.cc5
-rw-r--r--sql/item_row.h2
-rw-r--r--sql/item_subselect.cc15
-rw-r--r--sql/item_subselect.h4
-rw-r--r--sql/opt_subselect.cc7
-rw-r--r--sql/sql_derived.cc6
14 files changed, 152 insertions, 45 deletions
diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result
index 5deb2dfa9c5..d47e446fe8f 100644
--- a/mysql-test/r/subselect_exists2in.result
+++ b/mysql-test/r/subselect_exists2in.result
@@ -934,5 +934,42 @@ f2
foo
set optimizer_switch= @optimizer_switch_save;
DROP TABLE t1;
+#
+# MDEV-14164: Unknown column error when adding aggregate to function
+# in oracle style procedure FOR loop
+#
+CREATE TABLE t1(id INT, val INT);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE cur1 CURSOR FOR SELECT * FROM (
+SELECT DISTINCT id FROM t1) a
+WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
+WHERE a.id=b.id);
+OPEN cur1;
+CLOSE cur1;
+OPEN cur1;
+CLOSE cur1;
+END;
+//
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1(id INT, val INT);
+CREATE PROCEDURE p1()
+BEGIN
+SELECT * FROM (SELECT DISTINCT id FROM t1) a
+WHERE NOT a.id IN (SELECT b.id FROM t1 b);
+SELECT * FROM (SELECT DISTINCT id FROM t1) a
+WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id);
+END;
+//
+CALL p1();
+id
+id
+CALL p1();
+id
+id
+DROP PROCEDURE p1;
+DROP TABLE t1;
# End of 10.0 tests
set optimizer_switch=default;
diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test
index a4fdbe5c50b..5a8ddb3612f 100644
--- a/mysql-test/t/subselect_exists2in.test
+++ b/mysql-test/t/subselect_exists2in.test
@@ -786,6 +786,46 @@ set optimizer_switch= @optimizer_switch_save;
DROP TABLE t1;
+--echo #
+--echo # MDEV-14164: Unknown column error when adding aggregate to function
+--echo # in oracle style procedure FOR loop
+--echo #
+
+CREATE TABLE t1(id INT, val INT);
+DELIMITER //;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE cur1 CURSOR FOR SELECT * FROM (
+ SELECT DISTINCT id FROM t1) a
+ WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
+ WHERE a.id=b.id);
+ OPEN cur1;
+ CLOSE cur1;
+ OPEN cur1;
+ CLOSE cur1;
+END;
+//
+DELIMITER ;//
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+CREATE TABLE t1(id INT, val INT);
+DELIMITER //;
+CREATE PROCEDURE p1()
+BEGIN
+ SELECT * FROM (SELECT DISTINCT id FROM t1) a
+ WHERE NOT a.id IN (SELECT b.id FROM t1 b);
+ SELECT * FROM (SELECT DISTINCT id FROM t1) a
+ WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id);
+END;
+//
+DELIMITER ;//
+CALL p1();
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
--echo # End of 10.0 tests
#restore defaults
diff --git a/sql/item.cc b/sql/item.cc
index 050b37f6124..9466ab07921 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2745,7 +2745,8 @@ table_map Item_field::all_used_tables() const
return (get_depended_from() ? OUTER_REF_TABLE_BIT : field->table->map);
}
-void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+ bool merge)
{
if (new_parent == get_depended_from())
depended_from= NULL;
@@ -2789,6 +2790,18 @@ void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
if (!need_change)
return;
+ if (!merge)
+ {
+ /*
+ We just moved this field out to outer SELECT from inner one, and it
+ was outer field.
+ Outer fields uses normal SELECT_LEX context of upper selects for
+ name resolution, so we can switch everything to it.
+ */
+ this->context= &new_parent->context;
+ return;
+ }
+
Name_resolution_context *ctx= new Name_resolution_context();
if (context->select_lex == new_parent)
{
@@ -8107,18 +8120,20 @@ bool Item_outer_ref::fix_fields(THD *thd, Item **reference)
}
-void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent,
+ Item **ref, bool merge)
{
if (get_depended_from() == new_parent)
{
*ref= outer_ref;
- (*ref)->fix_after_pullout(new_parent, ref);
+ (*ref)->fix_after_pullout(new_parent, ref, merge);
}
}
-void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr,
+ bool merge)
{
- (*ref)->fix_after_pullout(new_parent, ref);
+ (*ref)->fix_after_pullout(new_parent, ref, merge);
if (get_depended_from() == new_parent)
depended_from= NULL;
}
diff --git a/sql/item.h b/sql/item.h
index d6b505b67b8..0470183faad 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -706,7 +706,9 @@ public:
Fix after some tables has been pulled out. Basically re-calculate all
attributes that are dependent on the tables.
*/
- virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {};
+ virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref,
+ bool merge)
+ {};
/*
This method should be used in case where we are sure that we do not need
@@ -2256,7 +2258,7 @@ public:
bool send(Protocol *protocol, String *str_arg);
void reset_field(Field *f);
bool fix_fields(THD *, Item **);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
void make_field(Send_field *tmp_field);
int save_in_field(Field *field,bool no_conversions);
void save_org_in_field(Field *field, fast_field_copier optimizer_data);
@@ -3410,7 +3412,7 @@ public:
bool send(Protocol *prot, String *tmp);
void make_field(Send_field *field);
bool fix_fields(THD *, Item **);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
int save_in_field(Field *field, bool no_conversions);
void save_org_in_field(Field *field, fast_field_copier optimizer_data);
fast_field_copier setup_fast_field_copier(Field *field)
@@ -3664,9 +3666,9 @@ public:
Item *it= ((Item *) item)->real_item();
return orig_item->eq(it, binary_cmp);
}
- void fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+ void fix_after_pullout(st_select_lex *new_parent, Item **refptr, bool merge)
{
- orig_item->fix_after_pullout(new_parent, &orig_item);
+ orig_item->fix_after_pullout(new_parent, &orig_item, merge);
}
int save_in_field(Field *to, bool no_conversions);
enum Item_result result_type () const { return orig_item->result_type(); }
@@ -3924,7 +3926,7 @@ public:
outer_ref->save_org_in_field(result_field, NULL);
}
bool fix_fields(THD *, Item **);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
table_map used_tables() const
{
return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 0134f628c80..b82e5e7f9f0 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1442,10 +1442,11 @@ bool Item_in_optimizer::is_top_level_item()
}
-void Item_in_optimizer::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_in_optimizer::fix_after_pullout(st_select_lex *new_parent,
+ Item **ref, bool merge)
{
/* This will re-calculate attributes of our Item_in_subselect: */
- Item_bool_func::fix_after_pullout(new_parent, ref);
+ Item_bool_func::fix_after_pullout(new_parent, ref, merge);
/* Then, re-calculate not_null_tables_cache: */
eval_not_null_tables(NULL);
@@ -2288,10 +2289,11 @@ bool Item_func_between::count_sargable_conds(uchar *arg)
}
-void Item_func_between::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func_between::fix_after_pullout(st_select_lex *new_parent,
+ Item **ref, bool merge)
{
/* This will re-calculate attributes of the arguments */
- Item_func_opt_neg::fix_after_pullout(new_parent, ref);
+ Item_func_opt_neg::fix_after_pullout(new_parent, ref, merge);
/* Then, re-calculate not_null_tables_cache according to our special rules */
eval_not_null_tables(NULL);
}
@@ -2681,10 +2683,11 @@ Item_func_if::eval_not_null_tables(uchar *opt_arg)
}
-void Item_func_if::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func_if::fix_after_pullout(st_select_lex *new_parent,
+ Item **ref, bool merge)
{
/* This will re-calculate attributes of the arguments */
- Item_func::fix_after_pullout(new_parent, ref);
+ Item_func::fix_after_pullout(new_parent, ref, merge);
/* Then, re-calculate not_null_tables_cache according to our special rules */
eval_not_null_tables(NULL);
}
@@ -4010,10 +4013,11 @@ Item_func_in::eval_not_null_tables(uchar *opt_arg)
}
-void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+ bool merge)
{
/* This will re-calculate attributes of the arguments */
- Item_func_opt_neg::fix_after_pullout(new_parent, ref);
+ Item_func_opt_neg::fix_after_pullout(new_parent, ref, merge);
/* Then, re-calculate not_null_tables_cache according to our special rules */
eval_not_null_tables(NULL);
}
@@ -4497,7 +4501,8 @@ Item_cond::eval_not_null_tables(uchar *opt_arg)
}
-void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+ bool merge)
{
List_iterator<Item> li(list);
Item *item;
@@ -4511,7 +4516,7 @@ void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
while ((item=li++))
{
table_map tmp_table_map;
- item->fix_after_pullout(new_parent, li.ref());
+ item->fix_after_pullout(new_parent, li.ref(), merge);
item= *li.ref();
used_tables_cache|= item->used_tables();
const_item_cache&= item->const_item();
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index ba0af32d76f..179ea10e5ef 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -272,7 +272,7 @@ public:
virtual void get_cache_parameters(List<Item> &parameters);
bool is_top_level_item();
bool eval_not_null_tables(uchar *opt_arg);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
bool invisible_mode();
void reset_cache() { cache= NULL; }
};
@@ -689,7 +689,7 @@ public:
virtual void print(String *str, enum_query_type query_type);
CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
bool eval_not_null_tables(uchar *opt_arg);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
bool count_sargable_conds(uchar *arg);
};
@@ -791,7 +791,7 @@ public:
uint decimal_precision() const;
const char *func_name() const { return "if"; }
bool eval_not_null_tables(uchar *opt_arg);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
private:
void cache_type_info(Item *source);
};
@@ -1337,7 +1337,7 @@ public:
bool nulls_in_row();
CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
bool eval_not_null_tables(uchar *opt_arg);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
};
class cmp_item_row :public cmp_item
@@ -1729,7 +1729,7 @@ public:
list.concat(nlist);
}
bool fix_fields(THD *, Item **ref);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
enum Type type() const { return COND_ITEM; }
List<Item>* argument_list() { return &list; }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 0ca8f700bfc..458db430222 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -265,7 +265,8 @@ Item_func::eval_not_null_tables(uchar *opt_arg)
}
-void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+ bool merge)
{
Item **arg,**arg_end;
@@ -276,7 +277,7 @@ void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
{
for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
{
- (*arg)->fix_after_pullout(new_parent, arg);
+ (*arg)->fix_after_pullout(new_parent, arg, merge);
Item *item= *arg;
used_tables_cache|= item->used_tables();
diff --git a/sql/item_func.h b/sql/item_func.h
index 5db92b6f3fe..7dea193c99b 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -143,7 +143,7 @@ public:
// Constructor used for Item_cond_and/or (see Item comment)
Item_func(THD *thd, Item_func *item);
bool fix_fields(THD *, Item **ref);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
void quick_fix_field();
table_map used_tables() const;
table_map not_null_tables() const;
diff --git a/sql/item_row.cc b/sql/item_row.cc
index b38cffd1ad5..c07ea612850 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -184,14 +184,15 @@ void Item_row::update_used_tables()
}
-void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+ bool merge)
{
used_tables_cache= 0;
const_item_cache= 1;
not_null_tables_cache= 0;
for (uint i= 0; i < arg_count; i++)
{
- items[i]->fix_after_pullout(new_parent, &items[i]);
+ items[i]->fix_after_pullout(new_parent, &items[i], merge);
used_tables_cache|= items[i]->used_tables();
const_item_cache&= items[i]->const_item();
not_null_tables_cache|= items[i]->not_null_tables();
diff --git a/sql/item_row.h b/sql/item_row.h
index 4d5c20711b5..ae9599baa57 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -65,7 +65,7 @@ public:
return 0;
};
bool fix_fields(THD *thd, Item **ref);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
void cleanup();
void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields);
table_map used_tables() const { return used_tables_cache; };
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index a215092f30f..eb1e1a3d9b7 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -446,7 +446,8 @@ bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select,
OUTER_REF_TABLE_BIT.
*/
-void Item_subselect::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_subselect::fix_after_pullout(st_select_lex *new_parent,
+ Item **ref, bool merge)
{
recalc_used_tables(new_parent, TRUE);
parent_select= new_parent;
@@ -1110,7 +1111,8 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
/*
as far as we moved content to upper level we have to fix dependences & Co
*/
- substitution->fix_after_pullout(select_lex->outer_select(), &substitution);
+ substitution->fix_after_pullout(select_lex->outer_select(),
+ &substitution, TRUE);
}
DBUG_RETURN(false);
}
@@ -2855,7 +2857,7 @@ bool Item_exists_subselect::exists2in_processor(uchar *opt_arg)
goto out;
}
}
- outer_exp->fix_after_pullout(unit->outer_select(), &outer_exp);
+ outer_exp->fix_after_pullout(unit->outer_select(), &outer_exp, FALSE);
outer_exp->update_used_tables();
outer.push_back(outer_exp);
}
@@ -3228,10 +3230,11 @@ err:
}
-void Item_in_subselect::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_in_subselect::fix_after_pullout(st_select_lex *new_parent,
+ Item **ref, bool merge)
{
- left_expr->fix_after_pullout(new_parent, &left_expr);
- Item_subselect::fix_after_pullout(new_parent, ref);
+ left_expr->fix_after_pullout(new_parent, &left_expr, merge);
+ Item_subselect::fix_after_pullout(new_parent, ref, merge);
used_tables_cache |= left_expr->used_tables();
}
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index ad43215e437..769be7ee925 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -172,7 +172,7 @@ public:
}
bool fix_fields(THD *thd, Item **ref);
bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item);
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
void recalc_used_tables(st_select_lex *new_parent, bool after_pullout);
virtual bool exec();
/*
@@ -608,7 +608,7 @@ public:
virtual void print(String *str, enum_query_type query_type);
bool fix_fields(THD *thd, Item **ref);
void fix_length_and_dec();
- void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
bool const_item() const
{
return Item_subselect::const_item() && left_expr->const_item();
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 6bf122fac5a..3ed55c2b9c8 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1794,7 +1794,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
Walk through sj nest's WHERE and ON expressions and call
item->fix_table_changes() for all items.
*/
- sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
+ sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr,
+ TRUE);
fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
@@ -1953,7 +1954,7 @@ static bool convert_subq_to_jtbm(JOIN *parent_join,
DBUG_ASSERT(parent_join->table_count < MAX_TABLES);
Item *conds= hash_sj_engine->semi_join_conds;
- conds->fix_after_pullout(parent_lex, &conds);
+ conds->fix_after_pullout(parent_lex, &conds, TRUE);
DBUG_EXECUTE("where", print_where(conds,"SJ-EXPR", QT_ORDINARY););
@@ -2005,7 +2006,7 @@ void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
while ((table= it++))
{
if (table->on_expr)
- table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
+ table->on_expr->fix_after_pullout(new_parent, &table->on_expr, TRUE);
if (table->nested_join)
fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
}
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 4578bcec394..0aedf5084a9 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -466,7 +466,8 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
// Update used tables cache according to new table map
if (derived->on_expr)
{
- derived->on_expr->fix_after_pullout(parent_lex, &derived->on_expr);
+ derived->on_expr->fix_after_pullout(parent_lex, &derived->on_expr,
+ TRUE);
fix_list_after_tbl_changes(parent_lex, &derived->nested_join->join_list);
}
}
@@ -636,7 +637,8 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
SELECT_LEX_UNIT *unit= derived->get_unit();
DBUG_ENTER("mysql_derived_prepare");
bool res= FALSE;
- DBUG_PRINT("enter", ("unit 0x%lx", (ulong) unit));
+ DBUG_PRINT("enter", ("unit: %p table_list: %p Alias '%s'",
+ unit, derived, derived->alias));
// Skip already prepared views/DT
if (!unit || unit->prepared ||
View Lorry Controller Status