Incorrect result when use volatile function alias in having clause

Is your feature request related to a problem or challenge?

related to #7876
when we do the query like

select a, random() as r from test group by a having r > 0.5;

we will get the incorrect result like

+----+---------------------+
| a  | r                   |
+----+---------------------+
| c  | 0.1664909010224056  |
| e  | 0.35292520754684475 |
| b  | 0.26159048688135855 |
+----+---------------------+

this is because when we convert the Statement into a LogicalPlan, we will "dereferences" any aliases in the HAVING clause in the below section
https://github.com/apache/arrow-datafusion/blob/1dd887cdff518ede1d1de457f4b20c22a9c7228f/datafusion/sql/src/select.rs#L110-L122

Describe the solution you'd like

Based on the way we implemented alias in having clause, I think we should disable the use of aliases for volatile functions in the having clause and report a error.
and we can use subqueries to implement the above SQL

select t.a, t.r from (select a, random() as r from test group by a) as t where t.r > 0.5;

Describe alternatives you've considered

support the sql like

select a, random() as r from test group by a having r > 0.5;

but this method is more complicated

Additional context

duchdb also do this query incorrect

D select t.c1, random() as r from agg.csv as t group by t.c1 having r > 0.5;
┌─────────┬─────────────────────┐
│   c1    │          r          │
│ varchar │       double        │
├─────────┼─────────────────────┤
│ b       │  0.5120539779309183 │
│ d       │ 0.20845546829514205 │
└─────────┴─────────────────────┘

mysql can get correct result
postgres disallow use alias in having clause

@alamb

cc @alamb @jonahgao

The solution is acceptable to me.

I wonder if it is possible to eliminate this dereference step entirely and directly generate the same logical plan as the subquery.

But it would involve modifying other parts of the code, which may be complicated.

I wonder if it is possible to eliminate this dereference step entirely and directly generate the same logical plan as the subquery.

This would be the most elegant (and correct) solution, in my opinion.

I am looking into this. Started off by making sure its still an issue - which it is.

> select a, random() as r from values (1), (2), (3), (4) as t('a') group by a having r > 0.5;
+---+---------------------+
| a | r                   |
+---+---------------------+
| 4 | 0.995627458014017   |
| 3 | 0.43503817742472006 |
+---+---------------------+
2 row(s) fetched.
Elapsed 0.003 seconds.

I checked duckdb which at the time the ticket was created also had the issue but now reports this as an error - although they say "This is not yet supported" so maybe something similar to

I wonder if it is possible to eliminate this dereference step entirely and directly generate the same logical plan as the subquery.

is planned. I will look into that next.

❯ duckdb
DuckDB v1.5.5 (Variegata)
Enter ".help" for usage hints.
memory D select a, random() as r from values (1), (2), (3), (4) as t('a') group by a having r > 0.5;
Binder Error:
Alias "r" referenced - but the expression has side effects. This is not yet supported.

This also shows up when using QUALIFY

> SELECT row_number() OVER () AS rn, random() AS r
  FROM (VALUES (1), (2), (3), (4)) t
  QUALIFY rn >= 1 AND r > 0.5;
+----+--------------------+
| rn | r                  |
+----+--------------------+
| 4  | 0.2104411177588813 |
+----+--------------------+
1 row(s) fetched.
Elapsed 0.002 seconds.

Move filter for `HAVING` and `QUALIFY` when volatile aliases are used…

Navigation Menu

Repository navigation

Incorrect result when use volatile function alias in having clause #7976

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Incorrect result when use volatile function alias in having clause #7976

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

haohuaijin commented on Oct 30, 2023

jonahgao commented on Oct 30, 2023

alamb commented on Oct 30, 2023

matthewmturner commented on Oct 28, 2026

matthewmturner commented on Oct 28, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions