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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
