AnnotationSet.getAllInheritedAnnotations() may generate invalid query on Postgres

[Nicklas Nordborg]

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(QueryExecutorImpl.java:1525)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
	at org.hibernate.loader.Loader.doQuery(Loader.java:674)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
	at org.hibernate.loader.Loader.doList(Loader.java:2220)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
	at org.hibernate.loader.Loader.list(Loader.java:2099)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
	at net.sf.basedb.core.HibernateUtil.loadList(HibernateUtil.java:1459)
	at net.sf.basedb.core.ItemQuery.list(ItemQuery.java:93)
	at org.apache.jsp.common.annotations.list_005fannotations_jsp._jspService(list_005fannotations_jsp.java:146)

The select list is created by Hibernate contains only columns from the Annotations table (which it should). The AnnotationTypes.name 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.

2. 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 ​http://www.mail-archive.com/basedb-devel@lists.sourceforge.net/msg00094.html for more information.

[Nicklas Nordborg]

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.

[Nicklas Nordborg]

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