Re: [sqlite] Segfault in window function
Beautiful, working on my end now as well. Thanks so much. On Mon, Jul 2, 2018 at 12:49 PM, Dan Kennedy 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 >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault in window function
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault in window function
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 wrote:
> Oh, and gdb is reporting the segfault occurs in "dupedExprSize".
>
> Partial traceback:
>
> #0 0x74d4645b in dupedExprSize () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #1 0x74d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #2 0x74d5c091 in sqlite3ExprListDup () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #3 0x74dbfa4f in sqlite3Select () from
> /usr/local/lib/libsqlite3.so.0
> No symbol table info available.
> #4 0x74df082b 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
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Segfault in window function
Oh, and gdb is reporting the segfault occurs in "dupedExprSize". Partial traceback: #0 0x74d4645b in dupedExprSize () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #1 0x74d5bf12 in exprDup () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #2 0x74d5c091 in sqlite3ExprListDup () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #3 0x74dbfa4f in sqlite3Select () from /usr/local/lib/libsqlite3.so.0 No symbol table info available. #4 0x74df082b 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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
