'IS NOT DISTINCT FROM' not work in JOIN

[PavelkoSemen]

Hello everyone!
Has anyone encountered such behavior in Trino: queries using NOT DISTINCT FROM return 0 rows after a JOIN.If in the query we leave only the part of the condition for K3, where both values are equal to NULL, then one row is returned.
If we use K1 and K2, one row is also returned.
However, when all three keys (K1, K2, K3) are specified in the condition, the query returns 0 rows.

CREATE  TABLE ice.test.ys_stage1
(
  k1 int,
  k2 int,
  k3 int,
  a1 int,
  a2 int,
  a3 int
);

CREATE  TABLE ice.test.ys_merge1
(
  k1 int,
  k2 int,
  k3 int,
  a1 int,
  a2 int,
  a3 int
);

INSERT INTO ice.test.ys_stage1 VALUES(0,0, NULL,9,9,9);
INSERT INTO ice.test.ys_merge1 VALUES(0,0, NULL,9,9,9);

# 0 row
SELECT
  count(*)
FROM ice.test.ys_stage1 t
  INNER JOIN ice.test.ys_merge1 AS s
    ON 1=1
    AND s.k1 IS NOT DISTINCT FROM t.k1
    AND s.k2 IS NOT DISTINCT FROM t.k2
    AND s.k3 IS NOT DISTINCT FROM t.k3;    

 # 1 row
SELECT
  count(*)
FROM ice.test.ys_stage1 t
  INNER JOIN ice.test.ys_merge1 AS s
    ON 1=1
    AND ((s.k1 = t.k1) OR (s.k1 IS NULL AND t.k1 IS NULL) )
    AND ((s.k2 = t.k2) OR (s.k2 IS NULL AND t.k2 IS NULL) )
    AND ((s.k3 = t.k3) OR (s.k3 IS NULL AND t.k3 IS NULL) );

[wendigo]

cc @kasiafi

[kasiafi]

The issue is related to dynamic filters. Investigating further.

[kasiafi]

This seems to be related to

• how we represent dynamic filter domains with null values
• how we optimize the resulting tuple domain

When we collect a dynamic filter, and there are only null values, the resulting domain is NONE. In the example above, we collect the values of the ice.test.ys_merge1.k3 column and get a NONE domain.
This NONE domain does not mean that no rows will pass when we apply the dynamic filter on ice.test.ys_stage1. The domain is combined with the "nullable" dynamic filter function, which passes through all null values.

However, when we optimize the tuple domain after collecting all dynamic filters, we don't take into account that NONE is not really NONE. In DynamicFilterService.translateSummaryToTupleDomain(), we propagate the NONE domain onto all columns.
In the example query above it means that we derive NONE domain for ice.test.ys_merge1.k1 and ice.test.ys_merge1.k2 columns. Hence the empty result.

We should avoid NONE-propagation for dynamic filters. Ideally, we should fix the domain representation wrt nulls.

cc @raunaqmorarka

[vmtuan12]

Minor additional information: I did not face this bug in version 455, after upgrading to 468, this occurred

[vmtuan12]

I have discovered that wrap the DISTINCT condition with () and explicitly use = TRUE/= FALSE would solve the problem

SELECT
  count(*)
FROM test_geo.ys_stage1 t
  INNER JOIN test_geo.ys_merge1 AS s
    ON 1=1
    AND (s.k1 IS NOT DISTINCT FROM t.k1) = TRUE
    AND (s.k2 IS NOT DISTINCT FROM t.k2) = TRUE
    AND (s.k3 IS NOT DISTINCT FROM t.k3) = TRUE;

# this returns 1 row

cc @kasiafi @raunaqmorarka

[codluca]

Created pull request: #26702