Re: [Tarantool-discussions] Implicit cast for assignment between numeric types and type mismatch error description.

[Peter Gulutzan <pgulutzan@ocelot.ca>]

Hi,

On 2020-06-25 2:02 a.m., Mergen Imeev wrote:
 > Hi,
 >
 > On 23.06.2020 22:42, Peter Gulutzan wrote:
 >> Hi,
 >>
 >> On 2020-06-23 10:15 a.m., Mergen Imeev wrote:
 >> <cut>
 >> > Thanks for the answer. However, I now have another question:
 >> > should we apply these rules for cases like this:
 >> > SELECT * FROM t LIMIT 2.5;
 >> >
 >> > I mean, we should throw an error here or execute it like this:
 >> > SELECT * FROM t LIMIT 2;
 >> <cut>
 >>
 >> In the SQL-standard equivalent of LIMIT n,
 >> which is FETCH FIRST n ROWS, that is a syntax
 >> error because
 >> "The declared type of <fetch first row count>
 >> shall be an exact numeric with scale 0 (zero)."
 >> For us it is a runtime error ...
 > Should we generate a syntax error here instead of a runtime error?
 > However, in this case, we will not be able to execute something
 > like this:
 > SELECT * FROM t LIMIT 1 + 2;
 >
 > I will fill an issue if you think so.
 >
 > Actually, generating an error here seems less painful than making
 > an integer from a double.
 >
 >> "
 >> tarantool> n = 0.000000001
 >> ---
 >> ...
 >> tarantool> box.execute([[SELECT 5 LIMIT ?;]], {n})
 >> ---
 >> - null
 >> - 'Failed to execute SQL statement: Only positive
 >>   integers are allowed in the LIMIT
 >>   clause'
 >> ...
 >> "
 >> and you are proposing that there should be no error.
 > No, I think the error should be here. However, I'm not sure, so I
 > asked you.
 >
 >> On the other hand,
 >> box.execute([[CREATE TABLE t (s1 VARCHAR(2.0) PRIMARY KEY);]])
 >> causes a "Syntax error", and you are not proposing
 >> that we change that.
 >> Another case is
 >> box.execute([[SELECT SUBSTR('abcde',2.99);]])
 >> which is legal, 2.99 is truncated to 2.
 >
 > I think this is worth fixing. Or is it better the way it is now?
 >
 >
 >>
 >> Therefore, I think your proposal means:
 >> If n is not an integer,
 >> and that is not detected as a syntax error,
 >> then there should be no error or warning.
 >> And I think the precedent of substr() means:
 >> there should be truncation not rounding.
 >>
 >> If I have understood correctly, then I agree.
 >>
 >> Peter Gulutzan
 >>
 >>

I did not understand correctly, but now I do.

Your example SELECT * FROM t LIMIT 1 + 2; must
be allowed because it is documented behaviour.
The manual says, in section "LIMIT clause":
"Expressions may contain integers and arithmetic
operators or functions, for example ABS(-3 / 1).
However, the result must be an integer value
greater than or equal to zero."
Therefore a syntax error check could only look
for a single literal value, and would be
unnecessary because the runtime check would
continue to exist.

Currently this is legal:
SELECT char(1.1),
        randomblob(1.1),
        substr('a', 1.1, 1.1),
        zeroblob(1.1)
        GROUP BY 1.1
        ORDER BY 1.1;
But this is not legal:
SELECT 1.1
        LIMIT 1.1
        OFFSET 1.1;
That is not consistent handling of cases
where (as far as I can see) the only
sensible arguments are unsigned integers.
Therefore, although this might cause a
million-row insertion to fail on the
millionth row, 1.1 should cause an error.
A very-low-priority bug.

Peter Gulutzan