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

[Luuk]

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 as it is.  In that form SQLite will 
figure out a very efficient query plan for what you're trying to do.  Don't 
worry about there being a subquery in there.

Simon.


Indeed, there's nothing wrong with the sub-query, but it 'can' be done 
without it. This 'example' has way too few records to test what 
performance will do when using the 'without sub-query', compared to the 
simple variant.



sqlite> select t1.i, t1.tekst, (select sum(t2.i) from test t2 where 
t2.tekst=t1.tekst) as totals from test t1;

QUERY PLAN
|--SCAN TABLE test AS t1
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN TABLE test AS t2
i tekst  tota
  -  
1 a  5
2 b  2
3 c  3
4 a  5
sqlite> select t1.i, t1.tekst, sum(t2.i) from test t1,test t2 where 
t2.tekst=t1.tekst group by t1.i,t1.tekst;

QUERY PLAN
|--SCAN TABLE test AS t1
|--SEARCH TABLE test AS t2 USING AUTOMATIC COVERING INDEX (tekst=?)
`--USE TEMP B-TREE FOR GROUP BY
i tekst  sum(
  -  
1 a  5
2 b  2
3 c  3
4 a  5
sqlite>

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

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

[Arun - Siara Logics (cc)]

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 Mon, 18 Feb 2019 22:50:55 +0530 Keith Medcalf  
wrote  
 > 
 > 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 will ALWAYS FROM NOW THROUGH THE END OF THE UNIVERSE always and 
 > without exception return the same value when presented with the same 
 > parameters.
 > 
 > SQLite3 really only cares about DETERMINISM within the current statment 
 > execution context.  Unless of course you use the output in something that 
 > persists outside of the "right bloody nowness" of a statement execution -- 
 > such as using the function in an index, for example.
 > 
 > ---
 > 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 Keith Medcalf
 > >Sent: Monday, 18 February, 2019 10:08
 > >To: SQLite mailing list
 > >Subject: 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 context of a single statement and not between
 > >statements or serial re-executions of the same statement.
 > >
 > >Ie, if you execute the statement:
 > >
 > >select cosine(34);
 > >
 > >then the results you get is TWO SEPARATE DETERMINISTIC RESULTS.  The
 > >fact that the function returned some particular value on some
 > >particular execution of the statement is not maintained between
 > >executions.  It is entirely possible that the function "cosine" is
 > >DETERMINISTIC with each single execution context yet returns
 > >different results when the statement is executed twice.
 > >
 > >---
 > >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 Arun - Siara Logics
 > >>(cc)
 > >>Sent: Monday, 18 February, 2019 09:23
 > >>To: SQLite mailing list
 > >>Subject: Re: [sqlite] Reading a table from inside a scalar function
 > >>Importance: High
 > >>
 > >>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 to the
 > >cell,
 > >>then the function will always return the same value. So it can be
 > >>deterministic and would work even if SQLite caches function results
 > >>across queries. Am I correct?
 > >>
 > >>  On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin
 > >> wrote 
 > >> > 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 you're interested in specifically.  Do not use "SELECT
 > >>*".
 > >> >
 > >> > > I still need the page cache feature and allow for concurrent
 > >>modification of the row involved, while ensuring determinism by
 > >>designing so.  I guess sqlite3_exec() would take care of this, but
 > >is
 > >>there a faster way?
 > >> >
 > >> > If you're allowing for the table row you're reading to be
 > >>modified, then how will your function be deterministic ?  Would
 > >>changing values in that row not lead to a change in the value
 > >>returned by your function ?  If not, why are you looking up the row
 > >?
 > >> >
 > >> > Note that if you mark that function as deterministic, you cannot
 > >>rely on SQLite calling your function at all.  SQLite may reason "I
 > >>called that function, with those arguments, a few instructions ago,
 > >>so I already know what the result will be.".
 > >> >
 > >> > Simon.
 > >> > ___
 > >> > sqlite-users mailing list
 > >> > sqlite-users@mailinglists.sqlite.org
 > >> > 

Re: [sqlite] typos in file format description

[Richard Hipp]

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.
>
> https://sqlite.org/fileformat2.html has the following typos:
>
> "is as a" -> "as a"
>
> "specify by" -> "specified by"
>
> "each trunk pages" -> "each trunk page"
>
> "varint are the" -> "varint are"
>
> "not truncate" -> "not truncated"
>
> Best,
> Roland
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Final preparations for the release of System.Data.SQLite v1.0.110.0 have begun...

[Joe Mistachkin]

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

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

[Keith Medcalf]

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 (fk_coach) references coaches (coachID)
);

create table matches (
matchID  integer primary key autoincrement,
playedAgainst text not null,
totalScore integer not null,
bets real not null
);

create table players_matches (
pmID  integer primary key autoincrement,
fk_match integer,
fk_player integer,
constraint mno foreign key (fk_player) references players (playerID),
constraint xyz foreign key (fk_match) references matches (matchID)
);

-- three coach_names CHECKED
insert into coaches (coach_name) values ("coach-1"), ("coach-2"), ("coach-3");

-- four players assigned to each coach CHECKED
insert into players (playerName, salary, fk_coach) values
("ella", 71, 1), ("alexander", 12, 1), ("eliza", 3, 1),
("theo", 91, 1), ("amelia", 82, 2), ("sebastian", 23, 2),
("louis", 53, 2), ("henry", 85, 2), ("penlope", 75, 3),
("james", 36, 3), ("nora", 46, 3), ("julian", 38, 3);

-- 3, 2, 3 matches in those coach_names
insert into matches (playedAgainst, totalScore, bets) values
("Utrecht University", 0, 129),
("Wesleyan University", 2, 112),
("City University of New York", 4, 118),
("Imperial College London", 1, 122),
("University of Göttingen", 2, 104),
("University of California–Davis", 1, 167),
("Dartmouth College", 3, 156),
("University of Sydney", 3, 146);

-- summary
insert into players_matches (fk_match, fk_player) values
(1, 1), (1, 3), (2, 2), (2, 3), (3, 4), (3, 3), (4, 6), (4, 7),
(5, 8), (5, 6), (6, 12), (6, 9), (7, 11), (7, 9), (8, 11), (8, 12);

analyze;

WITH MyMatches (matchID, playedAgainst, totalScore, bets, totalSalary)
 as (SELECT matchID, playedAgainst, totalScore, bets, (select sum(salary)
 from players, 
players_Matches
where 
players_matches.fk_player == players.playerID
  and 
players_matches.fk_match == matches.matchID)
  FROM matches)
select coach_name,
   matches.matchid as match_number,
   matches.playedagainst as match,
   playerName as player,
   salary,
   totalsalary - bets
  from players, mymatches as matches, players_matches, coaches
 where players.playerID == players_matches.fk_player
   and matches.matchID == players_matches.fk_match
   and players.fk_coach == coaches.coachID
;

QUERY PLAN
|--SCAN TABLE players_matches (~16 rows)
|--SEARCH TABLE players USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE matches USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE coaches USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
`--CORRELATED SCALAR SUBQUERY 1
   |--SCAN TABLE players_Matches (~4 rows)
   `--SEARCH TABLE players USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
coach_name  match_number  match   player  salary  
totalsalary - bets
--    --  --  --  
--
coach-1 1 Utrecht University  ella71  -55.0
coach-1 1 Utrecht University  eliza   3   -55.0
coach-1 2 Wesleyan Universit  alexander   12  -97.0
coach-1 2 Wesleyan Universit  eliza   3   -97.0
coach-1 3 City University of  theo91  -24.0
coach-1 3 City University of  eliza   3   -24.0
coach-2 4 Imperial College L  sebastian   23  -46.0
coach-2 4 Imperial College L  louis   53  -46.0
coach-2 5 University of Gött  henry   85  4.0
coach-2 5 University of Gött  sebastian   23  4.0
coach-3 6 University of Cali  julian  38  -54.0
coach-3 6 University of Cali  penlope 75  -54.0
coach-3 7 Dartmouth College   nora46  -35.0
coach-3 7 Dartmouth College   penlope 75  -35.0
coach-3 8 University of Sydn  nora46  -62.0
coach-3 8 University of Sydn  julian  38  -62.0

Creating the missing indexes:

sqlite> .lint fkey-indexes
CREATE INDEX 'players_fk_coach' ON 'players'('fk_coach'); --> coaches(coachID)
CREATE INDEX 'players_matches_fk_match' ON 'players_matches'('fk_match'); --> 
matches(matchID)
CREATE INDEX 'players_matches_fk_player' ON 'players_matches'('fk_player'); --> 
players(playerID)
sqlite>
sqlite> CREATE INDEX 

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

[Keith Medcalf]

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 will ALWAYS FROM NOW THROUGH THE END OF THE UNIVERSE always and 
without exception return the same value when presented with the same parameters.

SQLite3 really only cares about DETERMINISM within the current statment 
execution context.  Unless of course you use the output in something that 
persists outside of the "right bloody nowness" of a statement execution -- such 
as using the function in an index, for example.

---
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 Keith Medcalf
>Sent: Monday, 18 February, 2019 10:08
>To: SQLite mailing list
>Subject: 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 context of a single statement and not between
>statements or serial re-executions of the same statement.
>
>Ie, if you execute the statement:
>
>select cosine(34);
>
>then the results you get is TWO SEPARATE DETERMINISTIC RESULTS.  The
>fact that the function returned some particular value on some
>particular execution of the statement is not maintained between
>executions.  It is entirely possible that the function "cosine" is
>DETERMINISTIC with each single execution context yet returns
>different results when the statement is executed twice.
>
>---
>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 Arun - Siara Logics
>>(cc)
>>Sent: Monday, 18 February, 2019 09:23
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Reading a table from inside a scalar function
>>Importance: High
>>
>>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 to the
>cell,
>>then the function will always return the same value. So it can be
>>deterministic and would work even if SQLite caches function results
>>across queries. Am I correct?
>>
>>  On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin
>> wrote 
>> > 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 you're interested in specifically.  Do not use "SELECT
>>*".
>> >
>> > > I still need the page cache feature and allow for concurrent
>>modification of the row involved, while ensuring determinism by
>>designing so.  I guess sqlite3_exec() would take care of this, but
>is
>>there a faster way?
>> >
>> > If you're allowing for the table row you're reading to be
>>modified, then how will your function be deterministic ?  Would
>>changing values in that row not lead to a change in the value
>>returned by your function ?  If not, why are you looking up the row
>?
>> >
>> > Note that if you mark that function as deterministic, you cannot
>>rely on SQLite calling your function at all.  SQLite may reason "I
>>called that function, with those arguments, a few instructions ago,
>>so I already know what the result will be.".
>> >
>> > 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
>
>
>
>___
>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] ON CONFLICT with partial indexes

[Charles Leifer]

> 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 use the partial index.  It does not know that 7 is
greater than 4, because it doesn't look at the parameter's value before
doing its optimization.

Right -- I definitely understand the implications. My confusion is a result
of not having realized that parameterized queries are somewhat of a
second-class-citizen. That is, the same query will be treated differently
depending on whether it uses literal values vs bind parameters. I'm not
trying to debate or even question the rationale for these things -- I just
am noting that I found this surprising, since using bind parameters is
*always* what I do and suggest others to do.

This is a bummer because in multiple apps I have created some partial
indexes and did all my EXPLAIN analysis using the CLI and literal values,
not realizing that once I started using the DB from my application these
indexes would be unusable.

On Mon, Feb 18, 2019 at 10:54 AM Simon Slavin  wrote:

> 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 NULL"
>
> and the WHERE clause says
>
> "WHERE extra>?1"
>
> SQLite knows that for extra to be bigger than a number -- any number --
> extra cannot be NULL.  So it can deduce that it can use that partial
> index.  That's what Dr. Hipp wrote.
>
> 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 use the partial index.  It does not know that 7 is
> greater than 4, because it doesn't look at the parameter's value before
> doing its optimization.
>
> 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

[sqlite] typos in file format description

[Roland Illig]

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"

"specify by" -> "specified by"

"each trunk pages" -> "each trunk page"

"varint are the" -> "varint are"

"not truncate" -> "not truncated"

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

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

[Keith Medcalf]

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 context of a single 
statement and not between statements or serial re-executions of the same 
statement.

Ie, if you execute the statement:

select cosine(34);

then the results you get is TWO SEPARATE DETERMINISTIC RESULTS.  The fact that 
the function returned some particular value on some particular execution of the 
statement is not maintained between executions.  It is entirely possible that 
the function "cosine" is DETERMINISTIC with each single execution context yet 
returns different results when the statement is executed twice.

---
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 Arun - Siara Logics
>(cc)
>Sent: Monday, 18 February, 2019 09:23
>To: SQLite mailing list
>Subject: Re: [sqlite] Reading a table from inside a scalar function
>Importance: High
>
>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 to the cell,
>then the function will always return the same value. So it can be
>deterministic and would work even if SQLite caches function results
>across queries. Am I correct?
>
>  On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin
> wrote 
> > 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 you're interested in specifically.  Do not use "SELECT
>*".
> >
> > > I still need the page cache feature and allow for concurrent
>modification of the row involved, while ensuring determinism by
>designing so.  I guess sqlite3_exec() would take care of this, but is
>there a faster way?
> >
> > If you're allowing for the table row you're reading to be
>modified, then how will your function be deterministic ?  Would
>changing values in that row not lead to a change in the value
>returned by your function ?  If not, why are you looking up the row ?
> >
> > Note that if you mark that function as deterministic, you cannot
>rely on SQLite calling your function at all.  SQLite may reason "I
>called that function, with those arguments, a few instructions ago,
>so I already know what the result will be.".
> >
> > 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



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

Re: [sqlite] ON CONFLICT with partial indexes

[Keith Medcalf]

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 a BIND which 
changes information that may be relevant to the selection of INDEXES used by 
the planner.  This obviously means that the WHERE clause is now fully specified 
(because the plan is regenerated after the bind) and the planner MAYHAPS SO or 
MAYHAPS NOT make a different choice.

The difference is that with STAT4 enabled, every query that has bound 
parameters that could affect plan generation will be reprepared on the first 
step at execution time taking the value of the bindings into account.  If the 
time taken to "double prepare" the plan on every statement execution is greater 
than the time taken to execute the statement using the generic (first prepared 
plan), then the overall result will (wall clock elapsed time from GO to DONE) 
will be greater than if you did not use STAT4.  You will only know whether that 
is effective in your case is by giving it the old college try.  You may find 
that the use of the regenerated "particular" plan is significantly more 
performant to be worthwhile ... or you may find that the overhead of the 
re-prepare reduces performance over just using the generic plan.

Of course, I do not know if having the appropriate STAT4 statistics and the 
re-prepare after the BIND will result in using your partial index in this 
particular instance without going and testing it.  

Richard may know off-hand, but I do not.

---
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 Charles Leifer
>Sent: Monday, 18 February, 2019 08:23
>To: SQLite mailing list
>Subject: 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 query planner to do that.
>
>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 constraint is relevant)?
>
>This seems to me to have the effect that partial indexes are in many
>ways
>ineffective when mixed with parameterized queries. I understand that
>they
>might be applicable in cases where very general assumptions about
>null/not-null can be inferred, but that's not what I would expect as
>an
>end-user.
>
>On Sun, Feb 17, 2019 at 5:26 PM Richard Hipp  wrote:
>
>> On 2/17/19, Charles Leifer  wrote:
>> > I'm having trouble executing an INSERT ... ON CONFLICT with a
>partial
>> > index. It works fine in the SQLite shell, but it fails when I
>express the
>> > conflict constraint using a parameterized query.
>> >
>> > For example:
>> >
>> > CREATE TABLE ukvp ("id" integer primary key, "key" text not null,
>"value"
>> > int not null, "extra" int not null);
>> > CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra
>> 1;
>> >
>> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?,
>?), (?,
>> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE
>SET
>> > "extra" = EXCLUDED."extra"''
>> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1))
>> >
>> > But the following works:
>> >
>> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?,
>?), (?,
>> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE
>SET
>> > "extra" = EXCLUDED."extra"''
>> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3))
>> >
>> > Is this a bug?
>>
>> No, this is not a bug.
>>
>> The purpose of the WHERE clause in an upsert is to match the
>> constraint against a particular  UNIQUE index.  The query planner
>must
>> know which index constraint is failing in order to generate correct
>> code for the upsert.  But in this case with the WHERE clause is
>> incompletely specified, there is no way for the query planner to do
>> that.
>>
>> For the same reason, this query:
>>
>>SELECT value FROM ukvp WHERE key=?1 AND extra>?2;
>>
>> will *not* use the partial index, but this query:
>>
>>SELECT value FROM ukvp WHERE key=?1 AND extra>1;
>>
>> *will* use the partial index.  (Run each of the above with EXPLAIN
>> QUERY PLAN to see for yourself.)
>>
>> The query planner will never use a partial index unless it can
>prove
>> at SQL-statement compile-time that the WHERE clause of the query
>> implies that the WHERE clause of the partial index.  With your
>partial
>> index, you can never prove 

Re: [sqlite] ON CONFLICT with partial indexes

[Simon Slavin]

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

and the WHERE clause says

"WHERE extra>?1"

SQLite knows that for extra to be bigger than a number -- any number -- extra 
cannot be NULL.  So it can deduce that it can use that partial index.  That's 
what Dr. Hipp wrote.

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 use the partial index.  It does not know that 7 is greater than 4, 
because it doesn't look at the parameter's value before doing its optimization.

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

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

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 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 constraint is relevant)?
>
> SQLite knows before then.  The SQLite query optimizer does not do
> algebra.  It does its job without knowing what values you will plug into
> the parameters.
>
> Indexes are chosen by looking at how you are comparing certain columns:
> whether you compare a column using "<" or ">" or "=" or some other
> comparator.  The values you're comparing them with are ignored.
>
> 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] Reading a table from inside a scalar function

[Simon Slavin]

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 
predict whether a deterministic function will be called.  SQLite may not 
optimize out calls now, but it may do so in the future.

If you don't like this idea, don't mark your function as deterministic.

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

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

[Keith Medcalf]

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.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Arun - Siara Logics
>(cc)
>Sent: Monday, 18 February, 2019 06:16
>To: SQLite mailing list
>Subject: Re: [sqlite] Reading a table from inside a scalar function
>Importance: High
>
>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 so.  I guess sqlite3_exec() would take care
>of this, but is there a faster way?
>
>  On Mon, 18 Feb 2019 16:55:23 +0530 Richard Hipp
> wrote 
> > 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 existing open source
>implementation.
> >
> > https://www.sqlite.org/src/file/ext/misc/eval.c
> >
> > --
> > 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



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

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

[Keith Medcalf]

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 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 Arun - Siara Logics
>(cc)
>Sent: Monday, 18 February, 2019 03:06
>To: SQLite mailing list
>Subject: Re: [sqlite] Reading a table from inside a scalar function
>Importance: High
>
>> 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 recommended method ?   Is there an API to read a
>table row using RowID?  Or should I traverse the BTree pages using my
>own code? 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

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

[Keith Medcalf]

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: the links I gave
>help in
>"reproducing the problem" in a minimal viable way.
>Don't worry too much about ROWID, there are no deletes/updates on our
>little example.
>Finally, please reread the OP, I explicitly mentioned the
>conditions/relations exactly as they are supposed to be; if the words
>team
>/ player / coach make you uncomfortable, use abcd...

>Now, can we get the desired result without nested `select`s?

Why?  Would you like the prose to not use the words "the" and "then" as well?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>On Mon, Feb 18, 2019, 10:56 AM Keith Medcalf wrote:
>
>>
>>
>> create table coaches (coach_name text);
>>
>> create table players (
>> playerName text not null,
>> salary integer,
>> fk_coach text,
>> constraint abc foreign key (fk_coach) references coaches
>> (coach_name)
>> );
>>
>> create table matches (
>> playedAgainst text not null,
>> totalScore integer not null,
>> bets real not null
>> );
>>
>> create table players_matches (
>> fk_match integer,
>> fk_player integer,
>> constraint mno foreign key (fk_player) references players
>(ROWID),
>> constraint xyz foreign key (fk_match) references matches
>(ROWID)
>> );
>>
>> 1)  You do not have any columns in either PLAYERS or MATCHES called
>> ROWID.  Foreign Keys may only refer to explicitly named columns.
>> 2)  The tables have no PRIMARY KEY.  Therefore there is no way to
>identify
>> a particular set of attributes in a table (tuple).
>> 3)  text fields for playerName and Coach_Name are ill defined (case
>> sensitive/insensitive?  Unique?)
>> 4)  Parent Keys should be UNIQUE, Child Keys may be unique (1:1) or
>not
>> unique (1:N) (but nevertheless must be indexed)
>>
>> Your data structure does not model reality.  Players are on Teams.
>> Coaches coach Teams.  I suppose you could short-circuit and
>eliminate Teams
>> by assigning Players to Coaches, but that means the same coach may
>never
>> coach different teams.  Matches are between Teams (or coaches
>players if
>> you have gotten rid of teams).
>>
>> Or is this to model school-children at recess, where the so-called
>team is
>> merely the happenstance of the "John Picks Chris" and "Alice Picks
>Don"
>> verses the next recess where "John Picks Don" and "Alice gets stuck
>with
>> Chris"?
>>
>> In any event, why do you not use table generating subqueries that
>return
>> the data that you want to play with?  In other words, once you know
>what
>> data you need then you generate that data.
>>
>> For example.  You say " for a match X, profit/match is `sum(salary
>of all
>> players playing in X) - X.bets"
>>
>> So let us compute that using your wierd and invalid schema:
>>
>> select matches.rowid as match, (select sum(salary)
>>   from players, players_matches
>>  where players.rowid ==
>> players_matches.fk_player
>>and players_matches.fk_match ==
>> matches.rowid) - matches.bets AS profitmatch
>>   from matches
>> ;
>>
>>
>>
>>
>> So now you have the "profitmatch" for each match.
>>
>> The you also want a bunch of detail crap using the same wierd and
>invalid
>> schema:
>>
>> select fk_coach as coach_name,
>>matches.rowid as match_number,
>>matches.playedagainst as match,
>>playerName as player,
>>salary
>>   from players, matches, players_matches
>>  where players.rowid == players_matches.fk_player
>>and matches.rowid == players_matches.fk_match
>> ;
>>
>> And then you want to join those together based on the match:
>>
>> select fk_coach as coach_name,
>>matches.rowid as match_number,
>>matches.playedagainst as match,
>>playerName as player,
>>salary,
>>profitmatch as profit
>>   from players, matches, players_matches,
>>(
>> select matches.rowid as match, (select sum(salary)
>>   from players,
>players_matches
>>  where players.rowid ==
>> players_matches.fk_player
>>and
>players_matches.fk_match ==
>> matches.rowid) - matches.bets AS profitmatch
>>   from matches
>>) as profit
>>  where players.rowid == players_matches.fk_player
>>and matches.rowid == players_matches.fk_match
>>and profit.match == matches.rowid
>> ;
>>
>> The SQL is correct and produces the results, however, since the
>schema is
>> invalid it is difficult to actually run it.  Simplifications are
>possible
>> 

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

