Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Darren Duncan]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Generated columns in primary keys, indexes, and constraints

[Darren Duncan]

Per https://www.sqlite.org/draft/gencol.html thank you very much for adding this 
generated columns feature!


I consider this to be a very powerful foundation for a variety of useful 
features, in particular because it empowers generalized solutions to several 
classes of problem.


In particular, this provides a generalized foundation for arbitrary unique 
constraints and indexes.


Some SQL DBMSs have complicated syntax for declaring indexes or keys, say for 
example case-insensitive uniqueness or indexing only a prefix of a character 
string or various other things.


When generalized generated columns are supported as SQLite is going to do, the 
syntax for key or constraint declaration can simply reference a column as a 
whole and use the entire pristine column value; by default this means key or 
index in a case-sensitive etc manner, but if there is a generated column that is 
a lowercased version of a regular text field, one can put the unique constraint 
on that column instead to get the case-insensitive uniqueness without 
complicating the key/index declaration syntax to do so.


There is a lot of power here from such a basic foundation.

Related to this, I strongly encourage the SQLite developers to relax the 
constraint on generated columns being used in a PRIMARY KEY sooner rather than 
later.


Ideally a PRIMARY KEY would have no restrictions that a UNIQUE constraint lacks; 
they are conceptually the same thing, a subset of the columns of the row that 
uniquely identifies the row in the table, and designating one as PRIMARY is 
completely arbitrary in that sense.


The benefits I ascribed to generated columns as a foundation would be greatly 
weakened if a PRIMARY KEY can't use them.


Thank you.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Simon Slavin]

On 30 Oct 2019, at 3:58am, Keith Medcalf  wrote:

> Before you change anything, I think that is incorrect for the various 
> datetime functions.  I think they SHOULD be permitted in CHECK constraints 
> and in generated always columns, whether those are stored or virtual, whether 
> or not parameters are given since they are constant. They should not be 
> permitted in indexes however unless they are pure (deterministic).

But can SQLite tell the difference at that stage ?  For instance,

julianday('2019-10-30') is deterministic
   julianday('now') is non-deteerministic

Using 'now' as an argument gives different results for the same parameter 
values.  Worse still, the 'now' may not be explicit, you may have a table 
column with '2019-10-30' in one row and 'now' in another.

SQLite cannot make the distinction.  SQLITE_DETERMINISTIC applies to all values 
of the parameters of a function.  And since at least one value can yield 
different results, julianday() has to be marked as non-deterministic.

The standard way to handle this is that instead understanding the string 'now' 
you have a function now() marked as non-deterministic.  Then julianday() can be 
marked as deterministic.  But it's too late for that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Simon Slavin]

On 29 Oct 2019, at 10:16pm, Warren Young  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[J Decker]

On Tue, Oct 29, 2019 at 5:08 PM Keith Medcalf  wrote:

>
> On Tuesday, 29 October, 2019 16:17, Warren Young 
> wrote:
>
> >On Oct 29, 2019, at 7:20 AM, Simon Slavin  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.76603118978
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT/UPDATE generated columns?

[Keith Medcalf]

On Tuesday, 29 October, 2019 20:48, no...@null.net wrote:

>The gencol.html document does not describe the result of attempting to
>INSERT INTO or UPDATE a generated column. Does this raise an error (my
>preference) or is it simply ignored? Could the behaviour be added to
>the documentation?

sqlite> create table t(data, updated as (current_timestamp) stored);
sqlite> insert into t (data, updated) values (1,45);
Error: cannot INSERT into generated column "updated"
sqlite> insert into t (data) values (1);
sqlite> update t set updated=4;
Error: cannot UPDATE generated column "updated"

It thows an error during the prepare ...

-- 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Keith Medcalf]

Before you change anything, I think that is incorrect for the various datetime 
functions.  I think they SHOULD be permitted in CHECK constraints and in 
generated always columns, whether those are stored or virtual, whether or not 
parameters are given since they are constant.  They should not be permitted in 
indexes however unless they are pure (deterministic).

Your example in the ticket is (almost) perfectly reasonable.  If one were to 
have:

