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

Wrong result with LEFT JOINs involving constant tables

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1);
       
      CREATE TABLE t2 (b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1),(1);
       
      CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM;
       
      ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; # Optional, fails either way
       
      SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
      SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
       
      DROP TABLE t1, t2, t3;
      

      10.5 9d388192

      SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
      a	b	c
      1	NULL	NULL
      SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
      COUNT(*)
      2
      

      The expected result is (unless I'm mistaken)

      a	b	c
      1	1	NULL
      1	1	NULL
      

      so the actual result set is wrong both in the number of rows and in the values, while the COUNT result is correct.

      Reproducible on 10.5+. Could not reproduce on 10.4.

      Plan on 10.5

      EXPLAIN FORMAT=JSON SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b;
      EXPLAIN	{
        "query_block": {
          "select_id": 1,
          "const_condition": "1",
          "table": {
            "table_name": "t1",
            "access_type": "system",
            "rows": 1,
            "filtered": 100
          },
          "table": {
            "table_name": "t3",
            "access_type": "const",
            "possible_keys": ["PRIMARY"],
            "rows": 1,
            "filtered": 100,
            "impossible_on_condition": true
          },
          "table": {
            "table_name": "t2",
            "access_type": "const",
            "rows": 1,
            "filtered": 100,
            "impossible_on_condition": true
          }
        }
      }
      

        Attachments

          Activity

            People

            Assignee:
            monty Michael Widenius
            Reporter:
            elenst Elena Stepanova
            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.