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