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

Join with const table produces incorrect query result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.33
    • 5.5.33
    • None
    • None

    Description

      In 5.5, revision 3589, we've had:

      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (NULL);
      CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
      CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
      INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
      SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
      i1	i2	a	b
      # CHECK:
      SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b;
      i1	i2	a	b
      DROP VIEW v2;
      DROP TABLE t1,t2;

      in revision 3860, we get

      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (NULL);
      CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
      CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
      INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
      SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
      i1	i2	a	b
      NULL	NULL	2	3
      NULL	NULL	1	2
      # CHECK:
      SELECT * FROM t1 JOIN t2 ON i1 = i2 WHERE a < b;
      i1	i2	a	b
      DROP VIEW v2;
      DROP TABLE t1,t2;

      Note that the query that used to return zero records now returns two records. This is incorrect.

      Attachments

        Activity

          People

            igor Igor Babaev
            psergei Sergei Petrunia
            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.