Recently, I've found a message about weard NVL function behaviour on
TimesTen forum and decided to make a test (funny bug).
[oracle@nodett1 ~]$ ttversion
TimesTen Release 11.2.2.2.0 (32 bit Linux/x86) (tt1122:53392) 2011-12-23T09:21:34Z
Instance admin: oracle
Instance home directory: /u01/app/oracle/product/11.2.2/TimesTen/tt1122
Group owner: oinstall
Daemon home directory: /u01/app/oracle/product/11.2.2/TimesTen/tt1122/info
PL/SQL enabled.
[oracle@nodett1 ~]$
Command> create table test ( id number, name_1 varchar2(20), name_2 varchar2(20));
Command> select nvl(name_1, 123) t1, nvl(name_2, 123) t2 from test;
0 rows found.
Command> insert into test values (1,'test',null);
1 row inserted.
Command> select * from test;
< 1, test, <null> >
1 row found.
Command> select nvl(name_1, 123) t1, nvl(name_2, 123) t2 from test;
2922: Invalid number type value
0 rows found.
The command failed.
Command>
Command> select nvl(name_1, 123) from test;
2922: Invalid number type value
0 rows found.
The command failed.
Command> select nvl(name_2, 123) from test;
< 123 >
1 row found.
Command>
Command> insert into test values (2,null,'test');
1 row inserted.
Command> select * from test;
< 1, test, <null> >
< 2, <null>, test >
2 rows found.
Command>
Command> select nvl(name_1, 123) t1, nvl(name_2, 123) t2 from test;
2922: Invalid number type value
0 rows found.
The command failed.
Command>
Command> select nvl(name_1, 123) from test;
2922: Invalid number type value
0 rows found.
The command failed.
Command> select nvl(name_2, 123) from test;
< 123 >
2922: Invalid number type value
1 row found.
The command failed.
Command>
It looks like TimesTen tries to convert first expression into second’s expression data type. Especially I like the last statement :). Be careful with bugs.
