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

[Britton Kerin]

I just want to note here that I'm uncomfortable with the docs on type
affinity in a general sort of way.  I've read that section several
times in the hope of feeling more comfortable, but so far I still
don't.  Unfortunately I can't say how I would improve them exactly, I
just know they leave me with an unhappy feeling of not knowing exactly
what's going on.  Perhaps someone with more knowledge could improve
them somehow.

Britton

On 11/23/17, Peter Halasz  wrote:
> Thank you. It looks like I was looking at the wrong documentation page.
>
> So I will reiterate and clarify my advice that the datatype documentation (
> https://sqlite.org/datatype3.html), within the section on "Determination Of
> Column Affinity", should more clearly state the fact that the affinity
> rules do not apply for the special "INTEGER PRIMARY KEY" type.
>
> Perhaps "INTEGER PRIMARY KEY" could be a special rule #0, as otherwise the
> impression is that BigInt and Integer should act the same in all
> circumstances, which I have learned now is clearly not the case.
>
> I do notice now there is a cryptic link to "INTEGER PRIMARY KEY
> " in the section 2, before
> type affinity is introduced, but it gives no reason for the reader to think
> it links to special rules about how type affinity may or may not work. i.e.
> it says "Any column in an SQLite version 3 database, except an INTEGER
> PRIMARY KEY column, may be used to store a value of any storage class."
>
> So I hope this documentation page can be made clearer for future devs.
>
> As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
> avoid rowid reuse, but I can avoid using it for the sake of optimization,
> so probably will.
>
> Thanks again.
> ___
> 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] Does journal_mode=DELETE writes uncommitted queries into DB?

[J. King]

Thanks for the thorough explanation, Rowan. I must say I learned something, 
myself!

On November 23, 2017 11:23:11 PM EST, Rowan Worth  wrote:
>On 24 November 2017 at 10:27, J. King  wrote:
>
>> The rollback journal is used to return the database to its previous
>state
>> in the case of a partial write, not complete writes which are
>interrupted.
>> As you didn't commit the transaction, no write occurred, never mind a
>> partial one, so the database remained in its initial state.
>>
>> Deleting a rollback journal is a bad idea. In this case it was
>harmless,
>> but normally it's an excellent way to corrupt your database.
>>
>> As to where uncommitted transactions live, I am not sure. I'll let
>someone
>> else answer that.
>>
>
>When a database page is modified during a transaction, the initial data
>is
>written to the rollback journal and the modified data is held in memory
>until COMMIT or the transaction memory cache is exceeded (see PRAGMA
>cache_size).
>
>If either of those happen, sqlite obtains an EXCLUSIVE lock on the
>database
>and starts writing the modified data (after making sure the rollback
>journal is properly synced to disk). At this point you have a partially
>committed transaction and the DB is not necessarily consistent (which
>is
>why it's done under an exclusive lock, and why deleting the rollback
>journal is a terrible idea as you said).
>
>In the case of COMMIT this is just a matter of I/O (syncing the DB and
>deleting the rollback journal) and should be fairly quick. But in the
>case
>of a cache spill, this partially committed state can be observed for
>much
>longer, depending on the size of the transaction.
>
>-Rowan
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does journal_mode=DELETE writes uncommitted queries into DB?

[Rowan Worth]

On 24 November 2017 at 10:27, J. King  wrote:

> The rollback journal is used to return the database to its previous state
> in the case of a partial write, not complete writes which are interrupted.
> As you didn't commit the transaction, no write occurred, never mind a
> partial one, so the database remained in its initial state.
>
> Deleting a rollback journal is a bad idea. In this case it was harmless,
> but normally it's an excellent way to corrupt your database.
>
> As to where uncommitted transactions live, I am not sure. I'll let someone
> else answer that.
>

When a database page is modified during a transaction, the initial data is
written to the rollback journal and the modified data is held in memory
until COMMIT or the transaction memory cache is exceeded (see PRAGMA
cache_size).

If either of those happen, sqlite obtains an EXCLUSIVE lock on the database
and starts writing the modified data (after making sure the rollback
journal is properly synced to disk). At this point you have a partially
committed transaction and the DB is not necessarily consistent (which is
why it's done under an exclusive lock, and why deleting the rollback
journal is a terrible idea as you said).

