"BIGINT" doesn't act like INTEGER on table creation [Bug]

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|

"BIGINT" doesn't act like INTEGER on table creation [Bug]

Peter Halasz
BIGINT has a "resulting affinity" of INTEGER (
https://sqlite.org/datatype3.html) but cannot be used in its place in the
following example:

    CREATE TABLE [FailTable] ( [id] BigInt PRIMARY KEY AUTOINCREMENT )

...as it gives this error:

    AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

Manually replacing BIGINT with INTEGER leads to success:

    CREATE TABLE [OkTable] ( [id] Integer PRIMARY KEY AUTOINCREMENT )

I believe BigInt should work here the same as Integer, as it does in other
contexts. I feel like this is a bug that could be fixed in SQLite. Or
otherwise you might wish to mention the odd exception of AUTOINCREMENT
fields explicitly in the type documentation -- but it seems to me like this
is more something that should be fixed in the code.

Sorry if this is a duplicate, known or already patched bug. It was
difficult to search the bug tracker.

Thanks for taking the time to look into this.

For background, how I came across this: Linq2db, a library for C#,
generates bad SQL in this style and fails. Clearly that library needs to
fix its SQL generator to work better with SQLite (and I've posted a bug
report on their github). However, it also seems odd the SQL should produce
an error at all. If BigInt and Integer were treated synonymously, as
documented, It would be valid. So I felt the bug should be brought to the
attention of the SQLite developers. Hopefully it is something that can be
fixed.

Cheers
_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Keith Medcalf

That is correct.  You cannot spell "INTEGER PRIMARY KEY", which is an explicit alias for the rowid, as anything other than that one particular correct spelling.

Declaring "bigint primary key" (ie, using an incorrect spelling) defines a column that IS NOT an explicit alias for the rowid.  It is merely an integer column, completely separate from the rowid, whcih you happen to want to be the primary key.  Since the AUTOINCREMENT only applies to the rowid, you must spell the declaration correctly if you wish to (a) alias the rowid and (b) apply the AUTOINCREMENT option to the rowid.

And no, "BIGINT PRIMARY KEY" is not an alias for "INTEGER PRIMARY KEY"

https://sqlite.org/autoinc.html
https://sqlite.org/datatype3.html
https://www.sqlite.org/lang_createtable.html see "ROWIDs and the INTEGER PRIMARY KEY"

Personally, I have never found a use for the AUTOINCREMENT option.  Why is it being used?

---
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-
>[hidden email]] On Behalf Of Peter Halasz
>Sent: Thursday, 23 November, 2017 17:14
>To: [hidden email]
>Subject: [sqlite] "BIGINT" doesn't act like INTEGER on table creation
>[Bug]
>
>BIGINT has a "resulting affinity" of INTEGER (
>https://sqlite.org/datatype3.html) but cannot be used in its place in
>the
>following example:
>
>    CREATE TABLE [FailTable] ( [id] BigInt PRIMARY KEY AUTOINCREMENT
>)
>
>...as it gives this error:
>
>    AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>
>Manually replacing BIGINT with INTEGER leads to success:
>
>    CREATE TABLE [OkTable] ( [id] Integer PRIMARY KEY AUTOINCREMENT )
>
>I believe BigInt should work here the same as Integer, as it does in
>other
>contexts. I feel like this is a bug that could be fixed in SQLite. Or
>otherwise you might wish to mention the odd exception of
>AUTOINCREMENT
>fields explicitly in the type documentation -- but it seems to me
>like this
>is more something that should be fixed in the code.
>
>Sorry if this is a duplicate, known or already patched bug. It was
>difficult to search the bug tracker.
>
>Thanks for taking the time to look into this.
>
>For background, how I came across this: Linq2db, a library for C#,
>generates bad SQL in this style and fails. Clearly that library needs
>to
>fix its SQL generator to work better with SQLite (and I've posted a
>bug
>report on their github). However, it also seems odd the SQL should
>produce
>an error at all. If BigInt and Integer were treated synonymously, as
>documented, It would be valid. So I felt the bug should be brought to
>the
>attention of the SQLite developers. Hopefully it is something that
>can be
>fixed.
>
>Cheers
>_______________________________________________
>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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Peter Halasz
Thank you. It looks like I was looking at the wrong documentation page.

So I will reiterate and clarify my advice that the datatype documentation (
https://sqlite.org/datatype3.html), within the section on "Determination Of
Column Affinity", should more clearly state the fact that the affinity
rules do not apply for the special "INTEGER PRIMARY KEY" type.

Perhaps "INTEGER PRIMARY KEY" could be a special rule #0, as otherwise the
impression is that BigInt and Integer should act the same in all
circumstances, which I have learned now is clearly not the case.

I do notice now there is a cryptic link to "INTEGER PRIMARY KEY
<https://sqlite.org/lang_createtable.html#rowid>" in the section 2, before
type affinity is introduced, but it gives no reason for the reader to think
it links to special rules about how type affinity may or may not work. i.e.
it says "Any column in an SQLite version 3 database, except an INTEGER
PRIMARY KEY column, may be used to store a value of any storage class."

So I hope this documentation page can be made clearer for future devs.

As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
avoid rowid reuse, but I can avoid using it for the sake of optimization,
so probably will.

Thanks again.
_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Britton Kerin
I just want to note here that I'm uncomfortable with the docs on type
affinity in a general sort of way.  I've read that section several
times in the hope of feeling more comfortable, but so far I still
don't.  Unfortunately I can't say how I would improve them exactly, I
just know they leave me with an unhappy feeling of not knowing exactly
what's going on.  Perhaps someone with more knowledge could improve
them somehow.

Britton

On 11/23/17, Peter Halasz <[hidden email]> wrote:

> Thank you. It looks like I was looking at the wrong documentation page.
>
> So I will reiterate and clarify my advice that the datatype documentation (
> https://sqlite.org/datatype3.html), within the section on "Determination Of
> Column Affinity", should more clearly state the fact that the affinity
> rules do not apply for the special "INTEGER PRIMARY KEY" type.
>
> Perhaps "INTEGER PRIMARY KEY" could be a special rule #0, as otherwise the
> impression is that BigInt and Integer should act the same in all
> circumstances, which I have learned now is clearly not the case.
>
> I do notice now there is a cryptic link to "INTEGER PRIMARY KEY
> <https://sqlite.org/lang_createtable.html#rowid>" in the section 2, before
> type affinity is introduced, but it gives no reason for the reader to think
> it links to special rules about how type affinity may or may not work. i.e.
> it says "Any column in an SQLite version 3 database, except an INTEGER
> PRIMARY KEY column, may be used to store a value of any storage class."
>
> So I hope this documentation page can be made clearer for future devs.
>
> As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
> avoid rowid reuse, but I can avoid using it for the sake of optimization,
> so probably will.
>
> Thanks again.
> _______________________________________________
> 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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

R Smith
In reply to this post by Peter Halasz

On 2017/11/24 5:23 AM, Peter Halasz wrote:
> As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
> avoid rowid reuse, but I can avoid using it for the sake of optimization,
> so probably will.

I agree with Keith and has many times mentioned this before (apologies
to others for the déjà vu). You as the programmer / designer should
always be in control of how and why a new ID is assigned.

It feels like a saving to let the DB engine do it for you, but it isn't
really. What you save in a bit of code that decides the new ID
before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you
typically have to catch up afterward in code to do the usual
get-LastInsertedID and then pop that in wherever stuff wants to link to
the new item. It's a zero-sum gain really.

I'm not even a big fan of Integer IDs, I think codes / UUIDs are best,
but obviously the speed/size gain with an integer key (especially
INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored.

Disclaimer: This is only my opinion, well, I'm not completely alone in
it, but it is still an opinion and not a general SQL prescription.


_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Dominique Devienne
On Fri, Nov 24, 2017 at 9:51 AM, R Smith <[hidden email]> wrote:

> I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, but
> obviously the speed/size gain with an integer key (especially INTEGER
> PRIMARY KEY row-id alias in SQLite) can't be ignored.
>
> Disclaimer: This is only my opinion, well, I'm not completely alone in it,
> but it is still an opinion and not a general SQL prescription.


Many opinions on this everywhere, and discussed a few times on this list as
well.
Here's one among a million:
https://blog.codinghorror.com/primary-keys-ids-versus-guids/

--DD

PS: I use guids are surrogate keys (always with a companion natural key,
aka unique index, often composite).
_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Richard Damon
In reply to this post by R Smith
On 11/24/17 3:51 AM, R Smith wrote:

>
> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>> As for whether I need to use AUTOINCREMENT, it seemed like a good
>> idea to
>> avoid rowid reuse, but I can avoid using it for the sake of
>> optimization,
>> so probably will.
>
> I agree with Keith and has many times mentioned this before (apologies
> to others for the déjà vu). You as the programmer / designer should
> always be in control of how and why a new ID is assigned.
>
I would disagree here, in many cases the ID (rowid) is a purely internal
attribute with the purpose of accessing the data. It may have no problem
domain significance. If the primary key's purpose is purely data access,
then letting the data access layer handle it makes sense. The one case
where it makes sense for the programmer / designer to take control of
the PK is if the domain naturally has an identifier that would be
suitable for the key (an reasonable sized integer that is naturally
unique), The designer should also be fairly certain that it will remain so.
> It feels like a saving to let the DB engine do it for you, but it
> isn't really. What you save in a bit of code that decides the new ID
> before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you
> typically have to catch up afterward in code to do the usual
> get-LastInsertedID and then pop that in wherever stuff wants to link
> to the new item. It's a zero-sum gain really.
The big issues with this method is if two processes both try to create a
new item at the same time, one of them is going to get an error and have
to redo its work. If you start by creating the record with an
autoincrement id, and then getting the ID used, then you remove the need
to handle the error on the simultaneous creation.
>
> I'm not even a big fan of Integer IDs, I think codes / UUIDs are best,
> but obviously the speed/size gain with an integer key (especially
> INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored.
>
Making a UUID or other 'big' key the primary access key will increase
the cost of looking up a record for ALL purposes. For small tables, it
might not be measurable, but small tables are less apt to need that sort
of PK either. It can make sense to use a key like that as an External
Key to describe the record to the outside world. Perhaps if the ONLY
accesses to a table are via this 'big' key, and very rarely by some
other key/field, making the big key the primary key would make sense.
> Disclaimer: This is only my opinion, well, I'm not completely alone in
> it, but it is still an opinion and not a general SQL prescription.

--
Richard Damon

_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Stephen Chrzanowski
Obviously, this is a design time factor, but, in my applications, I always
use integer IDs if I'm ever going to bring the info that row contains (And
other relevant info) to the UI.  I have never had a solid reason to use
GUIDs or UUIDs or whatever.

Any time I'm adding something to a listbox, combo box, or whatever it is,
that element of that lists Object (RE: tStringList) gets the ID that is in
the database.  Using a UID would not work, unless I spend CPU cycles
converting a 32 character string to bytes, then to 128-bit numbers.  But
then, my compiler is 32-bit only, but can emulate 64-bit numbers.

I can see the reasoning why a UUID is appealing, but, an ID is an ID.  It
doesn't matter what it is.  When you use INTEGER PRIMARY KEY, you get from
1 to 2^64-1 numbers to play with at LEAST.  I don't know if SQLite will go
into 128 or 256bit integers.  With UUID, you're looking at a chance of
collision.  Small, yes.  But its there.  With INTEGER PRIMARY KEY, you're
going up by one each time.  Since I will never care what that ID is, as a
developer or as a user, Integer IDs are perfect.


On Fri, Nov 24, 2017 at 12:33 PM, Richard Damon <[hidden email]>
wrote:

> On 11/24/17 3:51 AM, R Smith wrote:
>
>>
>> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>>
>>> As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
>>> avoid rowid reuse, but I can avoid using it for the sake of optimization,
>>> so probably will.
>>>
>>
>> I agree with Keith and has many times mentioned this before (apologies to
>> others for the déjà vu). You as the programmer / designer should always be
>> in control of how and why a new ID is assigned.
>>
>> I would disagree here, in many cases the ID (rowid) is a purely internal
> attribute with the purpose of accessing the data. It may have no problem
> domain significance. If the primary key's purpose is purely data access,
> then letting the data access layer handle it makes sense. The one case
> where it makes sense for the programmer / designer to take control of the
> PK is if the domain naturally has an identifier that would be suitable for
> the key (an reasonable sized integer that is naturally unique), The
> designer should also be fairly certain that it will remain so.
>
>> It feels like a saving to let the DB engine do it for you, but it isn't
>> really. What you save in a bit of code that decides the new ID before-hand
>> (which can be as simple as SELECT MAX(id)+1 FROM t) you typically have to
>> catch up afterward in code to do the usual get-LastInsertedID and then pop
>> that in wherever stuff wants to link to the new item. It's a zero-sum gain
>> really.
>>
> The big issues with this method is if two processes both try to create a
> new item at the same time, one of them is going to get an error and have to
> redo its work. If you start by creating the record with an autoincrement
> id, and then getting the ID used, then you remove the need to handle the
> error on the simultaneous creation.
>
>>
>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are best,
>> but obviously the speed/size gain with an integer key (especially INTEGER
>> PRIMARY KEY row-id alias in SQLite) can't be ignored.
>>
>> Making a UUID or other 'big' key the primary access key will increase the
> cost of looking up a record for ALL purposes. For small tables, it might
> not be measurable, but small tables are less apt to need that sort of PK
> either. It can make sense to use a key like that as an External Key to
> describe the record to the outside world. Perhaps if the ONLY accesses to a
> table are via this 'big' key, and very rarely by some other key/field,
> making the big key the primary key would make sense.
>
>> Disclaimer: This is only my opinion, well, I'm not completely alone in
>> it, but it is still an opinion and not a general SQL prescription.
>>
>
> --
> Richard Damon
>
>
> _______________________________________________
> 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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Keith Medcalf
In reply to this post by Richard Damon

You are missing the point.  

The rowid is assigned automatically if it is not specified (that is, if it is null on insert).  This is the behaviour of the rowid.  In all databases and filesystems ever invented anywhere in the multiverse by any carbon (even non-carbon) based lifeform, whether an ugly bag of mostly water or not.

The AUTOINCREMENT keyword is an entirely different beast that I have never seen actually required for any useful purpose (except that there appears to be a number of folks who seem to think that the rowid is not automatically generated without that keyword because they never RTFM or have some form of cognitive disability).

It has nothing to do with choosing a primary key, a surrogate key, or an alternate key.

---
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-
>[hidden email]] On Behalf Of Richard Damon
>Sent: Friday, 24 November, 2017 10:33
>To: [hidden email]
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>On 11/24/17 3:51 AM, R Smith wrote:
>>
>> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>>> As for whether I need to use AUTOINCREMENT, it seemed like a good
>>> idea to
>>> avoid rowid reuse, but I can avoid using it for the sake of
>>> optimization,
>>> so probably will.
>>
>> I agree with Keith and has many times mentioned this before
>(apologies
>> to others for the déjà vu). You as the programmer / designer should
>> always be in control of how and why a new ID is assigned.
>>
>I would disagree here, in many cases the ID (rowid) is a purely
>internal
>attribute with the purpose of accessing the data. It may have no
>problem
>domain significance. If the primary key's purpose is purely data
>access,
>then letting the data access layer handle it makes sense. The one
>case
>where it makes sense for the programmer / designer to take control of
>the PK is if the domain naturally has an identifier that would be
>suitable for the key (an reasonable sized integer that is naturally
>unique), The designer should also be fairly certain that it will
>remain so.
>> It feels like a saving to let the DB engine do it for you, but it
>> isn't really. What you save in a bit of code that decides the new
>ID
>> before-hand (which can be as simple as SELECT MAX(id)+1 FROM t) you
>> typically have to catch up afterward in code to do the usual
>> get-LastInsertedID and then pop that in wherever stuff wants to
>link
>> to the new item. It's a zero-sum gain really.
>The big issues with this method is if two processes both try to
>create a
>new item at the same time, one of them is going to get an error and
>have
>to redo its work. If you start by creating the record with an
>autoincrement id, and then getting the ID used, then you remove the
>need
>to handle the error on the simultaneous creation.
>>
>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are
>best,
>> but obviously the speed/size gain with an integer key (especially
>> INTEGER PRIMARY KEY row-id alias in SQLite) can't be ignored.
>>
>Making a UUID or other 'big' key the primary access key will increase
>the cost of looking up a record for ALL purposes. For small tables,
>it
>might not be measurable, but small tables are less apt to need that
>sort
>of PK either. It can make sense to use a key like that as an External
>Key to describe the record to the outside world. Perhaps if the ONLY
>accesses to a table are via this 'big' key, and very rarely by some
>other key/field, making the big key the primary key would make sense.
>> Disclaimer: This is only my opinion, well, I'm not completely alone
>in
>> it, but it is still an opinion and not a general SQL prescription.
>
>--
>Richard Damon
>
>_______________________________________________
>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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Keith Medcalf
In reply to this post by Stephen Chrzanowski

Actually a UUID or a GUID has a 100% certainty of a collision,  not just a possibility of a collision.  Just as all hash algorithms which take something and generate a shorter "hash" or "checksum" will always have collisions.  Without exception and as an absolute 100% certainty.  There is no way to avoid this mathematical certainty.  

However, whether the absolute and unmitigatable certainty of a collision is of any import or not is an entirely different matter.

---
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-
>[hidden email]] On Behalf Of Stephen Chrzanowski
>Sent: Friday, 24 November, 2017 11:21
>To: SQLite mailing list
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>Obviously, this is a design time factor, but, in my applications, I
>always
>use integer IDs if I'm ever going to bring the info that row contains
>(And
>other relevant info) to the UI.  I have never had a solid reason to
>use
>GUIDs or UUIDs or whatever.
>
>Any time I'm adding something to a listbox, combo box, or whatever it
>is,
>that element of that lists Object (RE: tStringList) gets the ID that
>is in
>the database.  Using a UID would not work, unless I spend CPU cycles
>converting a 32 character string to bytes, then to 128-bit numbers.
>But
>then, my compiler is 32-bit only, but can emulate 64-bit numbers.
>
>I can see the reasoning why a UUID is appealing, but, an ID is an ID.
>It
>doesn't matter what it is.  When you use INTEGER PRIMARY KEY, you get
>from
>1 to 2^64-1 numbers to play with at LEAST.  I don't know if SQLite
>will go
>into 128 or 256bit integers.  With UUID, you're looking at a chance
>of
>collision.  Small, yes.  But its there.  With INTEGER PRIMARY KEY,
>you're
>going up by one each time.  Since I will never care what that ID is,
>as a
>developer or as a user, Integer IDs are perfect.
>
>
>On Fri, Nov 24, 2017 at 12:33 PM, Richard Damon <Richard@damon-
>family.org>
>wrote:
>
>> On 11/24/17 3:51 AM, R Smith wrote:
>>
>>>
>>> On 2017/11/24 5:23 AM, Peter Halasz wrote:
>>>
>>>> As for whether I need to use AUTOINCREMENT, it seemed like a good
>idea to
>>>> avoid rowid reuse, but I can avoid using it for the sake of
>optimization,
>>>> so probably will.
>>>>
>>>
>>> I agree with Keith and has many times mentioned this before
>(apologies to
>>> others for the déjà vu). You as the programmer / designer should
>always be
>>> in control of how and why a new ID is assigned.
>>>
>>> I would disagree here, in many cases the ID (rowid) is a purely
>internal
>> attribute with the purpose of accessing the data. It may have no
>problem
>> domain significance. If the primary key's purpose is purely data
>access,
>> then letting the data access layer handle it makes sense. The one
>case
>> where it makes sense for the programmer / designer to take control
>of the
>> PK is if the domain naturally has an identifier that would be
>suitable for
>> the key (an reasonable sized integer that is naturally unique), The
>> designer should also be fairly certain that it will remain so.
>>
>>> It feels like a saving to let the DB engine do it for you, but it
>isn't
>>> really. What you save in a bit of code that decides the new ID
>before-hand
>>> (which can be as simple as SELECT MAX(id)+1 FROM t) you typically
>have to
>>> catch up afterward in code to do the usual get-LastInsertedID and
>then pop
>>> that in wherever stuff wants to link to the new item. It's a zero-
>sum gain
>>> really.
>>>
>> The big issues with this method is if two processes both try to
>create a
>> new item at the same time, one of them is going to get an error and
>have to
>> redo its work. If you start by creating the record with an
>autoincrement
>> id, and then getting the ID used, then you remove the need to
>handle the
>> error on the simultaneous creation.
>>
>>>
>>> I'm not even a big fan of Integer IDs, I think codes / UUIDs are
>best,
>>> but obviously the speed/size gain with an integer key (especially
>INTEGER
>>> PRIMARY KEY row-id alias in SQLite) can't be ignored.
>>>
>>> Making a UUID or other 'big' key the primary access key will
>increase the
>> cost of looking up a record for ALL purposes. For small tables, it
>might
>> not be measurable, but small tables are less apt to need that sort
>of PK
>> either. It can make sense to use a key like that as an External Key
>to
>> describe the record to the outside world. Perhaps if the ONLY
>accesses to a
>> table are via this 'big' key, and very rarely by some other
>key/field,
>> making the big key the primary key would make sense.
>>
>>> Disclaimer: This is only my opinion, well, I'm not completely
>alone in
>>> it, but it is still an opinion and not a general SQL prescription.
>>>
>>
>> --
>> Richard Damon
>>
>>
>> _______________________________________________
>> 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
Reply | Threaded
Open this post in threaded view
|

Re: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Richard Damon
On 11/24/17 2:25 PM, Keith Medcalf wrote:
> Actually a UUID or a GUID has a 100% certainty of a collision,  not just a possibility of a collision.  Just as all hash algorithms which take something and generate a shorter "hash" or "checksum" will always have collisions.  Without exception and as an absolute 100% certainty.  There is no way to avoid this mathematical certainty.
>
> However, whether the absolute and unmitigatable certainty of a collision is of any import or not is an entirely different matter.
>
Absolutely incorrect, for a UID. Perhaps if you are talking about actual
hashes, you can say that there are always multiple (at least potential)
messages that will generate the same hash value (but for a good hash,
the likelihood that one of them is sensible or even actually generated
is minuscule). for a UID, while they are typically created by a hash of
various information, including something that varies each time a given
generator is used, what those inputs actually are is generally
unimportant, but are mostly provided to help make the 'randomness' of
the choice more 'random'. We are never really concerned with 'potential'
collisions, only actual collisions.

One proof of the falsehood of your assertion is that we CAN fill a
database with some data using UIDs, and we will almost certainly not get
a collision, while you assertion we will.

Yes, there is a limit on how many entries we can generate before we will
likely, or even certainly hit a collision, but if that number is
significantly higher than the number of records we will generate (or
even CAN generate), we can assume relative safety. With a 128 bit UID,
the approximate point we need to worry about random collisions is the
order of 2^64, I suspect that creating an SQLite database with 2^64
non-trivial records in a single table is likely going to have other
issues besides unique key collisions.

--
Richard Damon

_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Jean-Christophe Deschamps-3

At 22:38 24/11/2017, you wrote:
>One proof of the falsehood of your assertion is that we CAN fill a
>database with some data using UIDs, and we will almost certainly not
>get a collision, while you assertion we will.

This is an attempt at "proof by example". Keith is perfectly right
--mathematically speaking-- and your "proof" doesn't hold water, I mean
as a formal proof.  The best proof that your "proof" isn't a proof is
that you feel obliged  to add "almost certainly".

If SQLite was coded so that "SELECT 3.1415926" would "almost certainly"
return the expected value you just wouldn't use it. Same thing can be
applied to, say, a the classical Hello world! program and a C compiler,
but in this case experience teaches us to be even much more careful!

I agree that you or anyone else _may_ consider the odds of UUID
collision(s) rare enough to ignore the issue and this is examplified in
practice by a huge number of systems using UUIDs or similar things. But
this doesn't make a proof of anything. That's the difference between
theory and practice. Search for a good quote in this list about theory
and practice ;-)

JcD

_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Richard Damon
In reply to this post by Richard Damon
On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:

>
> At 22:38 24/11/2017, you wrote:
>> One proof of the falsehood of your assertion is that we CAN fill a
>> database with some data using UIDs, and we will almost certainly not
>> get a collision, while you assertion we will.
>
> This is an attempt at "proof by example". Keith is perfectly right
> --mathematically speaking-- and your "proof" doesn't hold water, I
> mean as a formal proof.  The best proof that your "proof" isn't a
> proof is that you feel obliged  to add "almost certainly".
>
DISproof by example is a perfectly valid method. If someone makes a
claim that something is ALWAYS true, ONE counter example IS a disproof.
I said almost certainly as the chance of a collision isn't 0 (to be able
to say with certainty) but is most defintely less than the 100% claimed.