[Arun - Siara Logics (cc)]

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 to the cell, then the function will 
always return the same value. So it can be deterministic and would work even if 
SQLite caches function results across queries. Am I correct?

  On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin  
wrote  
 > 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 you're interested in specifically.  Do not use "SELECT *".
 > 
 > > I still need the page cache feature and allow for concurrent modification 
 > > of the row involved, while ensuring determinism by designing so.  I guess 
 > > sqlite3_exec() would take care of this, but is there a faster way?
 > 
 > If you're allowing for the table row you're reading to be modified, then how 
 > will your function be deterministic ?  Would changing values in that row not 
 > lead to a change in the value returned by your function ?  If not, why are 
 > you looking up the row ?
 > 
 > Note that if you mark that function as deterministic, you cannot rely on 
 > SQLite calling your function at all.  SQLite may reason "I called that 
 > function, with those arguments, a few instructions ago, so I already know 
 > what the result will be.".
 > 
 > 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] ON CONFLICT with partial indexes

[Simon Slavin]

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 constraint is relevant)?

SQLite knows before then.  The SQLite query optimizer does not do algebra.  It 
does its job without knowing what values you will plug into the parameters.

Indexes are chosen by looking at how you are comparing certain columns: whether 
you compare a column using "<" or ">" or "=" or some other comparator.  The 
values you're comparing them with are ignored.

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

