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 <d...@sqlite.org> 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 <d...@sqlite.org> 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 <d...@ziggurat29.com> 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
