|
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(); |
