本文内容来自 YashanDB 官网，原文内容请见  https://www.yashandb.com/newsinfo/7664894.html?templateId=171...

问题

最近遇到一个问题，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 dual where 1 in ('1');
 
D
-
X
 
SQL>

不同的值报错不一致的原因

为什么 a2.c2=25 会报错，而 a2.c2=24 则不报错，则主要是因为执行计划的实际运行未进行 a1.c3 in ('1,2') 导致，可以用 set autotrace traceonly 和 alter session set statistics_level=all，看到崖山执行计划的实际运行细节。可以看到 nested loop 的外层驱动表是 a2，过滤条件是 a2.c2=24，因此 a2 无任何记录返回。由于 nested loop 驱动表是 0 行，所以内层 join 表 a1 的过滤条件 a1.c3 in ('1,2') 不会实际执行，因此不报错。

SQL> set autotrace traceonly
SQL> alter session set statistics_level=all;
 
Succeed.
 
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');
 
Execution Plan
----------------------------------------------------------------
SQL hash value: 2359756584Optimizer: ADOPT_C
                                                                 
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 1| | 59| 1| | | || 1 | AGGREGATE | | | 1| 1| 1( 0)| 54| 1| | | |
| 2 | NESTED INDEX LOOPS INNER | | | 1| | 1( 0)| 49| | | | || 3 | TABLE ACCESS BY INDEX ROWID | T1 | SYS | 1| | 1( 0)| | | | | |
|* 4 | INDEX UNIQUE SCAN | SYS_C_35 | SYS | 1| | 1( 0)| 46| | | | ||* 5 | TABLE ACCESS BY INDEX ROWID | T1 | SYS | 1| | 1( 0)| | | | | |
|* 6 | INDEX UNIQUE SCAN | SYS_C_34 | SYS | 1| | 1( 0)| | | | | |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
                                                                 
Operation Information (identified by operation id):
---------------------------------------------------                                                                 
   4 - Predicate : access("A2"."C2" = 24)   5 - Predicate : filter("A1"."C3" = '1,2')   6 - Predicate : access("A1"."C1" = "A2"."C1")
 
 
 
 
Statistics
----------------------------------------------------------------------------------------------------          0 physical reads          1 db block gets          0 consistent gets        496 redo size          1 recursive calls          0 bytes sent via SQL*Net to client          0 bytes received via SQL*Net from client          0 SQL*Net roundtrips to/from client          0 sorts (memory)          0 sorts (disk)          1 rows processed          0 bytes sent via PX          0 block received 
34 rows fetched.

如果执行 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')，由于外层驱动表 a2 的过滤条件是 a2.c2=25，恰好有 1 行匹配。由于 nested loop 驱动表是 1 行，所以内层 join 表 a1 的过滤条件 a1.c3 in ('1,2') 也会实际执行 1 次，因此报错，符合预期，而且 Oracle 的行为也是如此：

YashanDB 执行情况

SQL> select count(1) from t1 a1 left join t1 a2 on a1.c1=a2.c1 where a2.c2=24;
 
   COUNT(1)
-----------          0
 1 row fetched.
 
SQL> select count(1) from t1 a1 left join t1 a2 on a1.c1=a2.c1 where a2.c2=25;
 
   COUNT(1)
-----------          1
 1 row fetched.
 
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');
 
   COUNT(1)
-----------          1
 1 row fetched.
 
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

Oracle 执行情况

SQL> select count(1) from t1 a1 left join t1 a2 on a1.c1=a2.c1 where a2.c2=24;
 
  COUNT(1)
----------     0
 SQL> select count(1) from t1 a1 left join t1 a2 on a1.c1=a2.c1 where a2.c2=25;
 
  COUNT(1)
----------     1
 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');
 
  COUNT(1)
----------     1
 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');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')
                                                                                          *
ERROR at line 1:
ORA-01722: invalid number

MySQL 5.7 不报错原因

MySQL 5.7 对于 a1.c3 in ('1,2')，同样也是将 '1,2' 转换为数值，但是 MySQL 5.7 的特殊之处在于就算 '1,2' 是非法的数字，也能强行转换，所以不报错

mysql> select 1 from dual where 1 in ('a');Empty set, 1 warning (0.00 sec)
 mysql> show warnings;+---------+------+---------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |+---------+------+---------------------------------------+1 row in set (0.00 sec)
 mysql> select * from dual where 1 in ('1,2');ERROR 1096 (HY000): No tables usedmysql> select 1 from dual where 1 in ('1,2');+---+| 1 |+---+| 1 |+---+1 row in set, 1 warning (0.00 sec)
 mysql> show warnings;+---------+------+-----------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: '1,2' |+---------+------+-----------------------------------------+1 row in set (0.01 sec)
 mysql> select 1 from dual where 1 in ('1');+---+| 1 |+---+| 1 |+---+1 row in set (0.00 sec)

详情请参考 MySQL 官方文档：Strings are automatically converted to numbers and numbers to strings as necessary.

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators....

解决方法

实际上 MySQL 的非法数值的字符串依然可以转数字的隐式转换容易引起问题，可参考 csdn 的这篇技术文档：

https://blog.csdn.net/thekenofDIS/article/details/75005996

比较好的做法是尽量避免 mysql 隐式转换的这种行为，应该数字和数字进行等值运算，字符串和字符串进行等值运算，尽量不要数字和字符串进行等值运算，因此对 SQL 语句改写，问题解决：

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);
 
   COUNT(1)
-----------          1
 1 row fetched.