解决Hive里date_add函数的问题

一、坑的背景

　　今天有同事反映，他的一个sql，在Hive里查不出结果，而在Spark里查出结果，非常诡异。于是拿他的SQL来实验：

select *
from table_a a
inner JOIN 
  (
    SELECT  *
    FROM 
    (
    SELECT  trim(sn) sn
         ,trim(bn) AS bn 
         ,row_number () over (PARTITION BY sn ORDER BY sn DESC) rank
    FROM table_b
    ) tmp
    WHERE tmp.rank = 1  
  ) b
  ON trim(a.i) = trim(b.sn) 
inner join table_c c on b.bn=c.bn
where a.jhdate = 'null' and date_add(a.fgdate,1)<c.btime 
limit 100;

二、定位问题

　　分别在Hive里和在Spark里执行，发现真的如同事所说，Hive里查不出数据，而Spark里能查出数据。

　　起初怀疑是jhdate＝‘null'的条件，在hive与spark里的判空的语法不同导致的，遂改为：

where a.jhdate is null and date_add(a.fgdate,1)<c.btime

　　新的SQL在Hive里依然出不出数据。于是想将table_a表里的数据导出来，人肉看一下jhdate里是不是有脏数据导致hive计算出错，检索不出数据。结果一查table_a表有380多万数据，这个方法不现实。于是对table_a的jhdate字段进行groupby，结果傻眼了，的确该字段就是两种取值，一种是日期（如：2022-03-18），另一种就是字符串的“null”。所以a.jhdate='null'的条件没毛病。

 　　思考片刻，既然不是这个where条件出的问题，那就是另外一个条件出的问题。于是就对date_add(a.fgdate,1) < c.bttime这个条件进行验证测试。由于涉及到了table_a与table_c，中间需要table_b做关联查询，所以此时可以从结果出发进行推导。将spark里能查到的数据，分别在a、b、c三张表里查出来

select jhdate, fgdate
from table_a
where imei='60****DV'

+-------------+-------------+
|   jhdate    |   fgdate    |
+-------------+-------------+
| null        | 2021-12-13  |
+-------------+-------------+


select sn, bn
from table_b
where sn='60****DV'

+----------------+-------------------+
|       sn       |         bn        |
+----------------+-------------------+
|   60****DV     |      td****vh     |
+----------------+-------------------+


select btime
from table_c
where btime='td****vh';

+------------------------+
|         btime          |
+------------------------+
| 2021-12-09 16:18:39.0  |
| 2021-12-14 20:30:52.0  |
+------------------------+

　　结果一目了然，问题就出在 fgdate的“date_add('2021-12-13',1)”与btime的“2021-12-14 22:30:52.0”的比较里了！Spark认为条件是true的，而Hive认为是false！这明显是Hive的坑，返回true才是我们期望的结果。

三、解决方案

 　　问题找到了，接下来怎么改呢？由于没有源码，只能猜个大概，是hive在执行date_add之后，隐式地将日期字段类型转为字符串类型，与字符串类型的btime做比较，在隐式转换过程中出现了偏差。于是，我们可以利用concat，手动地将fgdate后面的时、分、秒也补全，这样在比较大小的时候，两边都是字符串了，就会按照字符串的比较方式进行比较：

select concat(date_add('2021-12-13',1),' 00:00:00.0') < '2021-12-14 20:30:52.0'

　　这样，无论hive还是spark，返回的结果都是true了。要不是业务偶然发现这个坑，都不知道数据不准的问题会持续多久。

=