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

原创

felixxdu

发布于 2019-02-25 16:14:14

7440

从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_typecast的get_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阶段显式加入类型转换算子来完成。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server