On Wed, Aug 26, 2015 at 3:00 AM, J Decker <d3ck0r at gmail.com> wrote:
> but, your order by is the only place that uses the date function... it
> would have to be reversed as
> elect ItemName,SoldDate, date('now','-1 month') as z from
> SoldItemDetails order by
> SoldDate<z desc,SoldDate asc;
>
> which I would think would evalutate to a token 'z' and use that same
> value, not retain it as a function.
>
> select random() order by random() should definately reprocess the
> function...
>
>
A judgement call still needs to be made, on a per SQL function basis, to
exactly when and where these functions are allowed to be called multiple
times in a query. I don't think this kind of judgement call should be put
on the SQLite engine developers.
As a sample query structure such as this:
[ select {FieldList} from Table where {CondList} order by {OrderList} ]
Remembering that all three "Lists" have the potential to be evaluated
either exactly once, or, per row. How it stands right now, I've no idea
when and how often each "List" is recalculated.
In the situation of Date, both our queries could return invalid results
depending on how {FieldList} works if the queries execution time pre and
post the "Midnight Barrier".
I vaugly remember a thread a while back about {FieldList} being evaluated
every row and getting odd dates and times or something of the sort, and
that was a bit of an issue for that OP, so a request was put in that
whatever the calling function was be evaluated once. But, Random would be
useful here, but so would a static date.
I'd like to say that {CondList} would be evaluated exactly once, but, I've
used queries where I want a random number of rows returned, regardless of
the actual conditions, so I know Random is evaluated every time in
{CondList}. The fact that I can use Random here is useful. If {CondList}
were to be evaluated exactly once, pending addtional conditions, I'd either
get all results or no result with a "Random>0.5" or whatever actual values
Random returns to make this a 50/50 call.
Finally, {OrderList}, the contention of this thread, also begs to ask the
question to what should be done right here. Looking at the query, I can
see that Random "shouldn't" be returning a re-randomly-ordered list, but,
I'm sure someone has invented a scenario in which this kind of behavior is
exactly required. Although I AM surprised that putting the random function
within a subquery or view didn't work as you'd think.
My thought, leave everything as is "function calling" wise by default, but
add three pragmas that allow the developer to state when and how often each
of those three "Lists" should be evaulated.
pragma ExecFieldOnce=[0/1]
pragma ExecCondOnce=[0/1]
pragma ExecOrderOnce=[0/1]
With this methodology, it takes the owness off Dr Hipp and all of deciding
what functions should be executed multiple times and where, will not affect
existing queries, and puts the owness directly on the developer to decide
how the query should be executed. If an all-or-nothing scenario is wanted
in the {CondList} using Random, it'd be up to the developer.
Note that these pragmas affect all subqueries the same. So if {CondList}
is set to evaluate once, then Random is executed once for ALL {CondList}
portions of all queries.
