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 will randomly return one row, but CURRENT_TIME variant
> randomly returns *all* rows).
... 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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
