[
https://issues.apache.org/jira/browse/CALCITE-5406?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhengqiang Duan updated CALCITE-5406:
-------------------------------------
Description:
Hi, community, I am currently using Calcite to help ShardingSphere implement
distributed database features. When we tested the SELECT DISTINCT ON statement
in PostgreSQL(as shown in the following sql), we found that Calcite currently
does not support the parsing and semantic processing of the SELECT DISTINCT ON
statement. Considering that the SELECT DISTINCT ON statement in PostgreSQL is
frequently used and valuable to users, we hope to support this new feature.
{code:java}
select distinct on (user_id) * from t_order order by user_id, order_id; {code}
was:
Hi, community, I am currently using Calcite to help ShardingSphere implement
distributed database features.
I use the following sql statement to test with PostgreSQL dialect:
{code:java}
select distinct on (user_id) * from t_order order by user_id, order_id; {code}
But a sql parsing exception is thrown.
{code:java}
org.apache.calcite.sql.parser.SqlParseException: Encountered "on" at line 1,
column 17.
Was expecting one of:
"ABS" ...
"ARRAY" ...
"AVG" ...
"CARDINALITY" ...
"CASE" ...
"CAST" ...
"CEIL" ...
"CEILING" ...
"CHAR" ...
"CHAR_LENGTH" ...
"CHARACTER_LENGTH" ...
"CLASSIFIER" ...
"COALESCE" ...
"COLLECT" ...
"CONVERT" ...
"COUNT" ...
"COVAR_POP" ...
"COVAR_SAMP" ...
"CUME_DIST" ...
"CURRENT" ...
"CURRENT_CATALOG" ...
"CURRENT_DATE" ...
"CURRENT_DEFAULT_TRANSFORM_GROUP" ...
"CURRENT_PATH" ...
"CURRENT_ROLE" ...
"CURRENT_SCHEMA" ...
"CURRENT_TIME" ...
"CURRENT_TIMESTAMP" ...
"CURRENT_USER" ...
"CURSOR" ...
"DATE" ...
"DENSE_RANK" ...
"ELEMENT" ...
"EVERY" ...
"EXISTS" ...
"EXP" ...
"EXTRACT" ...
"FALSE" ...
"FIRST_VALUE" ...
"FLOOR" ...
"FUSION" ...
"GROUPING" ...
"HOUR" ...
"INTERSECTION" ...
"INTERVAL" ...
"JSON_ARRAY" ...
"JSON_ARRAYAGG" ...
"JSON_EXISTS" ...
"JSON_OBJECT" ...
"JSON_OBJECTAGG" ...
"JSON_QUERY" ...
"JSON_VALUE" ...
"LAG" ...
"LAST_VALUE" ...
"LEAD" ...
"LEFT" ...
"LN" ...
"LOCALTIME" ...
"LOCALTIMESTAMP" ...
"LOWER" ...
"MATCH_NUMBER" ...
"MAX" ...
"MIN" ...
"MINUTE" ...
"MOD" ...
"MONTH" ...
"MULTISET" ...
"NEW" ...
"NEXT" ...
"NOT" ...
"NTH_VALUE" ...
"NTILE" ...
"NULL" ...
"NULLIF" ...
"OCTET_LENGTH" ...
"OVERLAY" ...
"PERCENTILE_CONT" ...
"PERCENTILE_DISC" ...
"PERCENT_RANK" ...
"PERIOD" ...
"POSITION" ...
"POWER" ...
"PREV" ...
"RANK" ...
"REGR_COUNT" ...
"REGR_SXX" ...
"REGR_SYY" ...
"RIGHT" ...
"ROW" ...
"ROW_NUMBER" ...
"RUNNING" ...
"SECOND" ...
"SESSION_USER" ...
"SOME" ...
"SPECIFIC" ...
"SQRT" ...
"STDDEV_POP" ...
"STDDEV_SAMP" ...
"SUBSTRING" ...
"SUM" ...
"SYSTEM_USER" ...
"TIME" ...
"TIMESTAMP" ...
"TRANSLATE" ...
"TRIM" ...
"TRUE" ...
"TRUNCATE" ...
"UNIQUE" ...
"UNKNOWN" ...
"UPPER" ...
"USER" ...
"VAR_POP" ...
"VAR_SAMP" ...
"YEAR" ...
<UNSIGNED_INTEGER_LITERAL> ...
<APPROX_NUMERIC_LITERAL> ...
<DECIMAL_NUMERIC_LITERAL> ...
<BINARY_STRING_LITERAL> ...
<QUOTED_STRING> ...
<PREFIXED_STRING_LITERAL> ...
<UNICODE_STRING_LITERAL> ...
<BIG_QUERY_DOUBLE_QUOTED_STRING> ...
<BIG_QUERY_QUOTED_STRING> ...
"(" ...
<LBRACE_D> ...
<LBRACE_T> ...
<LBRACE_TS> ...
<LBRACE_FN> ...
"?" ...
"+" ...
"-" ...
"*" ...
<BRACKET_QUOTED_IDENTIFIER> ...
<QUOTED_IDENTIFIER> ...
<BACK_QUOTED_IDENTIFIER> ...
<BIG_QUERY_BACK_QUOTED_IDENTIFIER> ...
<HYPHENATED_IDENTIFIER> ...
<IDENTIFIER> ...
<UNICODE_QUOTED_IDENTIFIER> ...
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:402)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:155)
at
org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:156)
at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:171)
{code}
> Support the SELECT DISTINCT ON statement for PostgreSQL dialect
> ---------------------------------------------------------------
>
> Key: CALCITE-5406
> URL: https://issues.apache.org/jira/browse/CALCITE-5406
> Project: Calcite
> Issue Type: New Feature
> Affects Versions: 1.32.0
> Reporter: Zhengqiang Duan
> Assignee: Zhengqiang Duan
> Priority: Major
>
> Hi, community, I am currently using Calcite to help ShardingSphere implement
> distributed database features. When we tested the SELECT DISTINCT ON
> statement in PostgreSQL(as shown in the following sql), we found that Calcite
> currently does not support the parsing and semantic processing of the SELECT
> DISTINCT ON statement. Considering that the SELECT DISTINCT ON statement in
> PostgreSQL is frequently used and valuable to users, we hope to support this
> new feature.
> {code:java}
> select distinct on (user_id) * from t_order order by user_id, order_id; {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
