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

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

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

Keith Medcalf

Actually, it is entirely possible to generate two and only two random xUID side by each and have them be duplicates.  Such is the nature of randomness.  

The only way to ensure that there is no collisions is to check whether the xUID is already in use/seen within the domain where it is used.

A rowid generated by the ordinary method (if no records then 1 else max(rowid)+1) cannot have a key collision within its domain.

---
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 14:39
>To: [hidden email]
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>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



_______________________________________________
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 8:58 PM, Jean-Christophe Deschamps wrote:

>
> 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

The statement, and I quote, was

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.

The claim makes a positive statement of a 100% chance of collision, and
makes an argument about it being a hash, and that it isn't just a chance.

Something happening at least one in an extremely large number of trials
was NEVER a 100% probability in any form of math I have heard of. The
number of records needed to have a significant chance of the collision,
is beyond any practical usage. If the claim was a non-zero chance, then
it would be true. Probability has always been related to times happened
/ times tried. So your claim is that 10^-10000...  == 1, saying
something will EVENTUAL happen is a claim on non-zero probability, not
of 100% probability.

If you mean that a finite width field can't hold unique values for an
infinite of records, well, duh, why all the irrelevant references to
hashes or even that it is a UUID or a GUID.

If it was meant that EVENTUAL, after an impossibly long time, you will
get a collision, that is being absurd. I can say with better certainty
that long before that happens, the computers running this database are
going to break, so we will never get to the point of the collision.

--
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. King-3
In reply to this post by Richard Damon
Version 1 UUIDs only use a random number (16 bits) in the case of an uninitialized clock sequence (a case which, ideally, should only occur the first time a device generates a UUID). Version 1 UUIDs especially avoid using random numbers; they are also not a shortening of longer input.

In short, version 1 UUIDs are not a PRNG scheme, nor are they the same as hash functions.

I'm not a mathematician, and it's been a while since I've read the relevant RFC, but I believe collisions in a proper, strict implementation of V1 UUIDs are impossible by design until either a) the 60-bit timestamp overflows, or b) the MAC address namespace is exhausted. It's not a matter of probability, and it's only "a certainty" after the end of their design lifetime.

Of course, UUIDs being of finite size, they will eventually be exhausted, and a single machine may only generate 65536 identifiers in a 100-nanosecond span of time. They will not, however, collide.

On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps <[hidden email]> wrote:

>
>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

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
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
Well, first you imagine you're back at the office in Santa Clara or Redmond
in the early 1990's.
Then take a belt of whisky, cross your eyes, and paste from doc to
clipboard.... a few edits and voila:

#include "sqlite3ext.h"
#include <string.h>
SQLITE_EXTENSION_INIT1
static struct metadata {
  char const *zDataType; /* OUTPUT: Declared data type */
  char const *zCollSeq; /* OUTPUT: Collation sequence name */
  int NotNull; /* OUTPUT: True if NOT NULL constraint exists */
  int PrimaryKey; /* OUTPUT: True if column part of PK */
  int Autoinc;/* OUTPUT: True if column is auto-increment */
} md;
static void initmd(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  sqlite3_table_column_metadata(
    sqlite3_context_db_handle(context), /* Connection handle */
    0, /* Database name or NULL */
    (char const*)sqlite3_value_text(argv[0]), /* Table name */
    (char const*)sqlite3_value_text(argv[1]), /* Column name */
    &md.zDataType, /* OUTPUT: Declared data type */
    &md.zCollSeq, /* OUTPUT: Collation sequence name */
    &md.NotNull, /* OUTPUT: True if NOT NULL constraint exists */
    &md.PrimaryKey, /* OUTPUT: True if column part of PK */
    &md.Autoinc/* OUTPUT: True if column is auto-increment */
    );
}
static void collseq(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  initmd(context,argc,argv);
  if (!md.zCollSeq) return;
  sqlite3_result_text(context, md.zCollSeq, strlen(md.zCollSeq),
SQLITE_TRANSIENT);
}
static void autoinc(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  initmd(context,argc,argv);
  sqlite3_result_int(context, md.Autoinc);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_colmetadata_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "collseq", 2, SQLITE_UTF8, 0,
collseq, 0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "autoinc", 2,
SQLITE_UTF8, 0, autoinc, 0, 0);
  return rc;
}

Linux box compile is something like this:

gcc -I<sqlite build dir> -fPIC -lm -shared colmetadata.c -o colmetadata.so

Then test it out:

