Home | History | Annotate | Download | only in test 
      1 # 2008 December 8
      2 #
      3 # The author disclaims copyright to this source code.  In place of
      4 # a legal notice, here is a blessing:
      5 #
      6 #    May you do good and not evil.
      7 #    May you find forgiveness for yourself and forgive others.
      8 #    May you share freely, never taking more than you give.
      9 #
     10 #***********************************************************************
     11 # This file implements regression tests for SQLite library.
     12 #
     13 # This file is a verification that the bugs identified in ticket
     14 # #3527 have been fixed.
     15 #
     16 # $Id: tkt3527.test,v 1.1 2008/12/08 13:42:36 drh Exp $
     17 
     18 set testdir [file dirname $argv0]
     19 source $testdir/tester.tcl
     20 
     21 do_test tkt3527-1.1 {
     22   db eval {
     23     CREATE TABLE Element (
     24      Code INTEGER PRIMARY KEY,
     25      Name VARCHAR(60)
     26     );
     27     
     28     CREATE TABLE ElemOr (
     29      CodeOr INTEGER NOT NULL,
     30      Code INTEGER NOT NULL,
     31      PRIMARY KEY(CodeOr,Code)
     32     );
     33     
     34     CREATE TABLE ElemAnd (
     35      CodeAnd INTEGER,
     36      Code INTEGER,
     37      Attr1 INTEGER,
     38      Attr2 INTEGER,
     39      Attr3 INTEGER,
     40      PRIMARY KEY(CodeAnd,Code)
     41     );
     42     
     43     INSERT INTO Element VALUES(1,'Elem1');
     44     INSERT INTO Element VALUES(2,'Elem2');
     45     INSERT INTO Element VALUES(3,'Elem3');
     46     INSERT INTO Element VALUES(4,'Elem4');
     47     INSERT INTO Element VALUES(5,'Elem5');
     48     INSERT INTO ElemOr Values(3,4);
     49     INSERT INTO ElemOr Values(3,5);
     50     INSERT INTO ElemAnd VALUES(1,3,1,1,1);
     51     INSERT INTO ElemAnd VALUES(1,2,1,1,1);
     52     
     53     CREATE VIEW ElemView1 AS
     54     SELECT
     55       CAST(Element.Code AS VARCHAR(50)) AS ElemId,
     56      Element.Code AS ElemCode,
     57      Element.Name AS ElemName,
     58      ElemAnd.Code AS InnerCode,
     59      ElemAnd.Attr1 AS Attr1,
     60      ElemAnd.Attr2 AS Attr2,
     61      ElemAnd.Attr3 AS Attr3,
     62      0 AS Level,
     63      0 AS IsOrElem
     64     FROM Element JOIN ElemAnd ON ElemAnd.CodeAnd=Element.Code
     65     WHERE ElemAnd.CodeAnd NOT IN (SELECT CodeOr FROM ElemOr)
     66     UNION ALL
     67     SELECT
     68       CAST(ElemOr.CodeOr AS VARCHAR(50)) AS ElemId,
     69       Element.Code AS ElemCode,
     70       Element.Name AS ElemName,
     71       ElemOr.Code AS InnerCode,
     72       NULL AS Attr1,
     73       NULL AS Attr2,
     74       NULL AS Attr3,
     75       0 AS Level,
     76       1 AS IsOrElem
     77     FROM ElemOr JOIN Element ON Element.Code=ElemOr.CodeOr
     78     ORDER BY ElemId, InnerCode;
     79     
     80     CREATE VIEW ElemView2 AS
     81     SELECT
     82       ElemId,
     83       ElemCode,
     84       ElemName,
     85       InnerCode,
     86       Attr1,
     87       Attr2,
     88       Attr3,
     89       Level,
     90       IsOrElem
     91     FROM ElemView1
     92     UNION ALL
     93     SELECT
     94       Element.ElemId || '.' || InnerElem.ElemId AS ElemId,
     95       InnerElem.ElemCode,
     96       InnerElem.ElemName,
     97       InnerElem.InnerCode,
     98       InnerElem.Attr1,
     99       InnerElem.Attr2,
    100       InnerElem.Attr3,
    101       InnerElem.Level+1,
    102       InnerElem.IsOrElem
    103     FROM ElemView1 AS Element
    104     JOIN ElemView1 AS InnerElem
    105          ON Element.Level=0 AND Element.InnerCode=InnerElem.ElemCode
    106     ORDER BY ElemId, InnerCode;
    107  
    108     SELECT * FROM ElemView1;
    109   }
    110 } {1 1 Elem1 2 1 1 1 0 0 1 1 Elem1 3 1 1 1 0 0 3 3 Elem3 4 {} {} {} 0 1 3 3 Elem3 5 {} {} {} 0 1}
    111    
    112 do_test tkt3527-1.2 {
    113   db eval {
    114     SELECT * FROM ElemView2;
    115   }
    116 } {1 1 Elem1 2 1 1 1 0 0 1 1 Elem1 3 1 1 1 0 0 1.3 3 Elem3 4 {} {} {} 1 1 1.3 3 Elem3 5 {} {} {} 1 1 3 3 Elem3 4 {} {} {} 0 1 3 3 Elem3 5 {} {} {} 0 1}
    117 
    118 finish_test
    119