[MDEV-2478] LP:715738 - Wrong result with implicit grouping and empty result set Created: 2011-02-09  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Trivial
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug715738.xml    

 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

-------------------------------------------------------------------------------------------------------------+

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-02-15 ]

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);

  • 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';
Comment by Timour Katchaounov (Inactive) [ 2011-02-15 ]

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.

Comment by Timour Katchaounov (Inactive) [ 2011-03-03 ]

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;

Comment by Timour Katchaounov (Inactive) [ 2011-03-04 ]

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.

Comment by Timour Katchaounov (Inactive) [ 2011-03-22 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 715738

Generated at Thu Feb 08 06:42:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.