1 /*
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 at
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  */
16 
17 #include "src/trace_processor/perfetto_sql/engine/perfetto_sql_engine.h"
18 
19 #include "src/trace_processor/sqlite/sql_source.h"
20 #include "src/trace_processor/tables/slice_tables_py.h"
21 #include "test/gtest_and_gmock.h"
22 
23 namespace perfetto {
24 namespace trace_processor {
25 namespace {
26 
27 class PerfettoSqlEngineTest : public ::testing::Test {
28  protected:
29   StringPool pool_;
30   PerfettoSqlEngine engine_{&pool_, true};
31 };
32 
CreateTestPackage(std::vector<std::pair<std::string,std::string>> files)33 sql_modules::RegisteredPackage CreateTestPackage(
34     std::vector<std::pair<std::string, std::string>> files) {
35   sql_modules::RegisteredPackage result;
36   for (auto& file : files) {
37     result.modules[file.first] =
38         sql_modules::RegisteredPackage::ModuleFile{file.second, false};
39   }
40   return result;
41 }
42 
43 // These are the smoke tests for the perfetto SQL engine, focusing on
44 // ensuring that the correct statements do not return an error and that
45 // incorrect statements do.
46 //
47 // Functional tests are covered by the diff tests in
48 // test/trace_processor/diff_tests/syntax/perfetto_sql.
49 
TEST_F(PerfettoSqlEngineTest,Function_Create)50 TEST_F(PerfettoSqlEngineTest, Function_Create) {
51   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
52       "CREATE PERFETTO FUNCTION foo() RETURNS INT AS select 1"));
53   ASSERT_TRUE(res.ok()) << res.status().c_message();
54 
55   res = engine_.Execute(
56       SqlSource::FromExecuteQuery("creatE PeRfEttO FUNCTION foo(x INT, y LONG) "
57                                   "RETURNS INT AS select $x + $y"));
58   ASSERT_TRUE(res.ok()) << res.status().c_message();
59 }
60 
TEST_F(PerfettoSqlEngineTest,Function_CreateWithArgs)61 TEST_F(PerfettoSqlEngineTest, Function_CreateWithArgs) {
62   auto res = engine_.ExecuteUntilLastStatement(
63       SqlSource::FromExecuteQuery("creatE PeRfEttO FUNCTION foo(x INT, y LONG) "
64                                   "RETURNS INT AS select $x + $y;"
65                                   "SELECT foo(1, 2)"));
66   ASSERT_TRUE(res.ok()) << res.status().c_message();
67   ASSERT_FALSE(res->stmt.IsDone());
68   ASSERT_EQ(sqlite3_column_int64(res->stmt.sqlite_stmt(), 0), 3);
69   ASSERT_FALSE(res->stmt.Step());
70 }
71 
TEST_F(PerfettoSqlEngineTest,Function_Invalid)72 TEST_F(PerfettoSqlEngineTest, Function_Invalid) {
73   auto res = engine_.ExecuteUntilLastStatement(
74       SqlSource::FromExecuteQuery("creatE PeRfEttO FUNCTION foo(x INT, y LONG) "
75                                   "AS select $x + $y;"
76                                   "SELECT foo(1, 2)"));
77   ASSERT_FALSE(res.ok());
78 }
79 
TEST_F(PerfettoSqlEngineTest,Function_Duplicates)80 TEST_F(PerfettoSqlEngineTest, Function_Duplicates) {
81   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
82       "CREATE PERFETTO FUNCTION foo() RETURNS INT AS SELECT 1"));
83   ASSERT_TRUE(res.ok()) << res.status().c_message();
84 
85   res = engine_.Execute(SqlSource::FromExecuteQuery(
86       "CREATE PERFETTO FUNCTION foo() RETURNS INT AS SELECT 2"));
87   ASSERT_FALSE(res.ok());
88 
89   res = engine_.Execute(SqlSource::FromExecuteQuery(
90       "CREATE OR REPLACE PERFETTO FUNCTION foo() RETURNS INT AS SELECT 3"));
91   ASSERT_TRUE(res.ok()) << res.status().c_message();
92 }
93 
TEST_F(PerfettoSqlEngineTest,TableFunction_Create)94 TEST_F(PerfettoSqlEngineTest, TableFunction_Create) {
95   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
96       "CREATE PERFETTO FUNCTION foo() RETURNS TABLE(x INT) AS "
97       "select 1 AS x"));
98   ASSERT_TRUE(res.ok()) << res.status().c_message();
99 }
100 
TEST_F(PerfettoSqlEngineTest,TableFunction_Duplicates)101 TEST_F(PerfettoSqlEngineTest, TableFunction_Duplicates) {
102   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
103       "CREATE PERFETTO FUNCTION foo() RETURNS TABLE(x INT) AS "
104       "select 1 AS x"));
105   ASSERT_TRUE(res.ok()) << res.status().c_message();
106 
107   res = engine_.Execute(SqlSource::FromExecuteQuery(
108       "CREATE PERFETTO FUNCTION foo() RETURNS TABLE(x INT) AS "
109       "select 1 AS x"));
110   ASSERT_FALSE(res.ok());
111 
112   res = engine_.Execute(SqlSource::FromExecuteQuery(
113       "CREATE OR REPLACE PERFETTO FUNCTION foo() RETURNS TABLE(x INT) AS "
114       "select 2 AS x"));
115   ASSERT_TRUE(res.ok()) << res.status().c_message();
116 }
117 
TEST_F(PerfettoSqlEngineTest,Table_Create)118 TEST_F(PerfettoSqlEngineTest, Table_Create) {
119   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
120       "CREATE PERFETTO TABLE foo AS SELECT 42 AS bar"));
121   ASSERT_TRUE(res.ok()) << res.status().c_message();
122 }
123 
TEST_F(PerfettoSqlEngineTest,Table_StringColumns)124 TEST_F(PerfettoSqlEngineTest, Table_StringColumns) {
125   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
126       "CREATE PERFETTO TABLE foo AS SELECT 'foo' AS bar"));
127   ASSERT_TRUE(res.ok()) << res.status().c_message();
128 }
129 
TEST_F(PerfettoSqlEngineTest,Table_Schema)130 TEST_F(PerfettoSqlEngineTest, Table_Schema) {
131   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
132       "CREATE PERFETTO TABLE foo(bar INT) AS SELECT 42 AS bar"));
133   ASSERT_TRUE(res.ok()) << res.status().c_message();
134 
135   res = engine_.Execute(SqlSource::FromExecuteQuery(
136       "CREATE PERFETTO TABLE foo2(bar INT) AS SELECT 42 AS bar; SELECT 1"));
137   ASSERT_TRUE(res.ok()) << res.status().c_message();
138 }
139 
TEST_F(PerfettoSqlEngineTest,Table_Schema_EmptyTable)140 TEST_F(PerfettoSqlEngineTest, Table_Schema_EmptyTable) {
141   // This test checks that the type checks correctly work on empty tables (and
142   // that columns with no data do not default to "int").
143   auto res = engine_.Execute(
144       SqlSource::FromExecuteQuery("CREATE PERFETTO TABLE foo(bar STRING) AS "
145                                   "SELECT 'bar' as bar WHERE bar = 'foo'"));
146   ASSERT_TRUE(res.ok()) << res.status().c_message();
147 }
148 
TEST_F(PerfettoSqlEngineTest,Table_Schema_NullColumn)149 TEST_F(PerfettoSqlEngineTest, Table_Schema_NullColumn) {
150   // This test checks that the type checks correctly work on columns without
151   // data (and that columns with no non-NULL data do not default to "int").
152   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
153       "CREATE PERFETTO TABLE foo(bar STRING) AS SELECT NULL as bar"));
154   ASSERT_TRUE(res.ok()) << res.status().c_message();
155 }
156 
TEST_F(PerfettoSqlEngineTest,Table_IncorrectSchema_MissingColumn)157 TEST_F(PerfettoSqlEngineTest, Table_IncorrectSchema_MissingColumn) {
158   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
159       "CREATE PERFETTO TABLE foo(x INT) AS SELECT 1 as y"));
160   ASSERT_FALSE(res.ok());
161   EXPECT_THAT(
162       res.status().c_message(),
163       testing::EndsWith("CREATE PERFETTO TABLE: the following columns are "
164                         "declared in the schema, but do not exist: x; and the "
165                         "folowing columns exist, but are not declared: y"));
166 }
167 
TEST_F(PerfettoSqlEngineTest,Table_IncorrectSchema_IncorrectType)168 TEST_F(PerfettoSqlEngineTest, Table_IncorrectSchema_IncorrectType) {
169   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
170       "CREATE PERFETTO TABLE foo(x INT) AS SELECT '1' as x"));
171   ASSERT_FALSE(res.ok());
172   EXPECT_THAT(
173       res.status().c_message(),
174       testing::EndsWith("CREATE PERFETTO TABLE(foo): column 'x' declared as "
175                         "INT (LONG) in the schema, but STRING found"));
176 }
177 
TEST_F(PerfettoSqlEngineTest,Table_Drop)178 TEST_F(PerfettoSqlEngineTest, Table_Drop) {
179   auto res_create = engine_.Execute(SqlSource::FromExecuteQuery(
180       "CREATE PERFETTO TABLE foo AS SELECT 'foo' AS bar"));
181   ASSERT_TRUE(res_create.ok());
182 
183   auto res_drop =
184       engine_.Execute(SqlSource::FromExecuteQuery("DROP TABLE foo"));
185   ASSERT_TRUE(res_drop.ok());
186 }
187 
TEST_F(PerfettoSqlEngineTest,Table_Duplicates)188 TEST_F(PerfettoSqlEngineTest, Table_Duplicates) {
189   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
190       "CREATE PERFETTO TABLE foo AS SELECT 1 as bar"));
191   ASSERT_TRUE(res.ok()) << res.status().c_message();
192 
193   res = engine_.Execute(SqlSource::FromExecuteQuery(
194       "CREATE PERFETTO TABLE foo AS SELECT 1 as bar"));
195   ASSERT_FALSE(res.ok());
196 
197   res = engine_.Execute(SqlSource::FromExecuteQuery(
198       "CREATE OR REPLACE PERFETTO TABLE foo AS SELECT 1 as bar"));
199   ASSERT_TRUE(res.ok()) << res.status().c_message();
200 }
201 
TEST_F(PerfettoSqlEngineTest,View_Create)202 TEST_F(PerfettoSqlEngineTest, View_Create) {
203   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
204       "CREATE PERFETTO VIEW foo AS SELECT 42 AS bar"));
205   ASSERT_TRUE(res.ok()) << res.status().c_message();
206 }
207 
TEST_F(PerfettoSqlEngineTest,View_Schema)208 TEST_F(PerfettoSqlEngineTest, View_Schema) {
209   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
210       "CREATE PERFETTO VIEW foo(bar INT) AS SELECT 42 AS bar"));
211   ASSERT_TRUE(res.ok()) << res.status().c_message();
212 
213   res = engine_.Execute(SqlSource::FromExecuteQuery(
214       "CREATE PERFETTO VIEW foo2(bar INT) AS SELECT 42 AS bar; SELECT 1"));
215   ASSERT_TRUE(res.ok()) << res.status().c_message();
216 }
217 
TEST_F(PerfettoSqlEngineTest,View_Drop)218 TEST_F(PerfettoSqlEngineTest, View_Drop) {
219   auto res_create = engine_.Execute(SqlSource::FromExecuteQuery(
220       "CREATE PERFETTO VIEW foo AS SELECT 'foo' AS bar"));
221   ASSERT_TRUE(res_create.ok());
222 
223   auto res_drop = engine_.Execute(SqlSource::FromExecuteQuery("DROP VIEW foo"));
224   ASSERT_TRUE(res_drop.ok());
225 }
226 
TEST_F(PerfettoSqlEngineTest,View_IncorrectSchema)227 TEST_F(PerfettoSqlEngineTest, View_IncorrectSchema) {
228   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
229       "CREATE PERFETTO VIEW foo(x INT) AS SELECT 1 as y"));
230   ASSERT_FALSE(res.ok());
231   EXPECT_THAT(
232       res.status().c_message(),
233       testing::EndsWith("CREATE PERFETTO VIEW: the following columns are "
234                         "declared in the schema, but do not exist: x; and the "
235                         "folowing columns exist, but are not declared: y"));
236 }
237 
TEST_F(PerfettoSqlEngineTest,View_Duplicates)238 TEST_F(PerfettoSqlEngineTest, View_Duplicates) {
239   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
240       "CREATE PERFETTO VIEW foo AS SELECT 1 as bar"));
241   ASSERT_TRUE(res.ok()) << res.status().c_message();
242 
243   res = engine_.Execute(SqlSource::FromExecuteQuery(
244       "CREATE PERFETTO VIEW foo AS SELECT 1 as bar"));
245   ASSERT_FALSE(res.ok());
246 
247   res = engine_.Execute(SqlSource::FromExecuteQuery(
248       "CREATE OR REPLACE PERFETTO VIEW foo AS SELECT 1 as bar"));
249   ASSERT_TRUE(res.ok()) << res.status().c_message();
250 }
251 
TEST_F(PerfettoSqlEngineTest,Macro_Create)252 TEST_F(PerfettoSqlEngineTest, Macro_Create) {
253   auto res_create = engine_.Execute(SqlSource::FromExecuteQuery(
254       "CREATE PERFETTO MACRO foo() RETURNS TableOrSubquery AS select 42 AS x"));
255   ASSERT_TRUE(res_create.ok()) << res_create.status().c_message();
256 
257   res_create = engine_.Execute(SqlSource::FromExecuteQuery(
258       "CREATE PERFETTO MACRO bar(x TableOrSubquery) RETURNS TableOrSubquery AS "
259       "select * from $x"));
260   ASSERT_TRUE(res_create.ok()) << res_create.status().c_message();
261 
262   auto res = engine_.ExecuteUntilLastStatement(
263       SqlSource::FromExecuteQuery("bar!((foo!()))"));
264   ASSERT_TRUE(res.ok()) << res.status().c_message();
265   ASSERT_FALSE(res->stmt.IsDone());
266   ASSERT_EQ(sqlite3_column_int64(res->stmt.sqlite_stmt(), 0), 42);
267   ASSERT_FALSE(res->stmt.Step());
268 }
269 
TEST_F(PerfettoSqlEngineTest,Macro_Duplicates)270 TEST_F(PerfettoSqlEngineTest, Macro_Duplicates) {
271   auto res = engine_.Execute(SqlSource::FromExecuteQuery(
272       "CREATE PERFETTO MACRO foo() RETURNS TableOrSubquery AS select 42 AS x"));
273   ASSERT_TRUE(res.ok()) << res.status().c_message();
274 
275   res = engine_.Execute(SqlSource::FromExecuteQuery(
276       "CREATE PERFETTO MACRO foo() RETURNS TableOrSubquery AS select 42 AS x"));
277   ASSERT_FALSE(res.ok());
278 
279   res = engine_.Execute(
280       SqlSource::FromExecuteQuery("CREATE OR REPLACE PERFETTO MACRO foo() "
281                                   "RETURNS TableOrSubquery AS select 42 AS x"));
282   ASSERT_TRUE(res.ok());
283 }
284 
TEST_F(PerfettoSqlEngineTest,Include_All)285 TEST_F(PerfettoSqlEngineTest, Include_All) {
286   engine_.RegisterPackage(
287       "foo", CreateTestPackage(
288                  {{"foo.foo", "CREATE PERFETTO TABLE foo AS SELECT 42 AS x"}}));
289   engine_.RegisterPackage(
290       "bar",
291       CreateTestPackage(
292           {{"bar.bar", "CREATE PERFETTO TABLE bar AS SELECT 42 AS x "}}));
293 
294   auto res_create =
295       engine_.Execute(SqlSource::FromExecuteQuery("INCLUDE PERFETTO MODULE *"));
296   ASSERT_TRUE(res_create.ok()) << res_create.status().c_message();
297   ASSERT_TRUE(engine_.FindPackage("foo")->modules["foo.foo"].included);
298   ASSERT_TRUE(engine_.FindPackage("bar")->modules["bar.bar"].included);
299 }
300 
TEST_F(PerfettoSqlEngineTest,Include_Module)301 TEST_F(PerfettoSqlEngineTest, Include_Module) {
302   engine_.RegisterPackage(
303       "foo", CreateTestPackage({
304                  {"foo.foo1", "CREATE PERFETTO TABLE foo1 AS SELECT 42 AS x"},
305                  {"foo.foo2", "CREATE PERFETTO TABLE foo2 AS SELECT 42 AS x"},
306              }));
307   engine_.RegisterPackage(
308       "bar",
309       CreateTestPackage(
310           {{"bar.bar", "CREATE PERFETTO TABLE bar AS SELECT 42 AS x "}}));
311 
312   auto res_create = engine_.Execute(
313       SqlSource::FromExecuteQuery("INCLUDE PERFETTO MODULE foo.*"));
314   ASSERT_TRUE(res_create.ok()) << res_create.status().c_message();
315   ASSERT_TRUE(engine_.FindPackage("foo")->modules["foo.foo1"].included);
316   ASSERT_TRUE(engine_.FindPackage("foo")->modules["foo.foo2"].included);
317   ASSERT_FALSE(engine_.FindPackage("bar")->modules["bar.bar"].included);
318 }
319 
TEST_F(PerfettoSqlEngineTest,MismatchedRange)320 TEST_F(PerfettoSqlEngineTest, MismatchedRange) {
321   tables::SliceTable parent(&pool_);
322   tables::ExpectedFrameTimelineSliceTable child(&pool_, &parent);
323 
324   engine_.RegisterStaticTable(&parent, "parent",
325                               tables::SliceTable::ComputeStaticSchema());
326   engine_.RegisterStaticTable(
327       &child, "child",
328       tables::ExpectedFrameTimelineSliceTable::ComputeStaticSchema());
329 
330   for (uint32_t i = 0; i < 5; i++) {
331     child.Insert({});
332   }
333 
334   for (uint32_t i = 0; i < 10; i++) {
335     parent.Insert({});
336   }
337 
338   auto res = engine_.Execute(
339       SqlSource::FromExecuteQuery("SELECT * FROM child WHERE ts > 3"));
340   ASSERT_TRUE(res.ok()) << res.status().c_message();
341 }
342 
343 }  // namespace
344 }  // namespace trace_processor
345 }  // namespace perfetto
346