Row is not fetched with PRAGMA reverse_unordered_selects=true

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

Row is not fetched with PRAGMA reverse_unordered_selects=true

Manuel Rigger
Hi,

I discovered another bug that is triggered when "PRAGMA
reverse_unordered_selects=true" is used. It's similar to a previous bug
that I reported [1], but the statement triggering the bug has a compound
expression that should always be true (for values that are not NULL) in the
WHERE clause:

CREATE TABLE t0(c0 INTEGER PRIMARY KEY);
INSERT INTO t0(c0) VALUES (1);
PRAGMA reverse_unordered_selects=true;
SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no row
SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1

Best,
Manuel


[1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51
_______________________________________________
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: Row is not fetched with PRAGMA reverse_unordered_selects=true

E.Pasma

> Op 9 mei 2019, om 00:07 heeft Manuel Rigger <[hidden email]> het volgende geschreven:
> Hi,
>
> I discovered another bug that is triggered when "PRAGMA
> reverse_unordered_selects=true" is used. It's similar to a previous bug
> that I reported [1], but the statement triggering the bug has a compound
> expression that should always be true (for values that are not NULL) in the
> WHERE clause:
>
> CREATE TABLE t0(c0 INTEGER PRIMARY KEY);
> INSERT INTO t0(c0) VALUES (1);
> PRAGMA reverse_unordered_selects=true;
> SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no row
> SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1
>
> Best,
> Manuel
>
>
> [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51

Hi Mauel,

This appears to have been fixed with the previous bug that you referred to. The example in the referred ticket is even more simple:
CREATE TABLE t14(x INTEGER PRIMARY KEY);
  INSERT INTO t14(x) VALUES (100);
  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
Hope I'm not mistaken,

E. Pasma
_______________________________________________
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: Row is not fetched with PRAGMA reverse_unordered_selects=true

Manuel Rigger
Hi E. Pasma,

Thanks for your interest in this! The test case failed on trunk, after the
previous bug that you are referring to was fixed. It seems that the bug I
newly reported has been fixed in
https://www.sqlite.org/src/info/ebe4845cd0ffb96b. In order to trigger it,
this more complex (and admittedly obscure) test case was necessary.

Best,
Manuel

On Thu, May 9, 2019 at 10:54 AM E.Pasma <[hidden email]> wrote:

>
> > Op 9 mei 2019, om 00:07 heeft Manuel Rigger <[hidden email]>
> het volgende geschreven:
> > Hi,
> >
> > I discovered another bug that is triggered when "PRAGMA
> > reverse_unordered_selects=true" is used. It's similar to a previous bug
> > that I reported [1], but the statement triggering the bug has a compound
> > expression that should always be true (for values that are not NULL) in
> the
> > WHERE clause:
> >
> > CREATE TABLE t0(c0 INTEGER PRIMARY KEY);
> > INSERT INTO t0(c0) VALUES (1);
> > PRAGMA reverse_unordered_selects=true;
> > SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no
> row
> > SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1
> >
> > Best,
> > Manuel
> >
> >
> > [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51
>
> Hi Mauel,
>
> This appears to have been fixed with the previous bug that you referred
> to. The example in the referred ticket is even more simple:
> CREATE TABLE t14(x INTEGER PRIMARY KEY);
>   INSERT INTO t14(x) VALUES (100);
>   SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
>   SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
> Hope I'm not mistaken,
>
> E. Pasma
> _______________________________________________
> 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