One joined row losses after right-side projection view materialization in a self-join

[123lpygithub]

Under SQL-equivalent rewriting, the source and mutated queries differ only in replacing the right side of a self-join with projection view v. The result set 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 (
  i TINYINT NOT NULL,
  s VARCHAR(1)
);

INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (1, 'b');

-- Source Original SQL
SELECT DISTINCT UPPER(b.s), a.i
FROM t AS a
JOIN t AS b ON b.i = a.i
GROUP BY UPPER(b.s), a.i
ORDER BY a.i DESC, UPPER(b.s) DESC;

-- View SQL
CREATE VIEW v AS
SELECT i, s
FROM t;

-- Mutated SQL
SELECT DISTINCT UPPER(v.s), a.i
FROM t AS a
JOIN v ON v.i = a.i
GROUP BY UPPER(v.s), a.i
ORDER BY a.i DESC, UPPER(v.s) DESC;

Observed mismatch:

• original result:

UPPER(b.s)	i
B	1
A	1

• mutated result:

UPPER(v.s)	i
A	1