Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Fri, Sep 20, 2013 at 9:23 AM, Keith Medcalf  wrote:

>
> Nice fix Richard, and it works as you have documented.
>
> With the added [cebd6fc551] to reset iCurrentTime when the statement is
> reset, my testing indicates that if the p->iCurrentTime=0; added at line
> 570 of vdbe.c by the original patch [daf6ba413c] (change fragment
>  [56e648f5ba9a9181]) is removed, then 'now' is stable for the statement
> rather than just each step.
>
> That is, 'now' is stable from its first "access" in a statement until that
> statement is reset.  Unless you have found that it causes other issues (I
> haven't found any yet) then removing the above mentioned clearing of
> iCurrentTime during each step will result in SQLite's concept of 'now'
> being consistent with that implemented in other SQL engines and in the
> standard.
>

That's why I added the iCurrentTime clear to sqlite3_reset(), so that
commenting out the iCurrentTime reset in sqlite3_step() would cause the
behavior you describe.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] racing with date('now') (was: Select with dates)

[Keith Medcalf]

Nice fix Richard, and it works as you have documented.  

With the added [cebd6fc551] to reset iCurrentTime when the statement is reset, 
my testing indicates that if the p->iCurrentTime=0; added at line 570 of vdbe.c 
by the original patch [daf6ba413c] (change fragment  [56e648f5ba9a9181]) is 
removed, then 'now' is stable for the statement rather than just each step.

That is, 'now' is stable from its first "access" in a statement until that 
statement is reset.  Unless you have found that it causes other issues (I 
haven't found any yet) then removing the above mentioned clearing of 
iCurrentTime during each step will result in SQLite's concept of 'now' being 
consistent with that implemented in other SQL engines and in the standard.

Personally, I think that 'now' should be constant for the duration of a 
transaction across all statements executed within the same transaction -- 
however that is not what the standard says -- the standard only says that 'now' 
be stable for the duration of the execution of a single statement which appears 
to have been met if the aforesaid clearing of iCurrentTime in vdbe.c is removed.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Monday, 16 September, 2013 08:38
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] racing with date('now') (was: Select with dates)
> 
> On Mon, Sep 16, 2013 at 10:21 AM, Ryan Johnson
> wrote:
> 
> > Rhetorical question: if sqlite3's behavior were tightened up would
> anybody
> > complain? Is there any possible use case where replacing the current
> > random-ish behavior with something consistent would change an
> application?
> > Seems like the requested behavior happens on accident often enough
> that no
> > current application could rely on its failure to appear.
> >
> 
> There are perhaps 2 million applications in the wild that use SQLite, so
> it
> will be difficult to check them all. But one can easily imagine that one
> or
> more of those two million applications does something like this:
> 
>  SELECT current_timestamp, * FROM tab;
> 
> And then runs sqlite3_step() every five or ten seconds in a background
> process to fetch a new row, and expects the timestamp on each row to
> reflect the actual time of retrieval from disk.  Causing 'now' to mean
> exactly the same time for an entire SQL statement would break such
> applications.
> 
> As a compromise, the current SQLite trunk causes 'now' to be exactly the
> same for all date and time functions within a single sqlite3_step()
> call.
> 
> 
> --
> D. Richard Hipp
> d...@sqlite.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] racing with date('now') (was: Select with dates)

[Simon Slavin]

On 17 Sep 2013, at 12:32am, James K. Lowden  wrote:

> I suggest that when the observed behavior is known to be at variance
> with what is specified in the SQL standard, it should be viewed as a
> defect and as a candidate for revision.  If it also fails on
> theoretical grounds -- as when atomicity is promised but not supplied
> -- it should be added to the list of known bugs until it's rectified.  

Presumably it should be added to



or something like it.

Actually, while I feel that this is a problem with respect to CURRENT_TIME, 
etc., since Kees pointed out that this is defined in the SQL standard, I have 
no real problem with current behaviour when 'now' is used with the datetime 
functions.  Unless someone finds that /that/ is in the standard too.

Wonder whether it can be fixed in SQLite4.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[James K. Lowden]

On Mon, 16 Sep 2013 10:38:03 -0400
Richard Hipp  wrote:

> one can easily imagine that one or
> more of those two million applications does something like this:
> 
>  SELECT current_timestamp, * FROM tab;
> 
> And then runs sqlite3_step() every five or ten seconds in a background
> process to fetch a new row, and expects the timestamp on each row to
> reflect the actual time of retrieval from disk. 

It is easy to imagine.  It's also broken by design.  The simple and
correct way to get new data is to issue a new query.  Something that
runs every 5 or 10 seconds to return a single row isn't going to suffer
from re-executing the query.  

Quite often when someone on this list suggest a feature, the answer is
there are N million applications using SQLite and a nonzero
probability of breaking one if current behavior changes.  

The question arises: under what circumstances should the behavior
change anyway?  Or must every error be defended as a feature?  

While it's true that something might break, it's also unlikely the
current feature set is pareto-optimal.  That is, there are would-be
users who don't choose SQLite because of missing/broken features, and
there are existing users who are forced to know about and work around
problems that might be relied on by others.  

I suggest that when the observed behavior is known to be at variance
with what is specified in the SQL standard, it should be viewed as a
defect and as a candidate for revision.  If it also fails on
theoretical grounds -- as when atomicity is promised but not supplied
-- it should be added to the list of known bugs until it's rectified.  

Application of such a standard would guide SQLite in the direction of
standards compliance and formal correctness.  I cannot but think that
would benefit all its users, present and future.  

--jkl


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[James K. Lowden]

On Sat, 14 Sep 2013 17:19:22 +0400
Yuriy Kaminskiy  wrote:

> > Notwithstanding the timezone you want to use, 
> > 
> > explain select * 
> >   from entry 
> >  where bankdate >= date('now', 'start of month') 
> >and bankdate < date('now', 'start of month', '-1 day');
> > 
> > will generate the following code:
> 
> And there are another pitfall in this query: date('now') is *RACY*.
> That is, if you execute this query *exactly* at midnight at month
> boundary, first and second DATE() invocation may refer to *different*
> months.
> 
> And if your query return several rows, and there will be month
> boundary between sqlite3_step(), your query also can return data from
> two month.

That's a disappointment, and nonstandard behavior.  It means SELECT is
not atomic.  

The notion of "now" should not change while the query is being
processed.  "date('now', 'start of month')" should return the same
value no matter how many times it appears it the query, and no matter
how long it takes to process the results.  

--jkl

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Jean-Christophe Deschamps]

There are other ways to get a consistent 'now' across an entire SQL
statement:


Of course!

This issue is sufficiently obscure that it is not worth adding (and 
testing

and documenting and supporting forever) yet another pragma.


I personally wouldn't call it so "obscure" as it pops up regularly with 
untold consequences in a scripting language I use, but it's part of the 
programmer's responsability after all. Not a deal breaker anyway.


--
JcD 


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Mon, Sep 16, 2013 at 12:37 PM, Jean-Christophe Deschamps <
j...@antichoc.net> wrote:

> I personally wouldn't call it so "obscure" as it pops up regularly with
> untold consequences in a scripting language I use, but it's part of the
> programmer's responsability after all. Not a deal breaker anyway.
>
>
I call it "obscure" because the date and time functions have worked the way
they do for a least 10 years and last week was the first time I have heard
any complaints.  Surely if the behavior were causing any significant
problems for anyone I would have heard about it long before.


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Mon, Sep 16, 2013 at 11:14 AM, Jean-Christophe Deschamps <
j...@antichoc.net> wrote:

> Perhaps a new connection-wide "pragma freeze_now=ON;" which would freeze
> 'now' at the beginning of the next statement and unfreeze it at the next
> auto-commit or when processing a "pragma freeze_now=OFF;" would solve
> another part of the race issue.
>

There are other ways to get a consistent 'now' across an entire SQL
statement:

(1) Run "SELECT julianday('now')" separately, then bind the result into
your SQL statement in place of the 'now' strings.
(2) Enclose the date and time functions in a subquery:  SELECT (SELECT
timestamp('now')), * FROM tab;

This issue is sufficiently obscure that it is not worth adding (and testing
and documenting and supporting forever) yet another pragma.


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Jean-Christophe Deschamps]

There are perhaps 2 million applications in the wild that use SQLite, 
so it
will be difficult to check them all. But one can easily imagine that 
one or

more of those two million applications does something like this:

 SELECT current_timestamp, * FROM tab;

And then runs sqlite3_step() every five or ten seconds in a background
process to fetch a new row, and expects the timestamp on each row to
reflect the actual time of retrieval from disk.  Causing 'now' to mean
exactly the same time for an entire SQL statement would break such
applications.

As a compromise, the current SQLite trunk causes 'now' to be exactly the
same for all date and time functions within a single sqlite3_step() call.


Perhaps a new connection-wide "pragma freeze_now=ON;" which would 
freeze 'now' at the beginning of the next statement and unfreeze it at 
the next auto-commit or when processing a "pragma freeze_now=OFF;" 
would solve another part of the race issue.


Applications relying on the old behavior don't change and new ones 
where the possibility of race is dangerous can avoid it at little cost.


Still some multi-threaded apps would need to consider the implications, 
but we all know what you Richard think about threads!


--
JcD 


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Richard Hipp]

On Mon, Sep 16, 2013 at 10:21 AM, Ryan Johnson
wrote:

> Rhetorical question: if sqlite3's behavior were tightened up would anybody
> complain? Is there any possible use case where replacing the current
> random-ish behavior with something consistent would change an application?
> Seems like the requested behavior happens on accident often enough that no
> current application could rely on its failure to appear.
>

There are perhaps 2 million applications in the wild that use SQLite, so it
will be difficult to check them all. But one can easily imagine that one or
more of those two million applications does something like this:

 SELECT current_timestamp, * FROM tab;

And then runs sqlite3_step() every five or ten seconds in a background
process to fetch a new row, and expects the timestamp on each row to
reflect the actual time of retrieval from disk.  Causing 'now' to mean
exactly the same time for an entire SQL statement would break such
applications.

As a compromise, the current SQLite trunk causes 'now' to be exactly the
same for all date and time functions within a single sqlite3_step() call.


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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Ryan Johnson]

On 15/09/2013 2:23 PM, Yuriy Kaminskiy wrote:

Stephan Beal wrote:

On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy  wrote:


Sure, there can be several way to interpret CURRENT_* and *('now').
However,
some of them can be useful (transaction, statement), and others (step) -
cannot
be. And some (sub-expression, the way it "works" currently) are purely
insane.


i've been following this list since 2006 or 2007 and i can't remember

Oh, yes, yes, "I was on debian [...] list since [...] and can't remember anyone
complaining about broken RNG for two years". So what?

And, by the way, I already complained about this behavior on this list in the
beginning of 2012 year.


anyone every complaining about the current behaviour before. If the
behaviour bothers you, use a user-defined function which provides the

