从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.cc
2785行中找到其实现,代码如下:
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 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。