Re: [sqlite] Once again about random values appearance

[Max Vlasov]

On Mon, Feb 17, 2014 at 1:22 AM, James K. Lowden
wrote:

> On Fri, 14 Feb 2014 08:32:02 +0400
> Max Vlasov  wrote:
>
> > From: Max Vlasov 
> > To: General Discussion of SQLite Database 
> > Reply-To: General Discussion of SQLite Database
> >  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
> > 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  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

[sqlite] Compiling SQLite with latest MinGW: undesired link to MinGW-specific dll.

[Jan Nijtmans]

When compiled SQLite out-of-the-box on MinGW-4.0
there are two problems. One is currently in progress
on the "mingw4x" branch, but there is one more.
For explanation and a suggested fix, see below.

When compiling sqlite3.dll, it contains a link to
libgcc_s_dw2-1.dll, which must be distributed
together with sqlite3.dll. Dit is - of course -
not desired. A workaround is easy: Add the
option "-static-libgcc" in the link line. Earlier
MinGW versions only had a static gcc library,
so it is safe to use this option always: In
MinGW 3 "-static-libgcc" was the default
anyway. Most recent MinGW-w64 versions
have exactly the same problem, only the
required dll name is different (libgcc_s_sjlj-1.dll).
This solution works for MinGW-w64 as well.

This bug is already reported to MinGW,
for details:


Regards,
Jan Nijtmans

===
--- Makefile.in
+++ Makefile.in
@@ -1001,7 +1001,7 @@
echo 'EXPORTS' >sqlite3.def
nm $(REAL_LIBOBJ) | grep ' T ' | grep ' _sqlite3_' \
| sed 's/^.* _//' >>sqlite3.def

 sqlite3.dll: $(REAL_LIBOBJ) sqlite3.def
-   $(TCC) -shared -o $@ sqlite3.def \
+   $(TCC) -shared -static-libgcc -o $@ sqlite3.def \
-Wl,"--strip-all" $(REAL_LIBOBJ)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Once again about random values appearance

[James K. Lowden]

On Fri, 14 Feb 2014 08:32:02 +0400
Max Vlasov  wrote:

> From: Max Vlasov 
> To: General Discussion of SQLite Database 
> Reply-To: General Discussion of SQLite Database
>  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
> 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  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