date_add和datediff同时使用时，返回的结果会出错

unknowwhite · 2020 年11 月 26 日 07:40

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：5.7.25-TiDB-v3.0.3
【问题描述】：date_add和datediff同时使用时，返回的结果会出错

诊断过程如下：
1.单独运行
在mysql下执行：
select date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY)
得到结果 2020-11-24
在tidb下执行
得到结果 2020-11-24

结果一致，但是放在语句中就不行了

2.放在语句中执行
select ‘1’ as joind,count(distinct if(fideo_v1_device.lib = ‘iOS’,if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=’’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as iOS,
count(distinct if(fideo_v1_device.lib in (‘MiniProgram’,‘Android’),if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=’’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as Android,
count(distinct if(fideo_v1_device.lib = ‘js’,if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=’’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as js
from fideo_v1_device left join fideo_v1_user on fideo_v1_device.distinct_id = fideo_v1_user.original_id and fideo_v1_device.lib=fideo_v1_user.lib where FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) <=DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY) and FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) >=date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY)

a.把斜体的部分和斜体加粗的部分都使用固定日期 2020-11-24 得到正确的结果
1 321 36 17390
b.单独使用date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY) 替换斜体字部分，得到正确的结果
1 321 36 17390
c.部分混合使用FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) >=date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL 0 DAY) 替换粗斜体部分，得到错误结果
1 227 16 8
d.部分混合使用FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) >=date_add(‘2020-11-24’,INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY)替换粗斜体部分，得到错误结果
1 227 16 8
e.完全使用拼接 FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) >=date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY)替换粗斜体部分，得到错误结果
1 227 16 8

证明问题是发生在date_add和datediff同时使用时，或两个date_add叠加时。

