luozenglin opened a new issue, #9737: URL: https://github.com/apache/incubator-doris/issues/9737
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Version 1.0 ### What's Wrong? 1. An exception occurs when the binary predicate and the in predicate of the same column are included in the query condition, and the literal in the in predicate is type-converted. ``` mysql> select * from cost2 where id = 2 or id in ("1"); ERROR 1105 (HY000): errCode = 2, detailMessage = Unexpected exception: org.apache.doris.analysis.CastExpr cannot be cast to org.apache.doris.analysis.LiteralExpr ``` 2. Cannot hit tablet using string literal in in predicate. ``` mysql> explain select * from cost2 where id in ("1"); +-----------------------------------------------------------------------------------------------+ | Explain String | +-----------------------------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:`default_cluster:testdb`.`cost2`.`id` | `default_cluster:testdb`.`cost2`.`cost` | | PARTITION: UNPARTITIONED | | | | RESULT SINK | | | | 1:EXCHANGE | | | | PLAN FRAGMENT 1 | | OUTPUT EXPRS: | | PARTITION: HASH_PARTITIONED: `default_cluster:testdb`.`cost2`.`id` | | | | STREAM DATA SINK | | EXCHANGE ID: 01 | | UNPARTITIONED | | | | 0:OlapScanNode | | TABLE: cost2 | | PREAGGREGATION: OFF. Reason: No AggregateInfo | | PREDICATES: `id` IN ('1') | | partitions=1/1 | | rollup: cost2 | | tabletRatio=8/8 | | tabletList=17010,17012,17014,17016,17018,17020,17022,17024 | | cardinality=1 | | avgRowSize=1570.0 | | numNodes=1 | +-----------------------------------------------------------------------------------------------+ 27 rows in set (0.05 sec) mysql> explain select * from cost2 where id in (1); +-----------------------------------------------------------------------------------------------+ | Explain String | +-----------------------------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:`default_cluster:testdb`.`cost2`.`id` | `default_cluster:testdb`.`cost2`.`cost` | | PARTITION: HASH_PARTITIONED: `default_cluster:testdb`.`cost2`.`id` | | | | RESULT SINK | | | | 0:OlapScanNode | | TABLE: cost2 | | PREAGGREGATION: OFF. Reason: No AggregateInfo | | PREDICATES: `id` IN (1) | | partitions=1/1 | | rollup: cost2 | | tabletRatio=1/8 | | tabletList=17024 | | cardinality=1 | | avgRowSize=1570.0 | | numNodes=1 | +-----------------------------------------------------------------------------------------------+ 17 rows in set (0.05 sec) ``` ### What You Expected? 1. ``` mysql> select * from cost2 where id = 2 or id in ("1"); +------+------+ | id | cost | +------+------+ | 1 | 100 | +------+------+ 1 row in set (0.07 sec) ``` 2. ``` mysql> explain select * from cost2 where id in ("1"); +-----------------------------------------------------------------------------------------------+ | Explain String | +-----------------------------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS:`default_cluster:testdb`.`cost2`.`id` | `default_cluster:testdb`.`cost2`.`cost` | | PARTITION: HASH_PARTITIONED: `default_cluster:testdb`.`cost2`.`id` | | | | RESULT SINK | | | | 0:OlapScanNode | | TABLE: cost2 | | PREAGGREGATION: OFF. Reason: No AggregateInfo | | PREDICATES: `id` IN (1) | | partitions=1/1 | | rollup: cost2 | | tabletRatio=1/8 | | tabletList=17024 | | cardinality=1 | | avgRowSize=1570.0 | | numNodes=1 | +-----------------------------------------------------------------------------------------------+ 17 rows in set (0.04 sec) ``` ### How to Reproduce? 1. create table ``` CREATE TABLE `cost2` ( `id` bigint(20) NULL COMMENT "", `cost` bigint(20) SUM NULL COMMENT "" ) ENGINE=OLAP AGGREGATE KEY(`id`) COMMENT "OLAP" DISTRIBUTED BY HASH(`id`) BUCKETS 8 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ) ``` 2. insert date ``` insert into cost2 values(1, 100); ``` 3. query ``` select * from cost2 where id = 2 or id in ("1"); explain select * from cost2 where id in ("1"); ``` ### Anything Else? In the first sql, both column `id` and literal `2` in expression `id = 2` will be converted to column type `bigint`, and both column `id` and literal `"2"` in expression `id in ("1")` will be converted to `varchar`. When the `ColumnRange` is generated, the `id` types in the two expressions are inconsistent, causing an exception. ### 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
