| 1165.2.4
|
|
|
Stewart Smith |
14 years ago
|
 |
|
| 1165.2.3
|
|
|
Stewart Smith |
14 years ago
|
|
|
| 1165.2.2
|
|
MySQL Bug #44139: Table scan when NULL appears in IN clause
fixed in MySQL by Gleb, merged into Drizzle by Stewart
SELECT ... WHERE ... IN (NULL, ...) does full table scan,
even if the same query without the NULL uses efficient range scan.
The bugfix for the bug 18360 introduced an optimization:
if
1) all right-hand arguments of the IN function are constants
2) result types of all right argument items are compatible
enough to use the same single comparison function to
compare all of them to the left argument,
then
we can convert the right-hand list of constant items to an array
of equally-typed constant values for the further
QUICK index access etc. (see Item_func_in::fix_length_and_dec()).
The Item_null constant item objects have STRING_RESULT
result types, so, as far as Item_func_in::fix_length_and_dec()
is aware of NULLs in the right list, this improvement efficiently
optimizes IN function calls with a mixed right list of NULLs and
string constants. However, the optimization doesn't affect mixed
lists of NULLs and integers, floats etc., because there is no
unique common comparator.
New optimization has been added to ignore the result type
of NULL constants in the static analysis of mixed right-hand lists.
This is safe, because at the execution phase we care about
presence of NULLs anyway.
1. The collect_cmp_types() function has been modified to optionally
ignore NULL constants in the item list.
2. NULL-skipping code of the Item_func_in::fix_length_and_dec()
function has been modified to work not only with in_string
vectors but with in_vectors of other types.
@ mysql-test/r/func_in.result
Added test case for the bug #44139.
@ mysql-test/t/func_in.test
Added test case for the bug #44139.
@ sql/item_cmpfunc.cc
Bug #44139: Table scan when NULL appears in IN clause
1. The collect_cmp_types() function has been modified to optionally
ignore NULL constants in the item list.
2. NULL-skipping code of the Item_func_in::fix_length_and_dec()
function has been modified to work not only with in_string
vectors but with in_vectors of other types.
modified:
mysql-test/r/func_in.result
mysql-test/t/func_in.test
sql/item_cmpfunc.cc
|
gshchepa at mysql |
14 years ago
|
|
|
| 1165.2.1
|
|
|
Stewart Smith |
14 years ago
|
|
|
| 1165
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1164
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1163
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1162
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1161
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1160
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1159
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1158
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1157
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1156
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1155
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1154
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1153
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1152
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1151
|
|
|
Brian Aker |
14 years ago
|
|
|
| 1150
|
|
|
Brian Aker |
14 years ago
|
|
|
collapse all
