Select from hybrid table returns not all rows

[alsugiliazova]

Describe the bug

CREATE TABLE left_table_3939d9ea_f08b_11f0_b05d_de7b9eea3490
(
    id Int32,
    value Int32,
    date_col Date,
    year_month ALIAS toYYYYMM(date_col),
    is_recent ALIAS date_col >= '2025-01-15'
)
ENGINE = MergeTree()
ORDER BY (date_col, id)
PARTITION BY toYYYYMM(date_col);

CREATE TABLE right_table_3939da62_f08b_11f0_b05d_de7b9eea3490
(
    id Int32,
    value Int32,
    date_col Date,
    year_month ALIAS toYYYYMM(date_col),
    is_recent ALIAS date_col >= '2025-01-15'
)
ENGINE = MergeTree()
ORDER BY (date_col, id)
PARTITION BY toYYYYMM(date_col);

INSERT INTO left_table_3939d9ea_f08b_11f0_b05d_de7b9eea3490 (id, value, date_col) VALUES
                                        (1, 10, '2025-01-15'),
                                        (2, 20, '2025-01-20'),
                                        (3, 30, '2025-02-01');

INSERT INTO right_table_3939da62_f08b_11f0_b05d_de7b9eea3490 (id, value, date_col) VALUES
                                        (4, 40, '2025-01-10'),
                                        (5, 50, '2025-01-12'),
                                        (6, 60, '2025-02-05');


SET allow_experimental_hybrid_table = 1;
CREATE TABLE default.hybrid_table_394f9280_f08b_11f0_b05d_de7b9eea3490

                (
                    id Int32,
                    value Int32,
                    date_col Date,
                    year_month UInt32,
                    is_recent UInt8,
                )

ENGINE = Hybrid(remote('localhost', currentDatabase(), left_table_3939d9ea_f08b_11f0_b05d_de7b9eea3490), date_col >= '2025-01-15', remote('localhost', currentDatabase(), right_table_3939da62_f08b_11f0_b05d_de7b9eea3490), date_col < '2025-01-15');

Select produces wrong results, I expect 6 rows:

SELECT *
FROM hybrid_table_394f9280_f08b_11f0_b05d_de7b9eea3490

Query id: 27204307-df72-40e7-9d0c-a26f38c1c38e

   ┌─id─┬─value─┬───date_col─┬─year_month─┬─is_recent─┐
1. │  1 │    10 │ 2025-01-15 │     202501 │         1 │
2. │  2 │    20 │ 2025-01-20 │     202501 │         1 │
3. │  4 │    40 │ 2025-01-10 │     202501 │         0 │
4. │  5 │    50 │ 2025-01-12 │     202501 │         0 │
5. │  3 │    30 │ 2025-02-01 │     202502 │         1 │
   └────┴───────┴────────────┴────────────┴───────────┘

5 rows in set. Elapsed: 0.010 sec. 

Order by breaks alias columns from the right side:

SELECT *
FROM hybrid_table_394f9280_f08b_11f0_b05d_de7b9eea3490
ORDER BY id ASC

   ┌─id─┬─value─┬───date_col─┬─year_month─┬─is_recent─┐
1. │  1 │    10 │ 2025-01-15 │     202501 │         1 │
2. │  2 │    20 │ 2025-01-20 │     202501 │         1 │
3. │  3 │    30 │ 2025-02-01 │     202502 │         1 │
4. │  4 │    40 │ 2025-01-10 │          0 │         5 │
5. │  5 │    50 │ 2025-01-12 │          0 │         5 │
   └────┴───────┴────────────┴────────────┴───────────┘

5 rows in set. Elapsed: 0.013 sec. 

[filimonov]

Order by breaks alias columns from the right side:

that part is fixed by 3da7904

[filimonov]

Select produces wrong results, I expect 6 rows:

please recheck. You have a condition date_col < '2025-01-15' attached to the right table. Hybrid generates that subquery:

SELECT __table1.id AS id, __table1.value AS value, __table1.date_col AS date_col, __table1.year_month AS year_month, __table1.is_recent AS is_recent FROM remote('localhost', 'default', 'right_table_3939da62_f08b_11f0_b05d_de7b9eea3490') AS __table1 WHERE __table1.date_col < '2025-01-15' (stage: Complete)

right table have one row from the february, and it's filtered out,

 (6, 60, '2025-02-05');

so that subquery returns 2 row. Together with 3 rows from the first subquery it gives 5.