maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #03473
bzr commit into file:///home/tsk/mprog/src/5.3-mwl89/ branch (timour:2803)
#At file:///home/tsk/mprog/src/5.3-mwl89/ based on revid:timour@xxxxxxxxxxxx-20100716121055-6pesx07gvsmivwm3
2803 timour@xxxxxxxxxxxx 2010-07-18
MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation
Step1 in the separation of the creation of IN->EXISTS equi-join conditions from
their injection. The goal of this separation is to make it possible that the
IN->EXISTS conditions can be used for cost estimation without actually modifying
the subquery.
This patch separates single_value_in_to_exists_transformer() into two methods:
- create_single_value_in_to_exists_cond(), and
- inject_single_value_in_to_exists_cond()
The patch performs minimal refactoring of the code so that it is easier to solve
problems resulting from the separation. There is a lot to be simplified in this
code, but this will be done separately.
modified:
sql/item_subselect.cc
sql/item_subselect.h
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2010-07-16 12:10:55 +0000
+++ b/sql/item_subselect.cc 2010-07-18 11:46:08 +0000
@@ -1521,16 +1521,35 @@ Item_in_subselect::single_value_transfor
*/
Item_subselect::trans_res
-Item_in_subselect::single_value_in_to_exists_transformer(JOIN * join, Comp_creator *func)
+Item_in_subselect::single_value_in_to_exists_transformer(JOIN * join,
+ Comp_creator *func)
+{
+ Item *where_term;
+ Item *having_term;
+ Item_subselect::trans_res res;
+
+ res= create_single_value_in_to_exists_cond(join, func,
+ &where_term, &having_term);
+ if (res != RES_OK)
+ return res;
+ res= inject_single_value_in_to_exists_cond(join, func,
+ where_term, having_term);
+ return res;
+}
+
+
+Item_subselect::trans_res
+Item_in_subselect::create_single_value_in_to_exists_cond(JOIN * join,
+ Comp_creator *func,
+ Item **where_term,
+ Item **having_term)
{
SELECT_LEX *select_lex= join->select_lex;
- DBUG_ENTER("Item_in_subselect::single_value_in_to_exists_transformer");
+ DBUG_ENTER("Item_in_subselect::create_single_value_in_to_exists_cond");
- select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
if (join->having || select_lex->with_sum_func ||
select_lex->group_list.elements)
{
- bool tmp;
Item *item= func->create(expr,
new Item_ref_null_helper(&select_lex->context,
this,
@@ -1546,132 +1565,199 @@ Item_in_subselect::single_value_in_to_ex
*/
item= new Item_func_trig_cond(item, get_cond_guard(0));
}
-
+
+ if (item->fix_fields(thd, 0))
+ DBUG_RETURN(RES_ERROR);
+
+ *having_term= item;
+ *where_term= NULL;
+ }
+ else
+ {
+ Item *item= (Item*) select_lex->item_list.head();
+
+ if (select_lex->table_list.elements)
+ {
+ Item *having= item;
+ Item *orig_item= item;
+
+ item= func->create(expr, item);
+ if (!abort_on_null && orig_item->maybe_null)
+ {
+ having= new Item_is_not_null_test(this, having);
+ if (left_expr->maybe_null)
+ {
+ if (!(having= new Item_func_trig_cond(having,
+ get_cond_guard(0))))
+ DBUG_RETURN(RES_ERROR);
+ }
+
+ if (having->fix_fields(thd, 0))
+ DBUG_RETURN(RES_ERROR);
+
+ *having_term= having;
+
+ item= new Item_cond_or(item,
+ new Item_func_isnull(orig_item));
+ }
+ /*
+ If we may encounter NULL IN (SELECT ...) and care whether subquery
+ result is NULL or FALSE, wrap condition in a trig_cond.
+ */
+ if (!abort_on_null && left_expr->maybe_null)
+ {
+ if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
+ DBUG_RETURN(RES_ERROR);
+ }
+
+ if (item->fix_fields(thd, 0))
+ DBUG_RETURN(RES_ERROR);
+
+ *where_term= item;
+ }
+ else
+ {
+ if (select_lex->master_unit()->is_union())
+ {
+ /*
+ comparison functions can't be changed during fix_fields()
+ we can assign select_lex->having here, and pass 0 as last
+ argument (reference) to fix_fields()
+ */
+ Item *new_having=
+ func->create(expr,
+ new Item_ref_null_helper(&select_lex->context, this,
+ select_lex->ref_pointer_array,
+ (char *)"<no matter>",
+ (char *)"<result>"));
+ if (!abort_on_null && left_expr->maybe_null)
+ {
+ if (!(new_having= new Item_func_trig_cond(new_having,
+ get_cond_guard(0))))
+ DBUG_RETURN(RES_ERROR);
+ }
+
+ if (new_having->fix_fields(thd, 0))
+ DBUG_RETURN(RES_ERROR);
+
+ *having_term= new_having;
+ *where_term= NULL;
+ }
+ else
+ {
+ *having_term= NULL;
+ *where_term= (Item*) select_lex->item_list.head();
+ }
+ }
+ }
+
+ DBUG_RETURN(RES_OK);
+}
+
+
+
+Item_subselect::trans_res
+Item_in_subselect::inject_single_value_in_to_exists_cond(JOIN * join,
+ Comp_creator *func,
+ Item *where_term,
+ Item *having_term)
+{
+ SELECT_LEX *select_lex= join->select_lex;
+ bool fix_res;
+ DBUG_ENTER("Item_in_subselect::single_value_in_to_exists_transformer");
+
+ select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+ if (join->having || select_lex->with_sum_func ||
+ select_lex->group_list.elements)
+ {
/*
AND and comparison functions can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
- select_lex->having= join->having= and_items(join->having, item);
- if (join->having == item)
- item->name= (char*)in_having_cond;
+ select_lex->having= join->having= and_items(join->having, having_term);
+ if (join->having == having_term)
+ having_term->name= (char*)in_having_cond;
select_lex->having_fix_field= 1;
/*
we do not check join->having->fixed, because Item_and (from and_items)
or comparison function (from func->create) can't be fixed after creation
*/
- tmp= join->having->fix_fields(thd, 0);
+ if (!join->having->fixed)
+ fix_res= join->having->fix_fields(thd, 0);
select_lex->having_fix_field= 0;
- if (tmp)
+ if (fix_res)
DBUG_RETURN(RES_ERROR);
}
else
{
- Item *item= (Item*) select_lex->item_list.head();
-
if (select_lex->table_list.elements)
{
- bool tmp;
- Item *having= item, *orig_item= item;
+ Item *orig_item= (Item*) select_lex->item_list.head();
select_lex->item_list.empty();
select_lex->item_list.push_back(new Item_int("Not_used",
(longlong) 1,
MY_INT64_NUM_DECIMAL_DIGITS));
select_lex->ref_pointer_array[0]= select_lex->item_list.head();
- item= func->create(expr, item);
if (!abort_on_null && orig_item->maybe_null)
{
- having= new Item_is_not_null_test(this, having);
- if (left_expr->maybe_null)
- {
- if (!(having= new Item_func_trig_cond(having,
- get_cond_guard(0))))
- DBUG_RETURN(RES_ERROR);
- }
/*
Item_is_not_null_test can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
- having->name= (char*)in_having_cond;
- select_lex->having= join->having= having;
+ having_term->name= (char*)in_having_cond;
+ select_lex->having= join->having= having_term;
select_lex->having_fix_field= 1;
/*
we do not check join->having->fixed, because Item_and (from
and_items) or comparison function (from func->create) can't be
fixed after creation
*/
- tmp= join->having->fix_fields(thd, 0);
+ if (!join->having->fixed)
+ fix_res= join->having->fix_fields(thd, 0);
select_lex->having_fix_field= 0;
- if (tmp)
+ if (fix_res)
DBUG_RETURN(RES_ERROR);
- item= new Item_cond_or(item,
- new Item_func_isnull(orig_item));
- }
- /*
- If we may encounter NULL IN (SELECT ...) and care whether subquery
- result is NULL or FALSE, wrap condition in a trig_cond.
- */
- if (!abort_on_null && left_expr->maybe_null)
- {
- if (!(item= new Item_func_trig_cond(item, get_cond_guard(0))))
- DBUG_RETURN(RES_ERROR);
}
/*
TODO: figure out why the following is done here in
single_value_transformer but there is no corresponding action in
row_value_transformer?
*/
- item->name= (char *)in_additional_cond;
+ where_term->name= (char *)in_additional_cond;
/*
AND can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
- select_lex->where= join->conds= and_items(join->conds, item);
+ select_lex->where= join->conds= and_items(join->conds, where_term);
select_lex->where->top_level_item();
/*
we do not check join->conds->fixed, because Item_and can't be fixed
after creation
*/
- if (join->conds->fix_fields(thd, 0))
- DBUG_RETURN(RES_ERROR);
+ if (!join->conds->fixed && join->conds->fix_fields(thd, 0))
+ DBUG_RETURN(RES_ERROR);
}
else
{
- bool tmp;
if (select_lex->master_unit()->is_union())
{
- /*
- comparison functions can't be changed during fix_fields()
- we can assign select_lex->having here, and pass 0 as last
- argument (reference) to fix_fields()
- */
- Item *new_having=
- func->create(expr,
- new Item_ref_null_helper(&select_lex->context, this,
- select_lex->ref_pointer_array,
- (char *)"<no matter>",
- (char *)"<result>"));
- if (!abort_on_null && left_expr->maybe_null)
- {
- if (!(new_having= new Item_func_trig_cond(new_having,
- get_cond_guard(0))))
- DBUG_RETURN(RES_ERROR);
- }
- new_having->name= (char*)in_having_cond;
- select_lex->having= join->having= new_having;
+ having_term->name= (char*)in_having_cond;
+ select_lex->having= join->having= having_term;
select_lex->having_fix_field= 1;
/*
we do not check join->having->fixed, because comparison function
(from func->create) can't be fixed after creation
*/
- tmp= join->having->fix_fields(thd, 0);
+ if (!join->having->fixed)
+ fix_res= join->having->fix_fields(thd, 0);
select_lex->having_fix_field= 0;
- if (tmp)
+ if (fix_res)
DBUG_RETURN(RES_ERROR);
}
else
@@ -1679,11 +1765,11 @@ Item_in_subselect::single_value_in_to_ex
// it is single select without tables => possible optimization
// remove the dependence mark since the item is moved to upper
// select and is not outer anymore.
- item->walk(&Item::remove_dependence_processor, 0,
- (uchar *) select_lex->outer_select());
- item= func->create(left_expr, item);
+ where_term->walk(&Item::remove_dependence_processor, 0,
+ (uchar *) select_lex->outer_select());
+ where_term= func->create(left_expr, where_term);
// fix_field of item will be done in time of substituting
- substitution= item;
+ substitution= where_term;
have_to_be_excluded= 1;
if (thd->lex->describe)
{
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2010-07-16 10:52:02 +0000
+++ b/sql/item_subselect.h 2010-07-18 11:46:08 +0000
@@ -425,8 +425,18 @@ public:
trans_res select_in_like_transformer(JOIN *join, Comp_creator *func);
trans_res single_value_transformer(JOIN *join, Comp_creator *func);
trans_res row_value_transformer(JOIN * join);
+
trans_res single_value_in_to_exists_transformer(JOIN * join,
Comp_creator *func);
+ trans_res create_single_value_in_to_exists_cond(JOIN * join,
+ Comp_creator *func,
+ Item **where_term,
+ Item **having_term);
+ trans_res inject_single_value_in_to_exists_cond(JOIN * join,
+ Comp_creator *func,
+ Item *where_term,
+ Item *having_term);
+
trans_res row_value_in_to_exists_transformer(JOIN * join);
virtual bool exec();
longlong val_int();
# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: timour@xxxxxxxxxxxx-20100718114608-wiz9ji9z80pzjw2k
# target_branch: file:///home/tsk/mprog/src/5.3-mwl89/
# testament_sha1: 22ab922f87bbce26a98a8b6b7f76fc239c810501
# timestamp: 2010-07-18 14:46:15 +0300
# source_branch: file:///home/tsk/mprog/src/5.3/
# base_revision_id: timour@xxxxxxxxxxxx-20100716121055-\
# 6pesx07gvsmivwm3
#
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQphvfcABdt/gF0wAgB59///
/v/e6r////5gDH93L33318LfT0PUJHvIdX2aQqdgxU3YPvYB60D4SKETTU8jTSepgmmSYk3qZE3k
iGRpiM01M0j1DRhAlCaJiGjQimao0AaD0jQGTQ0AAAADQYhCNNRNT1BoyPUe1JoBoNAGIAHqDQAA
JNVUMmjRoAAAAAAAAAAAAAbSqT01Gah5JoGRo0AaDCNMg0AAANAyAkSCBNACYmgFPT1Jsgpp4psU
0B6nqNGgeoDT1LRWMSQNAZjP84PxlKfat9Ylwf0JUfGhEOJwU+WzLHo1okFRMUgwf514Q22+F1Ls
yyaWhRKHDamnSOyY8d461feUTtZcYP45hGFWayuFsMyCPh905WaWM6Q7VL/rkZv6IgVnVc1VbqQU
XsalIw7t79eNPVr2Y/yTzJIG7OxEIMgBJEaKieyhSUVgS3Be4ekTphHDyMhal1CELtkIQpEgRIAm
QMUUgoTse7Q9RbiGQKYpU9ipjIlzC+lIkxNHeyAKqfaeQbYgbViwoK8J/Que9ZDTo4sc8+bRfvQm
Rr1SpkX9UFMkEqxY0FbZFKYl4q2wE6WRaXONKUYPGcOji7l5GfRA1r5wLrQh3CGWnJfMtHngrxw0
4lOqA1s6IzTTDzIQS3bnnCzxSKQbAb7a4v4aawSYQWaNnSvKRtiqi6wSne/7GWxbgulIDijRTQjC
vRdqz23wrs1WaQd1D3rwBRQqn7cDb9wjB0xyXlbHPSpdIV/i9IVOBlmkExLK/s1rNkVdFoDNj5Rf
LqdnYySmuZ0xxh1mtJ70x3roL8kbNVXLeu5CbhM/2b8DEiVSgg69JhJPJaKCPwNl2wWGSyknwjnZ
cMqzzokkoLYHw9eE0pgfDK2sbOXfUzKsmfGYkI68timjKIntlzk2AJJaULX6+0KyHKipOJOclLPe
F2pJooM2Jh7j3r1/CYrWNwUcKTbIk6YajiXw3n8Jqc23zAY6daknU21rt3lpqaoRFFWUrOeRChIY
m5sobE/2WxiGEbRRDJPK5Nds34h5frz7Q79h3AQXE9U+IsAVRJ9CUunj0jOma7zJdrJh7GgyM8WC
y+EBq7IQUB9KJIVkIUPUFC6QSYTRInFtELcEIJTBq0+ftsneBRIhps/4++iF678Q+XR3nRF1XkAn
TqM5SUOAxym/uc5QoZp/Ro+AVyAvayF1QTUmxzbBoDV9sgPNOkXmgUzE56mGXKWFpZtmErk2tWYt
pOxz8Ac1QR6A4UBqMGUTSR1wWKsq+Ru6HqHW3hAroZHczZLYYyl1bliRnKjw/sd+CSZX43cgVNoe
mGRA5H+G29NLVshc6QC6HURwnHWbAXCKR5yzHbe5MZK0psUui4ZYEqh0yqHPyUPLoYRqJhf+JJTB
9bKO4eDWcpGtLMQT1ZaWVObmtyUgygMzMJtssHDJg/80poMsryqhjvzYhu13BlsOo3gG7HQPNGtw
LwuGGg6CbP75lTeOmIbnJbdAY7w5rz5hAwJahqco6vSTAWdvGA4HA/WSO3kfZppRnORobanVw0Sa
5YbQeO7q5Lm8Uje5cYYXUeXZaV0zqa4DOBtxfG9hzEmbVytF1c2k4MDhfuiqYAUUaRcYEUuUwWg5
hbd0J1mQ35FghdZxNCEDJKk5xSY3NqIQC7pkMQMAGOryzsoHkcO5ajljhL6bc16xtlQSHybPMd1A
g3JLt7f88v41ZG2Nv+epKj0RgdHkGWTDXne8/rJnP8a6uX2fKN4fMQD2JE/Yn/vzSy0OtHBYU/Bf
K7gHc4TxIRj7z4QWukbgP3QmRUfS1cOH0Y1xXbuWVUkZkNRnL8Wplfsy5/oiRSF5WBiC+5kZEVCo
WhXBSFq6w7OpUojossb1pWhK5HdYD7PHCOXUVh9aPLEkjn+1d/39KDpPS/cjwdYLx/cIJgp/AyKh
YRNEAmXVXsCaL7BCiKowtnQ+UTRgo2NSiUFFVlIlKDKFMKCsZGKIsVWVTAo0JuiiKFXm89In19T8
249J6y46vXgwfp7Trz14s7vLwuqwgS6HkyI0aXn6zR7v+EpCuxiL+9YNtL58SkkcCDma5gVf0/GS
yX0W/b9BKqmu1bDAcyoGXxcyK1Rg4uCLVeECjLxaTGPsF37MXt3iXHag2sOYmM3Ya4cbZPDX6Wko
nkAWm+0mNcZvxznOWHfLqKRHFS5vKfFcNJruWZ6deklTFU+AWyB5U6S17IXOhM+SGNSgbRJFiEi8
tiINASk2mNtia3uGTTFC6wGtRh5rMdloUB1NrID6gqiA6T83QV9GbcWaZf0OSpTizaj+pmEpBR5o
otZqe3zwtHoVFQgoUjIlOxbXGLJYITx1q+ryncxx49M8TPE7ujdeOcL/KQY9nJPbmPICvRGnRajB
AVKIWpGc3UQFqkpkCNCOkM9SdXDCQuPk5M72atRYjBqslk0vmtgZrRBagRsOrbm4UgWEsnhEKZLk
5taiq1AgE/dK0KYTkcrGHxdjxiAcTZ70SAGxsC6ANVdzYa0UCUHZohG9QIXJCtRWV6A5B2BceU73
zHE8tCQ+RsUletA8wg0/Q8hBIGLwA8VH5exxNxAvE3TDz9AufwL6NOZn0qzH51GDFowSWCExHaMA
kmILRNT7iBKa2FmeqnmN4dZ3p8jTHbAoAguFo1wBklC0tQmESY/vce03yJItBe8aprWSCjPNCC8C
QQizY5nkNxPxCCRs0wNBuA0GTvQl05UPiSCejgPDlBtwd33uMe4uh1q+qcYxPBUgJUrCxpC7QsQG
5F3BtwYFvRVcYCSKkLxVmlmTLHb5qZcgLRfA20w4fkuBjeH1ZUZltS9gKwwswjiDKEe3SNkQ3y5X
WMMyQEgK7R6zUYUewD1GOdGhP3shNQmLvA9p5AayRA9/p0k9iNAlpLTqsI1HEKo9JOVjVXLKoEFD
SwfnQpIBGdB7EgrdYm89S5aVrKK1wtYqcwTR4YIgoDhkESMYVAqqpERFIsZRYQraeFQCIuFVVKI7
a2N69IdBk9RTyCVglfkqBep4FNpEI41CgxyiwJQlQ1KRKYnCQmG5qAY0A9AZD16QV4WwmgqgsQwg
JwkbCyglJoSKJoUbzGVo0VDdiZS1XJGCsaY02KQlVSWqXZ1sN3ao3Om8gFjiHST6ZZwjtBLquy4W
Imc4mSBANBOeAIwmEa0KalkaC0qGHPbFjbUmGSPEJTbSMwjDfsknG3KrH6rsOISyYNcZUaI2igQR
b0WzR3QMKKENSim0O4pz3abL8hU83K3Ge+49wFhrLu4O07jiZzkXnRtqbUQ011ygGpUkGsSakivS
kVRjRVYkdKA593euaitS3E2KjYNXR6nZwhW5RK5QWyWROamkBCDaOZ5TRzj0bRXy5fZyMTAxdoyG
zme9RmJQRFVjMlEQy0UhgSMXKHJEgbRJBOqICLJ9izq5mbeJeTUtv2D0uJzT3xERA+HKEcNIu8xk
uvKac7VskFIebUiZUS1hs9IWlVd6T723uxL1HCFDTgsSiT3ZkTA1ygnXm0mAseKb8/+BixWEkpCF
PGU3csUUAgFeiZIQrO3vJAyXC3eXIoxfFCwY02JiGkNMPVUs9eCmi1IBvfDXTA1AMXMMLpc5Rjuw
5kuJxTIMbA3DbBvPgSduItGgFutRRFlFeWDdiJKiDFqAuszkOzvoJe/E2XAHXt0CXiWrdU5KEjW1
pOajEqXrA2LPZns6xlbSgllnpyq8QKEVDUEeoBlc+PZ5O/4RmqVOQe/3VtpQLnHwHhdH1gdR4+AZ
faaAFxBmtIBoXONHaXcPC6RKBKOB4HX9oGAE3cNWYAYbtJ6CDwP7lKEh0omb/xdyRThQkAphvfc=