sqlite> .load colmetadata.so
sqlite> SELECT
*,collseq('sqlite_master',name)collseq,autoinc('sqlite_master',name)autoinc
FROM pragma_table_info('sqlite_master');
cid,name,type,notnull,dflt_value,pk,collseq,autoinc
0,type,text,0,,0,BINARY,0
1,name,text,0,,0,BINARY,0
2,tbl_name,text,0,,0,BINARY,0
3,rootpage,integer,0,,0,BINARY,0
4,sql,text,0,,0,BINARY,0

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

So armed with the above document and newly exposed pk,collseq, and autoinc
info you can deduce the rowid aliases.
Feel free to add more functions and clean up the error handling to suit
your needs.










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

> > 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
>
_______________________________________________
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
Is this a joke?
_______________________________________________
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. King-3
Peter (that is, the other Peter) is being more than a little flippant, yes, and at least a little obscure (I don't get the joke, either), but the substance appears quite serious.

His prior message suggested using what I can only assume would be a trivial extension to SQLite to do what you want to do---this extension, however, does not (already) exist.

His last message provided what I can only assume is an example implementation of such an extension.

On November 24, 2017 11:20:33 PM EST, Peter Halasz <[hidden email]> wrote:
>Is this a joke?
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
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 J. King-3

Only if you assume a monotonic clock ...


---
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 J. King
>Sent: Friday, 24 November, 2017 20:54
>To: SQLite mailing list
>Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table
>creation [Bug]
>
>Version 1 UUIDs only use a random number (16 bits) in the case of an
>uninitialized clock sequence (a case which, ideally, should only
>occur the first time a device generates a UUID). Version 1 UUIDs
>especially avoid using random numbers; they are also not a shortening
>of longer input.
>
>In short, version 1 UUIDs are not a PRNG scheme, nor are they the
>same as hash functions.
>
>I'm not a mathematician, and it's been a while since I've read the
>relevant RFC, but I believe collisions in a proper, strict
>implementation of V1 UUIDs are impossible by design until either a)
>the 60-bit timestamp overflows, or b) the MAC address namespace is
>exhausted. It's not a matter of probability, and it's only "a
>certainty" after the end of their design lifetime.
>
>Of course, UUIDs being of finite size, they will eventually be
>exhausted, and a single machine may only generate 65536 identifiers
>in a 100-nanosecond span of time. They will not, however, collide.
>
>On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps
><[hidden email]> wrote:
>>
>>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
>
>--
>Sent from my Android device with K-9 Mail. Please excuse my brevity.
>_______________________________________________
>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]

Darko Volaric
In reply to this post by J. King-3
What about invalid and reused MAC addresses and devices with no MAC address at all?
What about time resets to the epoch which are not restored, user time changes, daylight saving or leap seconds?

It sounds even more probabilistic than the probabilistic methods. Does anyone actually use it?


> On Nov 25, 2017, at 4:54 AM, J. King <[hidden email]> wrote:
>
> Version 1 UUIDs only use a random number (16 bits) in the case of an uninitialized clock sequence (a case which, ideally, should only occur the first time a device generates a UUID). Version 1 UUIDs especially avoid using random numbers; they are also not a shortening of longer input.
>
> In short, version 1 UUIDs are not a PRNG scheme, nor are they the same as hash functions.
>
> I'm not a mathematician, and it's been a while since I've read the relevant RFC, but I believe collisions in a proper, strict implementation of V1 UUIDs are impossible by design until either a) the 60-bit timestamp overflows, or b) the MAC address namespace is exhausted. It's not a matter of probability, and it's only "a certainty" after the end of their design lifetime.
>
> Of course, UUIDs being of finite size, they will eventually be exhausted, and a single machine may only generate 65536 identifiers in a 100-nanosecond span of time. They will not, however, collide.
>
> On November 24, 2017 8:58:02 PM EST, Jean-Christophe Deschamps <[hidden email]> wrote:
>>
>> 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
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> _______________________________________________
> 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 da Silva
In reply to this post by Richard Damon
Are people here talking about UUIDs or things that just look like UUIDs? It sounds like the latter. UUIDs are actually structured objects, with embedded type bits. There are multiple UUID generation schemes, one of which is based on random numbers, others are based on hashes, and there is the common ugly workaround of generating a 128 bit hash and calling it a UUID.

If you use version 1 UUIDs you're mathematically guaranteed to avoid collisions. At least for the next 3000 years:

Version 1 UUIDs are based on a node address (MAC), a 60 bit clock, and a node-specific sequence number. You can generate 163 billion version 1 UUIDs per second and they won't roll over until 5236 AD.

_______________________________________________
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 da Silva
In reply to this post by Darko Volaric
Ah, I see someone else has already brought up version 1 UUIDs.

> What about invalid and reused MAC addresses and devices with no MAC address at all?

