Hello,

I have a very strong problem with an HQL query and I'm looking for your precious help ;-)

My HQL query is :
select distinct articles from com.iserva.model.Article as articles
inner join articles.column as column
inner join articles.state as state
where
articles.language =:language
and articles.state.stepId =:stepId
and articles.state.status =:status
and articles.column =:column

The SQL Equivalent generated by Hibernate is :
select distinct ... article fields ... from ARTICLE article0_ inner join `COLUMN` column1_ on article0_.COLUMN_ID=column1_.COLUMN_ID inner join OS_CURRENTSTEP state2_ on article0_.WORKFLOW_ID=state2_.entry_id where (article0_.LANGUAGE=? )and(state2_.step_Id=? )and(state2_.status=? )and(column1_.COLUMN_ID=? )

The problem is taht the SQL query return the good results, but not the HQL one !

I have used the P6Spy framework to obtain the exact query the Hibernate motor launches (query with all values not '?' chars) and I still having the same problem : SQL is good not the HQL one

Whant does hibernate do after executing like my SQL query ? any idea please ?

After many searching hours I have found this alternative that works fine, the difference is that I do no join on Column object :

select distinct articles from com.iserva.model.Article as articles
inner join articles.state as state
where
articles.language =:language
and articles.state.stepId =:stepId
and articles.state.status =:status
and articles.column =:column

Why this work and not the first one ?

Thanks in advance.

What is happening when the HQL fails? Is there an exception? Are you getting an empty list back? If there's an exception, please post it.

You don't need either join in this query, but it should work even with the join. The only difference is that when you put the join in, the COLUMN table is joined to the Article table, even though it doesn't need to be (because the only column from the COLUMN table that's needed is its id, and that's in the Article table already).

It is possible that the problem is caused because you've given a table a keyword for its name. Hibernate seems to be handling it (putting the quotes around the name), but maybe there's somewhere else that needs to be aware of this. However, simply removing both joins (column and state) is a good idea, because it will make the resulting query smaller.

It is not really recommended that you use keywords in your mapping. Can you rename the table? ColumnTable might work...

Thanks to your your response.

1- I have no exception and the query returns empty result
2- I agree that there is no need to use join here, but I have to add others conditions to the query latter and I must use joins for these conditions
3- Even the Column table name is quoted (like 'COLUMN') the HQL query (not the SQL one) return no results.

I will try to rename the Column table and dont use keywords any where.

What do you think about my mapping, can It causes any problem ?

<class name="com.iserva.model.Article"
...
<many-to-one name="column"
column="COLUMN_ID"
not-null="true"
cascade="none"/>

</class>

I tried to change the name of alias and tables, and I still having the same problem !! it is realy very strong !!

I am working with hibenrate 2.

So in the database, you have no table called COLUMN any more? But you still have your original problem, that if you have an inner join to the table that used to be called COLUMN, you get no rows returned? If that's the case, the next thing to investigate is the parameter passed in for comparison with the COLUMN_ID. Perhaps there are no rows in the COLUMN table for that id, but there are rows in the Articles table with the column_id. Turn on debugging by editing your log4j.properties to includeCheck the output to find out what column_id is being compared to, then check that there is a row in the COLUMN table with that id.