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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.4, 5.5.33, 5.3.12
    • 5.5.34, 10.0.6, 5.3.13
    • None
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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