On 9/11/15, Richard Hipp <drh at sqlite.org> wrote:
>On 9/11/15, Aurel Wisse <aurel.wisse at gmail.com> 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 part.
>>
>> It worked, and now it is disabled. Why ?
>>
>
>If it worked for you before, that was luck. Aggregate queries in a
>recursive CTE have never worked for the general case. In fact, I
>didn't realize that there were any cases where it did actually work.
>Can you share with use an example of an aggregate query in a recursive
>CTE that gives a reasonable answer?
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 the date after calc_date (tmw).
CREATE TABLE offsets AS
WITH RECURSIVE dtoff(calc_date, tmw) AS (
WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
SELECT NULL, min(cd) FROM dates
UNION
SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates
WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
AND strftime('%%w', dates.cd)<>'0')
SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
--
Aurel Wisse
