|
2017-08-18
| ||
| 13:25 | • Fixed ticket [e20dd54a]: COLLATE sequence for ORDER/GROUP BY ignored when using an index-on-expression plus 3 other changes (artifact: 56c729e7 user: drh) | |
| 10:27 | • Ticket [e20dd54a]: 6 changes (artifact: ab63b0fa user: dan) | |
| 08:29 | Only use indexes on expressions to optimize ORDER BY and GROUP BY if the collation sequence matches. Possible fix for [e20dd54a]. (check-in: 37e19008 user: dan tags: trunk) | |
| 07:14 | • New ticket [e20dd54a] COLLATE sequence for ORDER/GROUP BY ignored when using an index-on-expression. (artifact: 752ae671 user: dan) | |
| Ticket Hash: | e20dd54ab0e4383805167564bd2c6df5aa2ad01c | |||
| Title: | COLLATE sequence for ORDER/GROUP BY ignored when using an index-on-expression | |||
| Status: | Fixed | Type: | Code_Defect | |
| Severity: | Critical | Priority: | Immediate | |
| Subsystem: | Unknown | Resolution: | Fixed | |
| Last Modified: | 2017-08-18 13:25:46 | |||
| Version Found In: | ||||
| User Comments: | ||||
|
dan added on 2017-08-18 07:14:45:
With 3.20.0, this script:
CREATE TABLE t2(x);
INSERT INTO t2 VALUES('.ABC');
INSERT INTO t2 VALUES('.abcd');
INSERT INTO t2 VALUES('.DEF');
SELECT substr(x, 2) FROM t2 ORDER BY substr(x, 2) COLLATE nocase;
.print ------
CREATE INDEX i2 ON t2( substr(x, 2) );
SELECT substr(x, 2) FROM t2 ORDER BY substr(x, 2) COLLATE nocase;
returns: ABC abcd DEF ------ ABC DEF abcd The problem is that in the second query SQLite uses the index to optimize the ORDER BY clause, even though the collation sequence of the index does not match that of the ORDER BY expression. dan added on 2017-08-18 10:27:01:
This problem has been present since 3.9.0 - when the indexes-on-expressions feature was added to SQLite. | ||||