大家好,又见面了,我是你们的朋友全栈君。
发现一个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账号...