You can explain an invalid SQL statement November 27, 2010

I’m in “nightmare weekend before presenting” mode. I’m up to my eyes at work (and have been for ages, thus the quiet blog) and my recent weekends have been full of normal {and abnormal} life.

As is the way, when up against it and putting together my proofs for wild claims, everything breaks subtly and makes my wild claims look a little, well, wild – even though they are real issues I’ve seen, worked through and fixed in the day job. *sigh*. It does not help when you come across little oddities you have never seen before and end up spending valuable time looking into them.

So here is one. I’m just putting together a very, very simple demo of how the number of rows the CBO expects to see drops off as you move outside the known range. In the below you can see the statement I am using (I keep passing in different days of the month and watching the expected number of rows drop until I hit 1 expected row), but look at how it progress to the last entry…

The expected number of rows drops, becomes and – and has shot up to 99 again (which is the expected number in the known range, as I have 10,000 rows spread over 100 days). My immediate thought is “Wow! Maybe Oracle have put some odd fix in where when you go well out of range it reverts to expecting an average number of rows”. Nope. It is because I asked for the data for 30th February. And I did not get an error.

I think it is because I have set autotrace traceonly explain. This causes the SQL statement not to be executed {if it is just a select, not an insert, update or delete}. It seems the costing section of the CBO is not so good at spotting duff dates, but it then gets the costing wrong.

I’ve spotted that the format of the filter also changes when the date is invalid, I really want to check that out – but I better continue failing to write the presentation!

I know, pretty pointless knowing this but it just amused me. Below is just a quick continuation to show that if the statment is to be executed you get an error and no plan and that utterly duff dates can be passed in.

Like this:

Related

I wonder if there might be another angle to examine. Is it possible that the SQL statement with the invalid date really is a valid SQL statement, it is just the EXECUTE stage that fails when it tries to convert the passed in text into a valid date? Consider the following:

SET AUTOTRACE OFF
SELECT * FROM T3 WHERE TO_NUMBER(C1)=1;
ERROR at line 1:
ORA-01722: invalid number
SELECT * FROM T3 WHERE TO_NUMBER(C1)=TO_NUMBER('A') AND ROWNUM=2;
ERROR at line 1:
ORA-01722: invalid number
SELECT * FROM T3 WHERE TO_NUMBER(C1)=1 AND 1=2;
no rows selected
SELECT * FROM T3 WHERE TO_NUMBER(C1)=TO_NUMBER('A') AND 1=2;
no rows selected

If Oracle Database does not actually touch a row, as in the last two cases, does that mean the SQL statement is valid because no error was returned?

As I stated, just another way to look at the situation (my view might not be correct).

Charles, you are making me think about this again!!! I’ve got to do this presentation!

Your points are very valid. In your first example, the statement is valid (though very naughty code in my opinion due to it’s inherent likelihood to fail) and it will work depending on the data. Or not, in this case.

The second example is similar to mine in that the CBO must be converting the provided into a value to make it’s estimation of cost, and that to my mind is a bit of a problem as the conversion is invalid – but the CBO does something with it to get a value to estimate the cost with.

The third option with rownum =2 is particularly interesting as it will pass or fail depending on in which order the two predicates get checked, I think

And at this point I have to (a) point out that the output does not seem to be quite matching the list of tests at point 3 and (b) stop thinking about this and do my presentation!

Sadly, Mohammad, it seems the more you know the more you realise you don’t know and it just gets worse and worse :-)
For example, I have just realised that Charles has added a LOT more to his post and I need to go and look at it again.
On the plus side, the more you look into things and read around, the more great people you find to learn along with.