意外的查询成功 [英] unexpected query success
问题描述
SELECT COUNT (*)
FROM rps2_workflow
WHERE workflow_added > TO_DATE ('01.09.2011', 'dd.mm.yyyy')
AND workflow_finished < TO_DATE ('wtf', 'dd.mm.yyyy')
AND workflow_status IN (7, 12, 17)
AND workflow_worker = 159
由于日期无效,我希望此查询失败,但是它返回0
I expect this query to fail, because of invalid date, but it returns 0
此查询的计划显示,在第8步处理了无效子句 :
The plan for this query shows that on 8th step the invalid clause is processed:
8 TABLE ACCESS BY INDEX ROWID TABLE RPS2.RPS2_WORKFLOW Object Instance: 1 Filter Predicates: ("WORKFLOW_STATUS"=7 OR "WORKFLOW_STATUS"=12 OR "WORKFLOW_STATUS"=17) AND SYS_EXTRACT_UTC("WORKFLOW_FINISHED")<SYS_EXTRACT_UTC(TO_DATE('wtf','dd.mm.yyyy')) Cost: 11 Bytes: 33 Cardinality: 1 CPU Cost: 8 M IO Cost: 10 Time: 1
如果我们注释掉AND workflow_status IN (7, 12, 17)
条件-那么预期我们会得到ORA-01858: a non-numeric character was found where a numeric was expected
If we comment out AND workflow_status IN (7, 12, 17)
condition - then expectedly we get ORA-01858: a non-numeric character was found where a numeric was expected
如果我们注释掉AND workflow_finished < TO_DATE ('wtf', 'dd.mm.yyyy')
,那么我们将获得满足该条件的记录数量(> 0)
If we comment out AND workflow_finished < TO_DATE ('wtf', 'dd.mm.yyyy')
then we get amount of records that fit that conditions (> 0)
这怎么可能?
UPD :
提示/*+no_index(rps2_workflow) */
并没有任何改变(而在该计划中,我们看到执行了全扫描)
The hint /*+no_index(rps2_workflow) */
doesn't change anything (whereas in the plan we see that fullscan is performed)
SELECT STATEMENT ALL_ROWSCost: 254 Bytes: 31 Cardinality: 1 CPU Cost: 34 M IO Cost: 248 Time: 4
2 SORT AGGREGATE Bytes: 31 Cardinality: 1
1 TABLE ACCESS FULL TABLE RPS2.RPS2_WORKFLOW Object Instance: 1 Filter Predicates: "WORKFLOW_WORKER"=159 AND ("WORKFLOW_STATUS"=7 OR "WORKFLOW_STATUS"=12 OR "WORKFLOW_STATUS"=17) AND SYS_EXTRACT_UTC("WORKFLOW_ADDED")>SYS_EXTRACT_UTC(TIMESTAMP' 2011-09-01 00:00:00') AND SYS_EXTRACT_UTC("WORKFLOW_FINISHED")<SYS_EXTRACT_UTC(TO_DATE('wtf','dd.mm.yyyy')) Cost: 254 Bytes: 31 Cardinality: 1 CPU Cost: 34 M IO Cost: 248 Time: 4
推荐答案
它可能发现满足所有其他条件的每个记录都有一个NULL
workflow_finished
字段.
It probably found that every record satisfying all the other conditions have a NULL
workflow_finished
field.
与NULL
相比的任何事物都是未知的,因此不需要评估另一个操作数.
And anything compared to NULL
is unknown so it doesn't need to evaluate the other operand.
这篇关于意外的查询成功的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!