while executing the search query on blogEntry page it executes the following query, i got it through debugging.

from

( select t.*,query

from blogEntry t,

plainto_tsquery('pg_catalog.english', ?) query

where t.blogEntry_BlogId = ? and query @@ tsv

order by ts_rank(tsv, query, 16|32) desc

) as results; limit ?

i don't have that "limit ?" part in my hbm file query but it takes it by default.

o,. i will come to your office at 3 pm. thank you.

I've got an idea on my way home. Try removing both the "limit" clause and the semicolon at the end. Let me know if this fixes the problem.

3000... wrote:

...

It's not a good idea to use your CIN as username. If you want to change to a different username, send me an email.

ellen214 wrote:

finally, it works with removing the semicolon at the end of "as results".

i keep

                order by ts_rank(tsv, query, 16|32) desc
                limit 40

only remove the semicolon...

isn't it weird?

I think inserting "limit ?" is how Hibernate implements the setMaxResults() method. Normally when HQL is used, it would not cause any problem because Hibernate guarantees that translation from HQL to SQL is syntactically correct. However, when native SQL query is used, as in our case, attaching "limt ?" directly to the end of the query may cause syntactical errors.

To avoid such errors, the native SQL query should not include a semicolon at the end so we avoid the case like "select ...; limit ?". Note that JDBC does not require SQL statements to be terminated by semicolon. In fact, a semicolon probably should never be used to ensure DBMS compatibility. Also the native query should not include the limit clause to avoid the case like "limit 40 limit ?". If a max result limit needs to be set, do it in Hibernate using query.setMaxResults() or in Spring using hibernateTemplate.setMaxResults().

The only question remains is why "limit ?" was inserted when we didn't call setMaxResults(). It could be a bug in Spring/Hibernate, or it could be something about HibernateTemplate that I didn't fully understand.

ellen214 wrote:

I think if we don't call setMaxResults()...it'll still insert "limit=0"

 That's the problem. It doesn't insert "limit 0" though because then nothing will be returned.

the question is ...if we have "limit=40" (for native sql) but no setMaxResults()

then will hibernate still insert "limit=0" into the end of sql? like this "limit=40 limit=0"???

 If we don't call setMaxResult(), Hibernate should not insert "limit ?". The unsolved mystery right now is that sometimes it does.

if it does "limit=40 limit=0", do you think the Forums really limit the results???

 It would be a syntax error.