Crash when querying a unique index containing 12 columns using sub-selects

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

Crash when querying a unique index containing 12 columns using sub-selects

Matthew Towler
Hi

I believe I have found a bug in sqlite, which my tests show was introduced between versions 3.7.17 and 3.8.0 and remains present in all versions up to and including 3.21.0 (I have also tested 3.8.11, 3.9.3, 3.12.2 all of which hang.  I believe it closely related to, and probably just a bigger version of, bug https://www.sqlite.org/src/tktview/9f2eb3abac9b83222f8a (problems with indices with more than four columns) which was introduced in 3.8.0, and fixed in a later version.  I am struggling slightly to produce a small example for reproduction as to get it to fail requires both a complex arrangement of tables and a significant volume of data (it all works flawlessly when there are only a few rows) and the data I have is proprietary.  I thought in the first instance I might be able to pass on what I can easily and as it is so very similar to the previous issue someone familiar with the code might understand the issue immediately - whilst I work on getting more data I can pass on.  I hope this is acceptable, apologies for the long message.

I have an ABUNDANCE table with 12 rows, with a unique index across all the rows.

CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,AbundanceId0 INTEGER,AbundanceId1 INTEGER,AbundanceId2 INTEGER,AbundanceId3 INTEGER,AbundanceId4 INTEGER,AbundanceId5 INTEGER,AbundanceId6 INTEGER,AbundanceId7 INTEGER,AbundanceId8 INTEGER,SetAbundanceId0 INTEGER,SetAbundanceId1 INTEGER,SetAbundanceId2 INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_UNIQUE_DATA_INDEX ON ABUNDANCE(AbundanceId0,AbundanceId1,AbundanceId2,AbundanceId3,AbundanceId4,AbundanceId5,AbundanceId6,AbundanceId7,AbundanceId8,SetAbundanceId0,SetAbundanceId1,SetAbundanceId2);
CREATE INDEX ABUNDANCE ON ABUNDANCE(AbundanceId0,AbundanceId1,AbundanceId2,AbundanceId3,AbundanceId4,AbundanceId5,AbundanceId6,AbundanceId7,AbundanceId8);
CREATE INDEX ABUNDANCE_SetAbundanceId0 ON ABUNDANCE(SetAbundanceId0);
CREATE INDEX ABUNDANCE_SetAbundanceId1 ON ABUNDANCE(SetAbundanceId1);
CREATE INDEX ABUNDANCE_SetAbundanceId2 ON ABUNDANCE(SetAbundanceId2);

With querying just this table I cannot get the issue to show itself, so I have to introduce some more tables.  The Abundance0-8 and SetAbundance0-2 columns in the table above are indices into some more tables with similar layout

CREATE TABLE ABUNDANCE_0(AbundanceId0 INTEGER PRIMARY KEY NOT NULL,Col6 INTEGER,Col16 INTEGER,Col19 INTEGER,Col3l INTEGER,Col14 INTEGER,Col15 INTEGER,Col3 INTEGER,Col8 INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX ON ABUNDANCE_0(Col6,Col16,Col19,Col3l,Col14,Col15,Col3,Col8);

CREATE TABLE ABUNDANCE_1(AbundanceId1 INTEGER PRIMARY KEY NOT NULL,Col16t INTEGER,Col26n INTEGER,Col13o INTEGER,Col9 INTEGER,Col18u INTEGER,Col14i INTEGER,Col3o INTEGER,Col1r INTEGER,Col19i INTEGER,Col6e INTEGER,Col1 INTEGER,Col3u INTEGER,Col19n INTEGER,Col23 INTEGER,Col13n INTEGER,Col16d INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX ON ABUNDANCE_1(Col16t,Col26n,Col13o,Col9,Col18u,Col14i,Col3o,Col1r,Col19i,Col6e,Col1,Col3u,Col19n,Col23,Col13n,Col16d);

CREATE TABLE ABUNDANCE_2(AbundanceId2 INTEGER PRIMARY KEY NOT NULL,Col15s INTEGER,Col26r INTEGER,Col22 INTEGER,Col9r INTEGER,Col0l INTEGER,Col0u INTEGER,Col11 INTEGER,Col3d INTEGER,Col20i INTEGER,Col14a INTEGER,Col12i INTEGER,Col3r INTEGER,Col19e INTEGER,Col18e INTEGER,Col0g INTEGER,Col18h INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX ON ABUNDANCE_2(Col15s,Col26r,Col22,Col9r,Col0l,Col0u,Col11,Col3d,Col20i,Col14a,Col12i,Col3r,Col19e,Col18e,Col0g,Col18h);

CREATE TABLE ABUNDANCE_3(AbundanceId3 INTEGER PRIMARY KEY NOT NULL,Col14b INTEGER,Col1i INTEGER,Col14d INTEGER,Col25b INTEGER,Col19m INTEGER,Col9n INTEGER,Col13g INTEGER,Col16b INTEGER,Col21 INTEGER,Col7e INTEGER,Col20e INTEGER,Col7a INTEGER,Col8g INTEGER,Col0s INTEGER,Col19b INTEGER,Col12a INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX ON ABUNDANCE_3(Col14b,Col1i,Col14d,Col25b,Col19m,Col9n,Col13g,Col16b,Col21,Col7e,Col20e,Col7a,Col8g,Col0s,Col19b,Col12a);

CREATE TABLE ABUNDANCE_4(AbundanceId4 INTEGER PRIMARY KEY NOT NULL,Col4y INTEGER,Col19r INTEGER,Col18b INTEGER,Col8f INTEGER,Col20b INTEGER,Col16r INTEGER,Col3e INTEGER,Col5r INTEGER,Col20l INTEGER,Col3s INTEGER,Col1a INTEGER,Col7d INTEGER,Col25 INTEGER,Col3a INTEGER,Col5u INTEGER,Col20a INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX ON ABUNDANCE_4(Col4y,Col19r,Col18b,Col8f,Col20b,Col16r,Col3e,Col5r,Col20l,Col3s,Col1a,Col7d,Col25,Col3a,Col5u,Col20a);

CREATE TABLE ABUNDANCE_5(AbundanceId5 INTEGER PRIMARY KEY NOT NULL,Col3m INTEGER,Col16a INTEGER,Col11r INTEGER,Col0m INTEGER,Col0r INTEGER,Col24e INTEGER,Col16u INTEGER,Col4 INTEGER,Col14p INTEGER,Col20m INTEGER,Col1e INTEGER,Col20h INTEGER,Col8o INTEGER,Col19c INTEGER,Col12u INTEGER,Col20c INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX ON ABUNDANCE_5(Col3m,Col16a,Col11r,Col0m,Col0r,Col24e,Col16u,Col4,Col14p,Col20m,Col1e,Col20h,Col8o,Col19c,Col12u,Col20c);

CREATE TABLE ABUNDANCE_6(AbundanceId6 INTEGER PRIMARY KEY NOT NULL,Col21nknown INTEGER,Col0t INTEGER,Col5s INTEGER,Col6m INTEGER,Col6r INTEGER,Col12r INTEGER,Col13d INTEGER,Col14e INTEGER,Col14o INTEGER,Col16o INTEGER,Col18a INTEGER,Col18n INTEGER,Col0c INTEGER,Col1k INTEGER,Col3f INTEGER,Col16m INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX ON ABUNDANCE_6(Col21nknown,Col0t,Col5s,Col6m,Col6r,Col12r,Col13d,Col14e,Col14o,Col16o,Col18a,Col18n,Col0c,Col1k,Col3f,Col16m);

CREATE TABLE ABUNDANCE_7(AbundanceId7 INTEGER PRIMARY KEY NOT NULL,Col8e INTEGER,Col1h INTEGER,Col3n INTEGER,Col4b INTEGER,Col4s INTEGER,Col5s INTEGER,Col6l INTEGER,Col6m INTEGER,Col6r INTEGER,Col8s INTEGER,Col12r INTEGER,Col12v INTEGER,Col13c INTEGER,Col13d INTEGER,Col13t INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX ON ABUNDANCE_7(Col8e,Col1h,Col3n,Col4b,Col4s,Col5s,Col6l,Col6m,Col6r,Col8s,Col12r,Col12v,Col13c,Col13d,Col13t);

CREATE TABLE ABUNDANCE_8(AbundanceId8 INTEGER PRIMARY KEY NOT NULL,Col14h INTEGER,Col14o INTEGER,Col15g INTEGER,Col16o INTEGER,Col18a INTEGER,Col18f INTEGER,Col18g INTEGER,Col18n INTEGER,Col19g INTEGER,Col20s INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_8_UNIQUE_DATA_INDEX ON ABUNDANCE_8(Col14h,Col14o,Col15g,Col16o,Col18a,Col18f,Col18g,Col18n,Col19g,Col20s);

CREATE TABLE ABUNDANCE_SETS_0(SetAbundanceId0 INTEGER PRIMARY KEY NOT NULL,Set4B INTEGER,Set5A INTEGER,Set6A INTEGER,SetX1 INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_SETS_0_UNIQUE_DATA_INDEX ON ABUNDANCE_SETS_0(Set4B,Set5A,Set6A,SetX1);

CREATE TABLE ABUNDANCE_SETS_1(SetAbundanceId1 INTEGER PRIMARY KEY NOT NULL,Set5B INTEGER,Set7B INTEGER,Set8Y INTEGER,Set1A INTEGER,Set6B INTEGER,Set8X INTEGER,Set4A INTEGER,Set3A INTEGER,Set2R INTEGER,Set7A INTEGER,SetTR INTEGER,Set4M INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_SETS_1_UNIQUE_DATA_INDEX ON ABUNDANCE_SETS_1(Set5B,Set7B,Set8Y,Set1A,Set6B,Set8X,Set4A,Set3A,Set2R,Set7A,SetTR,Set4M);

CREATE TABLE ABUNDANCE_SETS_2(SetAbundanceId2 INTEGER PRIMARY KEY NOT NULL,Set8A INTEGER,SetAN INTEGER,Set3B INTEGER,Set2A INTEGER,SetLN INTEGER,Set2B INTEGER,Set2M INTEGER,Set8Z INTEGER,Set1B INTEGER,Set1M INTEGER,Set3R INTEGER,Set3M INTEGER,Set8B INTEGER,Set1R INTEGER);
CREATE UNIQUE INDEX ABUNDANCE_SETS_2_UNIQUE_DATA_INDEX ON ABUNDANCE_SETS_2(Set8A,SetAN,Set3B,Set2A,SetLN,Set2B,Set2M,Set8Z,Set1B,Set1M,Set3R,Set3M,Set8B,Set1R);

Given all those tables I now need a query to show the issue.  I have tried cutting this down as much as possible, but the below is the smallest where the problem shows itself.  It is essentially a select on the first table where each of the ID columns is a sub-select on one of the child tables.

SELECT AbundanceId FROM [ABUNDANCE]
WHERE

ABUNDANCE.AbundanceId0 in (
SELECT AbundanceId0 FROM ABUNDANCE_0 WHERE
Col6 = 0 AND Col16 = 0 AND Col19 = 0 AND Col3l = 0 AND Col14 = 0 AND Col15 = 0 AND Col3 = 0
)

AND

ABUNDANCE.AbundanceId1 in (
SELECT AbundanceId1 FROM ABUNDANCE_1 WHERE
Col16t = 0 AND Col26n = 0 AND Col13o = 0 AND Col9 = 0 AND Col18u = 0 AND Col14i = 0 AND Col3o = 0 AND Col1r = 0 AND Col19i = 0 AND Col6e = 0 AND Col1 = 0 AND Col3u = 0 AND Col19n = 0 AND Col23 = 0 AND Col13n = 0 AND Col16d = 0
)

AND

ABUNDANCE.AbundanceId2 in (
SELECT AbundanceId2 FROM ABUNDANCE_2 WHERE
Col15s = 0 AND Col26r = 0 AND Col22 = 0 AND Col9r = 0 AND Col0l = 0 AND Col0u = 0 AND Col3d = 0 AND Col20i = 0 AND Col3r = 0 AND Col19e = 0 AND Col18e = 0 AND Col0g = 0 AND Col18h = 0
)

AND

ABUNDANCE.AbundanceId3 in (
SELECT AbundanceId3 FROM ABUNDANCE_3 WHERE
Col14b = 0 AND Col1i = 0 AND Col14d = 0 AND Col25b = 0 AND Col19m = 0 AND Col9n = 0 AND Col13g = 0 AND Col16b = 0 AND Col21 = 0 AND Col7e = 0 AND Col20e = 0 AND Col7a = 0 AND Col8g = 0 AND Col0s = 0 AND Col19b = 0 AND Col12a = 0
)

AND

ABUNDANCE.AbundanceId4 in (
SELECT AbundanceId4 FROM ABUNDANCE_4 WHERE
Col4y = 0 AND Col19r = 0 AND Col8f = 0 AND Col20b = 0 AND Col16r = 0 AND Col3e = 0 AND Col5r = 0 AND Col20l = 0 AND Col1a = 0 AND Col7d = 0 AND Col25 = 0 AND Col3a = 0 AND Col5u = 0 AND Col20a = 0
)

AND

ABUNDANCE.AbundanceId5 in (
SELECT AbundanceId5 FROM ABUNDANCE_5 WHERE
Col3m = 0 AND Col16a = 0 AND Col11r = 0 AND Col0m = 0 AND Col0r = 0 AND Col24e = 0 AND Col16u = 0 AND Col14p = 0 AND Col20m = 0 AND Col1e = 0 AND Col20h = 0 AND Col8o = 0 AND Col19c = 0 AND Col12u = 0 AND Col20c = 0
)

AND

ABUNDANCE.AbundanceId6 in (
SELECT AbundanceId6 FROM ABUNDANCE_6 WHERE
Col21nknown = 0 AND Col0t = 0 AND Col5s = 0 AND Col6m = 0 AND Col12r = 0 AND Col13d = 0 AND Col14e = 0 AND Col14o = 0 AND Col16o = 0 AND Col18a = 0 AND Col18n = 0 AND Col0c = 0 AND Col1k = 0 AND Col3f = 0 AND Col16m = 0
)

AND

ABUNDANCE.AbundanceId7 in (
SELECT AbundanceId7 FROM ABUNDANCE_7 WHERE
Col8e = 0 AND Col1h = 0 AND Col3n = 0 AND Col4b = 0 AND Col4s = 0 AND Col5s = 0 AND Col6l = 0 AND Col6m = 0 AND Col8s = 0 AND Col12r = 0 AND Col12v = 0 AND Col13c = 0 AND Col13d = 0 AND Col13t = 0
)

AND

ABUNDANCE.AbundanceId8 in (
SELECT AbundanceId8 FROM ABUNDANCE_8 WHERE
Col14h = 0 AND Col14o = 0 AND Col15g = 0 AND Col16o = 0 AND Col18a = 0 AND Col18f = 0 AND Col18g = 0 AND Col18n = 0 AND Col19g = 0 AND Col20s = 0
)

AND

ABUNDANCE.SetAbundanceId0 in (
SELECT SetAbundanceId0 FROM ABUNDANCE_SETS_0 WHERE
SetX1 >= 1 AND SetX1 <= 9999
)
AND
ABUNDANCE.SetAbundanceId1 in (
SELECT SetAbundanceId1 FROM ABUNDANCE_SETS_1 WHERE
Set1A >= 1 AND Set1A <= 9999
)

For small amounts of data (a few rows) this queries very well, but for the volumes of data I have (around 500k rows in the main table and between 300k and 40k rows in the child tables) sqlite hangs (or certainly takes more than two hours) with newer versions.  With versions prior to 3.8.0 the query returns in under a second (which is very impressive!).  A query without all the sub-selects (so just selecting some specific ID values from the main table) always works correctly (which is why I had to include the long version in this message, for which apologies).

What I have found, and the reason I feel this is closely related to the bug I mentioned earlier, is that if I delete the index ABUNDANCE_UNIQUE_DATA_INDEX on the main table (a unique index on all the columns) the query works perfectly on all versions I have tested.  I am guessing something becomes sub-linear.  I expect the answer may be to remove the unique indices and check for uniqueness in the application rather than the database, but it would also be nice if this just worked as is.

I hope this message is of some use.  If a more reproducable test case is required I may have to write an application to generate the necessary volume of data.

Matthew

_______________________________________________
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: Crash when querying a unique index containing 12 columns using sub-selects

Richard Hipp-3
On 1/11/18, Matthew Towler <[hidden email]> wrote:
> for the
> volumes of data I have sqlite hangs

Is it hanging, or is it crashing?  Your statement of the problem is
unclear on this point.
--
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: Crash when querying a unique index containing 12 columns using sub-selects

Simon Slavin-3
In reply to this post by Matthew Towler
On 11 Jan 2018, at 5:01pm, Matthew Towler <[hidden email]> wrote:

> With versions prior to 3.8.0 the query returns in under a second (which is very impressive!).  A query without all the sub-selects (so just selecting some specific ID values from the main table) always works correctly (which is why I had to include the long version in this message, for which apologies).

In your two versions, please prepend "EXPLAIN QUERY PLAN " to your SELECT command.  Do they both report the same query plan ?

> What I have found, and the reason I feel this is closely related to the bug I mentioned earlier, is that if I delete the index ABUNDANCE_UNIQUE_DATA_INDEX on the main table (a unique index on all the columns) the query works perfectly on all versions I have tested.

Does the query plan remain the same when you delete the index ?

Simon.
_______________________________________________
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: Crash when querying a unique index containing 12 columns using sub-selects

Luuk
In reply to this post by Matthew Towler
On 11-01-18 18:01, Matthew Towler wrote:
> Hi
>
> I believe I have found a bug in sqlite, which my tests show was introduced between versions 3.7.17 and 3.8.0 and remains present in all versions up to and including 3.21.0 (I have also tested 3.8.11, 3.9.3, 3.12.2 all of which hang.  I believe it closely related to, and probably just a bigger version of, bug https://www.sqlite.org/src/tktview/9f2eb3abac9b83222f8a (problems with indices with more than four columns) which was introduced in 3.8.0, and fixed in a later version.  I am struggling slightly to produce a small example for reproduction as to get it to fail requires both a complex arrangement of tables and a significant volume of data (it all works flawlessly when there are only a few rows) and the data I have is proprietary.  I thought in the first instance I might be able to pass on what I can easily and as it is so very similar to the previous issue someone familiar with the code might understand the issue immediately - whilst I work on getting more data I can pass on.  I hope this is acceptable, apologies for the long message.
>
> I have an ABUNDANCE table with 12 rows, with a unique index across all the rows.
>
> CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,AbundanceId0 INTEGER,AbundanceId1 INTEGER,AbundanceId2 INTEGER,AbundanceId3 INTEGER,AbundanceId4 INTEGER,AbundanceId5 INTEGER,AbundanceId6 INTEGER,AbundanceId7 INTEGER,AbundanceId8 INTEGER,SetAbundanceId0 INTEGER,SetAbundanceId1 INTEGER,SetAbundanceId2 INTEGER);

a more readable version:

CREATE TABLE ABUNDANCE(
  AbundanceId INTEGER PRIMARY KEY NOT NULL,
  AbundanceId0 INTEGER,
  AbundanceId1 INTEGER,
  AbundanceId2 INTEGER,
  AbundanceId3 INTEGER,
  AbundanceId4 INTEGER,
  AbundanceId5 INTEGER,
  AbundanceId6 INTEGER,
  AbundanceId7 INTEGER,
  AbundanceId8 INTEGER,
  SetAbundanceId0 INTEGER,
  SetAbundanceId1 INTEGER,
  SetAbundanceId2 INTEGER);


> CREATE UNIQUE INDEX ABUNDANCE_UNIQUE_DATA_INDEX ON ABUNDANCE(AbundanceId0,AbundanceId1,AbundanceId2,AbundanceId3,AbundanceId4,AbundanceId5,AbundanceId6,AbundanceId7,AbundanceId8,SetAbundanceId0,SetAbundanceId1,SetAbundanceId2);
> CREATE INDEX ABUNDANCE ON ABUNDANCE(AbundanceId0,AbundanceId1,AbundanceId2,AbundanceId3,AbundanceId4,AbundanceId5,AbundanceId6,AbundanceId7,AbundanceId8);
Error: there is already a table named ABUNDANCE


When trying (good try !) to report an error, make sure you copy/paste
this kind of stuff, of tripple check!

_______________________________________________
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: Crash when querying a unique index containing 12 columns using sub-selects

Matthew Towler
In reply to this post by Matthew Towler
Hello again,

Thanks for the responses, here is some additional information.

> Is it hanging, or is it crashing?  Your statement of the problem is unclear on this point.
It depends on the version. 3.8.0 gives an access violation (a crash) the other versions I have tried hang, in that the call to step "never" returns, where "never" means "takes longer than an hour".

As requested I have generated some explain plans, and I have had a go at making a stand-alone example program but it does not show the problem - I suspect because the distribution of data values is different and results in a different plan (I do have evidence for this view).  Read on for the details...

Firstly, here is a C++11 example application.  This works perfectly with 3.6.16 and 3.17.0.  note I had to change the close call at the end from sqlite_close_v2() to sqlite_close in order for it to work with 3.6.16 - I don't expect this changes the results.

#include "sqlite3.h"
#include <iostream>
#include <vector>

const std::vector<std::string> query_texts =
{
R"(CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL, AbundanceId0 INTEGER, AbundanceId1 INTEGER, AbundanceId2 INTEGER, AbundanceId3 INTEGER, AbundanceId4 INTEGER, AbundanceId5 INTEGER, AbundanceId6 INTEGER, AbundanceId7 INTEGER, AbundanceId8 INTEGER, SetAbundanceId0 INTEGER, SetAbundanceId1 INTEGER, SetAbundanceId2 INTEGER))",
R"(CREATE UNIQUE INDEX ABUNDANCE_UNIQUE_DATA_INDEX ON ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3, AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8, SetAbundanceId0, SetAbundanceId1, SetAbundanceId2))",
R"(CREATE INDEX ABUNDANCE_INDEX ON ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3, AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8))",
R"(CREATE INDEX ABUNDANCE_SetAbundanceId0 ON ABUNDANCE(SetAbundanceId0))",
R"(CREATE INDEX ABUNDANCE_SetAbundanceId1 ON ABUNDANCE(SetAbundanceId1))",
R"(CREATE INDEX ABUNDANCE_SetAbundanceId2 ON ABUNDANCE(SetAbundanceId2))",

R"(    CREATE TABLE ABUNDANCE_0(AbundanceId0 INTEGER PRIMARY KEY NOT NULL, Col6 INTEGER, Col16 INTEGER, Col19 INTEGER, Col3l INTEGER, Col14 INTEGER, Col15 INTEGER, Col3 INTEGER, Col8 INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX ON ABUNDANCE_0(Col6, Col16, Col19, Col3l, Col14, Col15, Col3, Col8))",

R"(    CREATE TABLE ABUNDANCE_1(AbundanceId1 INTEGER PRIMARY KEY NOT NULL, Col16t INTEGER, Col26n INTEGER, Col13o INTEGER, Col9 INTEGER, Col18u INTEGER, Col14i INTEGER, Col3o INTEGER, Col1r INTEGER, Col19i INTEGER, Col6e INTEGER, Col1 INTEGER, Col3u INTEGER, Col19n INTEGER, Col23 INTEGER, Col13n INTEGER, Col16d INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX ON ABUNDANCE_1(Col16t, Col26n, Col13o, Col9, Col18u, Col14i, Col3o, Col1r, Col19i, Col6e, Col1, Col3u, Col19n, Col23, Col13n, Col16d))",

R"(    CREATE TABLE ABUNDANCE_2(AbundanceId2 INTEGER PRIMARY KEY NOT NULL, Col15s INTEGER, Col26r INTEGER, Col22 INTEGER, Col9r INTEGER, Col0l INTEGER, Col0u INTEGER, Col11 INTEGER, Col3d INTEGER, Col20i INTEGER, Col14a INTEGER, Col12i INTEGER, Col3r INTEGER, Col19e INTEGER, Col18e INTEGER, Col0g INTEGER, Col18h INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX ON ABUNDANCE_2(Col15s, Col26r, Col22, Col9r, Col0l, Col0u, Col11, Col3d, Col20i, Col14a, Col12i, Col3r, Col19e, Col18e, Col0g, Col18h))",

R"(    CREATE TABLE ABUNDANCE_3(AbundanceId3 INTEGER PRIMARY KEY NOT NULL, Col14b INTEGER, Col1i INTEGER, Col14d INTEGER, Col25b INTEGER, Col19m INTEGER, Col9n INTEGER, Col13g INTEGER, Col16b INTEGER, Col21 INTEGER, Col7e INTEGER, Col20e INTEGER, Col7a INTEGER, Col8g INTEGER, Col0s INTEGER, Col19b INTEGER, Col12a INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX ON ABUNDANCE_3(Col14b, Col1i, Col14d, Col25b, Col19m, Col9n, Col13g, Col16b, Col21, Col7e, Col20e, Col7a, Col8g, Col0s, Col19b, Col12a))",

R"(    CREATE TABLE ABUNDANCE_4(AbundanceId4 INTEGER PRIMARY KEY NOT NULL, Col4y INTEGER, Col19r INTEGER, Col18b INTEGER, Col8f INTEGER, Col20b INTEGER, Col16r INTEGER, Col3e INTEGER, Col5r INTEGER, Col20l INTEGER, Col3s INTEGER, Col1a INTEGER, Col7d INTEGER, Col25 INTEGER, Col3a INTEGER, Col5u INTEGER, Col20a INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX ON ABUNDANCE_4(Col4y, Col19r, Col18b, Col8f, Col20b, Col16r, Col3e, Col5r, Col20l, Col3s, Col1a, Col7d, Col25, Col3a, Col5u, Col20a))",

R"(    CREATE TABLE ABUNDANCE_5(AbundanceId5 INTEGER PRIMARY KEY NOT NULL, Col3m INTEGER, Col16a INTEGER, Col11r INTEGER, Col0m INTEGER, Col0r INTEGER, Col24e INTEGER, Col16u INTEGER, Col4 INTEGER, Col14p INTEGER, Col20m INTEGER, Col1e INTEGER, Col20h INTEGER, Col8o INTEGER, Col19c INTEGER, Col12u INTEGER, Col20c INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX ON ABUNDANCE_5(Col3m, Col16a, Col11r, Col0m, Col0r, Col24e, Col16u, Col4, Col14p, Col20m, Col1e, Col20h, Col8o, Col19c, Col12u, Col20c))",

R"(    CREATE TABLE ABUNDANCE_6(AbundanceId6 INTEGER PRIMARY KEY NOT NULL, Col21nknown INTEGER, Col0t INTEGER, Col5s INTEGER, Col6m INTEGER, Col6r INTEGER, Col12r INTEGER, Col13d INTEGER, Col14e INTEGER, Col14o INTEGER, Col16o INTEGER, Col18a INTEGER, Col18n INTEGER, Col0c INTEGER, Col1k INTEGER, Col3f INTEGER, Col16m INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX ON ABUNDANCE_6(Col21nknown, Col0t, Col5s, Col6m, Col6r, Col12r, Col13d, Col14e, Col14o, Col16o, Col18a, Col18n, Col0c, Col1k, Col3f, Col16m))",

R"(    CREATE TABLE ABUNDANCE_7(AbundanceId7 INTEGER PRIMARY KEY NOT NULL, Col8e INTEGER, Col1h INTEGER, Col3n INTEGER, Col4b INTEGER, Col4s INTEGER, Col5s INTEGER, Col6l INTEGER, Col6m INTEGER, Col6r INTEGER, Col8s INTEGER, Col12r INTEGER, Col12v INTEGER, Col13c INTEGER, Col13d INTEGER, Col13t INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX ON ABUNDANCE_7(Col8e, Col1h, Col3n, Col4b, Col4s, Col5s, Col6l, Col6m, Col6r, Col8s, Col12r, Col12v, Col13c, Col13d, Col13t))",

R"(    CREATE TABLE ABUNDANCE_8(AbundanceId8 INTEGER PRIMARY KEY NOT NULL, Col14h INTEGER, Col14o INTEGER, Col15g INTEGER, Col16o INTEGER, Col18a INTEGER, Col18f INTEGER, Col18g INTEGER, Col18n INTEGER, Col19g INTEGER, Col20s INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_8_UNIQUE_DATA_INDEX ON ABUNDANCE_8(Col14h, Col14o, Col15g, Col16o, Col18a, Col18f, Col18g, Col18n, Col19g, Col20s))",

R"(    CREATE TABLE ABUNDANCE_SETS_0(SetAbundanceId0 INTEGER PRIMARY KEY NOT NULL, Set4B INTEGER, Set5A INTEGER, Set6A INTEGER, SetX1 INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_SETS_0_UNIQUE_DATA_INDEX ON ABUNDANCE_SETS_0(Set4B, Set5A, Set6A, SetX1))",

R"(    CREATE TABLE ABUNDANCE_SETS_1(SetAbundanceId1 INTEGER PRIMARY KEY NOT NULL, Set5B INTEGER, Set7B INTEGER, Set8Y INTEGER, Set1A INTEGER, Set6B INTEGER, Set8X INTEGER, Set4A INTEGER, Set3A INTEGER, Set2R INTEGER, Set7A INTEGER, SetTR INTEGER, Set4M INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_SETS_1_UNIQUE_DATA_INDEX ON ABUNDANCE_SETS_1(Set5B, Set7B, Set8Y, Set1A, Set6B, Set8X, Set4A, Set3A, Set2R, Set7A, SetTR, Set4M))",

R"(    CREATE TABLE ABUNDANCE_SETS_2(SetAbundanceId2 INTEGER PRIMARY KEY NOT NULL, Set8A INTEGER, SetAN INTEGER, Set3B INTEGER, Set2A INTEGER, SetLN INTEGER, Set2B INTEGER, Set2M INTEGER, Set8Z INTEGER, Set1B INTEGER, Set1M INTEGER, Set3R INTEGER, Set3M INTEGER, Set8B INTEGER, Set1R INTEGER))",
R"(    CREATE UNIQUE INDEX ABUNDANCE_SETS_2_UNIQUE_DATA_INDEX ON ABUNDANCE_SETS_2(Set8A, SetAN, Set3B, Set2A, SetLN, Set2B, Set2M, Set8Z, Set1B, Set1M, Set3R, Set3M, Set8B, Set1R))",

};

const std::string problem_query =
R"(    SELECT AbundanceId FROM[ABUNDANCE]
        WHERE

        ABUNDANCE.AbundanceId0 in(
        SELECT AbundanceId0 FROM ABUNDANCE_0 WHERE
        Col6 = 0 AND Col16 = 0 AND Col19 = 0 AND Col3l = 0 AND Col14 = 0 AND Col15 = 0 AND Col3 = 0
        )

        AND

        ABUNDANCE.AbundanceId1 in(
        SELECT AbundanceId1 FROM ABUNDANCE_1 WHERE
        Col16t = 0 AND Col26n = 0 AND Col13o = 0 AND Col9 = 0 AND Col18u = 0 AND Col14i = 0 AND Col3o = 0 AND Col1r = 0 AND Col19i = 0 AND Col6e = 0 AND Col1 = 0 AND Col3u = 0 AND Col19n = 0 AND Col23 = 0 AND Col13n = 0 AND Col16d = 0
        )

        AND

        ABUNDANCE.AbundanceId2 in(
        SELECT AbundanceId2 FROM ABUNDANCE_2 WHERE
        Col15s = 0 AND Col26r = 0 AND Col22 = 0 AND Col9r = 0 AND Col0l = 0 AND Col0u = 0 AND Col3d = 0 AND Col20i = 0 AND Col3r = 0 AND Col19e = 0 AND Col18e = 0 AND Col0g = 0 AND Col18h = 0
        )

        AND

        ABUNDANCE.AbundanceId3 in(
        SELECT AbundanceId3 FROM ABUNDANCE_3 WHERE
        Col14b = 0 AND Col1i = 0 AND Col14d = 0 AND Col25b = 0 AND Col19m = 0 AND Col9n = 0 AND Col13g = 0 AND Col16b = 0 AND Col21 = 0 AND Col7e = 0 AND Col20e = 0 AND Col7a = 0 AND Col8g = 0 AND Col0s = 0 AND Col19b = 0 AND Col12a = 0
        )

        AND

        ABUNDANCE.AbundanceId4 in(
        SELECT AbundanceId4 FROM ABUNDANCE_4 WHERE
        Col4y = 0 AND Col19r = 0 AND Col8f = 0 AND Col20b = 0 AND Col16r = 0 AND Col3e = 0 AND Col5r = 0 AND Col20l = 0 AND Col1a = 0 AND Col7d = 0 AND Col25 = 0 AND Col3a = 0 AND Col5u = 0 AND Col20a = 0
        )

        AND

        ABUNDANCE.AbundanceId5 in(
        SELECT AbundanceId5 FROM ABUNDANCE_5 WHERE
        Col3m = 0 AND Col16a = 0 AND Col11r = 0 AND Col0m = 0 AND Col0r = 0 AND Col24e = 0 AND Col16u = 0 AND Col14p = 0 AND Col20m = 0 AND Col1e = 0 AND Col20h = 0 AND Col8o = 0 AND Col19c = 0 AND Col12u = 0 AND Col20c = 0
        )

        AND

        ABUNDANCE.AbundanceId6 in(
        SELECT AbundanceId6 FROM ABUNDANCE_6 WHERE
        Col21nknown = 0 AND Col0t = 0 AND Col5s = 0 AND Col6m = 0 AND Col12r = 0 AND Col13d = 0 AND Col14e = 0 AND Col14o = 0 AND Col16o = 0 AND Col18a = 0 AND Col18n = 0 AND Col0c = 0 AND Col1k = 0 AND Col3f = 0 AND Col16m = 0
        )

        AND

        ABUNDANCE.AbundanceId7 in(
        SELECT AbundanceId7 FROM ABUNDANCE_7 WHERE
        Col8e = 0 AND Col1h = 0 AND Col3n = 0 AND Col4b = 0 AND Col4s = 0 AND Col5s = 0 AND Col6l = 0 AND Col6m = 0 AND Col8s = 0 AND Col12r = 0 AND Col12v = 0 AND Col13c = 0 AND Col13d = 0 AND Col13t = 0
        )

        AND

        ABUNDANCE.AbundanceId8 in(
        SELECT AbundanceId8 FROM ABUNDANCE_8 WHERE
        Col14h = 0 AND Col14o = 0 AND Col15g = 0 AND Col16o = 0 AND Col18a = 0 AND Col18f = 0 AND Col18g = 0 AND Col18n = 0 AND Col19g = 0 AND Col20s = 0
        )

        AND

        ABUNDANCE.SetAbundanceId0 in(
        SELECT SetAbundanceId0 FROM ABUNDANCE_SETS_0 WHERE
        SetX1 >= 1 AND SetX1 <= 9999
        )
        AND
        ABUNDANCE.SetAbundanceId1 in(
        SELECT SetAbundanceId1 FROM ABUNDANCE_SETS_1 WHERE
        Set1A >= 1 AND Set1A <= 9999
        )
    )";

void run_query(
    sqlite3* sqlite_connection,
    const std::string& query)
{
    sqlite3_stmt* statement = nullptr;
    const char* sql_tail = nullptr;
    int ret = sqlite3_prepare_v2(sqlite_connection, query.c_str(), query.size(), &statement, &sql_tail);

    if (ret != SQLITE_OK && ret != SQLITE_DONE)
        std::cout << "QUERY RETURNED: " << ret << std::endl;

    while (sqlite3_step(statement) == SQLITE_ROW)
    {
        size_t count = sqlite3_column_count(statement);
        for (size_t i = 0; i < count; ++i)
        {
            const char *o = (const char *)sqlite3_column_text(statement, i);
            std::cout << o << "|";
        }
        std::cout << std::endl;
    }

    sqlite3_finalize(statement);
}

int run_single_insert(
    sqlite3* sqlite_connection,
    const std::string& query,
    const std::vector<int>& values)
{
    sqlite3_stmt* statement = nullptr;
    const char* sql_tail = nullptr;
    int ret = sqlite3_prepare_v2(sqlite_connection, query.c_str(), query.size(), &statement, &sql_tail);

    if (ret != SQLITE_OK && ret != SQLITE_DONE)
        std::cout << "INSERT QUERY RETURNED: " << ret;

    for (int param = 0 ; param != values.size() ; ++param)
        sqlite3_bind_int(statement, param + 1, values[param]);

    ret = sqlite3_step(statement);

    if (ret != SQLITE_OK && ret != SQLITE_DONE)
        std::cout << "INSERT STEP RETURNED: " << ret << std::endl;

    int id = static_cast<int>(sqlite3_last_insert_rowid(sqlite_connection));
    sqlite3_finalize(statement);
    return id;
}

std::vector<int> run_bulk_insert(
    sqlite3* sqlite_connection,
    const std::string& query,
    const std::vector<int>& first_row_values,
    int row_count)
{
    sqlite3_stmt* statement = nullptr;
    const char* sql_tail = nullptr;
    int ret = sqlite3_prepare_v2(sqlite_connection, query.c_str(), query.size(), &statement, &sql_tail);

    if (ret != SQLITE_OK && ret != SQLITE_DONE)
        std::cout << "INSERT QUERY RETURNED: " << ret;

    std::vector<int> returned_ids;
    std::vector<int> values(first_row_values);

    for ( int row = 0 ; row != row_count ; ++row )
    {
        for (int param = 0; param != values.size(); ++param)
            sqlite3_bind_int(statement, param + 1, values[param]);

        ret = sqlite3_step(statement);

        if (ret != SQLITE_OK && ret != SQLITE_DONE)
            std::cout << "INSERT STEP RETURNED: " << ret << std::endl;

        returned_ids.push_back(static_cast<int>(sqlite3_last_insert_rowid(sqlite_connection)) );
   
        sqlite3_clear_bindings(statement);
        sqlite3_reset(statement);

        // first column is a count so rows are guaranteed unique
        ++values[0];

        for (size_t i = 1; i < values.size(); ++i)
            values[i] = rand() % 30;
    }
       
    sqlite3_finalize(statement);
    return returned_ids;
}

void run_inserts(sqlite3* sqlite_connection)
{
    struct InsertParameters
    {
        std::string query;
        int parameter_count;
    };

    const std::vector< InsertParameters > subtable_inserts =
    {
        { R"(INSERT INTO ABUNDANCE_0(Col6, Col16, Col19, Col3l, Col14, Col15, Col3, Col8) VALUES(?,?,?,?,?,?,?,?) )", 8 },
        { R"(INSERT INTO ABUNDANCE_1(Col16t, Col26n, Col13o, Col9, Col18u, Col14i, Col3o, Col1r, Col19i, Col6e, Col1, Col3u, Col19n, Col23, Col13n, Col16d) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
        { R"(INSERT INTO ABUNDANCE_2(Col15s, Col26r, Col22, Col9r, Col0l, Col0u, Col11, Col3d, Col20i, Col14a, Col12i, Col3r, Col19e, Col18e, Col0g, Col18h) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
        { R"(INSERT INTO ABUNDANCE_3(Col14b, Col1i, Col14d, Col25b, Col19m, Col9n, Col13g, Col16b, Col21, Col7e, Col20e, Col7a, Col8g, Col0s, Col19b, Col12a) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
        { R"(INSERT INTO ABUNDANCE_4(Col4y, Col19r, Col18b, Col8f, Col20b, Col16r, Col3e, Col5r, Col20l, Col3s, Col1a, Col7d, Col25, Col3a, Col5u, Col20a) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
        { R"(INSERT INTO ABUNDANCE_5(Col3m, Col16a, Col11r, Col0m, Col0r, Col24e, Col16u, Col4, Col14p, Col20m, Col1e, Col20h, Col8o, Col19c, Col12u, Col20c) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
        { R"(INSERT INTO ABUNDANCE_6(Col21nknown, Col0t, Col5s, Col6m, Col6r, Col12r, Col13d, Col14e, Col14o, Col16o, Col18a, Col18n, Col0c, Col1k, Col3f, Col16m) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 16 },
        { R"(INSERT INTO ABUNDANCE_7(Col8e, Col1h, Col3n, Col4b, Col4s, Col5s, Col6l, Col6m, Col6r, Col8s, Col12r, Col12v, Col13c, Col13d, Col13t) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 15 },
        { R"(INSERT INTO ABUNDANCE_8(Col14h, Col14o, Col15g, Col16o, Col18a, Col18f, Col18g, Col18n, Col19g, Col20s) VALUES(?,?,?,?,?,?,?,?,?,?) )", 10 },
        { R"(INSERT INTO ABUNDANCE_SETS_0(Set4B, Set5A, Set6A, SetX1) VALUES(?,?,?,?) )", 4 },
        { R"(INSERT INTO ABUNDANCE_SETS_1(Set5B, Set7B, Set8Y, Set1A, Set6B, Set8X, Set4A, Set3A, Set2R, Set7A, SetTR, Set4M) VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )", 12 },
        { R"(INSERT INTO ABUNDANCE_SETS_2(Set8A, SetAN, Set3B, Set2A, SetLN, Set2B, Set2M, Set8Z, Set1B, Set1M, Set3R, Set3M, Set8B, Set1R) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?) )", 14 }
    };

    std::string main_table_insert = R"(INSERT INTO ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3, AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8, SetAbundanceId0, SetAbundanceId1, SetAbundanceId2)
                                      VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )";

    const int nrows = 10;
    std::vector< std::vector< int > > inserted_ids;

    std::vector<int> insert_ids;
    for ( const auto& subtable : subtable_inserts)
    {
        std::vector<int> first_row_values(subtable.parameter_count, 0);
        inserted_ids.push_back(run_bulk_insert(sqlite_connection, subtable.query, first_row_values, nrows));
        std::cout << "Finished bulk insert: " << std::endl;
    }

    for (int row = 0; row != nrows; ++row)
    {
        std::vector<int> values;
        for (const auto& ids : inserted_ids)
            values.push_back(ids[row]);

        run_single_insert(sqlite_connection, main_table_insert, values);

        if (row % 10 == 0)
            std::cout << "Inserted row: " << row << std::endl;
    }
}

int main(int argc, char **argv)
{
    const char* path = "temp_db.sqlite";
    unlink(path);

    sqlite3* sqlite_connection;
    int ret;
    ret = sqlite3_open_v2(path,
        &sqlite_connection,
        SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
        0);

    for (const auto& query : query_texts)
        run_query(sqlite_connection, query);

    run_inserts(sqlite_connection);
   
    std::cout << "running final query" << std::endl;
    run_query(sqlite_connection, problem_query);

    sqlite3_close(sqlite_connection);
}

Running the above with "EXPLAIN QUERY PLAN" prepended to the text of "problem_query" gives the following

Version 3.6.16

0|0|TABLE ABUNDANCE WITH INDEX ABUNDANCE_SetAbundanceId1|
0|0|TABLE ABUNDANCE_SETS_1|
0|0|TABLE ABUNDANCE_0 WITH INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_1 WITH INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_2 WITH INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_3 WITH INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_4 WITH INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_5 WITH INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_6 WITH INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_7 WITH INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_8 WITH INDEX ABUNDANCE_8_UNIQUE_DATA_INDEX|
0|0|TABLE ABUNDANCE_SETS_0|

Version 3.17.0

0|0|0|SEARCH TABLE ABUNDANCE USING COVERING INDEX ABUNDANCE_UNIQUE_DATA_INDEX (AbundanceId0=? AND AbundanceId1=? AND AbundanceId2=? AND AbundanceId3=? AND AbundanceId4=? AND AbundanceId5=? AND AbundanceId6=? AND AbundanceId7=? AND AbundanceId8=? AND SetAbundanceId0=? AND SetAbundanceId1=?)|
0|0|0|EXECUTE LIST SUBQUERY 1|
1|0|0|SEARCH TABLE ABUNDANCE_0 USING COVERING INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX (Col6=? AND Col16=? AND Col19=? AND Col3l=? AND Col14=? AND Col15=? AND Col3=?)|
0|0|0|EXECUTE LIST SUBQUERY 2|
2|0|0|SEARCH TABLE ABUNDANCE_1 USING COVERING INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX (Col16t=? AND Col26n=? AND Col13o=? AND Col9=? AND Col18u=? AND Col14i=? AND Col3o=? AND Col1r=? AND Col19i=? AND Col6e=? AND Col1=? AND Col3u=? AND Col19n=? AND Col23=? AND Col13n=? AND Col16d=?)|
0|0|0|EXECUTE LIST SUBQUERY 3|
3|0|0|SEARCH TABLE ABUNDANCE_2 USING COVERING INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX (Col15s=? AND Col26r=? AND Col22=? AND Col9r=? AND Col0l=? AND Col0u=?)|
0|0|0|EXECUTE LIST SUBQUERY 4|
4|0|0|SEARCH TABLE ABUNDANCE_3 USING COVERING INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX (Col14b=? AND Col1i=? AND Col14d=? AND Col25b=? AND Col19m=? AND Col9n=? AND Col13g=? AND Col16b=? AND Col21=? AND Col7e=? AND Col20e=? AND Col7a=? AND Col8g=? AND Col0s=? AND Col19b=? AND Col12a=?)|
0|0|0|EXECUTE LIST SUBQUERY 5|
5|0|0|SEARCH TABLE ABUNDANCE_4 USING COVERING INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX (Col4y=? AND Col19r=?)|
0|0|0|EXECUTE LIST SUBQUERY 6|
6|0|0|SEARCH TABLE ABUNDANCE_5 USING COVERING INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX (Col3m=? AND Col16a=? AND Col11r=? AND Col0m=? AND Col0r=? AND Col24e=? AND Col16u=?)|
0|0|0|EXECUTE LIST SUBQUERY 7|
7|0|0|SEARCH TABLE ABUNDANCE_6 USING COVERING INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX (Col21nknown=? AND Col0t=? AND Col5s=? AND Col6m=?)|
0|0|0|EXECUTE LIST SUBQUERY 8|
8|0|0|SEARCH TABLE ABUNDANCE_7 USING COVERING INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX (Col8e=? AND Col1h=? AND Col3n=? AND Col4b=? AND Col4s=? AND Col5s=? AND Col6l=? AND Col6m=?)|
0|0|0|EXECUTE LIST SUBQUERY 9|
9|0|0|SEARCH TABLE ABUNDANCE_8 USING COVERING INDEX ABUNDANCE_8_UNIQUE_DATA_INDEX (Col14h=? AND Col14o=? AND Col15g=? AND Col16o=? AND Col18a=? AND Col18f=? AND Col18g=? AND Col18n=? AND Col19g=? AND Col20s=?)|
0|0|0|EXECUTE LIST SUBQUERY 10|
10|0|0|SCAN TABLE ABUNDANCE_SETS_0|
0|0|0|EXECUTE LIST SUBQUERY 11|
11|0|0|SCAN TABLE ABUNDANCE_SETS_1|

By way of comparison, and because the example program does not actually show the issue, I also ran an explain query plan on the original query in the original database.  This gives the following different result (slightly obfuscated by hand)

Version 3.6.16 looks broadly similar to the example, except for abundance_8 at the end.  This may be due to this particular table having 100 or 1000 rows in the example and only one or two rows in the full database.

0|0|TABLE ABUNDANCE WITH INDEX ABUNDANCE_INDEX_SetAbundanceId0
0|0|TABLE ABUNDANCE_SETS_0
0|0|TABLE ABUNDANCE_0 WITH INDEX ABUNDANCE_0_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_1 WITH INDEX ABUNDANCE_1_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_2 WITH INDEX ABUNDANCE_2_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_3 WITH INDEX ABUNDANCE_3_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_4 WITH INDEX ABUNDANCE_4_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_5 WITH INDEX ABUNDANCE_5_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_6 WITH INDEX ABUNDANCE_6_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_7 WITH INDEX ABUNDANCE_7_UNIQUE_DATA_INDEX
0|0|TABLE ABUNDANCE_8
0|0|TABLE ABUNDANCE_SETS_1

Version 3.17.0 - which is quite different.  I have edited out most of the column names but I don't feel this changes the information.  The use of "ANY" in the final clause is quite different from before.  I noted that where the UNIQUE_DATA_INDEX in the subtables are used, it is only a subset of the columns that are listed due to only some of them being in the WHERE of the subqueries, so the index will need to skip some of the levels as the columns used are not always a left prefix of the index.

0|0|0|SEARCH TABLE ABUNDANCE USING COVERING INDEX ABUNDANCE_UNIQUE_DATA_INDEX (AbundanceId0=? AND AbundanceId1=? AND AbundanceId2=? AND AbundanceId3=? AND AbundanceId4=? AND AbundanceId5=? AND AbundanceId6=? AND AbundanceId7=? AND AbundanceId8=? AND SetAbundanceId0=? AND SetAbundanceId1=?)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE ABUNDANCE_S_0 USING COVERING INDEX ABUNDANCE_S_0_UNIQUE_DATA_INDEX ([7 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SEARCH TABLE ABUNDANCE_S_1 USING COVERING INDEX ABUNDANCE_S_1_UNIQUE_DATA_INDEX ([16 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 3
3|0|0|SEARCH TABLE ABUNDANCE_S_2 USING COVERING INDEX ABUNDANCE_S_2_UNIQUE_DATA_INDEX ([6 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 4
4|0|0|SEARCH TABLE ABUNDANCE_S_3 USING COVERING INDEX ABUNDANCE_S_3_UNIQUE_DATA_INDEX ([16 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 5
5|0|0|SEARCH TABLE ABUNDANCE_S_4 USING COVERING INDEX ABUNDANCE_S_4_UNIQUE_DATA_INDEX ([2 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 6
6|0|0|SEARCH TABLE ABUNDANCE_S_5 USING COVERING INDEX ABUNDANCE_S_5_UNIQUE_DATA_INDEX ([7 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 7
7|0|0|SCAN TABLE ABUNDANCE_S_6
0|0|0|EXECUTE LIST SUBQUERY 8
8|0|0|SEARCH TABLE ABUNDANCE_S_7 USING COVERING INDEX ABUNDANCE_S_7_UNIQUE_DATA_INDEX ([8 columns listed])
0|0|0|EXECUTE LIST SUBQUERY 9
9|0|0|SCAN TABLE ABUNDANCE_S_8
0|0|0|EXECUTE LIST SUBQUERY 10
10|0|0|SCAN TABLE ABUNDANCE_SETS_0
0|0|0|EXECUTE LIST SUBQUERY 11
11|0|0|SEARCH TABLE ABUNDANCE_SETS_1 USING COVERING INDEX ABUNDANCE_SETS_1_UNIQUE_DATA_INDEX (ANY(Col1) AND ANY(Col2) AND ANY(Col3) AND Col4>? AND Col4<?)

It might help me reproduce the issue to get an idea of if the data content of the table and index cardinality etc. are likely to have caused the above differences in the query plans between my search on the whole database and the stand alone example application.

Matthew
_______________________________________________
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: Crash when querying a unique index containing 12 columns using sub-selects

Richard Hipp-3
On 1/16/18, Matthew Towler <[hidden email]> wrote:
>
> Firstly, here is a C++11 example application.

Does not compile.  These are the errors:

x2.cpp:53:2: warning: missing terminating " character
 R"(    SELECT AbundanceId FROM[ABUNDANCE]
  ^
x2.cpp:53:1: error: missing terminating " character
 R"(    SELECT AbundanceId FROM[ABUNDANCE]
 ^
x2.cpp:128:6: warning: missing terminating " character
     )";
      ^
x2.cpp:128:5: error: missing terminating " character
     )";
     ^
x2.cpp:246:38: warning: missing terminating " character
     std::string main_table_insert = R"(INSERT INTO
ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3,
AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8,
SetAbundanceId0, SetAbundanceId1, SetAbundanceId2)
                                      ^
x2.cpp:246:5: error: missing terminating " character
     std::string main_table_insert = R"(INSERT INTO
ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3,
AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8,
SetAbundanceId0, SetAbundanceId1, SetAbundanceId2)
     ^
x2.cpp:247:72: warning: missing terminating " character
                                       VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )";
                                                                        ^
x2.cpp:247:39: error: missing terminating " character
                                       VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )";
                                       ^
x2.cpp:7:1: error: ‘R’ was not declared in this scope
 R"(CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,
AbundanceId0 INTEGER, AbundanceId1 INTEGER, AbundanceId2 INTEGER,
AbundanceId3 INTEGER, AbundanceId4 INTEGER, AbundanceId5 INTEGER,
AbundanceId6 INTEGER, AbundanceId7 INTEGER, AbundanceId8 INTEGER,
SetAbundanceId0 INTEGER, SetAbundanceId1 INTEGER, SetAbundanceId2
INTEGER))",
 ^
x2.cpp:7:2: error: expected ‘}’ before string constant
 R"(CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,
AbundanceId0 INTEGER, AbundanceId1 INTEGER, AbundanceId2 INTEGER,
AbundanceId3 INTEGER, AbundanceId4 INTEGER, AbundanceId5 INTEGER,
AbundanceId6 INTEGER, AbundanceId7 INTEGER, AbundanceId8 INTEGER,
SetAbundanceId0 INTEGER, SetAbundanceId1 INTEGER, SetAbundanceId2
INTEGER))",
  ^
x2.cpp:7:2: error: in C++98 ‘query_texts’ must be initialized by
constructor, not by ‘{...}’
x2.cpp:7:2: error: could not convert ‘{<expression error>}’ from
‘<brace-enclosed initializer list>’ to ‘const
std::vector<std::__cxx11::basic_string<char> >’
x2.cpp:7:2: error: expected ‘,’ or ‘;’ before string constant
x2.cpp:50:1: error: expected declaration before ‘}’ token
 };
 ^

--
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: Crash when querying a unique index containing 12 columns using sub-selects

Dominique Devienne
On Tue, Jan 16, 2018 at 4:10 PM, Richard Hipp <[hidden email]> wrote:

> On 1/16/18, Matthew Towler <[hidden email]> wrote:
> >
> > Firstly, here is a C++11 example application.
>
> Does not compile.  These are the errors:
>
> x2.cpp:53:2: warning: missing terminating " character
>  R"(    SELECT AbundanceId FROM[ABUNDANCE]
>   ^
> ...

x2.cpp:7:2: error: in C++98 ‘query_texts’ must be initialized by
> constructor, not by ‘{...}’
>

You're not compiling in C++11 mode, are you?
Probably use -std=c++11 or perhaps -std=gnu++11 on your g++ command line.
--DD
_______________________________________________
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: Crash when querying a unique index containing 12 columns using sub-selects

Richard Hipp-3
On 1/16/18, Dominique Devienne <[hidden email]> wrote:
> You're not compiling in C++11 mode, are you?
> Probably use -std=c++11 or perhaps -std=gnu++11 on your g++ command line.

Did that.  It compiles now.  But it also just works.  There is no
slowdown.  Everything is very fast, regardless of which SQLite version
I use.  I added a line:

       std::cout << "SQLite version: " << sqlite3_libversion() << " "
<< sqlite3_sourceid() << std::endl;

Just to verify that I was testing against the particular versions of
SQLite that allegedly do not work.

I think it is now back to Matthew to come up with a new test case that
actually demonstrates his problem.
--
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: Crash when querying a unique index containing 12 columns using sub-selects

Matthew Towler
In reply to this post by Matthew Towler
Hi

> I think it is now back to Matthew to come up with a new test case that actually demonstrates his problem.

I believe I stated in my earlier message that the example test code did not show the issue - apologies that you spent time re-proving this.

What would really help me to achieve a failing test case is if someone could please look at the query plans I provided and give me some vague pointers as to what kind of changes to the data in the tables might cause the difference between the failing and passing query plans for version 1.17.0 (near the bottom of the last message).  This might enable me to adjust the data generation in the example application to give a failing result.

Thanks

Matthew
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users