Below is the list of changes that have just been committed into a local
5.2 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-05-22 23:53:44+04:00, sergefp@stripped +31 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
Combined into one commit
mysql-test/r/kill.result@stripped, 2007-05-22 23:53:38+04:00, sergefp@stripped +1 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Updated test results
mysql-test/r/subselect.result@stripped, 2007-05-22 23:53:38+04:00, sergefp@stripped +26 -26
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Updated test results
mysql-test/r/subselect2.result@stripped, 2007-05-22 23:53:38+04:00, sergefp@stripped +6 -6
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Updated test results
mysql-test/r/subselect3.result@stripped, 2007-05-22 23:53:38+04:00, sergefp@stripped +5 -5
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Updated test results
mysql-test/r/subselect_sj.result@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +196 -0
New BitKeeper file ``mysql-test/r/subselect_sj.result''
mysql-test/r/subselect_sj.result@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +0 -0
mysql-test/r/subselect_sj2.result@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +267 -0
New BitKeeper file ``mysql-test/r/subselect_sj2.result''
mysql-test/r/subselect_sj2.result@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +0 -0
mysql-test/t/kill.test@stripped, 2007-05-22 23:53:38+04:00, sergefp@stripped +1 -2
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Updated testcase
mysql-test/t/subselect_sj.test@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +88 -0
New BitKeeper file ``mysql-test/t/subselect_sj.test''
mysql-test/t/subselect_sj.test@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +0 -0
mysql-test/t/subselect_sj2.test@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +147 -0
New BitKeeper file ``mysql-test/t/subselect_sj2.test''
mysql-test/t/subselect_sj2.test@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +0 -0
sql/item.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +21 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +10 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_cmpfunc.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +38 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_cmpfunc.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +1 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_func.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +25 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_func.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +1 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_row.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +12 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_row.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +1 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_subselect.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +6 -2
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/item_subselect.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +20 -3
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added Item_XXX::fix_after_pullout()
sql/mysql_priv.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +6 -0
Added print_keyuse_array(), dump_TABLE_LIST_graph() debug functions
sql/sql_array.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +5 -0
Added Dynamic_array::clear()
sql/sql_base.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +5 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Use THD::thd_marker to track if we're doing fix_fields() for an AND-part
of the WHERE/ON clause
sql/sql_bitmap.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +57 -0
Added Table_map_iterator
sql/sql_class.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +2 -0
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Added THD::thd_marker
sql/sql_select.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +1755 -167
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Statement preparation phoase: Convert appropriate subquery predicates to
semi-join TABLE_LIST nests
- Join optimization: mark tables that can be pulled out of semi-join nests.
- Plan refinement: set up temptable-based duplicate elimination (use FirstMatch
in the confluent case)
- Join exection: Invoke duplicate weedout functions as necessary
- EXPLAIN [EXTENDED] output changed to display the new changes.
sql/sql_select.h@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +136 -12
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Let join execution runtime do duplicate elimination
sql/sql_show.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +4 -3
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Changed the parameters of create_myisam_from_heap()
sql/sql_test.cc@stripped, 2007-05-22 23:53:39+04:00, sergefp@stripped +238 -0
Added print_keyuse_array, dump_TABLE_LIST_graph debug functions
sql/sql_union.cc@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +15 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Changed the parameters of create_myisam_from_heap()
- Invoke flatten_subqueries() for UNION members
sql/sql_update.cc@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +3 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Changed the parameters of create_myisam_from_heap()
sql/table.h@stripped, 2007-05-22 23:53:40+04:00, sergefp@stripped +6 -1
WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables" &
WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
- Now TABLE_LIST can also represent a semi-join nest.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: sergefp
# Host: pylon.mylan
# Root: /home/psergey/mysql-5.2-subq-r4
--- 1.186/sql/item.cc 2007-05-22 23:53:55 +04:00
+++ 1.187/sql/item.cc 2007-05-22 23:53:55 +04:00
@@ -1984,6 +1984,19 @@
}
+void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+{
+ if (new_parent == depended_from)
+ depended_from= NULL;
+ Name_resolution_context *ctx= new Name_resolution_context();
+ ctx->outer_context= NULL; // We don't build a complete name resolver
+ ctx->select_lex= new_parent;
+ ctx->first_name_resolution_table= context->first_name_resolution_table;
+ ctx->last_name_resolution_table= context->last_name_resolution_table;
+ this->context=ctx;
+}
+
+
Item *Item_field::get_tmp_table_item(THD *thd)
{
Item_field *new_item= new Item_field(thd, this);
@@ -3930,7 +3943,6 @@
return TRUE;
}
-
Item *Item_field::safe_charset_converter(CHARSET_INFO *tocs)
{
no_const_subst= 1;
@@ -5587,6 +5599,14 @@
table_name= outer_field->table_name;
return Item_direct_ref::fix_fields(thd, reference);
}
+
+
+void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+{
+ if (depended_from == new_parent)
+ *ref= outer_field;
+}
+
/*
Compare two view column references for equality.
--- 1.206/sql/item.h 2007-05-22 23:53:55 +04:00
+++ 1.207/sql/item.h 2007-05-22 23:53:55 +04:00
@@ -443,7 +443,8 @@
typedef void (*Cond_traverser) (const Item *item, void *arg);
-class Item {
+class Item
+{
Item(const Item &); /* Prevent use of these */
void operator=(Item &);
public:
@@ -521,6 +522,12 @@
Field *make_string_field(TABLE *table);
virtual bool fix_fields(THD *, Item **);
/*
+ Fix after some tables has been pulled out. Basically re-calculate all
+ attributes that are dependent on the tables.
+ */
+ virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {};
+
+ /*
should be used in case where we are sure that we do not need
complete fix_fields() procedure.
*/
@@ -1351,6 +1358,7 @@
bool send(Protocol *protocol, String *str_arg);
void reset_field(Field *f);
bool fix_fields(THD *, Item **);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref);
void make_field(Send_field *tmp_field);
int save_in_field(Field *field,bool no_conversions);
void save_org_in_field(Field *field);
@@ -2088,6 +2096,7 @@
outer_field->save_org_in_field(result_field);
}
bool fix_fields(THD *, Item **);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref);
table_map used_tables() const
{
return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
--- 1.248/sql/item_cmpfunc.cc 2007-05-22 23:53:55 +04:00
+++ 1.249/sql/item_cmpfunc.cc 2007-05-22 23:53:55 +04:00
@@ -2795,11 +2795,15 @@
DBUG_ASSERT(fixed == 0);
List_iterator<Item> li(list);
Item *item;
+ void *orig_thd_marker= thd->thd_marker;
#ifndef EMBEDDED_LIBRARY
char buff[sizeof(char*)]; // Max local vars in function
#endif
not_null_tables_cache= used_tables_cache= 0;
const_item_cache= 1;
+
+ if (functype() == COND_OR_FUNC)
+ thd->thd_marker= 0;
/*
and_table_cache is the value that Item_cond_or() returns for
not_null_tables()
@@ -2858,10 +2862,44 @@
maybe_null=1;
}
thd->lex->current_select->cond_count+= list.elements;
+ thd->thd_marker= orig_thd_marker;
fix_length_and_dec();
fixed= 1;
return FALSE;
}
+
+
+void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+{
+ List_iterator<Item> li(list);
+ Item *item;
+
+ used_tables_cache=0;
+ const_item_cache=1;
+
+ and_tables_cache= ~(table_map) 0; // Here and below we do as fix_fields does
+ not_null_tables_cache= 0;
+
+ while ((item=li++))
+ {
+ table_map tmp_table_map;
+ item->fix_after_pullout(new_parent, li.ref());
+ item= *li.ref();
+ used_tables_cache|= item->used_tables();
+ const_item_cache&= item->const_item();
+
+ if (item->const_item())
+ and_tables_cache= (table_map) 0;
+ else
+ {
+ tmp_table_map= item->not_null_tables();
+ not_null_tables_cache|= tmp_table_map;
+ and_tables_cache&= tmp_table_map;
+ const_item_cache= FALSE;
+ }
+ }
+}
+
bool Item_cond::walk(Item_processor processor, bool walk_subquery, byte *arg)
{
--- 1.151/sql/item_cmpfunc.h 2007-05-22 23:53:55 +04:00
+++ 1.152/sql/item_cmpfunc.h 2007-05-22 23:53:55 +04:00
@@ -1284,6 +1284,7 @@
bool add(Item *item) { return list.push_back(item); }
void add_at_head(List<Item> *nlist) { list.prepand(nlist); }
bool fix_fields(THD *, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref);
enum Type type() const { return COND_ITEM; }
List<Item>* argument_list() { return &list; }
--- 1.357/sql/item_func.cc 2007-05-22 23:53:55 +04:00
+++ 1.358/sql/item_func.cc 2007-05-22 23:53:55 +04:00
@@ -141,10 +141,11 @@
{
DBUG_ASSERT(fixed == 0);
Item **arg,**arg_end;
+ void *save_thd_marker= thd->thd_marker;
#ifndef EMBEDDED_LIBRARY // Avoid compiler warning
char buff[STACK_BUFF_ALLOC]; // Max argument in function
#endif
-
+ thd->thd_marker= 0;
used_tables_cache= not_null_tables_cache= 0;
const_item_cache=1;
@@ -190,7 +191,30 @@
if (thd->net.report_error) // An error inside fix_length_and_dec occured
return TRUE;
fixed= 1;
+ thd->thd_marker= save_thd_marker;
return FALSE;
+}
+
+
+void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+{
+ Item **arg,**arg_end;
+
+ used_tables_cache= not_null_tables_cache= 0;
+ const_item_cache=1;
+
+ if (arg_count)
+ {
+ for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
+ {
+ (*arg)->fix_after_pullout(new_parent, arg);
+ Item *item= *arg;
+
+ used_tables_cache|= item->used_tables();
+ not_null_tables_cache|= item->not_null_tables();
+ const_item_cache&= item->const_item();
+ }
+ }
}
--- 1.161/sql/item_func.h 2007-05-22 23:53:55 +04:00
+++ 1.162/sql/item_func.h 2007-05-22 23:53:55 +04:00
@@ -116,6 +116,7 @@
// Constructor used for Item_cond_and/or (see Item comment)
Item_func(THD *thd, Item_func *item);
bool fix_fields(THD *, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref);
table_map used_tables() const;
table_map not_null_tables() const;
void update_used_tables();
--- 1.486/sql/mysql_priv.h 2007-05-22 23:53:55 +04:00
+++ 1.487/sql/mysql_priv.h 2007-05-22 23:53:55 +04:00
@@ -1464,8 +1464,14 @@
void TEST_filesort(SORT_FIELD *sortorder,uint s_length);
void print_plan(JOIN* join,uint idx, double record_count, double read_time,
double current_read_time, const char *info);
+void print_keyuse_array(DYNAMIC_ARRAY *keyuse_array);
+#define EXTRA_DEBUG_DUMP_TABLE_LISTS
+#ifdef EXTRA_DEBUG_DUMP_TABLE_LISTS
+void dump_TABLE_LIST_graph(SELECT_LEX *select_lex, TABLE_LIST* tl);
+#endif
#endif
void mysql_print_status();
+
/* key.cc */
int find_ref_key(KEY *key, uint key_count, byte *record, Field *field,
uint *key_length);
--- 1.376/sql/sql_base.cc 2007-05-22 23:53:55 +04:00
+++ 1.377/sql/sql_base.cc 2007-05-22 23:53:55 +04:00
@@ -5874,6 +5874,7 @@
SELECT_LEX *select_lex= thd->lex->current_select;
Query_arena *arena= thd->stmt_arena, backup;
TABLE_LIST *table= NULL; // For HP compilers
+ void *save_thd_marker= thd->thd_marker;
/*
it_is_update set to TRUE when tables of primary SELECT_LEX (SELECT_LEX
which belong to LEX, i.e. most up SELECT) will be updated by
@@ -5901,6 +5902,7 @@
goto err_no_arena;
}
+ thd->thd_marker= (void*)1;
if (*conds)
{
thd->where="where clause";
@@ -5908,6 +5910,7 @@
(*conds)->check_cols(1))
goto err_no_arena;
}
+ thd->thd_marker= save_thd_marker;
/*
Apply fix_fields() to all ON clauses at all levels of nesting,
@@ -5923,6 +5926,7 @@
if (embedded->on_expr)
{
/* Make a join an a expression */
+ thd->thd_marker= (void*)embedded;
thd->where="on clause";
if (!embedded->on_expr->fixed &&
embedded->on_expr->fix_fields(thd, &embedded->on_expr) ||
@@ -5947,6 +5951,7 @@
}
}
}
+ thd->thd_marker= save_thd_marker;
if (!thd->stmt_arena->is_conventional())
{
--- 1.341/sql/sql_class.h 2007-05-22 23:53:55 +04:00
+++ 1.342/sql/sql_class.h 2007-05-22 23:53:55 +04:00
@@ -954,6 +954,8 @@
/* container for handler's private per-connection data */
void *ha_data[MAX_HA];
+ /* Place to store various things */
+ void *thd_marker;
#ifndef MYSQL_CLIENT
int binlog_setup_trx_data();
--- 1.494/sql/sql_select.cc 2007-05-22 23:53:55 +04:00
+++ 1.495/sql/sql_select.cc 2007-05-22 23:53:55 +04:00
@@ -81,7 +81,7 @@
static bool make_simple_join(JOIN *join,TABLE *tmp_table);
static void make_outerjoin_info(JOIN *join);
static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *item);
-static void make_join_readinfo(JOIN *join, ulonglong options);
+static void make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after);
static bool only_eq_ref_tables(JOIN *join, ORDER *order, table_map tables);
static void update_depend_map(JOIN *join);
static void update_depend_map(JOIN *join, ORDER *order);
@@ -99,7 +99,7 @@
COND_EQUAL *cond_equal,
void *table_join_idx);
static COND *simplify_joins(JOIN *join, List<TABLE_LIST> *join_list,
- COND *conds, bool top);
+ COND *conds, bool top, bool in_sj);
static bool check_interleaving_with_nj(JOIN_TAB *last, JOIN_TAB *next);
static void restore_prev_nj_state(JOIN_TAB *last);
static void reset_nj_counters(List<TABLE_LIST> *join_list);
@@ -111,7 +111,9 @@
Item::cond_result *cond_value);
static bool const_expression_in_where(COND *conds,Item *item, Item **comp_item);
static bool open_tmp_table(TABLE *table);
-static bool create_myisam_tmp_table(TABLE *table,TMP_TABLE_PARAM *param,
+static bool create_myisam_tmp_table(TABLE *table, KEY *keyinfo,
+ MI_COLUMNDEF *start_recinfo,
+ MI_COLUMNDEF **recinfo,
ulonglong options);
static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table,
Procedure *proc);
@@ -235,6 +237,7 @@
{
SELECT_LEX_UNIT *unit= &lex->unit;
unit->set_limit(unit->global_parameters);
+ thd->thd_marker= 0;
/*
'options' of mysql_select will be set in JOIN, as far as JOIN for
every PS/SP execution new, we will not need reset this flag if
@@ -331,6 +334,7 @@
return res;
}
+#define MAGIC_IN_WHERE_TOP_LEVEL 10
/*
Function to setup clauses without sum functions
*/
@@ -440,15 +444,73 @@
if (!thd->lex->view_prepare_mode)
{
Item_subselect *subselect;
- /* Is it subselect? */
+ /* Are we in a subquery predicate? */
if ((subselect= select_lex->master_unit()->item))
{
Item_subselect::trans_res res;
- if ((res= subselect->select_transformer(this)) !=
- Item_subselect::RES_OK)
+ bool do_delay= TRUE;
+ /*
+ Check if we're in subquery that is a candidate for flattening into a
+ semi-join (which is done done in flatten_subqueries()). The
+ requirements are:
+ 1. Subquery predicate is an IN/=ANY subq predicate
+ 2. Subquery is a single SELECT (not a UNION)
+ 3. Subquery does not have GROUP BY or ORDER BY
+ 4. Subquery does not use aggregate functions or HAVING
+ 5. Subquery predicate is at the AND-top-level of ON/WHERE clause
+
+ (*). We are not in a subquery of a single table UPDATE/DELETE that
+ doesn't have a JOIN (TODO: We should handle this at some
+ point by switching to multi-table UPDATE/DELETE)
+
+ (**). We're not in a confluent table-less subquery, like
+ "SELECT 1".
+ */
+ if (subselect->substype() == Item_subselect::IN_SUBS && // 1
+ !select_lex->master_unit()->first_select()->next_select() && // 2
+ !select_lex->group_list.elements && !order && // 3
+ !having && !select_lex->with_sum_func && // 4
+ thd->thd_marker && // 5
+ select_lex->outer_select()->join && // (*)
+ select_lex->master_unit()->first_select()->leaf_tables && // (**)
+ do_delay)
{
- select_lex->fix_prepare_information(thd, &conds, &having);
- DBUG_RETURN((res == Item_subselect::RES_ERROR));
+ fprintf(stderr, "subq is an sj candidate\n");
+ Item_in_subselect *in_subs= (Item_in_subselect*)subselect;
+
+ if (thd->stmt_arena->state != Query_arena::PREPARED)
+ {
+ if (!in_subs->left_expr->fixed &&
+ in_subs->left_expr->fix_fields(thd, &in_subs->left_expr))
+ {
+ DBUG_RETURN(-1);
+ }
+ /*
+ Check that the right part of the subselect contains no more than one
+ column. E.g. in SELECT 1 IN (SELECT * ..) the right part is (SELECT * ...)
+ */
+ if (subselect->substype() == Item_subselect::IN_SUBS &&
+ (select_lex->item_list.elements !=
+ ((Item_in_subselect*)subselect)->left_expr->cols()))
+ {
+ my_error(ER_OPERAND_COLUMNS, MYF(0), ((Item_in_subselect*)subselect)->left_expr->cols());
+ DBUG_RETURN(-1);
+ }
+ }
+
+ /* Register the subquery for further processing */
+ select_lex->outer_select()->join->sj_subselects.append(in_subs);
+ in_subs->expr_join_nest= (TABLE_LIST*)thd->thd_marker;
+ }
+ else
+ {
+ fprintf(stderr, "subq is not an sj candidate\n");
+ if ((res= subselect->select_transformer(this)) !=
+ Item_subselect::RES_OK)
+ {
+ select_lex->fix_prepare_information(thd, &conds, &having);
+ DBUG_RETURN((res == Item_subselect::RES_ERROR));
+ }
}
}
}
@@ -655,6 +717,283 @@
}
+static bool bitmap_covers(const table_map x, const table_map y)
+{
+ return !test(y & ~x);
+}
+
+
+/*
+ Check if the table's rowid is included in the temptable
+
+ SYNOPSIS
+ sj_table_is_included()
+ join The join
+ join_tab The table to be checked
+
+ DESCRIPTION
+ SemiJoinDuplicateElimination: check the table's rowid should be included
+ in the temptable. This is so if
+
+ 1. The table is not embedded within some semi-join nest
+ 2. The has been pulled out of a semi-join nest, or
+
+ 3. The table is functionally dependent on some previous table
+
+ [4. This is also true for constant tables that can't be
+ NULL-complemented but this function is not called for such tables]
+
+ RETURN
+ TRUE - Include table's rowid
+ FALSE - Don't
+*/
+
+static bool sj_table_is_included(JOIN *join, JOIN_TAB *join_tab)
+{
+ if (join_tab->emb_sj_nest)
+ return FALSE;
+
+ /* Check if this table is functionally dependent on the tables that
+ are within the same outer join nest
+ */
+ TABLE_LIST *embedding= join_tab->table->pos_in_table_list->embedding;
+ if (join_tab->type == JT_EQ_REF)
+ {
+ Table_map_iterator it(join_tab->ref.depend_map & ~PSEUDO_TABLE_BITS);
+ uint idx;
+ while ((idx= it.next_bit())!=Table_map_iterator::BITMAP_END)
+ {
+ JOIN_TAB *ref_tab= join->join_tab + idx;
+ if (embedding == ref_tab->table->pos_in_table_list->embedding)
+ return TRUE;
+ }
+ /* Ok, functionally dependent */
+ return FALSE;
+ }
+ /* Not functionally dependent => need to include*/
+ return TRUE;
+}
+
+
+TABLE *create_duplicate_weedout_tmp_table(THD *thd, uint uniq_tuple_length_arg,
+ SJ_TMP_TABLE *sjtbl);
+
+
+/*
+ SemiJoinDuplicateElimination: Setup the temporary tables
+
+ SYNOPSIS
+ setup_semijoin_dups_elimination()
+ join Join to process
+
+ DESCRIPTION
+ SemiJoinDuplicateElimination strategy: setup the temporary tables needed
+ to handle semi-joins with strategy.
+
+ We have the join order. SJ-nest(s) have their members "dispersed" in the
+ join order. Each SJ-nest has a range that needs to be covered by a
+ temptable. The range is
+
+ [first-inner-table; latest(last-inner-table, last-correlated-table)]
+
+ If ranges of tow SJ-nests have non-empty intersection, they need to be
+ joined into one range.
+
+ This function calculates those ranges, joining them if necessary.
+
+ This optimization is performed at plan refinement stage so it is
+ naturally a per-EXECUTE optimization.
+
+ RETURN
+ FALSE OK
+ TRUE Out of memory error
+*/
+
+static
+int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, uint no_jbuf_after)
+{
+ THD *thd= join->thd;
+ SJ_TMP_TABLE **next_sjtbl_ptr= &join->sj_tmp_tables;
+
+ SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
+ uint jt_start_idx; // Start of dups-producing join order range
+ uint jt_rowid_offset; // # tuple bytes are already occupied (w/o NULL bytes)
+ uint jt_null_bits; // # null bits in tuple bytes
+ SJ_TMP_TABLE::TAB *jt_first_tab; // First temptable participant
+ SJ_TMP_TABLE::TAB *jt_last_tab; // Last temptable participant + 1
+ table_map emb_sj_map= 0; /* A bitmap of sj-nests we're currently in */
+ table_map cur_map= join->const_table_map;
+ bool dealing_with_jbuf= FALSE;
+ uint i;
+
+ DBUG_ENTER("setup_semijoin_dups_elimination");
+
+ jt_last_tab= jt_first_tab= sjtabs;
+
+ for (i=join->const_tables ; i < join->tables ; i++)
+ {
+ JOIN_TAB *tab=join->join_tab+i;
+ TABLE *table=tab->table;
+ cur_map |= table->map;
+
+ if (tab->emb_sj_nest)
+ {
+ if (!emb_sj_map)
+ {
+ jt_start_idx= i;
+ jt_rowid_offset= 0;
+ jt_null_bits= 0;
+ }
+ /*
+ If we're entering the coverage of another SJ-nest, record that we need
+ all of its tables
+ */
+ emb_sj_map |= tab->emb_sj_nest->sj_inner_tables |
+ tab->emb_sj_nest->nested_join->sj_depends_on;
+ }
+
+ /*
+ If we're in SJ-nest and current table should be included in it, add
+ it.
+ */
+ if (emb_sj_map)
+ {
+ tab->inside_sj_dups_range= TRUE;
+ if (sj_table_is_included(join, tab))
+ {
+ /* Get a TAB for this Item. */
+ jt_last_tab->join_tab= tab;
+ jt_last_tab->rowid_offset= jt_rowid_offset;
+ jt_rowid_offset += table->file->ref_length;
+ if (tab->table->maybe_null)
+ {
+ jt_last_tab->null_bit= jt_null_bits++;
+ }
+ jt_last_tab++;
+ tab->table->prepare_for_position();
+ tab->rowid_keep_flags= JOIN_TAB::KEEP_ROWID | JOIN_TAB::CALL_POSITION;
+ }
+
+ if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) &&
+ tab->type == JT_ALL && tab->use_quick != 2 && !tab->first_inner &&
+ i <= no_jbuf_after && !dealing_with_jbuf)
+ {
+ /*
+ This table uses join buffering, which invalidates the usual
+ assumptions.
+ x x [x x] x [x x x] x [x x X* x] x
+
+ We'll have to use temptable-based elimination w/o prefix tables.
+
+
+ All preceding temptables are eliminated.
+ include all of the preceding tables.
+ */
+ dealing_with_jbuf= TRUE;
+
+ /* Reset the current sj nest processor: */
+ jt_start_idx= 0;
+ jt_rowid_offset= 0;
+ jt_null_bits= 0;
+
+ // Remove all other tables:
+ join->sj_tmp_tables= NULL;
+ next_sjtbl_ptr= &join->sj_tmp_tables;
+ jt_last_tab= jt_first_tab= sjtabs;
+
+ JOIN_TAB *prev_tab;
+ for (prev_tab= join->join_tab + join->const_tables;
+ prev_tab != tab+1; prev_tab++)
+ {
+ /* Remove temptable-based weedout tables */
+ prev_tab->flush_weedout_table= NULL;
+ prev_tab->check_weed_out_table= NULL;
+
+ if (sj_table_is_included(join, prev_tab))
+ {
+ jt_last_tab->join_tab= prev_tab;
+ jt_last_tab->rowid_offset= jt_rowid_offset;
+ jt_rowid_offset += table->file->ref_length;
+ if (tab->table->maybe_null)
+ {
+ jt_last_tab->null_bit= jt_null_bits++;
+ }
+ jt_last_tab++;
+ prev_tab->table->prepare_for_position();
+ prev_tab->rowid_keep_flags= JOIN_TAB::KEEP_ROWID | JOIN_TAB::CALL_POSITION;
+ }
+ }
+ }
+
+ /*
+ Check if the partial join order now contains all tables of all
+ leaving coverage of all semi-joins.
+ */
+ if (bitmap_covers(cur_map, emb_sj_map & ~PSEUDO_TABLE_BITS))
+ {
+ /* Ok, this table concludes the duplicate-producing range */
+ if (jt_first_tab != jt_last_tab)
+ {
+ /* Need to use temptable */
+ SJ_TMP_TABLE *sjtbl;
+ uint tabs_size= (jt_last_tab - jt_first_tab) *
+ sizeof(SJ_TMP_TABLE::TAB);
+ if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))) ||
+ !(sjtbl->tabs= (SJ_TMP_TABLE::TAB*) thd->alloc(tabs_size)))
+ DBUG_RETURN(TRUE);
+ memcpy(sjtbl->tabs, jt_first_tab, tabs_size);
+ sjtbl->tabs_end= sjtbl->tabs + (jt_last_tab - jt_first_tab);
+ sjtbl->start_idx= jt_start_idx;
+ sjtbl->end_idx= i;
+ sjtbl->rowid_len= jt_rowid_offset;
+ sjtbl->null_bits= jt_null_bits;
+ sjtbl->null_bytes= (jt_null_bits + 7)/8;
+
+ *next_sjtbl_ptr= sjtbl;
+ next_sjtbl_ptr= &(sjtbl->next);
+ sjtbl->next= NULL;
+
+ sjtbl->tmp_table=
+ create_duplicate_weedout_tmp_table(thd,
+ sjtbl->rowid_len +
+ sjtbl->null_bytes,
+ sjtbl);
+
+ join->join_tab[jt_start_idx].flush_weedout_table= sjtbl;
+ tab->check_weed_out_table= sjtbl;
+ }
+ else
+ {
+ /*
+ No need to use temptable, setup shortcutting from tab #i to
+ "just before tab #jt_start_idx".
+ */
+ tab->do_firstmatch= join->join_tab + ((jt_start_idx > 0)?
+ jt_start_idx - 1 : 0);
+ }
+ emb_sj_map= 0;
+ }
+ }
+ }
+ DBUG_RETURN(FALSE);
+}
+
+
+static void cleanup_sj_tmp_tables(JOIN *join)
+{
+ for (SJ_TMP_TABLE *sj_tbl= join->sj_tmp_tables; sj_tbl;
+ sj_tbl= sj_tbl->next)
+ {
+ if (sj_tbl->tmp_table)
+ {
+ free_tmp_table(join->thd, sj_tbl->tmp_table);
+ }
+ }
+ join->sj_tmp_tables= NULL;
+}
+
+uint make_join_orderinfo(JOIN *join);
+
/*
global select optimisation.
return 0 - success
@@ -720,7 +1059,7 @@
sel->first_cond_optimization= 0;
/* Convert all outer joins to inner joins if possible */
- conds= simplify_joins(this, join_list, conds, TRUE);
+ conds= simplify_joins(this, join_list, conds, TRUE, FALSE);
build_bitmap_for_nested_joins(join_list, 0);
sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0;
@@ -1107,12 +1446,18 @@
(group_list && order) ||
test(select_options & OPTION_BUFFER_RESULT)));
- // No cache for MATCH
- make_join_readinfo(this,
- (select_options & (SELECT_DESCRIBE |
- SELECT_NO_JOIN_CACHE)) |
- (select_lex->ftfunc_list->elements ?
- SELECT_NO_JOIN_CACHE : 0));
+ uint no_jbuf_after= make_join_orderinfo(this);
+ ulonglong select_opts_for_readinfo=
+ (select_options & (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) |
+ (select_lex->ftfunc_list->elements ? SELECT_NO_JOIN_CACHE : 0);
+
+ sj_tmp_tables= NULL;
+ if (sj_nests.elements())
+ setup_semijoin_dups_elimination(this, select_opts_for_readinfo,
+ no_jbuf_after);
+
+ // No cache for MATCH == 'Don't use join buffering when we use MATCH'.
+ make_join_readinfo(this, select_opts_for_readinfo, no_jbuf_after);
/* Perform FULLTEXT search before all regular searches */
if (!(select_options & SELECT_DESCRIBE))
@@ -2042,6 +2387,9 @@
DBUG_RETURN(error);
}
+
+
+
/*
An entry point to single-unit select (a select without UNION).
@@ -2146,6 +2494,18 @@
}
}
+ if (!unit->item)
+ {
+ //dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables);
+ /* We're not in a subquery predicate */
+ if (join->flatten_subqueries())
+ {
+ thd->net.report_error= 1;
+ goto err;
+ }
+ //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
+ }
+
if ((err= join->optimize()))
{
goto err; // 1
@@ -2188,11 +2548,601 @@
DBUG_RETURN(join->error);
}
+
+int subq_sj_candidate_cmp(Item_in_subselect* const *el1,
+ Item_in_subselect * const *el2)
+{
+ return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 :
+ ( ((*el1)->sj_convert_priority == (*el2)->sj_convert_priority)? 0 : -1);
+}
+
+
+inline Item * and_items(Item* cond, Item *item)
+{
+ return (cond? (new Item_cond_and(cond, item)) : item);
+}
+
+
+static TABLE_LIST *alloc_join_nest(THD *thd)
+{
+ TABLE_LIST *tbl;
+ if (!(tbl= (TABLE_LIST*) thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST))+
+ sizeof(NESTED_JOIN))))
+ return NULL;
+ tbl->nested_join= (NESTED_JOIN*) ((byte*)tbl +
+ ALIGN_SIZE(sizeof(TABLE_LIST)));
+ return tbl;
+}
+
+
+void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
+{
+ List_iterator<TABLE_LIST> it(*tlist);
+ TABLE_LIST *table;
+ while ((table= it++))
+ {
+ if (table->on_expr)
+ table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
+ if (table->nested_join)
+ fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
+ }
+}
+
+
+/*
+ Convert a subquery predicate into a TABLE_LIST semi-join nest
+
+ SYNOPSIS
+ convert_subq_to_sj()
+ parent_join Parent join, the one that has subq_pred in its WHERE/ON
+ clause
+ subq_pred Subquery predicate to be converted
+
+ DESCRIPTION
+ Convert a subquery predicate into a TABLE_LIST semi-join nest. All the
+ prerequisites are already checked, so the conversion is always successfull.
+
+ Prepared Statements: the transformation is permanent:
+ - Changes in TABLE_LIST structures are naturally permanent
+ - Item tree changes are performed on statement MEM_ROOT:
+ = we activate statement MEM_ROOT
+ = this function is called before the first fix_prepare_information
+ call.
+
+ This is intended because the criteria for subquery-to-sj conversion remain
+ constant for the lifetime of the Prepared Statement.
+
+ RETURN
+ FALSE OK
+ TRUE Out of memory error
+*/
+
+bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
+{
+ SELECT_LEX *parent_lex= parent_join->select_lex;
+ TABLE_LIST *emb_tbl_nest= NULL;
+ List<TABLE_LIST> *emb_join_list= &parent_lex->top_join_list;
+ Query_arena *arena, backup;
+ THD *thd= parent_join->thd;
+ DBUG_ENTER("convert_subq_to_sj");
+ arena= thd->activate_stmt_arena_if_needed(&backup);
+
+ /*
+ 1. Find out where to put the predicate into.
+ Note: for "t1 LEFT JOIN t2" this will be t2, a leaf.
+ */
+ if ((void*)subq_pred->expr_join_nest != (void*)1)
+ {
+ if (subq_pred->expr_join_nest->nested_join)
+ {
+ /*
+ We're dealing with
+
+ ... [LEFT] JOIN ( ... ) ON (subquery AND whatever) ...
+
+ The sj-nest will be inserted into the brackets nest.
+ */
+ emb_tbl_nest= subq_pred->expr_join_nest;
+ emb_join_list= &emb_tbl_nest->nested_join->join_list;
+ }
+ else if (!subq_pred->expr_join_nest->outer_join)
+ {
+ /*
+ We're dealing with
+
+ ... INNER JOIN tblX ON (subquery AND whatever) ...
+
+ The sj-nest will be tblX's "sibling", i.e. another child of its
+ parent. This is ok because tblX is joined as an inner join.
+ */
+ emb_tbl_nest= subq_pred->expr_join_nest->embedding;
+ if (emb_tbl_nest)
+ emb_join_list= &emb_tbl_nest->nested_join->join_list;
+ }
+ else if (!subq_pred->expr_join_nest->nested_join)
+ {
+ TABLE_LIST *outer_tbl= subq_pred->expr_join_nest;
+ TABLE_LIST *wrap_nest;
+ /*
+ We're dealing with
+
+ ... LEFT JOIN tbl ON (on_expr AND subq_pred) ...
+
+ we'll need to convert it into:
+
+ ... LEFT JOIN ( tbl SJ (subq_tables) ) ON (on_expr AND subq_pred) ...
+ | |
+ |<----- wrap_nest ---->|
+
+ Q: other subqueries may be pointing to this element. What to do?
+ A1: simple solution: copy *subq_pred->expr_join_nest= *parent_nest.
+ But we'll need to fix other pointers.
+ A2: Another way: have TABLE_LIST::next_ptr so the following
+ subqueries know the table has been nested.
+ A3: changes in the TABLE_LIST::outer_join will make everything work
+ automatically.
+ */
+ if (!(wrap_nest= alloc_join_nest(parent_join->thd)))
+ {
+ DBUG_RETURN(TRUE);
+ }
+ wrap_nest->embedding= outer_tbl->embedding;
+ wrap_nest->join_list= outer_tbl->join_list;
+ wrap_nest->alias= (char*) "(sj-wrap)";
+
+ wrap_nest->nested_join->join_list.empty();
+ wrap_nest->nested_join->join_list.push_back(outer_tbl);
+
+ outer_tbl->embedding= wrap_nest;
+ outer_tbl->join_list= &wrap_nest->nested_join->join_list;
+
+ /*
+ wrap_nest will take place of outer_tbl, so move the outer join flag
+ and on_expr
+ */
+ wrap_nest->outer_join= outer_tbl->outer_join;
+ outer_tbl->outer_join= 0;
+
+ wrap_nest->on_expr= outer_tbl->on_expr;
+ outer_tbl->on_expr= NULL;
+
+ List_iterator<TABLE_LIST> li(*wrap_nest->join_list);
+ TABLE_LIST *tbl;
+ while ((tbl= li++))
+ {
+ if (tbl == outer_tbl)
+ {
+ li.replace(wrap_nest);
+ break;
+ }
+ }
+ /*
+ Ok now wrap_nest 'contains' outer_tbl and we're ready to add the
+ semi-join nest into it
+ */
+ emb_join_list= &wrap_nest->nested_join->join_list;
+ emb_tbl_nest= wrap_nest;
+ }
+ }
+
+ TABLE_LIST *sj_nest;
+ NESTED_JOIN *nested_join;
+ if (!(sj_nest= alloc_join_nest(parent_join->thd)))
+ {
+ DBUG_RETURN(TRUE);
+ }
+ nested_join= sj_nest->nested_join;
+
+ sj_nest->join_list= emb_join_list;
+ sj_nest->embedding= emb_tbl_nest;
+ sj_nest->alias= (char*) "(sj-nest)";
+ /* Nests do not participate in those 'chains', so: */
+ /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/
+ emb_join_list->push_back(sj_nest);
+
+ /*
+ nested_join->used_tables and nested_join->not_null_tables are
+ initialized in simplify_joins().
+ */
+
+ /*
+ 2. Walk through subquery's top list and set 'embedding' to point to the
+ sj-nest.
+ */
+ st_select_lex *subq_lex= subq_pred->unit->first_select();
+ nested_join->join_list.empty();
+ List_iterator_fast<TABLE_LIST> li(subq_lex->top_join_list);
+ TABLE_LIST *tl, *last_leaf;
+ while ((tl= li++))
+ {
+ tl->embedding= sj_nest;
+ tl->join_list= &nested_join->join_list;
+ nested_join->join_list.push_back(tl);
+ }
+
+ /*
+ Reconnect the next_leaf chain.
+ TODO: Do we have to put subquery's tables at the end of the chain?
+ Inserting them at the beginning would be a bit faster.
+ NOTE: We actually insert them at the front! That's because the order is
+ reversed in this list.
+ */
+ for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf);
+ tl->next_leaf= subq_lex->leaf_tables;
+ last_leaf= tl;
+
+ /*
+ Same as above for next_local chain
+ (a theory: a next_local chain always starts with ::leaf_tables
+ because view's tables are inserted after the view)
+ */
+ for (tl= parent_lex->leaf_tables; tl->next_local; tl= tl->next_local);
+ tl->next_local= subq_lex->leaf_tables;
+
+ /* A theory: no need to re-connect the next_global chain */
+
+ /* 3. Remove the original subquery predicate from the WHERE/ON */
+ *(subq_pred->ref_ptr)= new Item_int(1);
+ subq_pred->converted_to_sj= TRUE; // for subsequent executions
+ /*TODO: also reset the 'with_subselect' there. */
+
+ /* n. Adjust the parent_join->tables counter */
+ uint table_no= parent_join->tables;
+ /* n. Walk through child's tables and adjust table->map */
+ for (tl= subq_lex->leaf_tables; tl; tl= tl->next_leaf, table_no++)
+ {
+ tl->table->tablenr= table_no;
+ tl->table->map= ((table_map)1) << table_no;
+ SELECT_LEX *old_sl= tl->select_lex;
+ tl->select_lex= parent_join->select_lex;
+ for(TABLE_LIST *emb= tl->embedding; emb && emb->select_lex == old_sl; emb= emb->embedding)
+ emb->select_lex= parent_join->select_lex;
+ }
+ parent_join->tables += subq_lex->join->tables;
+
+ /*
+ Put the subquery's WHERE into semi-join's sj_on_expr
+ Add the subquery-induced equalities too.
+ */
+ if (!subq_pred->left_expr->fixed &&
+ subq_pred->left_expr->fix_fields(thd, &subq_pred->left_expr));
+
+ sj_nest->nested_join->sj_depends_on= subq_pred->used_tables() |
+ subq_pred->left_expr->used_tables();
+ sj_nest->sj_on_expr= subq_lex->where;
+ if (subq_pred->left_expr->cols() == 1)
+ {
+ Item *item_eq=
+ new Item_func_eq(subq_pred->left_expr,
+ subq_lex->ref_pointer_array[0]);
+ sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
+ }
+ else
+ {
+ for (uint i= 0; i < subq_pred->left_expr->cols(); i++)
+ {
+ Item *item_eq=
+ new Item_func_eq(subq_pred->left_expr->element_index(i),
+ subq_lex->ref_pointer_array[i]);
+ sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
+ }
+ }
+ /* Fix the created equality and AND */
+ sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr);
+
+ /*
+ Walk through sj nest's WHERE and ON expressions and call
+ item->fix_table_changes() for all items.
+ */
+ sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
+ fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
+
+
+ /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
+ subq_lex->master_unit()->exclude_level();
+
+ DBUG_EXECUTE("where",print_where(sj_nest->sj_on_expr,"SJ-EXPR"););
+ /* Inject sj_on_expr into the parent's WHERE or ON */
+ if (emb_tbl_nest)
+ {
+ emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
+ sj_nest->sj_on_expr);
+ emb_tbl_nest->on_expr->fix_fields(parent_join->thd, &emb_tbl_nest->on_expr);
+ }
+ else
+ {
+ /* Inject into the WHERE */
+ parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr);
+ parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
+ parent_join->select_lex->where= parent_join->conds;
+ }
+
+ if (subq_lex->ftfunc_list->elements)
+ {
+ Item_func_match *ifm;
+ List_iterator_fast<Item_func_match> li(*(subq_lex->ftfunc_list));
+ while ((ifm= li++))
+ parent_lex->ftfunc_list->push_front(ifm);
+ }
+
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+ DBUG_RETURN(FALSE);
+}
+
+
+/*
+ Convert candidate subquery predicates to semi-joins
+
+ SYNOPSIS
+ JOIN::flatten_subqueries()
+
+ DESCRIPTION
+ Convert candidate subquery predicates to semi-joins.
+
+ RETURN
+ FALSE OK
+ TRUE Error
+*/
+
+bool JOIN::flatten_subqueries()
+{
+ DBUG_ENTER("JOIN::flatten_subqueries");
+ Item_in_subselect **in_subq;
+ Item_in_subselect **in_subq_end;
+
+ if (sj_subselects.elements() == 0)
+ DBUG_RETURN(FALSE);
+
+ /* 1. Fix children subqueries */
+ for (in_subq= sj_subselects.front(), in_subq_end= sj_subselects.back();
+ in_subq != in_subq_end; in_subq++)
+ {
+ JOIN *child_join= (*in_subq)->unit->first_select()->join;
+ child_join->outer_tables = child_join->tables;
+ if (child_join->flatten_subqueries())
+ DBUG_RETURN(TRUE);
+ (*in_subq)->sj_convert_priority=
+ (*in_subq)->is_correlated * MAX_TABLES + child_join->outer_tables;
+ }
+
+ //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
+ /*
+ 2. Pick which subqueries to convert:
+ sort the subquery array
+ - prefer correlated subqueries over uncorrelated;
+ - prefer subqueries that have greater number of outer tables;
+ */
+ sj_subselects.sort(subq_sj_candidate_cmp);
+ // #tables-in-parent-query + #tables-in-subquery < MAX_TABLES
+ bool do_converts= TRUE;
+ for (in_subq= sj_subselects.front();
+ in_subq != in_subq_end &&
+ tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;
+ in_subq++)
+ {
+ if (!do_converts)
+ break;
+ if (convert_subq_to_sj(this, *in_subq))
+ DBUG_RETURN(TRUE);
+ }
+
+ /* 3. Finalize those we didn't convert */
+ for (; in_subq!= in_subq_end; in_subq++)
+ {
+ JOIN *child_join= (*in_subq)->unit->first_select()->join;
+ Item_subselect::trans_res res;
+ (*in_subq)->changed= 0;
+ (*in_subq)->fixed= 0;
+ res= (*in_subq)->select_transformer(child_join);
+ if (res == Item_subselect::RES_ERROR)
+ DBUG_RETURN(TRUE);
+
+ *((*in_subq)->ref_ptr)= (*in_subq)->substitution;
+ (*in_subq)->changed= 1;
+ (*in_subq)->fixed= 1;
+ if (!(*in_subq)->substitution->fixed &&
+ (*in_subq)->substitution->fix_fields(thd, (*in_subq)->ref_ptr))
+ DBUG_RETURN(TRUE);
+
+ //if ((*in_subq)->fix_fields(thd, (*in_subq)->ref_ptr))
+ // DBUG_RETURN(TRUE);
+ }
+ sj_subselects.clear();
+ DBUG_RETURN(FALSE);
+}
+
+
+/*
+ Check if table's KEYUSE elements have an eq_ref(outer_tables) candidate
+
+ SYNOPSIS
+ find_eq_ref_candidate()
+ table Table to be checked
+ sj_inner_tables Bitmap of inner tables. eq_ref(inner_table) doesn't
+ count.
+
+ DESCRIPTION
+ Check if table's KEYUSE elements have an eq_ref(outer_tables) candidate
+
+ TODO
+ Check again if it is feasible to factor common parts with constant table
+ search
+
+ RETURN
+ TRUE - There exists an eq_ref(outer-tables) candidate
+ FALSE - Otherwise
+*/
+
+bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables)
+{
+ KEYUSE *keyuse= table->reginfo.join_tab->keyuse;
+ uint key, part;
+
+ if (keyuse)
+ {
+ while (1) /* For each key */
+ {
+ key= keyuse->key;
+ KEY *keyinfo= table->key_info + key;
+ key_part_map bound_parts= 0;
+ if ((keyinfo->flags & (HA_NOSAME | HA_END_SPACE_KEY)) == HA_NOSAME)
+ {
+ do // For all key parts
+ {
+ do
+ { // For all ways to read the keypart
+ /* check if this is "t.keypart = expr(outer_tables) */
+ if (!(keyuse->used_tables & sj_inner_tables) &&
+ !(keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL))
+ {
+ bound_parts |= 1 << keyuse->keypart;
+ }
+ keyuse++;
+ } while (keyuse->key == key && keyuse->keypart == part &&
+ keyuse->table == table);
+ } while (keyuse->key == key && keyuse->table == table);
+
+ if (bound_parts == PREV_BITS(uint, keyinfo->key_parts))
+ return TRUE;
+ if (keyuse->table != table)
+ return FALSE;
+ }
+ else
+ {
+ do
+ {
+ keyuse++;
+ if (keyuse->table != table)
+ return FALSE;
+ }
+ while (keyuse->key == key);
+ }
+ }
+ }
+ return FALSE;
+}
+
+
+/*
+ Pull tables out of semi-join nests, if possible
+
+ SYNOPSIS
+ pull_out_semijoin_tables()
+ join The join where to do the semi-join flattening
+
+ DESCRIPTION
+ Try to pull tables out of semi-join nests.
+
+ PRECONDITIONS
+ When this function is called, the join may have several semi-join nests
+ (possibly within different semi-join nests), but it is guaranteed that
+ one semi-join nest does not contain another.
+
+ ACTION
+ A table can be pulled out of the semi-join nest if
+ - It is a constant table
+ - It is accessed
+
+ POSTCONDITIONS
+ * Pulled out tables have JOIN_TAB::emb_sj_nest == NULL (like the outer
+ tables)
+ * Tables that were not pulled out have JOIN_TAB::emb_sj_nest.
+ * Semi-join nests TABLE_LIST::sj_inner_tables
+
+ This operation is (and should be) performed at each PS execution since
+ tables may become/cease to be constant across PS reexecutions.
+
+ RETURN
+ 0 - OK
+ 1 - Out of memory error
+*/
+
+int pull_out_semijoin_tables(JOIN *join)
+{
+ DBUG_ENTER("pull_out_semijoin_tables");
+ TABLE_LIST **sj_nest, **sj_nest_end;
+
+ /* Try pulling out of the each of the semi-joins */
+ for (sj_nest= join->sj_nests.front(), sj_nest_end= join->sj_nests.back();
+ sj_nest != sj_nest_end; sj_nest++)
+ {
+ /* Action #1: Mark the constant tables to be pulled out */
+ table_map pulled_tables= 0;
+
+ List_iterator<TABLE_LIST> child_li((*sj_nest)->nested_join->join_list);
+ TABLE_LIST *tbl;
+ while ((tbl= child_li++))
+ {
+ if (tbl->table)
+ {
+ tbl->table->reginfo.join_tab->emb_sj_nest= *sj_nest;
+ if (tbl->table->map & join->const_table_map)
+ pulled_tables |= tbl->table->map;
+ }
+ }
+
+ /*
+ Action #2: Find which tables we can pull out based on
+ update_ref_and_keys() data. Note that pulling one table out can allow
+ us to pull out some other tables too.
+ */
+ bool pulled_a_table;
+ do
+ {
+ pulled_a_table= FALSE;
+ child_li.rewind();
+ while ((tbl= child_li++))
+ {
+ if (tbl->table && !(pulled_tables & tbl->table->map))
+ {
+ if (find_eq_ref_candidate(tbl->table, (*sj_nest)->nested_join->used_tables))
+ {
+ pulled_a_table= TRUE;
+ pulled_tables |= tbl->table->map;
+ }
+ }
+ }
+ } while (pulled_a_table);
+
+ child_li.rewind();
+ if ((*sj_nest)->nested_join->used_tables == pulled_tables)
+ {
+ (*sj_nest)->sj_inner_tables= 0;
+ fprintf(stderr, "subq nest removed\n");
+ while ((tbl= child_li++))
+ {
+ if (tbl->table)
+ tbl->table->reginfo.join_tab->emb_sj_nest= NULL;
+ }
+ }
+ else
+ {
+ /* Record the bitmap of inner tables, mark the inner tables */
+ table_map inner_tables=(*sj_nest)->nested_join->used_tables &
+ ~pulled_tables;
+ (*sj_nest)->sj_inner_tables= inner_tables;
+ while ((tbl= child_li++))
+ {
+ if (tbl->table)
+ {
+ if (inner_tables & tbl->table->map)
+ tbl->table->reginfo.join_tab->emb_sj_nest= (*sj_nest);
+ else
+ tbl->table->reginfo.join_tab->emb_sj_nest= NULL;
+ }
+ }
+ }
+ }
+ DBUG_RETURN(0);
+}
+
/*****************************************************************************
Create JOIN_TABS, make a guess about the table types,
Approximate how many records will be used in each table
*****************************************************************************/
+
static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,
TABLE *table,
const key_map *keys,ha_rows limit)
@@ -2320,7 +3270,7 @@
s->embedding_map|= embedding->nested_join->nj_map;
continue;
}
- if (embedding)
+ if (embedding && !(embedding->sj_on_expr && ! embedding->embedding))
{
/* s belongs to a nested join, maybe to several embedded joins */
s->embedding_map= 0;
@@ -2548,7 +3498,7 @@
}
}
} while (join->const_table_map & found_ref && ref_changed);
-
+
/*
Update info on indexes that can be used for search lookups as
reading const tables may has added new sargable predicates.
@@ -2569,6 +3519,12 @@
}
}
+ if (pull_out_semijoin_tables(join))
+ {
+ join->thd->net.report_error= 1;
+ DBUG_RETURN(TRUE);
+ }
+
/* Calc how many (possible) matched records in each table */
for (s=stat ; s < stat_end ; s++)
@@ -2679,7 +3635,7 @@
Field *field;
Item *val; // May be empty if diff constant
uint level;
- uint optimize;
+ uint optimize; // KEY_OPTIMIZE_*
bool eq_func;
/*
If true, the condition this struct represents will not be satisfied
@@ -2689,10 +3645,6 @@
bool *cond_guard; /* See KEYUSE::cond_guard */
} KEY_FIELD;
-/* Values in optimize */
-#define KEY_OPTIMIZE_EXISTS 1
-#define KEY_OPTIMIZE_REF_OR_NULL 2
-
/*
Merge new key definitions to old ones, remove those not used in both
@@ -3406,20 +4358,34 @@
SARGABLE_PARAM **sargables)
{
List_iterator<TABLE_LIST> li(nested_join_table->nested_join->join_list);
+ List_iterator<TABLE_LIST> li2(nested_join_table->nested_join->join_list);
+ bool have_another = FALSE;
table_map tables= 0;
TABLE_LIST *table;
DBUG_ASSERT(nested_join_table->nested_join);
- while ((table= li++))
+ while ((table= li++) || (have_another && (li=li2, have_another=FALSE,
+ (table= li++))))
{
if (table->nested_join)
- add_key_fields_for_nj(join, table, end, and_level, sargables);
+ {
+ if (!table->on_expr)
+ {
+ /* It's a semi-join nest. Walk into it as if it wasn't a nest */
+ have_another= TRUE;
+ li2= li;
+ li= List_iterator<TABLE_LIST>(table->nested_join->join_list);
+ }
+ else
+ add_key_fields_for_nj(join, table, end, and_level, sargables);
+ }
else
if (!table->on_expr)
tables |= table->table->map;
}
- add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
- sargables);
+ if (nested_join_table->on_expr)
+ add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
+ sargables);
}
@@ -3599,6 +4565,7 @@
VOID(set_dynamic(keyuse,(gptr) &key_end,i));
keyuse->elements=i;
}
+ DBUG_EXECUTE("opt", print_keyuse_array(keyuse););
return FALSE;
}
@@ -4151,7 +5118,7 @@
This is because table scans uses index and we would not win
anything by using a table scan.
- A word for word translation of the below if-statement in psergey's
+ A word for word translation of the below if-statement in sergefp's
understanding: we check if we should use table scan if:
(1) The found 'ref' access produces more records than a table scan
(or index scan, or quick select), or 'ref' is more expensive than
@@ -5456,6 +6423,8 @@
join_tab->read_first_record= join_init_read_record;
join_tab->join=join;
join_tab->ref.key_parts= 0;
+ join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL;
+ join_tab->do_firstmatch= NULL;
bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
tmp_table->status=0;
tmp_table->null_row=0;
@@ -5684,8 +6653,11 @@
}
for ( ; embedding ; embedding= embedding->embedding)
{
+ /* Ignore sj-nests: */
+ if (!embedding->on_expr)
+ continue;
NESTED_JOIN *nested_join= embedding->nested_join;
- if (!nested_join->counter)
+ if (!nested_join->counter_)
{
/*
Table tab is the first inner table for nested_join.
@@ -5699,7 +6671,7 @@
}
if (!tab->first_inner)
tab->first_inner= nested_join->first_nested;
- if (++nested_join->counter < nested_join->join_list.elements)
+ if (++nested_join->counter_ < nested_join->join_list.elements)
break;
/* Table tab is the last inner table for nested join. */
nested_join->first_nested->last_inner= tab;
@@ -6055,12 +7027,43 @@
DBUG_RETURN(0);
}
+
+uint make_join_orderinfo(JOIN *join)
+{
+ uint i;
+ for (i=join->const_tables ; i < join->tables ; i++)
+ {
+ JOIN_TAB *tab=join->join_tab+i;
+ TABLE *table=tab->table;
+ if ((table == join->sort_by_table &&
+ (!join->order || join->skip_sort_order ||
+ test_if_skip_sort_order(tab, join->order, join->select_limit,
+ 1))
+ ) ||
+ (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
+ {
+ break;
+ }
+ }
+ return i;
+}
+
+
+/*
+ ...
+ SYNOPSIS
+ make_join_readinfo()
+ join
+ options
+ no_jbuf_after X.
+*/
+
static void
-make_join_readinfo(JOIN *join, ulonglong options)
+make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
{
uint i;
bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
- bool ordered_set= 0;
+ //bool ordered_set= 0;
bool sorted= 1;
DBUG_ENTER("make_join_readinfo");
@@ -6078,6 +7081,7 @@
Code handles sort table that is at any location (not only first after
the const tables) despite the fact that it's currently prohibited.
*/
+ /*
if (!ordered_set &&
(table == join->sort_by_table &&
(!join->order || join->skip_sort_order ||
@@ -6086,6 +7090,7 @@
) ||
(join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
ordered_set= 1;
+ */
tab->sorted= sorted;
sorted= 0; // only first must be sorted
@@ -6163,7 +7168,7 @@
*/
table->status=STATUS_NO_RECORD;
if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) &&
- tab->use_quick != 2 && !tab->first_inner && !ordered_set)
+ tab->use_quick != 2 && !tab->first_inner && i <= no_jbuf_after)
{
if ((options & SELECT_DESCRIBE) ||
!join_init_cache(join->thd,join->join_tab+join->const_tables,
@@ -6458,6 +7463,7 @@
tab->table->file->ha_index_or_rnd_end();
}
}
+ cleanup_sj_tmp_tables(this);//
}
/*
We are not using tables anymore
@@ -8113,24 +9119,29 @@
consider any plan where one of the inner tables is before some of outer
tables.
- IMPLEMENTATION.
+ IMPLEMENTATION
The function is implemented by a recursive procedure. On the recursive
ascent all attributes are calculated, all outer joins that can be
converted are replaced and then all unnecessary braces are removed.
As join list contains join tables in the reverse order sequential
elimination of outer joins does not require extra recursive calls.
+ SEMI-JOIN NOTES
+ Remove all semi-joins that have are within another semi-join (i.e. have
+ an "ancestor" semi-join nest)
+
EXAMPLES
Here is an example of a join query with invalid cross references:
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t3.a LEFT JOIN t3 ON t3.b=t1.b
RETURN VALUE
The new condition, if success
- 0, otherwise
+ 0, otherwise
*/
static COND *
-simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
+simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top,
+ bool in_sj)
{
TABLE_LIST *table;
NESTED_JOIN *nested_join;
@@ -8165,14 +9176,15 @@
the corresponding on expression is added to E.
*/
expr= simplify_joins(join, &nested_join->join_list,
- expr, FALSE);
+ expr, FALSE, in_sj || table->sj_on_expr);
table->on_expr= expr;
if (!table->prep_on_expr)
table->prep_on_expr= expr->copy_andor_structure(join->thd);
}
nested_join->used_tables= (table_map) 0;
nested_join->not_null_tables=(table_map) 0;
- conds= simplify_joins(join, &nested_join->join_list, conds, top);
+ conds= simplify_joins(join, &nested_join->join_list, conds, top,
+ in_sj || table->sj_on_expr);
used_tables= nested_join->used_tables;
not_null_tables= nested_join->not_null_tables;
}
@@ -8200,7 +9212,7 @@
table->outer_join= 0;
if (table->on_expr)
{
- /* Add on expression to the where condition. */
+ /* Add ON expression to the WHERE or upper-level ON condition. */
if (conds)
{
conds= and_conds(conds, table->on_expr);
@@ -8262,12 +9274,23 @@
prev_table= table;
}
- /* Flatten nested joins that can be flattened. */
+ /*
+ Flatten nested joins that can be flattened.
+ no ON expression and not a semi-join => can be flattened.
+ */
li.rewind();
while ((table= li++))
{
nested_join= table->nested_join;
- if (nested_join && !table->on_expr)
+ if (table->sj_on_expr && !in_sj)
+ {
+ /*
+ If this is a semi-join that is not contained within another semi-join,
+ leave it intact (otherwise it is flattened)
+ */
+ join->sj_nests.append(table);
+ }
+ else if (nested_join && !table->on_expr)
{
TABLE_LIST *tbl;
List_iterator<TABLE_LIST> it(nested_join->join_list);
@@ -8319,16 +9342,20 @@
{
/*
It is guaranteed by simplify_joins() function that a nested join
- that has only one child represents a single table VIEW (and the child
- is an underlying table). We don't assign bits to such nested join
- structures because
+ that has only one child is either
+ - a single-table view (the child is the underlying table), or
+ - a single-table semi-join nest
+
+ We don't assign bits to such sj-nests because
1. it is redundant (a "sequence" of one table cannot be interleaved
with anything)
2. we could run out bits in nested_join_map otherwise.
*/
if (nested_join->join_list.elements != 1)
{
- nested_join->nj_map= (nested_join_map) 1 << first_unused++;
+ /* Don't assign bits to sj-nests */
+ if (table->on_expr)
+ nested_join->nj_map= (nested_join_map) 1 << first_unused++;
first_unused= build_bitmap_for_nested_joins(&nested_join->join_list,
first_unused);
}
@@ -8361,7 +9388,7 @@
NESTED_JOIN *nested_join;
if ((nested_join= table->nested_join))
{
- nested_join->counter= 0;
+ nested_join->counter_= 0;
reset_nj_counters(&nested_join->join_list);
}
}
@@ -8481,8 +9508,8 @@
*/
for (;next_emb; next_emb= next_emb->embedding)
{
- next_emb->nested_join->counter++;
- if (next_emb->nested_join->counter == 1)
+ next_emb->nested_join->counter_++;
+ if (next_emb->nested_join->counter_ == 1)
{
/*
next_emb is the first table inside a nested join we've "entered". In
@@ -8493,7 +9520,7 @@
}
if (next_emb->nested_join->join_list.elements !=
- next_emb->nested_join->counter)
+ next_emb->nested_join->counter_)
break;
/*
@@ -8525,9 +9552,14 @@
{
TABLE_LIST *last_emb= last->table->pos_in_table_list->embedding;
JOIN *join= last->join;
- while (last_emb && !(--last_emb->nested_join->counter))
+ while (last_emb)
{
- join->cur_embedding_map &= last_emb->nested_join->nj_map;
+ if (last_emb->on_expr)
+ {
+ if (!(--last_emb->nested_join->counter_))
+ return;
+ join->cur_embedding_map &= last_emb->nested_join->nj_map;
+ }
last_emb= last_emb->embedding;
}
}
@@ -9847,7 +10879,8 @@
share->db_record_offset= 1;
if (share->db_type == myisam_hton)
{
- if (create_myisam_tmp_table(table,param,select_options))
+ if (create_myisam_tmp_table(table, param->keyinfo, param->start_recinfo,
+ ¶m->recinfo, select_options))
goto err;
}
if (open_tmp_table(table))
@@ -9866,6 +10899,326 @@
}
+
+
+/*
+ Create a temporary table to weed out duplicate rowid combinations
+
+ SYNOPSIS
+
+ create_duplicate_weedout_tmp_table()
+ thd
+ uniq_tuple_length_arg
+ SJ_TMP_TABLE
+
+ DESCRIPTION
+ Create a temporary table to weed out duplicate rowid combinations. The
+ table has a single column that is a concatenation of all rowids in the
+ combination.
+
+ Depending on the needed length, there are two cases:
+
+ 1. When the length of the column < max_key_length:
+
+ CREATE TABLE tmp (col VARBINARY(n) NOT NULL, UNIQUE KEY(col));
+
+ 2. Otherwise (not a valid SQL syntax but internally supported):
+
+ CREATE TABLE tmp (col VARBINARY NOT NULL, UNIQUE CONSTRAINT(col));
+
+ The code in this function was produced by extraction of relevant parts
+ from create_tmp_table().
+
+ RETURN
+ created table
+ NULL on error
+*/
+
+TABLE *create_duplicate_weedout_tmp_table(THD *thd,
+ uint uniq_tuple_length_arg,
+ SJ_TMP_TABLE *sjtbl)
+{
+ MEM_ROOT *mem_root_save, own_root;
+ TABLE *table;
+ TABLE_SHARE *share;
+ uint temp_pool_slot=MY_BIT_NONE;
+ char *tmpname,path[FN_REFLEN];
+ Field **reg_field;
+ KEY_PART_INFO *key_part_info;
+ KEY *keyinfo;
+ byte *group_buff;
+ byte *bitmaps;
+ uint *blob_field;
+ MI_COLUMNDEF *recinfo, *start_recinfo;
+ bool using_unique_constraint=FALSE;
+ bool use_packed_rows= FALSE;
+ Field *field, *key_field;
+ uint blob_count, null_pack_length, null_count;
+ uchar *null_flags;
+ byte *pos;
+ DBUG_ENTER("create_duplicate_weedout_tmp_table");
+
+ /*
+ STEP 1: Get temporary table name
+ */
+ statistic_increment(thd->status_var.created_tmp_tables, &LOCK_status);
+ if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES))
+ temp_pool_slot = bitmap_lock_set_next(&temp_pool);
+
+ if (temp_pool_slot != MY_BIT_NONE) // we got a slot
+ sprintf(path, "%s_%lx_%i", tmp_file_prefix,
+ current_pid, temp_pool_slot);
+ else
+ {
+ /* if we run out of slots or we are not using tempool */
+ sprintf(path,"%s%lx_%lx_%x", tmp_file_prefix,current_pid,
+ thd->thread_id, thd->tmp_table++);
+ }
+ fn_format(path, path, mysql_tmpdir, "", MY_REPLACE_EXT|MY_UNPACK_FILENAME);
+
+ /* STEP 2: Figure if we'll be using a key or blob+constraint */
+ if (uniq_tuple_length_arg >= CONVERT_IF_BIGGER_TO_BLOB)
+ using_unique_constraint= TRUE;
+
+ /* STEP 3: Allocate memory for temptable description */
+ init_sql_alloc(&own_root, TABLE_ALLOC_BLOCK_SIZE, 0);
+ if (!multi_alloc_root(&own_root,
+ &table, sizeof(*table),
+ &share, sizeof(*share),
+ ®_field, sizeof(Field*) * (1+1),
+ &blob_field, sizeof(uint)*2,
+ &keyinfo, sizeof(*keyinfo),
+ &key_part_info, sizeof(*key_part_info) * 2,
+ &start_recinfo,
+ sizeof(*recinfo)*(1*2+4),
+ &tmpname, (uint) strlen(path)+1,
+ &group_buff, (!using_unique_constraint ?
+ uniq_tuple_length_arg : 0),
+ &bitmaps, bitmap_buffer_size(1)*2,
+ NullS))
+ {
+ if (temp_pool_slot != MY_BIT_NONE)
+ bitmap_lock_clear_bit(&temp_pool, temp_pool_slot);
+ DBUG_RETURN(NULL);
+ }
+ strmov(tmpname,path);
+
+
+ /* STEP 4: Create TABLE description */
+ bzero((char*) table,sizeof(*table));
+ bzero((char*) reg_field,sizeof(Field*)*2);
+
+ table->mem_root= own_root;
+ mem_root_save= thd->mem_root;
+ thd->mem_root= &table->mem_root;
+
+ table->field=reg_field;
+ table->alias= "weedout-tmp";
+ table->reginfo.lock_type=TL_WRITE; /* Will be updated */
+ table->db_stat=HA_OPEN_KEYFILE+HA_OPEN_RNDFILE;
+ table->map=1;
+ table->temp_pool_slot = temp_pool_slot;
+ table->copy_blobs= 1;
+ table->in_use= thd;
+ table->quick_keys.init();
+ table->used_keys.init();
+ table->keys_in_use_for_query.init();
+
+ table->s= share;
+ init_tmp_table_share(share, "", 0, tmpname, tmpname);
+ share->blob_field= blob_field;
+ share->blob_ptr_size= mi_portable_sizeof_char_ptr;
+ share->db_low_byte_first=1; // True for HEAP and MyISAM
+ share->table_charset= NULL;
+ share->primary_key= MAX_KEY; // Indicate no primary key
+ share->keys_for_keyread.init();
+ share->keys_in_use.init();
+
+ blob_count= 0;
+
+ /* Create the field */
+ {
+ /*
+ For the sake of uniformity, always use Field_varstring (altough we could
+ use Field_string for shorter keys)
+ */
+ field= new Field_varstring(uniq_tuple_length_arg, FALSE, "rowids", share,
+ &my_charset_bin);
+ if (!field)
+ DBUG_RETURN(0);
+ field->table= table;
+ field->key_start.init(0);
+ field->part_of_key.init(0);
+ field->part_of_sortkey.init(0);
+ field->unireg_check= Field::NONE;
+ field->flags= (NOT_NULL_FLAG | BINARY_FLAG | NO_DEFAULT_VALUE_FLAG);
+ field->reset_fields();
+ field->init(table);
+ field->orig_table= NULL;
+
+ field->field_index= 0;
+
+ *(reg_field++)= field;
+ *blob_field= 0;
+ *reg_field= 0;
+
+ share->fields= 1;
+ share->blob_fields= 0;
+ }
+
+ uint reclength= field->pack_length();
+ if (using_unique_constraint)
+ {
+ table->file= get_new_handler(share, &table->mem_root,
+ share->db_type= myisam_hton);
+ DBUG_ASSERT(uniq_tuple_length_arg <= table->file->max_key_length());
+ }
+ else
+ {
+ table->file= get_new_handler(share, &table->mem_root,
+ share->db_type= heap_hton);
+ }
+ if (!table->file)
+ goto err;
+
+ null_count=1;
+
+ null_pack_length= 1;
+ reclength += null_pack_length;
+
+ share->reclength= reclength;
+ {
+ uint alloc_length=ALIGN_SIZE(share->reclength + MI_UNIQUE_HASH_LENGTH+1);
+ share->rec_buff_length= alloc_length;
+ if (!(table->record[0]= (byte*)
+ alloc_root(&table->mem_root, alloc_length*3)))
+ goto err;
+ table->record[1]= table->record[0]+alloc_length;
+ share->default_values= table->record[1]+alloc_length;
+ }
+ setup_tmp_table_column_bitmaps(table, bitmaps);
+
+ recinfo= start_recinfo;
+ null_flags=(uchar*) table->record[0];
+ pos=table->record[0]+ null_pack_length;
+ if (null_pack_length)
+ {
+ bzero((byte*) recinfo,sizeof(*recinfo));
+ recinfo->type=FIELD_NORMAL;
+ recinfo->length=null_pack_length;
+ recinfo++;
+ bfill(null_flags,null_pack_length,255); // Set null fields
+
+ table->null_flags= (uchar*) table->record[0];
+ share->null_fields= null_count;
+ share->null_bytes= null_pack_length;
+ }
+ null_count=1;
+
+ {
+ //Field *field= *reg_field;
+ uint length;
+ bzero((byte*) recinfo,sizeof(*recinfo));
+ field->move_field((char*) pos,(uchar*) 0,0);
+
+ field->reset();
+ /*
+ Test if there is a default field value. The test for ->ptr is to skip
+ 'offset' fields generated by initalize_tables
+ */
+ // Initialize the table field:
+ bzero(field->ptr, field->pack_length());
+
+ length=field->pack_length();
+ pos+= length;
+
+ /* Make entry for create table */
+ recinfo->length=length;
+ if (field->flags & BLOB_FLAG)
+ recinfo->type= (int) FIELD_BLOB;
+ else if (use_packed_rows &&
+ field->real_type() == MYSQL_TYPE_STRING &&
+ length >= MIN_STRING_LENGTH_TO_PACK_ROWS)
+ recinfo->type=FIELD_SKIP_ENDSPACE;
+ else
+ recinfo->type=FIELD_NORMAL;
+
+ field->table_name= &table->alias;
+ }
+
+ //param->recinfo=recinfo;
+ //store_record(table,s->default_values); // Make empty default record
+
+ if (thd->variables.tmp_table_size == ~ (ulonglong) 0) // No limit
+ share->max_rows= ~(ha_rows) 0;
+ else
+ share->max_rows= (ha_rows) (((share->db_type == heap_hton) ?
+ min(thd->variables.tmp_table_size,
+ thd->variables.max_heap_table_size) :
+ thd->variables.tmp_table_size) /
+ share->reclength);
+ set_if_bigger(share->max_rows,1); // For dummy start options
+
+
+ //// keyinfo= param->keyinfo;
+ if (TRUE)
+ {
+ DBUG_PRINT("info",("Creating group key in temporary table"));
+ share->keys=1;
+ share->uniques= test(using_unique_constraint);
+ table->key_info=keyinfo;
+ keyinfo->key_part=key_part_info;
+ keyinfo->flags=HA_NOSAME;
+ keyinfo->usable_key_parts= keyinfo->key_parts= 1;
+ keyinfo->key_length=0;
+ keyinfo->rec_per_key=0;
+ keyinfo->algorithm= HA_KEY_ALG_UNDEF;
+ keyinfo->name= (char*) "weedout_key";
+ {
+ key_part_info->null_bit=0;
+ key_part_info->field= field;
+ key_part_info->offset= field->offset(table->record[0]);
+ key_part_info->length= (uint16) field->key_length();
+ key_part_info->type= (uint8) field->key_type();
+ key_part_info->key_type = FIELDFLAG_BINARY;
+ if (!using_unique_constraint)
+ {
+ if (!(key_field= field->new_key_field(thd->mem_root, table,
+ (char*) group_buff,
+ field->null_ptr,
+ field->null_bit)))
+ goto err;
+ key_part_info->key_part_flag|= HA_END_SPACE_ARE_EQUAL; //todo need this?
+ }
+ keyinfo->key_length+= key_part_info->length;
+ }
+ }
+
+ if (thd->is_fatal_error) // If end of memory
+ goto err;
+ share->db_record_offset= 1;
+ if (share->db_type == myisam_hton)
+ {
+ recinfo++;
+ if (create_myisam_tmp_table(table, keyinfo, start_recinfo, &recinfo, 0))
+ goto err;
+ }
+ sjtbl->start_recinfo= start_recinfo;
+ sjtbl->recinfo= recinfo;
+ if (open_tmp_table(table))
+ goto err;
+
+ thd->mem_root= mem_root_save;
+ DBUG_RETURN(table);
+
+err:
+ thd->mem_root= mem_root_save;
+ free_tmp_table(thd,table); /* purecov: inspected */
+ if (temp_pool_slot != MY_BIT_NONE)
+ bitmap_lock_clear_bit(&temp_pool, temp_pool_slot);
+ DBUG_RETURN(NULL); /* purecov: inspected */
+}
+
/****************************************************************************/
/*
@@ -10012,13 +11365,44 @@
}
-static bool create_myisam_tmp_table(TABLE *table,TMP_TABLE_PARAM *param,
+/*
+ Create MyISAM temporary table
+
+ SYNOPSIS
+ create_myisam_tmp_table()
+ table Table object that descrimes the table to be created
+ keyinfo Description of the index (there is always one index)
+ start_recinfo MyISAM's column descriptions
+ recinfo INOUT End of MyISAM's column descriptions
+ options Option bits
+
+ DESCRIPTION
+ Create a MyISAM temporary table according to passed description. The is
+ assumed to have one unique index or constraint.
+
+ The passed array or MI_COLUMNDEF structures must have this form:
+
+ 1. 1-byte column (afaiu for 'deleted' flag) (note maybe not 1-byte
+ when there are many nullable columns)
+ 2. Table columns
+ 3. One free MI_COLUMNDEF element (*recinfo points here)
+
+ This function may use the free element to create hash column for unique
+ constraint.
+
+ RETURN
+ FALSE - OK
+ TRUE - Error
+*/
+
+static bool create_myisam_tmp_table(TABLE *table, KEY *keyinfo,
+ MI_COLUMNDEF *start_recinfo,
+ MI_COLUMNDEF **recinfo,
ulonglong options)
{
int error;
MI_KEYDEF keydef;
MI_UNIQUEDEF uniquedef;
- KEY *keyinfo=param->keyinfo;
TABLE_SHARE *share= table->s;
DBUG_ENTER("create_myisam_tmp_table");
@@ -10045,10 +11429,10 @@
uniquedef.null_are_equal=1;
/* Create extra column for hash value */
- bzero((byte*) param->recinfo,sizeof(*param->recinfo));
- param->recinfo->type= FIELD_CHECK;
- param->recinfo->length=MI_UNIQUE_HASH_LENGTH;
- param->recinfo++;
+ bzero((byte*) *recinfo,sizeof(**recinfo));
+ (*recinfo)->type= FIELD_CHECK;
+ (*recinfo)->length=MI_UNIQUE_HASH_LENGTH;
+ (*recinfo)++;
share->reclength+=MI_UNIQUE_HASH_LENGTH;
}
else
@@ -10105,8 +11489,8 @@
create_info.data_file_length= ~(ulonglong) 0;
if ((error=mi_create(share->table_name.str, share->keys, &keydef,
- (uint) (param->recinfo-param->start_recinfo),
- param->start_recinfo,
+ (uint) (*recinfo-start_recinfo),
+ start_recinfo,
share->uniques, &uniquedef,
&create_info,
HA_CREATE_TMP_TABLE)))
@@ -10162,7 +11546,9 @@
* If a HEAP table gets full, create a MyISAM table and copy all rows to this
*/
-bool create_myisam_from_heap(THD *thd, TABLE *table, TMP_TABLE_PARAM *param,
+bool create_myisam_from_heap(THD *thd, TABLE *table,
+ MI_COLUMNDEF *start_recinfo,
+ MI_COLUMNDEF **recinfo,
int error, bool ignore_last_dupp_key_error)
{
TABLE new_table;
@@ -10188,8 +11574,9 @@
save_proc_info=thd->proc_info;
THD_SET_PROC_INFO(thd, "converting HEAP to MyISAM");
- if (create_myisam_tmp_table(&new_table, param,
- thd->lex->select_lex.options | thd->options))
+ if (create_myisam_tmp_table(&new_table, table->key_info, start_recinfo,
+ recinfo, thd->lex->select_lex.options |
+ thd->options))
goto err2;
if (open_tmp_table(&new_table))
goto err1;
@@ -10485,6 +11872,7 @@
return rc;
}
+
/*
Retrieve records ends with a given beginning from the result of a join
@@ -10596,6 +11984,7 @@
RETURN
return one of enum_nested_loop_state, except NESTED_LOOP_NO_MORE_ROWS.
*/
+int do_sj_reset(SJ_TMP_TABLE *sj_tbl);
enum_nested_loop_state
sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
@@ -10609,6 +11998,11 @@
my_bool *report_error= &(join->thd->net.report_error);
READ_RECORD *info= &join_tab->read_record;
+ if (join_tab->flush_weedout_table)
+ {
+ do_sj_reset(join_tab->flush_weedout_table);
+ }
+
if (join->resume_nested_loop)
{
/* If not the last table, plunge down the nested loop */
@@ -10640,8 +12034,12 @@
error= (*join_tab->read_first_record)(join_tab);
rc= evaluate_join_record(join, join_tab, error, report_error);
}
-
- while (rc == NESTED_LOOP_OK)
+
+ /*
+ Note: psergey has added the 2nd part of the following condition; the
+ change should probably be made in 5.1, too.
+ */
+ while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab)
{
error= info->read_record(info);
rc= evaluate_join_record(join, join_tab, error, report_error);
@@ -10658,6 +12056,90 @@
/*
+ SemiJoinDuplicateElimination: Weed out duplicate row combinations
+
+ SYNPOSIS
+ do_sj_dups_weedout()
+
+ RETURN
+ -1 Error
+ 1 The row combination is a duplicate (discard it)
+ 0 The row combination is not a duplicate (continue)
+*/
+
+int do_sj_dups_weedout(THD *thd, SJ_TMP_TABLE *sjtbl)
+{
+ int error;
+ SJ_TMP_TABLE::TAB *tab= sjtbl->tabs;
+ SJ_TMP_TABLE::TAB *tab_end= sjtbl->tabs_end;
+ byte *ptr= sjtbl->tmp_table->record[0] + 1;
+
+ /* Put the the rowids tuple into table->record[0]: */
+
+ // 1. Store the length
+ if (((Field_varstring*)(sjtbl->tmp_table->field[0]))->length_bytes == 1)
+ {
+ *ptr= (uchar)(sjtbl->rowid_len + sjtbl->null_bytes);
+ ptr++;
+ }
+ else
+ {
+ int2store(ptr, sjtbl->rowid_len + sjtbl->null_bytes);
+ ptr += 2;
+ }
+
+ // 2. Zero the length
+ if (sjtbl->null_bytes)
+ {
+ bzero(ptr, sjtbl->null_bytes);
+ ptr += sjtbl->null_bytes;
+ }
+
+ // 3. Put the rowids
+ for (uint i=0; tab != tab_end; tab++, i++)
+ {
+ handler *h= tab->join_tab->table->file;
+ if (tab->join_tab->table->maybe_null && tab->join_tab->table->null_row)
+ {
+ /* It's a NULL-complemented row */
+ *(tab->null_addr) |= tab->null_bit;
+ bzero(ptr + tab->rowid_offset, h->ref_length);
+ }
+ else
+ {
+ /* Copy the rowid value */
+ if (tab->join_tab->rowid_keep_flags & JOIN_TAB::CALL_POSITION)
+ h->position(tab->join_tab->table->record[0]);
+ memcpy(ptr + tab->rowid_offset, h->ref, h->ref_length);
+ }
+ }
+
+ if ((error= sjtbl->tmp_table->file->write_row(sjtbl->tmp_table->record[0])))
+ {
+ /* create_myisam_from_heap will generate error if needed */
+ if (sjtbl->tmp_table->file->is_fatal_error(error, HA_CHECK_DUP) &&
+ create_myisam_from_heap(thd, sjtbl->tmp_table, sjtbl->start_recinfo,
+ &sjtbl->recinfo, error, 1))
+ return -1;
+ //return (error == HA_ERR_FOUND_DUPP_KEY || error== HA_ERR_FOUND_DUPP_UNIQUE) ? 1: -1;
+ return 1;
+ }
+ return 0;
+}
+
+
+/*
+ SemiJoinDuplicateElimination: Reset the temporary table
+*/
+
+int do_sj_reset(SJ_TMP_TABLE *sj_tbl)
+{
+ if (sj_tbl->tmp_table)
+ return sj_tbl->tmp_table->file->delete_all_rows();
+ return 0;
+}
+
+/*
Process one record of the nested loop join.
DESCRIPTION
@@ -10739,6 +12221,24 @@
join_tab->first_unmatched= first_unmatched;
}
+ JOIN_TAB *return_tab= join->return_tab;
+ if (join_tab->check_weed_out_table)
+ {
+ int res= do_sj_dups_weedout(join->thd, join_tab->check_weed_out_table);
+ if (res == -1)
+ return NESTED_LOOP_ERROR;
+ if (res == 1)
+ return NESTED_LOOP_OK;
+ }
+ else if (join_tab->do_firstmatch)
+ {
+ /*
+ We should return to the join_tab->do_firstmatch after we have
+ enumerated all the suffixes for current prefix row combination
+ */
+ return_tab= join_tab->do_firstmatch;
+ }
+
/*
It was not just a return to lower loop level when one
of the newly activated predicates is evaluated as false
@@ -10758,6 +12258,9 @@
rc= (*join_tab->next_select)(join, join_tab+1, 0);
if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
return rc;
+ if (return_tab < join->return_tab)
+ join->return_tab= return_tab;
+
if (join->return_tab < join_tab)
return NESTED_LOOP_OK;
/*
@@ -10903,12 +12406,19 @@
read_cached_record(join_tab);
if (!select || !select->skip_record())
{
- rc= (join_tab->next_select)(join,join_tab+1,0);
- if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
+ int res= 0;
+ if (!join_tab->check_weed_out_table ||
+ !(res= do_sj_dups_weedout(join->thd, join_tab->check_weed_out_table)))
{
- reset_cache_write(&join_tab->cache);
- return rc;
+ rc= (join_tab->next_select)(join,join_tab+1,0);
+ if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
+ {
+ reset_cache_write(&join_tab->cache);
+ return rc;
+ }
}
+ if (res == -1)
+ return NESTED_LOOP_ERROR;
}
}
}
@@ -11682,8 +13192,10 @@
{
if (!table->file->is_fatal_error(error, HA_CHECK_DUP))
goto end;
- if (create_myisam_from_heap(join->thd, table, &join->tmp_table_param,
- error,1))
+ if (create_myisam_from_heap(join->thd, table,
+ join->tmp_table_param.start_recinfo,
+ &join->tmp_table_param.recinfo,
+ error, 1))
DBUG_RETURN(NESTED_LOOP_ERROR); // Not a table_is_full error
table->s->uniques=0; // To ensure rows are the same
}
@@ -11765,7 +13277,9 @@
copy_funcs(join->tmp_table_param.items_to_copy);
if ((error=table->file->write_row(table->record[0])))
{
- if (create_myisam_from_heap(join->thd, table, &join->tmp_table_param,
+ if (create_myisam_from_heap(join->thd, table,
+ join->tmp_table_param.start_recinfo,
+ &join->tmp_table_param.recinfo,
error, 0))
DBUG_RETURN(NESTED_LOOP_ERROR); // Not a table_is_full error
/* Change method to update rows */
@@ -11861,7 +13375,8 @@
{
int error= table->file->write_row(table->record[0]);
if (error && create_myisam_from_heap(join->thd, table,
- &join->tmp_table_param,
+ join->tmp_table_param.start_recinfo,
+ &join->tmp_table_param.recinfo,
error, 0))
DBUG_RETURN(NESTED_LOOP_ERROR);
}
@@ -13109,6 +14624,13 @@
calc_used_field_length(thd, join_tab);
cache->fields+=join_tab->used_fields;
blobs+=join_tab->used_blobs;
+
+ /* SemiJoinDuplicateElimination: reserve space for rowid */
+ if (join_tab->rowid_keep_flags & JOIN_TAB::KEEP_ROWID)
+ {
+ cache->fields++;
+ join_tab->used_fieldlength += join_tab->table->file->ref_length;
+ }
}
if (!(cache->field=(CACHE_FIELD*)
sql_alloc(sizeof(CACHE_FIELD)*(cache->fields+table_count*2)+(blobs+1)*
@@ -13142,6 +14664,7 @@
(*blob_ptr++)=copy;
if (field->maybe_null())
null_fields++;
+ copy->get_rowid= NULL;
copy++;
}
}
@@ -13152,6 +14675,7 @@
copy->length= tables[i].table->s->null_bytes;
copy->strip=0;
copy->blob_field=0;
+ copy->get_rowid= NULL;
length+=copy->length;
copy++;
cache->fields++;
@@ -13163,10 +14687,28 @@
copy->length=sizeof(tables[i].table->null_row);
copy->strip=0;
copy->blob_field=0;
+ copy->get_rowid= NULL;
length+=copy->length;
copy++;
cache->fields++;
}
+ /* SemiJoinDuplicateElimination: Allocate space for rowid if needed */
+ if (tables[i].rowid_keep_flags & JOIN_TAB::KEEP_ROWID)
+ {
+ copy->str= (char*)tables[i].table->file->ref;
+ copy->length= tables[i].table->file->ref_length;
+ copy->strip=0;
+ copy->blob_field=0;
+ copy->get_rowid= NULL;
+ if (tables[i].rowid_keep_flags & JOIN_TAB::CALL_POSITION)
+ {
+ /* We will need to call h->position(): */
+ copy->get_rowid= tables[i].table;
+ /* And those after us won't have to: */
+ tables[i].rowid_keep_flags &= ~((int)JOIN_TAB::CALL_POSITION);
+ }
+ copy++;
+ }
}
cache->length=length+blobs*sizeof(char*);
@@ -13211,7 +14753,6 @@
length+=used_blob_length(cache->blob_ptr);
if ((last_record=(length+cache->length > (uint) (cache->end - pos))))
cache->ptr_record=cache->records;
-
/*
There is room in cache. Put record there
*/
@@ -13236,6 +14777,10 @@
}
else
{
+ // SemiJoinDuplicateElimination: Get the rowid into table->ref:
+ if (copy->get_rowid)
+ copy->get_rowid->file->position(copy->get_rowid->record[0]);
+
if (copy->strip)
{
char *str,*end;
@@ -13285,7 +14830,6 @@
last_record=tab->cache.record_nr++ == tab->cache.ptr_record;
pos=tab->cache.pos;
-
for (copy=tab->cache.field,end_field=copy+tab->cache.fields ;
copy < end_field;
copy++)
@@ -14950,7 +16494,9 @@
copy_sum_funcs(sum_funcs_end[i+1], sum_funcs_end[i]);
if ((write_error= table_arg->file->write_row(table_arg->record[0])))
{
- if (create_myisam_from_heap(thd, table_arg, &tmp_table_param,
+ if (create_myisam_from_heap(thd, table_arg,
+ tmp_table_param.start_recinfo,
+ &tmp_table_param.recinfo,
write_error, 0))
return 1;
}
@@ -14988,6 +16534,7 @@
Send a description about what how the select will be done to stdout
****************************************************************************/
+
static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
bool distinct,const char *message)
{
@@ -15282,92 +16829,106 @@
if (tab->info)
item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
- else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+ else
{
- if (tab->packed_info & TAB_INFO_USING_INDEX)
- extra.append(STRING_WITH_LEN("; Using index"));
- if (tab->packed_info & TAB_INFO_USING_WHERE)
- extra.append(STRING_WITH_LEN("; Using where"));
- if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
- extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
- /* Skip initial "; "*/
- const char *str= extra.ptr();
- uint32 len= extra.length();
- if (len)
+ if (tab->packed_info & TAB_INFO_HAVE_VALUE)
{
- str += 2;
- len -= 2;
+ if (tab->packed_info & TAB_INFO_USING_INDEX)
+ extra.append(STRING_WITH_LEN("; Using index"));
+ if (tab->packed_info & TAB_INFO_USING_WHERE)
+ extra.append(STRING_WITH_LEN("; Using where"));
+ if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+ extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
}
- item_list.push_back(new Item_string(str, len, cs));
- }
- else
- {
- if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
+ else
{
- extra.append(STRING_WITH_LEN("; Using "));
- tab->select->quick->add_info_string(&extra);
- }
- if (tab->select)
- {
- if (tab->use_quick == 2)
- {
- char buf[MAX_KEY/8+1];
- extra.append(STRING_WITH_LEN("; Range checked for each "
- "record (index map: 0x"));
- extra.append(tab->keys.print(buf));
- extra.append(')');
- }
- else if (tab->select->cond)
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
+ quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
{
- const COND *pushed_cond= tab->table->file->pushed_cond;
-
- if (thd->variables.engine_condition_pushdown && pushed_cond)
+ extra.append(STRING_WITH_LEN("; Using "));
+ tab->select->quick->add_info_string(&extra);
+ }
+ if (tab->select)
+ {
+ if (tab->use_quick == 2)
+ {
+ char buf[MAX_KEY/8+1];
+ extra.append(STRING_WITH_LEN("; Range checked for each "
+ "record (index map: 0x"));
+ extra.append(tab->keys.print(buf));
+ extra.append(')');
+ }
+ else if (tab->select->cond)
{
- extra.append(STRING_WITH_LEN("; Using where with pushed "
- "condition"));
- if (thd->lex->describe & DESCRIBE_EXTENDED)
+ const COND *pushed_cond= tab->table->file->pushed_cond;
+
+ if (thd->variables.engine_condition_pushdown && pushed_cond)
{
- extra.append(STRING_WITH_LEN(": "));
- ((COND *)pushed_cond)->print(&extra);
+ extra.append(STRING_WITH_LEN("; Using where with pushed "
+ "condition"));
+ if (thd->lex->describe & DESCRIBE_EXTENDED)
+ {
+ extra.append(STRING_WITH_LEN(": "));
+ ((COND *)pushed_cond)->print(&extra);
+ }
}
+ else
+ extra.append(STRING_WITH_LEN("; Using where"));
}
+ }
+ if (key_read)
+ {
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
+ extra.append(STRING_WITH_LEN("; Using index for group-by"));
else
- extra.append(STRING_WITH_LEN("; Using where"));
+ extra.append(STRING_WITH_LEN("; Using index"));
}
- }
- if (key_read)
- {
- if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
- extra.append(STRING_WITH_LEN("; Using index for group-by"));
- else
- extra.append(STRING_WITH_LEN("; Using index"));
- }
- if (table->reginfo.not_exists_optimize)
- extra.append(STRING_WITH_LEN("; Not exists"));
- if (need_tmp_table)
- {
- need_tmp_table=0;
- extra.append(STRING_WITH_LEN("; Using temporary"));
- }
- if (need_order)
- {
- need_order=0;
- extra.append(STRING_WITH_LEN("; Using filesort"));
- }
- if (distinct & test_all_bits(used_tables,thd->used_tables))
- extra.append(STRING_WITH_LEN("; Distinct"));
+ if (table->reginfo.not_exists_optimize)
+ extra.append(STRING_WITH_LEN("; Not exists"));
+ if (need_tmp_table)
+ {
+ need_tmp_table=0;
+ extra.append(STRING_WITH_LEN("; Using temporary"));
+ }
+ if (need_order)
+ {
+ need_order=0;
+ extra.append(STRING_WITH_LEN("; Using filesort"));
+ }
+ if (distinct & test_all_bits(used_tables,thd->used_tables))
+ extra.append(STRING_WITH_LEN("; Distinct"));
- for (uint part= 0; part < tab->ref.key_parts; part++)
+ for (uint part= 0; part < tab->ref.key_parts; part++)
+ {
+ if (tab->ref.cond_guards[part])
+ {
+ extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+ break;
+ }
+ }
+ }
+ if (tab->flush_weedout_table)
+ extra.append(STRING_WITH_LEN("; Start temporary"));
+ else if (tab->check_weed_out_table)
+ extra.append(STRING_WITH_LEN("; End temporary"));
+ else if (tab->do_firstmatch)
{
- if (tab->ref.cond_guards[part])
+ extra.append(STRING_WITH_LEN("; FirstMatch("));
+ TABLE *prev_table=tab->do_firstmatch->table;
+ if (prev_table->derived_select_number)
{
- extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
- break;
+ char namebuf[NAME_LEN];
+ /* Derived table name generation */
+ int len= my_snprintf(namebuf, sizeof(namebuf)-1,
+ "<derived%u>",
+ prev_table->derived_select_number);
+ extra.append(namebuf, len);
}
+ else
+ extra.append(prev_table->pos_in_table_list->alias);
+ extra.append(STRING_WITH_LEN(")"));
}
-
/* Skip initial "; "*/
const char *str= extra.ptr();
uint32 len= extra.length();
@@ -15376,7 +16937,8 @@
str += 2;
len -= 2;
}
- item_list.push_back(new Item_string(str, len, cs));
+ item_list.push_back(new Item_string(str, len, cs));
+
}
// For next iteration
used_tables|=table->map;
@@ -15453,6 +17015,36 @@
}
+static void print_table_array(THD *thd, String *str, TABLE_LIST **table,
+ TABLE_LIST **end)
+{
+ (*table)->print(thd, str);
+
+ for (TABLE_LIST **tbl= table + 1; tbl < end; tbl++)
+ {
+ TABLE_LIST *curr= *tbl;
+ if (curr->outer_join)
+ {
+ /* MySQL converts right to left joins */
+ str->append(STRING_WITH_LEN(" left join "));
+ }
+ else if (curr->straight)
+ str->append(STRING_WITH_LEN(" straight_join "));
+ else if (curr->sj_inner_tables)
+ str->append(STRING_WITH_LEN(" semi join "));
+ else
+ str->append(STRING_WITH_LEN(" join "));
+ curr->print(thd, str);
+ if (curr->on_expr)
+ {
+ str->append(STRING_WITH_LEN(" on("));
+ curr->on_expr->print(str);
+ str->append(')');
+ }
+ }
+}
+
+
/*
Print joins from the FROM clause
@@ -15474,31 +17066,27 @@
for (TABLE_LIST **t= table + (tables->elements - 1); t >= table; t--)
*t= ti++;
-
- DBUG_ASSERT(tables->elements >= 1);
- (*table)->print(thd, str);
-
- TABLE_LIST **end= table + tables->elements;
- for (TABLE_LIST **tbl= table + 1; tbl < end; tbl++)
+
+ /*
+ If the first table is a semi-join nest, swap it with something that is
+ not a semi-join nest.
+ */
+ if ((*table)->sj_inner_tables)
{
- TABLE_LIST *curr= *tbl;
- if (curr->outer_join)
- {
- /* MySQL converts right to left joins */
- str->append(STRING_WITH_LEN(" left join "));
- }
- else if (curr->straight)
- str->append(STRING_WITH_LEN(" straight_join "));
- else
- str->append(STRING_WITH_LEN(" join "));
- curr->print(thd, str);
- if (curr->on_expr)
+ TABLE_LIST **end= table + tables->elements;
+ for (TABLE_LIST **t2= table; t2!=end; t2++)
{
- str->append(STRING_WITH_LEN(" on("));
- curr->on_expr->print(str);
- str->append(')');
+ if (!(*t2)->sj_inner_tables)
+ {
+ TABLE_LIST *tmp= *t2;
+ *t2= *table;
+ *table= tmp;
+ break;
+ }
}
}
+ DBUG_ASSERT(tables->elements >= 1);
+ print_table_array(thd, str, table, table + tables->elements);
}
--- 1.117/sql/sql_select.h 2007-05-22 23:53:55 +04:00
+++ 1.118/sql/sql_select.h 2007-05-22 23:53:55 +04:00
@@ -23,11 +23,16 @@
#include "procedure.h"
#include <myisam.h>
+/* Values in optimize */
+#define KEY_OPTIMIZE_EXISTS 1
+#define KEY_OPTIMIZE_REF_OR_NULL 2
+
typedef struct keyuse_t {
TABLE *table;
Item *val; /* or value if no field */
table_map used_tables;
- uint key, keypart, optimize;
+ uint key, keypart;
+ uint optimize; // 0, or KEY_OPTIMIZE_*
key_part_map keypart_map;
ha_rows ref_table_rows;
/*
@@ -89,17 +94,40 @@
*/
typedef struct st_cache_field {
+ /*
+ Where source data is located (i.e. this points to somewhere in
+ tableX->record[0])
+ */
char *str;
- uint length,blob_length;
+ uint length; /* Length of data at *str, in bytes */
+ uint blob_length; /* Valid IFF blob_field != 0 */
Field_blob *blob_field;
- bool strip;
+ bool strip; /* TRUE <=> Strip endspaces ?? */
+
+ TABLE *get_rowid; /* _ != NULL <=> */
} 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;
+typedef struct st_join_cache
+{
+ uchar *buff;
+ uchar *pos; /* Start of free space in the buffer */
+ uchar *end;
+ uint records; /* # of row cominations currently stored in the cache */
+ uint record_nr;
+ uint ptr_record;
+ /*
+ Number of fields (i.e. cache_field objects). Those correspond to table
+ columns, and there are also special fields for
+ - table's column null bits
+ - table's null-complementation byte
+ - [new] table's rowid.
+ */
+ uint fields;
+ uint length;
+ uint blobs;
+ CACHE_FIELD *field;
+ CACHE_FIELD **blob_ptr;
SQL_SELECT *select;
} JOIN_CACHE;
@@ -127,6 +155,8 @@
#define TAB_INFO_USING_WHERE 4
#define TAB_INFO_FULL_SCAN_ON_NULL 8
+class SJ_TMP_TABLE;
+
typedef enum_nested_loop_state
(*Next_select_func)(JOIN *, struct st_join_table *, bool);
typedef int (*Read_record_func)(struct st_join_table *tab);
@@ -190,9 +220,42 @@
TABLE_REF ref;
JOIN_CACHE cache;
JOIN *join;
- /* Bitmap of nested joins this table is part of */
- nested_join_map embedding_map;
+
+ /* SemiJoinDuplicateElimination variables: */
+ /*
+ Embedding SJ-nest (may be not the direct parent), or NULL if none.
+ This variable holds the result of table pullout.
+ */
+ TABLE_LIST *emb_sj_nest;
+
+ /* Variables to control NL-Join executioner */
+ SJ_TMP_TABLE *flush_weedout_table;
+ SJ_TMP_TABLE *check_weed_out_table;
+ struct st_join_table *do_firstmatch;
+ /*
+ TRUE<=> this tab is covered by SJ dups range (used to disable join
+ buffering) [slated for removal]
+ */
+ bool inside_sj_dups_range;
+ enum {
+ /* If set, the rowid of this table must be put into the temptable. */
+ KEEP_ROWID=1,
+ /*
+ If set, one should call h->position() to obtain the rowid,
+ otherwise, the rowid is assumed to already be in h->ref
+ (this is because join caching and filesort() save the rowid and then
+ put it back into h->ref)
+ */
+ CALL_POSITION=2
+ };
+ /* A set of flags from the above enum */
+ int rowid_keep_flags;
+
+
+ /* NestedOuterJoins: Bitmap of nested joins this table is part of */
+ nested_join_map embedding_map;
+
void cleanup();
inline bool is_using_loose_index_scan()
{
@@ -249,6 +312,54 @@
} ROLLUP;
+/*
+ Describes use of one temporary table to weed out join duplicates.
+ The temporar
+
+ Used to
+ - create a temp table
+ - when we reach the weed-out tab, walk through rowid-ed tabs and
+ and copy rowids.
+ For each table we need
+ - rowid offset
+ - null bit address.
+
+ TODO describe the confluent case of FirstMatch
+ TODO walk through and remove extra members
+*/
+
+class SJ_TMP_TABLE : public Sql_alloc
+{
+public:
+ uint start_idx;
+ uint end_idx;
+
+ /* Array of pointers to tables that should be "used" */
+ class TAB
+ {
+ public:
+ JOIN_TAB *join_tab;
+ uint rowid_offset;
+ byte *null_addr;
+ byte null_bit;
+ };
+ TAB *tabs;
+ TAB *tabs_end;
+
+ uint null_bits;
+ uint null_bytes;
+ uint rowid_len;
+
+ TABLE *tmp_table;
+
+ MI_COLUMNDEF *start_recinfo;
+ MI_COLUMNDEF *recinfo;
+
+ /* Pointer to next table (next->start_idx > this->end_idx) */
+ SJ_TMP_TABLE *next;
+};
+
+
class JOIN :public Sql_alloc
{
JOIN(const JOIN &rhs); /* not implemented */
@@ -258,7 +369,9 @@
JOIN_TAB **map2table; // mapping between table indexes and JOIN_TABs
JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
TABLE **table,**all_tables,*sort_by_table;
- uint tables,const_tables;
+ uint tables; /* Number of tables in the join */
+ uint outer_tables; /* Number of tables that are not inside semijoin */
+ uint const_tables;
uint send_group_parts;
bool sort_and_group,first_record,full_join,group, no_field_update;
bool do_send_rows;
@@ -357,6 +470,13 @@
bool union_part; // this subselect is part of union
bool optimized; // flag to avoid double optimization in EXPLAIN
+
+ //psergey-todo: Those two leak memory atm. Pre-alloc on pool?
+ Dynamic_array<Item_in_subselect*> sj_subselects;
+ Dynamic_array<TABLE_LIST*> sj_nests;
+
+ /* Descriptions of temporary tables used to weed-out semi-join duplicates */
+ SJ_TMP_TABLE *sj_tmp_tables;
/*
storage for caching buffers allocated during query execution.
@@ -429,6 +549,7 @@
tmp_table_param.init();
tmp_table_param.end_write_records= HA_POS_ERROR;
rollup.state= ROLLUP::STATE_NONE;
+ sj_tmp_tables= NULL;
}
int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num,
@@ -441,6 +562,7 @@
int destroy();
void restore_tmp();
bool alloc_func_list();
+ bool flatten_subqueries();
bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields,
bool before_group_by, bool recompute= FALSE);
@@ -509,8 +631,10 @@
uint elements, List<Item> &fields);
void copy_fields(TMP_TABLE_PARAM *param);
void copy_funcs(Item **func_ptr);
-bool create_myisam_from_heap(THD *thd, TABLE *table, TMP_TABLE_PARAM *param,
- int error, bool ignore_last_dupp_error);
+bool create_myisam_from_heap(THD *thd, TABLE *table,
+ MI_COLUMNDEF *start_recinfo,
+ MI_COLUMNDEF **recinfo,
+ int error, bool ignore_last_dupp_key_error);
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
Field* create_tmp_field_from_field(THD *thd, Field* org_field,
const char *name, TABLE *table,
--- 1.393/sql/sql_show.cc 2007-05-22 23:53:55 +04:00
+++ 1.394/sql/sql_show.cc 2007-05-22 23:53:55 +04:00
@@ -2224,9 +2224,10 @@
int error;
if ((error= table->file->ha_write_row(table->record[0])))
{
- if (create_myisam_from_heap(thd, table,
- table->pos_in_table_list->schema_table_param,
- error, 0))
+ TMP_TABLE_PARAM *param= table->pos_in_table_list->schema_table_param;
+
+ if (create_myisam_from_heap(thd, table, param->start_recinfo,
+ ¶m->recinfo, error, 0))
return 1;
}
return 0;
--- 1.51/sql/sql_test.cc 2007-05-22 23:53:55 +04:00
+++ 1.52/sql/sql_test.cc 2007-05-22 23:53:55 +04:00
@@ -206,6 +206,42 @@
DBUG_VOID_RETURN;
}
+#define FT_KEYPART (MAX_REF_PARTS+10)
+
+void print_keyuse(KEYUSE *keyuse)
+{
+ char buff[256];
+ char buf2[64];
+ const char *fieldname;
+ String str(buff,(uint32) sizeof(buff), system_charset_info);
+ str.length(0);
+ keyuse->val->print(&str);
+ str.append('\0');
+ if (keyuse->keypart == FT_KEYPART)
+ fieldname= "FT_KEYPART";
+ else
+ fieldname= keyuse->table->key_info[keyuse->key].key_part[keyuse->keypart].field->field_name;
+ longlong2str(keyuse->used_tables, buf2, 16);
+ DBUG_LOCK_FILE;
+ fprintf(DBUG_FILE, "KEYUSE: %s.%s=%s optimize= %d used_tables=%s "
+ "ref_table_rows= %lu keypart_map= %0lx\n",
+ keyuse->table->alias, fieldname, str.ptr(),
+ keyuse->optimize, buf2, (ulong)keyuse->ref_table_rows,
+ keyuse->keypart_map);
+ DBUG_UNLOCK_FILE;
+ //key_part_map keypart_map; --?? there can be several?
+}
+
+
+void print_keyuse_array(DYNAMIC_ARRAY *keyuse_array)
+{
+ DBUG_LOCK_FILE;
+ fprintf(DBUG_FILE, "KEYUSE array (%d elements)\n", keyuse_array->elements);
+ DBUG_UNLOCK_FILE;
+ for(uint i=0; i < keyuse_array->elements; i++)
+ print_keyuse((KEYUSE*)dynamic_array_ptr(keyuse_array, i));
+}
+
/*
Print the current state during query optimization.
@@ -540,3 +576,205 @@
Events::get_instance()->dump_internal_status();
puts("");
}
+
+
+#ifndef DBUG_OFF
+#ifdef EXTRA_DEBUG_DUMP_TABLE_LISTS
+
+
+/*
+ A fixed-size FIFO pointer queue that also doesn't allow one to put an
+ element that has previously been put into it.
+
+ There is a hard-coded limit of the total number of queue put operations.
+ The implementation is trivial and is intended for use in debug dumps only.
+*/
+
+template <class T> class Unique_fifo_queue
+{
+public:
+ /* Add an element to the queue */
+ void push_back(T *tbl)
+ {
+ if (!tbl)
+ return;
+ // check if we've already scheduled and/or dumped the element
+ for (int i= 0; i < last; i++)
+ {
+ if (elems[i] == tbl)
+ return;
+ }
+ elems[last++]= tbl;
+ }
+
+ bool pop_first(T **elem)
+ {
+ if (first < last)
+ {
+ *elem= elems[first++];
+ return TRUE;
+ }
+ return FALSE;
+ }
+
+ void reset()
+ {
+ first= last= 0;
+ }
+ enum { MAX_ELEMS=1000};
+ T *elems[MAX_ELEMS];
+ int first; // First undumped table
+ int last; // Last undumped element
+};
+
+class Dbug_table_list_dumper
+{
+ FILE *out;
+ Unique_fifo_queue<TABLE_LIST> tables_fifo;
+ Unique_fifo_queue<List<TABLE_LIST> > tbl_lists;
+public:
+ void dump_one_struct(TABLE_LIST *tbl);
+
+ int dump_graph(st_select_lex *select_lex, TABLE_LIST *first_leaf);
+};
+
+
+void dump_TABLE_LIST_graph(SELECT_LEX *select_lex, TABLE_LIST* tl)
+{
+ Dbug_table_list_dumper dumper;
+ dumper.dump_graph(select_lex, tl);
+}
+
+
+/*
+ - Dump one TABLE_LIST objects and its outgoing edges
+ - Schedule that other objects seen along the edges are dumped too.
+*/
+
+void Dbug_table_list_dumper::dump_one_struct(TABLE_LIST *tbl)
+{
+ fprintf(out, "\"%p\" [\n", tbl);
+ fprintf(out, " label = \"%p|", tbl);
+ fprintf(out, "alias=%s|", tbl->alias? tbl->alias : "NULL");
+ fprintf(out, "<next_leaf>next_leaf=%p|", tbl->next_leaf);
+ fprintf(out, "<next_local>next_local=%p|", tbl->next_local);
+ fprintf(out, "<next_global>next_global=%p|", tbl->next_global);
+ fprintf(out, "<embedding>embedding=%p", tbl->embedding);
+
+ if (tbl->nested_join)
+ fprintf(out, "|<nested_j>nested_j=%p", tbl->nested_join);
+ if (tbl->join_list)
+ fprintf(out, "|<join_list>join_list=%p", tbl->join_list);
+ if (tbl->on_expr)
+ fprintf(out, "|<on_expr>on_expr=%p", tbl->on_expr);
+ fprintf(out, "\"\n");
+ fprintf(out, " shape = \"record\"\n];\n\n");
+
+ if (tbl->next_leaf)
+ {
+ fprintf(out, "\n\"%p\":next_leaf -> \"%p\"[ color = \"#000000\" ];\n",
+ tbl, tbl->next_leaf);
+ tables_fifo.push_back(tbl->next_leaf);
+ }
+ if (tbl->next_local)
+ {
+ fprintf(out, "\n\"%p\":next_local -> \"%p\"[ color = \"#404040\" ];\n",
+ tbl, tbl->next_local);
+ tables_fifo.push_back(tbl->next_local);
+ }
+ if (tbl->next_global)
+ {
+ fprintf(out, "\n\"%p\":next_global -> \"%p\"[ color = \"#808080\" ];\n",
+ tbl, tbl->next_global);
+ tables_fifo.push_back(tbl->next_global);
+ }
+
+ if (tbl->embedding)
+ {
+ fprintf(out, "\n\"%p\":embedding -> \"%p\"[ color = \"#FF0000\" ];\n",
+ tbl, tbl->embedding);
+ tables_fifo.push_back(tbl->embedding);
+ }
+
+ if (tbl->join_list)
+ {
+ fprintf(out, "\n\"%p\":join_list -> \"%p\"[ color = \"#0000FF\" ];\n",
+ tbl, tbl->join_list);
+ tbl_lists.push_back(tbl->join_list);
+ }
+}
+
+
+int Dbug_table_list_dumper::dump_graph(st_select_lex *select_lex,
+ TABLE_LIST *first_leaf)
+{
+ DBUG_ENTER("Dbug_table_list_dumper::dump_graph");
+ char filename[500];
+ int no = 0;
+ do
+ {
+ sprintf(filename, "tlist_tree%.3d.g", no);
+ if ((out= fopen(filename, "rt")))
+ {
+ /* File exists, try next name */
+ fclose(out);
+ }
+ no++;
+ } while (out);
+
+ /* Ok, found an unoccupied name, create the file */
+ if (!(out= fopen(filename, "wt")))
+ {
+ DBUG_PRINT("tree_dump", ("Failed to create output file"));
+ DBUG_RETURN(1);
+ }
+
+ DBUG_PRINT("tree_dump", ("dumping tree to %s", filename));
+
+ fputs("digraph g {\n", out);
+ fputs("graph [", out);
+ fputs(" rankdir = \"LR\"", out);
+ fputs("];", out);
+
+ TABLE_LIST *tbl;
+ tables_fifo.reset();
+ dump_one_struct(first_leaf);
+ while (tables_fifo.pop_first(&tbl))
+ {
+ dump_one_struct(tbl);
+ }
+
+ List<TABLE_LIST> *plist;
+ tbl_lists.push_back(&select_lex->top_join_list);
+ while (tbl_lists.pop_first(&plist))
+ {
+ fprintf(out, "\"%p\" [\n", plist);
+ fprintf(out, " bgcolor = \"\"");
+ fprintf(out, " label = \"L %p\"", plist);
+ fprintf(out, " shape = \"record\"\n];\n\n");
+ }
+
+ fprintf(out, " { rank = same; ");
+ for (TABLE_LIST *tl=first_leaf; tl; tl= tl->next_leaf)
+ fprintf(out, " \"%p\"; ", tl);
+ fprintf(out, "};\n");
+ fputs("}", out);
+ fclose(out);
+
+ char filename2[500];
+ filename[strlen(filename) - 1]= 0;
+ filename[strlen(filename) - 1]= 0;
+ sprintf(filename2, "%s.query", filename);
+
+ if ((out= fopen(filename2, "wt")))
+ {
+ fprintf(out, "%s", current_thd->query);
+ fclose(out);
+ }
+ DBUG_RETURN(0);
+}
+
+#endif
+
+#endif
+
--- 1.218/sql/sql_update.cc 2007-05-22 23:53:55 +04:00
+++ 1.219/sql/sql_update.cc 2007-05-22 23:53:55 +04:00
@@ -1456,7 +1456,9 @@
{
if (tmp_table->file->is_fatal_error(error, HA_CHECK_DUP) &&
create_myisam_from_heap(thd, tmp_table,
- tmp_table_param + offset, error, 1))
+ tmp_table_param[offset].start_recinfo,
+ &tmp_table_param[offset].recinfo,
+ error, 1))
{
do_update=0;
DBUG_RETURN(1); // Not a table_is_full error
--- 1.161/sql/table.h 2007-05-22 23:53:55 +04:00
+++ 1.162/sql/table.h 2007-05-22 23:53:55 +04:00
@@ -659,6 +659,8 @@
(TABLE_LIST::natural_join != NULL)
- JOIN ... USING
(TABLE_LIST::join_using_fields != NULL)
+ - semi-join
+ ;
*/
typedef struct st_table_list
@@ -675,6 +677,8 @@
char *db, *alias, *table_name, *schema_table_name;
char *option; /* Used by cache index */
Item *on_expr; /* Used with outer join */
+ Item *sj_on_expr;
+ table_map sj_inner_tables;
/*
The structure of ON expression presented in the member above
can be changed during certain optimizations. This member
@@ -1036,8 +1040,9 @@
by the join optimizer.
Before each use the counters are zeroed by reset_nj_counters.
*/
- uint counter;
+ uint counter_;
nested_join_map nj_map; /* Bit used to identify this nested join*/
+ table_map sj_depends_on;
} NESTED_JOIN;
--- 1.146/sql/sql_union.cc 2007-05-22 23:53:55 +04:00
+++ 1.147/sql/sql_union.cc 2007-05-22 23:53:55 +04:00
@@ -65,7 +65,8 @@
{
/* create_myisam_from_heap will generate error if needed */
if (table->file->is_fatal_error(error, HA_CHECK_DUP) &&
- create_myisam_from_heap(thd, table, &tmp_table_param, error, 1))
+ create_myisam_from_heap(thd, table, tmp_table_param.start_recinfo,
+ &tmp_table_param.recinfo, error, 1))
return 1;
}
return 0;
@@ -469,6 +470,19 @@
sl->join->select_options=
(select_limit_cnt == HA_POS_ERROR || sl->braces) ?
sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
+
+ if (!this->item)
+ {
+ //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
+ /* We're not in a subquery predicate */
+ if (sl->join->flatten_subqueries())
+ {
+ thd->net.report_error= 1;
+ DBUG_RETURN(TRUE);
+ }
+ //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables);
+ }
+ ///
saved_error= sl->join->optimize();
}
if (!saved_error)
--- 1.166/mysql-test/r/subselect.result 2007-05-22 23:53:55 +04:00
+++ 1.167/mysql-test/r/subselect.result 2007-05-22 23:53:55 +04:00
@@ -1294,31 +1294,31 @@
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))
+Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using where; Using index
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1333,31 +1333,31 @@
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary
+1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index
+1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1370,10 +1370,10 @@
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
drop table t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -2819,10 +2819,10 @@
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = _latin1'N'))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
--- 1.14/mysql-test/r/subselect2.result 2007-05-22 23:53:55 +04:00
+++ 1.15/mysql-test/r/subselect2.result 2007-05-22 23:53:55 +04:00
@@ -123,14 +123,14 @@
c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL
EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 Using where
+1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 const 2 Using where; Start temporary
+1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where
+1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where
+1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where
+1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where
+1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 Using where; End temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1
-2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
-3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
-4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
-5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 Using where
-6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 Using where
drop table t1, t2, t3, t4;
CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
INSERT INTO t1 VALUES (1),(2);
--- 1.35/sql/item_row.cc 2007-05-22 23:53:55 +04:00
+++ 1.36/sql/item_row.cc 2007-05-22 23:53:55 +04:00
@@ -119,6 +119,18 @@
}
}
+void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+{
+ used_tables_cache= 0;
+ const_item_cache= 1;
+ for (uint i= 0; i < arg_count; i++)
+ {
+ items[i]->fix_after_pullout(new_parent, &items[i]);
+ used_tables_cache|= items[i]->used_tables();
+ const_item_cache&= items[i]->const_item();
+ }
+}
+
bool Item_row::check_cols(uint c)
{
if (c != arg_count)
--- 1.24/sql/item_row.h 2007-05-22 23:53:55 +04:00
+++ 1.25/sql/item_row.h 2007-05-22 23:53:55 +04:00
@@ -59,6 +59,7 @@
return 0;
};
bool fix_fields(THD *thd, Item **ref);
+ void fix_after_pullout(st_select_lex *new_parent, Item **ref);
void cleanup();
void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields);
table_map used_tables() const { return used_tables_cache; };
--- 1.146/sql/item_subselect.cc 2007-05-22 23:53:55 +04:00
+++ 1.147/sql/item_subselect.cc 2007-05-22 23:53:55 +04:00
@@ -612,7 +612,7 @@
Item_in_subselect::Item_in_subselect(Item * left_exp,
st_select_lex *select_lex):
Item_exists_subselect(), optimizer(0), transformed(0),
- pushed_cond_guards(NULL), upper_item(0)
+ pushed_cond_guards(NULL), upper_item(0), converted_to_sj(FALSE)
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
left_expr= left_exp;
@@ -1519,7 +1519,11 @@
bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref)
{
bool result = 0;
-
+ ref_ptr= ref;
+
+ if (converted_to_sj)
+ return !( (*ref)= new Item_int(1));
+
if (thd_arg->lex->view_prepare_mode && left_expr && !left_expr->fixed)
result = left_expr->fix_fields(thd_arg, &left_expr);
--- 1.89/sql/item_subselect.h 2007-05-22 23:53:55 +04:00
+++ 1.90/sql/item_subselect.h 2007-05-22 23:53:55 +04:00
@@ -31,13 +31,15 @@
class Item_subselect :public Item_result_field
{
my_bool value_assigned; /* value already assigned to subselect */
-protected:
+public:
/* thread handler, will be assigned in fix_fields only */
THD *thd;
/* substitution instead of subselect in case of optimization */
Item *substitution;
/* unit of subquery */
+public:
st_select_lex_unit *unit;
+protected:
/* engine that perform execution of subselect (single select or union) */
subselect_engine *engine;
/* old engine if engine was changed */
@@ -238,8 +240,9 @@
class Item_in_subselect :public Item_exists_subselect
{
-protected:
+public:
Item *left_expr;
+protected:
/*
expr & optimizer used in subselect rewriting to store Item for
all JOIN in UNION
@@ -252,6 +255,20 @@
public:
/* Used to trigger on/off conditions that were pushed down to subselect */
bool *pushed_cond_guards;
+
+ /* Priority of this predicate in the convert-to-semi-join-nest process. */
+ int sj_convert_priority;
+
+ /* TRUE <=> This Item was converted to semi-join nest and should be removed */
+ bool converted_to_sj;
+
+ /*
+ Location of the subquery predicate. It is either
+ - pointer to join nest if the subquery predicate is in the ON expression
+ - (TABLE_LIST*)1 if the predicate is in the WHERE.
+ */
+ TABLE_LIST *expr_join_nest;
+ Item **ref_ptr;
bool *get_cond_guard(int i)
{
@@ -265,7 +282,7 @@
Item_in_subselect(Item * left_expr, st_select_lex *select_lex);
Item_in_subselect()
:Item_exists_subselect(), optimizer(0), abort_on_null(0), transformed(0),
- pushed_cond_guards(NULL), upper_item(0)
+ pushed_cond_guards(NULL), upper_item(0), converted_to_sj(FALSE)
{}
subs_type substype() { return IN_SUBS; }
--- 1.17/mysql-test/r/kill.result 2007-05-22 23:53:55 +04:00
+++ 1.18/mysql-test/r/kill.result 2007-05-22 23:53:55 +04:00
@@ -22,7 +22,7 @@
insert into t2 select id from t1;
create table t3 (kill_id int);
insert into t3 values(connection_id());
-select id from t1 where id in (select distinct id from t2);
+select id from t1 where id in (select distinct id from t2) or id < 10000;
select ((@id := kill_id) - kill_id) from t3;
((@id := kill_id) - kill_id)
0
--- 1.25/mysql-test/t/kill.test 2007-05-22 23:53:55 +04:00
+++ 1.26/mysql-test/t/kill.test 2007-05-22 23:53:55 +04:00
@@ -75,13 +75,12 @@
create table t3 (kill_id int);
insert into t3 values(connection_id());
-
connect (conn2, localhost, root,,);
connection conn2;
connection conn1;
-- disable_result_log
-send select id from t1 where id in (select distinct id from t2);
+send select id from t1 where id in (select distinct id from t2) or id < 10000;
-- enable_result_log
connection conn2;
--- 1.18/sql/sql_bitmap.h 2007-05-22 23:53:55 +04:00
+++ 1.19/sql/sql_bitmap.h 2007-05-22 23:53:55 +04:00
@@ -138,3 +138,60 @@
ulonglong to_ulonglong() const { return map; }
};
+
+/* An iterator to quickly walk over bits in unlonglong bitmap. */
+class Table_map_iterator
+{
+ ulonglong bmp;
+ uint no;
+public:
+ Table_map_iterator(ulonglong t) : bmp(t), no(0) {}
+ int next_bit()
+ {
+ static const char last_bit[16]= {32, 0, 1, 0,
+ 2, 0, 1, 0,
+ 3, 0, 1, 0,
+ 2, 0, 1, 0};
+ uint bit;
+ while ((bit= last_bit[bmp & 0xF]) == 32)
+ {
+ no += 4;
+ bmp= bmp >> 4;
+ if (!bmp)
+ return BITMAP_END;
+ }
+ bmp &= ~(1LL << bit);
+ return no + bit;
+ }
+ enum { BITMAP_END= 64 };
+};
+
+
+#if 0
+void print_bits(table_map bmp)
+{
+ Table_map_iterator it(bmp);
+ int i, first= 1;
+ fprintf(stderr, "0x%llx = ", bmp);
+ while ((i= it.next_bit()) != Table_map_iterator::BITMAP_END)
+ {
+ fprintf(stderr, " %s 2^%d", (first?"":"+"), i);
+ if (first)
+ first= 0;
+ }
+ fprintf(stderr, "\n");
+}
+
+int main()
+{
+ print_bits(1024);
+ print_bits(3);
+ print_bits(0xF);
+ print_bits(0xF0);
+ print_bits(35);
+ print_bits(1LL<<63);
+ print_bits(0);
+ print_bits(-1LL);
+}
+#endif
+
--- 1.6/mysql-test/r/subselect3.result 2007-05-22 23:53:55 +04:00
+++ 1.7/mysql-test/r/subselect3.result 2007-05-22 23:53:55 +04:00
@@ -99,7 +99,7 @@
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 5
+Handler_read_rnd_next 11
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
flush status;
@@ -337,8 +337,8 @@
bb NULL NULL
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
oref a
-ff 2
aa 1
+ff 2
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
oref a
bb 2
@@ -421,8 +421,8 @@
bb NULL NULL
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
oref a
-ff 2
aa 1
+ff 2
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
oref a
bb 2
@@ -515,8 +515,8 @@
dd 1 NULL 0
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
-ff 2 2
aa 1 1
+ff 2 2
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
bb 2 1
@@ -560,8 +560,8 @@
dd 1 NULL 0
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
-ff 2 2
aa 1 1
+ff 2 2
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
bb 2 1
--- New file ---
+++ mysql-test/r/subselect_sj.result 07/05/22 23:53:40
drop table if exists t0, t1, t2, t10, t11, t12;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,2);
create table t2 as select * from t1;
create table t11(a int, b int);
create table t10 (pk int, a int, primary key(pk));
insert into t10 select a,a from t0;
create table t12 like t10;
insert into t12 select * from t10;
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
0 0
1 1
2 2
A confluent case of dependency
explain select * from t1 where a in (select a from t10 where pk=12);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 const PRIMARY PRIMARY 4 const 1 Using where
select * from t1 where a in (select a from t10 where pk=12);
a b
An empty table inside
explain select * from t1 where a in (select a from t11);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t11 system NULL NULL NULL NULL 0 const row not found
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
select * from t1 where a in (select a from t11);
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
a b
0 0
1 1
2 2
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index; FirstMatch(t10)
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
0 0
1 1
2 2
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index; FirstMatch(t10)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10` join `test`.`t12`) where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
subqueries within outer joins go into ON expr.
explain extended
select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk from t10));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.B.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`A`.`a` AS `a`,`test`.`A`.`b` AS `b`,`test`.`B`.`a` AS `a`,`test`.`B`.`b` AS `b` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`a` = `test`.`t1`.`a`) and 1 and (`test`.`B`.`a` = `test`.`t10`.`pk`))) where 1
t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
explain extended
select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2`) on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and 1 and (`test`.`t2`.`a` = `test`.`t10`.`pk`))) where 1
we shouldn't flatten if we're going to get a join of > MAX_TABLES.
explain select * from
t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19,
t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29,
t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39,
t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49
where
s00.a in (
select m00.a from
t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09,
t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY s01 ALL NULL NULL NULL NULL 3
1 PRIMARY s02 ALL NULL NULL NULL NULL 3
1 PRIMARY s03 ALL NULL NULL NULL NULL 3
1 PRIMARY s04 ALL NULL NULL NULL NULL 3
1 PRIMARY s05 ALL NULL NULL NULL NULL 3
1 PRIMARY s06 ALL NULL NULL NULL NULL 3
1 PRIMARY s07 ALL NULL NULL NULL NULL 3
1 PRIMARY s08 ALL NULL NULL NULL NULL 3
1 PRIMARY s09 ALL NULL NULL NULL NULL 3
1 PRIMARY s10 ALL NULL NULL NULL NULL 3
1 PRIMARY s11 ALL NULL NULL NULL NULL 3
1 PRIMARY s12 ALL NULL NULL NULL NULL 3
1 PRIMARY s13 ALL NULL NULL NULL NULL 3
1 PRIMARY s14 ALL NULL NULL NULL NULL 3
1 PRIMARY s15 ALL NULL NULL NULL NULL 3
1 PRIMARY s16 ALL NULL NULL NULL NULL 3
1 PRIMARY s17 ALL NULL NULL NULL NULL 3
1 PRIMARY s18 ALL NULL NULL NULL NULL 3
1 PRIMARY s19 ALL NULL NULL NULL NULL 3
1 PRIMARY s20 ALL NULL NULL NULL NULL 3
1 PRIMARY s21 ALL NULL NULL NULL NULL 3
1 PRIMARY s22 ALL NULL NULL NULL NULL 3
1 PRIMARY s23 ALL NULL NULL NULL NULL 3
1 PRIMARY s24 ALL NULL NULL NULL NULL 3
1 PRIMARY s25 ALL NULL NULL NULL NULL 3
1 PRIMARY s26 ALL NULL NULL NULL NULL 3
1 PRIMARY s27 ALL NULL NULL NULL NULL 3
1 PRIMARY s28 ALL NULL NULL NULL NULL 3
1 PRIMARY s29 ALL NULL NULL NULL NULL 3
1 PRIMARY s30 ALL NULL NULL NULL NULL 3
1 PRIMARY s31 ALL NULL NULL NULL NULL 3
1 PRIMARY s32 ALL NULL NULL NULL NULL 3
1 PRIMARY s33 ALL NULL NULL NULL NULL 3
1 PRIMARY s34 ALL NULL NULL NULL NULL 3
1 PRIMARY s35 ALL NULL NULL NULL NULL 3
1 PRIMARY s36 ALL NULL NULL NULL NULL 3
1 PRIMARY s37 ALL NULL NULL NULL NULL 3
1 PRIMARY s38 ALL NULL NULL NULL NULL 3
1 PRIMARY s39 ALL NULL NULL NULL NULL 3
1 PRIMARY s40 ALL NULL NULL NULL NULL 3
1 PRIMARY s41 ALL NULL NULL NULL NULL 3
1 PRIMARY s42 ALL NULL NULL NULL NULL 3
1 PRIMARY s43 ALL NULL NULL NULL NULL 3
1 PRIMARY s44 ALL NULL NULL NULL NULL 3
1 PRIMARY s45 ALL NULL NULL NULL NULL 3
1 PRIMARY s46 ALL NULL NULL NULL NULL 3
1 PRIMARY s47 ALL NULL NULL NULL NULL 3
1 PRIMARY s48 ALL NULL NULL NULL NULL 3
1 PRIMARY s49 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3
2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
a b a b
0 0 0 0
1 1 1 1
2 2 2 2
prepare s1 from
' select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5';
execute s1;
a b a b
0 0 0 0
1 1 1 1
2 2 2 2
execute s1;
a b a b
0 0 0 0
1 1 1 1
2 2 2 2
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
drop table t0, t1;
--- New file ---
+++ mysql-test/r/subselect_sj2.result 07/05/22 23:53:40
drop table if exists t0, t1, t2, t3;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int,
b int
);
insert into t1 values (1,1),(1,1),(2,2);
create table t2 (
a int,
b int,
key(b)
);
insert into t2 select a, a/2 from t0;
select * from t1;
a b
1 1
1 1
2 2
select * from t2;
a b
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
1 PRIMARY t2 ref b b 5 test.t1.a 2 Using where; End temporary
select * from t2 where b in (select a from t1);
a b
1 1
2 1
3 2
4 2
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb;
insert into t3 select a,a, a,a,a from t0;
explain select * from t3 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
1 PRIMARY t3 ref b b 5 test.t1.a 1 Using where; End temporary
select * from t3 where b in (select a from t1);
a b pk1 pk2 pk3
1 1 1 1 1
2 2 2 2 2
set @save_max_heap_table_size= @@max_heap_table_size;
set max_heap_table_size=16384;
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size= 8000;
drop table t3;
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200),
primary key(pk1, pk2)
) engine=innodb;
insert into t3 select
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
1 PRIMARY t3 ref b b 5 test.t0.a 1 Using where; End temporary
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
a b pk1 pk2
0 0 0 0
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6 6
7 7 7 7
8 8 8 8
9 9 9 9
10 10 10 10
11 11 11 11
12 12 12 12
13 13 13 13
set join_buffer_size= @save_join_buffer_size;
set max_heap_table_size= @save_max_heap_table_size;
explain select * from t1 where a in (select b from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t2 ref b b 5 test.t1.a 2 Using where; Using index; FirstMatch(t1)
select * from t1;
a b
1 1
1 1
2 2
select * from t1 where a in (select b from t2);
a b
1 1
1 1
2 2
drop table t1, t2, t3;
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size= 8000;
create table t1 (a int, filler1 binary(200), filler2 binary(200));
insert into t1 select a, 'filler123456', 'filler123456' from t0;
insert into t1 select a+10, 'filler123456', 'filler123456' from t0;
create table t2 as select * from t1;
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
insert into t1 values (2, 'duplicate ok', 'duplicate ok');
insert into t1 values (18, 'duplicate ok', 'duplicate ok');
insert into t2 values (3, 'duplicate ok', 'duplicate ok');
insert into t2 values (19, 'duplicate ok', 'duplicate ok');
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary
1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; End temporary
select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
a mid(filler1, 1,10) Z
0 filler1234 1
1 filler1234 1
2 filler1234 1
3 filler1234 1
4 filler1234 1
5 filler1234 1
6 filler1234 1
7 filler1234 1
8 filler1234 1
9 filler1234 1
10 filler1234 1
11 filler1234 1
12 filler1234 1
13 filler1234 1
14 filler1234 1
15 filler1234 1
16 filler1234 1
17 filler1234 1
18 filler1234 1
19 filler1234 1
2 duplicate 1
18 duplicate 1
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ot ALL NULL NULL NULL NULL 22 Start temporary
1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; End temporary
select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
a mid(filler1, 1,10) length(filler1)=length(filler2)
0 filler1234 1
1 filler1234 1
2 filler1234 1
3 filler1234 1
4 filler1234 1
5 filler1234 1
6 filler1234 1
7 filler1234 1
8 filler1234 1
9 filler1234 1
10 filler1234 1
11 filler1234 1
12 filler1234 1
13 filler1234 1
14 filler1234 1
15 filler1234 1
16 filler1234 1
17 filler1234 1
18 filler1234 1
3 duplicate 1
19 filler1234 1
19 duplicate 1
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary
1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; End temporary
select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
a mid(filler1, 1,10) Z
0 filler1234 1
1 filler1234 1
2 filler1234 1
3 filler1234 1
4 filler1234 1
5 filler1234 1
6 filler1234 1
7 filler1234 1
8 filler1234 1
9 filler1234 1
10 filler1234 1
11 filler1234 1
12 filler1234 1
13 filler1234 1
14 filler1234 1
15 filler1234 1
16 filler1234 1
17 filler1234 1
18 filler1234 1
19 filler1234 1
2 duplicate 1
18 duplicate 1
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ot ALL NULL NULL NULL NULL 22 Start temporary
1 PRIMARY it ALL NULL NULL NULL NULL 52 Using where; End temporary
select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
a mid(filler1, 1,10) length(filler1)=length(filler2)
0 filler1234 1
1 filler1234 1
2 filler1234 1
3 filler1234 1
4 filler1234 1
5 filler1234 1
6 filler1234 1
7 filler1234 1
8 filler1234 1
9 filler1234 1
10 filler1234 1
11 filler1234 1
12 filler1234 1
13 filler1234 1
14 filler1234 1
15 filler1234 1
16 filler1234 1
17 filler1234 1
18 filler1234 1
3 duplicate 1
19 filler1234 1
19 duplicate 1
drop table t1, t2;
create table t1 (a int, b int, key(a));
create table t2 (a int, b int, key(a));
create table t3 (a int, b int, key(a));
insert into t1 select a,a from t0;
insert into t2 select a,a from t0;
insert into t3 select a,a from t0;
t2 and t3 must be use 'ref', not 'ALL':
explain select *
from t0 where a in
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
1 PRIMARY t1 index NULL a 5 NULL 10 Using index
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
--- New file ---
+++ mysql-test/t/subselect_sj.test 07/05/22 23:53:40
#
# Nested Loops semi-join subquery evaluation tests
#
--disable_warnings
drop table if exists t0, t1, t2, t10, t11, t12;
--enable_warnings
#
# 1. Subqueries that are converted into semi-joins
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,2);
create table t2 as select * from t1;
create table t11(a int, b int);
create table t10 (pk int, a int, primary key(pk));
insert into t10 select a,a from t0;
create table t12 like t10;
insert into t12 select * from t10;
--echo Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
select * from t1 where a in (select pk from t10);
--echo A confluent case of dependency
explain select * from t1 where a in (select a from t10 where pk=12);
select * from t1 where a in (select a from t10 where pk=12);
--echo An empty table inside
explain select * from t1 where a in (select a from t11);
select * from t1 where a in (select a from t11);
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
--echo flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
--echo flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
--echo subqueries within outer joins go into ON expr.
explain extended
select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk from t10));
--echo t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
explain extended
select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10));
--echo we shouldn't flatten if we're going to get a join of > MAX_TABLES.
explain select * from
t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19,
t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29,
t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39,
t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49
where
s00.a in (
select m00.a from
t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09,
t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19
);
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
#
# Prepared statements
#
prepare s1 from
' select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5';
execute s1;
execute s1;
# Try I2O orders
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
drop table t0, t1;
--- New file ---
+++ mysql-test/t/subselect_sj2.test 07/05/22 23:53:40
#
# DuplicateElimination strategy test
#
--source include/have_innodb.inc
--disable_warnings
drop table if exists t0, t1, t2, t3;
--enable_warnings
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
# First test simple cases: I20 order, no join buffering.
create table t1 (
a int,
b int
);
insert into t1 values (1,1),(1,1),(2,2);
create table t2 (
a int,
b int,
key(b)
);
insert into t2 select a, a/2 from t0;
select * from t1;
select * from t2;
explain select * from t2 where b in (select a from t1);
select * from t2 where b in (select a from t1);
# Try an InnoDB table with very long rowid
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb;
insert into t3 select a,a, a,a,a from t0;
explain select * from t3 where b in (select a from t1);
select * from t3 where b in (select a from t1);
# Test overflow to MyISAM:
set @save_max_heap_table_size= @@max_heap_table_size;
set max_heap_table_size=16384;
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size= 8000;
drop table t3;
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200),
primary key(pk1, pk2)
) engine=innodb;
insert into t3 select
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
from t0 A, t0 B where B.a <5;
explain select * from t3 where b in (select a from t0);
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
set join_buffer_size= @save_join_buffer_size;
set max_heap_table_size= @save_max_heap_table_size;
# O2I join orders, with shortcutting:
explain select * from t1 where a in (select b from t2);
select * from t1;
select * from t1 where a in (select b from t2);
drop table t1, t2, t3;
# (no need for anything in range/index_merge/DS-MRR)
#
# Test join buffering
#
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size= 8000;
create table t1 (a int, filler1 binary(200), filler2 binary(200));
insert into t1 select a, 'filler123456', 'filler123456' from t0;
insert into t1 select a+10, 'filler123456', 'filler123456' from t0;
create table t2 as select * from t1;
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
insert into t1 values (2, 'duplicate ok', 'duplicate ok');
insert into t1 values (18, 'duplicate ok', 'duplicate ok');
insert into t2 values (3, 'duplicate ok', 'duplicate ok');
insert into t2 values (19, 'duplicate ok', 'duplicate ok');
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
# Now let the buffer overfill:
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
select
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
from t1 ot where a in (select a from t2 it);
explain select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
select
a, mid(filler1, 1,10), length(filler1)=length(filler2)
from t2 ot where a in (select a from t1 it);
drop table t1, t2;
# Check ref access to tables inside the OJ nest inside the SJ nest
create table t1 (a int, b int, key(a));
create table t2 (a int, b int, key(a));
create table t3 (a int, b int, key(a));
insert into t1 select a,a from t0;
insert into t2 select a,a from t0;
insert into t3 select a,a from t0;
--echo t2 and t3 must be use 'ref', not 'ALL':
explain select *
from t0 where a in
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
drop table t0, t1,t2,t3;
--- 1.3/sql/sql_array.h 2007-05-22 23:53:55 +04:00
+++ 1.4/sql/sql_array.h 2007-05-22 23:53:55 +04:00
@@ -53,6 +53,11 @@
return array.elements;
}
+ void clear()
+ {
+ array.elements= 0;
+ }
+
~Dynamic_array()
{
delete_dynamic(&array);
| Thread |
|---|
| • bk commit into 5.2 tree (sergefp:1.2456) | Sergey Petrunia | 22 May |
