Hibernate version:
Hibernate 3 beta 4

I'm trying to use the following Detached Criteria to use as a subQuery and am encountering some weirdness I can't quite figure out.

Based on executing this by itself:


I get the following SQL:



Which is perfect so far. So I'm getting a list of SRC_ADDR_IDs from a joined table where END_USE_CODE is sales. What isn't shown here is a layer of filtering that applies to the VERSIONS_PROPERTY but I'm trying to get this working for a simple case first.

So, expanding on this I take the subquery and wind up with this:


Which generates a sql query with the following WHERE clause (I've cut out the rest since all it contains is a bunch of column selects from my ADDRESS table (mapped to Location.class):


What I was hoping to get was something along the lines of:



So, in this case I end up pondering 4 things:

1 - What have I done so that the inner join doesn't get generated in the SubQuery?
2 - The SRC_ADDR_ID is actually mapped to a Location instance so I get a class cast trying to compare the ADDR_ID which is a Long to the Location.class
Is there any way to accomplish this?
3 - since the where clause is built up with "Where ? in (select....." does that mean the subquery is actually going to cause a separate select for each Location I have? i.e. will it load all locations and then try to match them based on the subQuery.
4 - Am I missing something so basic that gavin will appear and beat me soundly with a stick?

Try using Subqueries.propertyIn()

Ok, you got me halfway there. Using propertyIn I ended up with



And now it didn't complain about the class cast but I'm still missing the inner join. I hate to ask but a bug perhaps?

Before you do anything, try without those strange Constants you are using everywhere and repost - I am totaly unable to read that stuff properly.

All the strange constants are simply strings, but will do. The stange thing is that the subquery executed by itself will generate the inner join based on the createAlias() but passed as a property into the propertyIn() it doesn't.



Gives me the following where clause (without the expected inner join):


Where if the subQueryCriteria is executed standalone I get (with the expected inner join):


When executed as an actual subquery I the following exception (which is expected since I don't end up with the version1_ alias from the join):
Caused by: java.sql.SQLException: ORA-00904: "VERSIONS1_"."END_USE_CODE": invalid identifier

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1158)
at org.hibernate.loader.Loader.doQuery(Loader.java:357)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:199)
at org.hibernate.loader.Loader.doList(Loader.java:1417)
... 31 more

Hm, this might be a bug - give Gavin some time to reply here, and file a JIRA entry sometimes.

Thanks for taking the time to prod me in the right direction Michael. I'll log a JIRA if gavin either
a)requests one
or
b)he doesn't reply to the thread

but before I execute on b i'll take a peek up into the bowels to try to figure out what's going on. I saw some comments regarding the not allowing implicit joins in the code but I would assume I would be able to walk a table via the alias.

Using the subquery will save me from adding a special association and implementing a new set of filters. (assuming filters get applied on the subQuery in the first place :))

I was quite happy to see some movement in the Criteria API the past couple of releases.

Taking a look at the source code is definitely a good idea ;) Have patience with the new APIs :)

Oh, I've got patience, don't get me wrong there. I'm not screaming bloody murder or anything. I just wanted to determine that I wasn't absolutely daft before I went barking up any trees.

Most of the stuff works fine, especially with the test examples. But I didn't see any tests that were doing quite what I wanted to do so I had to question my thought pattern. The in() vs. the propertyIn() was a case in point. I completely missed that.

I was just excited to try out the new features and if I get a chance to help you guys knock out the cobwebs then that's what it's all about.

That was no critisism, people exploring the new API and well done posts about things that are not working as excpected are always welcome.

For now, joins are not possible in Criteria subselects. I need to do a little bit of a refactor of the OuterJoinLoader class and subclasses to make that possible. I hope to get this done "soon".

No problem gavin, just wanted to make sure I wasn't going simple or that it's on the books as it seemed a bit off that it didn't work.

I'm highly anticipating this as it should help me dump some associations that are there simply so we can do the criteria queries.

Criteria API is getting a massive shove in the right direction as far as I'm concerned.

As a bit of an aside, does this also mean that there are limitations with the grouping work as well?

PS, any more thoughts on that FetchMode.IMMEDIATE_SELECT? :D I can fire it off as an enhancement request if it helps you keep track of it better.

Has this been fixed yet?

All I can find on the JIRA is a bug which is closed as a duplicate, but no indication what it's a duplicate of. I could really do with finding out whether it's been fixed, as we might have to radically change our implementation if it's not fixed and isn't likely to be.

Unfortunately I can't easily test it with Hibernate 3.0.3 as the Annotations project we're also relying on doesn't work with 3.0.3 yet...

Jon

Don't believe so, I had actually opened another thread about this 2 weeks ago, forgetting that I had one open from before :)

It's the alzheimer's setting in, I swear.

Rats. Ah well.

I wonder - is there likely to be any point in me trying to fix the bug myself? I've done a certain amount of patching on open source projects before, but the Hibernate code base is probably rather more complex than anything I've tackled before...