sql_select.h 17 KB
Newer Older
unknown's avatar
unknown committed
1
/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
unknown's avatar
unknown committed
2

unknown's avatar
unknown committed
3 4 5 6
   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.
unknown's avatar
unknown committed
7

unknown's avatar
unknown committed
8 9 10 11
   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.
unknown's avatar
unknown committed
12

unknown's avatar
unknown committed
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
   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 */


/* classes to use when handling where clause */

#ifdef __GNUC__
#pragma interface			/* gcc class implementation */
#endif

#include "procedure.h"
#include <myisam.h>

typedef struct keyuse_t {
  TABLE *table;
  Item	*val;				/* or value if no field */
  table_map used_tables;
31
  uint	key, keypart, optimize;
32 33
  key_part_map keypart_map;
  ha_rows      ref_table_rows;
34 35 36 37 38
  /* 
    If true, the comparison this value was created from will not be
    satisfied if val has NULL 'value'.
  */
  bool null_rejecting;
unknown's avatar
unknown committed
39 40 41 42 43 44 45 46 47 48 49 50 51 52
} KEYUSE;

class store_key;

typedef struct st_table_ref
{
  bool		key_err;
  uint          key_parts;                // num of ...
  uint          key_length;               // length of key_buff
  int           key;                      // key no
  byte          *key_buff;                // value to look for with key
  byte          *key_buff2;               // key_buff+key_length
  store_key     **key_copy;               //
  Item          **items;                  // val()'s for each keypart
53 54 55 56 57
  /*
    (null_rejecting & (1<<i)) means the condition is '=' and no matching
    rows will be produced if items[i] IS NULL (see add_not_null_conds())
  */
  key_part_map  null_rejecting;
unknown's avatar
unknown committed
58
  table_map	depend_map;		  // Table depends on these tables.
59 60
  byte          *null_ref_key;		  // null byte position in the key_buf.
  					  // used for REF_OR_NULL optimization.
unknown's avatar
unknown committed
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
} TABLE_REF;

/*
** CACHE_FIELD and JOIN_CACHE is used on full join to cache records in outer
** table
*/


typedef struct st_cache_field {
  char *str;
  uint length,blob_length;
  Field_blob *blob_field;
  bool strip;
} CACHE_FIELD;


typedef struct st_join_cache {
  uchar *buff,*pos,*end;
  uint records,record_nr,ptr_record,fields,length,blobs;
  CACHE_FIELD *field,**blob_ptr;
  SQL_SELECT *select;
} JOIN_CACHE;


/*
** The structs which holds the join connections and join states
*/
enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF,
89
		 JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL,
90
		 JT_UNIQUE_SUBQUERY, JT_INDEX_SUBQUERY, JT_INDEX_MERGE};
unknown's avatar
unknown committed
91 92 93

class JOIN;

94 95 96 97 98 99 100 101 102
enum enum_nested_loop_state
{
  NESTED_LOOP_KILLED= -2, NESTED_LOOP_ERROR= -1,
  NESTED_LOOP_OK= 0, NESTED_LOOP_NO_MORE_ROWS= 1,
  NESTED_LOOP_QUERY_LIMIT= 3, NESTED_LOOP_CURSOR_LIMIT= 4
};

typedef enum_nested_loop_state
(*Next_select_func)(JOIN *, struct st_join_table *, bool);
103 104 105
typedef int (*Read_record_func)(struct st_join_table *tab);


unknown's avatar
unknown committed
106 107
typedef struct st_join_table {
  TABLE		*table;
unknown's avatar
unknown committed
108 109 110
  KEYUSE	*keyuse;			/* pointer to first used key */
  SQL_SELECT	*select;
  COND		*select_cond;
unknown's avatar
unknown committed
111
  QUICK_SELECT_I *quick;
unknown's avatar
unknown committed
112 113
  Item	       **on_expr_ref;   /* pointer to the associated on expression   */
  COND_EQUAL    *cond_equal;    /* multiple equalities for the on expression */
114 115 116 117 118 119
  st_join_table *first_inner;   /* first inner table for including outerjoin */
  bool           found;         /* true after all matches or null complement */
  bool           not_null_compl;/* true before null complement is added      */
  st_join_table *last_inner;    /* last table table for embedding outer join */
  st_join_table *first_upper;  /* first inner table for embedding outer join */
  st_join_table *first_unmatched; /* used for optimization purposes only     */
unknown's avatar
unknown committed
120
  const char	*info;
121 122
  Read_record_func read_first_record;
  Next_select_func next_select;
unknown's avatar
unknown committed
123
  READ_RECORD	read_record;
unknown's avatar
unknown committed
124
  double	worst_seeks;
unknown's avatar
unknown committed
125 126 127
  key_map	const_keys;			/* Keys with constant part */
  key_map	checked_keys;			/* Keys checked in find_best */
  key_map	needed_reg;
128
  key_map       keys;                           /* all keys with can be used */
unknown's avatar
unknown committed
129 130 131 132 133
  ha_rows	records,found_records,read_time;
  table_map	dependent,key_dependent;
  uint		use_quick,index;
  uint		status;				// Save status for cache
  uint		used_fields,used_fieldlength,used_blobs;
unknown's avatar
unknown committed
134 135
  enum join_type type;
  bool		cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
unknown's avatar
unknown committed
136
  TABLE_REF	ref;
unknown's avatar
unknown committed
137
  JOIN_CACHE	cache;
138
  JOIN		*join;
139 140

  void cleanup();
unknown's avatar
unknown committed
141 142 143
} JOIN_TAB;


