I’m getting very strange behavior from a class of query using `json_each`. The
intent here is to have a join condition based on one row’s JSON having an array
containing the other row’s “id” property. The query is horribly non-optimal but
I think it should return results; but it returns no results. After two hours of
experimenting with variations of this query, I'm suspecting a bug in SQLite,
possibly in the json_each function.
(I'm using SQLite 3.22 on macOS 10.13.3.)
# Here’s a minimal data set:
CREATE TABLE docs (id text, body text);
INSERT INTO docs VALUES('ecc:102',
'{"id":"ecc:102","type":"category","items":["eci:742","eci:743","eci:744"]}');
INSERT INTO docs VALUES('eci:742', '{"id":"eci:742","type":"item"}');
INSERT INTO docs VALUES('eci:743', '{"id":"eci:743","type":"item"}’);
# This is the query that occurs in a bug report I got from a developer; it
should return two rows but instead returns none:
SELECT "itemDS".id, "categoryDS".id
FROM docs AS "categoryDS"
INNER JOIN docs AS "itemDS"
ON (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item
WHERE _item.value = "itemDS".id));
# Let's simplify this by taking out the reference to itemDS in the nested
select, using a hardcoded string instead:
SELECT "itemDS".id, "categoryDS".id
FROM docs AS "categoryDS"
INNER JOIN docs AS "itemDS"
ON (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item
WHERE _item.value = 'eci:742'));
# Nope, still no results.
# Let's preflight the 'items' JSON property:
SELECT "itemDS".rowid, "categoryDS".rowid
FROM docs AS "itemDS"
INNER JOIN docs AS "categoryDS"
ON json_extract("categoryDS".body, '$.items') NOT NULL
AND (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item
WHERE _item.value = 'eci:743'));
# This returns three results, as expected!
The minimal complexity I can narrow the WTF down to is that this query doesn't
work (returns nothing):
SELECT itemDS.id, categoryDS.id
FROM docs AS "itemDS", docs AS "categoryDS"
WHERE (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item
WHERE _item.value = 'eci:742'));
…but this one does work:
SELECT categoryDS.id
FROM docs AS "categoryDS"
WHERE (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item
WHERE _item.value = 'eci:742'));
I'm willing to believe that there's some subtlety of SQL that causes this
behavior; but it really does smell like a SQLite bug to me…
—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
