【YashanDB知识库】MySQL和YashanDB 隐式转换不一致引起的报错

问题

最近遇到一个问题，MySQL 5.7的SQL语句执行无问题，但在YashanDB执行会报错：

SQL> select count(1) from t1 a1 left join t1 a2 on a1.c1=a2.c1 where a2.c2=25 and a1.c3 in ('1,2');

[1:91]YAS-00008 type convert error : not a valid number

另外，该问题有一个奇怪的地方，不同的值表现不一致，比如a2.c2=25会报错，而a2.c2=24则不报错，也需要分析清楚原因

SQL> select count(1) from t1 a1 left join t1 a2 on a1.c1=a2.c1 where a2.c2=24 and a1.c3 in ('1,2');

 

   COUNT(1)

-----------

          0

 

1 row fetched.

表的定义和数据如下：

create table t1(c1 int primary key, c2 int unique, c3 int);

insert into t1 values(1,25,1);

commit;

原因

YashanDB报错原因

对于a1.c3 in (‘1,2’)，由于a1.c3是数值类型，'1,2’是字符串类型，按照隐式转换的规则，会将’1,2’转换为数值，由于是’1,2’是非法的数字，所以报错，而且这个行为和Oracle是一致的。

YashanDB报错示例

SQL> select * from dual where 1 in ('a');

[1:32]YAS-00008 type convert error : not a valid number

SQL> select * from dual where 1 in ('1,2');

[1:32]YAS-00008 type convert error : not a valid number

SQL> select * from dual where 1 in ('1');

 

DUMMY

-----------------

X

 

1 row fetched.

Oracle报错示例

SQL> select * from dual where 1 in ('a');

select * from dual where 1 in ('a')

                               *

ERROR at line 1:

ORA-01722: invalid number

 

 

SQL> select * from dual where 1 in ('1,2');

select * from dual where 1 in ('1,2')

                               *

ERROR at line 1:

ORA-01722: invalid number

 

 

SQL> select * from dua