OmniSciDB  5ade3759e0
PlanTest.cpp
Go to the documentation of this file.
1 /*
2  * Copyright 2017 MapD Technologies, Inc.
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 <csignal>
18 #include <cstdlib>
19 #include <cstring>
20 #include <exception>
21 #include <iostream>
22 #include <memory>
23 #include <string>
24 #include "../Analyzer/Analyzer.h"
25 #include "../Catalog/Catalog.h"
26 #include "../DataMgr/DataMgr.h"
27 #include "../Parser/ParserNode.h"
28 #include "../Parser/parser.h"
29 #include "../Planner/Planner.h"
30 #include "../QueryRunner/QueryRunner.h"
31 #include "Shared/MapDParameters.h"
32 #include "TestHelpers.h"
33 #include "boost/filesystem.hpp"
34 #include "boost/program_options.hpp"
35 #include "gtest/gtest.h"
36 
37 using namespace std;
38 using namespace Catalog_Namespace;
39 using namespace Analyzer;
40 using namespace Planner;
41 
42 #ifndef BASE_PATH
43 #define BASE_PATH "./tmp"
44 #endif
45 
47 namespace {
48 
49 inline void run_ddl_statement(const string& input_str) {
50  QR::get()->runDDLStatement(input_str);
51 }
52 
53 RootPlan* plan_dml(const string& input_str) {
54  SQLParser parser;
55  list<std::unique_ptr<Parser::Stmt>> parse_trees;
56  string last_parsed;
57  CHECK_EQ(parser.parse(input_str, parse_trees, last_parsed), 0);
58  CHECK_EQ(parse_trees.size(), size_t(1));
59  const auto& stmt = parse_trees.front();
60  Parser::DMLStmt* dml = dynamic_cast<Parser::DMLStmt*>(stmt.get());
61  CHECK(dml);
62  Query query;
63  dml->analyze(*QR::get()->getCatalog(), query);
64  Optimizer optimizer(query, *QR::get()->getCatalog());
65  RootPlan* plan = optimizer.optimize();
66  return plan;
67 }
68 } // namespace
69 
70 TEST(ParseAnalyzePlan, Create) {
71  ASSERT_NO_THROW(run_ddl_statement("create table if not exists fat (a boolean, b "
72  "char(5), c varchar(10), d numeric(10,2) "
73  "encoding rl, e decimal(5,3) encoding sparse(16), f "
74  "int encoding fixed(16), g smallint, "
75  "h real, i float, j double, k bigint encoding diff, "
76  "l text not null encoding dict, m "
77  "timestamp(0), n time(0), o date);"););
78  ASSERT_TRUE(QR::get()->getCatalog()->getMetadataForTable("fat") != nullptr);
79  ASSERT_NO_THROW(
81  "create table if not exists skinny (a smallint, b int, c bigint);"););
82  ASSERT_TRUE(QR::get()->getCatalog()->getMetadataForTable("skinny") != nullptr);
83  ASSERT_NO_THROW(
85  "create table if not exists smallfrag (a int, b text, c bigint) with "
86  "(fragment_size = 1000, page_size = 512);"););
87  const TableDescriptor* td = QR::get()->getCatalog()->getMetadataForTable("smallfrag");
88  CHECK(td);
89  EXPECT_TRUE(td->maxFragRows == 1000 && td->fragPageSize == 512);
90  ASSERT_NO_THROW(
92  "create table if not exists testdict (a varchar(100) encoding dict(8), c "
93  "text encoding dict);"););
94  td = QR::get()->getCatalog()->getMetadataForTable("testdict");
95  CHECK(td);
96  const ColumnDescriptor* cd =
97  QR::get()->getCatalog()->getMetadataForColumn(td->tableId, "a");
98  const DictDescriptor* dd =
99  QR::get()->getCatalog()->getMetadataForDict(cd->columnType.get_comp_param());
100  ASSERT_TRUE(dd != nullptr);
101  EXPECT_EQ(dd->dictNBits, 8);
102  const std::string db_name("chelsea");
103  ASSERT_NO_THROW(run_ddl_statement("CREATE DATABASE " + db_name + ";"));
104  ASSERT_NO_THROW(run_ddl_statement("CREATE DATABASE IF NOT EXISTS " + db_name + ";"));
107  EXPECT_TRUE(sys_cat.getMetadataForDB(db_name, db));
108  EXPECT_EQ(db.dbName, db_name);
109  ASSERT_NO_THROW(run_ddl_statement("DROP DATABASE " + db_name + ";"));
110  ASSERT_NO_THROW(run_ddl_statement("DROP DATABASE IF EXISTS " + db_name + ";"));
111  EXPECT_FALSE(sys_cat.getMetadataForDB(db_name, db));
112 }
113 
114 TEST(ParseAnalyzePlan, Select) {
115  EXPECT_NO_THROW({ unique_ptr<RootPlan> plan_ptr(plan_dml("select * from fat;")); });
116  EXPECT_NO_THROW({ unique_ptr<RootPlan> plan_ptr(plan_dml("select f.* from fat f;")); });
117  EXPECT_NO_THROW({
118  unique_ptr<RootPlan> plan_ptr(plan_dml("select cast(a as int), d, l from fat;"));
119  });
120  EXPECT_NO_THROW({
121  unique_ptr<RootPlan> plan_ptr(plan_dml("select -1, -1.1, -1e-3, -a from fat;"));
122  });
123  EXPECT_NO_THROW({
124  unique_ptr<RootPlan> plan_ptr(plan_dml("select a, d, l from fat where not 1=0;"));
125  });
126  EXPECT_NO_THROW(
127  { unique_ptr<RootPlan> plan_ptr(plan_dml("select b, d+e, f*g as y from fat;")); });
128  EXPECT_NO_THROW({
129  unique_ptr<RootPlan> plan_ptr(plan_dml(
130  "select b, d+e, f*g as y from fat order by 2 asc null last, 3 desc null first;"));
131  });
132  EXPECT_NO_THROW({
133  unique_ptr<RootPlan> plan_ptr(
134  plan_dml("select b, d+e, f*g as y from fat order by 2 asc null last, 3 desc null "
135  "first limit 10;"));
136  });
137  EXPECT_NO_THROW({
138  unique_ptr<RootPlan> plan_ptr(
139  plan_dml("select b, d+e, f*g as y from fat order by 2 asc null last, 3 desc null "
140  "first limit all "
141  "offset 100 rows;"));
142  });
143  EXPECT_NO_THROW({
144  unique_ptr<RootPlan> plan_ptr(
145  plan_dml("select a, d, g from fat where f > 100 and g is null and k <= "
146  "100000000000 and c = "
147  "'xyz';"));
148  });
149  EXPECT_NO_THROW({
150  unique_ptr<RootPlan> plan_ptr(
151  plan_dml("select a, d, g from fat where f > 100 and g is not null or k <= "
152  "100000000000 and c = "
153  "'xyz';"));
154  });
155  EXPECT_NO_THROW({
156  unique_ptr<RootPlan> plan_ptr(
157  plan_dml("select i, j, k from fat where l like '%whatever%';"));
158  });
159  EXPECT_NO_THROW({
160  unique_ptr<RootPlan> plan_ptr(
161  plan_dml("select i, j, k from fat where l like '%whatever@%_' escape '@';"));
162  });
163  EXPECT_NO_THROW({
164  unique_ptr<RootPlan> plan_ptr(
165  plan_dml("select i, j, k from fat where l ilike '%whatever@%_' escape '@';"));
166  });
167  EXPECT_NO_THROW({
168  unique_ptr<RootPlan> plan_ptr(
169  plan_dml("select i, j, k from fat where l not like '%whatever@%_' escape '@';"));
170  });
171  EXPECT_NO_THROW({
172  unique_ptr<RootPlan> plan_ptr(
173  plan_dml("select i, j, k from fat where l not ilike '%whatever@%_' escape '@';"));
174  });
175  EXPECT_NO_THROW({
176  unique_ptr<RootPlan> plan_ptr(
177  plan_dml("select e, f, g from fat where e in (3.5, 133.33, 222.22);"));
178  });
179  EXPECT_NO_THROW({
180  unique_ptr<RootPlan> plan_ptr(
181  plan_dml("select e, f, g from fat where e not in (3.5, 133.33, 222.22);"));
182  });
183  EXPECT_NO_THROW({
184  unique_ptr<RootPlan> plan_ptr(plan_dml(
185  "select e, f, g from fat where e not in (3.5, 133.33, 222.22) or l not like "
186  "'%whatever%';"));
187  });
188  EXPECT_NO_THROW({
189  unique_ptr<RootPlan> plan_ptr(
190  plan_dml("select a, b, c from fat where i between 10e5 and 10e6 and j not "
191  "between 10e-4 and "
192  "10e-1;"));
193  });
194  EXPECT_NO_THROW({
195  unique_ptr<RootPlan> plan_ptr(
196  plan_dml("select case when e between 10 and 20 then 1 when e between 20 and 40 "
197  "then 2 when e is "
198  "null then 100 else 5 end as x, a from fat where case when g > f then "
199  "100 when l like "
200  "'%whatever%' then 200 else 300 end > 100;"));
201  });
202  EXPECT_NO_THROW({
203  unique_ptr<RootPlan> plan_ptr(
204  plan_dml("select case when e between 10 and 20 then 1 when e between 20 and 40 "
205  "then 2.1 when e is "
206  "null then 100.33 else 5e2 end as x, a from fat where case when g > f "
207  "then 100 when l like "
208  "'%whatever%' then 200 else 300 end > 100;"));
209  });
210  EXPECT_NO_THROW({
211  unique_ptr<RootPlan> plan_ptr(
212  plan_dml("select case when e between 10 and 20 then i when e between 20 and 40 "
213  "then j when e is "
214  "null then d else 5e2 end as x, a from fat where case when g > f then "
215  "100 when l like "
216  "'%whatever%' then 200 else 300 end > 100;"));
217  });
218  EXPECT_NO_THROW({
219  unique_ptr<RootPlan> plan_ptr(
220  plan_dml("select count(*), min(a), max(a), avg(b), sum(c), count(distinct b) "
221  "from skinny;"));
222  });
223  EXPECT_NO_THROW({
224  unique_ptr<RootPlan> plan_ptr(plan_dml("select a+b as x from skinny group by x;"));
225  });
226  EXPECT_NO_THROW({
227  unique_ptr<RootPlan> plan_ptr(
228  plan_dml("select a, b, count(*) from skinny group by a, b;"));
229  });
230  EXPECT_NO_THROW({
231  unique_ptr<RootPlan> plan_ptr(
232  plan_dml("select c, avg(b) from skinny where a > 10 group by c;"));
233  });
234  EXPECT_NO_THROW({
235  unique_ptr<RootPlan> plan_ptr(plan_dml(
236  "select c, avg(b) from skinny where a > 10 group by c having max(a) < 100;"));
237  });
238  EXPECT_NO_THROW({
239  unique_ptr<RootPlan> plan_ptr(
240  plan_dml("select c, avg(b) from skinny where a > 10 group by c having max(a) < "
241  "100 and count(*) > "
242  "1000;"));
243  });
244  EXPECT_NO_THROW({
245  unique_ptr<RootPlan> plan_ptr(
246  plan_dml("select count(*)*avg(c) - sum(c) from skinny;"));
247  });
248  EXPECT_NO_THROW({
249  unique_ptr<RootPlan> plan_ptr(
250  plan_dml("select a+b as x, count(*)*avg(c) - sum(c) as y from skinny where c "
251  "between 100 and 200 "
252  "group by a, b;"));
253  });
254  EXPECT_NO_THROW({
255  unique_ptr<RootPlan> plan_ptr(
256  plan_dml("select a+b as x, count(*)*avg(c) - sum(c) as y from skinny where c "
257  "between 100 and 200 "
258  "group by a, b having b > 2*a and min(b) > max(a);"));
259  });
260  EXPECT_NO_THROW({
261  unique_ptr<RootPlan> plan_ptr(
262  plan_dml("select a+b as x, count(*)*avg(c) - sum(c) as y from skinny where c "
263  "between 100 and 200 "
264  "group by a, b order by x desc null first;"));
265  });
266  EXPECT_NO_THROW({
267  unique_ptr<RootPlan> plan_ptr(
268  plan_dml("select a+b as x, count(*)*avg(c) - sum(c) as y from skinny where c "
269  "between 100 and 200 "
270  "group by a, b order by x desc null first limit 10 offset 100000000;"));
271  });
272  EXPECT_NO_THROW({
273  unique_ptr<RootPlan> plan_ptr(
274  plan_dml("select cast(a+b as decimal(10,3)) as x, count(*)*avg(c) - sum(c) as y "
275  "from skinny where c "
276  "between 100 and 200 group by a, b order by x desc null first limit 10 "
277  "offset 100000000;"));
278  });
279  EXPECT_NO_THROW({
280  unique_ptr<RootPlan> plan_ptr(
281  plan_dml("select a+b as x, count(*)*avg(c) - sum(c) as y from skinny where c "
282  "between 100 and 200 "
283  "group by x, b having x > 10;"));
284  });
285  EXPECT_NO_THROW({
286  unique_ptr<RootPlan> plan_ptr(
287  plan_dml("select distinct a+b as x, count(*)*avg(c) - sum(c) as y from skinny "
288  "where c between 100 "
289  "and 200 group by x, b having x > 10;"));
290  });
291  EXPECT_NO_THROW({
292  unique_ptr<RootPlan> plan_ptr(
293  plan_dml("select * from fat where m < timestamp(0) '2015-02-18 13:15:55' and n "
294  ">= time(0) '120000' "
295  "and o <> date '05/06/2014';"));
296  });
297  EXPECT_NO_THROW({
298  unique_ptr<RootPlan> plan_ptr(plan_dml(
299  "select * from fat where m >= '1999-09-09T111111' and n <= '222222' and o = "
300  "'1996-02-23';"));
301  });
302  EXPECT_THROW({ unique_ptr<RootPlan> plan_ptr(plan_dml("select AVG(*) from fat;")); },
303  std::runtime_error);
304 }
305 
306 TEST(ParseAnalyzePlan, Insert) {
307  EXPECT_NO_THROW({
308  unique_ptr<RootPlan> plan_ptr(
309  plan_dml("insert into skinny values (12345, 100000000, 100000000000);"));
310  });
311 }
312 
313 TEST(DISABLED_ParseAnalyzePlan, Views) {
314  EXPECT_NO_THROW(
316  "create view if not exists voo as select * from skinny where a > 15;"););
317  EXPECT_NO_THROW(
319  "create view if not exists moo as select * from skinny where a > 15;"););
320  EXPECT_NO_THROW(run_ddl_statement("create view if not exists mic as select c, avg(b) "
321  "from skinny where a > 10 group by c;"););
322  EXPECT_NO_THROW(run_ddl_statement("create view if not exists fatview as select a, d, g "
323  "from fat where f > 100 and g is not "
324  "null or k <= 100000000000 and c = 'xyz';"););
325  EXPECT_NO_THROW({ unique_ptr<RootPlan> plan_ptr(plan_dml("select * from fatview;")); });
326 }
327 
329  EXPECT_NO_THROW(run_ddl_statement("drop view if exists voo;"));
330  EXPECT_NO_THROW(run_ddl_statement("drop view if exists moo;"));
331  EXPECT_NO_THROW(run_ddl_statement("drop view if exists goo;"));
332  EXPECT_NO_THROW(run_ddl_statement("drop view if exists mic;"));
333  EXPECT_NO_THROW(run_ddl_statement("drop view if exists fatview;"));
334  EXPECT_NO_THROW(run_ddl_statement("drop table if exists fat;"));
335  EXPECT_NO_THROW(run_ddl_statement("drop table if exists skinny;"));
336  EXPECT_NO_THROW(run_ddl_statement("drop table if exists smallfrag;"));
337  EXPECT_NO_THROW(run_ddl_statement("drop table if exists testdict;"));
338  EXPECT_NO_THROW(run_ddl_statement("drop table if exists foxoxoxo;"));
339 }
340 
341 int main(int argc, char* argv[]) {
343  ::testing::InitGoogleTest(&argc, argv);
344 
346 
347  int err{0};
348  try {
349  err = RUN_ALL_TESTS();
350  } catch (const std::exception& e) {
351  LOG(ERROR) << e.what();
352  }
353 
355  QR::reset();
356 
357  return err;
358 }
virtual void analyze(const Catalog_Namespace::Catalog &catalog, Analyzer::Query &query) const =0
#define CHECK_EQ(x, y)
Definition: Logger.h:195
#define LOG(tag)
Definition: Logger.h:182
TEST(ParseAnalyzePlan, Create)
Definition: PlanTest.cpp:70
RootPlan * plan_dml(const string &input_str)
Definition: PlanTest.cpp:53
static QueryRunner * init(const char *db_path, const std::string &udf_filename="", const size_t max_gpu_mem=0, const int reserved_gpu_mem=256<< 20)
Definition: QueryRunner.h:70
static SysCatalog & instance()
Definition: SysCatalog.h:240
RootPlan * optimize()
Definition: Planner.cpp:43
#define BASE_PATH
Definition: PlanTest.cpp:43
virtual void runDDLStatement(const std::string &)
specifies the content in-memory of a row in the column metadata table
void drop_views_and_tables()
Definition: PlanTest.cpp:328
int main(int argc, char *argv[])
Definition: PlanTest.cpp:341
std::shared_ptr< Catalog_Namespace::Catalog > getCatalog() const
static QueryRunner * get()
Definition: QueryRunner.h:115
HOST DEVICE int get_comp_param() const
Definition: sqltypes.h:332
#define CHECK(condition)
Definition: Logger.h:187
void init_logger_stderr_only(int argc, char const *const *argv)
Definition: TestHelpers.h:194
Descriptor for a dictionary for a string columne.
SQLTypeInfo columnType
specifies the content in-memory of a row in the table metadata table
void run_ddl_statement(std::string ddl)