144 145
typedef struct st_position			/* Used in find_best */
{
unknown's avatar
unknown committed
146
  double records_read;
147
  double read_time;
unknown's avatar
unknown committed
148 149 150 151
  JOIN_TAB *table;
  KEYUSE *key;
} POSITION;

152 153 154 155
typedef struct st_rollup
{
  enum State { STATE_NONE, STATE_INITED, STATE_READY };
  State state;
unknown's avatar
unknown committed
156
  Item_null_result **null_items;
157 158 159 160
  Item ***ref_pointer_arrays;
  List<Item> *fields;
} ROLLUP;

unknown's avatar
unknown committed
161

162 163
class JOIN :public Sql_alloc
{
unknown's avatar
unknown committed
164
 public:
165 166 167
  JOIN_TAB *join_tab,**best_ref;
  JOIN_TAB **map2table;    // mapping between table indexes and JOIN_TABs
  JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
unknown's avatar
unknown committed
168 169 170 171
  TABLE    **table,**all_tables,*sort_by_table;
  uint	   tables,const_tables;
  uint	   send_group_parts;
  bool	   sort_and_group,first_record,full_join,group, no_field_update;
172
  bool	   do_send_rows;
173 174 175 176 177
  /*
    TRUE when we want to resume nested loop iterations when
    fetching data from a cursor
  */
  bool     resume_nested_loop;
178
  table_map const_table_map,found_const_table_map,outer_join;
unknown's avatar
unknown committed
179
  ha_rows  send_records,found_records,examined_rows,row_limit, select_limit;
180 181 182 183 184 185 186 187 188 189
  /*
    Used to fetch no more than given amount of rows per one 
    fetch operation of server side cursor.
    The value is checked in end_send and end_send_group in fashion, similar
    to offset_limit_cnt:
      - fetch_limit= HA_POS_ERROR if there is no cursor.
      - when we open a cursor, we set fetch_limit to 0,
      - on each fetch iteration we add num_rows to fetch to fetch_limit
  */
  ha_rows  fetch_limit;
unknown's avatar
unknown committed
190 191 192
  POSITION positions[MAX_TABLES+1],best_positions[MAX_TABLES+1];
  double   best_read;
  List<Item> *fields;
193
  List<Item_buff> group_fields, group_fields_cache;
unknown's avatar
unknown committed
194
  TABLE    *tmp_table;
195 196
  // used to store 2 possible tmp table of SELECT
  TABLE    *exec_tmp_table1, *exec_tmp_table2;
unknown's avatar
unknown committed
197
  THD	   *thd;
198
  Item_sum  **sum_funcs, ***sum_funcs_end;
199 200
  /* second copy of sumfuncs (for queries with 2 temporary tables */
  Item_sum  **sum_funcs2, ***sum_funcs_end2;
unknown's avatar
unknown committed
201 202
  Procedure *procedure;
  Item	    *having;
unknown's avatar
unknown committed
203 204
  Item      *tmp_having; // To store having when processed temporary table
  Item      *having_history; // Store having for explain
unknown's avatar
unknown committed
205 206 207 208
  uint	    select_options;
  select_result *result;
  TMP_TABLE_PARAM tmp_table_param;
  MYSQL_LOCK *lock;
209 210
  // unit structure (with global parameters) for this select
  SELECT_LEX_UNIT *unit;
211 212
  // select that processed
  SELECT_LEX *select_lex;
213 214
  
  JOIN *tmp_join; // copy of this JOIN to be used with temporary tables
215
  ROLLUP rollup;				// Used with rollup
216

unknown's avatar
unknown committed
217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233
  bool select_distinct;				// Set if SELECT DISTINCT

