1
/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
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., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
19
UNION's were introduced by Monty and Sinisa <sinisa@mysql.com>
25
#include "sql_union.h"
26
#include "sql_select.h"
27
#include "sql_cursor.h"
28
#include "sql_base.h" // fill_record
29
#include "filesort.h" // filesort_free_buffers
31
bool mysql_union(THD *thd, LEX *lex, select_result *result,
32
SELECT_LEX_UNIT *unit, ulong setup_tables_done_option)
34
DBUG_ENTER("mysql_union");
36
if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK |
37
setup_tables_done_option)))
39
res|= unit->cleanup();
44
/***************************************************************************
45
** store records in temporary table for UNION
46
***************************************************************************/
48
int select_union::prepare(List<Item> &list, SELECT_LEX_UNIT *u)
55
bool select_union::send_data(List<Item> &values)
58
if (unit->offset_limit_cnt)
59
{ // using limit offset,count
60
unit->offset_limit_cnt--;
63
fill_record(thd, table->field, values, 1);
67
if ((error= table->file->ha_write_row(table->record[0])))
69
/* create_myisam_from_heap will generate error if needed */
70
if (table->file->is_fatal_error(error, HA_CHECK_DUP) &&
71
create_myisam_from_heap(thd, table, &tmp_table_param, error, 1))
78
bool select_union::send_eof()
84
bool select_union::flush()
87
if ((error=table->file->extra(HA_EXTRA_NO_CACHE)))
89
table->file->print_error(error, MYF(0));
96
Create a temporary table to store the result of select_union.
99
select_union::create_result_table()
101
column_types a list of items used to define columns of the
103
is_union_distinct if set, the temporary table will eliminate
105
options create options
108
Create a temporary table that is used to store the result of a UNION,
109
derived table, or a materialized cursor.
112
0 The table has been created successfully.
113
1 create_tmp_table failed.
117
select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
118
bool is_union_distinct, ulonglong options,
121
DBUG_ASSERT(table == 0);
122
tmp_table_param.init();
123
tmp_table_param.field_count= column_types->elements;
125
if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
126
(ORDER*) 0, is_union_distinct, 1,
127
options, HA_POS_ERROR, alias)))
129
table->file->extra(HA_EXTRA_WRITE_CACHE);
130
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
136
initialization procedures before fake_select_lex preparation()
139
st_select_lex_unit::init_prepare_fake_select_lex()
147
st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg)
149
thd_arg->lex->current_select= fake_select_lex;
150
fake_select_lex->table_list.link_in_list(&result_table_list,
151
&result_table_list.next_local);
152
fake_select_lex->context.table_list=
153
fake_select_lex->context.first_name_resolution_table=
154
fake_select_lex->get_table_list();
155
if (!fake_select_lex->first_execution)
157
for (ORDER *order= global_parameters->order_list.first;
160
order->item= &order->item_ptr;
162
for (ORDER *order= global_parameters->order_list.first;
166
(*order->item)->walk(&Item::change_context_processor, 0,
167
(uchar*) &fake_select_lex->context);
172
bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
173
ulong additional_options)
175
SELECT_LEX *lex_select_save= thd_arg->lex->current_select;
176
SELECT_LEX *sl, *first_sl= first_select();
177
select_result *tmp_result;
178
bool is_union_select;
179
DBUG_ENTER("st_select_lex_unit::prepare");
181
describe= test(additional_options & SELECT_DESCRIBE);
184
result object should be reassigned even if preparing already done for
185
max/min subquery (ALL/ANY optimization)
193
/* fast reinit for EXPLAIN */
194
for (sl= first_sl; sl; sl= sl->next_select())
196
sl->join->result= result;
197
select_limit_cnt= HA_POS_ERROR;
199
if (!sl->join->procedure &&
200
result->prepare(sl->join->fields_list, this))
204
sl->join->select_options|= SELECT_DESCRIBE;
213
thd_arg->lex->current_select= sl= first_sl;
214
found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS;
215
is_union_select= is_union() || fake_select_lex;
221
if (!(tmp_result= union_result= new select_union))
224
tmp_result= sel_result;
227
tmp_result= sel_result;
229
sl->context.resolve_in_select_list= TRUE;
231
for (;sl; sl= sl->next_select())
233
bool can_skip_order_by;
234
sl->options|= SELECT_NO_UNLOCK;
235
JOIN *join= new JOIN(thd_arg, sl->item_list,
236
sl->options | thd_arg->variables.option_bits | additional_options,
239
setup_tables_done_option should be set only for very first SELECT,
240
because it protect from secont setup_tables call for select-like non
241
select commands (DELETE/INSERT/...) and they use only very first
242
SELECT (for union it can be only INSERT ... SELECT).
244
additional_options&= ~OPTION_SETUP_TABLES_DONE;
248
thd_arg->lex->current_select= sl;
250
can_skip_order_by= is_union_select && !(sl->braces && sl->explicit_limit);
252
saved_error= join->prepare(&sl->ref_pointer_array,
253
sl->table_list.first,
256
(can_skip_order_by ? 0 :
257
sl->order_list.elements) +
258
sl->group_list.elements,
260
NULL : sl->order_list.first,
261
sl->group_list.first,
263
(is_union_select ? NULL :
264
thd_arg->lex->proc_list.first),
266
/* There are no * in the statement anymore (for PS) */
268
last_procedure= join->procedure;
270
if (saved_error || (saved_error= thd_arg->is_fatal_error))
273
Use items list of underlaid select for derived tables to preserve
274
information about fields lengths and exact types
276
if (!is_union_select)
277
types= first_sl->item_list;
278
else if (sl == first_sl)
281
List_iterator_fast<Item> it(sl->item_list);
283
while ((item_tmp= it++))
285
/* Error's in 'new' will be detected after loop */
286
types.push_back(new Item_type_holder(thd_arg, item_tmp));
289
if (thd_arg->is_fatal_error)
290
goto err; // out of memory
294
if (types.elements != sl->item_list.elements)
296
my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT,
297
ER(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),MYF(0));
300
List_iterator_fast<Item> it(sl->item_list);
301
List_iterator_fast<Item> tp(types);
302
Item *type, *item_tmp;
303
while ((type= tp++, item_tmp= it++))
305
if (((Item_type_holder*)type)->join_types(thd_arg, item_tmp))
314
Check that it was possible to aggregate
315
all collations together for UNION.
317
List_iterator_fast<Item> tp(types);
319
ulonglong create_options;
323
if (type->result_type() == STRING_RESULT &&
324
type->collation.derivation == DERIVATION_NONE)
326
my_error(ER_CANT_AGGREGATE_NCOLLATIONS, MYF(0), "UNION");
332
Disable the usage of fulltext searches in the last union branch.
333
This is a temporary 5.x limitation because of the way the fulltext
334
search functions are handled by the optimizer.
335
This is manifestation of the more general problems of "taking away"
336
parts of a SELECT statement post-fix_fields(). This is generally not
337
doable since various flags are collected in various places (e.g.
338
SELECT_LEX) that carry information about the presence of certain
339
expressions or constructs in the parts of the query.
340
When part of the query is taken away it's not clear how to "divide"
341
the meaning of these accumulated flags and what to carry over to the
342
recipient query (SELECT_LEX).
344
if (global_parameters->ftfunc_list->elements &&
345
global_parameters->order_list.elements &&
346
global_parameters != fake_select_lex)
349
Item_func::Functype ft= Item_func::FT_FUNC;
350
for (ord= global_parameters->order_list.first; ord; ord= ord->next)
351
if ((*ord->item)->walk (&Item::find_function_processor, FALSE,
354
my_error (ER_CANT_USE_OPTION_HERE, MYF(0), "MATCH()");
360
create_options= (first_sl->options | thd_arg->variables.option_bits |
361
TMP_TABLE_ALL_COLUMNS);
363
Force the temporary table to be a MyISAM table if we're going to use
364
fullext functions (MATCH ... AGAINST .. IN BOOLEAN MODE) when reading
365
from it (this should be removed in 5.2 when fulltext search is moved
368
if (global_parameters->ftfunc_list->elements)
369
create_options= create_options | TMP_TABLE_FORCE_MYISAM;
371
if (union_result->create_result_table(thd, &types, test(union_distinct),
374
bzero((char*) &result_table_list, sizeof(result_table_list));
375
result_table_list.db= (char*) "";
376
result_table_list.table_name= result_table_list.alias= (char*) "union";
377
result_table_list.table= table= union_result->table;
379
thd_arg->lex->current_select= lex_select_save;
380
if (!item_list.elements)
382
Query_arena *arena, backup_arena;
384
arena= thd->activate_stmt_arena_if_needed(&backup_arena);
386
saved_error= table->fill_item_list(&item_list);
389
thd->restore_active_arena(arena, &backup_arena);
394
if (thd->stmt_arena->is_stmt_prepare())
396
/* Validate the global parameters of this union */
398
init_prepare_fake_select_lex(thd);
399
/* Should be done only once (the only item_list per statement) */
400
DBUG_ASSERT(fake_select_lex->join == 0);
401
if (!(fake_select_lex->join= new JOIN(thd, item_list, thd->variables.option_bits,
404
fake_select_lex->table_list.empty();
409
Fake st_select_lex should have item list for correct ref_array
412
fake_select_lex->item_list= item_list;
414
thd_arg->lex->current_select= fake_select_lex;
417
We need to add up n_sum_items in order to make the correct
418
allocation in setup_ref_array().
420
fake_select_lex->n_child_sum_items+= global_parameters->n_sum_items;
422
saved_error= fake_select_lex->join->
423
prepare(&fake_select_lex->ref_pointer_array,
424
fake_select_lex->table_list.first,
426
global_parameters->order_list.elements, // og_num
427
global_parameters->order_list.first, // order
429
fake_select_lex, this);
430
fake_select_lex->table_list.empty();
436
We're in execution of a prepared statement or stored procedure:
437
reset field items to point at fields from the created temporary table.
439
table->reset_item_list(&item_list);
443
thd_arg->lex->current_select= lex_select_save;
445
DBUG_RETURN(saved_error || thd_arg->is_fatal_error);
448
thd_arg->lex->current_select= lex_select_save;
454
bool st_select_lex_unit::exec()
456
SELECT_LEX *lex_select_save= thd->lex->current_select;
457
SELECT_LEX *select_cursor=first_select();
458
ulonglong add_rows=0;
459
ha_rows examined_rows= 0;
460
DBUG_ENTER("st_select_lex_unit::exec");
462
if (executed && !uncacheable && !describe)
466
if (uncacheable || !item || !item->assigned() || describe)
469
item->reset_value_registration();
470
if (optimized && item)
472
if (item->assigned())
474
item->assigned(0); // We will reinit & rexecute unit
476
table->file->ha_delete_all_rows();
478
/* re-enabling indexes for next subselect iteration */
479
if (union_distinct && table->file->ha_enable_indexes(HA_KEY_SWITCH_ALL))
484
for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
486
ha_rows records_at_start= 0;
487
thd->lex->current_select= sl;
490
saved_error= sl->join->reinit();
494
if (sl == global_parameters || describe)
498
We can't use LIMIT at this stage if we are using ORDER BY for the
501
if (sl->order_list.first || describe)
502
select_limit_cnt= HA_POS_ERROR;
506
When using braces, SQL_CALC_FOUND_ROWS affects the whole query:
507
we don't calculate found_rows() per union part.
508
Otherwise, SQL_CALC_FOUND_ROWS should be done on all sub parts.
510
sl->join->select_options=
511
(select_limit_cnt == HA_POS_ERROR || sl->braces) ?
512
sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
513
saved_error= sl->join->optimize();
517
records_at_start= table->file->stats.records;
519
if (sl == union_distinct)
521
if (table->file->ha_disable_indexes(HA_KEY_SWITCH_ALL))
525
saved_error= sl->join->error;
526
offset_limit_cnt= (ha_rows)(sl->offset_limit ?
527
sl->offset_limit->val_uint() :
531
examined_rows+= thd->examined_row_count;
532
if (union_result->flush())
534
thd->lex->current_select= lex_select_save;
541
thd->lex->current_select= lex_select_save;
542
DBUG_RETURN(saved_error);
544
/* Needed for the following test and for records_at_start in next loop */
545
int error= table->file->info(HA_STATUS_VARIABLE);
548
table->file->print_error(error, MYF(0));
551
if (found_rows_for_union && !sl->braces &&
552
select_limit_cnt != HA_POS_ERROR)
555
This is a union without braces. Remember the number of rows that
556
could also have been part of the result set.
557
We get this from the difference of between total number of possible
558
rows and actual rows added to the temporary table.
560
add_rows+= (ulonglong) (thd->limit_found_rows - (ulonglong)
561
((table->file->stats.records - records_at_start)));
567
/* Send result to 'result' */
570
List<Item_func_match> empty_list;
573
if (!thd->is_fatal_error) // Check if EOM
575
set_limit(global_parameters);
576
init_prepare_fake_select_lex(thd);
577
JOIN *join= fake_select_lex->join;
581
allocate JOIN for fake select only once (prevent
582
mysql_select automatic allocation)
583
TODO: The above is nonsense. mysql_select() will not allocate the
584
join if one already exists. There must be some other reason why we
585
don't let it allocate the join. Perhaps this is because we need
586
some special parameter values passed to join constructor?
588
if (!(fake_select_lex->join= new JOIN(thd, item_list,
589
fake_select_lex->options, result)))
591
fake_select_lex->table_list.empty();
594
fake_select_lex->join->no_const_tables= TRUE;
597
Fake st_select_lex should have item list for correct ref_array
600
fake_select_lex->item_list= item_list;
603
We need to add up n_sum_items in order to make the correct
604
allocation in setup_ref_array().
605
Don't add more sum_items if we have already done JOIN::prepare
606
for this (with a different join object)
608
if (!fake_select_lex->ref_pointer_array)
609
fake_select_lex->n_child_sum_items+= global_parameters->n_sum_items;
611
saved_error= mysql_select(thd, &fake_select_lex->ref_pointer_array,
614
global_parameters->order_list.elements,
615
global_parameters->order_list.first,
617
fake_select_lex->options | SELECT_NO_UNLOCK,
618
result, this, fake_select_lex);
625
In EXPLAIN command, constant subqueries that do not use any
626
tables are executed two times:
627
- 1st time is a real evaluation to get the subquery value
628
- 2nd time is to produce EXPLAIN output rows.
629
1st execution sets certain members (e.g. select_result) to perform
630
subquery execution rather than EXPLAIN line production. In order
631
to reset them back, we re-do all of the actions (yes it is ugly):
633
join->init(thd, item_list, fake_select_lex->options, result);
634
saved_error= mysql_select(thd, &fake_select_lex->ref_pointer_array,
637
global_parameters->order_list.elements,
638
global_parameters->order_list.first,
640
fake_select_lex->options | SELECT_NO_UNLOCK,
641
result, this, fake_select_lex);
645
join->examined_rows= 0;
646
saved_error= join->reinit();
651
fake_select_lex->table_list.empty();
654
thd->limit_found_rows = (ulonglong)table->file->stats.records + add_rows;
655
thd->examined_row_count+= examined_rows;
658
Mark for slow query log if any of the union parts didn't use
663
thd->lex->current_select= lex_select_save;
664
DBUG_RETURN(saved_error);
668
bool st_select_lex_unit::cleanup()
671
DBUG_ENTER("st_select_lex_unit::cleanup");
682
union_result=0; // Safety
684
free_tmp_table(thd, table);
688
for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
689
error|= sl->cleanup();
694
if ((join= fake_select_lex->join))
696
join->tables_list= 0;
699
error|= fake_select_lex->cleanup();
701
There are two cases when we should clean order items:
702
1. UNION with SELECTs which all enclosed into braces
703
in this case global_parameters == fake_select_lex
704
2. UNION where last SELECT is not enclosed into braces
705
in this case global_parameters == 'last select'
706
So we should use global_parameters->order_list for
707
proper order list clean up.
708
Note: global_parameters and fake_select_lex are always
709
initialized for UNION
711
DBUG_ASSERT(global_parameters);
712
if (global_parameters->order_list.elements)
715
for (ord= global_parameters->order_list.first; ord; ord= ord->next)
716
(*ord->item)->walk (&Item::cleanup_processor, 0, 0);
724
void st_select_lex_unit::reinit_exec_mechanism()
726
prepared= optimized= executed= 0;
730
List_iterator_fast<Item> it(item_list);
732
while ((field= it++))
735
we can't cleanup here, because it broke link to temporary table field,
736
but have to drop fixed flag to allow next fix_field of this field
747
change select_result object of unit
750
st_select_lex_unit::change_result()
751
result new select_result object
752
old_result old select_result object
759
bool st_select_lex_unit::change_result(select_subselect *new_result,
760
select_subselect *old_result)
763
for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
765
if (sl->join && sl->join->result == old_result)
766
if (sl->join->change_result(new_result))
769
if (fake_select_lex && fake_select_lex->join)
770
res= fake_select_lex->join->change_result(new_result);
775
Get column type information for this unit.
778
st_select_lex_unit::get_unit_column_types()
781
For a single-select the column types are taken
782
from the list of selected items. For a union this function
783
assumes that st_select_lex_unit::prepare has been called
784
and returns the type holders that were created for unioned
785
column types of all selects.
788
The implementation of this function should be in sync with
789
st_select_lex_unit::prepare()
792
List<Item> *st_select_lex_unit::get_unit_column_types()
794
SELECT_LEX *sl= first_select();
795
bool is_procedure= test(sl->join->procedure);
799
/* Types for "SELECT * FROM t1 procedure analyse()"
800
are generated during execute */
801
return &sl->join->procedure_fields_list;
807
DBUG_ASSERT(prepared);
808
/* Types are generated during prepare */
812
return &sl->item_list;
815
bool st_select_lex::cleanup()
818
DBUG_ENTER("st_select_lex::cleanup()");
822
DBUG_ASSERT((st_select_lex*)join->select_lex == this);
823
error= join->destroy();
827
for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
828
lex_unit= lex_unit->next_unit())
830
error= (bool) ((uint) error | (uint) lex_unit->cleanup());
832
non_agg_fields.empty();
833
inner_refs_list.empty();
838
void st_select_lex::cleanup_all_joins(bool full)
840
SELECT_LEX_UNIT *unit;
846
for (unit= first_inner_unit(); unit; unit= unit->next_unit())
847
for (sl= unit->first_select(); sl; sl= sl->next_select())
848
sl->cleanup_all_joins(full);