On Mon, Feb 17, 2014 at 1:22 AM, James K. Lowden
<jklow...@schemamania.org>wrote:
> On Fri, 14 Feb 2014 08:32:02 +0400
> Max Vlasov <max.vla...@gmail.com> wrote:
>
> > From: Max Vlasov <max.vla...@gmail.com>
> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Reply-To: General Discussion of SQLite Database
> > <sqlite-users@sqlite.org> Date: Fri, 14 Feb 2014 08:32:02 +0400
> > Subject: Re: [sqlite] Once again about random values appearance
> >
> > On Fri, Feb 14, 2014 at 6:35 AM, James K. Lowden
> > <jklow...@schemamania.org>wrote:
> >
> > > > > select id, (select id from TestTable where id = abs(random() %
> > > > > 100)) as rndid from TestTable where id=rndid
> > >
> > > On Thu, 13 Feb 2014 07:26:55 -0500
> > > Richard Hipp <d...@sqlite.org> wrote:
> > >
> > > > It is undefined behavior, subject to change depending the specific
> > > > version of SQLite, compile-time options, optimization settings,
> > > > and the whim of the query planner.
> > >
> > > It should be defined.
> > >
> > > In the above query, random() should be evaluated once. In the SQL
> > > model the user provides inputs once, and the system evaluates them
> > > once.
> > >
> > >
> > Once for the query or once for the row?
>
> Once for the query.
>
> As a user you have no control how the system evaluates your query.
> The evaluation may change over time with different implementations, but
> the semantics of the query do not.
>
> Not long ago on this list we discussed
>
> SELECT *, datetime('now') from T;
>
> and the behavior was that the datetime function was called per-row,
> resulting in different times on different rows. It was changed, the
> rationale IIRC to be compatible with the SQL standard and other DBMSs.
>
Ok, I hope I found the topic, the title was
"racing with date('now') (was: Select with dates):
one of the links to the archive
https://www.mail-archive.com/sqlite-users@sqlite.org/msg79456.html
CMIIW, but as I see it, the final modification was commented by Richard
> 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.
But this is just for now and date-related functions. I wanted to be sure so
created a user function NanoSec() that returns nanoseconds as it is
calculated with QueryPerformanceCounter and QueryPerformanceFrequency on
Windows and clock_gettime(CLOCK_REALTIME... on Linux. Seems like it's not
always real nanoseconds but value that is changed very frequently to be
different for close VDBE instructions of sqlite engine.
So
Select nanosec() - nanosec() from ...
returns non-zero values for most of the times, so there's no guarantee the
user functions or any other functions will be called once for the step.
My original issue was commented by Richard and there's no other argument I
can post because ability ot use alias in the WHERE clause is probably the
sqlite-only feature so you just can't reference any standard about this.
But.. your first reply triggered another problem, it's where outer query
uses alias from the inner query and here sqlite can be compared with other
engines. So the nanosec example modified
Select v-v from
(
Select nanosec() as v from TestTable
)
...shows non-zero values for the current (3.8.3) and for older (3.6.10)
version. And here it would be interesting to know whether any standard has
something to tell about the value of v in the outer query.
Max
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