  /*
    simple_xxxxx is set if ORDER/GROUP BY doesn't include any references
    to other tables than the first non-constant table in the JOIN.
    It's also set if ORDER/GROUP BY is empty.
  */
  bool simple_order, simple_group;
  /*
    Is set only in case if we have a GROUP BY clause
    and no ORDER BY after constant elimination of 'order'.
  */
  bool no_order;
  /* Is set if we have a GROUP BY and we have ORDER BY on a constant. */
  bool          skip_sort_order;

  bool need_tmp, hidden_group_fields, buffer_result;
234 235
  DYNAMIC_ARRAY keyuse;
  Item::cond_result cond_value;
236 237 238 239 240
  List<Item> all_fields; // to store all fields that used in query
  //Above list changed to use temporary table
  List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3;
  //Part, shared with list above, emulate following list
  List<Item> tmp_fields_list1, tmp_fields_list2, tmp_fields_list3;
241
  List<Item> &fields_list; // hold field list passed to mysql_select
242 243 244 245
  int error;

  ORDER *order, *group_list, *proc_param; //hold parameters of mysql_select
  COND *conds;                            // ---"---
unknown's avatar
unknown committed
246
  Item *conds_history;                    // store WHERE for explain
247 248
  TABLE_LIST *tables_list;           //hold 'tables' parameter of mysql_select
  List<TABLE_LIST> *join_list;       // list of joined tables in reverse order
249
  COND_EQUAL *cond_equal;
250
  SQL_SELECT *select;                //created in optimisation phase
251
  JOIN_TAB *return_tab;              //used only for outer joins
252 253
  Item **ref_pointer_array; //used pointer reference for this select
  // Copy of above to be used with different lists
254
  Item **items0, **items1, **items2, **items3, **current_ref_pointer_array;
255
  uint ref_pointer_array_size; // size of above in bytes
256
  const char *zero_result_cause; // not 0 if exec must return zero result
257
  
258 259
  bool union_part; // this subselect is part of union 
  bool optimized; // flag to avoid double optimization in EXPLAIN
260

261
  JOIN(THD *thd_arg, List<Item> &fields_arg, ulong select_options_arg,
262
       select_result *result_arg)
263
    :fields_list(fields_arg)
264
  {
265
    init(thd_arg, fields_arg, select_options_arg, result_arg);
266 267
  }
  
268
  void init(THD *thd_arg, List<Item> &fields_arg, ulong select_options_arg,
269 270
       select_result *result_arg)
  {
271
    join_tab= join_tab_save= 0;
272 273 274
    table= 0;
    tables= 0;
    const_tables= 0;
275
    join_list= 0;
276 277 278
    sort_and_group= 0;
    first_record= 0;
    do_send_rows= 1;
279
    resume_nested_loop= FALSE;
280 281
    send_records= 0;
    found_records= 0;
282
    fetch_limit= HA_POS_ERROR;
283 284 285 286
    examined_rows= 0;
    exec_tmp_table1= 0;
    exec_tmp_table2= 0;
    thd= thd_arg;
unknown's avatar
merge  
unknown committed
287
    sum_funcs= sum_funcs2= 0;
288
    procedure= 0;
unknown's avatar
unknown committed
289
    having= tmp_having= having_history= 0;
290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305
    select_options= select_options_arg;
    result= result_arg;
    lock= thd_arg->lock;
    select_lex= 0; //for safety
    tmp_join= 0;
    select_distinct= test(select_options & SELECT_DISTINCT);
    no_order= 0;
    simple_order= 0;
    simple_group= 0;
    skip_sort_order= 0;
    need_tmp= 0;
    hidden_group_fields= 0; /*safety*/
    buffer_result= test(select_options & OPTION_BUFFER_RESULT) &&
      !test(select_options & OPTION_FOUND_ROWS);
    error= 0;
    select= 0;
306
    return_tab= 0;
307 308 309 310
    ref_pointer_array= items0= items1= items2= items3= 0;
    ref_pointer_array_size= 0;
    zero_result_cause= 0;
    optimized= 0;
311
    cond_equal= 0;
312

unknown's avatar
unknown committed
313
    all_fields= fields_arg;
314
    fields_list= fields_arg;
315 316 317
    bzero((char*) &keyuse,sizeof(keyuse));
    tmp_table_param.copy_field=0;
    tmp_table_param.end_write_records= HA_POS_ERROR;
318
    rollup.state= ROLLUP::STATE_NONE;
319
  }
320

321 322 323
  int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num,
	      COND *conds, uint og_num, ORDER *order, ORDER *group,
	      Item *having, ORDER *proc_param, SELECT_LEX *select,
324
	      SELECT_LEX_UNIT *unit);