查询所使用的表为
CREATE TABLE fideo_v1_user (
distinct_id varchar(255) NOT NULL,
lib varchar(255) NOT NULL,
map_id varchar(255) NOT NULL,
original_id varchar(255) NOT NULL,
user_id varchar(255) DEFAULT NULL,
all_user_profile json DEFAULT NULL,
created_at int(11) DEFAULT NULL,
updated_at int(11) DEFAULT NULL,
PRIMARY KEY (distinct_id,lib,map_id,original_id),
KEY distinct_id (distinct_id),
KEY map_id (map_id),
KEY original_id (original_id),
KEY distinct_id_lib (distinct_id,lib)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE fideo_v1_device (
distinct_id varchar(255) NOT NULL,
lib varchar(255) DEFAULT NULL,
device_id varchar(255) DEFAULT NULL,
manufacturer varchar(255) DEFAULT NULL,
model varchar(255) DEFAULT NULL,
os varchar(255) DEFAULT NULL,
os_version varchar(255) DEFAULT NULL,
ua_platform varchar(1024) DEFAULT NULL,
ua_browser varchar(1024) DEFAULT NULL,
ua_version varchar(1024) DEFAULT NULL,
ua_language varchar(1024) DEFAULT NULL,
screen_width int(11) DEFAULT NULL,
screen_height int(11) DEFAULT NULL,
network_type varchar(255) DEFAULT NULL,
user_agent varchar(2048) DEFAULT NULL,
accept_language varchar(255) DEFAULT NULL,
ip varchar(255) DEFAULT NULL,
ip_city json DEFAULT NULL,
ip_asn json DEFAULT NULL,
wifi varchar(20) DEFAULT NULL,
app_version varchar(255) DEFAULT NULL,
carrier varchar(255) DEFAULT NULL,
referrer text DEFAULT NULL,
referrer_host varchar(2048) DEFAULT NULL,
bot_name varchar(255) DEFAULT NULL,
browser varchar(255) DEFAULT NULL,
browser_version varchar(255) DEFAULT NULL,
is_login_id varchar(255) DEFAULT NULL,
screen_orientation varchar(255) DEFAULT NULL,
gps_latitude decimal(11,7) DEFAULT NULL,
gps_longitude decimal(11,7) DEFAULT NULL,
first_visit_time datetime DEFAULT NULL,
first_referrer text DEFAULT NULL,
first_referrer_host varchar(1024) DEFAULT NULL,
first_browser_language varchar(1024) DEFAULT NULL,
first_browser_charset varchar(1024) DEFAULT NULL,
first_search_keyword varchar(1024) DEFAULT NULL,
first_traffic_source_type varchar(1024) DEFAULT NULL,
utm_content varchar(2048) DEFAULT NULL,
utm_campaign varchar(2048) DEFAULT NULL,
utm_medium varchar(2048) DEFAULT NULL,
utm_term varchar(2048) DEFAULT NULL,
utm_source varchar(2048) DEFAULT NULL,
latest_utm_content varchar(2048) DEFAULT NULL,
latest_utm_campaign varchar(2048) DEFAULT NULL,
latest_utm_medium varchar(2048) DEFAULT NULL,
latest_utm_term varchar(2048) DEFAULT NULL,
latest_utm_source varchar(2048) DEFAULT NULL,
latest_referrer varchar(2048) DEFAULT NULL,
latest_referrer_host varchar(2048) DEFAULT NULL,
latest_search_keyword varchar(2048) DEFAULT NULL,
latest_traffic_source_type varchar(255) DEFAULT NULL,
created_at int(11) DEFAULT NULL,
updated_at int(11) DEFAULT NULL,
PRIMARY KEY (distinct_id),
KEY distinct_id+lib (distinct_id,lib),
KEY utm_campaign (utm_campaign),
KEY utm_source (utm_source),
KEY utm_medium (utm_medium),
KEY utm_term (utm_term),
KEY utm_content (utm_content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

数据的话，是这个程序产生的https://github.com/white-shiro-bai/ghost_sa

yilong · 2020 年11 月 26 日 09:20

请问，是否方便升级到最新版本 4.0.8 试试？看您的版本还是 3.0.3 ，中间修复过很多 bug,先排查一下。

unknowwhite · 2020 年11 月 26 日 10:07

12月考虑升吧。毕竟生产库，能不动尽量不动。

yilong · 2020 年11 月 27 日 03:47

好的，那麻烦您反馈下 explain analyze sql 的各个结果，我们看下执行计划具体在哪里有问题，多谢。

robi · 2020 年11 月 27 日 06:33

hi 请提供下 explain analyze 的执行结果谢谢

unknowwhite · 2020 年11 月 30 日 02:23

这两个分别是计算错误的和计算正确的。
区别的就是正确的日期直接写了’2020-11-24’。错误的那个日期是通过date_add和date_diff计算而来的。

EXPLAIN
select ‘1’ as joind,count(distinct if(fideo_v1_device.lib = ‘iOS’,if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=‘’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as iOS,
count(distinct if(fideo_v1_device.lib in (‘MiniProgram’,‘Android’),if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=‘’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as Android,
count(distinct if(fideo_v1_device.lib = ‘js’,if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=‘’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as js
from fideo_v1_device left join fideo_v1_user on fideo_v1_device.distinct_id = fideo_v1_user.original_id and fideo_v1_device.lib=fideo_v1_user.lib where FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) <=DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY) and FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) >=date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY)

EXPLAIN
select ‘1’ as joind,count(distinct if(fideo_v1_device.lib = ‘iOS’,if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=‘’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as iOS,
count(distinct if(fideo_v1_device.lib in (‘MiniProgram’,‘Android’),if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=‘’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as Android,
count(distinct if(fideo_v1_device.lib = ‘js’,if(fideo_v1_user.original_id is not null and fideo_v1_user.original_id !=‘’,fideo_v1_user.distinct_id,fideo_v1_device.distinct_id),null)) as js
from fideo_v1_device left join fideo_v1_user on fideo_v1_device.distinct_id = fideo_v1_user.original_id and fideo_v1_device.lib=fideo_v1_user.lib where FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) <=‘2020-11-24’ and FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) >=‘2020-11-24’

robi · 2020 年11 月 30 日 05:12

hi 可以帮忙给下具体导致结果错误行的 created_time 值吗？看上去和 created_time 的具体值在不同类型推断结果下的计算有关，需要帮忙看下具体导致问题的 created_time 值

即这样：

CREATE TABLE `t` (
  `created_at` int(11) DEFAULT NULL
)

然后插入有问题的 created_time 具体值：

insert into t select [具体 int 值];

最后执行：

select * from t where FROM_UNIXTIME(created_at,'%Y-%m-%d') >= '2020-11-24';
select * from t where FROM_UNIXTIME(created_at,'%Y-%m-%d') >= date_add(DATE_ADD('2020-11-25',INTERVAL -1 DAY),INTERVAL DATEDIFF('2020-11-25','2020-11-25') DAY);

应该能找到具体导致两个查询结果不同的 [具体 int 值] 谢谢

unknowwhite · 2020 年11 月 30 日 06:08

不知道是不是我理解有错误，结果不是很理想。
受你的启发，做了如下测试：

直接使用对应日期数了数据条数：

select count(*) from fideo_v1_device where FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) <=‘2020-11-24’ and FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) >=‘2020-11-24’;

使用相对日期数了数据条数：

select count(*) from fideo_v1_device where FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) <=DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY) and FROM_UNIXTIME(fideo_v1_device.created_at,’%Y-%m-%d’) >=date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY);

两边结果一致

然后我又找了一下两种取数方式取到的created_at的差异，发现是没差异的。

SELECT
a.,
b.
FROM
(
SELECT
fideo_v1_device.created_at AS created_at
FROM
fideo_v1_device
WHERE
FROM_UNIXTIME( fideo_v1_device.created_at, ‘%Y-%m-%d’ ) <= ‘2020-11-24’ AND FROM_UNIXTIME( fideo_v1_device.created_at, ‘%Y-%m-%d’ ) >= ‘2020-11-24’
) a
LEFT JOIN (
SELECT
fideo_v1_device.created_at AS created_at
FROM
fideo_v1_device
WHERE
FROM_UNIXTIME( fideo_v1_device.created_at, ‘%Y-%m-%d’ ) <= DATE_ADD( ‘2020-11-25’, INTERVAL - 1 DAY ) AND FROM_UNIXTIME( fideo_v1_device.created_at, ‘%Y-%m-%d’ ) >= date_add( DATE_ADD( ‘2020-11-25’, INTERVAL - 1 DAY ), INTERVAL DATEDIFF( ‘2020-11-25’, ‘2020-11-25’ ) DAY )
) b ON a.created_at = b.created_at
WHERE
b.created_at IS NULL

所以似乎无法找到这样的int值用来插入表t。

robi · 2020 年11 月 30 日 06:25

另外使用 ‘2020-11-25’ 代替条件看上去不是一个很好的选择，从 plan 目前的条件比较变化为“字符串比较” 而不是日期比较

个人感觉更好的处理方式应该是将保持 >= 比较操作的左边为 int 的 created_at 列，然后将右边的列转换到 int

这样可以避免字符串比较的问题，如果 created at 列有 index 也可以用上。。

不过还是希望您提供下具体有问题行的 created at 列指我们看下类型转换比较问题

robi · 2020 年11 月 30 日 07:07

这两个 count 数居然一样不太科学可以帮忙再给下这两个 count sql 的 explain analyze select … 结果吗

unknowwhite · 2020 年11 月 30 日 07:25

确实不太科学。刚你提那个方法的时候我以为见到亮了呢

explain select count() from fideo_v1_device where FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) <=‘2020-11-24’ and FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) >=‘2020-11-24’;

