Skip to content

Commit ca6449a

Browse files
robot-clickhouserobot-clickhouse
committed
Backport #94816 to 25.10: Fix incorrect monotonicity for toWeek, toYearWeek, toStartOfWeek, toLastDayOfWeek, toDayOfWeek causing wrong pruning
1 parent 991029c commit ca6449a

File tree

3 files changed

+160
-10
lines changed

3 files changed

+160
-10
lines changed

‎src/Functions/IFunctionCustomWeek.h‎

Lines changed: 47 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,9 @@
33
#include <DataTypes/DataTypeDate32.h>
44
#include <DataTypes/DataTypeDateTime.h>
55
#include <DataTypes/DataTypeDateTime64.h>
6+
#include <DataTypes/DataTypeLowCardinality.h>
7+
#include <DataTypes/DataTypeNullable.h>
8+
#include <DataTypes/DataTypeString.h>
69
#include <Functions/CustomWeekTransforms.h>
710
#include <Functions/IFunction.h>
811
#include <Functions/TransformDateTime64.h>
@@ -29,35 +32,64 @@ class IFunctionCustomWeek : public IFunction
2932
bool useDefaultImplementationForConstants() const override { return true; }
3033
ColumnNumbers getArgumentsThatAreAlwaysConstant() const override { return {1, 2}; }
3134

35+
bool canBeExecutedOnDefaultArguments() const override
36+
{
37+
/// String default is empty (not parseable as DateTime), so avoid executing on LC default dictionary key
38+
if constexpr (Transform::value_may_be_string)
39+
return false;
40+
41+
return true;
42+
}
43+
3244
bool hasInformationAboutMonotonicity() const override { return true; }
3345

3446
Monotonicity getMonotonicityForRange(const IDataType & type, const Field & left, const Field & right) const override
3547
{
48+
const IDataType * type_ptr = &type;
49+
50+
if (const auto * lc_type = checkAndGetDataType<DataTypeLowCardinality>(type_ptr))
51+
type_ptr = lc_type->getDictionaryType().get();
52+
53+
if (const auto * nullable_type = checkAndGetDataType<DataTypeNullable>(type_ptr))
54+
type_ptr = nullable_type->getNestedType().get();
55+
56+
const IFunction::Monotonicity is_not_monotonic;
57+
58+
/// Parsing of String arguments is not monotonic w.r.t. String ordering
59+
if (checkAndGetDataType<DataTypeString>(type_ptr))
60+
return is_not_monotonic;
61+
3662
if constexpr (std::is_same_v<typename Transform::FactorTransform, ZeroTransform>)
3763
return {.is_monotonic = true, .is_always_monotonic = true};
3864

65+
if (left.isNull() || right.isNull())
66+
return is_not_monotonic;
67+
3968
const IFunction::Monotonicity is_monotonic = {.is_monotonic = true};
40-
const IFunction::Monotonicity is_not_monotonic;
4169

4270
/// This method is called only if the function has one argument. Therefore, we do not care about the non-local time zone.
4371
const DateLUTImpl & date_lut = DateLUT::instance();
4472

45-
if (left.isNull() || right.isNull())
46-
return {};
47-
4873
/// The function is monotonous on the [left, right] segment, if the factor transformation returns the same values for them.
4974

50-
if (checkAndGetDataType<DataTypeDate>(&type))
75+
if (checkAndGetDataType<DataTypeDate>(type_ptr))
5176
{
5277
return Transform::FactorTransform::execute(UInt16(left.safeGet<UInt64>()), date_lut)
5378
== Transform::FactorTransform::execute(UInt16(right.safeGet<UInt64>()), date_lut)
5479
? is_monotonic
5580
: is_not_monotonic;
5681
}
5782

58-
if (checkAndGetDataType<DataTypeDateTime64>(&type))
83+
if (checkAndGetDataType<DataTypeDate32>(type_ptr))
5984
{
85+
return Transform::FactorTransform::execute(Int32(left.safeGet<Int32>()), date_lut)
86+
== Transform::FactorTransform::execute(Int32(right.safeGet<Int32>()), date_lut)
87+
? is_monotonic
88+
: is_not_monotonic;
89+
}
6090

91+
if (checkAndGetDataType<DataTypeDateTime64>(type_ptr))
92+
{
6193
const auto & left_date_time = left.safeGet<DateTime64>();
6294
TransformDateTime64<typename Transform::FactorTransform> transformer_left(left_date_time.getScale());
6395

@@ -70,10 +102,15 @@ class IFunctionCustomWeek : public IFunction
70102
: is_not_monotonic;
71103
}
72104

73-
return Transform::FactorTransform::execute(UInt32(left.safeGet<UInt64>()), date_lut)
74-
== Transform::FactorTransform::execute(UInt32(right.safeGet<UInt64>()), date_lut)
75-
? is_monotonic
76-
: is_not_monotonic;
105+
if (checkAndGetDataType<DataTypeDateTime>(type_ptr))
106+
{
107+
return Transform::FactorTransform::execute(UInt32(left.safeGet<UInt64>()), date_lut)
108+
== Transform::FactorTransform::execute(UInt32(right.safeGet<UInt64>()), date_lut)
109+
? is_monotonic
110+
: is_not_monotonic;
111+
}
112+
113+
return is_not_monotonic;
77114
}
78115

79116
protected:

