282
282
Fix fields referenced from inner selects.
287
all_fields List of all fields used in select
288
select Current select
289
ref_pointer_array Array of references to Items used in current select
290
group_list GROUP BY list (is NULL by default)
293
The function serves 3 purposes - adds fields referenced from inner
294
selects to the current select list, resolves which class to use
295
to access referenced item (Item_ref of Item_direct_ref) and fixes
296
references (Item_ref objects) to these fields.
298
If a field isn't already in the select list and the ref_pointer_array
284
@param thd Thread handle
285
@param all_fields List of all fields used in select
286
@param select Current select
287
@param ref_pointer_array Array of references to Items used in current select
288
@param group_list GROUP BY list (is NULL by default)
291
The function serves 3 purposes
293
- adds fields referenced from inner query blocks to the current select list
295
- Decides which class to use to reference the items (Item_ref or
298
- fixes references (Item_ref objects) to these fields.
300
If a field isn't already on the select list and the ref_pointer_array
299
301
is provided then it is added to the all_fields list and the pointer to
300
302
it is saved in the ref_pointer_array.
302
304
The class to access the outer field is determined by the following rules:
303
1. If the outer field isn't used under an aggregate function
304
then the Item_ref class should be used.
305
2. If the outer field is used under an aggregate function and this
306
function is aggregated in the select where the outer field was
307
resolved or in some more inner select then the Item_direct_ref
308
class should be used.
309
Also it should be used if we are grouping by a subquery containing
306
-#. If the outer field isn't used under an aggregate function then the
307
Item_ref class should be used.
309
-#. If the outer field is used under an aggregate function and this
310
function is, in turn, aggregated in the query block where the outer
311
field was resolved or some query nested therein, then the
312
Item_direct_ref class should be used. Also it should be used if we are
313
grouping by a subquery containing the outer field.
311
315
The resolution is done here and not at the fix_fields() stage as
312
it can be done only after sum functions are fixed and pulled up to
313
selects where they are have to be aggregated.
316
it can be done only after aggregate functions are fixed and pulled up to
317
selects where they are to be aggregated.
314
319
When the class is chosen it substitutes the original field in the
315
320
Item_outer_ref object.
317
322
After this we proceed with fixing references (Item_outer_ref objects) to
318
323
this field from inner subqueries.
321
TRUE an error occured
326
@retval true An error occured.
326
331
fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
327
332
Item **ref_pointer_array, ORDER *group_list)
329
334
Item_outer_ref *ref;
331
bool direct_ref= FALSE;
333
336
List_iterator<Item_outer_ref> ref_it(select->inner_refs_list);
334
337
while ((ref= ref_it++))
339
bool direct_ref= false;
336
340
Item *item= ref->outer_ref;
337
341
Item **item_ref= ref->ref;
338
342
Item_ref *new_ref;
423
427
nesting_map save_allow_sum_func=thd->lex->allow_sum_func ;
425
Need to save the value, so we can turn off only the new NON_AGG_FIELD
429
Need to save the value, so we can turn off only any new non_agg_field_used
426
430
additions coming from the WHERE
428
uint8 saved_flag= thd->lex->current_select->full_group_by_flag;
432
const bool saved_non_agg_field_used=
433
thd->lex->current_select->non_agg_field_used();
429
434
DBUG_ENTER("setup_without_group");
431
436
thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level);
432
437
res= setup_conds(thd, tables, leaves, conds);
434
439
/* it's not wrong to have non-aggregated columns in a WHERE */
435
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
436
thd->lex->current_select->full_group_by_flag= saved_flag |
437
(thd->lex->current_select->full_group_by_flag & ~NON_AGG_FIELD_USED);
440
thd->lex->current_select->set_non_agg_field_used(saved_non_agg_field_used);
439
442
thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
440
443
res= res || setup_order(thd, ref_pointer_array, tables, fields, all_fields,
2517
2526
if (!(join= new JOIN(thd, fields, select_options, result)))
2518
2527
DBUG_RETURN(TRUE);
2519
2528
thd_proc_info(thd, "init");
2520
thd->used_tables=0; // Updated by setup_fields
2529
thd->lex->used_tables=0; // Updated by setup_fields
2521
2530
err= join->prepare(rref_pointer_array, tables, wild_num,
2522
2531
conds, og_num, order, group, having, proc_param,
2523
2532
select_lex, unit);
3348
3367
eq_func is NEVER true when num_values > 1
3353
Additional optimization: if we're processing
3354
"t.key BETWEEN c1 AND c1" then proceed as if we were processing
3356
TODO: This is a very limited fix. A more generic fix is possible.
3357
There are 2 options:
3358
A) Make equality propagation code be able to handle BETWEEN
3359
(including cases like t1.key BETWEEN t2.key AND t3.key)
3360
B) Make range optimizer to infer additional "t.key = c" equalities
3361
and use them in equality propagation process (see details in
3364
if ((cond->functype() != Item_func::BETWEEN) ||
3365
((Item_func_between*) cond)->negated ||
3366
!value[0]->eq(value[1], field->binary()))
3371
3371
if (field->result_type() == STRING_RESULT)
3373
3373
if ((*value)->result_type() != STRING_RESULT)
3563
3563
case Item_func::OPTIMIZE_KEY:
3567
if (is_local_field (cond_func->key_item()) &&
3568
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
3567
Build list of possible keys for 'a BETWEEN low AND high'.
3568
It is handled similar to the equivalent condition
3569
'a >= low AND a <= high':
3571
if (cond_func->functype() == Item_func::BETWEEN)
3573
Item_field *field_item;
3574
bool equal_func= FALSE;
3576
values= cond_func->arguments();
3578
bool binary_cmp= (values[0]->real_item()->type() == Item::FIELD_ITEM)
3579
? ((Item_field*)values[0]->real_item())->field->binary()
3583
Additional optimization: If 'low = high':
3584
Handle as if the condition was "t.key = low".
3586
if (!((Item_func_between*)cond_func)->negated &&
3587
values[1]->eq(values[2], binary_cmp))
3594
Append keys for 'field <cmp> value[]' if the
3595
condition is of the form::
3596
'<field> BETWEEN value[1] AND value[2]'
3598
if (is_local_field (values[0]))
3600
field_item= (Item_field *) (values[0]->real_item());
3601
add_key_equal_fields(key_fields, *and_level, cond_func,
3602
field_item, equal_func, &values[1],
3603
num_values, usable_tables, sargables);
3606
Append keys for 'value[0] <cmp> field' if the
3607
condition is of the form:
3608
'value[0] BETWEEN field1 AND field2'
3610
for (uint i= 1; i <= num_values; i++)
3612
if (is_local_field (values[i]))
3614
field_item= (Item_field *) (values[i]->real_item());
3615
add_key_equal_fields(key_fields, *and_level, cond_func,
3616
field_item, equal_func, values,
3617
1, usable_tables, sargables);
3620
} // if ( ... Item_func::BETWEEN)
3623
else if (is_local_field (cond_func->key_item()) &&
3624
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
3570
3626
values= cond_func->arguments()+1;
3571
3627
if (cond_func->functype() == Item_func::NE_FUNC &&
3579
3635
cond_func->argument_count()-1,
3580
3636
usable_tables, sargables);
3582
if (cond_func->functype() == Item_func::BETWEEN)
3584
values= cond_func->arguments();
3585
for (uint i= 1 ; i < cond_func->argument_count() ; i++)
3587
Item_field *field_item;
3588
if (is_local_field (cond_func->arguments()[i]))
3590
field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
3591
add_key_equal_fields(key_fields, *and_level, cond_func,
3592
field_item, 0, values, 1, usable_tables,
3599
3640
case Item_func::OPTIMIZE_OP:
12813
12865
return 0; // keep test
12869
Extract a condition that can be checked after reading given table
12871
@param cond Condition to analyze
12872
@param tables Tables for which "current field values" are available
12873
@param used_table Table that we're extracting the condition for (may
12874
also include PSEUDO_TABLE_BITS, and may be zero)
12875
@param exclude_expensive_cond Do not push expensive conditions
12877
@retval <>NULL Generated condition
12878
@retval =NULL Already checked, OR error
12881
Extract the condition that can be checked after reading the table
12882
specified in 'used_table', given that current-field values for tables
12883
specified in 'tables' bitmap are available.
12884
If 'used_table' is 0
12885
- extract conditions for all tables in 'tables'.
12886
- extract conditions are unrelated to any tables
12887
in the same query block/level(i.e. conditions
12888
which have used_tables == 0).
12890
The function assumes that
12891
- Constant parts of the condition has already been checked.
12892
- Condition that could be checked for tables in 'tables' has already
12895
The function takes into account that some parts of the condition are
12896
guaranteed to be true by employed 'ref' access methods (the code that
12897
does this is located at the end, search down for "EQ_FUNC").
12900
Make sure to keep the implementations of make_cond_for_table() and
12901
make_cond_after_sjm() synchronized.
12902
make_cond_for_info_schema() uses similar algorithm as well.
12817
12905
static COND *
12818
12906
make_cond_for_table(COND *cond, table_map tables, table_map used_table)
13654
13744
tab->join->tables > tab->join->const_tables + 1) &&
13655
13745
((unsigned) best_key != table->s->primary_key ||
13656
13746
!table->file->primary_key_is_clustered()))
13659
13749
if (best_key >= 0)
13661
bool quick_created= FALSE;
13662
13751
if (table->quick_keys.is_set(best_key) && best_key != ref_key)
13665
13754
map.clear_all(); // Force the creation of quick select
13666
13755
map.set_bit(best_key); // only best_key.
13668
select->test_quick_select(join->thd, map, 0,
13669
join->select_options & OPTION_FOUND_ROWS ?
13671
join->unit->select_limit_cnt,
13677
If ref_key used index tree reading only ('Using index' in EXPLAIN),
13678
and best_key doesn't, then revert the decision.
13680
if (!table->covering_keys.is_set(best_key))
13681
table->set_keyread(FALSE);
13682
if (!quick_created)
13684
tab->index= best_key;
13685
tab->read_first_record= best_key_direction > 0 ?
13686
join_read_first:join_read_last;
13687
tab->type=JT_NEXT; // Read with index_first(), index_next()
13688
if (select && select->quick)
13690
delete select->quick;
13693
if (table->covering_keys.is_set(best_key))
13694
table->set_keyread(TRUE);
13695
table->file->ha_index_or_rnd_end();
13696
if (join->select_options & SELECT_DESCRIBE)
13699
tab->ref.key_parts= 0;
13700
if (select_limit < table_records)
13701
tab->limit= select_limit;
13704
else if (tab->type != JT_ALL)
13707
We're about to use a quick access to the table.
13708
We need to change the access method so as the quick access
13709
method is actually used.
13711
DBUG_ASSERT(tab->select->quick);
13715
tab->ref.key_parts=0; // Don't use ref key.
13716
tab->read_first_record= join_init_read_record;
13717
if (tab->is_using_loose_index_scan())
13718
join->tmp_table_param.precomputed_group_by= TRUE;
13720
TODO: update the number of records in join->best_positions[tablenr]
13757
select->test_quick_select(join->thd, map, 0,
13758
join->select_options & OPTION_FOUND_ROWS ?
13760
join->unit->select_limit_cnt,
13724
13763
order_direction= best_key_direction;
13754
13796
quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
13756
13798
tab->limit= 0;
13757
select->quick= save_quick;
13758
DBUG_RETURN(0); // Use filesort
13799
goto use_filesort; // Use filesort
13806
Update query plan with access pattern for doing
13807
ordered access according to what we have decided
13810
if (!no_changes) // We are allowed to update QEP
13814
bool quick_created=
13815
(select && select->quick && select->quick!=save_quick);
13818
If ref_key used index tree reading only ('Using index' in EXPLAIN),
13819
and best_key doesn't, then revert the decision.
13821
if (!table->covering_keys.is_set(best_key))
13822
table->set_keyread(FALSE);
13823
if (!quick_created)
13825
if (select) // Throw any existing quick select
13826
select->quick= 0; // Cleanup either reset to save_quick,
13827
// or 'delete save_quick'
13828
tab->index= best_key;
13829
tab->read_first_record= order_direction > 0 ?
13830
join_read_first:join_read_last;
13831
tab->type=JT_NEXT; // Read with index_first(), index_next()
13833
if (table->covering_keys.is_set(best_key))
13834
table->set_keyread(TRUE);
13835
table->file->ha_index_or_rnd_end();
13836
if (tab->join->select_options & SELECT_DESCRIBE)
13839
tab->ref.key_parts= 0;
13840
if (select_limit < table->file->stats.records)
13841
tab->limit= select_limit;
13844
else if (tab->type != JT_ALL)
13847
We're about to use a quick access to the table.
13848
We need to change the access method so as the quick access
13849
method is actually used.
13851
DBUG_ASSERT(tab->select->quick);
13855
tab->ref.key_parts=0; // Don't use ref key.
13856
tab->read_first_record= join_init_read_record;
13857
if (tab->is_using_loose_index_scan())
13858
tab->join->tmp_table_param.precomputed_group_by= TRUE;
13860
TODO: update the number of records in join->best_positions[tablenr]
13865
if (order_direction == -1) // If ORDER BY ... DESC
13867
if (select && select->quick)
13869
QUICK_SELECT_DESC *tmp;
13761
13870
/* ORDER BY range_key DESC */
13762
tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick),
13871
tmp= new QUICK_SELECT_DESC((QUICK_RANGE_SELECT*)(select->quick),
13763
13872
used_key_parts);
13764
if (!tmp || tmp->error)
13767
select->quick= save_quick;
13873
if (tmp && select->quick == save_quick)
13874
save_quick= 0; // ::QUICK_SELECT_DESC consumed it
13876
if (!tmp || tmp->error)
13768
13879
tab->limit= 0;
13769
DBUG_RETURN(0); // Reverse sort not supported
13774
else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL &&
13775
tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts)
13778
SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
13780
Use a traversal function that starts by reading the last row
13781
with key part (A) and then traverse the index backwards.
13783
tab->read_first_record= join_read_last_key;
13784
tab->read_record.read_record= join_read_prev_same;
13880
goto use_filesort; // Reverse sort failed -> filesort
13882
select->quick= tmp;
13884
else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL &&
13885
tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts)
13888
SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
13890
Use a traversal function that starts by reading the last row
13891
with key part (A) and then traverse the index backwards.
13893
tab->read_first_record= join_read_last_key;
13894
tab->read_record.read_record= join_read_prev_same;
13897
else if (select && select->quick)
13898
select->quick->sorted= 1;
13900
} // QEP has been modified
13904
We may have both a 'select->quick' and 'save_quick' (original)
13905
at this point. Delete the one that we wan't use.
13909
// Keep current (ordered) select->quick
13910
if (select && save_quick != select->quick)
13787
else if (select && select->quick)
13788
select->quick->sorted= 1;
13789
13915
DBUG_RETURN(1);
13918
// Restore original save_quick
13919
if (select && select->quick != save_quick)
13921
delete select->quick;
13922
select->quick= save_quick;
added
removed
sql/sql_select.cc