It does not bother *me* - I can happily live with knowledge that SQLite
CURRENT_*/*('now') is broken by design and should not be used ever. It should
bother people that use sqlite for something serious.
Rhetorical question: if sqlite3's behavior were tightened up would 
anybody complain? Is there any possible use case where replacing the 
current random-ish behavior with something consistent would change an 
application? Seems like the requested behavior happens on accident often 
enough that no current application could rely on its failure to appear.


$0.02
Ryan

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Ryan Johnson]

On 15/09/2013 3:36 AM, Petite Abeille wrote:

On Sep 15, 2013, at 12:53 AM, Kees Nuyt  wrote:


3) If an SQL-statement generally contains more than one reference
   to one or more s, then all such ref-
   erences are effectively evaluated simultaneously.

FWIW, Oracle concurs:

"All of the datetime functions that return current system datetime information, such 
as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated once for each 
SQL statement, regardless how many times they are referenced in that statement."
I'm pretty sure that anything weaker breaks [the illusion of] 
serializability, by giving users a way to see that their transactions 
did not execute in the order they appeared to:


A: select CURRENT_TIMESTAMP; update foo set v=1 where k=0; select * from 
foo;
B: update foo set v=2 where k=0; select * from foo; select 
CURRENT_TIMESTAMP;


Whether that matters in practice, I wouldn't know...

Ryan

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Petite Abeille]

On Sep 15, 2013, at 10:32 PM, "Keith Medcalf"  wrote:

> On the other hand, if one knows that the value of 'now' is not stable then 
> one can always bind a parameter with the appropriate value set from the host 
> language 

Or write it down somewhere once (i.e temp table), or evaluate it once (i.e 
subquery).

Either ways, much of a pain as it's now. 

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Keith Medcalf]

You are correct.

Even though the standard says 'statement stability', I think that is less 
useful than transaction stability.  I personally think a reference to 'now' 
should be stable throughout a transaction (a static value set when 'now' first 
accessed in a transaction and  cleared on a commit or rollback [of the outer 
transaction only -- not changed on interim savepoint operations]) would be the 
most useful and logically consistent implementation.

If transactions are disabled then no static value is saved for the transaction 
and the behavior stays as it is.

This would require that the value of 'now' be cached in the transaction 
structure I suppose ...

On the other hand, if one knows that the value of 'now' is not stable then one 
can always bind a parameter with the appropriate value set from the host 
language (which is what I usually do anyway, even with other SQL 
implementations that do claim to have either statement or transaction stable 
concepts of 'now' since that is usually what one wants anyway).

> Keith Medcalf wrote:
> >> In C there are local variables, where you can save result of impure
> >> functions when it is important. There are no local variables in SQL
> >> - with even more extreme example shown in E.Pasma message nearby -
> >> `SELECT strftime('%f') AS q FROM t WHERE q <> q`;
> >> oh, by the way, `SELECT CURRENT_TIME AS q FROM t WHERE q <> q`
> >> trigger that bug too, I've just checked (and it took less than 2
> >> seconds to trigger).
> >
> > That is version specific.  What version of SQLite are you using?  The
> current version optimizes out the clause q <> q thusly:
> 
> No, it is NOT version specific.
> 
> strftime vs. CURRENT_TIME triggers racing differently (strftime trigger
> in inner
> loop [and return *random* rows, easily triggered in shell with big
> enough
> table], CURRENT_TIME trigger in outer loop [and *randomly* return all
> rows; not
> easy to trigger in shell, but still it is triggered by my perl script
> without
> much problem]), but still triggers it.
> 
> (By the way, better optimizer should've moved strftime out of inner loop
> as
> well; and even better optimizer should've eliminated all repeated calls
> for pure
> functions with constant arguments; but that's just "missing optimization
> opportunity/missing feature", not a "real bug"; and even if that
> optimization
> was implemented, it still would not have fixed racing between
> DATE('now',$foo)
> and DATE('now',$bar), as in OP's query).
> 
> > sqlite> .explain
> > sqlite> explain select value, current_time as q from x where q <> q
> and value < 10;
> > SELECT item[0] = {0:0}
> >item[1] = FUNCTION:current_time() AS q
> > FROM {0,*} = x
> > WHERE AND(REGISTER(1),LT({0:0},REGISTER(4)))
> > END
> > addr  opcode p1p2p3p4 p5  comment
> >   -        -  --  
> -
> > 0 Trace  0 0 000
> > 1 Function   0 0 2 current_time(0)  00
> > 2 Function   0 0 3 current_time(0)  00
> 
> ...And as you see, current_time function evaluated *many* times (and
> there are
> no caching *inside* of current_time function, in *any* sqlite version).
> 
> > 3 Ne 3 1 272
> > 4 Integer10  4 000
> > 5 Goto   0 18000
> > 6 VOpen  0 0 0 vtab:6E06F0:50B800  00
> > 7 Copy   4 7 000
> > 8 Integer4 5 000
> > 9 Integer1 6 000
> > 10VFilter0 16500
> > 11IfNot  1 15100
> > 12VColumn0 0 800
> > 13Function   0 0 9 current_time(0)  00
> > 14ResultRow  8 2 000
> > 15VNext  0 11000
> > 16Close  0 0 000
> > 17Halt   0 0 000
> > 18Transaction0 0 000
> > 19VerifyCookie   0 1 000
> > 20Goto   0 6 000
> >
> > So you will either get all rows or none.
> 
> And? It should ALWAYS return NONE. It does not.
> 
> ___
> 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] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
>> In C there are local variables, where you can save result of impure
>> functions when it is important. There are no local variables in SQL 
>> - with even more extreme example shown in E.Pasma message nearby - 
>> `SELECT strftime('%f') AS q FROM t WHERE q <> q`; 
>> oh, by the way, `SELECT CURRENT_TIME AS q FROM t WHERE q <> q` 
>> trigger that bug too, I've just checked (and it took less than 2
>> seconds to trigger).
> 
> That is version specific.  What version of SQLite are you using?  The current 
> version optimizes out the clause q <> q thusly:

No, it is NOT version specific.

strftime vs. CURRENT_TIME triggers racing differently (strftime trigger in inner
loop [and return *random* rows, easily triggered in shell with big enough
table], CURRENT_TIME trigger in outer loop [and *randomly* return all rows; not
easy to trigger in shell, but still it is triggered by my perl script without
much problem]), but still triggers it.

(By the way, better optimizer should've moved strftime out of inner loop as
well; and even better optimizer should've eliminated all repeated calls for pure
functions with constant arguments; but that's just "missing optimization
opportunity/missing feature", not a "real bug"; and even if that optimization
was implemented, it still would not have fixed racing between DATE('now',$foo)
and DATE('now',$bar), as in OP's query).

> sqlite> .explain
> sqlite> explain select value, current_time as q from x where q <> q and value 
> < 10;
> SELECT item[0] = {0:0}
>item[1] = FUNCTION:current_time() AS q
> FROM {0,*} = x
> WHERE AND(REGISTER(1),LT({0:0},REGISTER(4)))
> END
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Trace  0 0 000
> 1 Function   0 0 2 current_time(0)  00
> 2 Function   0 0 3 current_time(0)  00

...And as you see, current_time function evaluated *many* times (and there are
no caching *inside* of current_time function, in *any* sqlite version).

> 3 Ne 3 1 272
> 4 Integer10  4 000
> 5 Goto   0 18000
> 6 VOpen  0 0 0 vtab:6E06F0:50B800  00
> 7 Copy   4 7 000
> 8 Integer4 5 000
> 9 Integer1 6 000
> 10VFilter0 16500
> 11IfNot  1 15100
> 12VColumn0 0 800
> 13Function   0 0 9 current_time(0)  00
> 14ResultRow  8 2 000
> 15VNext  0 11000
> 16Close  0 0 000
> 17Halt   0 0 000
> 18Transaction0 0 000
> 19VerifyCookie   0 1 000
> 20Goto   0 6 000
> 
> So you will either get all rows or none.

And? It should ALWAYS return NONE. It does not.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Keith Medcalf]

> In C there are local variables, where you can save result of impure
> functions when it is important. There are no local variables in SQL 
> - with even more extreme example shown in E.Pasma message nearby - 
> `SELECT strftime('%f') AS q FROM t WHERE q <> q`; 
> oh, by the way, `SELECT CURRENT_TIME AS q FROM t WHERE q <> q` 
> trigger that bug too, I've just checked (and it took less than 2
> seconds to trigger).

That is version specific.  What version of SQLite are you using?  The current 
version optimizes out the clause q <> q thusly:

sqlite> .explain
sqlite> explain select value, current_time as q from x where q <> q and value < 
10;
SELECT item[0] = {0:0}
   item[1] = FUNCTION:current_time() AS q
FROM {0,*} = x
WHERE AND(REGISTER(1),LT({0:0},REGISTER(4)))
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Function   0 0 2 current_time(0)  00
2 Function   0 0 3 current_time(0)  00
3 Ne 3 1 272
4 Integer10  4 000
5 Goto   0 18000
6 VOpen  0 0 0 vtab:6E06F0:50B800  00
7 Copy   4 7 000
8 Integer4 5 000
9 Integer1 6 000
10VFilter0 16500
11IfNot  1 15100
12VColumn0 0 800
13Function   0 0 9 current_time(0)  00
14ResultRow  8 2 000
15VNext  0 11000
16Close  0 0 000
17Halt   0 0 000
18Transaction0 0 000
19VerifyCookie   0 1 000
20Goto   0 6 000

So you will either get all rows or none.





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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Stephan Beal wrote:
> On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy  wrote:
> 
>> Sure, there can be several way to interpret CURRENT_* and *('now').
>> However,
>> some of them can be useful (transaction, statement), and others (step) -
>> cannot
>> be. And some (sub-expression, the way it "works" currently) are purely
>> insane.
>>
> 
> i've been following this list since 2006 or 2007 and i can't remember

Oh, yes, yes, "I was on debian [...] list since [...] and can't remember anyone
complaining about broken RNG for two years". So what?

And, by the way, I already complained about this behavior on this list in the
beginning of 2012 year.

> anyone every complaining about the current behaviour before. If the
> behaviour bothers you, use a user-defined function which provides the

It does not bother *me* - I can happily live with knowledge that SQLite
CURRENT_*/*('now') is broken by design and should not be used ever. It should
bother people that use sqlite for something serious.

