3328 Roy Lyseng 2011-01-24
Bug#57525: Semijoin transformed subquery with inner grouped subquery
gives empty result.
This is a followup to bug#31480, which attempted to fix resolved
information in subqueries as part of semijoin transformation.
The specific problem here is that the inner subquery (which is not
transformed) contains an outer reference to the outer-most block in
its HAVING clause. The HAVING clause is represented by Item_ref
objects, and these are not re-resolved properly through
fix_after_pullout().
The solution lies in realizing that Item_ref objects contain a
complete set of resolution data structures (ie depended_from,
name resolution context), and that it may point to an Item object
with it's own set of resolution data structures.
Hence, we implement fix_after_pullout() for Item_ref by first
calling fix_after_pullout() on the referenced object and then for
itself.
The above fix revealed another problem: The offending query failed
in prepared statement mode. The reason is that the depended_from
field in Item_ref is reset in cleanup() and not restored at the
next fix_fields() call. The problem is fixed by not resetting the
depended_from flag. This is an acceptable solution, as long as
transformations are run only on the first execution of a query.
There is also another followup to bug#31480:
A - t1
\
B - t2
\
C - t3
\
D - t4
where t1.x=t4.y
The above figure describes an outer query expression (select_lex A)
with 3 nested subqueries represented by select_lex B, C and D.
The innermost subquery (D) contains a reference to a table t1
in the outermost block (A).
The original resolver marked the subquery containing query expression
B as using table t1, and the subqueries containing query expressions
C and D with OUTER_REF_TABLE_BIT.
The code in bug#31480 failed to preserve all outer references
when calling fix_after_pullout().
mysql-test/r/optimizer_switch.result
Updated with correct result and plan for query that exposed bug.
mysql-test/t/optimizer_switch.test
Warnings about wrong results removed.
sql/item.cc
A common implementation of fix_after_pullout() for all Item_ref
classes has been made. The exception is Item_outer_ref which we
still have no test case for.
Item_ref::fix_after_pullout() now calls fix_after_pullout() on the
referenced item, and then it calls Item_ident::fix_after_pullout()
on itself.
Item_field::fix_after_pullout() is also moved to class Item_ident,
which is parent class for both Item_field and Item_ref.
A new function Item_ref::resolved_used_tables() was needed.
sql/item.h
Some adjustments to function prototypes, see sql/item.cc.
sql/item_subselect.h
One changed friend declaration.
modified:
mysql-test/r/optimizer_switch.result
mysql-test/t/optimizer_switch.test
sql/item.cc
sql/item.h
sql/item_subselect.h
3327 Roy Lyseng 2011-01-24
Bug#57431: subquery returns wrong result (semijoin=on) with pred AND
The test case contains an outer query with a single table and an
IN subquery with two outerjoined tables. The MaterializeScan
semijoin strategy is selected, meaning that an outerjoin operation
is first performed over join_tabs 0 and 1. After the outerjoin,
sub_select_sjm() is called to perform a semijoin between the
result of the outerjoin in join_tab 1 and the outer table in
join_tab 2. However, join_tab 1 still contains some reminiscent data
from the outerjoin operation, hence this operation also behaves like
an outerjoin.
Fixed by clearing the outerjoin information from the join_tab that
represents the result of the outerjoin operation.
mysql-test/include/subquery_sj_innodb.inc
Test case for Bug#57431. The problem can only be reproduced with
1 row in the inner table, hence Innodb is needed to prevent
'const table' optimization.
mysql-test/r/subquery_sj_innodb_all.result mysql-test/r/subquery_sj_innodb_all_jcl6.result
mysql-test/r/subquery_sj_innodb_all_jcl7.result
mysql-test/r/subquery_sj_innodb_none.result
mysql-test/r/subquery_sj_innodb_none_jcl6.result
mysql-test/r/subquery_sj_innodb_none_jcl7.result
Updated test results for Bug#57431.
sql/sql_select.cc
In sub_select_sjm(), clear the last_inner field of the first
join_tab to take part in the semijoin operation.
modified:
mysql-test/include/subquery_sj_innodb.inc
mysql-test/r/subquery_sj_innodb_all.result
mysql-test/r/subquery_sj_innodb_all_jcl6.result
mysql-test/r/subquery_sj_innodb_all_jcl7.result
mysql-test/r/subquery_sj_innodb_none.result
mysql-test/r/subquery_sj_innodb_none_jcl6.result
mysql-test/r/subquery_sj_innodb_none_jcl7.result
sql/sql_select.cc
=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result 2010-11-30 18:16:43 +0000
+++ b/mysql-test/r/optimizer_switch.result 2011-01-24 14:17:03 +0000
@@ -293,14 +293,13 @@ a b
3 20
2 30
set @@optimizer_switch='materialization=off,semijoin=on';
-# The query result with semijoin is WRONG
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
GROUP BY f
HAVING ta.a=tc.f));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary
+1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary
1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, incremental buffers)
3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
SELECT * FROM t1 AS ta
@@ -309,6 +308,10 @@ WHERE tb.d >= SOME(SELECT SUM(g) FROM t4
GROUP BY f
HAVING ta.a=tc.f));
a b
+2 10
+2 20
+3 20
+2 30
# Subquery with ORDER BY and LIMIT
set @@optimizer_switch='materialization=off,semijoin=off';
# NOTE: The ordered subquery should have a LIMIT clause to make sense
@@ -332,7 +335,6 @@ a b
2 30
4 40
set @@optimizer_switch='materialization=off,semijoin=on';
-# The query result with semijoin is WRONG
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d IN (SELECT g FROM t4 as tc
=== modified file 'mysql-test/t/optimizer_switch.test'
--- a/mysql-test/t/optimizer_switch.test 2010-10-15 10:32:50 +0000
+++ b/mysql-test/t/optimizer_switch.test 2011-01-24 14:17:03 +0000
@@ -280,8 +280,6 @@ eval $query;
set @@optimizer_switch='materialization=off,semijoin=on';
---echo # The query result with semijoin is WRONG
-
eval EXPLAIN $query;
eval $query;
@@ -303,8 +301,6 @@ eval $query;
set @@optimizer_switch='materialization=off,semijoin=on';
---echo # The query result with semijoin is WRONG
-
eval EXPLAIN $query;
eval $query;
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2011-01-12 17:24:53 +0000
+++ b/sql/item.cc 2011-01-24 14:17:03 +0000
@@ -649,7 +649,6 @@ void Item_ident::cleanup()
db_name= orig_db_name;
table_name= orig_table_name;
field_name= orig_field_name;
- depended_from= 0;
DBUG_VOID_RETURN;
}
@@ -2310,7 +2309,7 @@ table_map Item_field::resolved_used_tabl
return field->table->map;
}
-void Item_field::fix_after_pullout(st_select_lex *parent_select,
+void Item_ident::fix_after_pullout(st_select_lex *parent_select,
st_select_lex *removed_select,
Item **ref)
{
@@ -2350,17 +2349,16 @@ void Item_field::fix_after_pullout(st_se
*/
st_select_lex *child_select= context->select_lex;
- if (child_select->outer_select() != depended_from)
+ while (child_select->outer_select() != depended_from)
{
/*
The subquery on this level is outer-correlated with respect to the field
*/
Item_subselect *subq_predicate= child_select->master_unit()->item;
- subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
- }
- while (child_select->outer_select() != depended_from)
+ subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT;
child_select= child_select->outer_select();
+ }
/*
child_select is select_lex immediately inner to the depended_from level.
@@ -6509,6 +6507,13 @@ void Item_ref::set_properties()
}
+table_map Item_ref::resolved_used_tables() const
+{
+ DBUG_ASSERT((*ref)->type() == FIELD_ITEM);
+ return ((Item_field*)(*ref))->resolved_used_tables();
+}
+
+
void Item_ref::cleanup()
{
DBUG_ENTER("Item_ref::cleanup");
@@ -6885,29 +6890,12 @@ void Item_ref::fix_after_pullout(st_sele
st_select_lex *removed_select,
Item **ref_arg)
{
- // @todo: Find an actual test case where depended_from == new_parent.
- DBUG_ASSERT(depended_from != parent_select);
- if (depended_from == parent_select)
- depended_from= NULL;
-}
-
-void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select,
- st_select_lex *removed_select,
- Item **refptr)
-{
- DBUG_EXECUTE("where",
- print_where(*refptr,
- "Item_direct_view_ref::fix_after_pullout",
- QT_ORDINARY););
-
(*ref)->fix_after_pullout(parent_select, removed_select, ref);
- // @todo: Find an actual test case where depended_from == parent_select.
- DBUG_ASSERT(depended_from != parent_select);
- if (depended_from == parent_select)
- depended_from= NULL;
+ Item_ident::fix_after_pullout(parent_select, removed_select, ref_arg);
}
+
/**
Compare two view column references for equality.
=== modified file 'sql/item.h'
--- a/sql/item.h 2011-01-10 16:37:47 +0000
+++ b/sql/item.h 2011-01-24 14:17:03 +0000
@@ -1675,7 +1675,13 @@ public:
const char *field_name_arg);
Item_ident(THD *thd, Item_ident *item);
Item_ident(TABLE_LIST *view_arg, const char *field_name_arg);
+ /*
+ Return used table information for the level on which this table is resolved.
+ */
+ virtual table_map resolved_used_tables() const= 0;
const char *full_name() const;
+ virtual void fix_after_pullout(st_select_lex *parent_select,
+ st_select_lex *removed_select, Item **ref);
void cleanup();
bool remove_dependence_processor(uchar * arg);
virtual void print(String *str, enum_query_type query_type);
@@ -1761,16 +1767,11 @@ 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 *parent_select,
- st_select_lex *removed_select, Item **ref);
void make_field(Send_field *tmp_field);
int save_in_field(Field *field,bool no_conversions);
void save_org_in_field(Field *field);
table_map used_tables() const;
- /*
- Return used table information for the level on which this table is resolved.
- */
- table_map resolved_used_tables() const;
+ virtual table_map resolved_used_tables() const;
enum Item_result result_type () const
{
return field->result_type();
@@ -2604,6 +2605,7 @@ public:
if (!depended_from)
(*ref)->update_used_tables();
}
+ virtual table_map resolved_used_tables() const;
table_map not_null_tables() const { return (*ref)->not_null_tables(); }
void set_result_field(Field *field) { result_field= field; }
bool is_result_field() { return 1; }
@@ -2726,8 +2728,6 @@ public:
{}
bool fix_fields(THD *, Item **);
- void fix_after_pullout(st_select_lex *parent_select,
- st_select_lex *removed_select, Item **ref);
bool eq(const Item *item, bool binary_cmp) const;
Item *get_tmp_table_item(THD *thd)
{
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2010-12-14 11:15:13 +0000
+++ b/sql/item_subselect.h 2011-01-24 14:17:03 +0000
@@ -171,7 +171,7 @@ public:
friend bool Item_field::fix_fields(THD *, Item **);
friend int Item_field::fix_outer_field(THD *, Field **, Item **);
friend bool Item_ref::fix_fields(THD *, Item **);
- friend void Item_field::fix_after_pullout(st_select_lex *parent_select,
+ friend void Item_ident::fix_after_pullout(st_select_lex *parent_select,
st_select_lex *removed_select,
Item **ref);
friend void mark_select_range_as_dependent(THD*,
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3327 to 3328) Bug#57525 | Roy Lyseng | 24 Jan |
