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)


相关推荐

  • 浅析currentHashmap的理解

    浅析currentHashmap的理解1,currentHashMap的介绍currentHashMap是线程安全并且高效的一种容器,我们就需要研究一下currentHashMap为什么既能够保证线程安全,又可以保证高效的操作currentHashMap使用的原因为什么使用currentHashMap,这时候我们就需要和HashMap以及HashTable进行比较HashMap线程不安全的原因?在多线程的情况下,HashMa…

  • latex表格生成器_latex表格换行

    latex表格生成器_latex表格换行在线生成latex表格代码http://www.tablesgenerator.com/

  • pycharm如何调试代码_pycharm调试debug入门

    pycharm如何调试代码_pycharm调试debug入门1.首先在怀疑出错的代码处的前面设置断点2.点击pycharmdebug按钮3.stepover也就是F8进行单击调试,只有光标在哪一行就是即将运行的代码只有光标跳到下一行,这一行才会执行4.运行到某一个自定义函数def的时候如果想知道里面如何运行单击stepinto(F7)然后继续stepover最后可能返回一个result回到main函数继续stepover。5.如果是嵌套函数,函数里面还有别的自定义函数可以运行到那一行时继续stepinto6.如.

  • 如何用决策树模型做数据分析报告_决策树实例

    如何用决策树模型做数据分析报告_决策树实例什么是决策树?决策树模型本质是一颗由多个判断节点组成的树。在树的每个节点做参数判断,进而在树的最末枝(叶结点)能够对所关心变量的取值作出最佳判断。通常,一棵决策树包含一个根结点,若干内…

  • 银河麒麟v10.1_银河麒麟v10系统

    银河麒麟v10.1_银河麒麟v10系统银河麒麟v10下载(服务器版桌面版)-2022-03-16更新银河麒麟桌面操作系统V10和银河麒麟高级服务器操作系统V10,分别推出了飞腾、鲲鹏、龙芯、申威、海光、兆芯六个版本银河麒麟高级服务器操作系统V10银河麒麟桌面操作系统V10…

    2022年10月16日
  • Oracle 11g下载及安装

    Oracle 11g下载及安装Oracle11g下载及安装前言Oracle11g下载Oracle11g安装1.引入库2.读入数据总结前言因为笔者公司所用数据库是oracle,新同事来了都会习惯下重装电脑,所以记录下oracle的下载及安装。Oracle11g下载进入oracle官网,看到如下视图,点击进入oracle官网点击Products,选择OracleDatabase此时进入oracle数据库的详情页面,选择一个长期发行版本进行下载,此时长期发行的版本为19c选择下载19c的下载按钮,进入下载

发表回复

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

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