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, BinaryProto 18from python.generators.diff_tests.testing import DiffTestBlueprint 19from python.generators.diff_tests.testing import TestSuite 20from python.generators.diff_tests.testing import PrintProfileProto 21from google.protobuf import text_format 22 23 24class PreludeWindowFunctions(TestSuite): 25 26 def test_first_non_null_frame(self): 27 return DiffTestBlueprint( 28 trace=TextProto(r""" 29 30 """), 31 query=""" 32 CREATE TABLE TEST(id INTEGER, val INTEGER); 33 34 INSERT INTO TEST 35 VALUES (1, 1), (2, NULL), (3, 3), (4, 4), (5, NULL), (6, NULL), (7, NULL); 36 37 SELECT 38 id, 39 LAST_NON_NULL(val) 40 OVER (ORDER BY id ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS val 41 FROM TEST 42 ORDER BY id ASC; 43 """, 44 out=Csv(""" 45 "id","val" 46 1,3 47 2,4 48 3,4 49 4,4 50 5,"[NULL]" 51 6,"[NULL]" 52 7,"[NULL]" 53 """)) 54 55 def test_first_non_null_partition(self): 56 return DiffTestBlueprint( 57 trace=TextProto(r""" 58 59 """), 60 query=""" 61 CREATE TABLE TEST(id INTEGER, part TEXT, val INTEGER); 62 63 INSERT INTO TEST 64 VALUES 65 (1, 'A', 1), 66 (2, 'A', NULL), 67 (3, 'A', 3), 68 (4, 'B', NULL), 69 (5, 'B', 5), 70 (6, 'B', NULL), 71 (7, 'B', 7); 72 73 SELECT id, LAST_NON_NULL(val) OVER (PARTITION BY part ORDER BY id ASC) AS val 74 FROM TEST 75 ORDER BY id ASC; 76 """, 77 out=Csv(""" 78 "id","val" 79 1,1 80 2,1 81 3,3 82 4,"[NULL]" 83 5,5 84 6,5 85 7,7 86 """)) 87 88 def test_first_non_null(self): 89 return DiffTestBlueprint( 90 trace=TextProto(r""" 91 92 """), 93 query=""" 94 CREATE TABLE TEST(id INTEGER, val INTEGER); 95 96 INSERT INTO TEST 97 VALUES (1, 1), (2, NULL), (3, 3), (4, 4), (5, NULL), (6, NULL), (7, NULL); 98 99 SELECT id, LAST_NON_NULL(val) OVER (ORDER BY id ASC) AS val 100 FROM TEST 101 ORDER BY id ASC; 102 """, 103 out=Csv(""" 104 "id","val" 105 1,1 106 2,1 107 3,3 108 4,4 109 5,4 110 6,4 111 7,4 112 """)) 113 114 def test_layout(self): 115 return DiffTestBlueprint( 116 trace=TextProto(r""" 117 """), 118 query=""" 119 CREATE TABLE TEST(start INTEGER, end INTEGER); 120 121 INSERT INTO TEST 122 VALUES 123 (1, 5), 124 (2, 4), 125 (3, 8), 126 (6, 7), 127 (6, 7), 128 (6, 7); 129 130 WITH custom_slices as ( 131 SELECT 132 start as ts, 133 end - start as dur 134 FROM test 135 ) 136 SELECT 137 ts, 138 INTERNAL_LAYOUT(ts, dur) over ( 139 order by ts 140 rows between unbounded preceding and current row 141 ) as depth 142 FROM custom_slices 143 """, 144 out=Csv(""" 145 "ts","depth" 146 1,0 147 2,1 148 3,2 149 6,0 150 6,1 151 6,3 152 """)) 153 154 def test_layout_with_instant_events(self): 155 return DiffTestBlueprint( 156 trace=TextProto(r""" 157 """), 158 query=""" 159 CREATE TABLE TEST(start INTEGER, end INTEGER); 160 161 INSERT INTO TEST 162 VALUES 163 (1, 5), 164 (2, 2), 165 (3, 3), 166 (4, 4); 167 168 WITH custom_slices as ( 169 SELECT 170 start as ts, 171 end - start as dur 172 FROM test 173 ) 174 SELECT 175 ts, 176 INTERNAL_LAYOUT(ts, dur) over ( 177 order by ts 178 rows between unbounded preceding and current row 179 ) as depth 180 FROM custom_slices 181 """, 182 out=Csv(""" 183 "ts","depth" 184 1,0 185 2,1 186 3,1 187 4,1 188 """)) 189 190 def test_layout_with_events_without_end(self): 191 return DiffTestBlueprint( 192 trace=TextProto(r""" 193 """), 194 query=""" 195 CREATE TABLE TEST(ts INTEGER, dur INTEGER); 196 197 INSERT INTO TEST 198 VALUES 199 (1, -1), 200 (2, -1), 201 (3, 5), 202 (4, 1), 203 (5, 1); 204 205 SELECT 206 ts, 207 INTERNAL_LAYOUT(ts, dur) over ( 208 order by ts 209 rows between unbounded preceding and current row 210 ) as depth 211 FROM test 212 """, 213 out=Csv(""" 214 "ts","depth" 215 1,0 216 2,1 217 3,2 218 4,3 219 5,3 220 """)) 221