OmniSciDB  04ee39c94c
TopKTest.cpp
Go to the documentation of this file.
1 
2 /*
3  * Copyright 2017 MapD Technologies, Inc.
4  *
5  * Licensed under the Apache License, Version 2.0 (the "License");
6  * you may not use this file except in compliance with the License.
7  * You may obtain a copy of the License at
8  *
9  * http://www.apache.org/licenses/LICENSE-2.0
10  *
11  * Unless required by applicable law or agreed to in writing, software
12  * distributed under the License is distributed on an "AS IS" BASIS,
13  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14  * See the License for the specific language governing permissions and
15  * limitations under the License.
16  */
17 
18 #include "../Import/Importer.h"
19 #include "../Parser/parser.h"
20 #include "../QueryEngine/ArrowResultSet.h"
21 #include "../QueryEngine/Descriptors/RelAlgExecutionDescriptor.h"
22 #include "../QueryEngine/Execute.h"
23 #include "../QueryRunner/QueryRunner.h"
24 #include "../SqliteConnector/SqliteConnector.h"
25 #include "TestHelpers.h"
26 
27 #include <gtest/gtest.h>
28 #include <boost/algorithm/string.hpp>
29 #include <boost/program_options.hpp>
30 #include <sstream>
31 
32 #ifndef BASE_PATH
33 #define BASE_PATH "./tmp"
34 #endif
35 
36 using namespace std;
37 
39 namespace {
40 
41 inline void run_ddl_statement(const std::string query_str) {
42  QR::get()->runDDLStatement(query_str);
43 }
44 
45 std::shared_ptr<ResultSet> run_multiple_agg(const string& query_str,
46  const ExecutorDeviceType device_type,
47  const bool allow_loop_joins) {
48  return QR::get()->runSQL(query_str, device_type, true, allow_loop_joins);
49 }
50 
51 std::shared_ptr<ResultSet> run_multiple_agg(const string& query_str,
52  const ExecutorDeviceType device_type) {
53  return run_multiple_agg(query_str, device_type, true);
54 }
55 
56 template <class T>
57 T v(const TargetValue& r) {
58  auto scalar_r = boost::get<ScalarTargetValue>(&r);
59  CHECK(scalar_r);
60  auto p = boost::get<T>(scalar_r);
61  CHECK(p);
62  return *p;
63 }
64 
65 bool skip_tests(const ExecutorDeviceType device_type) {
66 #ifdef HAVE_CUDA
67  return device_type == ExecutorDeviceType::GPU && !QR::get()->gpusPresent();
68 #else
69  return device_type == ExecutorDeviceType::GPU;
70 #endif
71 }
72 
73 bool approx_eq(const double v, const double target, const double eps = 0.01) {
74  const auto v_u64 = *reinterpret_cast<const uint64_t*>(may_alias_ptr(&v));
75  const auto target_u64 = *reinterpret_cast<const uint64_t*>(may_alias_ptr(&target));
76  return v_u64 == target_u64 || (target - eps < v && v < target + eps);
77 }
78 
80  public:
81  SQLiteComparator() : connector_("sqliteTestDB", "") {}
82 
83  void query(const std::string& query_string) { connector_.query(query_string); }
84 
85  void compare(const std::string& query_string, const ExecutorDeviceType device_type) {
86  const auto mapd_results = run_multiple_agg(query_string, device_type);
87  compare_impl(mapd_results.get(), query_string, device_type, false);
88  }
89 
90  void compare_arrow_output(const std::string& query_string,
91  const std::string& sqlite_query_string,
92  const ExecutorDeviceType device_type) {
93  const auto results = QR::get()->runSelectQuery(query_string, device_type, true, true);
94  const auto arrow_mapd_results = result_set_arrow_loopback(results);
95  compare_impl(arrow_mapd_results.get(), sqlite_query_string, device_type, false);
96  }
97 
98  void compare(const std::string& query_string,
99  const std::string& sqlite_query_string,
100  const ExecutorDeviceType device_type) {
101  const auto mapd_results = run_multiple_agg(query_string, device_type);
102  compare_impl(mapd_results.get(), sqlite_query_string, device_type, false);
103  }
104 
105  // added to deal with time shift for now testing
106  void compare_timstamp_approx(const std::string& query_string,
107  const ExecutorDeviceType device_type) {
108  const auto mapd_results = run_multiple_agg(query_string, device_type);
109  compare_impl(mapd_results.get(), query_string, device_type, true);
110  }
111 
112  private:
113  template <class MapDResults>
114  void compare_impl(const MapDResults* mapd_results,
115  const std::string& sqlite_query_string,
116  const ExecutorDeviceType device_type,
117  bool timestamp_approx) {
118  connector_.query(sqlite_query_string);
119  ASSERT_EQ(connector_.getNumRows(), mapd_results->rowCount());
120  const int num_rows{static_cast<int>(connector_.getNumRows())};
121  if (mapd_results->definitelyHasNoRows()) {
122  ASSERT_EQ(0, num_rows);
123  return;
124  }
125  if (!num_rows) {
126  return;
127  }
128  CHECK_EQ(connector_.getNumCols(), mapd_results->colCount());
129  const int num_cols{static_cast<int>(connector_.getNumCols())};
130  for (int row_idx = 0; row_idx < num_rows; ++row_idx) {
131  const auto crt_row = mapd_results->getNextRow(true, true);
132  CHECK(!crt_row.empty());
133  CHECK_EQ(static_cast<size_t>(num_cols), crt_row.size());
134  for (int col_idx = 0; col_idx < num_cols; ++col_idx) {
135  const auto ref_col_type = connector_.columnTypes[col_idx];
136  const auto mapd_variant = crt_row[col_idx];
137  const auto scalar_mapd_variant = boost::get<ScalarTargetValue>(&mapd_variant);
138  CHECK(scalar_mapd_variant);
139  const auto mapd_ti = mapd_results->getColType(col_idx);
140  const auto mapd_type = mapd_ti.get_type();
141  checkTypeConsistency(ref_col_type, mapd_ti);
142  const bool ref_is_null = connector_.isNull(row_idx, col_idx);
143  switch (mapd_type) {
144  case kSMALLINT:
145  case kINT:
146  case kBIGINT: {
147  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
148  ASSERT_NE(nullptr, mapd_as_int_p);
149  const auto mapd_val = *mapd_as_int_p;
150  if (ref_is_null) {
151  ASSERT_EQ(inline_int_null_val(mapd_ti), mapd_val);
152  } else {
153  const auto ref_val = connector_.getData<int64_t>(row_idx, col_idx);
154  ASSERT_EQ(ref_val, mapd_val);
155  }
156  break;
157  }
158  case kTEXT:
159  case kCHAR:
160  case kVARCHAR: {
161  const auto mapd_as_str_p = boost::get<NullableString>(scalar_mapd_variant);
162  ASSERT_NE(nullptr, mapd_as_str_p);
163  const auto mapd_str_notnull = boost::get<std::string>(mapd_as_str_p);
164  if (ref_is_null) {
165  CHECK(!mapd_str_notnull);
166  } else {
167  CHECK(mapd_str_notnull);
168  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
169  const auto mapd_val = *mapd_str_notnull;
170  ASSERT_EQ(ref_val, mapd_val);
171  }
172  break;
173  }
174  case kNUMERIC:
175  case kDECIMAL:
176  case kDOUBLE: {
177  const auto mapd_as_double_p = boost::get<double>(scalar_mapd_variant);
178  ASSERT_NE(nullptr, mapd_as_double_p);
179  const auto mapd_val = *mapd_as_double_p;
180  if (ref_is_null) {
181  ASSERT_EQ(inline_fp_null_val(SQLTypeInfo(kDOUBLE, false)), mapd_val);
182  } else {
183  const auto ref_val = connector_.getData<double>(row_idx, col_idx);
184  ASSERT_TRUE(approx_eq(ref_val, mapd_val));
185  }
186  break;
187  }
188  case kFLOAT: {
189  const auto mapd_as_float_p = boost::get<float>(scalar_mapd_variant);
190  ASSERT_NE(nullptr, mapd_as_float_p);
191  const auto mapd_val = *mapd_as_float_p;
192  if (ref_is_null) {
193  if (inline_fp_null_val(SQLTypeInfo(kFLOAT, false)) != mapd_val) {
194  CHECK(false);
195  }
196  } else {
197  const auto ref_val = connector_.getData<float>(row_idx, col_idx);
198  if (!approx_eq(ref_val, mapd_val)) {
199  CHECK(false);
200  }
201  }
202  break;
203  }
204  case kTIMESTAMP:
205  case kDATE: {
206  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
207  CHECK(mapd_as_int_p);
208  const auto mapd_val = *mapd_as_int_p;
209  if (ref_is_null) {
210  CHECK_EQ(inline_int_null_val(mapd_ti), mapd_val);
211  } else {
212  struct tm tm_struct {
213  0
214  };
215  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
216  const auto end_str =
217  strptime(ref_val.c_str(),
218  mapd_type == kTIMESTAMP ? "%Y-%m-%d %H:%M:%S" : "%Y-%m-%d",
219  &tm_struct);
220  if (end_str != nullptr) {
221  ASSERT_EQ(0, *end_str);
222  ASSERT_EQ(ref_val.size(), static_cast<size_t>(end_str - ref_val.c_str()));
223  }
224  if (timestamp_approx) {
225  // approximate result give 10 second lee way
226  ASSERT_NEAR(*mapd_as_int_p, timegm(&tm_struct), 10);
227  } else {
228  ASSERT_EQ(*mapd_as_int_p, timegm(&tm_struct));
229  }
230  }
231  break;
232  }
233  case kBOOLEAN: {
234  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
235  CHECK(mapd_as_int_p);
236  const auto mapd_val = *mapd_as_int_p;
237  if (ref_is_null) {
238  CHECK_EQ(inline_int_null_val(mapd_ti), mapd_val);
239  } else {
240  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
241  if (ref_val == "t") {
242  ASSERT_EQ(1, *mapd_as_int_p);
243  } else {
244  CHECK_EQ("f", ref_val);
245  ASSERT_EQ(0, *mapd_as_int_p);
246  }
247  }
248  break;
249  }
250  case kTIME: {
251  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
252  CHECK(mapd_as_int_p);
253  const auto mapd_val = *mapd_as_int_p;
254  if (ref_is_null) {
255  CHECK_EQ(inline_int_null_val(mapd_ti), mapd_val);
256  } else {
257  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
258  std::vector<std::string> time_tokens;
259  boost::split(time_tokens, ref_val, boost::is_any_of(":"));
260  ASSERT_EQ(size_t(3), time_tokens.size());
261  ASSERT_EQ(boost::lexical_cast<int64_t>(time_tokens[0]) * 3600 +
262  boost::lexical_cast<int64_t>(time_tokens[1]) * 60 +
263  boost::lexical_cast<int64_t>(time_tokens[2]),
264  *mapd_as_int_p);
265  }
266  break;
267  }
268  default:
269  CHECK(false);
270  }
271  }
272  }
273  }
274 
275  private:
276  static void checkTypeConsistency(const int ref_col_type, const SQLTypeInfo& mapd_ti) {
277  if (ref_col_type == SQLITE_NULL) {
278  // TODO(alex): re-enable the check that mapd_ti is nullable,
279  // got invalidated because of outer joins
280  return;
281  }
282  if (mapd_ti.is_integer()) {
283  CHECK_EQ(SQLITE_INTEGER, ref_col_type);
284  } else if (mapd_ti.is_fp() || mapd_ti.is_decimal()) {
285  CHECK_EQ(SQLITE_FLOAT, ref_col_type);
286  } else {
287  CHECK_EQ(SQLITE_TEXT, ref_col_type);
288  }
289  }
290 
292 };
293 
295 
296 void c(const std::string& query_string,
297  const std::string& sqlite_query_string,
298  const ExecutorDeviceType device_type) {
299  g_sqlite_comparator.compare(query_string, sqlite_query_string, device_type);
300 }
301 } // namespace
302 
303 #define SKIP_NO_GPU() \
304  if (skip_tests(dt)) { \
305  CHECK(dt == ExecutorDeviceType::GPU); \
306  LOG(WARNING) << "GPU not available, skipping GPU tests"; \
307  continue; \
308  }
309 
310 namespace {
311 
313  try {
314  // Drop old table
315  const std::string drop_old_test{"DROP TABLE IF EXISTS tdata;"};
316  run_ddl_statement(drop_old_test);
317  g_sqlite_comparator.query(drop_old_test);
318 
319  // Create a table
320  const std::string create_test_table{
321  "CREATE TABLE tdata (id SMALLINT, b BOOLEAN, i INT, bi BIGINT, n DECIMAL(10, 2), "
322  "f FLOAT, t TEXT, tt TIME, d "
323  "DATE, ts TIMESTAMP, vc VARCHAR(15));"};
324  run_ddl_statement(create_test_table);
325  g_sqlite_comparator.query(create_test_table);
326 
327  // Insert data into the table
328  std::vector<std::string> data_col_value_list;
329  data_col_value_list.emplace_back(
330  "1, 't', 23, 2349923, 111.1, 1.1, 'SFO', '15:13:14', '1999-09-09', '2014-12-13 "
331  "22:23:15', 'paris'");
332  data_col_value_list.emplace_back(
333  "2, 'f', null, -973273, 7263.11, 87.1, null, '20:05:00', '2017-12-12', "
334  "'2017-12-12 20:05:00', 'toronto'");
335  data_col_value_list.emplace_back(
336  "3, 'f', 702, 87395, 333.5, null, 'YVR', '11:11:11', '2010-01-01', '2010-01-02 "
337  "04:11:45', 'vancouver'");
338  data_col_value_list.emplace_back(
339  "4, null, 864, 100001, null, 9.9, 'SJC', null, '2015-05-05', '2010-05-05 "
340  "05:15:55', 'london'");
341  data_col_value_list.emplace_back(
342  "5, 'f', 333, 112233, 99.9, 9.9, 'ABQ', '22:22:22', '2015-05-05', '2010-05-05 "
343  "05:15:55', 'new york'");
344  data_col_value_list.emplace_back(
345  "6, 't', -3, 18, 765.8, 2.2, 'YYZ', '00:00:01', null, '2009-01-08 12:13:14', "
346  "null");
347  data_col_value_list.emplace_back(
348  "7, 'f', -9873, 3789, 789.3, 4.7, 'DCA', '11:22:33', '2001-02-03', '2005-04-03 "
349  "15:16:17', 'rio de janerio'");
350  data_col_value_list.emplace_back(
351  "8, 't', 12, 4321, 83.9, 1.2, 'DXB', '21:20:10', null, '2007-12-01 23:22:21', "
352  "'dubai'");
353  data_col_value_list.emplace_back(
354  "9, 't', 48, null, 83.9, 1.2, 'BWI', '09:08:07', '2001-09-11', null, "
355  "'washington'");
356  data_col_value_list.emplace_back(
357  "10, 'f', 99, 777, 77.7, 7.7, 'LLBG', '07:07:07', '2017-07-07', '2017-07-07 "
358  "07:07:07', 'Tel Aviv'");
359 
360  for (const auto& data_col_values : data_col_value_list) {
361  std::string insert_query = "INSERT INTO tdata VALUES(" + data_col_values + ");";
363  g_sqlite_comparator.query(insert_query);
364  }
365  } catch (...) {
366  LOG(ERROR) << "Failed to (re-)create table 'test'";
367  return -EEXIST;
368  }
369 
370  return 0;
371 }
372 
373 void drop_tables() {
374  const std::string drop_test_inner{"DROP TABLE tdata;"};
375  run_ddl_statement(drop_test_inner);
376  g_sqlite_comparator.query(drop_test_inner);
377 }
378 } // namespace
379 
380 TEST(Select, TopK_LIMIT_AscendSort) {
382  SKIP_NO_GPU();
383  c("SELECT i FROM tdata ORDER BY i NULLS FIRST LIMIT 5;",
384  "SELECT i FROM tdata ORDER BY i ASC LIMIT 5;",
385  dt);
386  c("SELECT b FROM tdata ORDER BY b NULLS FIRST LIMIT 5;",
387  "SELECT b FROM tdata ORDER BY b LIMIT 5;",
388  dt);
389  c("SELECT bi FROM tdata ORDER BY bi NULLS FIRST LIMIT 5;",
390  "SELECT bi FROM tdata ORDER BY bi LIMIT 5;",
391  dt);
392  c("SELECT n FROM tdata ORDER BY n NULLS FIRST LIMIT 5;",
393  "SELECT n FROM tdata ORDER BY n LIMIT 5;",
394  dt);
395  c("SELECT f FROM tdata ORDER BY f NULLS FIRST LIMIT 5;",
396  "SELECT f FROM tdata ORDER BY f LIMIT 5;",
397  dt);
398  c("SELECT tt FROM tdata ORDER BY tt NULLS FIRST LIMIT 5;",
399  "SELECT tt FROM tdata ORDER BY tt LIMIT 5;",
400  dt);
401  c("SELECT ts FROM tdata ORDER BY ts NULLS FIRST LIMIT 5;",
402  "SELECT ts FROM tdata ORDER BY ts LIMIT 5;",
403  dt);
404  c("SELECT d FROM tdata ORDER BY d NULLS FIRST LIMIT 5;",
405  "SELECT d FROM tdata ORDER BY d LIMIT 5;",
406  dt);
407  }
408 }
409 
410 TEST(Select, TopK_LIMIT_DescendSort) {
412  SKIP_NO_GPU();
413  c("SELECT i FROM tdata ORDER BY i DESC NULLS LAST LIMIT 5;",
414  "SELECT i FROM tdata ORDER BY i DESC LIMIT 5;",
415  dt);
416  c("SELECT b FROM tdata ORDER BY b DESC NULLS LAST LIMIT 5;",
417  "SELECT b FROM tdata ORDER BY b DESC LIMIT 5;",
418  dt);
419  c("SELECT bi FROM tdata ORDER BY bi DESC NULLS LAST LIMIT 5;",
420  "SELECT bi FROM tdata ORDER BY bi DESC LIMIT 5;",
421  dt);
422  c("SELECT n FROM tdata ORDER BY n DESC NULLS LAST LIMIT 5;",
423  "SELECT n FROM tdata ORDER BY n DESC LIMIT 5;",
424  dt);
425  c("SELECT f FROM tdata ORDER BY f DESC NULLS LAST LIMIT 5;",
426  "SELECT f FROM tdata ORDER BY f DESC LIMIT 5;",
427  dt);
428  c("SELECT tt FROM tdata ORDER BY tt DESC NULLS LAST LIMIT 5;",
429  "SELECT tt FROM tdata ORDER BY tt DESC LIMIT 5;",
430  dt);
431  c("SELECT ts FROM tdata ORDER BY ts DESC NULLS LAST LIMIT 5;",
432  "SELECT ts FROM tdata ORDER BY ts DESC LIMIT 5;",
433  dt);
434  c("SELECT d FROM tdata ORDER BY d DESC NULLS LAST LIMIT 5;",
435  "SELECT d FROM tdata ORDER BY d DESC LIMIT 5;",
436  dt);
437  }
438 }
439 
440 TEST(Select, TopK_LIMIT_GreaterThan_TotalOfDataRows_AscendSort) {
442  SKIP_NO_GPU();
443  c("SELECT b FROM tdata ORDER BY b NULLS FIRST LIMIT 11;",
444  "SELECT b FROM tdata ORDER BY b LIMIT 11;",
445  dt);
446  c("SELECT bi FROM tdata ORDER BY bi NULLS FIRST LIMIT 11;",
447  "SELECT bi FROM tdata ORDER BY bi LIMIT 11;",
448  dt);
449  c("SELECT n FROM tdata ORDER BY n NULLS FIRST LIMIT 11;",
450  "SELECT n FROM tdata ORDER BY n LIMIT 11;",
451  dt);
452  c("SELECT f FROM tdata ORDER BY f NULLS FIRST LIMIT 11;",
453  "SELECT f FROM tdata ORDER BY f LIMIT 11;",
454  dt);
455  c("SELECT tt FROM tdata ORDER BY tt NULLS FIRST LIMIT 11;",
456  "SELECT tt FROM tdata ORDER BY tt LIMIT 11;",
457  dt);
458  c("SELECT ts FROM tdata ORDER BY ts NULLS FIRST LIMIT 11;",
459  "SELECT ts FROM tdata ORDER BY ts LIMIT 11;",
460  dt);
461  c("SELECT d FROM tdata ORDER BY d NULLS FIRST LIMIT 11;",
462  "SELECT d FROM tdata ORDER BY d LIMIT 11;",
463  dt);
464  }
465 }
466 
467 TEST(Select, TopK_LIMIT_GreaterThan_TotalOfDataRows_DescendSort) {
469  SKIP_NO_GPU();
470  c("SELECT i FROM tdata ORDER BY i DESC NULLS LAST LIMIT 11;",
471  "SELECT i FROM tdata ORDER BY i DESC LIMIT 11;",
472  dt);
473  c("SELECT b FROM tdata ORDER BY b DESC NULLS LAST LIMIT 11;",
474  "SELECT b FROM tdata ORDER BY b DESC LIMIT 11;",
475  dt);
476  c("SELECT bi FROM tdata ORDER BY bi DESC NULLS LAST LIMIT 11;",
477  "SELECT bi FROM tdata ORDER BY bi DESC LIMIT 11;",
478  dt);
479  c("SELECT n FROM tdata ORDER BY n DESC NULLS LAST LIMIT 11;",
480  "SELECT n FROM tdata ORDER BY n DESC LIMIT 11;",
481  dt);
482  c("SELECT f FROM tdata ORDER BY f DESC NULLS LAST LIMIT 11;",
483  "SELECT f FROM tdata ORDER BY f DESC LIMIT 11;",
484  dt);
485  c("SELECT tt FROM tdata ORDER BY tt DESC NULLS LAST LIMIT 11;",
486  "SELECT tt FROM tdata ORDER BY tt DESC LIMIT 11;",
487  dt);
488  c("SELECT ts FROM tdata ORDER BY ts DESC NULLS LAST LIMIT 11;",
489  "SELECT ts FROM tdata ORDER BY ts DESC LIMIT 11;",
490  dt);
491  c("SELECT d FROM tdata ORDER BY d DESC NULLS LAST LIMIT 11;",
492  "SELECT d FROM tdata ORDER BY d DESC LIMIT 11;",
493  dt);
494  }
495 }
496 
497 TEST(Select, TopK_LIMIT_OFFSET_TopHalf_AscendSort) {
499  SKIP_NO_GPU();
500  c("SELECT i FROM tdata ORDER BY i NULLS FIRST LIMIT 5 OFFSET 0;",
501  "SELECT i FROM tdata ORDER BY i LIMIT 5 OFFSET 0;",
502  dt);
503  c("SELECT b FROM tdata ORDER BY b NULLS FIRST LIMIT 5 OFFSET 0;",
504  "SELECT b FROM tdata ORDER BY b LIMIT 5 OFFSET 0;",
505  dt);
506  c("SELECT bi FROM tdata ORDER BY bi NULLS FIRST LIMIT 5 OFFSET 0;",
507  "SELECT bi FROM tdata ORDER BY bi LIMIT 5 OFFSET 0;",
508  dt);
509  c("SELECT n FROM tdata ORDER BY n NULLS FIRST LIMIT 5 OFFSET 0;",
510  "SELECT n FROM tdata ORDER BY n LIMIT 5 OFFSET 0;",
511  dt);
512  c("SELECT f FROM tdata ORDER BY f NULLS FIRST LIMIT 5 OFFSET 0;",
513  "SELECT f FROM tdata ORDER BY f LIMIT 5 OFFSET 0;",
514  dt);
515  c("SELECT tt FROM tdata ORDER BY tt NULLS FIRST LIMIT 5 OFFSET 0;",
516  "SELECT tt FROM tdata ORDER BY tt LIMIT 5 OFFSET 0;",
517  dt);
518  c("SELECT ts FROM tdata ORDER BY ts NULLS FIRST LIMIT 5 OFFSET 0;",
519  "SELECT ts FROM tdata ORDER BY ts LIMIT 5 OFFSET 0;",
520  dt);
521  c("SELECT d FROM tdata ORDER BY d NULLS FIRST LIMIT 5 OFFSET 0;",
522  "SELECT d FROM tdata ORDER BY d LIMIT 5 OFFSET 0;",
523  dt);
524  }
525 }
526 
527 TEST(Select, TopK_LIMIT_OFFSET_TopHalf_DescendSort) {
529  SKIP_NO_GPU();
530  c("SELECT i FROM tdata ORDER BY i DESC NULLS LAST LIMIT 5 OFFSET 0;",
531  "SELECT i FROM tdata ORDER BY i DESC LIMIT 5 OFFSET 0;",
532  dt);
533  c("SELECT b FROM tdata ORDER BY b DESC NULLS LAST LIMIT 5 OFFSET 0;",
534  "SELECT b FROM tdata ORDER BY b DESC LIMIT 5 OFFSET 0;",
535  dt);
536  c("SELECT bi FROM tdata ORDER BY bi DESC NULLS LAST LIMIT 5 OFFSET 0;",
537  "SELECT bi FROM tdata ORDER BY bi DESC LIMIT 5 OFFSET 0;",
538  dt);
539  c("SELECT n FROM tdata ORDER BY n DESC NULLS LAST LIMIT 5 OFFSET 0;",
540  "SELECT n FROM tdata ORDER BY n DESC LIMIT 5 OFFSET 0;",
541  dt);
542  c("SELECT f FROM tdata ORDER BY f DESC NULLS LAST LIMIT 5 OFFSET 0;",
543  "SELECT f FROM tdata ORDER BY f DESC LIMIT 5 OFFSET 0;",
544  dt);
545  c("SELECT tt FROM tdata ORDER BY tt DESC NULLS LAST LIMIT 5 OFFSET 0;",
546  "SELECT tt FROM tdata ORDER BY tt DESC LIMIT 5 OFFSET 0;",
547  dt);
548  c("SELECT ts FROM tdata ORDER BY ts DESC NULLS LAST LIMIT 5 OFFSET 0;",
549  "SELECT ts FROM tdata ORDER BY ts DESC LIMIT 5 OFFSET 0;",
550  dt);
551  c("SELECT d FROM tdata ORDER BY d DESC NULLS LAST LIMIT 5 OFFSET 0;",
552  "SELECT d FROM tdata ORDER BY d DESC LIMIT 5 OFFSET 0;xxx",
553  dt);
554  }
555 }
556 
557 TEST(Select, DISABLED_TopK_LIMIT_OFFSET_BottomHalf_AscendSort) {
559  SKIP_NO_GPU();
560  c("SELECT i FROM tdata ORDER BY i NULLS FIRST LIMIT 5 OFFSET 5;",
561  "SELECT i FROM tdata ORDER BY i LIMIT 5 OFFSET 5;",
562  dt);
563  c("SELECT b FROM tdata ORDER BY b NULLS FIRST LIMIT 5 OFFSET 5;",
564  "SELECT b FROM tdata ORDER BY b LIMIT 5 OFFSET 5;",
565  dt);
566  c("SELECT bi FROM tdata ORDER BY bi NULLS FIRST LIMIT 5 OFFSET 5;",
567  "SELECT bi FROM tdata ORDER BY bi LIMIT 5 OFFSET 5;",
568  dt);
569  c("SELECT n FROM tdata ORDER BY n NULLS FIRST LIMIT 5 OFFSET 5;",
570  "SELECT n FROM tdata ORDER BY n LIMIT 5 OFFSET 5;",
571  dt);
572  c("SELECT f FROM tdata ORDER BY f NULLS FIRST LIMIT 5 OFFSET 5;",
573  "SELECT f FROM tdata ORDER BY f LIMIT 5 OFFSET 5;",
574  dt);
575  c("SELECT tt FROM tdata ORDER BY tt NULLS FIRST LIMIT 5 OFFSET 5;",
576  "SELECT tt FROM tdata ORDER BY tt LIMIT 5 OFFSET 5;",
577  dt);
578  c("SELECT ts FROM tdata ORDER BY ts NULLS FIRST LIMIT 5 OFFSET 5;",
579  "SELECT ts FROM tdata ORDER BY ts LIMIT 5 OFFSET 5;",
580  dt);
581  c("SELECT d FROM tdata ORDER BY d NULLS FIRST LIMIT 5 OFFSET 5;",
582  "SELECT d FROM tdata ORDER BY d LIMIT 5 OFFSET 5;",
583  dt);
584  }
585 }
586 
587 TEST(Select, DISABLED_TopK_LIMIT_OFFSET_BottomHalf_DescendSort) {
589  SKIP_NO_GPU();
590  c("SELECT i FROM tdata ORDER BY i DESC NULLS LAST LIMIT 5 OFFSET 5;",
591  "SELECT i FROM tdata ORDER BY i DESC LIMIT 5 OFFSET 5;",
592  dt);
593  c("SELECT b FROM tdata ORDER BY b DESC NULLS LAST LIMIT 5 OFFSET 5;",
594  "SELECT b FROM tdata ORDER BY b DESC LIMIT 5 OFFSET 5;",
595  dt);
596  c("SELECT bi FROM tdata ORDER BY bi DESC NULLS LAST LIMIT 5 OFFSET 5;",
597  "SELECT bi FROM tdata ORDER BY bi DESC LIMIT 5 OFFSET 5;",
598  dt);
599  c("SELECT n FROM tdata ORDER BY n DESC NULLS LAST LIMIT 5 OFFSET 5;",
600  "SELECT n FROM tdata ORDER BY n DESC LIMIT 5 OFFSET 5;",
601  dt);
602  c("SELECT f FROM tdata ORDER BY f DESC NULLS LAST LIMIT 5 OFFSET 5;",
603  "SELECT f FROM tdata ORDER BY f DESC LIMIT 5 OFFSET 5;",
604  dt);
605  c("SELECT tt FROM tdata ORDER BY tt DESC NULLS LAST LIMIT 5 OFFSET 5;",
606  "SELECT tt FROM tdata ORDER BY tt DESC LIMIT 5 OFFSET 5;",
607  dt);
608  c("SELECT ts FROM tdata ORDER BY ts DESC NULLS LAST LIMIT 5 OFFSET 5;",
609  "SELECT ts FROM tdata ORDER BY ts DESC LIMIT 5 OFFSET 5;",
610  dt);
611  c("SELECT d FROM tdata ORDER BY d DESC NULLS LAST LIMIT 5 OFFSET 5;",
612  "SELECT d FROM tdata ORDER BY d DESC LIMIT 5 OFFSET 5;",
613  dt);
614  }
615 }
616 
617 TEST(Select, DISABLED_TopK_LIMIT_OFFSET_GreaterThan_TotalOfDataRows_AscendSort) {
619  SKIP_NO_GPU();
620  c("SELECT i FROM tdata ORDER BY i NULLS FIRST LIMIT 5 OFFSET 11;",
621  "SELECT i FROM tdata ORDER BY i LIMIT 5 OFFSET 11;",
622  dt);
623  c("SELECT b FROM tdata ORDER BY b NULLS FIRST LIMIT 5 OFFSET 11;",
624  "SELECT b FROM tdata ORDER BY b LIMIT 5 OFFSET 11;",
625  dt);
626  c("SELECT bi FROM tdata ORDER BY bi NULLS FIRST LIMIT 5 OFFSET 11;",
627  "SELECT bi FROM tdata ORDER BY bi LIMIT 5 OFFSET 11;",
628  dt);
629  c("SELECT n FROM tdata ORDER BY n NULLS FIRST LIMIT 5 OFFSET 11;",
630  "SELECT n FROM tdata ORDER BY n LIMIT 5 OFFSET 11;",
631  dt);
632  c("SELECT f FROM tdata ORDER BY f NULLS FIRST LIMIT 5 OFFSET 11;",
633  "SELECT f FROM tdata ORDER BY f LIMIT 5 OFFSET 11;",
634  dt);
635  c("SELECT tt FROM tdata ORDER BY tt NULLS FIRST LIMIT 5 OFFSET 11;",
636  "SELECT tt FROM tdata ORDER BY tt LIMIT 5 OFFSET 11;",
637  dt);
638  c("SELECT ts FROM tdata ORDER BY ts NULLS FIRST LIMIT 5 OFFSET 11;",
639  "SELECT ts FROM tdata ORDER BY ts LIMIT 5 OFFSET 11;",
640  dt);
641  c("SELECT d FROM tdata ORDER BY d NULLS FIRST LIMIT 5 OFFSET 11;",
642  "SELECT d FROM tdata ORDER BY d LIMIT 5 OFFSET 11;",
643  dt);
644  }
645 }
646 
647 TEST(Select, DISABLED_TopK_LIMIT_OFFSET_GreaterThan_TotalOfDataRows_DescendSort) {
649  SKIP_NO_GPU();
650  c("SELECT i FROM tdata ORDER BY i DESC NULLS LAST LIMIT 5 OFFSET 11;",
651  "SELECT i FROM tdata ORDER BY i DESC LIMIT 5 OFFSET 11;",
652  dt);
653  c("SELECT b FROM tdata ORDER BY b DESC NULLS LAST LIMIT 5 OFFSET 11;",
654  "SELECT b FROM tdata ORDER BY b DESC LIMIT 5 OFFSET 11;",
655  dt);
656  c("SELECT bi FROM tdata ORDER BY bi DESC NULLS LAST LIMIT 5 OFFSET 11;",
657  "SELECT bi FROM tdata ORDER BY bi DESC LIMIT 5 OFFSET 11;",
658  dt);
659  c("SELECT n FROM tdata ORDER BY n DESC NULLS LAST LIMIT 5 OFFSET 11;",
660  "SELECT n FROM tdata ORDER BY n DESC LIMIT 5 OFFSET 11;",
661  dt);
662  c("SELECT f FROM tdata ORDER BY f DESC NULLS LAST LIMIT 5 OFFSET 11;",
663  "SELECT f FROM tdata ORDER BY f DESC LIMIT 5 OFFSET 11;",
664  dt);
665  c("SELECT tt FROM tdata ORDER BY tt DESC NULLS LAST LIMIT 5 OFFSET 11;",
666  "SELECT tt FROM tdata ORDER BY tt DESC LIMIT 5 OFFSET 11;",
667  dt);
668  c("SELECT ts FROM tdata ORDER BY ts DESC NULLS LAST LIMIT 5 OFFSET 11;",
669  "SELECT ts FROM tdata ORDER BY ts DESC LIMIT 5 OFFSET 11;",
670  dt);
671  c("SELECT d FROM tdata ORDER BY d DESC NULLS LAST LIMIT 5 OFFSET 11;",
672  "SELECT d FROM tdata ORDER BY d DESC LIMIT 5 OFFSET 11;",
673  dt);
674  }
675 }
676 
677 TEST(Select, DISABLED_TopK_LIMIT_OFFSET_DifferentOrders) {
679  SKIP_NO_GPU();
680  c("SELECT i,d FROM tdata ORDER BY d DESC NULLS LAST LIMIT 5 OFFSET 11;",
681  "SELECT i,d FROM tdata ORDER BY d DESC LIMIT 5 OFFSET 11;",
682  dt);
683  c("SELECT i,d FROM tdata ORDER BY i DESC NULLS LAST LIMIT 5 OFFSET 11;",
684  "SELECT i,d FROM tdata ORDER BY i DESC LIMIT 5 OFFSET 11;",
685  dt);
686  c("SELECT i,d FROM tdata ORDER BY i,d DESC NULLS LAST LIMIT 5 OFFSET 11;",
687  "SELECT i,d FROM tdata ORDER BY i,d DESC LIMIT 5 OFFSET 11;",
688  dt);
689  c("SELECT d FROM tdata ORDER BY i DESC NULLS LAST LIMIT 5 OFFSET 11;",
690  "SELECT d FROM tdata ORDER BY i DESC LIMIT 5 OFFSET 11;",
691  dt);
692  c("SELECT d FROM tdata ORDER BY i,d DESC NULLS LAST LIMIT 5 OFFSET 11;",
693  "SELECT d FROM tdata ORDER BY i,d DESC LIMIT 5 OFFSET 11;",
694  dt);
695 
696  c("SELECT i,d FROM tdata ORDER BY d NULLS FIRST LIMIT 5 OFFSET 11;",
697  "SELECT i,d FROM tdata ORDER BY d LIMIT 5 OFFSET 11;",
698  dt);
699  c("SELECT i,d FROM tdata ORDER BY i NULLS FIRST LIMIT 5 OFFSET 11;",
700  "SELECT i,d FROM tdata ORDER BY i LIMIT 5 OFFSET 11;",
701  dt);
702  c("SELECT i,d FROM tdata ORDER BY i,d NULLS FIRST LIMIT 5 OFFSET 11;",
703  "SELECT i,d FROM tdata ORDER BY i,d LIMIT 5 OFFSET 11;",
704  dt);
705  c("SELECT d FROM tdata ORDER BY i NULLS FIRST LIMIT 5 OFFSET 11;",
706  "SELECT d FROM tdata ORDER BY i LIMIT 5 OFFSET 11;",
707  dt);
708  c("SELECT d FROM tdata ORDER BY i,d NULLS FIRST LIMIT 5 OFFSET 11;",
709  "SELECT d FROM tdata ORDER BY i,d LIMIT 5 OFFSET 11;",
710  dt);
711  }
712 }
713 
714 int main(int argc, char* argv[]) {
716  ::testing::InitGoogleTest(&argc, argv);
717 
719 
720  int err{0};
722  if (err) {
723  return err;
724  }
725 
726  try {
727  err = RUN_ALL_TESTS();
728  } catch (const std::exception& e) {
729  LOG(ERROR) << e.what();
730  }
731 
732  drop_tables();
733  QR::reset();
734  return err;
735 }
#define CHECK_EQ(x, y)
Definition: Logger.h:195
virtual ExecutionResult runSelectQuery(const std::string &query_str, const ExecutorDeviceType device_type, const bool hoist_literals, const bool allow_loop_joins, const bool just_explain=false)
#define SKIP_NO_GPU()
Definition: TopKTest.cpp:303
SQLiteComparator g_sqlite_comparator
Definition: TopKTest.cpp:294
const int8_t const int64_t * num_rows
Definition: sqltypes.h:51
bool is_fp() const
Definition: sqltypes.h:454
ExecutorDeviceType
#define LOG(tag)
Definition: Logger.h:182
double inline_fp_null_val(const SQL_TYPE_INFO &ti)
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
virtual std::shared_ptr< ResultSet > runSQL(const std::string &query_str, const ExecutorDeviceType device_type, const bool hoist_literals=true, const bool allow_loop_joins=true)
void compare_arrow_output(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
Definition: TopKTest.cpp:90
bool is_integer() const
Definition: sqltypes.h:452
T v(const TargetValue &r)
void compare_impl(const MapDResults *mapd_results, const std::string &sqlite_query_string, const ExecutorDeviceType device_type, bool timestamp_approx)
Definition: TopKTest.cpp:114
bool is_decimal() const
Definition: sqltypes.h:453
void query(const std::string &query_string)
Definition: TopKTest.cpp:83
void compare(const std::string &query_string, const ExecutorDeviceType device_type)
Definition: TopKTest.cpp:85
virtual void runDDLStatement(const std::string &)
int main(int argc, char *argv[])
Definition: TopKTest.cpp:714
void drop_tables(std::string prefix, int max)
SQLTypeInfoCore< ArrayContextTypeSizer, ExecutorTypePackaging, DateTimeFacilities > SQLTypeInfo
Definition: sqltypes.h:823
static QueryRunner * get()
Definition: QueryRunner.h:115
bool approx_eq(const double v, const double target, const double eps=0.01)
Definition: TopKTest.cpp:73
Definition: sqltypes.h:54
Definition: sqltypes.h:55
std::unique_ptr< ArrowResultSet > result_set_arrow_loopback(const ExecutionResult &results)
void compare(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
Definition: TopKTest.cpp:98
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
Definition: TopKTest.cpp:296
std::vector< std::string > split(const std::string &str, const std::string &delim)
Definition: sqltypes.h:43
TEST(Select, TopK_LIMIT_AscendSort)
Definition: TopKTest.cpp:380
#define CHECK(condition)
Definition: Logger.h:187
void init_logger_stderr_only(int argc, char const *const *argv)
Definition: TestHelpers.h:194
int64_t inline_int_null_val(const SQL_TYPE_INFO &ti)
boost::variant< ScalarTargetValue, ArrayTargetValue, GeoTargetValue, GeoTargetValuePtr > TargetValue
Definition: TargetValue.h:167
Definition: sqltypes.h:47
#define BASE_PATH
Definition: TopKTest.cpp:33
static void checkTypeConsistency(const int ref_col_type, const SQLTypeInfo &mapd_ti)
Definition: TopKTest.cpp:276
void compare_timstamp_approx(const std::string &query_string, const ExecutorDeviceType device_type)
Definition: TopKTest.cpp:106
bool skip_tests(const ExecutorDeviceType device_type)
Definition: TopKTest.cpp:65
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)