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

A grouped NULL-extended RIGHT JOIN row survives a false HAVING predicate

    XMLWordPrintable

Details

    Description

      `t2` is empty, so the query can only produce one NULL-extended group from the `RIGHT JOIN`. On that group `MAX(t0.c0)` is `'1464731614'`, and `'1464731614' LIKE 'z]>4'` is false, so the `HAVING` clause should filter the group out and return an empty result. MariaDB instead wrongly keeps the group and returns `10 | '' | NULL | 1464731614 | 1`.

      CREATE TABLE t0(c0 CHAR(100) NOT NULL, PRIMARY KEY(c0));
      CREATE TABLE t2 LIKE t0;

      INSERT INTO t0 VALUES ('1464731614');

      SELECT LENGTH(t0.c0), IFNULL(t2.c0, ''), MAX(t2.c0), MAX(t0.c0), COUNT(1) OVER ()
      FROM t2
      RIGHT JOIN t0
      ON ((t2.c0 != t0.c0) AND (t0.c0 IS NOT NULL) AND (t2.c0 IS NULL))
      GROUP BY LENGTH(t0.c0), IFNULL(t2.c0, '')
      HAVING (MAX(t0.c0) LIKE 'z]>4')
      ORDER BY IFNULL(t2.c0, ''); – Expected correct result: <empty> – actual Wrong result: 10 | | NULL | 1464731614 | 1

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              yx yx
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.