github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/deep_interleaving (about)

     1  # Test querying deeply interleaved tables.
     2  
     3  statement ok
     4  CREATE TABLE level1 (
     5    k1 INT,
     6    PRIMARY KEY (k1)
     7  )
     8  
     9  statement ok
    10  CREATE TABLE level2 (
    11    k1 INT,
    12    PRIMARY KEY (k1),
    13    CONSTRAINT fk1 FOREIGN KEY (k1) REFERENCES level1
    14  ) INTERLEAVE IN PARENT level1 (k1)
    15  
    16  statement ok
    17  CREATE TABLE level3 (
    18    k1 INT,
    19    k2 INT,
    20    k3 INT,
    21    PRIMARY KEY (k1, k2, k3),
    22    CONSTRAINT fk2 FOREIGN KEY (k1) REFERENCES level2
    23  ) INTERLEAVE IN PARENT level2 (k1)
    24  
    25  statement ok
    26  CREATE TABLE level4 (
    27    k1 INT,
    28    k2 INT,
    29    k3 INT,
    30    PRIMARY KEY (k1, k2, k3),
    31    CONSTRAINT fk3 FOREIGN KEY (k1, k2, k3) REFERENCES level3
    32  ) INTERLEAVE IN PARENT level3 (k1, k2, k3)
    33  
    34  statement ok
    35  INSERT INTO level1 VALUES (1), (2), (3)
    36  
    37  statement ok
    38  INSERT INTO level2 VALUES (1), (2), (3)
    39  
    40  statement ok
    41  INSERT INTO level3 VALUES
    42    (1, 10, 100), (1, 10, 200), (1, 10, 300),
    43    (1, 20, 100), (1, 20, 200), (1, 20, 300),
    44    (1, 30, 100), (1, 30, 200), (1, 30, 300),
    45    (2, 10, 100), (2, 10, 200), (2, 10, 300),
    46    (2, 20, 100), (2, 20, 200), (2, 20, 300),
    47    (2, 30, 100), (2, 30, 200), (2, 30, 300),
    48    (3, 10, 100), (3, 10, 200), (3, 10, 300),
    49    (3, 20, 100), (3, 20, 200), (3, 20, 300),
    50    (3, 30, 100), (3, 30, 200), (3, 30, 300)
    51  
    52  statement ok
    53  INSERT INTO level4 VALUES
    54    (1, 10, 100), (1, 10, 200), (1, 10, 300),
    55    (1, 20, 100), (1, 20, 200), (1, 20, 300),
    56    (1, 30, 100), (1, 30, 200), (1, 30, 300),
    57    (2, 10, 100), (2, 10, 200), (2, 10, 300),
    58    (2, 20, 100), (2, 20, 200), (2, 20, 300),
    59    (2, 30, 100), (2, 30, 200), (2, 30, 300),
    60    (3, 10, 100), (3, 10, 200), (3, 10, 300),
    61    (3, 20, 100), (3, 20, 200), (3, 20, 300),
    62    (3, 30, 100), (3, 30, 200), (3, 30, 300)
    63  
    64  query III rowsort
    65  SELECT * FROM level4
    66  ----
    67  1  10  100
    68  1  10  200
    69  1  10  300
    70  1  20  100
    71  1  20  200
    72  1  20  300
    73  1  30  100
    74  1  30  200
    75  1  30  300
    76  2  10  100
    77  2  10  200
    78  2  10  300
    79  2  20  100
    80  2  20  200
    81  2  20  300
    82  2  30  100
    83  2  30  200
    84  2  30  300
    85  3  10  100
    86  3  10  200
    87  3  10  300
    88  3  20  100
    89  3  20  200
    90  3  20  300
    91  3  30  100
    92  3  30  200
    93  3  30  300
    94  
    95  query III rowsort
    96  SELECT * FROM level4 WHERE k1 > 1 AND k1 < 3
    97  ----
    98  2  10  100
    99  2  10  200
   100  2  10  300
   101  2  20  100
   102  2  20  200
   103  2  20  300
   104  2  30  100
   105  2  30  200
   106  2  30  300
   107  
   108  query III rowsort
   109  SELECT * FROM level4 WHERE k1 = 2 AND k2 > 10 AND k2 < 30
   110  ----
   111  2  20  100
   112  2  20  200
   113  2  20  300
   114  
   115  query III
   116  SELECT * FROM level4 WHERE k1 = 2 AND k2 = 20 AND k3 > 100 AND k3 < 300
   117  ----
   118  2  20  200
   119  
   120  query III
   121  SELECT * FROM level4 WHERE k1 = 2 AND k2 = 20 AND k3 = 200
   122  ----
   123  2  20  200