Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

Thanks both that is really helpful! I hadn't come across the query flattening optimisation before. The documentation is helpful as is prepending EXPLAIN. Thanks again. Kind Regards Keith On 11 April 2017 at 09:55, Clemens Ladisch wrote: > Keith Maxwell wrote: > > Can anyone

[sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

SELECT abs(random() % 10) FROM t1 ...> ) ...> SELECT Y, Y <= 5, Y BETWEEN 0 AND 5, Y < 6 FROM t2; 7|1|1|0 3|1|0|0 2|1|1|1 2|0|0|1 0|1|1|1 3|1|1|0 5|1|1|0 6|1|1|0 0|1|1|1 sqlite> Thanks in advance for your assistance. Kin

Re: [sqlite] thousand separator for printing large numbers

If the `printf` function could add a thousands separator there I are times I would have used it! Instead I've ended up using a recursive CTE and `||` operators to build up the string. I can't find the query right now. Maybe the CLI tool is a better place for the feature, I suggest either as a fun

Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

"any arbitrary expressions"? I'm afraid I didn't get very far trying to look up a standard. I appreciate your help! Thanks again. Kind Regards Keith Maxwell [1] https://en.wikipedia.org/wiki/Correlated_subquery [2] https://www.sqlite.org/lang_select.html#order-by ___

Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

g lost is that I can use z elsewhere in the sub-query, just not in its `ORDER BY` clause. I appreciate your continued help. Kind Regards Keith On 18 December 2016 at 18:51, Simon Slavin wrote: > > On 18 Dec 2016, at 6:41pm, Keith Maxwell wrote: > >> Thanks again, maybe I didn't ask

Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

ge you suggest helps. I appreciate that in my example, which I've probably over-simplified, there is only one value of z. In the more complicated real life example there are lots of values of z so I can't have `LIMIT 1` on the outer query. I appreciate your help! Kind Regards Keith M

Re: [sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

but to me the query as written is valid, and I don't understand why I get "no such column". I can try and clean up a more real life example, but I think that makes it harder to follow by adding in date functions. Thanks for your help. Kind Regards Keith Maxwell On 18 December 2

[sqlite] Unexpected 'no such column' with expression in subquery ORDER BY

ins enough. I'm using 3.15.2: sqlite> .version SQLite 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8 I appreciate any help you can offer. Thanks in advance! Keith Maxwell [1] https://www.sqlite.org/lang_select.html#order-by ___