✅ Doris 1.2.5联邦查询Clickhouse, 无法使用Date类型作为过滤条件, 报错: Cannot convert string to type Date

👨🏻‍💻 技术问答
1

Doris版本: 1.2.5
环境: 生产环境

问题描述: multi catalog方式配置连接clickhouse, sql语句的where条件中, 如果不存在Date类型, 则能查询. 如果包含Date类型则报错类似: Cannot convert string 2023-07-03 00:00:00 to type Date

SQL语句:

select * from catalog_clickhouse.test.test_member where d = '2023-07-03' limit 100;

select * from catalog_clickhouse.test.test_member where d=date_sub(current_date(), 1) limit 100;

报错截图:

image
image1398×68 8.27 KB

4

能否提供下CK的建表语句，ck版本，jdbc版本，然后explain一下查询的SQL截个图看一下

5

clickhouse建表语句

分布式表: 
CREATE TABLE test.test_member
(
    `d` Date COMMENT '日期',
    `id` UInt32 COMMENT 'id',
    `gender` String COMMENT '性别(男，女，不明)',
    ......
    `params` String COMMENT '自定义拓展字段'
)
ENGINE = Distributed('ch_cluster1', 'test_shard', 'test_member', sipHash64(id));

shard表:
CREATE TABLE test_shard.test_member
(
    `d` Date COMMENT '日期',
    `id` UInt32 COMMENT 'id',
    `gender` String COMMENT '性别(男，女，不明)',
    ......
    `params` String COMMENT '自定义拓展字段'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/test_shard/test_member/{layer}', '{replica}')
PARTITION BY d
ORDER BY id
SETTINGS index_granularity = 8192

clickhouse版本: version 21.3.6.55

jdbc版本: clickhouse-jdbc-0.3.2-patch11-all.jar

查询的sql: select d,id from catalog_clickhouse.test.test_member where d = ‘2023-07-03’ limit 100;

explain 查询SQL:

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    `d`
    `id`
  PARTITION: UNPARTITIONED
""
  VRESULT SINK
""
  0:VJdbcScanNode
"     TABLE: ""test"".""test_member"""
"     QUERY: SELECT ""d"", ""id"" FROM ""test"".""test_member"" WHERE (""d"" = '2023-07-03 00:00:00') LIMIT 100"
     limit: 100
6

https://github.com/apache/doris/issues/21684