test for https://github.com/ClickHouse/ClickHouse/issues/31414

run.sh

$ docker run --rm --name ch -v zzz-cluster.xml:/etc/clickhouse-server/config.d/zzz-cluster.xml:ro -it yandex/clickhouse-server:20.8
# 2021.11.19 14:47:23.804900 [ 1 ] {} <Information> : Starting ClickHouse 20.8.19.4 with revision 54438, build id: C4778BE3F258FC00, PID 1
$ docker exec -i ch clickhouse client -nm --testmode < test.sql

test.sql

-- https://github.com/ClickHouse/ClickHouse/issues/31414
-- Test has been modified to run on 20.8
-- And it is not compatible with non-default database right now.

DROP TABLE IF EXISTS RTDTestingDictionary_Data;
DROP TABLE IF EXISTS RTDTesting;
DROP TABLE IF EXISTS dist_RTDTesting;
DROP DICTIONARY IF EXISTS RTDTestingDictionary;

CREATE TABLE RTDTestingDictionary_Data
(
    owner_id UInt64,
    week String,
    shard Int64
)
Engine=Memory();

CREATE DICTIONARY RTDTestingDictionary
(
    owner_id UInt64,
    week String,
    shard Int64
)
PRIMARY KEY owner_id, week
LAYOUT(COMPLEX_KEY_HASHED())
SOURCE(CLICKHOUSE(HOST '127.1' PORT 9000 USER 'default' TABLE RTDTestingDictionary_Data))
LIFETIME(MIN 1800 MAX 3600);

CREATE TABLE RTDTesting
(
    owner_id UInt64,
    week String,
    num Int64,
    num2 Int64
)
ENGINE = MergeTree()
ORDER BY (owner_id, week)
SETTINGS index_granularity = 8192;

CREATE TABLE dist_RTDTesting
(
    owner_id UInt64,
    week String,
    num Int64,
    num2 Int64
)
ENGINE = Distributed('cluster_31414', currentDatabase(), 'RTDTesting', dictGetInt64('default.RTDTestingDictionary', 'shard', (owner_id, week)));

INSERT INTO RTDTestingDictionary_Data VALUES (1, '2021-11-15', 2);
INSERT INTO RTDTesting VALUES (1, '2021-11-15', 10, 20);

SET send_logs_level='trace';
SELECT
    owner_id,
    week,
    num + 999999999,
    num2,
    dictGetInt64('default.RTDTestingDictionary', 'shard', (owner_id, week)) AS shard
FROM dist_RTDTesting
WHERE (owner_id = 1) AND (week = '2021-11-15')
SETTINGS allow_nondeterministic_optimize_skip_unused_shards=1, optimize_skip_unused_shards=1, force_optimize_skip_unused_shards=1
FORMAT Vertical;

zzz-cluster.xml

<yandex>
    <remote_servers>
        <cluster_31414>
            <shard>
                <replica>
                    <host>unavail</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>unavail</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>127.0.0.1</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>unavail</host>
                    <port>9000</port>
                </replica>
            </shard>
        </cluster_31414>
    </remote_servers>
</yandex>