Re: [sqlite] racing with date('now') (was: Select with dates)
On Sep 15, 2013, at 12:53 AM, Kees Nuyt k.n...@zonnet.nl wrote: 3) If an SQL-statement generally contains more than one reference to one or more datetime value functions, 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
[sqlite] List of registered tokenizers
What is the correct way to get a list of all registered sqlite tokenizers? Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ 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 wrote:
On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy yum...@gmail.com 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)
On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy yum...@gmail.com 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)
Op 15 sep 2013, om 14:05 heeft Stephan Beal het volgende geschreven:
On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy yum...@gmail.com
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)
On 15 Sep 2013, at 12:58pm, Yuriy Kaminskiy yum...@gmail.com 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] Help with a self join please
Thanks Igor, that looks neater than my solution. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71280.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a self join please
... and it runs in under half the time of my version, including showing the percentage and selecting only those over 75% -- View this message in context: http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71281.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about date time
All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. Thanks, -Bill ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
On 15 Sep 2013 at 18:13, William Drago wdr...@suffolk.lib.ny.us wrote: All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. I convert everything to seconds since the epoch and have a column with that. All comparisons are done against that value. This is not too difficult in PHP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about begin/commit
All,
In the following bit of code found in the help file
SQLite.NET.chm, I see that BeginTransaction() encloses
everything. I always thought that just the for-loop needed
to be enclosed by begin/commit.
What are the reasons for enclosing the other commands?
Thanks,
-Bill
using (SQLiteTransaction mytransaction =
myconnection.BeginTransaction())
{
using (SQLiteCommand mycommand = new
SQLiteCommand(myconnection))
{
SQLiteParameter myparam = new SQLiteParameter();
int n;
mycommand.CommandText = INSERT INTO [MyTable]
([MyId]) VALUES(?);
mycommand.Parameters.Add(myparam);
for (n = 0; n 10; n ++)
{
myparam.Value = n + 1;
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}
___
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 wrote:
On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy yum...@gmail.com 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] Question about date time
Hi Tim, Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? Say for example, I want to know for the past month what my failure rate was between 11PM and 1AM every day. I'd figure out what 11PM and 1AM is in seconds since the epoch for the 1st of the month and then for each of the next 30 days, then figure out some SELECT statement to use that set of numbers. It seems very convoluted. Anyway, I have a feeling I'm asking this question in the wrong place. I'm not sure this is a SQLite specific question as the answer is probably going to be the same regardless of the DBMS. Thanks, -Bill On 9/15/2013 1:16 PM, Tim Streater wrote: On 15 Sep 2013 at 18:13, William Drago wdr...@suffolk.lib.ny.us wrote: All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. I convert everything to seconds since the epoch and have a column with that. All comparisons are done against that value. This is not too difficult in PHP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
On Sep 15, 2013, at 8:31 PM, William Drago wdr...@suffolk.lib.ny.us wrote: Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? (Date Time seems to be a popular topic at the moment) There is nothing prescriptive in using epoch time. As SQLite doesn't have a dedicated date type, you are free to decide how you want to handle it. There are two main encoding: (1) As a number: Julian date, unix epoch, etc (2) As a string: ISO 8601 co.. Just make sure that your string representation sorts properly. http://www.sqlite.org/lang_datefunc.html The granularity of the date is up to you as well: day, hour, milliseconds, etc. This is more driven by what's convenient for your application. Ditto if this should be split between date time. Depending on the task at hand, you could even require a much more full fledge set of entities: create table if not exists date ( idinteger not null constraint date_pk primary key, year integer not null, month integer not null, day integer not null, day_of_year integer not null, day_of_week integer not null, week_of_year integer not null, constraintdate_uk unique( year, month, day ) ) create table if not exists time ( id integer not null constraint time_pk primary key, hourinteger not null, minute integer not null, second integer not null, constraint time_uk unique( hour, minute, second ) ) And then there are timezones, etc… ___ 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)
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)
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)
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)
On Sep 15, 2013, at 10:32 PM, Keith Medcalf kmedc...@dessus.com 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
