diff options
author | Gleb Shchepa <gshchepa@mysql.com> | 2010-05-31 16:52:19 +0400 |
---|---|---|
committer | Gleb Shchepa <gshchepa@mysql.com> | 2010-05-31 16:52:19 +0400 |
commit | 3ca98f76695772bf8e55cac20e1d197a7b6e3615 (patch) | |
tree | a2e471c1c7859e245a55b668173deda4acb02b85 /sql/item_cmpfunc.cc | |
parent | 5c72bee9d4b935015db83ff1b8b5baf72c975e32 (diff) | |
download | mariadb-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.cc | 19 |
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(); } } |