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...

Answer 1

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.

Answer 2

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?

Answer 3

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.

Answer 4

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?

Answer 5

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.

Answer 6

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.