Four arithmetic operations between aggregation functions are not supported
Lvnszn opened this issue · 12 comments
select sum(1)/count(1) from dual
select sum(1)/7 from dual
select sum(1)*3 from dual
The sql parser cannot parse this kind of sql
Hey! This is indeed a limitation right now. You need to use a common table expression or subquery.
Will you upgrade sqlparser？I understand this limitation is because of low version of sqlparser.
This is unrelated to sqlparser and is just a limitation of the current SQL AST -> logical plan translation step. It would have to be written in a way that gets all leaf aggregations in each expression and then constructs a GroupBy (all aggregations) -> Map (operations on aggregations).
You mean that the parse is actually successful, but there is a problem when the ast is converted into a logical plan
This is unrelated to sqlparser and is just a limitation of the current SQL AST -> logical plan translation step.
When I parsed this sql, he had a problem. The sum(1)/7 would not be divided into two args 【sum(1) and 7 】.
I always thought that the vitess you introduced at that time was too old and did not parse this logic.
demo 1
SELECT SUM(_1)/SUM(_1) as \"_5\" from student_new0.student_new as a join sc_inner_new1.sc_inner_new as b on a._3 = b._1 limit 1
response1
{"error": "couldn't parse query: invalid argument syntax error at position 20"}
demo 2
SELECT SUM(_1)/100 as \"\" from student_new0.student_new as a join sc_inner_new1.sc_inner_new as b on a._3 = b._1 limit 10
response 2
{"error": "couldn't parse query: invalid argument syntax error at position 23 near 'as'"}
demo3
SELECT floor(float(sum((_1)))) as \"_5\" from student_new0.student_new as a join sc_inner_new1.sc_inner_new as b on a._3 = b._1 limit 10
response3
{"error": "typecheck error: unknown function: sum(Int)"}
There's a bug, which is in fact in the parser, where sometimes division isn't parsed correctly. This is because the parser supports just writing out file paths as tables names (FROM my/file.json) but this means there's ambiguity that's hard to solve. That's why you're getting the syntax errors, you can fix that by writing it as /(a, b).
You mean that the parse is actually successful, but there is a problem when the ast is converted into a logical plan
Yeah, you can see that in demo3. It tries to process the sum as a function, not as an aggregate.
There's a bug, which is in fact in the parser, where sometimes division isn't parsed correctly. This is because the parser supports just writing out file paths as tables names (
FROM my/file.json) but this means there's ambiguity that's hard to solve. That's why you're getting the syntax errors, you can fix that by writing it as/(a, b).
Thank you for your reply, can u give me some example about /(a,b). I try this sql and not work.
SELECT /(sum(_1), sum(_3)) as \"_1\" from student_new0.student_new as a outer join sc_inner_new1.sc_inner_new as b on a._3 = b._1 limit 1
Oh, it's indeed broken, but now I found the right way: leave a space between / and the operands. So sum(_1) / sum(_3) and it parses correctly (it will still not work since it reads it as a function, but it will parse).
yes, the error show as below.
{"error": "typecheck error: unknown function: sum(NULL | Int)"}
hi, how can I fix this problem