Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6337

Wrong result (extra row) with TEMPTABLE view and impossible condition

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 5.5.38, 10.0.11, 5.3.13
        • Fix Version/s: 5.3.13, 10.0, 5.5
        • Component/s: None
        • Labels:
          None

          Description

          The problem appeared in 5.3 tree with the following revision:

          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.

          Test case:

          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;

          Expected result: empty set
          Actual result:

          pk1	a	pk2
1	6	5

          On 5.3, the impossible condition pk2 < pk2 can be replaced with 0, the problem still exists. on 5.5, the wrong result disappears after that.

          Reproducible on 5.3 of revno 3788, 5.5 of revno 4216, 10.0 revno 4241

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    sanja Oleksandr Byelkin
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: