Re: [sqlite] ORDER BY is ignored during INSERT INTO

On 25 Aug 2019, at 10:09pm, André Borchert <0xa...@gmail.com> wrote: > I try to copy one table into a second identical one. Once the second table is > created I want to move the content over sorted by ASC. It's worth noting here that the rows of a table do not have any order in SQL. A table is

Re: [sqlite] ORDER BY is ignored during INSERT INTO

linglists.sqlite.org] On Behalf Of André Borchert >Sent: Sunday, 25 August, 2019 15:10 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] ORDER BY is ignored during INSERT INTO > >Hello, > >I try to copy one table into a second identical one. Once the second >table >

[sqlite] ORDER BY is ignored during INSERT INTO

Hello, I try to copy one table into a second identical one. Once the second table is created I want to move the content over sorted by ASC. The issue is that the ORDER BY statement gets ignored during a INSERT INTO: INSERT INTO CompanyDetails2 SELECT * FROM CompanyDetails WHERE CompanyDetails.ID

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp wrote: > On 6/28/18, Dominique Devienne wrote: > > From reading this list, I've learned that for an index to have a change > to > > be used to consume an order by, the collation of the query and the index > > must match. > > > > But in many instances,

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On 6/28/18, Dominique Devienne wrote: > From reading this list, I've learned that for an index to have a change to > be used to consume an order by, the collation of the query and the index > must match. > > But in many instances, that index is one from a virtual table we implement. > So is there

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne wrote: > So is there a way to tell SQLite that vindex is of a given custom > collation, > to open the possibility of the index being used? > Note that there's no mention at all of "collation" or "collate" in https://www.sqlite.org/vtab.html so

[sqlite] order by column_name collate custom_collation, with virtual table index

From reading this list, I've learned that for an index to have a change to be used to consume an order by, the collation of the query and the index must match. But in many instances, that index is one from a virtual table we implement. So is there a way to tell SQLite that vindex is of a given cus

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

ot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Danny Milosavljevic >Sent: Monday, 29 January, 2018 12:14 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] "ORDER BY ?&

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

> On Jan 29, 2018, at 11:13 AM, Danny Milosavljevic > wrote: > > Should this use case work? Nope. The ORDER BY clause can affect the query plan and the generated bytecode, so it's not something you can change in a binding. You have to compile a new statement with a different ORDER BY clause

Re: [sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Danny Milosavljevic wrote: > I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and > then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3). > > Expected result: Orders result by column "a", in ascending order. > Observed result: Orders in some strange order. Ordering by colu

[sqlite] "ORDER BY ?" sqlite3_bind_int OK, but does weird things

Hi, I'm trying to prepare a statement for "SELECT a FROM t ORDER BY ?" and then sqlite3_bind_int the parameter to 1 (on sqlite 3.19.3). Expected result: Orders result by column "a", in ascending order. Observed result: Orders in some strange order. I also tried sqlite3_bind_int64, didn't change

Re: [sqlite] Order By gives different result

On 08/21/2017 11:51 PM, Stephen Chrzanowski wrote: I think I may have found a query issue. I haven't checked the SQLite docs to see if this is something that is specified as an order of operations, or if this is a check that has been overlooked. I think using a column alias in a complex expres

[sqlite] Order By gives different result

I think I may have found a query issue. I haven't checked the SQLite docs to see if this is something that is specified as an order of operations, or if this is a check that has been overlooked. The database SQL code is here: https://pastebin.com/raw/FukX4qEB select [main].[StateLabels].[LabelTe

[sqlite] Order by multiple columns

016 11:27 AM > To: SQLite mailing list > Subject: Re: [sqlite] Order by multiple columns > Importance: High > > > On 24 Feb 2016, at 3:15am, admin at shuling.net wrote: > > > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER); > > > > Now if I want to s

[sqlite] Order by multiple columns

On 25 Feb 2016, at 2:31am, admin at shuling.net wrote: > Thank you. You're welcome. > In that case, if I create an index for (F1, F2, F3), then the > next time when I invoke SELECT statement like this: > > SELECT * FROM MyTable ORDER BY F1, F2, F3; > > Then SQLite will utilize the index autom

[sqlite] Order by multiple columns

Hi, I am using SQLite 3.11. I create a table as follows: CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER); Then add the following records: INSERT INTO MyTable (F1, F2, F3) Values (1, 2, 8);

