View Ticket
Ticket Hash: d60b3cd7cb0bdff8ff39f76c9faf16ba2efa442f
Title: COLLATE expression has an affinity
Status: Fixed Type: Documentation
Severity: Minor Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-11 03:00:24
Version Found In:
User Comments:
mrigger added on 2019-06-10 20:46:16:

Consider the following statement:

SELECT ((CAST(1 as INT)) COLLATE BINARY) == '1'; -- expected: 0, actual: 1

The expression CAST(1 as INT) has INT affinity. Since COLLATE BINARY is a postfix operator attached to the CAST, I expect that the left side of the comparison has no affinity. However, the comparison yields 1, which suggests that the left side was assigned an INT affinity and the right side converted to INT.

Note that having a column with the respective affinity has the same effect. The documentation explicitly states that operators on columns result in the expression having no affinity:

Any operators applied to column names, including the no-op unary "+" operator, convert the column name into an expression which always has no affinity.

This works as expected for other postfix operators (i.e., they resulting expression has no affinity):

SELECT ((CAST(1 as INT)) NOTNULL) == '1'; -- 0

drh added on 2019-06-11 03:00:24:

This is a documentation error, fixed by check-in https://www.sqlite.org/docsrc/info/722f0828b3074e89