Re: [sqlite] ON CONFLICT with partial indexes

[Dominique Devienne]

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 are known, as in general, this isn’t apt to help (you have
> one case where it could, but to help that case, you would need to hurt a
> lot of other more common cases).
>

Actually, "bind peeking" to find a more optimal plan does often help.
Otherwise "non-lite" RDBMS wouldn't do it.
The fact SQLite does not implement, does not mean it's not a useful thing
to do. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON CONFLICT with partial indexes

[Richard Damon]

> 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 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 are 
known, as in general, this isn’t apt to help (you have one case where it could, 
but to help that case, you would need to hurt a lot of other more common cases).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Typo in FTS5 documentation

[Dan Kennedy]

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


Dan.


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

Re: [sqlite] ON CONFLICT with partial indexes

[Charles Leifer]

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 query planner to do that.

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 constraint is relevant)?

This seems to me to have the effect that partial indexes are in many ways
ineffective when mixed with parameterized queries. I understand that they
might be applicable in cases where very general assumptions about
null/not-null can be inferred, but that's not what I would expect as an
end-user.

On Sun, Feb 17, 2019 at 5:26 PM Richard Hipp  wrote:

> On 2/17/19, Charles Leifer  wrote:
> > I'm having trouble executing an INSERT ... ON CONFLICT with a partial
> > index. It works fine in the SQLite shell, but it fails when I express the
> > conflict constraint using a parameterized query.
> >
> > For example:
> >
> > CREATE TABLE ukvp ("id" integer primary key, "key" text not null, "value"
> > int not null, "extra" int not null);
> > CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra > 1;
> >
> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE SET
> > "extra" = EXCLUDED."extra"''
> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1))
> >
> > But the following works:
> >
> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE SET
> > "extra" = EXCLUDED."extra"''
> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3))
> >
> > Is this a bug?
>
> No, this is not a bug.
>
> The purpose of the WHERE clause in an upsert is to match the
> constraint against a particular  UNIQUE index.  The query planner must
> know which index constraint is failing in order to generate correct
> code for the upsert.  But in this case with the WHERE clause is
> incompletely specified, there is no way for the query planner to do
> that.
>
> For the same reason, this query:
>
>SELECT value FROM ukvp WHERE key=?1 AND extra>?2;
>
> will *not* use the partial index, but this query:
>
>SELECT value FROM ukvp WHERE key=?1 AND extra>1;
>
> *will* use the partial index.  (Run each of the above with EXPLAIN
> QUERY PLAN to see for yourself.)
>
> The query planner will never use a partial index unless it can prove
> at SQL-statement compile-time that the WHERE clause of the query
> implies that the WHERE clause of the partial index.  With your partial
> index, you can never prove anything about the truth of the condition
> if the query contains "extra>?1".  However, if your partial index had
> said "WHERE extra IS NOT NULL", then the partial index would be usable
> in all of the above situations, since "extra>?1" does indeed imply
> that "extra IS NOT NULL".
> --
> 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] How to get aggregate without reducing number of rows (repeats are ok)?

