sql_union.cc 11.6 KB
Newer Older
1
/* Copyright (C) 2000-2003 MySQL AB
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi 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, bool tables_and_fields_initied)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
29 30 31
{
  DBUG_ENTER("mysql_union");
  int res= 0;
32
  if (!(res= unit->prepare(thd, result, tables_and_fields_initied)))
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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)
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
44
  :table(table_par), not_describe(0)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
62
  if (not_describe && list.elements != table->fields)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
80
  if (thd->net.report_error || write_record(table,&info))
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua 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,
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
86
				  info.last_errno, 1))
87 88 89
	return 1;
    }
    else
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
90 91 92 93 94
      return 1;
  }
  return 0;
}

95

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
96 97 98 99
bool select_union::send_eof()
{
  return 0;
}
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
100

101

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
102
bool select_union::flush()
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
103
{
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
104 105 106 107
  int error;
  if ((error=table->file->extra(HA_EXTRA_NO_CACHE)))
  {
    table->file->print_error(error,MYF(0));
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
108
    ::send_error(thd);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
109 110 111 112 113
    return 1;
  }
  return 0;
}

114 115

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

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

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

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

  if (!(union_result=new select_union(table)))
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
184
    goto err;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
185

monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
186
  union_result->not_describe=1;
187
  union_result->tmp_table_param=tmp_table_param;
188

189
  for (sl= select_cursor; sl; sl= sl->next_select())
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
190
  {
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
    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;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
216
  }
217

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

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

232
  DBUG_RETURN(res || thd->is_fatal_error ? 1 : 0);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
233
err:
234
  thd->lex.current_select= lex_select_save;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
235
  DBUG_RETURN(-1);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
236 237
}

238

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
239 240
int st_select_lex_unit::exec()
{
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
241
  SELECT_LEX *lex_select_save= thd->lex.current_select;
242
  SELECT_LEX *select_cursor=first_select_in_union();
243
  ha_rows add_rows=0;
244 245
  DBUG_ENTER("st_select_lex_unit::exec");

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

263
      if (optimized)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
264
	res= sl->join->reinit();
265 266 267 268 269 270
      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
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
271
	if (select_limit_cnt == HA_POS_ERROR || sl->braces)
272
	  sl->options&= ~OPTION_FOUND_ROWS;
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
273
	else 
274
	{
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
275 276 277 278 279
	  /*
	    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;
280
	}
281
	
282 283 284 285 286 287 288 289 290
	/* 
	   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++)
291
	{
292 293 294 295 296 297 298 299 300
	  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));
301
	    setup_table_map(table_list->table, table_list, tablenr);
302
	  }
303 304 305
	}
	res= sl->join->optimize();
      }
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
306 307
      if (!res)
      {
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
308
	records_at_start= table->file->records;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
309 310
	sl->join->exec();
	res= sl->join->error;
311 312 313 314 315
	if (!res && union_result->flush())
	{
	  thd->lex.current_select= lex_select_save;
	  DBUG_RETURN(1);
	}
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
316
      }
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
317
      if (res)
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
318
      {
319
	thd->lex.current_select= lex_select_save;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
320
	DBUG_RETURN(res);
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
321
      }
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
322 323 324 325 326 327 328 329 330 331 332
      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.
	*/
	add_rows+= (ulonglong) (thd->limit_found_rows - (table->file->records -
							 records_at_start));
      }
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
333
    }
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
334
  }
335
  optimized= 1;
336 337

  /* Send result to 'result' */
338

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
339

340
  res= -1;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
341
  {
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
342 343
    List<Item_func_match> empty_list;
    empty_list.empty();
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
344

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

409

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
410
int st_select_lex_unit::cleanup()
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
411
{
412
  int error= 0;
413
  DBUG_ENTER("st_select_lex_unit::cleanup");
414

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