> per-db/transaction/whatever behaviour your need. You could have implemented

I have not found any way to associate user-defined data with transaction in
sqlite API.

> it in the time you've expended bemoaning the current (well established, if
> perhaps fundamentally flawed) behaviour.
> 
> 
> where `2*2 <> 4`; using them them in product targeted to to general public
>> would
>> be insane; using them *randomly* (as it happens with CURRENT_TIME <>
>> CURRENT_TIME) - beyond insane.
>>
> 
> foo() == foo()
> 
> is never guaranteed to be true unless foo() is a pure function with no
> inputs. i consider the current behaviour to be correct. It would never

And functions in SQL are *expected* to be pure.

> occur to me to compare CURRENT_TIME to itself because it is, deep down

Comparing with itself just a method to *easily demonstrate* this bug.

In real-world, this bug affect any query where CURRENT_TIME used more than once.
E.g. OP's query - `...WHERE day BETWEEN DATE('now',...) AND DATE('now',...)`.
This *real-world* query is affected. Even through it does not *directly* compare
CURRENT_TIME with CURRENT_TIME.

> inside, a C function call which uses time-dependent, system-level state.
> i.e. it's a perfect candidate for races. So avoid them, in the same way
> that nobody should ever (in C) expect (time(0) == time(0)) to match 100% of
> the ... time.

In C there are local variables, where you can save result of impure functions
when it is important. There are no local variables in SQL - with even more
extreme example shown in E.Pasma message nearby - `SELECT strftime('%f') AS q
FROM t WHERE q <> q`; oh, by the way, `SELECT CURRENT_TIME AS q FROM t WHERE q
<> q` trigger that bug too, I've just checked (and it took less than 2 seconds
to trigger).

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Simon Slavin]

On 15 Sep 2013, at 12:58pm, Yuriy Kaminskiy  wrote:

> Wow. Are you *REALLY* arguing that
>  SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME;
> that randomly (!) returning rows any less broken than
>  SELECT * FROM t WHERE 2*2 <> 4;
> also randomly returning rows?

I was, because the word 'current' means 'now' and 'now' changes with time.  
However, Kees found a reference in the SQL standard that defines CURRENT_TIME, 
and shows that it doesn't really mean 'CURRENT' _TIME, it means something more 
like STATEMENT_TIME and must be constant throughout any one particular 
statement.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[E.Pasma]

Op 15 sep 2013, om 14:05 heeft Stephan Beal het volgende geschreven:

On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy   
wrote:



Sure, there can be several way to interpret CURRENT_* and *('now').
However,
some of them can be useful (transaction, statement), and others  
(step) -

cannot
be. And some (sub-expression, the way it "works" currently) are  
purely

insane.



i've been following this list since 2006 or 2007 and i can't remember
anyone every complaining about the current behaviour before. If the
behaviour bothers you, use a user-defined function which provides the
per-db/transaction/whatever behaviour your need. You could have  
implemented
it in the time you've expended bemoaning the current (well  
established, if

perhaps fundamentally flawed) behaviour.


where `2*2 <> 4`; using them them in product targeted to to general  
public

would
be insane; using them *randomly* (as it happens with CURRENT_TIME <>
CURRENT_TIME) - beyond insane.



foo() == foo()

is never guaranteed to be true unless foo() is a pure function with no
inputs. i consider the current behaviour to be correct. It would never
occur to me to compare CURRENT_TIME to itself because it is, deep down
inside, a C function call which uses time-dependent, system-level  
state.
i.e. it's a perfect candidate for races. So avoid them, in the same  
way
that nobody should ever (in C) expect (time(0) == time(0)) to match  
100% of

the ... time.

Although nobody has ever ccomplained it is good to be aware of this  
issue/feature.
Combined with other features, like the same expression being  
recalculated if referred to more than once, you may see unexpected  
results.

I got this on a table of about 1.000 rows:

sqlite> SELECT strftime('%f') AS q FROM t WHERE q <> q;
52.407
52.411
52.413
52.414
sqlite>

Still I can not think of a case where this would be a problem.




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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Stephan Beal]

On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy  wrote:

> Sure, there can be several way to interpret CURRENT_* and *('now').
> However,
> some of them can be useful (transaction, statement), and others (step) -
> cannot
> be. And some (sub-expression, the way it "works" currently) are purely
> insane.
>

i've been following this list since 2006 or 2007 and i can't remember
anyone every complaining about the current behaviour before. If the
behaviour bothers you, use a user-defined function which provides the
per-db/transaction/whatever behaviour your need. You could have implemented
it in the time you've expended bemoaning the current (well established, if
perhaps fundamentally flawed) behaviour.


where `2*2 <> 4`; using them them in product targeted to to general public
> would
> be insane; using them *randomly* (as it happens with CURRENT_TIME <>
> CURRENT_TIME) - beyond insane.
>

