[sqlite] order by not working in combination with random()

On Sat, Aug 29, 2015 at 4:16 AM, Yahoo! Mail wrote: > On 08/28/2015 09:36 PM, Scott Robison wrote: > >> On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail < >> stefanossofroniou542 at yahoo.com >> >>> wrote: >>> >>> On 08/26/2015 09:03 PM, Richard Hipp wrote: >>> >>> Time stands still for multiple

[sqlite] order by not working in combination with random()

On 08/28/2015 09:36 PM, Scott Robison wrote: > On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail yahoo.com >> wrote: >> >> On 08/26/2015 09:03 PM, Richard Hipp wrote: >> >> Time stands still for multiple rows, as long as they are within the >>> same sqlite3_step() call. For example, if you run: >>>

[sqlite] order by not working in combination with random()

On Fri, 28 Aug 2015 14:45:26 +, "Rousselot, Richard A" wrote: > I have noticed that SQLite Query Browser is running slower > than other IDEs, including SQLitespeed, for some reason. > Even when each IDE is set to using similar versions of the > SQLite3.dll. We had a recursive query in SQB

[sqlite] order by not working in combination with random()

R.Smith, Thank you for the reply. I'm pasting again the original message that explains which versions I have tested on which Operating System. /I have tested this code with version 3.8.10.2 using "DB Browser for SQLite" and it would crash; the same with SQLite Manager that uses the same

[sqlite] order by not working in combination with random()

On 2015-08-28 04:45 PM, Rousselot, Richard A wrote: > I have noticed that SQLite Query Browser is running slower than other IDEs, > including SQLitespeed, for some reason. Even when each IDE is set to using > similar versions of the SQLite3.dll. We had a recursive query in SQB take 6 > min,

[sqlite] order by not working in combination with random()

If you're talking about Database Browser for SQLite (formally named SQLite Database Browser), at least at one time (version 3.5) it executed each query twice, apparently the first time to figure out the return types, and the second time to display the results (this caused me a certain amount of

[sqlite] order by not working in combination with random()

On 2015-08-28 03:09 PM, Yahoo! Mail wrote: > Where did you see the vacuum happening inside the transaction? It's > just right before begin...anyway. It seems I'm unable to make clear > the actual "issue" of mine, but anyhow it's not a bit deal. I just > wanted to report what I have noticed,

[sqlite] order by not working in combination with random()

Where did you see the vacuum happening inside the transaction? It's just right before begin...anyway. It seems I'm unable to make clear the actual "issue" of mine, but anyhow it's not a bit deal. I just wanted to report what I have noticed, that's all. On 08/28/2015 03:43 PM, R.Smith wrote: >

[sqlite] order by not working in combination with random()

I have noticed that SQLite Query Browser is running slower than other IDEs, including SQLitespeed, for some reason. Even when each IDE is set to using similar versions of the SQLite3.dll. We had a recursive query in SQB take 6 min, on other IDEs it would be less than 2 min. My $0.02

[sqlite] order by not working in combination with random()

On 2015-08-28 01:17 PM, Yahoo! Mail wrote: > Obviously you did not get my issue; something is wrong and your timer > suggestion indicates this. During the execution of each command, I > would monitor it with *watch "du test.db*"*. The journal size would go > mad even surpassing the database's

[sqlite] order by not working in combination with random()

Obviously you did not get my issue; something is wrong and your timer suggestion indicates this. During the execution of each command, I would monitor it with *watch "du test.db*"*. The journal size would go mad even surpassing the database's actual size at some moments. *sqlite> .timer on

[sqlite] order by not working in combination with random()

Yahoo! Mail wrote: > sqlite> drop table if exists t1; create table t1(a datetime); begin; with > recursive c(x) as (values(1) union all select x + 1 from c where x < > 10) insert into t1(a) select datetime('now') from c; commit; > > It would take ages to finish and that is logical; it's

[sqlite] order by not working in combination with random()

On 08/26/2015 09:03 PM, Richard Hipp wrote: > Time stands still for multiple rows, as long as they are within the > same sqlite3_step() call. For example, if you run: > > CREATE TABLE t1(a DATETIME); > WITH RECURSIVE > c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE

[sqlite] order by not working in combination with random()

On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail wrote: > > > On 08/26/2015 09:03 PM, Richard Hipp wrote: > > Time stands still for multiple rows, as long as they are within the >> same sqlite3_step() call. For example, if you run: >> >> CREATE TABLE t1(a DATETIME); >> WITH RECURSIVE >>

[sqlite] order by not working in combination with random()

