Toplink bug. Empty result for valid sql with not empty result

Question

How is it possible?

We are executing EJBQL on Toplink(DB is Oracle) and query.getResultList is empty.

But! When i switched log level to FINE and received Sql query, that TopLink generates, i tried to execute this query on database and (miracle!) i got a non-empty result!

What could be the reason and how is it treated? Thanks in advance!

P.S. No exceptions.

UPDATE:

Query log:

SELECT DISTINCT t0.ID, t0.REG_NUM, t0.REG_DATE, t0.OBJ_NAME, t1.CAD_NUM, t1.CAD_NUM_EGRO, t2.ID, t2.DICT_TYPE, t2.ARCHIVE_DATE, t2.IS_DEFAULT, t2.IS_ACTUAL, t2.NAME, t0.INVENTORY_NUM FROM CODE_NAME_TREE_DICTIONARY t3, DEFAULTABLE_DICTIONARY t2, IMMOVABLE_PROP t1, ABSTRACT_PROPERTY t0 WHERE ((t3.ID IN (SELECT DISTINCT t4.ID FROM CODE_NAME_TREE_DICTIONARY t5, CODE_NAME_TREE_DICTIONARY t4, type_property_parents t6 WHERE (((t5.ID = ?) AND (t4.DICT_TYPE = ?)) AND ((t6.type_property_id = t4.ID) AND (t5.ID = t6.parent_id)))) AND ((t1.ID = t0.ID) AND (t0.PROP_TYPE_DISCR = ?))) AND ((t3.ID = t0.PROP_TYPE) AND ((t2.ID (+) = t1.STATUS_ID) AND (t2.DICT_TYPE = ?)))) ORDER BY t0.REG_NUM ASC
    bind => [4537, R, R, realty_status]|#]

This query returns 100k rows, but toplink believes that it is not...

Did you also log the query parameters? Any date or float parameter that might have been rounded differently? Or perhaps any locale specific conversions? — Jörn Horstmann, Apr 18 '11 at 19:40
No locale specific. Only integers in query params. Query parameter is only ids from two tables. I will post query. — Sergey Vedernikov, Apr 19 '11 at 03:53

score 1 · Answer 1 · answered Apr 15 '11 at 13:03

1

With log level to FINE can you verify that you are connecting to the same database? How simple is your testcase; can you verify that it is this exact JPQL that is being translated to that SQL?

answered Apr 15 '11 at 13:03

Gordon Yorke

1,996
11
7

this is precisely one base, and a valid request I'm not confused, but maybe it's because of the fact that the version of toplink not last – Sergey Vedernikov Apr 15 '11 at 16:03

score 1 · Answer 2 · answered Apr 19 '11 at 12:41

VPD (http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/vpd.htm)? Policies? Is something of this flavor defined on the schema? These features transparently add dynamic where clauses to the statement that is executed in the database session, so the query results depend on the state of the session in this case.

score 1 · Answer 3 · answered Apr 23 '11 at 22:50

When reformatting the query the following conditions seemed strange:

AND t2.ID (+) = t1.STATUS_ID
AND t2.DICT_TYPE = ?

The (+) indicates an outer join of t2 (DEFAULTABLE_DICTIONARY), but this table seems to be non-optional since it has to have a non-null DICT_TYPE for the second condition.

On closer looking, the bind parameters also seem to be off, the fields are in order

CODE_NAME_TREE_DICTIONARY.ID
CODE_NAME_TREE_DICTIONARY.DICT_TYPE
ABSTRACT_PROPERTY.PROP_TYPE_DISCR
DEFAULTABLE_DICTIONARY.DICT_TYPE

With the given parameters (4537, R, R, realty_status), the first DICT_TYPE would be 'R' while the second is the string "realty_status" which seems inconsistent.

score 0 · Answer 4 · answered Apr 15 '11 at 11:51

Transactions? Oracle never gives you a "dirty read" which database speak for access to uncommitted data. If you send data on one connection you cannot access it on any other connection until it is committed. If you try the query later by hand, the data has been committed and you get the expected result.

This situation can arise if you are updating the data in more than one connection, and the data manipulation is not set to "auto commit". JPA defaults to auto-commit, but flushing at transaction boundaries can give you a cleaner design.

No. Result have 100000 rows. There is no modifications of db in moment of reading. — Sergey Vedernikov, Apr 15 '11 at 11:53

score 0 · Answer 5 · answered Apr 21 '11 at 00:19

0

I can't tell exactly, but I am a little surprised that the string parameters are not quoted. Is it possible that interactively there are some automatic conversions, but over this connection instead of the string 'R' it was converted to the INT ascii for R?

answered Apr 21 '11 at 00:19

Andrew Lazarus

18,205
3
35
53

it was converted correctly, i will try to catch real sql query – Sergey Vedernikov Apr 21 '11 at 03:09

score 0 · Accepted Answer · answered Apr 26 '11 at 09:10

0

I found the reason! The reason is Oracle! I've tried the same code on Postgres and its worked!

I dont know why, but in some magic cases oracle ignores query parameters and query returns empty result.

answered Apr 26 '11 at 09:10

Sergey Vedernikov

7,609
2
25
27

Toplink bug. Empty result for valid sql with not empty result

6 Answers6