[
https://issues.apache.org/jira/browse/CALCITE-6944?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17945242#comment-17945242
]
juntaozhang edited comment on CALCITE-6944 at 4/17/25 11:19 PM:
----------------------------------------------------------------
After research I find that SQL 2016 standard(chapter 9 invocation)[1]
introduced a way of defining PTF including: define <table primary>, <SQL
argument list>,<SQL argument>,<table argument>...
here is a sample to illustrate table argument, which show <table argument>
wrapper by parentheses is not the standard:
!image-2025-04-17-14-16-53-570.png!
And I try this in Oracle, find it can enclose table name in parenthesis, but
not the analytic clause[2]. And this sample[3] demonstrate that <table
argument> with parentheses in oracle is not working.
I also try it in Trino, which follow the SQL 2016 standard [1], not support
parentheses wrapper by parentheses.
My conclusion :
# _topn( (TABLE orders PARTITION BY productid) )_ as I mentioned before, this
parsing should not be supported in Calcite.
# The _toSqlString_ method should not return an unparsable and nonstandard
result to users, the parentheses need be removed from the <table argument>
Should: _topn( TABLE orders PARTITION BY productid )_
_NOT : topn( (TABLE orders PARTITION BY productid) )_
# As the example shows <table argument proper> _TABLE(orders)_ is defined in
SQL 2016 [1] and supported by Trino[4] and Oracle[2], need to support in
Calcite, I am not sure why we use _TABLE order_ instead of {_}TABLE(orders){_}?
Anybody help to explain?
Feel free to share any doubts or questions you have. I’m glad to discuss with
you.
[1] [SO/IEC
19075-7:2021|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.iso.org%2Fstandard%2F78938.html&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965751791%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=fpCd4zjRmsfe4QTMLc9ZQpqoN0hZ0lUf89NZ4RdDSaI%3D&reserved=0](SQL
2016 standard)
[2]
[https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/overview-polymorphic-table-functions.html#GUID-4847CB51-6939-44C4-9913-CC3CE13B6730|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fen%2Fdatabase%2Foracle%2Foracle-database%2F23%2Flnpls%2Foverview-polymorphic-table-functions.html%23GUID-4847CB51-6939-44C4-9913-CC3CE13B6730&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965775746%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=ruAbXriT4YrmD3ZoyEp4VyM4hqWbRDzFD5iqAHVZnvs%3D&reserved=0]
[3]
[https://forums.oracle.com/ords/apexds/post/polymorphic-table-function-with-parentheses-in-partition-by-1913|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fforums.oracle.com%2Fords%2Fapexds%2Fpost%2Fpolymorphic-table-function-with-parentheses-in-partition-by-1913&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965789735%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=fpIa9%2ByJQAyv7oKFnWVjlWb%2FpSW%2BO0osUa4YMm7J38M%3D&reserved=0]
[4]
[https://trino.io/docs/current//functions/table.html|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftrino.io%2Fdocs%2Fcurrent%2Ffunctions%2Ftable.html&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965804269%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=KjzICylXscQqQWwJtfELE%2BH7KaDRAo7ayAQ44PZzHV0%3D&reserved=0]
was (Author: juntaozhang):
After research I find that SQL 2016 standard(chapter 9 invocation)[1]
introduced a way of defining PTF including: define <table primary>, <SQL
argument list>,<SQL argument>,<table argument>...
here is a sample to illustrate table argument, which show <table argument>
wrapper by parentheses is not the standard:
!image-2025-04-17-14-16-53-570.png!
And I try this in Oracle, find it can enclose table name in parenthesis, but
not the analytic clause[2]. And this sample[3] demonstrate that <table
argument> with parentheses in oracle is not working.
I also try it in Trino, which follow the SQL 2016 standard [1], not support
parentheses wrapper by parentheses.
My conclusion :
# _topn( (TABLE orders PARTITION BY productid) )_ as I mentioned before, this
parsing should not be supported in Calcite.
# The _toSqlString_ method should not return an unparsable and nonstandard
result to users, the parentheses need be removed from the <table argument>
Should: _topn( TABLE orders PARTITION BY productid )_
_NOT : topn( (TABLE orders PARTITION BY productid) )_
# As the example shows <table argument proper> _TABLE(orders)_ is defined in
SQL 2016 [1] and supported by Trino[4] and Oracle[2], need to support in
Calcite, I am not sure why we use _TABLE order_ instead of
\{_}TABLE(orders){_}? Anybody help to explain?
Feel free to share any doubts or questions you have. I’m glad to discuss with
you.
[1] [SO/IEC
19075-7:2021|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.iso.org%2Fstandard%2F78938.html&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965751791%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=fpCd4zjRmsfe4QTMLc9ZQpqoN0hZ0lUf89NZ4RdDSaI%3D&reserved=0](SQL
2016 standard)
[2]
[https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/overview-polymorphic-table-functions.html#GUID-4847CB51-6939-44C4-9913-CC3CE13B6730|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fen%2Fdatabase%2Foracle%2Foracle-database%2F23%2Flnpls%2Foverview-polymorphic-table-functions.html%23GUID-4847CB51-6939-44C4-9913-CC3CE13B6730&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965775746%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=ruAbXriT4YrmD3ZoyEp4VyM4hqWbRDzFD5iqAHVZnvs%3D&reserved=0]
[3]
[https://forums.oracle.com/ords/apexds/post/polymorphic-table-function-with-parentheses-in-partition-by-1913|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fforums.oracle.com%2Fords%2Fapexds%2Fpost%2Fpolymorphic-table-function-with-parentheses-in-partition-by-1913&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965789735%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=fpIa9%2ByJQAyv7oKFnWVjlWb%2FpSW%2BO0osUa4YMm7J38M%3D&reserved=0]
[4]
[https://trino.io/docs/current//functions/table.html|https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftrino.io%2Fdocs%2Fcurrent%2Ffunctions%2Ftable.html&data=05%7C02%7Cjuntzhang%40ebay.com%7C03f7a240db294305126d08dd7d76ef72%7C46326bff992841a0baca17c16c94ea99%7C0%7C0%7C638804671965804269%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=KjzICylXscQqQWwJtfELE%2BH7KaDRAo7ayAQ44PZzHV0%3D&reserved=0]
> Cannot parse parenthesized partition by in Table Function
> ---------------------------------------------------------
>
> Key: CALCITE-6944
> URL: https://issues.apache.org/jira/browse/CALCITE-6944
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: juntaozhang
> Assignee: juntaozhang
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2025-04-10-21-47-46-829.png,
> image-2025-04-17-14-16-29-776.png, image-2025-04-17-14-16-53-570.png
>
>
> Test example:
> {code:java}
> String sqlExpected = "f(a => TABLE t PARTITION BY f1 ORDER BY f2, b =>
> 1)";
> String sqlActual = parseExpression(sqlExpected)
> .toSqlString(new AnsiSqlDialect(SqlDialect.EMPTY_CONTEXT)).getSql();
> parseExpression(sqlActual);
> {code}
> Exception details:
> {code:java}
> Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered
> "PARTITION" at line 1, column 17.
> Was expecting one of:
> "EXCEPT" ...
> "FETCH" ...
> "INTERSECT" ...
> "LIMIT" ...
> "OFFSET" ...
> "ORDER" ...
> "MINUS" ...
> "UNION" ...
> ")" ...
> "," ...
> "." ...
>
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:44126)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:43937)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.ParenthesizedQueryOrCommaList(SqlParserImpl.java:899)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:18702)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.AddExpression2b(SqlParserImpl.java:17806)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:17848)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:17779)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:17756)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:17108)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:585)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.PartitionedQueryOrQueryOrExpr(SqlParserImpl.java:6912)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.AddArg0(SqlParserImpl.java:2522)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.FunctionParameterList(SqlParserImpl.java:1962)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.NamedCall(SqlParserImpl.java:32128)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.NamedFunctionCall(SqlParserImpl.java:31690)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.AtomicRowExpression(SqlParserImpl.java:19461)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:18666)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.AddExpression2b(SqlParserImpl.java:17806)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:17848)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:17779)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlExpressionEof(SqlParserImpl.java:17070)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlExpressionEof(SqlParserImpl.java:204)
> at
> org.apache.calcite.sql.parser.SqlParser.parseExpression(SqlParser.java:139)
> ... 62 more
> {code}
> After to *_toSqlString_* table argument cannot be recognized when parentheses
> are added:
> F(A => {color:#FF0000}({color}TABLE T PARTITION BY F1 ORDER BY
> F2{color:#FF0000}){color}, B => 1)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
