https://www.sqlite.org/draft/gencol.html Typo

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

https://www.sqlite.org/draft/gencol.html Typo

Keith Medcalf

At the end of the second paragraph of section 2.1:

Only VIRTUAL tables can be added using ALTER TABLE.

should be

Only VIRTUAL columns can be added using ALTER TABLE.

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




_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

Simon Slavin-3
<https://www.sqlite.org/draft/gencol.html>

OMG.  Much welcomed feature.

> • Every table must have at least one non-generated column.

I greatly admire this restriction.

Can I suggest an addition to gencol.html ?  An explicit statement saying whether VIRTUAL and/or STORED columns can be used in CREATE INDEX.  In fact it's such an obvious question it might be useful to have the same note in lang_createindex.html .  Not to mention expridx.html .

On 29 Oct 2019, at 8:44am, Keith Medcalf <[hidden email]> wrote:

> Only VIRTUAL columns can be added using ALTER TABLE.

I agree with your criticism but not your suggested alternative, because the command can be used to add other types of columns.  I suggest something like

    ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS … STORED columns

be used on /both/ pages.
_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

Doug
The draft says "Nor may a generated column depend on the ROWID."

If my table uses ROWID by default:

CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));

where id is ROWID by default, is the generated column disallowed because it has an implied dependency on ROWID?

Doug

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 29, 2019 6:21 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
>
> <https://www.sqlite.org/draft/gencol.html>
>
> OMG.  Much welcomed feature.
>
> > • Every table must have at least one non-generated column.
>
> I greatly admire this restriction.
>
> Can I suggest an addition to gencol.html ?  An explicit statement
> saying whether VIRTUAL and/or STORED columns can be used in CREATE
> INDEX.  In fact it's such an obvious question it might be useful
> to have the same note in lang_createindex.html .  Not to mention
> expridx.html .
>
> On 29 Oct 2019, at 8:44am, Keith Medcalf <[hidden email]>
> wrote:
>
> > Only VIRTUAL columns can be added using ALTER TABLE.
>
> I agree with your criticism but not your suggested alternative,
> because the command can be used to add other types of columns.  I
> suggest something like
>
>     ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS
> … STORED columns
>
> be used on /both/ pages.
> _______________________________________________
> 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: https://www.sqlite.org/draft/gencol.html Typo

Dominique Pellé
In reply to this post by Keith Medcalf
Keith Medcalf wrote:

> At the end of the second paragraph of section 2.1:
>
> Only VIRTUAL tables can be added using ALTER TABLE.
>
> should be
>
> Only VIRTUAL columns can be added using ALTER TABLE.

Above typos is already corrected, but here are 2 other typos
in the same page: https://sqlite.org/draft/gencol.html

section 2.1:  The value of a VIRTUAL *columns* (-> column) is computed
section 2.3: The value of a generated *columns* (-> column) is always

Regards
Dominique
_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

Keith Medcalf
In reply to this post by Doug

On Tuesday, 29 October, 2019 12:25, Doug <[hidden email]> wondered:

>The draft says "Nor may a generated column depend on the ROWID."

>If my table uses ROWID by default:

>CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));

>where id is ROWID by default, is the generated column disallowed because
>it has an implied dependency on ROWID?

sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS (id+a));
sqlite> insert into foo values (1,1),(2,1),(3,1);
sqlite> select * from foo;
1|1|2
2|1|3
3|1|4
sqlite> create table bar (a INTEGER, b AS (_rowid_ + a));
Error: no such column: _rowid_
sqlite> create table bar (a INTEGER, b AS (rowid + a));
Error: no such column: rowid
sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS (_rowid_+a));
Error: no such column: _rowid_
sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS (rowid + a));
Error: no such column: rowid
sqlite> create table bar (_rowid_ INTEGER PRIMARY KEY, a INTEGER, b as (_rowid_ + a));
sqlite> insert into bar (a) values (1),(1),(1);
sqlite> select * from bar;
1|1|2
2|1|3
3|1|4

So a generated column may not depend on the IMPLICITLY named rowid (by whatever magic you want to use), but it may depend on an EXPLICIT rowid (or alias).  In other words, only explicitly named columns are permitted.  If you EXPLICITY named the rowid alias to be _rowid_ it works without problem.  Same rule applies to FOREIGN KEY contraints which may only depend on EXPLICITLY named columns, not the IMPLICIT rowid.

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



_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

Doug
Thanks for the clarification.
I suggest you use your words of explanation in the document to make it clearer instead of a simple sentence.