325
  int optimize();
326
  int reinit();
327
  void exec();
328
  int cleanup();
329
  void restore_tmp();
330 331
  bool alloc_func_list();
  bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields,
332
			  bool before_group_by, bool recompute= FALSE);
333

334 335 336 337 338
  inline void set_items_ref_array(Item **ptr)
  {
    memcpy((char*) ref_pointer_array, (char*) ptr, ref_pointer_array_size);
    current_ref_pointer_array= ptr;
  }
339 340 341 342
  inline void init_items_ref_array()
  {
    items0= ref_pointer_array + all_fields.elements;
    memcpy(items0, ref_pointer_array, ref_pointer_array_size);
343
    current_ref_pointer_array= items0;
344
  }
345 346 347 348

  bool rollup_init();
  bool rollup_make_fields(List<Item> &all_fields, List<Item> &fields,
			  Item_sum ***func);
349
  int rollup_send_data(uint idx);
unknown's avatar
unknown committed
350
  int rollup_write_data(uint idx, TABLE *table);
351
  bool test_in_subselect(Item **where);
352
  void join_free(bool full);
353
  void clear();
354
  bool save_join_tab();
355 356 357 358 359
  bool send_row_on_empty_set()
  {
    return (do_send_rows && tmp_table_param.sum_func_count != 0 &&
	    !group_list);
  }
unknown's avatar
unknown committed
360
  bool change_result(select_result *result);
unknown's avatar
unknown committed
361 362 363
};


364 365 366 367 368 369 370 371 372 373 374 375 376 377
/*
  Server-side cursor (now stands only for basic read-only cursor)
  See class implementation in sql_select.cc
*/

class Cursor: public Sql_alloc, public Item_arena
{
  JOIN *join;
  SELECT_LEX_UNIT *unit;

  TABLE *open_tables;
  MYSQL_LOCK *lock;
  TABLE *derived_tables;
  /* List of items created during execution */
378
  query_id_t query_id;
379 380 381 382 383 384 385 386 387 388 389 390
public:
  select_send result;

  /* Temporary implementation as now we replace THD state by value */
  /* Save THD state into cursor */
  void init_from_thd(THD *thd);
  /* Restore THD from cursor to continue cursor execution */
  void init_thd(THD *thd);
  /* bzero cursor state in THD */
  void reset_thd(THD *thd);

  int open(JOIN *join);
391
  void fetch(ulong num_rows);
392 393 394 395 396
  void reset() { join= 0; }
  bool is_open() const { return join != 0; }
  void close();

  void set_unit(SELECT_LEX_UNIT *unit_arg) { unit= unit_arg; }
397
  Cursor() :Item_arena(TRUE), join(0), unit(0) {}
398 399 400 401
  ~Cursor();
};


unknown's avatar
unknown committed
402 403 404 405 406 407 408 409 410 411 412
typedef struct st_select_check {
  uint const_ref,reg_ref;
} SELECT_CHECK;

extern const char *join_type_str[];
void TEST_join(JOIN *join);

/* Extern functions in sql_select.cc */
bool store_val_in_field(Field *field,Item *val);
TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
			ORDER *group, bool distinct, bool save_sum_fields,
413 414
			ulong select_options, ha_rows rows_limit,
			char* alias);
415
TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list);
unknown's avatar
unknown committed
416
void free_tmp_table(THD *thd, TABLE *entry);
unknown's avatar
unknown committed
417 418
void count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
		       bool reset_with_sum_func);
419 420 421 422
bool setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param,
		       Item **ref_pointer_array,
		       List<Item> &new_list1, List<Item> &new_list2,
		       uint elements, List<Item> &fields);
unknown's avatar
unknown committed
423
void copy_fields(TMP_TABLE_PARAM *param);
unknown's avatar
unknown committed
424
void copy_funcs(Item **func_ptr);
unknown's avatar
unknown committed
425 426
bool create_myisam_from_heap(THD *thd, TABLE *table, TMP_TABLE_PARAM *param,
			     int error, bool ignore_last_dupp_error);
unknown's avatar
unknown committed
427
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
428
Field* create_tmp_field_from_field(THD *thd, Field* org_field,
unknown's avatar
unknown committed
429 430 431
                                   const char *name, TABLE *table,
                                   Item_field *item, uint convert_blob_length);
                                                                      
