Exception while aliasing expression to column name on distributed table engine.

[UnamedRus]

Describe the bug
When we use alias for expression with same name as table column but different data type, and that column appear in WHERE clause on Distributed table exception happens.

How to reproduce
ClickHouse server version 20.1.3, 20.3.1

CREATE TABLE d_numbers
(
    number UInt32
)
ENGINE = Distributed(cluster, system, numbers, rand())

SELECT '100' AS number FROM system.numbers AS n WHERE n.number = 100 FORMAT Null;

→ Progress: 1.10 billion rows, 8.78 GB (1.37 billion rows/s., 10.94 GB/s.) Cancelling query.
Ok.
Query was cancelled.

On *MergeTree table query works

SELECT '100' AS number FROM d_numbers AS n WHERE n.number = 100 FORMAT Null;

↗ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 20.1.3):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Received from 111.222.222.111.
 DB::Exception: Illegal types of arguments (String, UInt8) of function equals.

On Distributed table query falls

Expected behavior
Both queries works

Additional context
ClickHouse server version 19.13.7.57 everything works.

[KochetovNicolai]

@4ertus2 would you like to investigate this issue? I think you are the most experienced in aliases now.

[UnamedRus]

CREATE TABLE d_one  ( dummy UInt8 ) ENGINE = Distributed(cluster, system, one, rand())

select _shard_num, toUInt8(1) as dummy, o.dummy from d_one as o;
┌─_shard_num─┬─dummy─┬─o.dummy─┐
│          4 │     1 │       0 │
└────────────┴───────┴─────────┘
↘ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 20.1.3):
Code: 10. DB::Exception: Received from localhost:9000. DB::Exception: Not found column dummy in block. There are only columns: _shard_num, toUInt8(1).

1 rows in set. Elapsed: 0.004 sec.

localhost shard return row and throw exception at one time?

select _shard_num, '2' as dummy from d_one as o WHERE toUInt8(dummy);
Works, return all rows

select _shard_num, '2' as dummy from d_one as o WHERE toUInt8(o.dummy);
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown identifier: dummy.

select '2' as dummy from d_one as o WHERE toUInt8(o.dummy);
Works, return 3 rows (looks like except localhost shard)

select _shard_num, toString('2') as dummy from d_one as o WHERE toUInt8(o.dummy);
Works, return 3 rows (except localhost shard)

[4ertus2]

Cannot reproduce on master with #9891
Test added. Take a look at test result. It seems to be OK:

https://github.com/ClickHouse/ClickHouse/pull/9891/files#diff-f8c0f778b2fb25c80f645ac2737f6d78R1

You may use some specific configuration. If so you need add more info here.

[UnamedRus]

Clickhouse server version 20.3.4

CREATE TABLE d_numbers_local (number UInt32) ENGINE = Distributed(test_cluster_two_shards_localhost, system, numbers, rand());

CREATE TABLE d_numbers_remote (number UInt32) ENGINE = Distributed(test_cluster_two_shards, system, numbers, rand());
SET experimental_use_processors = 1;

SELECT '100' AS number FROM d_numbers_local AS n WHERE n.number = 100 LIMIT 2;
2 rows in set. Elapsed: 0.003 sec. Processed 262.14 thousand rows, 2.10 MB (96.64 million rows/s., 773.16 MB/s.)

SELECT '100' AS number FROM d_numbers_remote AS n WHERE n.number = 100 LIMIT 2;

↑ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 20.3.4):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Received from 127.0.0.2:9000. DB::Exception: Illegal types of arguments (String, UInt8) of function equals.

1 rows in set. Elapsed: 0.102 sec.

looks like that because clickhouse treat local and remote server differently

[UnamedRus]

Same error on master version and #9891 pull request

ClickHouse client version 20.4.1.2810.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.1 revision 54434.

SELECT '100' AS number
FROM d_numbers_remote AS n
WHERE n.number = 100
LIMIT 2

┌─number─┐
│ 100    │
└────────┘
↘ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 20.4.1):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Received from 127.0.0.2:9000. DB::Exception: Illegal types of arguments (String, UInt8) of function equals.

1 rows in set. Elapsed: 0.136 sec.