Re: [sqlite] Current Time in WHEN clause.

[Keith Medcalf]

That will work.  

If you run the query in the CLI prefixed with explain (after having given the 
.explain command), you will see the actual code that the query engine executes. 
 You will note that where it accesses strftime('%s', 'now') it is surrounded by 
a ONCE instruction, conditionally jumping over the recomputation if it has 
already been computed once ...

13Once   1 21000
14Null   0 13000
15Integer1 14000
16String80 160 %s 00
17String80 170 now00
18Function   3 1615strftime(-1)   02
19Move   1513100
20IfZero 1421-1   00
21Gt 132711collseq(BINARY)  6c

This compares to your original select, which did not use a scalar subquery, 
which had the code:

13String80 130 %s 00
14String80 140 now00
15Function   3 1312strftime(-1)   02
16Gt 122211collseq(BINARY)  6c
17Column 2 0 15   00

Which computed the value for each row, instead of just once for the entire 
query ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Kevin Martin
> Sent: Sunday, 22 July, 2012 15:32
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Current Time in WHEN clause.
> 
> For now I've decided to play safe and I've added a added an 'ok' column to
> tell me if I'm within 5s of an update.
> 
> > CREATE VIEW near_update as select count(*) as n from aliases where
> abs(strftime('%s', 'now') - validFrom) < 5;
> > CREATE VIEW alias_info as select a.name, s.url, s.type, nu.n == 0 as ok
> from latest_aliases as a join services as s on a.assignedTo = s.name join
> near_update as nu;
> 
> In the scripts, I just need to check the ok column, and if it's 0, then
> return a temporary error to the client.
> 
> Probably not the nicest solution, but it keeps complicated sql out of the
> scripts, and makes sure all the services reported back are always in sync -
> which are my main aims. Plus, there are only likely to be a few updates a
> month, most in the middle of the night, so the occasional temporary error
> won't be a problem.
> 
> On 22 Jul 2012, at 21:54, Keith Medcalf wrote:
> 
> > if they are tiny, then it matters not, if they will always be tiny
> 
> 
> Yeah, we're not anticipating many updates, and eventually, I intend writing a
> cron script to clear the old ones out.
> 
> Thanks,
> Kevin
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

For now I've decided to play safe and I've added a added an 'ok' column to tell 
me if I'm within 5s of an update.

> CREATE VIEW near_update as select count(*) as n from aliases where 
> abs(strftime('%s', 'now') - validFrom) < 5;
> CREATE VIEW alias_info as select a.name, s.url, s.type, nu.n == 0 as ok from 
> latest_aliases as a join services as s on a.assignedTo = s.name join 
> near_update as nu;

In the scripts, I just need to check the ok column, and if it's 0, then return 
a temporary error to the client.

Probably not the nicest solution, but it keeps complicated sql out of the 
scripts, and makes sure all the services reported back are always in sync - 
which are my main aims. Plus, there are only likely to be a few updates a 
month, most in the middle of the night, so the occasional temporary error won't 
be a problem.

On 22 Jul 2012, at 21:54, Keith Medcalf wrote:

> if they are tiny, then it matters not, if they will always be tiny


Yeah, we're not anticipating many updates, and eventually, I intend writing a 
cron script to clear the old ones out.

Thanks,
Kevin


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

Re: [sqlite] Current Time in WHEN clause.

[Keith Medcalf]

Pavel,

> No SQL format can guarantee anything. And changing SQL format won't change
> its execution plan - SQL optimizer will decide that for you. 

Agreed.

> And I don't know where did you take your information from but I can guarantee 
> you that with your query SQLite will execute strftime many times and OP's 
> problem will persist.

You are incorrect.  When the strftime function is used in either a scalar 
subquery or a joined table query, it is executed exactly once.  In the original 
query it was executed multiple times, however, making it a scalar subquery 
fixes that.

