Opened 17 years ago

Closed 17 years ago

#781 closed defect (fixed)

AnnotationSet.getAllInheritedAnnotations() may generate invalid query on Postgres

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: blocker Milestone: BASE 2.4.3
Component: core Version:
Keywords: Cc:


On the /common/annotations/list_annotations.jsp the AnnotationSet.getAllInheritedAnnotations() method is called and the result is sorted by the annotation type name. This should be deterministic since each annotation can have only one annotation type. But Postgres complains with:

org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(
	at org.postgresql.core.v3.QueryExecutorImpl.execute(
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(
	at org.hibernate.loader.Loader.getResultSet(
	at org.hibernate.loader.Loader.doQuery(
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(
	at org.hibernate.loader.Loader.doList(
	at org.hibernate.loader.Loader.listIgnoreQueryCache(
	at org.hibernate.loader.Loader.list(
	at org.hibernate.loader.hql.QueryLoader.list(
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(
	at org.hibernate.engine.query.HQLQueryPlan.performList(
	at org.hibernate.impl.SessionImpl.list(
	at org.hibernate.impl.QueryImpl.list(
	at net.sf.basedb.core.HibernateUtil.loadList(
	at net.sf.basedb.core.ItemQuery.list(
	at org.apache.jsp.common.annotations.list_005fannotations_jsp._jspService(

The select list is created by Hibernate contains only columns from the Annotations table (which it should). The column is not in the selection list. It is used in the ORDER BY clause and Postgres doesn't like that.

How can we solve that? Some alternative to check and try out:

  1. The ItemQuery should be smart and use DbEngine.selectOrderByColumnsIfDistinct() and add extra columns to the selection list if it is needed. It might be a bit problematic to know if the order by column is already in the selection list or not. Maybe it doesn't hurt if we add all order by columns we find just to be safe. But... this may change the result of the query in an unexpected way if we happen to order by a column that is not already distinct. This solution can probably fix the bug and possible also other similar bugs, but may cause queries some queries to behave differently than before.
  1. Use a fetch join to AnnotationTypes instead. This would force Hibernate to add all AnnotationType columns to the selection list. I don't know if Hibernate handles distinct queries with fetch join though... If it works it will fix this bug but not other queries affected by the same problem.

See for more information.

Change History (4)

comment:1 by Nicklas Nordborg, 17 years ago

Milestone: BASE 2.5BASE 2.4.3

comment:2 by Nicklas Nordborg, 17 years ago

Owner: changed from everyone to Nicklas Nordborg
Status: newassigned

comment:3 by Nicklas Nordborg, 17 years ago

I have just verified that option 2 with a fetch join seems to work. It is a very small change and will not affect other code so I think that we will go with option 2.

comment:4 by Nicklas Nordborg, 17 years ago

Resolution: fixed
Status: assignedclosed

(In [3776]) Fixes #781: AnnotationSet.getAllInheritedAnnotations() may generate invalid query on Postgres

Note: See TracTickets for help on using tickets.