本文内容来自 YashanDB 官网，原文内容请见https://www.yashandb.com/newsinfo/7664894.html?templateId=1718516
问题
最近遇到一个问题，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: 2359756584
Optimizer: 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 used
mysql> 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.html
解决方法
实际上 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.
复制代码
评论