sql_union.cc 13 KB
Newer Older
1
/* Copyright (C) 2000-2003 MySQL AB
unknown's avatar
unknown committed
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */


/*
  UNION  of select's
  UNION's  were introduced by Monty and Sinisa <sinisa@mysql.com>
*/


#include "mysql_priv.h"
#include "sql_select.h"

27
int mysql_union(THD *thd, LEX *lex, select_result *result,
28
		SELECT_LEX_UNIT *unit)
unknown's avatar
unknown committed
29 30 31
{
  DBUG_ENTER("mysql_union");
  int res= 0;
32
  if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK)))
unknown's avatar
unknown committed
33 34 35 36 37 38 39 40 41 42 43
    res= unit->exec();
  res|= unit->cleanup();
  DBUG_RETURN(res);
}


/***************************************************************************
** store records in temporary table for UNION
***************************************************************************/

select_union::select_union(TABLE *table_par)
unknown's avatar
unknown committed
44
  :table(table_par), not_describe(0)
unknown's avatar
unknown committed
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
{
  bzero((char*) &info,sizeof(info));
  /*
    We can always use DUP_IGNORE because the temporary table will only
    contain a unique key if we are using not using UNION ALL
  */
  info.handle_duplicates= DUP_IGNORE;
}

select_union::~select_union()
{
}


int select_union::prepare(List<Item> &list, SELECT_LEX_UNIT *u)
{
  unit= u;
  return 0;
}

65

unknown's avatar
unknown committed
66 67 68 69 70 71 72
bool select_union::send_data(List<Item> &values)
{
  if (unit->offset_limit_cnt)
  {						// using limit offset,count
    unit->offset_limit_cnt--;
    return 0;
  }
73
  fill_record(table->field, values, 1);
unknown's avatar
unknown committed
74
  if (thd->net.report_error || write_record(table,&info))
unknown's avatar
unknown committed
75
  {
76 77 78
    if (thd->net.last_errno == ER_RECORD_FILE_FULL)
    {
      thd->clear_error(); // do not report user about table overflow
79
      if (create_myisam_from_heap(thd, table, &tmp_table_param,
unknown's avatar
unknown committed
80
				  info.last_errno, 1))
81 82 83
	return 1;
    }
    else
unknown's avatar
unknown committed
84 85 86 87 88
      return 1;
  }
  return 0;
}

89

unknown's avatar
unknown committed
90 91 92 93
bool select_union::send_eof()
{
  return 0;
}
unknown's avatar
unknown committed
94

95

unknown's avatar
unknown committed
96
bool select_union::flush()
unknown's avatar
unknown committed
97
{
unknown's avatar
unknown committed
98 99 100 101
  int error;
  if ((error=table->file->extra(HA_EXTRA_NO_CACHE)))
  {
    table->file->print_error(error,MYF(0));
unknown's avatar
unknown committed
102
    ::send_error(thd);
unknown's avatar
unknown committed
103 104 105 106 107
    return 1;
  }
  return 0;
}

108

109 110
int st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
				ulong additional_options)