unknown's avatar
unknown committed
432
/* functions from opt_sum.cc */
433
bool simple_pred(Item_func *func_item, Item **args, bool *inv_order);
unknown's avatar
unknown committed
434 435
int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds);

436
/* from sql_delete.cc, used by opt_range.cc */
437
extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b);
438

unknown's avatar
unknown committed
439 440 441 442 443 444 445 446 447
/* class to copying an field/item to a key struct */

class store_key :public Sql_alloc
{
 protected:
  Field *to_field;				// Store data here
  char *null_ptr;
  char err;
 public:
unknown's avatar
unknown committed
448
  store_key(THD *thd, Field *field_arg, char *ptr, char *null, uint length)
unknown's avatar
unknown committed
449 450 451
    :null_ptr(null),err(0)
  {
    if (field_arg->type() == FIELD_TYPE_BLOB)
452 453 454
    {
      /* Key segments are always packed with a 2 byte length prefix */
      to_field=new Field_varstring(ptr, length, 2, (uchar*) null, 1, 
unknown's avatar
unknown committed
455
				   Field::NONE, field_arg->field_name,
456
				   field_arg->table, field_arg->charset());
457
    }
unknown's avatar
unknown committed
458
    else
unknown's avatar
unknown committed
459 460
      to_field=field_arg->new_key_field(thd->mem_root, field_arg->table,
                                        ptr, (uchar*) null, 1);
unknown's avatar
unknown committed
461 462 463 464 465 466 467 468 469 470 471 472
  }
  virtual ~store_key() {}			/* Not actually needed */
  virtual bool copy()=0;
  virtual const char *name() const=0;
};


class store_key_field: public store_key
{
  Copy_field copy_field;
  const char *field_name;
 public:
unknown's avatar
unknown committed
473
  store_key_field(THD *thd, Field *to_field_arg, char *ptr, char *null_ptr_arg,
unknown's avatar
unknown committed
474
		  uint length, Field *from_field, const char *name_arg)
unknown's avatar
unknown committed
475
    :store_key(thd, to_field_arg,ptr,
unknown's avatar
unknown committed
476 477 478 479 480 481 482 483
	       null_ptr_arg ? null_ptr_arg : from_field->maybe_null() ? &err
	       : NullS,length), field_name(name_arg)
  {
    if (to_field)
    {
      copy_field.set(to_field,from_field,0);
    }
  }
484 485 486 487 488 489
  bool copy()
  {
    copy_field.do_copy(&copy_field);
    return err != 0;
  }
  const char *name() const { return field_name; }
unknown's avatar
unknown committed
490 491 492 493 494 495 496 497
};


class store_key_item :public store_key
{
 protected:
  Item *item;
public:
unknown's avatar
unknown committed
498
  store_key_item(THD *thd, Field *to_field_arg, char *ptr, char *null_ptr_arg,
unknown's avatar
unknown committed
499
		 uint length, Item *item_arg)
unknown's avatar
unknown committed
500
    :store_key(thd, to_field_arg,ptr,
unknown's avatar
unknown committed
501 502 503 504 505
	       null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ?
	       &err : NullS, length), item(item_arg)
  {}
  bool copy()
  {
506
    return item->save_in_field_no_warnings(to_field, 1) || err != 0;
unknown's avatar
unknown committed
507 508 509 510 511 512 513 514 515
  }
  const char *name() const { return "func"; }
};


class store_key_const_item :public store_key_item
{
  bool inited;
public:
unknown's avatar
unknown committed
516
  store_key_const_item(THD *thd, Field *to_field_arg, char *ptr,
unknown's avatar
unknown committed
517 518
		       char *null_ptr_arg, uint length,
		       Item *item_arg)
unknown's avatar
unknown committed
519
    :store_key_item(thd, to_field_arg,ptr,
unknown's avatar
unknown committed
520 521 522 523 524 525 526 527 528
		    null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ?
		    &err : NullS, length, item_arg), inited(0)
  {
  }
  bool copy()
  {
    if (!inited)
    {
      inited=1;
529 530
      if (item->save_in_field(to_field, 1))
	err= 1;
unknown's avatar
unknown committed
531 532 533 534 535 536
    }
    return err != 0;
  }
  const char *name() const { return "const"; }
};

unknown's avatar
unknown committed
537
bool cp_buffer_from_ref(THD *thd, TABLE_REF *ref);
unknown's avatar
unknown committed
538
bool error_if_full_join(JOIN *join);
539
int report_error(TABLE *table, int error);
540
int safe_index_read(JOIN_TAB *tab);