Rules for determining the type of a numeric result when parsing, casting to a number, or arithmetic operations

[ImeevMA]

Hi all! With addition of DECIMAL I would like to suggest rules for determining the type of number in the following cases:

1. During parsing
2. During an explicit or implicit cast to number (without specifying INTEGER, DECIMAL or DOUBLE)
3. The result of arithmetic operations

The rules that I suggest:
Parsing:

1. At first we should try to parse the value as INTEGER.
2. If the value cannot be parsed as INTEGER, try to parse the value as DECIMAL.
3. If the value cannot be parsed as DECIMAL, an error should be thrown.

Explicit or implicit cast to number (without specifying INTEGER, DECIMAL, or DOUBLE):

1. Try converting the value to INTEGER.
2. If the value cannot be converted to INTEGER, try converting the value to DECIMAL.
3. If the value cannot be converted to DECIMAL, an error should be thrown.

Arithmetic operations (after implicit cast to number):

1. If one of the operands is DOUBLE, the result is DOUBLE.
2. If one of the operands is DECIMAL and the other is DECIMAL or INTEGER, the result is DECIMAL.
3. If both operands are INTEGERs, the result is INTEGER.

So, in general we try to work with numbers with fixed point where possible.

Also, be aware that if these rules will have big impact on performance, they may change.

[ImeevMA]

What I found for other verdors after some experiments:

1. PostgreSQL:
   1. Numbers of type "\d+" parsed as INTEGER if possible, otherwise as DECIMAL
   2. Otherwise numbers parsed as DECIMAL
      DECIMAL there is big enough to hold any INTEGER or DOUBLE values losslessly

I think, that for for arithmetic operations rules similar to suggested:
- "select 1000000000 * 1000000000 from t;" throws an error.
- "select (1e37 + 1) - 10000000000000000000000000000000000000 from t;" returns 1.
- "select (CAST(1e37 AS float) + 1) - 10000000000000000000000000000000000000 from t;" returns 0.

2. MS SQL Server 2017:
   1. Numbers of type "\d+" parsed as INTEGER if possible, otherwise as DECIMAL
   2. Numbers of type "\d+.\d*" parsed as DECIMAL
   3. Numbers of type "\d+.?\d*e[+-]?\d+" parsed as DOUBLE

I think, that for for arithmetic operations rules similar to suggested:
- "select 1000000000 * 1000000000 from t;" throws an error.
- "select (1e37 + 1) - 10000000000000000000000000000000000000 from t;" returns 0.

3. MySQL 5.6:
   1. Numbers of type "\d+.?" parsed as DECIMAL
   2. Numbers of type "\d+.?\d*e[+-]?\d+" parsed as DOUBLE

I think, that for for arithmetic operations rules similar to suggested:
- "select (1e37 + 1) - 10000000000000000000000000000000000000 from t;" returns 0.

[pgulutzan]

Two years ago I indicated what I was expecting in #4415 "Implement DECIMAL data type".

[ImeevMA]

Could you please express your position again.

[pgulutzan]

Okay. There are several matters that I mentioned in that comment but I'll try to stay on your stated topic.

1. Literal values which contain decimal points, but not exponents, will be DECIMAL.
   This applies not only to 5.5 but also to 5. and .5. In future to specify that 5.5 is DOUBLE you
   will have to say 5.5E0. This is a behaviour change and this is not what happens in Lua. However,
   this was discussed and I made sure to add a Limitations note in the SQL manual:
   https://www.tarantool.io/en/doc/latest/reference/reference_sql/sql_user_guide/#literals
   "It is non-standard to say that any number which contains a period has data type = DOUBLE."

2. As Mergen Imeev said, literal values which are outside the INTEGER range will be DECIMAL,
   even if they do not contain a decimal point. Although this is not required by the standard,
   it is like what the DB2 manual
   https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000731.html?pos=ant."2
   says: "A constant that is defined outside the range of big integer values is considered a decimal constant".
   For another example, in MySQL, 18446744073709551615 is BIGINT UNSIGNED, 18446744073709551616 is DECIMAL(20,0).
   (Assume that we will never have a separate BIGINT data type.)

3. Literal values which are outside the DECIMAL range (38 digits) will be an error.
   The error will presumably be like the old one for integers in /box/sql/mem.c:
   diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed");
   By the way, in my comment on March 21 2019 in sql: support -2^63 .. 2^64-1 integer type #3810 I said:
   I suggest changing the error message from "integer is overflowed" to "integer overflow".
   I still hope that someday this will happen.
   Two other occurrences of "is overflowed" in box/errcode.h should also be changed.

4. The result of adding DECIMAL to DOUBLE has type DOUBLE.
   This is consistent with the standard, which says
   "If the declared type of either operand of a dyadic arithmetic operator is approximate numeric,
   then the declared type of the result is an implementation-defined approximate numeric type."
   It also makes sense to me: why should adding exact to inexact cause an exact result?
   Unfortunately this is another difference between SQL and Lua, as our manual says:
   "If an operation has both decimal and non-decimal operands, then the non-decimal operand is converted to decimal before the operation happens."
   https://www.tarantool.io/ru/doc/latest/reference/reference_lua/decimal/
   Illustration with Tarantool/Lua:

  tarantool> 1.6666666666667 * 6
  ---
  - 10
  ...
  tarantool> 1.6666666666667 * decimal.new(6)
  ---
  - 10.0000000000002

But we are already doing the right thing with INTEGER and DOUBLE:

tarantool> box.execute([[SELECT 1.6666666666667 * 6;]])
---
- metadata:
  - name: COLUMN_1
    type: double
  rows:
  - [10]

Therefore I do not think this will be controversial.

5. Explicit casts from DECIMAL, such as SELECT CAST('1.0' AS DEC);, are of course okay.
   Implicit casts, such as SELECT '1.0' = 1.0;, are of course (in my view) a
   mistake. However, I realize that the mistake was done with INTEGER, and
   if we forbid it for DECIMAL then somebody will think we're inconsistent.

6. Explicit casts to DECIMAL, such as SELECT CAST(1.0 AS STRING);, are of course okay.
   I do wonder, though: should the result be "1.0" or should the result be "1"?
   I think that either result is okay.

7. I wrote:
   "SQL functions which expect numbers will all work with decimal values."
   I should have clearly said: sometimes that means that we'll accept nonsense,
   for example ZEROBLOB(1.5), and do rounding if an integer is expected.
   I think that any other result is okay.

8. The result of TYPEOF() is 'decimal'.

9. The result data type of CASE with INTEGER and DOUBLE, currently, is:

tarantool> box.execute([[SELECT TYPEOF(CASE 1 WHEN 1 THEN 1.0 WHEN 2 THEN 11 END);]])
---
- metadata:
  - name: COLUMN_1
    type: string
  rows:
  - ['double']

This happens to be correct, but is not happening because DOUBLE has priority.
This is not 100% standard, but I think it is the Tarantool way to do
because somewhere long ago we decided that it would be like SCALAR,
and therefore the data type of the returned value -- which usually
cannot be determined until execution time -- is correct.
However, if anyone is interested in changing to follow the standard rule,
for CASE or for other places where data types are combined, then the
result of CASE x WHEN 1 THEN 1.0 WHEN 2 THEN 11 END will be DECIMAL
if x is 1, and DECIMAL if x is 2, due to SQL/Foundation section
"Result of data type combinations".

10. Tarantool allows non-standard integer literals that begin with 0x or 0X,
    for example 0X55. I suppose that such literals are DECIMAL
    if they are outside the INTEGER range. Currently, if I say
    SELECT 0x10000000000000000; I get:
    Error . Hex literal 0x10000000000000000 length 17 exceeds the supported limit (16) (0.0 seconds)
    I suppose that this can change, length 38 is okay, it is DECIMAL.

[ImeevMA]

Thank you for the answer!

1. Agree. Though, I thought to make values with exponent to be DECIMAL, but make them DOUBLE is also an option. @tsafin, what do you think?
2. Agree.
3. Agree. I have nothing to say about text of the error. Maybe @sergos can say something about out plans about this.
4. Agree.
5. I believe here should be "to decimal". Explicit cast to decimal from string should be fine. As for implicit cast, than it should be removed completely for comparison, but for any other operations it will be according to our RFC "Consistent Lua/SQL types".
6. I believe here should be "from decimal". Also, I think that CAST(1.0 AS STRING) will be '1.0'.
7. I think that currently we decided that there won't be implicit cast from DECIMAL/DOUBLE to INTEGER and in any other combination, when original number is different from the result. For example DECIMAL 1.5 cannot be implicitly cast to INTEGER 1. No sure though, let's ask @tsafin. For explicit cast this rule is not valid, for example CAST(1.5 AS INTEGER) will be 1.
8. Agree.
9. Not sure what is right, but I believe we have ability to implicitly cast arguments to result type here. Could you create an issue?
10. Didn't think about this. Actually I am not sure that 0xFFFF should be DECIMAL, I think it is better to ask @tsafin

