Clickhouse: key condition negated incorrectly for monotonic functions

[alesapin]

How to reproduce

CREATE TABLE datetimeX
(
    `date` DateTime
)
ENGINE = MergeTree
ORDER BY toStartOfHour(date);

INSERT INTO datetimeX VALUES(toDateTime('2020-01-20 17:42:42'));


SELECT *
FROM datetimeX

┌────────────────date─┐
│ 2020-01-20 17:42:42 │
└─────────────────────┘
1 rows in set. Elapsed: 0.007 sec.


SELECT *
FROM datetimeX
WHERE 
    (date >= toDateTime('2020-01-20 17:00:00')) 
AND 
    (toStartOfHour(date) = toDateTime('2020-01-20 17:00:00'))

┌────────────────date─┐
│ 2020-01-20 17:42:42 │
└─────────────────────┘
1 rows in set. Elapsed: 0.012 sec.

SELECT *
FROM datetimeX
WHERE 
    (NOT (date < toDateTime('2020-01-20 17:00:00'))) 
AND 
    (toStartOfHour(date) = toDateTime('2020-01-20 17:00:00'))

Ok.

0 rows in set. Elapsed: 0.011 sec.

Condition (NOT (date < toDateTime('2020-01-20 17:00:00'))) is equal to (date >= toDateTime('2020-01-20 17:00:00')), but we get different results. Looks like bug.

[alesapin]

Even simplier.

 :) SELECT * from datetimeX where (NOT (date < toDateTime('2020-01-20 17:00:00')));

SELECT *
FROM datetimeX
WHERE NOT (date < toDateTime('2020-01-20 17:00:00'))

Ok.

0 rows in set. Elapsed: 0.009 sec. 

click.sas.yp-c.yandex.net :) SELECT * from datetimeX where date >= toDateTime('2020-01-20 17:00:00');

SELECT *
FROM datetimeX
WHERE date >= toDateTime('2020-01-20 17:00:00')

┌────────────────date─┐
│ 2020-01-20 17:42:42 │
└─────────────────────┘

1 rows in set. Elapsed: 0.009 sec. 

[filimonov]

19.10.1.5 - works properly.
19.11.2.7 - not.

[CurtizJ]

The issue is in final mark.
It also works.

CREATE TABLE datetimeX (`date` DateTime) ENGINE = MergeTree ORDER BY toStartOfHour(date) settings write_final_mark=0;
INSERT INTO datetimeX VALUES(toDateTime('2020-01-20 17:42:42'));
SELECT * FROM datetimeX WHERE (date >= toDateTime('2020-01-20 17:00:00')) AND (toStartOfHour(date) = toDateTime('2020-01-20 17:00:00')) ;
SELECT * FROM datetimeX WHERE (NOT (date < toDateTime('2020-01-20 17:00:00'))) AND (toStartOfHour(date) = toDateTime('2020-01-20 17:00:00'));

[CurtizJ]

But I'm not sure, that final mark is the original cause of the problem. It just adds another row to index and may be we have bug in index analyze in general case.

[CurtizJ]

Yes, I was wrong about final mark.
This reproduces even on 18.1 (oldest version that I found on my laptop)

ap11-nix :) CREATE TABLE datetimeX (`date` DateTime) ENGINE = MergeTree ORDER BY toStartOfHour(date) settings index_granularity=1

ap11-nix :) INSERT INTO datetimeX VALUES(toDateTime('2020-01-20 17:42:42')), (toDateTime('2020-01-20 17:42:43'));

ap11-nix :) SELECT * FROM datetimeX WHERE (NOT (date < toDateTime('2020-01-20 17:00:00')));

┌────────────────date─┐
│ 2020-01-20 17:42:43 │
└─────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 

ap11-nix :) SELECT * FROM datetimeX WHERE (NOT (identity(date) < toDateTime('2020-01-20 17:00:00')));

┌────────────────date─┐
│ 2020-01-20 17:42:42 │
│ 2020-01-20 17:42:43 │
└─────────────────────┘

2 rows in set. Elapsed: 0.002 sec. 

ap11-nix :) select version()

┌─version()─┐
│ 18.1.0    │
└───────────┘

1 rows in set. Elapsed: 0.001 sec.

[alesapin]

Simplier:

CREATE TABLE roundX
(
    `value` Float64
)
ENGINE = MergeTree
ORDER BY round(value)

Ok.
insert into roundX VALUES(7.42);

Ok.

1 rows in set. Elapsed: 0.006 sec.

SELECT *
FROM roundX
WHERE NOT (value < 7)

Ok.

0 rows in set. Elapsed: 0.014 sec.


SELECT *
FROM roundX
WHERE NOT (value > 7.43)

Ok.

[alesapin]

Introduced here https://github.com/ClickHouse/ClickHouse/pull/951.

[4ertus2]

It's also interesting that

CREATE TABLE roundX (value Decimal(18, 4)) ENGINE = MergeTree ORDER BY toDecimal32(round(value), 4);

INSERT INTO roundX VALUES(toDecimal32(7.42, 4));
SELECT * FROM roundX WHERE NOT (value < 7);
SELECT * FROM roundX WHERE NOT (value > toDecimal32(7.43, 4));

7.4200
7.4200

[den-crane]

@4ertus2

it's not. it does not use PK.

SET force_primary_key = 1;

SELECT *
FROM roundX
WHERE NOT (value < 7);

Received exception from server (version 20.2.1):
Code: 277. DB::Exception: Received from localhost:9000. DB::Exception: Primary key (toDecimal32(round(value), 4)) is not used and setting 'force_primary_key' is set..

toDecimal probably is not monotonic because accepts String argument

[4ertus2]

toDecimal probably is not monotonic because accepts String argument

It looks like the right answer is monotonicity currently not defined for functions of several arguments even if all of them except one are constants.