SQLite Forum

Unexpected Result by JSON
Login

Unexpected Result by JSON

(1) By Jinsheng Ba (bajinsheng) on 2022-06-09 07:46:03 [source]

NOT json_quote(v0.c0) is evaluated TRUE in the second SELECT, but the third SELECT fetches no row.

CREATE TABLE t1 (c0);
CREATE VIEW v0(c0) AS SELECT json(TRUE);
INSERT INTO t1 VALUES ('x');
SELECT * FROM v0, t1; -- 1|x
SELECT NOT json_quote(v0.c0) FROM v0, t1; -- 1
SELECT * FROM v0, t1 WHERE NOT json_quote(v0.c0); -- {} (empty result set)

Environment:

Version: 3.39.0

Commit ID: 5abb5ef5

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

(2) By Richard Hipp (drh) on 2022-06-09 13:52:33 in reply to 1 [link] [source]

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.

(3) By Richard Hipp (drh) on 2022-06-09 14:09:37 in reply to 1 [link] [source]

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);

(4) By Richard Hipp (drh) on 2022-06-09 20:26:46 in reply to 1 [link] [source]

This issue has now been resolved on trunk.

(5) By Jinsheng Ba (bajinsheng) on 2022-06-10 01:06:47 in reply to 4 [link] [source]

Thanks!