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

[Richard Hipp]

On 9/12/15, Aurel Wisse <aurel.wisse at gmail.com> wrote:
> On 9/11/15, Richard Hipp <drh at sqlite.org> 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);
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<1000)
INSERT INTO securities(calc_date)
   SELECT datetime(2440587.5 + ((x*123456789012345)%16700000)/1000.0) FROM c;
CREATE INDEX securities_calcdate ON securities(calc_date);


-- Compute the "offsets(calc_date, tmw)" which has one entry for each
-- calc_date in securities and for which tmw is the first non-Sunday date
-- in securities that comes after calc_date.
--
CREATE TABLE offsets AS
  WITH RECURSIVE dtoff(calc_date, tmw) AS (
    SELECT NULL, (SELECT min(calc_date) FROM securities)
    UNION
    SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
                        WHERE securities.calc_date>dtoff.tmw
                          AND strftime('%w',securities.calc_date)<>'0')
      FROM dtoff
  )
  SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
SELECT * FROM offsets;


-- 
D. Richard Hipp
drh at sqlite.org