| 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]. | ||||