sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)

Ignacio Losiggio
(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 GOOD    2018-12-31 17:58:05 f856676c8438dbf5 CURRENT
  6 GOOD    2018-12-31 16:36:42 4678cb1044f0b4dc
  4 GOOD    2018-12-24 20:00:27 3873941c4fb9aa2d
  2 GOOD    2018-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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)

dave
> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]] On
> Behalf Of Ignacio Losiggio
> Sent: Tuesday, February 19, 2019 6:27 PM
> To: [hidden email]
> 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 GOOD    2018-12-31 17:58:05 f856676c8438dbf5 CURRENT
>   6 GOOD    2018-12-31 16:36:42 4678cb1044f0b4dc
>   4 GOOD    2018-12-24 20:00:27 3873941c4fb9aa2d
>   2 GOOD    2018-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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)

dave

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)

Richard Hipp-3
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)

Richard Hipp-3
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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> [hidden email]
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)

Richard Hipp-3
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@.../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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>>
>
>
> --
> D. Richard Hipp
> [hidden email]
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE fieldIN (list, of, items)

dave
In reply to this post by Richard Hipp-3
...

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)

Richard Hipp-3
In reply to this post by Ignacio Losiggio
On 2/19/19, Ignacio Losiggio <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite segfault on INNER JOIN ON (...) + WHERE field IN (list, of, items)

Richard Hipp-3
On 2/19/19, Richard Hipp <[hidden email]> wrote:
>
> 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.

Further testing and analysis lead to a better fix, which is now on
trunk.  The prior fix from last night has been diverted onto a closed
branch and the ticket has been closed.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users