create table t (a real check (a < julianday());

ensures that at the time of record insertion or update that the value of "a" is 
not "in the future".  Similarly the construct:

create table t(data, updated generated always as (julianday()) stored);

ensures that the "updated" column always has the julianday the record was 
inserted or updated and cannot be changed by the user (ie, you cannot "UPDATE t 
SET updated=47" or "INSERT INTO t (data, updated) VALUES (1, 45)" as you cannot 
set/update the values of generated always columns).

Similarly, you might define a table thusly:

create table t(basedate text, days integer, deadline generated always as 
(datetime(basedate, '+' || days || ' days'));

so that the deadline is always calculated the same way no matter what ... it 
doesn't really matter if it is stored or virtual.

-- 
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  On
>Behalf Of Richard Hipp
>Sent: Tuesday, 29 October, 2019 19:48
>To: SQLite mailing list 
>Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have
>SQLITE_FUNC_CONSTANT ?
>
>On 10/29/19, Keith Medcalf  wrote:
>> Can the
>> SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions
>since the
>> value is constant during the running of a single statement?
>
>No.  The functions used in generated tables must be pure.  They must
>always give the same output given the same input.
>
>datetime() is this way, as long as it's input does not use modifiers
>like "now", or "localtime", or "utc".  And for that reason, datetime()
>is marked as constant.  But it throws an error if you use it in a way
>that gives a non-deterministic result.
>
>Or, at least it is suppose to.  I just tried it and that mechanism is
>not working correctly for generated columns, which is a bug.
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] INSERT/UPDATE generated columns?

[nomad]

The gencol.html document does not describe the result of attempting to
INSERT INTO or UPDATE a generated column. Does this raise an error (my
preference) or is it simply ignored? Could the behaviour be added to
the documentation?

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

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Richard Hipp]

On 10/29/19, Richard Hipp  wrote:
>
> Or, at least it is suppose to.  I just tried it and that mechanism is
> not working correctly for generated columns, which is a bug.
>

Ticket: 
https://www.sqlite.org/src/tktview/830277d9db6c3ba10df1c79c6c2be58323553240

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

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Richard Hipp]

On 10/29/19, Keith Medcalf  wrote:
> Can the
> SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions since the
> value is constant during the running of a single statement?

No.  The functions used in generated tables must be pure.  They must
always give the same output given the same input.

datetime() is this way, as long as it's input does not use modifiers
like "now", or "localtime", or "utc".  And for that reason, datetime()
is marked as constant.  But it throws an error if you use it in a way
that gives a non-deterministic result.

Or, at least it is suppose to.  I just tried it and that mechanism is
not working correctly for generated columns, which is a bug.

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

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Richard Hipp]

On 10/29/19, Keith Medcalf  wrote:
>>sqlite3
> SQLite version 3.31.0 2019-10-29 16:18:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table x(data, createdon default (current_timestamp),
> updatedon as (current_timestamp) stored);
> Error: non-deterministic functions prohibited in generated columns
> sqlite> create table x(data, createdon default (current_timestamp),
> updatedon as (datetime()) stored);
> sqlite> insert into x (data) values ('data 1');
> sqlite> select * from x;
> data 1|2019-10-30 00:45:49|2019-10-30 00:45:49
> sqlite> update x set data='data 2' where data='data 1';
> sqlite> select * from x;
> data 2|2019-10-30 00:45:49|2019-10-30 00:46:32
>
> The datetime() function has SQLITE_SLOCHNG and SQLITE_FUNC_CONSTANT, but the
> CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE only have SQLITE_SLOCHNG.
>
> This means that you can use DATETIME() in a generate always ... stored but
> not CURRENT_TIMESTAMP, even though both produce the same result.  Can the
> SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions since the
> value is constant during the running of a single statement?
>
> --
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Keith Medcalf]

>sqlite3
SQLite version 3.31.0 2019-10-29 16:18:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(data, createdon default (current_timestamp), updatedon 
as (current_timestamp) stored);
Error: non-deterministic functions prohibited in generated columns
sqlite> create table x(data, createdon default (current_timestamp), updatedon 
as (datetime()) stored);
sqlite> insert into x (data) values ('data 1');
sqlite> select * from x;
data 1|2019-10-30 00:45:49|2019-10-30 00:45:49
sqlite> update x set data='data 2' where data='data 1';
sqlite> select * from x;
data 2|2019-10-30 00:45:49|2019-10-30 00:46:32

The datetime() function has SQLITE_SLOCHNG and SQLITE_FUNC_CONSTANT, but the 
CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE only have SQLITE_SLOCHNG.