> -----Original Message-----
> From: sqlite-users <[hidden email]>
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 29, 2019 2:29 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
>
>
> On Tuesday, 29 October, 2019 12:25, Doug <[hidden email]>
> wondered:
>
> >The draft says "Nor may a generated column depend on the ROWID."
>
> >If my table uses ROWID by default:
>
> >CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (id+a));
>
> >where id is ROWID by default, is the generated column disallowed
> because
> >it has an implied dependency on ROWID?
>
> sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (id+a));
> sqlite> insert into foo values (1,1),(2,1),(3,1);
> sqlite> select * from foo;
> 1|1|2
> 2|1|3
> 3|1|4
> sqlite> create table bar (a INTEGER, b AS (_rowid_ + a));
> Error: no such column: _rowid_
> sqlite> create table bar (a INTEGER, b AS (rowid + a));
> Error: no such column: rowid
> sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (_rowid_+a));
> Error: no such column: _rowid_
> sqlite> create table bar (id INTEGER PRIMARY KEY, a INTEGER, b AS
> (rowid + a));
> Error: no such column: rowid
> sqlite> create table bar (_rowid_ INTEGER PRIMARY KEY, a INTEGER,
> b as (_rowid_ + a));
> sqlite> insert into bar (a) values (1),(1),(1);
> sqlite> select * from bar;
> 1|1|2
> 2|1|3
> 3|1|4
>
> So a generated column may not depend on the IMPLICITLY named rowid
> (by whatever magic you want to use), but it may depend on an
> EXPLICIT rowid (or alias).  In other words, only explicitly named
> columns are permitted.  If you EXPLICITY named the rowid alias to
> be _rowid_ it works without problem.  Same rule applies to FOREIGN
> KEY contraints which may only depend on EXPLICITLY named columns,
> not the IMPLICIT rowid.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to
> Heaven says a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> 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: https://www.sqlite.org/draft/gencol.html Typo

Warren Young
In reply to this post by Simon Slavin-3
On Oct 29, 2019, at 7:20 AM, Simon Slavin <[hidden email]> wrote:
>
> <https://www.sqlite.org/draft/gencol.html>
>
> OMG.  Much welcomed feature.

Yes, I can see immediate use for this.

One question I had after reading the draft doc is whether an application-defined SQLITE_DETERMINISTIC function can be used to compute a generated column.  My immediate use case for this feature would require logic I’d struggle to define in SQL, but which we already have logic for in C++.  Indeed, I’d be using this feature to cache the results of that C++ code in the DB table, so it only needs to run when the source DB column changes.

>    ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS … STORED columns

The doc should explain why this restriction exists, given that SQLite does otherwise allow ALTER TABLE ADD COLUMN.

My first use of this feature will likely have me adding a STORED column to an existing table, so unless this restriction is lifted before I get around to using the new feature, I’ll be doing the old table migration dance.
_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

Simon Slavin-3
On 29 Oct 2019, at 10:16pm, Warren Young <[hidden email]> wrote:

> One question I had after reading the draft doc is whether an application-defined SQLITE_DETERMINISTIC function can be used to compute a generated column.

Hmm.  Well, I can see that any non-deterministic function would be a problem here.  How about applying the logic from DEFAULT values and CHECK constraints ?  Do they allow deterministic functions ?

I just tried to find documentation to answer that but failed.

>>   ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS … STORED columns
>
> The doc should explain why this restriction exists, given that SQLite does otherwise allow ALTER TABLE ADD COLUMN.

I believe that the developers don't want any ALTER TABLE command to have to rewrite all the data in the table.  I can see the reasoning.
_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

Keith Medcalf
In reply to this post by Warren Young

On Tuesday, 29 October, 2019 16:17, Warren Young <[hidden email]> wrote:

>On Oct 29, 2019, at 7:20 AM, Simon Slavin <[hidden email]> wrote:

>One question I had after reading the draft doc is whether an application-
>defined SQLITE_DETERMINISTIC function can be used to compute a generated
>column.  My immediate use case for this feature would require logic I’d
>struggle to define in SQL, but which we already have logic for in C++.
>Indeed, I’d be using this feature to cache the results of that C++ code
>in the DB table, so it only needs to run when the source DB column
>changes.

But of course.

sqlite> create table sintab(x real not null, sin as (sin(radians(x))) stored);
sqlite> insert into sintab select value from generate_series where start=0 and stop=90 and step=5;
sqlite> select * from sintab;
0.0|0.0
5.0|0.0871557427476582
10.0|0.17364817766693
15.0|0.258819045102521
20.0|0.342020143325669
25.0|0.422618261740699
30.0|0.5
35.0|0.573576436351046
40.0|0.642787609686539
45.0|0.707106781186548
50.0|0.766044443118978
55.0|0.819152044288992
60.0|0.866025403784439
65.0|0.90630778703665
70.0|0.939692620785908
75.0|0.965925826289068
80.0|0.984807753012208
85.0|0.996194698091746
90.0|1.0

>> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS …
>> STORED columns

