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