Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Some Days I Feel Like I Know Nothing About Oracle 15 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • ... I didn't design it.

    The new query looks like:
    SELECT column_list FROM table_name WHERE pk_column = some_var AND ( column_y IS NULL OR column_x != column_y);

  • Surely that behaviour is the same in PostgreSQL too? Any attempt to compare a NULL value to any other value (including another NULL) will result in NULL rather than true or false.
  • I seem to recall hitting this once, and from what I could tell it reflects a sort of purist view by Oracle.

    You have something, and you have nothing. And you can't compare something with nothing. Thus, they can't be "different" because there's nothing there to be different with.

    Takes a bit of mind reversal...
  • If you know that the possibly null column can not be some value (say a single space) or you want to assume null is the same as some value, then you can use:

    where col1 != NVL(col2, ' ')
    • Oh, and it's not just Oracle (and PostgreSQL) that treats NULL's this way.

    • And if you want to be SQL-92 compliant, you can use COALESCE, which works the same as NVL (but sounds better, even though it takes longer to type).

    • Is that right?

      I am thinking right now that the correct way to write this test would be WHERE NVL(col1 != col2, FALSE). So it would return the resulting boolean value from all comparisons of existent values, but for comparisons involving NULL it would default the resulting NULL to FALSE. Basically you’re saying “compare the columns for inequality, and if they can’t be compared, then treat them as unequal.”

      By doing it this way you avoid semi-predicate problems.

      • I see what you're saying...except that the NVL() should return TRUE if the '!=' result is null (at least that's what the OP seems to want). And I know he said that only one of the columns could be null, but I'm so used to potentially both columns being null, and wanting to treat that case as the columns being equal, that I'm used to wrapping the columns in NVL() individually...and not thinking about brilliant shortcuts :-)
  • MySQL does the same. I bet SQLite does too.
    • Is that a 5.0 thing or has it always done it this way? I may be misremembering but I thought that MySQL 4 treated NULLs as either empty string or 0 depending on the equality tests.

  • ... but I really thought that I had seen a database treat NULLs like Perl treats undefs. I guess it's always possible that my memory is playing tricks on me. It certainly wouldn't be the first time. I did a lot of stupid stuff in my youth that almost certainly destroyed vast amounts of brain cells.

    The absence of a value shouldn't prevent you from checking [in]equality. But maybe I'm the only one that thinks that way. :)

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.