unknown's avatar
unknown committed
111
{
112
  SELECT_LEX *lex_select_save= thd_arg->lex->current_select;
113 114
  SELECT_LEX *sl, *first_select;
  select_result *tmp_result;
unknown's avatar
unknown committed
115
  ORDER *tmp_order;
unknown's avatar
unknown committed
116 117
  DBUG_ENTER("st_select_lex_unit::prepare");

118 119 120 121 122 123
  /*
    result object should be reassigned even if preparing already done for
    max/min subquery (ALL/ANY optimization)
  */
  result= sel_result;

unknown's avatar
unknown committed
124 125 126
  if (prepared)
    DBUG_RETURN(0);
  prepared= 1;
unknown's avatar
unknown committed
127
  res= 0;
unknown's avatar
unknown committed
128
  
129
  thd_arg->lex->current_select= sl= first_select= first_select_in_union();
130 131
  found_rows_for_union= first_select->options & OPTION_FOUND_ROWS;

132
  /* Global option */
133 134

  if (first_select->next_select())
unknown's avatar
unknown committed
135
  {
136 137 138
    if (!(tmp_result= union_result= new select_union(0)))
      goto err;
    union_result->not_describe= 1;
139
    union_result->tmp_table_param.init();
140 141 142
  }
  else
  {
143 144 145
    tmp_result= sel_result;
    // single select should be processed like select in p[arantses
    first_select->braces= 1;
unknown's avatar
unknown committed
146
  }
unknown's avatar
unknown committed
147

148
  for (;sl; sl= sl->next_select())
unknown's avatar
unknown committed
149
  {
unknown's avatar
unknown committed
150
    sl->options|=  SELECT_NO_UNLOCK;
151
    JOIN *join= new JOIN(thd_arg, sl->item_list, 
152
			 sl->options | thd_arg->options | additional_options,
153
			 tmp_result);
unknown's avatar
unknown committed
154 155 156
    if (!join)
      goto err;

157
    thd_arg->lex->current_select= sl;
158 159 160 161
    offset_limit_cnt= sl->offset_limit;
    select_limit_cnt= sl->select_limit+sl->offset_limit;
    if (select_limit_cnt < sl->select_limit)
      select_limit_cnt= HA_POS_ERROR;		// no limit
unknown's avatar
unknown committed
162
    if (select_limit_cnt == HA_POS_ERROR || sl->braces)
163 164 165 166 167 168 169 170 171 172 173 174
      sl->options&= ~OPTION_FOUND_ROWS;
    
    res= join->prepare(&sl->ref_pointer_array,
		       (TABLE_LIST*) sl->table_list.first, sl->with_wild,
		       sl->where,
		       ((sl->braces) ? sl->order_list.elements : 0) +
		       sl->group_list.elements,
		       (sl->braces) ? 
		       (ORDER *)sl->order_list.first : (ORDER *) 0,
		       (ORDER*) sl->group_list.first,
		       sl->having,
		       (ORDER*) NULL,
175
		       sl, this);
176
    if (res || thd_arg->is_fatal_error)
177
      goto err;
178 179 180 181
    if (sl == first_select)
    {
      types.empty();
      List_iterator_fast<Item> it(sl->item_list);
182 183
      Item *item_tmp;
      while ((item_tmp= it++))
184
      {
unknown's avatar
unknown committed
185
	/* Error's in 'new' will be detected after loop */
186
	types.push_back(new Item_type_holder(thd_arg, item_tmp));
187 188
      }

189
      if (thd_arg->is_fatal_error)
190 191 192 193 194 195 196 197 198 199 200 201
	goto err; // out of memory
    }
    else
    {
      if (types.elements != sl->item_list.elements)
      {
	my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT,
		   ER(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),MYF(0));
	goto err;
      }
      List_iterator_fast<Item> it(sl->item_list);
      List_iterator_fast<Item> tp(types);	
202 203
      Item *type, *item_tmp;
      while ((type= tp++, item_tmp= it++))
204
      {
205
	if (((Item_type_holder*)type)->join_types(thd_arg, item_tmp))
unknown's avatar
unknown committed
206
	  DBUG_RETURN(-1);
207 208
      }
    }
unknown's avatar
unknown committed
209
  }
210

211 212
  item_list.empty();
  // it is not single select
213
  if (first_select->next_select())
