Values outside of the lower extent of requested date range returned

[sadata7]

Describe the bug

Hi. I'm using DBeaver Version 23.2.2.202310081633 (October 9, 2023) which uses clickhouse-jdbc 0.4.6 pointed to ClickHouse Server 23.9.1 revision 54466.

I have a table defined as follows:

CREATE TABLE urls (
    day Date,
    hour Int8,
    minute Int8
) ENGINE = MergeTree()
ORDER BY (day, hour, minute);

It has around 10,000 test records in it spanning about a year's worth of values.

Whenever I query for a date range, the lower value returned is always outside of the range by one day. Every one of the following queries returns rows including the date 2022-12-31 when run through DBeaver and the JDBC driver, even though explicitly instructed to only return dates between 2023-01-01 and 2023-01-31.

SELECT * 
FROM urls
WHERE day BETWEEN '2023-01-01' AND '2023-01-31';

SELECT * 
FROM urls
WHERE day BETWEEN toDate('2023-01-01') AND toDate('2023-01-31');

SELECT * 
FROM urls
WHERE day >= toDate('2023-01-01') 
    AND day <= toDate('2023-01-31');

And, it still returns rows containing 2022-12-31 even when instructed to filter them out, even after the fact in a surrounding query:

SELECT * 
FROM urls
WHERE day >= toDate('2023-01-01') 
    AND day <= toDate('2023-01-31')
    AND day != toDate('2022-12-31');

SELECT * 
FROM (
    SELECT *
    FROM urls
    WHERE day >= toDate('2023-01-01') 
        AND day <= toDate('2023-01-31')
) WHERE day != toDate('2022-12-31');

If I change the 'from' date to 2023-01-02, then I will get back 2023-01-01, and so on (i.e. always one day less than the lower extent). Please note that all of the above queries run correctly and as expected from the ClickHouse command line, so it seems to be related to the JDBC driver (or perhaps DBeaver or the ClickHouse HTTP interface).

Any help would be appreciated. Thanks.

Steps to reproduce

1. See above

Expected behaviour

Only return rows containing dates within the requested range.

Code example

See above.

Error log

No errors.

Configuration

Environment

• Client version: clickhouse-jdbc 0.4.6
• Language version: n/a
• OS: Linux Mint 21.2

ClickHouse server

• ClickHouse Server version: 23.9.1 revision 54466
• ClickHouse Server non-default settings, if any: none
• CREATE TABLE statements for tables involved: see above
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[sadata7]

To investigate further, I truncated the table and inserted a single record:

INSERT INTO url (url, day, hour, minute)
VALUES ('https://someurl', '2023-01-01', 1, 1);

If I query through DBeaver via the JDBC driver, it returns 2022-12-31 for the date. If I run the same query on the ClickHouse command line, it returns 2023-01-01. So, this doesn't appear to be a date range issue. It looks like all dates being returned (or at least displayed by DBeaver) are one day behind. I've confirmed this by adding a few more records with successive dates and they all come back minus one day.

[chernser]

Looks like timezone issue.