Re: [sqlite] EXISTS optimisation?

Quick note to self and others: IN() and EXISTS() in all RDB's I know of are the uncle noone mentions; that is to say, they have different compare semantics than JOIN so the naive strategy is to evaluate them as dependent subqueries, not correlated ones, which would be consistent with the behavior

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

Please forgive the off-topicedness of this message, but I would like to second that this mailing list is incredibly edifying, and I would also like thank everyone who has both answered and asked questions in the time I've been subscribed. I've stumbled across answers here that I never thought

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

That's terrific! Looking forward to it being in a full release. Hopefully the next tool developer to come along won't need to write a custom SQL parser, get lost in irrelevant datatype documentation, sift through op codes from an unnestable "explain" statement, compile a custom SQLite C

Re: [sqlite] Datetime / Transactions / CLI

Although I don't do many long length transactions for date and times, I kind of like the idea of having the control of over how the library allows you to chose which way the dates and times are going to work. Default to the current model, of course, to allow for backward compatibility, but, either

Re: [sqlite] Datetime / Transactions / CLI

On Tue Nov 28, 2017 at 10:34:03AM -0700, Keith Medcalf wrote: > > Datetime functions (that is, what constitutes "now") was, by default, > step-stable. The value is cached within the VDBE (statement object) > on its first use per-step and retains the same value until the VDBE > code yields a row.

Re: [sqlite] Foreign key help

Good stuff Keith. One to archive. From: sqlite-users on behalf of Keith Medcalf Sent: Tuesday, November 28, 2017 4:02:35 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key help And

Re: [sqlite] Datetime / Transactions / CLI

On 28 Nov 2017, at 5:34pm, Keith Medcalf wrote: > This would indicate that "now" has statement-stability and not > transaction-stability, which matches with my observations. You’re right, I was wrong. Thanks for the correction. Simon.

Re: [sqlite] Datetime / Transactions / CLI

Datetime functions (that is, what constitutes "now") was, by default, step-stable. The value is cached within the VDBE (statement object) on its first use per-step and retains the same value until the VDBE code yields a row. Re-entry on the next step used to reset "now". This was changed to

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

Hey Peter. Good on you. Lobbying for sensible fixes to the public API does pay off sometimes. There's a new branch in the timeline. [Watch for a merge here: https://www.sqlite.org/src/timeline?n=50 ] https://www.sqlite.org/src/info/2494132a2b1221a4 ** PRAGMA table_ipk() ** ** If has

Re: [sqlite] Foreign key help

And of course in the command line shell you can and should use .lint fkey-indexes to let you know if you are missing any indexes required for efficient foreign-key enforcement operations. It will report missing indexes on the PARENT (table/columns referred to) and on CHILDREN (tables/columns

Re: [sqlite] Datetime / Transactions / CLI

On 28 Nov 2017, at 3:50pm, no...@null.net wrote: > Can someone point me to the documentation for behaviour of date/time > functions inside transactions? In my code it appears time is frozen. Correct. The value of 'now' is frozen at the time a transaction begins. This is to ensure that if

Re: [sqlite] Foreign key help

Thanks David / Simon. From: sqlite-users on behalf of Simon Slavin Sent: Tuesday, November 28, 2017 3:36:51 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key help On 28 Nov 2017,

[sqlite] Datetime / Transactions / CLI

Can someone point me to the documentation for behaviour of date/time functions inside transactions? In my code it appears time is frozen. The command-line client on the other hand doesn't behave the same way: sqlite> begin immediate; sqlite> select julianday(); julianday()

Re: [sqlite] Foreign key help

Leaves what's there alone and just starts enforcing from when you turn it on. It'll only go actively looking for issues if you do a pragma foreign_key_check; sqlite> pragma foreign_keys = off; sqlite> create table parent (id integer primary key); sqlite> create table child (id integer primary

Re: [sqlite] Foreign key help

On 28 Nov 2017, at 3:26pm, x wrote: > If I have foreign keys in place but always have foreign_keys = OFF then one > day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly > check all foreign keys and report / delete violations or does it leave >

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

With an integer primary key, not just any primary key. Probably something to do with the deterministic flag as well. Looks like in checking if it's gonna be a good integer for a rowid it calls it twice. Below you can see where random() gets called twice, so what the before trigger inserts into

[sqlite] Foreign key help

If I have foreign keys in place but always have foreign_keys = OFF then one day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly check all foreign keys and report / delete violations or does it leave everything as is and just enforce foreign keys from that point on?

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

I suspect that the udf() function is called once to build the record and once again to build the parameter list for the trigger program. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von no...@null.net Gesendet: Dienstag,

Re: [sqlite] Possible User Defined Function (UDF) Bug?

Here is a trimmed-down test case for my issue: CREATE TABLE d ( id INTEGER NOT NULL PRIMARY KEY ); CREATE TRIGGER bi_d BEFORE INSERT ON d FOR EACH ROW BEGIN select 1; END;

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

Along with the table_info pragma you could also look at the output of an explain statement to see how it gets the value. When it's a rowid table it uses a "Rowid" opcode, vs using a "Column" opcode for a non-rowid table. sqlite> create table rowidPK ...> ( ...> x integer, ...> y

Re: [sqlite] EXISTS optimisation?

Dinu wrote: > I triple-checked it and it is indeed generating different execution plans Probably different indexes? Are these actual tables? >> select * from t where exists (select * from lookup where x = t.x); >> select * from t where x in (select x from lookup); >> select t.* from t join

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>What about changing the remaining inner join to left join >Select BaseTbl.RowID >from BaseTbl >left join Tbl_2 on Tbl2.Y = BaseTbl.Y >where BaseTbl.Col=? >and see if the SQLiter optimizer now leaves Tbl_2 out from the query >plan. It will only do that if it is not a 1-to-n join. If Tbl_2

Re: [sqlite] Recursive aggregate query?

On Tue Nov 28, 2017 at 10:13:56AM +0100, no...@null.net wrote: > I don't understand the error message generated by the following > schema/query: > > CREATE TABLE x( > id integer > ); > ... > > WITH > x > AS Woops - I just realized the CTE uses a name already

[sqlite] Recursive aggregate query?

I don't understand the error message generated by the following schema/query: CREATE TABLE x( id integer ); CREATE TABLE y( id integer ); CREATE TABLE y_sequence ( seq INTEGER PRIMARY KEY AUTOINCREMENT ); WITH x AS

Re: [sqlite] sites inaccessible

On Sat, Oct 29, 2016 at 8:09 AM, Dan Kennedy wrote: > On 10/29/2016 12:28 PM, jungle Boogie wrote: > >> Hi Dr. Hipp, >> >> Probably a low concern for you at 1:30am your time but I can't connect >> to fossil-scm.org or sqlite.org over port 80. >> >> $ curl

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

Hi Simon, thanks for the hint. I won't do that. -- Stefan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXISTS optimisation?

Thanks Clemens, I triple-checked it and it is indeed generating different execution plans, with the queries being absolutely equivalent. I will try to produce a minimal test case (right now the query where this occurs is a 100 lines long monster). However, I am a bit confused by the examples

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

Peter Halasz wrote: > However, it's quite clear to me, as only a user of just a few of these > tools and libraries, that they would be improved greatly if they had a > direct way of querying which field in a SQLite table was acting as an alias > of ROW ID. The rowid column must have the type

Re: [sqlite] How to use sqlite3_table_column_metadata?

Bart Smissaert wrote: > If I pass a valid connection and valid table name but null for all other > input parameters I get: > > return value: 0 > > How does that work? The documentation says: | If the column-name parameter to