OmniSciDB  04ee39c94c
CtasIntegrationTest.cpp
Go to the documentation of this file.
1 /*
2  * Copyright 2018, OmniSci, 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 <gtest/gtest.h>
18 #include <boost/program_options.hpp>
19 
20 #include <thrift/Thrift.h>
21 #include <thrift/protocol/TBinaryProtocol.h>
22 #include <thrift/protocol/TJSONProtocol.h>
23 #include <thrift/transport/TBufferTransports.h>
24 #include <thrift/transport/TSocket.h>
25 
26 using namespace ::apache::thrift;
27 using namespace ::apache::thrift::protocol;
28 using namespace ::apache::thrift::transport;
29 
31 #include "Shared/ThriftClient.h"
32 #include "Shared/sqltypes.h"
33 #include "TestHelpers.h"
34 #include "gen-cpp/MapD.h"
35 
36 #include <ctime>
37 #include <iostream>
38 
39 // uncomment to run full test suite
40 // #define RUN_ALL_TEST
41 
42 TSessionId g_session_id;
43 std::shared_ptr<MapDClient> g_client;
44 
45 template <typename RETURN_TYPE, typename SOURCE_TYPE>
46 bool checked_get(size_t row,
47  const SOURCE_TYPE* boost_variant,
48  RETURN_TYPE& val,
49  RETURN_TYPE null_value) {
50  const auto val_p = boost::get<RETURN_TYPE>(boost_variant);
51  if (nullptr == val_p) {
52  return false;
53  }
54 
55  val = *val_p;
56  return true;
57 }
58 
59 template <>
60 bool checked_get(size_t row,
61  const ScalarTargetValue* boost_variant,
62  std::string& val,
63  std::string null_value) {
64  const auto val_p = boost::get<NullableString>(boost_variant);
65  if (nullptr == val_p) {
66  return false;
67  }
68 
69  const auto mapd_str_p = boost::get<std::string>(val_p);
70 
71  if (nullptr == mapd_str_p) {
72  val = null_value;
73  } else {
74  val = *mapd_str_p;
75  }
76 
77  return true;
78 }
79 
80 template <>
81 bool checked_get(size_t row, const TDatum* datum, int64_t& val, int64_t null_value) {
82  if (datum->is_null) {
83  val = null_value;
84  } else {
85  val = datum->val.int_val;
86  }
87 
88  return true;
89 }
90 
91 template <>
92 bool checked_get(size_t row, const TDatum* datum, float& val, float null_value) {
93  if (datum->is_null) {
94  val = null_value;
95  } else {
96  val = (float)datum->val.real_val;
97  }
98  return true;
99 }
100 
101 template <>
102 bool checked_get(size_t row, const TDatum* datum, double& val, double null_value) {
103  if (datum->is_null) {
104  val = null_value;
105  } else {
106  val = datum->val.real_val;
107  }
108 
109  return true;
110 }
111 
112 template <>
113 bool checked_get(size_t row,
114  const TDatum* datum,
115  std::string& val,
116  std::string null_value) {
117  if (datum->is_null) {
118  val = null_value;
119  } else {
120  val = datum->val.str_val;
121  }
122 
123  return true;
124 }
125 
127  public:
128  virtual std::string get_column_definition() = 0;
129  virtual std::string get_column_value(int row) = 0;
130  virtual std::string get_update_column_value(int row) { return get_column_value(row); }
131 
132  virtual bool check_column_value(const int row, const TDatum* datum) = 0;
133  virtual ~TestColumnDescriptor() = default;
134 
135  virtual bool skip_test(std::string name) { return false; }
136 };
137 
138 template <typename T>
140  std::string column_definition;
143 
144  public:
145  NumberColumnDescriptor(std::string col_type, SQLTypes sql_type, T null)
146  : column_definition(col_type), rs_type(sql_type), null_value(null){};
147 
148  bool skip_test(std::string name) override {
149  if (kDECIMAL == rs_type) {
150  return "Array.UpdateColumnByLiteral" == name;
151  }
152  if (kDOUBLE == rs_type || kFLOAT == rs_type) {
153  return "Array.UpdateColumnByLiteral" == name;
154  }
155  return false;
156  }
157 
158  std::string get_column_definition() override { return column_definition; };
159  std::string get_column_value(int row) override {
160  if (0 == row) {
161  return "null";
162  }
163 
164  return std::to_string(row);
165  };
166 
167  bool check_column_value(int row, const TDatum* value) override {
168  T mapd_val;
169 
170  if (!checked_get(row, value, mapd_val, null_value)) {
171  return false;
172  }
173 
174  T value_to_check = (T)row;
175  if (row == 0) {
176  value_to_check = null_value;
177  }
178 
179  if (mapd_val == value_to_check) {
180  return true;
181  }
182 
183  LOG(ERROR) << "row: " << std::to_string(row) << " " << std::to_string(value_to_check)
184  << " vs. " << std::to_string(mapd_val);
185  return false;
186  }
187 };
188 
190  std::string column_definition;
191 
192  public:
193  BooleanColumnDescriptor(std::string col_type, SQLTypes sql_type)
194  : column_definition(col_type){};
195 
196  bool skip_test(std::string name) override {
197  return "UpdateColumnByColumn" == name || "UpdateColumnByLiteral" == name ||
198  "Array.UpdateColumnByLiteral" == name;
199  }
200 
201  std::string get_column_definition() override { return column_definition; };
202  std::string get_column_value(int row) override {
203  if (0 == row) {
204  return "null";
205  }
206 
207  return (row % 2) ? "'true'" : "'false'";
208  };
209 
210  bool check_column_value(int row, const TDatum* value) override {
211  int64_t mapd_val;
212  if (!checked_get(row, value, mapd_val, (int64_t)NULL_TINYINT)) {
213  return false;
214  }
215 
216  int64_t value_to_check = (row % 2);
217  if (row == 0) {
218  value_to_check = NULL_TINYINT;
219  }
220 
221  if (mapd_val == value_to_check) {
222  return true;
223  }
224 
225  LOG(ERROR) << "row: " << std::to_string(row) << " " << std::to_string(value_to_check)
226  << " vs. " << std::to_string(mapd_val);
227  return false;
228  }
229 };
230 
232  std::string column_definition;
233  std::string prefix;
234 
235  public:
236  StringColumnDescriptor(std::string col_type, SQLTypes sql_type, std::string pfix)
237  : column_definition(col_type), prefix(pfix){};
238 
239  bool skip_test(std::string name) override {
240  return "Array.UpdateColumnByLiteral" == name;
241  }
242 
243  std::string get_column_definition() override { return column_definition; };
244  std::string get_column_value(int row) override {
245  if (0 == row) {
246  return "null";
247  }
248 
249  return "'" + prefix + "_" + std::to_string(row) + "'";
250  };
251  bool check_column_value(int row, const TDatum* value) override {
252  std::string mapd_val;
253 
254  if (!checked_get(row, value, mapd_val, std::string(""))) {
255  return 0 == row;
256  }
257 
258  if (row == 0) {
259  if (mapd_val == "") {
260  return true;
261  }
262  } else if (mapd_val == (prefix + "_" + std::to_string(row))) {
263  return true;
264  }
265 
266  LOG(ERROR) << "row: " << std::to_string(row) << " "
267  << (prefix + "_" + std::to_string(row)) << " vs. " << mapd_val;
268  return false;
269  }
270 };
271 
273  std::string column_definition;
275  std::string format;
276  long offset;
277  long scale;
278 
279  public:
280  DateTimeColumnDescriptor(std::string col_type,
281  SQLTypes sql_type,
282  std::string fmt,
283  long offset,
284  int scale)
285  : column_definition(col_type)
286  , rs_type(sql_type)
287  , format(fmt)
288  , offset(offset)
289  , scale(scale){};
290 
291  bool skip_test(std::string name) override {
292  return "Array.UpdateColumnByLiteral" == name;
293  }
294 
295  std::string get_column_definition() override { return column_definition; };
296  std::string get_column_value(int row) override {
297  if (0 == row) {
298  return "null";
299  }
300 
301  return "'" + getValueAsString(row) + "'";
302  };
303  bool check_column_value(int row, const TDatum* value) override {
304  int64_t mapd_val;
305 
306  if (!checked_get(row, value, mapd_val, NULL_BIGINT)) {
307  return 0 == row;
308  }
309 
310  int64_t value_to_check = (offset + (scale * row));
311  if (rs_type == kDATE) {
312  value_to_check /= (24 * 60 * 60);
313  value_to_check *= (24 * 60 * 60);
314  }
315  if (row == 0) {
316  value_to_check = NULL_BIGINT;
317  }
318 
319  if (mapd_val == value_to_check) {
320  return true;
321  }
322 
323  LOG(ERROR) << "row: " << std::to_string(row) << " "
324  << std::to_string((offset + (scale * row))) << " vs. "
325  << std::to_string(mapd_val);
326  return false;
327  }
328 
329  std::string getValueAsString(int row) {
330  std::tm tm_struct;
331  time_t t = offset + (scale * row);
332  gmtime_r(&t, &tm_struct);
333  char buf[128];
334  strftime(buf, 128, format.c_str(), &tm_struct);
335  return std::string(buf);
336  }
337 };
338 
340  public:
341  std::string column_definition;
342  const std::shared_ptr<TestColumnDescriptor> element_descriptor;
344 
345  ArrayColumnDescriptor(std::string def,
346  const std::shared_ptr<TestColumnDescriptor> columnDesc,
347  int fixed_len = 0)
348  : column_definition(def +
349  (fixed_len ? "[" + std::to_string(fixed_len) + "]" : "[]"))
350  , element_descriptor(columnDesc)
351  , fixed_array_length(fixed_len) {}
352 
353  bool skip_test(std::string name) override {
354  return element_descriptor->skip_test("Array." + name);
355  }
356 
357  std::string get_column_definition() override { return column_definition; }
358 
359  std::string make_column_value(int rows, std::string prefix, std::string suffix) {
360  std::string values = prefix;
361 
362  int i = 0;
363 
364  if (fixed_array_length) {
365  i = rows;
366  rows += fixed_array_length;
367  }
368 
369  bool firstElementWritten = false;
370 
371  for (; i < rows; i++) {
372  if (firstElementWritten) {
373  values += ", ";
374  }
375  values += element_descriptor->get_column_value(i + 1);
376  firstElementWritten = true;
377  }
378  values += suffix;
379 
380  return values;
381  }
382 
383  std::string get_column_value(int row) override {
384  return make_column_value(row, "{", "}");
385  }
386 
387  std::string get_update_column_value(int row) override {
388  return make_column_value(row, "ARRAY[", "]");
389  }
390 
391  bool check_column_value(const int row, const TDatum* datum) override {
392  if (row == 0 && datum->is_null) {
393  } else if (datum->is_null) {
394  return false;
395  }
396 
397  int elementIndex = 1;
398 
399  if (fixed_array_length) {
400  elementIndex += row;
401  }
402 
403  for (auto& dv : datum->val.arr_val) {
404  if (!element_descriptor->check_column_value(elementIndex, &dv)) {
405  return false;
406  }
407 
408  elementIndex++;
409  }
410 
411  return true;
412  }
413 };
414 
416  std::string prefix;
417 
418  public:
420 
421  bool skip_test(std::string name) override { return "CreateTableAsSelect" != name; }
422 
423  std::string get_column_definition() override { return "POINT"; };
424 
425  std::string getColumnWktStringValue(int row) {
426  return "POINT (" + std::to_string(row) + " 0)";
427  }
428  std::string get_column_value(int row) override {
429  return "'" + getColumnWktStringValue(row) + "'";
430  };
431 
432  bool check_column_value(int row, const TDatum* value) override {
433  std::string mapd_val;
434  if (!checked_get(row, value, mapd_val, std::string(""))) {
435  return false;
436  }
437 
438  if (mapd_val == getColumnWktStringValue(row)) {
439  return true;
440  }
441 
442  LOG(ERROR) << "row: " << std::to_string(row) << " " << getColumnWktStringValue(row)
443  << " vs. " << mapd_val;
444  return false;
445  }
446 };
447 
449  std::string prefix;
450 
451  public:
453 
454  bool skip_test(std::string name) override { return "CreateTableAsSelect" != name; }
455 
456  std::string get_column_definition() override { return "LINESTRING"; };
457 
458  std::string getColumnWktStringValue(int row) {
459  std::string linestring = "LINESTRING (0 0";
460  for (int i = 0; i <= row; i++) {
461  linestring += "," + std::to_string(row) + " 0";
462  }
463  linestring += ")";
464  return linestring;
465  }
466  std::string get_column_value(int row) override {
467  return "'" + getColumnWktStringValue(row) + "'";
468  };
469 
470  bool check_column_value(int row, const TDatum* value) override {
471  std::string mapd_val;
472  if (!checked_get(row, value, mapd_val, std::string(""))) {
473  return false;
474  }
475 
476  if (mapd_val == getColumnWktStringValue(row)) {
477  return true;
478  }
479 
480  LOG(ERROR) << "row: " << std::to_string(row) << " " << getColumnWktStringValue(row)
481  << " vs. " << mapd_val;
482  return false;
483  }
484 };
485 
487  std::string prefix;
488 
489  public:
491 
492  bool skip_test(std::string name) override { return "CreateTableAsSelect" != name; }
493 
494  std::string get_column_definition() override { return "MULTIPOLYGON"; };
495 
496  std::string getColumnWktStringValue(int row) {
497  std::string polygon =
498  "MULTIPOLYGON (((0 " + std::to_string(row) + ",4 " + std::to_string(row) + ",4 " +
499  std::to_string(row + 4) + ",0 " + std::to_string(row + 4) + ",0 " +
500  std::to_string(row) + "),(1 " + std::to_string(row + 1) + ",1 " +
501  std::to_string(row + 2) + ",2 " + std::to_string(row + 2) + ",2 " +
502  std::to_string(row + 1) + ",1 " + std::to_string(row + 1) + ")))";
503  return polygon;
504  }
505 
506  std::string get_column_value(int row) override {
507  return "'" + getColumnWktStringValue(row) + "'";
508  };
509 
510  bool check_column_value(int row, const TDatum* value) override {
511  std::string mapd_val;
512  if (!checked_get(row, value, mapd_val, std::string(""))) {
513  return false;
514  }
515 
516  if (mapd_val == getColumnWktStringValue(row)) {
517  return true;
518  }
519 
520  LOG(ERROR) << "row: " << std::to_string(row) << " " << getColumnWktStringValue(row)
521  << " vs. " << mapd_val;
522  return false;
523  }
524 };
525 
527  std::string prefix;
528 
529  public:
531 
532  bool skip_test(std::string name) override { return "CreateTableAsSelect" != name; }
533 
534  std::string get_column_definition() override { return "POLYGON"; };
535 
536  std::string getColumnWktStringValue(int row) {
537  std::string polygon =
538  "POLYGON ((0 " + std::to_string(row) + ",4 " + std::to_string(row) + ",4 " +
539  std::to_string(row + 4) + ",0 " + std::to_string(row + 4) + ",0 " +
540  std::to_string(row) + "),(1 " + std::to_string(row + 1) + ",1 " +
541  std::to_string(row + 2) + ",2 " + std::to_string(row + 2) + ",2 " +
542  std::to_string(row + 1) + ",1 " + std::to_string(row + 1) + "))";
543  return polygon;
544  }
545 
546  std::string get_column_value(int row) override {
547  return "'" + getColumnWktStringValue(row) + "'";
548  };
549 
550  bool check_column_value(int row, const TDatum* value) override {
551  std::string mapd_val;
552  if (!checked_get(row, value, mapd_val, std::string(""))) {
553  return false;
554  }
555 
556  if (mapd_val == getColumnWktStringValue(row)) {
557  return true;
558  }
559 
560  LOG(ERROR) << "row: " << std::to_string(row) << " " << getColumnWktStringValue(row)
561  << " vs. " << mapd_val;
562  return false;
563  }
564 };
565 
566 struct Ctas
567  : testing::Test,
568  testing::WithParamInterface<std::vector<std::shared_ptr<TestColumnDescriptor>>> {
569  std::vector<std::shared_ptr<TestColumnDescriptor>> columnDescriptors;
570 
571  Ctas() { columnDescriptors = GetParam(); }
572 };
573 
574 struct Itas
575  : testing::Test,
576  testing::WithParamInterface<std::vector<std::shared_ptr<TestColumnDescriptor>>> {
577  std::vector<std::shared_ptr<TestColumnDescriptor>> columnDescriptors;
578 
579  Itas() { columnDescriptors = GetParam(); }
580 };
581 
582 struct Update
583  : testing::Test,
584  testing::WithParamInterface<std::vector<std::shared_ptr<TestColumnDescriptor>>> {
585  std::vector<std::shared_ptr<TestColumnDescriptor>> columnDescriptors;
586 
587  Update() { columnDescriptors = GetParam(); }
588 };
589 
592  g_client->sql_execute(result, g_session_id, sql, false, "", -1, -1);
593  return result;
594 }
595 
596 void run_ddl_statement(std::string ddl) {
597  run_multiple_agg(ddl);
598 }
599 
600 TEST(Ctas, SyntaxCheck) {
601  std::string ddl = "DROP TABLE IF EXISTS CTAS_SOURCE;";
602  run_ddl_statement(ddl);
603  ddl = "DROP TABLE IF EXISTS CTAS_TARGET;";
604 
605  run_ddl_statement(ddl);
606 
607  run_ddl_statement("CREATE TABLE CTAS_SOURCE (id int);");
608 
609  ddl = "CREATE TABLE CTAS_TARGET AS SELECT * FROM CTAS_SOURCE;";
610  run_ddl_statement(ddl);
611  EXPECT_THROW(run_ddl_statement(ddl), apache::thrift::TException);
612  ddl = "DROP TABLE CTAS_TARGET;";
613  run_ddl_statement(ddl);
614 
615  ddl = "CREATE TEMPORARY TABLE CTAS_TARGET AS SELECT * FROM CTAS_SOURCE;";
616  run_ddl_statement(ddl);
617  EXPECT_THROW(run_ddl_statement(ddl), apache::thrift::TException);
618  ddl = "DROP TABLE CTAS_TARGET;";
619  run_ddl_statement(ddl);
620 
621  ddl = "CREATE TABLE CTAS_TARGET AS SELECT * FROM CTAS_SOURCE WITH( FRAGMENT_SIZE=3 );";
622  run_ddl_statement(ddl);
623  EXPECT_THROW(run_ddl_statement(ddl), apache::thrift::TException);
624  ddl = "DROP TABLE CTAS_TARGET;";
625  run_ddl_statement(ddl);
626 
627  ddl = "CREATE TABLE CTAS_TARGET AS SELECT * FROM CTAS_SOURCE WITH( MAX_CHUNK_SIZE=3 );";
628  run_ddl_statement(ddl);
629  EXPECT_THROW(run_ddl_statement(ddl), apache::thrift::TException);
630  ddl = "DROP TABLE CTAS_TARGET;";
631  run_ddl_statement(ddl);
632 }
633 
634 TEST_P(Ctas, CreateTableAsSelect) {
635  run_ddl_statement("DROP TABLE IF EXISTS CTAS_SOURCE;");
636  run_ddl_statement("DROP TABLE IF EXISTS CTAS_TARGET;");
637 
638  std::string create_sql = "CREATE TABLE CTAS_SOURCE (id int";
639  for (unsigned int col = 0; col < columnDescriptors.size(); col++) {
640  auto tcd = columnDescriptors[col];
641  if (tcd->skip_test("CreateTableAsSelect")) {
642  LOG(ERROR) << "not supported... skipping";
643  return;
644  }
645 
646  create_sql += ", col_" + std::to_string(col) + " " + tcd->get_column_definition();
647  }
648  create_sql += ");";
649 
650  LOG(INFO) << create_sql;
651 
652  run_ddl_statement(create_sql);
653 
654  size_t num_rows = 25;
655 
656  // fill source table
657  for (unsigned int row = 0; row < num_rows; row++) {
658  std::string insert_sql = "INSERT INTO CTAS_SOURCE VALUES (" + std::to_string(row);
659  for (unsigned int col = 0; col < columnDescriptors.size(); col++) {
660  auto tcd = columnDescriptors[col];
661  insert_sql += ", " + tcd->get_column_value(row);
662  }
663  insert_sql += ");";
664 
665  // LOG(INFO) << "insert_sql: " << insert_sql;
666 
667  run_multiple_agg(insert_sql);
668  }
669 
670  // execute CTAS
671  std::string create_ctas_sql = "CREATE TABLE CTAS_TARGET AS SELECT * FROM CTAS_SOURCE;";
672  LOG(INFO) << create_ctas_sql;
673 
674  run_ddl_statement(create_ctas_sql);
675 
676  // check tables
677  TTableDetails td_source;
678  TTableDetails td_target;
679 
680  g_client->get_table_details(td_source, g_session_id, "CTAS_SOURCE");
681  g_client->get_table_details(td_target, g_session_id, "CTAS_TARGET");
682  ASSERT_EQ(td_source.row_desc.size(), td_target.row_desc.size());
683 
684  // compare source against CTAS
685  std::string select_sql = "SELECT * FROM CTAS_SOURCE ORDER BY id;";
686  std::string select_ctas_sql = "SELECT * FROM CTAS_TARGET ORDER BY id;";
687 
688  LOG(INFO) << select_sql;
689  auto select_result = run_multiple_agg(select_sql);
690 
691  LOG(INFO) << select_ctas_sql;
692  auto select_ctas_result = run_multiple_agg(select_ctas_sql);
693 
694  ASSERT_EQ(num_rows, select_result.row_set.rows.size());
695  ASSERT_EQ(num_rows, select_ctas_result.row_set.rows.size());
696 
697  for (unsigned int row = 0; row < num_rows; row++) {
698  const auto select_crt_row = select_result.row_set.rows[row];
699  const auto select_ctas_crt_row = select_ctas_result.row_set.rows[row];
700 
701  for (unsigned int col = 0; col < columnDescriptors.size(); col++) {
702  auto tcd = columnDescriptors[col];
703 
704  {
705  const auto mapd_variant = select_crt_row.cols[col + 1];
706  ASSERT_EQ(true, tcd->check_column_value(row, &mapd_variant));
707  }
708  {
709  const auto mapd_variant = select_ctas_crt_row.cols[col + 1];
710  ASSERT_EQ(true, tcd->check_column_value(row, &mapd_variant));
711  }
712  }
713  }
714 }
715 
716 void itasTestBody(std::vector<std::shared_ptr<TestColumnDescriptor>>& columnDescriptors,
717  std::string targetPartitionScheme = ")") {
718  run_ddl_statement("DROP TABLE IF EXISTS ITAS_SOURCE;");
719  run_ddl_statement("DROP TABLE IF EXISTS ITAS_TARGET;");
720 
721  std::string create_source_sql = "CREATE TABLE ITAS_SOURCE (id int";
722  std::string create_target_sql = "CREATE TABLE ITAS_TARGET (id int";
723  for (unsigned int col = 0; col < columnDescriptors.size(); col++) {
724  auto tcd = columnDescriptors[col];
725  if (tcd->skip_test("CreateTableAsSelect")) {
726  LOG(ERROR) << "not supported... skipping";
727  return;
728  }
729 
730  create_source_sql +=
731  ", col_" + std::to_string(col) + " " + tcd->get_column_definition();
732  create_target_sql +=
733  ", col_" + std::to_string(col) + " " + tcd->get_column_definition();
734  }
735  create_source_sql += ");";
736  create_target_sql += targetPartitionScheme + ";";
737 
738  LOG(INFO) << create_source_sql;
739  LOG(INFO) << create_target_sql;
740 
741  run_ddl_statement(create_source_sql);
742  run_ddl_statement(create_target_sql);
743 
744  size_t num_rows = 25;
745 
746  // fill source table
747  for (unsigned int row = 0; row < num_rows; row++) {
748  std::string insert_sql = "INSERT INTO ITAS_SOURCE VALUES (" + std::to_string(row);
749  for (unsigned int col = 0; col < columnDescriptors.size(); col++) {
750  auto tcd = columnDescriptors[col];
751  insert_sql += ", " + tcd->get_column_value(row);
752  }
753  insert_sql += ");";
754 
755  // LOG(INFO) << "insert_sql: " << insert_sql;
756 
757  run_multiple_agg(insert_sql);
758  }
759 
760  // execute CTAS
761  std::string insert_itas_sql = "INSERT INTO ITAS_TARGET SELECT * FROM ITAS_SOURCE;";
762  LOG(INFO) << insert_itas_sql;
763 
764  run_ddl_statement(insert_itas_sql);
765 
766  // compare source against CTAS
767  std::string select_sql = "SELECT * FROM ITAS_SOURCE ORDER BY id;";
768  std::string select_itas_sql = "SELECT * FROM ITAS_TARGET ORDER BY id;";
769 
770  LOG(INFO) << select_sql;
771  auto select_result = run_multiple_agg(select_sql);
772 
773  LOG(INFO) << select_itas_sql;
774  auto select_itas_result = run_multiple_agg(select_itas_sql);
775 
776  ASSERT_EQ(num_rows, select_result.row_set.rows.size());
777  ASSERT_EQ(num_rows, select_itas_result.row_set.rows.size());
778 
779  for (unsigned int row = 0; row < num_rows; row++) {
780  const auto select_crt_row = select_result.row_set.rows[row];
781  const auto select_itas_crt_row = select_itas_result.row_set.rows[row];
782 
783  for (unsigned int col = 0; col < columnDescriptors.size(); col++) {
784  auto tcd = columnDescriptors[col];
785 
786  {
787  const auto mapd_variant = select_crt_row.cols[col + 1];
788  ASSERT_EQ(true, tcd->check_column_value(row, &mapd_variant));
789  }
790  {
791  const auto mapd_variant = select_itas_crt_row.cols[col + 1];
792  ASSERT_EQ(true, tcd->check_column_value(row, &mapd_variant));
793  }
794  }
795  }
796 }
797 
798 TEST_P(Itas, InsertIntoTableFromSelect) {
799  itasTestBody(columnDescriptors, ")");
800 }
801 
802 TEST_P(Itas, InsertIntoTableFromSelectReplicated) {
803  itasTestBody(columnDescriptors, ") WITH (partitions='REPLICATED')");
804 }
805 
806 TEST_P(Itas, InsertIntoTableFromSelectSharded) {
807  itasTestBody(columnDescriptors,
808  ", SHARD KEY (id)) WITH (shard_count = 4, partitions='SHARDED')");
809 }
810 
811 const std::shared_ptr<TestColumnDescriptor> STRING_NONE_BASE =
812  std::make_shared<StringColumnDescriptor>("TEXT ENCODING NONE",
813  kTEXT,
814  "STRING_NONE_BASE");
815 
816 #ifdef RUN_ALL_TEST
817 
818 #define INSTANTIATE_DATA_INGESTION_TEST(CDT) \
819  INSTANTIATE_TEST_CASE_P( \
820  CDT, \
821  Ctas, \
822  testing::Values(std::vector<std::shared_ptr<TestColumnDescriptor>>{CDT})); \
823  INSTANTIATE_TEST_CASE_P( \
824  CDT, \
825  Itas, \
826  testing::Values(std::vector<std::shared_ptr<TestColumnDescriptor>>{CDT})); \
827  INSTANTIATE_TEST_CASE_P( \
828  CDT, \
829  Update, \
830  testing::Values(std::vector<std::shared_ptr<TestColumnDescriptor>>{CDT})); \
831  INSTANTIATE_TEST_CASE_P( \
832  VARLEN_TEXT_AND_##CDT, \
833  Update, \
834  testing::Values( \
835  std::vector<std::shared_ptr<TestColumnDescriptor>>{STRING_NONE_BASE, CDT})); \
836  INSTANTIATE_TEST_CASE_P( \
837  CDT##_AND_VARLEN_TEXT, \
838  Update, \
839  testing::Values( \
840  std::vector<std::shared_ptr<TestColumnDescriptor>>{CDT, STRING_NONE_BASE}))
841 
842 #else
843 
844 #define INSTANTIATE_DATA_INGESTION_TEST(CDT)
845 
846 #endif
847 
848 #define BOOLEAN_COLUMN_TEST(name, c_type, definition, sql_type, null) \
849  const std::shared_ptr<TestColumnDescriptor> name = \
850  std::make_shared<BooleanColumnDescriptor>(definition, sql_type); \
851  INSTANTIATE_DATA_INGESTION_TEST(name)
852 
853 #define NUMBER_COLUMN_TEST(name, c_type, definition, sql_type, null) \
854  const std::shared_ptr<TestColumnDescriptor> name = \
855  std::make_shared<NumberColumnDescriptor<c_type>>(definition, sql_type, null); \
856  INSTANTIATE_DATA_INGESTION_TEST(name)
857 
858 #define STRING_COLUMN_TEST(name, definition, sql_type) \
859  const std::shared_ptr<TestColumnDescriptor> name = \
860  std::make_shared<StringColumnDescriptor>(definition, sql_type, #name); \
861  INSTANTIATE_DATA_INGESTION_TEST(name)
862 
863 #define TIME_COLUMN_TEST(name, definition, sql_type, format, offset, scale) \
864  const std::shared_ptr<TestColumnDescriptor> name = \
865  std::make_shared<DateTimeColumnDescriptor>( \
866  definition, sql_type, format, offset, scale); \
867  INSTANTIATE_DATA_INGESTION_TEST(name)
868 
869 #define ARRAY_COLUMN_TEST(name, definition) \
870  const std::shared_ptr<TestColumnDescriptor> name##_ARRAY = \
871  std::make_shared<ArrayColumnDescriptor>(definition, name, 0); \
872  INSTANTIATE_DATA_INGESTION_TEST(name##_ARRAY); \
873  const std::shared_ptr<TestColumnDescriptor> name##_FIXED_LEN_ARRAY = \
874  std::make_shared<ArrayColumnDescriptor>(definition, name, 3); \
875  INSTANTIATE_DATA_INGESTION_TEST(name##_FIXED_LEN_ARRAY)
876 
877 BOOLEAN_COLUMN_TEST(BOOLEAN, int64_t, "BOOLEAN", kBOOLEAN, NULL_TINYINT);
878 ARRAY_COLUMN_TEST(BOOLEAN, "BOOLEAN");
879 
880 NUMBER_COLUMN_TEST(TINYINT, int64_t, "TINYINT", kTINYINT, NULL_TINYINT);
881 ARRAY_COLUMN_TEST(TINYINT, "TINYINT");
882 
883 NUMBER_COLUMN_TEST(SMALLINT, int64_t, "SMALLINT", kSMALLINT, NULL_SMALLINT);
884 NUMBER_COLUMN_TEST(SMALLINT_8,
885  int64_t,
886  "SMALLINT ENCODING FIXED(8)",
887  kSMALLINT,
888  NULL_SMALLINT);
889 ARRAY_COLUMN_TEST(SMALLINT, "SMALLINT");
890 
891 NUMBER_COLUMN_TEST(INTEGER, int64_t, "INTEGER", kINT, NULL_INT);
892 NUMBER_COLUMN_TEST(INTEGER_8, int64_t, "INTEGER ENCODING FIXED(8)", kINT, NULL_INT);
893 NUMBER_COLUMN_TEST(INTEGER_16, int64_t, "INTEGER ENCODING FIXED(16)", kINT, NULL_INT);
894 ARRAY_COLUMN_TEST(INTEGER, "INTEGER");
895 
896 NUMBER_COLUMN_TEST(BIGINT, int64_t, "BIGINT", kBIGINT, NULL_BIGINT);
897 NUMBER_COLUMN_TEST(BIGINT_8, int64_t, "BIGINT ENCODING FIXED(8)", kBIGINT, NULL_BIGINT);
898 NUMBER_COLUMN_TEST(BIGINT_16, int64_t, "BIGINT ENCODING FIXED(16)", kBIGINT, NULL_BIGINT);
899 NUMBER_COLUMN_TEST(BIGINT_32, int64_t, "BIGINT ENCODING FIXED(32)", kBIGINT, NULL_BIGINT);
900 ARRAY_COLUMN_TEST(BIGINT, "BIGINT");
901 
902 NUMBER_COLUMN_TEST(FLOAT, float, "FLOAT", kFLOAT, NULL_FLOAT);
903 ARRAY_COLUMN_TEST(FLOAT, "FLOAT");
904 
905 NUMBER_COLUMN_TEST(DOUBLE, double, "DOUBLE", kDOUBLE, NULL_DOUBLE);
906 ARRAY_COLUMN_TEST(DOUBLE, "DOUBLE");
907 
908 NUMBER_COLUMN_TEST(NUMERIC, double, "NUMERIC(18)", kNUMERIC, NULL_DOUBLE);
909 NUMBER_COLUMN_TEST(NUMERIC_32,
910  double,
911  "NUMERIC(9) ENCODING FIXED(32)",
912  kNUMERIC,
913  NULL_DOUBLE);
914 NUMBER_COLUMN_TEST(NUMERIC_16,
915  double,
916  "NUMERIC(4) ENCODING FIXED(16)",
917  kNUMERIC,
918  NULL_DOUBLE);
919 ARRAY_COLUMN_TEST(NUMERIC, "NUMERIC(18)");
920 
921 NUMBER_COLUMN_TEST(DECIMAL, double, "DECIMAL(18,9)", kDECIMAL, NULL_DOUBLE);
922 NUMBER_COLUMN_TEST(DECIMAL_32,
923  double,
924  "DECIMAL(9,2) ENCODING FIXED(32)",
925  kDECIMAL,
926  NULL_DOUBLE);
927 NUMBER_COLUMN_TEST(DECIMAL_16,
928  double,
929  "DECIMAL(4,2) ENCODING FIXED(16)",
930  kDECIMAL,
931  NULL_DOUBLE);
932 ARRAY_COLUMN_TEST(DECIMAL, "DECIMAL(18,9)");
933 
934 STRING_COLUMN_TEST(CHAR, "CHAR(100)", kCHAR);
935 STRING_COLUMN_TEST(CHAR_DICT, "CHAR(100) ENCODING DICT", kCHAR);
936 STRING_COLUMN_TEST(CHAR_DICT_8, "CHAR(100) ENCODING DICT(8)", kCHAR);
937 STRING_COLUMN_TEST(CHAR_DICT_16, "CHAR(100) ENCODING DICT(16)", kCHAR);
938 STRING_COLUMN_TEST(CHAR_NONE, "CHAR(100) ENCODING NONE", kCHAR);
939 ARRAY_COLUMN_TEST(CHAR, "CHAR(100)");
940 
941 STRING_COLUMN_TEST(VARCHAR, "VARCHAR(100)", kCHAR);
942 STRING_COLUMN_TEST(VARCHAR_DICT, "VARCHAR(100) ENCODING DICT", kCHAR);
943 STRING_COLUMN_TEST(VARCHAR_DICT_8, "VARCHAR(100) ENCODING DICT(8)", kCHAR);
944 STRING_COLUMN_TEST(VARCHAR_DICT_16, "VARCHAR(100) ENCODING DICT(16)", kCHAR);
945 STRING_COLUMN_TEST(VARCHAR_NONE, "VARCHAR(100) ENCODING NONE", kCHAR);
946 ARRAY_COLUMN_TEST(VARCHAR, "VARCHAR(100)");
947 
948 STRING_COLUMN_TEST(TEXT, "TEXT", kTEXT);
949 STRING_COLUMN_TEST(TEXT_DICT, "TEXT ENCODING DICT", kTEXT);
950 STRING_COLUMN_TEST(TEXT_DICT_8, "TEXT ENCODING DICT(8)", kTEXT);
951 STRING_COLUMN_TEST(TEXT_DICT_16, "TEXT ENCODING DICT(16)", kTEXT);
952 STRING_COLUMN_TEST(TEXT_NONE, "TEXT ENCODING NONE", kTEXT);
953 ARRAY_COLUMN_TEST(TEXT, "TEXT");
954 
955 TIME_COLUMN_TEST(TIME, "TIME", kTIME, "%T", 0, 1);
956 TIME_COLUMN_TEST(TIME_32, "TIME ENCODING FIXED(32)", kTIME, "%T", 0, 1);
957 ARRAY_COLUMN_TEST(TIME, "TIME");
958 
959 TIME_COLUMN_TEST(DATE, "DATE", kDATE, "%F", 0, 160 * 60 * 100);
960 TIME_COLUMN_TEST(DATE_16, "DATE ENCODING FIXED(16)", kDATE, "%F", 0, 160 * 60 * 100);
961 ARRAY_COLUMN_TEST(DATE, "DATE");
962 
963 TIME_COLUMN_TEST(TIMESTAMP, "TIMESTAMP", kTIMESTAMP, "%F %T", 0, 160 * 60 * 100);
964 TIME_COLUMN_TEST(TIMESTAMP_32,
965  "TIMESTAMP ENCODING FIXED(32)",
966  kTIMESTAMP,
967  "%F %T",
968  0,
969  160 * 60 * 100);
970 ARRAY_COLUMN_TEST(TIMESTAMP, "TIMESTAMP");
971 
972 const std::shared_ptr<TestColumnDescriptor> GEO_POINT =
973  std::shared_ptr<TestColumnDescriptor>(new GeoPointColumnDescriptor(kPOINT));
975 
976 const std::shared_ptr<TestColumnDescriptor> GEO_LINESTRING =
977  std::shared_ptr<TestColumnDescriptor>(new GeoLinestringColumnDescriptor(kLINESTRING));
979 
980 const std::shared_ptr<TestColumnDescriptor> GEO_POLYGON =
981  std::shared_ptr<TestColumnDescriptor>(new GeoPolygonColumnDescriptor(kPOLYGON));
983 
984 const std::shared_ptr<TestColumnDescriptor> GEO_MULTI_POLYGON =
985  std::shared_ptr<TestColumnDescriptor>(
988 
990  MIXED_NO_GEO,
991  Ctas,
992  testing::Values(std::vector<std::shared_ptr<TestColumnDescriptor>>{BOOLEAN,
993  TINYINT,
994  SMALLINT,
995  INTEGER,
996  BIGINT,
997  FLOAT,
998  DOUBLE,
999  NUMERIC,
1000  DECIMAL,
1001  CHAR,
1002  VARCHAR,
1003  TEXT,
1004  TIME,
1005  DATE,
1006  TIMESTAMP}));
1007 
1009  MIXED_NO_GEO,
1010  Itas,
1011  testing::Values(std::vector<std::shared_ptr<TestColumnDescriptor>>{BOOLEAN,
1012  TINYINT,
1013  SMALLINT,
1014  INTEGER,
1015  BIGINT,
1016  FLOAT,
1017  DOUBLE,
1018  NUMERIC,
1019  DECIMAL,
1020  CHAR,
1021  VARCHAR,
1022  TEXT,
1023  TIME,
1024  DATE,
1025  TIMESTAMP}));
1026 
1028  MIXED_WITH_GEO,
1029  Update,
1030  testing::Values(std::vector<std::shared_ptr<TestColumnDescriptor>>{TEXT,
1031  INTEGER,
1032  DOUBLE,
1033  GEO_POINT,
1035  GEO_POLYGON,
1037 
1038  }));
1039 
1040 int main(int argc, char* argv[]) {
1041  int err = 0;
1043 
1044  try {
1045  testing::InitGoogleTest(&argc, argv);
1046 
1047  namespace po = boost::program_options;
1048 
1049  po::options_description desc("Options");
1050 
1051  // these two are here to allow passing correctly google testing parameters
1052  desc.add_options()("gtest_list_tests", "list all test");
1053  desc.add_options()("gtest_filter", "filters tests, use --help for details");
1054 
1055  std::string host = "localhost";
1056  int port = 6274;
1057  std::string cert = "";
1058 
1059  std::string user = "admin";
1060  std::string pwd = "HyperInteractive";
1061  std::string db = "omnisci";
1062 
1063  desc.add_options()(
1064  "host",
1065  po::value<std::string>(&host)->default_value(host)->implicit_value(host),
1066  "hostname of target server");
1067  desc.add_options()("port",
1068  po::value<int>(&port)->default_value(port)->implicit_value(port),
1069  "tcp port of target server");
1070  desc.add_options()(
1071  "cert",
1072  po::value<std::string>(&cert)->default_value(cert)->implicit_value(cert),
1073  "tls/ssl certificate to use for contacting target server");
1074  desc.add_options()(
1075  "user",
1076  po::value<std::string>(&user)->default_value(user)->implicit_value(user),
1077  "user name to connect as");
1078  desc.add_options()(
1079  "pwd",
1080  po::value<std::string>(&pwd)->default_value(pwd)->implicit_value(pwd),
1081  "password to connect with");
1082  desc.add_options()("db",
1083  po::value<std::string>(&db)->default_value(db)->implicit_value(db),
1084  "db to connect to");
1085 
1086  po::variables_map vm;
1087  po::store(po::command_line_parser(argc, argv).options(desc).run(), vm);
1088  po::notify(vm);
1089  mapd::shared_ptr<ThriftClientConnection> connMgr;
1090  connMgr = std::make_shared<ThriftClientConnection>();
1091  auto transport = connMgr->open_buffered_client_transport(host, port, cert);
1092  transport->open();
1093  auto protocol = std::make_shared<TBinaryProtocol>(transport);
1094  g_client = std::make_shared<MapDClient>(protocol);
1095 
1096  g_client->connect(g_session_id, user, pwd, db);
1097 
1098  err = RUN_ALL_TESTS();
1099 
1100  } catch (const std::exception& e) {
1101  LOG(ERROR) << e.what();
1102  err = -1;
1103  }
1104 
1105  return err;
1106 }
std::string get_column_definition() override
std::string make_column_value(int rows, std::string prefix, std::string suffix)
std::string get_column_value(int row) override
#define NULL_DOUBLE
Definition: sqltypes.h:176
std::string getColumnWktStringValue(int row)
#define INSTANTIATE_DATA_INGESTION_TEST(CDT)
const int8_t const int64_t * num_rows
Definition: sqltypes.h:51
SQLTypes
Definition: sqltypes.h:40
bool check_column_value(int row, const TDatum *value) override
std::string get_column_definition() override
std::string get_update_column_value(int row) override
#define NULL_BIGINT
Definition: sqltypes.h:174
const std::shared_ptr< TestColumnDescriptor > element_descriptor
virtual std::string get_update_column_value(int row)
INSTANTIATE_TEST_CASE_P(MIXED_NO_GEO, Ctas, testing::Values(std::vector< std::shared_ptr< TestColumnDescriptor >>{BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, TEXT, TIME, DATE, TIMESTAMP}))
std::string get_column_definition() override
std::string getColumnWktStringValue(int row)
#define LOG(tag)
Definition: Logger.h:182
std::string get_column_value(int row) override
const std::shared_ptr< TestColumnDescriptor > STRING_NONE_BASE
const std::shared_ptr< TestColumnDescriptor > GEO_MULTI_POLYGON
bool check_column_value(int row, const TDatum *value) override
const std::shared_ptr< TestColumnDescriptor > GEO_POLYGON
ArrayColumnDescriptor(std::string def, const std::shared_ptr< TestColumnDescriptor > columnDesc, int fixed_len=0)
TEST(Ctas, SyntaxCheck)
auto sql(const std::string &sql_stmts)
bool skip_test(std::string name) override
std::vector< std::shared_ptr< TestColumnDescriptor > > columnDescriptors
DateTimeColumnDescriptor(std::string col_type, SQLTypes sql_type, std::string fmt, long offset, int scale)
bool skip_test(std::string name) override
Constants for Builtin SQL Types supported by MapD.
const std::shared_ptr< TestColumnDescriptor > GEO_POINT
std::string to_string(char const *&&v)
StringColumnDescriptor(std::string col_type, SQLTypes sql_type, std::string pfix)
std::string get_column_definition() override
std::string getColumnWktStringValue(int row)
NumberColumnDescriptor(std::string col_type, SQLTypes sql_type, T null)
std::string get_column_value(int row) override
std::string getValueAsString(int row)
virtual std::string get_column_definition()=0
std::string get_column_value(int row) override
#define TIME_COLUMN_TEST(name, definition, sql_type, format, offset, scale)
bool skip_test(std::string name) override
bool check_column_value(int row, const TDatum *value) override
bool check_column_value(int row, const TDatum *value) override
bool checked_get(size_t row, const SOURCE_TYPE *boost_variant, RETURN_TYPE &val, RETURN_TYPE null_value)
virtual bool skip_test(std::string name)
std::string get_column_definition() override
#define NULL_TINYINT
Definition: sqltypes.h:171
std::shared_ptr< MapDClient > g_client
std::string get_column_definition() override
#define NULL_FLOAT
Definition: sqltypes.h:175
BooleanColumnDescriptor(std::string col_type, SQLTypes sql_type)
bool skip_test(std::string name) override
#define NULL_INT
Definition: sqltypes.h:173
GeoPointColumnDescriptor(SQLTypes sql_type=kPOINT)
TSessionId g_session_id
bool check_column_value(int row, const TDatum *value) override
Definition: sqltypes.h:54
Definition: sqltypes.h:55
void itasTestBody(std::vector< std::shared_ptr< TestColumnDescriptor >> &columnDescriptors, std::string targetPartitionScheme=")")
std::string get_column_definition() override
std::string get_column_definition() override
bool skip_test(std::string name) override
std::string get_column_definition() override
bool skip_test(std::string name) override
#define ARRAY_COLUMN_TEST(name, definition)
Definition: sqltypes.h:43
std::string get_column_value(int row) override
TEST_P(Ctas, CreateTableAsSelect)
virtual std::string get_column_value(int row)=0
virtual bool check_column_value(const int row, const TDatum *datum)=0
std::vector< std::shared_ptr< TestColumnDescriptor > > columnDescriptors
bool check_column_value(const int row, const TDatum *datum) override
GeoPolygonColumnDescriptor(SQLTypes sql_type=kPOLYGON)
bool check_column_value(int row, const TDatum *value) override
#define NULL_SMALLINT
Definition: sqltypes.h:172
std::vector< std::shared_ptr< TestColumnDescriptor > > columnDescriptors
GeoLinestringColumnDescriptor(SQLTypes sql_type=kLINESTRING)
std::string get_column_value(int row) override
const std::shared_ptr< TestColumnDescriptor > GEO_LINESTRING
void init_logger_stderr_only(int argc, char const *const *argv)
Definition: TestHelpers.h:194
bool skip_test(std::string name) override
std::string get_column_value(int row) override
int main(int argc, char *argv[])
std::string get_column_value(int row) override
bool skip_test(std::string name) override
std::string getColumnWktStringValue(int row)
Definition: sqltypes.h:47
bool skip_test(std::string name) override
static bool run
virtual ~TestColumnDescriptor()=default
#define BOOLEAN_COLUMN_TEST(name, c_type, definition, sql_type, null)
bool check_column_value(int row, const TDatum *value) override
std::string get_column_value(int row) override
#define STRING_COLUMN_TEST(name, definition, sql_type)
GeoMultiPolygonColumnDescriptor(SQLTypes sql_type=kMULTIPOLYGON)
bool check_column_value(int row, const TDatum *value) override
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
boost::variant< int64_t, double, float, NullableString > ScalarTargetValue
Definition: TargetValue.h:156
#define NUMBER_COLUMN_TEST(name, c_type, definition, sql_type, null)