Thanks for the report.

This error goes back to the introduction of the json_quote() function with SQLite version 3.14.0 and specifically to a check-in on 2016-07-23.

The problem arises because the json_quote() function behaves different depending on whether its argument comes directly from another JSON function, or is an ordinary SQL value. The behavior attempts to mimic what the json_quote() function in MySQL does. See the json_quote() function documentation for details on the behavior differences. In summary:

SELECT json(1);              -- yields TEXT value '1'
SELECT json_quote(json(1));  -- yields TEXT value '1'
SELECT json_quote('1');      -- yields TEXT value '"1"'

The string '1' is considered TRUE and the string '"1"' is considered FALSE.

The problem is that SQLite attempts to optimize the final query in the statement of the problem above. The original query is

SELECT * FROM v0, t1 WHERE NOT json_quote(v0.c0);

This should be evaluated as if it were:

SELECT * FROM v0, t1 WHERE NOT json_quote('1');

The WHERE condition should be true and one row should be returned. But the query optimizer tries to be clever and evaluate this as:

SELECT * FROM v0, t1 WHERE NOT json_quote(json('1'));

The WHERE condition in this optimized case is false, resulting in no rows being returned.

We do not have a solution for this yet, though probably it will involve modifying the query optimizer so that it knows not to make the optimization that causes the failure.

Note that while this is technically a problem and it is something that we need to fix, this is not something that is likely to come up in a real-world application, and hence is not a high priority. It is also not a vulnerability.

Thanks again for the bug report.

A more detailed example of the problem. The database:

CREATE TABLE t1(a TEXT);  INSERT INTO t1 VALUES('x');
CREATE VIEW v0(b) AS SELECT json(1);
CREATE TABLE t2 AS SELECT * FROM v0;

Table t2 and view v0 should always behave the same. But they do not:

SELECT * FROM v0, t1;
SELECT * FROM t2, t1;
SELECT NOT json_quote(b) FROM v0, t1;
SELECT NOT json_quote(b) FROM t2, t1;
SELECT * FROM v0, t1 WHERE NOT json_quote(b);
SELECT * FROM t2, t1 WHERE NOT json_quote(b);

The last two SELECT statements in the previous code block should give the same answer, but they do not. The statement that uses t2 in place of v0 is correct.

If we disable the Push-Down optimization, then everything works:

.testctrl optimizations 0x1000
SELECT * FROM v0, t1;
SELECT * FROM t2, t1;
SELECT NOT json_quote(b) FROM v0, t1;
SELECT NOT json_quote(b) FROM t2, t1;
SELECT * FROM v0, t1 WHERE NOT json_quote(b);
SELECT * FROM t2, t1 WHERE NOT json_quote(b);

This issue has now been resolved on trunk.

Thanks!