maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #03210
bzr commit into file:///home/tsk/mprog/src/5.3-mwl89/ branch (timour:2793)
#At file:///home/tsk/mprog/src/5.3-mwl89/ based on revid:timour@xxxxxxxxxxxx-20100527131347-unr62oupctbp912x
2793 timour@xxxxxxxxxxxx 2010-06-01
MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation
Phase 2: Changed the code-generation for subquery materialization to be
performed in runtime memory for each (re)execution, instead of in
statement memory (once per prepared statement).
- Item_in_subselect::setup_engine() no longer wraps materialization related
objects to be created in statement memory.
- Merged subselect_hash_sj_engine::init_permanent and
subselect_hash_sj_engine::init_runtime into subselect_hash_sj_engine::init,
which is called for each (re)execution.
- Fixed deletion of the temp table accordingly.
modified:
sql/item_subselect.cc
sql/item_subselect.h
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2010-05-27 13:13:47 +0000
+++ b/sql/item_subselect.cc 2010-06-01 11:57:35 +0000
@@ -148,6 +148,7 @@ void Item_in_subselect::cleanup()
Item_subselect::~Item_subselect()
{
delete engine;
+ engine= NULL;
}
Item_subselect::trans_res
@@ -2090,82 +2091,62 @@ void Item_in_subselect::update_used_tabl
bool Item_in_subselect::setup_engine()
{
- subselect_hash_sj_engine *new_engine= NULL;
- bool res= FALSE;
+ subselect_hash_sj_engine *mat_engine= NULL;
+ subselect_single_select_engine *select_engine;
DBUG_ENTER("Item_in_subselect::setup_engine");
+ /*
+ The select (IN=>EXISTS) engine is pre-created already at parse time, and
+ is stored in statment memory (preserved across PS executions).
+ */
+ DBUG_ASSERT(engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE);
+ select_engine= (subselect_single_select_engine*) engine;
- if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
- {
- /* Create/initialize objects in permanent memory. */
- subselect_single_select_engine *old_engine;
- Query_arena *arena= thd->stmt_arena, backup;
-
- old_engine= (subselect_single_select_engine*) engine;
-
- if (arena->is_conventional())
- arena= 0;
- else
- thd->set_n_backup_active_arena(arena, &backup);
-
- if (!(new_engine= new subselect_hash_sj_engine(thd, this,
- old_engine)) ||
- new_engine->init_permanent(&old_engine->join->fields_list))
- {
- Item_subselect::trans_res trans_res;
- /*
- If for some reason we cannot use materialization for this IN predicate,
- delete all materialization-related objects, and apply the IN=>EXISTS
- transformation.
- */
- delete new_engine;
- new_engine= NULL;
- exec_method= NOT_TRANSFORMED;
- if (left_expr->cols() == 1)
- trans_res= single_value_in_to_exists_transformer(old_engine->join,
- &eq_creator);
- else
- trans_res= row_value_in_to_exists_transformer(old_engine->join);
- /*
- The IN=>EXISTS transformation above injects new predicates into the
- WHERE and HAVING clauses. Since the subquery was already optimized,
- below we force its reoptimization with the new injected conditions
- by the first call to subselect_single_select_engine::exec().
- This is the only case of lazy subquery optimization in the server.
- */
- DBUG_ASSERT(old_engine->join->optimized);
- old_engine->join->optimized= false;
- res= (trans_res != Item_subselect::RES_OK);
- }
- if (new_engine)
- engine= new_engine;
-
- if (arena)
- thd->restore_active_arena(arena, &backup);
- }
- else
- {
- DBUG_ASSERT(engine->engine_type() == subselect_engine::HASH_SJ_ENGINE);
- new_engine= (subselect_hash_sj_engine*) engine;
- }
+ /* Create/initialize execution objects. */
+ if (!(mat_engine= new subselect_hash_sj_engine(thd, this, select_engine)))
+ DBUG_RETURN(TRUE);
- /* Initilizations done in runtime memory, repeated for each execution. */
- if (new_engine)
+ if (mat_engine->init(&select_engine->join->fields_list))
{
+ Item_subselect::trans_res trans_res;
+ /*
+ If for some reason we cannot use materialization for this IN predicate,
+ delete all materialization-related objects, and apply the IN=>EXISTS
+ transformation.
+ */
+ delete mat_engine;
+ mat_engine= NULL;
+ exec_method= NOT_TRANSFORMED;
+
+ if (left_expr->cols() == 1)
+ trans_res= single_value_in_to_exists_transformer(select_engine->join,
+ &eq_creator);
+ else
+ trans_res= row_value_in_to_exists_transformer(select_engine->join);
/*
- Reset the LIMIT 1 set in Item_exists_subselect::fix_length_and_dec.
- TODO:
- Currently we set the subquery LIMIT to infinity, and this is correct
- because we forbid at parse time LIMIT inside IN subqueries (see
- Item_in_subselect::test_limit). However, once we allow this, here
- we should set the correct limit if given in the query.
+ The IN=>EXISTS transformation above injects new predicates into the
+ WHERE and HAVING clauses. Since the subquery was already optimized,
+ below we force its reoptimization with the new injected conditions
+ by the first call to subselect_single_select_engine::exec().
+ This is the only case of lazy subquery optimization in the server.
*/
- unit->global_parameters->select_limit= NULL;
- if ((res= new_engine->init_runtime()))
- DBUG_RETURN(res);
+ DBUG_ASSERT(select_engine->join->optimized);
+ select_engine->join->optimized= false;
+ DBUG_RETURN(trans_res != Item_subselect::RES_OK);
}
- DBUG_RETURN(res);
+ /*
+ Reset the "LIMIT 1" set in Item_exists_subselect::fix_length_and_dec.
+ TODO:
+ Currently we set the subquery LIMIT to infinity, and this is correct
+ because we forbid at parse time LIMIT inside IN subqueries (see
+ Item_in_subselect::test_limit). However, once we allow this, here
+ we should set the correct limit if given in the query.
+ */
+ unit->global_parameters->select_limit= NULL;
+
+ engine= mat_engine;
+ DBUG_RETURN(FALSE);
}
@@ -3680,14 +3661,14 @@ bitmap_init_memroot(MY_BITMAP *map, uint
@retval FALSE otherwise
*/
-bool subselect_hash_sj_engine::init_permanent(List<Item> *tmp_columns)
+bool subselect_hash_sj_engine::init(List<Item> *tmp_columns)
{
select_union *result_sink;
/* Options to create_tmp_table. */
ulonglong tmp_create_options= thd->options | TMP_TABLE_ALL_COLUMNS;
/* | TMP_TABLE_FORCE_MYISAM; TIMOUR: force MYISAM */
- DBUG_ENTER("subselect_hash_sj_engine::init_permanent");
+ DBUG_ENTER("subselect_hash_sj_engine::init");
if (bitmap_init_memroot(&non_null_key_parts, tmp_columns->elements,
thd->mem_root) ||
@@ -3762,6 +3743,17 @@ bool subselect_hash_sj_engine::init_perm
!(lookup_engine= make_unique_engine()))
DBUG_RETURN(TRUE);
+ /*
+ Repeat name resolution for 'cond' since cond is not part of any
+ clause of the query, and it is not 'fixed' during JOIN::prepare.
+ */
+ if (semi_join_conds && !semi_join_conds->fixed &&
+ semi_join_conds->fix_fields(thd, (Item**)&semi_join_conds))
+ DBUG_RETURN(TRUE);
+ /* Let our engine reuse this query plan for materialization. */
+ materialize_join= materialize_engine->join;
+ materialize_join->change_result(result);
+
DBUG_RETURN(FALSE);
}
@@ -3907,30 +3899,6 @@ subselect_hash_sj_engine::make_unique_en
}
-/**
- Initialize members of the engine that need to be re-initilized at each
- execution.
-
- @retval TRUE if a memory allocation error occurred
- @retval FALSE if success
-*/
-
-bool subselect_hash_sj_engine::init_runtime()
-{
- /*
- Repeat name resolution for 'cond' since cond is not part of any
- clause of the query, and it is not 'fixed' during JOIN::prepare.
- */
- if (semi_join_conds && !semi_join_conds->fixed &&
- semi_join_conds->fix_fields(thd, (Item**)&semi_join_conds))
- return TRUE;
- /* Let our engine reuse this query plan for materialization. */
- materialize_join= materialize_engine->join;
- materialize_join->change_result(result);
- return FALSE;
-}
-
-
subselect_hash_sj_engine::~subselect_hash_sj_engine()
{
delete lookup_engine;
@@ -3967,6 +3935,13 @@ void subselect_hash_sj_engine::cleanup()
count_null_only_columns= 0;
strategy= UNDEFINED;
materialize_engine->cleanup();
+ /*
+ Restore the original Item_in_subselect engine. This engine is created once
+ at parse time and stored across executions, while all other materialization
+ related engines are created and chosen for each execution.
+ */
+ ((Item_in_subselect *) item)->engine= materialize_engine;
+
if (lookup_engine_type == TABLE_SCAN_ENGINE ||
lookup_engine_type == ROWID_MERGE_ENGINE)
{
@@ -3983,6 +3958,9 @@ void subselect_hash_sj_engine::cleanup()
DBUG_ASSERT(lookup_engine->engine_type() == UNIQUESUBQUERY_ENGINE);
lookup_engine->cleanup();
result->cleanup(); /* Resets the temp table as well. */
+ DBUG_ASSERT(tmp_table);
+ free_tmp_table(thd, tmp_table);
+ tmp_table= NULL;
}
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2010-05-27 13:13:47 +0000
+++ b/sql/item_subselect.h 2010-06-01 11:57:35 +0000
@@ -802,8 +802,7 @@ public:
}
~subselect_hash_sj_engine();
- bool init_permanent(List<Item> *tmp_columns);
- bool init_runtime();
+ bool init(List<Item> *tmp_columns);
void cleanup();
int prepare();
int exec();
# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: timour@xxxxxxxxxxxx-20100601115735-z0k9me0mv58v0d40
# target_branch: file:///home/tsk/mprog/src/5.3-mwl89/
# testament_sha1: 9647cb4a7fa4cd5f7b0b58eade83b6b62bcb6ce8
# timestamp: 2010-06-01 14:57:40 +0300
# base_revision_id: timour@xxxxxxxxxxxx-20100527131347-\
# unr62oupctbp912x
#
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQ2fwywABHZfgFoQeff//3//
38C////wYAsH3tVw3rx6AUVbU0pWxpa32yIQpAqQYojEEyBoU/SZpR+qeSehPUDT1AaPU9Q9NIA9
QNU2TUyngRqQAANNNAAAAAAABqp5PVPSeoyAADQaANAGgAAAAAkRCaJpomFT9Jmiap6am3qiabNI
jI9HqCMQGRgZUmjT1BoNAHqAAGgAAAAAACSIJoExMhNIegCTxMo9U9AjyaTQ9TxTZT1M9UjF9EED
MDfyduPH33DCg9BioCiXGatjLIR6HSBu5tZcm/nlY+jey+nwwSts/Sz1Xfa6Z1EgD8elXaXYHNQi
ZExMWTzdyFdc24lU1GUW6+vHfGU2EtGQbyFXXVJywk0obeGFOaEJYAg4NQ0htCOXKm+fy7fvvHDS
hzO0Qb0vgMaSnqDAdR4DGEkSUs/EHPjvZLkG7qYQRrsse29TIrRnd4TatKhuwOqtsdWCxcOawYVt
WlJjU84tFswHjz5K7rUMX3dV3R55nucDJKInyhFRWV7XNjTlZODz6FtuVprY+icnNYTj2elTxjGM
i2Hp+K+7XVZxhBsYWPa8HpQqpG0lVNFMb8fecU4Wkb3kZUOKQO26TaTWgtDQxQriDbW8gyFtwmAy
mbgqFcIAgFo7sow0qeRBosUJouEiSRDDY5qKU6DEiTHRna5YL5ZIA7XMIrsmUsFZnNpyoVUAUrjs
KwsQJGbmqeaOSPAsX/3BnX9TFwZOjWOd2tgZsZJisJaLbzQRfQO/YIhTrdq8AjPCdmiwYdRBKAoG
ScYjAEZgEcJSFFViMIzDwMtA5lkKu/RII4aot+jdbqmypHrJWCi5taD60YOnoa2boCjpZGFtOKGo
Vj7ELvLLN18LSuCUCEPCPWzhzXdYUxO5HYBdfLGDtbnBsHi8bKE8BMJcSWwNzIq9iKFtUSGZ+FtE
mZL8WQfSQTYIMP0N3o0OoSpBCsArmKhR5BDCEej571oBiZjl3LpqOw/IzP0A8ew/UDeBrzsYRY2q
zCE2MYIX+Mc24TkBaA9rFoTKnHndYicbUUQ9qpsRONmZiRCOBqAcgw7Wns5Iye0LUx/VLdMmF2J4
iC4MwklFg6BlUhxt5ah2IujIsMkZEUdE3a6WrZOWcCqjI1YGx9xEUgKFGR/A1zMWTUDFt1w6xImR
maH8lVeqmNd5Y1WInBhiiKiqBtH3EtplWFw6VxtJm9MGowV+vB7rNUL7czOYHuw1FBqBwqh6QvJJ
a/IltJaGM2MisroIvMG5QIG01Y6XSpSubASJQOCL6KUaYBxGY88pHXUMh2Mo3BUPAsfVU8DTPDUh
mevgjA9h1zArxJ3Gg2h3EjUROBoG8tHM8CFMX0swWu6dCjmB/njvt2GZAczLDADC9NKNzNGFfKjC
okNF3ZFRQhDeaHtA+mGdVnq3DMj7XXMSGLdboZbAij3LUi05A/Xq/w/qlxCIfjMuRv1uXp8ftNzk
YXYxT3e3hOIO1rFxPCHoYqIDqpHY1BjoN69X5Hr1xOMl/nKToq+IgzEv8+X6r/duLbbH2gfSbl7I
iGUdMn83LFJo/cdA30tMhFnFCHzjpnNwAM5JxRpQf3cpPIMl85+8o7Gxmay65G2/hMwR9XsNP4Wk
RyAMlWzXNYRXk5V5u87mg9bHgYboERjFRX+RqflM8t03gJaGHWp3V6FgUDyx46rXsPYBajENUvPa
HGpNewQh8vA7A+Pf8vuk7gfYw6HdLv94/1ZBFVqQOHxBkSkez6/7TeiLPSkpnzNEpVMkrQRrDCSh
H4PyPwLic7+qJA/sOfsfv+xgjoLVg44aj80Y/LchTNoy+H/CYVW8TzQeri3KyQiQ3wNMosJb6qsX
S8hLO1qdzSBhTRjbexU+BoKvk3tYodbYWfxPu7txvPDy2H4mXbDKa+BWjigS/GOtHzRgwCskkGvN
huY+JQ2nDlJnvG1Bzj/BY1RbRlj3ElKLe7fYSjxQdpdI5/LOHCC7U3NEkHmQ0joGcNsYPVEMBjq/
O5YZmY6Vmb952gchUEQ/ceByV3AR5R448m7PL7SwYTln8gVSKnkkEMkMWuRi6szR4p7V0tlZGxSy
+F4icOqE5jlnTCBlGSVgwGtVbA3usZNlSMcYAnW4pUqB6w9QVEYsKsWhZf9L9XRVMF6iCwpVbxxV
+rRbWLZt4EObpsQHJFBXfGsm50duG+bMeJPJQKcz7W+Za64E0IMVjcCIBeD7IY4cDYmD3NKGA2DY
/hhxp16g2LFNLvGf0//29uly5v69+d7+NEjf3UlhJKWnK9pq1QezSNCzpJbyYbioz8UuNkFq8end
9GZjjDUi7EOr9Xc4Md6OY6yJ4JwTaRzmQ5RRbwGwQNSiqXm8otc4g8xrQ6w1gwS+d7wT1nglCDRY
7TN0kFjBg2h2vqO0/pxBy+tP2EH0Qn9j+3mlUXuYDYcn1dCKNsMDHge47wCq3HqCMSqoQkUraqyW
WAAukLOQwyoXZazYIXQSgmuVCQEAYArhb7/d60QSQT01TDDfzNNl88IC+/sshiFjJJgNW3UTTT4x
B6EHDxP5LnbmEMbI/r0GztpxraeWi/u81Vja6hhCf7iUvoLngjsO9g0e+e0CwgigliernvGa8kHS
Tn93grjT8oGNEyYZlsCCEF3NhBUdWiCuqNU5GDTIuQVud/JZWgOkL7jl+sICIrELr3/UB1GQV2sQ
OrqpVm8wZcTI85dxPYdBLALCTx1i6LQ5ncB2+jWZLKAeUEMJQ0xsGJjTBg0t4e2ZbdUm04REIhCB
uKxVNSUdEkEoeBhKNjFa2C7ersMEaGG9t3EYmaVhwNNioJQqXnqWTUXIO7NajpweCIc4KhJcw16E
7JKfoaKXdZCSuEvwNheiWvpdtYGQmLnXiJSigidNpNhjOw8dir0l7UQoJYayv5x2payaILewU55D
nT084QDGiKLxpLys1h0kdpy2ay08JrU3LlkNXci0oZGWXQHcUPQbT0lhuDa2e/oHHOMohNN4NaId
RKiK37xGu1U6xvMZ8xdw5EbyhOo1+andsmUYUaiaGd7n3+LnGyhjeJMhVM+jug2HKeg21XhmW+BW
W24rrZyLFdTmwiOxia0jF354zITEGeuAbHPXhdspUiTjSyGOaIkLmOjZgnjPHTsQtsOT+b81afJa
UvU1bDy/Pj6eGOxwxzvR0NqUrYrVcJ+8ghBUS4hes+1d7b8DWWOWUsk6YrPWiklU0a0JrJo3upbu
yZpl2TqwcUVddYWEJ2DsvhMurpdI/jBNyibAY2u+ErlYqdCEzXtCqKI4moyMKwqsL/SAxMOq9rpF
GyiGFWGtRSWTIp3kJ5kjWMuZGwuDrIBmgeZp5woYx5QYz5zlszRVuSvECVXO3nGGqJbbWTmNacI3
kXgJPO8mhLm2HcBp5pETV6EWG+pE2SVfpq0xYsyFxnsA6gNZbq00VsLP7wBWL8I0F8G+ZMB4mHyk
9NYw1AQmHnGjcbMVx2wtHL+OnSYdWRAlDfpI947AOvRBmNI+SunO37C7kinChIBs/hlg
