0

Looking at the below query,

SELECT null IS NOT DISTINCT FROM null AS indf,
  null = null AS eq;
 indf | eq 
------+----
 t    | 

From this we can see the result of the IS NOT DISTINCT FROM is true, and the result of the eq is false. The following then logically follows from that,

-- 1 rows
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON t.a IS NOT DISTINCT FROM g.a;

-- 0 rows
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON t.a = g.a;

Because if the condition returns null the join fails. But, this throws me off, with row-wise comparison

-- returns 1 row.
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON (t) IS NOT DISTINCT FROM (g);

-- also returns one row.
SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON (t) = (g);

Why does row-wise comparison treat nulls different than scalar comparison? And is there a point of IS NOT DISTINCT FROM in row-wise comparison?

0

This behavior is documented under Composite Type Comparison,

The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors (as in Section 9.23.5) or comparing a row constructor to the output of a subquery (as in Section 9.22). In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.

This essentially means that the standard behavior can only be seen in the following syntax (ROW() is the row-constructor):

SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON ROW(t.*) = ROW(g.*);

versus,

SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON ROW(t.*) IS NOT DISTINCT FROM ROW(g.*);

Just using the ROW constructor with the type will not work either ON ROW(t) = ROW(g)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.