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
