1
/* Copyright (C) 2000 MySQL AB
3
This program is free software; you can redistribute it and/or modify
4
it under the terms of the GNU General Public License as published by
5
the Free Software Foundation; version 2 of the License.
7
This program is distributed in the hope that it will be useful,
8
but WITHOUT ANY WARRANTY; without even the implied warranty of
9
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
GNU General Public License for more details.
12
You should have received a copy of the GNU General Public License
13
along with this program; if not, write to the Free Software
14
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
23
- add function from mysql_select that use JOIN* as parameter to JOIN
24
methods (sql_select.h/sql_select.cc)
27
#ifdef USE_PRAGMA_IMPLEMENTATION
28
#pragma implementation // gcc: Class implementation
31
#include "mysql_priv.h"
32
#include "sql_select.h"
34
inline Item * and_items(Item* cond, Item *item)
36
return (cond? (new Item_cond_and(cond, item)) : item);
39
Item_subselect::Item_subselect():
40
Item_result_field(), value_assigned(0), thd(0), substitution(0),
41
engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
42
const_item_cache(1), engine_changed(0), changed(0),
48
Item value is NULL if select_result_interceptor didn't change this value
49
(i.e. some rows will be found returned)
55
void Item_subselect::init(st_select_lex *select_lex,
56
select_result_interceptor *result)
59
Please see Item_singlerow_subselect::invalidate_and_restore_select_lex(),
60
which depends on alterations to the parse tree implemented here.
63
DBUG_ENTER("Item_subselect::init");
64
DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
65
unit= select_lex->master_unit();
70
Item can be changed in JOIN::prepare while engine in JOIN::optimize
71
=> we do not copy old_engine here
73
engine= unit->item->engine;
74
parsing_place= unit->item->parsing_place;
75
unit->item->engine= 0;
77
engine->change_result(this, result);
81
SELECT_LEX *outer_select= unit->outer_select();
83
do not take into account expression inside aggregate functions because
84
they can access original table fields
86
parsing_place= (outer_select->in_sum_expr ?
88
outer_select->parsing_place);
90
engine= new subselect_union_engine(unit, result, this);
92
engine= new subselect_single_select_engine(select_lex, result, this);
95
SELECT_LEX *upper= unit->outer_select();
96
if (upper->parsing_place == IN_HAVING)
97
upper->subquery_in_having= 1;
103
Item_subselect::get_select_lex()
105
return unit->first_select();
108
void Item_subselect::cleanup()
110
DBUG_ENTER("Item_subselect::cleanup");
111
Item_result_field::cleanup();
126
void Item_singlerow_subselect::cleanup()
128
DBUG_ENTER("Item_singlerow_subselect::cleanup");
130
Item_subselect::cleanup();
135
void Item_in_subselect::cleanup()
137
DBUG_ENTER("Item_in_subselect::cleanup");
140
left_expr_cache->delete_elements();
141
delete left_expr_cache;
142
left_expr_cache= NULL;
144
first_execution= TRUE;
145
Item_subselect::cleanup();
149
Item_subselect::~Item_subselect()
154
Item_subselect::trans_res
155
Item_subselect::select_transformer(JOIN *join)
157
DBUG_ENTER("Item_subselect::select_transformer");
162
bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
164
char const *save_where= thd_param->where;
168
DBUG_ASSERT(fixed == 0);
169
engine->set_thd((thd= thd_param));
171
if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
174
res= engine->prepare();
176
// all transformation is done (used by prepared statements)
182
Substitute the current item with an Item_in_optimizer that was
183
created by Item_in_subselect::select_in_like_transformer and
184
call fix_fields for the substituted item which in turn calls
185
engine->prepare for the subquery predicate.
191
// did we changed top item of WHERE condition
192
if (unit->outer_select()->where == (*ref))
193
unit->outer_select()->where= substitution; // correct WHERE for PS
194
else if (unit->outer_select()->having == (*ref))
195
unit->outer_select()->having= substitution; // correct HAVING for PS
197
(*ref)= substitution;
198
substitution->name= name;
199
if (have_to_be_excluded)
202
thd->where= "checking transformed subquery";
204
ret= (*ref)->fix_fields(thd, ref);
205
thd->where= save_where;
208
// Is it one field subselect?
209
if (engine->cols() > max_columns)
211
my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
214
fix_length_and_dec();
219
if ((uncacheable= engine->uncacheable()))
222
if (uncacheable & UNCACHEABLE_RAND)
223
used_tables_cache|= RAND_TABLE_BIT;
228
thd->where= save_where;
233
bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
239
for (SELECT_LEX *lex= unit->first_select(); lex; lex= lex->next_select())
241
List_iterator<Item> li(lex->item_list);
245
if (lex->where && (lex->where)->walk(processor, walk_subquery, argument))
247
if (lex->having && (lex->having)->walk(processor, walk_subquery,
253
if (item->walk(processor, walk_subquery, argument))
256
for (order= (ORDER*) lex->order_list.first ; order; order= order->next)
258
if ((*order->item)->walk(processor, walk_subquery, argument))
261
for (order= (ORDER*) lex->group_list.first ; order; order= order->next)
263
if ((*order->item)->walk(processor, walk_subquery, argument))
268
return (this->*processor)(argument);
272
bool Item_subselect::exec()
277
/* Do not execute subselect in case of a fatal error */
292
Compute the IN predicate if the left operand's cache changed.
295
bool Item_in_subselect::exec()
297
DBUG_ENTER("Item_in_subselect::exec");
298
DBUG_ASSERT(exec_method != MATERIALIZATION ||
299
(exec_method == MATERIALIZATION &&
300
engine->engine_type() == subselect_engine::HASH_SJ_ENGINE));
302
Initialize the cache of the left predicate operand. This has to be done as
303
late as now, because Cached_item directly contains a resolved field (not
304
an item, and in some cases (when temp tables are created), these fields
305
end up pointing to the wrong field. One solution is to change Cached_item
306
to not resolve its field upon creation, but to resolve it dynamically
307
from a given Item_ref object.
308
TODO: the cache should be applied conditionally based on:
309
- rules - e.g. only if the left operand is known to be ordered, and/or
310
- on a cost-based basis, that takes into account the cost of a cache
311
lookup, the cache hit rate, and the savings per cache hit.
313
if (!left_expr_cache && exec_method == MATERIALIZATION)
314
init_left_expr_cache();
316
/* If the new left operand is already in the cache, reuse the old result. */
317
if (left_expr_cache && test_if_item_cache_changed(*left_expr_cache) < 0)
319
/* Always compute IN for the first row as the cache is not valid for it. */
320
if (!first_execution)
322
first_execution= FALSE;
326
The exec() method below updates item::value, and item::null_value, thus if
327
we don't call it, the next call to item::val_int() will return whatever
328
result was computed by its previous call.
330
DBUG_RETURN(Item_subselect::exec());
334
Item::Type Item_subselect::type() const
336
return SUBSELECT_ITEM;
340
void Item_subselect::fix_length_and_dec()
342
engine->fix_length_and_dec(0);
346
table_map Item_subselect::used_tables() const
348
return (table_map) (engine->uncacheable() ? used_tables_cache : 0L);
352
bool Item_subselect::const_item() const
354
return const_item_cache;
357
Item *Item_subselect::get_tmp_table_item(THD *thd_arg)
359
if (!with_sum_func && !const_item())
360
return new Item_field(result_field);
361
return copy_or_same(thd_arg);
364
void Item_subselect::update_used_tables()
366
if (!engine->uncacheable())
368
// did all used tables become static?
369
if (!(used_tables_cache & ~engine->upper_select_const_tables()))
375
void Item_subselect::print(String *str, enum_query_type query_type)
378
engine->print(str, query_type);
383
Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex)
384
:Item_subselect(), value(0)
386
DBUG_ENTER("Item_singlerow_subselect::Item_singlerow_subselect");
387
init(select_lex, new select_singlerow_subselect(this));
389
max_columns= UINT_MAX;
394
Item_singlerow_subselect::invalidate_and_restore_select_lex()
396
DBUG_ENTER("Item_singlerow_subselect::invalidate_and_restore_select_lex");
397
st_select_lex *result= get_select_lex();
402
This code restore the parse tree in it's state before the execution of
403
Item_singlerow_subselect::Item_singlerow_subselect(),
404
and in particular decouples this object from the SELECT_LEX,
405
so that the SELECT_LEX can be used with a different flavor
406
or Item_subselect instead, as part of query rewriting.
413
Item_maxmin_subselect::Item_maxmin_subselect(THD *thd_param,
414
Item_subselect *parent,
415
st_select_lex *select_lex,
417
:Item_singlerow_subselect(), was_values(TRUE)
419
DBUG_ENTER("Item_maxmin_subselect::Item_maxmin_subselect");
421
init(select_lex, new select_max_min_finder_subselect(this, max_arg));
427
Following information was collected during performing fix_fields()
428
of Items belonged to subquery, which will be not repeated
430
used_tables_cache= parent->get_used_tables_cache();
431
const_item_cache= parent->get_const_item_cache();
434
this subquery always creates during preparation, so we can assign
442
void Item_maxmin_subselect::cleanup()
444
DBUG_ENTER("Item_maxmin_subselect::cleanup");
445
Item_singlerow_subselect::cleanup();
448
By default it is TRUE to avoid TRUE reporting by
449
Item_func_not_all/Item_func_nop_all if this item was never called.
451
Engine exec() set it to FALSE by reset_value_registration() call.
452
select_max_min_finder_subselect::send_data() set it back to TRUE if some
460
void Item_maxmin_subselect::print(String *str, enum_query_type query_type)
462
str->append(max?"<max>":"<min>", 5);
463
Item_singlerow_subselect::print(str, query_type);
467
void Item_singlerow_subselect::reset()
471
value->null_value= 1;
477
- We cant change name of Item_field or Item_ref, because it will
478
prevent it's correct resolving, but we should save name of
479
removed item => we do not make optimization if top item of
480
list is field or reference.
481
- switch off this optimization for prepare statement,
482
because we do not rollback this changes.
483
Make rollback for it, or special name resolving mode in 5.0.
485
Item_subselect::trans_res
486
Item_singlerow_subselect::select_transformer(JOIN *join)
488
DBUG_ENTER("Item_singlerow_subselect::select_transformer");
492
SELECT_LEX *select_lex= join->select_lex;
494
if (!select_lex->master_unit()->is_union() &&
495
!select_lex->table_list.elements &&
496
select_lex->item_list.elements == 1 &&
497
!select_lex->item_list.head()->with_sum_func &&
499
We cant change name of Item_field or Item_ref, because it will
500
prevent it's correct resolving, but we should save name of
501
removed item => we do not make optimization if top item of
502
list is field or reference.
503
TODO: solve above problem
505
!(select_lex->item_list.head()->type() == FIELD_ITEM ||
506
select_lex->item_list.head()->type() == REF_ITEM) &&
507
!join->conds && !join->having
511
have_to_be_excluded= 1;
512
if (thd->lex->describe)
514
char warn_buff[MYSQL_ERRMSG_SIZE];
515
sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
516
push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
517
ER_SELECT_REDUCED, warn_buff);
519
substitution= select_lex->item_list.head();
521
as far as we moved content to upper level, field which depend of
522
'upper' select is not really dependent => we remove this dependence
524
substitution->walk(&Item::remove_dependence_processor, 0,
525
(uchar *) select_lex->outer_select());
526
DBUG_RETURN(RES_REDUCE);
532
void Item_singlerow_subselect::store(uint i, Item *item)
537
enum Item_result Item_singlerow_subselect::result_type() const
539
return engine->type();
543
Don't rely on the result type to calculate field type.
544
Ask the engine instead.
546
enum_field_types Item_singlerow_subselect::field_type() const
548
return engine->field_type();
551
void Item_singlerow_subselect::fix_length_and_dec()
553
if ((max_columns= engine->cols()) == 1)
555
engine->fix_length_and_dec(row= &value);
559
if (!(row= (Item_cache**) sql_alloc(sizeof(Item_cache*)*max_columns)))
561
engine->fix_length_and_dec(row);
564
unsigned_flag= value->unsigned_flag;
566
If there are not tables in subquery then ability to have NULL value
567
depends on SELECT list (if single row subquery have tables then it
568
always can be NULL if there are not records fetched).
570
if (engine->no_tables())
571
maybe_null= engine->may_be_null();
574
uint Item_singlerow_subselect::cols()
576
return engine->cols();
579
bool Item_singlerow_subselect::check_cols(uint c)
581
if (c != engine->cols())
583
my_error(ER_OPERAND_COLUMNS, MYF(0), c);
589
bool Item_singlerow_subselect::null_inside()
591
for (uint i= 0; i < max_columns ; i++)
593
if (row[i]->null_value)
599
void Item_singlerow_subselect::bring_value()
604
double Item_singlerow_subselect::val_real()
606
DBUG_ASSERT(fixed == 1);
607
if (!exec() && !value->null_value)
610
return value->val_real();
619
longlong Item_singlerow_subselect::val_int()
621
DBUG_ASSERT(fixed == 1);
622
if (!exec() && !value->null_value)
625
return value->val_int();
634
String *Item_singlerow_subselect::val_str(String *str)
636
if (!exec() && !value->null_value)
639
return value->val_str(str);
649
my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
651
if (!exec() && !value->null_value)
654
return value->val_decimal(decimal_value);
664
bool Item_singlerow_subselect::val_bool()
666
if (!exec() && !value->null_value)
669
return value->val_bool();
679
Item_exists_subselect::Item_exists_subselect(st_select_lex *select_lex):
682
DBUG_ENTER("Item_exists_subselect::Item_exists_subselect");
684
init(select_lex, new select_exists_subselect(this));
685
max_columns= UINT_MAX;
686
null_value= 0; //can't be NULL
687
maybe_null= 0; //can't be NULL
693
void Item_exists_subselect::print(String *str, enum_query_type query_type)
695
str->append(STRING_WITH_LEN("exists"));
696
Item_subselect::print(str, query_type);
700
bool Item_in_subselect::test_limit(st_select_lex_unit *unit_arg)
702
if (unit_arg->fake_select_lex &&
703
unit_arg->fake_select_lex->test_limit())
706
SELECT_LEX *sl= unit_arg->first_select();
707
for (; sl; sl= sl->next_select())
709
if (sl->test_limit())
715
Item_in_subselect::Item_in_subselect(Item * left_exp,
716
st_select_lex *select_lex):
717
Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
718
optimizer(0), pushed_cond_guards(NULL), exec_method(NOT_TRANSFORMED),
721
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
723
init(select_lex, new select_exists_subselect(this));
724
max_columns= UINT_MAX;
728
//if test_limit will fail then error will be reported to client
729
test_limit(select_lex->master_unit());
733
Item_allany_subselect::Item_allany_subselect(Item * left_exp,
734
chooser_compare_func_creator fc,
735
st_select_lex *select_lex,
737
:Item_in_subselect(), func_creator(fc), all(all_arg)
739
DBUG_ENTER("Item_allany_subselect::Item_allany_subselect");
741
func= func_creator(all_arg);
742
init(select_lex, new select_exists_subselect(this));
746
//if test_limit will fail then error will be reported to client
747
test_limit(select_lex->master_unit());
752
void Item_exists_subselect::fix_length_and_dec()
756
max_columns= engine->cols();
757
/* We need only 1 row to determine existence */
758
unit->global_parameters->select_limit= new Item_int((int32) 1);
761
double Item_exists_subselect::val_real()
763
DBUG_ASSERT(fixed == 1);
769
return (double) value;
772
longlong Item_exists_subselect::val_int()
774
DBUG_ASSERT(fixed == 1);
783
String *Item_exists_subselect::val_str(String *str)
785
DBUG_ASSERT(fixed == 1);
791
str->set((ulonglong)value,&my_charset_bin);
796
my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
798
DBUG_ASSERT(fixed == 1);
804
int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
805
return decimal_value;
809
bool Item_exists_subselect::val_bool()
811
DBUG_ASSERT(fixed == 1);
821
double Item_in_subselect::val_real()
824
As far as Item_in_subselect called only from Item_in_optimizer this
825
method should not be used
828
DBUG_ASSERT(fixed == 1);
836
if (was_null && !value)
838
return (double) value;
842
longlong Item_in_subselect::val_int()
845
As far as Item_in_subselect called only from Item_in_optimizer this
846
method should not be used
849
DBUG_ASSERT(fixed == 1);
857
if (was_null && !value)
863
String *Item_in_subselect::val_str(String *str)
866
As far as Item_in_subselect called only from Item_in_optimizer this
867
method should not be used
870
DBUG_ASSERT(fixed == 1);
878
if (was_null && !value)
883
str->set((ulonglong)value, &my_charset_bin);
888
bool Item_in_subselect::val_bool()
890
DBUG_ASSERT(fixed == 1);
896
Must mark the IN predicate as NULL so as to make sure an enclosing NOT
897
predicate will return FALSE. See the comments in
898
subselect_uniquesubquery_engine::copy_ref_key for further details.
903
if (was_null && !value)
908
my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value)
911
As far as Item_in_subselect called only from Item_in_optimizer this
912
method should not be used
916
DBUG_ASSERT(fixed == 1);
923
if (was_null && !value)
925
int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
926
return decimal_value;
931
Rewrite a single-column IN/ALL/ANY subselect
934
Item_in_subselect::single_value_transformer()
935
join Join object of the subquery (i.e. 'child' join).
936
func Subquery comparison creator
939
Rewrite a single-column subquery using rule-based approach. The subquery
941
oe $cmp$ (SELECT ie FROM ... WHERE subq_where ... HAVING subq_having)
943
First, try to convert the subquery to scalar-result subquery in one of
946
- oe $cmp$ (SELECT MAX(...) ) // handled by Item_singlerow_subselect
947
- oe $cmp$ <max>(SELECT ...) // handled by Item_maxmin_subselect
949
If that fails, the subquery will be handled with class Item_in_optimizer.
950
There are two possibilites:
951
- If the subquery execution method is materialization, then the subquery is
952
not transformed any further.
953
- Otherwise the IN predicates is transformed into EXISTS by injecting
954
equi-join predicates and possibly other helper predicates. For details
955
see method single_value_in_like_transformer().
958
RES_OK Either subquery was transformed, or appopriate
959
predicates where injected into it.
960
RES_REDUCE The subquery was reduced to non-subquery
964
Item_subselect::trans_res
965
Item_in_subselect::single_value_transformer(JOIN *join,
968
SELECT_LEX *select_lex= join->select_lex;
969
DBUG_ENTER("Item_in_subselect::single_value_transformer");
972
Check that the right part of the subselect contains no more than one
973
column. E.g. in SELECT 1 IN (SELECT * ..) the right part is (SELECT * ...)
975
if (select_lex->item_list.elements > 1)
977
my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
978
DBUG_RETURN(RES_ERROR);
982
If this is an ALL/ANY single-value subselect, try to rewrite it with
983
a MIN/MAX subselect. We can do that if a possible NULL result of the
984
subselect can be ignored.
985
E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten
986
with SELECT * FROM t1 WHERE b > (SELECT MAX(a) FROM t2).
987
We can't check that this optimization is safe if it's not a top-level
988
item of the WHERE clause (e.g. because the WHERE clause can contain IS
989
NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
990
later in this method.
992
if ((abort_on_null || (upper_item && upper_item->top_level())) &&
993
!select_lex->master_unit()->uncacheable && !func->eqne_op())
997
// It is second (third, ...) SELECT of UNION => All is done
1002
if (!select_lex->group_list.elements &&
1003
!select_lex->having &&
1004
!select_lex->with_sum_func &&
1005
!(select_lex->next_select()) &&
1006
select_lex->table_list.elements)
1008
Item_sum_hybrid *item;
1009
nesting_map save_allow_sum_func;
1013
(ALL && (> || =>)) || (ANY && (< || =<))
1014
for ALL condition is inverted
1016
item= new Item_sum_max(*select_lex->ref_pointer_array);
1021
(ALL && (< || =<)) || (ANY && (> || =>))
1022
for ALL condition is inverted
1024
item= new Item_sum_min(*select_lex->ref_pointer_array);
1027
upper_item->set_sum_test(item);
1028
*select_lex->ref_pointer_array= item;
1030
List_iterator<Item> it(select_lex->item_list);
1035
save_allow_sum_func= thd->lex->allow_sum_func;
1036
thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
1038
Item_sum_(max|min) can't substitute other item => we can use 0 as
1039
reference, also Item_sum_(max|min) can't be fixed after creation, so
1040
we do not check item->fixed
1042
if (item->fix_fields(thd, 0))
1043
DBUG_RETURN(RES_ERROR);
1044
thd->lex->allow_sum_func= save_allow_sum_func;
1045
/* we added aggregate function => we have to change statistic */
1046
count_field_types(select_lex, &join->tmp_table_param, join->all_fields,
1049
subs= new Item_singlerow_subselect(select_lex);
1053
Item_maxmin_subselect *item;
1054
subs= item= new Item_maxmin_subselect(thd, this, select_lex, func->l_op());
1056
upper_item->set_sub_test(item);
1058
/* fix fields is already called for left expression */
1059
substitution= func->create(left_expr, subs);
1060
DBUG_RETURN(RES_OK);
1065
/* We're invoked for the 1st (or the only) SELECT in the subquery UNION */
1066
SELECT_LEX_UNIT *master_unit= select_lex->master_unit();
1067
substitution= optimizer;
1069
SELECT_LEX *current= thd->lex->current_select, *up;
1071
thd->lex->current_select= up= current->return_after_parsing();
1072
//optimizer never use Item **ref => we can pass 0 as parameter
1073
if (!optimizer || optimizer->fix_left(thd, 0))
1075
thd->lex->current_select= current;
1076
DBUG_RETURN(RES_ERROR);
1078
thd->lex->current_select= current;
1081
As far as Item_ref_in_optimizer do not substitute itself on fix_fields
1082
we can use same item for all selects.
1084
expr= new Item_direct_ref(&select_lex->context,
1085
(Item**)optimizer->get_cache(),
1086
(char *)"<no matter>",
1087
(char *)in_left_expr_name);
1089
master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
1092
if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
1094
if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool))))
1095
DBUG_RETURN(RES_ERROR);
1096
pushed_cond_guards[0]= TRUE;
1100
If this IN predicate can be computed via materialization, do not
1101
perform the IN -> EXISTS transformation.
1103
if (exec_method == MATERIALIZATION)
1104
DBUG_RETURN(RES_OK);
1106
/* Perform the IN=>EXISTS transformation. */
1107
DBUG_RETURN(single_value_in_to_exists_transformer(join, func));
1112
Transofrm an IN predicate into EXISTS via predicate injection.
1114
@details The transformation injects additional predicates into the subquery
1115
(and makes the subquery correlated) as follows.
1117
- If the subquery has aggregates, GROUP BY, or HAVING, convert to
1119
SELECT ie FROM ... HAVING subq_having AND
1120
trigcond(oe $cmp$ ref_or_null_helper<ie>)
1122
the addition is wrapped into trigger only when we want to distinguish
1123
between NULL and FALSE results.
1125
- Otherwise (no aggregates/GROUP BY/HAVING) convert it to one of the
1128
= If we don't need to distinguish between NULL and FALSE subquery:
1130
SELECT 1 FROM ... WHERE (oe $cmp$ ie) AND subq_where
1132
= If we need to distinguish between those:
1135
WHERE subq_where AND trigcond((oe $cmp$ ie) OR (ie IS NULL))
1136
HAVING trigcond(<is_not_null_test>(ie))
1138
@param join Join object of the subquery (i.e. 'child' join).
1139
@param func Subquery comparison creator
1141
@retval RES_OK Either subquery was transformed, or appopriate
1142
predicates where injected into it.
1143
@retval RES_REDUCE The subquery was reduced to non-subquery
1144
@retval RES_ERROR Error
1147
Item_subselect::trans_res
1148
Item_in_subselect::single_value_in_to_exists_transformer(JOIN * join, Comp_creator *func)
1150
SELECT_LEX *select_lex= join->select_lex;
1151
DBUG_ENTER("Item_in_subselect::single_value_in_to_exists_transformer");
1153
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
1154
if (join->having || select_lex->with_sum_func ||
1155
select_lex->group_list.elements)
1158
Item *item= func->create(expr,
1159
new Item_ref_null_helper(&select_lex->context,
1164
this->full_name()));
1165
if (!abort_on_null && left_expr->maybe_null)
1168
We can encounter "NULL IN (SELECT ...)". Wrap the added condition
1171
item= new Item_func_trig_cond(item, get_cond_guard(0));
1175
AND and comparison functions can't be changed during fix_fields()
1176
we can assign select_lex->having here, and pass 0 as last
1177
argument (reference) to fix_fields()
1179
select_lex->having= join->having= and_items(join->having, item);
1180
if (join->having == item)
1181
item->name= (char*)in_having_cond;
1182
select_lex->having_fix_field= 1;
1184
we do not check join->having->fixed, because Item_and (from and_items)
1185
or comparison function (from func->create) can't be fixed after creation
1187
tmp= join->having->fix_fields(thd, 0);
1188
select_lex->having_fix_field= 0;
1190
DBUG_RETURN(RES_ERROR);
1194
Item *item= (Item*) select_lex->item_list.head();
1196
if (select_lex->table_list.elements)
1199
Item *having= item, *orig_item= item;
1200
select_lex->item_list.empty();
1201
select_lex->item_list.push_back(new Item_int("Not_used",
1203
MY_INT64_NUM_DECIMAL_DIGITS));
1204
select_lex->ref_pointer_array[0]= select_lex->item_list.head();
1206
item= func->create(expr, item);
1207
if (!abort_on_null && orig_item->maybe_null)
1209
having= new Item_is_not_null_test(this, having);
1210
if (left_expr->maybe_null)
1212
if (!(having= new Item_func_trig_cond(having,
1213
get_cond_guard(0))))
1214
DBUG_RETURN(RES_ERROR);
1217
Item_is_not_null_test can't be changed during fix_fields()
1218
we can assign select_lex->having here, and pass 0 as last
1219
argument (reference) to fix_fields()
1221
having->name= (char*)in_having_cond;
1222
select_lex->having= join->having= having;
1223
select_lex->having_fix_field= 1;
1225
we do not check join->having->fixed, because Item_and (from
1226
and_items) or comparison function (from func->create) can't be
1227
fixed after creation
1229
tmp= join->having->fix_fields(thd, 0);
1230
select_lex->having_fix_field= 0;
1232
DBUG_RETURN(RES_ERROR);
1233
item= new Item_cond_or(item,
1234
new Item_func_isnull(orig_item));
1237
If we may encounter NULL IN (SELECT ...) and care whether subquery
1238
result is NULL or FALSE, wrap condition in a trig_cond.
1240
if (!abort_on_null && left_expr->maybe_null)
1242
if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
1243
DBUG_RETURN(RES_ERROR);
1246
TODO: figure out why the following is done here in
1247
single_value_transformer but there is no corresponding action in
1248
row_value_transformer?
1250
item->name= (char *)in_additional_cond;
1253
AND can't be changed during fix_fields()
1254
we can assign select_lex->having here, and pass 0 as last
1255
argument (reference) to fix_fields()
1257
select_lex->where= join->conds= and_items(join->conds, item);
1258
select_lex->where->top_level_item();
1260
we do not check join->conds->fixed, because Item_and can't be fixed
1263
if (join->conds->fix_fields(thd, 0))
1264
DBUG_RETURN(RES_ERROR);
1269
if (select_lex->master_unit()->is_union())
1272
comparison functions can't be changed during fix_fields()
1273
we can assign select_lex->having here, and pass 0 as last
1274
argument (reference) to fix_fields()
1278
new Item_ref_null_helper(&select_lex->context, this,
1279
select_lex->ref_pointer_array,
1280
(char *)"<no matter>",
1281
(char *)"<result>"));
1282
if (!abort_on_null && left_expr->maybe_null)
1284
if (!(new_having= new Item_func_trig_cond(new_having,
1285
get_cond_guard(0))))
1286
DBUG_RETURN(RES_ERROR);
1288
new_having->name= (char*)in_having_cond;
1289
select_lex->having= join->having= new_having;
1290
select_lex->having_fix_field= 1;
1293
we do not check join->having->fixed, because comparison function
1294
(from func->create) can't be fixed after creation
1296
tmp= join->having->fix_fields(thd, 0);
1297
select_lex->having_fix_field= 0;
1299
DBUG_RETURN(RES_ERROR);
1303
// it is single select without tables => possible optimization
1304
item= func->create(left_expr, item);
1305
// fix_field of item will be done in time of substituting
1307
have_to_be_excluded= 1;
1308
if (thd->lex->describe)
1310
char warn_buff[MYSQL_ERRMSG_SIZE];
1311
sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
1312
push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
1313
ER_SELECT_REDUCED, warn_buff);
1315
DBUG_RETURN(RES_REDUCE);
1320
DBUG_RETURN(RES_OK);
1324
Item_subselect::trans_res
1325
Item_in_subselect::row_value_transformer(JOIN *join)
1327
SELECT_LEX *select_lex= join->select_lex;
1328
uint cols_num= left_expr->cols();
1330
DBUG_ENTER("Item_in_subselect::row_value_transformer");
1332
if (select_lex->item_list.elements != left_expr->cols())
1334
my_error(ER_OPERAND_COLUMNS, MYF(0), left_expr->cols());
1335
DBUG_RETURN(RES_ERROR);
1339
Wrap the current IN predicate in an Item_in_optimizer. The actual
1340
substitution in the Item tree takes place in Item_subselect::fix_fields.
1344
//first call for this unit
1345
SELECT_LEX_UNIT *master_unit= select_lex->master_unit();
1346
substitution= optimizer;
1348
SELECT_LEX *current= thd->lex->current_select, *up;
1349
thd->lex->current_select= up= current->return_after_parsing();
1350
//optimizer never use Item **ref => we can pass 0 as parameter
1351
if (!optimizer || optimizer->fix_left(thd, 0))
1353
thd->lex->current_select= current;
1354
DBUG_RETURN(RES_ERROR);
1357
// we will refer to upper level cache array => we have to save it in PS
1358
optimizer->keep_top_level_cache();
1360
thd->lex->current_select= current;
1361
master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
1363
if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
1365
if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool) *
1366
left_expr->cols())))
1367
DBUG_RETURN(RES_ERROR);
1368
for (uint i= 0; i < cols_num; i++)
1369
pushed_cond_guards[i]= TRUE;
1374
If this IN predicate can be computed via materialization, do not
1375
perform the IN -> EXISTS transformation.
1377
if (exec_method == MATERIALIZATION)
1378
DBUG_RETURN(RES_OK);
1380
/* Perform the IN=>EXISTS transformation. */
1381
DBUG_RETURN(row_value_in_to_exists_transformer(join));
1386
Tranform a (possibly non-correlated) IN subquery into a correlated EXISTS.
1389
The IF-ELSE below can be refactored so that there is no duplication of the
1390
statements that create the new conditions. For this we have to invert the IF
1391
and the FOR statements as this:
1392
for (each left operand)
1393
create the equi-join condition
1394
if (is_having_used || !abort_on_null)
1395
create the "is null" and is_not_null_test items
1397
add the equi-join and the null tests to HAVING
1399
add the equi-join and the "is null" to WHERE
1400
add the is_not_null_test to HAVING
1403
Item_subselect::trans_res
1404
Item_in_subselect::row_value_in_to_exists_transformer(JOIN * join)
1406
SELECT_LEX *select_lex= join->select_lex;
1407
Item *having_item= 0;
1408
uint cols_num= left_expr->cols();
1409
bool is_having_used= (join->having || select_lex->with_sum_func ||
1410
select_lex->group_list.first ||
1411
!select_lex->table_list.elements);
1413
DBUG_ENTER("Item_in_subselect::row_value_in_to_exists_transformer");
1415
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
1419
(l1, l2, l3) IN (SELECT v1, v2, v3 ... HAVING having) =>
1420
EXISTS (SELECT ... HAVING having and
1421
(l1 = v1 or is null v1) and
1422
(l2 = v2 or is null v2) and
1423
(l3 = v3 or is null v3) and
1424
is_not_null_test(v1) and
1425
is_not_null_test(v2) and
1426
is_not_null_test(v3))
1427
where is_not_null_test used to register nulls in case if we have
1428
not found matching to return correct NULL value
1429
TODO: say here explicitly if the order of AND parts matters or not.
1431
Item *item_having_part2= 0;
1432
for (uint i= 0; i < cols_num; i++)
1434
DBUG_ASSERT(left_expr->fixed &&
1435
select_lex->ref_pointer_array[i]->fixed ||
1436
(select_lex->ref_pointer_array[i]->type() == REF_ITEM &&
1437
((Item_ref*)(select_lex->ref_pointer_array[i]))->ref_type() ==
1438
Item_ref::OUTER_REF));
1439
if (select_lex->ref_pointer_array[i]->
1440
check_cols(left_expr->element_index(i)->cols()))
1441
DBUG_RETURN(RES_ERROR);
1443
new Item_func_eq(new
1444
Item_ref(&select_lex->context,
1445
(*optimizer->get_cache())->
1447
(char *)"<no matter>",
1448
(char *)in_left_expr_name),
1450
Item_ref(&select_lex->context,
1451
select_lex->ref_pointer_array + i,
1452
(char *)"<no matter>",
1453
(char *)"<list ref>")
1456
new Item_func_isnull(new
1457
Item_ref(&select_lex->context,
1458
select_lex->ref_pointer_array+i,
1459
(char *)"<no matter>",
1460
(char *)"<list ref>")
1462
Item *col_item= new Item_cond_or(item_eq, item_isnull);
1463
if (!abort_on_null && left_expr->element_index(i)->maybe_null)
1465
if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i))))
1466
DBUG_RETURN(RES_ERROR);
1468
having_item= and_items(having_item, col_item);
1470
Item *item_nnull_test=
1471
new Item_is_not_null_test(this,
1472
new Item_ref(&select_lex->context,
1474
ref_pointer_array + i,
1475
(char *)"<no matter>",
1476
(char *)"<list ref>"));
1477
if (!abort_on_null && left_expr->element_index(i)->maybe_null)
1479
if (!(item_nnull_test=
1480
new Item_func_trig_cond(item_nnull_test, get_cond_guard(i))))
1481
DBUG_RETURN(RES_ERROR);
1483
item_having_part2= and_items(item_having_part2, item_nnull_test);
1484
item_having_part2->top_level_item();
1486
having_item= and_items(having_item, item_having_part2);
1487
having_item->top_level_item();
1492
(l1, l2, l3) IN (SELECT v1, v2, v3 ... WHERE where) =>
1493
EXISTS (SELECT ... WHERE where and
1494
(l1 = v1 or is null v1) and
1495
(l2 = v2 or is null v2) and
1496
(l3 = v3 or is null v3)
1497
HAVING is_not_null_test(v1) and
1498
is_not_null_test(v2) and
1499
is_not_null_test(v3))
1500
where is_not_null_test register NULLs values but reject rows
1502
in case when we do not need correct NULL, we have simplier construction:
1503
EXISTS (SELECT ... WHERE where and
1508
Item *where_item= 0;
1509
for (uint i= 0; i < cols_num; i++)
1511
Item *item, *item_isnull;
1512
DBUG_ASSERT(left_expr->fixed &&
1513
select_lex->ref_pointer_array[i]->fixed ||
1514
(select_lex->ref_pointer_array[i]->type() == REF_ITEM &&
1515
((Item_ref*)(select_lex->ref_pointer_array[i]))->ref_type() ==
1516
Item_ref::OUTER_REF));
1517
if (select_lex->ref_pointer_array[i]->
1518
check_cols(left_expr->element_index(i)->cols()))
1519
DBUG_RETURN(RES_ERROR);
1521
new Item_func_eq(new
1522
Item_direct_ref(&select_lex->context,
1523
(*optimizer->get_cache())->
1525
(char *)"<no matter>",
1526
(char *)in_left_expr_name),
1528
Item_direct_ref(&select_lex->context,
1530
ref_pointer_array+i,
1531
(char *)"<no matter>",
1532
(char *)"<list ref>")
1536
Item *having_col_item=
1537
new Item_is_not_null_test(this,
1539
Item_ref(&select_lex->context,
1540
select_lex->ref_pointer_array + i,
1541
(char *)"<no matter>",
1542
(char *)"<list ref>"));
1546
Item_func_isnull(new
1547
Item_direct_ref(&select_lex->context,
1549
ref_pointer_array+i,
1550
(char *)"<no matter>",
1551
(char *)"<list ref>")
1553
item= new Item_cond_or(item, item_isnull);
1555
TODO: why we create the above for cases where the right part
1558
if (left_expr->element_index(i)->maybe_null)
1560
if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
1561
DBUG_RETURN(RES_ERROR);
1562
if (!(having_col_item=
1563
new Item_func_trig_cond(having_col_item, get_cond_guard(i))))
1564
DBUG_RETURN(RES_ERROR);
1566
having_item= and_items(having_item, having_col_item);
1568
where_item= and_items(where_item, item);
1571
AND can't be changed during fix_fields()
1572
we can assign select_lex->where here, and pass 0 as last
1573
argument (reference) to fix_fields()
1575
select_lex->where= join->conds= and_items(join->conds, where_item);
1576
select_lex->where->top_level_item();
1577
if (join->conds->fix_fields(thd, 0))
1578
DBUG_RETURN(RES_ERROR);
1583
select_lex->having= join->having= and_items(join->having, having_item);
1584
if (having_item == select_lex->having)
1585
having_item->name= (char*)in_having_cond;
1586
select_lex->having->top_level_item();
1588
AND can't be changed during fix_fields()
1589
we can assign select_lex->having here, and pass 0 as last
1590
argument (reference) to fix_fields()
1592
select_lex->having_fix_field= 1;
1593
res= join->having->fix_fields(thd, 0);
1594
select_lex->having_fix_field= 0;
1597
DBUG_RETURN(RES_ERROR);
1601
DBUG_RETURN(RES_OK);
1605
Item_subselect::trans_res
1606
Item_in_subselect::select_transformer(JOIN *join)
1608
return select_in_like_transformer(join, &eq_creator);
1613
Prepare IN/ALL/ANY/SOME subquery transformation and call appropriate
1614
transformation function.
1616
To decide which transformation procedure (scalar or row) applicable here
1617
we have to call fix_fields() for left expression to be able to call
1618
cols() method on it. Also this method make arena management for
1619
underlying transformation methods.
1621
@param join JOIN object of transforming subquery
1622
@param func creator of condition function of subquery
1627
RES_REDUCE OK, and current subquery was reduced during
1633
Item_subselect::trans_res
1634
Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
1636
SELECT_LEX *current= thd->lex->current_select, *up;
1637
const char *save_where= thd->where;
1638
Item_subselect::trans_res res= RES_ERROR;
1641
DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
1645
IN/SOME/ALL/ANY subqueries aren't support LIMIT clause. Without it
1646
ORDER BY clause becomes meaningless thus we drop it here.
1648
SELECT_LEX *sl= current->master_unit()->first_select();
1649
for (; sl; sl= sl->next_select())
1657
DBUG_RETURN(RES_OK);
1659
thd->where= "IN/ALL/ANY subquery";
1662
In some optimisation cases we will not need this Item_in_optimizer
1663
object, but we can't know it here, but here we need address correct
1664
reference on left expresion.
1668
result= (!(optimizer= new Item_in_optimizer(left_expr, this)));
1673
thd->lex->current_select= up= current->return_after_parsing();
1674
result= (!left_expr->fixed &&
1675
left_expr->fix_fields(thd, optimizer->arguments()));
1676
/* fix_fields can change reference to left_expr, we need reassign it */
1677
left_expr= optimizer->arguments()[0];
1679
thd->lex->current_select= current;
1684
If we didn't choose an execution method up to this point, we choose
1685
the IN=>EXISTS transformation.
1687
if (exec_method == NOT_TRANSFORMED)
1688
exec_method= IN_TO_EXISTS;
1691
Both transformers call fix_fields() only for Items created inside them,
1692
and all those items do not make permanent changes in the current item arena
1693
which allows us to call them with changed arena (if we do not know the
1694
nature of Item, we have to call fix_fields() for it only with the original
1695
arena to avoid memory leak).
1697
if (left_expr->cols() == 1)
1698
res= single_value_transformer(join, func);
1701
/* we do not support row operation for ALL/ANY/SOME */
1702
if (func != &eq_creator)
1704
my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
1705
DBUG_RETURN(RES_ERROR);
1707
res= row_value_transformer(join);
1710
thd->where= save_where;
1715
void Item_in_subselect::print(String *str, enum_query_type query_type)
1717
if (exec_method == IN_TO_EXISTS)
1718
str->append(STRING_WITH_LEN("<exists>"));
1721
left_expr->print(str, query_type);
1722
str->append(STRING_WITH_LEN(" in "));
1724
Item_subselect::print(str, query_type);
1728
bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref)
1732
if (exec_method == SEMI_JOIN)
1733
return !( (*ref)= new Item_int(1));
1735
if (thd_arg->lex->view_prepare_mode && left_expr && !left_expr->fixed)
1736
result = left_expr->fix_fields(thd_arg, &left_expr);
1738
return result || Item_subselect::fix_fields(thd_arg, ref);
1743
Try to create an engine to compute the subselect via materialization,
1744
and if this fails, revert to execution via the IN=>EXISTS transformation.
1747
The purpose of this method is to hide the implementation details
1748
of this Item's execution. The method creates a new engine for
1749
materialized execution, and initializes the engine.
1751
If this initialization fails
1752
- either because it wasn't possible to create the needed temporary table
1754
- or because of a memory allocation error,
1755
then we revert back to execution via the IN=>EXISTS tranformation.
1757
The initialization of the new engine is divided in two parts - a permanent
1758
one that lives across prepared statements, and one that is repeated for each
1762
@retval TRUE memory allocation error occurred
1763
@retval FALSE an execution method was chosen successfully
1766
bool Item_in_subselect::setup_engine()
1768
subselect_hash_sj_engine *new_engine= NULL;
1771
DBUG_ENTER("Item_in_subselect::setup_engine");
1773
if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
1775
/* Create/initialize objects in permanent memory. */
1776
subselect_single_select_engine *old_engine;
1778
old_engine= (subselect_single_select_engine*) engine;
1780
if (!(new_engine= new subselect_hash_sj_engine(thd, this,
1782
new_engine->init_permanent(unit->get_unit_column_types()))
1784
Item_subselect::trans_res trans_res;
1786
If for some reason we cannot use materialization for this IN predicate,
1787
delete all materialization-related objects, and apply the IN=>EXISTS
1792
exec_method= NOT_TRANSFORMED;
1793
if (left_expr->cols() == 1)
1794
trans_res= single_value_in_to_exists_transformer(old_engine->join,
1797
trans_res= row_value_in_to_exists_transformer(old_engine->join);
1798
res= (trans_res != Item_subselect::RES_OK);
1805
DBUG_ASSERT(engine->engine_type() == subselect_engine::HASH_SJ_ENGINE);
1806
new_engine= (subselect_hash_sj_engine*) engine;
1809
/* Initilizations done in runtime memory, repeated for each execution. */
1813
Reset the LIMIT 1 set in Item_exists_subselect::fix_length_and_dec.
1815
Currently we set the subquery LIMIT to infinity, and this is correct
1816
because we forbid at parse time LIMIT inside IN subqueries (see
1817
Item_in_subselect::test_limit). However, once we allow this, here
1818
we should set the correct limit if given in the query.
1820
unit->global_parameters->select_limit= NULL;
1821
if ((res= new_engine->init_runtime()))
1830
Initialize the cache of the left operand of the IN predicate.
1832
@note This method has the same purpose as alloc_group_fields(),
1833
but it takes a different kind of collection of items, and the
1834
list we push to is dynamically allocated.
1836
@retval TRUE if a memory allocation error occurred or the cache is
1837
not applicable to the current query
1838
@retval FALSE if success
1841
bool Item_in_subselect::init_left_expr_cache()
1844
Next_select_func end_select;
1845
bool use_result_field= FALSE;
1847
outer_join= unit->outer_select()->join;
1848
if (!outer_join || !outer_join->tables)
1851
If we use end_[send | write]_group to handle complete rows of the outer
1852
query, make the cache of the left IN operand use Item_field::result_field
1853
instead of Item_field::field. We need this because normally
1854
Cached_item_field uses Item::field to fetch field data, while
1855
copy_ref_key() that copies the left IN operand into a lookup key uses
1856
Item::result_field. In the case end_[send | write]_group result_field is
1857
one row behind field.
1859
end_select= outer_join->join_tab[outer_join->tables-1].next_select;
1860
if (end_select == end_send_group || end_select == end_write_group)
1861
use_result_field= TRUE;
1863
if (!(left_expr_cache= new List<Cached_item>))
1866
for (uint i= 0; i < left_expr->cols(); i++)
1868
Cached_item *cur_item_cache= new_Cached_item(thd,
1869
left_expr->element_index(i),
1871
if (!cur_item_cache || left_expr_cache->push_front(cur_item_cache))
1879
Callback to test if an IN predicate is expensive.
1882
IN predicates are considered expensive only if they will be executed via
1883
materialization. The return value affects the behavior of
1884
make_cond_for_table() in such a way that it is unchanged when we use
1885
the IN=>EXISTS transformation to compute IN.
1887
@retval TRUE if the predicate is expensive
1888
@retval FALSE otherwise
1891
bool Item_in_subselect::is_expensive_processor(uchar *arg)
1893
return exec_method == MATERIALIZATION;
1897
Item_subselect::trans_res
1898
Item_allany_subselect::select_transformer(JOIN *join)
1900
DBUG_ENTER("Item_allany_subselect::select_transformer");
1901
exec_method= IN_TO_EXISTS;
1903
upper_item->show= 1;
1904
DBUG_RETURN(select_in_like_transformer(join, func));
1908
void Item_allany_subselect::print(String *str, enum_query_type query_type)
1910
if (exec_method == IN_TO_EXISTS)
1911
str->append(STRING_WITH_LEN("<exists>"));
1914
left_expr->print(str, query_type);
1916
str->append(func->symbol(all));
1917
str->append(all ? " all " : " any ", 5);
1919
Item_subselect::print(str, query_type);
1923
void subselect_engine::set_thd(THD *thd_arg)
1927
result->set_thd(thd_arg);
1931
subselect_single_select_engine::
1932
subselect_single_select_engine(st_select_lex *select,
1933
select_result_interceptor *result_arg,
1934
Item_subselect *item_arg)
1935
:subselect_engine(item_arg, result_arg),
1936
prepared(0), executed(0), select_lex(select), join(0)
1938
select_lex->master_unit()->item= item_arg;
1942
void subselect_single_select_engine::cleanup()
1944
DBUG_ENTER("subselect_single_select_engine::cleanup");
1945
prepared= executed= 0;
1952
void subselect_union_engine::cleanup()
1954
DBUG_ENTER("subselect_union_engine::cleanup");
1955
unit->reinit_exec_mechanism();
1961
bool subselect_union_engine::is_executed() const
1963
return unit->executed;
1968
Check if last execution of the subquery engine produced any rows
1971
subselect_union_engine::no_rows()
1974
Check if last execution of the subquery engine produced any rows. The
1975
return value is undefined if last execution ended in an error.
1978
TRUE - Last subselect execution has produced no rows
1982
bool subselect_union_engine::no_rows()
1984
/* Check if we got any rows when reading UNION result from temp. table: */
1985
return test(!unit->fake_select_lex->join->send_records);
1989
void subselect_uniquesubquery_engine::cleanup()
1991
DBUG_ENTER("subselect_uniquesubquery_engine::cleanup");
1992
/* Tell handler we don't need the index anymore */
1993
if (tab->table->file->inited)
1994
tab->table->file->ha_index_end();
1999
subselect_union_engine::subselect_union_engine(st_select_lex_unit *u,
2000
select_result_interceptor *result_arg,
2001
Item_subselect *item_arg)
2002
:subselect_engine(item_arg, result_arg)
2005
unit->item= item_arg;
2010
Create and prepare the JOIN object that represents the query execution
2011
plan for the subquery.
2014
This method is called from Item_subselect::fix_fields. For prepared
2015
statements it is called both during the PREPARE and EXECUTE phases in the
2017
- During PREPARE the optimizer needs some properties
2018
(join->fields_list.elements) of the JOIN to proceed with preparation of
2019
the remaining query (namely to complete ::fix_fields for the subselect
2020
related classes. In the end of PREPARE the JOIN is deleted.
2021
- When we EXECUTE the query, Item_subselect::fix_fields is called again, and
2022
the JOIN object is re-created again, prepared and executed. In the end of
2023
execution it is deleted.
2024
In all cases the JOIN is created in runtime memory (not in the permanent
2028
Re-check what properties of 'join' are needed during prepare, and see if
2029
we can avoid creating a JOIN during JOIN::prepare of the outer join.
2031
@retval 0 if success
2035
int subselect_single_select_engine::prepare()
2039
join= new JOIN(thd, select_lex->item_list,
2040
select_lex->options | SELECT_NO_UNLOCK, result);
2041
if (!join || !result)
2042
return 1; /* Fatal error is set already. */
2044
SELECT_LEX *save_select= thd->lex->current_select;
2045
thd->lex->current_select= select_lex;
2046
if (join->prepare(&select_lex->ref_pointer_array,
2047
(TABLE_LIST*) select_lex->table_list.first,
2048
select_lex->with_wild,
2050
select_lex->order_list.elements +
2051
select_lex->group_list.elements,
2052
(ORDER*) select_lex->order_list.first,
2053
(ORDER*) select_lex->group_list.first,
2055
(ORDER*) 0, select_lex,
2056
select_lex->master_unit()))
2058
thd->lex->current_select= save_select;
2062
int subselect_union_engine::prepare()
2064
return unit->prepare(thd, result, SELECT_NO_UNLOCK);
2067
int subselect_uniquesubquery_engine::prepare()
2069
/* Should never be called. */
2076
Check if last execution of the subquery engine produced any rows
2079
subselect_single_select_engine::no_rows()
2082
Check if last execution of the subquery engine produced any rows. The
2083
return value is undefined if last execution ended in an error.
2086
TRUE - Last subselect execution has produced no rows
2090
bool subselect_single_select_engine::no_rows()
2092
return !item->assigned();
2097
makes storage for the output values for the subquery and calcuates
2098
their data and column types and their nullability.
2100
void subselect_engine::set_row(List<Item> &item_list, Item_cache **row)
2103
List_iterator_fast<Item> li(item_list);
2104
res_type= STRING_RESULT;
2105
res_field_type= MYSQL_TYPE_VAR_STRING;
2106
for (uint i= 0; (sel_item= li++); i++)
2108
item->max_length= sel_item->max_length;
2109
res_type= sel_item->result_type();
2110
res_field_type= sel_item->field_type();
2111
item->decimals= sel_item->decimals;
2112
item->unsigned_flag= sel_item->unsigned_flag;
2113
maybe_null= sel_item->maybe_null;
2114
if (!(row[i]= Item_cache::get_cache(sel_item)))
2116
row[i]->setup(sel_item);
2118
if (item_list.elements > 1)
2119
res_type= ROW_RESULT;
2122
void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
2124
DBUG_ASSERT(row || select_lex->item_list.elements==1);
2125
set_row(select_lex->item_list, row);
2126
item->collation.set(row[0]->collation);
2131
void subselect_union_engine::fix_length_and_dec(Item_cache **row)
2133
DBUG_ASSERT(row || unit->first_select()->item_list.elements==1);
2135
if (unit->first_select()->item_list.elements == 1)
2137
set_row(unit->types, row);
2138
item->collation.set(row[0]->collation);
2142
bool maybe_null_saved= maybe_null;
2143
set_row(unit->types, row);
2144
maybe_null= maybe_null_saved;
2148
void subselect_uniquesubquery_engine::fix_length_and_dec(Item_cache **row)
2150
//this never should be called
2154
int init_read_record_seq(JOIN_TAB *tab);
2155
int join_read_always_key_or_null(JOIN_TAB *tab);
2156
int join_read_next_same_or_null(READ_RECORD *info);
2158
int subselect_single_select_engine::exec()
2160
DBUG_ENTER("subselect_single_select_engine::exec");
2161
char const *save_where= thd->where;
2162
SELECT_LEX *save_select= thd->lex->current_select;
2163
thd->lex->current_select= select_lex;
2164
if (!join->optimized)
2166
SELECT_LEX_UNIT *unit= select_lex->master_unit();
2168
unit->set_limit(unit->global_parameters);
2169
if (join->flatten_subqueries())
2171
thd->is_fatal_error= TRUE;
2174
if (join->optimize())
2176
thd->where= save_where;
2178
thd->lex->current_select= save_select;
2179
DBUG_RETURN(join->error ? join->error : 1);
2181
if (!select_lex->uncacheable && thd->lex->describe &&
2182
!(join->select_options & SELECT_DESCRIBE) &&
2183
join->need_tmp && item->const_item())
2186
Force join->join_tmp creation, because this subquery will be replaced
2187
by a simple select from the materialization temp table by optimize()
2188
called by EXPLAIN and we need to preserve the initial query structure
2189
so we can display it.
2191
select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
2192
select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
2193
if (join->init_save_join_tab())
2194
DBUG_RETURN(1); /* purecov: inspected */
2196
if (item->engine_changed)
2201
if (select_lex->uncacheable &&
2202
select_lex->uncacheable != UNCACHEABLE_EXPLAIN
2207
thd->where= save_where;
2208
thd->lex->current_select= save_select;
2212
item->assigned((executed= 0));
2216
item->reset_value_registration();
2217
JOIN_TAB *changed_tabs[MAX_TABLES];
2218
JOIN_TAB **last_changed_tab= changed_tabs;
2219
if (item->have_guarded_conds())
2222
For at least one of the pushed predicates the following is true:
2223
We should not apply optimizations based on the condition that was
2224
pushed down into the subquery. Those optimizations are ref[_or_null]
2225
acceses. Change them to be full table scans.
2227
for (uint i=join->const_tables ; i < join->tables ; i++)
2229
JOIN_TAB *tab=join->join_tab+i;
2230
if (tab && tab->keyuse)
2232
for (uint i= 0; i < tab->ref.key_parts; i++)
2234
bool *cond_guard= tab->ref.cond_guards[i];
2235
if (cond_guard && !*cond_guard)
2237
/* Change the access method to full table scan */
2238
tab->save_read_first_record= tab->read_first_record;
2239
tab->save_read_record= tab->read_record.read_record;
2240
tab->read_first_record= init_read_record_seq;
2241
tab->read_record.record= tab->table->record[0];
2242
tab->read_record.thd= join->thd;
2243
tab->read_record.ref_length= tab->table->file->ref_length;
2244
*(last_changed_tab++)= tab;
2254
/* Enable the optimizations back */
2255
for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
2257
JOIN_TAB *tab= *ptab;
2258
tab->read_record.record= 0;
2259
tab->read_record.ref_length= 0;
2260
tab->read_first_record= tab->save_read_first_record;
2261
tab->read_record.read_record= tab->save_read_record;
2264
thd->where= save_where;
2265
thd->lex->current_select= save_select;
2266
DBUG_RETURN(join->error||thd->is_fatal_error);
2268
thd->where= save_where;
2269
thd->lex->current_select= save_select;
2273
int subselect_union_engine::exec()
2275
char const *save_where= thd->where;
2276
int res= unit->exec();
2277
thd->where= save_where;
2283
Search for at least one row satisfying select condition
2286
subselect_uniquesubquery_engine::scan_table()
2289
Scan the table using sequential access until we find at least one row
2290
satisfying select condition.
2292
The caller must set this->empty_result_set=FALSE before calling this
2293
function. This function will set it to TRUE if it finds a matching row.
2300
int subselect_uniquesubquery_engine::scan_table()
2303
TABLE *table= tab->table;
2304
DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
2306
if (table->file->inited)
2307
table->file->ha_index_end();
2309
table->file->ha_rnd_init(1);
2310
table->file->extra_opt(HA_EXTRA_CACHE,
2311
current_thd->variables.read_buff_size);
2315
error=table->file->rnd_next(table->record[0]);
2316
if (error && error != HA_ERR_END_OF_FILE)
2318
error= report_error(table, error);
2325
if (!cond || cond->val_int())
2327
empty_result_set= FALSE;
2332
table->file->ha_rnd_end();
2333
DBUG_RETURN(error != 0);
2338
Copy ref key and check for null parts in it
2341
subselect_uniquesubquery_engine::copy_ref_key()
2344
Copy ref key and check for null parts in it.
2345
Depending on the nullability and conversion problems this function
2346
recognizes and processes the following states :
2347
1. Partial match on top level. This means IN has a value of FALSE
2348
regardless of the data in the subquery table.
2349
Detected by finding a NULL in the left IN operand of a top level
2351
We may actually skip reading the subquery, so return TRUE to skip
2352
the table scan in subselect_uniquesubquery_engine::exec and make
2353
the value of the IN predicate a NULL (that is equal to FALSE on
2355
2. No exact match when IN is nested inside another predicate.
2356
Detected by finding a NULL in the left IN operand when IN is not
2357
a top level predicate.
2358
We cannot have an exact match. But we must proceed further with a
2359
table scan to find out if it's a partial match (and IN has a value
2360
of NULL) or no match (and IN has a value of FALSE).
2361
So we return FALSE to continue with the scan and see if there are
2362
any record that would constitute a partial match (as we cannot
2363
determine that from the index).
2364
3. Error converting the left IN operand to the column type of the
2365
right IN operand. This counts as no match (and IN has the value of
2366
FALSE). We mark the subquery table cursor as having no more rows
2367
(to ensure that the processing that follows will not find a match)
2368
and return FALSE, so IN is not treated as returning NULL.
2372
FALSE - The value of the IN predicate is not known. Proceed to find the
2373
value of the IN predicate using the determined values of
2374
null_keypart and table->status.
2375
TRUE - IN predicate has a value of NULL. Stop the processing right there
2376
and return NULL to the outer predicates.
2379
bool subselect_uniquesubquery_engine::copy_ref_key()
2381
DBUG_ENTER("subselect_uniquesubquery_engine::copy_ref_key");
2383
for (store_key **copy= tab->ref.key_copy ; *copy ; copy++)
2385
enum store_key::store_key_result store_res;
2386
store_res= (*copy)->copy();
2387
tab->ref.key_err= store_res;
2390
When there is a NULL part in the key we don't need to make index
2391
lookup for such key thus we don't need to copy whole key.
2392
If we later should do a sequential scan return OK. Fail otherwise.
2394
See also the comment for the subselect_uniquesubquery_engine::exec()
2397
null_keypart= (*copy)->null_key;
2400
bool top_level= ((Item_in_subselect *) item)->is_top_level_item();
2403
/* Partial match on top level */
2408
/* No exact match when IN is nested inside another predicate */
2414
Check if the error is equal to STORE_KEY_FATAL. This is not expressed
2415
using the store_key::store_key_result enum because ref.key_err is a
2416
boolean and we want to detect both TRUE and STORE_KEY_FATAL from the
2417
space of the union of the values of [TRUE, FALSE] and
2418
store_key::store_key_result.
2419
TODO: fix the variable an return types.
2421
if (store_res == store_key::STORE_KEY_FATAL)
2424
Error converting the left IN operand to the column type of the right
2427
tab->table->status= STATUS_NOT_FOUND;
2439
subselect_uniquesubquery_engine::exec()
2442
Find rows corresponding to the ref key using index access.
2443
If some part of the lookup key is NULL, then we're evaluating
2444
NULL IN (SELECT ... )
2445
This is a special case, we don't need to search for NULL in the table,
2446
instead, the result value is
2447
- NULL if select produces empty row set
2450
In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE)
2451
the caller doesn't distinguish between NULL and FALSE result and we just
2453
Otherwise we make a full table scan to see if there is at least one
2456
The result of this function (info about whether a row was found) is
2457
stored in this->empty_result_set.
2462
TRUE - an error occured while scanning
2465
int subselect_uniquesubquery_engine::exec()
2467
DBUG_ENTER("subselect_uniquesubquery_engine::exec");
2469
TABLE *table= tab->table;
2470
empty_result_set= TRUE;
2473
/* TODO: change to use of 'full_scan' here? */
2479
We know that there will be no rows even if we scan.
2480
Can be set in copy_ref_key.
2482
((Item_in_subselect *) item)->value= 0;
2487
DBUG_RETURN(scan_table());
2489
if (!table->file->inited)
2490
table->file->ha_index_init(tab->ref.key, 0);
2491
error= table->file->index_read_map(table->record[0],
2493
make_prev_keypart_map(tab->ref.key_parts),
2495
DBUG_PRINT("info", ("lookup result: %i", error));
2497
error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
2498
error= report_error(table, error);
2503
if (!table->status && (!cond || cond->val_int()))
2505
((Item_in_subselect *) item)->value= 1;
2506
empty_result_set= FALSE;
2509
((Item_in_subselect *) item)->value= 0;
2512
DBUG_RETURN(error != 0);
2517
Index-lookup subselect 'engine' - run the subquery
2520
subselect_indexsubquery_engine:exec()
2524
The engine is used to resolve subqueries in form
2526
oe IN (SELECT key FROM tbl WHERE subq_where)
2528
The value of the predicate is calculated as follows:
2529
1. If oe IS NULL, this is a special case, do a full table scan on
2530
table tbl and search for row that satisfies subq_where. If such
2531
row is found, return NULL, otherwise return FALSE.
2532
2. Make an index lookup via key=oe, search for a row that satisfies
2533
subq_where. If found, return TRUE.
2534
3. If check_null==TRUE, make another lookup via key=NULL, search for a
2535
row that satisfies subq_where. If found, return NULL, otherwise
2539
The step #1 can be optimized further when the index has several key
2540
parts. Consider a subquery:
2542
(oe1, oe2) IN (SELECT keypart1, keypart2 FROM tbl WHERE subq_where)
2544
and suppose we need to evaluate it for {oe1, oe2}=={const1, NULL}.
2545
Current code will do a full table scan and obtain correct result. There
2546
is a better option: instead of evaluating
2548
SELECT keypart1, keypart2 FROM tbl WHERE subq_where (1)
2550
and checking if it has produced any matching rows, evaluate
2552
SELECT keypart2 FROM tbl WHERE subq_where AND keypart1=const1 (2)
2554
If this query produces a row, the result is NULL (as we're evaluating
2555
"(const1, NULL) IN { (const1, X), ... }", which has a value of UNKNOWN,
2556
i.e. NULL). If the query produces no rows, the result is FALSE.
2558
We currently evaluate (1) by doing a full table scan. (2) can be
2559
evaluated by doing a "ref" scan on "keypart1=const1", which can be much
2560
cheaper. We can use index statistics to quickly check whether "ref" scan
2561
will be cheaper than full table scan.
2568
int subselect_indexsubquery_engine::exec()
2570
DBUG_ENTER("subselect_indexsubquery_engine::exec");
2572
bool null_finding= 0;
2573
TABLE *table= tab->table;
2575
((Item_in_subselect *) item)->value= 0;
2576
empty_result_set= TRUE;
2582
/* We need to check for NULL if there wasn't a matching value */
2583
*tab->ref.null_ref_key= 0; // Search first for not null
2584
((Item_in_subselect *) item)->was_null= 0;
2587
/* Copy the ref key and check for nulls... */
2594
We know that there will be no rows even if we scan.
2595
Can be set in copy_ref_key.
2597
((Item_in_subselect *) item)->value= 0;
2602
DBUG_RETURN(scan_table());
2604
if (!table->file->inited)
2605
table->file->ha_index_init(tab->ref.key, 1);
2606
error= table->file->index_read_map(table->record[0],
2608
make_prev_keypart_map(tab->ref.key_parts),
2611
error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
2612
error= report_error(table, error);
2621
if ((!cond || cond->val_int()) && (!having || having->val_int()))
2623
empty_result_set= FALSE;
2625
((Item_in_subselect *) item)->was_null= 1;
2627
((Item_in_subselect *) item)->value= 1;
2630
error= table->file->index_next_same(table->record[0],
2632
tab->ref.key_length);
2633
if (error && error != HA_ERR_END_OF_FILE)
2635
error= report_error(table, error);
2641
if (!check_null || null_finding)
2642
break; /* We don't need to check nulls */
2643
*tab->ref.null_ref_key= 1;
2645
/* Check if there exists a row with a null value in the index */
2646
if ((error= (safe_index_read(tab) == 1)))
2651
DBUG_RETURN(error != 0);
2655
uint subselect_single_select_engine::cols()
2657
return select_lex->item_list.elements;
2661
uint subselect_union_engine::cols()
2663
return unit->types.elements;
2667
uint8 subselect_single_select_engine::uncacheable()
2669
return select_lex->uncacheable;
2673
uint8 subselect_union_engine::uncacheable()
2675
return unit->uncacheable;
2679
void subselect_single_select_engine::exclude()
2681
select_lex->master_unit()->exclude_level();
2684
void subselect_union_engine::exclude()
2686
unit->exclude_level();
2690
void subselect_uniquesubquery_engine::exclude()
2692
//this never should be called
2697
table_map subselect_engine::calc_const_tables(TABLE_LIST *table)
2700
for (; table; table= table->next_leaf)
2702
TABLE *tbl= table->table;
2703
if (tbl && tbl->const_table)
2710
table_map subselect_single_select_engine::upper_select_const_tables()
2712
return calc_const_tables((TABLE_LIST *) select_lex->outer_select()->
2717
table_map subselect_union_engine::upper_select_const_tables()
2719
return calc_const_tables((TABLE_LIST *) unit->outer_select()->leaf_tables);
2723
void subselect_single_select_engine::print(String *str,
2724
enum_query_type query_type)
2726
select_lex->print(thd, str, query_type);
2730
void subselect_union_engine::print(String *str, enum_query_type query_type)
2732
unit->print(str, query_type);
2736
void subselect_uniquesubquery_engine::print(String *str,
2737
enum_query_type query_type)
2739
char *table_name= tab->table->s->table_name.str;
2740
str->append(STRING_WITH_LEN("<primary_index_lookup>("));
2741
tab->ref.items[0]->print(str, query_type);
2742
str->append(STRING_WITH_LEN(" in "));
2743
if (tab->table->s->table_category == TABLE_CATEGORY_TEMPORARY)
2746
Temporary tables' names change across runs, so they can't be used for
2749
str->append(STRING_WITH_LEN("<temporary table>"));
2752
str->append(table_name, tab->table->s->table_name.length);
2753
KEY *key_info= tab->table->key_info+ tab->ref.key;
2754
str->append(STRING_WITH_LEN(" on "));
2755
str->append(key_info->name);
2758
str->append(STRING_WITH_LEN(" where "));
2759
cond->print(str, query_type);
2766
The above ::print method should be changed as below. Do it after
2767
all other tests pass.
2769
void subselect_uniquesubquery_engine::print(String *str)
2771
KEY *key_info= tab->table->key_info + tab->ref.key;
2772
str->append(STRING_WITH_LEN("<primary_index_lookup>("));
2773
for (uint i= 0; i < key_info->key_parts; i++)
2774
tab->ref.items[i]->print(str);
2775
str->append(STRING_WITH_LEN(" in "));
2776
str->append(tab->table->s->table_name.str, tab->table->s->table_name.length);
2777
str->append(STRING_WITH_LEN(" on "));
2778
str->append(key_info->name);
2781
str->append(STRING_WITH_LEN(" where "));
2788
void subselect_indexsubquery_engine::print(String *str,
2789
enum_query_type query_type)
2791
str->append(STRING_WITH_LEN("<index_lookup>("));
2792
tab->ref.items[0]->print(str, query_type);
2793
str->append(STRING_WITH_LEN(" in "));
2794
str->append(tab->table->s->table_name.str, tab->table->s->table_name.length);
2795
KEY *key_info= tab->table->key_info+ tab->ref.key;
2796
str->append(STRING_WITH_LEN(" on "));
2797
str->append(key_info->name);
2799
str->append(STRING_WITH_LEN(" checking NULL"));
2802
str->append(STRING_WITH_LEN(" where "));
2803
cond->print(str, query_type);
2807
str->append(STRING_WITH_LEN(" having "));
2808
having->print(str, query_type);
2814
change select_result object of engine.
2816
@param si new subselect Item
2817
@param res new select_result object
2825
bool subselect_single_select_engine::change_result(Item_subselect *si,
2826
select_result_interceptor *res)
2830
return select_lex->join->change_result(result);
2835
change select_result object of engine.
2837
@param si new subselect Item
2838
@param res new select_result object
2846
bool subselect_union_engine::change_result(Item_subselect *si,
2847
select_result_interceptor *res)
2850
int rc= unit->change_result(res, result);
2857
change select_result emulation, never should be called.
2859
@param si new subselect Item
2860
@param res new select_result object
2868
bool subselect_uniquesubquery_engine::change_result(Item_subselect *si,
2869
select_result_interceptor *res)
2877
Report about presence of tables in subquery.
2880
TRUE there are not tables used in subquery
2882
FALSE there are some tables in subquery
2884
bool subselect_single_select_engine::no_tables()
2886
return(select_lex->table_list.elements == 0);
2891
Check statically whether the subquery can return NULL
2894
subselect_single_select_engine::may_be_null()
2897
FALSE can guarantee that the subquery never return NULL
2900
bool subselect_single_select_engine::may_be_null()
2902
return ((no_tables() && !join->conds && !join->having) ? maybe_null : 1);
2907
Report about presence of tables in subquery.
2910
TRUE there are not tables used in subquery
2912
FALSE there are some tables in subquery
2914
bool subselect_union_engine::no_tables()
2916
for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
2918
if (sl->table_list.elements)
2926
Report about presence of tables in subquery.
2929
TRUE there are not tables used in subquery
2931
FALSE there are some tables in subquery
2934
bool subselect_uniquesubquery_engine::no_tables()
2936
/* returning value is correct, but this method should never be called */
2941
/******************************************************************************
2942
WL#1110 - Implementation of class subselect_hash_sj_engine
2943
******************************************************************************/
2947
Create all structures needed for IN execution that can live between PS
2951
- Create a temporary table to store the result of the IN subquery. The
2952
temporary table has one hash index on all its columns.
2953
- Create a new result sink that sends the result stream of the subquery to
2954
the temporary table,
2955
- Create and initialize a new JOIN_TAB, and TABLE_REF objects to perform
2956
lookups into the indexed temporary table.
2959
Currently Item_subselect::init() already chooses and creates at parse
2960
time an engine with a corresponding JOIN to execute the subquery.
2962
@retval TRUE if error
2963
@retval FALSE otherwise
2966
bool subselect_hash_sj_engine::init_permanent(List<Item> *tmp_columns)
2968
/* The result sink where we will materialize the subquery result. */
2969
select_union *tmp_result_sink;
2970
/* The table into which the subquery is materialized. */
2972
KEY *tmp_key; /* The only index on the temporary table. */
2973
uint tmp_key_parts; /* Number of keyparts in tmp_key. */
2974
Item_in_subselect *item_in= (Item_in_subselect *) item;
2976
DBUG_ENTER("subselect_hash_sj_engine::init_permanent");
2978
/* 1. Create/initialize materialization related objects. */
2981
Create and initialize a select result interceptor that stores the
2982
result stream in a temporary table. The temporary table itself is
2983
managed (created/filled/etc) internally by the interceptor.
2985
if (!(tmp_result_sink= new select_union))
2987
if (tmp_result_sink->create_result_table(
2988
thd, tmp_columns, TRUE,
2989
thd->options | TMP_TABLE_ALL_COLUMNS,
2990
"materialized subselect", TRUE))
2993
tmp_table= tmp_result_sink->table;
2994
tmp_key= tmp_table->key_info;
2995
tmp_key_parts= tmp_key->key_parts;
2998
If the subquery has blobs, or the total key lenght is bigger than some
2999
length, then the created index cannot be used for lookups and we
3000
can't use hash semi join. If this is the case, delete the temporary
3001
table since it will not be used, and tell the caller we failed to
3002
initialize the engine.
3004
if (tmp_table->s->keys == 0)
3006
DBUG_ASSERT(tmp_table->s->db_type() == myisam_hton);
3008
tmp_table->s->uniques ||
3009
tmp_table->key_info->key_length >= tmp_table->file->max_key_length() ||
3010
tmp_table->key_info->key_parts > tmp_table->file->max_key_parts());
3011
free_tmp_table(thd, tmp_table);
3016
result= tmp_result_sink;
3019
Make sure there is only one index on the temp table, and it doesn't have
3020
the extra key part created when s->uniques > 0.
3022
DBUG_ASSERT(tmp_table->s->keys == 1 && tmp_columns->elements == tmp_key_parts);
3025
/* 2. Create/initialize execution related objects. */
3028
Create and initialize the JOIN_TAB that represents an index lookup
3029
plan operator into the materialized subquery result. Notice that:
3030
- this JOIN_TAB has no corresponding JOIN (and doesn't need one), and
3031
- here we initialize only those members that are used by
3032
subselect_uniquesubquery_engine, so these objects are incomplete.
3034
if (!(tab= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB))))
3036
tab->table= tmp_table;
3037
tab->ref.key= 0; /* The only temp table index. */
3038
tab->ref.key_length= tmp_key->key_length;
3039
if (!(tab->ref.key_buff=
3040
(uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) ||
3041
!(tab->ref.key_copy=
3042
(store_key**) thd->alloc((sizeof(store_key*) *
3043
(tmp_key_parts + 1)))) ||
3045
(Item**) thd->alloc(sizeof(Item*) * tmp_key_parts)))
3048
KEY_PART_INFO *cur_key_part= tmp_key->key_part;
3049
store_key **ref_key= tab->ref.key_copy;
3050
uchar *cur_ref_buff= tab->ref.key_buff;
3052
for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
3054
tab->ref.items[i]= item_in->left_expr->element_index(i);
3055
int null_count= test(cur_key_part->field->real_maybe_null());
3056
*ref_key= new store_key_item(thd, cur_key_part->field,
3058
the NULL byte is taken into account in
3059
cur_key_part->store_length, so instead of
3060
cur_ref_buff + test(maybe_null), we could
3061
use that information instead.
3063
cur_ref_buff + null_count,
3064
null_count ? tab->ref.key_buff : 0,
3065
cur_key_part->length, tab->ref.items[i]);
3066
cur_ref_buff+= cur_key_part->store_length;
3068
*ref_key= NULL; /* End marker. */
3069
tab->ref.key_err= 1;
3070
tab->ref.key_parts= tmp_key_parts;
3077
Initialize members of the engine that need to be re-initilized at each
3080
@retval TRUE if a memory allocation error occurred
3081
@retval FALSE if success
3084
bool subselect_hash_sj_engine::init_runtime()
3087
Create and optimize the JOIN that will be used to materialize
3088
the subquery if not yet created.
3090
materialize_engine->prepare();
3091
/* Let our engine reuse this query plan for materialization. */
3092
materialize_join= materialize_engine->join;
3093
materialize_join->change_result(result);
3098
subselect_hash_sj_engine::~subselect_hash_sj_engine()
3102
free_tmp_table(thd, tab->table);
3107
Cleanup performed after each PS execution.
3110
Called in the end of JOIN::prepare for PS from Item_subselect::cleanup.
3113
void subselect_hash_sj_engine::cleanup()
3115
is_materialized= FALSE;
3116
result->cleanup(); /* Resets the temp table as well. */
3117
materialize_engine->cleanup();
3118
subselect_uniquesubquery_engine::cleanup();
3123
Execute a subquery IN predicate via materialization.
3126
If needed materialize the subquery into a temporary table, then
3127
copmpute the predicate via a lookup into this table.
3129
@retval TRUE if error
3130
@retval FALSE otherwise
3133
int subselect_hash_sj_engine::exec()
3135
Item_in_subselect *item_in= (Item_in_subselect *) item;
3137
DBUG_ENTER("subselect_hash_sj_engine::exec");
3140
Optimize and materialize the subquery during the first execution of
3141
the subquery predicate.
3143
if (!is_materialized)
3146
SELECT_LEX *save_select= thd->lex->current_select;
3147
thd->lex->current_select= materialize_engine->select_lex;
3148
if ((res= materialize_join->optimize()))
3150
materialize_join->exec();
3151
if ((res= test(materialize_join->error || thd->is_fatal_error)))
3156
- Unlock all subquery tables as we don't need them. To implement this
3157
we need to add new functionality to JOIN::join_free that can unlock
3158
all tables in a subquery (and all its subqueries).
3159
- The temp table used for grouping in the subquery can be freed
3160
immediately after materialization (yet it's done together with
3163
is_materialized= TRUE;
3165
If the subquery returned no rows, the temporary table is empty, so we know
3166
directly that the result of IN is FALSE. We first update the table
3167
statistics, then we test if the temporary table for the query result is
3170
tab->table->file->info(HA_STATUS_VARIABLE);
3171
if (!tab->table->file->stats.records)
3173
empty_result_set= TRUE;
3174
item_in->value= FALSE;
3175
/* TODO: check we need this: item_in->null_value= FALSE; */
3178
/* Set tmp_param only if its usable, i.e. tmp_param->copy_field != NULL. */
3179
tmp_param= &(item_in->unit->outer_select()->join->tmp_table_param);
3180
if (tmp_param && !tmp_param->copy_field)
3184
thd->lex->current_select= save_select;
3190
Lookup the left IN operand in the hash index of the materialized subquery.
3192
DBUG_RETURN(subselect_uniquesubquery_engine::exec());
3197
Print the state of this engine into a string for debugging and views.
3200
void subselect_hash_sj_engine::print(String *str, enum_query_type query_type)
3202
str->append(STRING_WITH_LEN(" <materialize> ("));
3203
materialize_engine->print(str, query_type);
3204
str->append(STRING_WITH_LEN(" ), "));
3206
subselect_uniquesubquery_engine::print(str, query_type);
3208
str->append(STRING_WITH_LEN(
3209
"<the access method for lookups is not yet created>"
added
removed
sql/item_subselect.cc
