Skip to content

Commit 9581921

Browse files
roylysengroylyseng
committed
Bug#17600176: This query returns a row in 5.5 but not 5.6 or current 5.7
The problem query contains an IN subquery that is transformed to a semi-join. The subquery contains an outer join operation. When run standalone, the outer join in the subquery is preserved as an outer join and returns one row. However, when the full query is run, the outer join is converted into an inner join, and the subquery no longer returns any row. This causes the outer query to return no rows. The problem is with the IF clause in the WHERE clause of the subquery, When transforming into semi-join, we rely on Item::fix_after_pullout() to adjust used_tables and not_null_tables information within the condition objects, in order to determine e.g. the outer join to inner join transform. However, there is no specific implementation of ::fix_after_pullout() for the IF clause, so it returns generic information generated by Item_func::fix_after_pullout(). The fix is to add this function. By analysis, this appears to be a problem for BETWEEN predicates and IN predicates too. A specific implementation of :;fix_after_pullout() is added for both classes. In addition, it was detected that not_null_tables information was not updated correctly for class Item_row. However, I was not able to think out any failing test for this problem, so no test case was added.
1 parent 781df1f commit 9581921

3 files changed

Lines changed: 64 additions & 14 deletions

File tree

‎sql/item_cmpfunc.cc‎

Lines changed: 54 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -2481,11 +2481,11 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref)
24812481

24822482
thd->lex->current_select->between_count++;
24832483

2484-
/* not_null_tables_cache == union(T1(e),T1(e1),T1(e2)) */
2484+
// not_null_tables_cache == union(T1(e),T1(e1),T1(e2))
24852485
if (pred_level && !negated)
24862486
return 0;
24872487

2488-
/* not_null_tables_cache == union(T1(e), intersection(T1(e1),T1(e2))) */
2488+
// not_null_tables_cache == union(T1(e), intersection(T1(e1),T1(e2)))
24892489
not_null_tables_cache= (args[0]->not_null_tables() |
24902490
(args[1]->not_null_tables() &
24912491
args[2]->not_null_tables()));
@@ -2494,6 +2494,22 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref)
24942494
}
24952495

24962496

2497+
void Item_func_between::fix_after_pullout(st_select_lex *parent_select,
2498+
st_select_lex *removed_select)
2499+
{
2500+
Item_func_opt_neg::fix_after_pullout(parent_select, removed_select);
2501+
2502+
// not_null_tables_cache == union(T1(e),T1(e1),T1(e2))
2503+
if (pred_level && !negated)
2504+
return;
2505+
2506+
// not_null_tables_cache == union(T1(e), intersection(T1(e1),T1(e2)))
2507+
not_null_tables_cache= args[0]->not_null_tables() |
2508+
(args[1]->not_null_tables() &
2509+
args[2]->not_null_tables());
2510+
}
2511+
2512+
24972513
void Item_func_between::fix_length_and_dec()
24982514
{
24992515
max_length= 1;
@@ -2937,6 +2953,15 @@ Item_func_if::fix_fields(THD *thd, Item **ref)
29372953
}
29382954

29392955