foo() == foo()

is never guaranteed to be true unless foo() is a pure function with no
inputs. i consider the current behaviour to be correct. It would never
occur to me to compare CURRENT_TIME to itself because it is, deep down
inside, a C function call which uses time-dependent, system-level state.
i.e. it's a perfect candidate for races. So avoid them, in the same way
that nobody should ever (in C) expect (time(0) == time(0)) to match 100% of
the ... time.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy  wrote:
> 
>> ... and I'd call even that difference between CURRENT_* and *('now') rather
>> "query optimizer artifact" rather than "documented feature one can rely 
>> upon".
>> Anyway, one way or other, it is BROKEN.
> 
> I would agree with you if the labels were different.  But 'CURRENT_TIME' 
> means 'current'.  If those keywords were STATEMENT_TIME, STATEMENT_DATE and 
> STATEMENT_TIMESTAMP then that behaviour would definitely be wrong.

Wow. Are you *REALLY* arguing that
  SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME;
that randomly (!) returning rows any less broken than
  SELECT * FROM t WHERE 2*2 <> 4;
also randomly returning rows?

Sure, there can be several way to interpret CURRENT_* and *('now'). However,
some of them can be useful (transaction, statement), and others (step) - cannot
be. And some (sub-expression, the way it "works" currently) are purely insane.

IMO, 'now' evaluation *MUST* be consistent within statement (think about query
from OP - `... WHERE day BETWEEN date('now','start of month') AND date('now',
'start of month','+1 month')` - unless you have statement-level consistency, it
*randomly* will cover *one* or *two* months).

And there are good arguments that they *should* be consistent within transaction
(point of transaction is that you have *consistent* view of database; evaluating
CURRENT_*/'now' more than once within transaction, obviously, provide
*inconsistent* view).

And while we are talking about "procedural elements", obviously, volatile global
constants and ​impure functions are *more* from "procedural world" than from
"SQL world" (constants are expected to be constant, function from constant
arguments are expected to return same value each time, query optimizer should be
free to reorder and eliminate function calls, flatten queries, reorder joins,
etc; you cannot do that with "volatile constants" or impure functions).

PS sure, if you ask in uni's math dept, you'll find some interpretation of math
where `2*2 <> 4`; using them them in product targeted to to general public would
be insane; using them *randomly* (as it happens with CURRENT_TIME <>
CURRENT_TIME) - beyond insane.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Petite Abeille]

On Sep 15, 2013, at 12:53 AM, Kees Nuyt  wrote:

> 3) If an SQL-statement generally contains more than one reference
>   to one or more s, then all such ref-
>   erences are effectively evaluated simultaneously. 

FWIW, Oracle concurs:

"All of the datetime functions that return current system datetime information, 
such as SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, and so forth, are evaluated 
once for each SQL statement, regardless how many times they are referenced in 
that statement."

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] racing with date('now') (was: Select with dates)

[Kees Nuyt]

On Sat, 14 Sep 2013 21:56:23 +0400, Yuriy Kaminskiy 
wrote:

>
>PS from postgresql documentation:
>=== cut
>http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
>===
>Since these functions return the start time of the current transaction, their
>values do not change during the transaction. This is considered a feature: the
>intent is to allow a single transaction to have a consistent notion of the
>"current" time, so that multiple modifications within the same transaction bear
>the same time stamp.
>=== cut ===
>*That's* way to go.

I agree.
See also:
http://troels.arvin.dk/db/rdbms/#functions-LOCALTIMESTAMP
and
http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt 

page 139, 6.8, General Rules :

3) If an SQL-statement generally contains more than one reference
   to one or more s, then all such ref-
   erences are effectively evaluated simultaneously. 

Other sources (Interbase) add even more rules:
Any statements triggered by a SQL statement (eg: Triggers or Stored
Procedures) will evaluate CURRENT_TIME to be the same value. This value
persists until the end of the SQL statement.

Now, the discussion can be, did the standard really mean a constant
timestamp for a statement or for a transaction?

I would vote for the latter, but then another value should be available
to get the systemtime, for ewxample to be able to register the start-
and end time of a transaction. Perhaps that should be the difference
between CURRENT_TIME and datetime('now')?

just my two cents...

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Simon Slavin]

On 14 Sep 2013, at 10:58pm, Simon Slavin  wrote:

> On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy  wrote:
> 
>> ... and I'd call even that difference between CURRENT_* and *('now') rather
>> "query optimizer artifact" rather than "documented feature one can rely 
>> upon".
>> Anyway, one way or other, it is BROKEN.
> 
> I would agree with you if the labels were different.  But 'CURRENT_TIME' 
> means 'current'. If those keywords were STATEMENT_TIME, STATEMENT_DATE and 
> STATEMENT_TIMESTAMP then that behaviour would definitely be wrong.

The answer, as usual, is to use your programming language to do non-database 
things.  This is one of the reasons why I don't like seeing people try to use 
the procedural features of a DBMS to write software complicated enough for 
normal users.  A GUI database manager for a DBMS is a wonderful tool for a 
programmer.  It's not an appropriate solution for users on its own.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Simon Slavin]

On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy  wrote:

> ... and I'd call even that difference between CURRENT_* and *('now') rather
> "query optimizer artifact" rather than "documented feature one can rely upon".
> Anyway, one way or other, it is BROKEN.

I would agree with you if the labels were different.  But 'CURRENT_TIME' means 
'current'.  If those keywords were STATEMENT_TIME, STATEMENT_DATE and 
STATEMENT_TIMESTAMP then that behaviour would definitely be wrong.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Keith Medcalf wrote:
> You can easily reproduce this problem if you switch unit from month to
> millisecond, e.g.
> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
> will non-deterministically return rows.
> IMO, correct [= least surprise] behavior should be "timestamp used for
> 'now' should cached on first row step, and reused in all following calls
> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy 
> :-|.
 That would require a change to SQLite itself.  Without doing that I
>>> believe this will solve those problems by using only one 'now', and also
>>> only running each date() call once (even my previous solutions could
>>> suffer from race conditions since they retrieved 'now' multiple times):
 sqlite> explain select *