This means that you can use DATETIME() in a generate always ... stored but not 
CURRENT_TIMESTAMP, even though both produce the same result.  Can the 
SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions since the 
value is constant during the running of a single statement?

-- 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Keith Medcalf]

On Tuesday, 29 October, 2019 16:17, Warren Young  wrote:

>On Oct 29, 2019, at 7:20 AM, Simon Slavin  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.76603118978
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Warren Young]

On Oct 29, 2019, at 7:20 AM, Simon Slavin  wrote:
> 
> 
> 
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite - AIX memory fault coredupm when using .output command [WARNING! - EXTERNAL]

[Warren Young]

On Oct 29, 2019, at 2:56 PM, Dawson, Jeff G  wrote:
> 
> SQLite version 3.7.14.1 2012-10-04 19:37:12

I infer that you’re migrating a legacy system.  There are two good alternatives 
to your current method that should avoid the symptom entirely:

1. Build a current version of SQLite for the old AIX system and try the dump 
with that instead.  It should read that old file just fine.

2. Copy the SQLite DB to the new system and work with it there.  If you’re 
moving away from both AIX and SQLite, you can do the dump on the modern system 
with a current version of SQLite, which should work much better.  If you’re 
sticking with SQLite on the new system, you don’t need the dump at all; just 
use the copied file on the new system.

If it happens that either path also crashes, you’re far more likely to get a 
fix for it than for this greatly outdated version of SQLite.

If you get another core dump with either alternative conversion path, please 
include the backtrace.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
> On Behalf Of Keith Medcalf
> Sent: Tuesday, October 29, 2019 2:29 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
> 
> 
> On Tuesday, 29 October, 2019 12:25, Doug 
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Keith Medcalf]

On Tuesday, 29 October, 2019 12:25, Doug  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite - AIX memory fault coredupm when using .output command [WARNING! - EXTERNAL]

[Dawson, Jeff G]

Hello,

We are running sqlite version below on AX 7.2 TL 1.  We have a database around 
430MB and was trying to export the contents to a file shown below, done this 
with other databases but it seems there is some type of limitation possibly 
with the version of sqlite we are running?  We did open a ticket with IBM which 
looked at the core dump and said it was on the application side, sqlite.   We 
ended up doing 3 select statements using a unique number to pull files of about 
500k of rows to finally clear the table.  Wanted to see if anyone has come 
across this type of issue before.

SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from EpicADT;
1457871
sqlite> .output finalAllHospTable.txt
sqlite> select * from EpicADT ORDER BY acctNum;
Memory fault(coredump)


iscax_testcis02@hci (/cistest/cis6.2/integrator/sqlLiteDbs/JGD)
/>ls -altr
total 1378760
-rw-r--r--1 hci  staff 436788224 Oct 29 15:46 EpicADT_PROD1TBL.db
drwxrwxr-x   20 hci  staff  4096 Oct 29 15:48 ..
-rw-rw-r--1 hci  staff 0 Oct 29 15:48 finalAllHospTable.txt
drwxrwxr-x2 hci  staff   256 Oct 29 15:49 .
-rw-rw-r--1 hci  staff 269121503 Oct 29 15:49 core


Table schema

sqlite> .schema
CREATE TABLE EpicADT (msgDT TEXT, msgID TEXT, hosp TEXT, FirstName TEXT, MI 
TEXT, LastName TEXT, DOB TEXT, Gender TEXT, pt


AIX Power 8 + server 16 cpu's running

/>ulimit -a
time(seconds)unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes)unlimited
memory(kbytes)   unlimited
coredump(blocks) unlimited
nofiles(descriptors) 2
threads(per process) unlimited
processes(per user)  unlimited

Thanks,
Jeff

WARNING! – This email is from an external sender.  Do not click links or open 
attachments unless you know the sender.  Never give out your username and 
password.


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

[sqlite] sqldiff does not report foreign key constraint differences

[Anthony DeRobertis]

The "Limitations" section of the sqldiff documentation mentions it 
doesn't handle triggers or views, but nothing about constraints. So I 
expected this to report something (note how in db1, t3.ref refences t1; 
in db2, it references t2):


sqlite> .open db1
sqlite> pragma foreign_keys = 1;
sqlite> create table t1 (a integer not null primary key);
sqlite> create table t2 (b integer not null primary key);
sqlite> create table t3 (c integer not null primary key, ref integer not null 
references t1);
sqlite> .open db2
sqlite> pragma foreign_keys = 1;
sqlite> create table t1 (a integer not null primary key);
sqlite> create table t2 (b integer not null primary key);
sqlite> create table t3 (c integer not null primary key, ref integer not null 
references t2);
sqlite> .quit
$ sqldiff db1 db2
$

