Fix JOIN with a Join-engine table whose key type is narrower than the left key - #112390

…ess-safe

Fix round 1, items 2 to 6 of the review fix plan. No source change: every item
here is test coverage, a tag, or a contract wording correction.

Mixed signedness is pinned as a recorded residual. getLeastSupertype promotes a
signed and an unsigned operand to a type wider than both, so for a UInt32 left
key against an Int16 storage key the common type is Int64, the rewrite declines
and the refusal stands. Measured: UInt32/Int16 and Int64/UInt32 both give Int64,
Int32/UInt16 gives Int32, and UInt64/Int16 raises NO_COMMON_TYPE before the
branch. The changelog entry now states the same-signedness restriction instead of
promising the declined pair.

The LowCardinality-left by Nullable-storage-key quadrant was untested, and three
wrapper-matrix rows asserted results without asserting that the direct lookup
survived, so a silent degradation to a generic hash join would have passed. Both
are covered now, each against an ENGINE = Memory oracle and each with a
FilledJoin assertion.

Null-safe equals reaches the same conversion, and RIGHT and FULL USING joins
materialise the right key from the left key column whose type the rewrite
changes. Both are measured correct against an oracle and both keep the direct
lookup, so they are pinned rather than left to inference.

Both tests carry the memory-engine tag. With echo the statement text is the
output, so the stress job's --replace-log-memory-with-mergetree arm would rewrite
21 lines in one test and 3 in the other and diverge from the reference.

…in the remaining shapes

The first clause of canNarrowLeftKeyToStorageKey declines a nullability-only
mismatch in one direction only, and that is deliberate: when the LEFT key is the
nullable one the pre-existing right-side skip already leaves the storage key
un-cast, so that pair has a working plan today and rewriting it changed the plan
of every such existing join. A nullable STORAGE key is the opposite case, since
that skip does not fire for it, the storage key still gets a _CAST and is still
refused, so narrowing the left key is what makes the query work at all. The
clause was correct but undocumented and untested, which read as a defect. It is
now stated in the comment and pinned in both directions at once.

Three more shapes are pinned rather than changed, because all three behave the
same as they do on master:

- a wide integer left key (UInt128 against a UInt64 storage key) stays refused,
  because the narrowing is restricted to native integer widths;
- null-safe equals against a nullable storage key stays refused, because the
  null-safe rewrite wraps both keys in tuple() and a tuple expression is not a
  mapped storage column;
- the mirrored nullability pair is now covered by an oracle comparison and a
  FilledJoin assertion for both a plain and a LowCardinality left key.

Also renumbers the test sections so they ascend.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

…ction

The stress job passes randomized settings to every stateless test as
--client-option, so they are in force for CREATE TABLE as well as for the
queries. Two of those settings break these tests.

join_use_nulls is set for one thread in three. registerStorageJoin seeds the
Join engine table's use_nulls from the session setting, and getJoinLocked then
refuses any join whose forceNullableRight disagrees with it, so 04652 aborted
on its very first query with INCOMPATIBLE_TYPE_OF_JOIN. That is worse than a
plain failure: the serverError pins further down assert the narrowing decline,
and under the injection they would have kept passing while actually observing
the settings-mismatch refusal. Independently, the setting makes an unmatched
LEFT join row read NULL instead of 0, which moves 24 reference lines in 04652
and 3 in 04653.

join_algorithm is overridden for odd threads to one of five algorithms.
04653 asserts that a type-matched key-value join still uses
DirectKeyValueJoin, but tryDirectJoin returns nothing unless the direct
algorithm is enabled, so the assertion silently flipped to 0 while the results
stayed correct. 04652 needs no such pin: a Join engine table is attached and
returned before any join_algorithm gate is consulted, in both
trySetStorageInTableJoin and chooseJoinAlgorithm.

Both pins are file-level SET statements because the CREATE TABLE statements
are where use_nulls is frozen into the table. Removing either one reddens the
matching test under the matching injection.

Also close two coverage gaps in 04652. The Join engine accepts SEMI and ANTI
as well, and a SEMI LEFT join reaches the same conversion, so it gets an
oracle-backed carrier and a plan assertion; master refuses that shape today.
And the projected USING key's declared type is now asserted rather than only
its values, since the rewrite changes the type of the column it is
materialised from.

…abase

The stress job runs some of its workers with a single shared database for
every test (stress.py appends --database=test_N). In that mode
clickhouse-test neither creates nor drops a per-test database, so the 51
tables these two tests created were left behind and a second run failed
with TABLE_ALREADY_EXISTS. Two of the names also collide with tables owned
by other tests: 00830_join_overwrite creates kv, as a Join-engine table,
and 03394_distributed_broadcast_join creates big.

