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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: 5.5.34, 10.0.6
    • Fix versions: 5.5.35, 10.0.8
    • Components: None
    • Labels:
      None
    • Sprint:

      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

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

              Dates

              • Created:
                Updated:
                Resolved: