sql_union.cc 11.6 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,
unknown's avatar
unknown committed
28
		SELECT_LEX_UNIT *unit, bool tables_and_fields_initied)
unknown's avatar
unknown committed
29 30 31
{
  DBUG_ENTER("mysql_union");
  int res= 0;
unknown's avatar
unknown committed
32
  if (!(res= unit->prepare(thd, result, tables_and_fields_initied)))
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
{
  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;
unknown's avatar
unknown committed
62
  if (not_describe && list.elements != table->fields)
unknown's avatar
unknown committed
63 64 65 66 67 68 69 70
  {
    my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT,
	       ER(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT),MYF(0));
    return -1;
  }
  return 0;
}

71

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

95

unknown's avatar
unknown committed
96 97 98 99
bool select_union::send_eof()
{
  return 0;
}
unknown's avatar
unknown committed
100

101

unknown's avatar
unknown committed
102
bool select_union::flush()
unknown's avatar
unknown committed
103
{
unknown's avatar
unknown committed
104 105 106 107
  int error;
  if ((error=table->file->extra(HA_EXTRA_NO_CACHE)))
  {
    table->file->print_error(error,MYF(0));
unknown's avatar
unknown committed
108
    ::send_error(thd);
unknown's avatar
unknown committed
109 110 111 112 113
    return 1;
  }
  return 0;
}

114 115

int st_select_lex_unit::prepare(THD *thd, select_result *sel_result,
unknown's avatar
unknown committed
116
				bool tables_and_fields_initied)
unknown's avatar
unknown committed
117
{
unknown's avatar
unknown committed
118
  SELECT_LEX *lex_select_save= thd->lex.current_select;
119
  SELECT_LEX *select_cursor,*sl;
unknown's avatar
unknown committed
120 121 122 123 124
  DBUG_ENTER("st_select_lex_unit::prepare");

  if (prepared)
    DBUG_RETURN(0);
  prepared= 1;
unknown's avatar
unknown committed
125
  res= 0;
unknown's avatar
unknown committed
126 127
  found_rows_for_union= test(first_select_in_union()->options
			     & OPTION_FOUND_ROWS);
unknown's avatar
unknown committed
128
  TMP_TABLE_PARAM tmp_table_param;
129
  result= sel_result;
unknown's avatar
unknown committed
130
  t_and_f= tables_and_fields_initied;
unknown's avatar
unknown committed
131 132
  
  bzero((char *)&tmp_table_param,sizeof(TMP_TABLE_PARAM));
133
  thd->lex.current_select= sl= select_cursor= first_select_in_union();
134
  /* Global option */
unknown's avatar
unknown committed
135
  if (t_and_f)
unknown's avatar
unknown committed
136
  {
unknown's avatar
unknown committed
137
    // Item list and tables will be initialized by mysql_derived
138
    item_list= select_cursor->item_list;
139 140 141 142
  }
  else
  {
    item_list.empty();
143
    TABLE_LIST *first_table= (TABLE_LIST*) select_cursor->table_list.first;
unknown's avatar
unknown committed
144

145
    if (setup_tables(first_table) ||
146 147
	setup_wild(thd, first_table, select_cursor->item_list, 0,
		   select_cursor->with_wild))
148
      goto err;
149
    List_iterator<Item> it(select_cursor->item_list);	
150
    Item *item;
151 152
    item_list= select_cursor->item_list;
    select_cursor->with_wild= 0;
unknown's avatar
unknown committed
153 154 155
    if (select_cursor->setup_ref_array(thd,
				       select_cursor->order_list.elements +
				       select_cursor->group_list.elements) ||
156 157
	setup_fields(thd, select_cursor->ref_pointer_array, first_table,
		     item_list, 0, 0, 1))
unknown's avatar
unknown committed
158
      goto err;
unknown's avatar
unknown committed
159
    t_and_f= 1;
unknown's avatar
unknown committed
160 161 162 163 164 165
    while((item=it++))
    {
      item->maybe_null=1;
      if (item->type() == Item::FIELD_ITEM)
	((class Item_field *)item)->field->table->maybe_null=1;
    }
unknown's avatar
unknown committed
166
  }
unknown's avatar
unknown committed
167

unknown's avatar
unknown committed
168 169
  tmp_table_param.field_count=item_list.elements;
  if (!(table= create_tmp_table(thd, &tmp_table_param, item_list,
unknown's avatar
unknown committed
170
				(ORDER*) 0, !union_option,
171
				1, (select_cursor->options | thd->options |
unknown's avatar
unknown committed
172
				    TMP_TABLE_ALL_COLUMNS),
173
				HA_POS_ERROR, (char*) "")))
unknown's avatar
unknown committed
174
    goto err;
unknown's avatar
unknown committed
175 176 177 178
  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*) "";
179
  result_table_list.real_name=result_table_list.alias= (char*) "union";
unknown's avatar
unknown committed
180 181 182
  result_table_list.table=table;

  if (!(union_result=new select_union(table)))
unknown's avatar
unknown committed
183
    goto err;
unknown's avatar
unknown committed
184

unknown's avatar
unknown committed
185
  union_result->not_describe=1;
186
  union_result->tmp_table_param=tmp_table_param;
187

188
  for (;sl; sl= sl->next_select())
unknown's avatar
unknown committed
189
  {
unknown's avatar
unknown committed
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214
    JOIN *join= new JOIN(thd, sl->item_list, 
			 sl->options | thd->options | SELECT_NO_UNLOCK,
			 union_result);
    thd->lex.current_select= sl;
    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
    if (select_limit_cnt == HA_POS_ERROR || sl->braces)
      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,
		       sl, this, t_and_f);
    t_and_f= 0;
    if (res || thd->is_fatal_error)
      goto err;
unknown's avatar
unknown committed
215
  }
216

217
  item_list.empty();
218
  thd->lex.current_select= lex_select_save;
219
  {
220
    List_iterator<Item> it(select_cursor->item_list);
221 222 223 224 225 226 227 228 229 230
    Field **field;

    for (field= table->field; *field; field++)
    {
      (void) it++;
      if (item_list.push_back(new Item_field(*field)))
	DBUG_RETURN(-1);
    }
  }

231
  DBUG_RETURN(res || thd->is_fatal_error ? 1 : 0);
unknown's avatar
unknown committed
232
err:
233
  thd->lex.current_select= lex_select_save;
unknown's avatar
unknown committed
234
  DBUG_RETURN(-1);
unknown's avatar
unknown committed
235 236
}

237