There are many ways to phrase the query, and if you force the appropriate 
covering index on aliases (name, validFrom, assignedTo) they all come out with 
just quite similar code.  As long as "strftime('%s', 'now')" is replaced with 
"(select strftime('%s', 'now'))" it will only be executed once.  Whether you 
unravel the query by hand or let the optimizer do it, you get almost the same 
result in all cases, though some queries will be more efficient than others, 
depending on how big the tables are (if they are tiny, then it matters not, if 
they will always be tiny).

You can use .explain and explain (not explain query plan) and see that for 
youself.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org



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

Re: [sqlite] Current Time in WHEN clause.

[Pavel Ivanov]

Keith,

No SQL format can guarantee anything. And changing SQL format won't change
its execution plan - SQL optimizer will decide that for you. And I don't
know where did you take your information from but I can guarantee you that
with your query SQLite will execute strftime many times and OP's problem
will persist.

Pavel
On Jul 22, 2012 2:24 PM, "Keith Medcalf"  wrote:

> select a.name, a.assignedTo, a.validFrom
>   from aliases a,
>(select name, max(validfrom) as mvf
>   from aliases,
>(select strftime('%s', 'now') as now) as c
>  where validFrom <= now
>   group by name) as b
>  where a.name=b.name and a.validfrom=b.mvf;
>
> should also work.  I changed the syntax slightly:
> from a join b join c on a.x=b.y and b.z=c.p is just a different spelling
> of from a, b, c where a.x=b.y and b.z=c.p
> designed to make it clear (COBOL style) which clauses are join columns
> (ON) and which are row selectors (WHERE).  It should have zero effect on
> the actually query plan.
>
> Effectively, creates a temp table with a single row containing a single
> value now, which is used as a selector against the rows of aliases.  This
> format should guarantee that the strftime function is only ever executed
> once.
>
> sqlite> explain query plan select a.name, a.assignedTo, a.validFrom
>...>   from aliases a,
>...>(select name, max(validfrom) as mvf
>...>   from aliases,
>...>(select strftime('%s', 'now') as now) as c
>...>  where validFrom <= now
>...>   group by name) as b
>...>  where a.name=b.name and a.validfrom=b.mvf;
> SELECT item[0] = {0:0}
>item[1] = {0:1}
>item[2] = {0:2}
> FROM {0,*} = aliases (AS a)
>  {1,*} = SELECT agg_flag
> item[0] = AGG{2:0}
> item[1] = AGG_FUNCTION:max(AGG{2:2})
>  FROM {2,*} = aliases
>   {3,*} = SELECT FUNCTION:strftime(item[0] = '%s'
>item[1] = 'now')
>   END (tabname=sqlite_subquery_53E850_) (AS c)
>  WHERE LE({2:2},{3:0})
>  GROUPBY {2:0}
>  END (tabname=sqlite_subquery_53E4D0_) (AS b)
> WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
> END
> sele  order  from  deta
>   -    
> 1 0  1 SCAN SUBQUERY 2 AS c (~1 rows)
> 1 1  0 SCAN TABLE aliases (~33 rows)
> 1 0  0 USE TEMP B-TREE FOR GROUP BY
> 0 0  1 SCAN SUBQUERY 1 AS b (~100 rows)
> 0 1  0 SEARCH TABLE aliases AS a USING AUTOMATIC
> COVERING INDEX (name=? AND validFrom=?) (~7 rows)
> sqlite>
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Current Time in WHEN clause.

[Keith Medcalf]

select a.name, a.assignedTo, a.validFrom 
  from aliases a,
   (select name, max(validfrom) as mvf 
  from aliases, 
   (select strftime('%s', 'now') as now) as c
 where validFrom <= now 
  group by name) as b
 where a.name=b.name and a.validfrom=b.mvf;

should also work.  I changed the syntax slightly:  
from a join b join c on a.x=b.y and b.z=c.p is just a different spelling of 
from a, b, c where a.x=b.y and b.z=c.p
designed to make it clear (COBOL style) which clauses are join columns (ON) and 
which are row selectors (WHERE).  It should have zero effect on the actually 
query plan.

