SQLite

View Ticket
Login
Ticket Hash: f043b1130b16826ab8454fd0d708b0907dda0930
Title: INDEXED BY results in row not being fetched
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-08-20 11:45:21
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-08-20 09:29:54: (text/x-fossil-wiki)
<pre>
CREATE TABLE t0 (c0, c1);
CREATE INDEX i0 ON t0(CAST(c0 AS NUMERIC));
INSERT INTO t0(c0, c1) VALUES ('a', -1);
SELECT * FROM t0 INDEXED BY i0 WHERE CAST(t0.c0 AS NUMERIC) > LOWER(t0.c1) GROUP BY t0.rowid; -- expected: row is fetched, actual: no row is fetched
</pre>

In the test case above, the INDEXED by causes the row not to be fetched. If it is omitted, the row is fetched, as expected:

<pre>
SELECT * FROM t0 WHERE CAST(t0.c0 AS NUMERIC) > LOWER(t0.c1) GROUP BY t0.rowid;
</pre>

dan added on 2019-08-20 11:45:21: (text/x-fossil-wiki)
Fixed by [511da081].