SQLite

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

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