2
SQL> select to_date('    ','YYYYMMDD') from dual;
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, not be 0

SQL> select * from dual where to_date('    ','YYYYMMDD') = '19960512' or 1 = 2;
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, not be 0

SQL> select * from dual where to_date('    ','YYYYMMDD') = '19960512' or 1 = 1;
Success.

In first two statements we are passing spaces to TO_DATE function which gives an error which is expected. But

Now in third statement we are passing spaces to TO_DATE oracle function which is not allowed. But still it executes successfully.

Why?

In actual code I might pass spaces from variable and OR part may or may not be true.

Can anyone explain me this behaviour and how to handle to get an error?

P.S - I am on Oracle 12g EE

Shubham Khandare
  • 135
  • 1
  • 15

3 Answers3

4

Oracle uses short-circuit logic here. Since 1=1 is known to be true, Oracle optimizes out the evaluation of the other condition. Since it's never evaluated, the fact that you're passing invalid arguments to it is inconsequential.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    Upvoted, but it would be good to see a doc reference for your answer, because AFAIK in SQL, in general, there is no guaranteed short-circuiting order (unlike app languages like Java and C#, which _do_ have such guaranteed rules). – Tim Biegeleisen Dec 19 '18 at 06:48
  • Justin cave here https://stackoverflow.com/questions/8900631/does-oracle-use-short-circuit-evaluation asserts that the optimizer looks for truths in any order so that it can short a set of predicates, no reference to oracle docs though – Caius Jard Dec 19 '18 at 06:52
  • I have added 1=1 for sake of OR condition but that part depends upon flow which may or may not be true. But how can I make sure I get an error when spaces gets passed to TO_DATE ? – Shubham Khandare Dec 19 '18 at 06:59
  • adding `1=1` has influenced the result. If an extra condition is added like this: `select * from dual where to_date(' ','YYYYMMDD') = '19960512' or to_date('19960512','YYYYMMDD') = '19960512'` then you will get the error you expected. So the solution to: **how to handle to get an error?** is don't use `1=1` – Paul Maxwell Dec 19 '18 at 07:08
3

The other answer didn't deal with the part of your question where you still wanted to get an error

I recommend you do the date parsing in isolation. If this is a stored procedure, don't put the query as you have done here, create a date variable, use to_date to assign a date value to it (it will crash if spaces are given by the user) then use the date variable in the query. In this way if bad data is given oracle still has a chance to choke on it.

Alternatively make your store procedure take a date type parameter rather than a string, to force the calling program to provide something reasonable and consistently a date

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • These queries with or condition are in data layer and string from database getting fetched and passed to TO_DATE in the query. – Shubham Khandare Dec 19 '18 at 07:40
  • 2
    I kinda didn't really understand what you meant but if tou feel there is no way other than to have the query like this, then you'll have to do something else to force the crash; select the to_date aswell as making it in the where clause or put it in a case when that forces order. I still think if you're saying the query is all that there is, that you should parameterise it properly. This date shouldn't be spaces at all. There is another argument that if you expect bad data of a certain type you should handle it rather than crash, to minimise the amount of work the tech support team have to do. – Caius Jard Dec 19 '18 at 07:45
1

You could repeat the conversion within the or branch, which is obviously a bit messy:

select * from dual
where to_date('    ','YYYYMMDD') = '19960512'
or (1 = 1 and to_date('    ','YYYYMMDD') != '19960512');

Error report -
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Or if the only invalid-date scenario you have to worry about is spaces, you could do something else to make that branch evaluate to false in that case:

select * from dual
where to_date('    ','YYYYMMDD') = '19960512'
or (1 = 1 and trim('    ') is not null);

string from database getting fetched and passed to TO_DATE in the query

implies you're storing dates as strings in your database, which is not a good idea; so if you can have spaces in there you could have anything, and will have to deal with other potential errors too, which the is not null approach won't catch.

And neither of those queries will error with an empty string (i.e. null) passed in - but then neither will your first two original queries; they'll all just get no-data-found.

Of course, '19960512' is not a date so you're doing additional implicit conversions here; it would be safer to pass that through to_date() too, or use a date literal if it's really a fixed value:

where to_date('    ','YYYYMMDD') = date '1996-05-12'
Alex Poole
  • 183,384
  • 11
  • 179
  • 318