Stamatis Zampetakis created HIVE-24528:
------------------------------------------
Summary: Wrong implicit type conversion when comparing decimals
and strings
Key: HIVE-24528
URL: https://issues.apache.org/jira/browse/HIVE-24528
Project: Hive
Issue Type: Bug
Components: HiveServer2
Affects Versions: 2.3.0
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
In many cases when comparing decimals and strings (literals/columns) the
comparison is done using doubles which can create some quite unexpected results
in the answers of queries.
{code:sql}
create table t_str (str_col string);
insert into t_str values ('1208925742523269458163819');
select * from t_str where str_col=1208925742523269479013976;
{code}
The SELECT query brings up one row while the filtering value is not the same
with the one present in the string column of the table. The problem is that
both types are converted to doubles and due to loss of precision the values are
deemed equal.
The same happens during the join of a decimal with a string type.
{code:sql}
create table t_dec (dec_col decimal(25,0));
insert into t_dec values (1208925742523269479013976);
select * from t_dec inner join t_str on dec_col=str_col;
{code}
The join result contains one row although the values are not equal.
Implicit type conversions are working differently in every DBMS and for some of
them (e.g., mysql) the above behavior is normal or not allowed at all (e.g.
Postgres).
In the past, Hive used to compare decimal with string columns by converting to
decimals but this behavior changed in 2.3.0 (with HIVE-13380). It seems that
this behavior change was not intentional since following jiras (e.g.,
HIVE-18434) imply that comparison of decimals with strings should be done using
decimals. Since decimal is an exact numeric it appears a more appropriate type
for comparing decimals and strings.
The goal of this issue is to change the implicit conversion of decimals with
strings to doubles and use decimals instead.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
