/ View Ticket
Ticket Hash: be31cf009c8540d0b21b237ab4b8c7e6d792ffa8
Title: Unexpected result for % and '1E1'
Status: Closed Type: Code_Defect
Severity: Minor Priority: Low
Subsystem: Unknown Resolution: Works_As_Designed
Last Modified: 2020-08-27 13:33:54
Version Found In:
User Comments:
mrigger added on 2020-08-26 14:12:56: (text/x-fossil-wiki)
Consider the following statement:

SELECT 1 % '1E1'; -- expected: {1.0}, actual: {0.0}

Unexpectedly, the result is 0.0. This seems to be a regression bug that was introduced by [048add13fc10e69a], and was found by SQLancer's PQS implementation based on commit [6c716f4b55].

drh added on 2020-08-27 13:21:31: (text/x-markdown)
The intent of the "%" operator is to work on integers only.  For any values
$A and $B, the following should always be true:

>  `($A % $B) == (CAST($A AS INT) % CAST($B AS INT))`

This fact is not clearly defined in the documentation, but it has always
been so.  I will use this ticket to enhance the documentation.
The behavior of `CAST($A as INT)` when $A is a string is well-defined in
the documentation.  It says:

> "When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored."

The longest possible prefix of the string "`'1e1'`" that can be interpreted
as an integer is "`'1'`".  Hence `CAST('1e1' as INT)` yields 1, not 10.
Check-in [](048add13fc10e69a) was a bug fix to make the implicit CAST
operation on the operands of "%" work correctly.

drh added on 2020-08-27 13:33:54: (text/x-markdown)
Documentation updated.  <https://www.sqlite.org/docsrc/info/153857859d220dbd>