`query_plan_convert_outer_join_to_inner_join` drops outer rows when the filter applies `toDateTime`/`toDate` to a `Date32` column: the pass probes the type default 1900-01-01 while the join fills 1970-01-01

Start with ConvertOuterJoinToInnerJoin and ActionsDAG::isFilterAlwaysFalseForDefaultValueInputs, then inspect DataTypeDate32::getDefault and HashJoin's insertDefault path. Re-run the listed LEFT, RIGHT, and FULL JOIN queries with the optimizer setting on and off; done means non-matched Date32 rows remain and results match the setting-off behavior.

Describe what's wrong

query_plan_convert_outer_join_to_inner_join (on by default) turns a LEFT/RIGHT/FULL JOIN into an INNER JOIN when the WHERE filter on the non-preserved side's columns is judged to reject the default values that the join fills in for non-matched rows. For a Date32 column that judgement is made on the wrong default: the pass evaluates the filter on DataTypeDate32::getDefault() = 1900-01-01, but the hash join fills non-matched rows with insertDefault() = 1970-01-01. Any filter that goes through toDateTime, toDate or toUnixTimestamp of the Date32 column separates the two (toDateTime(toDate32('1900-01-01')) wraps to 2036-02-07 06:28:16), so the pass concludes "always false for defaults", converts the join, and every non-matched outer row silently disappears. Wrong result at pure defaults, both LEFT and RIGHT (and FULL) forms.

Does it reproduce on the most recent release?

Reproduced on master 26.10.1.181 (also 26.10.1.1), 20/20 runs.

How to reproduce

CREATE TABLE kl (d Date32, v Int64) ENGINE = MergeTree ORDER BY d;
CREATE TABLE kr (k UInt32, v Int64) ENGINE = MergeTree ORDER BY k;
INSERT INTO kl VALUES ('2021-01-01', 1);
INSERT INTO kr VALUES (1, 2), (2, 3);   -- no key of kr matches kl

-- the join fills the non-matched left side with 1970-01-01 (not 1900-01-01):
SELECT r.k, l.d, toDateTime(l.d) FROM kr AS r LEFT JOIN kl AS l ON l.v = r.v ORDER BY r.k;
-- 1  1970-01-01  1970-01-01 00:00:00
-- 2  1970-01-01  1970-01-01 00:00:00

-- so a filter that 1970-01-01 passes must keep both rows, yet:
SELECT count() FROM kr AS r LEFT JOIN kl AS l ON l.v = r.v WHERE toDateTime(l.d) < now();
-- 0            <-- expected 2

SELECT count() FROM kl AS l RIGHT JOIN kr AS r ON l.v = r.v WHERE toDateTime(l.d) <= toDateTime('2020-07-25 12:00:00');
-- 0            <-- expected 2

SELECT count() FROM kl AS l RIGHT JOIN kr AS r ON l.v = r.v WHERE toDateTime(l.d) <= toDateTime('2020-07-25 12:00:00')
SETTINGS query_plan_convert_outer_join_to_inner_join = 0;
-- 2            (correct)

-- the same shapes through toDate / toUnixTimestamp are wrong too; a direct comparison of the
-- Date32 column, or a Date column instead of Date32, is fine (1900-01-01 and 1970-01-01 agree):
SELECT count() FROM kl AS l RIGHT JOIN kr AS r ON l.v = r.v WHERE toDate(l.d) <= toDate('2020-07-25');           -- 0, expected 2
SELECT count() FROM kl AS l RIGHT JOIN kr AS r ON l.v = r.v WHERE toUnixTimestamp(l.d) <= 100;                    -- 0, expected 2
SELECT count() FROM kl AS l RIGHT JOIN kr AS r ON l.v = r.v WHERE l.d <= toDate32('2020-07-25');                  -- 2 (correct)

-- why the pass thinks the filter rejects defaults:
SELECT defaultValueOfTypeName('Date32'), toDateTime(toDate32('1900-01-01')), toDateTime(toDate32('1970-01-01'));
-- 1900-01-01   2036-02-07 06:28:16   1970-01-01 00:00:00

EXPLAIN actions = 1 shows Type: inner for the failing queries and Type: left with the setting off.

Expected behavior

The outer rows are kept: 2 for every query above, as with query_plan_convert_outer_join_to_inner_join = 0.

Additional context

ConvertOuterJoinToInnerJoin calls ActionsDAG::isFilterAlwaysFalseForDefaultValueInputs, which builds the probe row from input->result_type->getDefault(). DataTypeDate32::getDefault returns -getDayNumOffsetEpoch() (1900-01-01, matching what INSERT with a missing column, ALTER TABLE ... ADD COLUMN and toDate32OrZero produce), while ColumnInt32::insertDefault — what HashJoin uses to fill non-matched rows, and also arrayResize padding and assumeNotNull(NULL) — yields 0 = 1970-01-01. Either the pass should probe with the value the join actually fills, or the join should fill with the type default; today the two disagree and the optimizer's conclusion is unsound.

Found by an automatic optimizer-testing framework (differential testing of optimizer settings, query plans, and equivalent rewrites).