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