...>   from entry,
...>(select bdate, (select date(bdate, '+1 month'))
>>> as edate
...>   From (select date('now', 'localtime', 'start
>>> of month') as bdate) as ttemp limit 1) as tstamp
...>  where bankdate >= bdate
...>and bankdate < edate;
>>> Unfortunately, it is not only extremely inconvenient to rewrite queries
>>> this
>>> way, but also this workaround relies on current implementation of query
>>> optimizer. Next version may decide to e.g. flatten query, and break
>>> assumption
>>> that inner date() will be called only once.
>>>
 It is a bit complicated though.  It would be much simpler to do the
>>> date calculation in the host language and pass the boundary values as
>>> parameters to the query.
>>>
>>> In other words, "The way it is implemented currently, sqlite DATE()
>>> function
>>> considered harmful and should not be used." Huh.
>> Not at all.  They may be quite useful for formatting output.  That the 
>> function date('now') returns the date now ought to be expected.  If you 
>> wanted the date at the start of the transaction, one ought to reasonably 
>> expect to call date('start of transaction').
> 
> That `SELECT date('now'), date('now')` can randomly return mismatching results
> is most certainly NOT what anyone would expect.
> 
>> CURRENT_TIMESTAMP is a static timestamp, but you still have the same issue 
>> if you wish to eliminate the repetitive calls to the date function.  If that 
>> doesn't bother you then you ought to be able to replace 'now' with 
>> current_timestamp.
> 
> It *should be* static timestamp. But it is NOT in sqlite.
> 
>>> And same problem: some next version of query optimizer may decide to
>>> change the way this query will be executed.
>> Perhaps, and that is why you have to clearly declare what you want in your 
>> select statement.
> 
> Unfortunately, there are no way to do this in sqlite.
> 
>>> PS from postgresql documentation:
>>> === cut
>>> http://www.postgresql.org/docs/9.3/static/functions-
>>> datetime.html#FUNCTIONS-DATETIME-CURRENT
>>> ===
>>> Since these functions return the start time of the current transaction,
>>> their
>>> values do not change during the transaction. This is considered a
>>> feature: the
>>> intent is to allow a single transaction to have a consistent notion of
>>> the
>>> "current" time, so that multiple modifications within the same
>>> transaction bear
>>> the same time stamp.
>>> === cut ===
>>> *That's* way to go.
>> Well, that is one way to go.  SQLite goes that way too.
>> Instead of asking for datetime('now') which returns the current 'nowness' 
>> value, you can use CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE which is a 
>> static value probably representing the first time is was called in the 
>> transaction (or perhaps statement).
> 
> Unfortunately, sqlite does NOT go this way. Repeated calls to CURRENT_DATE/​
> CURRENT_TIME within same statement still can return *different* results on
> date/time boundary. Race window is smaller, but still present. Enjoy:
> 
> #!/usr/bin/perl
> use DBI;
> #DBI -> trace( 1 );
> my $dbh = DBI->connect( "dbi:SQLite:dbname=/tmp/t.db3","","" );
> $dbh->do("CREATE TABLE IF NOT EXISTS t (i INTEGER)");
> $dbh->do( "begin immediate" );
> unless(($dbh->selectrow_array("SELECT COUNT(*) FROM t"))) {
> $dbh->do("INSERT INTO t VALUES (0)");
> $dbh->do("INSERT INTO t VALUES (1)");
> $dbh->do("INSERT INTO t VALUES (2)");
> $dbh->do("INSERT INTO t VALUES (3)");
> $dbh->do("INSERT INTO t VALUES (4)");
> }
> $dbh->do("commit");
> my $sth = $dbh->prepare( << '__E__' );
> SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME
> __E__
> ### SELECT * FROM t WHERE strftime('%f') <> strftime('%f')
> $dbh-> do( "begin" );
> my $i = 0;
> while(1) {
> $sth -> execute;
> if (my $row = $sth->fetch) {
>   print "Got bug: $sth->{Statement} returned @$row\n";
>   last;
> }
> $i++;
> }
> print "$i iterations\n";
> $sth->finish;
> $dbh->do("commit");
> __END__
> 
> (fwiw, only difference with strftime('%f') variant: when it hit bug,
> strftime('%f') variant 

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
 You can easily reproduce this problem if you switch unit from month to
 millisecond, e.g.
 SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
 will non-deterministically return rows.
> 
 IMO, correct [= least surprise] behavior should be "timestamp used for
 'now' should cached on first row step, and reused in all following calls
 [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
> 
>>> That would require a change to SQLite itself.  Without doing that I
>> believe this will solve those problems by using only one 'now', and also
>> only running each date() call once (even my previous solutions could
>> suffer from race conditions since they retrieved 'now' multiple times):
>>> sqlite> explain select *
>>>...>   from entry,
>>>...>(select bdate, (select date(bdate, '+1 month'))
>> as edate
>>>...>   From (select date('now', 'localtime', 'start
>> of month') as bdate) as ttemp limit 1) as tstamp
>>>...>  where bankdate >= bdate
>>>...>and bankdate < edate;
>> Unfortunately, it is not only extremely inconvenient to rewrite queries
>> this
>> way, but also this workaround relies on current implementation of query
>> optimizer. Next version may decide to e.g. flatten query, and break
>> assumption
>> that inner date() will be called only once.
>>
>>> It is a bit complicated though.  It would be much simpler to do the
>> date calculation in the host language and pass the boundary values as
>> parameters to the query.
>>
>> In other words, "The way it is implemented currently, sqlite DATE()
>> function
>> considered harmful and should not be used." Huh.
> 
> Not at all.  They may be quite useful for formatting output.  That the 
> function date('now') returns the date now ought to be expected.  If you 
> wanted the date at the start of the transaction, one ought to reasonably 
> expect to call date('start of transaction').

That `SELECT date('now'), date('now')` can randomly return mismatching results
is most certainly NOT what anyone would expect.

> CURRENT_TIMESTAMP is a static timestamp, but you still have the same issue if 
> you wish to eliminate the repetitive calls to the date function.  If that 
> doesn't bother you then you ought to be able to replace 'now' with 
> current_timestamp.

It *should be* static timestamp. But it is NOT in sqlite.

>> And same problem: some next version of query optimizer may decide to
>> change the way this query will be executed.
> 
> Perhaps, and that is why you have to clearly declare what you want in your 
> select statement.

Unfortunately, there are no way to do this in sqlite.

>> PS from postgresql documentation:
>> === cut
>> http://www.postgresql.org/docs/9.3/static/functions-
>> datetime.html#FUNCTIONS-DATETIME-CURRENT
>> ===
>> Since these functions return the start time of the current transaction,
>> their
>> values do not change during the transaction. This is considered a
>> feature: the
>> intent is to allow a single transaction to have a consistent notion of
>> the
>> "current" time, so that multiple modifications within the same
>> transaction bear
>> the same time stamp.
>> === cut ===
>> *That's* way to go.
> 
> Well, that is one way to go.  SQLite goes that way too.
> Instead of asking for datetime('now') which returns the current 'nowness' 
> value, you can use CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE which is a 
> static value probably representing the first time is was called in the 
> transaction (or perhaps statement).

Unfortunately, sqlite does NOT go this way. Repeated calls to CURRENT_DATE/​
CURRENT_TIME within same statement still can return *different* results on
date/time boundary. Race window is smaller, but still present. Enjoy:

#!/usr/bin/perl
use DBI;
#DBI -> trace( 1 );
my $dbh = DBI->connect( "dbi:SQLite:dbname=/tmp/t.db3","","" );
$dbh->do("CREATE TABLE IF NOT EXISTS t (i INTEGER)");
$dbh->do( "begin immediate" );
unless(($dbh->selectrow_array("SELECT COUNT(*) FROM t"))) {
$dbh->do("INSERT INTO t VALUES (0)");
$dbh->do("INSERT INTO t VALUES (1)");
$dbh->do("INSERT INTO t VALUES (2)");
$dbh->do("INSERT INTO t VALUES (3)");
$dbh->do("INSERT INTO t VALUES (4)");
}
$dbh->do("commit");
my $sth = $dbh->prepare( << '__E__' );
SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME
__E__
### SELECT * FROM t WHERE strftime('%f') <> strftime('%f')
$dbh-> do( "begin" );
my $i = 0;
while(1) {
$sth -> execute;
if (my $row = $sth->fetch) {
print "Got bug: $sth->{Statement} returned @$row\n";
last;
}
$i++;
}
print "$i iterations\n";
$sth->finish;
$dbh->do("commit");
__END__

(fwiw, only difference with strftime('%f') variant: when it hit bug,
strftime('%f') variant will randomly return one row, but CURRENT_TIME variant
randomly returns *all* rows).

___
sqlite-users mailing list

Re: [sqlite] racing with date('now') (was: Select with dates)

[Keith Medcalf]

> >> You can easily reproduce this problem if you switch unit from month to
> >> millisecond, e.g.
> >> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
> >> will non-deterministically return rows.

> >> IMO, correct [= least surprise] behavior should be "timestamp used for
> >> 'now' should cached on first row step, and reused in all following calls
> >> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.

> > That would require a change to SQLite itself.  Without doing that I
> believe this will solve those problems by using only one 'now', and also
> only running each date() call once (even my previous solutions could
> suffer from race conditions since they retrieved 'now' multiple times):
> >
> > sqlite> explain select *
> >...>   from entry,
> >...>(select bdate, (select date(bdate, '+1 month'))
> as edate
> >...>   From (select date('now', 'localtime', 'start
> of month') as bdate) as ttemp limit 1) as tstamp
> >...>  where bankdate >= bdate
> >...>and bankdate < edate;
> 
> Unfortunately, it is not only extremely inconvenient to rewrite queries
> this
> way, but also this workaround relies on current implementation of query
> optimizer. Next version may decide to e.g. flatten query, and break
> assumption
> that inner date() will be called only once.
> 
> > It is a bit complicated though.  It would be much simpler to do the
> date calculation in the host language and pass the boundary values as
> parameters to the query.
> 
> In other words, "The way it is implemented currently, sqlite DATE()
> function
> considered harmful and should not be used." Huh.

Not at all.  They may be quite useful for formatting output.  That the function 
date('now') returns the date now ought to be expected.  If you wanted the date 
at the start of the transaction, one ought to reasonably expect to call 
date('start of transaction').

CURRENT_TIMESTAMP is a static timestamp, but you still have the same issue if 
you wish to eliminate the repetitive calls to the date function.  If that 
doesn't bother you then you ought to be able to replace 'now' with 
current_timestamp.

> And same problem: some next version of query optimizer may decide to
> change the way this query will be executed.

Perhaps, and that is why you have to clearly declare what you want in your 
select statement.
 
> PS from postgresql documentation:
> === cut
> http://www.postgresql.org/docs/9.3/static/functions-
> datetime.html#FUNCTIONS-DATETIME-CURRENT
> ===
> Since these functions return the start time of the current transaction,
> their
> values do not change during the transaction. This is considered a
> feature: the
> intent is to allow a single transaction to have a consistent notion of
> the
> "current" time, so that multiple modifications within the same
> transaction bear
> the same time stamp.
> === cut ===
> *That's* way to go.

Well, that is one way to go.  SQLite goes that way too.  Instead of asking for 
datetime('now') which returns the current 'nowness' value, you can use 
CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE which is a static value probably 
representing the first time is was called in the transaction (or perhaps 
statement).




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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
>> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said:
>> Keith Medcalf wrote:
 On Thu, 12 Sep 2013 14:01:04 +0100
 Simon Davies  wrote:

> Why not
> SELECT * FROM "entry" WHERE
>bankdate >= date('now','start of month')
>   AND bankdate < date('now','start of month','+1 month')
 The half-open interval strikes again!  :-)
>>> And you are using UTC ...
>>>
>>> Notwithstanding the timezone you want to use,
>>>
>>> explain select *
>>>   from entry
>>>  where bankdate >= date('now', 'start of month')
>>>and bankdate < date('now', 'start of month', '-1 day');
>>>
>>> will generate the following code:
>> And there are another pitfall in this query: date('now') is *RACY*. That
>> is, if you execute this query *exactly* at midnight at month boundary, first
>> and second DATE() invocation may refer to *different* months.
>  
>> And if your query return several rows, and there will be month boundary
>> between sqlite3_step(), your query also can return data from two month.
>  
>> You can easily reproduce this problem if you switch unit from month to
>> millisecond, e.g.
>> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
>> will non-deterministically return rows.
>  
>> IMO, correct [= least surprise] behavior should be "timestamp used for
>> 'now' should cached on first row step, and reused in all following calls
>> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
> 
> That would require a change to SQLite itself.  Without doing that I believe 
> this will solve those problems by using only one 'now', and also only running 
> each date() call once (even my previous solutions could suffer from race 
> conditions since they retrieved 'now' multiple times):
> 
> sqlite> explain select *
>...>   from entry,
>...>(select bdate, (select date(bdate, '+1 month')) as 
> edate
>...>   From (select date('now', 'localtime', 'start of 
> month') as bdate) as ttemp limit 1) as tstamp
>...>  where bankdate >= bdate
>...>and bankdate < edate;

Unfortunately, it is not only extremely inconvenient to rewrite queries this
way, but also this workaround relies on current implementation of query
optimizer. Next version may decide to e.g. flatten query, and break assumption
that inner date() will be called only once.

> It is a bit complicated though.  It would be much simpler to do the date 
> calculation in the host language and pass the boundary values as parameters 
> to the query.

In other words, "The way it is implemented currently, sqlite DATE() function
considered harmful and should not be used." Huh.

> Of course, you could also do the following:
> 
> Select * from entry where bankdate like (select substr(date('now', 
> 'localtime'), 7) + '%');
> 
> But of course this will always do a table/index scan that cannot be optimized 
> away ...

And same problem: some next version of query optimizer may decide to change the
way this query will be executed.

PS from postgresql documentation:
=== cut
http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
===
Since these functions return the start time of the current transaction, their
values do not change during the transaction. This is considered a feature: the
intent is to allow a single transaction to have a consistent notion of the
"current" time, so that multiple modifications within the same transaction bear
the same time stamp.
=== cut ===
*That's* way to go.

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

Re: [sqlite] racing with date('now') (was: Select with dates)

[Keith Medcalf]

> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said:
> Keith Medcalf wrote:
> >> On Thu, 12 Sep 2013 14:01:04 +0100
> >> Simon Davies  wrote:
> >>
> >>> Why not
> >>> SELECT * FROM "entry" WHERE
> >>>bankdate >= date('now','start of month')
> >>>   AND bankdate < date('now','start of month','+1 month')
> >> The half-open interval strikes again!  :-)
> >
> > And you are using UTC ...
> >
> > Notwithstanding the timezone you want to use,
> >
> > explain select *
> >   from entry
> >  where bankdate >= date('now', 'start of month')
> >and bankdate < date('now', 'start of month', '-1 day');
> >
> > will generate the following code:
> 
> And there are another pitfall in this query: date('now') is *RACY*. That
> is, if you execute this query *exactly* at midnight at month boundary, first
> and second DATE() invocation may refer to *different* months.
 
> And if your query return several rows, and there will be month boundary
> between sqlite3_step(), your query also can return data from two month.
 
> You can easily reproduce this problem if you switch unit from month to
> millisecond, e.g.
> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
> will non-deterministically return rows.
 
> IMO, correct [= least surprise] behavior should be "timestamp used for
> 'now' should cached on first row step, and reused in all following calls
> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.

That would require a change to SQLite itself.  Without doing that I believe 
this will solve those problems by using only one 'now', and also only running 
each date() call once (even my previous solutions could suffer from race 
conditions since they retrieved 'now' multiple times):

sqlite> explain select *
   ...>   from entry,
   ...>(select bdate, (select date(bdate, '+1 month')) as edate
   ...>   From (select date('now', 'localtime', 'start of 
month') as bdate) as ttemp limit 1) as tstamp
   ...>  where bankdate >= bdate
   ...>and bankdate < edate;

SELECT item[0] = {0:0} AS bankdate
   item[1] = {1:0} AS bdate
   item[2] = {1:1} AS edate
FROM {0,*} = entry
 {1,*} = SELECT item[0] = {2:0}
item[1] = (SELECT FUNCTION:date(item[0] = {2:0}
item[1] = '+1 month')
   LIMIT 1
   END) AS edate
 FROM {2,*} = SELECT FUNCTION:date(item[0] = 'now'
   item[1] = 'localtime'
   item[2] = 'start of month')
  END (tabname=sqlite_subquery_56DA50_) (AS ttemp)
 LIMIT 1
 END (tabname=sqlite_subquery_56F8D0_) (AS tstamp)
WHERE AND(GE({0:0},{1:0}),LT({0:0},{1:1}))
END

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Integer331 000
2 Once   0 33000
3 Goto   0 50000
4 Goto   0 14000
5 OpenPseudo 2 4 101
6 Integer0 3 001
7 String80 5 0 now00
8 String80 6 0 localtime  00
9 String80 7 0 start of month  00
10Function   7 5 4 date(-1)   03
11Yield  2 0 000
12Integer1 3 000
13Yield  2 0 000
14OpenEphemeral  1 2 000
15Integer1 8 000
16Integer4 2 000
17Yield  2 0 000
18If 3 33000
19Column 2 0 900
20Null   0 11000
21Integer1 12000
22Column 2 0 14   00
23String80 150 +1 month   00
24Function   2 1413date(-1)   02
25Move   1311000
26IfZero 1227-1   00
27SCopy  1110000
28MakeRecord 9 2 16   00
29NewRowid   1 17000
30Insert 1 1617   08
31IfZero 8 33-1   00
32Goto   0 17000
33Return 1 0 0 

Re: [sqlite] racing with date('now') (was: Select with dates)

[Stephan Beal]

On Sat, Sep 14, 2013 at 3:19 PM, Yuriy Kaminskiy  wrote:

> IMO, correct [= least surprise] behavior should be "timestamp used for
> 'now'
> should cached on first row step, and reused in all following calls [until
> SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
>

That can easily be done with a custom function. Here's one which returns
the current time but could easily be refactored to cache the time in a
static or in app-state (via the sqlite3 context data).

/*
** SQL function to return the number of seconds since 1970.  This is
** the same as strftime('%s','now') but is more compact.
*/
static void fsl_db_now_function(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_int64(context, (sqlite3_int64)time(0));
}


Caching like that is only really useful for short-lived apps, though. If
the app runs for several minutes, it may very well later expect 'now' to be
now. One could extend the above function to take an optional boolean
parameter, and reset the cached value if passed a truthy value.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] racing with date('now') (was: Select with dates)

[Yuriy Kaminskiy]

Keith Medcalf wrote:
>> On Thu, 12 Sep 2013 14:01:04 +0100
>> Simon Davies  wrote:
>>
>>> Why not
>>> SELECT * FROM "entry" WHERE
>>>bankdate >= date('now','start of month')
>>>   AND bankdate < date('now','start of month','+1 month')
>> The half-open interval strikes again!  :-)
> 
> And you are using UTC ...
> 
> Notwithstanding the timezone you want to use, 
> 
> explain select * 
>   from entry 
>  where bankdate >= date('now', 'start of month') 
>and bankdate < date('now', 'start of month', '-1 day');
> 
> will generate the following code:

And there are another pitfall in this query: date('now') is *RACY*. That is, if
you execute this query *exactly* at midnight at month boundary, first and second
DATE() invocation may refer to *different* months.

And if your query return several rows, and there will be month boundary between
sqlite3_step(), your query also can return data from two month.

You can easily reproduce this problem if you switch unit from month to
millisecond, e.g.
SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
will non-deterministically return rows.

IMO, correct [= least surprise] behavior should be "timestamp used for 'now'
should cached on first row step, and reused in all following calls [until
SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.

[...]

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