214
  {
215 216 217
    union_result->tmp_table_param.field_count= types.elements;
    if (!(table= create_tmp_table(thd_arg,
				  &union_result->tmp_table_param, types,
218
				  (ORDER*) 0, union_distinct, 1, 
219
				  (first_select_in_union()->options |
220
				   thd_arg->options |
221 222 223 224 225 226 227 228 229 230
				   TMP_TABLE_ALL_COLUMNS),
				  HA_POS_ERROR, (char*) "")))
      goto err;
    table->file->extra(HA_EXTRA_WRITE_CACHE);
    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
    bzero((char*) &result_table_list, sizeof(result_table_list));
    result_table_list.db= (char*) "";
    result_table_list.real_name= result_table_list.alias= (char*) "union";
    result_table_list.table= table;
    union_result->set_table(table);
231

232
    thd_arg->lex->current_select= lex_select_save;
233
    {
234 235 236 237
      Statement *stmt= thd->current_statement;
      Statement backup;
      if (stmt)
	thd->set_n_backup_item_arena(stmt, &backup);
238 239 240
      Field **field;
      for (field= table->field; *field; field++)
      {
unknown's avatar
unknown committed
241 242
	Item_field *item= new Item_field(*field);
	if (!item || item_list.push_back(item))
243 244 245
	{
	  if (stmt)
	    thd->restore_backup_item_arena(stmt, &backup);
246
	  DBUG_RETURN(-1);
247
	}
248
      }
249 250
      if (stmt)
	thd->restore_backup_item_arena(stmt, &backup);
251 252
    }
  }
253 254 255
  else
    first_select->braces= 0; // remove our changes

256
  thd_arg->lex->current_select= lex_select_save;
257

258
  DBUG_RETURN(res || thd_arg->is_fatal_error ? 1 : 0);
259

unknown's avatar
unknown committed
260
err:
261
  thd_arg->lex->current_select= lex_select_save;
unknown's avatar
unknown committed
262
  DBUG_RETURN(-1);
unknown's avatar
unknown committed
263 264
}

265

