Problems with subselect and DetachedCriteria

arbitrary · **Joined:** Mon Jul 18, 2005 10:34 am **Posts:** 3

I'm using Hibernate 3.0.5 with Firebird 1.5 as database.

Simplified scenario: I have a main table, let's call it Person, with a one-to-many subtable called Contacts.

Creating criteria for Person:

Code:

DetachedCriteria dc = DetachedCriteria.forClass(Person.class);
dc.add(Restrictions.eq("city", "Somewhere"));

Later I want to use the detached criteria to select records from the Contacts table:

Code:

Criteria criteria = session.createCriteria(Contacts.class);
criteria.add(property.forName("person").in(dc));
criteria.list();

I get this exception:

Code:

Exception in thread "main" java.lang.NullPointerException
   at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:298)
   at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:56)
   at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:314)
   at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:92)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1303)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
   at test.HibernateTest.main(HibernateTest.java:107)

After some debugging, I figured out that Hibernate expects some kind of projection. If there is none, you get a null pointer exception. Ok... so I added a projection to my detached criteria, like this:

Code:

DetachedCriteria dc = DetachedCriteria.forClass(Person.class);
dc.add(Restrictions.eq("city", "Somewhere"));
dc.setProjection(Projections.property("id"));

This actually worked, at least it generated an SQL. But Firebird didn't like that SQL, because there was an "as" in the subselect. Here's the SQL (some fields in the outer select left out)

Code:

select this_.ID as ID0_, ...(more)...  from CONTACTS this_ where this_.PERSON in (select this0__.ID as y0_ from PERSON this0__ where this0__.CITY=?)

Exception:

Code:

Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 365
as

Apparently, Firebird doesn't allow the keyword "as" in subselects.

I tried to do the same with HQL instead of Criteria, using a query like this:

Code:

from Contacts c where c.person in (select p.id from Person p where p.city = 'Someplace')

Funny enough, that worked perfectly. So it just seems to be a problem with criteria. Unfortunately I can't use HQL, I need to make subselects with the criteria API. I'd be thankful for any hints how to achieve that.

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

I sure am glad you made that post, you saved me some time =) I ran into the same NPE doing the same thing you were doing, and was just about ready to go to the code base myself. Adding a projection was just the ticket.

It's been so long that I'm sure you've got a solution to your second problem regarding SQL, but did you ever create a JIRA issue for it? Seems like the sort of thing that should be fixable with a tweak to a SQL Dialect.

arbitrary · **Joined:** Mon Jul 18, 2005 10:34 am **Posts:** 3

Well, I don't think the use of the "as" keyword can be switched off in the dialect, Hibernate uses it everywhere, and I don't know any database which doesn't support it. So I consider that a bug in Firebird. The guys on the Firebird mailing list helped me patch the Firebird source to make it work, and it might be in Firebird 2.0. But we're switching to Postgresql anyway, after having more issues with Firebird.

Regarding the problems with subselect, detached criteria, etc... there are plenty of bugs in JIRA regarding that topic, some of them with lots of votes. I gave up using Criteria with subselects after discovering that I can't even use joins in subselects. The Criteria API seems to have a low priority at the moment... some of those bugs have been open for more than a year now :( Unfortunately I don't have the time to mess with the Hibernate source myself.

http://opensource.atlassian.com/project ... se/HHH-158
http://opensource.atlassian.com/project ... se/HHH-879
http://opensource.atlassian.com/project ... se/HHH-952
http://opensource.atlassian.com/project ... se/HHH-957

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

Sigh. I've run into some of those as well, and possibly others. For example, I suspect that implicit polymorphism may be broken in subqueries. So I've largely given up on the Criteria API as well, and will likely do most of my querying in HQL.