MySQL Bugs: #115035: View with ORDER BY misses a row

Bug #115035	View with ORDER BY misses a row
Submitted:	16 May 2024 10:46	Modified:	17 Jun 2024 17:17
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.34, 8.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Please, consider
SELECT 1 FROM t1 ORDER BY count(*) OVER (PARTITION BY min(t1.c0));
(t1 being empty).

Because it has COUNT(*) it must return one row.
During resolution, mysql says "there's only one row so ORDER BY can be removed", so it removes it. Then during execution, as it still has a trace of COUNT(*) somewhere else in Items, it will still return one row properly.

But if instead we put this SELECT into a view, the view's body has no ORDER BY, so when later we SELECT from the view (in a new statement), there's no trace of COUNT, it's just "select 1 from t1" and it returns an empty set.

How to repeat:
CREATE TABLE t1 (c0 INT PRIMARY KEY);

# returns 1 row
SELECT 1 FROM t1 ORDER BY count(*) OVER (PARTITION BY min(t1.c0));

# put same SELECT into a view
create view v as  SELECT 1 FROM t1 ORDER BY count(*) OVER (PARTITION BY min(t1.c0));

# returns 0 row
select * from v;

Suggested fix:
remove ORDER BY only if is_view_context_analysis() is true, in setup_order_final().

Hello Guilhem,

Thank you for the report and test case.

regards,
Umesh

Documented fix as follows in the MySQL 9.0.0 changelog:

    In some cases, a SELECT constant from an empty table with ORDER
    BY COUNT(*) used in a view did not return any rows.

Closed.