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 <colei...@gmail.com> 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 <colei...@gmail.com> > 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