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

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

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

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

[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

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

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 >

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

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

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

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

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

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

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

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

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

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

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

[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

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

Re: [sqlite] [EXTERNAL] help with EXPLAIN QUERY PLAN

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

Re: [sqlite] help with EXPLAIN QUERY PLAN

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

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

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

Re: [sqlite] Can I recursively concatenate strings?

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

Re: [sqlite] Web issue

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

Re: [sqlite] Can I recursively concatenate strings?

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

Re: [sqlite] Web issue

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

[sqlite] Using computed column once in SELECT

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

Re: [sqlite] Using computed column once in SELECT

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

Re: [sqlite] Can I recursively concatenate strings?

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

[sqlite] Web issue

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] Web issue

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

[sqlite] EXISTS optimisation?

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,

Re: [sqlite] Web issue

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

[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

Re: [sqlite] Using computed column once in SELECT

>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