1#!/usr/bin/env python3 2# Copyright (C) 2023 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 a 7# 8# http://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 16from python.generators.diff_tests.testing import Path, DataPath, Metric 17from python.generators.diff_tests.testing import Csv, Json, TextProto 18from python.generators.diff_tests.testing import DiffTestBlueprint 19from python.generators.diff_tests.testing import TestSuite 20 21 22class SpanJoinOuterJoin(TestSuite): 23 24 def test_span_outer_join(self): 25 return DiffTestBlueprint( 26 trace=Path('../../common/synth_1.py'), 27 query=Path('span_outer_join_test.sql'), 28 out=Path('span_outer_join.out')) 29 30 def test_span_outer_join_empty(self): 31 return DiffTestBlueprint( 32 trace=Path('../../common/synth_1.py'), 33 query=""" 34 CREATE TABLE t1( 35 ts BIGINT, 36 dur BIGINT, 37 part BIGINT, 38 PRIMARY KEY (part, ts) 39 ) WITHOUT ROWID; 40 41 CREATE TABLE t2( 42 ts BIGINT, 43 dur BIGINT, 44 part BIGINT, 45 PRIMARY KEY (part, ts) 46 ) WITHOUT ROWID; 47 48 INSERT INTO t1(ts, dur, part) 49 VALUES (500, 100, 10); 50 51 52 CREATE VIRTUAL TABLE sp USING span_outer_join(t1 PARTITIONED part, 53 t2 PARTITIONED part); 54 55 SELECT * FROM sp; 56 """, 57 out=Csv(""" 58 "ts","dur","part" 59 500,100,10 60 """)) 61 62 def test_span_outer_join_unpartitioned_empty(self): 63 return DiffTestBlueprint( 64 trace=Path('../../common/synth_1.py'), 65 query=""" 66 CREATE TABLE t1( 67 ts BIGINT, 68 dur BIGINT, 69 PRIMARY KEY (ts) 70 ) WITHOUT ROWID; 71 72 CREATE TABLE t2( 73 ts BIGINT, 74 dur BIGINT, 75 PRIMARY KEY (ts) 76 ) WITHOUT ROWID; 77 78 79 CREATE VIRTUAL TABLE sp USING span_outer_join(t1, t2); 80 81 SELECT * FROM sp; 82 """, 83 out=Csv(""" 84 "ts","dur" 85 """)) 86 87 def test_span_outer_join_unpartitioned_left_empty(self): 88 return DiffTestBlueprint( 89 trace=Path('../../common/synth_1.py'), 90 query=""" 91 CREATE TABLE t1( 92 ts BIGINT, 93 dur BIGINT, 94 PRIMARY KEY (ts) 95 ) WITHOUT ROWID; 96 97 CREATE TABLE t2( 98 ts BIGINT, 99 dur BIGINT, 100 PRIMARY KEY (ts) 101 ) WITHOUT ROWID; 102 103 INSERT INTO t2(ts, dur) 104 VALUES 105 (100, 400), 106 (500, 50), 107 (600, 100); 108 109 CREATE VIRTUAL TABLE sp USING span_outer_join(t1, t2); 110 111 SELECT * FROM sp; 112 """, 113 out=Csv(""" 114 "ts","dur" 115 100,400 116 500,50 117 600,100 118 """)) 119 120 def test_span_outer_join_unpartitioned_right_empty(self): 121 return DiffTestBlueprint( 122 trace=Path('../../common/synth_1.py'), 123 query=""" 124 CREATE TABLE t1( 125 ts BIGINT, 126 dur BIGINT, 127 PRIMARY KEY (ts) 128 ) WITHOUT ROWID; 129 130 CREATE TABLE t2( 131 ts BIGINT, 132 dur BIGINT, 133 PRIMARY KEY (ts) 134 ) WITHOUT ROWID; 135 136 INSERT INTO t1(ts, dur) 137 VALUES 138 (100, 400), 139 (500, 50), 140 (600, 100); 141 142 CREATE VIRTUAL TABLE sp USING span_outer_join(t1, t2); 143 144 SELECT * FROM sp; 145 """, 146 out=Csv(""" 147 "ts","dur" 148 100,400 149 500,50 150 600,100 151 """)) 152 153 def test_span_outer_join_mixed(self): 154 return DiffTestBlueprint( 155 trace=Path('../../common/synth_1.py'), 156 query=Path('span_outer_join_mixed_test.sql'), 157 out=Path('span_outer_join_mixed.out')) 158 159 def test_span_outer_join_mixed_empty(self): 160 return DiffTestBlueprint( 161 trace=Path('../../common/synth_1.py'), 162 query=""" 163 CREATE TABLE t1( 164 ts BIGINT, 165 dur BIGINT, 166 part BIGINT, 167 PRIMARY KEY (part, ts) 168 ) WITHOUT ROWID; 169 170 CREATE TABLE t2( 171 ts BIGINT, 172 dur BIGINT, 173 PRIMARY KEY (ts) 174 ) WITHOUT ROWID; 175 176 177 CREATE VIRTUAL TABLE sp USING span_outer_join(t1 PARTITIONED part, t2); 178 179 SELECT * FROM sp; 180 """, 181 out=Csv(""" 182 "ts","dur","part" 183 """)) 184 185 def test_span_outer_join_mixed_left_empty(self): 186 return DiffTestBlueprint( 187 trace=Path('../../common/synth_1.py'), 188 query=""" 189 CREATE TABLE t1( 190 ts BIGINT, 191 dur BIGINT, 192 part BIGINT, 193 PRIMARY KEY (part, ts) 194 ) WITHOUT ROWID; 195 196 CREATE TABLE t2( 197 ts BIGINT, 198 dur BIGINT, 199 PRIMARY KEY (ts) 200 ) WITHOUT ROWID; 201 202 INSERT INTO t2(ts, dur) 203 VALUES 204 (100, 400), 205 (500, 50), 206 (600, 100); 207 208 CREATE VIRTUAL TABLE sp USING span_outer_join(t1 PARTITIONED part, t2); 209 210 SELECT * FROM sp; 211 """, 212 out=Csv(""" 213 "ts","dur","part" 214 """)) 215 216 def test_span_outer_join_mixed_left_empty_rev(self): 217 return DiffTestBlueprint( 218 trace=Path('../../common/synth_1.py'), 219 query=""" 220 CREATE TABLE t1( 221 ts BIGINT, 222 dur BIGINT, 223 part BIGINT, 224 PRIMARY KEY (part, ts) 225 ) WITHOUT ROWID; 226 227 CREATE TABLE t2( 228 ts BIGINT, 229 dur BIGINT, 230 PRIMARY KEY (ts) 231 ) WITHOUT ROWID; 232 233 INSERT INTO t1(ts, dur, part) 234 VALUES 235 (100, 400, 0), 236 (100, 50, 1), 237 (600, 100, 1); 238 239 CREATE VIRTUAL TABLE sp USING span_outer_join(t2, t1 PARTITIONED part); 240 241 SELECT * FROM sp; 242 """, 243 out=Csv(""" 244 "ts","dur","part" 245 100,400,0 246 100,50,1 247 600,100,1 248 """)) 249 250 def test_span_outer_join_mixed_right_empty(self): 251 return DiffTestBlueprint( 252 trace=Path('../../common/synth_1.py'), 253 query=""" 254 CREATE TABLE t1( 255 ts BIGINT, 256 dur BIGINT, 257 part BIGINT, 258 PRIMARY KEY (part, ts) 259 ) WITHOUT ROWID; 260 261 CREATE TABLE t2( 262 ts BIGINT, 263 dur BIGINT, 264 b BIGINT, 265 PRIMARY KEY (ts) 266 ) WITHOUT ROWID; 267 268 INSERT INTO t1(ts, dur, part) 269 VALUES 270 (100, 400, 0), 271 (100, 50, 1), 272 (600, 100, 1); 273 274 CREATE VIRTUAL TABLE sp USING span_outer_join(t1 PARTITIONED part, t2); 275 276 SELECT * FROM sp; 277 """, 278 out=Csv(""" 279 "ts","dur","part","b" 280 100,400,0,"[NULL]" 281 100,50,1,"[NULL]" 282 600,100,1,"[NULL]" 283 """)) 284 285 def test_span_outer_join_mixed_right_empty_rev(self): 286 return DiffTestBlueprint( 287 trace=Path('../../common/synth_1.py'), 288 query=""" 289 CREATE TABLE t1( 290 ts BIGINT, 291 dur BIGINT, 292 part BIGINT, 293 PRIMARY KEY (part, ts) 294 ) WITHOUT ROWID; 295 296 CREATE TABLE t2( 297 ts BIGINT, 298 dur BIGINT, 299 b BIGINT, 300 PRIMARY KEY (ts) 301 ) WITHOUT ROWID; 302 303 INSERT INTO t2(ts, dur) 304 VALUES 305 (100, 400), 306 (500, 50), 307 (600, 100); 308 309 CREATE VIRTUAL TABLE sp USING span_outer_join(t2, t1 PARTITIONED part); 310 311 SELECT * FROM sp; 312 """, 313 out=Csv(""" 314 "ts","dur","part","b" 315 """)) 316 317 def test_span_outer_join_mixed_2(self): 318 return DiffTestBlueprint( 319 trace=Path('../../common/synth_1.py'), 320 query=Path('span_outer_join_mixed_test.sql'), 321 out=Path('span_outer_join_mixed.out')) 322