Make Schema inference works for CREATE AS SELECT

[ucasfl]

Changelog category (leave one):

• Improvement

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Make Schema inference works for CREATE AS SELECT. Closes #47599.

[pufit]

We have some concerns about why the performance build report failed. I will temporarily revert this pr

[pufit]

@ucasfl I found a query that was broken by this commit. It can be reproduced like this:

cat mock_data.tsv

UserName	Age	Tags
String	Int8	Map(String, UInt64)
user127	20	{'test': 123}
user405	43	{'test': 123}
user902	43	{'test': 123}

CREATE VIEW users AS SELECT * FROM file('mock_data.tsv', TSVWithNamesAndTypes);

CREATE TABLE users_output ENGINE File(TSV, 'output.tsv')
AS
WITH (SELECT groupUniqArrayArray(mapKeys(Tags))
      FROM users) AS unique_tags
SELECT UserName, length(unique_tags)
FROM users;

The last query will result in an error

Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown column: Tags, there are only columns : While processing (SELECT groupUniqArrayArray(mapKeys(Tags)) FROM users) AS unique_tags. (UNKNOWN_IDENTIFIER)

[ucasfl]

@ucasfl I found a query that was broken by this commit. It can be reproduced like this:

cat mock_data.tsv

UserName	Age	Tags
String	Int8	Map(String, UInt64)
user127	20	{'test': 123}
user405	43	{'test': 123}
user902	43	{'test': 123}

CREATE VIEW users AS SELECT * FROM file('mock_data.tsv', TSVWithNamesAndTypes);

CREATE TABLE users_output ENGINE File(TSV, 'output.tsv')
AS
WITH (SELECT groupUniqArrayArray(mapKeys(Tags))
      FROM users) AS unique_tags
SELECT UserName, length(unique_tags)
FROM users;

The last query will result in an error

Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown column: Tags, there are only columns : While processing (SELECT groupUniqArrayArray(mapKeys(Tags)) FROM users) AS unique_tags. (UNKNOWN_IDENTIFIER)

@pufit Although the change makes the query above does not work. However, even without the change, the query also does not work if we first create table users_output, then execute:

INSERT INTO users_output 
WITH (SELECT groupUniqArrayArray(mapKeys(Tags))
      FROM users) AS unique_tags
SELECT UserName, length(unique_tags);
FROM users;

Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown column: Tags, there are only columns : While processing (SELECT groupUniqArrayArray(mapKeys(Tags)) FROM users) AS unique_tags. (UNKNOWN_IDENTIFIER)

So, it's a different issue with insert schema inference.