'where ... isnull' in create index

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

'where ... isnull' in create index

ingo
For the second insert in the code below I expected a failure. From the
diagrams in the create index doc I understand the WHERE ts_to ISNULL is
legal. Do I misunderstand the docs or is there an other place where I
should look. I'm awar that I could use some future data as default for
ts_to but it is not elegant to me,

TIA,

ingo

---%<------%<------%<---

CREATE TABLE IF NOT EXISTS person (
  pid INTEGER NOT NULL,
  full_name TEXT,
  ts_to TEXT DEFAULT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_person
    ON person (pid, ts_to)
 WHERE ts_to ISNULL
;

INSERT INTO person (pid, full_name)
VALUES (1,'pietje puk');

INSERT INTO person (pid, full_name)
VALUES (1,'jan tabak');  -----> should fail??

SELECT * FROM person WHERE ts_to ISNULL;
_______________________________________________
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: [EXTERNAL] 'where ... isnull' in create index

Hick Gunter
NULL is considered different from any other value, including another NULL, in the context of UNIQUE.

So your unique index has 2 entries (1, NULL1) for rowid 1 and (1, NULL2) for rowid2.

See https://sqlite.org/nulls.html

It also states that NULL1 == NULL2 is TRUE in UNION and DISTINCT contices.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von ingo
Gesendet: Mittwoch, 05. Juni 2019 09:28
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] 'where ... isnull' in create index

For the second insert in the code below I expected a failure. From the diagrams in the create index doc I understand the WHERE ts_to ISNULL is legal. Do I misunderstand the docs or is there an other place where I should look. I'm awar that I could use some future data as default for ts_to but it is not elegant to me,

TIA,

ingo

---%<------%<------%<---

CREATE TABLE IF NOT EXISTS person (
  pid INTEGER NOT NULL,
  full_name TEXT,
  ts_to TEXT DEFAULT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_person
    ON person (pid, ts_to)
 WHERE ts_to ISNULL
;

INSERT INTO person (pid, full_name)
VALUES (1,'pietje puk');

INSERT INTO person (pid, full_name)
VALUES (1,'jan tabak');  -----> should fail??

SELECT * FROM person WHERE ts_to ISNULL; _______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] 'where ... isnull' in create index

ingo


On 5-6-2019 09:38, Hick Gunter wrote:
> NULL is considered different from any other value, including another NULL, in the context of UNIQUE.
>
> So your unique index has 2 entries (1, NULL1) for rowid 1 and (1, NULL2) for rowid2.
>
> See https://sqlite.org/nulls.html
>
> It also states that NULL1 == NULL2 is TRUE in UNION and DISTINCT contices.
>

Thanks,

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