Partial NOT NULL index malfunctions with IS NOT/!=

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

Partial NOT NULL index malfunctions with IS NOT/!=

Manuel Rigger
Hi everyone,

I found another test case that demonstrates a malfunctioning index:

CREATE TABLE IF NOT EXISTS t0 (c0);
CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL;
INSERT INTO t0(c0) VALUES(NULL);
SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row

If the index is created, no rows are fetched. Without the index, the NULL
row is returned.

I think that this looks like a rather general pattern that could be used in
practice.

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: Partial NOT NULL index malfunctions with IS NOT/!=

Dan Kennedy-4

On 11/5/62 16:54, Manuel Rigger wrote:
> Hi everyone,
>
> I found another test case that demonstrates a malfunctioning index:
>
> CREATE TABLE IF NOT EXISTS t0 (c0);
> CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL;
> INSERT INTO t0(c0) VALUES(NULL);
> SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row


Thanks for this. Should be fixed now. Ticket here:

   https://sqlite.org/src/tktview/80256748471a01

Dan.


>
> If the index is created, no rows are fetched. Without the index, the NULL
> row is returned.
>
> I think that this looks like a rather general pattern that could be used in
> practice.
>
> Best,
> Manuel
> _______________________________________________
> 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: Partial NOT NULL index malfunctions with IS NOT/!=

Manuel Rigger
Great, thanks, Dan!

Best,
Manuel

On Sat, May 11, 2019 at 4:05 PM Dan Kennedy <[hidden email]> wrote:

>
> On 11/5/62 16:54, Manuel Rigger wrote:
> > Hi everyone,
> >
> > I found another test case that demonstrates a malfunctioning index:
> >
> > CREATE TABLE IF NOT EXISTS t0 (c0);
> > CREATE INDEX IF NOT EXISTS i0 ON t0(1) WHERE c0 NOT NULL;
> > INSERT INTO t0(c0) VALUES(NULL);
> > SELECT * FROM t0 WHERE t0.c0 IS NOT 1; -- returns no row
>
>
> Thanks for this. Should be fixed now. Ticket here:
>
>    https://sqlite.org/src/tktview/80256748471a01
>
> Dan.
>
>
> >
> > If the index is created, no rows are fetched. Without the index, the NULL
> > row is returned.
> >
> > I think that this looks like a rather general pattern that could be used
> in
> > practice.
> >
> > Best,
> > Manuel
> > _______________________________________________
> > 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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users