[YSQL] Merge scan: cross-type relied-on equality silently loses rows

[jasonyb]

Jira Link: DB-23129

Description

A merge scan whose ordering relies on an equality the executor later fails to bind silently returns wrong results. In the observed case an entire stream's rows disappeared from the output, with no error in any build type and no Sort node to compensate.

Mechanism: when build_index_pathkeys walks the index columns, a column whose EquivalenceClass is pinned to a constant (EC_MUST_BE_REDUNDANT) is skipped as redundant, and the merge sort keys continue past it. Each stream is then sorted by the merge sort keys only while that equality stays bound in the per-stream request. A cross-type equality breaks this: for example a float8 value against a float4 column is rejected at bind time by YbIsScanCompatible (conservative default for the float pair), the condition falls back to recheck, the column silently drops out of the scan bounds, and the streams come back sorted by the skipped column first rather than by the merge sort keys.

This is the scalar-equality sibling of #32841, which is the same bind-time drop for a cross-type SAOP on a stream key column. The fix model is #32732 (commit a096167d371dbdbc21eb1732d2c1a3bd4d4a2c76): a plan-time reliance must be checked against plan-time evaluability, so the pathkeys walk should not rely on (or should plan around) an equality it cannot bind.

Reproduction

CREATE TABLE num_tbl (id int, f8 float8, i2 int2, x text, nu numeric, i4 int, f4 real, i8 int8, PRIMARY KEY (f4 ASC, id ASC));
INSERT INTO num_tbl (i2, i4, i8, f4, f8, nu, id, x) SELECT i % 3, i % 7, i % 11, (1 + (i % 5))::real + 0.5, ((1 + (i % 13)) + 0.5)::float8, (1 + (i % 17)) + 0.5, i, 'x' || i FROM generate_series(1, 10000) i;
ANALYZE num_tbl;
CREATE INDEX NONCONCURRENTLY num_tbl_i2_f4_id_idx ON num_tbl (i2 ASC, f4 ASC, id ASC);
SET yb_enable_base_scans_cost_model = on;
/*+IndexScan(num_tbl num_tbl_i2_f4_id_idx) Set(yb_max_merge_scan_streams 64)*/ SELECT f4, id, i2, x FROM num_tbl WHERE i2 IN (1, 2) AND f4 = '2.5'::float8 ORDER BY id LIMIT 10;

The plan pins i2 as the merge stream key, relies on f4 = 2.5 for the id ordering (the f4 pathkey is skipped as constant), and the float8 value fails to bind against the float4 column. Observed output: only the i2 = 2 stream's rows, every i2 = 1 row silently missing. Control: the same query with '2.5'::real, or with yb_max_merge_scan_streams = 0 (recheck path), returns the correct rows from both streams.

Observed on master at commit 4e48c41 behavior (verified on a branch whose only additional changes do not affect this shape).

Mitigation and fix ordering

The relied-on equality validation under #32735 declares these columns and converts the silent wrong results into a clean error (merge scan equality column "f4" has no usable bound condition). Its yb.orig.merge_scan_cross_type regress cells encode that error state, so this issue's planner fix must land after the #32735 validation and will flip those cells to correct rows, the same red/green handoff as #32841's cells.

Environment

• AlmaLinux 8, kernel 4.18.0-477.27.2.el8_8.x86_64, GCP n2d-standard-4 (4 vCPU AMD EPYC 7B13, 16 GB RAM)
• fastdebug gcc15 build, RF1 single-node yb-ctl cluster

automated · Fable 5

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.