Strange SQLite behavior - NULL values of computed column in certain cases

Question

Hello everyone,

I've just discovered a very strange behavior of SQLite. I do hope I'm missing something here and this is not a bug in the implementation.

The problem is that...

a) if a query includes a computed column which takes another real column as its input, AND
b) the computed column is used in the where clause, AND
c) the real column is indexed

...sometimes this leads to the returned value of the computed column being NULL instead of whatever value it should have.

Here's a sample SQL to reproduce and illustrate the problem:

CREATE TABLE records (
    record_id INTEGER PRIMARY KEY,
    record_type INTEGER
);
CREATE INDEX records_record_type_index ON records (record_type);

INSERT INTO records (record_id, record_type) VALUES (101, 0);
INSERT INTO records (record_id, record_type) VALUES (102, 1);
INSERT INTO records (record_id, record_type) VALUES (103, 0);
INSERT INTO records (record_id, record_type) VALUES (104, 1);
INSERT INTO records (record_id, record_type) VALUES (105, 0);

.headers on

-- 1st select
SELECT * FROM records;

-- 2nd select
SELECT record_id, record_type, (record_type * 5) AS zzzA FROM records WHERE ((record_id > 0) OR ((record_type = 2) AND (1 == zzzA)));

-- 3rd select
SELECT record_id, record_type, (record_type * 5) AS zzzB FROM records WHERE ((record_id > 0) OR ((+record_type = 2) AND (1 == zzzB)));

-- 4th select
SELECT record_id, record_type, (record_type * 5) AS zzzA FROM records WHERE ((record_id > 0) OR ((record_type = 2) AND (1 == zzzA)));

DROP INDEX records_record_type_index;

-- 5th select
SELECT record_id, record_type, (record_type * 5) AS zzzC FROM records WHERE ((record_id > 0) OR ((record_type = 2) AND (1 == zzzC)));

CREATE INDEX records_record_type_index ON records (record_type);

-- 6th select
SELECT record_id, record_type, (record_type * 5) AS zzzA FROM records WHERE ((record_id > 0) OR ((record_type = 2) AND (1 == zzzA)));

INSERT INTO records (record_id, record_type) VALUES (106, 2);

-- 7th select
SELECT record_id, record_type, (record_type * 5) AS zzzD FROM records WHERE ((record_id > 0) OR ((record_type = 2) AND (1 == zzzD)));

And the output is:

record_id|record_type
101|0
102|1
103|0
104|1
105|0
record_id|record_type|zzzA
101|0|
102|1|
103|0|
104|1|
105|0|
record_id|record_type|zzzB
101|0|0
102|1|5
103|0|0
104|1|5
105|0|0
record_id|record_type|zzzA
101|0|
102|1|
103|0|
104|1|
105|0|
record_id|record_type|zzzC
101|0|0
102|1|5
103|0|0
104|1|5
105|0|0
record_id|record_type|zzzA
101|0|
102|1|
103|0|
104|1|
105|0|
record_id|record_type|zzzD
101|0|10
102|1|10
103|0|10
104|1|10
105|0|10
106|2|10

As you can see,

a) in the 2nd select, the computed zzzA column values are NULL;
b) if the record_type index is explicitly forbidden (3rd select) or dropped (5th select), everything works as expected;
c) if we add a record with the record_type value for which one of the where clause parts is checking, everything works again (7th select).

Your opinion on this case is deeply appreciated.

UPDATE1: This is happening with SQLite 3.6.12.

UPDATE2: Looks like in the most recent version of SQLite (3.6.23.1) this bug has been fixed already. So I'd just have to find a workaround for my old version for now.