> If SQLite was coded so that "SELECT 3.1415926" would "almost
> certainly" return the expected value you just wouldn't use it. Same
> thing can be applied to, say, a the classical Hello world! program and
> a C compiler, but in this case experience teaches us to be even much
> more careful!
>
> I agree that you or anyone else _may_ consider the odds of UUID
> collision(s) rare enough to ignore the issue and this is examplified
> in practice by a huge number of systems using UUIDs or similar things.
> But this doesn't make a proof of anything. That's the difference
> between theory and practice. Search for a good quote in this list
> about theory and practice ;-)
Considering that for a 'reasonable' number of records, (maybe even into
billions), the odds of a collision are probably on the order of once in
the life of the universe (I would need to run the math to get the exact
number, but it is minuscule in the period of the lifetime of a typical
computer), it seems to be a reasonable assumption. A big part might be
the consequences of a collision.
>
> JcD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

J Decker
In reply to this post by Keith Medcalf
On Fri, Nov 24, 2017 at 11:25 AM, Keith Medcalf <[hidden email]> wrote:

>
> Actually a UUID or a GUID has a 100% certainty of a collision,  not just a
> possibility of a collision.  Just as all hash algorithms which take
> something and generate a shorter "hash" or "checksum" will always have
> collisions.  Without exception and as an absolute 100% certainty.  There is
> no way to avoid this mathematical certainty.
>
> However, whether the absolute and unmitigatable certainty of a collision
> is of any import or not is an entirely different matter.
>
>
*I*t's also 100% certainty that collision will occur with a 32 or 64 bit
row id before a UUID collision occurs.

