Invalid SQL generated from HQL based on order of where clauses when using size()

[jamoore]

Queries:

success = "select i from Image i join i.annotationLinks links join links.child ann where size(i.datasetLinks) > 0 and ann.id = :id"
failing = "select i from Image i join i.annotationLinks links join links.child ann where ann.id = :id and size(i.datasetLinks) > 0"

Generated SQL from failing query:

select image0_.id as id114_, image0_.condition as condition114_, image0_.context as context114_, image0_.description as descript2_114_, image0_.creation_id as creation8_114_, image0_.external_id as external14_114_, image0_.group_id as group15_114_, image0_.owner_id as owner7_114_, image0_.permissions as permissi3_114_, image0_.update_id as update12_114_, image0_.name as name114_, image0_.objectiveSettings as objecti10_114_, image0_.position as position114_, image0_.setup as setup114_, image0_.version as version114_ from image image0_ inner join imageannotationlink annotation1_ on image0_.id=annotation1_.parent and 
( 
 :securityFilter.is_admin OR 
 (annotation1_.group_id in (:securityFilter.leader_of_groups)) OR 
 (annotation1_.owner_id = :securityFilter.current_user AND (cast(annotation1_.permissions as bit(64)) & cast(1024 as bit(64))) = cast(1024 as bit(64))) OR 
 (annotation1_.group_id in (:securityFilter.current_groups) AND (cast(annotation1_.permissions as bit(64)) & cast(64 as bit(64))) = cast(64 as bit(64))) OR 
 ((cast(annotation1_.permissions as bit(64)) & cast(4 as bit(64))) = cast(4 as bit(64))) 
) inner join annotation annotation2_ on annotation1_.child=annotation2_.id where 
( 
 :securityFilter.is_admin OR 
 (image0_.group_id in (:securityFilter.leader_of_groups)) OR 
 (image0_.owner_id = :securityFilter.current_user AND (cast(image0_.permissions as bit(64)) & cast(1024 as bit(64))) = cast(1024 as bit(64))) OR 
 (image0_.group_id in (:securityFilter.current_groups) AND (cast(image0_.permissions as bit(64)) & cast(64 as bit(64))) = cast(64 as bit(64))) OR 
 ((cast(image0_.permissions as bit(64)) & cast(4 as bit(64))) = cast(4 as bit(64))) 
) and annotation2_.id=? and (select count(datasetlin3_.child) from datasetimagelink datasetlin3_ where image0_.id=datasetlin3_.child and 
( 
 :securityFilter.is_admin OR 
 (datasetlin3_.group_id in (:securityFilter.leader_of_groups)) OR 
 (datasetlin3_.owner_id = :securityFilter.current_user AND (cast(datasetlin3_.permissions as bit(64)) & cast(1024 as bit(64))) = cast(1024 as bit(64))) OR 
 (datasetlin3_.group_id in (:securityFilter.current_groups) AND (cast(datasetlin3_.permissions as bit(64)) & cast(64 as bit(64))) = cast(64 as bit(64))) OR 
 ((cast(datasetlin3_.permissions as bit(64)) & cast(4 as bit(64))) = cast(4 as bit(64))) 
)
)>0

Exception:

1118995    [l.Server-5] INFO           ome.services.util.ServiceHandler  -  Meth:       findAllByQuery
1118995    [l.Server-5] INFO           ome.services.util.ServiceHandler  -  Args:       [select i from Image i join i.annotationLinks links join links.child ann where 
ann.id = :id and size(i.datasetLinks) > 0, ome.parameters.Parameters@9e2a]
1118996    [l.Server-5] INFO            ome.security.basic.EventHandler  -   Auth:      user=100,group=2,event=null(User)
1119029    [l.Server-5] ERROR  org.hibernate.util.JDBCExceptionReporter  - ERROR: operator does not exist: bigint = boolean
1119030    [l.Server-5] WARN           ome.services.util.ServiceHandler  - InvalidDataAccessResourceUsageException thrown.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not ex
ecute query
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:613)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
        at ome.logic.QueryImpl.execute(QueryImpl.java:147)
        at ome.logic.QueryImpl.findAllByQuery(QueryImpl.java:375)
        at sun.reflect.GeneratedMethodAccessor176.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
...
        at Ice.ConnectionI.invokeAll(ConnectionI.java:2249)
        at Ice.ConnectionI.message(ConnectionI.java:1362)
        at IceInternal.ThreadPool.run(ThreadPool.java:782)
        at IceInternal.ThreadPool.access$100(ThreadPool.java:12)
        at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:1242)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2216)
        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 ome.services.query.Query.doInHibernate(Query.java:239)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
        ... 53 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = boolean
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:257)
        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:2213)
        ... 62 more

[jmoore]

r2399 has a python test.

[jmoore]

Moving this to cleanup. It'll require testing new Hibernate versions.

[jamoore]

Marking as a duplicate of #12442 which is a more general issue with size()