[
https://issues.apache.org/jira/browse/CALCITE-5406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17644093#comment-17644093
]
Julian Hyde commented on CALCITE-5406:
--------------------------------------
Here are some tricky cases:
{code:sql}
# Are duplicates allowed?
select distinct on (deptno, deptno) * from emp;
# Are expressions allowed?
select distinct on (empno % 2) * from emp;
# Can DISTINCT ON be empty?
select distinct on () from emp;
# Can I reference aliases?
select distinct on (x) empno as x, deptno from emp;
# If alias and column name clash, which do I get?
select distinct on (deptno) empno as deptno, deptno as d from emp;
# If a column has been aliased, can I still reference it by qualified name?
select distinct on (e.deptno) e.deptno as x, e.empno from emp;
# Can I use ordinals (as I can in ORDER BY)?
select distinct on (2) * from emp;
# Integers in expressions, decimals, negative integers should not be
interpreted as ordinals
select distinct on (2 + 3, 4.0, -1) from emp;
# Can I reference columns that are not projected?
select distinct on (deptno) empno, ename from emp;
# Can I apply DISTINCT ON to an aggregate query?
select distinct on (deptno) deptno, sum(sal) from emp group by deptno;
select distinct on (sum(sal)) deptno, sum(sal) from emp group by deptno;
select distinct on (sum_sal) deptno, sum(sal) as sum_sal from emp group by
deptno;
# Can I apply DISTINCT ON to a query with USING or NATURAL JOIN?
select distinct on (deptno) * from emp join dept using (deptno);
select distinct on (deptno) * from emp natural join dept;
{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}
> As suggested by [~asolimando] and [~julianhyde] , first, we need to support
> SELECT DISTINCT ON statement parsing at PostgreSQL and lenient compliance
> level.
> Secondly, we need to investigate whether we need to rewrite in
> SqlToRelConverter or in planner rules?
> I will investigate the solution to support this new feature and update it in
> jira later.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
