Null comparisons and the transform_null_equals run-time parameter

Поиск
Список
Период
Сортировка
От Ken Winter
Тема Null comparisons and the transform_null_equals run-time parameter
Дата
Msg-id FE0A345408BB42D2B659B9DD3235E1FA@KenIBM
обсуждение исходный текст
Ответы Re: Null comparisons and the transform_null_equals run-time parameter  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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();

В списке pgsql-general по дате отправления:

Предыдущее
От: DM
Дата:
Сообщение: Re: Move data from DB2 to Postgres any software/solutions/approach?
Следующее
От: Eliot Gable
Дата:
Сообщение: Re: cannot assign non-composite value to a row variable