Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)

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

Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)

Marco Foit
Dear SQLite Developers,

I just noticed the following bug in SQLite version 3.28.0:

<CODE>

create table t AS values (1), (2);

.print "select with correct output  ..."
select * from ( select * from t limit 1 )
union all
select * from t
;

.print "same select leads to incorrect result when used inside view ..."
create view v as
select * from ( select * from t limit 1 )
union all
select * from t
;

select * from v;

</CODE>


* How to reproduce:
Run the attached SQL code from a shell with:

        sqlite3 < sqlite-bug.sql


* Expected result:
Both queries should yield the following output:
1
1
2

* What did go wrong:
The second query yields to the output:
1


It seems that the limit clause in the compound select when used inside a
view is used for the overall result set and not for the subquery.


In the hope that this might help others.
Thank you very much for your hard work!


Cheers,
Marco


_______________________________________________
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: Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)

Richard Hipp-3
Thank you for the report.  The problem is now fixed on trunk.

Ticket: https://www.sqlite.org/src/info/c41afac34f15781f
Fix: https://www.sqlite.org/src/info/523b42371122d9e1

On 5/29/19, Marco Foit <[hidden email]> wrote:

> Dear SQLite Developers,
>
> I just noticed the following bug in SQLite version 3.28.0:
>
> <CODE>
>
> create table t AS values (1), (2);
>
> .print "select with correct output  ..."
> select * from ( select * from t limit 1 )
> union all
> select * from t
> ;
>
> .print "same select leads to incorrect result when used inside view ..."
> create view v as
> select * from ( select * from t limit 1 )
> union all
> select * from t
> ;
>
> select * from v;
>
> </CODE>
>
>
> * How to reproduce:
> Run the attached SQL code from a shell with:
>
> sqlite3 < sqlite-bug.sql
>
>
> * Expected result:
> Both queries should yield the following output:
> 1
> 1
> 2
>
> * What did go wrong:
> The second query yields to the output:
> 1
>
>
> It seems that the limit clause in the compound select when used inside a
> view is used for the overall result set and not for the subquery.
>
>
> In the hope that this might help others.
> Thank you very much for your hard work!
>
>
> Cheers,
> Marco
>
>
> _______________________________________________
> 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