[Bug]Filter pushdown `!(col <=> value)` is not correct when col has null value

[xieshuaihu]

Search before asking

• I searched in the issues and found nothing similar.

Paimon version

Latest version, 1.4-SNAPSHOT

Compute Engine

Spark

Minimal reproduce step

Step 1: prepare data

CREATE TABLE T (a INT, b INT);
INSERT INTO T VALUES (1, null);

Step 2: this result is true

SELECT !(b <=> 1) FROM T;

Step 3: but this return empty rows

SELECT a, b FROM T WHERE !(b <=> 1);

What doesn't meet your expectations?

After filter pushdown, the result is not correct.

Spark supports Equal (i.e. =) and EqualNullSafe (i.e. <=>). After pushdown, paimon treat both as Equal and simplify Not Equal (two predicate expressions) as NotEqual (one predicate expression).

This table shows the differences between spark and paimon when col is null:

	WHERE col = 1	WHERE col <=> 1	WHERE !(col = 1)	WHERE !(col <=> 1)
spark	false	false	false	true
paimon	false	false	false	false

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[venusMask]

This is because Paimon's statistical information led to the correct data file being wrongly filtered! The problem occurs at the LeafPredicate node. Consider the following scenario:

create table t(id int, age int name int primary key (id) not enforced);
insert into t select 1 as id, 2 as age, cast(null as int) as name union all select 2,2,2;
select * from t where !(b <=> 2)

Statistical information of the Paimon data file:

minValue: 1, 1, 2
maxValue: 2, 2, 2 

According to the current logic, this data file will be filtered out. However, in reality, it should not be filtered.

  // LeafPredicate.test()
  public boolean test(DataType type, long rowCount, Object min, Object max, Long nullCount, Object literal) {
        // type: int, rowCount: 2, min:2, max:2, nullCount:1, literal: 2
        return CompareUtils.compareLiteral(type, literal, min) != 0 || CompareUtils.compareLiteral(type, literal, max) != 0;
    })