[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2800) Support for "DISTINCT ON"

[Petr Ferschmann (JIRA)]

Support for "DISTINCT ON"
-------------------------

                 Key: HHH-2800
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2800
             Project: Hibernate3
          Issue Type: New Feature
          Components: query-hql
         Environment: PostgreSQL 8.1, Hibernate 3.2.1.ga
            Reporter: Petr Ferschmann


I want to use equivalent of "distinct on" in PostgreSQL. 

If I have data like that:

create table tbl (
  F1 char,
  F2 char
);

F1, F2
| A | 1 |
| A | 2 |
| B | 1 |

for query:
select distinct on (F1) F1, F2 from tbl

I get:
A, 1
B, 1

Currently "distinct on (xxx, xxx)"  (no comma here) is not valid for HQL parser:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 17 [select distinct on (this.ID) this, p from ...]
org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)

For my usage it is enough to pass this syntax to SQL server. The current meaning of "DISTINCT" in Hibernate is not enough because we are working with large data sets and it is not even usable for it.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira