SELECT '-- Single partition by function';

CREATE TABLE 03173_single_function (
    dt Date,
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY toMonth(dt);
OPTIMIZE TABLE 03173_single_function FINAL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_single_function';


SELECT '-- Nested partition by function';

CREATE TABLE 03173_nested_function(
    id Int32,
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3;

INSERT INTO 03173_nested_function SELECT number FROM numbers(100);
OPTIMIZE TABLE 03173_nested_function FINAL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function';
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_subexpr';


SELECT '-- Nested partition by function, LowCardinality';

SET allow_suspicious_low_cardinality_types = 1;

CREATE TABLE 03173_nested_function_lc(
    id LowCardinality(Int32),
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3;

INSERT INTO 03173_nested_function_lc SELECT number FROM numbers(100);
OPTIMIZE TABLE 03173_nested_function_lc FINAL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_lc';
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_subexpr_lc';


SELECT '-- Nested partition by function, Nullable';

CREATE TABLE 03173_nested_function_null(
    id Nullable(Int32),
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3
SETTINGS allow_nullable_key=1;

INSERT INTO 03173_nested_function_null SELECT number FROM numbers(100);
OPTIMIZE TABLE 03173_nested_function_null FINAL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_null';
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_subexpr_null';


SELECT '-- Nested partition by function, LowCardinality + Nullable';


SET allow_suspicious_low_cardinality_types = 1;
CREATE TABLE 03173_nested_function_lc_null(
    id LowCardinality(Nullable(Int32)),
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3
SETTINGS allow_nullable_key=1;

INSERT INTO 03173_nested_function_lc_null SELECT number FROM numbers(100);
OPTIMIZE TABLE 03173_nested_function_lc_null FINAL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_lc_null';
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_function_subexpr_lc_null';


SELECT '-- Non-safe cast';

CREATE TABLE 03173_nonsafe_cast(
    id Int64,
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3;

INSERT INTO 03173_nonsafe_cast SELECT number FROM numbers(100);
OPTIMIZE TABLE 03173_nonsafe_cast FINAL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nonsafe_cast';


SELECT '-- Multiple partition columns';
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_multiple_columns';
-- Due to xxHash32() in WHERE condition, MinMax is unable to eliminate any parts,
-- so partition pruning leave two parts (for key1 // 50 = 0 and key1 // 50 = 1)
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_multiple_columns_subexpr';

-- Preparing base table for filtering by LowCardinality/Nullable sets
CREATE TABLE 03173_base_data_source(
    id Int32,
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3;

INSERT INTO 03173_base_data_source SELECT number FROM numbers(100);
OPTIMIZE TABLE 03173_base_data_source FINAL;

SELECT '-- LowCardinality set';

SET allow_suspicious_low_cardinality_types = 1;
CREATE TABLE 03173_low_cardinality_set (id LowCardinality(Int32)) ENGINE=Memory AS SELECT 10;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_low_cardinality_set';


SELECT '-- Nullable set';

CREATE TABLE 03173_nullable_set (id Nullable(Int32)) ENGINE=Memory AS SELECT 10;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nullable_set';


SELECT '-- LowCardinality + Nullable set';

CREATE TABLE 03173_lc_nullable_set (id LowCardinality(Nullable(Int32))) ENGINE=Memory AS SELECT 10 UNION ALL SELECT NULL;
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_lc_nullable_set';


SELECT '-- Not failing with date parsing functions';

CREATE TABLE 03173_date_parsing (
    id String
)
ENGINE=MergeTree
ORDER BY tuple()
PARTITION BY toDate(id);


SELECT '-- Pruning + not failing with nested date parsing functions';

CREATE TABLE 03173_nested_date_parsing (
    id String
)
ENGINE=MergeTree
ORDER BY tuple()
PARTITION BY toMonth(toDate(id));
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_nested_date_parsing';


SELECT '-- Empty transform functions';

CREATE TABLE 03173_empty_transform(
    id Int32,
)
ENGINE = MergeTree
ORDER BY tuple()
PARTITION BY xxHash32(id) % 3;

INSERT INTO 03173_empty_transform SELECT number FROM numbers(6);
OPTIMIZE TABLE 03173_empty_transform FINAL;

SELECT id FROM 03173_empty_transform WHERE xxHash32(id) % 3 IN (xxHash32(2::Int32) % 3) SETTINGS log_comment='03173_empty_transform';
SYSTEM FLUSH LOGS;
SELECT ProfileEvents['SelectedParts'] FROM system.query_log WHERE type = 'QueryFinish' AND current_database = currentDatabase() AND log_comment = '03173_empty_transform';

