[MDEV-7599] Wrong result with ALL subquery returning NULL - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 5.5.57
Component/s: Optimizer
Labels:
- upstream
- verified

Description

CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,6),(2,4);

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

EXPLAIN EXTENDED

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

SET SESSION optimizer_switch = "materialization=off";

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

EXPLAIN EXTENDED

SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

MariaDB [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

Empty set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

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

| id   | select_type  | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                  |

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

|    1 | PRIMARY      | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using where; Using index                               |

|    2 | MATERIALIZED | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using index                                            |

|    2 | MATERIALIZED | t2x   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer (flat, BNL join)                     |

|    2 | MATERIALIZED | t2y   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (incremental, BNL join) |

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

4 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                   |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` `t2x` join `test`.`t2` `t2y` where (`test`.`t2y`.`c` = `test`.`t2x`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`MIN(a)`)))))))) |

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

1 row in set (0.00 sec)

MariaDB [test]> SET SESSION optimizer_switch = "materialization=off";

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

+------+

| a    |

+------+

|    2 |

+------+

1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED

    -> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;

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

| id   | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                  |

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

|    1 | PRIMARY            | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using where; Using index                               |

|    2 | DEPENDENT SUBQUERY | t1    | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using index                                            |

|    2 | DEPENDENT SUBQUERY | t2x   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer (flat, BNL join)                     |

|    2 | DEPENDENT SUBQUERY | t2y   | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (incremental, BNL join) |

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

4 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                    |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` `t2x` join `test`.`t2` `t2y` where (`test`.`t2y`.`c` = `test`.`t2x`.`b`) having trigcond((<cache>(`test`.`t1`.`a`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))))))) |

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

1 row in set (0.00 sec)

5.5 commit fdd6c111c254c5044cd9b6c2f7e4d0c74f427a79.
Also reproducible on previous 5.5 releases, 10.0, MySQL 5.6, 5.7 (only MySQL returns 2 rows where MariaDB returns 1).

Attachments

Issue Links

links to

Bug #75967 - Wrong result (extra rows) with SQ and comparison to NULL, materialization=off

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2015-02-19 15:11

After fixing, also check test case from ~~MDEV-7602~~. It is not simplified and hence ugly, but it should be obvious whether it's fixed or not. If not, please re-open ~~MDEV-7602~~ and assign it to me.

Elena Stepanova added a comment - 2015-02-19 15:11 After fixing, also check test case from MDEV-7602 . It is not simplified and hence ugly, but it should be obvious whether it's fixed or not. If not, please re-open MDEV-7602 and assign it to me.

Elena Stepanova added a comment - 2016-01-25 18:32 - edited

Another case, which does not require switching off materialization:

CREATE TABLE t1 (f1 varchar(10));

INSERT INTO t1 VALUES ('foo'),('bar');

CREATE TABLE t2 (f2 varchar(10), key(f2));

INSERT INTO t2 VALUES ('baz'),('qux');

CREATE TABLE t3 (f3 varchar(10));

INSERT INTO t3 VALUES ('abc'),('def');

# Join returns no rows (obviously)

SELECT * FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) ;

# Max returns NULL (correctly)

SELECT MAX(t2a.f2) FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2);

# But query with ALL returns something

SELECT * FROM t1 WHERE f1 = ALL(

  SELECT MAX(t2a.f2)

  FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2)

);

EXPLAIN EXTENDED

SELECT * FROM t1 WHERE f1 = ALL(

  SELECT MAX(t2a.f2)

  FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2)

);

Actual result
MariaDB [test]> SELECT * FROM t1 WHERE f1 = ALL(
-> SELECT MAX(t2a.f2)
-> FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2)
-> );
+------+
\| f1 \|
+------+
\| bar \|
+------+
1 row in set (0.00 sec)

MariaDB [test]>
MariaDB [test]> EXPLAIN EXTENDED
-> SELECT * FROM t1 WHERE f1 = ALL(
-> SELECT MAX(t2a.f2)
-> FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2)
-> );
+------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|
+------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
\| 1 \| PRIMARY \| t1 \| ALL \| NULL \| NULL \| NULL \| NULL \| 2 \| 100.00 \| Using where \|
\| 2 \| DEPENDENT SUBQUERY \| t2a \| index \| NULL \| f2 \| 13 \| NULL \| 2 \| 100.00 \| Using index \|
\| 2 \| DEPENDENT SUBQUERY \| t2b \| index \| f2 \| f2 \| 13 \| NULL \| 2 \| 100.00 \| Using index; Using join buffer (flat, BNL join) \|
\| 2 \| DEPENDENT SUBQUERY \| t3 \| ALL \| NULL \| NULL \| NULL \| NULL \| 2 \| 100.00 \| Using where; Using join buffer (incremental, BNL join) \|
+------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+

