Unexpected output from the SELECT statement

(1) By Yu Liang (LY1598773890) on 2021-05-12 23:06:58 [source]

Hi all:

For query:

CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) WITHOUT ROWID;
INSERT INTO v0 VALUES (10) ;
ALTER TABLE v0 ADD v2 INT;

SELECT * FROM v0 WHERE ( v1 = 20 ) OR ( v1 = 10 AND v2 = 10 );
/* outputs '10| '*/

Since column v2 is empty, it is expected that the SELECT statement would output empty result. Unfortunately, the actual output returns the row.

Looking forward to your reply.

(2) By Scott Robison (casaderobison) on 2021-05-13 05:03:54 in reply to 1 [link] [source]

If you type these statements:

  explain select * from v0 where (v1=20) or (v1=10 and v2=10);

  explain select * from v0 where (v1=20) union select * from v0 where (v1=10 and v2=10);

You'll see that it generates very different VM programs for the first than the last. The second explain output clearly shows that v2 is checked, but not in the first.

Someone with more knowledge about the code generator will have to speak as to why the code generator works that way at present and what was necessary to eventually fix it.

(3) By Richard Hipp (drh) on 2021-05-13 13:56:14 in reply to 1 [link] [source]

This problem should be fixed by check-in c21bc5a2353e660f.

The following description of the problem is for the historical record:

A WITHOUT ROWID table is implemented using an index-btree with extra columns appended for the table columns that are not part of the PRIMARY KEY. Unlike all other index-btrees, the appended columns are not ordered. Therefore, the appended columns (column V2 in the example above) should not be used to help look up the desired row. Only the index-btree that implements a WITHOUT ROWID table works this way - all other index-btrees in SQLite have all columns ordered. But the query planner was not taking this exception into account and would (rarely) try to use the additional columns on a WITHOUT ROWID index-btree to help narrow down the search.

You could see this by doing an EXPLAIN QUERY PLAN on the SELECT statement that was malfunctioning. Before the fix, that query plan output contained a line:

SEARCH v0 USING PRIMARY KEY (v1=? AND v2=?)

This is incorrect since the V2 column is unordered in the index-btree that implements the WITHOUT ROWID table and so cannot be used as part of a binary search. After the check-in c21bc5a2353e660f fix, the EXPLAIN QUERY PLAN output shows the correct search condition:

SEARCH v0 USING PRIMARY KEY (v1=?)

(4) By Yu Liang (LY1598773890) on 2021-05-13 14:16:51 in reply to 3 [link] [source]

Thank you for the explanation and the fix.