[Simon Slavin]

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 
figure out a very efficient query plan for what you're trying to do.  Don't 
worry about there being a subquery in there.

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

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

[Simon Slavin]

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 
you're interested in specifically.  Do not use "SELECT *".

> I still need the page cache feature and allow for concurrent modification of 
> the row involved, while ensuring determinism by designing so.  I guess 
> sqlite3_exec() would take care of this, but is there a faster way?

If you're allowing for the table row you're reading to be modified, then how 
will your function be deterministic ?  Would changing values in that row not 
lead to a change in the value returned by your function ?  If not, why are you 
looking up the row ?

Note that if you mark that function as deterministic, you cannot rely on SQLite 
calling your function at all.  SQLite may reason "I called that function, with 
those arguments, a few instructions ago, so I already know what the result will 
be.".

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

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

[Dominique Devienne]

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
> modification of the row involved, while ensuring determinism by designing
> so.  I guess sqlite3_exec() would take care of this, but is there a faster
> way?
>

No there isn't. Query parsing and planner is fast enough. Pages are not
accessible using the public API.
"Regular" tables (i.e. not the new Without Rowid tables) have an implicit
rowid integral column.
Querying use that column is faster, since no index access is necessary at
all.
You cannot do any better than this. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Rocky Ji]

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 sub-query?