According to documentation, the last condition with MAX should return NULL, hence no rows should be selected.

Elena Stepanova added a comment - 2016-01-25 18:32 - edited Another case, which does not require switching off materialization: CREATE TABLE t1 (f1 varchar(10)); INSERT INTO t1 VALUES ('foo'),('bar'); CREATE TABLE t2 (f2 varchar(10), key(f2)); INSERT INTO t2 VALUES ('baz'),('qux'); CREATE TABLE t3 (f3 varchar(10)); INSERT INTO t3 VALUES ('abc'),('def'); # Join returns no rows (obviously) SELECT * FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) ; # Max returns NULL (correctly) SELECT MAX(t2a.f2) FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2); # But query with ALL returns something SELECT * FROM t1 WHERE f1 = ALL( SELECT MAX(t2a.f2) FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) ); EXPLAIN EXTENDED SELECT * FROM t1 WHERE f1 = ALL( SELECT MAX(t2a.f2) FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) ); Actual result MariaDB [test]> SELECT * FROM t1 WHERE f1 = ALL( -> SELECT MAX(t2a.f2) -> FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) -> ); +------+ | f1 | +------+ | bar | +------+ 1 row in set (0.00 sec) MariaDB [test]> MariaDB [test]> EXPLAIN EXTENDED -> SELECT * FROM t1 WHERE f1 = ALL( -> SELECT MAX(t2a.f2) -> FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 ON (f3 = t2b.f2) -> ); +------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2a | index | NULL | f2 | 13 | NULL | 2 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | t2b | index | f2 | f2 | 13 | NULL | 2 | 100.00 | Using index; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (incremental, BNL join) | +------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+ According to documentation , the last condition with MAX should return NULL, hence no rows should be selected.

Igor Babaev (Inactive) added a comment - 2017-05-12 18:09 - edited

With materialization=off the following query returns a wrong result set:

ariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ;

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

| b    | c    |

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

|    2 |    4 |

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

Using join_cache_level=0 doesn't help

MariaDB [test]> set join_cache_level=0;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ;

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

| b    | c    |

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

|    2 |    4 |

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

1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ;

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

| id   | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

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

|    1 | PRIMARY            | t2    | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |

|    2 | DEPENDENT SUBQUERY | t2    | ALL   | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |

|    2 | DEPENDENT SUBQUERY | t1    | index | NULL          | a    | 5       | NULL |    4 |   100.00 | Using index |

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

3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                            |

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

| Note  | 1003 | select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) having trigcond((<cache>(`test`.`t2`.`b`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))))))) |

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

1 row in set (0.00 sec)

At the same time if we prohibit using the index t1(a) the returned result set becomes correct:

MariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1 USE INDEX(), t2 WHERE t2.c = t2.b ) ;

Empty set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1 USE INDEX(), t2 WHERE t2.c = t2.b ) ;

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

| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

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

|    1 | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |

|    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |

|    2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |             |

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

3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                |

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

| Note  | 1003 | select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` USE INDEX () join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) having trigcond((<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`))))))))) |

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

1 row in set (0.00 sec)

Igor Babaev (Inactive) added a comment - 2017-05-12 18:09 - edited With materialization=off the following query returns a wrong result set: ariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ; +------+------+ | b | c | +------+------+ | 2 | 4 | +------+------+ Using join_cache_level=0 doesn't help MariaDB [test]> set join_cache_level=0; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ; +------+------+ | b | c | +------+------+ | 2 | 4 | +------+------+ 1 row in set (0.00 sec) MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ; +------+--------------------+-------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | index | NULL | a | 5 | NULL | 4 | 100.00 | Using index | +------+--------------------+-------+-------+---------------+------+---------+------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> SHOW WARNINGS; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) having trigcond((<cache>(`test`.`t2`.`b`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))))))) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) At the same time if we prohibit using the index t1(a) the returned result set becomes correct: MariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1 USE INDEX(), t2 WHERE t2.c = t2.b ) ; Empty set (0.00 sec) MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1 USE INDEX(), t2 WHERE t2.c = t2.b ) ; +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> SHOW WARNINGS; +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` USE INDEX () join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) having trigcond((<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`))))))))) | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

