Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » FUNC make postgre throwing exception(Can't call a postgre function with FUNC)
FUNC make postgre throwing exception [message #885714] Wed, 13 June 2012 09:20 Go to next message
François Dussert is currently offline François Dussert
Messages: 1
Registered: June 2012
Junior Member
Hi all,
i'm using eclipselink 2.3.2 on glassfish 3.1.1.
My database is a Postgre 9.1 (and jdbc driver)

SQL query that works in postgre 9.1 :

SELECT sum(count), date_part('WEEK', tcreated) as week
FROM usulTests
GROUP BY week
ORDER BY week DESC


And the JPA (eclipselink 2.3.2) version that doesn't work :

SELECT SUM(b.count), FUNC('date_part','WEEK', b.tcreated) AS week FROM BidLEntreprise b GROUP BY week ORDER BY week


The exception :
org.postgresql.util.PSQLException: ERROR: column "usulTests.tcreated" must appear in the GROUP BY clause or be used in an aggregate function


If i try

SELECT SUM(b.count), FUNC('date_part','WEEK', b.tcreated) AS week FROM BidLEntreprise b GROUP BY FUNC('date_part','WEEK', b.tcreated) ORDER BY FUNC('date_part','WEEK', b.tcreated)


I have the same error
but if i try


SELECT SUM(b.count), b.tcreated AS week FROM BidLEntreprise b GROUP BY week ORDER BY  week


It works Sad
Re: FUNC make postgre throwing exception [message #889721 is a reply to message #885714] Tue, 19 June 2012 10:05 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

What is the SQL that is generated? The issue seems to be that the alias is not used in the group-by, please log a bug for this.
Although it is odd that Postgres does not recognize that the two values are equivalent as other databases do.

You can use an native SQL query as a workaround.


James : Wiki : Book : Blog : Twitter
Previous Topic:Bean validator implementation in eclipselink
Next Topic:HistoryPolicy OneToOneMapping StackOverflowError
Goto Forum:
  


Current Time: Wed Apr 16 19:04:55 EDT 2014

Powered by FUDForum. Page generated in 0.10493 seconds