[sqlite] Order by multiple columns

On 24 Feb 2016, at 3:15am, admin at shuling.net wrote: > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER); > > Now if I want to select from MyTable, and sort the result based on F1 > (ascendant), and for two records with same F1, then sort based on > F2(ascendant), then sort based on F

[sqlite] 'order by' doesn't work with 'group_concat()'

On 5 January 2016 at 00:14, Yuri wrote: > Please consider this example: . . . > The 'order by' clause doesn't work, because if it did the result would have > been: > 1|x,y > 2|x,y > > sqlite3-3.9.2 > > Yuri See http://www.sqlite.org/lang_aggfunc.html last sentence " group_concat(X) group_conc

[sqlite] 'order by' doesn't work with 'group_concat()'

NB: This format of the select only works co-incidentally. Presently, the SQLite query planner "believes" that the order by is significant to the result and preserves it at some expense. This may not always be the case. Thus using this method to obtain the desired result is relying on an "im

[sqlite] 'order by' doesn't work with 'group_concat()'

; From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Yuri > Sent: Monday, 4 January, 2016 17:14 > To: General Discussion of SQLite Database > Subject: [sqlite] 'order by' doesn't work with '

[sqlite] 'order by' doesn't work with 'group_concat()'

Please consider this example: ---begin--- #!/bin/sh DB=sq.sqlite rm -f $DB sql() { echo "$1" | sqlite3 $DB } sql "create table a(id integer not null, primary key(id));" sql "create table b(oid integer not null, chr char null);" sql "insert into a values(1);" sql "insert into a values(2);" sq