In the case of COMMIT this is just a matter of I/O (syncing the DB and
deleting the rollback journal) and should be fairly quick. But in the case
of a cache spill, this partially committed state can be observed for much
longer, depending on the size of the transaction.

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

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

[Peter Halasz]

Thank you. It looks like I was looking at the wrong documentation page.

So I will reiterate and clarify my advice that the datatype documentation (
https://sqlite.org/datatype3.html), within the section on "Determination Of
Column Affinity", should more clearly state the fact that the affinity
rules do not apply for the special "INTEGER PRIMARY KEY" type.

Perhaps "INTEGER PRIMARY KEY" could be a special rule #0, as otherwise the
impression is that BigInt and Integer should act the same in all
circumstances, which I have learned now is clearly not the case.

I do notice now there is a cryptic link to "INTEGER PRIMARY KEY
" in the section 2, before
type affinity is introduced, but it gives no reason for the reader to think
it links to special rules about how type affinity may or may not work. i.e.
it says "Any column in an SQLite version 3 database, except an INTEGER
PRIMARY KEY column, may be used to store a value of any storage class."

So I hope this documentation page can be made clearer for future devs.

As for whether I need to use AUTOINCREMENT, it seemed like a good idea to
avoid rowid reuse, but I can avoid using it for the sake of optimization,
so probably will.

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

Re: [sqlite] Does journal_mode=DELETE writes uncommitted queries into DB?

[林自均]

Hi King,

Thank you for the detailed explanation! Now I understand rollback journal a
bit more.

John Lin

J. King  於 2017年11月24日 週五 上午10:28寫道：

> The rollback journal is used to return the database to its previous state
> in the case of a partial write, not complete writes which are interrupted.
> As you didn't commit the transaction, no write occurred, never mind a
> partial one, so the database remained in its initial state.
>
> Deleting a rollback journal is a bad idea. In this case it was harmless,
> but normally it's an excellent way to corrupt your database.
>
> As to where uncommitted transactions live, I am not sure. I'll let someone
> else answer that.
>
> On November 23, 2017 9:13:20 PM EST, "林自均"  wrote:
> >Hi folks,
> >
> >When I was learning about rollback journal, I did the following tests:
> >
> >(in shell 1)
> >$ sqlite3 /tmp/db.sqlite
> >SQLite version 3.21.0 2017-10-24 18:55:49
> >Enter ".help" for usage hints.
> >sqlite> PRAGMA journal_mode;
> >delete
> >sqlite> CREATE TABLE bank (name STR, money INT);
> >sqlite> INSERT INTO bank VALUES ("john", 5566);
> >sqlite> BEGIN;
> >sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
> >sqlite>
> >
> >(then in shell 2)
> >$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
> >$ rm -f /tmp/db.sqlite-journal
> >$ sqlite3 /tmp/db.sqlite .dump
> >PRAGMA foreign_keys=OFF;
> >BEGIN TRANSACTION;
> >CREATE TABLE bank (name STR, money INT);
> >INSERT INTO bank VALUES('john',5566);
> >COMMIT;
> >
> >I was expecting that deleting the rollback journal would commit the
> >uncommitted transaction (i.e. increase money from 5566 to 5666).
> >However,
> >it didn't.
> >
> >I also noticed that the md5sum of db.sqlite are the same before the
> >UPDATE
> >query and after it, which means that the UPDATE query doesn't really
> >write
> >into db.sqlite. Does it only write into memory?
> >
> >Thanks for answering my questions.
> >
> >John Lin
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >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
> 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] Does journal_mode=DELETE writes uncommitted queries into DB?

[J. King]

The rollback journal is used to return the database to its previous state in 
the case of a partial write, not complete writes which are interrupted. As you 
didn't commit the transaction, no write occurred, never mind a partial one, so 
the database remained in its initial state. 

Deleting a rollback journal is a bad idea. In this case it was harmless, but 
normally it's an excellent way to corrupt your database. 

As to where uncommitted transactions live, I am not sure. I'll let someone else 
answer that. 

