Segfault in window function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Segfault in window function

Charles Leifer
I'm getting a segfault with the following query, which uses window
functions:

SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank"
FROM "sample" AS "t1"
WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC)
ORDER BY "t1"."counter", RANK() OVER w

The sql used to create the "sample" table:

CREATE TABLE IF NOT EXISTS "sample" (
  "id" INTEGER NOT NULL PRIMARY KEY,
  "counter" INTEGER NOT NULL,
  "value" REAL NOT NULL);

Test data:

INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
(2, 3.), (3, 100.);

The expected output for the window query is:

1, 20., 1
1, 10., 2
2, 3., 1
2, 1., 2
3, 100., 1
_______________________________________________
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 in window function

Charles Leifer
Oh, and gdb is reporting the segfault occurs in "dupedExprSize".

Partial traceback:

#0  0x00007ffff4d4645b in dupedExprSize () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.
#1  0x00007ffff4d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0
No symbol table info available.
#2  0x00007ffff4d5c091 in sqlite3ExprListDup () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.
#3  0x00007ffff4dbfa4f in sqlite3Select () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.
#4  0x00007ffff4df082b in sqlite3RunParser () from
/usr/local/lib/libsqlite3.so.0
No symbol table info available.


On Mon, Jul 2, 2018 at 10:54 AM, Charles Leifer <[hidden email]> wrote:

> I'm getting a segfault with the following query, which uses window
> functions:
>
> SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank"
> FROM "sample" AS "t1"
> WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC)
> ORDER BY "t1"."counter", RANK() OVER w
>
> The sql used to create the "sample" table:
>
> CREATE TABLE IF NOT EXISTS "sample" (
>   "id" INTEGER NOT NULL PRIMARY KEY,
>   "counter" INTEGER NOT NULL,
>   "value" REAL NOT NULL);
>
> Test data:
>
> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
> (2, 3.), (3, 100.);
>
> The expected output for the window query is:
>
> 1, 20., 1
> 1, 10., 2
> 2, 3., 1
> 2, 1., 2
> 3, 100., 1
>
_______________________________________________
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 in window function

Charles Leifer
Furthermore, I've found another example of a window function returning
incorrect results.

Using the above "sample" table and sample data, the following query
produces the wrong results.

Query:

SELECT "counter", "value", SUM("value") OVER (ORDER BY "id" ROWS 2
PRECEDING)
FROM "sample"
ORDER BY "id"

Expected results:

1, 10., 10.
1, 20., 30.
2, 1., 31.
2, 3., 24.
3, 100., 104.

SQLite is reporting the following results:

1, 10., 10.
1, 20., 30.
2, 1., 31.
2, 3., 44.
3, 100., 164.

I hope that helps diagnose the issue.

On Mon, Jul 2, 2018 at 10:55 AM, Charles Leifer <[hidden email]> wrote:

> Oh, and gdb is reporting the segfault occurs in "dupedExprSize".
>
> Partial traceback:
>
> #0  0x00007ffff4d4645b in dupedExprSize () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #1  0x00007ffff4d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #2  0x00007ffff4d5c091 in sqlite3ExprListDup () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #3  0x00007ffff4dbfa4f in sqlite3Select () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #4  0x00007ffff4df082b in sqlite3RunParser () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
>
>
> On Mon, Jul 2, 2018 at 10:54 AM, Charles Leifer <[hidden email]>
> wrote:
>
>> I'm getting a segfault with the following query, which uses window
>> functions:
>>
>> SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank"
>> FROM "sample" AS "t1"
>> WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC)
>> ORDER BY "t1"."counter", RANK() OVER w
>>
>> The sql used to create the "sample" table:
>>
>> CREATE TABLE IF NOT EXISTS "sample" (
>>   "id" INTEGER NOT NULL PRIMARY KEY,
>>   "counter" INTEGER NOT NULL,
>>   "value" REAL NOT NULL);
>>
>> Test data:
>>
>> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
>> (2, 3.), (3, 100.);
>>
>> The expected output for the window query is:
>>
>> 1, 20., 1
>> 1, 10., 2
>> 2, 3., 1
>> 2, 1., 2
>> 3, 100., 1
>>
>
>
_______________________________________________
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 in window function

Dan Kennedy-4
In reply to this post by Charles Leifer
On 07/02/2018 10:54 PM, Charles Leifer wrote:
> I'm getting a segfault with the following query, which uses window
> functions:

Thanks for reporting this. Should now be fixed here:

   https://www.sqlite.org/src/info/693b4350d7413912

Thanks also for the sum() bug report in the follow-up. That one is fixed
here:

   https://www.sqlite.org/src/info/693b4350d7413912

Dan.




>
> SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank"
> FROM "sample" AS "t1"
> WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC)
> ORDER BY "t1"."counter", RANK() OVER w
>
> The sql used to create the "sample" table:
>
> CREATE TABLE IF NOT EXISTS "sample" (
>    "id" INTEGER NOT NULL PRIMARY KEY,
>    "counter" INTEGER NOT NULL,
>    "value" REAL NOT NULL);
>
> Test data:
>
> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
> (2, 3.), (3, 100.);
>
> The expected output for the window query is:
>
> 1, 20., 1
> 1, 10., 2
> 2, 3., 1
> 2, 1., 2
> 3, 100., 1
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
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 in window function

Charles Leifer
Beautiful, working on my end now as well. Thanks so much.

On Mon, Jul 2, 2018 at 12:49 PM, Dan Kennedy <[hidden email]> wrote:

> On 07/02/2018 10:54 PM, Charles Leifer wrote:
>
>> I'm getting a segfault with the following query, which uses window
>> functions:
>>
>
> Thanks for reporting this. Should now be fixed here:
>
>   https://www.sqlite.org/src/info/693b4350d7413912
>
> Thanks also for the sum() bug report in the follow-up. That one is fixed
> here:
>
>   https://www.sqlite.org/src/info/693b4350d7413912
>
> Dan.
>
>
>
>
>
>> SELECT "t1"."counter", "t1"."value", RANK() OVER w AS "rank"
>> FROM "sample" AS "t1"
>> WINDOW w AS (PARTITION BY "t1"."counter" ORDER BY "t1"."value" DESC)
>> ORDER BY "t1"."counter", RANK() OVER w
>>
>> The sql used to create the "sample" table:
>>
>> CREATE TABLE IF NOT EXISTS "sample" (
>>    "id" INTEGER NOT NULL PRIMARY KEY,
>>    "counter" INTEGER NOT NULL,
>>    "value" REAL NOT NULL);
>>
>> Test data:
>>
>> INSERT INTO "sample" (counter, value) VALUES (1, 10.), (1, 20.), (2, 1.),
>> (2, 3.), (3, 100.);
>>
>> The expected output for the window query is:
>>
>> 1, 20., 1
>> 1, 10., 2
>> 2, 3., 1
>> 2, 1., 2
>> 3, 100., 1
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users