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.
Like datetime, random() is a nondeterministic function taking constant
(but void) input. It should be evaluated once, as provided. Where it
appears in your query should not matter. That would make it consistent
with how other functions work, and with the SQL standard.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
