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

Wrong result on LEFT JOIN with a SELECT SQ or a merge view, UNION in IN subquery

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.4, 5.5.33, 5.3.12
    • Fix Version/s: 5.5.34, 10.0.6, 5.3.13
    • Component/s: None
    • Labels:
      None

      Description

      The problem appeared on 5.3 tree with the following revision:

      revno: 3574
      revision-id: sanja@montyprogram.com-20120905202358-r2qds8sj178cbjkn
      committer: sanja@montyprogram.com
      timestamp: Wed 2012-09-05 23:23:58 +0300
      message:
        MDEV-486 LP BUG#1010116 fix.
       
        Link view/derived table fields to a real table to check turning the table record to null row.
       
        Item_direct_view_ref wrapper now checks if table is turned to null row.

      CREATE TABLE t1 (state VARCHAR(32), INDEX(state));
      INSERT INTO t1 VALUES ('Indiana'),('Vermont');
       
      CREATE TABLE t2 (state VARCHAR(32));
      INSERT INTO t2 VALUES ('Hawaii'),('Oregon'),('Vermont');
       
      CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* FROM t2, t1;
       
      SELECT * FROM t1 AS outer_t1 LEFT JOIN v1 AS joined_t1
      ON (joined_t1.state = outer_t1.state AND joined_t1.state IN ( SELECT 'Vermont' UNION  SELECT 'Florida' ) );

      Actual result:

      state   state
      Indiana NULL
      Vermont NULL

      Expected result:

      state   state
      Indiana NULL
      Vermont Vermont
      Vermont Vermont
      Vermont Vermont

      Also reproducible with a subquery instead of the view, in this case derived_merge=on is required.
      With a TEMPTABLE view, the result is correct.
      With a SELECT subquery and derived_merge=off, the result is correct.

      MariaDB 5.2 and MySQL 5.6 return the correct result.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: