Describe the bug
mysql> desc select * from tbl1 left join tbl3 on tbl1.k1 = tbl3.k1 where tbl3.k1 in ('abc');
ERROR 5012 (HY000): errCode = 2, detailMessage = Unexpected exception: org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = 'abc' is not a numberTo Reproduce
CREATE TABLE `tbl1` (
`k1` int(11) NULL COMMENT "",
`k2` int(11) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`k1`, `k2`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`k1`) BUCKETS 1
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);
CREATE TABLE `tbl3` (
`k1` varchar(32) NULL COMMENT "",
`k2` int(11) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`k1`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`k1`) BUCKETS 1
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);
insert into tbl1 values(3,4);
insert into tbl3 values("abc",4);
select * from tbl1 join tbl3 on tbl1.k1 = tbl3.k1 where tbl3.k1 in ('abc');Expected behavior
Not throw error
3条答案
按热度按时间j91ykkif1#
This should be a problem caused by predicate pass optimization.
When the condition is passed to another table, the parameters in the condition need to be converted to the type of the corresponding table. This error will be reported when the type conversion fails. I think if the type conversion fails, the empty set should be returned directly?
avkwfej42#
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
MySQL automatically converts strings to numbers as necessary, and vice versa.
If follow the MySQL method, 'abc' will be converted to 0.
other examples:
results:
1 1
0 0
1 1 0
so the
will be rewrite as
does Doris want to be compatible with this type conversion?
41ik7eoe3#
@Astralidea You are right. Doris is want to be compatible with Mysql. We should do it and we could do this work in a single other PR.