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

Wrong result (NULLs instead of real values) with TEMPTABLE view, LEFT JOIN

    XMLWordPrintable

    Details

    • Sprint:
      5.5.48-0

      Description

      The problem appeared in 5.3 tree with the following revision:

      revno: 3793 [merge]
      revision-id: igor@askmonty.org-20140804170551-76ohozgq308a9f3a
      parent: sergii@pisem.net-20140801100455-jtqd7ofg3xwau6j1
      parent: igor@askmonty.org-20140801051743-7i2297y96icyx9vx
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-5.3
      timestamp: Mon 2014-08-04 10:05:51 -0700
      message:
        Merge.
          ------------------------------------------------------------
          revno: 3774.1.1
          revision-id: igor@askmonty.org-20140801051743-7i2297y96icyx9vx
          parent: psergey@askmonty.org-20140318080632-c1pql218bfrx26y2
          committer: Igor Babaev <igor@askmonty.org>
          branch nick: maria-5.3-mdev5721
          timestamp: Thu 2014-07-31 22:17:43 -0700
          message:
            Fixed bug mdev-5721.
            Do not define a look-up key for a temporary table if its length
            exceeds the maximum length of such keys. 

      Test case:

      CREATE TABLE t1 (i INT, state VARCHAR(1024)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (2,'Louisiana'),(9,'Maine');
       
      CREATE TABLE t2 (state VARCHAR(1024), j INT) ENGINE=MyISAM;
      CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
      INSERT INTO t2 VALUES 
      ('Louisiana',9),('Alaska',5);
       
      SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN v2 ON t1_2.i = j ) ON t1_1.state = v2.state;
       
      DROP VIEW v2;
      DROP TABLE t1, t2;

      Expected result:

      i	state	i	state	state	j
      2	Louisiana	9	Maine	Louisiana	9
      9	Maine	NULL	NULL	NULL	NULL

      Actual result:

      i	state	i	state	state	j
      2	Louisiana	NULL	NULL	NULL	NULL
      9	Maine	NULL	NULL	NULL	NULL

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: