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
I expect this query to fail, because of invalid date, but it returns 0
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
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
If we comment out AND workflow_finished < TO_DATE ('wtf', 'dd.mm.yyyy')
then we get amount of records that fit that conditions (> 0)
How is this possible?
UPD:
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
If the optimizer decides that it doesn't need to evaluate a function, it won't, so the function will never throw exceptions:
select 1 from dual where 1 = 1 OR to_date('asdasdasd','asdasdasdas') > sysdate ;
1
----------
1
The function raises an exception only if it actually gets evaluated:
SQL> select 1 from dual where 1 = 1 AND to_date('asdasd','asdas') > sysdate ;
select 1 from dual where 1 = 1 AND to_date('asdasd','asdas') > sysdate
*
ERROR at line 1:
ORA-01821: date format not recognized
However, if the parser can decide statically that the query is invalid - because the function has the wrong type of arguments or the query has invalid types, then the parser will raise an exception before the optimizer gets a swing at it:
SQL> select 1 from dual where 1 = 1 or to_date('asdasdasd',0) > sysdate ;
select 1 from dual where 1 = 1 or to_date('asdasdasd',0) > sysdate
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
SQL> select 1 from dual where 1 = 1 or to_date('asdasdasd','asdasdasdas') > 42 ;
select 1 from dual where 1 = 1 or to_date('asdasdasd','asdasdasdas') > 42
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
It probably found that every record satisfying all the other conditions have a NULL
workflow_finished
field.
And anything compared to NULL
is unknown so it doesn't need to evaluate the other operand.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With