> The doc should explain why this restriction exists, given that SQLite
> does otherwise allow ALTER TABLE ADD COLUMN.

For the same reason that you cannot add a column that does not have a default.  Adding a column merely adds the definition of the column, not the data for that column to each existing record of the database.  In order to be able to add a stored column, you must compute the value of that column for each record and update every record.

You can add a virtual column, however, since it is the same thing as adding a regular column with a default -- the default is merely the computation expression and the result is never stored.

However, I suppose it would theoretically be possible to add a generated stored column and have the value of the stored column computed on retrieval just like for a regular column that is added where the default is computed at retrieval time if the stored value does not exist -- and that the value would only become "stored" once the record were updated (just like how a regular added column is treated).

> My first use of this feature will likely have me adding a STORED column
> to an existing table, so unless this restriction is lifted before I get
> around to using the new feature, I’ll be doing the old table migration
> dance.

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



_______________________________________________
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: https://www.sqlite.org/draft/gencol.html Typo

J Decker
On Tue, Oct 29, 2019 at 5:08 PM Keith Medcalf <[hidden email]> wrote:

>
> On Tuesday, 29 October, 2019 16:17, Warren Young <[hidden email]>
> wrote:
>
> >On Oct 29, 2019, at 7:20 AM, Simon Slavin <[hidden email]> wrote:
>
> >One question I had after reading the draft doc is whether an application-
> >defined SQLITE_DETERMINISTIC function can be used to compute a generated
> >column.  My immediate use case for this feature would require logic I’d
> >struggle to define in SQL, but which we already have logic for in C++.
> >Indeed, I’d be using this feature to cache the results of that C++ code
> >in the DB table, so it only needs to run when the source DB column
> >changes.
>
> But of course.
>
> sqlite> create table sintab(x real not null, sin as (sin(radians(x)))
> stored);
> sqlite> insert into sintab select value from generate_series where start=0
> and stop=90 and step=5;
> sqlite> select * from sintab;
> 0.0|0.0
> 5.0|0.0871557427476582
> 10.0|0.17364817766693
> 15.0|0.258819045102521
> 20.0|0.342020143325669
> 25.0|0.422618261740699
> 30.0|0.5
> 35.0|0.573576436351046
> 40.0|0.642787609686539
> 45.0|0.707106781186548
> 50.0|0.766044443118978
> 55.0|0.819152044288992
> 60.0|0.866025403784439
> 65.0|0.90630778703665
> 70.0|0.939692620785908
> 75.0|0.965925826289068
> 80.0|0.984807753012208
> 85.0|0.996194698091746
> 90.0|1.0
>
> >> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS …
> >> STORED columns
>
> > The doc should explain why this restriction exists, given that SQLite
> > does otherwise allow ALTER TABLE ADD COLUMN.
>
> For the same reason that you cannot add a column that does not have a
> default.  Adding a column merely adds the definition of the column, not the
> data for that column to each existing record of the database.  In order to
> be able to add a stored column, you must compute the value of that column
> for each record and update every record.
>
> You can add a virtual column, however, since it is the same thing as
> adding a regular column with a default -- the default is merely the
> computation expression and the result is never stored.
>
> However, I suppose it would theoretically be possible to add a generated
> stored column and have the value of the stored column computed on retrieval
> just like for a regular column that is added where the default is computed
> at retrieval time if the stored value does not exist -- and that the value
> would only become "stored" once the record were updated (just like how a
> regular added column is treated).
>
I like the sounds of that... but I would consider going one step further
and using that in an INDEX for the table with a deterministic function...
it would be nice if the data was only stored in the index.

The application of this I was thinking of is, I have user_id's.  Each
service gets a computed value of that user_id that makes it different
between every service, and being able to reverse lookup from the computed
user_id to the real user.row would be nice.

But, I suppose indexes are updated on every insert, and a full scan of the
table when created?  So it wouldn't really be sparse... like not all users
use all services, so the computed IDs would exist for lots of relations
that can't happen.



>
> > My first use of this feature will likely have me adding a STORED column
> > to an existing table, so unless this restriction is lifted before I get
> > around to using the new feature, I’ll be doing the old table migration
> > dance.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> 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: https://www.sqlite.org/draft/gencol.html Typo

Darren Duncan
In reply to this post by Simon Slavin-3
On 2019-10-29 6:20 a.m., Simon Slavin wrote:
>> • Every table must have at least one non-generated column.
>
> I greatly admire this restriction.

 From a relational purist perspective that restriction is counter-productive.

But then, disallowing a table or a key/unique constraint from having exactly
zero columns is likewise counter-productive and SQL already does that.

Allowing zero-column primary key constraints is the most elegant way to restrict
a table to having not more than 1 row, useful for storing singleton data like
some application configuration settings for example.

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