Not an issue with SQLite since that part of the UUID is a constant within a given database. It would be reasonable to simply pick "0" then set the low bit of the first octet to 1 in accordance with the standard for non-MAC node IDs. The standard suggests using a hash or a cryptographically secure random number generator, setting the

> What about time resets to the epoch which are not restored, user time changes,

I know some systems at least increment the node each time a time change is detected. It will take 2^47 time changes to roll over. Since the node part is not relevant to SQLite, this is perfectly safe.

> daylight saving or leap seconds?

Not relevant to the timestamp format, since it's an epoch time.

> It sounds even more probabilistic than the probabilistic methods. Does anyone actually use it?

It's Microsoft's standard GUID/UUID format and is also used by a lot of other systems for compatibility.

_______________________________________________
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 da Silva
>> What about time resets to the epoch which are not restored, user time changes,
>
> I know some systems at least increment the node each time a time change is detected. It will take 2^47 time changes to roll over. Since the node part is not relevant to SQLite, this is perfectly safe.

Also, the UUID clock doesn't need to be the system clock, so you can simply ignore backwards changes in the system clock (or maintain a common offset that gets updated whenever a backwards change is detected in the system clock). Over time this may trim a few decades off the 3000+ year life of the format.
_______________________________________________
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]

wmertens
The one thing that saddens me is that the clock is not the full first
part of the UUID, so it's not a proxy for sorting by creation date. I
often wonder why they did that, they must have done it on purpose.

On 11/25/17, Peter Da Silva <[hidden email]> wrote:

>>> What about time resets to the epoch which are not restored, user time
>>> changes,
>>
>> I know some systems at least increment the node each time a time change is
>> detected. It will take 2^47 time changes to roll over. Since the node part
>> is not relevant to SQLite, this is perfectly safe.
>
> Also, the UUID clock doesn't need to be the system clock, so you can simply
> ignore backwards changes in the system clock (or maintain a common offset
> that gets updated whenever a backwards change is detected in the system
> clock). Over time this may trim a few decades off the 3000+ year life of the
> format.
> _______________________________________________
> 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]

Eduardo
In reply to this post by Keith Medcalf
On Thu, 23 Nov 2017 17:35:08 -0700
"Keith Medcalf" <[hidden email]> escribió:

>
> 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.
>
> Personally, I have never found a use for the AUTOINCREMENT option.  Why is
> it being used?

If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number
from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than
the last one inserted/updated(1), so you can use it to know if one row is older
or newer than others, without autoincrement no. You can do similar behavior
with a trigger, for example for TEXT columns or automatically with DATE current
time.

(1) You can change the integer primary key to whatever value you want.
 

> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.

.... See you there then? ;)
 

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

Hick Gunter
Sorry to rain on your parade, but " The sqlite_sequence table does not track ROWID changes associated with UPDATE statement, only INSERT statements.", so if you change the (auto-generated) rowid, your "row age to row id" relation no longer holds. Same goes for updating the sqlite_sequence entry associated with the table.

Indeed, if you immediately update the sqlite_sequence entry for your INTEGER PRIMARY KEY to MAXINT64, you will achieve "random" rowids.


BTW: Consider the following transaction to implement LRU via rowids:

BEGIN
-- retrieve the rowid for the target row (store as rowid in calling program)
SELECT rowid,... FROM mytable WHERE ...;

-- retrieve the sequence value for the table (store as seq in calling program)
SELECT seq+1 FROM SQLITE_SEQUENCE WHERE name='mytable';

-- update values and set new rowid
UPDATE mytable SET rowid=?seq, ... WHERE rowid=?rowid;

-- update sequence value
UPDATE SQLITE_SEQUENCE SET seq=?seq WHERE name='mytable';

COMMIT;

Yes, this will probably cause foreign keys to break or require execssive work (ON UPDATE CASCADE).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Eduardo
Gesendet: Montag, 27. November 2017 10:28
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On Thu, 23 Nov 2017 17:35:08 -0700
"Keith Medcalf" <[hidden email]> escribió:

>
> 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.
>
> Personally, I have never found a use for the AUTOINCREMENT option.
> Why is it being used?

If you declare a INTEGER PRIMARY KEY alone, sqlite could reuse rowid number from deleted rows. Using AUTOINCREMENT forces sqlite to use a rowid bigger than the last one inserted/updated(1), so you can use it to know if one row is older or newer than others, without autoincrement no. You can do similar behavior with a trigger, for example for TEXT columns or automatically with DATE current time.

(1) You can change the integer primary key to whatever value you want.


> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says a lot about anticipated traffic volume.

.... See you there then? ;)


--
Eduardo <[hidden email]>
_______________________________________________
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
12