Other fix of maybe_null problem and revert of revno: 3608 "MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery from a MERGE view."
Analysis:
The cause of the valgrind warning was an attempt to evaluate a Field that was not yet read.
The reason was that on one hand Item_func_isnotnull was marked as constant by
Item_func_isnotnull::update_used_tables, and this allowed eval_const_cond() to be called.
On the other hand Item_func_isnotnull::val_int() evaluated its argument as if it was not
constant.
Solution:
The fix make sure that Item_func_isnotnull::val_int() doesn't evaluate its argument when
it is constant and cannot be NULL, because the result is known in this case.
This patch almost totally revised the patch for bug mdev-4177.
The latter had too many defects. In particular, it did not
propagate multiple equalities formed when merging a degenerate
disjunct into underlying AND formula.
- Modify the way Item_cond::fix_fields() and Item_cond::eval_not_null_tables()
calculate bitmap for Item_cond_or::not_null_tables():
if they see a "... OR inexpensive_const_false_item OR ..." then the item can
be ignored.
- Updated test results. There can be more warnings produced since parts of WHERE
are evaluated more times.
includes:
* remove some remnants of "Bug#14521864: MYSQL 5.1 TO 5.5 BUGS PARTITIONING"
* introduce LOCK_share, now LOCK_ha_data is strictly for engines
* rea_create_table() always creates .par file (even in "frm-only" mode)
* fix a 5.6 bug, temp file leak on dummy ALTER TABLE
Cleanup: remove TIME_FUZZY_DATE.
Introduce TIME_FUZZY_DATES which means "very fuzzy, the resulting
value is only used for comparison. It can be invalid date, fine, as long as it can be
compared".
Updated many tests results (they're better now).
get_datetime_value() should not double-cache its own Item_cache_temporal items,
but it *should* cache other Item_cache items, such as Item_cache_str.
sql/item.h:
shortcut, to avoid going through the switch in Item::cmp_type()
sql/item_cmpfunc.cc:
even if the item is Item_cache_str - it still needs to be converted and cached.
sql/item_timefunc.h:
all descendants of Item_temporal_func always have cmp_type==TIME_RESULT.
Even Item_date_add_interval, that might have field_type == MYSQL_TYPE_STRING.
IN IN-CLAUSE USING MYISAM OR MEMORY ENGINE
Backport from 5.6. Original message:
The coincidences caused a data loss:
* The query has IN subqueries nested twice,
* the WHERE clause of the inner subquery refers to the
outer field, and the whole WHERE clause returns FALSE,
* the inner subquery has a LEFT JOIN that joins a single
row with a row of NULLs; one of that NULL columns
represents the select list of the subquery.
Normally, that inner subquery should return empty record set.
However, in our case:
* the Item_is_not_null_test item goes constant, since
its underlying field is NULL (because of LEFT JOIN ... ON
FALSE of const table row with a row of nulls);
* we evaluate Item_is_not_null_test::val_int() as a part
of fake HAVING expression of the transformed subquery;
* as far as the underlying field is NULL, we optimize
out the whole fake HAVING expression as FALSE as well
as a whole subquery with a zero result:
Impossible HAVING noticed after reading const tables";
* thus, the optimizer ignores the presence of the WHERE
clause (the WHERE expression is FALSE in our case, so
the subquery should return empty set);
* however, during the evaluation of the
Item_is_not_null_test::val_int() in the optimizer,
it marked its "owner" with the "was_null" flag -- that
forced the subquery to return UNKNOWN instead of empty
set.
That caused a wrong result.
The problem is a regression of the small cleanup in
the fix for the bug11827369 (the Item_is_not_null_test part)
that conflicts with optimizations in the fix for the bug11752543.
Before that regression the Item_is_not_null_test items
never were constants.
The fix is the rollback of Item_is_not_null_test parts
of the bug11827369 fix.
