Re: [sqlite] sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)
On 2/19/19, Ignacio Losiggio wrote: > > CREATE TABLE IF NOT EXISTS t (id integer NOT NULL PRIMARY KEY > AUTOINCREMENT); > INSERT INTO t VALUES(1); > SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id WHERE a.id IN > (1, 2, 3); I have checked in a fix on trunk (https://www.sqlite.org/src/info/b5f90bfe6295ab3a) but the ticket (https://www.sqlite.org/src/info/df46dfb631f75694) has been kept open pending further testing and analysis. If everything still looks good in the morning, I'll close the ticket then. Thank you for an excellent bug report, Ignacio. It is always great when we can get a concise and easily reproducible testcase like this. The fact that you went to the trouble to bisect is above and beyond the call of duty. Thanks. -- 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] sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)
... > To: SQLite mailing list > Subject: Re: [sqlite] sqlite segfault on INNER JOIN ON (...) > + WHERE fieldIN (list, of, items) > > > If you compile with assert() statements enabled (using the > --enable-debug option with ./configure or otherwise adding the > -DSQLITE_DEBUG flag) then you will hit an assertion fault earlier. > The problem is that the same ephemeral table - the in-memory table > that is constructed to hold the RHS of the IN operator "(1,2,3)" - is > being used for two incompatible purposes. The check-in that caused ... Thanks for the info. I wonder if it makes sense for me to include the SQLITE_DEBUG in all debug configurations of my product? Does it have any untoward effect other than maybe slowdowns etc? -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)
Additional background: The optimization that caused this bug was an attempt to fix a performance regression reported here (https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg113314.html) which was in turned caused by a fix to a prior bug here (https://sqlite.org/src/info/787fa716be3a7f650cac). On 2/19/19, Richard Hipp wrote: > Further debugging hints (for those who are interested): > > If you compile with --enable-debug and run the script below, it will > give you more information about what is going on in the bytecode: > > CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY); > INSERT INTO t1 VALUES(1); > .eqp trace > SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3); > > On 2/19/19, Richard Hipp wrote: >> If you compile with assert() statements enabled (using the >> --enable-debug option with ./configure or otherwise adding the >> -DSQLITE_DEBUG flag) then you will hit an assertion fault earlier. >> The problem is that the same ephemeral table - the in-memory table >> that is constructed to hold the RHS of the IN operator "(1,2,3)" - is >> being used for two incompatible purposes. The check-in that caused >> the problem (https://www.sqlite.org/src/info/e130319317e76119) tries >> to reuse the RHS of IN operators so that they do not have to be >> computed multiple times. Check-in e130319317 is an optimization. But >> apparently the optimization is a little too aggressive. I did not >> foresee that the RHS of the IN operator might be used in incompatible >> ways. >> >> The first use of the (1,2,3) table is to drive a loop. The loop runs >> over every entry in the (1,2,3) table, then seeks in the "A" table to >> see if a row exists with the same value "A.ID" value. >> >> The second use of the (1,2,3) table is to verify that the "B.ID" value >> exists in the table. >> >> The first use wants the (1,2,3) table to be a rowid-table with no >> content. It just stores rowids. The second use wants the table to be >> an index, for fast lookups. >> >> The fault occurs when the bytecode tries to use the (1,2,3) table, >> which is a rowid table, as an index. >> >> On 2/19/19, dave wrote: >>> Wow; can confirm. I crashed it in my debugger in the amalgamation of 3.27.1 in the function SQLITE_PRIVATE RecordCompare sqlite3VdbeFindCompare(UnpackedRecord *p) At line 80720, if( p->pKeyInfo->nAllField<=13 ) in that case: pKeyInfo is NULL >>> >>> Lastly, if it helps, converting the query to: >>> >>> SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id WHERE >>> a.id = 1 or a.id = 2 or a.id = 3; >>> >>> Does /not/ crash. >>> >>> (and nice work on the bisect! Lol) >>> >>> -dave >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> > > > -- > D. Richard Hipp > d...@sqlite.org > -- 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] sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)
Further debugging hints (for those who are interested): If you compile with --enable-debug and run the script below, it will give you more information about what is going on in the bytecode: CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY); INSERT INTO t1 VALUES(1); .eqp trace SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3); On 2/19/19, Richard Hipp wrote: > If you compile with assert() statements enabled (using the > --enable-debug option with ./configure or otherwise adding the > -DSQLITE_DEBUG flag) then you will hit an assertion fault earlier. > The problem is that the same ephemeral table - the in-memory table > that is constructed to hold the RHS of the IN operator "(1,2,3)" - is > being used for two incompatible purposes. The check-in that caused > the problem (https://www.sqlite.org/src/info/e130319317e76119) tries > to reuse the RHS of IN operators so that they do not have to be > computed multiple times. Check-in e130319317 is an optimization. But > apparently the optimization is a little too aggressive. I did not > foresee that the RHS of the IN operator might be used in incompatible > ways. > > The first use of the (1,2,3) table is to drive a loop. The loop runs > over every entry in the (1,2,3) table, then seeks in the "A" table to > see if a row exists with the same value "A.ID" value. > > The second use of the (1,2,3) table is to verify that the "B.ID" value > exists in the table. > > The first use wants the (1,2,3) table to be a rowid-table with no > content. It just stores rowids. The second use wants the table to be > an index, for fast lookups. > > The fault occurs when the bytecode tries to use the (1,2,3) table, > which is a rowid table, as an index. > > On 2/19/19, dave wrote: >> >>> Wow; can confirm. I crashed it in my debugger in the >>> amalgamation of 3.27.1 >>> in the function SQLITE_PRIVATE RecordCompare >>> sqlite3VdbeFindCompare(UnpackedRecord *p) >>> >>> At line 80720, if( p->pKeyInfo->nAllField<=13 ) >>> >>> in that case: >>> pKeyInfo is NULL >> >> Lastly, if it helps, converting the query to: >> >> SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id WHERE >> a.id = 1 or a.id = 2 or a.id = 3; >> >> Does /not/ crash. >> >> (and nice work on the bisect! Lol) >> >> -dave >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > -- > D. Richard Hipp > d...@sqlite.org > -- 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] sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)
If you compile with assert() statements enabled (using the --enable-debug option with ./configure or otherwise adding the -DSQLITE_DEBUG flag) then you will hit an assertion fault earlier. The problem is that the same ephemeral table - the in-memory table that is constructed to hold the RHS of the IN operator "(1,2,3)" - is being used for two incompatible purposes. The check-in that caused the problem (https://www.sqlite.org/src/info/e130319317e76119) tries to reuse the RHS of IN operators so that they do not have to be computed multiple times. Check-in e130319317 is an optimization. But apparently the optimization is a little too aggressive. I did not foresee that the RHS of the IN operator might be used in incompatible ways. The first use of the (1,2,3) table is to drive a loop. The loop runs over every entry in the (1,2,3) table, then seeks in the "A" table to see if a row exists with the same value "A.ID" value. The second use of the (1,2,3) table is to verify that the "B.ID" value exists in the table. The first use wants the (1,2,3) table to be a rowid-table with no content. It just stores rowids. The second use wants the table to be an index, for fast lookups. The fault occurs when the bytecode tries to use the (1,2,3) table, which is a rowid table, as an index. On 2/19/19, dave wrote: > >> Wow; can confirm. I crashed it in my debugger in the >> amalgamation of 3.27.1 >> in the function SQLITE_PRIVATE RecordCompare >> sqlite3VdbeFindCompare(UnpackedRecord *p) >> >> At line 80720, if( p->pKeyInfo->nAllField<=13 ) >> >> in that case: >> pKeyInfo is NULL > > Lastly, if it helps, converting the query to: > > SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id WHERE > a.id = 1 or a.id = 2 or a.id = 3; > > Does /not/ crash. > > (and nice work on the bisect! Lol) > > -dave > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)
> Wow; can confirm. I crashed it in my debugger in the > amalgamation of 3.27.1 > in the function SQLITE_PRIVATE RecordCompare > sqlite3VdbeFindCompare(UnpackedRecord *p) > > At line 80720, if( p->pKeyInfo->nAllField<=13 ) > > in that case: > pKeyInfo is NULL Lastly, if it helps, converting the query to: SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id WHERE a.id = 1 or a.id = 2 or a.id = 3; Does /not/ crash. (and nice work on the bisect! Lol) -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)
> -Original Message- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Ignacio Losiggio > Sent: Tuesday, February 19, 2019 6:27 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] sqlite segfault on INNER JOIN ON (...) + > WHERE field IN (list, of, items) > > > (I'm sorry if my description of the bug is not good. I tried > to get as > much > information possible before sending this email). > > A few days ago I encountered some weird segfaults on my django > application. > After a bit of troubleshooting I realized that the applications was > crashing on > the sqlite library. > > I dumped the database and recompiled python with debugging symbols to > get the > segfaulting query. After some digging I managed to get a small > reproduction of > the segfault: > > CREATE TABLE IF NOT EXISTS t (id integer NOT NULL PRIMARY KEY > AUTOINCREMENT); > INSERT INTO t VALUES(1); > SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id > WHERE a.id IN > (1, 2, 3); > > Once I got that I installed fossil and bisected the segfault to the > e130319317e76119 version, below is the output of `fossil chart`: > > 1 BAD 2019-02-19 20:29:05 f16d127c3b4a735a > 3 BAD 2019-01-14 13:32:15 ddc3697efd61830f > 5 BAD 2019-01-05 21:09:37 598d7358e7329f0d > 7 BAD 2019-01-03 15:17:01 bef216dfa1456a78 > 8 BAD 2019-01-01 19:17:42 911342f7512145a8 > 9 BAD 2018-12-31 21:43:55 b57c545a384ab5d6 > 10 BAD 2018-12-31 20:39:37 e130319317e76119 > 11 GOOD2018-12-31 17:58:05 f856676c8438dbf5 CURRENT > 6 GOOD2018-12-31 16:36:42 4678cb1044f0b4dc > 4 GOOD2018-12-24 20:00:27 3873941c4fb9aa2d > 2 GOOD2018-12-01 12:34:55 bf8c1b2b7a5960c2 > > I don't have experience with fossil nor sqlite3 nor SQL in > general, so > I really > don't know how to add more information to this report. > > > Thanks in advance. Wow; can confirm. I crashed it in my debugger in the amalgamation of 3.27.1 in the function SQLITE_PRIVATE RecordCompare sqlite3VdbeFindCompare(UnpackedRecord *p) At line 80720, if( p->pKeyInfo->nAllField<=13 ) in that case: pKeyInfo is NULL Stack traceback: sqlite3.exe!sqlite3VdbeFindCompare(UnpackedRecord * p) Line 80720 sqlite3.exe!sqlite3BtreeMovetoUnpacked(BtCursor * pCur, UnpackedRecord * pIdxKey, __int64 intKey, int biasRight, int * pRes) Line 68434 sqlite3.exe!sqlite3VdbeExec(Vdbe * p) Line 87626 sqlite3.exe!sqlite3Step(Vdbe * p) Line 81718 sqlite3.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 81784 sqlite3.exe!exec_prepared_stmt(ShellState * pArg, sqlite3_stmt * pStmt) Line 10445 sqlite3.exe!shell_exec(ShellState * pArg, const char * zSql, char * * pzErrMsg) Line 10752 sqlite3.exe!runOneSqlLine(ShellState * p, char * zSql, _iobuf * in, int startline) Line 16106 sqlite3.exe!process_input(ShellState * p) Line 16206 sqlite3.exe!wmain(int argc, wchar_t * * wargv) Line 16959 Hth a little. Cheers, -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)
(I'm sorry if my description of the bug is not good. I tried to get as much information possible before sending this email). A few days ago I encountered some weird segfaults on my django application. After a bit of troubleshooting I realized that the applications was crashing on the sqlite library. I dumped the database and recompiled python with debugging symbols to get the segfaulting query. After some digging I managed to get a small reproduction of the segfault: CREATE TABLE IF NOT EXISTS t (id integer NOT NULL PRIMARY KEY AUTOINCREMENT); INSERT INTO t VALUES(1); SELECT a.id FROM t AS a INNER JOIN t as b ON a.id = b.id WHERE a.id IN (1, 2, 3); Once I got that I installed fossil and bisected the segfault to the e130319317e76119 version, below is the output of `fossil chart`: 1 BAD 2019-02-19 20:29:05 f16d127c3b4a735a 3 BAD 2019-01-14 13:32:15 ddc3697efd61830f 5 BAD 2019-01-05 21:09:37 598d7358e7329f0d 7 BAD 2019-01-03 15:17:01 bef216dfa1456a78 8 BAD 2019-01-01 19:17:42 911342f7512145a8 9 BAD 2018-12-31 21:43:55 b57c545a384ab5d6 10 BAD 2018-12-31 20:39:37 e130319317e76119 11 GOOD2018-12-31 17:58:05 f856676c8438dbf5 CURRENT 6 GOOD2018-12-31 16:36:42 4678cb1044f0b4dc 4 GOOD2018-12-24 20:00:27 3873941c4fb9aa2d 2 GOOD2018-12-01 12:34:55 bf8c1b2b7a5960c2 I don't have experience with fossil nor sqlite3 nor SQL in general, so I really don't know how to add more information to this report. Thanks in advance. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] virtual table xBestIndex and SQLITE_CONSTRAINT andzErrMsg
> On 2/19/19, dave wrote: > > addition, but I have lost a capability relative to the > prior scheme of using > > high query cost along with a special flag communicated in > pIdxInfo->idxNum, > > that being the ablilty to emit contextual info as to why > the query failed. > > Yeah. There is no way to report an error out of xBestIndex. And, in > fact, you would not want to do that because one or more xBestIndex > calls might actually work. Or, there might be multiple xBestIndex > calls that all fail for different reasons, in which case it is unclear > which error should be reported. > > I will ponder your request. In the meantime, you can continue to use > the old method, which still works like it always has. > > -- > D. Richard Hipp OK, well the theory being that the message would be emitted only when all the candidate plans were tried, and still no solution waa found (I guess at the same spot where the current message is emitted). But maybe that is too late, and any messages set along the way are already gone. As for multiple messages, even just emitting an arbitrary one is useful. These failures happen at design time and the developer incrementally refines his/her query until there were no such errors. I'm not sure if it is possible to happen once a working query has been created. I would think that if you had defined a query that was demonstably solvable once, that any subsequent executions would at worst gravitate to that known working soluton even if the planner tried to do things differently that time (maybe based on data values). OK, for now I will revert to the old method. Cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] virtual table xBestIndex and SQLITE_CONSTRAINT and zErrMsg
On 2/19/19, dave wrote: > I noticed that in 3.26 a feature was added whereby a proposed execution plan > can be rejected in vtables by returning SQLITE_CONSTRAINT. I welcome this > addition, but I have lost a capability relative to the prior scheme of using > high query cost along with a special flag communicated in pIdxInfo->idxNum, > that being the ablilty to emit contextual info as to why the query failed. Yeah. There is no way to report an error out of xBestIndex. And, in fact, you would not want to do that because one or more xBestIndex calls might actually work. Or, there might be multiple xBestIndex calls that all fail for different reasons, in which case it is unclear which error should be reported. I will ponder your request. In the meantime, you can continue to use the old method, which still works like it always has. -- 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
[sqlite] virtual table xBestIndex and SQLITE_CONSTRAINT and zErrMsg
I noticed that in 3.26 a feature was added whereby a proposed execution plan
can be rejected in vtables by returning SQLITE_CONSTRAINT. I welcome this
addition, but I have lost a capability relative to the prior scheme of using
high query cost along with a special flag communicated in pIdxInfo->idxNum,
that being the ablilty to emit contextual info as to why the query failed.
Under the new scheme, a failed query is met with:
Error: no query solution
But under the old scheme I was able to emit:
Error: GROUPACCTS: There must be equality constraints on GNAME and ISLOCAL
The context info is handy for developers building the query so they can know
what they are missing, since required constraints like this are non-obvious
from a pure SQL standpoint. This is especially true in the context of
joins, since then you otherwise wouldn't even know what table is
problemattic.
Under the old scheme I would have to fail my query in xFilter, and I would
set the error text like this:
if ( IDXVAL_FAILQUERYPLAN == idxNum )
{
sqlite3_free( pThis->pVtab->zErrMsg );
pThis->pVtab->zErrMsg = sqlite3_mprintf( VTBLA4GNAME": There must be
equality constraints on GNAME and ISLOCAL" );
return SQLITE_CONSTRAINT;
}
I did try setting the error text in a similar manner in the xFilter method,
however it seems this text is ignored in that case, and I only get the 'no
solution message'.
My suggestion would be to not ignore it in the case of failing xBestIndex
for no query plan, and to emit it if it has been set.
If this is done, I imagine some additional consideration would have to be
made for the case where one proposed query plan is rejected, and another
plan has been accepted. In that case, maybe the net successful plan would
still have error texts from the previous rejected plan? I don't know if
this would cause a problem or not.
Cheers!
-dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About server-process-edition branch
Hi, I've reread [1] about the "server mode" of SQLite, but noticed that a) it's not been touched in almost a year [2], and b) it's not mentioned in https://www.sqlite.org/serverless.html It's also limited to same-process clients and synchronous=off, making it "not safe" for production use I guess. May I ask about the status of that branch? Is it abandoned? Are there plans to officially support it one day? Across-processes? With durability, to avoid corrupting the database? Just wondering. Thanks, --DD [1] https://sqlite.org/src/raw/README-server-edition.html?name=0c6bc6f55191b6900595fe37470bbe5772953ab5c64dae967d07a5d58a0c3508 [2] https://www.sqlite.org/src/timeline?r=server-process-edition ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
