timestampdiff的一个BUG

timestampdiff的一个BUG发现一个timestampdiff无法正确判断列值的BUG,看下面例子:–测试表如下:mysql>select*fromtest;+—-+———————+|id|col2|+—-+———————+|1|2019-03-2000:00:00||2|2…

大家好,又见面了,我是你们的朋友全栈君。

发现一个timestampdiff无法正确判断列值的BUG,看下面例子:

--测试表如下:
mysql> select * from test;
+----+---------------------+
| id | col2                |
+----+---------------------+
|  1 | 2019-03-20 00:00:00 |
|  2 | 2019-04-01 00:00:00 |
|  3 | 2019-04-20 00:00:00 |
|  4 | 2019-04-01 14:30:00 |
+----+---------------------+
4 rows in set (0.00 sec)
mysql> desc test;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | NO   |     | NULL              |                             |
| col2  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
--查询2019-04-01 00:00:00以来的数据,不包括2019-04-01 00:00:00:
mysql> select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>0;       
+----+---------------------+
| id | col2                |
+----+---------------------+
|  3 | 2019-04-20 00:00:00 |
|  4 | 2019-04-01 14:30:00 |
+----+---------------------+
2 rows in set (0.00 sec)
--查询距离2019-04-01 00:00:00一个月内的数据:
mysql> select * from test where timestampdiff(month,'2019-04-01 00:00:00',col2)=0;
+----+---------------------+
| id | col2                |
+----+---------------------+
|  1 | 2019-03-20 00:00:00 |
|  2 | 2019-04-01 00:00:00 |
|  3 | 2019-04-20 00:00:00 |
|  4 | 2019-04-01 14:30:00 |
+----+---------------------+
4 rows in set (0.00 sec)
--可以看到上面两个查询,有两条重复的数据,分别是id=3和id=4两行
--但是将两个谓词条件却查不到记录:
mysql> select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0;
Empty set (0.00 sec)
--进一步查看执行计划以及优化器内部转化:
mysql> desc select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `sam`.`test`.`id` AS `id`,`sam`.`test`.`col2` AS `col2` from `sam`.`test` where 0 |
+-------+------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--可以看到优化器显示Impossible WHERE,认为谓词条件是始终不成立的,在warning中我们看到谓词条件转化成了where 0,但是我们分开查询是有结果并且有交集的,不应该是这样的结果。
--如果我们将谓词改成timestampdiff(second,'2019-04-01 00:00:00',col2)>=0,就可以查到数据了:
mysql> select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>=0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0;     
+----+---------------------+
| id | col2                |
+----+---------------------+
|  1 | 2019-03-20 00:00:00 |
|  2 | 2019-04-01 00:00:00 |
|  3 | 2019-04-20 00:00:00 |
|  4 | 2019-04-01 14:30:00 |
+----+---------------------+
4 rows in set (0.00 sec)
--但以上数据并不正确,因为2019-03-20 00:00:00这一条数据并不符合timestampdiff(second,'2019-04-01 00:00:00',col2)>=0,但是返回的所有数据都符合timestampdiff(month,'2019-04-01 00:00:00',col2)=0,都在一个月之内。
--那么,我们也看一下优化器到底做了什么:
mysql> desc select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>=0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                    |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `sam`.`test`.`id` AS `id`,`sam`.`test`.`col2` AS `col2` from `sam`.`test` where (timestampdiff(MONTH,'2019-04-01 00:00:00',`sam`.`test`.`col2`) = 0) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--在show warnings中,我们可以看到,优化器把谓词条件timestampdiff(second,'2019-04-01 00:00:00',col2)>=0忽略掉了,只留下timestampdiff(month,'2019-04-01 00:00:00',col2)=0,所以才有上述的返回结果。
--那么,优化器是怎样把谓词条件timestampdiff(second,'2019-04-01 00:00:00',col2)>=0忽略掉的呢,我们进一步追踪一下:
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>=0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test`.`id` AS `id`,`test`.`col2` AS `col2` from `test` where ((timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`) >= 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`) >= 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`) >= 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((0 >= 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`test`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`test`",
"table_scan": {
"rows": 4,
"cost": 1
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 4,
"access_type": "scan",
"resulting_rows": 4,
"cost": 1.8,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 4,
"cost_for_plan": 1.8,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`test`",
"attached": "(timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0)"
}
]
}
},
{
"refine_plan": [
{
"table": "`test`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
--注意transformation部分的constant_propagation,优化器将timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`转化成了常数0,所以该谓词条件转化成了0>=0,条件成立,所以整个where部分取决于(timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0)
--那么回顾前面第一个无法返回结果查询,优化器同样是将timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`转化成了常数0,所以该谓词条件转化成了0>0,条件不成立,而对于and关系,该where条件恒为0,所以执行计划里会显示Impossible WHERE
--这里,我们怀疑对于timestampdiff,当指定某个维度的时间差为0,例如上述例子的timestampdiff(MONTH,'xxxx',`test`.`col2`) = 0,那么,其他维度的时间差一律被MySQL认为是0,例如上述例子的minute
--下面用day,second来验证一下:
mysql> select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0 and timestampdiff(year,'2019-04-01 00:00:00',col2)=0;      
Empty set (0.01 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from test where timestampdiff(second,'2019-04-01 00:00:00',col2)>0 and timestampdiff(month,'2019-04-01 00:00:00',col2)=0 and timestampdiff(year,'2019-04-01 00:00:00',col2)=0
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `test`.`id` AS `id`,`test`.`col2` AS `col2` from `test` where ((timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`) > 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0) and (timestampdiff(YEAR,'2019-04-01 00:00:00',`test`.`col2`) = 0))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`) > 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0) and (timestampdiff(YEAR,'2019-04-01 00:00:00',`test`.`col2`) = 0))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((timestampdiff(SECOND,'2019-04-01 00:00:00',`test`.`col2`) > 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0) and (timestampdiff(YEAR,'2019-04-01 00:00:00',`test`.`col2`) = 0))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((0 > 0) and (timestampdiff(MONTH,'2019-04-01 00:00:00',`test`.`col2`) = 0) and (0 = 0))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": null
}
]
}
}
],
"empty_result": {
"cause": "Impossible WHERE"
}
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
--可以看到,谓词条件被转化成了((0 > 0) and (timestampdiff(DAY,'2019-04-01 00:00:00',`test`.`col2`) = 0) and (0 = 0)),然后无论是second还是year的条件,表中都是有符合的数据的。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/133674.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • window安装maven配置环境变量[通俗易懂]

    window安装maven配置环境变量[通俗易懂]首先去maven官网下载,点击这里去下载页面,下拉选择下图点击下载下载好之后解压出来,然后配置环境变量,在我的电脑-右键-属性-高级系统设置-环境变量然后在系统变量下点击新建变量名:M2_HOME变量值:你下载的maven解压出来的路径,我的如下,复制路径粘贴到变量值里,点击确定再找到系统变量里的:Path在变量值里加入:%M2_HOME%/bin不要…

  • declare-styleable的详细用法

    declare-styleable的详细用法简述declare-styleable的定义使用规则,做好笔记方便自己后期使用也方便他人使用。

  • Ubuntu下的解压缩

    一.命令:.tar 解包:tarxvfFileName.tar打包:tarcvfFileName.tarDirName(注:tar是打包,不是压缩!)—————————————

    2021年12月28日
  • Redis-03Redis数据结构–全局命令及字符串string

    Redis-03Redis数据结构–全局命令及字符串string在了解具体的数据结构类型之前,我们有必要了解下Redis提供的操作key的全局命令、数据结构和内部编码、单线程命令处理机制,都有助于加深对Redis的理解。

  • 修改DeDe标签Pagelist分页样式,自定义分页样式

    修改DeDe标签Pagelist分页样式,自定义分页样式

  • 二进制减法的简单过程

    二进制减法的简单过程二进制减法的运算方法二进制减法有两种运算方法,可以使用向高位借位的办法,也可以将减法转为加法采用借位的办法不是很快捷,比较容易看错,示例如下:1010-0111————0011被减数末尾的0向前一位借1,相当于10(2)-1得到1,倒数第二位被借一位后变0,继续向前一位借,以此来得到结果,如果减数大于被减数,则需将最后的结果按位取反得到结果。另外也可采用转换为加法…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号