Null comparisons and the transform_null_equals run-time parameter - Mailing list pgsql-general

When the run-time parameter transform_null_equals is on, shouldn’t two variables with NULL values evaluate as equal?  They don’t seem to. 

At the bottom of this message is a little test function.  It tries all comparisons of NULL-valued variables and NULL constants, both before and after turning transform_null_equals on.  Here’s what it returns:

transform_null_equals OFF: NULL = NULL -> Unknown

transform_null_equals OFF: v1 = NULL -> Unknown

transform_null_equals OFF: NULL = v2 -> Unknown

transform_null_equals OFF: v1 = v2 -> Unknown

transform_null_equals ON: NULL = NULL -> True

transform_null_equals ON: v1 = NULL -> True

transform_null_equals ON: NULL = v2 -> True

transform_null_equals ON: v1 = v2 -> Unknown

My problem is in the last line: Comparing two NULL variables produces an unknown result.  I need it to evaluate as True, like the preceding three comparisons.

Any suggestions?

~ TIA

~ Ken

CREATE OR REPLACE FUNCTION test() RETURNS varchar AS

$BODY$

DECLARE

      v1 VARCHAR;

      v2 VARCHAR;

      s VARCHAR := '';

BEGIN

      v1 := Null;

      v2 := Null;

      IF NULL = NULL THEN

            s := s || 'transform_null_equals OFF: NULL = NULL -> True ';

      ELSIF NOT (NULL = NULL) THEN

            s := s || 'transform_null_equals OFF: NULL = NULL -> False ';

      ELSE

            s := s || 'transform_null_equals OFF: NULL = NULL -> Unknown ';

      END IF;

      s := s || chr(10);

      IF v1 = NULL THEN

            s := s || 'transform_null_equals OFF: v1 = NULL -> True ';

      ELSIF NOT (v1 = NULL) THEN

            s := s || 'transform_null_equals OFF: v1 = NULL -> False ';

      ELSE

            s := s || 'transform_null_equals OFF: v1 = NULL -> Unknown ';

      END IF;

      s := s || chr(10);

      IF NULL = v2 THEN

            s := s || 'transform_null_equals OFF: NULL = v2 -> True ';

      ELSIF NOT (NULL = v2) THEN

            s := s || 'transform_null_equals OFF: NULL = v2 -> False ';

      ELSE

            s := s || 'transform_null_equals OFF: NULL = v2 -> Unknown ';

      END IF;

      s := s || chr(10);

      IF v1 = v2 THEN

            s := s || 'transform_null_equals OFF: v1 = v2 - > True ';

      ELSIF NOT v1 = v2 THEN

            s := s || 'transform_null_equals OFF: v1 = v2 -> False ';

      ELSE

            s := s || 'transform_null_equals OFF: v1 = v2 -> Unknown ';

      END IF;

      s := s || chr(10);

      SET LOCAL transform_null_equals TO ON;

      IF NULL = NULL THEN

            s := s || 'transform_null_equals ON: NULL = NULL -> True ';

      ELSIF NOT (NULL = NULL) THEN

            s := s || 'transform_null_equals ON: NULL = NULL -> False ';

      ELSE

            s := s || 'transform_null_equals ON: NULL = NULL -> Unknown ';

      END IF;

      s := s || chr(10);

      IF v1 = NULL THEN

            s := s || 'transform_null_equals ON: v1 = NULL -> True ';

      ELSIF NOT (v1 = NULL) THEN

            s := s || 'transform_null_equals ON: v1 = NULL -> False ';

      ELSE

            s := s || 'transform_null_equals ON: v1 = NULL -> Unknown ';

      END IF;

      s := s || chr(10);

      IF NULL = v2 THEN

            s := s || 'transform_null_equals ON: NULL = v2 -> True ';

      ELSIF NOT (NULL = v2) THEN

            s := s || 'transform_null_equals ON: NULL = v2 -> False ';

      ELSE

            s := s || 'transform_null_equals ON: NULL = v2 -> Unknown ';

      END IF;

      s := s || chr(10);

      IF v1 = v2 THEN

            s := s || 'transform_null_equals ON: v1 = v2 -> True ';

      ELSIF NOT v1 = v2 THEN

            s := s || 'transform_null_equals ON: v1 = v2 -> False ';

      ELSE

            s := s || 'transform_null_equals ON: v1 = v2 -> Unknown ';

      END IF;

      RETURN s;  

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

SELECT test();