顶部链接错误。有效 sql 结果为空且结果不为空
怎么可能呢？
我们正在 Toplink 上执行 EJBQL（DB 是 Oracle），query.getResultList 为空。
但是！ 当我将日志级别切换到 FINE 并收到 TopLink 生成的 Sql 查询时，我尝试在数据库上执行此查询，（奇迹！）我得到了一个非空结果！
可能是什么原因以及如何治疗？ 提前致谢！
PS 没有例外。
更新：
查询日志：
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]|#]
此查询返回 100k 行，但 toplink 认为它不是...
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...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
当日志级别设置为 FINE 时，您能否验证是否正在连接到同一个数据库？你的测试用例有多简单？您能否验证是否正是这个 JPQL 被转换为该 SQL？
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?
VPD（http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/vpd.htm）？政策？
模式中是否定义了这种风格？这些功能透明地向数据库会话中执行的语句添加动态 where 子句，因此在这种情况下查询结果取决于会话的状态。
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.
重新格式化查询时，以下条件似乎很奇怪：
(+)指示 t2 (DEFAULTABLE_DICTIONARY) 的外连接，但该表似乎是非可选的，因为它必须具有非空 DICT_TYPE对于第二个条件。仔细观察，绑定参数似乎也已关闭，字段按顺序排列
使用给定的参数（4537、R、R、realty_status），第一个 DICT_TYPE 将是 ' R' 而第二个是字符串“realty_status”，这似乎不一致。
When reformatting the query the following conditions seemed strange:
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
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.
交易？ Oracle 永远不会为您提供“脏读”，即数据库代表对未提交数据的访问。如果您在一个连接上发送数据，则在提交数据之前，您无法在任何其他连接上访问该数据。如果您稍后手动尝试查询，则数据已提交并且您将获得预期结果。
如果您在多个连接中更新数据，并且数据操作未设置为“自动提交”，则可能会出现这种情况。 JPA 默认为自动提交，但在事务边界刷新可以为您提供更简洁的设计。
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.
我不能确切地告诉你，但我有点惊讶的是字符串参数没有被引用。是否有可能以交互方式进行一些自动转换，但通过此连接而不是字符串“R”，它被转换为 R 的 INT ascii？
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?
我找到原因了！
原因就是甲骨文！我在 Postgres 上尝试了相同的代码并且成功了！
我不知道为什么，但在某些神奇的情况下，oracle 会忽略查询参数并且查询返回空结果。
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.