image1738×126 7.68 KB

explain select count() from fideo_v1_device where FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) <=DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY) and FROM_UNIXTIME(fideo_v1_device.created_at,‘%Y-%m-%d’) >=date_add(DATE_ADD(‘2020-11-25’,INTERVAL -1 DAY),INTERVAL DATEDIFF(‘2020-11-25’,‘2020-11-25’) DAY);

image1779×138 7.56 KB

unknowwhite · 2020 年11 月 30 日 07:26

这个方法好。我试试看能不能绕开BUG。

unknowwhite · 2020 年11 月 30 日 07:31

该方法成功绕开BUG了。

robi · 2020 年11 月 30 日 13:49

绕过不是本意，我们还是希望能找到根本原因，不能让用户后续再遇到类似问题

从单独读 device 表的 plan 看在原始 SQL 在 device 侧能 selection 出的数据结果也是一致的，所以应该不是表达式过滤的问题。。。

再次分析了前面两个带 join 的两个 plan 看原因可能是，表达式变化后 planner 选择了不同的 join 方式

在写死 2020-11-24 时走的是 left out join，但使用日期计算后走的 inner join 。。

所以如果有“有 device 记录没有对应的 user 记录“的时候就会导致少统计。。。

所以目前问题就是“使用日期计算后为什么 planner 会错误的的做出 inner join”。。