On November 23, 2017 9:13:20 PM EST, "林自均"  wrote:
>Hi folks,
>
>When I was learning about rollback journal, I did the following tests:
>
>(in shell 1)
>$ sqlite3 /tmp/db.sqlite
>SQLite version 3.21.0 2017-10-24 18:55:49
>Enter ".help" for usage hints.
>sqlite> PRAGMA journal_mode;
>delete
>sqlite> CREATE TABLE bank (name STR, money INT);
>sqlite> INSERT INTO bank VALUES ("john", 5566);
>sqlite> BEGIN;
>sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
>sqlite>
>
>(then in shell 2)
>$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
>$ rm -f /tmp/db.sqlite-journal
>$ sqlite3 /tmp/db.sqlite .dump
>PRAGMA foreign_keys=OFF;
>BEGIN TRANSACTION;
>CREATE TABLE bank (name STR, money INT);
>INSERT INTO bank VALUES('john',5566);
>COMMIT;
>
>I was expecting that deleting the rollback journal would commit the
>uncommitted transaction (i.e. increase money from 5566 to 5666).
>However,
>it didn't.
>
>I also noticed that the md5sum of db.sqlite are the same before the
>UPDATE
>query and after it, which means that the UPDATE query doesn't really
>write
>into db.sqlite. Does it only write into memory?
>
>Thanks for answering my questions.
>
>John Lin
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does journal_mode=DELETE writes uncommitted queries into DB?

[林自均]

Hi folks,

When I was learning about rollback journal, I did the following tests:

(in shell 1)
$ sqlite3 /tmp/db.sqlite
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode;
delete
sqlite> CREATE TABLE bank (name STR, money INT);
sqlite> INSERT INTO bank VALUES ("john", 5566);
sqlite> BEGIN;
sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
sqlite>

(then in shell 2)
$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
$ rm -f /tmp/db.sqlite-journal
$ sqlite3 /tmp/db.sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE bank (name STR, money INT);
INSERT INTO bank VALUES('john',5566);
COMMIT;

I was expecting that deleting the rollback journal would commit the
uncommitted transaction (i.e. increase money from 5566 to 5666). However,
it didn't.

I also noticed that the md5sum of db.sqlite are the same before the UPDATE
query and after it, which means that the UPDATE query doesn't really write
into db.sqlite. Does it only write into memory?

Thanks for answering my questions.

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

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

[Keith Medcalf]

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.

Declaring "bigint primary key" (ie, using an incorrect spelling) defines a 
column that IS NOT an explicit alias for the rowid.  It is merely an integer 
column, completely separate from the rowid, whcih you happen to want to be the 
primary key.  Since the AUTOINCREMENT only applies to the rowid, you must spell 
the declaration correctly if you wish to (a) alias the rowid and (b) apply the 
AUTOINCREMENT option to the rowid.

And no, "BIGINT PRIMARY KEY" is not an alias for "INTEGER PRIMARY KEY"

https://sqlite.org/autoinc.html
https://sqlite.org/datatype3.html
https://www.sqlite.org/lang_createtable.html see "ROWIDs and the INTEGER 
PRIMARY KEY"

Personally, I have never found a use for the AUTOINCREMENT option.  Why is it 
being used?

---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Peter Halasz
>Sent: Thursday, 23 November, 2017 17:14
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] "BIGINT" doesn't act like INTEGER on table creation
>[Bug]
>
>BIGINT has a "resulting affinity" of INTEGER (
>https://sqlite.org/datatype3.html) but cannot be used in its place in
>the
>following example:
>
>CREATE TABLE [FailTable] ( [id] BigInt PRIMARY KEY AUTOINCREMENT
>)
>
>...as it gives this error:
>
>AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>
>Manually replacing BIGINT with INTEGER leads to success:
>
>CREATE TABLE [OkTable] ( [id] Integer PRIMARY KEY AUTOINCREMENT )
>
>I believe BigInt should work here the same as Integer, as it does in
>other
>contexts. I feel like this is a bug that could be fixed in SQLite. Or
>otherwise you might wish to mention the odd exception of
>AUTOINCREMENT
>fields explicitly in the type documentation -- but it seems to me
>like this
>is more something that should be fixed in the code.
>
>Sorry if this is a duplicate, known or already patched bug. It was
>difficult to search the bug tracker.
>
>Thanks for taking the time to look into this.
>
>For background, how I came across this: Linq2db, a library for C#,
>generates bad SQL in this style and fails. Clearly that library needs
>to
>fix its SQL generator to work better with SQLite (and I've posted a
>bug
>report on their github). However, it also seems odd the SQL should
>produce
>an error at all. If BigInt and Integer were treated synonymously, as
>documented, It would be valid. So I felt the bug should be brought to
>the
>attention of the SQLite developers. Hopefully it is something that
>can be
>fixed.
>
>Cheers
>___
>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] Using computed column once in SELECT

[Keith Medcalf]

>BTW, is ‘now’ value locked during the query execution to avoid the
>possibility (however small) of two columns ending up with different
>age calculations (e.g., running during date crossover on someone’s
>birthday)?

By default, 'now' is step stable (that is, it will return the same value for 
all usage within the same step of a statement) -- ie, for each row.  

You can make it statement stable with a small patch to sqlite3VdbeExec and 
compiling with -DSQLITE_NOW_STABILITY_STMT.  This will cause the 
p->iCurrentTime to be reset ONLY when step 0 of the VDBE program is executed, 
rather than on each step.

  assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
  assert( p->bIsReader || p->readOnly!=0 );
#ifdef SQLITE_NOW_STABILITY_STMT/* INSERT */
  if (p->pc == 0)   /* INSERT */
#endif  /* INSERT */
  p->iCurrentTime = 0;

I believe that since the addition of the SLOCHG option the value of 'now' is 
statement stable by default (ie, it is deterministic for the purposes of 
indexes but not for the query planner), however I still have the above 
suspenders in place just to be sure it is statement stable.

Since the time is cached in the VDBE it can only be made statement stable (all 
steps in the same statement), not transaction stable (all statements in the 
same transaction).  You would have to write your own User-Defined-Function that 
attaches to the commit/rollback hooks to have the 'now' be transaction stable 
if that was required.





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

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

[Peter Halasz]

BIGINT has a "resulting affinity" of INTEGER (
https://sqlite.org/datatype3.html) but cannot be used in its place in the
following example:

CREATE TABLE [FailTable] ( [id] BigInt PRIMARY KEY AUTOINCREMENT )

...as it gives this error:

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

Manually replacing BIGINT with INTEGER leads to success:

CREATE TABLE [OkTable] ( [id] Integer PRIMARY KEY AUTOINCREMENT )

I believe BigInt should work here the same as Integer, as it does in other
contexts. I feel like this is a bug that could be fixed in SQLite. Or
otherwise you might wish to mention the odd exception of AUTOINCREMENT
fields explicitly in the type documentation -- but it seems to me like this
is more something that should be fixed in the code.

Sorry if this is a duplicate, known or already patched bug. It was
difficult to search the bug tracker.

Thanks for taking the time to look into this.

For background, how I came across this: Linq2db, a library for C#,
generates bad SQL in this style and fails. Clearly that library needs to
fix its SQL generator to work better with SQLite (and I've posted a bug
report on their github). However, it also seems odd the SQL should produce
an error at all. If BigInt and Integer were treated synonymously, as
documented, It would be valid. So I felt the bug should be brought to the
attention of the SQLite developers. Hopefully it is something that can be
fixed.

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

[sqlite] EXISTS optimisation?

[Constantin Emil MARINA]

Hi all,

I am wondering if in SQLITE the EXISTS clause is expanded and optimized 
in any way.


This is generated by the observation that 2 algebrically equivalent queries,

SELECT WHERE EXISTS ()

and

SELECT WHERE id IN (SELECT ...)

produce different execution plans and different performance, with WHERE 
id IN (SELECT ) looking properly optimized.


We could not find any reference to this in 
https://sqlite.org/optoverview.html


Thanks,
Dinu

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

Re: [sqlite] Web issue

[R Smith]

Indeed, all fixed now.

Thanks.


On 2017/11/24 1:41 AM, J. King wrote:

It seems to be fixed now. It's working for me now in the same environment in 
which it was not working earlier.

On November 23, 2017 4:54:56 PM EST, Simon Slavin  wrote:


On 23 Nov 2017, at 9:14pm, R Smith  wrote:


In searching the sqlite.org pages for a previous post, I tried to

search with this computed url:

http://www.sqlite.org/search?q=CTE

To which there was about 8 seconds wait time and then this response

returned:

Seems to work fine for me.  Can you try a different browser or device ?

Simon.
___
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] Web issue

[J. King]

It seems to be fixed now. It's working for me now in the same environment in 
which it was not working earlier. 

