1

This query returns rows where colA is null: 

select * from viewA where colA is null;

But this query does not - 

select * from viewA where nvl(colA, 'X') = 'X';

Any idea why the second query is not returning any results?

Marcus Leon
  • 55,199
  • 118
  • 297
  • 429
  • What datatype is colA? – Frank Schmitt Oct 09 '13 at 20:30
  • Are you absolutely positive that `'X'` == `'X'`, i.e. have you used another character that looks like an X and what is the datatype of `cola`? NCHAR for instance? – Ben Oct 09 '13 at 20:31
  • I just tried your test on a table column that has some null values, and checked the count where `IS NULL` and also where `nvl(colA, 'X') = 'X'`, and I got the same result. Something else is affecting your result set. – WoMo Oct 09 '13 at 20:38
  • could it be connected to the fact this is view as opposed to a table? – Marcus Leon Oct 09 '13 at 20:41
  • @Marcus Shouldn't make any difference. What is the output of select colA, dump(colA), dump(nvl(colA, 'X')), dump('X') from viewA where colA is null; – Frank Schmitt Oct 09 '13 at 20:46
  • No it won't make a difference, what you're saying is happening is probably impossible... it'd be interesting to see if you can replicate this anywhere and then provide code that others can use to test it. – Ben Oct 09 '13 at 20:46
  • Please include some sample data. Ideally a fiddle: http://sqlfiddle.com/#!4/87690/1 – Roger Oct 09 '13 at 21:04
  • 1
    I'm not sure I'd dismiss the view as a factor so quickly, there have seem been some bugs in this area. What version are you using, does adding a `no_push_pred` hint make any difference, and can you show the view code? Or ideally recreate the problem with a similarly-structured view that doesn't rely on your own tables. Or it might not be the view of course, but I don't have any other ideas *8-) – Alex Poole Oct 09 '13 at 22:40

1 Answers1

-3

select * from viewA where nvl(colA, 'null') = 'null';

Arun Narang
  • 21
  • 1
  • 4