SELECT urls.url, urls.id FROM f4t.hibernate.Url as urls
WHERE urls.id = html.urlid
AND
(
urls.url LIKE 'http://foo/%'"
OR urls.url LIKE 'http://bar/%'"
)

return 3768 rows while

SELECT urls.url, urls.id FROM urls
WHERE urls.id = html.urlid
AND
(
urls.url LIKE 'http://foo/%'"
OR urls.url LIKE 'http://bar/%'"
)


does return only 3656 rows.

Why is that? And which result is correct actually?

Look at the SQL Hibernate generates. This is much better than guessing.

I did:

select url0_.url as x0_0_, url0_.id as x1_0_ from urls url0_ where (url0_.id=html.urlid )AND((url0_.url LIKE 'http://foo/%' )OR(url0_.url LIKE 'http://bar/%' ))

:-)

...which actually does return 3656 rows :-\

I did the counting this way:

Iterator iter = session.createQuery(query).iterate();
int i = 0;
for(;iter.hasNext();iter.next()) i++;
System.out.println(i);

And this outputs 3768...(?!).

As I understand both queries are invalid and must return an error not a result.

SELECT urls.url, urls.id FROM urls
WHERE urls.id = html.urlid
^^^^^^
AND
(
urls.url LIKE 'http://foo/%'"
OR urls.url LIKE 'http://bar/%'"
)


Are you sure you executed this query ?

try to use
List l = session.createQuery(query).list();

System.out.println(l.size());

how many rows you get...

3768...

The query is valid and executes.

SELECT urls.url, urls.id
FROM urls
WHERE urls.id = html.urlid
AND
(
urls.url LIKE 'http://foo/%'"
OR urls.url LIKE 'http://bar/%'"
)

Is "html" a custom type field in "urls" table ?

http://forum.hibernate.org/viewtopic.ph ... 167ac813b4

This SQL is invalid, it works on postgresql, but this is more bug than feature in parser to support queries like "SELECT 1" and I am not sure it returns expected results.

Ok, and what would the correct HQL look like?

As I understand you want this:

SELECT urls.url, urls.id
FROM f4t.hibernate.Url as urls
WHERE urls.url LIKE 'http://foo/%'" OR urls.url LIKE 'http://bar/%'"