Description:
By nesting a window function inside a scalar subquery it's possible to get crashes or absurd results.
verified with bbffe0dd6dc37bc6314f096303c9bf4580375c22
How to repeat:
create table t(a int, b int);
# Crash: put WF in subquery
select (select sum(a) over ()) from t ;
# Bad result: using the special MySQL feature which says that in a SELECT list's expression you can reference an alias of another expression of that list IF you use a subquery
# (see https://bugs.mysql.com/bug.php?id=79549 for more background). So, I reference a WF by alias, putting it inside a group aggregate, which doesn't make sense! Here:
insert into t values(1,2);
insert into t values(4,5);
select sum(a) over () as c, (SELECT sum(c)) from t ;
+------+-----------------+
| c | (SELECT sum(c)) |
+------+-----------------+
| 5 | 6 |
| 5 | 10 |
+------+-----------------+
Notice how ORDER BY properly rejects sum(c):
select sum(a) over () as c from t order by sum(c);
ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query
