Existentialist SQL


  • Discourse touched me in a no-no place

    I am going tu type this on a mobile keyboard because it is just so glorious it cannot wait till I get home.

    ([object Object] is a varchar initialized null and has an int selected into it, meaning the only possible values are a stringy int and null)

    IF (ISNULL([object Object], '') <> '')



  • Someone has been burned and is now overcautious. Consider if it were written this way (reversal of truth):

    IF (ISNULL([object Object],'') = '')

    In that variation, the ISNULL is required for an ANSI NULL compliant SQL. Comparison predicates where either side is NULL are treated as undefined, so that if [object Object] is NULL then [object Object] = '' should be undefined; neither false nor true.

    Since the target of the IF is executed only if the logical result is true, then the target would be executed only if [object Object] contained exactly '' (empty string).

    That is, [object Object] of NULL or '123' wuld produce exactly the same behavior, which is a bit counter intuitive.

    This is further complicated in some databases like Access and, in certain modes, SQL Server and Oracle, which treat the empty string '' as equivalent to NULL. (Which it really should not be.)

    If [object Object] is truly either NULL or an int string, then I would test it this way (for databases that support IS NULL and IS NOT NULL) :

    IF ([object Object] IS NOT NULL)



  • @CoyneTheDup said:

    some databases like Access

    Ahem..


  • Winner of the 2016 Presidential Election

    @CoyneTheDup said:

    which treat the empty string '' as equivalent to NULL. (Which it really should not be.)

    :popcorn:



  • @swayde said:

    Ahem..

    Well, yes, but they call it a "database"...and one can do useful things with it.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.