Wrong result with implicit grouping and empty result set

[Philip Stoev]

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 |
+----+-------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+

[Timour Katchaounov (timour)]

Simpler query:

SELECT f4 FROM t3 WHERE ( 2 , 7 ) NOT IN ( SELECT f1 , MIN( f3 ) FROM t2);

* In 5.3 the query always produces wrong result
* In 5.3-mwl89 the query produces wrong result only with materialization ON:
  SET SESSION optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off';

[Timour Katchaounov (timour)]

The bug is present also in MySQL 5.6.2-m5-debug.

[Timour Katchaounov (timour)]

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;

[Timour Katchaounov (timour)]

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.

[Timour Katchaounov (timour)]

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.