[sqlite] Segfault when querying deeply nested view

[Andy Chambers]

Hi List,

I've found a situation which causes sqlite to segfault.

System Info
-----------------
OS: Debian Etch
Sqlite Version: 3.3.8

Run the attached sql script to see segfault.

I'd be interested to hear of a workaround if anybody knows of one.  Of
course, I'll post to the list if I find one.

-- Raw data (RBS) --------

create table records (
  date          real,
  type          text,
  description   text,
  value         integer,
  acc_name      text,
  acc_no        text
);

-- Direct Debits ----------------
drop view direct_debits;
create view direct_debits as
  select *
    from records
   where type = 'D/D';

drop view monthly_direct_debits;
create view monthly_direct_debits as
  select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
    from direct_debits
group by strftime('%Y-%m', date);

-- Expense Categories ---------------
drop view energy;
create view energy as
  select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
    from direct_debits
   where description like '%NPOWER%'
group by strftime('%Y-%m', date);

drop view phone_internet;
create view phone_internet as
  select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
    from direct_debits
   where description like '%BT DIRECT%'
      or description like '%SUPANET%'
      or description like '%ORANGE%'
group by strftime('%Y-%m', date);

drop view credit_cards;
create view credit_cards as
  select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
    from direct_debits
   where description like '%VISA%'
group by strftime('%Y-%m', date);

-- Overview ---------------------

drop view expense_overview;
create view expense_overview as
  select 'Energy' as expense, date, value
    from energy
  union
  select 'Phone/Internet' as expense, date, value
    from phone_internet
  union
  select 'Credit Card' as expense, date, value
    from credit_cards;

drop view jan;
create view jan as
  select 'jan', expense, value
    from expense_overview
   where date like '%-01';

drop view nov;
create view nov as
  select 'nov', expense, value
    from expense_overview
   where date like '%-11';

drop view summary;
create view summary as
  select *
    from jan join nov
      on (jan.expense = nov.expense);

-- This causes a segfault
select * 
  from summary;
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------