# 2007 January 26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests to verify that ticket #2192 has been # fixed. # # # $Id: tkt2192.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !datetime||!compound { finish_test return } do_test tkt2192-1.1 { execsql { -- Raw data (RBS) -------- create table records ( date real, type text, description text, value integer, acc_name text, acc_no text ); -- Direct Debits ---------------- create view direct_debits as select * from records where type = 'D/D'; 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 --------------- 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); 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); 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 --------------------- 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; create view jan as select 'jan', expense, value from expense_overview where date like '%-01'; create view nov as select 'nov', expense, value from expense_overview where date like '%-11'; create view summary as select * from jan join nov on (jan.expense = nov.expense); } } {} do_test tkt2192-1.2 { # set ::sqlite_addop_trace 1 execsql { select * from summary; } } {} do_test tkt2192-2.1 { execsql { CREATE TABLE t1(a,b); CREATE VIEW v1 AS SELECT * FROM t1 WHERE b%7=0 UNION SELECT * FROM t1 WHERE b%5=0; INSERT INTO t1 VALUES(1,7); INSERT INTO t1 VALUES(2,10); INSERT INTO t1 VALUES(3,14); INSERT INTO t1 VALUES(4,15); INSERT INTO t1 VALUES(1,16); INSERT INTO t1 VALUES(2,17); INSERT INTO t1 VALUES(3,20); INSERT INTO t1 VALUES(4,21); INSERT INTO t1 VALUES(1,22); INSERT INTO t1 VALUES(2,24); INSERT INTO t1 VALUES(3,25); INSERT INTO t1 VALUES(4,26); INSERT INTO t1 VALUES(1,27); SELECT b FROM v1 ORDER BY b; } } {7 10 14 15 20 21 25} do_test tkt2192-2.2 { execsql { SELECT * FROM v1 ORDER BY a, b; } } {1 7 2 10 3 14 3 20 3 25 4 15 4 21} do_test tkt2192-2.3 { execsql { SELECT x.a || '/' || x.b || '/' || y.b FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b