2956+
void Item_func_if::fix_after_pullout(st_select_lex *parent_select,
2957+
st_select_lex *removed_select)
2958+
{
2959+
Item_func::fix_after_pullout(parent_select, removed_select);
2960+
2961+
not_null_tables_cache= (args[1]->not_null_tables() &
2962+
args[2]->not_null_tables());
2963+
}
2964+
29402965
void Item_func_if::cache_type_info(Item *source)
29412966
{
29422967
collation.set(source->collation);
@@ -4332,24 +4357,41 @@ bool Item_func_in::nulls_in_row()
43324357
1 got error
43334358
*/
43344359

4335-
bool
4336-
Item_func_in::fix_fields(THD *thd, Item **ref)
4360+
bool Item_func_in::fix_fields(THD *thd, Item **ref)
43374361
{
4338-
Item **arg, **arg_end;
4339-
43404362
if (Item_func_opt_neg::fix_fields(thd, ref))
4341-
return 1;
4363+
return true;
43424364

4343-
/* not_null_tables_cache == union(T1(e),union(T1(ei))) */
4365+
// not_null_tables_cache == union(T1(e),union(T1(ei)))
43444366
if (pred_level && negated)
4345-
return 0;
4367+
return false;
43464368

4347-
/* not_null_tables_cache = union(T1(e),intersection(T1(ei))) */
4369+
// not_null_tables_cache = union(T1(e),intersection(T1(ei)))
43484370
not_null_tables_cache= ~(table_map) 0;
4349-
for (arg= args + 1, arg_end= args + arg_count; arg != arg_end; arg++)
4371+
Item **arg_end= args + arg_count;
4372+
for (Item **arg= args + 1; arg != arg_end; arg++)
4373+
not_null_tables_cache&= (*arg)->not_null_tables();
4374+
not_null_tables_cache|= (*args)->not_null_tables();
4375+
4376+
return false;
4377+
}
4378+
4379+
4380+
void Item_func_in::fix_after_pullout(st_select_lex *parent_select,
4381+
st_select_lex *removed_select)
4382+
{
4383+
Item_func_opt_neg::fix_after_pullout(parent_select, removed_select);
4384+
4385+
// not_null_tables_cache == union(T1(e),union(T1(ei)))
4386+
if (pred_level && negated)
4387+
return;
4388+
4389+
// not_null_tables_cache = union(T1(e),intersection(T1(ei)))
4390+
not_null_tables_cache= ~(table_map) 0;
4391+
Item **arg_end= args + arg_count;
4392+
for (Item **arg= args + 1; arg != arg_end; arg++)
43504393
not_null_tables_cache&= (*arg)->not_null_tables();
43514394
not_null_tables_cache|= (*args)->not_null_tables();
4352-
return 0;
43534395
}
43544396

43554397

‎sql/item_cmpfunc.h‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -753,6 +753,8 @@ class Item_func_between :public Item_func_opt_neg
753753
enum Functype functype() const { return BETWEEN; }
754754
const char *func_name() const { return "between"; }
755755
bool fix_fields(THD *, Item **);
756+
void fix_after_pullout(st_select_lex *parent_select,
757+
st_select_lex *removed_select);
756758
void fix_length_and_dec();
757759
virtual void print(String *str, enum_query_type query_type);
758760
bool is_bool_func() { return 1; }
@@ -865,6 +867,8 @@ class Item_func_if :public Item_func
865867
enum_field_types field_type() const { return cached_field_type; }
866868
bool fix_fields(THD *, Item **);
867869
void fix_length_and_dec();
870+
void fix_after_pullout(st_select_lex *parent_select,
871+
st_select_lex *removed_select);
868872
uint decimal_precision() const;
869873
const char *func_name() const { return "if"; }
870874
private:
@@ -1410,6 +1414,8 @@ class Item_func_in :public Item_func_opt_neg
14101414
}
14111415
longlong val_int();
14121416
bool fix_fields(THD *, Item **);
1417+
void fix_after_pullout(st_select_lex *parent_select,
1418+
st_select_lex *removed_select);
14131419
void fix_length_and_dec();
14141420
uint decimal_precision() const { return 1; }
14151421
void cleanup()

‎sql/item_row.cc‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -126,7 +126,7 @@ void Item_row::split_sum_func(THD *thd, Ref_ptr_array ref_pointer_array,
126126
void Item_row::update_used_tables()
127127
{
128128
used_tables_cache= 0;
129-
const_item_cache= 1;
129+
const_item_cache= true;
130130
with_subselect= false;
131131
with_stored_program= false;
132132
for (uint i= 0; i < arg_count; i++)
@@ -143,11 +143,13 @@ void Item_row::fix_after_pullout(st_select_lex *parent_select,
143143
st_select_lex *removed_select)
144144
{
145145
used_tables_cache= 0;
146-
const_item_cache= 1;
146+
not_null_tables_cache= 0;
147+
const_item_cache= true;
147148
for (uint i= 0; i < arg_count; i++)
148149
{
149150
items[i]->fix_after_pullout(parent_select, removed_select);
150151
used_tables_cache|= items[i]->used_tables();
152+
not_null_tables_cache|= items[i]->not_null_tables();
151153
const_item_cache&= items[i]->const_item();
152154
}
153155
}

0 commit comments

Comments
 (0)