Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6T3Fuf65700 for ; Sat, 28 Jul 2001 23:15:56 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6T3FNC00200; Sat, 28 Jul 2001 23:15:23 -0400 (EDT) To: "Josh Berkus" cc: "guard" , pgsql-sql@postgresql.org Subject: Re: nullif BUG??? In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Fri, 27 Jul 2001 08:19:41 -0700" Date: Sat, 28 Jul 2001 23:15:23 -0400 Message-ID: <197.996376523@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/373 X-Sequence-Number: 4187 "Josh Berkus" 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