Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Except in SQLite where as a documented behavioural anomaly maintained for backwards compatibility it simply means "UNIQUE" (for ROWID tables). And UNIQUE indexes may have NULL components. This is because despite your wishing that your primary key is the primary key, it is not the primary key. However when WITHOUT ROWID tables were introduced there was no backwards compatibility issues (they were new after all) then PRIMARY KEY could be implemented as UNIQUE NOT NULL ... https://sqlite.org/nulls.html https://sqlite.org/rowidtable.html https://sqlite.org/withoutrowid.html See especially 2 sub 4 in the latter. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz >Sent: Thursday, 18 July, 2019 12:33 >To: SQLite mailing list >Subject: Re: [sqlite] I can insert multiple rows with the same >primary key when one of the value of the PK is NULL ... > >> You might prefer adding an explicit NOT NULL on both "client" and >"salesman" columns. >> There is an historical reason why SQLite accepts NULL for primary >key column(s). > >Ok, thanks for the hint, I didn't know that either. But it is a very >odd behavior, because PRIMARY KEY per definition doesn't mean >anything else than UNIQUE NOT NULL. > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
> You might prefer adding an explicit NOT NULL on both "client" and "salesman" > columns. > There is an historical reason why SQLite accepts NULL for primary key > column(s). Ok, thanks for the hint, I didn't know that either. But it is a very odd behavior, because PRIMARY KEY per definition doesn't mean anything else than UNIQUE NOT NULL. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
"integer primary key"s cannot contain a null since they're an alias for the
rowid. So when you insert a null into them they act similar to autoincrement
and automatically fill it in with an unused id. (Current implementation is 1
more than the _current_ highest rowid. Subject to change)
If you made it just an int primary key it shows what you want.
D:\Programs\PostgreSQL\11\bin>sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table TheTable (ID int primary key, Info text);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
sqlite> insert into theTable values (1, 'Test');
sqlite> insert into theTable values (2, 'Test2');
sqlite> insert into theTable values (null, 'TestNull1');
sqlite> insert into theTable values (null, 'TestNull2');
sqlite> insert into theTable values (null, 'TestNull3');
sqlite> insert into theTable values (2, 'Test2.1');
Error: UNIQUE constraint failed: TheTable.ID
sqlite> select * from TheTable;
QUERY PLAN
`--SCAN TABLE TheTable
ID|Info
1|Test
2|Test2
|TestNull1
|TestNull2
|TestNull3
sqlite> select count(distinct id) from theTable;
QUERY PLAN
`--SCAN TABLE theTable USING COVERING INDEX sqlite_autoindex_TheTable_1
count(distinct id)
2
sqlite> select id, count(*) from theTable group by id order by id;
QUERY PLAN
`--SCAN TABLE theTable USING COVERING INDEX sqlite_autoindex_TheTable_1
ID|count(*)
|3
1|1
2|1
sqlite>
-Original Message-
From: sqlite-users On Behalf Of
Stephen Chrzanowski
Sent: Thursday, July 18, 2019 1:18 PM
To: SQLite mailing list
Subject: Re: [sqlite] I can insert multiple rows with the same primary key when
one of the value of the PK is NULL ...
Actually, I take that back...
sqlite> select * from TheTable;
1|Test
2|Test2
3|TestNull1
4|TestNull2
5|TestNull3
So the inserting of NULL in a primary key (Single instance maybe?) will
insert the new rowid.
Try running a SELECT against your table and see what kind of results you're
obtaining.
On Thu, Jul 18, 2019 at 1:13 PM Stephen Chrzanowski
wrote:
> NULL is a special thing. It's never considered unique.
>
> SQLite version 3.20.0 2017-08-01 13:24:15
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
> sqlite> insert into TheTable (1,"Test");
> Error: near "1": syntax error
> sqlite> insert into TheTable values (1,"Test");
> sqlite> insert into TheTable values (2,"Test2");
> sqlite> insert into TheTable values (null,"TestNull1");
> sqlite> insert into TheTable values (null,"TestNull2");
> sqlite> insert into TheTable values (null,"TestNull3");
> sqlite> insert into TheTable values (2,"Test2.1");
> Error: UNIQUE constraint failed: TheTable.ID
> sqlite> select count(*) from TheTable;
> 5
> sqlite>
>
>
> On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon
> wrote:
>
>> Hello,
>>
>> I have created a table.
>>
>> CREATE TABLE "sales" (
>> "client"TEXT,
>> "salesman" TEXT,
>> "revenue" REAL,
>> PRIMARY KEY("client","salesman")
>> );
>>
>>
>> I can run the query below mutliple times without any error :
>>
>> INSERT INTO sales ("client", "salesman", "revenue")
>> VALUES ('C1', NULL, 10.0);
>>
>>
>> Have I missed something in the CREATE instruction ?
>> Is this a normal behaviour ?
>>
>> I have tried to read https://sqlite.org/lang_createtable.html#constraints
>> and https://www.sqlite.org/nulls.html but I don't really have found why
>> I can insert 2 records that have the same primary key.
>>
>> SQLite version 3.27.2 2019-02-25 16:06:06
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .headers on
>> sqlite> .mode column
>> sqlite> .nullvalue null
>> sqlite>
>> sqlite> CREATE TABLE "sales" (
>>...> "client"TEXT,
>>...> "salesman"TEXT,
>>...> "revenue"REAL,
>>...> PRIMARY KEY("client","salesman")
>>...> );
>> sqlite>
>> sqlite>
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> Error: UNIQUE constraint failed:
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Actually, I take that back...
sqlite> select * from TheTable;
1|Test
2|Test2
3|TestNull1
4|TestNull2
5|TestNull3
So the inserting of NULL in a primary key (Single instance maybe?) will
insert the new rowid.
Try running a SELECT against your table and see what kind of results you're
obtaining.
On Thu, Jul 18, 2019 at 1:13 PM Stephen Chrzanowski
wrote:
> NULL is a special thing. It's never considered unique.
>
> SQLite version 3.20.0 2017-08-01 13:24:15
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
> sqlite> insert into TheTable (1,"Test");
> Error: near "1": syntax error
> sqlite> insert into TheTable values (1,"Test");
> sqlite> insert into TheTable values (2,"Test2");
> sqlite> insert into TheTable values (null,"TestNull1");
> sqlite> insert into TheTable values (null,"TestNull2");
> sqlite> insert into TheTable values (null,"TestNull3");
> sqlite> insert into TheTable values (2,"Test2.1");
> Error: UNIQUE constraint failed: TheTable.ID
> sqlite> select count(*) from TheTable;
> 5
> sqlite>
>
>
> On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon
> wrote:
>
>> Hello,
>>
>> I have created a table.
>>
>> CREATE TABLE "sales" (
>> "client"TEXT,
>> "salesman" TEXT,
>> "revenue" REAL,
>> PRIMARY KEY("client","salesman")
>> );
>>
>>
>> I can run the query below mutliple times without any error :
>>
>> INSERT INTO sales ("client", "salesman", "revenue")
>> VALUES ('C1', NULL, 10.0);
>>
>>
>> Have I missed something in the CREATE instruction ?
>> Is this a normal behaviour ?
>>
>> I have tried to read https://sqlite.org/lang_createtable.html#constraints
>> and https://www.sqlite.org/nulls.html but I don't really have found why
>> I can insert 2 records that have the same primary key.
>>
>> SQLite version 3.27.2 2019-02-25 16:06:06
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .headers on
>> sqlite> .mode column
>> sqlite> .nullvalue null
>> sqlite>
>> sqlite> CREATE TABLE "sales" (
>>...> "client"TEXT,
>>...> "salesman"TEXT,
>>...> "revenue"REAL,
>>...> PRIMARY KEY("client","salesman")
>>...> );
>> sqlite>
>> sqlite>
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> Error: UNIQUE constraint failed: sales.client, sales.salesman
>> sqlite>
>> sqlite> SELECT * FROM sales;
>> client salesmanrevenue
>> -- -- --
>> C1 null10.0
>> C1 null10.0
>> C1 10.0
>> sqlite>
>>
>> Thanks in advance for your insights.
>>
>> Alex
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
NULL is a special thing. It's never considered unique.
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
sqlite> insert into TheTable (1,"Test");
Error: near "1": syntax error
sqlite> insert into TheTable values (1,"Test");
sqlite> insert into TheTable values (2,"Test2");
sqlite> insert into TheTable values (null,"TestNull1");
sqlite> insert into TheTable values (null,"TestNull2");
sqlite> insert into TheTable values (null,"TestNull3");
sqlite> insert into TheTable values (2,"Test2.1");
Error: UNIQUE constraint failed: TheTable.ID
sqlite> select count(*) from TheTable;
5
sqlite>
On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon wrote:
> Hello,
>
> I have created a table.
>
> CREATE TABLE "sales" (
> "client"TEXT,
> "salesman" TEXT,
> "revenue" REAL,
> PRIMARY KEY("client","salesman")
> );
>
>
> I can run the query below mutliple times without any error :
>
> INSERT INTO sales ("client", "salesman", "revenue")
> VALUES ('C1', NULL, 10.0);
>
>
> Have I missed something in the CREATE instruction ?
> Is this a normal behaviour ?
>
> I have tried to read https://sqlite.org/lang_createtable.html#constraints
> and https://www.sqlite.org/nulls.html but I don't really have found why I
> can insert 2 records that have the same primary key.
>
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .headers on
> sqlite> .mode column
> sqlite> .nullvalue null
> sqlite>
> sqlite> CREATE TABLE "sales" (
>...> "client"TEXT,
>...> "salesman"TEXT,
>...> "revenue"REAL,
>...> PRIMARY KEY("client","salesman")
>...> );
> sqlite>
> sqlite>
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', NULL, 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', NULL, 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', '', 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', '', 10.0);
> Error: UNIQUE constraint failed: sales.client, sales.salesman
> sqlite>
> sqlite> SELECT * FROM sales;
> client salesmanrevenue
> -- -- --
> C1 null10.0
> C1 null10.0
> C1 10.0
> sqlite>
>
> Thanks in advance for your insights.
>
> Alex
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
On 18 Jul 2019, at 5:02pm, Alexandre Billon wrote:
> INSERT INTO sales ("client", "salesman", "revenue")
> VALUES ('C1', NULL, 10.0);
The value NULL in SQLite means 'value unknown'. It is a special case. If you
compare two NULLs SQLite will act as if those values are different. Therefore
you can have two PRIMARY KEYs with NULL in the same place.
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
> ...> VALUES ('C1', '', 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
> ...> VALUES ('C1', '', 10.0);
> Error: UNIQUE constraint failed: sales.client, sales.salesman
As you see here when you try the sme thing with non-NULL values, you get the
error you expected.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
> Le 18 juil. 2019 à 18:05, J. King a écrit :
>
>> Hello,
>>
>> I have created a table.
>>
>> CREATE TABLE "sales" (
>> "client"TEXT,
>> "salesman" TEXT,
>> "revenue" REAL,
>> PRIMARY KEY("client","salesman")
>> );
>>
>>
>> I can run the query below mutliple times without any error :
>>
>> INSERT INTO sales ("client", "salesman", "revenue")
>> VALUES ('C1', NULL, 10.0);
>>
>>
>> Have I missed something in the CREATE instruction ?
>> Is this a normal behaviour ?
>>
> It is indeed normal behaviour. See for
> an explanation. If you want a true primary key, use a without rowid table:
>
> CREATE TABLE "sales" (
> "client"TEXT,
> "salesman" TEXT,
> "revenue" REAL,
> PRIMARY KEY("client","salesman")
> ) WITHOUT ROWID;
You might prefer adding an explicit NOT NULL on both "client" and "salesman"
columns.
There is an historical reason why SQLite accepts NULL for primary key column(s).
https://www.sqlite.org/quirks.html#primary_keys_can_sometimes_contain_nulls
Then, now that you know why NULL is accepted (when you rightly would have
expected it to be refused), you can insert many times because each NULL is
treated distinct in a UNIQUE column.
https://www.sqlite.org/nulls.html
—
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
Grüßen,
Olivier Mascia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
On 2019-07-18 12:02:18, "Alexandre Billon" wrote:
Hello,
I have created a table.
CREATE TABLE "sales" (
"client" TEXT,
"salesman"TEXT,
"revenue" REAL,
PRIMARY KEY("client","salesman")
);
I can run the query below mutliple times without any error :
INSERT INTO sales ("client", "salesman", "revenue")
VALUES ('C1', NULL, 10.0);
Have I missed something in the CREATE instruction ?
Is this a normal behaviour ?
It is indeed normal behaviour. See
for an explanation. If you want a true primary key, use a without rowid
table:
CREATE TABLE "sales" (
"client" TEXT,
"salesman"TEXT,
"revenue" REAL,
PRIMARY KEY("client","salesman")
) WITHOUT ROWID;
--
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...
Hello,
I have created a table.
CREATE TABLE "sales" (
"client"TEXT,
"salesman" TEXT,
"revenue" REAL,
PRIMARY KEY("client","salesman")
);
I can run the query below mutliple times without any error :
INSERT INTO sales ("client", "salesman", "revenue")
VALUES ('C1', NULL, 10.0);
Have I missed something in the CREATE instruction ?
Is this a normal behaviour ?
I have tried to read https://sqlite.org/lang_createtable.html#constraints and
https://www.sqlite.org/nulls.html but I don't really have found why I can
insert 2 records that have the same primary key.
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers on
sqlite> .mode column
sqlite> .nullvalue null
sqlite>
sqlite> CREATE TABLE "sales" (
...> "client"TEXT,
...> "salesman"TEXT,
...> "revenue"REAL,
...> PRIMARY KEY("client","salesman")
...> );
sqlite>
sqlite>
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', NULL, 10.0);
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', NULL, 10.0);
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', '', 10.0);
sqlite>
sqlite> INSERT INTO sales ("client", "salesman", "revenue")
...> VALUES ('C1', '', 10.0);
Error: UNIQUE constraint failed: sales.client, sales.salesman
sqlite>
sqlite> SELECT * FROM sales;
client salesmanrevenue
-- -- --
C1 null10.0
C1 null10.0
C1 10.0
sqlite>
Thanks in advance for your insights.
Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Adapt HAVE_GETHOSTUUID conditional for UIKit for macOS
Thanks for the response. Just to clarify, this is separate from the `warning "gethostuuid() is disabled."` exuded from the else portion of the conditional (when targeting iOS). When targeting UIKit for macOS, the conditional gets into the if portion because TARGET_OS_UIKITFORMAC is not included as an exemption (this is the adjustment I was suggesting with my post). This is an error and causes a build failure because the HAVE_GETHOSTUUID is set to 1 (so the gethostuuid() paths are included later on in the code), even though it's not available on UIKit for macOS. >It looks like you're building SQLite from source (why? it's already available as a dynamic library on Apple platforms) While the dynamic library on Apple doesn't fail with the same error, this issue does impact any custom builds/configurations of SQLite being built to target the Apple ecosystem. I can provide a test project that displays the error with Xcode 11 beta + macOS 10.15 beta if that would be helpful. Cheers, Micah ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report: documentation for the SQLite Android bindings should clearly call out their poor query performance
I apologize in advance if this is the wrong place to report bugs with the SQLite Android bindings, but I couldn’t figure out how to file a ticket on that subsite (https://sqlite.org/android/) and didn’t see any contact information there, so I figured I’d try here. Query performance, particularly for large result sets, is significantly worse (by up to an order of magnitude) when using the SQLite Android bindings than with AOSP SQLite, even for completely identical data. This slowness originates from the bindings’ nativeExecuteForCursorWindow implementation, which uses JNI in a loop because the much faster C++ methods the AOSP uses are private. This is responsible for the entire performance difference. I understand that this is the only way for the bindings to manipulate CursorWindows from C++, but this is too big a downside to be mentioned only in code comments (https://www.sqlite.org/android/artifact/afd8e719c44fb86f), and obliquely at that. It’s a systematic scalability issue and should be called out clearly, in the “Differences From the Built-in SQLite Support” section here (https://www.sqlite.org/android/doc/trunk/www/usage.wiki) or even on the bindings’ home page, given the severity of the difference. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