[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 rows

[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 t

[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 versi

[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, th

[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()

-Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith Sent: Friday, August 28, 2015 7:44 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] order by not working in combination with

[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 a

[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 sqli

[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 a

[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 x<100

[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. No

[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 this

[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 valu

[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 me

[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 &g

[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 generat

[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 or

[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 dur

[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. The

[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 m

[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 funct

[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 cor

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

gt; 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"

[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 re

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

S (1),(2),(3),(4),(5); CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable; SELECT rr FROM myView WHERE rr < 30 ORDER BY rr; ? Cheers ! > Thu Aug 27 2015 3:03:21 am CEST CEST from "James K. Lowden" > Subject: Re: [sqlite] order by not working in >combinat

[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 needi

[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()

Aug 26 2015 4:03:53 pm CEST CEST from "Clemens Ladisch" > Subject: Re: [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

[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 if

[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 calcul

[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()

uarte Sent: Wednesday, August 26, 2015 12:23 PM To: General Discussion of SQLite Database Subject: Re: [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 th

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

Database Subject: Re: [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 fro

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

uot;Simon Slavin" > Subject: Re: [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

[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 c

[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 to

[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 En

[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 t

[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 ItemName,

[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 wrote: > > ORDER BY s

[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. It'

[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. That'

[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() * col_thats_a

[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 value

[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 sub-sele

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

sts.sqlite.org > From: clemens at ladisch.de > Date: Mon, 17 Aug 2015 12:01:58 +0200 > Subject: Re: [sqlite] order by not working in combination with random() > The ORDER BY clause _is_ working, but it sorts the results > of _another_ call to random(). > > Just because the

[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 N

[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 -6349939216

Re: [sqlite] ORDER BY and LIMIT regression

> On 1/19/15, Angelo Mottola converge.it> > wrote: >> Hello, >> >> I have a regression to report, that seems to have been introduced between >> SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 >> and stopped working somewhere in 3.8.7.x; we were hoping

Re: [sqlite] ORDER BY and LIMIT regression

On Mon, 19 Jan 2015, Richard Hipp wrote: Thank you for reporting the problem. Certainly! We always fix every problem that we are aware of in SQLite. But this problem had not been previously reported to us, and did not occur in any of the 168 million test cases that we ran prior to releasi

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Angelo Mottola wrote: > Hello, > > I have a regression to report, that seems to have been introduced between > SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 > and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in > 3.8.8 but eventually it

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Simon Slavin wrote: > > On 19 Jan 2015, at 3:10pm, Richard Hipp wrote: > >> It is a very >> complex problem. In particular, the sample query works fine as long >> as the number of columns in the result set is not exactly 60. Adding >> or removing a single column of result gives the

Re: [sqlite] ORDER BY and LIMIT regression

On 19 Jan 2015, at 3:10pm, Richard Hipp wrote: > It is a very > complex problem. In particular, the sample query works fine as long > as the number of columns in the result set is not exactly 60. Adding > or removing a single column of result gives the correct answer. I would love to know the

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, RSmith wrote: > Might this not be a "reverse_unordered_selects" pragma or compile option > going wrong, or at least the code making it work getting > somehow hooked in the new versions for this query? > It looks like a bug. If you update to the latest trunk check-in and set: .t

Re: [sqlite] ORDER BY and LIMIT regression

Might this not be a "reverse_unordered_selects" pragma or compile option going wrong, or at least the code making it work getting somehow hooked in the new versions for this query? I have seen similar things when using that pragma (but of course that was intended). Just a thought... On 2015/

Re: [sqlite] ORDER BY and LIMIT regression

Ignore my previous email on this subject. We are able to get different results from 3.8.6 and 3.8.8. Unclear yet if the one or the other is incorrect. On 1/19/15, Richard Hipp wrote: > On 1/19/15, Angelo Mottola wrote: >> Hello, >> >> I have a regression to report, that seems to have been intr

Re: [sqlite] ORDER BY and LIMIT regression

On 1/19/15, Angelo Mottola wrote: > Hello, > > I have a regression to report, that seems to have been introduced between > SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 > and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in > 3.8.8 but eventually it

[sqlite] ORDER BY and LIMIT regression

Hello, I have a regression to report, that seems to have been introduced between SQLite 3.8.6 and the newest 3.8.8 (at least our test case worked in 3.8.6 and stopped working somewhere in 3.8.7.x; we were hoping it got fixed in 3.8.8 but eventually it wasn’t). In our application we can have SQ

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Federal University Oye Ekiti conduct Third Matriculation this February visit http://www.fuoye.edu.ng for more details -- View this message in context: http://sqlite.1065341.n5.nabble.com/ORDER-BY-issue-v3-8-2-amalgamtion-tp73605p73704.html Sent from the SQLite mailing list archive at Nabble.co

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Hello Simon, Clemens, and Richard, Thank you for your help. > Simon: "What are you seeing from your SELECT that you weren't expecting ?" 0 | Batt 0 | ClockBatt 0 | Batt 0 | BP 0 | ORP 0 | Ref 0 | pH 0 | pH 0 | DO ... > Simon: "Can you reduce your INSERTs to just two rows, and still get results

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

On Sun, Feb 2, 2014 at 11:58 PM, Labar, Ken wrote: > > > > This used to work until we upgraded sqlite to v3.8.2. It still does work at the PC level. And earlier: > - IAR C compiler Can you try recompiling with all compiler optimizations turned off and see if you still get the error?

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Labar, Ken wrote: > [...] > select (rtTableSort < 1000) as selected, abbrString from userParameter order > by abbrString; > > This used to work until we upgraded sqlite to v3.8.2. It still does work at > the PC level. What is the EXPLAIN QUERY PLAN output for this query on the handheld? When it

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

On 3 Feb 2014, at 4:58am, Labar, Ken wrote: > select (rtTableSort < 1000) as selected, abbrString from userParameter order > by abbrString; > > > This used to work until we upgraded sqlite to v3.8.2. It still does work at > the PC level. Thanks for test data and SELECT command, which allo

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Hello Simon, Below is the query, and the schema I’m trying to debug. FYI: I’m signed up for the daily digest, so please CC kla...@hach.com on replies for a faster response. CREATE TABLE userParameter ( hepi_parameter_id INT NOT NULL ,hepi_c

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

On 1 Feb 2014, at 3:53am, Labar, Ken wrote: > Upgrading from 3.7.14.1 to 3.8.2 our previously sorted queries are no > longer sorted. > Testing this same database with the win7 binary 3.8.2 the query is correctly > sorted. Does your query include an ORDER BY clause ? If not, then you cannot

[sqlite] ORDER BY issue v3.8.2 amalgamtion

Hello, We have a small sqlite3 database compiled into our handheld unit (system details below). Upgrading from 3.7.14.1 to 3.8.2 our previously sorted queries are no longer sorted. Testing this same database with the win7 binary 3.8.2 the query is correctly sorted. I have tried: * a

  1   2   3   >