[SQL][Bug] Column type judgment error

[morningman]

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 number

To 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

[EmmyMiao87]

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?

[Astralidea]

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:

select '2.34' = 2.34,'2.34abc' = 2.34,
       'abc2.34' = 2.34, '2 .34abc' = 2.34,
       '2 .34abc' = 2, 'abc' = 0, 'abc' = 1;

results: 1 1
0 0
1 1 0

so the

select * from tbl1 join tbl3 on tbl1.k1 = tbl3.k1 where tbl3.k1  in ('abc');

will be rewrite as

select * from tbl1 join tbl3 on tbl1.k1 = tbl3.k1 where tbl3.k1 in (0);

does Doris want to be compatible with this type conversion?

[kangkaisen]

@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.