专栏首页SQL引擎研究记 MySQL SQL表达式计算中一个问题的定位和修复
原创

记 MySQL SQL表达式计算中一个问题的定位和修复

从SQL规范实现层面上讲,MySQL做得不是特别好,有很多不符合SQL规范,或者实现与文档自相矛盾的地方(这方面做得不错当属SQL Server和Oracle,商业软件还是屌)。最近在偶然间发现MySQL SQL引擎的一个魔性的行为,并根据源码探究了一下底层的原因。这里分享给大家,以供大家一乐。

现象

MySQL中有一个内建函数,名为ADDTIME,其作用是官方文档见:ADDTIME官方说明。其中关于第二个参数的说明如下:

ADDTIME(expr1,expr2) ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.

重点在于最后一句。expr2 is a time expression, 那么,当expr2不是一个time expression的时候,行为是怎样的呢?用MySQL 5.7.19版本做实验如下:

mysql> create table a ( a datetime);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into a values('2017-01-01 01:01:01');
Query OK, 1 row affected (0.01 sec)

mysql> select addtime(a, a) from a;
+---------------+
| addtime(a, a) |
+---------------+
| NULL          |   
+---------------+
1 row in set (0.09 sec)

mysql> create table b(a datetime, b time);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into b values("2017-01-01 01:01:01", "01:01:01");
Query OK, 1 row affected (0.01 sec)

mysql> select addtime(a, a) from b;
+---------------+
| addtime(a, a) |
+---------------+
| NULL          |
+---------------+
1 row in set (0.00 sec)

mysql> select addtime(a,b) from b;
+---------------------+
| addtime(a,b)        |
+---------------------+
| 2017-01-01 02:02:02 |
+---------------------+
1 row in set (0.00 sec)

OK, 看来当addtime第二个参数不是TIME类型的时候,ADDTIME返回值为NULL。 但是,偶然间我测试了另一个等价SQL,发现结果却并不符合预期:

mysql> select addtime(a, cast(a as datetime)) from a;
+---------------------------------+
| addtime(a, cast(a as datetime)) |
+---------------------------------+
| 2017-01-01 02:02:02             |
+---------------------------------+
1 row in set (0.02 sec)

讲道理, 根据Table a的Schema(desc 如下), a字段本来就是DATETIME类型,在执行CAST AS DATETIME之后应该还是自己,为啥结果就变了呢?这就是发现的这个魔性的地方。。。

mysql> desc a;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a     | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

分析

目前的各种关系型数据库,其SQL执行引擎万变不离其宗,基本还是沿用所谓的Volcano-Style Execution Engine论文链接)。所谓Volcano-Style Execution Engine, 就是把一个SQL转换成一个SQL算子树,然后以One-Tuple a time的方式执行。其中的表达式计算也是如此。因此,大概可以估计到,在SELECT addtime(a,a) FROM a中,表达式树的结构大概如下图所示:

> ADDTIME ____|____ | | ColumnRef ColumnRef

而在SELECT addtime(a, cast(a as datime)) FROM a中,表达式树的结构大概如下图所示:

> ADDTIME ____|____ | | ColumnRef CAST_AS_DATETIME | ColumnRef

两者相比,后者的表达式树比前者多了一个CAST_AS_DATETIME算子。当然,在如果SQL Optimizer做得比较好,SELECT addtime(a, cast(a as datetime)) FROM a中,可以发现字段a本来就是DATETIME类型,然后优化掉这个CAST_AS_DATETIME算子。遗憾的是,这里MySQL明显没有这样做,因为两个SQL语句表现明显不同。所以,这里大概率问题出在CAST_AS_DATETIME身上。

源码探究

下载MySQL5.7.19版本源码后,首先去找ADDTIME的实现。在sql/item_timefunc.cc2785行中找到其实现,代码如下:

