sql: investigate using arbitrary precision constants

[petermattis]

Currently, SELECT 1 + 1.0 does not work because 1 is parsed as an int and 1.0 is parsed as a float and we disallow implicit conversions between int and float. An alternative is to borrow from the Go playbook and support arbitrary precision constants and allow implicit conversions of these constants and expressions involving them when the conversion does not lose precision. This would be quite a bit more involved than the current system, making this issue low priority. Can draw inspiration and ideas from https://godoc.org/golang.org/x/tools/go/exact.

[rlowe]

While SQL:2011 basically leaves much of this as an implementation detail ...

"Every number has a precision (number of digits), and exact numeric types also have a scale (digits after the radix point). Arithmetic operations may be performed on operands of different or the same numeric type, and the result is of a numeric type that depends only on the numeric type of the operands. If the result cannot be represented exactly in the result type, then whether it is rounded or truncated is implementation-defined. An exception condition is raised if the result is outside the range of numeric values of the result type, or if the arithmetic operation is not defined for the operands."

Users of, operators of, and software powered by relational data stores really expect arithmetic functions like this to just work. For example:

mysql> SELECT 1 + 1.0;
+---------+
| 1 + 1.0 |
+---------+
|     2.0 |
+---------+
1 row in set (0.00 sec)

postgres=# SELECT 1 + 1.0 AS math;
 math 
------
  2.0
(1 row)

UPDATE: Formatting is hard

[nvanbenschoten]

Implemented in #6358.