Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NVL in view: discrepancy between 10G and 11G

user12142460Jan 17 2012 — edited Jan 17 2012
Hi,

I've discovered some discrepancy between 10G and 11G in a way how view deals with NVL-based column in a where clause.
Here are two examples:

*10G:*

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production

SQL> create table test_nvl (null_column number);

Table created.

SQL> insert into test_nvl values (null);

1 row created.

SQL> commit;

Commit complete.

SQL> create view view_nvl as select nvl(null_column,0) null_column from test_nvl;

View created.

SQL> select * from view_nvl where null_column is not null and null_column = 0;

NULL_COLUMN
- - - - - - - - - - -
0

*11G*

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> create table test_nvl (null_column number);

Table created.

SQL> insert into test_nvl values (null);

1 row created.

SQL> commit;

Commit complete.

SQL> create view view_nvl as select nvl(null_column,0) null_column from test_nvl;

View created.

SQL> select * from view_nvl where null_column is not null and null_column = 0;

no rows selected

-----

Does anyone know why it behaves differently?
I couldn't find any documentation regarding this.
Or I'm just missing something here.

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 14 2012
Added on Jan 17 2012
4 comments
616 views