好坑的sql_mode--设置的不同导致查询获得的结果不同

昨天上班回到家里之后，接到电话单位同事打过来的电话，描述的情况如下“我们正在上一个系统，...



昨天下班回到家里之后，接到单位同事打过来的电话，描述的情况如下 “ 我们正在上一个系统，同样的数据，同样的sql,在总部的数据库环境里面查询获得的是正确的结果，结果集是五条，但在分部数据库里面，查出来的结果只有两条数据，跟预想的结果不一样，少了三条数据 ，然后特别的说明了一下环境差异， 总部使用的版本是mysql 5.6.21, 而分部使用的数据库版本是您推荐的mysql 5.6.30（实际上，这也是合作公司给我们同事反馈的结果，因为该系统由外部的软件公司开发，上线部署等都由其支持） “，听到最后一句话，看起来貌似躺枪了---- mysql 5.6.30的版本是作者推荐的，是mysql5.6的最新版本。难道是因为版本差异导致？ 但对于疑问，本人认为可能性只有1%，因为觉得高的小版本应该是解决了更多的问题，而带来问题的可能是会非常小。

how can 1 do ?

已经到家，再返回单位？算了吧，路上花的时间有可能让我已经把问题解决了。决定先让合作公司的人把原始数据还有执行的sql发邮件给我，我先在家里看看。

很快收到了数据跟sql ，因为我个人电脑上也没有装mysql 5.6.30,所以不能在5.6.30上进行验证,只有装载虚拟机上的最新的mysql 5.7.12版本。因为本来就觉得是小版本差异导致查询结果差异的可能性极低 。加上从官网上下载mysql 5.6.30，下载速度不够快，得花半个小时才能下载完成， 所以决定先在5.7.12上找原因。

把数据灌进数据库里面花了一点时间，然后执行sql验证，发现果然是5条数据 。 但在分部的数据库里面，只能查出2条数据，why ? 但很快，意识到分部的数据库是分部科技人员或者合作商自己装的， 可能使用的大多数参数都是默认值，最可能影响sql执行结果或许是sql_mode的设置。

所以，立马将sql_mode 修改成默认值，然后执行sql ,结果如下（表名跟结果，以及关键查询条件都做了脱敏处理）：

mysql> set sql_mode='';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT * FROM table_a l

-> LEFT JOIN table_b a ON a.`APPLY_ID` = l.`APPLY_ID`

-> LEFT JOIN table_c m ON a.`INST_NO` = m.`INST_NO`

-> WHERE l.`ASSIGN_TYPE` = 3 AND l.`ASSIGN_TO` IN (

-> SELECT CAST(u.`ROLE_ID` AS CHAR) FROM table_d u

-> WHERE u.`USER_ID` IN (SELECT p.`UID` FROM table_e p

->    WHERE p.`INST_NO` = a.`INST_NO`

->    AND p.`POST_TYPE` = 'YFSD_' || CAST(a.APPLY_TYPE AS CHAR)

->    AND p.`ROLE_ID` = l.`ASSIGN_TO`

->    AND p.`UID` = '***************'

->            ))

-> ;

+--------+-----------------------------+----------+------------+-----------------+--------------+

| JOB_ID | JOB_NAME                    | JOB_FLAG | JOB_STATUS | STATUS_NAME     | NEXT_OP      |

+--------+-----------------------------+----------+------------+-----------------+--------------+

|    209 | 房产抵押贷款申请620         | LOAN     |          2 | 申请已分配      | 等待受理     |

|    211 | 房产抵押贷款申请622         | LOAN     |          2 | 申请已分配      | 等待受理     |

+--------+-----------------------------+----------+------------+-----------------+--------------+

2 rows in set (0.10 sec)

mysql> exit

如图：



出现了两条结果，跟合作商的研发人员反馈的现象一致。到此，问题也就解决了。 Mysql 5.6.30 下载仍然在继续，进度条显示42%。

问题导致解决了，但是是什么原因，导致了sql_mode为空时，丢失了3条数据的呢？ 为什么是这3条数据 ？丢失的三条数据有何特征 ？

因为sql能够正确执行且不报出任何错误，所以一直在数据上找瑕疵，猜测是发生某些转换导致了结果集的不正确。仔细比对数据之后，没有任何发现。

再后来，仔细review sql语句，不是有个|| 管道符吗 ? 是不是这样造成的， 再结合的验证库中的标准的sql_mode 中含有“pipes_as_concat "， 是不是不指定pipes_as_concat , 就不支持|| 做为连接符。 但是，不支持不该报个错误信息莫？  居然还能出结果，只不过是少了3条？

如是， 将sql_mode 仅设置成”pipes_as_concat “ ，然后一测， 出来正常的结果了。 这不是坑人莫，不支持也得报个一下错误，不是莫？

历史文章：

这是一个严重bug不？

Mysql的JSON与SequoiaDB的比较

一个会导致Mysql crash 的半同步的bug

主键无序插入对性能的影响以及innodb buffer的效率指标分析

有一种主键重复冲突叫自增字段溢出

Mysql的表中含有Blob字段对性能的影响有几何？

又一个有趣的mysql死锁测试与源码分析

Mysql5.7 的错误日志中最常见的note级别日志解释

pt-table-checksum检查mysql5.7主从一致性的小bug.

mysql5.6与mysql5.7的半同步对比测试

mysql性能分析

mysql再一个有趣的死锁现象--删除空行导致

Xtrabackup 是否支持mysql 5.7 ?

又一个有趣的mysql死锁测试与源码分析

Mysql的meta data lock 源码分析-初篇

mysql的purge线程知多少

replace into与insert into ...on duplicate key update的区别以及陷阱

在MYSQL中通过唯一性索引删除同一条纪录出现死锁的分析与总结

MySQL5.7事务提交过程以及无损复制源码解析

Mysql5.7半同步复制源码解析-last

    关注 数据库随笔