Skip to content

One grouped DISTINCT row is dropped after projection-only view materialization #160

Description

@123lpygithub

Under SQL-equivalent rewriting, the source and mutated queries differ only in replacing base table t with projection view v(c1, c9). The result changes from two rows to one row.

Version: 8.0.44-alisql-dev

How to repeat:

DROP VIEW IF EXISTS v;
DROP TABLE IF EXISTS t;

CREATE TABLE t (
  c1 TINYINT,
  c9 TINYINT
);

INSERT INTO t VALUES (0, 0), (0, 1);

-- Original SQL
SELECT DISTINCT -c9 AS col_1, c1
FROM t
GROUP BY -c9, c1
ORDER BY c1, -c9;

-- View SQL
CREATE VIEW v AS
SELECT c1, c9
FROM t;

-- Mutated SQL
SELECT DISTINCT -v.c9 AS col_1, v.c1
FROM v
GROUP BY -v.c9, v.c1
ORDER BY v.c1, -v.c9;

Observed mismatch:

  • original result:
col_1 c1
-1 0
0 0
  • mutated result:
col_1 c1
0 0

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Fields

    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions