The rowcounts were:
1) 999 - aurel's original version is 3.8.9
2) 1000 - Luuks non-recursive version, same for my own trial
3) 837 -the version with temp table
4) 838 - DRH;s version
and there is nothing to worry here.
The main difference is explained by a mistake in the format parameter
to
op 13-09-2015 01:45 schreef E.Pasma op pasma10 at concepts.nl:
>>
>> On 2015-09-13 12:40 AM, Aurel Wisse wrote:
>>> @R.Smith
>>> I wasn't aware that recursive aggregate queries give wrong answers
>>> in 99%
>>> of all general use cases//...
>>
>> I apologize - I thought Richard had made that
>
> On 2015-09-13 12:40 AM, Aurel Wisse wrote:
>> @R.Smith
>> I wasn't aware that recursive aggregate queries give wrong answers
>> in 99%
>> of all general use cases//...
>
> I apologize - I thought Richard had made that much clear early on in
> the exchange and you were simply not
On 2015-09-13 12:40 AM, Aurel Wisse wrote:
> @R.Smith
> I wasn't aware that recursive aggregate queries give wrong answers in 99%
> of all general use cases//...
I apologize - I thought Richard had made that much clear early on in the
exchange and you were simply not recognizing or accepting
On 2015-09-12 10:58 PM, Aurel Wisse wrote:
> The fastest solution is actually the temporary table:
Glad you found a faster solution.
> Still Richard : How about allowing recursive aggregate queries again ?
You say "allow" like it's something that worked and they blocked it out
for no good
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
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.
> 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
@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
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
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
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
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
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
@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,
@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
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
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);
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
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
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 that use aggregate queries
> in the recursive
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 that use aggregate queries
in the recursive part.
It worked, and now it is disabled. Why ?
22 matches
Mail list logo
