On Thu, Aug 27, 2015 at 11:41 AM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote:
> On this case: > > select random(), random() from blah order by random() > > > > Error ambiguous column "random()" near "order by". > > Cheers ! > Are you saying ambiguous column is what *should* be reported, or are you saying that is the error message that *is* reported? Because I just tried the query with sqlite3.exe 3.8.11 and it worked just fine. > > > > Thu Aug 27 2015 6:48:54 pm CEST CEST from "Scott Robison" > ><scott at casaderobison.com> Subject: Re: [sqlite] order by not working in > >combination with random() > > > > On Thu, Aug 27, 2015 at 10:06 AM, Jean-Christophe Deschamps < > > jcd at antichoc.net> wrote: > > > > > >>At 16:00 27/08/2015, you wrote: > >> > >> > >>><quote> > >>> An *ORDER BY* clause in SQL <https://en.wikipedia.org/wiki/SQL> > specifies > >>> that a SQL SELECT <https://en.wikipedia.org/wiki/Select_(SQL)> > statement > >>> returns a result set <https://en.wikipedia.org/wiki/Result_set> with > the > >>> rows being sorted by the values of one or more columns. The sort > criteria > >>> do not have to be included in the result set. The sort criteria can be > >>> expressions, including ? but not limited to ? column names, > >>>user-defined > >>> functions <https://en.wikipedia.org/wiki/User_defined_function>, > >>> arithmetic > >>> operations, or CASE expressions. The expressions are evaluated and the > >>> results are used for the sorting, i.e. the values stored in the column > or > >>> the results of the function call. > >>> <quote> > >>> > >>> > > >> Pretty clear until now. > >> > >> So, applying this to our subject, "rr" is _NOT_ the name a column in the > >> > >>>table. > >>> > >>> > > >> Yes: it's a temporary schema name. > >> > >> > > > I can see both sides of this debate, whether or not random() should be > > evaluated twice in this context: > > > > select random() from blah order by random() > > > > So let me pose a question. What should the following query generate? > > > > select random(), random() from blah order by random() > > > > Should all three random values be the same? Certainly I can see a case > for > > that, but it doesn't seem useful to me. What would be even worse is if it > > behaved like other functions which maintain their value over an entire > > select statement as DRH indicated recently. Do we really want a single > > random number used three times per row for thousands of rows? I doubt it. > > > > Perhaps you think it should create two random values for the columns but > > reuse one of them for the order by. But which? Sure, if the columns were > > aliased with names one might expect it to pick the right one, but it > seems > > that is not a requirement of at least one interpretation of the standard. > > > > Ultimately it is a question of "what should happen with a function that > has > > side effects"? SQLite has a flag for user defined functions named > > SQLITE_DETERMINISTIC. This flag allows the author of the function to > inform > > SQLite that the function will always return a single result for a given > set > > of input. I can only assume that the "native" functions of SQLite use > > something very similar to this interface (where "using that exact > > interface" is as similar as possible). > > > > Clearly random() cannot be deterministic as it takes no user supplied > input > > (even indirectly from a separate seed function) yet is expected to > > (probably) return different numbers from each invocation that cannot be > > predicted in advance. > > > > It seems that if someone needs a list of random values that can > > subsequently be sorted (or selected or whatever) they need to resort to > > temp tables or subqueries. One step to generate the random values, > another > > to process the now fixed set of random numbers. > > > > Alternatively, we need a third class of functions. NON_DETERMINISTIC, > > DETERMINISTIC, and PSEUDO_DETERMINISTIC. I don't care for that solution. > > > > Realizing this is not a democracy, my vote if cast would be to keep the > > existing functionality. It is not difficult to work around the > > non-deterministic nature of the random function via temp tables or > > subqueries. I dare say it is an extremely tiny subset of queries that > will > > ever have to worry about this. Update the documentation in some way to > make > > it clear random behaves this way (and perhaps that column aliases in an > > order by clause re-evaluate their definitions from the select). But my > vote > > doesn't mean much, so whatever. > > > > Maybe the best answer to this issue is "to change the functionality at > this > > point may break existing queries that depend on the current behavior" and > > thus it should not be changed. > > > > -- > > Scott Robison > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
