23 #include <drizzled/sql_select.h>
24 #include <drizzled/nested_join.h>
25 #include <drizzled/item/cmpfunc.h>
26 #include <drizzled/table.h>
27 #include <drizzled/optimizer/key_field.h>
28 #include <drizzled/optimizer/key_use.h>
29 #include <drizzled/sql_lex.h>
30 #include <drizzled/item/subselect.h>
39 void optimizer::add_key_part(DYNAMIC_ARRAY *keyuse_array,
40 optimizer::KeyField *key_field)
42 Field *field= key_field->getField();
43 Table *form= field->getTable();
45 if (key_field->isEqualityCondition() &&
46 ! (key_field->getOptimizeFlags() & KEY_OPTIMIZE_EXISTS))
48 for (uint32_t key= 0; key < form->sizeKeys(); key++)
50 if (! (form->keys_in_use_for_query.test(key)))
53 uint32_t key_parts= (uint32_t) form->key_info[key].key_parts;
54 for (uint32_t part= 0; part < key_parts; part++)
56 if (field->eq(form->key_info[key].key_part[part].field))
58 optimizer::KeyUse keyuse(field->getTable(),
59 key_field->getValue(),
60 key_field->getValue()->used_tables(),
63 key_field->getOptimizeFlags() & KEY_OPTIMIZE_REF_OR_NULL,
66 key_field->rejectNullValues(),
67 key_field->getConditionalGuard());
68 keyuse_array->push_back(&keyuse);
75 void optimizer::add_key_fields_for_nj(Join *join,
76 TableList *nested_join_table,
77 optimizer::KeyField **end,
79 vector<optimizer::SargableParam> &sargables)
81 List<TableList>::iterator li(nested_join_table->getNestedJoin()->join_list.begin());
82 List<TableList>::iterator li2(nested_join_table->getNestedJoin()->join_list.begin());
83 bool have_another=
false;
86 assert(nested_join_table->getNestedJoin());
88 while ((table= li++) || (have_another && (li=li2, have_another=
false,
91 if (table->getNestedJoin())
98 li= List<TableList>::iterator(table->getNestedJoin()->join_list.begin());
101 add_key_fields_for_nj(join, table, end, and_level, sargables);
104 if (! table->on_expr)
105 tables|= table->table->map;
107 if (nested_join_table->on_expr)
112 nested_join_table->on_expr,
118 optimizer::KeyField *optimizer::merge_key_fields(optimizer::KeyField *start,
119 optimizer::KeyField *new_fields,
120 optimizer::KeyField *end,
123 if (start == new_fields)
125 if (new_fields == end)
128 optimizer::KeyField *first_free= new_fields;
131 for (; new_fields != end; new_fields++)
133 for (optimizer::KeyField *old= start; old != first_free; old++)
135 if (old->getField() == new_fields->getField())
149 if (! new_fields->getValue()->const_item())
155 if (old->getValue()->eq(new_fields->getValue(), old->getField()->binary()))
157 old->setLevel(and_level);
158 old->setOptimizeFlags(((old->getOptimizeFlags() &
159 new_fields->getOptimizeFlags() &
160 KEY_OPTIMIZE_EXISTS) |
161 ((old->getOptimizeFlags() |
162 new_fields->getOptimizeFlags()) &
163 KEY_OPTIMIZE_REF_OR_NULL)));
164 old->setRejectNullValues(old->rejectNullValues() &&
165 new_fields->rejectNullValues());
168 else if (old->isEqualityCondition() &&
169 new_fields->isEqualityCondition() &&
170 old->getValue()->eq_by_collation(new_fields->getValue(),
171 old->getField()->binary(),
172 old->getField()->charset()))
175 old->setLevel(and_level);
176 old->setOptimizeFlags(((old->getOptimizeFlags() &
177 new_fields->getOptimizeFlags() &
178 KEY_OPTIMIZE_EXISTS) |
179 ((old->getOptimizeFlags() |
180 new_fields->getOptimizeFlags()) &
181 KEY_OPTIMIZE_REF_OR_NULL)));
182 old->setRejectNullValues(old->rejectNullValues() &&
183 new_fields->rejectNullValues());
185 else if (old->isEqualityCondition() &&
186 new_fields->isEqualityCondition() &&
187 ((old->getValue()->const_item() &&
188 old->getValue()->is_null()) ||
189 new_fields->getValue()->is_null()))
192 old->setLevel(and_level);
193 old->setOptimizeFlags(KEY_OPTIMIZE_REF_OR_NULL);
198 if (! old->getValue()->used_tables() &&
199 old->getValue()->is_null())
201 old->setValue(new_fields->getValue());
204 old->setRejectNullValues(
false);
213 if (old == --first_free)
222 for (optimizer::KeyField *old= start; old != first_free;)
224 if (old->getLevel() != and_level)
226 if (old == --first_free)
236 void optimizer::add_key_field(optimizer::KeyField **key_fields,
243 table_map usable_tables,
244 vector<optimizer::SargableParam> &sargables)
246 uint32_t exists_optimize= 0;
247 if (! (field->flags & PART_KEY_FLAG))
250 if (! eq_func || (*value)->type() != Item::NULL_ITEM ||
251 ! field->getTable()->maybe_null || field->null_ptr)
253 exists_optimize= KEY_OPTIMIZE_EXISTS;
254 assert(num_values == 1);
258 table_map used_tables= 0;
260 for (uint32_t i= 0; i < num_values; i++)
262 used_tables|= (value[i])->used_tables();
263 if (! ((value[i])->used_tables() & (field->getTable()->map | RAND_TABLE_BIT)))
268 if (! (usable_tables & field->getTable()->map))
270 if (! eq_func || (*value)->type() != Item::NULL_ITEM ||
271 ! field->getTable()->maybe_null || field->null_ptr)
273 exists_optimize= KEY_OPTIMIZE_EXISTS;
277 JoinTable *stat= field->getTable()->reginfo.join_tab;
278 key_map possible_keys= field->key_start;
279 possible_keys&= field->getTable()->keys_in_use_for_query;
280 stat[0].keys|= possible_keys;
293 stat[0].key_dependent|= used_tables;
296 for (uint32_t i= 0; i < num_values; i++)
298 if (! (is_const&= value[i]->const_item()))
302 stat[0].const_keys|= possible_keys;
311 optimizer::SargableParam tmp(field, value, num_values);
312 sargables.push_back(tmp);
333 if ((cond->functype() != Item_func::BETWEEN) ||
334 ((Item_func_between*) cond)->negated ||
335 ! value[0]->eq(value[1], field->binary()))
340 if (field->result_type() == STRING_RESULT)
342 if ((*value)->result_type() != STRING_RESULT)
344 if (field->cmp_type() != (*value)->result_type())
353 if (field->cmp_type() == STRING_RESULT &&
354 ((Field_str*)field)->charset() != cond->compare_collation())
366 (*key_fields)->setField(field);
367 (*key_fields)->setEqualityConditionUsed(eq_func);
368 (*key_fields)->setValue(*value);
369 (*key_fields)->setLevel(and_level);
370 (*key_fields)->setOptimizeFlags(exists_optimize);
378 (*key_fields)->setRejectNullValues((cond->functype() == Item_func::EQ_FUNC ||
379 cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
380 ((*value)->type() == Item::FIELD_ITEM) &&
381 ((Item_field*)*value)->field->maybe_null());
382 (*key_fields)->setConditionalGuard(NULL);
386 void optimizer::add_key_equal_fields(optimizer::KeyField **key_fields,
389 Item_field *field_item,
393 table_map usable_tables,
394 vector<optimizer::SargableParam> &sargables)
396 Field *field= field_item->field;
397 add_key_field(key_fields, and_level, cond, field,
398 eq_func, val, num_values, usable_tables, sargables);
399 Item_equal *item_equal= field_item->item_equal;
406 Item_equal_iterator it(item_equal->begin());
410 if (! field->eq(item->field))
412 add_key_field(key_fields, and_level, cond, item->field,
413 eq_func, val, num_values, usable_tables,
420 void optimizer::add_key_fields(Join *join,
421 optimizer::KeyField **key_fields,
424 table_map usable_tables,
425 vector<optimizer::SargableParam> &sargables)
427 if (cond->type() == Item_func::COND_ITEM)
429 List<Item>::iterator li(((Item_cond*) cond)->argument_list()->begin());
430 optimizer::KeyField *org_key_fields= *key_fields;
432 if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
444 for (; org_key_fields != *key_fields; org_key_fields++)
445 org_key_fields->setLevel(*and_level);
459 optimizer::KeyField *start_key_fields= *key_fields;
467 *key_fields= merge_key_fields(org_key_fields, start_key_fields,
468 *key_fields, ++(*and_level));
480 if (cond->type() == Item::FUNC_ITEM &&
481 ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC)
483 Item *cond_arg= ((Item_func*)cond)->arguments()[0];
484 if (! join->group_list &&
487 join->unit->item->substype() == Item_subselect::IN_SUBS &&
488 ! join->unit->is_union())
490 optimizer::KeyField *save= *key_fields;
498 for (; save != *key_fields; save++)
499 save->setConditionalGuard(((Item_func_trig_cond*)cond)->get_trig_var());
506 if (cond->type() != Item::FUNC_ITEM)
508 Item_func *cond_func= (Item_func*) cond;
509 switch (cond_func->select_optimize())
511 case Item_func::OPTIMIZE_NONE:
513 case Item_func::OPTIMIZE_KEY:
517 if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
518 ! (cond_func->used_tables() & OUTER_REF_TABLE_BIT))
520 values= cond_func->arguments() + 1;
521 if (cond_func->functype() == Item_func::NE_FUNC &&
522 cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
523 ! (cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
525 assert(cond_func->functype() != Item_func::IN_FUNC ||
526 cond_func->argument_count() != 2);
527 add_key_equal_fields(key_fields, *and_level, cond_func,
528 (Item_field*) (cond_func->key_item()->real_item()),
530 cond_func->argument_count()-1,
531 usable_tables, sargables);
533 if (cond_func->functype() == Item_func::BETWEEN)
535 values= cond_func->arguments();
536 for (uint32_t i= 1 ; i < cond_func->argument_count(); i++)
538 Item_field *field_item;
539 if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM
541 ! (cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT))
543 field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
544 add_key_equal_fields(key_fields, *and_level, cond_func,
545 field_item, 0, values, 1, usable_tables,
552 case Item_func::OPTIMIZE_OP:
554 bool equal_func= (cond_func->functype() == Item_func::EQ_FUNC ||
555 cond_func->functype() == Item_func::EQUAL_FUNC);
557 if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
558 ! (cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
560 add_key_equal_fields(key_fields, *and_level, cond_func,
561 (Item_field*) (cond_func->arguments()[0])->real_item(),
563 cond_func->arguments()+1, 1, usable_tables,
566 if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
567 cond_func->functype() != Item_func::LIKE_FUNC &&
568 ! (cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
570 add_key_equal_fields(key_fields, *and_level, cond_func,
571 (Item_field*) (cond_func->arguments()[1])->real_item(), equal_func,
572 cond_func->arguments(),1,usable_tables,
577 case Item_func::OPTIMIZE_NULL:
579 if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
580 ! (cond_func->used_tables() & OUTER_REF_TABLE_BIT))
582 Item *tmp=
new Item_null;
585 add_key_equal_fields(key_fields, *and_level, cond_func,
586 (Item_field*) (cond_func->arguments()[0])->real_item(),
587 cond_func->functype() == Item_func::ISNULL_FUNC,
588 &tmp, 1, usable_tables, sargables);
591 case Item_func::OPTIMIZE_EQUAL:
592 Item_equal *item_equal= (Item_equal *) cond;
593 Item *const_item= item_equal->get_const();
594 Item_equal_iterator it(item_equal->begin());
605 add_key_field(key_fields, *and_level, cond_func, item->field,
606 true, &const_item, 1, usable_tables, sargables);
617 Item_equal_iterator fi(item_equal->begin());
620 Field *field= item->field;
623 if (! field->eq(item->field))
625 add_key_field(key_fields, *and_level, cond_func, field,
626 true, (Item **) &item, 1, usable_tables,
630 it= item_equal->begin();