haohuaijin opened a new issue, #7976: URL: https://github.com/apache/arrow-datafusion/issues/7976
### Is your feature request related to a problem or challenge? related to #7876 when we do the query like ```sql 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 ```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 ```sql 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 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
