Segfault when using FILTER (WHERE) referencing aliases from a subquery

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

Segfault when using FILTER (WHERE) referencing aliases from a subquery

Simon Charette
While trying to enable support for FILTER (WHERE) on SQLite 3.30 for
the Django ORM we discovered a crash that can be reduced to the
following

sqlite> CREATE TABLE item (id int, price int);
sqlite> INSERT INTO item (id, price) VALUES (1, 1);
sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
id, (price * 2) as double_price FROM item);
Erreur de segmentation (core dumped)

Cheers,
Simon
_______________________________________________
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: Segfault when using FILTER (WHERE) referencing aliases from a subquery

Jose Isaias Cabrera-4
>
>

Simon Charette, on Tuesday, October 8, 2019 08:00 PM, wrote...

>
> While trying to enable support for FILTER (WHERE) on SQLite 3.30 for
> the Django ORM we discovered a crash that can be reduced to the
> following
>
> sqlite> CREATE TABLE item (id int, price int);
> sqlite> INSERT INTO item (id, price) VALUES (1, 1);
> sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
> id, (price * 2) as double_price FROM item);
> Erreur de segmentation (core dumped)

I get this,

sqlite> CREATE TABLE item (id int, price int);
sqlite>  INSERT INTO item (id, price) VALUES (1, 1);
sqlite>  SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
   ...> id, (price * 2) as double_price FROM item);
Error: near "FROM": syntax error

why don't you just do the simpler,

sqlite> select count(id) from item where price * 2 > 42;
0
sqlite> select count(id) from item where price * 2 > 1;
1

I know, because you are probably trying to use that FILTER thingy. :-)  Thanks.

josé
_______________________________________________
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: Segfault when using FILTER (WHERE) referencing aliases from a subquery

Jose Isaias Cabrera-4

Jose Isaias Cabrera, on Thursday, October 10, 2019 08:55 AM, wrote...

> Simon Charette, on Tuesday, October 8, 2019 08:00 PM, wrote...
> >
> > While trying to enable support for FILTER (WHERE) on SQLite 3.30 for
> > the Django ORM we discovered a crash that can be reduced to the
> > following
> >
> > sqlite> CREATE TABLE item (id int, price int);
> > sqlite> INSERT INTO item (id, price) VALUES (1, 1);
> > sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
> > id, (price * 2) as double_price FROM item);
> > Erreur de segmentation (core dumped)
>
> I get this,
>
> sqlite> CREATE TABLE item (id int, price int);
> sqlite>  INSERT INTO item (id, price) VALUES (1, 1);
> sqlite>  SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
>    ...> id, (price * 2) as double_price FROM item);
> Error: near "FROM": syntax error
>
> why don't you just do the simpler,
>
> sqlite> select count(id) from item where price * 2 > 42;
> 0
> sqlite> select count(id) from item where price * 2 > 1;
> 1
>
> I know, because you are probably trying to use that FILTER thingy. :-)  Thanks.

I am sorry, Simon. You are correct.  In Windows sqlite3 just disappears. :-)

 9:00:24.34>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE item (id int, price int);
sqlite> INSERT INTO item (id, price) VALUES (1, 1);
sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
   ...> id, (price * 2) as double_price FROM item);

 9:03:13.72>

So, there is a problem with FILTER and v3.30.0.  Apologies.  I was using 3.29.0.

josé
_______________________________________________
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: Segfault when using FILTER (WHERE) referencing aliases from a subquery

Jan Nijtmans
Op do 10 okt. 2019 om 15:06 schreef Jose Isaias Cabrera:
> > Simon Charette, on Tuesday, October 8, 2019 08:00 PM, wrote...
> > >
> > > While trying to enable support for FILTER (WHERE) on SQLite 3.30 for
> > > the Django ORM we discovered a crash that can be reduced to the
> > > following

> I am sorry, Simon. You are correct.  In Windows sqlite3 just disappears. :-)

On Cygwin:

$ sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE item (id int, price int);
sqlite> INSERT INTO item (id, price) VALUES (1, 1);
sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
id, (price * 2) as double_price FROM item);
Segmentation fault (core dumped)

Indeed, something is wrong there!

Regards,
        Jan Nijtmans
_______________________________________________
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: Segfault when using FILTER (WHERE) referencing aliases from a subquery

Jan Nijtmans
Op do 10 okt. 2019 om 15:15 schreef Jan Nijtmans:

