Bad HQL Translation?

gstamp · **Posted:** Wed Feb 04, 2004 5:54 am

I've for the following HSQL

Code:

from Instance as i
where i.content.documentType.description = ? and elements(i.content.keywords) in ( 'glen', 'stampoultzis' )

which hibernate is translating to the following SQL Server 2000 query:

Code:

select instance0_.InstanceId as InstanceId, instance0_.MajorRevisionNumber as MajorRev2_, instance0_.MinorRevisionNumber as MinorRev3_, instance0_.InstanceCreateDateTime as Instance4_, instance0_.User1 as User1, instance0_.User2 as User2, instance0_.User3 as User3, instance0_.User4 as User4, instance0_.User5 as User5, instance0_.Barcode as Barcode, instance0_.Comment as Comment, instance0_.ModifiedByUserId as Modifie12_, instance0_.ContentId as ContentId 

from SVD_INSTANCE instance0_, SVD_SEAVIEWCONTENT content1_, SVD_DOCUMENTTYPE documentty2_ 

where (documentty2_.Description=?  and instance0_.ContentId=content1_.ContentId and content1_.DocumentTypeExtention=documentty2_.DocumentTypeExtention)and((select keywords3_.keyword from SVD_KEYWORD keywords3_ where content1_.ContentId=keywords3_.InstanceId)in('glen' , 'stampoultzis') and instance0_.ContentId=content1_.ContentId)

This is invalid SQL for SQL server and produces the following error:

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is this a problem in my HQL or a bug in hibernate?

emmanuel · **Posted:** Wed Feb 04, 2004 9:01 am

Code:

elements(i.content.keywords) in ( 'glen', 'stampoultzis' )

is not allowed, neither in HQL or SQL the left side of in must be a single value

gstamp · **Posted:** Wed Feb 04, 2004 6:23 pm

emmanuel wrote:

Code:

elements(i.content.keywords) in ( 'glen', 'stampoultzis' )

is not allowed, neither in HQL or SQL the left side of in must be a single value

Thanks Emmanuel. I assumed that it was valid HQL because hibernate tried to convert it.

Do you know an alternative way of expressing this in HQL that is valid?

Regards,

Glen

michael · **Posted:** Wed Feb 04, 2004 6:26 pm

from Instance as i
left join i.content.keywords keyword
where i.content.documentType.description = ? and keyword in ( 'glen', 'stampoultzis' )

emmanuel · **Posted:** Wed Feb 04, 2004 8:06 pm

Mostly HQL is based on SQL, so lot's of check are done by the DB.