github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/tkt3527.test (about)

     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  ifcapable !compound {
    22    finish_test
    23    return
    24  }
    25  
    26  do_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     
   117  do_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  
   123  finish_test