unknown's avatar
unknown committed
238 239
int st_select_lex_unit::exec()
{
unknown's avatar
unknown committed
240
  SELECT_LEX *lex_select_save= thd->lex.current_select;
unknown's avatar
unknown committed
241
  SELECT_LEX *select_cursor=first_select_in_union();
unknown's avatar
unknown committed
242
  ha_rows add_rows=0;
243 244
  DBUG_ENTER("st_select_lex_unit::exec");

unknown's avatar
unknown committed
245
  if (executed && !(dependent || uncacheable))
unknown's avatar
unknown committed
246 247 248
    DBUG_RETURN(0);
  executed= 1;
  
249
  if ((dependent || uncacheable) || !item || !item->assigned())
unknown's avatar
unknown committed
250 251
  {
    if (optimized && item && item->assigned())
unknown's avatar
unknown committed
252
    {
unknown's avatar
unknown committed
253
      item->assigned(0); // We will reinit & rexecute unit
254
      item->reset();
unknown's avatar
unknown committed
255 256
      table->file->delete_all_rows();
    }
257
    for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
unknown's avatar
unknown committed
258
    {
unknown's avatar
unknown committed
259
      ha_rows records_at_start= 0;
260 261
      thd->lex.current_select= sl;

unknown's avatar
unknown committed
262
      if (optimized)
unknown's avatar
unknown committed
263
	res= sl->join->reinit();
unknown's avatar
unknown committed
264 265 266 267 268 269
      else
      {
	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
270
	if (select_limit_cnt == HA_POS_ERROR || sl->braces)
unknown's avatar
unknown committed
271
	  sl->options&= ~OPTION_FOUND_ROWS;
unknown's avatar
unknown committed
272
	else 
273
	{
unknown's avatar
unknown committed
274 275 276 277 278
	  /*
	    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;
279
	}
unknown's avatar
unknown committed
280
	
281 282 283 284 285 286 287 288 289
	/* 
	   As far as union share table space we should reassign table map,
	   which can be spoiled by 'prepare' of JOIN of other UNION parts
	   if it use same tables
	*/
	uint tablenr=0;
	for (TABLE_LIST *table_list= (TABLE_LIST*) sl->table_list.first;
	     table_list;
	     table_list= table_list->next, tablenr++)
unknown's avatar
unknown committed
290
	{
291 292 293 294 295 296 297 298 299
	  if (table_list->shared)
	  {
	    /*
	      review notes: Check it carefully. I still can't understand
	      why I should not touch table->used_keys. For my point of
	      view we should do here same procedura as it was done by
	      setup_table
	    */
	    DBUG_PRINT("SUBS", ("shared %s", table_list->real_name));
unknown's avatar
unknown committed
300
	    setup_table_map(table_list->table, table_list, tablenr);
301
	  }
unknown's avatar
unknown committed
302 303 304
	}
	res= sl->join->optimize();
      }
unknown's avatar
unknown committed
305 306
      if (!res)
      {
unknown's avatar
unknown committed
307
	records_at_start= table->file->records;
unknown's avatar
unknown committed
308 309
	sl->join->exec();
	res= sl->join->error;
unknown's avatar
unknown committed
310 311 312 313 314
	if (!res && union_result->flush())
	{
	  thd->lex.current_select= lex_select_save;
	  DBUG_RETURN(1);
	}
unknown's avatar
unknown committed
315
      }
unknown's avatar
unknown committed
316
      if (res)
unknown's avatar
unknown committed
317
      {
318
	thd->lex.current_select= lex_select_save;
unknown's avatar
unknown committed
319
	DBUG_RETURN(res);
unknown's avatar
unknown committed
320
      }
unknown's avatar
unknown committed
321 322 323 324 325 326 327 328
      if (found_rows_for_union & sl->options)
      {
	/*
	  This is a union without braces. Remember the number of rows that could
	  also have been part of the result set.
	  We get this from the difference of between total number of possible
	  rows and actual rows added to the temporary table.
	*/
unknown's avatar
unknown committed
329 330
	add_rows+= (ha_rows) (thd->limit_found_rows - (ulonglong)
			      ((table->file->records -  records_at_start)));
unknown's avatar
unknown committed
331
      }
unknown's avatar
unknown committed
332
    }
unknown's avatar
unknown committed
333
  }
unknown's avatar
unknown committed
334
  optimized= 1;
335 336

  /* Send result to 'result' */
337

unknown's avatar
unknown committed
338

339
  res= -1;
unknown's avatar
unknown committed
340
  {
unknown's avatar
unknown committed
341 342
    List<Item_func_match> empty_list;
    empty_list.empty();
unknown's avatar
unknown committed
343

344
    if (!thd->is_fatal_error)			// Check if EOM
345
    {
unknown's avatar
unknown committed
346
      ulong options= thd->options;
unknown's avatar
unknown committed
347
      thd->lex.current_select= fake_select_lex;
348 349 350
      if (select_cursor->braces)
      {
	offset_limit_cnt= global_parameters->offset_limit;
unknown's avatar
unknown committed
351 352
	select_limit_cnt= global_parameters->select_limit +
	  global_parameters->offset_limit;
353 354 355
	if (select_limit_cnt < global_parameters->select_limit)
	  select_limit_cnt= HA_POS_ERROR;		// no limit
      }
unknown's avatar
unknown committed
356
      if (select_limit_cnt == HA_POS_ERROR)
unknown's avatar
unknown committed
357
	options&= ~OPTION_FOUND_ROWS;
358
      else if (found_rows_for_union && !describe)
unknown's avatar
unknown committed
359
	options|= OPTION_FOUND_ROWS;
unknown's avatar
unknown committed
360 361 362 363
      fake_select_lex->ftfunc_list= &empty_list;
      fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
					       (byte **)
					       &result_table_list.next);
364 365 366 367 368 369 370 371
      JOIN *join= fake_select_lex->join;
      if (!join)
      {
	/*
	  allocate JOIN for fake select only once (privent
	  mysql_select automatic allocation)
	*/
	fake_select_lex->join= new JOIN(thd, item_list, thd->options, result);
unknown's avatar
unknown committed
372 373 374 375 376
	/*
	  Fake st_select_lex should have item list for correctref_array
	  allocation.
	*/
	fake_select_lex->item_list= item_list;
377 378 379 380 381 382 383 384 385 386 387
      }
      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
388 389
      res= mysql_select(thd, &fake_select_lex->ref_pointer_array,
			&result_table_list,
390 391
			0, item_list, NULL,
			global_parameters->order_list.elements,
unknown's avatar
unknown committed
392
			(ORDER*)global_parameters->order_list.first,
393
			(ORDER*) NULL, NULL, (ORDER*) NULL,
unknown's avatar
unknown committed
394
			options | SELECT_NO_UNLOCK,
unknown's avatar
merge  
unknown committed
395
			result, this, fake_select_lex, 0);
unknown's avatar
unknown committed
396 397
      if (!res)
	thd->limit_found_rows = (ulonglong)table->file->records + add_rows;
398 399 400 401
      /*
	Mark for slow query log if any of the union parts didn't use
	indexes efficiently
      */
402
    }
unknown's avatar
unknown committed
403
  }
404
  thd->lex.current_select= lex_select_save;
unknown's avatar
unknown committed
405 406 407
  DBUG_RETURN(res);
}

408

unknown's avatar
unknown committed
409
int st_select_lex_unit::cleanup()
unknown's avatar
unknown committed
410
{
411
  int error= 0;
412
  DBUG_ENTER("st_select_lex_unit::cleanup");
413

unknown's avatar
unknown committed
414 415 416
  if (union_result)
  {
    delete union_result;
417 418
    if (table)
      free_tmp_table(thd, table);
unknown's avatar
unknown committed
419 420
    table= 0; // Safety
  }
421
  JOIN *join;
422
  for (SELECT_LEX *sl= first_select_in_union(); sl; sl= sl->next_select())
unknown's avatar
unknown committed
423
  {
424 425
    if ((join= sl->join))
    {
426
      error|= sl->join->cleanup();
427 428
      delete join;
    }
unknown's avatar
unknown committed
429
  }
430 431 432 433 434 435 436
  if (fake_select_lex && (join= fake_select_lex->join))
  {
    join->tables_list= 0;
    join->tables= 0;
    error|= join->cleanup();
    delete join;
  }
437
  DBUG_RETURN(error);
unknown's avatar
unknown committed
438
}