[pgulutzan]

Thanks for reading. Just some little details now.
5, 6, You are right, I should have written "to DECIMAL" and "from DECIMAL".
6. If CAST(1.0 AS STRING) will be '1.0', this will be another
difference from Lua, which says tostring(decimal.new(1.0)) is '1'.
I think something is wrong with Lua here. Does anyone remember whether there
has been a behaviour change, so that Lua decimal module discards trailing 0s now?
7. tsafin was CCed in the email thread "Implicit cast for assignment between
numeric types and type mismatch error description" in June 2020 so he will know
what we thought then.
8. I will only create an issue if somebody definitely agrees that
"the result of CASE x WHEN 1 THEN 1.0 WHEN 2 THEN 11 END will be DECIMAL
if x is 1, and DECIMAL if x is 2". You can see that this only works when all
possibilities are numeric.
10. 0xFFFF is INTEGER, the suggestion is that 0x10000000000000000 is DECIMAL.

[ImeevMA]

6. I got this:

tarantool> tostring(require('decimal').new('1.000'))
---
- '1.000'
...

Maybe I did something differently from you? I believe that DECIMAL in Lua does not discards trailing 0s until their number hit the limit (total number of digits no more 38).
8. I see. Thank you!
10. I mean, for me hex literals looks natural for integer, but not so natural for decimal. But this is just my thoughts, I believe you are right and there is nothing that prevents us from making 0x10000000000000000 to be DECIMAL.

[tsafin]

For future reference here is excerpt from PostgreSQL documentation https://www.postgresql.org/docs/13/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS :

4.1.2.6. Numeric Constants

Numeric constants are accepted in these general forms:

digits
digits.[digits][e[+-]digits]
[digits].digits[e[+-]digits]
digitse[+-]digits

where digits is one or more decimal digits (0 through 9). At least one digit must be before or after the decimal point,
if one is used. At least one digit must follow the exponent marker (e), if one is present. There cannot be any spaces or
other characters embedded in the constant. Note that any leading plus or minus sign is not actually considered
part of the constant; it is an operator applied to the constant.
...
A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer
if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if its value fits in type bigint (64 bits);
otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially
presumed to be type numeric.

Their heuristics is pretty much as what we want to achieve, module their specific types which we do not have (e.g. bigint).
We should specify our rules same way terse, and similarly clear as PostgreSQL done. [See my attempt below]

[Gerold103]

For me all the options looks fine. Can't say which one is much better or more correct. Only want to say that we must not use "decimal" for all numbers regardless of their value. Must try to use integer types where possible. Because most of the time I suppose it would be possible and much faster to work with.

[tsafin]

There were multiple edge cases described, and it's hard to construct some simple system rules out of it, but let me try

• All numeric literals (be them hexadecimal or decimal) without decimal point or exponent will be assumed to be
  INTEGER if they fit into the 64-bit integer range, otherwise they considered to be DECIMAL (numeric) with scale 0.
  If literal is out of range for our decimal implementation (i.e. for precision/scale 38) then we generate compile error;

• All constant literals which contain decimal point and/or exponent are DECIMAL type of appropriate precision and scale
  If literal is out of range for our decimal implementation (i.e. for precision/scale 38) then we generate compile error;

• If developer wants to write constant with decimal point but outside of allowed range of values for DECIMAL type, then
  one could use suffix f for enforcing of DOUBLE type. This value will similarly generate compile error of it is not in allowed
  values range for doubles;

i.e.

• 0x10, 1234 are INTEGER type constants;
• 0.5, 1e20 are DECIMAL;
• 0.5f, 1e300f are DOUBLE

The idea here is - we still have consistent type promotion rules (which eventually may be extended with infinite decimal implementation like in PostgreSQL), but still have convenient for developer way to enforce DOUBLE wherever he really wants it, or work-around current limitations of decNumber library.

Explicit and Implicit casts

It's already defined in original RFC. Essentially DECIMAL behaves similarly to NUMBER meta-class, and integrated into all its' combinations. Wherever we allow conditional cast we use DECIMAL boundaries.

Please see below conversions table we agreed upon before, after #5910 discussion

• Explicit casts

• Implicit casts

• Comparisons

We do not use implicit casts table for comparisons (see reasoning here) but meta-class NUMBER do actually use heuristics for numeric types, which is very similar to implicit casts between all numbers. DECIMAL should be integrated into this meta-type number similar to double as an example.

i.e. select 1.0 < 1.1f; should be allowed and comparable, providing expected results.

[pgulutzan]

The standard and DB2 and SQL Server and MySQL all say literals with exponents should be non-exact / float. Oracle is different (and perhaps it's where the idea of using "f" suffix comes from although in Oracle it's for 32-bit float). PostgreSQL is different but unlike Tarantool it's able to have many digits before and after the decimal point and still be exact. So, please, let floats need e and no f.

[tsafin]

Well, yes, then. Those big names sound convincing :)

[ImeevMA]

Numeric types in Tarantool SQL

• Status: In progress
• Start date: 23-06-2021
• Authors: Mergen Imeev @ImeevMA [email protected], Peter Gulutzan @pgulutzan, Timur Safin @tsafin
• Issues: #4415

Introduction

This RFC describes:

1. Numeric types in Tarantool SQL.
2. Explicit and implicit cast from non-numeric type to numeric type.
3. Explicit cast from one numeric type to another.
4. Implicit cast from one numeric type to another.
5. Arithmetic operations.
6. Type of numeric literal.

Numeric types in Tarantool SQL.

Currently Tarantool supports following numeric types:

1. INTEGER - integer values in range [-9223372036854775808, 18446744073709551615]. Precise numeric type.
2. UNSIGNED - integer values in range [0, 18446744073709551615]. Subset of INTEGER. Precise numeric type.
3. DOUBLE - numeric type that use double-precision floating-point format. Approximate numeric type.
4. DECIMAL - numeric type that contains numbers with up to 38 digits and have fixed-point format. Precise numeric type.
5. NUMBER - numeric type that can contain values of INTEGER, DOUBLE and DECIMAL. In this RFC "original" type of a number of NUMBER type will be named "subtype".

Explicit and implicit cast from non-numeric type to numeric type.

Rules of explicit and implicit cast are the same for cast from STRING to any numeric type.

Explicit cast from STRING to INTEGER

A STRING value can be explicitly cast to INTEGER if it follows these rules:

1. It does not contain a point or an exponent.
2. It starts with a sign (+ or -) or does not have a sign.
3. Not including sign, it should consist of decimal digits or 0x followed by hexadecimal digits.
4. It can be cast to integer in [-9223372036854775808, 18446744073709551615].

Explicit cast from STRING to UNSIGNED

A STRING value can be explicitly cast to UNSIGNED if it follows these rules:

1. It does not contain a point or an exponent.
2. It starts with a sign (+) or does not have a sign.
3. Not including sign, it should consist of decimal digits or 0x followed by hexadecimal digits.
4. It can be cast to integer in [0, 18446744073709551615].

Explicit cast from STRING to DOUBLE

A STRING value can be explicitly cast to DOUBLE if it follows these rules:

1. It contains an exponent.
2. Before an exponent it has a subvalue, that may starts with a sign(+ or -), may have a point and besides a sign and a points has only decimal digits.
3. After an exponent it has a subvalue, that may starts with a sign(+ ot -) and besides a sign has only decimal digits.

Explicit cast from STRING to DECIMAL

A STRING value can be explicitly cast to DECIMAL if it follows any of two set of rules:

First set:

1. It does not contain a point or an exponent.
2. It starts with a sign (+ or -) or does not have a sign.
3. Not including a sign, it should consist of decimal digits or 0x followed by hexadecimal digits.
4. It cannot be cast to integer in [-9223372036854775808, 18446744073709551615].
5. After parsing it contains no more than 38 digits.

Second set:

1. It does not contain an exponent.
2. It contains a point.
3. It starts with a sign (+ or -) or does not have a sign.
4. Not including a sign and a pont, it should consist of decimal digits.
5. Length of part left from a point is no more than 38.

Explicit cast from STRING to NUMBER

During cast from STRING to NUMBER we apply rules for cast from STRING to INTEGER, if not successful we try to apply rules to cast from STRING to NUMBER and lastly we try to apply rules to cast from STRING to DOUBLE.

Explicit cast from one numeric type to another.

Explicit cast to INTEGER.

Value of type USIGNED can always be explicitly cast to INTEGER. Conversion is precise.

Value of types DOUBLE, DECIMAL and NUMBER can be explicitly cast to INTEGER only if the value is more than -9223372036854775809 and less than 18446744073709551616. Conversion may be not precise.

Explicit cast to UNSIGNED.

Value of type INTEGER can be explicitly cast to UNSIGNED only if the value is no less than 0. Conversion is precise.

Value of types DOUBLE, DECIMAL and NUMBER can be explicitly cast to UNSIGNED only if the value is more than -1 and less than 18446744073709551616. Conversion may be not precise.

Explicit cast to DOUBLE.

Any numeric value can be explicitly cast to DOUBLE. Conversion may be not precise.

Explicit cast to DECIMAL.

Value of types INTEGER and UNSIGNED can always be explicitly cast to DECIMAL. Conversion is precise.

Value of types DOUBLE and NUMBER can be explicitly cast to DECIMAL only if the value is more than -10^38 and less than 10^38. Conversion may be not precise.

Explicit cast to NUMBER.

Any numeric value can be explicitly cast to NUMBER. Conversion may be not precise.

Implicit cast from one numeric type to another.

Implicit cast to INTEGER.

Value of type USIGNED can always be implicitly cast to INTEGER. Conversion is precise.

Value of types DOUBLE, DECIMAL and NUMBER can be implicitly cast to INTEGER only if fractional part of the number is zero and it is no less than -9223372036854775808 and no more than 18446744073709551615.

Implicit cast to UNSIGNED.

Value of type INTEGER can be implicitly cast to UNSIGNED only if the value is no less than 0.

Value of types DOUBLE, DECIMAL and NUMBER can be implicitly cast to UNSIGNED only if fractional part of the number is zero and it is no less than 0 and no more than 18446744073709551615.

Implicit cast to DOUBLE.

A number of any numeric type can be implicitly cast to DOUBLE only if the number can be represented in double-precision floating-point format.

Implicit cast to DECIMAL.

Value of types INTEGER and UNSIGNED can always be implicitly cast to DECIMAL. Conversion is precise.

Value of types DOUBLE and NUMBER can be implicitly cast to DECIMAL only if the value is more than -10^38 and less than 10^38. Conversion may be not precise.

Implicit cast to NUMBER.

Any numeric value can be implicitly cast to NUMBER. Conversion is precise.

Arithmetic operations.

For operation % both operands and result should always be of INTEGER type.

If both operands of +, -, * and / have the same type, there is no need to apply implicit cast to any of them. Otherwise type of operands is determined using these rules:

1. If one of operands is of DOUBLE type and the second is of type INTEGER, UNSIGNED or DECIMAL, then the second operand is implicitly cast to DOUBLE.
2. If one of operands is of DECIMAL type and the second is of type INTEGER or UNSIGNED, then the second operand is implicitly cast to DECIMAL.
3. If both operands are of INTEGER or UNSIGNED type than both operands are implcitly cast to INTEGER.
4. For operands of type NUMBER subtype used in rules above instead of their type. After applying these rules, values are implicitly cast to NUMBER if necessary.

Now, when both operands have the same type (which is NUMBER, DOUBLE, DECIMAL or INTEGER) type of result is the same as type of operands.

Determining type of numeric literal during parsing.

Here we define rules for parser to recognize literal as a number.

Literal will be recognized as INTEGER if it follows these rules:

1. It does not contain a point or an exponent.
2. It starts with a sign (+ or -) or does not have a sign.
3. Not including sign, it should consist of decimal digits or 0x followed by hexadecimal digits.
4. It can be cast to integer in [-9223372036854775808, 18446744073709551615].

Literal will be recognized as DOUBLE if it follows these rules:

1. It contains an exponent.
2. Before an exponent it has a subvalue, that may starts with a sign(+ or -), may have a point and besides a sign and a points has only decimal digits.
3. After an exponent it has a subvalue, that may starts with a sign(+ ot -) and besides a sign has only decimal digits.

For recognizing literal as DECIMAL there is two set of rules:
First set:

1. It does not contain a point or an exponent.
2. It starts with a sign (+ or -) or does not have a sign.
3. Not including a sign, it should consist of decimal digits or 0x followed by hexadecimal digits.
4. It cannot be cast to integer in [-9223372036854775808, 18446744073709551615].
5. After parsing it contains no more than 38 digits.

Second set:

1. It does not contain an exponent.
2. It contains a point.
3. It starts with a sign (+ or -) or does not have a sign.
4. Not including a sign and a pont, it should consist of decimal digits.
5. Length of part left from a point is no more than 38.