[sqlite] Segfault when querying deeply nested view
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_notext
);
-- 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]
-
[sqlite] Segfault with deeply nested views
Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the sql listed below 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_notext
);
-- 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]
-
[sqlite] vertical -> horizontal data conversion
Hi, I have a table like this create table clinical_data ( group_def text, item_def text, value text ); Assuming this example data... GROUP_DEF, ITEM_DEF, VALUE --- "MEDHIST", "BODSYS", "foo" "MEDHIST", "TERM", "bar" "MEDHIST", "ONSET", "baz" I'd like to create a view that looks like BODSYS, TERM, ONSET --- "foo", "bar", "baz" I've got a feeling the answer lies in outer joins but my SQL is a little rusty. This seems like something people would need to do a lot. Is there a name for this that I can go lookup? Many Thanks, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] maximum length for a column name
Hi, I've checked the "Limits in sqlite" page and don't see any limits to tablenames or columnnames. Does that mean there isn't any? (or at least they're only limited by the maximum length of a sql statement). Cheers, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Version compatibility
Hi, I've been reading about version compatibility between different versions of sqlite at the link below: http://www.sqlite.org/formatchng.html It states the expected behaviour for old and new with a different first number, and a different second number, but not a different third number. I presume this means that a different third number means old and new are completely compatible but is there anything I can show to my QA person that states this? Cheers, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Distinct Bug
Is this a bug?
create table t_distinct_bug (
a,
b,
c
);
insert into t_distinct_bug values ('1', '1', 'a');
insert into t_distinct_bug values ('1', '2', 'b');
insert into t_distinct_bug values ('1', '3', 'c');
insert into t_distinct_bug values ('1', '1', 'd');
insert into t_distinct_bug values ('1', '2', 'e');
insert into t_distinct_bug values ('1', '3', 'f');
select a
from (select distinct a, b
from t_distinct_bug)
=> 1
I'd have thought it should return
1
1
1
I'm on SQLite 3.6.22
--
Andy Chambers
Formedix Ltd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Vertical -> Horizontal transformation
Hi, I've got a nice normalized table and need to produce a de-normalized view of this table (i.e. convert it from tall skinny, into wide short table). In order to do this, I was planning on just joining the table to itself for each "value" that needs to be turned into a column. However, it turns out there's 96 values so I run into the "only 64 tables in a join" constraint. Is there another idiom for doing this sort of transformation or should I just use two helper tables with 33 values in each and then join them. For those who have used other databases, is it quite common to have this constraint? This seems like it would be quite a common problem in ETL projects (admittedly it's perhaps unusual to be using sqlite for an ETL project). Hope I've made myself clear. Cheers, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vertical -> Horizontal transformation
On Wed, Sep 29, 2010 at 6:16 PM, Petite Abeille wrote: > > On Sep 29, 2010, at 7:05 PM, Simon Slavin wrote: > >> SQL (not just SQLite) is traditionally terribly bad at doing matrix >> inversions. What do you want to use the wide short table for ? Does it >> really need to exist as a table ? > > Just for fun (and profit): > > http://www.orafaq.com/wiki/PIVOT > http://www.orafaq.com/wiki/UNPIVOT Ah, this is a pivot!!! Doh. And this [1] recipe shows how you can do it in SQLite. Thanks [1] http://softwaresalariman.blogspot.com/2008/05/pivot-table-hack-in-sqlite3-and-mysql.html I wonder if this is also more efficient To Simon, yes, we do *really* need the short wide structure but it doesn't necessarily have to be a table (i.e. could be a view). We do need it to be a database object though because a subsequent step of the process queries it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tricky grouping query
Given the following
create table events (
id,
date,
status
);
insert into events values ('001','a','N');
insert into events values ('001','b','N');
insert into events values ('001','c','Y');
insert into events values ('001','d','N');
insert into events values ('001','e','Y');
insert into events values ('001','f','Y');
insert into events values ('001','g','N');
Is it possible, using plain SQL, to derive the following
001,c,d
001,e,g
i.e. an "N" in the third column means event "001" has stopped, and a
"Y" means it
has started back up again. Note that because the status immediately
preceding "f"
is also "Y", there is no corresponding row in the output
Cheers,
Andy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