Effectively, creates a temp table with a single row containing a single value 
now, which is used as a selector against the rows of aliases.  This format 
should guarantee that the strftime function is only ever executed once.

sqlite> explain query plan select a.name, a.assignedTo, a.validFrom
   ...>   from aliases a,
   ...>(select name, max(validfrom) as mvf
   ...>   from aliases,
   ...>(select strftime('%s', 'now') as now) as c
   ...>  where validFrom <= now
   ...>   group by name) as b
   ...>  where a.name=b.name and a.validfrom=b.mvf;
SELECT item[0] = {0:0}
   item[1] = {0:1}
   item[2] = {0:2}
FROM {0,*} = aliases (AS a)
 {1,*} = SELECT agg_flag
item[0] = AGG{2:0}
item[1] = AGG_FUNCTION:max(AGG{2:2})
 FROM {2,*} = aliases
  {3,*} = SELECT FUNCTION:strftime(item[0] = '%s'
   item[1] = 'now')
  END (tabname=sqlite_subquery_53E850_) (AS c)
 WHERE LE({2:2},{3:0})
 GROUPBY {2:0}
 END (tabname=sqlite_subquery_53E4D0_) (AS b)
WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
END
sele  order  from  deta
  -    
1 0  1 SCAN SUBQUERY 2 AS c (~1 rows)
1 1  0 SCAN TABLE aliases (~33 rows)
1 0  0 USE TEMP B-TREE FOR GROUP BY
0 0  1 SCAN SUBQUERY 1 AS b (~100 rows)
0 1  0 SEARCH TABLE aliases AS a USING AUTOMATIC COVERING 
INDEX (name=? AND validFrom=?) (~7 rows)
sqlite>

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Current Time in WHEN clause.

[Simon Slavin]

On 22 Jul 2012, at 6:48pm, Kevin Martin  wrote:

> Is the behaviour it will only run once guaranteed by any standard, or likely 
> to change in future/previous versions of sqlite?

It's not predictable, even in the current version.  SQLite can choose to 
re-prepare a prepared statement.  The clause on a statement can be reevaluated 
or not.  Another process may choose to change the underlying data, causing 
everything to reevaluated.  My best recommendation is not to depend on any 
behaviour not made obvious by the documentation.

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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

On 22 Jul 2012, at 18:35, Keith Medcalf wrote:

Thanks for that. I can't say I understand much of that output at the moment. 
I'm just reading http://www.sqlite.org/eqp.html to try and figure it out.

Is the behaviour it will only run once guaranteed by any standard, or likely to 
change in future/previous versions of sqlite?

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

Re: [sqlite] Current Time in WHEN clause.

[Keith Medcalf]

> > (select name, max(validfrom) as mvf from aliases where validFrom <= (select 
> > strftime('%s', 'now')) group by name)
 
> I don't think that solves the problem because each time the group by query is 
> executed, your subquery will be executed again.

Not possible.  
WHERE conditions are evaluated once per row to select rows which are included 
in GROUP BY operations.  
HAVING conditions are evaluated per group to determine whether the group is a 
part of the returned result set.

** of course, not all WHERE and HAVING are necessarily executed for every row 
or group.  If one of the conditions eliminates the row/group, then no further 
constraints need evaluating, obviously **

The only case in which a WHERE condition is evaluated once per GROUP is when 
all groups only contain a single row.

In any case, the generated execution plan clearly indicates that the scalar 
subquery is only evaluated once.

Sqlite> .explain
sqlite> explain query plan select a.name, a.assignedTo, a.validFrom from 
aliases as a join (select name, max(validfrom) as mvf from
aliases where validFrom <= strftime('%s', 'now') group by name) as b on 
a.name=b.name and a.validFrom=b.mvf;
SELECT item[0] = {0:0}
   item[1] = {0:1}
   item[2] = {0:2}
FROM {0,*} = aliases (AS a)
 {1,*} = SELECT agg_flag
