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

Join with const table produces incorrect query result

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.33
    • Fix Version/s: 5.5.33
    • Component/s: None
    • Labels:
      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

            • Assignee:
              igor Igor Babaev
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: