/ View Ticket
Login
Ticket Hash: 18458b1ad63b6a5db6bc34a01ad07904cd706b55
Title: COLLATE issue in view
Status: Closed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-09-11 11:30:28
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-09-09 21:02:49:

Consider the following test case:

CREATE TABLE t0(c0 COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('B');
CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0;
SELECT v0.c1 >= v0.c0 FROM v0; -- actual: 1, expected: 0

I would expect the query to yield TRUE. The condition expectedly yields FALSE when used in a WHERE condition:

SELECT * FROM v0 WHERE v0.c1 >= v0.c0; -- no row is fetched


drh added on 2019-09-10 07:50:27:

This appears to be due to the push-down optimization that pushes the "C1 >= C0" constraint down into the view, without taking collating functions into account. This can be seen in that disabling the push-down optimization results in different answers:

CREATE TABLE t0(c0 COLLATE NOCASE);
INSERT INTO t0(c0) VALUES ('B');
CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0;
SELECT count(*) FROM v0 WHERE c1 >= c0;
.testctrl optimizations 0x1000
SELECT count(*) FROM v0 WHERE c1 >= c0;


dan added on 2019-09-11 11:30:28:

Fixed by [18458b1a].