There has recently been a change in behavior in Hive wrt doubles and decimals, HIVE-13380; where the literals were changed to be double by default, resulting in some unexpected behavior when comparing decimal columns with arithmetic expressions on literals. Right now it has been reverted from 2.1, it’s still there on master until we decide what to do.
According to SQL92 (I don’t have access to a later one), section 5.3 <exact numeric literal> ::= <unsigned integer> [ <period> [ <unsigned integer> ] ] | <period> <unsigned integer> ... 13)The data type of an <exact numeric literal> is exact numeric. From previous comments there, exact-numeric would basically be decimal in this case. Approximate (basically, float/double in this case) literal is defined as <approximate numeric literal> ::= <mantissa> E <exponent> Then, in 6.12, the expression (at least the arithmetic) on two exacts needs to have the exact results; if either side is approximate, the result is approximate. However, some RDBMS-es apparently prefer double over decimal. I think we should go according to SQL92, revert the patch also from master, and also potentially investigate ANSI SQL compatibility for existing type resolution in other places. Opinions, suggestions, comments?