Wednesday, May 05, 2010

Not something new...

But something I see people learning over and over and over again.

I was reminded of a recently asked asktom question - regarding an "intermittent" 'Oracle' bug in a java application ('Oracle' used for sarcasm on my part).

I was reading the current Steven Feuerstein's Blog entry (read that link before going on). It was exactly the same problem found by the java developers on asktom - which they were certain would be an 'Oracle bug' (hint: it wasn't, it was clearly and demonstrably in their code).

Funny, I see the pattern so often that I saw the bug in both bits of code almost immediately. It jumped out and hit my in the face.

SPOILER ALERT: don't read past here if you want to test your ability to find the bug, read Steven's article first.




Maybe I'll put in a request for the to_date function to be overloaded to accept a date as input and just return that date as output.

edit: added after *thinking* about what I just said...

That of course would never work. People are expecting the date format to be applied to the string, so just returning the date could of course NOT be the right thing to do. I guess the overload would have to turn:

to_date( DATE, 'fmt' )

into

to_date( to_char(date,'fmt'), 'fmt' )

instead of

to_date( to_char(date, IMPLICIT_FORMAT ), 'fmt' )

as it does now, but it would be a HUGE change to existing code that 'relies' the way it currently works...

The to_date function takes a string as input and since the date can be converted to a string - it is. I've seen MANY people use:

to_date( date )

