sqlite 3.35 stalls tracker-miner-fs-3

(1) By heftig on 2021-03-16 17:31:42 [source]

After upgrading sqlite from 3.34.1 to 3.35.0, tracker-miner-fs-3 can no longer initialize. It burns CPU executing a query with a subquery having many UNION ALLs.

3.34 executed it quickly. 3.35.1 does not help.

The problem was bisected to

commit de9ed6293de53e89b7c37e7de9a8697d86d7f619
Author: dan <Dan Kennedy>
Date:   Wed Dec 16 20:00:46 2020 +0000

    Allow sub-queries that use UNION ALL to be flattened, even if the parent query is a join. Still some problems on this branch.

    FossilOrigin-Name: 00e4bf74d3dfb87666a2266905f7d1a2afc6eb088d22cfd4f38f048733d6b936

More details at https://gitlab.gnome.org/GNOME/tracker-miners/-/issues/161#note_1059821.

(2) By Dan Kennedy (dan) on 2021-03-16 18:48:47 in reply to 1 [link] [source]

It would be good to fix this quickly I think.

We can see the query under "Example Query" in a post from Carlos Garnacho in the thread you linked. If we could also get a copy of the database that this query is running against we should be able to figure something out. Are you able to run the Nautilus tests and get us that database?

Thanks,

Dan.

(3) By heftig on 2021-03-16 21:23:39 in reply to 2 [link] [source]

I've managed to dump the 📎 schema for my miner databases. In addition, Carlos is gathering info for you.

(4.1) Originally by anonymous with edits by Richard Hipp (drh) on 2021-03-17 12:51:15 from 4.0 in reply to 2 [link] [source]

Carlos Garnacho here, there is now a reproducer attached in the GNOME issue (https://gitlab.gnome.org/GNOME/tracker-miners/uploads/2e5d6addca1da33726143bd85d713d39/queries.txt), It was also sent to Richard as requested there.

(5) By Richard Hipp (drh) on 2021-03-17 18:59:35 in reply to 1 [link] [source]

Thanks for the report. The "queries.txt" repo case was especially helpful.

This problem appears to arise when the new UNION ALL optimization is applied repeatedly, causing the size of the parse tree to mushroom. Check-in 9520bed2bd87dc56 disables that optimization if the number of subqueries within the statement grows too large. This is a small change that is appropriate for a patch release.

We will be looking closely at this kind of problem (optimizations causing unchecked parse tree growth) during the next release cycle and possibly introducing new and better prevention strategies. If nothing else, we will do something about the new magic number "500" that was inserted by the patch.