unknown's avatar
unknown committed
266 267
int st_select_lex_unit::exec()
{
268
  SELECT_LEX *lex_select_save= thd->lex->current_select;
unknown's avatar
unknown committed
269
  SELECT_LEX *select_cursor=first_select_in_union();
unknown's avatar
unknown committed
270
  ulonglong add_rows=0;
271 272
  DBUG_ENTER("st_select_lex_unit::exec");

273
  if (executed && !uncacheable)
unknown's avatar
unknown committed
274 275 276
    DBUG_RETURN(0);
  executed= 1;
  
277
  if (uncacheable || !item || !item->assigned())
unknown's avatar
unknown committed
278 279
  {
    if (optimized && item && item->assigned())
unknown's avatar
unknown committed
280
    {
unknown's avatar
unknown committed
281
      item->assigned(0); // We will reinit & rexecute unit
282
      item->reset();
unknown's avatar
unknown committed
283 284
      table->file->delete_all_rows();
    }
285 286
    if (union_distinct) // for subselects
      table->file->extra(HA_EXTRA_CHANGE_KEY_TO_UNIQUE);
287
    for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
unknown's avatar
unknown committed
288
    {
unknown's avatar
unknown committed
289
      ha_rows records_at_start= 0;
290
      thd->lex->current_select= sl;
291

unknown's avatar
unknown committed
292
      if (optimized)
unknown's avatar
unknown committed
293
	res= sl->join->reinit();
unknown's avatar
unknown committed
294 295
      else
      {
296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312
	if (sl != global_parameters)
	{
	  offset_limit_cnt= sl->offset_limit;
	  select_limit_cnt= sl->select_limit+sl->offset_limit;
	}
	else
	{
	  offset_limit_cnt= 0;
	  /*
	    We can't use LIMIT at this stage if we are using ORDER BY for the
	    whole query
	  */
	  if (sl->order_list.first)
	    select_limit_cnt= HA_POS_ERROR;
	  else
	    select_limit_cnt= sl->select_limit+sl->offset_limit;
	}
unknown's avatar
unknown committed
313 314
	if (select_limit_cnt < sl->select_limit)
	  select_limit_cnt= HA_POS_ERROR;		// no limit
unknown's avatar
unknown committed
315 316 317 318 319

	/*
	  When using braces, SQL_CALC_FOUND_ROWS affects the whole query.
	  We don't calculate found_rows() per union part
	*/
unknown's avatar
unknown committed
320
	if (select_limit_cnt == HA_POS_ERROR || sl->braces)
unknown's avatar
unknown committed
321
	  sl->options&= ~OPTION_FOUND_ROWS;
unknown's avatar
unknown committed
322
	else 
323
	{
unknown's avatar
unknown committed
324 325 326 327 328
	  /*
	    We are doing an union without braces.  In this case
	    SQL_CALC_FOUND_ROWS should be done on all sub parts
	  */
	  sl->options|= found_rows_for_union;
329
	}
330
	sl->join->select_options=sl->options;
unknown's avatar
unknown committed
331 332
	res= sl->join->optimize();
      }
unknown's avatar
unknown committed
333 334
      if (!res)
      {
unknown's avatar
unknown committed
335
	records_at_start= table->file->records;
unknown's avatar
unknown committed
336
	sl->join->exec();
337 338
        if (sl == union_distinct)
          table->file->extra(HA_EXTRA_CHANGE_KEY_TO_DUP);
unknown's avatar
unknown committed
339
	res= sl->join->error;
340
	offset_limit_cnt= sl->offset_limit;
unknown's avatar
unknown committed
341 342
	if (!res && union_result->flush())
	{
343
	  thd->lex->current_select= lex_select_save;
unknown's avatar
unknown committed
344 345
	  DBUG_RETURN(1);
	}
unknown's avatar
unknown committed
346
      }
unknown's avatar
unknown committed
347
      if (res)
unknown's avatar
unknown committed
348
      {
349
	thd->lex->current_select= lex_select_save;
unknown's avatar
unknown committed
350
	DBUG_RETURN(res);
unknown's avatar
unknown committed
351
      }
unknown's avatar
unknown committed
352 353
      /* Needed for the following test and for records_at_start in next loop */
      table->file->info(HA_STATUS_VARIABLE);
unknown's avatar
unknown committed
354 355 356
      if (found_rows_for_union & sl->options)
      {
	/*
unknown's avatar
unknown committed
357 358
	  This is a union without braces. Remember the number of rows that
	  could also have been part of the result set.
unknown's avatar
unknown committed
359 360 361
	  We get this from the difference of between total number of possible
	  rows and actual rows added to the temporary table.
	*/
362
	add_rows+= (ulonglong) (thd->limit_found_rows - (ulonglong)
unknown's avatar
unknown committed
363
			      ((table->file->records -  records_at_start)));
unknown's avatar
unknown committed
364
      }
unknown's avatar
unknown committed
365
    }
unknown's avatar
unknown committed
366
  }
unknown's avatar
unknown committed
367
  optimized= 1;
368 369

  /* Send result to 'result' */
370

unknown's avatar
unknown committed
371

372
  res= -1;
unknown's avatar
unknown committed
373
  {
unknown's avatar
unknown committed
374 375
    List<Item_func_match> empty_list;
    empty_list.empty();
unknown's avatar
unknown committed
376

unknown's avatar
unknown committed
377
    if (!thd->is_fatal_error)				// Check if EOM
378
    {
379
      ulong options_tmp= thd->options;
380
      thd->lex->current_select= fake_select_lex;
381 382 383
      offset_limit_cnt= global_parameters->offset_limit;
      select_limit_cnt= global_parameters->select_limit +
	global_parameters->offset_limit;
unknown's avatar
unknown committed
384

385 386
      if (select_limit_cnt < global_parameters->select_limit)
	select_limit_cnt= HA_POS_ERROR;		// no limit
unknown's avatar
unknown committed
387
      if (select_limit_cnt == HA_POS_ERROR)
388
	options_tmp&= ~OPTION_FOUND_ROWS;
389
      else if (found_rows_for_union && !thd->lex->describe)
390
	options_tmp|= OPTION_FOUND_ROWS;
unknown's avatar
unknown committed
391 392 393 394
      fake_select_lex->ftfunc_list= &empty_list;
      fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
					       (byte **)
					       &result_table_list.next);
395 396 397 398 399 400 401
      JOIN *join= fake_select_lex->join;
      if (!join)
      {
	/*
	  allocate JOIN for fake select only once (privent
	  mysql_select automatic allocation)
	*/
unknown's avatar
unknown committed
402 403
	if (!(fake_select_lex->join= new JOIN(thd, item_list, thd->options,
					      result)))
404 405
	{
	  fake_select_lex->table_list.empty();
unknown's avatar
unknown committed
406
	  DBUG_RETURN(-1);
407
	}
unknown's avatar
unknown committed
408

unknown's avatar
unknown committed
409 410 411 412 413
	/*
	  Fake st_select_lex should have item list for correctref_array
	  allocation.
	*/
	fake_select_lex->item_list= item_list;
414 415 416 417 418 419 420 421 422 423 424
      }
      else
      {
	JOIN_TAB *tab,*end;
	for (tab=join->join_tab,end=tab+join->tables ; tab != end ; tab++)
	{
	  delete tab->select;
	  delete tab->quick;
	}
	join->init(thd, item_list, thd->options, result);
      }
unknown's avatar
unknown committed
425 426
      res= mysql_select(thd, &fake_select_lex->ref_pointer_array,
			&result_table_list,
427 428
			0, item_list, NULL,
			global_parameters->order_list.elements,
unknown's avatar
unknown committed
429
			(ORDER*)global_parameters->order_list.first,
430
			(ORDER*) NULL, NULL, (ORDER*) NULL,
431
			options_tmp | SELECT_NO_UNLOCK,
432
			result, this, fake_select_lex);
433 434

      fake_select_lex->table_list.empty();
unknown's avatar
unknown committed
435 436
      if (!res)
	thd->limit_found_rows = (ulonglong)table->file->records + add_rows;
437 438 439 440
      /*
	Mark for slow query log if any of the union parts didn't use
	indexes efficiently
      */
441
    }
unknown's avatar
unknown committed
442
  }