On November 23, 2017 4:54:56 PM EST, Simon Slavin  wrote:
>
>
>On 23 Nov 2017, at 9:14pm, R Smith  wrote:
>
>> In searching the sqlite.org pages for a previous post, I tried to
>search with this computed url:
>> 
>> http://www.sqlite.org/search?q=CTE
>> 
>> To which there was about 8 seconds wait time and then this response
>returned:
>
>Seems to work fine for me.  Can you try a different browser or device ?
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using computed column once in SELECT

[petern]

CTE's can give you the dose of syntactic sugar you're craving:
[No, you can reference other column definitions within the same SELECT
statement.]

WITH w_age_col AS (SELECT *,CAST((julianday('now')-julianday(dob))/365.25
AS INT)age FROM the_table)
SELECT *, age,87-age life_expectancy FROM w_age_col;




On Thu, Nov 23, 2017 at 2:17 PM, Tony Papadimitriou  wrote:

> I don’t know if this has come up before.
>
> Example:
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
>87-cast((julianday('now')-julianday(dob))/365.25 as int)
> life_expectancy
>
> (... assuming 87 year average life span)
>
> This works, but as you see the age calculation has to be repeated in every
> column that needs it (and there could many more).
>
> Why not be able to this instead?
>
> select cast((julianday('now')-julianday(dob))/365.25 as int) age,
>87-age life_expectancy
>
> Note that age is defined before it is referenced.
>
> Apparently, ‘age’ can be used in a subsequent join but not a subsequent
> column definition.
> Is there a technical limitation for this or simply an unimplemented
> feature?
>
> BTW, is ‘now’ value locked during the query execution to avoid the
> possibility (however small) of two columns ending up with different age
> calculations (e.g., running during date crossover on someone’s birthday)?
>
> Thanks.
> ___
> 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] Using computed column once in SELECT

[Tony Papadimitriou]

I don’t know if this has come up before.

Example:

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
   87-cast((julianday('now')-julianday(dob))/365.25 as int) life_expectancy

(... assuming 87 year average life span)

This works, but as you see the age calculation has to be repeated in every 
column that needs it (and there could many more).

Why not be able to this instead?

select cast((julianday('now')-julianday(dob))/365.25 as int) age,
   87-age life_expectancy

Note that age is defined before it is referenced.

Apparently, ‘age’ can be used in a subsequent join but not a subsequent column 
definition.
Is there a technical limitation for this or simply an unimplemented feature?

BTW, is ‘now’ value locked during the query execution to avoid the possibility 
(however small) of two columns ending up with different age calculations (e.g., 
running during date crossover on someone’s birthday)?

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

Re: [sqlite] Web issue

[Simon Slavin]

On 23 Nov 2017, at 9:14pm, R Smith  wrote:

> In searching the sqlite.org pages for a previous post, I tried to search with 
> this computed url:
> 
> http://www.sqlite.org/search?q=CTE
> 
> To which there was about 8 seconds wait time and then this response returned:

Seems to work fine for me.  Can you try a different browser or device ?

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

Re: [sqlite] Can I recursively concatenate strings?

[Shane Dev]

