← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to drizzled/sql_select.cc

  • Committer: Brian Aker
  • Date: 2011-02-14 05:47:07 UTC
  • mto: This revision was merged to the branch mainline in revision 2167.
  • Revision ID: brian@tangent.org-20110214054707-61nsqgg1g4w1zhx1
Merge in all changes for current_session, etc.

Show diffs side-by-side

added added

removed removed

Lines of Context:
drizzled/sql_select.cc
17
17
  @file
18
18
 
19
19
  @brief
20
 
  mysql_select and join optimization
 
20
  select_query and join optimization
21
21
 
22
22
  @defgroup Query_Optimizer  Query Optimizer
23
23
  @{
29
29
#include <algorithm>
30
30
#include <vector>
31
31
 
32
 
#include "drizzled/sql_select.h" /* include join.h */
33
 
 
34
 
#include "drizzled/error.h"
35
 
#include "drizzled/gettext.h"
36
 
#include "drizzled/util/test.h"
37
 
#include "drizzled/name_resolution_context_state.h"
38
 
#include "drizzled/nested_join.h"
39
 
#include "drizzled/probes.h"
40
 
#include "drizzled/show.h"
41
 
#include "drizzled/item/cache.h"
42
 
#include "drizzled/item/cmpfunc.h"
43
 
#include "drizzled/item/copy_string.h"
44
 
#include "drizzled/item/uint.h"
45
 
#include "drizzled/cached_item.h"
46
 
#include "drizzled/sql_base.h"
47
 
#include "drizzled/field/blob.h"
48
 
#include "drizzled/check_stack_overrun.h"
49
 
#include "drizzled/lock.h"
50
 
#include "drizzled/item/outer_ref.h"
51
 
#include "drizzled/index_hint.h"
52
 
#include "drizzled/records.h"
53
 
#include "drizzled/internal/iocache.h"
54
 
#include "drizzled/drizzled.h"
55
 
 
56
 
#include "drizzled/sql_union.h"
57
 
#include "drizzled/optimizer/key_field.h"
58
 
#include "drizzled/optimizer/position.h"
59
 
#include "drizzled/optimizer/sargable_param.h"
60
 
#include "drizzled/optimizer/key_use.h"
61
 
#include "drizzled/optimizer/range.h"
62
 
#include "drizzled/optimizer/quick_range_select.h"
63
 
#include "drizzled/optimizer/quick_ror_intersect_select.h"
64
 
 
65
 
#include "drizzled/filesort.h"
 
32
#include <drizzled/sql_select.h> /* include join.h */
 
33
 
 
34
#include <drizzled/error.h>
 
35
#include <drizzled/gettext.h>
 
36
#include <drizzled/util/test.h>
 
37
#include <drizzled/name_resolution_context_state.h>
 
38
#include <drizzled/nested_join.h>
 
39
#include <drizzled/probes.h>
 
40
#include <drizzled/show.h>
 
41
#include <drizzled/item/cache.h>
 
42
#include <drizzled/item/cmpfunc.h>
 
43
#include <drizzled/item/copy_string.h>
 
44
#include <drizzled/item/uint.h>
 
45
#include <drizzled/cached_item.h>
 
46
#include <drizzled/sql_base.h>
 
47
#include <drizzled/field/blob.h>
 
48
#include <drizzled/check_stack_overrun.h>
 
49
#include <drizzled/lock.h>
 
50
#include <drizzled/item/outer_ref.h>
 
51
#include <drizzled/index_hint.h>
 
52
#include <drizzled/records.h>
 
53
#include <drizzled/internal/iocache.h>
 
54
#include <drizzled/drizzled.h>
 
55
#include <drizzled/plugin/storage_engine.h>
 
56
 
 
57
#include <drizzled/sql_union.h>
 
58
#include <drizzled/optimizer/key_field.h>
 
59
#include <drizzled/optimizer/position.h>
 
60
#include <drizzled/optimizer/sargable_param.h>
 
61
#include <drizzled/optimizer/key_use.h>
 
62
#include <drizzled/optimizer/range.h>
 
63
#include <drizzled/optimizer/quick_range_select.h>
 
64
#include <drizzled/optimizer/quick_ror_intersect_select.h>
 
65
 
 
66
#include <drizzled/filesort.h>
 
67
#include <drizzled/sql_lex.h>
 
68
#include <drizzled/session.h>
 
69
#include <drizzled/sort_field.h>
 
70
#include <drizzled/select_result.h>
66
71
 
67
72
using namespace std;
68
73
 
78
83
static Item* part_of_refkey(Table *form,Field *field);
79
84
static bool cmp_buffer_with_ref(JoinTable *tab);
80
85
static void change_cond_ref_to_const(Session *session,
81
 
                                     vector<COND_CMP>& save_list,
 
86
                                     list<COND_CMP>& save_list,
82
87
                                     Item *and_father,
83
88
                                     Item *cond,
84
89
                                     Item *field,
131
136
    unit->set_limit(unit->global_parameters);
132
137
    session->session_marker= 0;
133
138
    /*
134
 
      'options' of mysql_select will be set in JOIN, as far as JOIN for
 
139
      'options' of select_query will be set in JOIN, as far as JOIN for
135
140
      every PS/SP execution new, we will not need reset this flag if
136
141
      setup_tables_done_option changed for next rexecution
137
142
    */
138
 
    res= mysql_select(session,
 
143
    res= select_query(session,
139
144
                      &select_lex->ref_pointer_array,
140
145
                      (TableList*) select_lex->table_list.first,
141
146
                      select_lex->with_wild,
270
275
 
271
276
/*****************************************************************************
272
277
  Check fields, find best join, do the select and output fields.
273
 
  mysql_select assumes that all tables are already opened
 
278
  select_query assumes that all tables are already opened
274
279
*****************************************************************************/
275
280
 
276
281
/*
350
355
  @retval
351
356
    true   an error
352
357
*/
353
 
bool mysql_select(Session *session,
 
358
bool select_query(Session *session,
354
359
                  Item ***rref_pointer_array,
355
360
                  TableList *tables, 
356
361
                  uint32_t wild_num, 
572
577
  sz= sizeof(optimizer::KeyField) *
573
578
      (((session->lex->current_select->cond_count+1)*2 +
574
579
        session->lex->current_select->between_count)*m+1);
575
 
  if (! (key_fields= (optimizer::KeyField*) session->alloc(sz)))
 
580
  if (! (key_fields= (optimizer::KeyField*) session->getMemRoot()->allocate(sz)))
576
581
    return true;
577
582
  and_level= 0;
578
583
  field= end= key_fields;
931
936
 
932
937
  /*
933
938
    we should restore old value of count_cuted_fields because
934
 
    store_val_in_field can be called from mysql_insert
 
939
    store_val_in_field can be called from insert_query
935
940
    with select_insert, which make count_cuted_fields= 1
936
941
   */
937
942
  enum_check_fields old_count_cuted_fields= session->count_cuted_fields;
1004
1009
  j->ref.key_length=length;
1005
1010
  j->ref.key=(int) key;
1006
1011
  if (!(j->ref.key_buff= (unsigned char*) session->calloc(ALIGN_SIZE(length)*2)) ||
1007
 
      !(j->ref.key_copy= (StoredKey**) session->alloc((sizeof(StoredKey*) *
 
1012
      !(j->ref.key_copy= (StoredKey**) session->getMemRoot()->allocate((sizeof(StoredKey*) *
1008
1013
               (keyparts+1)))) ||
1009
 
      !(j->ref.items=    (Item**) session->alloc(sizeof(Item*)*keyparts)) ||
1010
 
      !(j->ref.cond_guards= (bool**) session->alloc(sizeof(uint*)*keyparts)))
 
1014
      !(j->ref.items=    (Item**) session->getMemRoot()->allocate(sizeof(Item*)*keyparts)) ||
 
1015
      !(j->ref.cond_guards= (bool**) session->getMemRoot()->allocate(sizeof(uint*)*keyparts)))
1011
1016
  {
1012
1017
    return(true);
1013
1018
  }
2307
2312
  @param cond       condition whose multiple equalities are to be checked
2308
2313
  @param table      constant table that has been read
2309
2314
*/
2310
 
static void update_const_equal_items(COND *cond, JoinTable *tab)
 
2315
void update_const_equal_items(COND *cond, JoinTable *tab)
2311
2316
{
2312
2317
  if (!(cond->used_tables() & tab->table->map))
2313
2318
    return;
2364
2369
  and_level
2365
2370
*/
2366
2371
static void change_cond_ref_to_const(Session *session,
2367
 
                                     vector<COND_CMP>& save_list,
 
2372
                                     list<COND_CMP>& save_list,
2368
2373
                                     Item *and_father,
2369
2374
                                     Item *cond,
2370
2375
                                     Item *field,
2470
2475
}
2471
2476
 
2472
2477
static void propagate_cond_constants(Session *session, 
2473
 
                                     vector<COND_CMP>& save_list, 
 
2478
                                     list<COND_CMP>& save_list, 
2474
2479
                                     COND *and_father, 
2475
2480
                                     COND *cond)
2476
2481
{
2479
2484
    bool and_level= ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC;
2480
2485
    List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list());
2481
2486
    Item *item;
2482
 
    vector<COND_CMP> save;
 
2487
    list<COND_CMP> save;
2483
2488
    while ((item=li++))
2484
2489
    {
2485
2490
      propagate_cond_constants(session, save, and_level ? cond : item, item);
2487
2492
    if (and_level)
2488
2493
    {
2489
2494
      // Handle other found items
2490
 
      for (vector<COND_CMP>::iterator iter= save.begin(); iter != save.end(); ++iter)
 
2495
      for (list<COND_CMP>::iterator iter= save.begin(); iter != save.end(); ++iter)
2491
2496
      {
2492
 
        Item **args= iter->cmp_func->arguments();
 
2497
        Item **args= iter->second->arguments();
2493
2498
        if (not args[0]->const_item())
2494
2499
        {
2495
 
          change_cond_ref_to_const(session, save_list, iter->and_level,
2496
 
                                   iter->and_level, args[0], args[1] );
 
2500
          change_cond_ref_to_const(session, save, iter->first,
 
2501
                                   iter->first, args[0], args[1] );
2497
2502
        }
2498
2503
      }
2499
2504
    }
2606
2611
         position:
2607
2612
          1. join->cur_embedding_map - bitmap of pairs of brackets (aka nested
2608
2613
             joins) we've opened but didn't close.
2609
 
          2. {each nested_join_st structure not simplified away}->counter - number
 
2614
          2. {each NestedJoin class not simplified away}->counter - number
2610
2615
             of this nested join's children that have already been added to to
2611
2616
             the partial join order.
2612
2617
  @endverbatim
2684
2689
                             &join->cond_equal);
2685
2690
 
2686
2691
    /* change field = field to field = const for each found field = const */
2687
 
    vector<COND_CMP> temp;
 
2692
    list<COND_CMP> temp;
2688
2693
    propagate_cond_constants(session, temp, conds, conds);
2689
2694
    /*
2690
2695
      Remove all instances of item == item
3053
3058
    table->emptyRecord();
3054
3059
    if (table->group && join->tmp_table_param.sum_func_count &&
3055
3060
        table->getShare()->sizeKeys() && !table->cursor->inited)
3056
 
      table->cursor->startIndexScan(0, 0);
 
3061
    {
 
3062
      int tmp_error;
 
3063
      tmp_error= table->cursor->startIndexScan(0, 0);
 
3064
      if (tmp_error != 0)
 
3065
      {
 
3066
        table->print_error(tmp_error, MYF(0));
 
3067
        return -1;
 
3068
      }
 
3069
    }
3057
3070
  }
3058
3071
  /* Set up select_end */
3059
3072
  Next_select_func end_select= setup_end_select_func(join);
3359
3372
  return 0;
3360
3373
}
3361
3374
 
3362
 
int join_read_const_table(JoinTable *tab, optimizer::Position *pos)
3363
 
{
3364
 
  int error;
3365
 
  Table *table=tab->table;
3366
 
  table->const_table=1;
3367
 
  table->null_row=0;
3368
 
  table->status=STATUS_NO_RECORD;
3369
 
 
3370
 
  if (tab->type == AM_SYSTEM)
3371
 
  {
3372
 
    if ((error=join_read_system(tab)))
3373
 
    {                                           // Info for DESCRIBE
3374
 
      tab->info="const row not found";
3375
 
      /* Mark for EXPLAIN that the row was not found */
3376
 
      pos->setFanout(0.0);
3377
 
      pos->clearRefDependMap();
3378
 
      if (! table->maybe_null || error > 0)
3379
 
        return(error);
3380
 
    }
3381
 
  }
3382
 
  else
3383
 
  {
3384
 
    if (! table->key_read && 
3385
 
        table->covering_keys.test(tab->ref.key) && 
3386
 
        ! table->no_keyread &&
3387
 
        (int) table->reginfo.lock_type <= (int) TL_READ_WITH_SHARED_LOCKS)
3388
 
    {
3389
 
      table->key_read=1;
3390
 
      table->cursor->extra(HA_EXTRA_KEYREAD);
3391
 
      tab->index= tab->ref.key;
3392
 
    }
3393
 
    error=join_read_const(tab);
3394
 
    if (table->key_read)
3395
 
    {
3396
 
      table->key_read=0;
3397
 
      table->cursor->extra(HA_EXTRA_NO_KEYREAD);
3398
 
    }
3399
 
    if (error)
3400
 
    {
3401
 
      tab->info="unique row not found";
3402
 
      /* Mark for EXPLAIN that the row was not found */
3403
 
      pos->setFanout(0.0);
3404
 
      pos->clearRefDependMap();
3405
 
      if (!table->maybe_null || error > 0)
3406
 
        return(error);
3407
 
    }
3408
 
  }
3409
 
  if (*tab->on_expr_ref && !table->null_row)
3410
 
  {
3411
 
    if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0)))
3412
 
      table->mark_as_null_row();
3413
 
  }
3414
 
  if (!table->null_row)
3415
 
    table->maybe_null=0;
3416
 
 
3417
 
  /* Check appearance of new constant items in Item_equal objects */
3418
 
  Join *join= tab->join;
3419
 
  if (join->conds)
3420
 
    update_const_equal_items(join->conds, tab);
3421
 
  TableList *tbl;
3422
 
  for (tbl= join->select_lex->leaf_tables; tbl; tbl= tbl->next_leaf)
3423
 
  {
3424
 
    TableList *embedded;
3425
 
    TableList *embedding= tbl;
3426
 
    do
3427
 
    {
3428
 
      embedded= embedding;
3429
 
      if (embedded->on_expr)
3430
 
         update_const_equal_items(embedded->on_expr, tab);
3431
 
      embedding= embedded->getEmbedding();
3432
 
    }
3433
 
    while (embedding &&
3434
 
           embedding->getNestedJoin()->join_list.head() == embedded);
3435
 
  }
3436
 
 
3437
 
  return(0);
3438
 
}
3439
 
 
3440
 
int join_read_system(JoinTable *tab)
3441
 
{
3442
 
  Table *table= tab->table;
3443
 
  int error;
3444
 
  if (table->status & STATUS_GARBAGE)           // If first read
3445
 
  {
3446
 
    if ((error=table->cursor->read_first_row(table->getInsertRecord(),
3447
 
                                           table->getShare()->getPrimaryKey())))
3448
 
    {
3449
 
      if (error != HA_ERR_END_OF_FILE)
3450
 
        return table->report_error(error);
3451
 
      tab->table->mark_as_null_row();
3452
 
      table->emptyRecord();                     // Make empty record
3453
 
      return -1;
3454
 
    }
3455
 
    table->storeRecord();
3456
 
  }
3457
 
  else if (!table->status)                      // Only happens with left join
3458
 
    table->restoreRecord();                     // restore old record
3459
 
  table->null_row=0;
3460
 
  return table->status ? -1 : 0;
3461
 
}
3462
 
 
3463
3375
/**
3464
3376
  Read a (constant) table when there is at most one matching row.
3465
3377
 
3531
3443
 
3532
3444
  if (!table->cursor->inited)
3533
3445
  {
3534
 
    table->cursor->startIndexScan(tab->ref.key, tab->sorted);
 
3446
    error= table->cursor->startIndexScan(tab->ref.key, tab->sorted);
 
3447
    if (error != 0)
 
3448
    {
 
3449
      table->print_error(error, MYF(0));
 
3450
    }
3535
3451
  }
3536
3452
 
3537
3453
  /* TODO: Why don't we do "Late NULLs Filtering" here? */
3579
3495
 
3580
3496
  /* Initialize the index first */
3581
3497
  if (!table->cursor->inited)
3582
 
    table->cursor->startIndexScan(tab->ref.key, tab->sorted);
 
3498
  {
 
3499
    error= table->cursor->startIndexScan(tab->ref.key, tab->sorted);
 
3500
    if (error != 0)
 
3501
      return table->report_error(error);
 
3502
  }
3583
3503
 
3584
3504
  /* Perform "Late NULLs Filtering" (see internals manual for explanations) */
3585
3505
  for (uint32_t i= 0 ; i < tab->ref.key_parts ; i++)
3613
3533
  Table *table= tab->table;
3614
3534
 
3615
3535
  if (!table->cursor->inited)
3616
 
    table->cursor->startIndexScan(tab->ref.key, tab->sorted);
 
3536
  {
 
3537
    error= table->cursor->startIndexScan(tab->ref.key, tab->sorted);
 
3538
    if (error != 0)
 
3539
      return table->report_error(error);
 
3540
  }
3617
3541
  if (cp_buffer_from_ref(tab->join->session, &tab->ref))
3618
3542
    return -1;
3619
3543
  if ((error=table->cursor->index_read_last_map(table->getInsertRecord(),
3728
3652
  if (tab->select && tab->select->quick && tab->select->quick->reset())
3729
3653
    return 1;
3730
3654
 
3731
 
  tab->read_record.init_read_record(tab->join->session, tab->table, tab->select, 1, true);
 
3655
  if (tab->read_record.init_read_record(tab->join->session, tab->table, tab->select, 1, true))
 
3656
    return 1;
3732
3657
 
3733
3658
  return (*tab->read_record.read_record)(&tab->read_record);
3734
3659
}
3761
3686
  }
3762
3687
 
3763
3688
  if (!table->cursor->inited)
3764
 
    table->cursor->startIndexScan(tab->index, tab->sorted);
 
3689
  {
 
3690
    error= table->cursor->startIndexScan(tab->index, tab->sorted);
 
3691
    if (error != 0)
 
3692
    {
 
3693
      table->report_error(error);
 
3694
      return -1;
 
3695
    }
 
3696
  }
3765
3697
  if ((error=tab->table->cursor->index_first(tab->table->getInsertRecord())))
3766
3698
  {
3767
3699
    if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
3820
3752
  tab->read_record.index=tab->index;
3821
3753
  tab->read_record.record=table->getInsertRecord();
3822
3754
  if (!table->cursor->inited)
3823
 
    table->cursor->startIndexScan(tab->index, 1);
 
3755
  {
 
3756
    error= table->cursor->startIndexScan(tab->index, 1);
 
3757
    if (error != 0)
 
3758
      return table->report_error(error);
 
3759
  }
3824
3760
  if ((error= tab->table->cursor->index_last(tab->table->getInsertRecord())))
3825
3761
    return table->report_error(error);
3826
3762
 
3900
3836
              error=join->result->send_data(*join->fields) ? 1 : 0;
3901
3837
            join->send_records++;
3902
3838
          }
3903
 
          if (join->rollup.state != ROLLUP::STATE_NONE && error <= 0)
 
3839
          if (join->rollup.getState() != Rollup::STATE_NONE && error <= 0)
3904
3840
          {
3905
3841
            if (join->rollup_send_data((uint32_t) (idx+1)))
3906
3842
              error= 1;
3990
3926
            return NESTED_LOOP_ERROR;
3991
3927
          }
3992
3928
        }
3993
 
        if (join->rollup.state != ROLLUP::STATE_NONE)
 
3929
        if (join->rollup.getState() != Rollup::STATE_NONE)
3994
3930
        {
3995
3931
          if (join->rollup_write_data((uint32_t) (idx+1), table))
3996
3932
            return NESTED_LOOP_ERROR;
5099
5035
  org_record=(char*) (record=table->getInsertRecord())+offset;
5100
5036
  new_record=(char*) table->getUpdateRecord()+offset;
5101
5037
 
5102
 
  cursor->startTableScan(1);
 
5038
  if ((error= cursor->startTableScan(1)))
 
5039
    goto err;
 
5040
 
5103
5041
  error=cursor->rnd_next(record);
5104
5042
  for (;;)
5105
5043
  {
5216
5154
    return(1);
5217
5155
  }
5218
5156
 
5219
 
  cursor->startTableScan(1);
 
5157
  if ((error= cursor->startTableScan(1)))
 
5158
    goto err;
 
5159
 
5220
5160
  key_pos= &key_buffer[0];
5221
5161
  for (;;)
5222
5162
  {
5428
5368
    if (!count || count > fields.elements)
5429
5369
    {
5430
5370
      my_error(ER_BAD_FIELD_ERROR, MYF(0),
5431
 
               order_item->full_name(), session->where);
 
5371
               order_item->full_name(), session->where());
5432
5372
      return true;
5433
5373
    }
5434
5374
    order->item= ref_pointer_array + count - 1;
5505
5445
      push_warning_printf(session, DRIZZLE_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
5506
5446
                          ER(ER_NON_UNIQ_ERROR),
5507
5447
                          ((Item_ident*) order_item)->field_name,
5508
 
                          session->where);
 
5448
                          session->where());
5509
5449
    }
5510
5450
  }
5511
5451
 
5547
5487
                List<Item> &all_fields,
5548
5488
                Order *order)
5549
5489
{
5550
 
  session->where="order clause";
 
5490
  session->setWhere("order clause");
5551
5491
  for (; order; order=order->next)
5552
5492
  {
5553
5493
    if (find_order_in_list(session, ref_pointer_array, tables, order, fields,
5598
5538
 
5599
5539
  uint32_t org_fields=all_fields.elements;
5600
5540
 
5601
 
  session->where="group statement";
 
5541
  session->setWhere("group statement");
5602
5542
  for (ord= order; ord; ord= ord->next)
5603
5543
  {
5604
5544
    if (find_order_in_list(session, ref_pointer_array, tables, ord, fields,
5701
5641
  {
5702
5642
    if (order->in_field_list)
5703
5643
    {
5704
 
      Order *ord=(Order*) session->memdup((char*) order,sizeof(Order));
 
5644
      Order *ord=(Order*) session->getMemRoot()->duplicate((char*) order,sizeof(Order));
5705
5645
      if (!ord)
5706
5646
        return 0;
5707
5647
      *prev=ord;
6356
6296
{
6357
6297
  /* List is reversed => we should reverse it before using */
6358
6298
  List_iterator_fast<TableList> ti(*tables);
6359
 
  TableList **table= (TableList **)session->alloc(sizeof(TableList*) *
 
6299
  TableList **table= (TableList **)session->getMemRoot()->allocate(sizeof(TableList*) *
6360
6300
                                                tables->elements);
6361
6301
  if (table == 0)
6362
6302
    return;  // out of memory