‎tests/queries/0_stateless/03789_to_year_week_monotonicity_key_condition.reference‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
-- { echo }
2+
3+
DROP TABLE IF EXISTS t;
4+
CREATE TABLE t (s String)
5+
ENGINE = MergeTree
6+
ORDER BY s;
7+
INSERT INTO t VALUES
8+
('2020-01-10 00:00:00'),
9+
('2020-01-2 00:00:00');
10+
SELECT * FROM t
11+
WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
12+
2020-01-2 00:00:00
13+
DROP TABLE IF EXISTS t;
14+
CREATE TABLE t (d Date32)
15+
ENGINE = MergeTree
16+
ORDER BY d;
17+
INSERT INTO t VALUES ('2020-12-31'), ('2021-01-01');
18+
SELECT * FROM t
19+
WHERE toWeek(d) = toWeek(toDate32('2020-12-31'));
20+
2020-12-31
21+
DROP TABLE IF EXISTS t;
22+
CREATE TABLE t (s String)
23+
ENGINE = MergeTree
24+
ORDER BY s;
25+
INSERT INTO t VALUES
26+
('2020-02-11 00:00:00'),
27+
('2020-02-3 00:00:00');
28+
SELECT * FROM t
29+
WHERE toWeek(s) = toWeek('2020-02-3 00:00:00');
30+
2020-02-3 00:00:00
31+
DROP TABLE IF EXISTS t;
32+
CREATE TABLE t (dt DateTime) ENGINE=MergeTree ORDER BY dt SETTINGS index_granularity=1;
33+
INSERT INTO t SELECT toDateTime('2020-01-01 00:00:00') + number * 3600 FROM numbers(24 * 40);
34+
SELECT count()
35+
FROM t
36+
WHERE toWeek(dt) = toWeek(toDateTime('2020-01-15 00:00:00')) SETTINGS force_primary_key = 1, max_rows_to_read = 169;
37+
168
38+
DROP TABLE IF EXISTS t;
39+
CREATE TABLE t (s LowCardinality(String)) ENGINE = MergeTree ORDER BY s;
40+
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
41+
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
42+
2020-01-2 00:00:00
43+
DROP TABLE IF EXISTS t;
44+
CREATE TABLE t (s Nullable(String)) ENGINE = MergeTree ORDER BY s SETTINGS allow_nullable_key = 1;
45+
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
46+
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
47+
2020-01-2 00:00:00
48+
DROP TABLE IF EXISTS t;
49+
CREATE TABLE t (s LowCardinality(String)) ENGINE = MergeTree ORDER BY s;
50+
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
51+
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
52+
2020-01-2 00:00:00

‎tests/queries/0_stateless/03789_to_year_week_monotonicity_key_condition.sql‎

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
-- { echo }
2+
3+
DROP TABLE IF EXISTS t;
4+
CREATE TABLE t (s String)
5+
ENGINE = MergeTree
6+
ORDER BY s;
7+
8+
INSERT INTO t VALUES
9+
('2020-01-10 00:00:00'),
10+
('2020-01-2 00:00:00');
11+
12+
SELECT * FROM t
13+
WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
14+
15+
DROP TABLE IF EXISTS t;
16+
CREATE TABLE t (d Date32)
17+
ENGINE = MergeTree
18+
ORDER BY d;
19+
20+
INSERT INTO t VALUES ('2020-12-31'), ('2021-01-01');
21+
22+
SELECT * FROM t
23+
WHERE toWeek(d) = toWeek(toDate32('2020-12-31'));
24+
25+
DROP TABLE IF EXISTS t;
26+
CREATE TABLE t (s String)
27+
ENGINE = MergeTree
28+
ORDER BY s;
29+
30+
INSERT INTO t VALUES
31+
('2020-02-11 00:00:00'),
32+
('2020-02-3 00:00:00');
33+
34+
SELECT * FROM t
35+
WHERE toWeek(s) = toWeek('2020-02-3 00:00:00');
36+
37+
DROP TABLE IF EXISTS t;
38+
CREATE TABLE t (dt DateTime) ENGINE=MergeTree ORDER BY dt SETTINGS index_granularity=1;
39+
INSERT INTO t SELECT toDateTime('2020-01-01 00:00:00') + number * 3600 FROM numbers(24 * 40);
40+
41+
SELECT count()
42+
FROM t
43+
WHERE toWeek(dt) = toWeek(toDateTime('2020-01-15 00:00:00')) SETTINGS force_primary_key = 1, max_rows_to_read = 169;
44+
45+
DROP TABLE IF EXISTS t;
46+
CREATE TABLE t (s LowCardinality(String)) ENGINE = MergeTree ORDER BY s;
47+
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
48+
49+
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
50+
51+
DROP TABLE IF EXISTS t;
52+
CREATE TABLE t (s Nullable(String)) ENGINE = MergeTree ORDER BY s SETTINGS allow_nullable_key = 1;
53+
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
54+
55+
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');
56+
57+
DROP TABLE IF EXISTS t;
58+
CREATE TABLE t (s LowCardinality(String)) ENGINE = MergeTree ORDER BY s;
59+
INSERT INTO t VALUES ('2020-01-10 00:00:00'), ('2020-01-2 00:00:00');
60+
61+
SELECT * FROM t WHERE toYearWeek(s) = toYearWeek('2020-01-2 00:00:00');

0 commit comments

Comments
 (0)