`HAVING ... IN` With `dateDiff` And `now()` Fails On Cluster() Query Due To Incorrect Type Inference

by ADMIN 101 views

Introduction

When using the HAVING ... IN clause in a cluster() query with dateDiff and now(), a type error occurs. This issue is not present when using the HAVING ... = clause. In this article, we will delve into the problem, provide a reproducible example, and discuss the expected behavior.

Problem Description

The HAVING ... IN clause fails with a type error when using dateDiff and now() in a cluster() query. The same logic using HAVING ... = works correctly. This issue is reproducible on the most recent release of ClickHouse.

Reproducible Example

To reproduce the issue, follow these steps:

  1. Create a local table on a cluster:
CREATE TABLE default.test_table_local ON CLUSTER '{cluster}'
(
    first_seen DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/{cluster}/{shard}/tables/test_table', '{replica}')
ORDER BY tuple();
  1. Run the following query, which fails:
SELECT
    min(first_seen) AS first_seen,
    dateDiff('minute', first_seen, now()) AS diff,
    if(diff < 30, 'Inactive', 'OK') AS status
FROM cluster('{cluster}', default.test_table_local)
HAVING status IN ('OK');

The error message is:

Code: 43. DB::Exception: A value of illegal type was provided as 3rd argument 'enddate' to function 'dateDiff'.
Expected: Date[32] or DateTime[64], got: UInt32
  1. Run the following query, which works fine:
SELECT
    min(first_seen) AS first_seen,
    dateDiff('minute', first_seen, now()) AS diff,
    if(diff < 30, 'Inactive', 'OK') AS status
FROM cluster('{cluster}', default.test_table_local)
HAVING status = 'OK';

Expected Behavior

Both HAVING status IN ('OK') and HAVING status = 'OK' should behave identically and not raise type errors when using dateDiff(..., now()).

Error Message and/or Stacktrace

The error message received from the server is:

Received exception from server (version 25.4.1):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: A value of illegal type was provided as 3rd argument 'enddate' to function 'dateDiff'. Expected: Date[32] or DateTime[64], got: UInt32: In scope SELECT min(__table1.first_seen) AS firstSeen, dateDiff('minute', min(__table1.first_seen), _CAST('2025-04-29 10:40:16', 'DateTime')) AS diff, if(dateDiff('minute', min(__table1.first_seen), _CAST('2025-04-29 10:40:16', 'DateTime')) < 30, 'Inactive', 'OK') AS status FROM default.test_table AS __table1 HAVING if(dateDiff('minute', min(__table1.first_seen), 1745923216) < 30, 'Inactive', 'OK') IN ('OK'). (ILLEGAL_TYPE_OF_ARGUMENT)

Additional Context

abling the analyzer using SETTINGS enable_analyzer=0 also works as a workaround for this issue.

Conclusion

The HAVING ... IN clause fails with a type error when using dateDiff and now() in a cluster() query. This issue is not present when using the HAVING ... = clause. The expected behavior is for both clauses to behave identically and not raise type errors. Disabling the analyzer is a workaround for this issue.

Introduction

In our previous article, we discussed the issue of the HAVING ... IN clause failing with a type error when using dateDiff and now() in a cluster() query. In this Q&A article, we will provide answers to some common questions related to this issue.

Q: What is the cause of this issue?

A: The cause of this issue is incorrect type inference by the ClickHouse analyzer. When using dateDiff and now() in a cluster() query, the analyzer incorrectly infers the type of the enddate argument, leading to a type error.

Q: Why does the HAVING ... = clause work correctly?

A: The HAVING ... = clause works correctly because it does not involve the enddate argument, which is the source of the type error. When using the HAVING ... = clause, the analyzer does not attempt to infer the type of the enddate argument, and therefore does not encounter the type error.

Q: Is this issue specific to the cluster() function?

A: No, this issue is not specific to the cluster() function. It can occur with any query that uses dateDiff and now() in a HAVING clause.

Q: Can I use a workaround to avoid this issue?

A: Yes, you can use a workaround to avoid this issue. Disabling the analyzer using SETTINGS enable_analyzer=0 can prevent the type error from occurring. However, this may have performance implications and should be used with caution.

Q: Is this issue fixed in the latest version of ClickHouse?

A: No, this issue is not fixed in the latest version of ClickHouse. However, the ClickHouse team is aware of the issue and is working to resolve it.

Q: How can I report this issue to the ClickHouse team?

A: You can report this issue to the ClickHouse team by submitting a bug report on the ClickHouse issue tracker. Be sure to include a reproducible example and any relevant details about your environment.

Q: Can I use a different function to achieve the same result?

A: Yes, you can use a different function to achieve the same result. For example, you can use the toDateTime function to convert the now() value to a DateTime type, which can then be used with dateDiff.

Q: What are the implications of this issue for my application?

A: The implications of this issue for your application will depend on your specific use case. If you are using dateDiff and now() in a HAVING clause, you may need to modify your query to avoid the type error. Alternatively, you can use a workaround such as disabling the analyzer or using a different function.

Q: Can I get help from the ClickHouse community?

A: Yes, you can get help from the ClickHouse community by posting a question on the ClickHouse forum or seeking assistance from a ClickHouse expert. The ClickHouse community is known for its helpfulness and expertise, and you may be able to find a solution to your issue by seeking help from others.