summaryrefslogtreecommitdiff
path: root/sql/item_cmpfunc.cc
diff options
context:
space:
mode:
authorGleb Shchepa <gshchepa@mysql.com>2010-05-31 16:52:19 +0400
committerGleb Shchepa <gshchepa@mysql.com>2010-05-31 16:52:19 +0400
commit3ca98f76695772bf8e55cac20e1d197a7b6e3615 (patch)
treea2e471c1c7859e245a55b668173deda4acb02b85 /sql/item_cmpfunc.cc
parent5c72bee9d4b935015db83ff1b8b5baf72c975e32 (diff)
downloadmariadb-git-3ca98f76695772bf8e55cac20e1d197a7b6e3615.tar.gz
Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
when it should use index Sometimes the LEFT/RIGHT JOIN with an empty table caused an unnecessary filesort. Sample query, where t1.i1 is indexed and t3 is empty: SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2 LEFT JOIN t3 ON t2.i2 = t3.i3 ORDER BY t1.i1 LIMIT 5; The server erroneously used an item of empty outer-joined table as a common constant of a Item_equal (multi-equivalence expression). By the fix for the bug 16590 the constant status of such an item has been propagated to st_table::const_key_parts map bits related to other Item_equal argument-related key parts (those are obviously not constant in our case). As far as test_if_skip_sort_order function skips constant prefixes of testing keys, this caused an ignorance of available indices, since some prefixes were marked as constant by mistake.
Diffstat (limited to 'sql/item_cmpfunc.cc')
-rw-r--r--sql/item_cmpfunc.cc19
1 files changed, 17 insertions, 2 deletions
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 19e8385539f..3c871bc0663 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -5466,7 +5466,21 @@ void Item_equal::update_const()
Item *item;
while ((item= it++))
{
- if (item->const_item())
+ if (item->const_item() &&
+ /*
+ Don't propagate constant status of outer-joined column.
+ Such a constant status here is a result of:
+ a) empty outer-joined table: in this case such a column has a
+ value of NULL; but at the same time other arguments of
+ Item_equal don't have to be NULLs and the value of the whole
+ multiple equivalence expression doesn't have to be NULL or FALSE
+ because of the outer join nature;
+ or
+ b) outer-joined table contains only 1 row: the result of
+ this column is equal to a row field value *or* NULL.
+ Both values are inacceptable as Item_equal constants.
+ */
+ !item->is_outer_field())
{
it.remove();
add(item);
@@ -5505,7 +5519,8 @@ void Item_equal::update_used_tables()
{
item->update_used_tables();
used_tables_cache|= item->used_tables();
- const_item_cache&= item->const_item();
+ /* see commentary at Item_equal::update_const() */
+ const_item_cache&= item->const_item() && !item->is_outer_field();
}
}