Hive日期比较查询中的异常行为及原因疑问

首先，我们来拆解你遇到的第三个查询结果为0的核心原因：Hive在不同类型比较时的隐式转换规则，以及to_date()函数的行为差异。

1. 第三个查询的本质：字符串转日期失败导致全量NULL #

当你执行select count(*) from somedates where datecol > to_date('2017-01-01');时，to_date('2017-01-01')返回的是DATE类型的值2017-01-01。而你的datecol是STRING类型，值为2017-08-29 23:25:16.0这种带时分秒和小数点的格式。

Hive在进行跨类型比较时，会遵循把字符串转换为日期类型的规则（而不是反过来把日期转成字符串）。但Hive默认的日期解析格式是yyyy-MM-dd，你的datecol字符串包含了时分秒和小数点，无法被正确解析为DATE类型，会被转换为NULL。

而NULL和任何值进行比较的结果都是FALSE，所以所有行都不满足条件，最终count结果为0。

2. 为什么你的测试语句select '2017-01-01' > to_date('2016-01-01')返回true？ #

因为这里的字符串'2017-01-01'完全符合Hive默认的日期格式yyyy-MM-dd，可以被成功转换为DATE类型2017-01-01。此时两个DATE类型的值比较，2017-01-01确实大于2016-01-01，所以返回true。

3. 验证这个逻辑 #

你可以执行以下语句验证转换结果：

-- 带时分秒的字符串转DATE，返回NULL
select cast('2017-08-29 23:25:16.0' as date);

-- 纯日期字符串转DATE，返回正常日期
select cast('2017-01-01' as date);

4. 前两个查询为什么正常？ #

• 第一个查询：datecol > '2017-01-01'是字符串和字符串的字典序比较。'2017-08-29...'按字典序确实大于'2017-01-01'，所以能正确统计到9行。
• 第二个查询：to_date(datecol)会自动从带时分秒的字符串中提取日期部分（to_date()函数可以处理yyyy-MM-dd HH:mm:ss.S格式的字符串），返回DATE类型的2017-08-29，再和to_date('2017-01-01')的DATE值比较，结果正确。

总结 #

第三个查询的异常是因为字符串转日期失败产生NULL，而你忽略了Hive跨类型比较时的隐式转换规则，以及datecol的字符串格式无法被默认日期解析规则识别的问题。

内容的提问来源于stack exchange，提问作者Dinesh Raj