I expected some output there.

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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Allen, Marc]

Sorry for top posting.. I'm way too lazy to figure out how to make Outlook play 
nicely.

You say:

FROM R LEFT OUTER JOIN S
ON R.key = S.key AND R.col = 'foo'

Here, 

AND R.col = 'foo'

while valid syntax (unfortunately), has no effect.  

However, this isn't true.  While it doesn't constrain R, it does constrain the 
join.  This will still return all rows of R, but all S columns will be NULL for 
any row where R.col is not 'foo'.

This is actually useful, especially for efficiency.  This allows the database 
to not even bother doing the lookup on S if R.col != 'foo' and still return 
that R record.

Marc


Marc L. Allen | Software Engineer IV, PDI Marketing Cloud Solutions
2551 Eltham Ave. Suite N, Norfolk, VA 23513
Office  757.853.3000 x6015
Direct  757.605.6015
mlal...@pdisoftware.com 
www.pdisoftware.com 
 

On 10/28/19, 1:20 PM, "sqlite-users on behalf of James K. Lowden" 
 wrote:

On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher  wrote:

> My colleagues and I are trying to understand the role of ON
> constraints vs WHERE clauses in JOINs. 

ON applies before JOIN.  WHERE applies after.  

That's a loose interpretation, but IMO it's nevertheless a useful way
to think about it.  

It *mostly* doesn't matter.  It was invented for outer joins.  

In SQL-89, there was only the inner join. There was no JOIN keyword.
You listed all the tables in FROM: 

FROM R, S
WHERE R.key = S.key

and all was good with the world.  

To support outer joins, vendors invented various syntaxes.  To unifiy
them, SQL-92 defined JOIN and ON: 

FROM R JOIN S
ON R.key = S.key

with that syntax, JOIN could be modifed to be LEFT or RIGHT outer, and
the inner table could be further restricted:  

FROM R LEFT OUTER JOIN S
ON R.key = S.key AND S.col = 'foo'

That means: all rows from R joined to rows in S for which col = 'foo'.
If no such S rows exist, every result row will be NULL for the S
columns.  Compare with 

FROM R LEFT OUTER JOIN S
ON R.key = S.key 
WHERE S.col = 'foo'

Here, R and S are joined, and the WHERE test is applied to the joined
result.  If no such S rows exist, there will be no result rows at all.

When wrestling with this topic, it's useful to remember that ON doesn't
constrain the outer table: 

FROM R LEFT OUTER JOIN S
ON R.key = S.key AND R.col = 'foo'

Here, 

AND R.col = 'foo'

while valid syntax (unfortunately), has no effect.  R is the outer
table, and all rows of the outer table are always produced, no matter
what ON says about it.  Perhaps the above should mean: 

FROM (select * from R WHERE R.col = 'foo') as R 
LEFT OUTER JOIN S 
ON R.key = S.key 

but it does not.  Perfection remains, as ever, elusive.  

--jkl


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


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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Dominique Pellé]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 29, 2019 6:21 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] https://www.sqlite.org/draft/gencol.html
> Typo
> 
> 
> 
> 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 
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] https://www.sqlite.org/draft/gencol.html Typo

[Simon Slavin]

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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility

[Dominique Devienne]

On Tue, Oct 29, 2019 at 8:38 AM Thomas Kurz  wrote:

> We recently had a discussion about date/time support, but also other
> suggestions, which sooner or later end up at the point "cannot be done,
> would break backward compatibility". (See also: "Backward compatibility vs.
> new features (was: Re: dates, times and R)")
>
> I'm always curious and monitoring trunk development, and now I have read
> in the draft release notes for 3.31:
>
> > If an earlier version of SQLite attempts to read a database file that
> contains a generated column in its schema, then that earlier version will
> perceive the generated column syntax as an error and will report that the
> database schema is corrupt.
>
> ...which leads me to some confusion. So there actually *are* features
> breaking compability? Wouldn't that be a chance for full date/time support
> as well?
>

To complement Graham's answer, I see 3 different levels of backward
compatibility:

1) file format: https://www.sqlite.org/fileformat.html
2) SQLite C API: https://www.sqlite.org/capi3ref.html
3) SQL language: https://www.sqlite.org/lang.html (including the built-in
SQL functions)

