Eclipse Community Forums: EclipseLink » FUNC make postgre throwing exception

Home » Eclipse Projects » EclipseLink » FUNC make postgre throwing exception(Can't call a postgre function with FUNC)

Show: Today's Messages :: Show Polls :: Message Navigator

FUNC make postgre throwing exception [message #885714]

Wed, 13 June 2012 13:20

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

Report message to a moderator

Re: FUNC make postgre throwing exception [message #889721 is a reply to message #885714]

Tue, 19 June 2012 14:05

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

Report message to a moderator

Previous Topic:	NOT operator is not handled properly in EclipseLink 2.4.0
Next Topic:	Noob Problem: ClientSession.getAccessors() returns null

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 29 07:30:28 GMT 2017

.:: Contact :: Home ::.