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:

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:

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: