[Hibernate-issues] [Hibernate-JIRA] Closed: (HB-131) Select...Limit syntax changed from PostgreSQL 6.5 to 7.0

[<leg...@at...>]

Message:

   The following issue has been closed.

   Resolver: Gavin King
       Date: Sat, 14 Jun 2003 11:24 AM

I have changed the Postgres dialect to use LIMIT x OFFSET y
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-131


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-131
    Summary: Select...Limit syntax changed from PostgreSQL 6.5 to 7.0
       Type: Improvement

     Status: Closed
   Priority: Major
 Resolution: FIXED

    Project: Hibernate2
 Components: 
             core
   Fix Fors:
             2.0.1
   Versions:
             2.0rc2
             2.1
             2.0 final
             2.0.1

   Assignee: Gavin King
   Reporter: Jesse McLaughlin

    Created: Thu, 12 Jun 2003 12:16 AM
    Updated: Sat, 14 Jun 2003 11:24 AM

Description:
Using the

Query.setMaxResults(); 

method with the PostgreSQL dialect while using PostgreSQL version >= 7.0 causes the following error: 

14:20:31,140 ERROR [JDBCExceptionReporter] Could not execute query 
java.sql.SQLException: ERROR: LIMIT #,# syntax not supported. 
Use separate LIMIT and OFFSET clauses. 

PostgreSQLDialect.java reads: 

public String getLimitString(String sql) { 
  StringBuffer pagingSelect = new StringBuffer(100); 
  pagingSelect.append(sql); 
  pagingSelect.append(" limit ?, ?"); 
  return pagingSelect.toString(); 
} 

However, the Select..Limit syntax has changed from PostgreSQL 7.0, refer:

SELECT

PostgreSQL 6.5 Synopsis

http://www.postgresql.org/docs/view.php?version=6.5&idoc=1&file=sql-select.htm

SELECT [ALL|DISTINCT [ON column] ]
    expression [ AS
   name ] [, ...]
    [ INTO [TEMP] [TABLE] new_table ]
    [ FROM table
   [alias ] [, ...] ]
    [ WHERE condition ]
    [ GROUP BY column [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION [ALL] | INTERSECT | EXCEPT } select ]
    [ ORDER BY column [ ASC | DESC ] [, ...] ]
    [ FOR UPDATE [OF class_name...]]
    [ LIMIT count [OFFSET|, count]]

Compare with:

PostgreSQL 7.0 Synopsis

http://www.postgresql.org/docs/view.php?version=7.0&idoc=1&file=sql-select.htm

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    expression [ AS name ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
    [ FROM table [ alias ] [, ...] ]
    [ WHERE condition ]
    [ GROUP BY column [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
    [ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF class_name [, ...] ] ]
    LIMIT { count | ALL } [ { OFFSET | , } start ]

The Hibernate PostgreSQL dialect should be updated (or a new one created) to reflect this change.



---------------------------------------------------------------------
JIRA INFORMATION:
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/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira