Results of negation must be the same for each datatype (smallint / int /bigint / int128) when argument is least possible value for this type

[pavel-zotov]

Following examples are based on messages in fb-devel, Vol 186, Issue 18 (date: 11-oct-2021).

Example #1.

    set heading off;
    set sqlda_display on;
    select -(-32768) as n from rdb$database;
    01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
    ...
           32768 

No error.
This means that rule from SQL:2016 which was mentioned by Mark:
"rule 2 establishes that the type of a negation is the type of the negated expression" - is broken here. Type of negation must remain smallint and thus negation of -32768 should raise exception.

Example #2.

    select -(-2147483648) as n from rdb$database;
    01: sqltype: 580 INT64 scale: 0 subtype: 0 len: 8
    ...
    Statement failed, SQLSTATE = 22003
    Integer overflow. The result of an integer operation caused the most significant bit of the result to carry.

This result looks also not clear:

1. why type of least possible integer value changed from INT to BIGINT ?
2. why negation of this BIGINT value (though it is only 2^31, i.e. must less than limit for bigint) raises exception ?
   /* Dmitry replied me privately about this and now I understand the reason: generating of BLR forces this value to "go back" to INT type. */

Shortly speaking, both examples must behave like this:

    select -(-9223372036854775808) as n from rdb$database;
    01: sqltype: 580 INT64 scale: 0 subtype: 0 len: 8
    ...
    Statement failed, SQLSTATE = 22003
    Integer overflow.  The result of an integer operation caused the most significant bit of the result to carry.

(i.e. produce exception and do not change type of argument that is subject for negation).

[asfernandes]

Example #1.

    set heading off;
    set sqlda_display on;
    select -(-32768) as n from rdb$database;
    01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
    ...
           32768 

No error. This means that rule from SQL:2016 which was mentioned by Mark: "rule 2 establishes that the type of a negation is the type of the negated expression" - is broken here. Type of negation must remain smallint and thus negation of -32768 should raise exception.

select 32768 as n from rdb$database also returns LONG type, not SHORT.

So there is no inconsistency in this case.

[dyemanov]

But -32768 is expected to be SMALLINT, thus -(-32768) should also be a SMALLINT (by SQL spec) and raise an error. The problem here is that our parser does not support SMALLINT constants at all, they're described as INT (or bigger) instead.

[asfernandes]

Do the standard defines that a small numeric constant like 1 should be smallint (and not integer or implementation defined)?

[dyemanov]

The declared type of an ENL is an implementation-defined exact numeric type
whose scale is the number of s to the right of the . There shall be an exact numeric type
capable of representing the value of ENL exactly.

So we're compliant regarding -32768 being an INT. IMO, the only real issue is that -2147483648 is described as BIGINT but calculated as INT. It should be either INT everywhere or BIGINT everywhere.

[mrotteveel]

Do the standard defines that a small numeric constant like 1 should be smallint (and not integer or implementation defined)?

SQL:2016-2 section 5.3 <literal> says:

21. An <exact numeric literal> without a <period> has an implied <period> following the last <digit>.
22. The declared type of an <exact numeric literal> ENL is an implementation-defined exact numeric type
    whose scale is the number of <digit>s to the right of the <period>. There shall be an exact numeric type
    capable of representing the value of ENL exactly.

So the current behaviour for -32768 and -(-32768) is correct, as the -32768 literal is of type INTEGER.

[AlexPeshkoff]

Made it INT everywhere.