timestampdiff的一个BUG

清疚 2022-02-25 10:28 288阅读 0赞

发现一个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的条件，表中都是有符合的数据的。