443
  thd->lex->current_select= lex_select_save;
unknown's avatar
unknown committed
444 445 446
  DBUG_RETURN(res);
}

447

unknown's avatar
unknown committed
448
int st_select_lex_unit::cleanup()
unknown's avatar
unknown committed
449
{
450
  int error= 0;
451
  DBUG_ENTER("st_select_lex_unit::cleanup");
452

453 454 455 456
  if (cleaned)
  {
    DBUG_RETURN(0);
  }
457
  cleaned= 1;
458

unknown's avatar
unknown committed
459 460 461
  if (union_result)
  {
    delete union_result;
unknown's avatar
unknown committed
462
    union_result=0; // Safety
463 464
    if (table)
      free_tmp_table(thd, table);
unknown's avatar
unknown committed
465 466
    table= 0; // Safety
  }
467
  JOIN *join;
468 469
  SELECT_LEX *sl= first_select_in_union();
  for (; sl; sl= sl->next_select())
unknown's avatar
unknown committed
470
  {
471 472
    if ((join= sl->join))
    {
473
      error|= sl->join->cleanup();
474 475
      delete join;
    }
476 477 478 479 480 481 482 483 484 485
    else
    {
      // it can be DO/SET with subqueries
      for (SELECT_LEX_UNIT *lex_unit= sl->first_inner_unit();
	   lex_unit != 0;
	   lex_unit= lex_unit->next_unit())
      {
	error|= lex_unit->cleanup();
      }
    }
unknown's avatar
unknown committed
486
  }
487 488 489 490 491 492 493
  if (fake_select_lex && (join= fake_select_lex->join))
  {
    join->tables_list= 0;
    join->tables= 0;
    error|= join->cleanup();
    delete join;
  }
494
  DBUG_RETURN(error);
unknown's avatar
unknown committed
495
}
unknown's avatar
unknown committed
496 497 498 499 500


void st_select_lex_unit::reinit_exec_mechanism()
{
  prepared= optimized= executed= 0;
unknown's avatar
unknown committed
501
#ifndef DBUG_OFF
502
  if (first_select()->next_select())
unknown's avatar
unknown committed
503
  {
504 505 506 507 508 509 510 511 512 513 514
    List_iterator_fast<Item> it(item_list);
    Item *field;
    while ((field= it++))
    {
      /*
	we can't cleanup here, because it broke link to temporary table field,
	but have to drop fixed flag to allow next fix_field of this field
	during re-executing
      */
      field->fixed= 0;
    }
unknown's avatar
unknown committed
515 516
  }
#endif
unknown's avatar
unknown committed
517
}