Be nice.

On Mon, Feb 18, 2019, 3:22 PM R Smith  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
>
> Whomever said to avoid nested or sub queries are lying to you - it's
> like saying "Use only left turns when driving, avoid right turns" - it's
> just silly, they both help to get you there.
>
> That said, when you can achieve a result using only an outer query or a
> join that may utilize an index, then sure, you should prefer that over a
> sub query for efficiency purposes, but that in no way means to "avoid"
> them completely. They are part and parcel of SQL and very much optimized
> for (in modern DB engines) and often work faster and better.
>
> Your advisors may have meant a specific older engine that had known
> issues with sub-selects or such.
>
> I see you telling Keith that the schema wording doesn't matter and that
> the question is hypothetical, but have you actually run the schema you
> made? It doesn't work because, as Keith pointed out, those are invalid
> foreign keys. (It only runs if you set PRAGMA foreign_keys = 0; but that
> negates the purpose, it should be on).
>
> Remake the schema, use ABCD if that suits you better, but at least make
> a schema that works, and restate the question. That way we can run the
> schema on our side, compose the queries that would answer your question
> without us having to spend half an hour first rewriting the schema into
> a working one (which then may well destroy the premise of your question).
>
> Cheers,
> Ryan
>
> ___
> 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] Reading a table from inside a scalar function

