Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[John Hascall]

Would dropping the non-functioning default clause from the schema be a
"breaking change"?

That is from:
# sqlite3 dummy.db
sqlite> CREATE TABLE x(
   ...> id INTEGER PRIMARY KEY DEFAULT (random()),
   ...> val VARCHAR
   ...> );
sqlite> .schema
CREATE TABLE x(
*id INTEGER PRIMARY KEY DEFAULT (random()),*
val VARCHAR
);
to:
# sqlite3 dummy.db
sqlite> CREATE TABLE x(
   ...> id INTEGER PRIMARY KEY DEFAULT (random()),
   ...> val VARCHAR
   ...> );
sqlite> .schema
CREATE TABLE x(
*id INTEGER PRIMARY KEY*,
val VARCHAR
);

And would it be better, or cause more head-scratching, I dunno...


John

On Thu, Sep 25, 2014 at 4:07 PM, Richard Hipp  wrote:

> On Thu, Sep 25, 2014 at 4:46 PM, Mark Lawrence  wrote:
>
> >
> > If you are going to keep this behaviour would it not make more sense to
> > ensure that the table creation fails? The DEFAULT clause is pretty
> > straight-forward and I don't find it intuitive to go looking for
> > PRIMARY KEY documentation when it is ignored.
> >
> > SQLite should either fail to accept the statement or do what the table
> > definition says - anything else means heartache for those debugging
> > problems (which I did for hours on this issue) and those reading the
> > code afterwards.
> >
>
> That would break backwards compatibility for the millions and millions of
> applications currently using SQLite.  Most of those millions would be
> unaffected, no doubt, but out of millions I'm sure there are a goodly
> number that would break.  I am unwilling to implement a breaking change
> simply to make the interface more "intuitive".
>
> Had you brought this up in 2002, the outcome would likely have been very
> different.  But at this point, the behavior of INTEGER PRIMARY KEY in
> SQLite is not something that can be modified.
>
> Sorry to disappoint.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[Mark Lawrence]

On Thu Sep 25, 2014 at 03:59:55PM -0400, Richard Hipp wrote:
> 
> I will make an effort to clarify this in the documentation.

If you are going to keep this behaviour would it not make more sense to
ensure that the table creation fails? The DEFAULT clause is pretty
straight-forward and I don't find it intuitive to go looking for
PRIMARY KEY documentation when it is ignored.

SQLite should either fail to accept the statement or do what the table
definition says - anything else means heartache for those debugging
problems (which I did for hours on this issue) and those reading the
code afterwards.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[Richard Hipp]

On Thu, Sep 25, 2014 at 3:30 PM, Mark Lawrence  wrote:

>
> I understand that that behaviour exists and applies when an insert does
> not provide a value, but I don't see the contradiction. The table
> defines an *explicit* default that should (to my mind) override any
> kind of magical-in-the-absence-of-a-default-default. Such an explicit
> default should certainly not be accepted if it is going to be ignored.
>

I will make an effort to clarify this in the documentation.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[Mark Lawrence]

On Thu Sep 25, 2014 at 03:18:04PM -0400, Adam Devita wrote:

> Your table definition seems to have a contradiction.  The expression
> INTEGER PRIMARY KEY is a special keyword that means 'auto-increment',
> which would be a default value.

I understand that that behaviour exists and applies when an insert does
not provide a value, but I don't see the contradiction. The table
defines an *explicit* default that should (to my mind) override any
kind of magical-in-the-absence-of-a-default-default. Such an explicit
default should certainly not be accepted if it is going to be ignored.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[Adam Devita]

Your table definition seems to have a contradiction.
The expression INTEGER PRIMARY KEY is a special keyword that means
'auto-increment', which would be a default value.  DEFAULT (random() )
would contradict the auto-increment instruction.  The row id was being used
to generate the key.



On Thu, Sep 25, 2014 at 3:10 PM, Mark Lawrence  wrote:

> Plan:
>
> CREATE TABLE x(
> id INTEGER PRIMARY KEY DEFAULT (random()),
> val VARCHAR
> );
>
> INSERT INTO x(val) VALUES ('a');
> SELECT * FROM x;
>
> Result:
>
> id  val
> --  --
> 1   a
>
> Expected result:
>
> id   val
> ---  --
> 4841191733402647298  a
>
> I get the expected result if I create the table WITHOUT ROWID.
>
> --
> Mark Lawrence
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DEFAULT expression ignored for INTEGER PRIMARY KEYs?

[Mark Lawrence]

Plan:

CREATE TABLE x(
id INTEGER PRIMARY KEY DEFAULT (random()),
val VARCHAR
);

INSERT INTO x(val) VALUES ('a');
SELECT * FROM x;

Result:

id  val   
--  --
1   a 

Expected result:

id   val   
---  --
4841191733402647298  a 

I get the expected result if I create the table WITHOUT ROWID.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users