union + window functions = sqlite crash (version 3.25.2)

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

union + window functions = sqlite crash (version 3.25.2)

Peter Ďurica
Table with sample data:
*create table t(a int, b int);*
*insert into t values(1,11);*
*insert into t values(2,12);*

now query using any window function (row_number, rank, ....) after UNION or
UNION ALL will cause sqlite.exe crash (no regular error)

for example:
*select a, rank() over(order by b) from t *
*union all *
*select a, rank() over(order by b desc) from t;*
WILL CRASH

but single statement is fine

*select a, rank() over(order by b desc) from t;  *

and also window function just before union is fine
*select a, rank() over(order by b) from t *
*union all *
*select a, b from t;*


when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
my c# app, I got error below
Exception thrown at 0x00007FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
0xC0000005: Access violation reading location 0x0000000000000008.

I believe it's a bug in core sqlite, which should be fixed

Please let me know how it goes

Thanks in advacne,
Peter Ďurica
_______________________________________________
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: union + window functions = sqlite crash (version 3.25.2)

David Yip
I dug a little more into this with a debug build; was able to get the
same crash trace with the slightly smaller query

    CREATE TABLE t(a);
    SELECT 1, 1
    UNION ALL
    SELECT a, RANK() OVER (ORDER BY a) FROM t;

which fails the pTab!=0 assertion in sqlite3ColumnsFromExprList.

It seems like you need to have a window function and a column from t in
the second SELECT to trigger the error; e.g. having 1, RANK()... there
won't do it.

- David

On Monday, October 22, 2018 3:13:12 PM CDT Peter Ďurica wrote:

> Table with sample data:
> *create table t(a int, b int);*
> *insert into t values(1,11);*
> *insert into t values(2,12);*
>
> now query using any window function (row_number, rank, ....) after UNION or
> UNION ALL will cause sqlite.exe crash (no regular error)
>
> for example:
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, rank() over(order by b desc) from t;*
> WILL CRASH
>
> but single statement is fine
>
> *select a, rank() over(order by b desc) from t;  *
>
> and also window function just before union is fine
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, b from t;*
>
>
> when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
> my c# app, I got error below
> Exception thrown at 0x00007FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
> 0xC0000005: Access violation reading location 0x0000000000000008.
>
> I believe it's a bug in core sqlite, which should be fixed
>
> Please let me know how it goes
>
> Thanks in advacne,
> Peter Ďurica
> _______________________________________________
> 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: union + window functions = sqlite crash (version 3.25.2)

Keith Medcalf
In reply to this post by Peter Ďurica
On Monday, 22 October, 2018 14:13, Peter Ďurica <[hidden email]> wrote:

>Table with sample data:
>*create table t(a int, b int);*
>*insert into t values(1,11);*
>*insert into t values(2,12);*

...

What is up with the asterisks, they make copying VERY VERY VERY VERY difficult.  
If you want to put "stars" around your text, how about you do it before the beginning and after the end so that the script commands remain a script command, as in:

*****
create table t(a int, b int);
insert into t values(1,11);
insert into t values(2,12);
*****

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
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: union + window functions = sqlite crash (version 3.25.2)

Dan Kennedy-4
In reply to this post by Peter Ďurica
On 10/23/2018 03:13 AM, Peter Ďurica wrote:

> Table with sample data:
> *create table t(a int, b int);*
> *insert into t values(1,11);*
> *insert into t values(2,12);*
>
> now query using any window function (row_number, rank, ....) after UNION or
> UNION ALL will cause sqlite.exe crash (no regular error)
>
> for example:
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, rank() over(order by b desc) from t;*
> WILL CRASH
>
> but single statement is fine
>
> *select a, rank() over(order by b desc) from t;  *
>
> and also window function just before union is fine
> *select a, rank() over(order by b) from t *
> *union all *
> *select a, b from t;*
>
> when I used e_sqlite3.dll (https://github.com/ericsink/SQLitePCL.raw) from
> my c# app, I got error below
> Exception thrown at 0x00007FFF563BF797 (e_sqlite3.dll) in WebLES.exe:
> 0xC0000005: Access violation reading location 0x0000000000000008.
>
> I believe it's a bug in core sqlite, which should be fixed


It is indeed, thanks for reporting it. Should now be fixed here:

   https://www.sqlite.org/src/info/059ff53a46c7f1e4

Dan.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users