Anything that breaks #1 or #2 is dead on arrival basically.
SQLite4 was a brief attempt at #1, but it fizzled out.

OTOH, #3 keeps adding new features over time, because as long as you don't
use those new features, older versions of SQLite can read/process the DB
files just fine.
This is more "forward-compatibility" in a sense, when older SQLite versions
can or cannot read DB files written using newer versions of SQLite.
While "backward-compatibility" is the latest version of SQLite reading
older SQLite's DB files (mostly about #1 and #2).

Your DB files are as forward-compatible as you choose them to be, depending
on the new SQL features you use or not.
I didn't follow the date/time thread closely enough to see where the
proposed changes fall in the above categorization.
I hope this helps. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility

[Graham Holden]

Tuesday, October 29, 2019, 7:37:40 AM, Thomas Kurz  
wrote:

>> If an earlier version of SQLite attempts to read a database file
>> that contains a generated column in its schema, then that earlier
>> version will perceive the generated column syntax as an error and
>> will report that the database schema is corrupt.   

> ...which leads me to some confusion. So there actually *are*
> features breaking compability? Wouldn't that be a chance for full
> date/time support as well?

There are different types of compatibility (that probably have proper
names). The main one that the SQLite devs (seem to) want to avoid is
the case where someone has an existing app/program that uses SQLite.
They upgrade their copy of SQLite to the latest, and that program
suddenly stops working because of a change in SQLite. This type of
"breaking compatibility" is very rare (the change in behaviour of
ALTER TABLE RENAME, which now fixes references to a renamed table,
is one of the few that comes to mind).

The situaton described above is different: to get that situation, a
database has to have been deliberately created/modified in a LATER
version of SQLite and then handed over to a program using an earlier
of SQLite to process.  Note: simply _creating_ the database with a
later version is [almost always] safe (so long as you only use
features the older version understands): the problem comes if you
deliberately make use of a feature (e.g. "generated columns") that
only the later version supports. This is sort-of the equivalent of
asking Microsoft Word 2.0 to open a file created with Word 2007.

Graham


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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[x]

If I remember correctly I found out by mistake that the ON doesn’t even have to 
relate to the JOIN it follows as long as it fits in with the WHERE. E.g.



SELECT * FROM Tbl1 INNER JOIN Tbl2 ON Tbl3.Col=Tbl1.Col INNER JOIN Tbl3 ON 
Tbl2.Col=Tbl1.Col;



Something like that.






From: sqlite-users  on behalf of 
James K. Lowden 
Sent: Monday, October 28, 2019 10:32:21 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf"  wrote:

> CREATE TABLE t1 (a, b);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(2,3);
> INSERT INTO t1 VALUES(1,3);
> CREATE TABLE t2 (b, c);
> INSERT INTO t2 VALUES(2,3);
> INSERT INTO t2 VALUES(3,4);
> select * from t1 left join t2 on t1.b == t2.b and t1.a == 1;
> 1|2|2|3
> 2|3||
> 1|3|3|4

Thank you, Keith.  What you're saying is that when ON applies to the
outer table, it still constrains the JOIN.  The tuple

2 3 3 4

would have been produced, but for the ON restriction

t.a = 1

> This sort of thing is useful

I don't doubt it.  I can't remember ever having written a query like
that (not knowing that's what it would do).  I would probably have
expressed the giraffe-neck problem as

select * from t1
left join ( select * from t2 where b <> 1 ) as t2
on t1.b = t2.b

because usually, in domains I worked with, the constraint on the inner
table wasn't a function of the join criteria, but of some type or group
or catagory column, or date.

--jkl

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

[sqlite] Backward compatibility

[Thomas Kurz]

We recently had a discussion about date/time support, but also other 
suggestions, which sooner or later end up at the point "cannot be done, would 
break backward compatibility". (See also: "Backward compatibility vs. new 
features (was: Re: dates, times and R)")

I'm always curious and monitoring trunk development, and now I have read in the 
draft release notes for 3.31:

> If an earlier version of SQLite attempts to read a database file that 
> contains a generated column in its schema, then that earlier version will 
> perceive the generated column syntax as an error and will report that the 
> database schema is corrupt.

...which leads me to some confusion. So there actually *are* features breaking 
compability? Wouldn't that be a chance for full date/time support as well?

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