bug: Inconsistent query results: multi-table reconstructed query produces duplicate rows due to WHERE predicate pushdown causing RIGHT JOIN anti-join to emit extra rows

[Annie191]

Search before asking

• I had searched in the issues and found no similar issues.

Version

• Databend version: v1.2.925-patch-11-ebcd374c34
• Build toolchain: rust-1.94.0-nightly-2026-08-26

What's Wrong?

When executing a multi-table reconstructed query (LEFT JOIN UNION ALL RIGHT JOIN), the result is inconsistent with the single-table query. The single-table query returns 1 row, while the multi-table reconstructed query returns 2 rows.

The root cause is that the outer WHERE predicate is pushed down to part_l, causing RIGHT JOIN ... WHERE l.vp_rowid IS NULL to incorrectly believe that the left table has no matching rows, so the same row from part_r passes through the anti-join branch again.

How to Reproduce?

1. Start the Databend query service and connect using a MySQL client.
2. Execute the following SQL script:

DROP DATABASE IF EXISTS repro_databend925_db2_min;
CREATE DATABASE repro_databend925_db2_min;
USE repro_databend925_db2_min;

CREATE TABLE source (
  vp_rowid BIGINT NOT NULL,
  c0varchar VARCHAR NULL
) ENGINE=FUSE;

CREATE TABLE part_l (
  vp_rowid BIGINT NOT NULL,
  c0varchar VARCHAR NULL
) ENGINE=FUSE;

CREATE TABLE part_r (
  vp_rowid BIGINT NOT NULL,
  c0varchar VARCHAR NULL
) ENGINE=MEMORY;

INSERT INTO source VALUES (1, '');
INSERT INTO part_l SELECT * FROM source;
INSERT INTO part_r SELECT * FROM source;

SET enable_query_result_cache = 0;

-- Single-table query
SELECT
  (- source.vp_rowid),
  ROW_NUMBER() OVER (ORDER BY `vp_rowid`, `vp_rowid`),
  (((((source.vp_rowid) * (source.vp_rowid)) - ((+ source.vp_rowid)))
    * (((source.vp_rowid) / (-1401453600)) % (source.vp_rowid))))
FROM source
WHERE (((+ source.vp_rowid)) - ((source.vp_rowid) % (((1208992282) / (41992119)))))
ORDER BY source.c0varchar, source.vp_rowid;

-- Multi-table reconstructed query: LEFT JOIN UNION ALL RIGHT JOIN, preserving the original log structure
SELECT
  (- reconstructed.vp_rowid),
  ROW_NUMBER() OVER (ORDER BY `vp_rowid`, `vp_rowid`),
  (((((reconstructed.vp_rowid) * (reconstructed.vp_rowid)) - ((+ reconstructed.vp_rowid)))
    * (((reconstructed.vp_rowid) / (-1401453600)) % (reconstructed.vp_rowid))))
FROM (
    SELECT l.vp_rowid AS vp_rowid, r.c0varchar AS c0varchar
    FROM part_l l
    LEFT JOIN part_r r ON l.vp_rowid = r.vp_rowid

    UNION ALL

    SELECT r.vp_rowid AS vp_rowid, r.c0varchar AS c0varchar
    FROM part_l l
    RIGHT JOIN part_r r ON l.vp_rowid = r.vp_rowid
    WHERE l.vp_rowid IS NULL
) reconstructed
WHERE (((+ reconstructed.vp_rowid)) - ((reconstructed.vp_rowid) % (((1208992282) / (41992119)))))
ORDER BY reconstructed.c0varchar, reconstructed.vp_rowid;

Expected Result

The multi-table reconstructed query should return the same result as the single-table query, i.e., 1 row:

-1    1    -0.0

Actual Result

• The single-table query returns 1 row:

-1    1    -0.0

• The multi-table reconstructed query returns 2 rows:

-1    1    -0.0
-1    2    -0.0

Additional Context

Single-table query plan:

Sort
└── EvalScalar
    └── Window
        └── Sort
            └── TableScan(source)

Characteristics:

• Scans source directly
• WHERE takes effect on the final result
• Produces only 1 row
• ROW_NUMBER() is computed on the single-table result

Multi-table reconstructed query plan:

Sort
└── EvalScalar
    └── Window
        └── Sort
            └── UnionAll
                ├── RIGHT OUTER JOIN
                │   ├── part_l
                │   └── part_r
                └── LEFT OUTER JOIN
                    ├── part_l
                    └── part_r

The key anomaly is in the second RIGHT JOIN branch:

TableScan(part_l)
└── filters:
    is_true(
      CAST(part_l.vp_rowid AS Int64)
      - CAST(part_l.vp_rowid AS Int64) % 28.7909329367
    )

Databend pushes the outer WHERE predicate down to part_l. In the minimal case, part_l.vp_rowid = 1 is filtered out by this condition, so:

RIGHT JOIN ... WHERE l.vp_rowid IS NULL

incorrectly believes that the left table has no matching rows, allowing the same row from part_r to pass through the anti-join branch again.

Therefore:

• Single table: 1 row
• Reconstructed query: LEFT JOIN produces 1 row; RIGHT JOIN anti-join incorrectly produces 1 row; after UNION ALL, there are 2 rows in total.

Moreover, the Window in both plans is located after UnionAll, so the duplicate row also causes:

ROW_NUMBER(): 1, 2

Are you willing to submit PR?

• Yes I am willing to submit a PR!