Four arithmetic operations between aggregation functions are not supported

[Lvnszn]

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

[cube2222]

Hey! This is indeed a limitation right now. You need to use a common table expression or subquery.

[Lvnszn]

Will you upgrade sqlparser？I understand this limitation is because of low version of sqlparser.

[cube2222]

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).

[Lvnszn]

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.

[Lvnszn]

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'"}

[Lvnszn]

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)"}

[cube2222]

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).

[cube2222]

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.

[Lvnszn]

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

[cube2222]

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).

[Lvnszn]

yes, the error show as below.

{"error": "typecheck error: unknown function: sum(NULL | Int)"}

[Lvnszn]

hi, how can I fix this problem