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 a BIG INT, 20 PRIMARY KEY (ts) 21) without rowid; 22 23create table t2( 24 ts BIG INT, 25 dur BIG INT, 26 b BIG INT, 27 part BIG INT, 28 PRIMARY KEY (part, ts) 29) without rowid; 30 31-- Insert some rows into t2 which are in part 0 and 1 but before t1's rows. 32INSERT INTO t2(ts, dur, part, b) 33VALUES 34(0, 25, 0, 111), 35(50, 50, 0, 222), 36(0, 40, 1, 333); 37 38-- Then insert some rows into t1 in part 1, 3, 4 and 5. 39INSERT INTO t1(ts, dur, a) 40VALUES 41(100, 400, 111), 42(500, 50, 222), 43(600, 100, 333), 44(900, 100, 444); 45 46-- Insert a row into t2 which should be split up by t1's first row. 47INSERT INTO t2(ts, dur, part, b) VALUES (50, 200, 1, 444); 48 49-- Insert a row into t2 should should be completely covered by t1's first row. 50INSERT INTO t2(ts, dur, part, b) VALUES (300, 100, 1, 555); 51 52-- Insert a row into t2 which should span between t1's first and second rows. 53INSERT INTO t2(ts, dur, part, b) VALUES (400, 250, 1, 666); 54 55-- Insert a row into t2 in partition 2. 56INSERT INTO t2(ts, dur, part, b) VALUES (100, 1000, 2, 777); 57 58-- Insert a row into t2 before t1's first row in partition 4. 59INSERT INTO t2(ts, dur, part, b) VALUES (50, 50, 4, 888); 60 61-- Insert a row into t2 which perfectly matches the second row in partition 4. 62INSERT INTO t2(ts, dur, part, b) VALUES (500, 50, 4, 999); 63 64-- Insert a row into t2 which intersects the first row of partition 5. 65INSERT INTO t2(ts, dur, part, b) VALUES (50, 75, 5, 1111); 66 67-- Insert a row into t2 which intersects the third row of partition 5. 68INSERT INTO t2(ts, dur, part, b) VALUES (525, 75, 5, 2222); 69 70-- Insert a row into t2 which misses everything in partition 6. 71INSERT INTO t2(ts, dur, part, b) VALUES (0, 100, 6, 2222); 72 73create virtual table sp using span_left_join(t1, t2 PARTITIONED part); 74 75select * from sp; 76