[Arun - Siara Logics (cc)]

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 so.  
I guess sqlite3_exec() would take care of this, but is there a faster way?

  On Mon, 18 Feb 2019 16:55:23 +0530 Richard Hipp  wrote 
 
 > 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 existing open source implementation.
 > 
 > https://www.sqlite.org/src/file/ext/misc/eval.c
 > 
 > -- 
 > 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] Reading a table from inside a scalar function

[Dominique Devienne]

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.
read_uncommitted is evil. never use it. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 transaction, you are inside it. And will see the
current rows for that TX,
whether you are inside the outer query, or the "inner" one (inside the
UDF). --DD


Thank you for saying Dominique, this is important. I neglected to state 
that it is inside another transaction, not it's own transaction.


To be clear, and to check if I do not have it wrong perhaps, let me ask 
here: If I start a new SELECT query (whether inside a UDF or not), while 
another transaction is going on in a DB with WAL and read_uncommitted 
set - can I or can I not "see" data changed by that transaction but not 
yet committed?


If not, I apologize, and is very interested in where then 
"read_uncommitted" becomes useful?



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

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

[Dominique Devienne]

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 what the
comment seems to imply.
Also, the max test was probably meant for
sqlite3_realloc(void*,int), to avoid overflowing that int,
while now realloc64() takes a sqlite3_uint64. My $0.02. --DD

  /* Using sqlite3_realloc64() would be better, but it is a recent
  ** addition and will cause a segfault if loaded by an older version
  ** of SQLite.  */

  zNew = p->nAlloc<=0x7fff ? sqlite3_realloc64(p->z, p->nAlloc) : 0;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 existing open source implementation.

