1--
2-- Copyright 2019 The Android Open Source Project
3--
4-- Licensed under the Apache License, Version 2.0 (the "License");
5-- you may not use this file except in compliance with the License.
6-- You may obtain a copy of the License at
7--
8--     https://www.apache.org/licenses/LICENSE-2.0
9--
10-- Unless required by applicable law or agreed to in writing, software
11-- distributed under the License is distributed on an "AS IS" BASIS,
12-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13-- See the License for the specific language governing permissions and
14-- limitations under the License.
15--
16create table t1(
17  ts BIG INT,
18  dur BIG INT,
19  part BIG INT,
20  a BIG INT,
21  PRIMARY KEY (part, ts)
22) without rowid;
23
24create table t2(
25  ts BIG INT,
26  dur BIG INT,
27  part BIG INT,
28  b BIG INT,
29  PRIMARY KEY (part, ts)
30) without rowid;
31
32-- Insert some rows into t2 which are in part 0 and 1 but before t1's rows.
33INSERT INTO t2(ts, dur, part, b)
34VALUES
35(0, 100, 0, 111),
36(100, 200, 0, 222),
37(0, 50, 1, 333);
38
39-- Then insert some rows into t1 in part 1, 3, 4 and 5.
40INSERT INTO t1(ts, dur, part, a)
41VALUES
42(100, 400, 1, 111),
43(500, 50, 1, 222),
44(600, 100, 1, 333),
45(500, 100, 3, 444),
46(100, 100, 4, 555),
47(200, 50, 4, 666),
48(250, 50, 4, 777),
49(100, 100, 5, 888);
50
51-- Insert a row into t2 which should be split up by t1's first row.
52INSERT INTO t2(ts, dur, part, b) VALUES (50, 200, 1, 444);
53
54-- Insert a row into t2 should should be completely covered by t1's first row.
55INSERT INTO t2(ts, dur, part, b) VALUES (300, 100, 1, 555);
56
57-- Insert a row into t2 which should span between t1's first and second rows.
58INSERT INTO t2(ts, dur, part, b) VALUES (400, 250, 1, 666);
59
60-- Insert a row into t2 in partition 2.
61INSERT INTO t2(ts, dur, part, b) VALUES (100, 1000, 2, 777);
62
63-- Insert a row into t2 before t1's first row in partition 4.
64INSERT INTO t2(ts, dur, part, b) VALUES (50, 50, 4, 888);
65
66-- Insert a row into t2 which perfectly matches the second row in partition 4.
67INSERT INTO t2(ts, dur, part, b) VALUES (200, 50, 4, 999);
68
69-- Insert a row into t2 which intersects the first row of partition 5.
70INSERT INTO t2(ts, dur, part, b) VALUES (125, 50, 5, 1111);
71
72-- Insert a row into t2 which intersects the first row of partition 5.
73INSERT INTO t2(ts, dur, part, b) VALUES (190, 20, 5, 2222);
74
75create virtual table sp using span_outer_join(t1 PARTITIONED part,
76                                              t2 PARTITIONED part);
77
78select * from sp;
79