SQLite

View Ticket
Login
Ticket Hash: f8a7060ece7252a459eddad7b472504b3cafbd33
Title: Incorrect result for query that uses MIN() and a CAST on rowid
Status: Closed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-08-27 20:00:48
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-08-07 20:20:22:

Consider the following test case:

CREATE TABLE t0(c0 UNIQUE, c1);
INSERT INTO t0(c1) VALUES (0);
INSERT INTO t0(c0) VALUES (0);
CREATE VIEW v0(c0, c1) AS SELECT t0.c1, t0.c0 FROM t0 WHERE CAST(t0.rowid AS INT) = 1;
SELECT v0.c0, MIN(v0.c1) FROM v0; -- expected: 0|, actual: |

The view holds a row with the values 0|:

SELECT c0, c1 FROM v0; -- 0|

Thus, it is unexpected that fetching from it returns |.


drh added on 2019-08-07 21:30:38:

This problem was introduced on 2012-12-13 by check-in [52e755943f87354f] and first appeared in release 3.7.16 on 2013-03-18


dan added on 2019-08-08 08:31:39:

Another example:

CREATE TABLE t1(a, b, c);
INSERT INTO t1 VALUES(NULL, 1, 'x');
CREATE INDEX i1 ON t1(a);

SELECT min(a), b, c FROM t1 WHERE c='x';  -- returns NULL|1|x

INSERT INTO t1 VALUES(1,    2, 'y');

SELECT min(a), b, c FROM t1 WHERE c='x';  -- returns NULL|NULL|NULL


dan added on 2019-08-27 20:00:48:

Fixed by [94085fb3].