https://www.sqlite.org/src/file/ext/misc/eval.c

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

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

[Dominique Devienne]

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
current rows for that TX,
whether you are inside the outer query, or the "inner" one (inside the
UDF). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 would be an 
error condition.


What Dominique referred to is not so much the your intent, but an actual 
specification on a UDF (when added) to let the query planner know it is 
Deterministic or not, simply informing the QP that: "If you have 
computed me once for f(x) --> y then for every next same value of x you 
will always receive the same value of y, so that you can assume the 
output and need not call me again."


This is undesirable if the function needs to log something, for 
instance, or if the value of f(x) --> y over time can change for the 
same values of x. Non-deterministic functions are obviously slower in 
large queries, but there is nothing wrong with it, you CAN do it - 
Dominic simply pointed out that what you wish to do implies 
non-determinism (though you can force it otherwise if you can guarantee 
conformance and the consequences are acceptable, as it would seem from 
your explanation).




Is there an API to read a table row using RowID?  Or should I traverse the 
BTree pages using my own code? Thanks!


You should use a query and the standard API. This is pretty 
straight-forward and conforms to the most basic use of the SQLite API 
(no real need for examples). It probably goes without saying, but you 
need to obviously get and release all used API resources within your 
UDF, regardless of error conditions.


Note that in a transaction without WAL mode (and enabled/disabled 
READ_UNCOMMITTED) your query may or may not "see" data that is older, at 
least from before any current transaction(s) - which can be made to play 
exactly into your hand by controlling said journal mode. The question 
you need to answer is - will you be controlling the Journal mode?



Cheers!
Ryan

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

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

[Arun - Siara Logics (cc)]

> 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 recommended method ?   Is there an API to read a table row 
using RowID?  Or should I traverse the BTree pages using my own code? Thanks!


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

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

[R Smith]

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

Whomever said to avoid nested or sub queries are lying to you - it's 
like saying "Use only left turns when driving, avoid right turns" - it's 
just silly, they both help to get you there.


That said, when you can achieve a result using only an outer query or a 
join that may utilize an index, then sure, you should prefer that over a 
sub query for efficiency purposes, but that in no way means to "avoid" 
them completely. They are part and parcel of SQL and very much optimized 
for (in modern DB engines) and often work faster and better.


Your advisors may have meant a specific older engine that had known 
issues with sub-selects or such.


