PRAGMA reverse_unordered_selects=true results in row not being fetched

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

PRAGMA reverse_unordered_selects=true results in row not being fetched

Manuel Rigger
Hi everyone,

I found a curious bug, which I could reproduce only with a very specific
statement sequence:

PRAGMA reverse_unordered_selects=true;
CREATE TABLE t1 (c0, c1);
CREATE TABLE t2 (c0 INT UNIQUE);
INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);
INSERT INTO t2(c0) VALUES (1);
SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM t1, t2
WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC LIMIT 100);
-- no row is fetched

The query does not fetch a row. However, without the PRAGMA statement one
row is fetched.

Best,
Manuel
_______________________________________________
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: PRAGMA reverse_unordered_selects=true results in row not being fetched

Donald Griggs
On Tue, May 14, 2019 at 10:18 AM Manuel Rigger <[hidden email]>
wrote:

> ...
>
> The query does not fetch a row. However, without the PRAGMA statement one
> row is fetched.
>

It probably helps the devs to specify the version you were testing.
For me, your example returns a single row of "1" in each case.
I'm using 3.28.0,  windows CLI:

C:\Users\c058905\webauto>sqlite3

*SQLite version 3.28.0* 2019-04-16 19:49:53

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> PRAGMA reverse_unordered_selects;

0

sqlite> CREATE TABLE t1 (c0, c1);

sqlite> CREATE TABLE t2 (c0 INT UNIQUE);

sqlite> INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);

sqlite> INSERT INTO t2(c0) VALUES (1);

sqlite> SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM
t1, t2

   ...> WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC
LIMIT 100);

1|
====================================

C:\Users\c058905\webauto>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> PRAGMA reverse_unordered_selects=1;
sqlite> PRAGMA reverse_unordered_selects;
1
sqlite> CREATE TABLE t1 (c0, c1);
sqlite> CREATE TABLE t2 (c0 INT UNIQUE);
sqlite> INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);
sqlite> INSERT INTO t2(c0) VALUES (1);
sqlite> SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM
t1, t2
   ...> WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC
LIMIT 100);
1|
sqlite>


Regards,
    Donald
_______________________________________________
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: PRAGMA reverse_unordered_selects=true results in row not being fetched

Manuel Rigger
Hi Donald,

You are right. Sorry for not mentioning that. I was working on trunk and
using Linux. The bug has already been fixed:
https://www.sqlite.org/src/info/bc7d2c1656396bb4

Best,
Manuel

On Wed, May 15, 2019 at 3:13 PM Donald Griggs <[hidden email]> wrote:

> On Tue, May 14, 2019 at 10:18 AM Manuel Rigger <[hidden email]>
> wrote:
>
> > ...
> >
> > The query does not fetch a row. However, without the PRAGMA statement one
> > row is fetched.
> >
>
> It probably helps the devs to specify the version you were testing.
> For me, your example returns a single row of "1" in each case.
> I'm using 3.28.0,  windows CLI:
>
> C:\Users\c058905\webauto>sqlite3
>
> *SQLite version 3.28.0* 2019-04-16 19:49:53
>
> Enter ".help" for usage hints.
>
> Connected to a transient in-memory database.
>
> Use ".open FILENAME" to reopen on a persistent database.
>
> sqlite> PRAGMA reverse_unordered_selects;
>
> 0
>
> sqlite> CREATE TABLE t1 (c0, c1);
>
> sqlite> CREATE TABLE t2 (c0 INT UNIQUE);
>
> sqlite> INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);
>
> sqlite> INSERT INTO t2(c0) VALUES (1);
>
> sqlite> SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM
> t1, t2
>
>    ...> WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC
> LIMIT 100);
>
> 1|
> ====================================
>
> C:\Users\c058905\webauto>sqlite3
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> PRAGMA reverse_unordered_selects=1;
> sqlite> PRAGMA reverse_unordered_selects;
> 1
> sqlite> CREATE TABLE t1 (c0, c1);
> sqlite> CREATE TABLE t2 (c0 INT UNIQUE);
> sqlite> INSERT INTO t1(c0, c1) VALUES (0, 0), (0, NULL);
> sqlite> INSERT INTO t2(c0) VALUES (1);
> sqlite> SELECT 1, NULL INTERSECT SELECT * FROM (SELECT t2.c0, t1.c1 FROM
> t1, t2
>    ...> WHERE ((t2.rowid <= 'a')) OR (t1.c0 <= t2.c0) ORDER BY 'a' DESC
> LIMIT 100);
> 1|
> sqlite>
>
>
> Regards,
>     Donald
> _______________________________________________
> 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