pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: john@august.com, Josh Berkus <josh@agliodbs.com>
To: Peter Eisentraut <peter_e@gmx.net>
To: john@august.com
Cc: pgsql-sql@postgresql.org
Subject: Re: not null - trivial, unexpected behavior
Date: Sat, 16 Jun 2001 16:52:06 -0700
Message-ID: <web-72932@davinci.ethosmedia.com> (raw)
In-Reply-To: <20010616232607.5720.qmail@web10007.mail.yahoo.com>

John,

> but, as i understand the sql92 standard, both att = null and att !=
> null
> are NOT sql92. 

You are correct.

> perhaps they're just remnants of the Cish nature of
> quel.

Tom seems to indicate that = Null is a result of (possibly misguided)
desire for compatibility with Microsoft ODBC.

 
> seems to me that if att != null is unknown, then att = null
> should be unknown as well.  or at least documented as such.

Yup.  Absolutely.  Any expression which includes a NULL should evaluate
to NULL.  If the expression includes an unknown value, its result must
also remain unknown.
 
> a practical example of where this becomes a problem
> is a query like
> 
>     select * from t where a != :aqual
> 
> to do this properly for a null 'aqual' in sql92 i need to change '='
> to 
> 'is not null', which is considerably more complex than simple
> variable
> substitution.

If this is a concern, then set the column to NOT NULL and force a value
to be inserted ... 0 for numerical columns, '' for VARCHAR, and
'1900-01-01' for dates.

Hmmm ... what is a good "no date" value for dates?  Depends on the
application, I suppose.  

> i guess a summary question would be : is a predicate like
> "att = null" even predicatable and, if not, 
> why should any unpredicatble query
> be accepted by the database engine?

It *is* predictable in that "att = null" evaluates to False, regardless
of the value of att.  att cannot be equal to Null since Null is the
"unknown value".

If you wish to test two values as equivalent because they both equal
NULL, then you can do like the following:

IF (COALESCE(att, 'NULL')) = (COALESCE(pbs, 'NULL')) 

or:

IF (att = pbs) OR (att IS NULL and pbs IS NULL) 

All of the troubles you mention above are the reason why some DB
theorists are opposed to the use of NULLs at all.  However, NULLs are
part of the SQL standard, as is all null tests = False and all null
computations = Null.  We gain little by flaunting the standard.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



view thread (10+ messages)  latest in thread

Message-ID: <web-72932@davinci.ethosmedia.com>
Permalink:  ../web-72932@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-72932@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, peter_e@gmx.net, john@august.com
  Subject: Re: not null - trivial, unexpected behavior
  In-Reply-To: <web-72932@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox