Queries using json_each in a JOIN are not working

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Queries using json_each in a JOIN are not working

Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Queries using json_each in a JOIN are not working

Richard Hipp-3
On 1/26/18, Jens Alfke <[hidden email]> wrote:
>
> # 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));

For now, please use CROSS JOIN instead of INNER JOIN to force the
correct join order.  I'll investigate further to see if this is
something SQLite ought to be doing automatically.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Queries using json_each in a JOIN are not working

Jens Alfke-2


> On Jan 26, 2018, at 1:36 PM, Richard Hipp <[hidden email]> wrote:
>
> For now, please use CROSS JOIN instead of INNER JOIN to force the
> correct join order.

You're right, that fixed the query. Thanks!

I must admit, I don't understand the significance of join order here. It seems to me that the results would be the same whether "itemDS" or "categoryDS" is the outer loop; it's only performance that might change. Could someone explain?

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Queries using json_each in a JOIN are not working

Richard Hipp-3
Proper fix is now on trunk and will appear in the next release.

On 1/26/18, Jens Alfke <[hidden email]> wrote:

>
>
>> On Jan 26, 2018, at 1:36 PM, Richard Hipp <[hidden email]> wrote:
>>
>> For now, please use CROSS JOIN instead of INNER JOIN to force the
>> correct join order.
>
> You're right, that fixed the query. Thanks!
>
> I must admit, I don't understand the significance of join order here. It
> seems to me that the results would be the same whether "itemDS" or
> "categoryDS" is the outer loop; it's only performance that might change.
> Could someone explain?
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Queries using json_each in a JOIN are not working

Jens Alfke-2


> On Jan 26, 2018, at 3:48 PM, Richard Hipp <[hidden email]> wrote:
>
> Proper fix is now on trunk and will appear in the next release.

Thanks very much! I found the bug report you filed*, which matches my hypothesis, and that will help me write up some release notes for our library to warn developers not to create queries that trigger this (until we can integrate SQLite 1.23.)

—Jens

* http://www.sqlite.org/src/info/80177f0c226ff54f6dd
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users