item[0] = AGG{2:0}
item[1] = AGG_FUNCTION:max(AGG{2:2})
 FROM {2,*} = aliases
 WHERE LE({2:2},FUNCTION:strftime(item[0] = '%s'
  item[1] = 'now'))
 GROUPBY {2:0}
 END (tabname=sqlite_subquery_53D2D0_) (AS b)
WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
END
sele  order  from  deta
  -    
1 0  0 SCAN TABLE aliases (~33 rows)
1 0  0 USE TEMP B-TREE FOR GROUP BY
0 0  1 SCAN SUBQUERY 1 AS b (~100 rows)
0 1  0 SEARCH TABLE aliases AS a USING AUTOMATIC COVERING 
INDEX (name=? AND validFrom=?) (~7 rows)
sqlite>

-vs-

sqlite> explain query plan select a.name, a.assignedTo, a.validFrom from 
aliases as a join (select name, max(validfrom) as mvf from
aliases where validFrom <= (select strftime('%s', 'now')) group by name) as b 
on a.name=b.name and a.validFrom=b.mvf;
SELECT item[0] = {0:0}
   item[1] = {0:1}
   item[2] = {0:2}
FROM {0,*} = aliases (AS a)
 {1,*} = SELECT agg_flag
item[0] = AGG{2:0}
item[1] = AGG_FUNCTION:max(AGG{2:2})
 FROM {2,*} = aliases
 WHERE LE({2:2},(SELECT FUNCTION:strftime(item[0] = '%s'
  item[1] = 'now')
 LIMIT 1
 END))
 GROUPBY {2:0}
 END (tabname=sqlite_subquery_53E4D0_) (AS b)
WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
END
sele  order  from  deta
  -    
1 0  0 SCAN TABLE aliases (~33 rows)
1 0  0 EXECUTE SCALAR SUBQUERY 2
1 0  0 USE TEMP B-TREE FOR GROUP BY
0 0  1 SCAN SUBQUERY 1 AS b (~100 rows)
0 1  0 SEARCH TABLE aliases AS a USING AUTOMATIC COVERING 
INDEX (name=? AND validFrom=?) (~7 rows)
sqlite>

as you can see, the scalar subquery is executed only once ... (if it were a 
correlated suquery, then it would be executed for each row, and the plan would 
indicate that).


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

On 22 Jul 2012, at 16:37, Pavel Ivanov wrote:

> You can execute "select strftime('%s', 'now')" first and then put its
> result into your query. 

It seems as though the opinion is that it may execute multiple times.

> But that won't work with view, of course.

I'd rather keep the alias_info view as it is because it's simple - I prefer DB 
logic to be in the DB, rather than the PHP script.

I'm toying with the idea of creating a temporary table with the current time in 
and selecting from that in the latest_alias subquery, but would prefer 
better/cleaner options.

Thanks,
Kevin Martin

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

Re: [sqlite] Current Time in WHEN clause.

[Kevin Martin]

On 22 Jul 2012, at 17:06, Keith Medcalf wrote:

> (select name, max(validfrom) as mvf from aliases where validFrom <= (select 
> strftime('%s', 'now')) group by name)

I don't think that solves the problem because each time the group by query is 
executed, your subquery will be executed again.

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

Re: [sqlite] Current Time in WHEN clause.

[Keith Medcalf]

If you want to make sure the strftime function is only called once why not say:

CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from 
aliases as a join (select name, max(validfrom) as mvf from aliases where 
validFrom <= (select strftime('%s', 'now')) group by name) as b on 
a.name=b.name and a.validFrom=b.mvf;


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Kevin Martin
> Sent: Sunday, 22 July, 2012 09:17
> To: General Discussion of SQLite Database
> Subject: [sqlite] Current Time in WHEN clause.
> 
> Hi,
> 
> I have the following schema:
> 
> > CREATE TABLE aliases (name text not null, assignedTo text, validFrom
> integer not null);
> > CREATE TABLE services (name text primary key, url text not null, type text
> not null);
> > CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from
> aliases as a join (select name, max(validfrom) as mvf from aliases where
> validFrom <= strftime('%s', 'now') group by name) as b on a.name=b.name and
> a.validFrom=b.mvf;
> > CREATE VIEW alias_info as select a.name, s.url, s.type from latest_aliases
> as a join services as s on a.assignedTo = s.name;
> 
> The aliases table maps names to services at different times. So for example
> with the following data:
> 
> > sqlite> select * from services;
> > svc1|http://somewhere|type1
> > svc2|http://somewhere.else|type1
> 
> > sqlite> select *,datetime(validFrom, 'unixepoch') from aliases;
> > env1|svc1|1342967110|2012-07-22 14:25:10
> > env1|svc2|1342967185|2012-07-22 14:26:25
> 
> I want env1 to be mapped to svc1 after 14:25:10, until 14:26:25 after which
> point I want it to be mapped to svc2.
> 
> This is done with the latest_aliases view, alias_info just joins
> latest_aliases to the services table to get the connection info.
> 
> However, I'm quite concerned about executing strftime('%s', 'now') inside the
> subquery, specifically does it execute multiple times? I don't want this to
> happen because it could cause a rare bug when the list of services returned
> is partly pre and partly post an update.
> 
> I'm trying to convince myself that the subquery in latest_aliases only
> executes once, and also that alias_info only runs latest_aliases once.
> However, I'm not doing very well at convincing myself. Can someone confirm
> this is true, or suggest a nice solution to get the current time function out
> of the when clause.
> 
> Thanks,
> Kevin Martin.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Current Time in WHEN clause.

[Pavel Ivanov]

> I'm trying to convince myself that the subquery in latest_aliases only 
> executes once, and also that alias_info only runs latest_aliases once. 
> However, I'm not doing very well at convincing myself. Can someone confirm 
> this is true, or suggest a nice solution to get the current time function out 
> of the when clause.

AFAIK, that's not true, i.e. subquery will be executed several times.
You can execute "select strftime('%s', 'now')" first and then put its
result into your query. But that won't work with view, of course.

Pavel


On Sun, Jul 22, 2012 at 11:17 AM, Kevin Martin  wrote:
> Hi,
>
> I have the following schema:
>
>> CREATE TABLE aliases (name text not null, assignedTo text, validFrom integer 
>> not null);
>> CREATE TABLE services (name text primary key, url text not null, type text 
>> not null);
>> CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from 
>> aliases as a join (select name, max(validfrom) as mvf from aliases where 
>> validFrom <= strftime('%s', 'now') group by name) as b on a.name=b.name and 
>> a.validFrom=b.mvf;
>> CREATE VIEW alias_info as select a.name, s.url, s.type from latest_aliases 
>> as a join services as s on a.assignedTo = s.name;
>
> The aliases table maps names to services at different times. So for example 
> with the following data:
>
>> sqlite> select * from services;
>> svc1|http://somewhere|type1
>> svc2|http://somewhere.else|type1
>
>> sqlite> select *,datetime(validFrom, 'unixepoch') from aliases;
>> env1|svc1|1342967110|2012-07-22 14:25:10
>> env1|svc2|1342967185|2012-07-22 14:26:25
>
> I want env1 to be mapped to svc1 after 14:25:10, until 14:26:25 after which 
> point I want it to be mapped to svc2.
>
> This is done with the latest_aliases view, alias_info just joins 
> latest_aliases to the services table to get the connection info.
>
> However, I'm quite concerned about executing strftime('%s', 'now') inside the 
> subquery, specifically does it execute multiple times? I don't want this to 
> happen because it could cause a rare bug when the list of services returned 
> is partly pre and partly post an update.
>
> I'm trying to convince myself that the subquery in latest_aliases only 
> executes once, and also that alias_info only runs latest_aliases once. 
> However, I'm not doing very well at convincing myself. Can someone confirm 
> this is true, or suggest a nice solution to get the current time function out 
> of the when clause.
>
> Thanks,
> Kevin Martin.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users