At 09:14 28/08/2015, you wrote: >--- > > Looks like "How many zillion devices are going to misbehave if this > is fixed?" > >We will find out, because SQLite was changed two days ago: >http://www.sqlite.org/cgi/src/info/c2f3bbad77850468 >--- Fine, let's sit down and watch the world collapse.

[sqlite] order by not working in combination with random()

Jean-Christophe Deschamps wrote: > At 21:11 27/08/2015, you wrote: >> I think it still comes back to my earlier comment: Would changing it to >> behave more like the most common / expected outcome above be a breaking >> change? > > Looks like "How many zillion devices are going to misbehave if

[sqlite] order by not working in combination with random()

At 21:11 27/08/2015, you wrote: > > There are 2 distinct and volontary function invokations, so I don't see > > how SQL engine would decide not to perform the second call. > >Agreed, though I'm pretty sure I've read messages in this thread at >advocate the same function should return the same

[sqlite] order by not working in combination with random()

On 27 Aug 2015 at 18:49, Simon Slavin wrote: > On 27 Aug 2015, at 6:41pm, Domingo Alvarez Duarte dev.dadbiz.es> > wrote: > >> select random(), random() from blah order by random() >> >> >> >> Error ambiguous column "random()" near "order by". > > Thing is, that's not ambiguous. I don't really

[sqlite] order by not working in combination with random()

>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() There are 2 distinct and volontary function invokations, so I don't see how SQL engine would decide not to perform the second call. >So let

[sqlite] order by not working in combination with random()

On this case: select random(), random() from blah order by random() ? Error ambiguous column "random()" near "order by". Cheers ! ? > Thu Aug 27 2015 6:48:54 pm CEST CEST from "Scott Robison" > Subject: Re: [sqlite] order by not working in >combination with random() > > On Thu,

[sqlite] order by not working in combination with random()

On 27 Aug 2015, at 6:41pm, Domingo Alvarez Duarte wrote: > select random(), random() from blah order by random() > > > > Error ambiguous column "random()" near "order by". Thing is, that's not ambiguous. I don't really care how SQLite implements it, but there is no excuse for

[sqlite] order by not working in combination with random()

At 16:00 27/08/2015, you wrote: > >An *ORDER BY* clause in SQL specifies >that a SQL SELECT statement >returns a result set with the >rows being sorted by the values of one

[sqlite] order by not working in combination with random()

On 2015-08-27 04:50 PM, Scott Hess wrote: > I keep thinking I remember a thread from years ago where a lot of this was > hashed out, but I cannot find it. > //// > There is already some precedent for this, because ORDER BY RANDOM() must > internally be holding the random values used fixed

[sqlite] order by not working in combination with random()

On 2015-08-27 03:29 PM, Simon Slavin wrote: > > Sure. I chose to use an alias just to emphasise how wrong the result looked. > However, I have seen code written by teams where the person writing the > query has no real idea whether they're querying a TABLE, a VIEW, or a virtual > table.

[sqlite] order by not working in combination with random()

On 27 Aug 2015, at 9:11am, Domingo Alvarez Duarte wrote: > A very instructive post, could you give your opinion about what should be the > behavior for the "WHERE" clause ? > > I meam if we have a function on the field definition and reference it on the > "WHERE" clause: > > CREATE TABLE

[sqlite] order by not working in combination with random()

On Thu, Aug 27, 2015 at 11:55 AM, Jean-Christophe Deschamps < jcd at antichoc.net> wrote: > > 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() >> > > There are 2 distinct and volontary

[sqlite] order by not working in combination with random()

On 2015-08-27 03:03 AM, James K. Lowden wrote: > On Wed, 26 Aug 2015 13:39:09 +0100 > Simon Slavin wrote: > >> On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: >> >>> Plus, it apparently recognizes if the random() expression in the >>> ORDER BY is the same as the SELECT one and again sort

[sqlite] order by not working in combination with random()

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

[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: > >> >> An *ORDER BY* clause in SQL specifies >> that a SQL SELECT statement >> returns a

[sqlite] order by not working in combination with random()

A very instructive post, could you give your opinion about what should be the behavior for the "WHERE" clause ? I meam if we have a function on the field definition and reference it on the "WHERE" clause: CREATE TABLE myTable (a INTEGER); INSERT INTO myTable VALUES (1),(2),(3),(4),(5);

[sqlite] order by not working in combination with random()

In the case: SELECT random() AS rr FROM sometable ORDER BY rr, the SQLite result is anti-intuitive. In my ignorance, I thought that ORDER BY sorted the results of the SELECT. It sure _looks_ that way from my view point. I cannot access the ANSI standard because I'm too cheap to buy them. So I went

[sqlite] order by not working in combination with random()

I keep thinking I remember a thread from years ago where a lot of this was hashed out, but I cannot find it. I seem to remember one point which made sense was that while most functions with no parameters were reasonably considered static across the entire statement's execution, RANDOM() needed to

[sqlite] order by not working in combination with random()

On Wed, 26 Aug 2015 13:39:09 +0100 Simon Slavin wrote: > > On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: > > > Plus, it apparently recognizes if the random() expression in the > > ORDER BY is the same as the SELECT one and again sort correctly > > (without re-evaluating) and without

[sqlite] order by not working in combination with random()

Domingo Alvarez Duarte wrote: > This assumption is a bit naive : > >> In SQLite, this cannot happen because queries execute infinitely fast >> (as far as the built-in date/time functions are concerned). Nonetheless it's true. says: | the current time

[sqlite] order by not working in combination with random()

I just saw this commit http://www.sqlite.org/src/info/c2f3bbad77850468 and the same principle probably should apply to the where clause ? SELECT rr FROM myView WHERE rr < 30 ORDER BY rr; Cheers ! ?

[sqlite] order by not working in combination with random()

This assumption is a bit naive : In SQLite, this cannot happen because queries execute infinitely fast (as far as the built-in date/time functions are concerned). There is different cpus with different processing power, load variations, io access latency, ... Cheers ! ? > Wed Aug

[sqlite] order by not working in combination with random()

Graham Holden wrote: > And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the > example from J Decker: > > select ItemName,SoldDate, date('now','-1 month') as z from > SoldItemDetails order by > SoldDate > if it were to show the same behaviour (I haven't tested it) might break

[sqlite] order by not working in combination with random()

On 8/26/15, Igor Tandetnik wrote: >> >> says: >> | the current time (ex: julianday('now')) is always the same for multiple >> | function invocations within the same sqlite3_step() call. > > This only says that the time "stands still" for all the

[sqlite] order by not working in combination with random()

On 8/26/2015 10:52 AM, Clemens Ladisch wrote: > Domingo Alvarez Duarte wrote: >> This assumption is a bit naive : >> >>> In SQLite, this cannot happen because queries execute infinitely fast >>> (as far as the built-in date/time functions are concerned). > > Nonetheless it's true. > >

[sqlite] order by not working in combination with random()

On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: > Plus, it apparently recognizes if the random() expression in the ORDER BY is > the same as the SELECT one and again sort correctly (without re-evaluating) > and without needing an alias. Ah, but I would call /that/ a bug ! Simon.

[sqlite] order by not working in combination with random()

BTW, MySQL also seems to do it 'correctly'. Plus, it apparently recognizes if the random() expression in the ORDER BY is the same as the SELECT one and again sort correctly (without re-evaluating) and without needing an alias. -Original Message- From: Domingo Alvarez Duarte Sent:

[sqlite] order by not working in combination with random()

Apologies .. I assumed the random() example was a "created to show the effect" query by the OP. ?Your example was the one using the date function. Original message From: J Decker Date: 26/08/2015 11:42 (GMT+00:00) To: Graham Holden ,General Discussion of

[sqlite] order by not working in combination with random()

I tested this on postgresql and I get a correctly ordered list for "SELECT rr FROM myView ORDER BY rr;" So I also will say that the sqlite behavior of reevaluate columns function calls on "order by" is a bug. Cheers ! > Wed Aug 26 2015 9:50:48 am CEST CEST from "Simon Slavin" > Subject:

[sqlite] order by not working in combination with random()

And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the example from J Decker: select ItemName,SoldDate, date('now','-1 month') as z from SoldItemDetails order by SoldDate wrote: > select random() order by random() should definately reprocess the function... Agreed.? But I've

[sqlite] order by not working in combination with random()

On Wed, Aug 26, 2015 at 3:00 AM, J Decker 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 > which I would think would evalutate to a

[sqlite] order by not working in combination with random()

On Wed, Aug 26, 2015 at 9:52 AM, Clemens Ladisch wrote: > Domingo Alvarez Duarte wrote: > > This assumption is a bit naive : > > > >> In SQLite, this cannot happen because queries execute infinitely fast > >> (as far as the built-in date/time functions are concerned). > > Nonetheless it's true.

[sqlite] order by not working in combination with random()

On 26 Aug 2015, at 8:00am, J Decker wrote: > select random() order by random() should definately reprocess the function... Agreed. But I've come to the conclusion that SELECT random() AS rr ORDER BY rr should not. Here's a nasty result SQLite version 3.8.10.2 2015-05-20 18:14:01

[sqlite] order by not working in combination with random()

On Wed, Aug 26, 2015 at 2:47 AM, Graham Holden wrote: > And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the > example from J Decker: > contrived? cause I copied it from the original poster's first message? it was used in MySQL as a way to shuffle a deck of cards, and worked

[sqlite] order by not working in combination with random()

On Tue, Aug 25, 2015 at 7:16 PM, Stephen Chrzanowski wrote: > Somewhat of a devils advocate here, but I'm not sure one can order based on > JUST data. Take the DATE function, for example. If, by your words, ORDER > BY should only act on the data, consider this kind of query: > > select

[sqlite] order by not working in combination with random()

Somewhat of a devils advocate here, but I'm not sure one can order based on JUST data. Take the DATE function, for example. If, by your words, ORDER BY should only act on the data, consider this kind of query: select ItemName,SoldDate from SoldItemDetails order by SoldDate

[sqlite] order by not working in combination with random()

On Mon, 17 Aug 2015 12:01:58 +0200 Clemens Ladisch wrote: > Just because the ORDER BY clause refers to a column of the > SELECT clause does not mean that the value is not computed > a second time. Let's at least recognize that as a bug. ORDER BY shouldn't interpret SQL or invoke functions.

[sqlite] order by not working in combination with random()

Simon Slavin wrote: > On 18 Aug 2015, at 1:32am, Simon Davies > wrote: > >> sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; >> -6629212185178073901 >> -5293473521544706766 >> 2649466971390864878 >> -6185422953036640443 >> 1855956853707028764 > > Eek. Sorry, I should

[sqlite] order by not working in combination with random()

On 18 Aug 2015, at 1:32am, Simon Davies wrote: > sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; > -6629212185178073901 > -5293473521544706766 > 2649466971390864878 > -6185422953036640443 > 1855956853707028764 Eek. Sorry, I should have tried it before posting.

[sqlite] order by not working in combination with random()

On 17 August 2015 at 21:50, Simon Slavin wrote: > > On 17 Aug 2015, at 9:46pm, Jeffrey Mattox wrote: > >> Could the random() be made part of an expression (that doesn't change the >> result) to fool the optimizer into only doing the random() once, like this: >> >> SELECT ( random() *

[sqlite] order by not working in combination with random()

> On Aug 17, 2015, at 12:01 PM, Clemens Ladisch wrote: > > Just because the ORDER BY clause refers to a column of the > SELECT clause does not mean that the value is not computed > a second time. And yet: with DataSet( position, value ) as ( select 1 as position, random() as

[sqlite] order by not working in combination with random()

On Aug 17, 2015 21:53, "Yuriy M. Kaminskiy" wrote: > > ... and then, at some wonderful moment, sqlite devs will implement query > flattening for CTE (like they did for subquery above), and you'll be in > square one. > > (Or, maybe, they will finally implement "common subexpression > elimination",

[sqlite] order by not working in combination with random()

On 17 Aug 2015, at 9:46pm, Jeffrey Mattox wrote: > Could the random() be made part of an expression (that doesn't change the > result) to fool the optimizer into only doing the random() once, like this: > > SELECT ( random() * col_thats_always_one ) AS x FROM table ORDER BY x Use a

[sqlite] order by not working in combination with random()

I guess that a query likeSELECT random() as x FROM some_non_empty_table ORDER BY xis indeed transformed intoSELECT random() as x FROM some_non_empty_table ORDER BY random()prior to its execution. Question is why the implementation does that. Both from an efficiency point (don't calculate

[sqlite] order by not working in combination with random()

Could the random() be made part of an expression (that doesn't change the result) to fool the optimizer into only doing the random() once, like this: SELECT ( random() * col_thats_always_one ) AS x FROM table ORDER BY x Jeff > On Aug 17, 2015, at 5:01 AM, Clemens Ladisch wrote: > > select

[sqlite] order by not working in combination with random()

Richard Brinkman wrote: > When a perform the following query: > select random() as x from some_non_empty_table order by x desc limit 20; > I get something like: > -4348240540797173967 > -8823092517172356709 > 4237024158005380173 > 897958093325532613 > -6349939216731113298 > ... > which clearly is

[sqlite] order by not working in combination with random()

I've encountered strange behaviour which seems to be a bug in sqlite3. When a perform the following query: select random() as x from some_non_empty_table order by x desc limit 20; I get something like: -4348240540797173967 -8823092517172356709 4237024158005380173 897958093325532613