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

Wrong result upon query with GROUP BY and correlated subquery

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t (a INT, b varchar(1), KEY (b,a));
       
      INSERT INTO t VALUES
        (1,'a'),(NULL,'a'),(NULL,'x'),(8,'x'),(6,'f'),
        (6,'l'),(9,'r'),(9,'s'),(7,'y'),(6,'k');
       
      SELECT b, COUNT(*) FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
      SELECT b FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
       
      DROP TABLE t;
      

      The first query, with COUNT, returns all expected rows:

      10.2 a92f07f4

      SELECT b, COUNT(*) FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
      b	COUNT(*)
      a	1
      f	1
      k	1
      l	1
      r	1
      s	1
      x	1
      y	1
      

      The second query, same but without COUNT, doesn't return rows a and x:

      SELECT b FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
      b
      f
      k
      l
      r
      s
      y
      

      Reproducible on 10.2-10.4, with at least MyISAM and InnoDB.
      On 10.5+ it was recently fixed by this commit:

      commit d314bd266491baf0954d13fa51dc22b730a6f4d1
      Author: Monty
      Date:   Wed Feb 2 14:09:21 2022 +0200
       
          MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.