pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: guard <guard@ficnet.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: nullif BUG???
Date: Sat, 28 Jul 2001 23:15:23 -0400
Message-ID: <197.996376523@sss.pgh.pa.us> (raw)
In-Reply-To: <web-92561@davinci.ethosmedia.com>
References: <web-92561@davinci.ethosmedia.com>

"Josh Berkus" <josh@agliodbs.com> writes:
> Er... what were you expecting, exactly?

AFAICT, the quoted behavior is correct per the defined behavior of
nullif(), cf 
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-conditional.html

	NULLIF(value1, value2)

	The NULLIF function returns NULL if and only if value1 and
	value2 are equal. Otherwise it returns value1.

> Except for IS NULL (and COALESCE, which uses IS NULL) any operation
> involving a NULL is also NULL.

Well, that's not quite the correct reasoning.

NULLIF and COALESCE are both shorthands for CASE expressions, and hence
are capable of returning non-NULL for a NULL input.  It all depends on
how the CASE tests are phrased.  NULLIF is essentially
	CASE WHEN value1 = value2 THEN NULL ELSE value1 END
In the quoted example, "NULL = 5" will yield NULL, which is interpreted
as a FALSE case test, so you get the ELSE case, ie value1, ie NULL.

			regards, tom lane



view thread (3+ messages)

Message-ID: <197.996376523@sss.pgh.pa.us>
Permalink:  ../197.996376523@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/197.996376523@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, josh@agliodbs.com, guard@ficnet.net
  Subject: Re: nullif BUG???
  In-Reply-To: <197.996376523@sss.pgh.pa.us>

* 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