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

Wrong result (0 instead of NULL) on 2nd execution of PS with LEFT JOIN, TEMPTABLE view

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.34, 10.0.6
    • 5.5.35, 10.0.8
    • None
    • None

    Description

      The problem appeared on the 5.5 tree with the following revision:

      revno: 3922 [merge]
      revision-id: sanja@askmonty.org-20130925141613-wyi3pdd0viulaxtd
      parent: wlad@montyprogram.com-20130924175251-2wvjo1i166cacwam
      parent: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn
      committer: sanja@askmonty.org
      branch nick: work-maria-5.5-merge-5.3
      timestamp: Wed 2013-09-25 17:16:13 +0300
      message:
        merge 5.3 -> 5.5
          ------------------------------------------------------------
          revno: 2502.567.143
          revision-id: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn
          parent: bar@mnogosearch.org-20130916120355-atoza9gj16selqtp
          committer: sanja@askmonty.org
          branch nick: work-maria-5.3-MDEV-5039
          timestamp: Wed 2013-09-25 15:30:13 +0300
          message:
            MDEV-5039: incorrect Item_func_regex::update_used_tables()
            
            Other fix of maybe_null problem and revert of revno: 3608 "MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery from a MERGE view."

      However it doesn't seem to be reproducible on 5.3 tree after the merged revision.

      Test case:

      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (0),(8);
       
      CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=MyISAM;
      CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
       
      SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
       
      PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
      EXECUTE stmt; 
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
       
      DROP VIEW v2;
      DROP TABLE t1, t2;

      Actual result:

      SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
      SUM(pk)
      NULL
      PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
      EXECUTE stmt;
      SUM(pk)
      NULL
      EXECUTE stmt;
      SUM(pk)
      0

      Expected result:

      SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk;
      SUM(pk)
      NULL
      PREPARE stmt FROM "SELECT SUM(pk) FROM t1 LEFT JOIN v2 ON a = pk";
      EXECUTE stmt;
      SUM(pk)
      NULL
      EXECUTE stmt;
      SUM(pk)
      NULL

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.