to "truncate" a date (horrible idea - not only slow, but RISKY) - it would break their code (not that it isn't already broken) so it would probably be questionable...

Important enough to point out to a wide audience though - beware implicit conversions and watch out for to_date( of a date )!
POST A COMMENT

15 Comments:

Anonymous Anonymous said....

Sort of confused.. Should it matter at all. Need to test it out. But from what I understand is that the end result is still DATE. So why should the result differ. Ok, maybe extra work is involved but then why should it alter the outout.

Wed May 05, 11:28:00 PM EDT  

Blogger Thomas Kyte said....

@Anonymous

you are the reason why writing a post like this is so important I guess.


Work through the examples - read what was written by Steven and by me. then it might make sense.

when you realize that:

to_date( SOME_DATE, 'dd-mon-yyyy')

is equivalent to:

to_date(
TO_CHAR(some_date),
'dd-mon-yyyy' )

and that that is really equivalent to:

to_date(
to_char(some_date,'NLS_DATE_FORMAT'),
'dd-mon-yyyy' )

maybe then you can see why it is "bad"

It could easily FAIL..
It could easily return *the wrong answer*...

I demonstrated it failing (twice, in two different ways!)

Steven demonstrated it returning the wrong answer.


That is showing the result differs, the reason is "the above", it is an obvious side effect of IMPLICITLY converting a date to a string..

ops$tkyte%ORA11GR2> select *
2 from nls_session_parameters
3 where parameter = 'NLS_DATE_FORMAT'
4 /

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR

ops$tkyte%ORA11GR2> select to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
2 to_char( to_date(sysdate,'dd-mon-yyyy'), 'dd-mon-yyyy hh24:mi:ss' )
3 from dual
4 /

TO_CHAR(SYSDATE,'DD- TO_CHAR(TO_DATE(SYSD
-------------------- --------------------
05-may-2010 23:41:15 05-may-0010 00:00:00


See how the year gets messed up - because the implicit to_char of sysdate will use RR and just return 10, but the explicit use of YYYY will say "i need four characters".

Wed May 05, 11:42:00 PM EDT  

Anonymous glenm said....

What about an enhancement for a warning in the pl/sql compiler (that can turn on and off) where it will show any implicit conversions? Then it could be up to individual site standards that 'No code gets to production if it has ANY implicit conversions!'

Thu May 06, 03:24:00 AM EDT  

Anonymous Anonymous said....

Thanks Tom. As I had mentioned I did not try it out before posting the comments (guilty as charged). My suspicion was on TO_DATE as well, having read about a similar problem on AskTom.
Having tested it now, I rest my case.

I can claim to have re-learnt something "again" today.

Have a Great Day ahead!!

Thu May 06, 03:25:00 AM EDT  

Anonymous Anonymous said....

Sorry, forgot to add. Without the date_format the same code works fine. Understandable since there is an implicit conversion based in the NLS_DATE_FORMAT setting.

SQL> Select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.00
SQL>
SQL> Select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER VALUE
-------------------- --------------------
NLS_DATE_FORMAT DD-MON-RR

Elapsed: 00:00:00.00
SQL>
SQL> Select sysdate from dual;

SYSDATE
---------
06-MAY-10

Elapsed: 00:00:00.01
SQL>
SQL> DECLARE
2 global_end_date DATE := '06-May-2010';
3 global_beg_date DATE := '01-May-2010';
4 BEGIN
5 IF TRUNC (SYSDATE) > TO_DATE (global_end_date)
6 THEN
7 DBMS_OUTPUT.put_line (TRUNC (SYSDATE));
8 DBMS_OUTPUT.put_line (TO_DATE (global_end_date, 'DD-MON-YYYY'));
9 DBMS_OUTPUT.put_line ('> global_end_date');
10 ELSIF
11 TRUNC (SYSDATE) < TO_DATE (global_beg_date)
THEN DBMS_OUTPUT.put_line ('sysdate < beg_date');
12 13 ELSE DBMS_OUTPUT.put_line ('Neither');
14 END IF;
15 END;
16 /
Neither

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Thu May 06, 03:33:00 AM EDT  

Anonymous Anonymous said....

Hello Tom,

Apparently, we don't have the same behaviour if NLS_DATE_LANGUAGE is not default positioned(i.e. American), PL/SQL compilation shows an error instantly :

SQL> DECLARE
2 global_end_date DATE := '06-MAI-2010';
3 global_beg_date DATE := '01-MAI-2010';
4 BEGIN
5 IF TRUNC (SYSDATE) > TO_DATE (global_end_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=FRENCH')
6 THEN
7 DBMS_OUTPUT.put_line (TRUNC (SYSDATE));
8 DBMS_OUTPUT.put_line (TO_DATE (global_end_date, 'DD-MON-YYYY'));
9 DBMS_OUTPUT.put_line ('> global_end_date');
10 ELSIF TRUNC (SYSDATE) < TO_DATE (global_beg_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE=FRENCH')
11 THEN
12 DBMS_OUTPUT.put_line ('sysdate < beg_date');
13 ELSE
14 DBMS_OUTPUT.put_line ('Neither');
15 END IF;
16 END;
17 /
DECLARE
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 5



But what about this much simpler solution ?

SQL> alter session set nls_date_language='FRENCH';

Session altered.


SQL>
SQL>
SQL> DECLARE
2 global_end_date DATE := '06-MAI-2010';
3 global_beg_date DATE := '01-MAI-2010';
4 BEGIN
5 IF TRUNC (SYSDATE) > global_end_date
6 THEN
7 DBMS_OUTPUT.put_line (TRUNC (SYSDATE));
8 DBMS_OUTPUT.put_line (TO_DATE (global_end_date, 'DD-MON-YYYY'));
9 DBMS_OUTPUT.put_line ('> global_end_date');
10 ELSIF TRUNC (SYSDATE) < global_beg_date
11 THEN
12 DBMS_OUTPUT.put_line ('sysdate < beg_date');
13 ELSE
14 DBMS_OUTPUT.put_line ('Neither');
15 END IF;
16 END;
17 /
Neither

PL/SQL procedure successfully completed.

Thu May 06, 09:04:00 AM EDT  

Blogger Thomas Kyte said....

@All who say "it works when this is set like that"


That is true, that is the true *EVIL* of defaults, implicit conversions and their ilk.


They sometimes work (read the link I provided to the java developers above, their statement was "Method getUserDate has been running fine all these years"

My response was "subroutine getUserDate has had a serious bug all these years, it just waited till now to surface, it was a time bomb waiting to go off"

Thu May 06, 10:19:00 AM EDT  

Blogger Islam said....

Very informative and sense of humor in your answers Mr. Tom, Thanks

Thu May 06, 02:02:00 PM EDT  

Anonymous Sokrates said....

create function to_date(vdate in date, vfmt in varchar2 default null, vnlsparam in varchar2 default null) return date deterministic is begin return vdate; end to_date;

would be a quick fix
happy overloading ;-)

I am absolutely with glenm:
when none of all overloaded functions does fit with your input params, implicit conversion comes into play, correct ?

one should be warned by the compiler if this is the case

Fri May 07, 06:38:00 AM EDT  

Blogger Thomas Kyte said....

@Sokrates

ah, but that to_date function you propose would change everything about the way it "works", thus breaking tons of code. So many people use to_date to *truncate* for some unknown reason.


I talked to the plsql guys about this - and they convinced me "it probably should not happen" (that plsql would generate compiler warnings.

The most basic of reasons why: You would be literally flooded with warnings to such a degree that they would become meaningless.

And I believe they are right - there would be so many implicit conversions in code that the warnings would be ignored (not only these warnings, all warnings) rendering them useless.

code such as:

DBMS_Output.Put_Line(Some_Number);

would generate a warning, there would be so many - you would never see the forest for the trees.

but that also means they've been thinking about the problem - and actually have some neat ideas for the future.

Fri May 07, 07:13:00 AM EDT  

Anonymous Slavik Markovich said....

Another thing that one has to remember is that implicit conversion might be a security issue. If you have a privileged program unit and you can set the date format to something evil then you might be able to perform lateral SQL injection into the program unit without ever passing parameters to it.

Slavik

Fri May 07, 07:16:00 PM EDT  

Blogger Joel Garry said....

Predicable or predictable?

If you said it once or twice, I'd just think typo... but it could fit as an Aristotelian attribute...

word: remiz

Fri May 07, 07:21:00 PM EDT  

Anonymous riski said....

hey mr. Tom, nice blog :)
http://blog.beswandjarum.com/riskiarista/

Mon May 10, 04:55:00 AM EDT  

Anonymous Anonymous said....

I know your not the DBA but Ask Tom is returning this error when a question is clicked...

ORA-01653: unable to extend table ASKTOM.ATE_QUESTION_LOG$ by 1024 in tablespace FLOW_19246

Might be worth a blog post on pro-active monitoring and capacity planning :)

Mon May 10, 05:14:00 AM EDT  

Anonymous Basil said....

I put in a followup over at Feuerstein's blog. Obviously, the TO_DATE() applied to a date value and the original date assignment from a string with no format are both issues.

However, the TRUNC() compared with a date that was created without a time component results in values that are exactly equal. Thus, the greater-than (>) test that is so "obviously" true in the original post really isn't.

Thu May 20, 02:16:00 PM EDT  

POST A COMMENT

<< Home