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

Distinct SUM on CROSS JOIN and grouped returns wrong result

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.6
    • Fix Version/s: 10.4
    • Component/s: Optimizer
    • Labels:
    • Environment:
      Ubuntu 18.04, running MariaDB 10.4.6 in Docker

      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

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              Alicen -
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: