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

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

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

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

[sqlite] Backward compatibility

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

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

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.

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

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.

Re: [sqlite] Backward compatibility

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: [sqlite] Backward compatibility

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

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

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

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 >

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

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:

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

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

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

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

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] INSERT/UPDATE generated columns?

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] sqlite - AIX memory fault coredupm when using .output command [WARNING! - EXTERNAL]

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

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

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,

[sqlite] sqldiff does not report foreign key constraint differences

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;

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

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

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

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

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

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

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 >

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

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

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

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

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

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

Re: [sqlite] INSERT/UPDATE generated columns?

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?

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

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

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

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.

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

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