|Title:||Issue with DISTINCT and COLLATE|
|Last Modified:||2019-07-29 06:05:34|
|Version Found In:||3.29.0|
mrigger added on 2019-07-28 21:53:44:
Consider the test case below:
CREATE TABLE t0(c0 COLLATE RTRIM); INSERT INTO t0(c0) VALUES (' '); SELECT DISTINCT(t0.c0) != '' FROM t0; -- expected: 1, actual: 0
I would expect the SELECT to fetch a row with a value 1, since the DISTINCT(t0.c0) should be equal to ' ', and ' ' != '' should be true. However, the result is 0. I suspect that DISTINCT(t0.c0) assumes the COLLATE RTRIM. This is unexpected, since the documentation mentions that only the plus operator and CAST in combination with a column still assume the column's COLLATE, rather than having no implicit COLLATE:
If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary "+" operators and/or CAST operators is still considered a column name.
drh added on 2019-07-29 06:05:34:
The DISTINCT keyword is a modifier on SELECT. It is not a function that is applied to parts of the result set. The query actually parses out like this:
SELECT DISTINCT ((t0.c0) != '') FROM t0;
Hence, the RTRIM collating sequence does apply to the LHS of the comparison, and the result is 0.