Bug report: a bug in VIEW

(1.1) By Wang Ke (krking) on 2021-06-02 13:31:17 edited from 1.0 [source]

Hi all,

For this example:

CREATE TABLE t0(c0 , c1 INT);
CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;
INSERT INTO t0 VALUES (NULL, 1);

SELECT * FROM v0; -- 1
SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1
SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 1
SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- empty

I have tried to find the commit which causes this problem through bisect, but I find that it seems like this problem has been existing for a long time. Since d794b34da6 and bed42116ad allowed us to use column names of a view, the problem already exists.

(2) By Richard Hipp (drh) on 2021-06-02 12:28:44 in reply to 1.0 [link] [source]

Alternative formulation:

CREATE TABLE t0(a , b INT);
INSERT INTO t0 VALUES (NULL, 1);
CREATE VIEW v0(c) AS SELECT b FROM t0 EXCEPT SELECT a FROM t0;
CREATE VIEW v1 AS SELECT b AS 'c' FROM t0 EXCEPT SELECT a FROM t0;
SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v0; -- 1
SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v1; -- 0

The problem bisects to d794b34da6f9c77d which is the check-in that introduced the ability to specify the column names of a view. First appeared in version 3.9.0.

(3.2) By Wang Ke (krking) on 2021-06-02 13:26:18 edited from 3.1 in reply to 2 [link] [source]

Thanks for the information.

When I am on bed42116ad, which is later than d794b34da6, SQLite prompts an error message:

./sqlite3 test.db
SQLite version 3.8.6 2014-08-16 19:01:00
Enter ".help" for usage hints.
sqlite> CREATE TABLE t0(a , b INT);
sqlite> INSERT INTO t0 VALUES (NULL, 1);
sqlite> CREATE VIEW v0(c) AS SELECT b FROM t0 EXCEPT SELECT a FROM t0;
Error: near "(": syntax error
sqlite> CREATE VIEW v1 AS SELECT b AS 'c' FROM t0 EXCEPT SELECT a FROM t0;
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v0; -- 1
Error: no such table: v0
sqlite> SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v1; -- 0
0

It seems that after introduced the ability to specify the column names of a view, the ability disappeared. So I can't figure out exactly which check-in introduced the problem.

Besides, there are some chech-in that can't compile, like 227bb8a181.

sqlite3.c: In function ‘pagerFreeBitvecs’:
sqlite3.c:44125:30: error: ‘Pager’ {aka ‘struct Pager’} has no member named ‘pAllRead’
make: *** [Makefile:631：sqlite3.lo] error 1

(4) By Dan Kennedy (dan) on 2021-06-02 14:20:44 in reply to 1.1 [link] [source]

I think this is a consequence of the oddity described here:

https://www.sqlite.org/datatype3.html#affcompoundview

The expression is true if "c0" is assigned INTEGER affinity, but false if it is assigned BLOB affinity. And since which affinity the column is assigned is indeterminate, so are the results of the query.

Dan.

(5) By Richard Hipp (drh) on 2021-06-02 14:38:57 in reply to 4 [link] [source]

I have now amplified the documentation by giving it a heading and adding a link to this forum thread. The same hyperlink above still works. The paragraph that describes the indeterminacy of affinity in compound SELECT statements has been there for a long while - only the heading and the link to this forum thread are new.

(6) By Wang Ke (krking) on 2021-06-02 14:53:36 in reply to 4 [link] [source]

Thanks for the explanation.

I have made changes to the type and tested again.

CREATE TABLE t0(c0, c1);

CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;

INSERT INTO t0 VALUES (NULL, 1);

SELECT c0 FROM v0; -- 1

SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1

SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 1

SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- 1

and

CREATE TABLE t0(c0 INT, c1 INT);

CREATE VIEW v0(c0) AS SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0;

INSERT INTO t0 VALUES (NULL, 1);

SELECT c0 FROM v0; -- 1

SELECT c1 FROM t0 EXCEPT SELECT c0 FROM t0; -- 1

SELECT (c0 NOT BETWEEN '-1' AND c0) FROM v0; -- 0

SELECT * FROM v0 WHERE (c0 NOT BETWEEN '-1' AND c0); -- empty

It is indeed the same as you say. So it's not a bug.

Thank you for all.