For the truly paranoid UUID can be made CUID  (
https://github.com/ericelliott/cuid ) by using seconds for 32 of the 128
bits.

" 1 billion UUIDs per second for about 85 years, and a file containing this
many UUIDs, at 16 bytes per UUID, would be about 45 exabytes, many times
larger than the largest databases currently in existence, which are on the
order of hundreds of petabytes. "
85 years in seconds is 2 680 560 000, which is about 1/2 of a 32 bit
number, so you can add a factor of 170 to that ... or 14,450 years before
50% collision probability.  (generating a billion a second mind you for
14,000 years)
_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Peter Halasz
In reply to this post by Keith Medcalf
Sorry to steer the conversation back to the topic.

Looks like I do need to use AUTOINCREMENT after all, otherwise the
framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
columns are allowed a NULL value on insert. That's a shame.

Is there a way to query a schema to get a direct answer to whether table
has a column which acts as the ROW ID alias? I'd like to improve linq2db's
code generation if I can, but I can't see any straightforward way to do it
as the ROW ID information is hidden (in SQLite's API, not just its
documentation)

I found someone asking the same question on this mailing list in 2010 who
was told to: [1]

    "look at column 'sql' of TABLE sqlite_master and parse the creation
statement"

Which is frankly ridiculous.

I'm hoping there's an actual answer 7 years later that doesn't involve
implementing a SQL parser? (* please *don't even make suggestions on how to
do this)

I can't find anything in the pragma documentation. [2] The closest I could
see is "*PRAGMA **index_xinfo(**index-name**);*" which will give
information on whether an index references a rowid. I tried creating an
index for a column just to test if it was the ROW ID but it doesn't
actually work that way.

On the page https://www.sqlite.org/rowidtable.html it states:

   -

   All of the complications above (and others not mentioned here) arise
   from the need to preserve backwards compatibility to the tens of billions
   of SQLite database files in circulation. In a perfect world, there would be
   no such thing as a "rowid" and all tables would following the standard
   semantics implemented as WITHOUT ROWID tables, only without the extra
   "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of
   SQLite offers his sincere apology for the current mess.

So it appears ROWID is sticking around. Why is it hidden in the
documentation (not listed on on pages like the datatype page), and hidden
in the pragma interface as well?

Is there an API call or PRAGMA statement that gives this information
directly?

Thanks.


[1]
http://sqlite.1065341.n5.nabble.com/Introspection-and-RowID-INTEGER-PRIMARY-KEY-td60462.html
[2] https://sqlite.org/pragma.html
_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

J Decker
On Fri, Nov 24, 2017 at 3:13 PM, Peter Halasz <[hidden email]>
wrote:

> Sorry to steer the conversation back to the topic.
>
> Looks like I do need to use AUTOINCREMENT after all, otherwise the
> framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
> columns are allowed a NULL value on insert. That's a shame.
>
> Is there a way to query a schema to get a direct answer to whether table
> has a column which acts as the ROW ID alias? I'd like to improve linq2db's
> code generation if I can, but I can't see any straightforward way to do it
> as the ROW ID information is hidden (in SQLite's API, not just its
> documentation)
>
> I found someone asking the same question on this mailing list in 2010 who
> was told to: [1]
>
>     "look at column 'sql' of TABLE sqlite_master and parse the creation
> statement"
>
> Which is frankly ridiculous.
>

*shrug* it probably is ridiculous, but I did anyway....  XDataTable extends
DataTable and adds foriegn key relations; otherwise you need the full
DataSet to deal with some table-oriented things (like foreign keys)
https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/XDataTable.cs

This is a parser that can either generate a create table statement from a
datatable, or a datatable from a create table statement.  (Or merge a data
table filled with existing columns and merge it with what's already in a
database)

https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/SQL_Utilities.cs

I use this to parse the create table statement into reasonable tokens.
https://sourceforge.net/p/xperdex/mercurial/ci/default/tree/xperdex.classes.2/Types/XString.cs

The above can probably be disected from the full surrounding library with a
little work... Some features of XDataTable should probably be removed...
the first version automatically synced to the database when rows were
added/deleted/modified.... I eventually moved that behind an option 'live'
that if 'live' do the inserts directly; otherwise wait, and then later, a
full dataset (XDataSet) can sync all changes in a dataset in appropriate
order so foriegn key parent keys are inserted first.

It has a personality selection also, based on information from
DsnConnection class, which tells it whether it is SQL Server, MySQL or
Sqlite, so generation of types for columns in the database form the types
in the DataTable change depending on the flavor of database, and
constraint/foriegn key generation changes also....




> I'm hoping there's an actual answer 7 years later that doesn't involve
> implementing a SQL parser? (* please *don't even make suggestions on how to
> do this)
>
> I can't find anything in the pragma documentation. [2] The closest I could
> see is "*PRAGMA **index_xinfo(**index-name**);*" which will give
> information on whether an index references a rowid. I tried creating an
> index for a column just to test if it was the ROW ID but it doesn't
> actually work that way.
>
> On the page https://www.sqlite.org/rowidtable.html it states:
>
>    -
>
>    All of the complications above (and others not mentioned here) arise
>    from the need to preserve backwards compatibility to the tens of
> billions
>    of SQLite database files in circulation. In a perfect world, there
> would be
>    no such thing as a "rowid" and all tables would following the standard
>    semantics implemented as WITHOUT ROWID tables, only without the extra
>    "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of
>    SQLite offers his sincere apology for the current mess.
>
> So it appears ROWID is sticking around. Why is it hidden in the
> documentation (not listed on on pages like the datatype page), and hidden
> in the pragma interface as well?
>
> Is there an API call or PRAGMA statement that gives this information
> directly?
>
> Thanks.
>
>
> [1]
> http://sqlite.1065341.n5.nabble.com/Introspection-and-
> RowID-INTEGER-PRIMARY-KEY-td60462.html
> [2] https://sqlite.org/pragma.html
> _______________________________________________
> 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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

petern
In reply to this post by Peter Halasz
Apparently you would query pragma_table_info for "INTEGER" PK columns and
then ask through a trivial extension function about the other column
meta-data:

https://www.sqlite.org/c3ref/table_column_metadata.html

The basic plot is illustrated below:

sqlite> .load column-meta-data.so
sqlite> SELECT *,isRowId(name) FROM pragma_table_info('the_table') WHERE pk
AND type='INTEGER'

cid,name,type,notnull,dflt_value,pk,"isRowId('the_table',name)"
...1
...etc

Your only serious problem, I think, would be to distribute the
column-meta-data.c extension with your tool.



On Fri, Nov 24, 2017 at 3:13 PM, Peter Halasz <[hidden email]>
wrote:

> Sorry to steer the conversation back to the topic.
>
> Looks like I do need to use AUTOINCREMENT after all, otherwise the
> framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
> columns are allowed a NULL value on insert. That's a shame.
>
> Is there a way to query a schema to get a direct answer to whether table
> has a column which acts as the ROW ID alias? I'd like to improve linq2db's
> code generation if I can, but I can't see any straightforward way to do it
> as the ROW ID information is hidden (in SQLite's API, not just its
> documentation)
>
> I found someone asking the same question on this mailing list in 2010 who
> was told to: [1]
>
>     "look at column 'sql' of TABLE sqlite_master and parse the creation
> statement"
>
> Which is frankly ridiculous.
>
> I'm hoping there's an actual answer 7 years later that doesn't involve
> implementing a SQL parser? (* please *don't even make suggestions on how to
> do this)
>
> I can't find anything in the pragma documentation. [2] The closest I could
> see is "*PRAGMA **index_xinfo(**index-name**);*" which will give
> information on whether an index references a rowid. I tried creating an
> index for a column just to test if it was the ROW ID but it doesn't
> actually work that way.
>
> On the page https://www.sqlite.org/rowidtable.html it states:
>
>    -
>
>    All of the complications above (and others not mentioned here) arise
>    from the need to preserve backwards compatibility to the tens of
> billions
>    of SQLite database files in circulation. In a perfect world, there
> would be
>    no such thing as a "rowid" and all tables would following the standard
>    semantics implemented as WITHOUT ROWID tables, only without the extra
>    "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of
>    SQLite offers his sincere apology for the current mess.
>
> So it appears ROWID is sticking around. Why is it hidden in the
> documentation (not listed on on pages like the datatype page), and hidden
> in the pragma interface as well?
>
> Is there an API call or PRAGMA statement that gives this information
> directly?
>
> Thanks.
>
>
> [1]
> http://sqlite.1065341.n5.nabble.com/Introspection-and-
> RowID-INTEGER-PRIMARY-KEY-td60462.html
> [2] https://sqlite.org/pragma.html
> _______________________________________________
> 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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Peter Halasz
> sqlite> .load column-meta-data.so

Sorry I'm at a loss to find this extension? Google gives me nothing related
to SQLite for "isRowId", "column-meta-data.so", "column-meta-data.c", etc.
_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Jean-Christophe Deschamps-3
In reply to this post by Richard Damon

At 23:49 24/11/2017, you wrote:

>On 11/24/17 5:26 PM, Jean-Christophe Deschamps wrote:
>
>>At 22:38 24/11/2017, you wrote:
>>>One proof of the falsehood of your assertion is that we CAN fill a
>>>database with some data using UIDs, and we will almost certainly not
>>>get a collision, while you assertion we will.
>
>>This is an attempt at "proof by example". Keith is perfectly right
>>--mathematically speaking-- and your "proof" doesn't hold water, I
>>mean as a formal proof.  The best proof that your "proof" isn't a
>>proof is that you feel obliged  to add "almost certainly".
>
>DISproof by example is a perfectly valid method. If someone makes a
>claim that something is ALWAYS true, ONE counter example IS a
>disproof. I said almost certainly as the chance of a collision isn't 0
>(to be able to say with certainty) but is most defintely less than the
>100% claimed.

You're confusing one mathematical theorem and one practical statement.
The first is the _mathematical_ fact that any PRNG (using any fixed
number of random bits, which is what xUIDs are) will provide an
infinite number of collisions with probability 1. This is definitely
true. Of course here, the number of samples is implicitely infinite.

Your practical statement is that you can "most certainly" ignore the
possibility of collision when feeding 2^N xUIDs into a unique column
without loosing sleep. That's good enough in practice. The issue with
your "demonstration" is that 2^N is bounded, whatever finite N you
choose. Hence you don't contradict what Keith said, you just say
something different applying to restricted cases. You're speaking about
practice, while Keith told about math. You're both right, each from his
own point of view. But you can't claim to disproof a trivially true
theorem this way, by changing its premices.

An event with probability 10^-100000...000 (any finite number of
zeroes) will occur at least once, provided you run enough tries. It'll
occur an infinite number of times if you run an infinite number of
tries. Else its probability would be zero.
Your "disproof" amounts to say that 10^-100000...000 = 0

And neither Keith nor I ever said that an xUID collision will occur
with probability 1 after 2^64 samples. That would be false and that's
why people feel free to use xUIDs _AND_ sleep quietly.

JcD

_______________________________________________
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: "BIGINT" doesn't act like INTEGER on table creation [Bug]

Jean-Christophe Deschamps-3
In reply to this post by Peter Halasz

At 00:13 25/11/2017, you wrote:
>Looks like I do need to use AUTOINCREMENT after all, otherwise the
>framework I'm using doesn't appear to recognize that INTEGER PRIMARY KEY
>columns are allowed a NULL value on insert.

I can't answer about just INTEGER PRIMARY KEY columns, but any table
with an AUTOINCREMENT column has an entry in table sqlite_sequence,
something much easier to deal with than digging into sqlite_master.

JcD

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