DynamicQuery in error with PostgreSQL, but not in MySQL

[Tommaso Nicoletti，修改在9 年前。]

Hi all

I make this code to do a DynamicQuery

ClassLoader classLoader = (ClassLoader)PortletBeanLocatorUtil.locate(ClpSerializer.getServletContextName(), "portletClassLoader");
DynamicQuery notifiedUsersQuery = DynamicQueryFactoryUtil.forClass(UserNotificationEvent.class, classLoader);
notifiedUsersQuery.setProjection(ProjectionFactoryUtil.property("deliverBy"));
notifiedUsersQuery.setProjection(ProjectionFactoryUtil.property("timestamp"));
notifiedUsersQuery.add(PropertyFactoryUtil.forName("type").eq(portletName));
notifiedUsersQuery.add(PropertyFactoryUtil.forName("deliverBy").ne(0L));
notifiedUsersQuery.add(PropertyFactoryUtil.forName("payload").like("%\"className\":\""+className+"\"%"));
notifiedUsersQuery.add(PropertyFactoryUtil.forName("payload").like("%\"classPK\":"+classPK+"%"));
notifiedUsersQuery.add(PropertyFactoryUtil.forName("payload").like("%\"notificationType\":"+notificationType+"%"));
notifiedUsersQuery.setProjection(ProjectionFactoryUtil.groupProperty("deliverBy"));
notifiedUsersQuery.addOrder(OrderFactoryUtil.desc("timestamp"));
notifiedUsersQuery.setLimit(0, numOfElements);

DynamicQuery userNotifiactionEvent = DynamicQueryFactoryUtil.forClass(User.class, classLoader);
userNotifiactionEvent.add(PropertyFactoryUtil.forName("userId").in(notifiedUsersQuery));
userList = UserNotificationEventLocalServiceUtil.dynamicQuery(userNotifiactionEvent);

We use two kind of DB server, in first one we have MySQL 5.5.x, in the second one PostgreSQL 8.4.x. Both have liferay-portal-tomcat-6.2.0-ce-ga1-20131101192857659.

With PostgreSQL DB server we obtain:

17:10:10,369 ERROR [http-bio-8080-exec-16][JDBCExceptionReporter:82] ERRORE: la colonna "this_.timestamp" deve comparire nella clausola GROUP BY o essere usata in una funzione di aggregazione_  Posizione: 1802 [Sanitized]
17:10:10,373 ERROR [http-bio-8080-exec-16][BasePersistenceImpl:244] Caught unexpected exception org.hibernate.exception.SQLGrammarException
17:10:10,374 ERROR [http-bio-8080-exec-16][InboxNotificationLocalServiceImpl:387] org.hibernate.exception.SQLGrammarException: could not execute query

No problem with MySQL.

Target raw SQL:

select * from user_
where userId in (
select deliverBy from usernotificationevent
where deliverBy != 0
and payload like '%<!--?-->%'
and type_ like '%<!--?-->%'
group by deliverBy)

any suggestion?

[Jitendra Rajput，修改在9 年前。]

hibernate.show_sql=true

Set above property true in portal-ext.properties file and in logs check sql query generated from dynamic query.

[David H Nebinger，修改在9 年前。]

That might be your target sql, but you've included timestamp as a projection.

The error is indicating that postgres requires both deliver by and timestamp in the group by clause or possibly used in some other aggregate function.

The show sql suggestion should help because you'll get to see exactly what is getting generated from the dynamic query and you can test it directly in your database tool.

[Tommaso Nicoletti，修改在9 年前。]

I tried to obtain resulting SQL, I tested it on PostgreSQL client, but the error is the same showing in Liferay console.