I see you telling Keith that the schema wording doesn't matter and that 
the question is hypothetical, but have you actually run the schema you 
made? It doesn't work because, as Keith pointed out, those are invalid 
foreign keys. (It only runs if you set PRAGMA foreign_keys = 0; but that 
negates the purpose, it should be on).


Remake the schema, use ABCD if that suits you better, but at least make 
a schema that works, and restate the question. That way we can run the 
schema on our side, compose the queries that would answer your question 
without us having to spend half an hour first rewriting the schema into 
a working one (which then may well destroy the premise of your question).


Cheers,
Ryan

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

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

[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: the links I gave help in
"reproducing the problem" in a minimal viable way.
Don't worry too much about ROWID, there are no deletes/updates on our
little example.
Finally, please reread the OP, I explicitly mentioned the
conditions/relations exactly as they are supposed to be; if the words team
/ player / coach make you uncomfortable, use abcd...

Now, can we get the desired result without nested `select`s?

On Mon, Feb 18, 2019, 10:56 AM Keith Medcalf 
>
> create table coaches (coach_name text);
>
> create table players (
> playerName text not null,
> salary integer,
> fk_coach text,
> constraint abc foreign key (fk_coach) references coaches
> (coach_name)
> );
>
> create table matches (
> playedAgainst text not null,
> totalScore integer not null,
> bets real not null
> );
>
> create table players_matches (
> fk_match integer,
> fk_player integer,
> constraint mno foreign key (fk_player) references players (ROWID),
> constraint xyz foreign key (fk_match) references matches (ROWID)
> );
>
> 1)  You do not have any columns in either PLAYERS or MATCHES called
> ROWID.  Foreign Keys may only refer to explicitly named columns.
> 2)  The tables have no PRIMARY KEY.  Therefore there is no way to identify
> a particular set of attributes in a table (tuple).
> 3)  text fields for playerName and Coach_Name are ill defined (case
> sensitive/insensitive?  Unique?)
> 4)  Parent Keys should be UNIQUE, Child Keys may be unique (1:1) or not
> unique (1:N) (but nevertheless must be indexed)
>
> Your data structure does not model reality.  Players are on Teams.
> Coaches coach Teams.  I suppose you could short-circuit and eliminate Teams
> by assigning Players to Coaches, but that means the same coach may never
> coach different teams.  Matches are between Teams (or coaches players if
> you have gotten rid of teams).
>
> Or is this to model school-children at recess, where the so-called team is
> merely the happenstance of the "John Picks Chris" and "Alice Picks Don"
> verses the next recess where "John Picks Don" and "Alice gets stuck with
> Chris"?
>
> In any event, why do you not use table generating subqueries that return
> the data that you want to play with?  In other words, once you know what
> data you need then you generate that data.
>
> For example.  You say " for a match X, profit/match is `sum(salary of all
> players playing in X) - X.bets"
>
> So let us compute that using your wierd and invalid schema:
>
> select matches.rowid as match, (select sum(salary)
>   from players, players_matches
>  where players.rowid ==
> players_matches.fk_player
>and players_matches.fk_match ==
> matches.rowid) - matches.bets AS profitmatch
>   from matches
> ;
>
>
>
>
> So now you have the "profitmatch" for each match.
>
> The you also want a bunch of detail crap using the same wierd and invalid
> schema:
>
> select fk_coach as coach_name,
>matches.rowid as match_number,
>matches.playedagainst as match,
>playerName as player,
>salary
>   from players, matches, players_matches
>  where players.rowid == players_matches.fk_player
>and matches.rowid == players_matches.fk_match
> ;
>
> And then you want to join those together based on the match:
>
> select fk_coach as coach_name,
>matches.rowid as match_number,
>matches.playedagainst as match,
>playerName as player,
>salary,
>profitmatch as profit
>   from players, matches, players_matches,
>(
> select matches.rowid as match, (select sum(salary)
>   from players, players_matches
>  where players.rowid ==
> players_matches.fk_player
>and players_matches.fk_match ==
> matches.rowid) - matches.bets AS profitmatch
>   from matches
>) as profit
>  where players.rowid == players_matches.fk_player
>and matches.rowid == players_matches.fk_match
>and profit.match == matches.rowid
> ;
>
> The SQL is correct and produces the results, however, since the schema is
> invalid it is difficult to actually run it.  Simplifications are possible
> (there is no point in including the "matches" table twice, for example, and
> the correlated subquery could be put right in the main query, and if the
> database schema itself were normalized, then even further simplifications
> would be possible).
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original 

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

[Clemens Ladisch]

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 written this way are
often easier to understand and maintain.


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

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

[Dominique Devienne]

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 *Deterministic*.
The latter means the function always returns the same result given inputs.
If you have to lookup rows in some table, mostly likely based on inputs,
then those rows are other indirect inputs of some kind, so determinism is
unlikely
(although not possible, depends).

SQLite statements "can" nest. Whether that's a good idea, it depends :).
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Reading a table from inside a scalar function

[Arun - Siara Logics (cc)]

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


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