Igor Babaev (Inactive) added a comment - 2017-05-12 18:40 - edited

We see the difference here:

having trigcond((<cache>(`test`.`t2`.`b`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))) // wrong result

having trigcond((<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`))))))    // correct result

Let's find out why caching <ref_null_helper>(min(`test`.`t1`.`a`))) matters.

Here's the explanation.
When <ref_null_helper>(min(`test`.`t1`.`a`)) is <ref_null_helper>(min(`test`.`t1`.`a`)) evaluated it sets the was_null flag of Item_in_subselect to true. When the value of <ref_null_helper>(min(`test`.`t1`.`a`)) is taken from cache nobody set was_null to true. So Item_in_subselec thinks that its subquery does not return any rows.
If we add an additional row with two different components to t2 this additional row will appear in the result set:

MariaDB [test]> INSERT INTO t2 VALUES (8,9);

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ;

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

| b    | c    |

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

|    2 |    4 |

|    8 |    9 |

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

Igor Babaev (Inactive) added a comment - 2017-05-12 18:40 - edited We see the difference here: having trigcond((<cache>(`test`.`t2`.`b`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))) // wrong result having trigcond((<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))) // correct result Let's find out why caching <ref_null_helper>(min(`test`.`t1`.`a`))) matters. Here's the explanation. When <ref_null_helper>(min(`test`.`t1`.`a`)) is <ref_null_helper>(min(`test`.`t1`.`a`)) evaluated it sets the was_null flag of Item_in_subselect to true. When the value of <ref_null_helper>(min(`test`.`t1`.`a`)) is taken from cache nobody set was_null to true. So Item_in_subselec thinks that its subquery does not return any rows. If we add an additional row with two different components to t2 this additional row will appear in the result set: MariaDB [test]> INSERT INTO t2 VALUES (8,9); Query OK, 1 row affected (0.00 sec) MariaDB [test]> SELECT * FROM t2 WHERE b != ALL ( SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b ) ; +------+------+ | b | c | +------+------+ | 2 | 4 | | 8 | 9 | +------+------+

Igor Babaev (Inactive) added a comment - 2017-05-13 06:52

Now let's find out why for the query that does not use indexes the value of <ref_null_helper>(min(`test`.`t1`.`a`)) is not cached.

Igor Babaev (Inactive) added a comment - 2017-05-13 06:52 Now let's find out why for the query that does not use indexes the value of <ref_null_helper>(min(`test`.`t1`.`a`)) is not cached.

Igor Babaev (Inactive) added a comment - 2017-05-13 19:32 - edited

Ok, here's what happens.
With indexes activated the function opt_sum_query() applies the MIN/MAX
optimization to MIN(a) and substitutes it for a constant item. Later
the Item_in_subselect with the subquery

(SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b)

is converted into an EXISTS subquery. This conversion injects a new condition into the subquery. The subquery becomes correlated and this invalidates the applied
MIN/MAX optimization. So any references to MIN(a) cannot be considered as references
to a constant and should not be cached.

This bug appeared in 5.3 when the code for the cost base choice between
materialization and in-to-exists transformation of non-correlated
IN subqueries was introduced. Before this code in-to-exists
transformations were always performed before the call of opt_sum_query().

Igor Babaev (Inactive) added a comment - 2017-05-13 19:32 - edited Ok, here's what happens. With indexes activated the function opt_sum_query() applies the MIN/MAX optimization to MIN(a) and substitutes it for a constant item. Later the Item_in_subselect with the subquery (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b) is converted into an EXISTS subquery. This conversion injects a new condition into the subquery. The subquery becomes correlated and this invalidates the applied MIN/MAX optimization. So any references to MIN(a) cannot be considered as references to a constant and should not be cached. This bug appeared in 5.3 when the code for the cost base choice between materialization and in-to-exists transformation of non-correlated IN subqueries was introduced. Before this code in-to-exists transformations were always performed before the call of opt_sum_query().

Igor Babaev (Inactive) added a comment - 2017-05-16 15:58

The fix for this bug was pushed into the 5.5 tree.
It should be applied upstream as it is.

Igor Babaev (Inactive) added a comment - 2017-05-16 15:58 The fix for this bug was pushed into the 5.5 tree. It should be applied upstream as it is.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2015-02-17 14:02

Updated:: 2017-06-07 23:29

Resolved:: 2017-05-16 15:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server