[#MDEV-6337] Wrong result (extra row) with TEMPTABLE view and impossible condition

revno: 3628

revision-id: igor@askmonty.org-20130225031611-jk8lyhhjazov66qc

parent: igor@askmonty.org-20130222011312-0n7i0ki83efkz17e

committer: Igor Babaev <igor@askmonty.org>

branch nick: maria-5.3-mdev4177

timestamp: Sun 2013-02-24 19:16:11 -0800

message:

  Fixed bug mdev-4177

  The function remove_eq_cond removes the parts of a disjunction

  for which it has been proved that they are always true. In the

  result of this removal the disjunction may be converted into a

  formula without OR that must be merged into the the AND formula

  that contains the disjunction.

  The merging of two AND conditions must take into account the

  multiple equalities that may be part of each of them.

  These multiple equality must be merged and become part of the

  and object built as the result of the merge of the AND conditions.

  Erroneously the function remove_eq_cond lacked the code that

  would merge multiple equalities of the merged AND conditions.

  This could lead to confusing situations when at the same AND

  level there were two multiple equalities with common members

  and the list of equal items contained only some of these

  multiple equalities.

  This, in its turn, could lead to an incorrect work of the

  function substitute_for_best_equal_field when it tried to optimize

  ref accesses. This resulted in forming invalid TABLE_REF objects

  that were used to build look-up keys when materialized subqueries

  were exploited.

CREATE TABLE t1 (pk1 INT PRIMARY KEY, a INT);

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

CREATE TABLE t2 (pk2 INT PRIMARY KEY);

INSERT INTO t2 VALUES (1),(2),(3),(4),(5);

CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;

SELECT * FROM t1 LEFT JOIN v2 ON a = pk2 WHERE ( pk1 < pk1 OR pk2 = 5 ) AND a = 6;

MariaDB [test]> CREATE TABLE t1 (pk1 INT PRIMARY KEY, a INT);

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t1 VALUES (1,6),(2,2);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]>

MariaDB [test]> CREATE TABLE t2 (pk2 INT PRIMARY KEY);

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t2 VALUES (1),(2),(3),(4),(5);

Query OK, 5 rows affected (0.00 sec)

Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]> CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 ON a = pk2 WHERE ( pk1 < pk1 OR pk2 = 5 ) AND a = 6;

Empty set (0.00 sec)

MariaDB [test]> select @@version;

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

| @@version          |

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

| 10.1.9-MariaDB-log |

commit 94c8f33569ec2b8094928463150567d4dcf67398

Author: Sergey Petrunya <psergey@askmonty.org>

Date:   Wed Oct 29 01:20:45 2014 +0300

    MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled

    - TABLE::create_key_part_by_field() should not set PART_KEY_FLAG in field->flags

      = The reason is that it is used by hash join code which calls it to create a hash

        table lookup structure. It doesn't create a real index.

      = Another caller of the function is TABLE::add_tmp_key(). Made it to set the flag itself.

    - The differences in join_cache.result could also be observed before this patch: one

      could put "FLUSH TABLES" before the queries and get exactly the same difference.

EXPLAIN EXTENDED

SELECT * FROM t1 LEFT JOIN v2 ON a = pk2 WHERE ( pk1 < pk1 OR pk2 = 5 ) AND a = 6;

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

1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    5       100.00  Using where; Using join buffer (flat, BNL join)

2       DERIVED t2      index   NULL    PRIMARY 4       NULL    5       100.00  Using index

Before the patch
Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a` AS `a`,`v2`.`pk2` AS `pk2` from `test`.`t1` left join `test`.`v2` on(1) where ((`test`.`t1`.`a` = 6) and (`v2`.`pk2` = 5))

After the patch
Note 1003 select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a` AS `a`,`v2`.`pk2` AS `pk2` from `test`.`t1` left join `test`.`v2` on((`v2`.`pk2` = 6)) where ((`test`.`t1`.`a` = 6) and (`v2`.`pk2` = 5))

[MDEV-6337] Wrong result (extra row) with TEMPTABLE view and impossible condition Created: 2014-06-12 Updated: 2022-12-02 Resolved: 2022-12-01
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5.38, 10.0.11, 5.3.13
Fix Version/s:	N/A