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

Distinct SUM on CROSS JOIN and grouped returns wrong result

    XMLWordPrintable

Details

    Description

      This sql query returns a wrong result:

      CREATE SCHEMA A;
       
      CREATE TABLE A.B (C INTEGER, D INTEGER);
       
      INSERT INTO A.B VALUES (5, 1), (0, 3);
       
      SELECT DISTINCT SUM(DISTINCT 1), SUM( B.D) > 2 FROM (A.B AS E CROSS JOIN A.B) GROUP BY B.C
      

      Postgres 11.3 returns (1, 0) and (1,1), MariaDB in Version 10.4.6 and MySQL in Version 8.0.16 both only return (1, 1). Manually verified, the Postgres result is the correct one (group by results into two rows, sum > 2 is one for one group and false for the other. Therefore, I expect that MariaDB should also return 1,1 and 1,0.

      Removing one DISTINCT, one of the columns or > 2 removes the issue. I reduced the testcase as much as possible, therefore I replaced columns with constant if possible. The program now looks a bit constructed, but with columns, the bug also occurs.

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              Alicen -
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.