Materialize view doesn't populate columns after join and migration to 24.3.2.23

[p-pekala]

Describe what's wrong

After migration from 23.8 to 24.3 I noticed that some columns are not populated in the destination table (after materialized view processing). After investigating and narrowing down the issue, I noticed that it applies only when we use join in the materialized view and final column names are the same as in the joined table. It doesn't matter if columns from the joined table are used.

CREATE TABLE IF NOT EXISTS bug_reproduction (
missingId LowCardinality(String),
someId LowCardinality(String),
dateTime DateTime64(3, 'UTC')
) ENGINE MergeTree()
PARTITION BY toYYYYMM(dateTime)
ORDER BY (someId, dateTime);

CREATE MATERIALIZED VIEW IF NOT EXISTS bug_reproduction_mv TO bug_reproduction AS
select
st.missingId,
st.someId,
st.dateTime
from
source_table st
LEFT JOIN joined_table i
using (missingId, some_column1, some_column2, some_column3);

If I remove the left join from materialized view or change the column names in the final table (and use aliast in MV) the missingId is populated correctly. It also worked correctly in version 23.8.

Expected behavior

missingId should be populated

[den-crane]

try

CREATE MATERIALIZED VIEW IF NOT EXISTS bug_reproduction_mv TO bug_reproduction AS
select
st.missingId as missingId,
st.someId as someId,
st.dateTime as dateTime
...

[den-crane]

@p-pekala I cannot reproduce it https://fiddle.clickhouse.com/8fa47363-5efa-47aa-9fbc-f9b2332d8ac4

[p-pekala]

@den-crane Yes the alias seems to help. I also updated your example that reproduces the problem (not sure which modification changed the behavior but I adjusted it to more like my case - with a dictionary and different types).

[den-crane]

I also updated your example that reproduces the problem

You cannot update my example. fiddle generates a different url on each execution. You need to share your own URL.

[p-pekala]

right: https://fiddle.clickhouse.com/a7a28ab1-1675-4da6-a573-d4d83bd40a0d

[den-crane]

related to an allow_experimental_analyzer and the join with a dictionary.

https://fiddle.clickhouse.com/76d74308-b8ba-4df6-b519-3b0de9b120b5

[novikd]

In the QueryAnalyzer::tryResolveIdentifierFromJoin function resolved column is promoted to the common type of columns in the both tables.

ClickHouse/src/Analyzer/Passes/QueryAnalysisPass.cpp

Lines 3622 to 3624 in 92bb69e

	auto left_resolved_column_clone = std::static_pointer_cast<ColumnNode>(left_resolved_column.clone());
	left_resolved_column_clone->setColumnType(using_column_node_it->second->getColumnType());
	resolved_identifier = std::move(left_resolved_column_clone);

Currently when this promotion takes place, node_to_projection_name hash table is not updated, so projection name is not properly calculated and the fall back is used:

ClickHouse/src/Analyzer/Passes/QueryAnalysisPass.cpp

Line 6370 in 92bb69e

result_projection_names.push_back(unresolved_identifier.getFullName());

I'm working on the fix.