[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 12-09-15 22:12, Aurel Wisse wrote: > @Luuk : I tried your query and I cancelled after 9 minutes when it wasn't > finished. > yes, it was/IS bad... i should stick to non-recursive queries select s1.calc_date, (select min(s2.calc_date) from securities s2 where

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 12 Sep 2015, at 9:14pm, Simon Slavin wrote: > Do you have an index on calc_date ? If not, make one. Uh ... and then run ANALYZE. Simon.

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

> On 12 Sep 2015, at 9:12pm, Aurel Wisse wrote: > > CREATE TEMP TABLE offsets AS > > WITH RECURSIVE dtoff(calc_date, tmw) AS ( > > WITH dates AS (SELECT DISTINCT calc_date FROM securities) > >SELECT NULL, (SELECT min(calc_date) FROM dates) > ... > This is inevitable as "securities" is a

[sqlite] Third test of json and index expressions, now it works

On Wed, 09 Sep 2015 09:56:12 +0200 "Domingo Alvarez Duarte" wrote: > With your knowledge could you give your view about how evaluation of > calculated/function columns should be done to have correct results. ... > CREATE TABLE a(b); ... > SELECT a, random() as r FROM a WHERE r <> r;

[sqlite] Bug in SQLite 3.8.11.1 source code

Hello, I was just looking at updating to SQLite 3.8.11.1 when I spotted what appears to be an error. Here?s a patch to fix it: --- sqlite-amalgamation-3081101/sqlite3.c 2015-07-30 03:06:58.0 +0100 +++ sqlite3.c 2015-09-12 19:03:55.0 +0100 @@ -92265,7 +92265,7 @@ }

[sqlite] BEGINNER - Transactions in shell script

As with Rob, this is my final say as well. On Fri, Sep 11, 2015 at 1:38 PM, Petr L?z?ovsk? wrote: > > 1. Security through obscurity is your first mistake. There is no such > thing. > > Interesting It does not exist, but it have article on wikipedia. > Sounds like UFO or Yetti... >

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

On 2015-09-12 06:30 PM, Florian Weimer wrote: >> On 09/06/2015 11:13 AM, Florian Weimer wrote: >>> Surely that's not true, and NFS and SMB are fine as long as there >>> is no concurrent access? >> And no program crashes, no network glitches, no optimisation in the >> protocols to deal with

[sqlite] Native sqlite4 on FreeBSD

On Friday, 11 Sep 2015 10:12 AM -0400, Valentin Davydov wrote: > On Thu, Sep 10, 2015 at 01:30:37PM +0200, Eduardo Morras wrote: >> >> Use gmake to compile. > > It didn't work either. Finally I've just installed some brand new linux > on a nearby virtual machine, made there make -f

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

@R.Smith I wasn't aware that recursive aggregate queries give wrong answers in 99% of all general use cases. In that case, it is obviously preferable to disable it. On Sat, Sep 12, 2015 at 5:51 PM, R.Smith wrote: > > > On 2015-09-12 10:58 PM, Aurel Wisse wrote: > >> The fastest solution is

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

* Roger Binns: > On 09/06/2015 11:13 AM, Florian Weimer wrote: >> Surely that's not true, and NFS and SMB are fine as long as there >> is no concurrent access? > > And no program crashes, no network glitches, no optimisation in the > protocols to deal with latency, nothing else futzing with the

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 9/12/15, Aurel Wisse wrote: > > In your example, there are 1000 rows of indexed dates. > It's easy enough to change it to 630 or whatever number is closer to your test case. I get: Run Time: real 2.311 user 2.177056 sys 0.131887 Run Time: real 0.164 user 0.159917 sys 0.003997 The first

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 12-09-15 16:36, Aurel Wisse wrote: > Yes it works, but it takes forever. By comparison: > > Original recursive query: 7-8 seconds (SQLite 3.8.10.1) > Non recursive query (see below): 18-19 seconds. (SQLite 3.8.10.1) > Proposed query: Not finished after 10 minutes. I am cancelling. > > Non

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

The fastest solution is actually the temporary table: CREATE TEMP TABLE uniqdt AS SELECT DISTINCT calc_date FROM securities WHERE strftime('%w', calc_date)<>'0' ORDER BY calc_date; CREATE TEMP TABLE dtotemp AS SELECT ud1.calc_date AS calc_date, ud2.calc_date AS tmw FROM uniqdt AS ud1 INNER

[sqlite] sqlite3 file as database

On 12 Sep 2015, at 1:19pm, s.movaseghi at eramtec.ir wrote: > I have a database file as database.db3 but I have to use database.sqlite3 . > How can I convert the db3 file to sqlite3 file? If it is actually a SQLite database already then just rename the file. SQLite does not care what the file

[sqlite] sqlite3 file as database

Hello dears I have a database file as database.db3 but I have to use database.sqlite3 . How can I convert the db3 file to sqlite3 file? Thanks for your help sanam

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

There is an index on securities (6.2 million rows). On the dates query from the with clause, WITH dates AS (SELECT DISTINCT calc_date FROM securities), there can be no index. In your example, there are 1000 rows of indexed dates. The runtime of the full example (four steps) is: Run Time: real

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 9/12/15, Aurel Wisse wrote: > @Luuk : I tried your query and I cancelled after 9 minutes when it wasn't > finished. > > @Richard : The query isn't done after 15 minutes. I added a small > modification, reinserting the WITH clause inside the recursive WITH (and > using dates instead of

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

@Simon : Yes there is an index. The raw query CREATE TEMP TABLE uniqdt AS SELECT DISTINCT calc_date FROM securities; runs exactly 1.2 seconds. @Luuk : Yes, there is a non recursive solution, but it is slower than the recursive solution, by a factor of about 2. On Sat, Sep 12, 2015 at 4:14 PM,

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

@Luuk : I tried your query and I cancelled after 9 minutes when it wasn't finished. @Richard : The query isn't done after 15 minutes. I added a small modification, reinserting the WITH clause inside the recursive WITH (and using dates instead of securities in the remainder of the query) : CREATE

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven: > > > Here is the example. It doesn't use an actual aggregation (sum, > count), but > the > > "min" aggregate function. > > > From a list of dates, create a lookup table with two > > columns: the original date (calc_date) and

[sqlite] About backup example

Hi, I see something in the second backup example ( http://www.sqlite.org/backup.html ) that seems like logical error to me. if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){ sqlite3_sleep (250); } Why should we

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 9/12/15, Aurel Wisse wrote: > On 9/11/15, Richard Hipp wrote: > > Here is the example. Have you considered using code like the following instead: -- Create a table "securities(calc_date)" and fill it with lots -- with lots of dates in a crazy order. -- CREATE TABLE securities(calc_date);

[sqlite] sqlite3 file as database

sanam If renaming the file does not work; then use whatever package that can read the file to read it in and write it back out as a comma or tab delimited file. In the SQLite command line interface (CLI, implemented as SQLite3.exe) one can: "Use the ".import" command to import CSV (comma

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

Yes it works, but it takes forever. By comparison: Original recursive query: 7-8 seconds (SQLite 3.8.10.1) Non recursive query (see below): 18-19 seconds. (SQLite 3.8.10.1) Proposed query: Not finished after 10 minutes. I am cancelling. Non recursive query with same result: CREATE TEMP TABLE

[sqlite] Feedback request: JSON support in SQLite

Hello ! Looking at the documentation of json* functions and after see the example given for a query on a field mixed owith string and json_array: ___ SELECT name FROM user WHERE phone LIKE '704-%' UNION SELECT user.nameFROM user, json_each(user.phone) WHERE

[sqlite] Feedback request: JSON support in SQLite

Hello ! I did some tests to see how the json* functions behave when we specify collation to the column that stores it and it seems that the json* functions ignore the collation completely, is that by design or is it a forgotten implementation ? ___ CREATE TABLE a(j collate nocase);

[sqlite] BEGINNER - Transactions in shell script

Petr, Since this is the SQLite mailing list, we are moving away from the intentions of the list, however I think your points need addressing as they may be relevant to other people using this mailing list. I apologise to other people if this is off topic but I think its important enough to

[sqlite] sqlite3 file as database

Have you tried simply renaming the file? On Sep 12, 2015 8:29 AM, wrote: > Hello dears > I have a database file as database.db3 but I have to use database.sqlite3 . > How can I convert the db3 file to sqlite3 file? > Thanks for your help > sanam > ___

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

On 9/11/15, Richard Hipp wrote: >On 9/11/15, Aurel Wisse wrote: >> I used a recursive aggregate query in 3.8.9 and it worked very well. Just >> upgraded to 3.11.1 and the query is broken. >> >> This seems to be directly related to >> >> Check-in [6d2999af]: Do not allow recursive CTEs