SQLite Forum

sqlite 3.35 stalls tracker-miner-fs-3
Login

sqlite 3.35 stalls tracker-miner-fs-3

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

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](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

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]

I've managed to dump the [📎 schema](https://gitlab.gnome.org/GNOME/tracker-miners/uploads/aed72aa83dc6ed4291bc8e793dcbe029/schema.sql) 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]

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]

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](src:/info/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](src:/info/9520bed2bd87dc56).