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), is_correlated(FALSE)
47
item value is NULL if select_subselect not changed this value
48
(i.e. some rows will be found returned)
54
void Item_subselect::init(st_select_lex *select_lex,
55
select_subselect *result)
58
Please see Item_singlerow_subselect::invalidate_and_restore_select_lex(),
59
which depends on alterations to the parse tree implemented here.
62
DBUG_ENTER("Item_subselect::init");
63
DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
64
unit= select_lex->master_unit();
69
Item can be changed in JOIN::prepare while engine in JOIN::optimize
70
=> we do not copy old_engine here
72
engine= unit->item->engine;
73
parsing_place= unit->item->parsing_place;
74
unit->item->engine= 0;
76
engine->change_result(this, result);
80
SELECT_LEX *outer_select= unit->outer_select();
82
do not take into account expression inside aggregate functions because
83
they can access original table fields
85
parsing_place= (outer_select->in_sum_expr ?
87
outer_select->parsing_place);
89
engine= new subselect_union_engine(unit, result, this);
91
engine= new subselect_single_select_engine(select_lex, result, this);
94
SELECT_LEX *upper= unit->outer_select();
95
if (upper->parsing_place == IN_HAVING)
96
upper->subquery_in_having= 1;
102
Item_subselect::get_select_lex()
104
return unit->first_select();
107
void Item_subselect::cleanup()
109
DBUG_ENTER("Item_subselect::cleanup");
110
Item_result_field::cleanup();
125
void Item_singlerow_subselect::cleanup()
127
DBUG_ENTER("Item_singlerow_subselect::cleanup");
129
Item_subselect::cleanup();
133
Item_subselect::~Item_subselect()
138
Item_subselect::trans_res
139
Item_subselect::select_transformer(JOIN *join)
141
DBUG_ENTER("Item_subselect::select_transformer");
146
bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
148
char const *save_where= thd_param->where;
152
DBUG_ASSERT(fixed == 0);
153
engine->set_thd((thd= thd_param));
155
if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
158
if (!(res= engine->prepare()))
160
// all transformation is done (used by prepared statements)
167
// did we changed top item of WHERE condition
168
if (unit->outer_select()->where == (*ref))
169
unit->outer_select()->where= substitution; // correct WHERE for PS
170
else if (unit->outer_select()->having == (*ref))
171
unit->outer_select()->having= substitution; // correct HAVING for PS
173
(*ref)= substitution;
174
substitution->name= name;
175
if (have_to_be_excluded)
178
thd->where= "checking transformed subquery";
180
ret= (*ref)->fix_fields(thd, ref);
181
thd->where= save_where;
184
// Is it one field subselect?
185
if (engine->cols() > max_columns)
187
my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
190
fix_length_and_dec();
195
if ((uncacheable= engine->uncacheable()))
198
if (uncacheable & UNCACHEABLE_RAND)
199
used_tables_cache|= RAND_TABLE_BIT;
204
thd->where= save_where;
209
bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
215
for (SELECT_LEX *lex= unit->first_select(); lex; lex= lex->next_select())
217
List_iterator<Item> li(lex->item_list);
221
if (lex->where && (lex->where)->walk(processor, walk_subquery, argument))
223
if (lex->having && (lex->having)->walk(processor, walk_subquery,
229
if (item->walk(processor, walk_subquery, argument))
232
for (order= (ORDER*) lex->order_list.first ; order; order= order->next)
234
if ((*order->item)->walk(processor, walk_subquery, argument))
237
for (order= (ORDER*) lex->group_list.first ; order; order= order->next)
239
if ((*order->item)->walk(processor, walk_subquery, argument))
244
return (this->*processor)(argument);
248
bool Item_subselect::exec()
253
/* Do not execute subselect in case of a fatal error */
256
Simulate a failure in sub-query execution. Used to test e.g.
257
out of memory or query being killed conditions.
259
DBUG_EXECUTE_IF("subselect_exec_fail", return 1;);
271
Item::Type Item_subselect::type() const
273
return SUBSELECT_ITEM;
277
void Item_subselect::fix_length_and_dec()
279
engine->fix_length_and_dec(0);
283
table_map Item_subselect::used_tables() const
285
return (table_map) (engine->uncacheable() ? used_tables_cache : 0L);
289
bool Item_subselect::const_item() const
291
return const_item_cache;
294
Item *Item_subselect::get_tmp_table_item(THD *thd_arg)
296
if (!with_sum_func && !const_item())
297
return new Item_field(result_field);
298
return copy_or_same(thd_arg);
301
void Item_subselect::update_used_tables()
303
if (!engine->uncacheable())
305
// did all used tables become static?
306
if (!(used_tables_cache & ~engine->upper_select_const_tables()))
312
void Item_subselect::print(String *str, enum_query_type query_type)
317
engine->print(str, query_type);
321
str->append("(...)");
325
Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex)
326
:Item_subselect(), value(0)
328
DBUG_ENTER("Item_singlerow_subselect::Item_singlerow_subselect");
329
init(select_lex, new select_singlerow_subselect(this));
331
max_columns= UINT_MAX;
336
Item_singlerow_subselect::invalidate_and_restore_select_lex()
338
DBUG_ENTER("Item_singlerow_subselect::invalidate_and_restore_select_lex");
339
st_select_lex *result= get_select_lex();
344
This code restore the parse tree in it's state before the execution of
345
Item_singlerow_subselect::Item_singlerow_subselect(),
346
and in particular decouples this object from the SELECT_LEX,
347
so that the SELECT_LEX can be used with a different flavor
348
or Item_subselect instead, as part of query rewriting.
355
Item_maxmin_subselect::Item_maxmin_subselect(THD *thd_param,
356
Item_subselect *parent,
357
st_select_lex *select_lex,
359
:Item_singlerow_subselect(), was_values(TRUE)
361
DBUG_ENTER("Item_maxmin_subselect::Item_maxmin_subselect");
363
init(select_lex, new select_max_min_finder_subselect(this, max_arg));
369
Following information was collected during performing fix_fields()
370
of Items belonged to subquery, which will be not repeated
372
used_tables_cache= parent->get_used_tables_cache();
373
const_item_cache= parent->get_const_item_cache();
376
this subquery always creates during preparation, so we can assign
384
void Item_maxmin_subselect::cleanup()
386
DBUG_ENTER("Item_maxmin_subselect::cleanup");
387
Item_singlerow_subselect::cleanup();
390
By default it is TRUE to avoid TRUE reporting by
391
Item_func_not_all/Item_func_nop_all if this item was never called.
393
Engine exec() set it to FALSE by reset_value_registration() call.
394
select_max_min_finder_subselect::send_data() set it back to TRUE if some
402
void Item_maxmin_subselect::print(String *str, enum_query_type query_type)
404
str->append(max?"<max>":"<min>", 5);
405
Item_singlerow_subselect::print(str, query_type);
409
void Item_singlerow_subselect::reset()
413
value->null_value= 1;
419
- We cant change name of Item_field or Item_ref, because it will
420
prevent it's correct resolving, but we should save name of
421
removed item => we do not make optimization if top item of
422
list is field or reference.
423
- switch off this optimization for prepare statement,
424
because we do not rollback this changes.
425
Make rollback for it, or special name resolving mode in 5.0.
427
Item_subselect::trans_res
428
Item_singlerow_subselect::select_transformer(JOIN *join)
433
SELECT_LEX *select_lex= join->select_lex;
434
Query_arena *arena= thd->stmt_arena;
436
if (!select_lex->master_unit()->is_union() &&
437
!select_lex->table_list.elements &&
438
select_lex->item_list.elements == 1 &&
439
!select_lex->item_list.head()->with_sum_func &&
441
We cant change name of Item_field or Item_ref, because it will
442
prevent it's correct resolving, but we should save name of
443
removed item => we do not make optimization if top item of
444
list is field or reference.
445
TODO: solve above problem
447
!(select_lex->item_list.head()->type() == FIELD_ITEM ||
448
select_lex->item_list.head()->type() == REF_ITEM) &&
449
!join->conds && !join->having &&
451
switch off this optimization for prepare statement,
452
because we do not rollback this changes
453
TODO: make rollback for it, or special name resolving mode in 5.0.
455
!arena->is_stmt_prepare_or_first_sp_execute()
459
have_to_be_excluded= 1;
460
if (thd->lex->describe)
462
char warn_buff[MYSQL_ERRMSG_SIZE];
463
sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
464
push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
465
ER_SELECT_REDUCED, warn_buff);
467
substitution= select_lex->item_list.head();
469
as far as we moved content to upper level, field which depend of
470
'upper' select is not really dependent => we remove this dependence
472
substitution->walk(&Item::remove_dependence_processor, 0,
473
(uchar *) select_lex->outer_select());
480
void Item_singlerow_subselect::store(uint i, Item *item)
483
row[i]->cache_value();
486
enum Item_result Item_singlerow_subselect::result_type() const
488
return engine->type();
492
Don't rely on the result type to calculate field type.
493
Ask the engine instead.
495
enum_field_types Item_singlerow_subselect::field_type() const
497
return engine->field_type();
500
void Item_singlerow_subselect::fix_length_and_dec()
502
if ((max_columns= engine->cols()) == 1)
504
engine->fix_length_and_dec(row= &value);
508
if (!(row= (Item_cache**) sql_alloc(sizeof(Item_cache*)*max_columns)))
510
engine->fix_length_and_dec(row);
513
unsigned_flag= value->unsigned_flag;
515
If there are not tables in subquery then ability to have NULL value
516
depends on SELECT list (if single row subquery have tables then it
517
always can be NULL if there are not records fetched).
519
if (engine->no_tables())
520
maybe_null= engine->may_be_null();
523
uint Item_singlerow_subselect::cols()
525
return engine->cols();
528
bool Item_singlerow_subselect::check_cols(uint c)
530
if (c != engine->cols())
532
my_error(ER_OPERAND_COLUMNS, MYF(0), c);
538
bool Item_singlerow_subselect::null_inside()
540
for (uint i= 0; i < max_columns ; i++)
542
if (row[i]->null_value)
548
void Item_singlerow_subselect::bring_value()
553
double Item_singlerow_subselect::val_real()
555
DBUG_ASSERT(fixed == 1);
556
if (!exec() && !value->null_value)
559
return value->val_real();
568
longlong Item_singlerow_subselect::val_int()
570
DBUG_ASSERT(fixed == 1);
571
if (!exec() && !value->null_value)
574
return value->val_int();
583
String *Item_singlerow_subselect::val_str(String *str)
585
if (!exec() && !value->null_value)
588
return value->val_str(str);
598
my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
600
if (!exec() && !value->null_value)
603
return value->val_decimal(decimal_value);
613
bool Item_singlerow_subselect::val_bool()
615
if (!exec() && !value->null_value)
618
return value->val_bool();
628
Item_exists_subselect::Item_exists_subselect(st_select_lex *select_lex):
631
DBUG_ENTER("Item_exists_subselect::Item_exists_subselect");
633
init(select_lex, new select_exists_subselect(this));
634
max_columns= UINT_MAX;
635
null_value= 0; //can't be NULL
636
maybe_null= 0; //can't be NULL
642
void Item_exists_subselect::print(String *str, enum_query_type query_type)
644
str->append(STRING_WITH_LEN("exists"));
645
Item_subselect::print(str, query_type);
649
bool Item_in_subselect::test_limit(st_select_lex_unit *unit_arg)
651
if (unit_arg->fake_select_lex &&
652
unit_arg->fake_select_lex->test_limit())
655
SELECT_LEX *sl= unit_arg->first_select();
656
for (; sl; sl= sl->next_select())
658
if (sl->test_limit())
664
Item_in_subselect::Item_in_subselect(Item * left_exp,
665
st_select_lex *select_lex):
666
Item_exists_subselect(), optimizer(0), transformed(0),
667
pushed_cond_guards(NULL), upper_item(0)
669
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
671
init(select_lex, new select_exists_subselect(this));
672
max_columns= UINT_MAX;
676
//if test_limit will fail then error will be reported to client
677
test_limit(select_lex->master_unit());
681
Item_allany_subselect::Item_allany_subselect(Item * left_exp,
682
chooser_compare_func_creator fc,
683
st_select_lex *select_lex,
685
:Item_in_subselect(), func_creator(fc), all(all_arg)
687
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
689
func= func_creator(all_arg);
690
init(select_lex, new select_exists_subselect(this));
694
//if test_limit will fail then error will be reported to client
695
test_limit(select_lex->master_unit());
700
void Item_exists_subselect::fix_length_and_dec()
704
max_columns= engine->cols();
705
/* We need only 1 row to determine existence */
706
unit->global_parameters->select_limit= new Item_int((int32) 1);
709
double Item_exists_subselect::val_real()
711
DBUG_ASSERT(fixed == 1);
717
return (double) value;
720
longlong Item_exists_subselect::val_int()
722
DBUG_ASSERT(fixed == 1);
733
Return the result of EXISTS as a string value
735
Converts the true/false result into a string value.
736
Note that currently this cannot be NULL, so if the query exection fails
739
@param decimal_value[out] buffer to hold the resulting string value
740
@retval Pointer to the converted string.
741
Can't be a NULL pointer, as currently
742
EXISTS cannot return NULL.
745
String *Item_exists_subselect::val_str(String *str)
747
DBUG_ASSERT(fixed == 1);
750
str->set((ulonglong)value,&my_charset_bin);
756
Return the result of EXISTS as a decimal value
758
Converts the true/false result into a decimal value.
759
Note that currently this cannot be NULL, so if the query exection fails
762
@param decimal_value[out] Buffer to hold the resulting decimal value
763
@retval Pointer to the converted decimal.
764
Can't be a NULL pointer, as currently
765
EXISTS cannot return NULL.
768
my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value)
770
DBUG_ASSERT(fixed == 1);
773
int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
774
return decimal_value;
778
bool Item_exists_subselect::val_bool()
780
DBUG_ASSERT(fixed == 1);
790
double Item_in_subselect::val_real()
793
As far as Item_in_subselect called only from Item_in_optimizer this
794
method should not be used
797
DBUG_ASSERT(fixed == 1);
805
if (was_null && !value)
807
return (double) value;
811
longlong Item_in_subselect::val_int()
814
As far as Item_in_subselect called only from Item_in_optimizer this
815
method should not be used
818
DBUG_ASSERT(fixed == 1);
826
if (was_null && !value)
832
String *Item_in_subselect::val_str(String *str)
835
As far as Item_in_subselect called only from Item_in_optimizer this
836
method should not be used
839
DBUG_ASSERT(fixed == 1);
847
if (was_null && !value)
852
str->set((ulonglong)value, &my_charset_bin);
857
bool Item_in_subselect::val_bool()
859
DBUG_ASSERT(fixed == 1);
865
Must mark the IN predicate as NULL so as to make sure an enclosing NOT
866
predicate will return FALSE. See the comments in
867
subselect_uniquesubquery_engine::copy_ref_key for further details.
872
if (was_null && !value)
877
my_decimal *Item_in_subselect::val_decimal(my_decimal *decimal_value)
880
As far as Item_in_subselect called only from Item_in_optimizer this
881
method should not be used
885
DBUG_ASSERT(fixed == 1);
892
if (was_null && !value)
894
int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
895
return decimal_value;
900
Rewrite a single-column IN/ALL/ANY subselect
903
Item_in_subselect::single_value_transformer()
904
join Join object of the subquery (i.e. 'child' join).
905
func Subquery comparison creator
908
Rewrite a single-column subquery using rule-based approach. The subquery
910
oe $cmp$ (SELECT ie FROM ... WHERE subq_where ... HAVING subq_having)
912
First, try to convert the subquery to scalar-result subquery in one of
915
- oe $cmp$ (SELECT MAX(...) ) // handled by Item_singlerow_subselect
916
- oe $cmp$ <max>(SELECT ...) // handled by Item_maxmin_subselect
918
If that fails, the subquery will be handled with class Item_in_optimizer,
919
Inject the predicates into subquery, i.e. convert it to:
921
- If the subquery has aggregates, GROUP BY, or HAVING, convert to
923
SELECT ie FROM ... HAVING subq_having AND
924
trigcond(oe $cmp$ ref_or_null_helper<ie>)
926
the addition is wrapped into trigger only when we want to distinguish
927
between NULL and FALSE results.
929
- Otherwise (no aggregates/GROUP BY/HAVING) convert it to one of the
932
= If we don't need to distinguish between NULL and FALSE subquery:
934
SELECT 1 FROM ... WHERE (oe $cmp$ ie) AND subq_where
936
= If we need to distinguish between those:
939
WHERE subq_where AND trigcond((oe $cmp$ ie) OR (ie IS NULL))
940
HAVING trigcond(<is_not_null_test>(ie))
943
RES_OK - OK, either subquery was transformed, or appopriate
944
predicates where injected into it.
945
RES_REDUCE - The subquery was reduced to non-subquery
949
Item_subselect::trans_res
950
Item_in_subselect::single_value_transformer(JOIN *join,
953
SELECT_LEX *select_lex= join->select_lex;
954
DBUG_ENTER("Item_in_subselect::single_value_transformer");
957
Check that the right part of the subselect contains no more than one
958
column. E.g. in SELECT 1 IN (SELECT * ..) the right part is (SELECT * ...)
960
if (select_lex->item_list.elements > 1)
962
my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
963
DBUG_RETURN(RES_ERROR);
967
If this is an ALL/ANY single-value subselect, try to rewrite it with
968
a MIN/MAX subselect. We can do that if a possible NULL result of the
969
subselect can be ignored.
970
E.g. SELECT * FROM t1 WHERE b > ANY (SELECT a FROM t2) can be rewritten
971
with SELECT * FROM t1 WHERE b > (SELECT MAX(a) FROM t2).
972
We can't check that this optimization is safe if it's not a top-level
973
item of the WHERE clause (e.g. because the WHERE clause can contain IS
974
NULL/IS NOT NULL functions). If so, we rewrite ALL/ANY with NOT EXISTS
975
later in this method.
977
if ((abort_on_null || (upper_item && upper_item->top_level())) &&
978
!select_lex->master_unit()->uncacheable && !func->eqne_op())
982
// It is second (third, ...) SELECT of UNION => All is done
987
if (!select_lex->group_list.elements &&
988
!select_lex->having &&
989
!select_lex->with_sum_func &&
990
!(select_lex->next_select()) &&
991
select_lex->table_list.elements)
993
Item_sum_hybrid *item;
994
nesting_map save_allow_sum_func;
998
(ALL && (> || =>)) || (ANY && (< || =<))
999
for ALL condition is inverted
1001
item= new Item_sum_max(*select_lex->ref_pointer_array);
1006
(ALL && (< || =<)) || (ANY && (> || =>))
1007
for ALL condition is inverted
1009
item= new Item_sum_min(*select_lex->ref_pointer_array);
1012
upper_item->set_sum_test(item);
1013
*select_lex->ref_pointer_array= item;
1015
List_iterator<Item> it(select_lex->item_list);
1020
save_allow_sum_func= thd->lex->allow_sum_func;
1021
thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
1023
Item_sum_(max|min) can't substitute other item => we can use 0 as
1024
reference, also Item_sum_(max|min) can't be fixed after creation, so
1025
we do not check item->fixed
1027
if (item->fix_fields(thd, 0))
1028
DBUG_RETURN(RES_ERROR);
1029
thd->lex->allow_sum_func= save_allow_sum_func;
1030
/* we added aggregate function => we have to change statistic */
1031
count_field_types(select_lex, &join->tmp_table_param, join->all_fields,
1034
subs= new Item_singlerow_subselect(select_lex);
1038
Item_maxmin_subselect *item;
1039
subs= item= new Item_maxmin_subselect(thd, this, select_lex, func->l_op());
1041
upper_item->set_sub_test(item);
1043
/* fix fields is already called for left expression */
1044
substitution= func->create(left_expr, subs);
1045
DBUG_RETURN(RES_OK);
1050
/* We're invoked for the 1st (or the only) SELECT in the subquery UNION */
1051
SELECT_LEX_UNIT *master_unit= select_lex->master_unit();
1052
substitution= optimizer;
1054
SELECT_LEX *current= thd->lex->current_select, *up;
1056
thd->lex->current_select= up= current->return_after_parsing();
1057
//optimizer never use Item **ref => we can pass 0 as parameter
1058
if (!optimizer || optimizer->fix_left(thd, 0))
1060
thd->lex->current_select= current;
1061
DBUG_RETURN(RES_ERROR);
1063
thd->lex->current_select= current;
1066
As far as Item_ref_in_optimizer do not substitute itself on fix_fields
1067
we can use same item for all selects.
1069
expr= new Item_direct_ref(&select_lex->context,
1070
(Item**)optimizer->get_cache(),
1071
(char *)"<no matter>",
1072
(char *)in_left_expr_name);
1074
master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
1076
if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
1078
if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool))))
1079
DBUG_RETURN(RES_ERROR);
1080
pushed_cond_guards[0]= TRUE;
1083
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
1084
if (join->having || select_lex->with_sum_func ||
1085
select_lex->group_list.elements)
1088
Item *item= func->create(expr,
1089
new Item_ref_null_helper(&select_lex->context,
1094
this->full_name()));
1095
if (!abort_on_null && left_expr->maybe_null)
1098
We can encounter "NULL IN (SELECT ...)". Wrap the added condition
1101
item= new Item_func_trig_cond(item, get_cond_guard(0));
1105
AND and comparison functions can't be changed during fix_fields()
1106
we can assign select_lex->having here, and pass 0 as last
1107
argument (reference) to fix_fields()
1109
select_lex->having= join->having= and_items(join->having, item);
1110
if (join->having == item)
1111
item->name= (char*)in_having_cond;
1112
select_lex->having_fix_field= 1;
1114
we do not check join->having->fixed, because Item_and (from and_items)
1115
or comparison function (from func->create) can't be fixed after creation
1117
tmp= join->having->fix_fields(thd, 0);
1118
select_lex->having_fix_field= 0;
1120
DBUG_RETURN(RES_ERROR);
1124
Item *item= (Item*) select_lex->item_list.head();
1126
if (select_lex->table_list.elements)
1129
Item *having= item, *orig_item= item;
1130
select_lex->item_list.empty();
1131
select_lex->item_list.push_back(new Item_int("Not_used",
1133
MY_INT64_NUM_DECIMAL_DIGITS));
1134
select_lex->ref_pointer_array[0]= select_lex->item_list.head();
1136
item= func->create(expr, item);
1137
if (!abort_on_null && orig_item->maybe_null)
1139
having= new Item_is_not_null_test(this, having);
1140
if (left_expr->maybe_null)
1142
if (!(having= new Item_func_trig_cond(having,
1143
get_cond_guard(0))))
1144
DBUG_RETURN(RES_ERROR);
1147
Item_is_not_null_test can't be changed during fix_fields()
1148
we can assign select_lex->having here, and pass 0 as last
1149
argument (reference) to fix_fields()
1151
having->name= (char*)in_having_cond;
1152
select_lex->having= join->having= having;
1153
select_lex->having_fix_field= 1;
1155
we do not check join->having->fixed, because Item_and (from
1156
and_items) or comparison function (from func->create) can't be
1157
fixed after creation
1159
tmp= join->having->fix_fields(thd, 0);
1160
select_lex->having_fix_field= 0;
1162
DBUG_RETURN(RES_ERROR);
1163
item= new Item_cond_or(item,
1164
new Item_func_isnull(orig_item));
1167
If we may encounter NULL IN (SELECT ...) and care whether subquery
1168
result is NULL or FALSE, wrap condition in a trig_cond.
1170
if (!abort_on_null && left_expr->maybe_null)
1172
if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
1173
DBUG_RETURN(RES_ERROR);
1176
TODO: figure out why the following is done here in
1177
single_value_transformer but there is no corresponding action in
1178
row_value_transformer?
1180
item->name= (char *)in_additional_cond;
1183
AND can't be changed during fix_fields()
1184
we can assign select_lex->having here, and pass 0 as last
1185
argument (reference) to fix_fields()
1187
select_lex->where= join->conds= and_items(join->conds, item);
1188
select_lex->where->top_level_item();
1190
we do not check join->conds->fixed, because Item_and can't be fixed
1193
if (join->conds->fix_fields(thd, 0))
1194
DBUG_RETURN(RES_ERROR);
1199
if (select_lex->master_unit()->is_union())
1202
comparison functions can't be changed during fix_fields()
1203
we can assign select_lex->having here, and pass 0 as last
1204
argument (reference) to fix_fields()
1208
new Item_ref_null_helper(&select_lex->context, this,
1209
select_lex->ref_pointer_array,
1210
(char *)"<no matter>",
1211
(char *)"<result>"));
1212
if (!abort_on_null && left_expr->maybe_null)
1214
if (!(new_having= new Item_func_trig_cond(new_having,
1215
get_cond_guard(0))))
1216
DBUG_RETURN(RES_ERROR);
1218
new_having->name= (char*)in_having_cond;
1219
select_lex->having= join->having= new_having;
1220
select_lex->having_fix_field= 1;
1223
we do not check join->having->fixed, because comparison function
1224
(from func->create) can't be fixed after creation
1226
tmp= join->having->fix_fields(thd, 0);
1227
select_lex->having_fix_field= 0;
1229
DBUG_RETURN(RES_ERROR);
1233
// it is single select without tables => possible optimization
1234
// remove the dependence mark since the item is moved to upper
1235
// select and is not outer anymore.
1236
item->walk(&Item::remove_dependence_processor, 0,
1237
(uchar *) select_lex->outer_select());
1238
item= func->create(left_expr, item);
1239
// fix_field of item will be done in time of substituting
1241
have_to_be_excluded= 1;
1242
if (thd->lex->describe)
1244
char warn_buff[MYSQL_ERRMSG_SIZE];
1245
sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
1246
push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
1247
ER_SELECT_REDUCED, warn_buff);
1249
DBUG_RETURN(RES_REDUCE);
1254
DBUG_RETURN(RES_OK);
1258
Item_subselect::trans_res
1259
Item_in_subselect::row_value_transformer(JOIN *join)
1261
SELECT_LEX *select_lex= join->select_lex;
1262
Item *having_item= 0;
1263
uint cols_num= left_expr->cols();
1264
bool is_having_used= (join->having || select_lex->with_sum_func ||
1265
select_lex->group_list.first ||
1266
!select_lex->table_list.elements);
1267
DBUG_ENTER("Item_in_subselect::row_value_transformer");
1269
if (select_lex->item_list.elements != left_expr->cols())
1271
my_error(ER_OPERAND_COLUMNS, MYF(0), left_expr->cols());
1272
DBUG_RETURN(RES_ERROR);
1277
//first call for this unit
1278
SELECT_LEX_UNIT *master_unit= select_lex->master_unit();
1279
substitution= optimizer;
1281
SELECT_LEX *current= thd->lex->current_select, *up;
1282
thd->lex->current_select= up= current->return_after_parsing();
1283
//optimizer never use Item **ref => we can pass 0 as parameter
1284
if (!optimizer || optimizer->fix_left(thd, 0))
1286
thd->lex->current_select= current;
1287
DBUG_RETURN(RES_ERROR);
1290
// we will refer to upper level cache array => we have to save it in PS
1291
optimizer->keep_top_level_cache();
1293
thd->lex->current_select= current;
1294
master_unit->uncacheable|= UNCACHEABLE_DEPENDENT;
1296
if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards)
1298
if (!(pushed_cond_guards= (bool*)join->thd->alloc(sizeof(bool) *
1299
left_expr->cols())))
1300
DBUG_RETURN(RES_ERROR);
1301
for (uint i= 0; i < cols_num; i++)
1302
pushed_cond_guards[i]= TRUE;
1306
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
1310
(l1, l2, l3) IN (SELECT v1, v2, v3 ... HAVING having) =>
1311
EXISTS (SELECT ... HAVING having and
1312
(l1 = v1 or is null v1) and
1313
(l2 = v2 or is null v2) and
1314
(l3 = v3 or is null v3) and
1315
is_not_null_test(v1) and
1316
is_not_null_test(v2) and
1317
is_not_null_test(v3))
1318
where is_not_null_test used to register nulls in case if we have
1319
not found matching to return correct NULL value
1320
TODO: say here explicitly if the order of AND parts matters or not.
1322
Item *item_having_part2= 0;
1323
for (uint i= 0; i < cols_num; i++)
1325
DBUG_ASSERT((left_expr->fixed &&
1326
select_lex->ref_pointer_array[i]->fixed) ||
1327
(select_lex->ref_pointer_array[i]->type() == REF_ITEM &&
1328
((Item_ref*)(select_lex->ref_pointer_array[i]))->ref_type() ==
1329
Item_ref::OUTER_REF));
1330
if (select_lex->ref_pointer_array[i]->
1331
check_cols(left_expr->element_index(i)->cols()))
1332
DBUG_RETURN(RES_ERROR);
1334
new Item_func_eq(new
1335
Item_ref(&select_lex->context,
1336
(*optimizer->get_cache())->
1338
(char *)"<no matter>",
1339
(char *)in_left_expr_name),
1341
Item_ref(&select_lex->context,
1342
select_lex->ref_pointer_array + i,
1343
(char *)"<no matter>",
1344
(char *)"<list ref>")
1347
new Item_func_isnull(new
1348
Item_ref(&select_lex->context,
1349
select_lex->ref_pointer_array+i,
1350
(char *)"<no matter>",
1351
(char *)"<list ref>")
1353
Item *col_item= new Item_cond_or(item_eq, item_isnull);
1354
if (!abort_on_null && left_expr->element_index(i)->maybe_null)
1356
if (!(col_item= new Item_func_trig_cond(col_item, get_cond_guard(i))))
1357
DBUG_RETURN(RES_ERROR);
1359
having_item= and_items(having_item, col_item);
1361
Item *item_nnull_test=
1362
new Item_is_not_null_test(this,
1363
new Item_ref(&select_lex->context,
1365
ref_pointer_array + i,
1366
(char *)"<no matter>",
1367
(char *)"<list ref>"));
1368
if (!abort_on_null && left_expr->element_index(i)->maybe_null)
1370
if (!(item_nnull_test=
1371
new Item_func_trig_cond(item_nnull_test, get_cond_guard(i))))
1372
DBUG_RETURN(RES_ERROR);
1374
item_having_part2= and_items(item_having_part2, item_nnull_test);
1375
item_having_part2->top_level_item();
1377
having_item= and_items(having_item, item_having_part2);
1378
having_item->top_level_item();
1383
(l1, l2, l3) IN (SELECT v1, v2, v3 ... WHERE where) =>
1384
EXISTS (SELECT ... WHERE where and
1385
(l1 = v1 or is null v1) and
1386
(l2 = v2 or is null v2) and
1387
(l3 = v3 or is null v3)
1388
HAVING is_not_null_test(v1) and
1389
is_not_null_test(v2) and
1390
is_not_null_test(v3))
1391
where is_not_null_test register NULLs values but reject rows
1393
in case when we do not need correct NULL, we have simplier construction:
1394
EXISTS (SELECT ... WHERE where and
1399
Item *where_item= 0;
1400
for (uint i= 0; i < cols_num; i++)
1402
Item *item, *item_isnull;
1403
DBUG_ASSERT((left_expr->fixed &&
1404
select_lex->ref_pointer_array[i]->fixed) ||
1405
(select_lex->ref_pointer_array[i]->type() == REF_ITEM &&
1406
((Item_ref*)(select_lex->ref_pointer_array[i]))->ref_type() ==
1407
Item_ref::OUTER_REF));
1408
if (select_lex->ref_pointer_array[i]->
1409
check_cols(left_expr->element_index(i)->cols()))
1410
DBUG_RETURN(RES_ERROR);
1412
new Item_func_eq(new
1413
Item_direct_ref(&select_lex->context,
1414
(*optimizer->get_cache())->
1416
(char *)"<no matter>",
1417
(char *)in_left_expr_name),
1419
Item_direct_ref(&select_lex->context,
1421
ref_pointer_array+i,
1422
(char *)"<no matter>",
1423
(char *)"<list ref>")
1427
Item *having_col_item=
1428
new Item_is_not_null_test(this,
1430
Item_ref(&select_lex->context,
1431
select_lex->ref_pointer_array + i,
1432
(char *)"<no matter>",
1433
(char *)"<list ref>"));
1437
Item_func_isnull(new
1438
Item_direct_ref(&select_lex->context,
1440
ref_pointer_array+i,
1441
(char *)"<no matter>",
1442
(char *)"<list ref>")
1444
item= new Item_cond_or(item, item_isnull);
1446
TODO: why we create the above for cases where the right part
1449
if (left_expr->element_index(i)->maybe_null)
1451
if (!(item= new Item_func_trig_cond(item, get_cond_guard(i))))
1452
DBUG_RETURN(RES_ERROR);
1453
if (!(having_col_item=
1454
new Item_func_trig_cond(having_col_item, get_cond_guard(i))))
1455
DBUG_RETURN(RES_ERROR);
1457
having_item= and_items(having_item, having_col_item);
1459
where_item= and_items(where_item, item);
1462
AND can't be changed during fix_fields()
1463
we can assign select_lex->where here, and pass 0 as last
1464
argument (reference) to fix_fields()
1466
select_lex->where= join->conds= and_items(join->conds, where_item);
1467
select_lex->where->top_level_item();
1468
if (join->conds->fix_fields(thd, 0))
1469
DBUG_RETURN(RES_ERROR);
1474
select_lex->having= join->having= and_items(join->having, having_item);
1475
if (having_item == select_lex->having)
1476
having_item->name= (char*)in_having_cond;
1477
select_lex->having->top_level_item();
1479
AND can't be changed during fix_fields()
1480
we can assign select_lex->having here, and pass 0 as last
1481
argument (reference) to fix_fields()
1483
select_lex->having_fix_field= 1;
1484
res= join->having->fix_fields(thd, 0);
1485
select_lex->having_fix_field= 0;
1488
DBUG_RETURN(RES_ERROR);
1492
DBUG_RETURN(RES_OK);
1496
Item_subselect::trans_res
1497
Item_in_subselect::select_transformer(JOIN *join)
1499
return select_in_like_transformer(join, &eq_creator);
1504
Prepare IN/ALL/ANY/SOME subquery transformation and call appropriate
1505
transformation function.
1507
To decide which transformation procedure (scalar or row) applicable here
1508
we have to call fix_fields() for left expression to be able to call
1509
cols() method on it. Also this method make arena management for
1510
underlying transformation methods.
1512
@param join JOIN object of transforming subquery
1513
@param func creator of condition function of subquery
1518
RES_REDUCE OK, and current subquery was reduced during
1524
Item_subselect::trans_res
1525
Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
1527
Query_arena *arena, backup;
1528
SELECT_LEX *current= thd->lex->current_select, *up;
1529
const char *save_where= thd->where;
1530
Item_subselect::trans_res res= RES_ERROR;
1533
DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
1537
IN/SOME/ALL/ANY subqueries aren't support LIMIT clause. Without it
1538
ORDER BY clause becomes meaningless thus we drop it here.
1540
SELECT_LEX *sl= current->master_unit()->first_select();
1541
for (; sl; sl= sl->next_select())
1550
DBUG_RETURN(RES_OK);
1553
thd->where= "IN/ALL/ANY subquery";
1556
In some optimisation cases we will not need this Item_in_optimizer
1557
object, but we can't know it here, but here we need address correct
1558
reference on left expresion.
1562
arena= thd->activate_stmt_arena_if_needed(&backup);
1563
result= (!(optimizer= new Item_in_optimizer(left_expr, this)));
1565
thd->restore_active_arena(arena, &backup);
1570
thd->lex->current_select= up= current->return_after_parsing();
1571
result= (!left_expr->fixed &&
1572
left_expr->fix_fields(thd, optimizer->arguments()));
1573
/* fix_fields can change reference to left_expr, we need reassign it */
1574
left_expr= optimizer->arguments()[0];
1576
thd->lex->current_select= current;
1581
arena= thd->activate_stmt_arena_if_needed(&backup);
1584
Both transformers call fix_fields() only for Items created inside them,
1585
and all that items do not make permanent changes in current item arena
1586
which allow to us call them with changed arena (if we do not know nature
1587
of Item, we have to call fix_fields() for it only with original arena to
1590
if (left_expr->cols() == 1)
1591
res= single_value_transformer(join, func);
1594
/* we do not support row operation for ALL/ANY/SOME */
1595
if (func != &eq_creator)
1598
thd->restore_active_arena(arena, &backup);
1599
my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
1600
DBUG_RETURN(RES_ERROR);
1602
res= row_value_transformer(join);
1605
thd->restore_active_arena(arena, &backup);
1607
thd->where= save_where;
1612
void Item_in_subselect::print(String *str, enum_query_type query_type)
1615
str->append(STRING_WITH_LEN("<exists>"));
1618
left_expr->print(str, query_type);
1619
str->append(STRING_WITH_LEN(" in "));
1621
Item_subselect::print(str, query_type);
1625
bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref)
1629
if (thd_arg->lex->view_prepare_mode && left_expr && !left_expr->fixed)
1630
result = left_expr->fix_fields(thd_arg, &left_expr);
1632
return result || Item_subselect::fix_fields(thd_arg, ref);
1636
Item_subselect::trans_res
1637
Item_allany_subselect::select_transformer(JOIN *join)
1641
upper_item->show= 1;
1642
return select_in_like_transformer(join, func);
1646
void Item_allany_subselect::print(String *str, enum_query_type query_type)
1649
str->append(STRING_WITH_LEN("<exists>"));
1652
left_expr->print(str, query_type);
1654
str->append(func->symbol(all));
1655
str->append(all ? " all " : " any ", 5);
1657
Item_subselect::print(str, query_type);
1661
void subselect_engine::set_thd(THD *thd_arg)
1665
result->set_thd(thd_arg);
1669
subselect_single_select_engine::
1670
subselect_single_select_engine(st_select_lex *select,
1671
select_subselect *result_arg,
1672
Item_subselect *item_arg)
1673
:subselect_engine(item_arg, result_arg),
1674
prepared(0), optimized(0), executed(0),
1675
select_lex(select), join(0)
1677
select_lex->master_unit()->item= item_arg;
1681
void subselect_single_select_engine::cleanup()
1683
DBUG_ENTER("subselect_single_select_engine::cleanup");
1684
prepared= optimized= executed= 0;
1691
void subselect_union_engine::cleanup()
1693
DBUG_ENTER("subselect_union_engine::cleanup");
1694
unit->reinit_exec_mechanism();
1700
bool subselect_union_engine::is_executed() const
1702
return unit->executed;
1707
Check if last execution of the subquery engine produced any rows
1710
subselect_union_engine::no_rows()
1713
Check if last execution of the subquery engine produced any rows. The
1714
return value is undefined if last execution ended in an error.
1717
TRUE - Last subselect execution has produced no rows
1721
bool subselect_union_engine::no_rows()
1723
/* Check if we got any rows when reading UNION result from temp. table: */
1724
return test(!unit->fake_select_lex->join->send_records);
1727
void subselect_uniquesubquery_engine::cleanup()
1729
DBUG_ENTER("subselect_uniquesubquery_engine::cleanup");
1731
subselect_uniquesubquery_engine have not 'result' assigbed, so we do not
1738
subselect_union_engine::subselect_union_engine(st_select_lex_unit *u,
1739
select_subselect *result_arg,
1740
Item_subselect *item_arg)
1741
:subselect_engine(item_arg, result_arg)
1744
if (!result_arg) //out of memory
1745
current_thd->fatal_error();
1746
unit->item= item_arg;
1750
int subselect_single_select_engine::prepare()
1754
join= new JOIN(thd, select_lex->item_list,
1755
select_lex->options | SELECT_NO_UNLOCK, result);
1756
if (!join || !result)
1758
thd->fatal_error(); //out of memory
1762
SELECT_LEX *save_select= thd->lex->current_select;
1763
thd->lex->current_select= select_lex;
1764
if (join->prepare(&select_lex->ref_pointer_array,
1765
(TABLE_LIST*) select_lex->table_list.first,
1766
select_lex->with_wild,
1768
select_lex->order_list.elements +
1769
select_lex->group_list.elements,
1770
(ORDER*) select_lex->order_list.first,
1771
(ORDER*) select_lex->group_list.first,
1773
(ORDER*) 0, select_lex,
1774
select_lex->master_unit()))
1776
thd->lex->current_select= save_select;
1780
int subselect_union_engine::prepare()
1782
return unit->prepare(thd, result, SELECT_NO_UNLOCK);
1785
int subselect_uniquesubquery_engine::prepare()
1787
//this never should be called
1794
Check if last execution of the subquery engine produced any rows
1797
subselect_single_select_engine::no_rows()
1800
Check if last execution of the subquery engine produced any rows. The
1801
return value is undefined if last execution ended in an error.
1804
TRUE - Last subselect execution has produced no rows
1808
bool subselect_single_select_engine::no_rows()
1810
return !item->assigned();
1815
makes storage for the output values for the subquery and calcuates
1816
their data and column types and their nullability.
1818
void subselect_engine::set_row(List<Item> &item_list, Item_cache **row)
1821
List_iterator_fast<Item> li(item_list);
1822
res_type= STRING_RESULT;
1823
res_field_type= MYSQL_TYPE_VAR_STRING;
1824
for (uint i= 0; (sel_item= li++); i++)
1826
item->max_length= sel_item->max_length;
1827
res_type= sel_item->result_type();
1828
res_field_type= sel_item->field_type();
1829
item->decimals= sel_item->decimals;
1830
item->unsigned_flag= sel_item->unsigned_flag;
1831
maybe_null= sel_item->maybe_null;
1832
if (!(row[i]= Item_cache::get_cache(sel_item)))
1834
row[i]->setup(sel_item);
1835
row[i]->store(sel_item);
1837
if (item_list.elements > 1)
1838
res_type= ROW_RESULT;
1841
void subselect_single_select_engine::fix_length_and_dec(Item_cache **row)
1843
DBUG_ASSERT(row || select_lex->item_list.elements==1);
1844
set_row(select_lex->item_list, row);
1845
item->collation.set(row[0]->collation);
1850
void subselect_union_engine::fix_length_and_dec(Item_cache **row)
1852
DBUG_ASSERT(row || unit->first_select()->item_list.elements==1);
1854
if (unit->first_select()->item_list.elements == 1)
1856
set_row(unit->types, row);
1857
item->collation.set(row[0]->collation);
1861
bool maybe_null_saved= maybe_null;
1862
set_row(unit->types, row);
1863
maybe_null= maybe_null_saved;
1867
void subselect_uniquesubquery_engine::fix_length_and_dec(Item_cache **row)
1869
//this never should be called
1873
int init_read_record_seq(JOIN_TAB *tab);
1874
int join_read_always_key_or_null(JOIN_TAB *tab);
1875
int join_read_next_same_or_null(READ_RECORD *info);
1877
int subselect_single_select_engine::exec()
1879
DBUG_ENTER("subselect_single_select_engine::exec");
1880
char const *save_where= thd->where;
1881
SELECT_LEX *save_select= thd->lex->current_select;
1882
thd->lex->current_select= select_lex;
1885
SELECT_LEX_UNIT *unit= select_lex->master_unit();
1888
unit->set_limit(unit->global_parameters);
1889
if (join->optimize())
1891
thd->where= save_where;
1893
thd->lex->current_select= save_select;
1894
DBUG_RETURN(join->error ? join->error : 1);
1896
if (!select_lex->uncacheable && thd->lex->describe &&
1897
!(join->select_options & SELECT_DESCRIBE) &&
1898
join->need_tmp && item->const_item())
1901
Force join->join_tmp creation, because this subquery will be replaced
1902
by a simple select from the materialization temp table by optimize()
1903
called by EXPLAIN and we need to preserve the initial query structure
1904
so we can display it.
1906
select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
1907
select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
1908
if (join->init_save_join_tab())
1909
DBUG_RETURN(1); /* purecov: inspected */
1911
if (item->engine_changed)
1916
if (select_lex->uncacheable &&
1917
select_lex->uncacheable != UNCACHEABLE_EXPLAIN
1922
thd->where= save_where;
1923
thd->lex->current_select= save_select;
1927
item->assigned((executed= 0));
1931
item->reset_value_registration();
1932
JOIN_TAB *changed_tabs[MAX_TABLES];
1933
JOIN_TAB **last_changed_tab= changed_tabs;
1934
if (item->have_guarded_conds())
1937
For at least one of the pushed predicates the following is true:
1938
We should not apply optimizations based on the condition that was
1939
pushed down into the subquery. Those optimizations are ref[_or_null]
1940
acceses. Change them to be full table scans.
1942
for (uint i=join->const_tables ; i < join->tables ; i++)
1944
JOIN_TAB *tab=join->join_tab+i;
1945
if (tab && tab->keyuse)
1947
for (uint i= 0; i < tab->ref.key_parts; i++)
1949
bool *cond_guard= tab->ref.cond_guards[i];
1950
if (cond_guard && !*cond_guard)
1952
/* Change the access method to full table scan */
1953
tab->save_read_first_record= tab->read_first_record;
1954
tab->save_read_record= tab->read_record.read_record;
1955
tab->read_first_record= init_read_record_seq;
1956
tab->read_record.record= tab->table->record[0];
1957
tab->read_record.thd= join->thd;
1958
tab->read_record.ref_length= tab->table->file->ref_length;
1959
tab->read_record.unlock_row= rr_unlock_row;
1960
*(last_changed_tab++)= tab;
1970
/* Enable the optimizations back */
1971
for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
1973
JOIN_TAB *tab= *ptab;
1974
tab->read_record.record= 0;
1975
tab->read_record.ref_length= 0;
1976
tab->read_first_record= tab->save_read_first_record;
1977
tab->read_record.read_record= tab->save_read_record;
1980
thd->where= save_where;
1981
thd->lex->current_select= save_select;
1982
DBUG_RETURN(join->error||thd->is_fatal_error);
1984
thd->where= save_where;
1985
thd->lex->current_select= save_select;
1989
int subselect_union_engine::exec()
1991
char const *save_where= thd->where;
1992
int res= unit->exec();
1993
thd->where= save_where;
1999
Search for at least one row satisfying select condition
2002
subselect_uniquesubquery_engine::scan_table()
2005
Scan the table using sequential access until we find at least one row
2006
satisfying select condition.
2008
The caller must set this->empty_result_set=FALSE before calling this
2009
function. This function will set it to TRUE if it finds a matching row.
2016
int subselect_uniquesubquery_engine::scan_table()
2019
TABLE *table= tab->table;
2020
DBUG_ENTER("subselect_uniquesubquery_engine::scan_table");
2022
if (table->file->inited)
2023
table->file->ha_index_end();
2025
table->file->ha_rnd_init(1);
2026
table->file->extra_opt(HA_EXTRA_CACHE,
2027
current_thd->variables.read_buff_size);
2031
error=table->file->rnd_next(table->record[0]);
2032
if (error && error != HA_ERR_END_OF_FILE)
2034
error= report_error(table, error);
2041
if (!cond || cond->val_int())
2043
empty_result_set= FALSE;
2048
table->file->ha_rnd_end();
2049
DBUG_RETURN(error != 0);
2054
Copy ref key and check for null parts in it
2057
subselect_uniquesubquery_engine::copy_ref_key()
2060
Copy ref key and check for null parts in it.
2061
Depending on the nullability and conversion problems this function
2062
recognizes and processes the following states :
2063
1. Partial match on top level. This means IN has a value of FALSE
2064
regardless of the data in the subquery table.
2065
Detected by finding a NULL in the left IN operand of a top level
2067
We may actually skip reading the subquery, so return TRUE to skip
2068
the table scan in subselect_uniquesubquery_engine::exec and make
2069
the value of the IN predicate a NULL (that is equal to FALSE on
2071
2. No exact match when IN is nested inside another predicate.
2072
Detected by finding a NULL in the left IN operand when IN is not
2073
a top level predicate.
2074
We cannot have an exact match. But we must proceed further with a
2075
table scan to find out if it's a partial match (and IN has a value
2076
of NULL) or no match (and IN has a value of FALSE).
2077
So we return FALSE to continue with the scan and see if there are
2078
any record that would constitute a partial match (as we cannot
2079
determine that from the index).
2080
3. Error converting the left IN operand to the column type of the
2081
right IN operand. This counts as no match (and IN has the value of
2082
FALSE). We mark the subquery table cursor as having no more rows
2083
(to ensure that the processing that follows will not find a match)
2084
and return FALSE, so IN is not treated as returning NULL.
2088
FALSE - The value of the IN predicate is not known. Proceed to find the
2089
value of the IN predicate using the determined values of
2090
null_keypart and table->status.
2091
TRUE - IN predicate has a value of NULL. Stop the processing right there
2092
and return NULL to the outer predicates.
2095
bool subselect_uniquesubquery_engine::copy_ref_key()
2097
DBUG_ENTER("subselect_uniquesubquery_engine::copy_ref_key");
2099
for (store_key **copy= tab->ref.key_copy ; *copy ; copy++)
2101
tab->ref.key_err= (*copy)->copy();
2104
When there is a NULL part in the key we don't need to make index
2105
lookup for such key thus we don't need to copy whole key.
2106
If we later should do a sequential scan return OK. Fail otherwise.
2108
See also the comment for the subselect_uniquesubquery_engine::exec()
2111
null_keypart= (*copy)->null_key;
2114
bool top_level= ((Item_in_subselect *) item)->is_top_level_item();
2117
/* Partial match on top level */
2122
/* No exact match when IN is nested inside another predicate */
2128
Check if the error is equal to STORE_KEY_FATAL. This is not expressed
2129
using the store_key::store_key_result enum because ref.key_err is a
2130
boolean and we want to detect both TRUE and STORE_KEY_FATAL from the
2131
space of the union of the values of [TRUE, FALSE] and
2132
store_key::store_key_result.
2133
TODO: fix the variable an return types.
2135
if (tab->ref.key_err & 1)
2138
Error converting the left IN operand to the column type of the right
2141
tab->table->status= STATUS_NOT_FOUND;
2153
subselect_uniquesubquery_engine::exec()
2156
Find rows corresponding to the ref key using index access.
2157
If some part of the lookup key is NULL, then we're evaluating
2158
NULL IN (SELECT ... )
2159
This is a special case, we don't need to search for NULL in the table,
2160
instead, the result value is
2161
- NULL if select produces empty row set
2164
In some cases (IN subselect is a top level item, i.e. abort_on_null==TRUE)
2165
the caller doesn't distinguish between NULL and FALSE result and we just
2167
Otherwise we make a full table scan to see if there is at least one
2170
The result of this function (info about whether a row was found) is
2171
stored in this->empty_result_set.
2176
TRUE - an error occured while scanning
2179
int subselect_uniquesubquery_engine::exec()
2181
DBUG_ENTER("subselect_uniquesubquery_engine::exec");
2183
TABLE *table= tab->table;
2184
empty_result_set= TRUE;
2187
/* TODO: change to use of 'full_scan' here? */
2193
We know that there will be no rows even if we scan.
2194
Can be set in copy_ref_key.
2196
((Item_in_subselect *) item)->value= 0;
2201
DBUG_RETURN(scan_table());
2203
if (!table->file->inited)
2204
table->file->ha_index_init(tab->ref.key, 0);
2205
error= table->file->index_read_map(table->record[0],
2207
make_prev_keypart_map(tab->ref.key_parts),
2210
error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
2211
error= report_error(table, error);
2216
if (!table->status && (!cond || cond->val_int()))
2218
((Item_in_subselect *) item)->value= 1;
2219
empty_result_set= FALSE;
2222
((Item_in_subselect *) item)->value= 0;
2225
DBUG_RETURN(error != 0);
2229
subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine()
2231
/* Tell handler we don't need the index anymore */
2232
tab->table->file->ha_index_end();
2237
Index-lookup subselect 'engine' - run the subquery
2240
subselect_uniquesubquery_engine:exec()
2244
The engine is used to resolve subqueries in form
2246
oe IN (SELECT key FROM tbl WHERE subq_where)
2248
The value of the predicate is calculated as follows:
2249
1. If oe IS NULL, this is a special case, do a full table scan on
2250
table tbl and search for row that satisfies subq_where. If such
2251
row is found, return NULL, otherwise return FALSE.
2252
2. Make an index lookup via key=oe, search for a row that satisfies
2253
subq_where. If found, return TRUE.
2254
3. If check_null==TRUE, make another lookup via key=NULL, search for a
2255
row that satisfies subq_where. If found, return NULL, otherwise
2259
The step #1 can be optimized further when the index has several key
2260
parts. Consider a subquery:
2262
(oe1, oe2) IN (SELECT keypart1, keypart2 FROM tbl WHERE subq_where)
2264
and suppose we need to evaluate it for {oe1, oe2}=={const1, NULL}.
2265
Current code will do a full table scan and obtain correct result. There
2266
is a better option: instead of evaluating
2268
SELECT keypart1, keypart2 FROM tbl WHERE subq_where (1)
2270
and checking if it has produced any matching rows, evaluate
2272
SELECT keypart2 FROM tbl WHERE subq_where AND keypart1=const1 (2)
2274
If this query produces a row, the result is NULL (as we're evaluating
2275
"(const1, NULL) IN { (const1, X), ... }", which has a value of UNKNOWN,
2276
i.e. NULL). If the query produces no rows, the result is FALSE.
2278
We currently evaluate (1) by doing a full table scan. (2) can be
2279
evaluated by doing a "ref" scan on "keypart1=const1", which can be much
2280
cheaper. We can use index statistics to quickly check whether "ref" scan
2281
will be cheaper than full table scan.
2288
int subselect_indexsubquery_engine::exec()
2290
DBUG_ENTER("subselect_indexsubquery_engine::exec");
2292
bool null_finding= 0;
2293
TABLE *table= tab->table;
2295
((Item_in_subselect *) item)->value= 0;
2296
empty_result_set= TRUE;
2302
/* We need to check for NULL if there wasn't a matching value */
2303
*tab->ref.null_ref_key= 0; // Search first for not null
2304
((Item_in_subselect *) item)->was_null= 0;
2307
/* Copy the ref key and check for nulls... */
2314
We know that there will be no rows even if we scan.
2315
Can be set in copy_ref_key.
2317
((Item_in_subselect *) item)->value= 0;
2322
DBUG_RETURN(scan_table());
2324
if (!table->file->inited)
2325
table->file->ha_index_init(tab->ref.key, 1);
2326
error= table->file->index_read_map(table->record[0],
2328
make_prev_keypart_map(tab->ref.key_parts),
2331
error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
2332
error= report_error(table, error);
2341
if ((!cond || cond->val_int()) && (!having || having->val_int()))
2343
empty_result_set= FALSE;
2345
((Item_in_subselect *) item)->was_null= 1;
2347
((Item_in_subselect *) item)->value= 1;
2350
error= table->file->index_next_same(table->record[0],
2352
tab->ref.key_length);
2353
if (error && error != HA_ERR_END_OF_FILE)
2355
error= report_error(table, error);
2361
if (!check_null || null_finding)
2362
break; /* We don't need to check nulls */
2363
*tab->ref.null_ref_key= 1;
2365
/* Check if there exists a row with a null value in the index */
2366
if ((error= (safe_index_read(tab) == 1)))
2371
DBUG_RETURN(error != 0);
2375
uint subselect_single_select_engine::cols()
2377
DBUG_ASSERT(select_lex->join != 0); // should be called after fix_fields()
2378
return select_lex->join->fields_list.elements;
2382
uint subselect_union_engine::cols()
2384
DBUG_ASSERT(unit->is_prepared()); // should be called after fix_fields()
2385
return unit->types.elements;
2389
uint8 subselect_single_select_engine::uncacheable()
2391
return select_lex->uncacheable;
2395
uint8 subselect_union_engine::uncacheable()
2397
return unit->uncacheable;
2401
void subselect_single_select_engine::exclude()
2403
select_lex->master_unit()->exclude_level();
2406
void subselect_union_engine::exclude()
2408
unit->exclude_level();
2412
void subselect_uniquesubquery_engine::exclude()
2414
//this never should be called
2419
table_map subselect_engine::calc_const_tables(TABLE_LIST *table)
2422
for (; table; table= table->next_leaf)
2424
TABLE *tbl= table->table;
2425
if (tbl && tbl->const_table)
2432
table_map subselect_single_select_engine::upper_select_const_tables()
2434
return calc_const_tables((TABLE_LIST *) select_lex->outer_select()->
2439
table_map subselect_union_engine::upper_select_const_tables()
2441
return calc_const_tables((TABLE_LIST *) unit->outer_select()->leaf_tables);
2445
void subselect_single_select_engine::print(String *str,
2446
enum_query_type query_type)
2448
select_lex->print(thd, str, query_type);
2452
void subselect_union_engine::print(String *str, enum_query_type query_type)
2454
unit->print(str, query_type);
2458
void subselect_uniquesubquery_engine::print(String *str,
2459
enum_query_type query_type)
2461
str->append(STRING_WITH_LEN("<primary_index_lookup>("));
2462
tab->ref.items[0]->print(str, query_type);
2463
str->append(STRING_WITH_LEN(" in "));
2464
str->append(tab->table->s->table_name.str, tab->table->s->table_name.length);
2465
KEY *key_info= tab->table->key_info+ tab->ref.key;
2466
str->append(STRING_WITH_LEN(" on "));
2467
str->append(key_info->name);
2470
str->append(STRING_WITH_LEN(" where "));
2471
cond->print(str, query_type);
2477
void subselect_indexsubquery_engine::print(String *str,
2478
enum_query_type query_type)
2480
str->append(STRING_WITH_LEN("<index_lookup>("));
2481
tab->ref.items[0]->print(str, query_type);
2482
str->append(STRING_WITH_LEN(" in "));
2483
str->append(tab->table->s->table_name.str, tab->table->s->table_name.length);
2484
KEY *key_info= tab->table->key_info+ tab->ref.key;
2485
str->append(STRING_WITH_LEN(" on "));
2486
str->append(key_info->name);
2488
str->append(STRING_WITH_LEN(" checking NULL"));
2491
str->append(STRING_WITH_LEN(" where "));
2492
cond->print(str, query_type);
2496
str->append(STRING_WITH_LEN(" having "));
2497
having->print(str, query_type);
2503
change select_result object of engine.
2505
@param si new subselect Item
2506
@param res new select_result object
2514
bool subselect_single_select_engine::change_result(Item_subselect *si,
2515
select_subselect *res)
2519
return select_lex->join->change_result(result);
2524
change select_result object of engine.
2526
@param si new subselect Item
2527
@param res new select_result object
2535
bool subselect_union_engine::change_result(Item_subselect *si,
2536
select_subselect *res)
2539
int rc= unit->change_result(res, result);
2546
change select_result emulation, never should be called.
2548
@param si new subselect Item
2549
@param res new select_result object
2557
bool subselect_uniquesubquery_engine::change_result(Item_subselect *si,
2558
select_subselect *res)
2566
Report about presence of tables in subquery.
2569
TRUE there are not tables used in subquery
2571
FALSE there are some tables in subquery
2573
bool subselect_single_select_engine::no_tables()
2575
return(select_lex->table_list.elements == 0);
2580
Check statically whether the subquery can return NULL
2583
subselect_single_select_engine::may_be_null()
2586
FALSE can guarantee that the subquery never return NULL
2589
bool subselect_single_select_engine::may_be_null()
2591
return ((no_tables() && !join->conds && !join->having) ? maybe_null : 1);
2596
Report about presence of tables in subquery.
2599
TRUE there are not tables used in subquery
2601
FALSE there are some tables in subquery
2603
bool subselect_union_engine::no_tables()
2605
for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
2607
if (sl->table_list.elements)
2615
Report about presence of tables in subquery.
2618
TRUE there are not tables used in subquery
2620
FALSE there are some tables in subquery
2623
bool subselect_uniquesubquery_engine::no_tables()
2625
/* returning value is correct, but this method should never be called */
added
removed
sql/item_subselect.cc