bool Item_func_add_time::val_datetime(MYSQL_TIME *time, uint fuzzy_date)
{
  DBUG_ASSERT(fixed == 1);
  MYSQL_TIME l_time1, l_time2;
  bool is_time= 0;
  long days, microseconds;
  longlong seconds;
  int l_sign= sign;

  null_value=0;
  if (cached_field_type == MYSQL_TYPE_DATETIME)  // TIMESTAMP function
  {
    if (get_arg0_date(&l_time1, fuzzy_date) || 
        args[1]->get_time(&l_time2) ||
        l_time1.time_type == MYSQL_TIMESTAMP_TIME || 
        l_time2.time_type != MYSQL_TIMESTAMP_TIME)
      goto null_date;
  }
  else                                // ADDTIME function
  {
    if (args[0]->get_time(&l_time1) || 
        args[1]->get_time(&l_time2) ||
        l_time2.time_type == MYSQL_TIMESTAMP_DATETIME)
      goto null_date;
    is_time= (l_time1.time_type == MYSQL_TIMESTAMP_TIME);
  }
 \\....other codes

注意注释//ADDTIME function后的if语句:

    if (args[0]->get_time(&l_time1) || 
        args[1]->get_time(&l_time2) ||
        l_time2.time_type == MYSQL_TIMESTAMP_DATETIME)
      goto null_date;

很明显这里实现了返回NULL的逻辑。根据前后两个SQL表达式树的不同结构,猜测在SELECT addtime(a, cast(a as datetime)) FROM a中,args[1]->gettime(&l_time2)返回了false,导致这段逻辑被跳过,从而整个ADDTIME结果非NULL。这和最初的推断:锅改CAST_AS_DATETIME背基本一致。 接着看CAST_AS_DATETIME的定义。在sql/item_timefunc.h中,有相关定义如下:

class Item_datetime_typecast :public Item_datetime_func
{
\\...
}

Item_datetime_typecast类继承了Item_datetime_func类,而这个类实现了get_time方法如下:

class Item_datetime_func :public Item_temporal_func
{
\\....
  bool get_time(MYSQL_TIME *ltime)
  {
    return get_time_from_datetime(ltime);
  }
\\....
}

追溯get_time_from_datetime的实现,在sql/item.cc中:

bool Item::get_time_from_datetime(MYSQL_TIME *ltime)
{
  DBUG_ASSERT(fixed == 1);
  if (get_date(ltime, TIME_FUZZY_DATE))
    return true;
  datetime_to_time(ltime);
  return false;
}

然后其中的datetime_to_time实现在sql/sql_time.h中:

inline void datetime_to_time(MYSQL_TIME *ltime)
{
  ltime->year= ltime->month= ltime->day= 0;
  ltime->time_type= MYSQL_TIMESTAMP_TIME;
}

可以看到,这里的逻辑就是把一个DATETIME类型值的year, month, day字段全部扒光,然后类型改成TIME,就返回完事儿了。因此,在CAST_AS_DATETIME当中,get_time函数是work并且可以返回正确结果的,所以SELECT addtime(a, cast(a as datetime)) FROM a可以计算出结果,并且输出看起来也是完全正确的。。。。

修改/验证

从以上分析,可以知道,要修复上述行为,只要让CAST_AS_DATETIME算子的get_time返回true即可。因此,最简单的办法是重载调类Item_datetime_typecastget_time方法,直接返回true。如下:

bool Item_datetime_typecast::get_time(MYSQL_TIME *ltime)
{
    return true;
}

加上修改后重新编译安装MySQL,运行如下:

mysql> select addtime(a, cast(a as datetime)) from a;
+---------------------------------+
| addtime(a, cast(a as datetime)) |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set (0.00 sec)

可见结果符合预期了。

后记

严格的说,MySQL这个行为并不算违反SQL标准,因为SQL标准在这个地方并没有严格规定,而是“留由实现决定”。但是这里在SQL等价的情况下返回结果不一致,倒是怎么都说不过去的。对这个问题,我在Oracle的官网上也提了一个issue, 看看官方怎么说,然而到目前为止貌似没人鸟我。。。。。

MySQL的SQL引擎在这里是有点偷懒了。按照道理说,一个Builtin 如果只有固定的返回类型(比如CAST_AS_XXX), 那么从基类继承的get_othertypes接口就应该全部继承并disable掉。如果真的有类型转化的需求,应该在Optimizer的Expression Rewrite阶段显式加入类型转换算子来完成。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • python 判断当前时间是否在一个时间范围内

    https://blog.csdn.net/qq_21570029/article/details/83185168

    py3study
  • datetime

    datetime是Python处理日期和时间的标准库。 获取当前日期和时间 我们先看如何获取当前日期和时间: >>> from datetime import ...

    用户1214487
  • Python 处理时间差

    1.计算两个时间相差多少天,或者几个小时,可以使用datetime模块解决这个问题

    我是李超人
  • python的datetime模块处理时

    python的datetime模块主要用来处理时间,里面包含很多类,包括timedelay,date,time,datetime等

    py3study
  • Python中对时间日期的处理方法简单汇总

    这篇文章主要介绍了Python实用日期时间处理方法汇总,本文讲解了获取当前datetime、获取当天date、获取明天/前N天、获取当天开始和结束时间(00:0...

    用户1217611
  • Python常用模块:datetime

    注:Python的timestamp是一个浮点数。如果有小数位,小数位表示毫秒数。

    听着music睡
  • Python如何根据日期判断周几

    https://jingyan.baidu.com/article/4dc40848d2536e88d846f105.html

    py3study
  • python 计算时间差

    其本上常用的类有:datetime和timedelta两个。它们之间可以相互加减。每个类都有一些方法和属性可以查看具体的值,如datetime可以查看:天数(d...

    py3study
  • python3中datetime库详解

    在 Python 文档里,time是归类在Generic Operating System Services中,换句话说, 它提供的功能是更加接近于操作系统层面...

    py3study
  • Python获取当前年月日

    py3study

扫码关注云+社区

领取腾讯云代金券