>
>
> PS: If you do like the SQlite features and CTEs (which is one of my
> favourite additions ever), I could post you the CTE example tutorials made
> to accompany an sqlite DB manager (which I made very long ago, after the
> introduction in 3.8 I think) - they have some nifty stuff, like splitting
> CSV data from a column, drawing graphs etc. (or you can just get it from
> sqlitespeed yourself - but it's only windows for now, sadly)
>
> Previously I used SQL for solely for routine data manipulation and
queries. Then I discovered recursive CTEs by accident while reading
wikipedia https://en.wikipedia.org/wiki/Turing_completeness#Examples
"Turing completeness in declarative SQL is implemented through recursive
common table expressions"
That piqued my interest. https://sqlite.org/lang_with.html became one of my
favourite sqlite pages. I would like to see your CTE example tutorials.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Web issue

[Stephen Chrzanowski]

I can reproduce with that URL, however, if I go to SQLite.org and hit
Search, then search for CTE, I get a successful result.  But, the URL I'm
given in the bar is different than what you posted.

http://www.sqlite.org/search?s=d=cte


On Thu, Nov 23, 2017 at 4:14 PM, R Smith  wrote:

> In searching the sqlite.org pages for a previous post, I tried to search
> with this computed url:
>
> http://www.sqlite.org/search?q=CTE
>
> To which there was about 8 seconds wait time and then this response
> returned:
>
> can't read "::A(s)": no such element in array
> while executing
> "subst {
> 

[sqlite] Web issue

[R Smith]

In searching the sqlite.org pages for a previous post, I tried to search 
with this computed url:


http://www.sqlite.org/search?q=CTE

To which there was about 8 seconds wait time and then this response 
returned:


can't read "::A(s)": no such element in array
while executing
"subst {

Re: [sqlite] Can I recursively concatenate strings?

[R Smith]

On 2017/11/23 10:15 PM, Shane Dev wrote:

Perfect! I guessed this could be achieved with a recursive CTE but I could
not find one that would produce my desired view. Your CTE is simply a table
of strings keyed by the length and then you join it with the stringlengths
table to create the final view. Thanks


It's a pleasure, but let me note that I simply made a CTE to do the job 
because that was the most trivial (to me anyway), I'm not 100% sure it's 
the most efficient way, but then I have this notion that the question 
was more out of interest than planning/designing a really big 
needing-to-be-amazingly-efficient system.


If I were to ever do it in a real system, I would probably compute one 
string of adequate length in a single-row CTE and just join that and use 
substr() to copy as many characters as needed from it. I believe that 
might be more efficient (and more scalable).



PS: If you do like the SQlite features and CTEs (which is one of my 
favourite additions ever), I could post you the CTE example tutorials 
made to accompany an sqlite DB manager (which I made very long ago, 
after the introduction in 3.8 I think) - they have some nifty stuff, 
like splitting CSV data from a column, drawing graphs etc. (or you can 
just get it from sqlitespeed yourself - but it's only windows for now, 
sadly)


There's also real great examples on the sqlite.org pages.



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

Re: [sqlite] Can I recursively concatenate strings?

[Shane Dev]

Perfect! I guessed this could be achieved with a recursive CTE but I could
not find one that would produce my desired view. Your CTE is simply a table
of strings keyed by the length and then you join it with the stringlengths
table to create the final view. Thanks

On 22 November 2017 at 23:55, R Smith  wrote:

>
> On 2017/11/22 11:56 PM, Shane Dev wrote:
>
>> Let's say I have a table of stringlengths -
>>
>> sqlite>select * from stringlengths;
>> length
>> 4
>> 1
>> 9
>> ...
>>
>> Can I create a view xstrings containing strings (for example of char 'x')
>> with the lengths specified in stringlengths?
>>
>
> Pretty easily:
>
>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>
>   -- Script Items: 5  Parameter Count: 0
>   -- 2017-11-23 00:53:19.542  |  [Info]   Script Initialized, Started
> executing...
>   -- 
> 
>
>
> CREATE TABLE SL(id INTEGER PRIMARY KEY, sLength INT);
>
> INSERT INTO SL(sLength) VALUES (4),(1),(9),(72),(5);
>
> SELECT * FROM SL;
>
>   --  id  | sLength
>   --  | ---
>   --   1  |4
>   --   2  |1
>   --   3  |9
>   --   4  |72
>   --   5  |5
>
>
> CREATE VIEW xstrings AS
> WITH SB(i,xs) AS (
>   SELECT 0, ''
>   UNION ALL
>   SELECT i+1, xs||'x' FROM SB WHERE i<=(SELECT MAX(sLength) FROM SL)
> )
> SELECT xs
>   FROM SL,SB
>  WHERE SB.i = SL.sLength
>  ORDER BY SL.id
> ;
>
> SELECT * FROM xstrings;
>
>
>   -- xs
>   -- 
> 
>   -- 
>   -- x
>   -- x
>   -- 
> 
>   -- x
>
>   --   Script Stats: Total Script Execution Time: 0d 00h 00m and
> 00.031s
>   -- Total Script Query Time: 0d 00h 00m and
> 00.001s
>   -- Total Database Rows Changed: 5
>   -- Total Virtual-Machine Steps: 2250
>   -- Last executed Item Index:5
>   -- Last Script Error:
>   -- 
> 
>
>
>
> ___
> 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] [EXTERNAL] help with EXPLAIN QUERY PLAN

