[GitHub] [doris] itsallsame opened a new issue, #21684: [Bug] "using mutil catalog, connect to ClickHouse, unable to use Date type as filter condition, error message: Cannot convert string to type Date."

[via GitHub]

itsallsame opened a new issue, #21684:
URL: https://github.com/apache/doris/issues/21684

   ### Search before asking
   
   - [X] I had searched in the 
[issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no 
similar issues.
   
   
   ### Version
   
   1.2.5
   
   ### What's Wrong?
   
   "When configuring the connection to ClickHouse in multi-catalog mode, 
queries without Date types in the WHERE clause can be executed successfully. 
However, if the WHERE clause contains Date types, an error will occur, such as: 
'Cannot convert string 2023-07-03 00:00:00 to type Date'."
   
   PS1:
   
   my 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;
   ```
   
   error: 
   
![image](https://github.com/apache/doris/assets/115528312/8c4a73db-06f8-4563-bed2-6bdd511c2cac)
   
   PS2: 
   clickhouse version: 21.3.6.55
   
   catalog jdbc version: clickhouse-jdbc-0.3.2-patch11-all.jar
   
   create table sql: 
   ```
   distribute table sql
   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 table sql:
   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
   ```
   
   PS3: 
   doris sql explain:
   ```
   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
   ```
   
   
   ### What You Expected?
   
   get data from clickhouse
   
   ### How to Reproduce?
   
   _No response_
   
   ### Anything Else?
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of 
Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org