Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1
-
linux arm64
-
Unexpected results
Description
Summary
A SELECT that combines an aggregate window function with a quantified subquery predicate
(col < ANY (…), col >= ALL (…)) in the `WHERE` returns wrong (missing, often zero) rows when
the queried relation is a mergeable view rather than the base table.
Version: 13.1.0-MariaDB-debug, source revision cded2b25e65853a75c2213cfe0832819832708bd (main, assertions on)
Minimal repro
|
|
CREATE TABLE b (id BIGINT); |
INSERT INTO b VALUES (0),(1); |
CREATE VIEW t AS SELECT id FROM b; |
|
|
SELECT AVG(id) OVER () FROM b WHERE id < ANY (SELECT id FROM b); -- 1 row (correct) |
SELECT AVG(id) OVER () FROM t WHERE id < ANY (SELECT id FROM t); -- 0 rows (WRONG) |
Expected vs actual
| Query | Expected | Actual |
| … FROM b WHERE id < ANY (SELECT id FROM b) (base table) | 1 row | 1 row |
| … FROM t WHERE id < ANY (SELECT id FROM t) (view) | 1 row | *0 rows* |
| … FROM t WHERE id >= ALL (SELECT id FROM t) (view) | 1 row | *0 rows* |
Attachments
Issue Links
- relates to
-
MDEV-35869 Wrong result using degenerated subquery with window function
-
- Closed
-