[x]

Understand now. Thanks Lutz & Gunter.




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Thursday, November 23, 2017 4:40:16 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] help with EXPLAIN QUERY PLAN

COVERING INDEX means that all required fields for the query can be read from 
the index alone, without accessing the row itself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Donnerstag, 23. November 2017 17:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] help with EXPLAIN QUERY PLAN

Can anyone tell me why the detail column sometime states ‘USING INDEX ’ and 
other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only 
the first m columns of an n column index (m less than n) but I’ve also seen 
USING COVERING INDEX when the query was searching all of the columns in the 
index.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
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
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] [EXTERNAL] help with EXPLAIN QUERY PLAN

[Hick Gunter]

COVERING INDEX means that all required fields for the query can be read from 
the index alone, without accessing the row itself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Donnerstag, 23. November 2017 17:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] help with EXPLAIN QUERY PLAN

Can anyone tell me why the detail column sometime states ‘USING INDEX ’ and 
other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only 
the first m columns of an n column index (m less than n) but I’ve also seen 
USING COVERING INDEX when the query was searching all of the columns in the 
index.

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

Re: [sqlite] help with EXPLAIN QUERY PLAN

[Lutz Horn]

Ho,

Am 23.11.17 um 17:33 schrieb x:
> Can anyone tell me why the detail column sometime states ‘USING INDEX
> ’ and other time ‘USING COVERING INDEX ...’?

See https://sqlite.org/queryplanner.html#_covering_indices

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

[sqlite] help with EXPLAIN QUERY PLAN

[x]

Can anyone tell me why the detail column sometime states ‘USING INDEX ’ and 
other time ‘USING COVERING INDEX ...’?

At first I thought USING COVERING INDEX implied the search was searching only 
the first m columns of an n column index (m less than n) but I’ve also seen 
USING COVERING INDEX when the query was searching all of the columns in the 
index.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Gerry Snyder]

I hope that ways are found to keep the email list viable.

If not, my only request is that it should push the messages to me, rather
than making me pull them.

If I had to pull, I might have skipped the thread on inserting date in the
middle and updating the ID column--and I have learned a lot from all the
posts there.

Thank you,

Gerry Snyder (55 years of working with computers and still an ignoramus)

On Tue, Nov 21, 2017 at 7:30 AM, Richard Hipp  wrote:

> On 11/21/17, Paul Sanderson  wrote:
> > Coincidence!  I have just been in my gmail folder marking a load of
> SQLite
> > email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> 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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Kees Nuyt]

On Thu, 23 Nov 2017 08:45:25 +, Tim Streater
 wrote:

>On 22 Nov 2017, at 19:49, "Niall O'Reilly"  wrote:
>
>> On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:
>>
>>>  Please, not a forum. The email list is instant, dynamic, and convenient. I
>>> don't think checking into a forum to stay current with the brisk activity
>>> here is very practical or appealing.
>>
>> I agree with Bill on this.
>>
>> It seems to me that the idea of re-architecting such a useful communications
>> channel as this mailing list on account of a cluster of false positives 
>> raised
>> by a single provider's triage system would best be characterized as an 
>> example
>> of "the tail wagging the dog".
>
> Well quite. My advice to anyone whose ISP or gmail is
> doing unwanted spam filtering is to switch elsewhere.
>
> And there's no need for 'likes' and related nonsense on a technical list.

+1

I prefer mailing lists and usenet groups. With a proper
threading reader program I just press the spacebar to page
through a message and to the next unread message.
No other medium allows me to consume this kind of info at such a
high speed without getting RSI problems.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Tim Streater]

On 22 Nov 2017, at 19:49, "Niall O'Reilly"  wrote:

> On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:
>
>>  Please, not a forum. The email list is instant, dynamic, and convenient. I
>> don't think checking into a forum to stay current with the brisk activity
>> here is very practical or appealing.
>
> I agree with Bill on this.
>
> It seems to me that the idea of re-architecting such a useful communications
> channel as this mailing list on account of a cluster of false positives raised
> by a single provider's triage system would best be characterized as an example
> of "the tail wagging the dog".

Well quite. My advice to anyone whose ISP or gmail is doing unwanted spam 
filtering is to switch elsewhere.

And there's no need for 'likes' and related nonsense on a technical list.


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