Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 18-2-2019 14:51, Simon Slavin wrote: On 18 Feb 2019, at 1:33pm, Rocky Ji wrote: Here's the new thing: https://pastebin.com/raw/pSqjvJdZ Again, can we get rid of them sub-query? The "sum()" with the "group by" in the subquery make it difficult. There's nothing wrong with that query just

Re: [sqlite] Reading a table from inside a scalar function

Hi Keith, Thank you. I think I have all inputs to go ahead. The function I have in mind is deterministic, that is, it is expected to return the same value for a given input whether its called once or infinite number of times. I think I can get it work with SQLite. Regards Arun On

Re: [sqlite] typos in file format description

Thanks for the typo reports. On 2/18/19, Roland Illig wrote: > The sqlite.org website doesn't mention any contact address to which to > send typos in the documentation, therefore I'm trying it here. I had > looked at https://sqlite.org, the FAQ, the Support page, to no avail. > >

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.110.0 have begun...

If you have any issues with the current trunk code, please report them via this mailing list (and/or by creating a ticket on "https://system.data.sqlite.org/;) prior to Friday, February 22nd. Thanks. -- Joe Mistachkin ___ sqlite-users mailing list

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

create table coaches ( coachID integer primary key autoincrement, coach_name text ); create table players ( playerID integer primary key autoincrement, playerName text not null, salary integer, fk_coach integer, constraint abc foreign key

Re: [sqlite] Reading a table from inside a scalar function

Note that really in the latter case the correct attribute is SLO_CHNG which indicates that the function is fully deterministic WITHIN a statement execution but may be volatile BETWEEN statement executions. The DETERMINISTIC attribute means the opposite of the default volatile. The function

Re: [sqlite] ON CONFLICT with partial indexes

> However, suppose you had a partial index that keyed on > > "extra > 4" > > and your SELECT had the clause > > "WHERE extra > ?1" > > The parameter might be set to 7. You and I know that this means SQLite could use the partial index. But SQLite will not compare the 7 and the 4 and decide it can

[sqlite] typos in file format description

The sqlite.org website doesn't mention any contact address to which to send typos in the documentation, therefore I'm trying it here. I had looked at https://sqlite.org, the FAQ, the Support page, to no avail. https://sqlite.org/fileformat2.html has the following typos: "is as a" -> "as a"

Re: [sqlite] Reading a table from inside a scalar function

SQLite does not maintain state between VDBE executions ... each execution is a context onto itself. Nor is maintain state between separate VDBE executions executing concurrently. That is to say that the default volatile, SLO_CHNG or DETERMINISTIC attributes apply only within the execution

Re: [sqlite] ON CONFLICT with partial indexes

The only thing which MIGHT (note that I said MIGHT, you will have to try and see whether it does or not) make a difference is if you have STAT4 enabled and have generated statistics on your database. When STAT4 has been enabled AND you have statistics THEN the query plan is re-prepared after

Re: [sqlite] ON CONFLICT with partial indexes

On 18 Feb 2019, at 4:41pm, Charles Leifer wrote: > Simon, I appreciate that, but your comment seems to contradict the example I > provided, as well as the example Dr. Hipp provided. Am I misunderstanding? SQLite can compare two comparators. In Dr. Hipp's example the index says "extra IS NOT

Re: [sqlite] ON CONFLICT with partial indexes

Simon, I appreciate that, but your comment seems to contradict the example I provided, as well as the example Dr. Hipp provided. Am I misunderstanding? On Mon, Feb 18, 2019, 9:44 AM Simon Slavin On 18 Feb 2019, at 3:23pm, Charles Leifer wrote: > > > At some point before actually executing the

Re: [sqlite] Reading a table from inside a scalar function

On 18 Feb 2019, at 4:22pm, Arun - Siara Logics (cc) wrote: > Do you mean to say SQLite might keep function results across queries? It depends where you use it. See section 4 of However, the documentation should prepare you for the idea that you cannot

Re: [sqlite] Reading a table from inside a scalar function

sqlite3_exec still prepares an SQL query and executes it with step. It is merely syntactic sugar (a convenience wrapper) around the standard prepare and step procedure. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] Reading a table from inside a scalar function

You do it the same way you do in any C function. You obtain a handle to the db connection (which is very conveniently passed to your UDF), you prepare and execute the sql statement, retrieve the results of that execution, compute your response, and return it from the UDF function. --- The

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On Monday, 18 February, 2019 02:27, Rocky Ji : >Awesome, thanks for the detailed analysis; this is why I love mailing >lists. >It seems you jumped to the meat directly, in the opening of my >question, >notice the words "scenario" and "given". >I am sure you know what they mean, but for record:

Re: [sqlite] Reading a table from inside a scalar function

Thanks Dominique, Thanks Simon, Do you mean to say SQLite might keep function results across queries? My design would be more complicated, but it is something like this: If my function uses first part of a text column in the row involved and if I make sure all modifications are always appended

Re: [sqlite] ON CONFLICT with partial indexes

On 18 Feb 2019, at 3:23pm, Charles Leifer wrote: > At some point before actually executing the query I'm calling > sqlite3_bind...() to set the value of the parameter placeholder. Presumably > SQLite would know at that point that a certain index is usable (or in this > case, that a certain

Re: [sqlite] ON CONFLICT with partial indexes

On Mon, Feb 18, 2019 at 4:38 PM Richard Damon wrote: > Remember the query plan is determined when that statement is compiled, > which is BEFORE you do the binding of the parameters, so the plan can not > depend on the value of parameters. There is no later attempt to optimize > once the values

Re: [sqlite] ON CONFLICT with partial indexes

> On Feb 18, 2019, at 10:23 AM, Charles Leifer wrote: > > Thanks for the explanation. So does this mean that two identical queries > can have completely different query plans depending on whether they use a > parameters vs literal values embedded in the SQL string? > Remember the query plan is

Re: [sqlite] Typo in FTS5 documentation

On 17/2/62 02:21, J. King wrote: states: the following three queries all specify the same phrase: Four queries are then listed. Presumably it should read: Thanks for pointing this out. Will be fixed next time the website is built and

Re: [sqlite] ON CONFLICT with partial indexes

Thanks for the explanation. So does this mean that two identical queries can have completely different query plans depending on whether they use a parameters vs literal values embedded in the SQL string? > But in this case with the WHERE clause is incompletely specified, there is no way for the

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 18 Feb 2019, at 1:33pm, Rocky Ji wrote: > Here's the new thing: https://pastebin.com/raw/pSqjvJdZ > > Again, can we get rid of them sub-query? The "sum()" with the "group by" in the subquery make it difficult. There's nothing wrong with that query just as it is. In that form SQLite will

Re: [sqlite] Reading a table from inside a scalar function

On 18 Feb 2019, at 1:15pm, Arun - Siara Logics (cc) wrote: > Thank you, for the detailed advice, info and the pointer. Is there a faster > way to query the table using row id, that is, skip the query parsing and > planner? No. For fastest queries, use "WHERE rowid = ", and list the columns

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 2:16 PM Arun - Siara Logics (cc) wrote: > Thank you, for the detailed advice, info and the pointer. Is there a > faster way to query the table using row id, that is, skip the query parsing > and planner? I still need the page cache feature and allow for concurrent >

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

Whoa! Big revelation. I didn't know foreign keys were disabled by default. And my code just ran in sqlite3 shell, and this made me think text and rowid and etc foreign keys "just worked". Sorry for the ruckus. Here's the new thing: https://pastebin.com/raw/pSqjvJdZ Again, can we get rid of them

Re: [sqlite] Reading a table from inside a scalar function

Thank you, for the detailed advice, info and the pointer. Is there a faster way to query the table using row id, that is, skip the query parsing and planner? I still need the page cache feature and allow for concurrent modification of the row involved, while ensuring determinism by designing

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 1:05 PM R Smith wrote: > If not, I apologize, and is very interested in where then > "read_uncommitted" becomes useful? > read_uncommitted is about changes in *other* transactions than your own. You can always see your own changes, in *your* current transaction.

Re: [sqlite] Reading a table from inside a scalar function

On 2019/02/18 1:02 PM, Dominique Devienne wrote: On Mon, Feb 18, 2019 at 11:48 AM R Smith wrote: Note that in a transaction without WAL mode ... your query may or may not "see" data that is older, Hmmm, I don't think so. Journal mode, WAL or not, doesn't matter. If you are inside a

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 12:25 PM Richard Hipp wrote: > On 2/18/19, Arun - Siara Logics (cc) wrote: > > If known, kindly point me to an existing open source implementation. > > https://www.sqlite.org/src/file/ext/misc/eval.c Looks like the code was updated to use sqlite3_realloc64() despite

Re: [sqlite] Reading a table from inside a scalar function

On 2/18/19, Arun - Siara Logics (cc) wrote: > Hi, > > This is for an experimental feasibility study. Is it possible to read > specific row(s) from a table within same db (using row ids) from inside a > User defined Deterministic scalar function (C API)? > > If known, kindly point me to an

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 11:48 AM R Smith wrote: > Note that in a transaction without WAL mode ... your query may or may not > "see" data that is older, > Hmmm, I don't think so. Journal mode, WAL or not, doesn't matter. If you are inside a transaction, you are inside it. And will see the

Re: [sqlite] Reading a table from inside a scalar function

On 2019/02/18 12:06 PM, Arun - Siara Logics (cc) wrote: Yes it is possible, but then your UDF is unlikely to be *Deterministic*. By design, I intend to make sure that for any given input the function always returns the same value. If the dependent rows are missing or change over time, then it

Re: [sqlite] Reading a table from inside a scalar function

> Yes it is possible, but then your UDF is unlikely to be *Deterministic*. By design, I intend to make sure that for any given input the function always returns the same value. If the dependent rows are missing or change over time, then it would be an error condition. What would be the

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 2019/02/18 11:24 AM, Clemens Ladisch wrote: Rocky Ji wrote: But everyone advices against nested select statements. Who? I've heard rumors that older version of the Oracle query optimizer did worse with subqueries than with joins, but such advice is not necessarily correct for SQLite. +1

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

Awesome, thanks for the detailed analysis; this is why I love mailing lists. It seems you jumped to the meat directly, in the opening of my question, notice the words "scenario" and "given". I am sure you know what they mean, but for record: the links I gave help in "reproducing the problem" in a

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

Rocky Ji wrote: > But everyone advices against nested select statements. Who? I've heard rumors that older version of the Oracle query optimizer did worse with subqueries than with joins, but such advice is not necessarily correct for SQLite. SQL is set-based language, and queries that are

Re: [sqlite] Reading a table from inside a scalar function

On Mon, Feb 18, 2019 at 9:58 AM Arun - Siara Logics (cc) wrote: > [...]. Is it possible to read specific row(s) from a table within same > db (using row ids) from inside a User defined Deterministic scalar function > (C API)? > Yes it is possible, but then your UDF is unlikely to be

[sqlite] Reading a table from inside a scalar function

Hi, This is for an experimental feasibility study. Is it possible to read specific row(s) from a table within same db (using row ids) from inside a User defined Deterministic scalar function (C API)? If known, kindly point me to an existing open source implementation. Thanks Arun