|
| 1 | +-- Tags: no-random-merge-tree-settings |
| 2 | + |
| 3 | +-- A `WHERE` equality on a `Dynamic` column is not merged into the JOIN condition |
| 4 | +-- unless `allow_dynamic_type_in_join_keys` is enabled. |
| 5 | + |
| 6 | +DROP TABLE IF EXISTS t_int; |
| 7 | +DROP TABLE IF EXISTS t_dyn; |
| 8 | +SET enable_analyzer = 1; |
| 9 | +SET enable_parallel_replicas = 0; |
| 10 | +SET query_plan_merge_filter_into_join_condition = 1; |
| 11 | +SET query_plan_join_swap_table = 'false'; |
| 12 | +SET enable_join_runtime_filters = 0; |
| 13 | +-- CI randomizes this to 0, which leaves `ON 1` reported as `inner` instead of `cross`. |
| 14 | +SET query_plan_optimize_join_order_limit = 10; |
| 15 | + |
| 16 | +CREATE TABLE t_int (a Int32) ENGINE = MergeTree ORDER BY tuple(); |
| 17 | +CREATE TABLE t_dyn (d Dynamic) ENGINE = MergeTree ORDER BY tuple(); |
| 18 | + |
| 19 | +INSERT INTO t_int VALUES (1), (2), (3); |
| 20 | +INSERT INTO t_dyn VALUES (1), (2), (5); |
| 21 | + |
| 22 | +SELECT '-- `Dynamic` key is rejected with allow_dynamic_type_in_join_keys = 0'; |
| 23 | +SELECT |
| 24 | + extract(arrayStringConcat(groupArray(explain), '\n'), 'Type: (\\w+)') AS join_kind, |
| 25 | + extract(arrayStringConcat(groupArray(explain), '\n'), 'Join conditions: ([^\n]*)') AS join_conditions, |
| 26 | + countIf(explain LIKE '%Filter column:%') AS filters_above_join |
| 27 | +FROM ( |
| 28 | + EXPLAIN SELECT * FROM (SELECT * FROM t_int INNER JOIN t_dyn ON 1) WHERE a = d |
| 29 | + SETTINGS allow_dynamic_type_in_join_keys = 0 |
| 30 | +); |
| 31 | + |
| 32 | +SELECT a, toString(d) FROM (SELECT * FROM t_int INNER JOIN t_dyn ON 1) WHERE a = d |
| 33 | +ORDER BY ALL SETTINGS allow_dynamic_type_in_join_keys = 0; |
| 34 | + |
| 35 | +SELECT '-- `Dynamic` key is merged with allow_dynamic_type_in_join_keys = 1'; |
| 36 | +SELECT |
| 37 | + extract(arrayStringConcat(groupArray(explain), '\n'), 'Type: (\\w+)') AS join_kind, |
| 38 | + extract(arrayStringConcat(groupArray(explain), '\n'), 'Join conditions: ([^\n]*)') AS join_conditions, |
| 39 | + countIf(explain LIKE '%Filter column:%') AS filters_above_join |
| 40 | +FROM ( |
| 41 | + EXPLAIN SELECT * FROM (SELECT * FROM t_int INNER JOIN t_dyn ON 1) WHERE a = d |
| 42 | + SETTINGS allow_dynamic_type_in_join_keys = 1 |
| 43 | +); |
| 44 | + |
| 45 | +DROP TABLE t_int; |
| 46 | +DROP TABLE t_dyn; |
0 commit comments