Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Trivial
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
When executing the following query with materialization, it returns a row, even though the WHERE clause must be false because the subquery in the NOT IN predicate returns ( NULL , NULL ) , which should make the NOT IN predicate FALSE. It seems this is only observed with queries that violate the ONLY_FULL_GROUP_BY SQL mode.
Test case:
SET SESSION optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off';
CREATE TABLE t1 ( f10 varchar(1)) ;
CREATE TABLE t2 ( f1 int(11), f3 int(11), PRIMARY KEY (f1)) ;
CREATE TABLE t3 ( f4 date, f11 varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('1900-01-01','f');
SELECT f4 FROM t3
WHERE ( 2 , 7 ) NOT IN (
SELECT f1 , MIN( f3 ) FROM t2 WHERE ( 'j' ) IN ( SELECT t1.f10 FROM t1 ) );
explain:
-------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
-------------------------------------------------------------------------------------------------------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with materialization=on in maria-5.3 maria-5.3-mwl89
The bug is present also in MySQL 5.6.2-m5-debug.
Re: Wrong result with materialization=on in maria-5.3 maria-5.3-mwl89
Also this query incorrectly produces "1" instead of "NULL":
select ( 2 , 7 ) NOT IN ( SELECT f1, MIN( f3 ) FROM t2) as not_in;
While this query correctly returns NULL:
select ( 2 , 7 ) NOT IN ( SELECT MIN(f1), MIN( f3 ) FROM t2) as not_in;
Re: Wrong result with materialization=on in maria-5.3 maria-5.3-mwl89
Analysis:
In 5.3 the result is wrong both for in-to-exists and materialization.
In 5.3-mwl89 the result is wrong only with materialization.
- IN-TO-EXISTS in 5.3:
The result is incorrect because Item_func_isnull::val_int()
returns the incorrect value of Item_func_isnull::cached_value.
I didn't dig further why this value is incorrect, but it is affected
by the Item->maybe_null property of the field "f1", which is
considered non-nullable.
The strategy works fine in 5.3-mwl89 because there
Item_func_isnull::val_int() has been changed not to use the
cached value at all. This was done to solve a different unrelated
problem.
- MATERIALIZATION in 5.3 and 5.3-mwl89
In both trees the problem is that the analysis for the partial match
strategy relies on meta-data. Column "f1" is considered to be impossible
to contain NULLs, and as a result partial matching doesn't detect that
there is a complete NULL row in
subselect_hash_sj_engine::get_strategy_using_data().
Solution:
Since 5.3-mwl89 has been changed so that things work properly for
IN-TO-EXISTS, I suggest to fix the bug in 5.3-mwl89.
The fix is to check if a table has 0 rows in
subselect_hash_sj_engine::get_strategy_using_schema(), and then
consider all rows NULL-able.
Re: Wrong result with implicit grouping and empty result set
Notice that the bug is present in the latest MySQL 5.5, given non-empty tables,
if the subquery result is empty. There is no related MySQL bug AFAIK.
Launchpad bug id: 715738
Re: Wrong result with materialization=on in maria-5.3 maria-5.3-mwl89
Simpler query:
SELECT f4 FROM t3 WHERE ( 2 , 7 ) NOT IN ( SELECT f1 , MIN( f3 ) FROM t2);
SET SESSION optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off';