xref: /sqlite-3.40.0/test/tkt3527.test (revision 21172c4c) 
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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21ifcapable !compound {
22  finish_test
23  return
24}
25
26do_test tkt3527-1.1 {
27  db eval {
28    CREATE TABLE Element (
29     Code INTEGER PRIMARY KEY,
30     Name VARCHAR(60)
31    );
32
33    CREATE TABLE ElemOr (
34     CodeOr INTEGER NOT NULL,
35     Code INTEGER NOT NULL,
36     PRIMARY KEY(CodeOr,Code)
37    );
38
39    CREATE TABLE ElemAnd (
40     CodeAnd INTEGER,
41     Code INTEGER,
42     Attr1 INTEGER,
43     Attr2 INTEGER,
44     Attr3 INTEGER,
45     PRIMARY KEY(CodeAnd,Code)
46    );
47
48    INSERT INTO Element VALUES(1,'Elem1');
49    INSERT INTO Element VALUES(2,'Elem2');
50    INSERT INTO Element VALUES(3,'Elem3');
51    INSERT INTO Element VALUES(4,'Elem4');
52    INSERT INTO Element VALUES(5,'Elem5');
53    INSERT INTO ElemOr Values(3,4);
54    INSERT INTO ElemOr Values(3,5);
55    INSERT INTO ElemAnd VALUES(1,3,'a','b','c');
56    INSERT INTO ElemAnd VALUES(1,2,'x','y','z');
57
58    CREATE VIEW ElemView1 AS
59    SELECT
60      CAST(Element.Code AS VARCHAR(50)) AS ElemId,
61     Element.Code AS ElemCode,
62     Element.Name AS ElemName,
63     ElemAnd.Code AS InnerCode,
64     ElemAnd.Attr1 AS Attr1,
65     ElemAnd.Attr2 AS Attr2,
66     ElemAnd.Attr3 AS Attr3,
67     0 AS Level,
68     0 AS IsOrElem
69    FROM Element JOIN ElemAnd ON ElemAnd.CodeAnd=Element.Code
70    WHERE ElemAnd.CodeAnd NOT IN (SELECT CodeOr FROM ElemOr)
71    UNION ALL
72    SELECT
73      CAST(ElemOr.CodeOr AS VARCHAR(50)) AS ElemId,
74      Element.Code AS ElemCode,
75      Element.Name AS ElemName,
76      ElemOr.Code AS InnerCode,
77      NULL AS Attr1,
78      NULL AS Attr2,
79      NULL AS Attr3,
80      0 AS Level,
81      1 AS IsOrElem
82    FROM ElemOr JOIN Element ON Element.Code=ElemOr.CodeOr
83    ORDER BY ElemId, InnerCode;
84
85    CREATE VIEW ElemView2 AS
86    SELECT
87      ElemId,
88      ElemCode,
89      ElemName,
90      InnerCode,
91      Attr1,
92      Attr2,
93      Attr3,
94      Level,
95      IsOrElem
96    FROM ElemView1
97    UNION ALL
98    SELECT
99      Element.ElemId || '.' || InnerElem.ElemId AS ElemId,
100      InnerElem.ElemCode,
101      InnerElem.ElemName,
102      InnerElem.InnerCode,
103      InnerElem.Attr1,
104      InnerElem.Attr2,
105      InnerElem.Attr3,
106      InnerElem.Level+1,
107      InnerElem.IsOrElem
108    FROM ElemView1 AS Element
109    JOIN ElemView1 AS InnerElem
110         ON Element.Level=0 AND Element.InnerCode=InnerElem.ElemCode
111    ORDER BY ElemId, InnerCode;
112
113    SELECT * FROM ElemView1;
114  }
115} {1 1 Elem1 2 x y z 0 0 1 1 Elem1 3 a b c 0 0 3 3 Elem3 4 {} {} {} 0 1 3 3 Elem3 5 {} {} {} 0 1}
116
117do_test tkt3527-1.2 {
118  db eval {
119    SELECT * FROM ElemView2;
120  }
121} {1 1 Elem1 2 x y z 0 0 1 1 Elem1 3 a b c 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}
122
123finish_test
124