>
> Op do 10 okt. 2019 om 15:06 schreef Jose Isaias Cabrera:
> > > Simon Charette, on Tuesday, October 8, 2019 08:00 PM, wrote...
> > > >
> > > > While trying to enable support for FILTER (WHERE) on SQLite 3.30 for
> > > > the Django ORM we discovered a crash that can be reduced to the
> > > > following
>
> > I am sorry, Simon. You are correct.  In Windows sqlite3 just disappears. :-)
>
> On Cygwin:
>
> $ sqlite3
> SQLite version 3.30.0 2019-10-04 15:03:17
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE item (id int, price int);
> sqlite> INSERT INTO item (id, price) VALUES (1, 1);
> sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
> id, (price * 2) as double_price FROM item);
> Segmentation fault (core dumped)
>
> Indeed, something is wrong there!
>
> Regards,
>         Jan Nijtmans

So, the crash happens here:
    <https://www.sqlite.org/src/artifact/e1d8ac7102f3f283?ln=530>

???

Thread 1 "sqlite3" received signal SIGSEGV, Segmentation fault.
sqlite3VdbeExec (p=p@entry=0x600094450) at
/usr/src/debug/sqlite3-3.30.0-1/sqlite3.c:21673
21673     if( sqlite3GlobalConfig.bLocaltimeFault ) pX = 0;
(gdb) stacktrace


(gdb) backtrace
#0  sqlite3VdbeExec (p=p@entry=0x600094450) at
/usr/src/debug/sqlite3-3.30.0-1/sqlite3.c:21673
#1  0x00000003e45bdb70 in sqlite3Step (p=0x600094450) at
/usr/src/debug/sqlite3-3.30.0-1/sqlite3.c:82993
#2  sqlite3_step (pStmt=pStmt@entry=0x600094450) at
/usr/src/debug/sqlite3-3.30.0-1/sqlite3.c:17522
#3  0x0000000100413694 in exec_prepared_stmt (pStmt=0x600094450,
pArg=0xffffb630) at /usr/src/debug/sqlite3-3.30.0-1/shell.c:11410
#4  shell_exec (pArg=pArg@entry=0xffffb630, zSql=<optimized out>,
    zSql@entry=0x60007f200 "SELECT COUNT(id) FILTER (WHERE
double_price > 42) FROM (SELECT id, (price * 2) as double_price FROM
item);",
    pzErrMsg=pzErrMsg@entry=0xffffb3e8) at
/usr/src/debug/sqlite3-3.30.0-1/shell.c:11715
#5  0x0000000100414afd in runOneSqlLine (p=p@entry=0xffffb630,
    zSql=zSql@entry=0x60007f200 "SELECT COUNT(id) FILTER (WHERE
double_price > 42) FROM (SELECT id, (price * 2) as double_price FROM
item);", in=0x0, startline=startline@entry=3) at
/usr/src/debug/sqlite3-3.30.0-1/shell.c:18268
#6  0x000000010041f346 in process_input (p=p@entry=0xffffb630) at
/usr/src/debug/sqlite3-3.30.0-1/shell.c:18368
#7  0x000000010042133f in main (argc=1, argv=0xffffcc40) at
/usr/src/debug/sqlite3-3.30.0-1/shell.c:19142
_______________________________________________
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: Segfault when using FILTER (WHERE) referencing aliases from a subquery

Richard Hipp-3
In reply to this post by Simon Charette
Thank you for the succinct bug report!

A ticket for this problem can be seen here:
https://www.sqlite.org/src/info/1079ad19993d13fa

The problem is in the query flattener optimization
(https://www.sqlite.org/optoverview.html#subquery_flattening) when
applied to queries that use the new FILTER clause on aggregate
functions.  We have a candidate fix now.  We are merely testing and
analyzing the fix to try to ensure that it is complete and accurate.
Expect a patch soon.

On 10/8/19, Simon Charette <[hidden email]> wrote:

> While trying to enable support for FILTER (WHERE) on SQLite 3.30 for
> the Django ORM we discovered a crash that can be reduced to the
> following
>
> sqlite> CREATE TABLE item (id int, price int);
> sqlite> INSERT INTO item (id, price) VALUES (1, 1);
> sqlite> SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (SELECT
> id, (price * 2) as double_price FROM item);
> Erreur de segmentation (core dumped)
>
> Cheers,
> Simon
> _______________________________________________
> 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