有空希望您帮忙导出下这 device 和 user 表的统计信息 https://docs.pingcap.com/zh/tidb/stable/statistics#导出统计信息

感谢感谢～

unknowwhite · 2020 年12 月 1 日 01:59

请问导出的时候
{tidb-server-status-port}
这个变量默认值是多少？是需要开什么服务才有，还是默认就开启？

另外原因上我觉得你分析的是对的。确实有device有记录，但是user没记录的情况。

robi · 2020 年12 月 1 日 06:08

那个端口默认是 10080

可以通过 ps aux | grep tidb-server 查看，默认 tidb 会监听两个端口一个 mysql 协议一个 http 协议

unknowwhite · 2020 年12 月 1 日 06:45

[root@tidb1 ~]# ps aux | grep tidb-server
tidb 6310 14.0 3.4 16326168 1125708 ? Ssl Nov12 3876:14 bin/tidb-server -P 4000 --status=10080 --advertise-address=172.18.25.83 --path=172.18.25.83:2379,172.18.25.81:2379,172.18.25.84:2379 --config=conf/tidb.toml --log-slow-query=/data1/log/tidb_slow_query.log --log-file=/data1/log/tidb.log
root 31680 0.0 0.0 112712 992 pts/0 S+ 14:37 0:00 grep --color=auto tidb-server

这步可见 10080端口是开放的

[root@tidb1 export]# wget http://172.18.25.83:10080/stats/dump/event/fideo_v1_device
–2020-12-01 14:42:51-- http://172.18.25.83:10080/stats/dump/event/fideo_v1_device
Connecting to 172.18.25.83:10080… connected.
HTTP request sent, awaiting response… 400 Bad Request
2020-12-01 14:42:51 ERROR 400: Bad Request.

但是请求该接口的时候会有请求错误。试过localhost和127.0.0.1也不行。

robi · 2020 年12 月 1 日 07:32

hi 可以帮忙把 content 也输出下应该是能访问但发生了什么错误

wget --content-on-error http://172.18.25.83:10080/stats/dump/event/fideo_v1_device

unknowwhite · 2020 年12 月 1 日 08:29

fideo_v1.7z (614.3 KB)
OK来啦。包里两个json都有啦。错误是我的问题。库名打错了。

robi · 2020 年12 月 1 日 09:54

排查后发现是一个已知的问题。。

已经被 https://github.com/pingcap/tidb/pull/12701 修复了，升级新版可以让原来的 sql 也正常工作

修复最低版本是 3.0.5 不过建议升级到 3.0.x 最新版本，抱歉给您带来的不便