Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 1/16/18, Dominique Devienne 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On Tue, Jan 16, 2018 at 4:10 PM, Richard Hipp wrote:
> On 1/16/18, Matthew Towler 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 1/16/18, Matthew Towler 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 ‘{}’ from
‘’ to ‘const
std::vector >’
x2.cpp:7:2: error: expected ‘,’ or ‘;’ before string constant
x2.cpp:50:1: error: expected declaration before ‘}’ token
};
^
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
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
#include
const std::vector 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 TA
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 11 Jan 2018, at 5:01pm, Matthew Towler 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 1/11/18, Matthew Towler 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