Each DROP pins ignore_drop_queries_probability to 0, because the same job
injects 0.2 for every non-upgrade thread and InterpreterDropQuery skips a
DROP of a table that stores data on disk when that roll fires, reporting
success. Without the pin the prologue passes its first run while cleaning
up nothing.

Also assert that the narrowing rewrite is visible in the plan for a shape
where it does fire, so the three assertions that it does not fire have a
positive counterpart.

…disarmed by a setting

The three plan pins that assert the narrowing rewrite does NOT fire (nullable-left,
the RIGHT-join mirror, and the key-value entity) used EXPLAIN PLAN description = 1.
That spelling observes the join's action text only indirectly: InterpreterExplainQuery
forces query_plan_options.actions = true, but only when explain_query_plan_default is
PRETTY. Under explain_query_plan_default = 'legacy' the forcing is skipped, actions
falls back to its declared default false, and the pins report 0 because nothing is
printed at all rather than because the rewrite declined. They would have stayed green
even if the rewrite had started firing on any of those three paths.

The mode is not exotic: 489 stateless tests set it to legacy, so a server default, a
future default flip, or a client option would quietly disarm all three assertions.

actions = 1 sets the flag the plan printer actually reads, so it cannot be turned off
from outside the test. Measured on the positive control, where the rewrite does fire:
actions = 1 reports 1 under both PRETTY and legacy, while description = 1 reports 1
under PRETTY and 0 under legacy. The four pins keep their values (1, 0, 0, 0) and only
the three echoed statement lines move in the reference files.

…n pin setting-independent

The narrowing rewrite lives only in the analyzer's plan path: every JoinStepLogical
construction site is under src/Planner or Optimizations/optimizeJoin.cpp. The old
analyzer builds FilledJoinStep directly and types StorageJoin keys through
TableJoin::inferJoinKeyCommonType with allow_right = !isSpecialStorage(), which is
false for a Join engine, so it refuses this shape with TYPE_MISMATCH ("Can't change
type for right table"). Both new tests carried no analyzer pin, so they failed
deterministically wherever the old analyzer is selected: the dedicated old-analyzer
stateless job, and the stress job's compatibility randomization (the analyzer default
flipped in 24.3, so any drawn version below that turns it off). Pin it with a session
SET rather than a no-old-analyzer tag: the SET also defends against the compatibility
route, because applyCompatibilitySetting skips a setting the session already changed,
and it keeps the tests running in every lane instead of silently dropping them.

The DirectKeyValueJoin assertion in the key-value test read EXPLAIN PLAN with
description = 1. That string is the join algorithm name, reached only through
describeJoinActions from FilledJoinStep::describeActions, which runs under
options.actions; under the legacy explain default it is absent, so this positive pin
returned 0 and the test failed. Read it with actions = 1 instead, which is
setting-independent. The FilledJoin pins are the step name, printed unconditionally,
and are left as they are.

Also record why the narrowed left key is Nullable while the storage key is not: the
conversion is accurateCastOrNull, the resulting NULL marks a left value outside the
storage key domain and never matches, and the join tolerates the difference up to
typesEqualUpToNullability.

No executable change to src/. The Build ID moves because this is a Debug build and the
DWARF line tables shift by the inserted comment lines; the objects are byte-identical
after --strip-debug.

…llable

`TableJoin::getRequiredRightKeys` promotes a required right key to Nullable when
the paired left key is Nullable, so that an unmatched-left row fills a genuine
NULL which `firstNonDefault` prefers over the storage default. The planner
registered every corrected right key as a candidate for that promotion,
including a key corrected by a plain cast to a non-Nullable type.

The narrowing rewrite on this branch casts the left key with
`accurateCastOrNull`, which is Nullable even when neither the left column nor
the storage key is. A LEFT/FULL USING join therefore reached that combination:
the join emitted `__table2.k` as `Nullable(UInt32)` while the post-join
correction had been typed against the storage key and declared
`_CAST(__table2.k, 'UInt64') -> UInt64`, so executing it raised

    Unexpected return type from _CAST. Expected UInt64. Got Nullable(UInt64)

Register a corrected right key only when its corrected type is itself Nullable.
A genuinely Nullable left key always makes the common type Nullable, so the
promotion still fires for the case it was written for.

Caught by section 13 of 04652_storage_join_narrow_key_type after merging master.

The block also described what widening the right key would do and named the
error code that produces. That is a rejected design, not an invariant holding
at this line. What a reader needs here is that the hash table is keyed on the
declared storage key type and cannot be rebuilt at query time, and that the
`Nullable` the conversion introduces is safe because an out-of-domain NULL
never matches.