OmniSciDB  c07336695a
ExecuteTest.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 "TestHelpers.h"
18 
19 #include "../Import/Importer.h"
20 #include "../Parser/parser.h"
21 #include "../QueryEngine/ArrowResultSet.h"
22 #include "../QueryEngine/Descriptors/RelAlgExecutionDescriptor.h"
23 #include "../QueryEngine/Execute.h"
24 #include "../QueryEngine/ResultSetReductionJIT.h"
25 #include "../QueryRunner/QueryRunner.h"
26 #include "../Shared/ConfigResolve.h"
27 #include "../Shared/TimeGM.h"
28 #include "../Shared/scope.h"
29 #include "../SqliteConnector/SqliteConnector.h"
30 #include "DistributedLoader.h"
31 
32 #include <gtest/gtest.h>
33 #include <boost/algorithm/string.hpp>
34 #include <boost/any.hpp>
35 #include <boost/program_options.hpp>
36 #include <cmath>
37 #include <sstream>
38 
39 #ifndef BASE_PATH
40 #define BASE_PATH "./tmp"
41 #endif
42 
43 using namespace std;
44 using namespace TestHelpers;
45 
46 bool g_aggregator{false};
47 
49 extern bool g_enable_smem_group_by;
50 extern bool g_allow_cpu_retry;
51 extern bool g_enable_watchdog;
52 extern bool g_skip_intermediate_count;
53 
54 extern unsigned g_trivial_loop_join_threshold;
55 extern bool g_enable_overlaps_hashjoin;
56 extern double g_gpu_mem_limit_percent;
57 
58 extern bool g_enable_window_functions;
59 extern bool g_enable_bump_allocator;
60 
61 extern size_t g_leaf_count;
62 
64 
65 namespace {
66 
67 bool g_hoist_literals{true};
68 size_t g_shard_count{0};
69 bool g_use_row_iterator{true};
70 size_t g_num_leafs{1};
71 bool g_keep_test_data{false};
72 
74  auto session = QR::get()->getSession();
75  const auto cuda_mgr = session->getCatalog().getDataMgr().getCudaMgr();
76  const int device_count = cuda_mgr ? cuda_mgr->getDeviceCount() : 0;
77  return g_num_leafs * (device_count > 1 ? device_count : 1);
78 }
79 
80 struct ShardInfo {
81  const std::string shard_col;
82  const size_t shard_count;
83 };
84 
86  const std::string col;
87  const std::string ref_table;
88  const std::string ref_col;
89 };
90 
92  const std::string& columns_definition,
93  const std::string& table_name,
94  const ShardInfo& shard_info,
95  const std::vector<SharedDictionaryInfo>& shared_dict_info,
96  const size_t fragment_size,
97  const bool delete_support = true,
98  const bool replicated = false) {
99  const std::string shard_key_def{
100  shard_info.shard_col.empty() ? "" : ", SHARD KEY (" + shard_info.shard_col + ")"};
101 
102  std::vector<std::string> shared_dict_def;
103  if (shared_dict_info.size() > 0) {
104  for (size_t idx = 0; idx < shared_dict_info.size(); ++idx) {
105  shared_dict_def.push_back(", SHARED DICTIONARY (" + shared_dict_info[idx].col +
106  ") REFERENCES " + shared_dict_info[idx].ref_table + "(" +
107  shared_dict_info[idx].ref_col + ")");
108  }
109  }
110 
111  std::ostringstream with_statement_assembly;
112  if (!shard_info.shard_col.empty()) {
113  with_statement_assembly << "shard_count=" << shard_info.shard_count << ", ";
114  }
115  with_statement_assembly << "fragment_size=" << fragment_size;
116 
117  if (delete_support) {
118  with_statement_assembly << ", vacuum='delayed'";
119  }
120 
121  // const std::string fragment_size_def{shard_info.shard_col.empty() ? "fragment_size=" +
122  // std::to_string(fragment_size)
123  // : ""};
124  // const std::string shard_count_def{shard_info.shard_col.empty() ? "" : "shard_count="
125  // +
126  // std::to_string(shard_info.shard_count)};
127  const std::string replicated_def{
128  (!replicated || !shard_info.shard_col.empty()) ? "" : ", PARTITIONS='REPLICATED' "};
129 
130  return "CREATE TABLE " + table_name + "(" + columns_definition + shard_key_def +
131  boost::algorithm::join(shared_dict_def, "") + ") WITH (" +
132  with_statement_assembly.str() + replicated_def + ");";
133 }
134 
135 std::shared_ptr<ResultSet> run_multiple_agg(const string& query_str,
136  const ExecutorDeviceType device_type,
137  const bool allow_loop_joins) {
138  return QR::get()->runSQL(query_str, device_type, g_hoist_literals, allow_loop_joins);
139 }
140 
141 std::shared_ptr<ResultSet> run_multiple_agg(const string& query_str,
142  const ExecutorDeviceType device_type) {
143  return QR::get()->runSQL(query_str, device_type, g_hoist_literals, true);
144 }
145 
146 TargetValue run_simple_agg(const string& query_str,
147  const ExecutorDeviceType device_type,
148  const bool geo_return_geo_tv = true,
149  const bool allow_loop_joins = true) {
150  auto rows = QR::get()->runSQL(query_str, device_type, allow_loop_joins);
151  if (geo_return_geo_tv) {
152  rows->setGeoReturnType(ResultSet::GeoReturnType::GeoTargetValue);
153  }
154  auto crt_row = rows->getNextRow(true, true);
155  CHECK_EQ(size_t(1), crt_row.size()) << query_str;
156  return crt_row[0];
157 }
158 
159 TargetValue get_first_target(const string& query_str,
160  const ExecutorDeviceType device_type,
161  const bool geo_return_geo_tv = true) {
162  auto rows = run_multiple_agg(query_str, device_type);
163  if (geo_return_geo_tv) {
164  rows->setGeoReturnType(ResultSet::GeoReturnType::GeoTargetValue);
165  }
166  auto crt_row = rows->getNextRow(true, true);
167  CHECK_GE(crt_row.size(), size_t(1)) << query_str;
168  return crt_row[0];
169 }
170 
171 inline void run_ddl_statement(const std::string& create_table_stmt) {
172  QR::get()->runDDLStatement(create_table_stmt);
173 }
174 
175 bool skip_tests(const ExecutorDeviceType device_type) {
176 #ifdef HAVE_CUDA
177  return device_type == ExecutorDeviceType::GPU && !(QR::get()->gpusPresent());
178 #else
179  return device_type == ExecutorDeviceType::GPU;
180 #endif
181 }
182 
183 bool approx_eq(const double v, const double target, const double eps = 0.01) {
184  const auto v_u64 = *reinterpret_cast<const uint64_t*>(may_alias_ptr(&v));
185  const auto target_u64 = *reinterpret_cast<const uint64_t*>(may_alias_ptr(&target));
186  return v_u64 == target_u64 || (target - eps < v && v < target + eps);
187 }
188 
189 int parse_fractional_seconds(uint sfrac, int ntotal, SQLTypeInfo& ti) {
190  return TimeGM::instance().parse_fractional_seconds(sfrac, ntotal, ti);
191 }
192 
194  public:
195  SQLiteComparator() : connector_("sqliteTestDB", "") {}
196 
197  void query(const std::string& query_string) { connector_.query(query_string); }
198 
199  void compare(const std::string& query_string, const ExecutorDeviceType device_type) {
200  const auto mapd_results = run_multiple_agg(query_string, device_type);
201  compare_impl(mapd_results.get(), query_string, device_type, false);
202  }
203 
204  void compare_arrow_output(const std::string& query_string,
205  const std::string& sqlite_query_string,
206  const ExecutorDeviceType device_type) {
207  const auto results =
208  QR::get()->runSQL(query_string, device_type, g_hoist_literals, true);
209  const auto arrow_mapd_results = result_set_arrow_loopback(nullptr, results);
210  compare_impl(arrow_mapd_results.get(), sqlite_query_string, device_type, false, true);
211  }
212 
213  void compare(const std::string& query_string,
214  const std::string& sqlite_query_string,
215  const ExecutorDeviceType device_type) {
216  const auto mapd_results = run_multiple_agg(query_string, device_type);
217  compare_impl(mapd_results.get(), sqlite_query_string, device_type, false);
218  }
219 
220  // added to deal with time shift for now testing
221  void compare_timstamp_approx(const std::string& query_string,
222  const ExecutorDeviceType device_type) {
223  const auto mapd_results = run_multiple_agg(query_string, device_type);
224  compare_impl(mapd_results.get(), query_string, device_type, true);
225  }
226 
227  private:
228  template <class MapDResults>
229  void compare_impl(const MapDResults* mapd_results,
230  const std::string& sqlite_query_string,
231  const ExecutorDeviceType device_type,
232  const bool timestamp_approx,
233  const bool is_arrow = false) {
234  connector_.query(sqlite_query_string);
235  ASSERT_EQ(connector_.getNumRows(), mapd_results->rowCount());
236  const int num_rows{static_cast<int>(connector_.getNumRows())};
237  if (mapd_results->definitelyHasNoRows()) {
238  ASSERT_EQ(0, num_rows);
239  return;
240  }
241  if (!num_rows) {
242  return;
243  }
244  CHECK_EQ(connector_.getNumCols(), mapd_results->colCount());
245  const int num_cols{static_cast<int>(connector_.getNumCols())};
246  auto row_iterator = mapd_results->rowIterator(true, true);
247  for (int row_idx = 0; row_idx < num_rows; ++row_idx) {
248  const auto crt_row =
249  g_use_row_iterator ? *row_iterator++ : mapd_results->getNextRow(true, true);
250  CHECK(!crt_row.empty());
251  CHECK_EQ(static_cast<size_t>(num_cols), crt_row.size());
252  for (int col_idx = 0; col_idx < num_cols; ++col_idx) {
253  const auto ref_col_type = connector_.columnTypes[col_idx];
254  const auto mapd_variant = crt_row[col_idx];
255  const auto scalar_mapd_variant = boost::get<ScalarTargetValue>(&mapd_variant);
256  CHECK(scalar_mapd_variant);
257  auto mapd_ti = mapd_results->getColType(col_idx);
258  const auto mapd_type = mapd_ti.get_type();
259  checkTypeConsistency(ref_col_type, mapd_ti);
260  const bool ref_is_null = connector_.isNull(row_idx, col_idx);
261  switch (mapd_type) {
262  case kTINYINT:
263  case kSMALLINT:
264  case kINT:
265  case kBIGINT: {
266  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
267  ASSERT_NE(nullptr, mapd_as_int_p);
268  const auto mapd_val = *mapd_as_int_p;
269  if (ref_is_null) {
270  ASSERT_EQ(inline_int_null_val(mapd_ti), mapd_val);
271  } else {
272  const auto ref_val = connector_.getData<int64_t>(row_idx, col_idx);
273  ASSERT_EQ(ref_val, mapd_val);
274  }
275  break;
276  }
277  case kTEXT:
278  case kCHAR:
279  case kVARCHAR: {
280  const auto mapd_as_str_p = boost::get<NullableString>(scalar_mapd_variant);
281  ASSERT_NE(nullptr, mapd_as_str_p);
282  const auto mapd_str_notnull = boost::get<std::string>(mapd_as_str_p);
283  if (ref_is_null) {
284  // CHECK(!mapd_str_notnull); // JUST TO DEBUG SOMETHING TO BE UNCOMENTED
285  } else {
286  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
287  if (mapd_str_notnull) {
288  const auto mapd_val = *mapd_str_notnull;
289  ASSERT_EQ(ref_val, mapd_val);
290  } else {
291  // not null but no data, so val is empty string
292  const auto mapd_val = "";
293  ASSERT_EQ(ref_val, mapd_val);
294  }
295  }
296  break;
297  }
298  case kNUMERIC:
299  case kDECIMAL:
300  case kDOUBLE: {
301  const auto mapd_as_double_p = boost::get<double>(scalar_mapd_variant);
302  ASSERT_NE(nullptr, mapd_as_double_p);
303  const auto mapd_val = *mapd_as_double_p;
304  if (ref_is_null) {
305  ASSERT_EQ(inline_fp_null_val(SQLTypeInfo(kDOUBLE, false)), mapd_val);
306  } else {
307  const auto ref_val = connector_.getData<double>(row_idx, col_idx);
308  ASSERT_TRUE(approx_eq(ref_val, mapd_val));
309  }
310  break;
311  }
312  case kFLOAT: {
313  const auto mapd_as_float_p = boost::get<float>(scalar_mapd_variant);
314  ASSERT_NE(nullptr, mapd_as_float_p);
315  const auto mapd_val = *mapd_as_float_p;
316  if (ref_is_null) {
317  ASSERT_EQ(inline_fp_null_val(SQLTypeInfo(kFLOAT, false)), mapd_val);
318  } else {
319  const auto ref_val = connector_.getData<float>(row_idx, col_idx);
320  ASSERT_TRUE(approx_eq(ref_val, mapd_val));
321  }
322  break;
323  }
324  case kTIMESTAMP:
325  case kDATE: {
326  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
327  CHECK(mapd_as_int_p);
328  const auto mapd_val = *mapd_as_int_p;
329  time_t nsec = 0;
330  const int dimen = mapd_ti.get_dimension();
331  if (ref_is_null) {
332  CHECK_EQ(inline_int_null_val(mapd_ti), mapd_val);
333  } else {
334  struct tm tm_struct {
335  0
336  };
337  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
338  auto end_str =
339  strptime(ref_val.c_str(),
340  mapd_type == kTIMESTAMP ? "%Y-%m-%d %H:%M:%S" : "%Y-%m-%d",
341  &tm_struct);
342  // handle fractional seconds
343  if (end_str != nullptr && *end_str != '.') {
344  if (end_str) {
345  ASSERT_EQ(0, *end_str);
346  }
347  ASSERT_EQ(ref_val.size(), static_cast<size_t>(end_str - ref_val.c_str()));
348  }
349  if (dimen > 0 && mapd_type == kTIMESTAMP) {
350  int fs = 0;
351  if (*end_str == '.') {
352  end_str++;
353  uint frac_num;
354  int ntotal;
355  sscanf(end_str, "%d%n", &frac_num, &ntotal);
356  fs = parse_fractional_seconds(frac_num, ntotal, mapd_ti);
357  nsec = timegm(&tm_struct) * pow(10, dimen);
358  nsec += fs;
359  } else if (*end_str == '\0') {
360  nsec = timegm(&tm_struct) * pow(10, dimen);
361  } else {
362  CHECK(false);
363  }
364  }
365  if (timestamp_approx) {
366  // approximate result give 10 second lee way
367  ASSERT_NEAR(*mapd_as_int_p,
368  dimen > 0 ? nsec : timegm(&tm_struct),
369  dimen > 0 ? 10 * pow(10, dimen) : 10);
370  } else {
371  if (is_arrow && mapd_type == kDATE) {
372  ASSERT_EQ(*mapd_as_int_p, timegm(&tm_struct) * kMilliSecsPerSec);
373  } else {
374  ASSERT_EQ(*mapd_as_int_p, dimen > 0 ? nsec : timegm(&tm_struct));
375  }
376  }
377  }
378  break;
379  }
380  case kBOOLEAN: {
381  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
382  CHECK(mapd_as_int_p);
383  const auto mapd_val = *mapd_as_int_p;
384  if (ref_is_null) {
385  CHECK_EQ(inline_int_null_val(mapd_ti), mapd_val);
386  } else {
387  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
388  if (ref_val == "t") {
389  ASSERT_EQ(1, *mapd_as_int_p);
390  } else {
391  CHECK_EQ("f", ref_val);
392  ASSERT_EQ(0, *mapd_as_int_p);
393  }
394  }
395  break;
396  }
397  case kTIME: {
398  const auto mapd_as_int_p = boost::get<int64_t>(scalar_mapd_variant);
399  CHECK(mapd_as_int_p);
400  const auto mapd_val = *mapd_as_int_p;
401  if (ref_is_null) {
402  CHECK_EQ(inline_int_null_val(mapd_ti), mapd_val);
403  } else {
404  const auto ref_val = connector_.getData<std::string>(row_idx, col_idx);
405  std::vector<std::string> time_tokens;
406  boost::split(time_tokens, ref_val, boost::is_any_of(":"));
407  ASSERT_EQ(size_t(3), time_tokens.size());
408  ASSERT_EQ(boost::lexical_cast<int64_t>(time_tokens[0]) * 3600 +
409  boost::lexical_cast<int64_t>(time_tokens[1]) * 60 +
410  boost::lexical_cast<int64_t>(time_tokens[2]),
411  *mapd_as_int_p);
412  }
413  break;
414  }
415  default:
416  CHECK(false);
417  }
418  }
419  }
420  }
421 
422  private:
423  static void checkTypeConsistency(const int ref_col_type, const SQLTypeInfo& mapd_ti) {
424  if (ref_col_type == SQLITE_NULL) {
425  // TODO(alex): re-enable the check that mapd_ti is nullable,
426  // got invalidated because of outer joins
427  return;
428  }
429  if (mapd_ti.is_integer()) {
430  CHECK_EQ(SQLITE_INTEGER, ref_col_type);
431  } else if (mapd_ti.is_fp() || mapd_ti.is_decimal()) {
432  CHECK(ref_col_type == SQLITE_FLOAT || ref_col_type == SQLITE_INTEGER);
433  } else {
434  CHECK_EQ(SQLITE_TEXT, ref_col_type);
435  }
436  }
437 
439 };
440 
441 const ssize_t g_num_rows{10};
443 
444 void c(const std::string& query_string, const ExecutorDeviceType device_type) {
445  g_sqlite_comparator.compare(query_string, device_type);
446 }
447 
448 void c(const std::string& query_string,
449  const std::string& sqlite_query_string,
450  const ExecutorDeviceType device_type) {
451  g_sqlite_comparator.compare(query_string, sqlite_query_string, device_type);
452 }
453 
454 /* timestamp approximate checking for NOW() */
455 void cta(const std::string& query_string, const ExecutorDeviceType device_type) {
456  g_sqlite_comparator.compare_timstamp_approx(query_string, device_type);
457 }
458 
459 void c_arrow(const std::string& query_string, const ExecutorDeviceType device_type) {
460  g_sqlite_comparator.compare_arrow_output(query_string, query_string, device_type);
461 }
462 
463 } // namespace
464 
465 #define SKIP_NO_GPU() \
466  if (skip_tests(dt)) { \
467  CHECK(dt == ExecutorDeviceType::GPU); \
468  LOG(WARNING) << "GPU not available, skipping GPU tests"; \
469  continue; \
470  }
471 
472 #define SKIP_ALL_ON_AGGREGATOR() \
473  if (g_aggregator) { \
474  LOG(ERROR) << "Tests not valid in distributed mode"; \
475  return; \
476  }
477 
478 #define SKIP_ON_AGGREGATOR(EXP) \
479  if (!g_aggregator) { \
480  EXP; \
481  }
482 
483 bool validate_statement_syntax(const std::string& stmt) {
484  SQLParser parser;
485  list<std::unique_ptr<Parser::Stmt>> parse_trees;
486  std::string last_parsed;
487  return parser.parse(stmt, parse_trees, last_parsed) == 0;
488 }
489 
490 namespace {
491 
493  const std::pair<std::string, bool> type_meta,
494  const std::pair<std::string, std::vector<std::string>> values) {
495  auto validate = [&type_meta](const std::string& add_column, const std::string& val) {
496  ASSERT_NO_THROW(run_ddl_statement("DROP TABLE IF EXISTS chelsea_storage;"));
497  const std::string query =
498  "CREATE TABLE chelsea_storage(id TEXT ENCODING DICT(32), val INT " + add_column +
499  ") WITH (" + type_meta.first + "=" + val + ");";
500  ASSERT_EQ(true, validate_statement_syntax(query));
501  if (type_meta.second) {
502  ASSERT_THROW(run_ddl_statement(query), std::runtime_error);
503  } else {
504  ASSERT_NO_THROW(run_ddl_statement(query));
505  }
506  };
507  for (const auto& val : values.second) {
508  validate(values.first, val);
509  }
510 }
511 
512 } // namespace
513 
514 TEST(Create, StorageOptions) {
516  SKIP_NO_GPU();
517  const auto shard_count = choose_shard_count();
518  static const std::map<std::pair<std::string, bool>,
519  std::pair<std::string, std::vector<std::string>>>
520  params{
521  {{"fragment_size"s, true}, {"", {"-1", "0"}}},
522  {{"fragment_size"s, false},
523  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
524  {{"max_rows"s, true}, {"", {"-1", "0"}}},
525  {{"max_rows"s, false},
526  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
527  {{"page_size"s, true}, {"", {"-1", "0"}}},
528  {{"page_size"s, false},
529  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
530  {{"max_chunk_size"s, true}, {"", {"-1", "0"}}},
531  {{"max_chunk_size"s, false},
532  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
533  {{"partitions"s, true}, {"", {"'No'", "'null'", "'-1'"}}},
534  {{"partitions"s, false}, {"", {"'SHARDED'", "'REPLICATED'"}}},
535  {{"shard_count"s, true}, {"", {std::to_string(shard_count)}}},
536  {{"shard_count"s, false}, {", SHARD KEY(id)", {std::to_string(shard_count)}}},
537  {{"vacuum"s, true}, {"", {"'-1'", "'0'", "'null'"}}},
538  {{"vacuum"s, false}, {"", {"'IMMEDIATE'", "'delayed'"}}},
539  {{"sort_column"s, true}, {"", {"'arsenal'", "'barca'", "'city'"}}},
540  {{"sort_column"s, false}, {"", {"'id'", "'val'"}}}};
541 
542  for (auto& elem : params) {
543  validate_storage_options(elem.first, elem.second);
544  }
545  }
546 }
547 
548 TEST(Insert, ShardedTableWithGeo) {
550  SKIP_NO_GPU();
551  run_ddl_statement("DROP TABLE IF EXISTS table_with_geo_and_shard_key;");
552  EXPECT_NO_THROW(
553  run_ddl_statement("CREATE TABLE table_with_geo_and_shard_key (x Int, poly "
554  "POLYGON, b SMALLINT, SHARD KEY(b)) WITH (shard_count = 4);"));
555 
556  EXPECT_NO_THROW(
557  run_multiple_agg("INSERT INTO table_with_geo_and_shard_key VALUES (1, "
558  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 0);",
559  dt));
560  EXPECT_NO_THROW(run_multiple_agg(
561  "INSERT INTO table_with_geo_and_shard_key (x, poly, b) VALUES (1, "
562  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1);",
563  dt));
564  EXPECT_NO_THROW(run_multiple_agg(
565  "INSERT INTO table_with_geo_and_shard_key (b, poly, x) VALUES (2, "
566  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1);",
567  dt));
568  EXPECT_NO_THROW(run_multiple_agg(
569  "INSERT INTO table_with_geo_and_shard_key (x, b, poly) VALUES (1, 3, "
570  "'POLYGON((0 0, 1 1, 2 2, 3 3))');",
571  dt));
572  EXPECT_NO_THROW(
573  run_multiple_agg("INSERT INTO table_with_geo_and_shard_key (poly, x, b) VALUES ("
574  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1, 4);",
575  dt));
576 
577  ASSERT_EQ(5,
578  v<int64_t>(run_simple_agg(
579  "SELECT count(*) FROM table_with_geo_and_shard_key;", dt)));
580  }
581 }
582 
583 TEST(Insert, NullArrayNullEmpty) {
584  const char* create_table_array_with_nulls =
585  R"(create table table_array_with_nulls (i smallint, sia smallint[], fa2 float[2]);)";
587  SKIP_NO_GPU();
588  run_ddl_statement("DROP TABLE IF EXISTS table_array_empty;");
589  EXPECT_NO_THROW(run_ddl_statement("create table table_array_empty (val int[]);"));
590  EXPECT_NO_THROW(run_multiple_agg("INSERT INTO table_array_empty VALUES({});", dt));
591  EXPECT_NO_THROW(run_simple_agg("SELECT * from table_array_empty;", dt));
592  ASSERT_EQ(0,
593  v<int64_t>(run_simple_agg(
594  "SELECT CARDINALITY(val) from table_array_empty limit 1;", dt)));
595 
596  run_ddl_statement("DROP TABLE IF EXISTS table_array_fixlen_text;");
597  EXPECT_NO_THROW(
598  run_ddl_statement("create table table_array_fixlen_text (strings text[2]);"));
599  EXPECT_THROW(
600  run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES(NULL);", dt),
601  std::runtime_error);
602  EXPECT_THROW(run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES({});", dt),
603  std::runtime_error);
604  EXPECT_NO_THROW(
605  run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES({NULL,NULL});", dt));
606  EXPECT_NO_THROW(
607  run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES({'a','b'});", dt));
608  ASSERT_EQ(
609  1,
610  v<int64_t>(run_simple_agg(
611  "SELECT count(*) FROM table_array_fixlen_text WHERE strings[1] IS NOT NULL;",
612  dt)));
613  ASSERT_EQ(
614  1,
615  v<int64_t>(run_simple_agg(
616  "SELECT count(*) FROM table_array_fixlen_text WHERE strings[2] IS NULL;",
617  dt)));
618 
619  run_ddl_statement("DROP TABLE IF EXISTS table_array_with_nulls;");
620  EXPECT_NO_THROW(run_ddl_statement(create_table_array_with_nulls));
621  EXPECT_NO_THROW(
622  run_multiple_agg("INSERT INTO table_array_with_nulls "
623  "VALUES(1, {1,1}, ARRAY[1.0,1.0]);",
624  dt));
625  EXPECT_NO_THROW(
626  run_multiple_agg("INSERT INTO table_array_with_nulls "
627  "VALUES(2, {NULL,2}, {NULL,2.0});",
628  dt));
629  EXPECT_NO_THROW(
630  run_multiple_agg("INSERT INTO table_array_with_nulls "
631  "VALUES(3, {3,NULL}, {3.0, NULL});",
632  dt));
633  EXPECT_NO_THROW(
634  run_multiple_agg("INSERT INTO table_array_with_nulls "
635  "VALUES(4, {NULL,NULL}, {NULL,NULL});",
636  dt));
637  EXPECT_NO_THROW(
638  run_multiple_agg("INSERT INTO table_array_with_nulls "
639  "VALUES(5, NULL, NULL);",
640  dt));
641  EXPECT_NO_THROW(
642  run_multiple_agg("INSERT INTO table_array_with_nulls "
643  "VALUES(6, {}, NULL);",
644  dt));
645  EXPECT_NO_THROW(
646  run_multiple_agg("INSERT INTO table_array_with_nulls "
647  "VALUES(7, {NULL,NULL}, {NULL,NULL});",
648  dt));
649 
650  ASSERT_EQ(1,
651  v<int64_t>(
652  run_simple_agg("SELECT MIN(sia[1]) FROM table_array_with_nulls;", dt)));
653  ASSERT_EQ(3,
654  v<int64_t>(
655  run_simple_agg("SELECT MAX(sia[1]) FROM table_array_with_nulls;", dt)));
656  ASSERT_EQ(
657  5,
658  v<int64_t>(run_simple_agg(
659  "SELECT count(*) FROM table_array_with_nulls WHERE sia[2] IS NULL;", dt)));
660  ASSERT_EQ(
661  3.0,
662  v<float>(run_simple_agg("SELECT MAX(fa2[1]) FROM table_array_with_nulls;", dt)));
663  ASSERT_EQ(
664  2.0,
665  v<float>(run_simple_agg("SELECT MAX(fa2[2]) FROM table_array_with_nulls;", dt)));
666  ASSERT_EQ(2,
667  v<int64_t>(run_simple_agg(
668  "SELECT count(*) FROM table_array_with_nulls WHERE fa2[1] IS NOT NULL;",
669  dt)));
670  ASSERT_EQ(1,
671  v<int64_t>(run_simple_agg(
672  "SELECT count(*) FROM table_array_with_nulls WHERE sia IS NULL;", dt)));
673  ASSERT_EQ(
674  5,
675  v<int64_t>(run_simple_agg(
676  "SELECT count(*) FROM table_array_with_nulls WHERE fa2 IS NOT NULL;", dt)));
677  ASSERT_EQ(1,
678  v<int64_t>(run_simple_agg(
679  "SELECT count(*) FROM table_array_with_nulls WHERE CARDINALITY(sia)=0;",
680  dt)));
681  ASSERT_EQ(5,
682  v<int64_t>(run_simple_agg(
683  "SELECT count(*) FROM table_array_with_nulls WHERE CARDINALITY(sia)=2;",
684  dt)));
685  ASSERT_EQ(
686  1,
687  v<int64_t>(run_simple_agg(
688  "SELECT count(*) FROM table_array_with_nulls WHERE CARDINALITY(sia) IS NULL;",
689  dt)));
690 
691  // Simple lazy projection
693  run_simple_agg("SELECT sia FROM table_array_with_nulls WHERE i = 5;", dt),
694  std::vector<int64_t>({}));
695 
696  // Simple non-lazy projection
698  run_simple_agg("SELECT sia FROM table_array_with_nulls WHERE sia IS NULL;", dt),
699  std::vector<int64_t>({}));
700  }
701 }
702 
703 TEST(Insert, DictBoundary) {
705  SKIP_NO_GPU();
706  run_ddl_statement("DROP TABLE IF EXISTS table_with_small_dict;");
707  EXPECT_NO_THROW(run_ddl_statement(
708  "CREATE TABLE table_with_small_dict (i INT, t TEXT ENCODING DICT(8));"));
709 
710  for (int cVal = 0; cVal < 280; cVal++) {
711  string insString = "INSERT INTO table_with_small_dict VALUES (" +
712  std::to_string(cVal) + ", '" + std::to_string(cVal) + "');";
713  EXPECT_NO_THROW(run_multiple_agg(insString, dt));
714  }
715 
716  ASSERT_EQ(
717  280,
718  v<int64_t>(run_simple_agg("SELECT count(*) FROM table_with_small_dict;", dt)));
719  ASSERT_EQ(255,
720  v<int64_t>(run_simple_agg(
721  "SELECT count(distinct t) FROM table_with_small_dict;", dt)));
722  ASSERT_EQ(25,
723  v<int64_t>(run_simple_agg(
724  "SELECT count(*) FROM table_with_small_dict WHERE t IS NULL;", dt)));
725  }
726 }
727 
728 TEST(KeyForString, KeyForString) {
730  SKIP_NO_GPU();
731  run_ddl_statement("drop table if exists kfs;");
732  EXPECT_NO_THROW(run_ddl_statement(
733  "create table kfs(ts text encoding dict(8), ss text encoding "
734  "dict(16), ws text encoding dict, ns text not null encoding dict, sa text[]);"));
735  EXPECT_NO_THROW(
736  run_multiple_agg("insert into kfs values('0', '0', '0', '0', {'0','0'});",
738  EXPECT_NO_THROW(
739  run_multiple_agg("insert into kfs values('1', '1', '1', '1', {'1','1'});",
741  EXPECT_NO_THROW(
742  run_multiple_agg("insert into kfs values(null, null, null, '2', {'2','2'});",
744  ASSERT_EQ(3, v<int64_t>(run_simple_agg("select count(*) from kfs;", dt)));
745  ASSERT_EQ(2,
746  v<int64_t>(run_simple_agg(
747  "select count(*) from kfs where key_for_string(ts) is not null;", dt)));
748  ASSERT_EQ(2,
749  v<int64_t>(run_simple_agg(
750  "select count(*) from kfs where key_for_string(ss) is not null;", dt)));
751  ASSERT_EQ(2,
752  v<int64_t>(run_simple_agg(
753  "select count(*) from kfs where key_for_string(ws) is not null;", dt)));
754  ASSERT_EQ(3,
755  v<int64_t>(run_simple_agg(
756  "select count(*) from kfs where key_for_string(ns) is not null;", dt)));
757  ASSERT_EQ(
758  3,
759  v<int64_t>(run_simple_agg(
760  "select count(*) from kfs where key_for_string(sa[1]) is not null;", dt)));
761  ASSERT_EQ(
762  2,
763  v<int64_t>(run_simple_agg(
764  "select count(*) from kfs where key_for_string(ts) = key_for_string(ss);",
765  dt)));
766  ASSERT_EQ(
767  2,
768  v<int64_t>(run_simple_agg(
769  "select count(*) from kfs where key_for_string(ss) = key_for_string(ws);",
770  dt)));
771  ASSERT_EQ(
772  2,
773  v<int64_t>(run_simple_agg(
774  "select count(*) from kfs where key_for_string(ws) = key_for_string(ts);",
775  dt)));
776  ASSERT_EQ(
777  2,
778  v<int64_t>(run_simple_agg(
779  "select count(*) from kfs where key_for_string(ws) = key_for_string(ns);",
780  dt)));
781  ASSERT_EQ(
782  2,
783  v<int64_t>(run_simple_agg(
784  "select count(*) from kfs where key_for_string(ws) = key_for_string(sa[1]);",
785  dt)));
786  ASSERT_EQ(0,
787  v<int64_t>(run_simple_agg("select min(key_for_string(ts)) from kfs;", dt)));
788  ASSERT_EQ(0,
789  v<int64_t>(run_simple_agg("select min(key_for_string(ss)) from kfs;", dt)));
790  ASSERT_EQ(0,
791  v<int64_t>(run_simple_agg("select min(key_for_string(ws)) from kfs;", dt)));
792  ASSERT_EQ(0,
793  v<int64_t>(run_simple_agg("select min(key_for_string(ns)) from kfs;", dt)));
794  ASSERT_EQ(
795  0, v<int64_t>(run_simple_agg("select min(key_for_string(sa[1])) from kfs;", dt)));
796  ASSERT_EQ(
797  0, v<int64_t>(run_simple_agg("select min(key_for_string(sa[2])) from kfs;", dt)));
798  ASSERT_EQ(1,
799  v<int64_t>(run_simple_agg("select max(key_for_string(ts)) from kfs;", dt)));
800  ASSERT_EQ(1,
801  v<int64_t>(run_simple_agg("select max(key_for_string(ss)) from kfs;", dt)));
802  ASSERT_EQ(1,
803  v<int64_t>(run_simple_agg("select max(key_for_string(ws)) from kfs;", dt)));
804  ASSERT_EQ(2,
805  v<int64_t>(run_simple_agg("select max(key_for_string(ns)) from kfs;", dt)));
806  ASSERT_EQ(
807  2, v<int64_t>(run_simple_agg("select max(key_for_string(sa[1])) from kfs;", dt)));
808  ASSERT_EQ(
809  2, v<int64_t>(run_simple_agg("select max(key_for_string(sa[2])) from kfs;", dt)));
810  ASSERT_EQ(
811  2, v<int64_t>(run_simple_agg("select count(key_for_string(ts)) from kfs;", dt)));
812  ASSERT_EQ(
813  2, v<int64_t>(run_simple_agg("select count(key_for_string(ss)) from kfs;", dt)));
814  ASSERT_EQ(
815  2, v<int64_t>(run_simple_agg("select count(key_for_string(ws)) from kfs;", dt)));
816  ASSERT_EQ(
817  3, v<int64_t>(run_simple_agg("select count(key_for_string(ns)) from kfs;", dt)));
818  ASSERT_EQ(
819  3,
820  v<int64_t>(run_simple_agg("select count(key_for_string(sa[1])) from kfs;", dt)));
821  ASSERT_EQ(
822  3,
823  v<int64_t>(run_simple_agg("select count(key_for_string(sa[2])) from kfs;", dt)));
824  }
825 }
826 
827 TEST(Select, IsTrue) {
829  SKIP_NO_GPU();
830 
831  run_ddl_statement("DROP TABLE IF EXISTS table_bool_test;");
832  g_sqlite_comparator.query("DROP TABLE IF EXISTS table_bool_test;");
833 
834  run_ddl_statement("CREATE TABLE table_bool_test (id INT, val BOOLEAN);");
835  g_sqlite_comparator.query("CREATE TABLE table_bool_test (id INT, val BOOLEAN);");
836 
837  run_multiple_agg("INSERT INTO table_bool_test VALUES(1, NULL);", dt);
838  g_sqlite_comparator.query("INSERT INTO table_bool_test VALUES(1, NULL);");
839  run_multiple_agg("INSERT INTO table_bool_test VALUES(2, 'true');", dt);
840  g_sqlite_comparator.query("INSERT INTO table_bool_test VALUES(2, 'true');");
841  run_multiple_agg("INSERT INTO table_bool_test VALUES(3, 'false');", dt);
842  g_sqlite_comparator.query("INSERT INTO table_bool_test VALUES(3, 'false');");
843 
844  // the case statement is due to return type mismatch with sqllite
845  c("select id, (case when (val is true) then 't' else 'f' end) from "
846  "table_bool_test "
847  "order by id;",
848  "select id, (case when (val is 'true') then 't' else 'f' end) from "
849  "table_bool_test "
850  "order by id;",
851  dt);
852  c("select id, (case when (val is not true) then 't' else 'f' end) from "
853  "table_bool_test order by id;",
854  "select id, (case when (val is not 'true') then 't' else 'f' end) from "
855  "table_bool_test order by id;",
856  dt);
857  c("select id, (case when (val is false) then 't' else 'f' end) from "
858  "table_bool_test "
859  "order by id;",
860  "select id, (case when (val is 'false') then 't' else 'f' end) from "
861  "table_bool_test "
862  "order by id;",
863  dt);
864  c("select id, (case when (val is not false) then 't' else 'f' end) from "
865  "table_bool_test order by id;",
866  "select id, (case when (val is not 'false') then 't' else 'f' end) from "
867  "table_bool_test order by id;",
868  dt);
869  }
870 }
871 
872 TEST(Select, NullGroupBy) {
874  SKIP_NO_GPU();
875  run_ddl_statement("DROP TABLE IF EXISTS table_null_group_by;");
876  run_ddl_statement("CREATE TABLE table_null_group_by (val TEXT);");
877  run_multiple_agg("INSERT INTO table_null_group_by VALUES( NULL );", dt);
878  run_simple_agg("SELECT val FROM table_null_group_by GROUP BY val;", dt);
879 
880  run_ddl_statement("DROP TABLE IF EXISTS table_null_group_by;");
881  run_ddl_statement("CREATE TABLE table_null_group_by (val DOUBLE);");
882  run_multiple_agg("INSERT INTO table_null_group_by VALUES( NULL );", dt);
883  run_simple_agg("SELECT val FROM table_null_group_by GROUP BY val;", dt);
884  }
885 }
886 
887 TEST(Select, FilterAndSimpleAggregation) {
889  SKIP_NO_GPU();
890  c("SELECT COUNT(*) FROM test;", dt);
891  c("SELECT COUNT(f) FROM test;", dt);
892  c("SELECT MIN(x) FROM test;", dt);
893  c("SELECT MAX(x) FROM test;", dt);
894  c("SELECT MIN(z) FROM test;", dt);
895  c("SELECT MAX(z) FROM test;", dt);
896  c("SELECT MIN(t) FROM test;", dt);
897  c("SELECT MAX(t) FROM test;", dt);
898  c("SELECT MIN(ff) FROM test;", dt);
899  c("SELECT MIN(fn) FROM test;", dt);
900  c("SELECT SUM(ff) FROM test;", dt);
901  c("SELECT SUM(fn) FROM test;", dt);
902  c("SELECT SUM(x + y) FROM test;", dt);
903  c("SELECT SUM(x + y + z) FROM test;", dt);
904  c("SELECT SUM(x + y + z + t) FROM test;", dt);
905  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8;", dt);
906  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102;", dt);
907  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 OR (z > 100 AND z < 103);", dt);
908  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
909  "1000 AND t < 1002;",
910  dt);
911  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 OR (z > 100 AND z < 103);", dt);
912  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 OR (z > 100 AND z < 102) OR (t > "
913  "1000 AND t < 1003);",
914  dt);
915  c("SELECT COUNT(*) FROM test WHERE x <> 7;", dt);
916  c("SELECT COUNT(*) FROM test WHERE z <> 102;", dt);
917  c("SELECT COUNT(*) FROM test WHERE t <> 1002;", dt);
918  c("SELECT COUNT(*) FROM test WHERE x + y = 49;", dt);
919  c("SELECT COUNT(*) FROM test WHERE x + y + z = 150;", dt);
920  c("SELECT COUNT(*) FROM test WHERE x + y + z + t = 1151;", dt);
921  c("SELECT SUM(x + y) FROM test WHERE x + y = 49;", dt);
922  c("SELECT SUM(x + y + z) FROM test WHERE x + y = 49;", dt);
923  c("SELECT SUM(x + y + z + t) FROM test WHERE x + y = 49;", dt);
924  c("SELECT COUNT(*) FROM test WHERE x - y = -35;", dt);
925  c("SELECT COUNT(*) FROM test WHERE x - y + z = 66;", dt);
926  c("SELECT COUNT(*) FROM test WHERE x - y + z + t = 1067;", dt);
927  c("SELECT COUNT(*) FROM test WHERE y - x = 35;", dt);
928  c("SELECT 'Hello', 'World', 7 FROM test WHERE x <> 7;", dt);
929  c("SELECT 'Total', COUNT(*) FROM test WHERE x <> 7;", dt);
930  c("SELECT SUM(2 * x) FROM test WHERE x = 7;", dt);
931  c("SELECT SUM(2 * x + z) FROM test WHERE x = 7;", dt);
932  c("SELECT SUM(x + y) FROM test WHERE x - y = -35;", dt);
933  c("SELECT SUM(x + y) FROM test WHERE y - x = 35;", dt);
934  c("SELECT SUM(x + y - z) FROM test WHERE y - x = 35;", dt);
935  c("SELECT SUM(x * y + 15) FROM test WHERE x + y + 1 = 50;", dt);
936  c("SELECT SUM(x * y + 15) FROM test WHERE x + y + z + 1 = 151;", dt);
937  c("SELECT SUM(x * y + 15) FROM test WHERE x + y + z + t + 1 = 1152;", dt);
938  c("SELECT SUM(z) FROM test WHERE z IS NOT NULL;", dt);
939  c("SELECT MIN(x * y + 15) FROM test WHERE x + y + 1 = 50;", dt);
940  c("SELECT MIN(x * y + 15) FROM test WHERE x + y + z + 1 = 151;", dt);
941  c("SELECT MIN(x * y + 15) FROM test WHERE x + y + z + t + 1 = 1152;", dt);
942  c("SELECT MAX(x * y + 15) FROM test WHERE x + y + 1 = 50;", dt);
943  c("SELECT MAX(x * y + 15) FROM test WHERE x + y + z + 1 = 151;", dt);
944  c("SELECT MAX(x * y + 15) FROM test WHERE x + y + z + t + 1 = 1152;", dt);
945  c("SELECT MIN(x) FROM test WHERE x = 7;", dt);
946  c("SELECT MIN(z) FROM test WHERE z = 101;", dt);
947  c("SELECT MIN(t) FROM test WHERE t = 1001;", dt);
948  c("SELECT AVG(x + y) FROM test;", dt);
949  c("SELECT AVG(x + y + z) FROM test;", dt);
950  c("SELECT AVG(x + y + z + t) FROM test;", dt);
951  c("SELECT AVG(y) FROM test WHERE x > 6 AND x < 8;", dt);
952  c("SELECT AVG(y) FROM test WHERE z > 100 AND z < 102;", dt);
953  c("SELECT AVG(y) FROM test WHERE t > 1000 AND t < 1002;", dt);
954  c("SELECT MIN(dd) FROM test;", dt);
955  c("SELECT MAX(dd) FROM test;", dt);
956  c("SELECT SUM(dd) FROM test;", dt);
957  c("SELECT AVG(dd) FROM test;", dt);
958  c("SELECT AVG(dd) FROM test WHERE x > 6 AND x < 8;", dt);
959  c("SELECT COUNT(*) FROM test WHERE dd > 100;", dt);
960  c("SELECT COUNT(*) FROM test WHERE dd > 200;", dt);
961  c("SELECT COUNT(*) FROM test WHERE dd > 300;", dt);
962  c("SELECT COUNT(*) FROM test WHERE dd > 111.0;", dt);
963  c("SELECT COUNT(*) FROM test WHERE dd > 111.1;", dt);
964  c("SELECT COUNT(*) FROM test WHERE dd > 222.2;", dt);
965  c("SELECT MAX(x + dd) FROM test;", dt);
966  c("SELECT MAX(x + 2 * dd), MIN(x + 2 * dd) FROM test;", dt);
967  c("SELECT COUNT(*) FROM test WHERE dd > CAST(111.0 AS decimal(10, 2));", dt);
968  c("SELECT COUNT(*) FROM test WHERE dd > CAST(222.0 AS decimal(10, 2));", dt);
969  c("SELECT COUNT(*) FROM test WHERE dd > CAST(333.0 AS decimal(10, 2));", dt);
970  c("SELECT MIN(dd * dd) FROM test;", dt);
971  c("SELECT MAX(dd * dd) FROM test;", dt);
972  c("SELECT COUNT(*) FROM test WHERE u IS NOT NULL;", dt);
973  c("SELECT AVG(u * f) FROM test;", dt);
974  c("SELECT AVG(u * d) FROM test;", dt);
975  c("SELECT SUM(-y) FROM test;", dt);
976  c("SELECT SUM(-z) FROM test;", dt);
977  c("SELECT SUM(-t) FROM test;", dt);
978  c("SELECT SUM(-dd) FROM test;", dt);
979  c("SELECT SUM(-f) FROM test;", dt);
980  c("SELECT SUM(-d) FROM test;", dt);
981  c("SELECT SUM(dd * 0.99) FROM test;", dt);
982  c("SELECT COUNT(*) FROM test WHERE 1<>2;", dt);
983  c("SELECT COUNT(*) FROM test WHERE 1=1;", dt);
984  c("SELECT COUNT(*) FROM test WHERE 22 > 33;", dt);
985  c("SELECT COUNT(*) FROM test WHERE ff < 23.0/4.0 AND 22 < 33;", dt);
986  c("SELECT COUNT(*) FROM test WHERE x + 3*8/2 < 35 + y - 20/5;", dt);
987  c("SELECT x + 2 * 10/4 + 3 AS expr FROM test WHERE x + 3*8/2 < 35 + y - 20/5 ORDER "
988  "BY expr ASC;",
989  dt);
990  c("SELECT COUNT(*) FROM test WHERE ff + 3.0*8 < 20.0/5;", dt);
991  c("SELECT COUNT(*) FROM test WHERE x < y AND 0=1;", dt);
992  c("SELECT COUNT(*) FROM test WHERE x < y AND 1=1;", dt);
993  c("SELECT COUNT(*) FROM test WHERE x < y OR 1<1;", dt);
994  c("SELECT COUNT(*) FROM test WHERE x < y OR 1=1;", dt);
995  c("SELECT COUNT(*) FROM test WHERE x < 35 AND x < y AND 1=1 AND 0=1;", dt);
996  c("SELECT COUNT(*) FROM test WHERE 1>2 AND x < 35 AND x < y AND y < 10;", dt);
998  c("SELECT COUNT(*) FROM test WHERE x < y GROUP BY x HAVING 0=1;", dt));
999  c("SELECT COUNT(*) FROM test WHERE x < y GROUP BY x HAVING 1=1;", dt);
1000  c("SELECT COUNT(*) FROM test WHERE ofq >= 0 OR ofq IS NULL;", dt);
1001  c("SELECT COUNT(*) AS val FROM test WHERE (test.dd = 0.5 OR test.dd = 3);", dt);
1002  c("SELECT MAX(dd_notnull * 1) FROM test;", dt);
1003  c("SELECT x, COUNT(*) AS n FROM test GROUP BY x, ufd ORDER BY x, n;", dt);
1004  c("SELECT MIN(x), MAX(x) FROM test WHERE real_str LIKE '%nope%';", dt);
1005  c("SELECT COUNT(*) FROM test WHERE (x > 7 AND y / (x - 7) < 44);", dt);
1006  c("SELECT x, AVG(ff) AS val FROM test GROUP BY x ORDER BY val;", dt);
1007  c("SELECT x, MAX(fn) as val FROM test WHERE fn IS NOT NULL GROUP BY x ORDER BY val;",
1008  dt);
1009  c("SELECT MAX(dn) FROM test WHERE dn IS NOT NULL;", dt);
1010  c("SELECT x, MAX(dn) as val FROM test WHERE dn IS NOT NULL GROUP BY x ORDER BY val;",
1011  dt);
1012  c("SELECT COUNT(*) as val FROM test GROUP BY x, y, ufd ORDER BY val;", dt);
1013  ASSERT_NEAR(
1014  static_cast<double>(-1000.3),
1015  v<double>(run_simple_agg(
1016  "SELECT AVG(fn) AS val FROM test GROUP BY rowid ORDER BY val LIMIT 1;", dt)),
1017  static_cast<double>(0.2));
1018  c("SELECT COUNT(*) FROM test WHERE d = 2.2", dt);
1019  c("SELECT COUNT(*) FROM test WHERE fx + 1 IS NULL;", dt);
1020  c("SELECT COUNT(ss) FROM test;", dt);
1021  c("SELECT COUNT(*) FROM test WHERE null IS NULL;", dt);
1022  c("SELECT COUNT(*) FROM test WHERE null_str IS NULL;", dt);
1023  c("SELECT COUNT(*) FROM test WHERE null IS NOT NULL;", dt);
1024  c("SELECT COUNT(*) FROM test WHERE o1 > '1999-09-08';", dt);
1025  c("SELECT COUNT(*) FROM test WHERE o1 <= '1999-09-08';", dt);
1026  c("SELECT COUNT(*) FROM test WHERE o1 = '1999-09-08';", dt);
1027  c("SELECT COUNT(*) FROM test WHERE o1 <> '1999-09-08';", dt);
1028  c("SELECT COUNT(*) FROM test WHERE o >= CAST('1999-09-09' AS DATE);", dt);
1029  c("SELECT COUNT(*) FROM test WHERE o2 > '1999-09-08';", dt);
1030  c("SELECT COUNT(*) FROM test WHERE o2 <= '1999-09-08';", dt);
1031  c("SELECT COUNT(*) FROM test WHERE o2 = '1999-09-08';", dt);
1032  c("SELECT COUNT(*) FROM test WHERE o2 <> '1999-09-08';", dt);
1033  c("SELECT COUNT(*) FROM test WHERE o1 = o2;", dt);
1034  c("SELECT COUNT(*) FROM test WHERE o1 <> o2;", dt);
1035  ASSERT_EQ(19,
1036  v<int64_t>(run_simple_agg("SELECT rowid FROM test WHERE rowid = 19;", dt)));
1037  ASSERT_EQ(
1038  2 * g_num_rows,
1039  v<int64_t>(run_simple_agg("SELECT MAX(rowid) - MIN(rowid) + 1 FROM test;", dt)));
1040  ASSERT_EQ(
1041  15,
1042  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) = 0;", dt)));
1043  ASSERT_EQ(
1044  0,
1045  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) = 7;", dt)));
1046  ASSERT_EQ(5,
1047  v<int64_t>(
1048  run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) <> 0;", dt)));
1049  ASSERT_EQ(20,
1050  v<int64_t>(
1051  run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) <> 7;", dt)));
1052  c("SELECT MIN(x) FROM test WHERE x <> 7 AND x <> 8;", dt);
1053  c("SELECT MIN(x) FROM test WHERE z <> 101 AND z <> 102;", dt);
1054  c("SELECT MIN(x) FROM test WHERE t <> 1001 AND t <> 1002;", dt);
1055  ASSERT_NEAR(static_cast<double>(0.5),
1056  v<double>(run_simple_agg("SELECT STDDEV_POP(x) FROM test;", dt)),
1057  static_cast<double>(0.2));
1058  ASSERT_NEAR(static_cast<double>(0.5),
1059  v<double>(run_simple_agg("SELECT STDDEV_SAMP(x) FROM test;", dt)),
1060  static_cast<double>(0.2));
1061  ASSERT_NEAR(static_cast<double>(0.2),
1062  v<double>(run_simple_agg("SELECT VAR_POP(x) FROM test;", dt)),
1063  static_cast<double>(0.1));
1064  ASSERT_NEAR(static_cast<double>(0.2),
1065  v<double>(run_simple_agg("SELECT VAR_SAMP(x) FROM test;", dt)),
1066  static_cast<double>(0.1));
1067  ASSERT_NEAR(static_cast<double>(92.0),
1068  v<double>(run_simple_agg("SELECT STDDEV_POP(dd) FROM test;", dt)),
1069  static_cast<double>(2.0));
1070  ASSERT_NEAR(static_cast<double>(94.5),
1071  v<double>(run_simple_agg("SELECT STDDEV_SAMP(dd) FROM test;", dt)),
1072  static_cast<double>(1.0));
1073  ASSERT_NEAR(
1074  static_cast<double>(94.5),
1075  v<double>(run_simple_agg("SELECT POWER(((SUM(dd * dd) - SUM(dd) * SUM(dd) / "
1076  "COUNT(dd)) / (COUNT(dd) - 1)), 0.5) FROM test;",
1077  dt)),
1078  static_cast<double>(1.0));
1079  ASSERT_NEAR(static_cast<double>(8485.0),
1080  v<double>(run_simple_agg("SELECT VAR_POP(dd) FROM test;", dt)),
1081  static_cast<double>(10.0));
1082  ASSERT_NEAR(static_cast<double>(8932.0),
1083  v<double>(run_simple_agg("SELECT VAR_SAMP(dd) FROM test;", dt)),
1084  static_cast<double>(10.0));
1085  ASSERT_EQ(20,
1086  v<int64_t>(run_simple_agg(
1087  "SELECT COUNT(*) FROM test HAVING STDDEV_POP(x) < 1.0;", dt)));
1088  ASSERT_EQ(20,
1089  v<int64_t>(run_simple_agg(
1090  "SELECT COUNT(*) FROM test HAVING STDDEV_POP(x) * 5 < 3.0;", dt)));
1091  ASSERT_NEAR(
1092  static_cast<double>(0.65),
1093  v<double>(run_simple_agg("SELECT stddev(x) + VARIANCE(x) FROM test;", dt)),
1094  static_cast<double>(0.10));
1095  ASSERT_NEAR(static_cast<float>(0.5),
1096  v<float>(run_simple_agg("SELECT STDDEV_POP_FLOAT(x) FROM test;", dt)),
1097  static_cast<float>(0.2));
1098  ASSERT_NEAR(static_cast<float>(0.5),
1099  v<float>(run_simple_agg("SELECT STDDEV_SAMP_FLOAT(x) FROM test;", dt)),
1100  static_cast<float>(0.2));
1101  ASSERT_NEAR(static_cast<float>(0.2),
1102  v<float>(run_simple_agg("SELECT VAR_POP_FLOAT(x) FROM test;", dt)),
1103  static_cast<float>(0.1));
1104  ASSERT_NEAR(static_cast<float>(0.2),
1105  v<float>(run_simple_agg("SELECT VAR_SAMP_FLOAT(x) FROM test;", dt)),
1106  static_cast<float>(0.1));
1107  ASSERT_NEAR(static_cast<float>(92.0),
1108  v<float>(run_simple_agg("SELECT STDDEV_POP_FLOAT(dd) FROM test;", dt)),
1109  static_cast<float>(2.0));
1110  ASSERT_NEAR(static_cast<float>(94.5),
1111  v<float>(run_simple_agg("SELECT STDDEV_SAMP_FLOAT(dd) FROM test;", dt)),
1112  static_cast<float>(1.0));
1113  ASSERT_NEAR(
1114  static_cast<double>(94.5),
1115  v<double>(run_simple_agg("SELECT POWER(((SUM(dd * dd) - SUM(dd) * SUM(dd) / "
1116  "COUNT(dd)) / (COUNT(dd) - 1)), 0.5) FROM test;",
1117  dt)),
1118  static_cast<double>(1.0));
1119  ASSERT_NEAR(static_cast<float>(8485.0),
1120  v<float>(run_simple_agg("SELECT VAR_POP_FLOAT(dd) FROM test;", dt)),
1121  static_cast<float>(10.0));
1122  ASSERT_NEAR(static_cast<float>(8932.0),
1123  v<float>(run_simple_agg("SELECT VAR_SAMP_FLOAT(dd) FROM test;", dt)),
1124  static_cast<float>(10.0));
1125  ASSERT_EQ(20,
1126  v<int64_t>(run_simple_agg(
1127  "SELECT COUNT(*) FROM test HAVING STDDEV_POP_FLOAT(x) < 1.0;", dt)));
1128  ASSERT_EQ(
1129  20,
1130  v<int64_t>(run_simple_agg(
1131  "SELECT COUNT(*) FROM test HAVING STDDEV_POP_FLOAT(x) * 5 < 3.0;", dt)));
1132  ASSERT_NEAR(static_cast<float>(0.65),
1133  v<float>(run_simple_agg(
1134  "SELECT stddev_FLOAT(x) + VARIANCE_float(x) FROM test;", dt)),
1135  static_cast<float>(0.10));
1136  ASSERT_NEAR(static_cast<double>(0.125),
1137  v<double>(run_simple_agg("SELECT COVAR_POP(x, y) FROM test;", dt)),
1138  static_cast<double>(0.001));
1139  ASSERT_NEAR(static_cast<float>(0.125),
1140  v<float>(run_simple_agg("SELECT COVAR_POP_FLOAT(x, y) FROM test;", dt)),
1141  static_cast<float>(0.001));
1142  ASSERT_NEAR(
1143  static_cast<double>(0.125), // covar_pop expansion
1144  v<double>(run_simple_agg("SELECT avg(x * y) - avg(x) * avg(y) FROM test;", dt)),
1145  static_cast<double>(0.001));
1146  ASSERT_NEAR(static_cast<double>(0.131),
1147  v<double>(run_simple_agg("SELECT COVAR_SAMP(x, y) FROM test;", dt)),
1148  static_cast<double>(0.001));
1149  ASSERT_NEAR(static_cast<double>(0.131),
1150  v<double>(run_simple_agg("SELECT COVAR_SAMP_FLOAT(x, y) FROM test;", dt)),
1151  static_cast<double>(0.001));
1152  ASSERT_NEAR(
1153  static_cast<double>(0.131), // covar_samp expansion
1154  v<double>(run_simple_agg(
1155  "SELECT ((sum(x * y) - sum(x) * avg(y)) / (count(x) - 1)) FROM test;", dt)),
1156  static_cast<double>(0.001));
1157  ASSERT_NEAR(static_cast<double>(0.58),
1158  v<double>(run_simple_agg("SELECT CORRELATION(x, y) FROM test;", dt)),
1159  static_cast<double>(0.01));
1160  ASSERT_NEAR(static_cast<float>(0.58),
1161  v<float>(run_simple_agg("SELECT CORRELATION_FLOAT(x, y) FROM test;", dt)),
1162  static_cast<float>(0.01));
1163  ASSERT_NEAR(static_cast<double>(0.58),
1164  v<double>(run_simple_agg("SELECT CORR(x, y) FROM test;", dt)),
1165  static_cast<double>(0.01));
1166  ASSERT_NEAR(static_cast<float>(0.58),
1167  v<float>(run_simple_agg("SELECT CORR_FLOAT(x, y) FROM test;", dt)),
1168  static_cast<float>(0.01));
1169  ASSERT_NEAR(static_cast<double>(0.33),
1170  v<double>(run_simple_agg("SELECT POWER(CORR(x, y), 2) FROM test;", dt)),
1171  static_cast<double>(0.01));
1172  ASSERT_NEAR(static_cast<double>(0.58), // corr expansion
1173  v<double>(run_simple_agg("SELECT (avg(x * y) - avg(x) * avg(y)) /"
1174  "(stddev_pop(x) * stddev_pop(y)) FROM test;",
1175  dt)),
1176  static_cast<double>(0.01));
1177 
1178  // == Tests related to GPU shared-memory support
1180  c("SELECT COUNT(*) FROM test GROUP BY x ORDER BY x DESC;", dt);
1181  c("SELECT y, COUNT(*) FROM test GROUP BY y ORDER BY y DESC;", dt);
1182  c("SELECT str, COUNT(*) FROM test GROUP BY str ORDER BY str DESC;", dt);
1183  c("SELECT COUNT(*), z FROM test where x = 7 GROUP BY z ORDER BY z DESC;", dt);
1184  c("SELECT z as z0, z as z1, COUNT(*) FROM test GROUP BY z0, z1 ORDER BY z0 DESC;",
1185  dt);
1186  ;
1187  }
1188  }
1189 }
1190 
1191 TEST(Select, AggregateOnEmptyTable) {
1193  SKIP_NO_GPU();
1194  c("SELECT AVG(x), AVG(y), AVG(z), AVG(t), AVG(f), AVG(d) from empty_test_table;", dt);
1195  c("SELECT MIN(x), MIN(y), MIN(z), MIN(t), MIN(f), MIN(d) from empty_test_table;", dt);
1196  c("SELECT MAX(x), MAX(y), MAX(z), MAX(t), MAX(f), MAX(d) from empty_test_table;", dt);
1197  c("SELECT SUM(x), SUM(y), SUM(z), SUM(t), SUM(f), SUM(d) from empty_test_table;", dt);
1198  c("SELECT COUNT(x), COUNT(y), COUNT(z), COUNT(t), COUNT(f), COUNT(d) from "
1199  "empty_test_table;",
1200  dt);
1201  // skipped fragment
1202  c("SELECT AVG(x), AVG(y), AVG(z), AVG(t), AVG(f), AVG(d) from empty_test_table "
1203  "where id > 5;",
1204  dt);
1205  c("SELECT MIN(x), MIN(y), MIN(z), MIN(t), MIN(f), MIN(d) from empty_test_table where "
1206  "id > 5;",
1207  dt);
1208  c("SELECT MAX(x), MAX(y), MAX(z), MAX(t), MAX(f), MAX(d) from empty_test_table where "
1209  "id > 5;",
1210  dt);
1211  c("SELECT SUM(x), SUM(y), SUM(z), SUM(t), SUM(f), SUM(d) from empty_test_table where "
1212  "id > 5;",
1213  dt);
1214  c("SELECT COUNT(x), COUNT(y), COUNT(z), COUNT(t), COUNT(f), COUNT(d) from "
1215  "empty_test_table where id > 5;",
1216  dt);
1217  }
1218 }
1219 
1220 TEST(Select, LimitAndOffset) {
1221  CHECK(g_num_rows >= 4);
1223  SKIP_NO_GPU();
1224  {
1225  const auto rows = run_multiple_agg("SELECT * FROM test LIMIT 5;", dt);
1226  ASSERT_EQ(size_t(5), rows->rowCount());
1227  }
1228  {
1229  const auto rows = run_multiple_agg("SELECT * FROM test LIMIT 5 OFFSET 3;", dt);
1230  ASSERT_EQ(size_t(5), rows->rowCount());
1231  }
1232  {
1233  const auto rows =
1234  run_multiple_agg("SELECT * FROM test WHERE x <> 8 LIMIT 3 OFFSET 1;", dt);
1235  ASSERT_EQ(size_t(3), rows->rowCount());
1236  }
1237  c("SELECT str FROM (SELECT str, SUM(y) as total_y FROM test GROUP BY str ORDER BY "
1238  "total_y DESC, "
1239  "str LIMIT 1);",
1240  dt);
1241  EXPECT_THROW(run_multiple_agg("SELECT * FROM test LIMIT 0;", dt), std::runtime_error);
1242  }
1243 }
1244 
1245 TEST(Select, FloatAndDoubleTests) {
1247  SKIP_NO_GPU();
1248  c("SELECT MIN(f) FROM test;", dt);
1249  c("SELECT MAX(f) FROM test;", dt);
1250  c("SELECT AVG(f) FROM test;", dt);
1251  c("SELECT MIN(d) FROM test;", dt);
1252  c("SELECT MAX(d) FROM test;", dt);
1253  c("SELECT AVG(d) FROM test;", dt);
1254  c("SELECT SUM(f) FROM test;", dt);
1255  c("SELECT SUM(d) FROM test;", dt);
1256  c("SELECT SUM(f + d) FROM test;", dt);
1257  c("SELECT AVG(x * f) FROM test;", dt);
1258  c("SELECT AVG(z - 200) FROM test;", dt);
1259  c("SELECT SUM(CAST(x AS FLOAT)) FROM test;", dt);
1260  c("SELECT SUM(CAST(x AS FLOAT)) FROM test GROUP BY z;", dt);
1261  c("SELECT AVG(CAST(x AS FLOAT)) FROM test;", dt);
1262  c("SELECT AVG(CAST(x AS FLOAT)) FROM test GROUP BY y;", dt);
1263  c("SELECT COUNT(*) FROM test WHERE f > 1.0 AND f < 1.2;", dt);
1264  c("SELECT COUNT(*) FROM test WHERE f > 1.101 AND f < 1.299;", dt);
1265  c("SELECT COUNT(*) FROM test WHERE f > 1.201 AND f < 1.4;", dt);
1266  c("SELECT COUNT(*) FROM test WHERE f > 1.0 AND f < 1.2 AND d > 2.0 AND d < 2.4;", dt);
1267  c("SELECT COUNT(*) FROM test WHERE f > 1.0 AND f < 1.2 OR (d > 2.0 AND d < 3.0);",
1268  dt);
1269  c("SELECT SUM(x + y) FROM test WHERE f > 1.0 AND f < 1.2;", dt);
1270  c("SELECT SUM(x + y) FROM test WHERE d + f > 3.0 AND d + f < 4.0;", dt);
1271  c("SELECT SUM(f + d) FROM test WHERE x - y = -35;", dt);
1272  c("SELECT SUM(f + d) FROM test WHERE x + y + 1 = 50;", dt);
1273  c("SELECT SUM(f * d + 15) FROM test WHERE x + y + 1 = 50;", dt);
1274  c("SELECT MIN(x), AVG(x * y), MAX(y + 7), AVG(x * f + 15), COUNT(*) FROM test WHERE "
1275  "x + y > 47 AND x + y < 51;",
1276  dt);
1277  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(y) > 42.0 "
1278  "ORDER BY n;",
1279  dt);
1280  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(f) > 1.09 "
1281  "ORDER BY n;",
1282  dt);
1283  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(f) > 1.09 "
1284  "AND AVG(y) > 42.0 ORDER BY n;",
1285  dt);
1286  c("SELECT AVG(d), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(d) > 2.2 "
1287  "AND AVG(y) > 42.0 ORDER BY n;",
1288  dt);
1289  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(d) > 2.2 "
1290  "AND AVG(y) > 42.0 ORDER BY n;",
1291  dt);
1292  c("SELECT AVG(f) + AVG(d), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING "
1293  "AVG(f) + AVG(d) > 3.0 ORDER BY n;",
1294  dt);
1295  c("SELECT AVG(f) + AVG(d), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING "
1296  "AVG(f) + AVG(d) > 3.5 ORDER BY n;",
1297  dt);
1298  c("SELECT f + d AS s, x * y FROM test ORDER by s DESC;", dt);
1299  c("SELECT COUNT(*) AS n FROM test GROUP BY f ORDER BY n;", dt);
1300  c("SELECT f, COUNT(*) FROM test GROUP BY f HAVING f > 1.25;", dt);
1301  c("SELECT COUNT(*) AS n FROM test GROUP BY d ORDER BY n;", dt);
1302  c("SELECT MIN(x + y) AS n FROM test WHERE x + y > 47 AND x + y < 53 GROUP BY f + 1, "
1303  "f + d ORDER BY n;",
1304  dt);
1305  c("SELECT f + d AS s FROM test GROUP BY s ORDER BY s DESC;", dt);
1306  c("SELECT f + 1 AS s, AVG(u * f) FROM test GROUP BY s ORDER BY s DESC;", dt);
1307  c("SELECT (CAST(dd AS float) * 0.5) AS key FROM test GROUP BY key ORDER BY key DESC;",
1308  dt);
1309  c("SELECT (CAST(dd AS double) * 0.5) AS key FROM test GROUP BY key ORDER BY key "
1310  "DESC;",
1311  dt);
1312 
1313  c("SELECT fn FROM test ORDER BY fn ASC NULLS FIRST;",
1314  "SELECT fn FROM test ORDER BY fn ASC;",
1315  dt);
1316  c("SELECT fn FROM test WHERE fn < 0 OR fn IS NULL ORDER BY fn ASC NULLS FIRST;",
1317  "SELECT fn FROM test WHERE fn < 0 OR fn IS NULL ORDER BY fn ASC;",
1318  dt);
1319  ASSERT_NEAR(static_cast<double>(1.3),
1320  v<double>(run_simple_agg("SELECT AVG(f) AS n FROM test WHERE x = 7 GROUP "
1321  "BY z HAVING AVG(y) + STDDEV(y) "
1322  "> 42.0 ORDER BY n + VARIANCE(y);",
1323  dt)),
1324  static_cast<double>(0.1));
1325  ASSERT_NEAR(
1326  static_cast<double>(92.0),
1327  v<double>(run_simple_agg("SELECT STDDEV_POP(dd) AS n FROM test ORDER BY n;", dt)),
1328  static_cast<double>(1.0));
1329  }
1330 }
1331 
1332 TEST(Select, FilterShortCircuit) {
1334  SKIP_NO_GPU();
1335  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1336  "1000 AND UNLIKELY(t < 1002);",
1337  dt);
1338  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1339  "1000 AND t > 1000 AND t > 1001 "
1340  "AND t > 1002 AND t > 1003 AND t > 1004 AND UNLIKELY(t < 1002);",
1341  dt);
1342  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1343  "1000 AND t > 1000 AND t > 1001 "
1344  "AND t > 1002 AND t > 1003 AND t > 1004 AND t > 1005 AND UNLIKELY(t < 1002);",
1345  dt);
1346  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1347  "1000 AND t > 1000 AND t > 1001 "
1348  "AND t > 1002 AND t > 1003 AND UNLIKELY(t < 111) AND (str LIKE 'f__%%');",
1349  dt);
1350  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND UNLIKELY(z < 200) AND z > 100 "
1351  "AND z < 102 AND t > 1000 AND "
1352  "t > 1000 AND t > 1001 AND UNLIKELY(t < 1111 AND t > 1100) AND (str LIKE 'f__%%') "
1353  "AND t > 1002 AND t > 1003;",
1354  dt);
1355  c("SELECT COUNT(*) FROM test WHERE UNLIKELY(x IN (7, 8, 9, 10)) AND y > 42;", dt);
1356  }
1357 }
1358 
1359 TEST(Select, FilterAndMultipleAggregation) {
1361  SKIP_NO_GPU();
1362  c("SELECT AVG(x), AVG(y) FROM test;", dt);
1363  c("SELECT MIN(x), AVG(x * y), MAX(y + 7), COUNT(*) FROM test WHERE x + y > 47 AND x "
1364  "+ y < 51;",
1365  dt);
1366  c("SELECT str, AVG(x), COUNT(*) as xx, COUNT(*) as countval FROM test GROUP BY str "
1367  "ORDER BY str;",
1368  dt);
1369  }
1370 }
1371 
1372 TEST(Select, GroupBy) {
1374  SKIP_NO_GPU();
1375 
1376  c("SELECT COUNT(*) FROM test_ranges GROUP BY i, b;", dt);
1377  c("SELECT i, b FROM test_ranges GROUP BY i, b;", dt);
1378 
1379  {
1380  const auto big_group_threshold = g_big_group_threshold;
1381  ScopeGuard reset_big_group_threshold = [&big_group_threshold] {
1382  g_big_group_threshold = big_group_threshold;
1383  };
1385  c("SELECT d, COUNT(*) FROM test GROUP BY d ORDER BY d DESC LIMIT 10;", dt);
1386  }
1387 
1389  // TODO: Fixup the tests below when running with columnar output enabled
1390  continue;
1391  }
1392 
1393  c("SELECT x, y, COUNT(*) FROM test GROUP BY x, y;", dt);
1394  c("SELECT x, y, APPROX_COUNT_DISTINCT(str) FROM test GROUP BY x, y;",
1395  "SELECT x, y, COUNT(distinct str) FROM test GROUP BY x, y;",
1396  dt);
1397  c("SELECT f, ff, APPROX_COUNT_DISTINCT(str) from test group by f, ff ORDER BY f, ff;",
1398  "SELECT f, ff, COUNT(distinct str) FROM test GROUP BY f, ff ORDER BY f, ff;",
1399  dt);
1400  }
1401 }
1402 
1403 TEST(Select, FilterAndGroupBy) {
1405  SKIP_NO_GPU();
1406  c("SELECT MIN(x + y) FROM test WHERE x + y > 47 AND x + y < 53 GROUP BY x, y;", dt);
1407  c("SELECT MIN(x + y) FROM test WHERE x + y > 47 AND x + y < 53 GROUP BY x + 1, x + "
1408  "y;",
1409  dt);
1410  c("SELECT x, y, COUNT(*) FROM test GROUP BY x, y;", dt);
1411  c("SELECT x, dd, COUNT(*) FROM test GROUP BY x, dd ORDER BY x, dd;", dt);
1412  c("SELECT dd AS key1, COUNT(*) AS value1 FROM test GROUP BY key1 HAVING key1 IS NOT "
1413  "NULL ORDER BY key1, value1 "
1414  "DESC "
1415  "LIMIT 12;",
1416  dt);
1417  c("SELECT 'literal_string' AS key0 FROM test GROUP BY key0;", dt);
1418  c("SELECT str, MIN(y) FROM test WHERE y IS NOT NULL GROUP BY str ORDER BY str DESC;",
1419  dt);
1420  c("SELECT x, MAX(z) FROM test WHERE z IS NOT NULL GROUP BY x HAVING x > 7;", dt);
1421  c("SELECT CAST((dd - 0.5) * 2.0 AS int) AS key0, COUNT(*) AS val FROM test WHERE (dd "
1422  ">= 100.0 AND dd < 400.0) "
1423  "GROUP "
1424  "BY key0 HAVING key0 >= 0 AND key0 < 400 ORDER BY val DESC LIMIT 50 OFFSET 0;",
1425  dt);
1426  c("SELECT y, AVG(CASE WHEN x BETWEEN 6 AND 7 THEN x END) FROM test GROUP BY y ORDER "
1427  "BY y;",
1428  dt);
1429  c("SELECT x, AVG(u), COUNT(*) AS n FROM test GROUP BY x ORDER BY n DESC;", dt);
1430  c("SELECT f, ss FROM test GROUP BY f, ss ORDER BY f DESC;", dt);
1431  c("SELECT fx, COUNT(*) FROM test GROUP BY fx HAVING COUNT(*) > 5;", dt);
1432  c("SELECT fx, COUNT(*) n FROM test GROUP BY fx ORDER BY n DESC, fx IS NULL DESC;",
1433  dt);
1434  c("SELECT CASE WHEN x > 8 THEN 100000000 ELSE 42 END AS c, COUNT(*) FROM test GROUP "
1435  "BY c;",
1436  dt);
1437  c("SELECT COUNT(*) FROM test WHERE CAST((CAST(x AS FLOAT) - 0) * 0.2 AS INT) = 1;",
1438  dt);
1439  c("SELECT CAST(CAST(d AS FLOAT) AS INTEGER) AS key, COUNT(*) FROM test GROUP BY key;",
1440  dt);
1441  c("SELECT x * 2 AS x2, COUNT(DISTINCT y) AS n FROM test GROUP BY x2 ORDER BY n DESC;",
1442  dt);
1443  c("SELECT x, COUNT(real_str) FROM test GROUP BY x ORDER BY x DESC;", dt);
1444  c("SELECT str, SUM(y - y) FROM test GROUP BY str ORDER BY str ASC;", dt);
1445  c("SELECT str, SUM(y - y) FROM test WHERE y - y IS NOT NULL GROUP BY str ORDER BY "
1446  "str ASC;",
1447  dt);
1448  c("select shared_dict,m from test where (m >= CAST('2014-12-13 22:23:15' AS "
1449  "TIMESTAMP(0)) and m <= "
1450  "CAST('2014-12-14 22:23:15' AS TIMESTAMP(0))) and CAST(m AS TIMESTAMP(0)) BETWEEN "
1451  "'2014-12-14 22:23:15' AND "
1452  "'2014-12-13 22:23:15' group by shared_dict,m;",
1453  dt);
1454  c("SELECT x, SUM(z) FROM test WHERE z IS NOT NULL GROUP BY x ORDER BY x;", dt);
1455  EXPECT_THROW(run_multiple_agg(
1456  "SELECT x, MIN(real_str) FROM test GROUP BY x ORDER BY x DESC;", dt),
1457  std::runtime_error);
1458  EXPECT_THROW(run_multiple_agg(
1459  "SELECT x, MAX(real_str) FROM test GROUP BY x ORDER BY x DESC;", dt),
1460  std::runtime_error);
1461  EXPECT_THROW(run_multiple_agg("SELECT MIN(str) FROM test GROUP BY x;", dt),
1462  std::runtime_error);
1463  }
1464 }
1465 
1466 TEST(Select, GroupByBoundariesAndNull) {
1468  SKIP_NO_GPU();
1469  {
1470  std::string query(
1471  "SELECT CAST(CASE WHEN x = 7 THEN 2147483647 ELSE null END AS INTEGER) AS "
1472  "col0, COUNT(*) FROM test GROUP BY col0 ORDER BY col0 ASC");
1473  c(query + " NULLS FIRST;", query + ";", dt);
1474  }
1475  {
1476  std::string query(
1477  "SELECT smallint_nulls, COUNT(*) FROM test GROUP BY smallint_nulls ORDER BY "
1478  "smallint_nulls ASC");
1479  c(query + " NULLS FIRST;", query + ";", dt);
1480  }
1481  {
1482  std::string query(
1483  "SELECT CAST(CASE WHEN x = 7 THEN 127 ELSE null END AS TINYINT) AS col0, "
1484  "COUNT(*) FROM test GROUP BY col0 ORDER BY col0 ASC");
1485  c(query + " NULLS FIRST;", query + ";", dt);
1486  }
1487  }
1488 }
1489 
1490 TEST(Select, Arrays) {
1492  SKIP_NO_GPU();
1493 
1494  // Simple lazy projection
1495  compare_array(run_simple_agg("SELECT arr_i16 FROM array_test WHERE x = 8;", dt),
1496  std::vector<int64_t>({2, 3, 4}));
1497  compare_array(run_simple_agg("SELECT arr_i32 FROM array_test WHERE x = 8;", dt),
1498  std::vector<int64_t>({20, 30, 40}));
1499  compare_array(run_simple_agg("SELECT arr_i64 FROM array_test WHERE x = 8;", dt),
1500  std::vector<int64_t>({200, 300, 400}));
1501  compare_array(run_simple_agg("SELECT arr_str FROM array_test WHERE x = 8;", dt),
1502  std::vector<std::string>({"bb", "cc", "dd"}));
1503  compare_array(run_simple_agg("SELECT arr_float FROM array_test WHERE x = 8;", dt),
1504  std::vector<float>({2.2, 3.3, 4.4}));
1505  compare_array(run_simple_agg("SELECT arr_double FROM array_test WHERE x = 8;", dt),
1506  std::vector<double>({22.2, 33.3, 44.4}));
1507  compare_array(run_simple_agg("SELECT arr_bool FROM array_test WHERE x = 8;", dt),
1508  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1509 
1510  compare_array(run_simple_agg("SELECT arr3_i8 FROM array_test WHERE x = 8;", dt),
1511  std::vector<int64_t>({2, 3, 4}));
1512  compare_array(run_simple_agg("SELECT arr3_i16 FROM array_test WHERE x = 8;", dt),
1513  std::vector<int64_t>({2, 3, 4}));
1514  compare_array(run_simple_agg("SELECT arr3_i32 FROM array_test WHERE x = 8;", dt),
1515  std::vector<int64_t>({20, 30, 40}));
1516  compare_array(run_simple_agg("SELECT arr3_i64 FROM array_test WHERE x = 8;", dt),
1517  std::vector<int64_t>({200, 300, 400}));
1518  compare_array(run_simple_agg("SELECT arr3_float FROM array_test WHERE x = 8;", dt),
1519  std::vector<float>({2.2, 3.3, 4.4}));
1520  compare_array(run_simple_agg("SELECT arr3_double FROM array_test WHERE x = 8;", dt),
1521  std::vector<double>({22.2, 33.3, 44.4}));
1522  compare_array(run_simple_agg("SELECT arr6_bool FROM array_test WHERE x = 8;", dt),
1523  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1524 
1526  compare_array(
1528  "SELECT ARRAY[1,2,3,5] from array_test WHERE x = 8 limit 8675309;", dt),
1529  std::vector<int64_t>({1, 2, 3, 5})););
1531  run_simple_agg("SELECT ARRAY[2*arr3_i32[1],2*arr3_i32[2],2*arr3_i32[3]] FROM "
1532  "array_test a WHERE x = 8 limit 31337;",
1533  dt),
1534  std::vector<int64_t>({40, 60, 80})));
1535 
1536  // Simple non-lazy projection
1537  compare_array(
1538  run_simple_agg("SELECT arr_i16 FROM array_test WHERE arr_i16[1] = 2;", dt),
1539  std::vector<int64_t>({2, 3, 4}));
1540  compare_array(
1541  run_simple_agg("SELECT arr_i32 FROM array_test WHERE arr_i32[1] = 20;", dt),
1542  std::vector<int64_t>({20, 30, 40}));
1543  compare_array(
1544  run_simple_agg("SELECT arr_i64 FROM array_test WHERE arr_i64[1] = 200;", dt),
1545  std::vector<int64_t>({200, 300, 400}));
1546  compare_array(
1547  run_simple_agg("SELECT arr_str FROM array_test WHERE arr_str[1] = 'bb';", dt),
1548  std::vector<std::string>({"bb", "cc", "dd"}));
1549  // TODO(adb): Calcite is casting the column value to DOUBLE to do the comparison,
1550  // which results in the comparison failing. Is this desired behavior or a bug? Adding
1551  // the CAST below for now to test projection.
1552  compare_array(
1554  "SELECT arr_float FROM array_test WHERE arr_float[1] = CAST(2.2 as FLOAT);",
1555  dt),
1556  std::vector<float>({2.2, 3.3, 4.4}));
1557  compare_array(
1558  run_simple_agg("SELECT arr_double FROM array_test WHERE arr_double[1] = 22.2;",
1559  dt),
1560  std::vector<double>({22.2, 33.3, 44.4}));
1562  "SELECT arr_bool FROM array_test WHERE x < 9 AND arr_bool[1];", dt),
1563  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1564 
1565  compare_array(
1566  run_simple_agg("SELECT arr3_i8 FROM array_test WHERE arr3_i8[1] = 2;", dt),
1567  std::vector<int64_t>({2, 3, 4}));
1568  compare_array(
1569  run_simple_agg("SELECT arr3_i16 FROM array_test WHERE arr3_i16[1] = 2;", dt),
1570  std::vector<int64_t>({2, 3, 4}));
1571  compare_array(
1572  run_simple_agg("SELECT arr3_i32 FROM array_test WHERE arr3_i32[1] = 20;", dt),
1573  std::vector<int64_t>({20, 30, 40}));
1574  compare_array(
1575  run_simple_agg("SELECT arr3_i64 FROM array_test WHERE arr3_i64[1] = 200;", dt),
1576  std::vector<int64_t>({200, 300, 400}));
1577  compare_array(
1579  "SELECT arr3_float FROM array_test WHERE arr3_float[1] = CAST(2.2 AS FLOAT);",
1580  dt),
1581  std::vector<float>({2.2, 3.3, 4.4}));
1582  compare_array(
1583  run_simple_agg("SELECT arr3_double FROM array_test WHERE arr3_double[1] = 22.2;",
1584  dt),
1585  std::vector<double>({22.2, 33.3, 44.4}));
1586  compare_array(
1587  run_simple_agg("SELECT arr6_bool FROM array_test WHERE x < 9 AND arr6_bool[1];",
1588  dt),
1589  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1590  }
1591 }
1592 
1593 TEST(Select, FilterCastToDecimal) {
1595  SKIP_NO_GPU();
1596 
1597  ASSERT_EQ(static_cast<int64_t>(5),
1598  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE x > 7.1;", dt)));
1599  ASSERT_EQ(
1600  static_cast<int64_t>(10),
1601  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE y > 42.5;", dt)));
1602  ASSERT_EQ(static_cast<int64_t>(10),
1603  v<int64_t>(run_simple_agg(
1604  "SELECT COUNT(*) FROM test WHERE ufd > -2147483648.0;", dt)));
1605  ASSERT_EQ(static_cast<int64_t>(15),
1606  v<int64_t>(run_simple_agg(
1607  "SELECT COUNT(*) FROM test WHERE ofd > -2147483648;", dt)));
1608  }
1609 }
1610 
1611 TEST(Select, FilterAndGroupByMultipleAgg) {
1613  SKIP_NO_GPU();
1614  c("SELECT MIN(x + y), COUNT(*), AVG(x + 1) FROM test WHERE x + y > 47 AND x + y < 53 "
1615  "GROUP BY x, y;",
1616  dt);
1617  c("SELECT MIN(x + y), COUNT(*), AVG(x + 1) FROM test WHERE x + y > 47 AND x + y < 53 "
1618  "GROUP BY x + 1, x + y;",
1619  dt);
1620  }
1621 }
1622 
1623 TEST(Select, GroupByKeylessAndNotKeyless) {
1625  SKIP_NO_GPU();
1626  c("SELECT fixed_str FROM test WHERE fixed_str = 'fish' GROUP BY fixed_str;", dt);
1627  c("SELECT AVG(x), fixed_str FROM test WHERE fixed_str = 'fish' GROUP BY fixed_str;",
1628  dt);
1629  c("SELECT AVG(smallint_nulls), fixed_str FROM test WHERE fixed_str = 'foo' GROUP BY "
1630  "fixed_str;",
1631  dt);
1632  c("SELECT null_str, AVG(smallint_nulls) FROM test GROUP BY null_str;", dt);
1633  }
1634 }
1635 
1636 TEST(Select, Having) {
1638  SKIP_NO_GPU();
1639  c("SELECT MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING MAX(x) > 5 ORDER BY n;",
1640  dt);
1641  c("SELECT MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING MAX(x) > 5 ORDER BY n "
1642  "LIMIT 1;",
1643  dt);
1644  c("SELECT MAX(y) AS n FROM test WHERE x > 7 GROUP BY z HAVING MAX(x) < 100 ORDER BY "
1645  "n;",
1646  dt);
1647  c("SELECT z, SUM(y) AS n FROM test WHERE x > 6 GROUP BY z HAVING MAX(x) < 100 ORDER "
1648  "BY n;",
1649  dt);
1650  c("SELECT z, SUM(y) AS n FROM test WHERE x > 6 GROUP BY z HAVING MAX(x) < 100 AND "
1651  "COUNT(*) > 5 ORDER BY n;",
1652  dt);
1653  c("SELECT z, SUM(y) AS n FROM test WHERE x > 6 GROUP BY z HAVING MAX(x) < 100 AND "
1654  "COUNT(*) > 9 ORDER BY n;",
1655  dt);
1656  c("SELECT str, COUNT(*) AS n FROM test GROUP BY str HAVING str IN ('bar', 'baz') "
1657  "ORDER BY str;",
1658  dt);
1659  c("SELECT str, COUNT(*) AS n FROM test GROUP BY str HAVING str LIKE 'ba_' ORDER BY "
1660  "str;",
1661  dt);
1662  c("SELECT ss, COUNT(*) AS n FROM test GROUP BY ss HAVING ss LIKE 'bo_' ORDER BY ss;",
1663  dt);
1664  c("SELECT x, COUNT(*) FROM test WHERE x > 9 GROUP BY x HAVING x > 15;", dt);
1665  c("SELECT x, AVG(y), AVG(y) FROM test GROUP BY x HAVING x >= 0 ORDER BY x;", dt);
1666  c("SELECT AVG(y), x, AVG(y) FROM test GROUP BY x HAVING x >= 0 ORDER BY x;", dt);
1667  c("SELECT x, y, COUNT(*) FROM test WHERE real_str LIKE 'nope%' GROUP BY x, y HAVING "
1668  "x >= 0 AND x < 12 AND y >= 0 "
1669  "AND y < 12 ORDER BY x, y;",
1670  dt);
1671  }
1672 }
1673 
1674 TEST(Select, CountDistinct) {
1676  SKIP_NO_GPU();
1677  c("SELECT COUNT(distinct x) FROM test;", dt);
1678  c("SELECT COUNT(distinct b) FROM test;", dt);
1679  SKIP_ON_AGGREGATOR(c("SELECT COUNT(distinct f) FROM test;",
1680  dt)); // Exception: Cannot use a fast path for COUNT distinct
1681  SKIP_ON_AGGREGATOR(c("SELECT COUNT(distinct d) FROM test;",
1682  dt)); // Exception: Cannot use a fast path for COUNT distinct
1683  c("SELECT COUNT(distinct str) FROM test;", dt);
1684  c("SELECT COUNT(distinct ss) FROM test;", dt);
1685  c("SELECT COUNT(distinct x + 1) FROM test;", dt);
1686  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x) FROM test "
1687  "GROUP BY y ORDER BY n;",
1688  dt);
1689  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x + 1) FROM "
1690  "test GROUP BY y ORDER BY n;",
1691  dt);
1692  c("SELECT COUNT(distinct dd) AS n FROM test GROUP BY y ORDER BY n;", dt);
1693  c("SELECT z, str, AVG(z), COUNT(distinct z) FROM test GROUP BY z, str ORDER BY z, "
1694  "str;",
1695  dt);
1696  c("SELECT AVG(z), COUNT(distinct x) AS dx FROM test GROUP BY y HAVING dx > 1;", dt);
1698  c("SELECT z, str, COUNT(distinct f) FROM test GROUP BY z, str ORDER BY str DESC;",
1699  dt)); // Exception: Cannot use a fast path for COUNT distinct
1700  c("SELECT COUNT(distinct x * (50000 - 1)) FROM test;", dt);
1701  EXPECT_THROW(run_multiple_agg("SELECT COUNT(distinct real_str) FROM test;", dt),
1702  std::runtime_error); // Exception: Strings must be dictionary-encoded
1703  // for COUNT(DISTINCT).
1704  }
1705 }
1706 
1707 TEST(Select, ApproxCountDistinct) {
1709  SKIP_NO_GPU();
1710  c("SELECT APPROX_COUNT_DISTINCT(x) FROM test;",
1711  "SELECT COUNT(distinct x) FROM test;",
1712  dt);
1713  c("SELECT APPROX_COUNT_DISTINCT(x) FROM test_empty;",
1714  "SELECT COUNT(distinct x) FROM test_empty;",
1715  dt);
1716  c("SELECT APPROX_COUNT_DISTINCT(x) FROM test_one_row;",
1717  "SELECT COUNT(distinct x) FROM test_one_row;",
1718  dt);
1719  c("SELECT APPROX_COUNT_DISTINCT(b) FROM test;",
1720  "SELECT COUNT(distinct b) FROM test;",
1721  dt);
1722  c("SELECT APPROX_COUNT_DISTINCT(f) FROM test;",
1723  "SELECT COUNT(distinct f) FROM test;",
1724  dt);
1725  c("SELECT APPROX_COUNT_DISTINCT(d) FROM test;",
1726  "SELECT COUNT(distinct d) FROM test;",
1727  dt);
1728  c("SELECT APPROX_COUNT_DISTINCT(str) FROM test;",
1729  "SELECT COUNT(distinct str) FROM test;",
1730  dt);
1731  c("SELECT APPROX_COUNT_DISTINCT(null_str) FROM test;",
1732  "SELECT COUNT(distinct null_str) FROM test;",
1733  dt);
1734  c("SELECT APPROX_COUNT_DISTINCT(ss) FROM test WHERE ss IS NOT NULL;",
1735  "SELECT COUNT(distinct ss) FROM test;",
1736  dt);
1737  c("SELECT APPROX_COUNT_DISTINCT(x + 1) FROM test;",
1738  "SELECT COUNT(distinct x + 1) FROM test;",
1739  dt);
1740  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x) "
1741  "FROM test GROUP BY y ORDER "
1742  "BY n;",
1743  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x) FROM test "
1744  "GROUP BY y ORDER BY n;",
1745  dt);
1746  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x + "
1747  "1) FROM test GROUP BY y "
1748  "ORDER BY n;",
1749  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x + 1) FROM "
1750  "test GROUP BY y ORDER BY n;",
1751  dt);
1752  c("SELECT APPROX_COUNT_DISTINCT(dd) AS n FROM test GROUP BY y ORDER BY n;",
1753  "SELECT COUNT(distinct dd) AS n FROM test GROUP BY y ORDER BY n;",
1754  dt);
1755  c("SELECT z, str, AVG(z), APPROX_COUNT_DISTINCT(z) FROM test GROUP BY z, str ORDER "
1756  "BY z;",
1757  "SELECT z, str, AVG(z), COUNT(distinct z) FROM test GROUP BY z, str ORDER BY z;",
1758  dt);
1759  c("SELECT APPROX_COUNT_DISTINCT(null_str) AS n FROM test GROUP BY x ORDER BY n;",
1760  "SELECT COUNT(distinct null_str) AS n FROM test GROUP BY x ORDER BY n;",
1761  dt);
1762  c("SELECT z, APPROX_COUNT_DISTINCT(null_str) AS n FROM test GROUP BY z ORDER BY z, "
1763  "n;",
1764  "SELECT z, COUNT(distinct null_str) AS n FROM test GROUP BY z ORDER BY z, n;",
1765  dt);
1766  c("SELECT AVG(z), APPROX_COUNT_DISTINCT(x) AS dx FROM test GROUP BY y HAVING dx > 1;",
1767  "SELECT AVG(z), COUNT(distinct x) AS dx FROM test GROUP BY y HAVING dx > 1;",
1768  dt);
1769  c("SELECT approx_value, exact_value FROM (SELECT APPROX_COUNT_DISTINCT(x) AS "
1770  "approx_value FROM test), (SELECT "
1771  "COUNT(distinct x) AS exact_value FROM test);",
1772  "SELECT approx_value, exact_value FROM (SELECT COUNT(distinct x) AS approx_value "
1773  "FROM test), (SELECT "
1774  "COUNT(distinct x) AS exact_value FROM test);",
1775  dt);
1776  c("SELECT APPROX_COUNT_DISTINCT(x, 1) FROM test;",
1777  "SELECT COUNT(distinct x) FROM test;",
1778  dt);
1779  c("SELECT APPROX_COUNT_DISTINCT(b, 10) FROM test;",
1780  "SELECT COUNT(distinct b) FROM test;",
1781  dt);
1782  c("SELECT APPROX_COUNT_DISTINCT(f, 20) FROM test;",
1783  "SELECT COUNT(distinct f) FROM test;",
1784  dt);
1785  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x, 1) "
1786  "FROM test GROUP BY y ORDER "
1787  "BY n;",
1788  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x) FROM test "
1789  "GROUP BY y ORDER BY n;",
1790  dt);
1791  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x + "
1792  "1, 1) FROM test GROUP BY y "
1793  "ORDER BY n;",
1794  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x + 1) FROM "
1795  "test GROUP BY y ORDER BY n;",
1796  dt);
1797  // Test approx_count_distinct buffer allocation with multi-slot targets
1798  // sqlite does not support SAMPLE, grab the first row only
1799  c("SELECT SAMPLE(real_str), str, APPROX_COUNT_DISTINCT(x) FROM test WHERE real_str = "
1800  "'real_bar' GROUP BY str;",
1801  "SELECT real_str, str, COUNT( distinct x) FROM test WHERE real_str = "
1802  "'real_bar' GROUP BY str;",
1803  dt);
1804  c("SELECT SAMPLE(real_str), str, APPROX_COUNT_DISTINCT(x) FROM test WHERE real_str = "
1805  "'real_foo' GROUP BY str;",
1806  "SELECT real_str, str, COUNT(distinct x) FROM test WHERE real_str = "
1807  "'real_foo' GROUP BY str, real_str;",
1808  dt);
1809 
1810  EXPECT_NO_THROW(run_multiple_agg(
1811  "SELECT APPROX_COUNT_DISTINCT(x), SAMPLE(real_str) FROM test GROUP BY x;", dt));
1812  EXPECT_THROW(
1813  run_multiple_agg("SELECT APPROX_COUNT_DISTINCT(real_str) FROM test;", dt),
1814  std::runtime_error);
1815  EXPECT_THROW(run_multiple_agg("SELECT APPROX_COUNT_DISTINCT(x, 0) FROM test;", dt),
1816  std::runtime_error);
1817  }
1818 }
1819 
1820 TEST(Select, ScanNoAggregation) {
1822  SKIP_NO_GPU();
1823  c("SELECT * FROM test ORDER BY x ASC, y ASC;", dt);
1824  c("SELECT t.* FROM test t ORDER BY x ASC, y ASC;", dt);
1825  c("SELECT x, z, t FROM test ORDER BY x ASC, y ASC;", dt);
1826  c("SELECT x, y, x + 1 FROM test ORDER BY x ASC, y ASC;", dt);
1827  c("SELECT x + z, t FROM test WHERE x <> 7 AND y > 42;", dt);
1828  c("SELECT * FROM test WHERE x > 8;", dt);
1829  c("SELECT fx FROM test WHERE fx IS NULL;", dt);
1830  c("SELECT z,t,f,m,d,x,real_str,u,z,y FROM test WHERE z = -78 AND t = "
1831  "1002 AND x >= 8 AND y = 43 AND d > 1.0 AND f > 1.0 AND real_str = 'real_bar' "
1832  "ORDER BY f ASC;",
1833  dt);
1834  c("SELECT * FROM test WHERE d > 2.4 AND real_str IS NOT NULL AND fixed_str IS NULL "
1835  "AND z = 102 AND fn < 0 AND y = 43 AND t >= 0 AND x <> 8;",
1836  dt);
1837  c("SELECT * FROM test WHERE d > 2.4 AND real_str IS NOT NULL AND fixed_str IS NULL "
1838  "AND z = 102 AND fn < 0 AND y = 43 AND t >= 0 AND x = 8;",
1839  dt);
1840  c("SELECT real_str,f,fn,y,d,x,z,str,fixed_str,t,dn FROM test WHERE f IS NOT NULL AND "
1841  "y IS NOT NULL AND str = 'bar' AND x >= 7 AND t < 1003 AND z < 0;",
1842  dt);
1843  c("SELECT t,y,str,real_str,d,fixed_str,dn,fn,z,f,x FROM test WHERE f IS NOT NULL AND "
1844  "y IS NOT NULL AND str = 'baz' AND x >= 7 AND t < 1003 AND f > 1.2 LIMIT 1;",
1845  dt);
1846  c("SELECT fn,real_str,str,z,d,x,fixed_str,dn,y,t,f FROM test WHERE f < 1.4 AND "
1847  "real_str IS NOT NULL AND fixed_str IS NULL AND z = 102 AND dn < 0 AND y = 43;",
1848  dt);
1849  c("SELECT dn,str,y,z,fixed_str,fn,d,real_str,t,f,x FROM test WHERE z < 0 AND f < 2 "
1850  "AND d > 2.0 AND fn IS NOT NULL AND dn < 2000 AND str IS NOT NULL AND fixed_str = "
1851  "'bar' AND real_str = 'real_bar' AND t >= 1001 AND y >= 42 AND x > 7 ORDER BY z, "
1852  "x;",
1853  dt);
1854  c("SELECT z,f,d,str,real_str,x,dn,y,t,fn,fixed_str FROM test WHERE fn IS NULL AND dn "
1855  "IS NULL AND x >= 0 AND real_str = 'real_foo' ORDER BY y;",
1856  dt);
1857  }
1858 }
1859 
1860 TEST(Select, OrderBy) {
1862  SKIP_NO_GPU();
1863  const auto rows = run_multiple_agg(
1864  "SELECT x, y, z + t, x * y AS m FROM test ORDER BY 3 desc LIMIT 5;", dt);
1865  CHECK_EQ(rows->rowCount(), std::min(size_t(5), static_cast<size_t>(g_num_rows)) + 0);
1866  CHECK_EQ(rows->colCount(), size_t(4));
1867  for (size_t row_idx = 0; row_idx < rows->rowCount(); ++row_idx) {
1868  ASSERT_TRUE(v<int64_t>(rows->getRowAt(row_idx, 0, true)) == 8 ||
1869  v<int64_t>(rows->getRowAt(row_idx, 0, true)) == 7);
1870  ASSERT_EQ(v<int64_t>(rows->getRowAt(row_idx, 1, true)), 43);
1871  ASSERT_EQ(v<int64_t>(rows->getRowAt(row_idx, 2, true)), 1104);
1872  ASSERT_TRUE(v<int64_t>(rows->getRowAt(row_idx, 3, true)) == 344 ||
1873  v<int64_t>(rows->getRowAt(row_idx, 3, true)) == 301);
1874  }
1875  c("SELECT x, COUNT(distinct y) AS n FROM test GROUP BY x ORDER BY n DESC;", dt);
1876  c("SELECT x x1, x, COUNT(*) AS val FROM test GROUP BY x HAVING val > 5 ORDER BY val "
1877  "DESC LIMIT 5;",
1878  dt);
1879  c("SELECT ufd, COUNT(*) n FROM test GROUP BY ufd, str ORDER BY ufd, n;", dt);
1880  c("SELECT -x, COUNT(*) FROM test GROUP BY x ORDER BY x DESC;", dt);
1881  c("SELECT real_str FROM test WHERE real_str LIKE '%real%' ORDER BY real_str ASC;",
1882  dt);
1883  c("SELECT ss FROM test GROUP by ss ORDER BY ss ASC NULLS FIRST;",
1884  "SELECT ss FROM test GROUP by ss ORDER BY ss ASC;",
1885  dt);
1886  c("SELECT str, COUNT(*) n FROM test WHERE x < 0 GROUP BY str ORDER BY n DESC LIMIT "
1887  "5;",
1888  dt);
1889  c("SELECT x FROM test ORDER BY x LIMIT 50;", dt);
1890  c("SELECT x FROM test ORDER BY x LIMIT 5;", dt);
1891  c("SELECT x FROM test ORDER BY x ASC LIMIT 20;", dt);
1892  c("SELECT dd FROM test ORDER BY dd ASC LIMIT 20;", dt);
1893  c("SELECT f FROM test ORDER BY f ASC LIMIT 5;", dt);
1894  c("SELECT f FROM test ORDER BY f ASC LIMIT 20;", dt);
1895  c("SELECT fn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1896  "SELECT fn as k FROM test ORDER BY k ASC LIMIT 5;",
1897  dt);
1898  c("SELECT fn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1899  "SELECT fn as k FROM test ORDER BY k ASC LIMIT 20;",
1900  dt);
1901  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1902  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 5;",
1903  dt);
1904  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1905  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 20;",
1906  dt);
1907  c("SELECT ff as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1908  "SELECT ff as k FROM test ORDER BY k ASC LIMIT 5;",
1909  dt);
1910  c("SELECT ff as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1911  "SELECT ff as k FROM test ORDER BY k ASC LIMIT 20;",
1912  dt);
1913  c("SELECT d as k FROM test ORDER BY k ASC LIMIT 5;", dt);
1914  c("SELECT d as k FROM test ORDER BY k ASC LIMIT 20;", dt);
1915  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1916  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 5;",
1917  dt);
1918  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1919  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 20;",
1920  dt);
1921  c("SELECT ofq AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1922  "SELECT ofq as k FROM test ORDER BY k ASC LIMIT 5;",
1923  dt);
1924  c("SELECT ofq AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1925  "SELECT ofq as k FROM test ORDER BY k ASC LIMIT 20;",
1926  dt);
1927  c("SELECT ufq as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1928  "SELECT ufq as k FROM test ORDER BY k ASC LIMIT 5;",
1929  dt);
1930  c("SELECT ufq as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1931  "SELECT ufq as k FROM test ORDER BY k ASC LIMIT 20;",
1932  dt);
1933  c("SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1934  "SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 5;",
1935  dt);
1936  c("SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1937  "SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 20;",
1938  dt);
1939  c("SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1940  "SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 5;",
1941  dt);
1942  c("SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1943  "SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 20;",
1944  dt);
1945  c("SELECT m AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1946  "SELECT m AS k FROM test ORDER BY k ASC LIMIT 20;",
1947  dt);
1948  c("SELECT n AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1949  "SELECT n AS k FROM test ORDER BY k ASC LIMIT 20;",
1950  dt);
1951  c("SELECT o AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1952  "SELECT o AS k FROM test ORDER BY k ASC LIMIT 20;",
1953  dt);
1954  }
1955 }
1956 
1957 TEST(Select, TopKHeap) {
1959  SKIP_NO_GPU();
1960  c("SELECT str, x FROM proj_top ORDER BY x DESC LIMIT 1;", dt);
1961  }
1962 }
1963 
1964 TEST(Select, ComplexQueries) {
1966  SKIP_NO_GPU();
1967  c("SELECT COUNT(*) * MAX(y) - SUM(z) FROM test;", dt);
1968  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1969  "AND 200 GROUP BY x, y;",
1970  dt);
1971  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1972  "AND 200 "
1973  "GROUP BY x, y HAVING y > 2 * x AND MIN(y) > MAX(x);",
1974  dt);
1975  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1976  "AND 200 "
1977  "GROUP BY x, y HAVING y > 2 * x AND MIN(y) > MAX(x) + 35;",
1978  dt);
1979  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1980  "AND 200 "
1981  "GROUP BY x, y HAVING y > 2 * x AND MIN(y) > MAX(x) + 36;",
1982  dt);
1983  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test "
1984  "WHERE z BETWEEN 100 AND 200 GROUP BY a, y;",
1985  dt);
1986  c("SELECT x, y FROM (SELECT a.str AS str, b.x AS x, a.y AS y FROM test a, join_test "
1987  "b WHERE a.x = b.x) WHERE str = "
1988  "'foo' ORDER BY x LIMIT 1;",
1989  dt);
1990  const auto rows = run_multiple_agg(
1991  "SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test "
1992  "WHERE z BETWEEN 100 AND 200 GROUP BY x, y ORDER BY a DESC LIMIT 2;",
1993  dt);
1994  ASSERT_EQ(rows->rowCount(), size_t(2));
1995  {
1996  auto crt_row = rows->getNextRow(true, true);
1997  CHECK_EQ(size_t(2), crt_row.size());
1998  ASSERT_EQ(v<int64_t>(crt_row[0]), 50);
1999  ASSERT_EQ(v<int64_t>(crt_row[1]), -295);
2000  }
2001  {
2002  auto crt_row = rows->getNextRow(true, true);
2003  CHECK_EQ(size_t(2), crt_row.size());
2004  ASSERT_EQ(v<int64_t>(crt_row[0]), 49);
2005  ASSERT_EQ(v<int64_t>(crt_row[1]), -590);
2006  }
2007  auto empty_row = rows->getNextRow(true, true);
2008  CHECK(empty_row.empty());
2009  }
2010 }
2011 
2012 TEST(Select, MultiStepQueries) {
2014  SKIP_NO_GPU();
2015 
2016  const auto skip_intermediate_count = g_skip_intermediate_count;
2017  ScopeGuard reset_skip_intermediate_count = [&skip_intermediate_count] {
2018  g_skip_intermediate_count = skip_intermediate_count;
2019  };
2020 
2021  c("SELECT z, (z * SUM(x)) / SUM(y) + 1 FROM test GROUP BY z ORDER BY z;", dt);
2022  c("SELECT z,COUNT(*), AVG(x) / SUM(y) + 1 FROM test GROUP BY z ORDER BY z;", dt);
2023  }
2024 }
2025 
2026 TEST(Select, GroupByPushDownFilterIntoExprRange) {
2028  SKIP_NO_GPU();
2029  const auto rows = run_multiple_agg(
2030  "SELECT b, COUNT(*) AS n FROM test WHERE b GROUP BY b ORDER BY b", dt);
2031  ASSERT_EQ(
2032  size_t(1),
2033  rows->rowCount()); // Sqlite does not have a boolean type, so do this for now
2034  c("SELECT x, COUNT(*) AS n FROM test WHERE x > 7 GROUP BY x ORDER BY x", dt);
2035  c("SELECT y, COUNT(*) AS n FROM test WHERE y <= 43 GROUP BY y ORDER BY n DESC", dt);
2036  c("SELECT z, COUNT(*) AS n FROM test WHERE z <= 43 AND y > 10 GROUP BY z ORDER BY n "
2037  "DESC",
2038  dt);
2039  c("SELECT t, SUM(y) AS sum_y FROM test WHERE t < 2000 GROUP BY t ORDER BY t DESC",
2040  dt);
2041  c("SELECT t, SUM(y) AS sum_y FROM test WHERE t < 2000 GROUP BY t ORDER BY sum_y", dt);
2042  c("SELECT o, COUNT(*) as n FROM test WHERE o <= '1999-09-09' GROUP BY o ORDER BY n",
2043  dt);
2044  c("SELECT t + x, AVG(x) AS avg_x FROM test WHERE z <= 50 and t < 2000 GROUP BY t + x "
2045  "ORDER BY avg_x DESC",
2046  dt);
2047  }
2048 }
2049 
2050 TEST(Select, GroupByExprNoFilterNoAggregate) {
2052  SKIP_NO_GPU();
2053  c("SELECT x + y AS a FROM test GROUP BY a ORDER BY a;", dt);
2054  ASSERT_EQ(8,
2055  v<int64_t>(run_simple_agg("SELECT TRUNCATE(x, 0) AS foo FROM test GROUP BY "
2056  "TRUNCATE(x, 0) ORDER BY foo DESC LIMIT 1;",
2057  dt)));
2058  }
2059 }
2060 
2061 TEST(Select, DistinctProjection) {
2063  SKIP_NO_GPU();
2064  c("SELECT DISTINCT str FROM test ORDER BY str;", dt);
2065  c("SELECT DISTINCT(str), SUM(x) FROM test WHERE x > 7 GROUP BY str LIMIT 2;", dt);
2066  }
2067 }
2068 
2069 TEST(Select, Case) {
2071  SKIP_NO_GPU();
2072  c("SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 WHEN x BETWEEN 8 AND 9 THEN 2 ELSE "
2073  "3 END) FROM test;",
2074  dt);
2075  c("SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 END) FROM test;", dt);
2076  c("SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 WHEN x BETWEEN 8 AND 9 THEN 2 ELSE "
2077  "3 END) "
2078  "FROM test WHERE CASE WHEN y BETWEEN 42 AND 43 THEN 5 ELSE 4 END > 4;",
2079  dt);
2080  ASSERT_EQ(std::numeric_limits<int64_t>::min(),
2081  v<int64_t>(run_simple_agg(
2082  "SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 WHEN x BETWEEN 8 AND 9 "
2083  "THEN 2 ELSE 3 END) FROM test "
2084  "WHERE CASE WHEN y BETWEEN 44 AND 45 THEN 5 ELSE 4 END > 4;",
2085  dt)));
2086  c("SELECT CASE WHEN x + y > 50 THEN 77 ELSE 88 END AS foo, COUNT(*) FROM test GROUP "
2087  "BY foo ORDER BY foo;",
2088  dt);
2089  ASSERT_EQ(std::numeric_limits<double>::min(),
2090  v<double>(run_simple_agg(
2091  "SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1.1 WHEN x BETWEEN 8 AND "
2092  "9 THEN 2.2 ELSE 3.3 END) FROM "
2093  "test WHERE CASE WHEN y BETWEEN 44 AND 45 THEN 5.1 ELSE 3.9 END > 4;",
2094  dt)));
2095  c("SELECT CASE WHEN x BETWEEN 1 AND 3 THEN 'oops 1' WHEN x BETWEEN 4 AND 6 THEN "
2096  "'oops 2' ELSE real_str END c "
2097  "FROM "
2098  "test ORDER BY c ASC;",
2099  dt);
2100 
2101  c("SELECT CASE WHEN x BETWEEN 1 AND 3 THEN 'oops 1' WHEN x BETWEEN 4 AND 6 THEN "
2102  "'oops 2' ELSE str END c FROM "
2103  "test "
2104  "ORDER BY c ASC;",
2105  dt);
2106  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN "
2107  "'eight' ELSE 'ooops' END c FROM "
2108  "test ORDER BY c ASC;",
2109  dt);
2110  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN "
2111  "real_str ELSE 'ooops' END AS g "
2112  "FROM test ORDER BY g ASC;",
2113  dt);
2114  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN str "
2115  "ELSE 'ooops' END c FROM test "
2116  "ORDER BY c ASC;",
2117  dt);
2118  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN "
2119  "'eight' ELSE 'ooops' END c FROM "
2120  "test ORDER BY c ASC;",
2121  dt);
2122  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN str WHEN x BETWEEN 7 AND 10 THEN 'eight' "
2123  "ELSE 'ooops' END AS g, "
2124  "COUNT(*) FROM test GROUP BY g ORDER BY g;",
2125  dt);
2126  c("SELECT y AS key0, SUM(CASE WHEN x > 7 THEN x / (x - 7) ELSE 99 END) FROM test "
2127  "GROUP BY key0 ORDER BY key0;",
2128  dt);
2129  c("SELECT CASE WHEN str IN ('str1', 'str3', 'str8') THEN 'foo' WHEN str IN ('str2', "
2130  "'str4', 'str9') THEN 'bar' "
2131  "ELSE 'baz' END AS bucketed_str, COUNT(*) AS n FROM query_rewrite_test GROUP BY "
2132  "bucketed_str ORDER BY n "
2133  "DESC;",
2134  dt);
2135  c("SELECT CASE WHEN y > 40 THEN x END c, x FROM test ORDER BY c ASC;", dt);
2136  c("SELECT COUNT(CASE WHEN str = 'foo' THEN 1 END) FROM test;", dt);
2137  c("SELECT COUNT(CASE WHEN str = 'foo' THEN 1 ELSE NULL END) FROM test;", dt);
2138  c("SELECT CASE WHEN x BETWEEN 1 AND 3 THEN y ELSE y END AS foobar FROM test ORDER BY "
2139  "foobar DESC;",
2140  dt);
2141  c("SELECT x, AVG(CASE WHEN y BETWEEN 41 AND 42 THEN y END) FROM test GROUP BY x "
2142  "ORDER BY x;",
2143  dt);
2144  c("SELECT x, SUM(CASE WHEN y BETWEEN 41 AND 42 THEN y END) FROM test GROUP BY x "
2145  "ORDER BY x;",
2146  dt);
2147  c("SELECT x, COUNT(CASE WHEN y BETWEEN 41 AND 42 THEN y END) FROM test GROUP BY x "
2148  "ORDER BY x;",
2149  dt);
2150  c("SELECT CASE WHEN x > 8 THEN 'oops' ELSE 'ok' END FROM test LIMIT 1;", dt);
2151  c("SELECT CASE WHEN x < 9 THEN 'ok' ELSE 'oops' END FROM test LIMIT 1;", dt);
2152  c("SELECT CASE WHEN str IN ('foo', 'bar') THEN str END key1, COUNT(*) FROM test "
2153  "GROUP BY str HAVING key1 IS NOT "
2154  "NULL ORDER BY key1;",
2155  dt);
2156 
2157  c("SELECT CASE WHEN str IN ('foo') THEN 'FOO' WHEN str IN ('bar') THEN 'BAR' ELSE "
2158  "'BAZ' END AS g, COUNT(*) "
2159  "FROM test GROUP BY g ORDER BY g DESC;",
2160  dt);
2161  c("SELECT x, COUNT(case when y = 42 then 1 else 0 end) AS n1, COUNT(*) AS n2 FROM "
2162  "test GROUP BY x ORDER BY n2 "
2163  "DESC;",
2164  dt);
2165  c("SELECT CASE WHEN test.str = 'foo' THEN 'foo' ELSE test.str END AS g FROM test "
2166  "GROUP BY g ORDER BY g ASC;",
2167  dt);
2168  c("SELECT COUNT(*) FROM test WHERE CASE WHEN x > 8 THEN 'oops' END = 'oops' OR CASE "
2169  "WHEN x > 8 THEN 'oops' END = 'oops';",
2170  dt);
2171  ASSERT_EQ(
2172  int64_t(1418428800),
2173  v<int64_t>(run_simple_agg(
2174  "SELECT CASE WHEN 1 > 0 THEN DATE_TRUNC(day, m) ELSE DATE_TRUNC(year, m) END "
2175  "AS date_bin FROM test GROUP BY date_bin;",
2176  dt)));
2177  ASSERT_EQ(
2178  int64_t(1388534400),
2179  v<int64_t>(run_simple_agg(
2180  "SELECT CASE WHEN 1 < 0 THEN DATE_TRUNC(day, m) ELSE DATE_TRUNC(year, m) END "
2181  "AS date_bin FROM test GROUP BY date_bin;",
2182  dt)));
2183  c("SELECT COUNT(CASE WHEN str IN ('foo', 'bar') THEN 'foo_bar' END) from test;", dt);
2184  ASSERT_EQ(
2185  int64_t(1),
2186  v<int64_t>(run_simple_agg(
2187  "SELECT MIN(CASE WHEN x BETWEEN 7 AND 8 THEN true ELSE false END) FROM test;",
2188  dt)));
2189  ASSERT_EQ(
2190  int64_t(0),
2191  v<int64_t>(run_simple_agg(
2192  "SELECT MIN(CASE WHEN x BETWEEN 6 AND 7 THEN true ELSE false END) FROM test;",
2193  dt)));
2194  c("SELECT CASE WHEN test.str in ('boo', 'simple', 'case', 'not', 'much', 'to', "
2195  "'see', 'foo_in_case', 'foo', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', "
2196  "'k', 'l') THEN 'foo_in_case' ELSE test.str END AS g FROM test GROUP BY g ORDER BY "
2197  "g ASC;",
2198  dt);
2199  c("SELECT CASE WHEN shared_dict is null THEN 'hello' ELSE 'world' END key0, count(*) "
2200  "val FROM test GROUP BY key0 ORDER BY val;",
2201  dt);
2202  c("WITH distinct_x AS (SELECT x FROM test GROUP BY x) SELECT SUM(CASE WHEN x = 7 "
2203  "THEN -32767 ELSE -1 END) FROM distinct_x",
2204  dt);
2205  c("WITH distinct_x AS (SELECT x FROM test GROUP BY x) SELECT AVG(CASE WHEN x = 7 "
2206  "THEN -32767 ELSE -1 END) FROM distinct_x",
2207  dt);
2208  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN '1' WHEN x BETWEEN 8 AND 10 THEN '2' ELSE "
2209  "real_str END AS c FROM test WHERE y IN (43) ORDER BY c ASC;",
2210  dt);
2211 
2212  const auto constrained_by_in_threshold_state = g_constrained_by_in_threshold;
2214  ScopeGuard reset_constrained_by_in_threshold = [&constrained_by_in_threshold_state] {
2215  g_constrained_by_in_threshold = constrained_by_in_threshold_state;
2216  };
2217  c("SELECT fixed_str AS key0, str as key1, count(*) as val FROM test WHERE "
2218  "((fixed_str IN (SELECT fixed_str FROM test GROUP BY fixed_str))) GROUP BY key0, "
2219  "key1 ORDER BY val desc;",
2220  dt);
2221  }
2222 }
2223 
2224 TEST(Select, Strings) {
2226  SKIP_NO_GPU();
2227 
2228  c("SELECT str, COUNT(*) FROM test GROUP BY str HAVING COUNT(*) > 5 ORDER BY str;",
2229  dt);
2230  c("SELECT str, COUNT(*) FROM test WHERE str = 'bar' GROUP BY str HAVING COUNT(*) > 4 "
2231  "ORDER BY str;",
2232  dt);
2233  c("SELECT str, COUNT(*) FROM test WHERE str = 'bar' GROUP BY str HAVING COUNT(*) > 5 "
2234  "ORDER BY str;",
2235  dt);
2236  c("SELECT str, COUNT(*) FROM test where str IS NOT NULL GROUP BY str ORDER BY str;",
2237  dt);
2238  c("SELECT COUNT(*) FROM test WHERE str IS NULL;", dt);
2239  c("SELECT COUNT(*) FROM test WHERE str IS NOT NULL;", dt);
2240  c("SELECT COUNT(*) FROM test WHERE ss IS NULL;", dt);
2241  c("SELECT COUNT(*) FROM test WHERE ss IS NOT NULL;", dt);
2242  c("SELECT COUNT(*) FROM test WHERE str LIKE '%%%';", dt);
2243  c("SELECT COUNT(*) FROM test WHERE str LIKE 'ba%';", dt);
2244  c("SELECT COUNT(*) FROM test WHERE str LIKE '%eal_bar';", dt);
2245  c("SELECT COUNT(*) FROM test WHERE str LIKE '%ba%';", dt);
2246  c("SELECT * FROM test WHERE str LIKE '%' ORDER BY x ASC, y ASC;", dt);
2247  c("SELECT * FROM test WHERE str LIKE 'f%%' ORDER BY x ASC, y ASC;", dt);
2248  c("SELECT * FROM test WHERE str LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2249  c("SELECT * FROM test WHERE ss LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2250  c("SELECT * FROM test WHERE str LIKE '@f%%' ESCAPE '@' ORDER BY x ASC, y ASC;", dt);
2251  c("SELECT COUNT(*) FROM test WHERE str LIKE 'ba_' or str LIKE 'fo_';", dt);
2252  c("SELECT COUNT(*) FROM test WHERE str IS NULL;", dt);
2253  c("SELECT COUNT(*) FROM test WHERE str IS NOT NULL;", dt);
2254  c("SELECT COUNT(*) FROM test WHERE str > 'bar';", dt);
2255  c("SELECT COUNT(*) FROM test WHERE str > 'fo';", dt);
2256  c("SELECT COUNT(*) FROM test WHERE str >= 'bar';", dt);
2257  c("SELECT COUNT(*) FROM test WHERE 'bar' < str;", dt);
2258  c("SELECT COUNT(*) FROM test WHERE 'fo' < str;", dt);
2259  c("SELECT COUNT(*) FROM test WHERE 'bar' <= str;", dt);
2260  c("SELECT COUNT(*) FROM test WHERE str = 'bar';", dt);
2261  c("SELECT COUNT(*) FROM test WHERE 'bar' = str;", dt);
2262  c("SELECT COUNT(*) FROM test WHERE str <> 'bar';", dt);
2263  c("SELECT COUNT(*) FROM test WHERE 'bar' <> str;", dt);
2264  c("SELECT COUNT(*) FROM test WHERE str = 'foo' OR str = 'bar';", dt);
2265  // The following tests throw Cast from dictionary-encoded string to none-encoded not
2266  // supported for distributed queries in distributed mode
2267  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE str = real_str;", dt));
2268  c("SELECT COUNT(*) FROM test WHERE str <> str;", dt);
2269  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE ss <> str;", dt));
2270  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE ss = str;", dt));
2271  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE LENGTH(str) = 3;", dt));
2272  c("SELECT fixed_str, COUNT(*) FROM test GROUP BY fixed_str HAVING COUNT(*) > 5 ORDER "
2273  "BY fixed_str;",
2274  dt);
2275  c("SELECT fixed_str, COUNT(*) FROM test WHERE fixed_str = 'bar' GROUP BY fixed_str "
2276  "HAVING COUNT(*) > 4 ORDER BY "
2277  "fixed_str;",
2278  dt);
2279  c("SELECT COUNT(*) FROM emp WHERE ename LIKE 'D%%' OR ename = 'Julia';", dt);
2281  ASSERT_EQ(2 * g_num_rows,
2282  v<int64_t>(run_simple_agg(
2283  "SELECT COUNT(*) FROM test WHERE CHAR_LENGTH(str) = 3;",
2284  dt)))); // Cast from dictionary-encoded string to none-encoded not
2285  // supported for distributed queries
2286  ASSERT_EQ(g_num_rows,
2287  v<int64_t>(run_simple_agg(
2288  "SELECT COUNT(*) FROM test WHERE str ILIKE 'f%%';", dt)));
2289  ASSERT_EQ(g_num_rows,
2290  v<int64_t>(run_simple_agg(
2291  "SELECT COUNT(*) FROM test WHERE (str ILIKE 'f%%');", dt)));
2292  ASSERT_EQ(g_num_rows,
2293  v<int64_t>(run_simple_agg(
2294  "SELECT COUNT(*) FROM test WHERE ( str ILIKE 'f%%' );", dt)));
2295  ASSERT_EQ(0,
2296  v<int64_t>(run_simple_agg(
2297  "SELECT COUNT(*) FROM test WHERE str ILIKE 'McDonald''s';", dt)));
2298  ASSERT_EQ("foo",
2299  boost::get<std::string>(v<NullableString>(run_simple_agg(
2300  "SELECT str FROM test WHERE REGEXP_LIKE(str, '^f.?.+');", dt))));
2301  ASSERT_EQ("bar",
2302  boost::get<std::string>(v<NullableString>(run_simple_agg(
2303  "SELECT str FROM test WHERE REGEXP_LIKE(str, '^[a-z]+r$');", dt))));
2304  ASSERT_EQ(2 * g_num_rows,
2305  v<int64_t>(run_simple_agg(
2306  "SELECT COUNT(*) FROM test WHERE str REGEXP '.*';", dt)));
2307  ASSERT_EQ(2 * g_num_rows,
2308  v<int64_t>(run_simple_agg(
2309  "SELECT COUNT(*) FROM test WHERE str REGEXP '...';", dt)));
2310  ASSERT_EQ(2 * g_num_rows,
2311  v<int64_t>(run_simple_agg(
2312  "SELECT COUNT(*) FROM test WHERE str REGEXP '.+.+.+';", dt)));
2313  ASSERT_EQ(2 * g_num_rows,
2314  v<int64_t>(run_simple_agg(
2315  "SELECT COUNT(*) FROM test WHERE str REGEXP '.?.?.?';", dt)));
2316  ASSERT_EQ(2 * g_num_rows,
2317  v<int64_t>(run_simple_agg(
2318  "SELECT COUNT(*) FROM test WHERE str REGEXP 'ba.' or str REGEXP 'fo.';",
2319  dt)));
2320  ASSERT_EQ(2 * g_num_rows,
2321  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE "
2322  "REGEXP_LIKE(str, 'ba.') or str REGEXP 'fo.?';",
2323  dt)));
2324  ASSERT_EQ(2 * g_num_rows,
2325  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE str REGEXP "
2326  "'ba.' or REGEXP_LIKE(str, 'fo.+');",
2327  dt)));
2328  ASSERT_EQ(g_num_rows,
2329  v<int64_t>(run_simple_agg(
2330  "SELECT COUNT(*) FROM test WHERE str REGEXP 'ba.+';", dt)));
2331  ASSERT_EQ(g_num_rows,
2332  v<int64_t>(run_simple_agg(
2333  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(str, '.?ba.*');", dt)));
2334  ASSERT_EQ(
2335  2 * g_num_rows,
2336  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE "
2337  "REGEXP_LIKE(str,'ba.') or REGEXP_LIKE(str, 'fo.+');",
2338  dt)));
2339  ASSERT_EQ(2 * g_num_rows,
2340  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE str REGEXP "
2341  "'ba.' or REGEXP_LIKE(str, 'fo.+');",
2342  dt)));
2343  ASSERT_EQ(2 * g_num_rows,
2344  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE "
2345  "REGEXP_LIKE(str, 'ba.') or str REGEXP 'fo.?';",
2346  dt)));
2347  ASSERT_EQ(2 * g_num_rows,
2348  v<int64_t>(run_simple_agg(
2349  "SELECT COUNT(*) FROM test WHERE str REGEXP 'ba.' or str REGEXP 'fo.';",
2350  dt)));
2351  }
2352 }
2353 
2354 TEST(Select, SharedDictionary) {
2356  SKIP_NO_GPU();
2357 
2358  c("SELECT shared_dict, COUNT(*) FROM test GROUP BY shared_dict HAVING COUNT(*) > 5 "
2359  "ORDER BY shared_dict;",
2360  dt);
2361  c("SELECT shared_dict, COUNT(*) FROM test WHERE shared_dict = 'bar' GROUP BY "
2362  "shared_dict HAVING COUNT(*) > 4 ORDER "
2363  "BY shared_dict;",
2364  dt);
2365  c("SELECT shared_dict, COUNT(*) FROM test WHERE shared_dict = 'bar' GROUP BY "
2366  "shared_dict HAVING COUNT(*) > 5 ORDER "
2367  "BY shared_dict;",
2368  dt);
2369  c("SELECT shared_dict, COUNT(*) FROM test where shared_dict IS NOT NULL GROUP BY "
2370  "shared_dict ORDER BY shared_dict;",
2371  dt);
2372  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NULL;", dt);
2373  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NOT NULL;", dt);
2374  c("SELECT COUNT(*) FROM test WHERE ss IS NULL;", dt);
2375  c("SELECT COUNT(*) FROM test WHERE ss IS NOT NULL;", dt);
2376  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE '%%%';", dt);
2377  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE 'ba%';", dt);
2378  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE '%eal_bar';", dt);
2379  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE '%ba%';", dt);
2380  c("SELECT * FROM test WHERE shared_dict LIKE '%' ORDER BY x ASC, y ASC;", dt);
2381  c("SELECT * FROM test WHERE shared_dict LIKE 'f%%' ORDER BY x ASC, y ASC;", dt);
2382  c("SELECT * FROM test WHERE shared_dict LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2383  c("SELECT * FROM test WHERE ss LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2384  c("SELECT * FROM test WHERE shared_dict LIKE '@f%%' ESCAPE '@' ORDER BY x ASC, y "
2385  "ASC;",
2386  dt);
2387  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE 'ba_' or shared_dict LIKE 'fo_';",
2388  dt);
2389  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NULL;", dt);
2390  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NOT NULL;", dt);
2391  c("SELECT COUNT(*) FROM test WHERE shared_dict = 'bar';", dt);
2392  c("SELECT COUNT(*) FROM test WHERE 'bar' = shared_dict;", dt);
2393  c("SELECT COUNT(*) FROM test WHERE shared_dict <> 'bar';", dt);
2394  c("SELECT COUNT(*) FROM test WHERE 'bar' <> shared_dict;", dt);
2395  c("SELECT COUNT(*) FROM test WHERE shared_dict = 'foo' OR shared_dict = 'bar';", dt);
2396  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE shared_dict = real_str;", dt));
2397  c("SELECT COUNT(*) FROM test WHERE shared_dict <> shared_dict;", dt);
2398  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'bar';", dt);
2399  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'fo';", dt);
2400  c("SELECT COUNT(*) FROM test WHERE shared_dict >= 'bar';", dt);
2401  c("SELECT COUNT(*) FROM test WHERE 'bar' < shared_dict;", dt);
2402  c("SELECT COUNT(*) FROM test WHERE 'fo' < shared_dict;", dt);
2403  c("SELECT COUNT(*) FROM test WHERE 'bar' <= shared_dict;", dt);
2404  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE LENGTH(shared_dict) = 3;", dt));
2405 
2406  EXPECT_THROW(run_ddl_statement("CREATE TABLE t1(a text, b text, SHARED DICTIONARY "
2407  "(b) REFERENCES t1(a), SHARED "
2408  "DICTIONARY (a) REFERENCES t1(b));"),
2409  std::runtime_error);
2410 
2411  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2412  15,
2413  v<int64_t>(run_simple_agg(
2414  "SELECT COUNT(*) FROM test WHERE CHAR_LENGTH(shared_dict) = 3;", dt))));
2415  ASSERT_EQ(g_num_rows,
2416  v<int64_t>(run_simple_agg(
2417  "SELECT COUNT(*) FROM test WHERE shared_dict ILIKE 'f%%';", dt)));
2418  ASSERT_EQ(g_num_rows,
2419  v<int64_t>(run_simple_agg(
2420  "SELECT COUNT(*) FROM test WHERE (shared_dict ILIKE 'f%%');", dt)));
2421  ASSERT_EQ(g_num_rows,
2422  v<int64_t>(run_simple_agg(
2423  "SELECT COUNT(*) FROM test WHERE ( shared_dict ILIKE 'f%%' );", dt)));
2424  ASSERT_EQ(
2425  0,
2426  v<int64_t>(run_simple_agg(
2427  "SELECT COUNT(*) FROM test WHERE shared_dict ILIKE 'McDonald''s';", dt)));
2428 
2429  ASSERT_EQ(
2430  "foo",
2431  boost::get<std::string>(v<NullableString>(run_simple_agg(
2432  "SELECT shared_dict FROM test WHERE REGEXP_LIKE(shared_dict, '^f.?.+');",
2433  dt))));
2434  ASSERT_EQ(
2435  "baz",
2436  boost::get<std::string>(v<NullableString>(run_simple_agg(
2437  "SELECT shared_dict FROM test WHERE REGEXP_LIKE(shared_dict, '^[a-z]+z$');",
2438  dt))));
2439 
2440  ASSERT_EQ(15,
2441  v<int64_t>(run_simple_agg(
2442  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '.*';", dt)));
2443  ASSERT_EQ(15,
2444  v<int64_t>(run_simple_agg(
2445  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '...';", dt)));
2446  ASSERT_EQ(15,
2447  v<int64_t>(run_simple_agg(
2448  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '.+.+.+';", dt)));
2449  ASSERT_EQ(15,
2450  v<int64_t>(run_simple_agg(
2451  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '.?.?.?';", dt)));
2452 
2453  ASSERT_EQ(15,
2454  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict "
2455  "REGEXP 'ba.' or shared_dict REGEXP 'fo.';",
2456  dt)));
2457  ASSERT_EQ(15,
2458  v<int64_t>(run_simple_agg(
2459  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict, 'ba.') or "
2460  "shared_dict REGEXP 'fo.?';",
2461  dt)));
2462  ASSERT_EQ(
2463  15,
2464  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict REGEXP "
2465  "'ba.' or REGEXP_LIKE(shared_dict, 'fo.+');",
2466  dt)));
2467  ASSERT_EQ(5,
2468  v<int64_t>(run_simple_agg(
2469  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP 'ba.+';", dt)));
2470  ASSERT_EQ(
2471  5,
2472  v<int64_t>(run_simple_agg(
2473  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict, '.?ba.*');", dt)));
2474  ASSERT_EQ(15,
2475  v<int64_t>(run_simple_agg(
2476  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict,'ba.') or "
2477  "REGEXP_LIKE(shared_dict, 'fo.+');",
2478  dt)));
2479  ASSERT_EQ(
2480  15,
2481  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict REGEXP "
2482  "'ba.' or REGEXP_LIKE(shared_dict, 'fo.+');",
2483  dt)));
2484  ASSERT_EQ(15,
2485  v<int64_t>(run_simple_agg(
2486  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict, 'ba.') or "
2487  "shared_dict REGEXP 'fo.?';",
2488  dt)));
2489  ASSERT_EQ(15,
2490  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict "
2491  "REGEXP 'ba.' or shared_dict REGEXP 'fo.';",
2492  dt)));
2493  }
2494 }
2495 
2498  SKIP_NO_GPU();
2499  c("SELECT COUNT(*) FROM test WHERE str = 'ba';", dt);
2500  c("SELECT COUNT(*) FROM test WHERE str <> 'ba';", dt);
2501 
2502  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'ba';", dt);
2503  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'bar';", dt);
2504  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'baf';", dt);
2505  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'baz';", dt);
2506  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'bbz';", dt);
2507  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'foo';", dt);
2508  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'foon';", dt);
2509 
2510  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'ba';", dt);
2511  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'bar';", dt);
2512  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'baf';", dt);
2513  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'baz';", dt);
2514  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'bbz';", dt);
2515  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'foo';", dt);
2516  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'foon';", dt);
2517 
2518  c("SELECT COUNT(*) FROM test WHERE real_str <= 'ba';", dt);
2519  c("SELECT COUNT(*) FROM test WHERE real_str <= 'bar';", dt);
2520  c("SELECT COUNT(*) FROM test WHERE real_str <= 'baf';", dt);
2521  c("SELECT COUNT(*) FROM test WHERE real_str <= 'baz';", dt);
2522  c("SELECT COUNT(*) FROM test WHERE real_str <= 'bbz';", dt);
2523  c("SELECT COUNT(*) FROM test WHERE real_str <= 'foo';", dt);
2524  c("SELECT COUNT(*) FROM test WHERE real_str <= 'foon';", dt);
2525 
2526  c("SELECT COUNT(*) FROM test WHERE real_str >= 'ba';", dt);
2527  c("SELECT COUNT(*) FROM test WHERE real_str >= 'bar';", dt);
2528  c("SELECT COUNT(*) FROM test WHERE real_str >= 'baf';", dt);
2529  c("SELECT COUNT(*) FROM test WHERE real_str >= 'baz';", dt);
2530  c("SELECT COUNT(*) FROM test WHERE real_str >= 'bbz';", dt);
2531  c("SELECT COUNT(*) FROM test WHERE real_str >= 'foo';", dt);
2532  c("SELECT COUNT(*) FROM test WHERE real_str >= 'foon';", dt);
2533 
2534  c("SELECT COUNT(*) FROM test WHERE real_str <= 'รครข';", dt);
2535 
2536  c("SELECT COUNT(*) FROM test WHERE 'ba' < shared_dict;", dt);
2537  c("SELECT COUNT(*) FROM test WHERE 'bar' < shared_dict;", dt);
2538  c("SELECT COUNT(*) FROM test WHERE 'ba' > shared_dict;", dt);
2539  c("SELECT COUNT(*) FROM test WHERE 'bar' > shared_dict;", dt);
2540 
2541  EXPECT_THROW(run_multiple_agg("SELECT COUNT(*) FROM test, test_inner WHERE "
2542  "test.shared_dict < test_inner.str",
2543  dt),
2544  std::runtime_error);
2545  }
2546 }
2547 
2548 TEST(Select, StringsNoneEncoding) {
2550  SKIP_NO_GPU();
2551 
2552  c("SELECT COUNT(*) FROM test WHERE real_str LIKE 'real_%%%';", dt);
2553  c("SELECT COUNT(*) FROM test WHERE real_str LIKE 'real_ba%';", dt);
2554  c("SELECT COUNT(*) FROM test WHERE real_str LIKE '%eal_bar';", dt);
2555  c("SELECT * FROM test_lots_cols WHERE real_str LIKE '%' ORDER BY x0 ASC;", dt);
2556  c("SELECT * FROM test WHERE real_str LIKE '%' ORDER BY x ASC, y ASC;", dt);
2557  c("SELECT * FROM test WHERE real_str LIKE 'real_f%%' ORDER BY x ASC, y ASC;", dt);
2558  c("SELECT * FROM test WHERE real_str LIKE 'real_f%\%' ORDER BY x ASC, y ASC;", dt);
2559  c("SELECT * FROM test WHERE real_str LIKE 'real_@f%%' ESCAPE '@' ORDER BY x ASC, y "
2560  "ASC;",
2561  dt);
2562  c("SELECT COUNT(*) FROM test WHERE real_str LIKE 'real_ba_' or real_str LIKE "
2563  "'real_fo_';",
2564  dt);
2565  c("SELECT COUNT(*) FROM test WHERE real_str IS NULL;", dt);
2566  c("SELECT COUNT(*) FROM test WHERE real_str IS NOT NULL;", dt);
2567  c("SELECT COUNT(*) FROM test WHERE real_str > 'real_bar';", dt);
2568  c("SELECT COUNT(*) FROM test WHERE real_str > 'real_fo';", dt);
2569  c("SELECT COUNT(*) FROM test WHERE real_str >= 'real_bar';", dt);
2570  c("SELECT COUNT(*) FROM test WHERE 'real_bar' < real_str;", dt);
2571  c("SELECT COUNT(*) FROM test WHERE 'real_fo' < real_str;", dt);
2572  c("SELECT COUNT(*) FROM test WHERE 'real_bar' <= real_str;", dt);
2573  c("SELECT COUNT(*) FROM test WHERE real_str = 'real_bar';", dt);
2574  c("SELECT COUNT(*) FROM test WHERE 'real_bar' = real_str;", dt);
2575  c("SELECT COUNT(*) FROM test WHERE real_str <> 'real_bar';", dt);
2576  c("SELECT COUNT(*) FROM test WHERE 'real_bar' <> real_str;", dt);
2577  c("SELECT COUNT(*) FROM test WHERE real_str = 'real_foo' OR real_str = 'real_bar';",
2578  dt);
2579  c("SELECT COUNT(*) FROM test WHERE real_str = real_str;", dt);
2580  c("SELECT COUNT(*) FROM test WHERE real_str <> real_str;", dt);
2581  ASSERT_EQ(g_num_rows,
2582  v<int64_t>(run_simple_agg(
2583  "SELECT COUNT(*) FROM test WHERE real_str ILIKE 'rEaL_f%%';", dt)));
2584  c("SELECT COUNT(*) FROM test WHERE LENGTH(real_str) = 8;", dt);
2585  ASSERT_EQ(2 * g_num_rows,
2586  v<int64_t>(run_simple_agg(
2587  "SELECT COUNT(*) FROM test WHERE CHAR_LENGTH(real_str) = 8;", dt)));
2588  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2589  2 * g_num_rows,
2590  v<int64_t>(run_simple_agg(
2591  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(real_str,'real_.*.*.*');",
2592  dt))));
2593  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2594  g_num_rows,
2595  v<int64_t>(run_simple_agg(
2596  "SELECT COUNT(*) FROM test WHERE real_str REGEXP 'real_ba.*';", dt))));
2598  ASSERT_EQ(2 * g_num_rows,
2599  v<int64_t>(run_simple_agg(
2600  "SELECT COUNT(*) FROM test WHERE real_str REGEXP '.*';", dt))));
2601  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2602  g_num_rows,
2603  v<int64_t>(run_simple_agg(
2604  "SELECT COUNT(*) FROM test WHERE real_str REGEXP 'real_f.*.*';", dt))));
2605  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2606  0,
2607  v<int64_t>(run_simple_agg(
2608  "SELECT COUNT(*) FROM test WHERE real_str REGEXP 'real_f.+\%';", dt))));
2609  EXPECT_THROW(
2610  run_multiple_agg("SELECT COUNT(*) FROM test WHERE real_str LIKE str;", dt),
2611  std::runtime_error);
2612  EXPECT_THROW(run_multiple_agg(
2613  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(real_str, str);", dt),
2614  std::runtime_error);
2615  }
2616 }
2617 
2618 namespace {
2619 
2620 void check_date_trunc_groups(const ResultSet& rows) {
2621  {
2622  const auto crt_row = rows.getNextRow(true, true);
2623  CHECK(!crt_row.empty());
2624  CHECK_EQ(size_t(3), crt_row.size());
2625  const auto sv0 = v<int64_t>(crt_row[0]);
2626  ASSERT_EQ(int64_t(936144000), sv0);
2627  const auto sv1 = boost::get<std::string>(v<NullableString>(crt_row[1]));
2628  ASSERT_EQ("foo", sv1);
2629  const auto sv2 = v<int64_t>(crt_row[2]);
2630  ASSERT_EQ(g_num_rows, sv2);
2631  }
2632  {
2633  const auto crt_row = rows.getNextRow(true, true);
2634  CHECK(!crt_row.empty());
2635  CHECK_EQ(size_t(3), crt_row.size());
2636  const auto sv0 = v<int64_t>(crt_row[0]);
2637  ASSERT_EQ(inline_int_null_val(rows.getColType(0)), sv0);
2638  const auto sv1 = boost::get<std::string>(v<NullableString>(crt_row[1]));
2639  ASSERT_EQ("bar", sv1);
2640  const auto sv2 = v<int64_t>(crt_row[2]);
2641  ASSERT_EQ(g_num_rows / 2, sv2);
2642  }
2643  {
2644  const auto crt_row = rows.getNextRow(true, true);
2645  CHECK(!crt_row.empty());
2646  CHECK_EQ(size_t(3), crt_row.size());
2647  const auto sv0 = v<int64_t>(crt_row[0]);
2648  ASSERT_EQ(int64_t(936144000), sv0);
2649  const auto sv1 = boost::get<std::string>(v<NullableString>(crt_row[1]));
2650  ASSERT_EQ("baz", sv1);
2651  const auto sv2 = v<int64_t>(crt_row[2]);
2652  ASSERT_EQ(g_num_rows / 2, sv2);
2653  }
2654  const auto crt_row = rows.getNextRow(true, true);
2655  CHECK(crt_row.empty());
2656 }
2657 
2658 void check_one_date_trunc_group(const ResultSet& rows, const int64_t ref_ts) {
2659  const auto crt_row = rows.getNextRow(true, true);
2660  ASSERT_EQ(size_t(1), crt_row.size());
2661  const auto actual_ts = v<int64_t>(crt_row[0]);
2662  ASSERT_EQ(ref_ts, actual_ts);
2663  const auto empty_row = rows.getNextRow(true, true);
2664  ASSERT_TRUE(empty_row.empty());
2665 }
2666 
2667 void check_one_date_trunc_group_with_agg(const ResultSet& rows,
2668  const int64_t ref_ts,
2669  const int64_t ref_agg) {
2670  const auto crt_row = rows.getNextRow(true, true);
2671  ASSERT_EQ(size_t(2), crt_row.size());
2672  const auto actual_ts = v<int64_t>(crt_row[0]);
2673  ASSERT_EQ(ref_ts, actual_ts);
2674  const auto actual_agg = v<int64_t>(crt_row[1]);
2675  ASSERT_EQ(ref_agg, actual_agg);
2676  const auto empty_row = rows.getNextRow(true, true);
2677  ASSERT_TRUE(empty_row.empty());
2678 }
2679 
2680 } // namespace
2681 
2682 TEST(Select, Time) {
2684  SKIP_NO_GPU();
2685  // check DATE Formats
2686  ASSERT_EQ(
2687  g_num_rows + g_num_rows / 2,
2688  v<int64_t>(run_simple_agg(
2689  "SELECT COUNT(*) FROM test WHERE CAST('1999-09-10' AS DATE) > o;", dt)));
2690  ASSERT_EQ(
2691  g_num_rows + g_num_rows / 2,
2692  v<int64_t>(run_simple_agg(
2693  "SELECT COUNT(*) FROM test WHERE CAST('10/09/1999' AS DATE) > o;", dt)));
2694  ASSERT_EQ(g_num_rows + g_num_rows / 2,
2695  v<int64_t>(run_simple_agg(
2696  "SELECT COUNT(*) FROM test WHERE CAST('10-Sep-99' AS DATE) > o;", dt)));
2697  ASSERT_EQ(
2698  g_num_rows + g_num_rows / 2,
2699  v<int64_t>(run_simple_agg(
2700  "SELECT COUNT(*) FROM test WHERE CAST('31/Oct/2013' AS DATE) > o;", dt)));
2701  // check TIME FORMATS
2702  ASSERT_EQ(2 * g_num_rows,
2703  v<int64_t>(run_simple_agg(
2704  "SELECT COUNT(*) FROM test WHERE CAST('15:13:15' AS TIME) > n;", dt)));
2705  ASSERT_EQ(2 * g_num_rows,
2706  v<int64_t>(run_simple_agg(
2707  "SELECT COUNT(*) FROM test WHERE CAST('151315' AS TIME) > n;", dt)));
2708 
2709  ASSERT_EQ(
2710  g_num_rows + g_num_rows / 2,
2711  v<int64_t>(run_simple_agg(
2712  "SELECT COUNT(*) FROM test WHERE CAST('1999-09-10' AS DATE) > o;", dt)));
2713  ASSERT_EQ(
2714  0,
2715  v<int64_t>(run_simple_agg(
2716  "SELECT COUNT(*) FROM test WHERE CAST('1999-09-10' AS DATE) <= o;", dt)));
2717  ASSERT_EQ(2 * g_num_rows,
2718  v<int64_t>(run_simple_agg(
2719  "SELECT COUNT(*) FROM test WHERE CAST('15:13:15' AS TIME) > n;", dt)));
2720  ASSERT_EQ(0,
2721  v<int64_t>(run_simple_agg(
2722  "SELECT COUNT(*) FROM test WHERE CAST('15:13:15' AS TIME) <= n;", dt)));
2723  cta("SELECT DATETIME('NOW') FROM test limit 1;", dt);
2724  // these next tests work because all dates are before now 2015-12-8 17:00:00
2725  ASSERT_EQ(
2726  2 * g_num_rows,
2727  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m < NOW();", dt)));
2728  ASSERT_EQ(2 * g_num_rows,
2729  v<int64_t>(run_simple_agg(
2730  "SELECT COUNT(*) FROM test WHERE m > timestamp(0) '2014-12-13T000000';",
2731  dt)));
2732  ASSERT_EQ(g_num_rows + g_num_rows / 2,
2733  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE CAST(o AS "
2734  "TIMESTAMP) > timestamp(0) '1999-09-08T160000';",
2735  dt)));
2736  ASSERT_EQ(0,
2737  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE CAST(o AS "
2738  "TIMESTAMP) > timestamp(0) '1999-09-10T160000';",
2739  dt)));
2740  ASSERT_EQ(14185957950L,
2741  v<int64_t>(run_simple_agg(
2742  "SELECT MAX(EXTRACT(EPOCH FROM m) * 10) FROM test;", dt)));
2743  ASSERT_EQ(20140,
2744  v<int64_t>(run_simple_agg(
2745  "SELECT MAX(EXTRACT(YEAR FROM m) * 10) FROM test;", dt)));
2746  ASSERT_EQ(120,
2747  v<int64_t>(run_simple_agg(
2748  "SELECT MAX(EXTRACT(MONTH FROM m) * 10) FROM test;", dt)));
2749  ASSERT_EQ(140,
2750  v<int64_t>(
2751  run_simple_agg("SELECT MAX(EXTRACT(DAY FROM m) * 10) FROM test;", dt)));
2752  ASSERT_EQ(
2753  22,
2754  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(HOUR FROM m)) FROM test;", dt)));
2755  ASSERT_EQ(
2756  23,
2757  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(MINUTE FROM m)) FROM test;", dt)));
2758  ASSERT_EQ(
2759  15,
2760  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(SECOND FROM m)) FROM test;", dt)));
2761  ASSERT_EQ(
2762  6, v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(DOW FROM m)) FROM test;", dt)));
2763  ASSERT_EQ(
2764  348,
2765  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(DOY FROM m)) FROM test;", dt)));
2766  ASSERT_EQ(
2767  15,
2768  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(HOUR FROM n)) FROM test;", dt)));
2769  ASSERT_EQ(
2770  13,
2771  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(MINUTE FROM n)) FROM test;", dt)));
2772  ASSERT_EQ(
2773  14,
2774  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(SECOND FROM n)) FROM test;", dt)));
2775  ASSERT_EQ(
2776  1999,
2777  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(YEAR FROM o)) FROM test;", dt)));
2778  ASSERT_EQ(
2779  9,
2780  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(MONTH FROM o)) FROM test;", dt)));
2781  ASSERT_EQ(
2782  9, v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(DAY FROM o)) FROM test;", dt)));
2783  ASSERT_EQ(4,
2784  v<int64_t>(run_simple_agg(
2785  "SELECT EXTRACT(DOW FROM o) FROM test WHERE o IS NOT NULL;", dt)));
2786  ASSERT_EQ(252,
2787  v<int64_t>(run_simple_agg(
2788  "SELECT EXTRACT(DOY FROM o) FROM test WHERE o IS NOT NULL;", dt)));
2789  ASSERT_EQ(
2790  936835200L,
2791  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(EPOCH FROM o)) FROM test;", dt)));
2792  ASSERT_EQ(1L,
2793  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(WEEK FROM CAST('2012-01-01 "
2794  "20:15:12' AS TIMESTAMP))) FROM test limit 1;",
2795  dt)));
2796  ASSERT_EQ(10L,
2797  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(WEEK FROM CAST('2008-03-03 "
2798  "20:15:12' AS TIMESTAMP))) FROM test limit 1;",
2799  dt)));
2800  // Monday
2801  ASSERT_EQ(1L,
2802  v<int64_t>(run_simple_agg("SELECT EXTRACT(DOW FROM CAST('2008-03-03 "
2803  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2804  dt)));
2805  // Monday
2806  ASSERT_EQ(1L,
2807  v<int64_t>(run_simple_agg("SELECT EXTRACT(ISODOW FROM CAST('2008-03-03 "
2808  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2809  dt)));
2810  // Sunday
2811  ASSERT_EQ(0L,
2812  v<int64_t>(run_simple_agg("SELECT EXTRACT(DOW FROM CAST('2008-03-02 "
2813  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2814  dt)));
2815  // Sunday
2816  ASSERT_EQ(7L,
2817  v<int64_t>(run_simple_agg("SELECT EXTRACT(ISODOW FROM CAST('2008-03-02 "
2818  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2819  dt)));
2820  ASSERT_EQ(15000000000L,
2821  v<int64_t>(run_simple_agg(
2822  "SELECT EXTRACT(nanosecond from m) FROM test limit 1;", dt)));
2823  ASSERT_EQ(15000000L,
2824  v<int64_t>(run_simple_agg(
2825  "SELECT EXTRACT(microsecond from m) FROM test limit 1;", dt)));
2826  ASSERT_EQ(15000L,
2827  v<int64_t>(run_simple_agg(
2828  "SELECT EXTRACT(millisecond from m) FROM test limit 1;", dt)));
2829  ASSERT_EQ(56000000000L,
2830  v<int64_t>(run_simple_agg("SELECT EXTRACT(nanosecond from TIMESTAMP(0) "
2831  "'1999-03-14 23:34:56') FROM test limit 1;",
2832  dt)));
2833  ASSERT_EQ(56000000L,
2834  v<int64_t>(run_simple_agg("SELECT EXTRACT(microsecond from TIMESTAMP(0) "
2835  "'1999-03-14 23:34:56') FROM test limit 1;",
2836  dt)));
2837  ASSERT_EQ(56000L,
2838  v<int64_t>(run_simple_agg("SELECT EXTRACT(millisecond from TIMESTAMP(0) "
2839  "'1999-03-14 23:34:56') FROM test limit 1;",
2840  dt)));
2841  ASSERT_EQ(2005,
2842  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '2005-12-31 "
2843  "23:59:59') from test limit 1;",
2844  dt)));
2845  ASSERT_EQ(1997,
2846  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '1997-01-01 "
2847  "23:59:59') from test limit 1;",
2848  dt)));
2849  ASSERT_EQ(2006,
2850  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '2006-01-01 "
2851  "00:0:00') from test limit 1;",
2852  dt)));
2853  ASSERT_EQ(2014,
2854  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '2014-01-01 "
2855  "00:00:00') from test limit 1;",
2856  dt)));
2857 
2858  // do some DATE_TRUNC tests
2859  /*
2860  * year
2861  * month
2862  * day
2863  * hour
2864  * minute
2865  * second
2866  *
2867  * millennium
2868  * century
2869  * decade
2870  * milliseconds
2871  * microseconds
2872  * week
2873  */
2874  ASSERT_EQ(1325376000L,
2875  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(year, CAST('2012-05-08 "
2876  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2877  dt)));
2878  ASSERT_EQ(1335830400L,
2879  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(month, CAST('2012-05-08 "
2880  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2881  dt)));
2882  ASSERT_EQ(1336435200L,
2883  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(day, CAST('2012-05-08 "
2884  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2885  dt)));
2886  ASSERT_EQ(1336507200L,
2887  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(hour, CAST('2012-05-08 "
2888  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2889  dt)));
2890  ASSERT_EQ(1336508112L,
2891  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(second, CAST('2012-05-08 "
2892  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2893  dt)));
2894  ASSERT_EQ(978307200L,
2895  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millennium, CAST('2012-05-08 "
2896  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2897  dt)));
2898  ASSERT_EQ(978307200L,
2899  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(century, CAST('2012-05-08 "
2900  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2901  dt)));
2902  ASSERT_EQ(1293840000L,
2903  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('2012-05-08 "
2904  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2905  dt)));
2906  ASSERT_EQ(1336508112L,
2907  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millisecond, CAST('2012-05-08 "
2908  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2909  dt)));
2910  ASSERT_EQ(1336508112L,
2911  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(microsecond, CAST('2012-05-08 "
2912  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2913  dt)));
2914  ASSERT_EQ(1336262400L,
2915  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(week, CAST('2012-05-08 "
2916  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2917  dt)));
2918 
2919  ASSERT_EQ(-2114380800L,
2920  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(year, CAST('1903-05-08 "
2921  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2922  dt)));
2923  ASSERT_EQ(-2104012800L,
2924  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(month, CAST('1903-05-08 "
2925  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2926  dt)));
2927  ASSERT_EQ(-2103408000L,
2928  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(day, CAST('1903-05-08 "
2929  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2930  dt)));
2931  ASSERT_EQ(-2103336000L,
2932  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(hour, CAST('1903-05-08 "
2933  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2934  dt)));
2935  ASSERT_EQ(-2103335088L,
2936  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(second, CAST('1903-05-08 "
2937  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2938  dt)));
2939  ASSERT_EQ(-30578688000L,
2940  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millennium, CAST('1903-05-08 "
2941  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2942  dt)));
2943  ASSERT_EQ(-2177452800L,
2944  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(century, CAST('1903-05-08 "
2945  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2946  dt)));
2947  ASSERT_EQ(-2177452800L,
2948  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('1903-05-08 "
2949  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2950  dt)));
2951  ASSERT_EQ(-2103335088L,
2952  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millisecond, CAST('1903-05-08 "
2953  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2954  dt)));
2955  ASSERT_EQ(-2103335088L,
2956  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(microsecond, CAST('1903-05-08 "
2957  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2958  dt)));
2959  ASSERT_EQ(-2103840000L,
2960  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(week, CAST('1903-05-08 "
2961  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2962  dt)));
2963 
2964  ASSERT_EQ(31536000L,
2965  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('1972-05-08 "
2966  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2967  dt)));
2968  ASSERT_EQ(662688000L,
2969  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('2000-05-08 "
2970  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2971  dt)));
2972  // test QUARTER
2973  ASSERT_EQ(4,
2974  v<int64_t>(run_simple_agg("select EXTRACT(quarter FROM CAST('2008-11-27 "
2975  "12:12:12' AS timestamp)) FROM test limit 1;",
2976  dt)));
2977  ASSERT_EQ(1,
2978  v<int64_t>(run_simple_agg("select EXTRACT(quarter FROM CAST('2008-03-21 "
2979  "12:12:12' AS timestamp)) FROM test limit 1;",
2980  dt)));
2981  ASSERT_EQ(1199145600L,
2982  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2008-03-21 "
2983  "12:12:12' AS timestamp)) FROM test limit 1;",
2984  dt)));
2985  ASSERT_EQ(1230768000L,
2986  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2009-03-21 "
2987  "12:12:12' AS timestamp)) FROM test limit 1;",
2988  dt)));
2989  ASSERT_EQ(1254355200L,
2990  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2009-11-21 "
2991  "12:12:12' AS timestamp)) FROM test limit 1;",
2992  dt)));
2993  ASSERT_EQ(946684800L,
2994  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2000-03-21 "
2995  "12:12:12' AS timestamp)) FROM test limit 1;",
2996  dt)));
2997  ASSERT_EQ(-2208988800L,
2998  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('1900-03-21 "
2999  "12:12:12' AS timestamp)) FROM test limit 1;",
3000  dt)));
3001  // test DATE format processing
3002  ASSERT_EQ(1434844800L,
3003  v<int64_t>(run_simple_agg(
3004  "select CAST('2015-06-21' AS DATE) FROM test limit 1;", dt)));
3005  ASSERT_EQ(
3006  g_num_rows + g_num_rows / 2,
3007  v<int64_t>(run_simple_agg(
3008  "SELECT COUNT(*) FROM test WHERE o < CAST('06/21/2015' AS DATE);", dt)));
3009  ASSERT_EQ(g_num_rows + g_num_rows / 2,
3010  v<int64_t>(run_simple_agg(
3011  "SELECT COUNT(*) FROM test WHERE o < CAST('21-Jun-15' AS DATE);", dt)));
3012  ASSERT_EQ(
3013  g_num_rows + g_num_rows / 2,
3014  v<int64_t>(run_simple_agg(
3015  "SELECT COUNT(*) FROM test WHERE o < CAST('21/Jun/2015' AS DATE);", dt)));
3016  ASSERT_EQ(
3017  g_num_rows + g_num_rows / 2,
3018  v<int64_t>(run_simple_agg(
3019  "SELECT COUNT(*) FROM test WHERE o < CAST('1434844800' AS DATE);", dt)));
3020 
3021  // test different input formats
3022  // added new format for customer
3023  ASSERT_EQ(
3024  1434896116L,
3025  v<int64_t>(run_simple_agg(
3026  "select CAST('2015-06-21 14:15:16' AS timestamp) FROM test limit 1;", dt)));
3027  ASSERT_EQ(2 * g_num_rows,
3028  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3029  "CAST('2015-06-21:141516' AS TIMESTAMP);",
3030  dt)));
3031  ASSERT_EQ(
3032  2 * g_num_rows,
3033  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= CAST('21-JUN-15 "
3034  "2.15.16.12345 PM' AS TIMESTAMP);",
3035  dt)));
3036  ASSERT_EQ(
3037  2 * g_num_rows,
3038  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= CAST('21-JUN-15 "
3039  "2.15.16.12345 AM' AS TIMESTAMP);",
3040  dt)));
3041  ASSERT_EQ(2 * g_num_rows,
3042  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3043  "CAST('21-JUN-15 2:15:16 AM' AS TIMESTAMP);",
3044  dt)));
3045 
3046  ASSERT_EQ(2 * g_num_rows,
3047  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3048  "CAST('06/21/2015 14:15:16' AS TIMESTAMP);",
3049  dt)));
3050 
3051  // Support ISO date offset format
3052  ASSERT_EQ(
3053  2 * g_num_rows,
3054  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3055  "CAST('21/Aug/2015:12:13:14 -0600' AS TIMESTAMP);",
3056  dt)));
3057  ASSERT_EQ(2 * g_num_rows,
3058  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3059  "CAST('2015-08-21T12:13:14 -0600' AS TIMESTAMP);",
3060  dt)));
3061  ASSERT_EQ(2 * g_num_rows,
3062  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3063  "CAST('21-Aug-15 12:13:14 -0600' AS TIMESTAMP);",
3064  dt)));
3065  ASSERT_EQ(
3066  2 * g_num_rows,
3067  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3068  "CAST('21/Aug/2015:13:13:14 -0500' AS TIMESTAMP);",
3069  dt)));
3070  ASSERT_EQ(2 * g_num_rows,
3071  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3072  "CAST('2015-08-21T18:13:14' AS TIMESTAMP);",
3073  dt)));
3074  // add test for quarterday behaviour
3075  ASSERT_EQ(1L,
3076  v<int64_t>(run_simple_agg(
3077  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T04:23:11' AS "
3078  "timestamp)) FROM test limit 1;",
3079  dt)));
3080  ASSERT_EQ(1L,
3081  v<int64_t>(run_simple_agg(
3082  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T00:00:00' AS "
3083  "timestamp)) FROM test limit 1;",
3084  dt)));
3085  ASSERT_EQ(2L,
3086  v<int64_t>(run_simple_agg(
3087  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T08:23:11' AS "
3088  "timestamp)) FROM test limit 1;",
3089  dt)));
3090  ASSERT_EQ(3L,
3091  v<int64_t>(run_simple_agg(
3092  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T14:23:11' AS "
3093  "timestamp)) FROM test limit 1;",
3094  dt)));
3095  ASSERT_EQ(4L,
3096  v<int64_t>(run_simple_agg(
3097  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T23:23:11' AS "
3098  "timestamp)) FROM test limit 1;",
3099  dt)));
3100  ASSERT_EQ(1440115200L,
3101  v<int64_t>(run_simple_agg(
3102  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T04:23:11' AS "
3103  "timestamp)) FROM test limit 1;",
3104  dt)));
3105  ASSERT_EQ(1440136800L,
3106  v<int64_t>(run_simple_agg(
3107  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T08:23:11' AS "
3108  "timestamp)) FROM test limit 1;",
3109  dt)));
3110  ASSERT_EQ(1440158400L,
3111  v<int64_t>(run_simple_agg(
3112  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T13:23:11' AS "
3113  "timestamp)) FROM test limit 1;",
3114  dt)));
3115  ASSERT_EQ(1440180000L,
3116  v<int64_t>(run_simple_agg(
3117  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T23:59:59' AS "
3118  "timestamp)) FROM test limit 1;",
3119  dt)));
3120  ASSERT_EQ(2007,
3121  v<int64_t>(run_simple_agg("SELECT DATEPART('year', CAST('2007-10-30 "
3122  "12:15:32' AS TIMESTAMP)) FROM test;",
3123  dt)));
3124  ASSERT_EQ(2007,
3125  v<int64_t>(run_simple_agg("SELECT DATEPART('yyyy', CAST('2007-10-30 "
3126  "12:15:32' AS TIMESTAMP)) FROM test;",
3127  dt)));
3128  ASSERT_EQ(
3129  2007,
3130  v<int64_t>(run_simple_agg(
3131  "SELECT DATEPART('yy', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3132  dt)));
3133  ASSERT_EQ(4,
3134  v<int64_t>(run_simple_agg("SELECT DATEPART('quarter', CAST('2007-10-30 "
3135  "12:15:32' AS TIMESTAMP)) FROM test;",
3136  dt)));
3137  ASSERT_EQ(
3138  4,
3139  v<int64_t>(run_simple_agg(
3140  "SELECT DATEPART('qq', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3141  dt)));
3142  ASSERT_EQ(
3143  4,
3144  v<int64_t>(run_simple_agg(
3145  "SELECT DATEPART('q', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3146  dt)));
3147  ASSERT_EQ(10,
3148  v<int64_t>(run_simple_agg("SELECT DATEPART('month', CAST('2007-10-30 "
3149  "12:15:32' AS TIMESTAMP)) FROM test;",
3150  dt)));
3151  ASSERT_EQ(
3152  10,
3153  v<int64_t>(run_simple_agg(
3154  "SELECT DATEPART('mm', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3155  dt)));
3156  ASSERT_EQ(
3157  10,
3158  v<int64_t>(run_simple_agg(
3159  "SELECT DATEPART('m', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3160  dt)));
3161  ASSERT_EQ(303,
3162  v<int64_t>(run_simple_agg("SELECT DATEPART('dayofyear', CAST('2007-10-30 "
3163  "12:15:32' AS TIMESTAMP)) FROM test;",
3164  dt)));
3165  ASSERT_EQ(
3166  303,
3167  v<int64_t>(run_simple_agg(
3168  "SELECT DATEPART('dy', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3169  dt)));
3170  ASSERT_EQ(
3171  303,
3172  v<int64_t>(run_simple_agg(
3173  "SELECT DATEPART('y', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3174  dt)));
3175  ASSERT_EQ(
3176  30,
3177  v<int64_t>(run_simple_agg(
3178  "SELECT DATEPART('day', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3179  dt)));
3180  ASSERT_EQ(
3181  30,
3182  v<int64_t>(run_simple_agg(
3183  "SELECT DATEPART('dd', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3184  dt)));
3185  ASSERT_EQ(
3186  30,
3187  v<int64_t>(run_simple_agg(
3188  "SELECT DATEPART('d', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3189  dt)));
3190  ASSERT_EQ(12,
3191  v<int64_t>(run_simple_agg("SELECT DATEPART('hour', CAST('2007-10-30 "
3192  "12:15:32' AS TIMESTAMP)) FROM test;",
3193  dt)));
3194  ASSERT_EQ(
3195  12,
3196  v<int64_t>(run_simple_agg(
3197  "SELECT DATEPART('hh', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3198  dt)));
3199  ASSERT_EQ(15,
3200  v<int64_t>(run_simple_agg("SELECT DATEPART('minute', CAST('2007-10-30 "
3201  "12:15:32' AS TIMESTAMP)) FROM test;",
3202  dt)));
3203  ASSERT_EQ(
3204  15,
3205  v<int64_t>(run_simple_agg(
3206  "SELECT DATEPART('mi', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3207  dt)));
3208  ASSERT_EQ(
3209  15,
3210  v<int64_t>(run_simple_agg(
3211  "SELECT DATEPART('n', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3212  dt)));
3213  ASSERT_EQ(32,
3214  v<int64_t>(run_simple_agg("SELECT DATEPART('second', CAST('2007-10-30 "
3215  "12:15:32' AS TIMESTAMP)) FROM test;",
3216  dt)));
3217  ASSERT_EQ(
3218  32,
3219  v<int64_t>(run_simple_agg(
3220  "SELECT DATEPART('ss', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3221  dt)));
3222  ASSERT_EQ(
3223  32,
3224  v<int64_t>(run_simple_agg(
3225  "SELECT DATEPART('s', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3226  dt)));
3227  ASSERT_EQ(
3228  32,
3229  v<int64_t>(run_simple_agg(
3230  "SELECT DATEPART('s', TIMESTAMP '2007-10-30 12:15:32') FROM test;", dt)));
3231  ASSERT_EQ(
3232  3,
3233  v<int64_t>(run_simple_agg("SELECT DATEDIFF('year', CAST('2006-01-07 00:00:00' as "
3234  "TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3235  "TIMESTAMP)) FROM TEST LIMIT 1;",
3236  dt)));
3237  ASSERT_EQ(
3238  36,
3239  v<int64_t>(run_simple_agg("SELECT DATEDIFF('month', CAST('2006-01-07 00:00:00' "
3240  "as TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3241  "TIMESTAMP)) FROM TEST LIMIT 1;",
3242  dt)));
3243  ASSERT_EQ(
3244  1096,
3245  v<int64_t>(run_simple_agg("SELECT DATEDIFF('day', CAST('2006-01-07 00:00:00' as "
3246  "TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3247  "TIMESTAMP)) FROM TEST LIMIT 1;",
3248  dt)));
3249  ASSERT_EQ(
3250  12,
3251  v<int64_t>(run_simple_agg("SELECT DATEDIFF('quarter', CAST('2006-01-07 00:00:00' "
3252  "as TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3253  "TIMESTAMP)) FROM TEST LIMIT 1;",
3254  dt)));
3255  ASSERT_EQ(1,
3256  v<int64_t>(run_simple_agg("SELECT DATEDIFF('day', DATE '2009-2-28', DATE "
3257  "'2009-03-01') FROM TEST LIMIT 1;",
3258  dt)));
3259  ASSERT_EQ(2,
3260  v<int64_t>(run_simple_agg("SELECT DATEDIFF('day', DATE '2008-2-28', DATE "
3261  "'2008-03-01') FROM TEST LIMIT 1;",
3262  dt)));
3263  ASSERT_EQ(-425,
3264  v<int64_t>(run_simple_agg("select DATEDIFF('day', DATE '1971-03-02', DATE "
3265  "'1970-01-01') from test limit 1;",
3266  dt)));
3267  ASSERT_EQ(
3268  1,
3269  v<int64_t>(run_simple_agg(
3270  "SELECT DATEDIFF('day', o, o + INTERVAL '1' DAY) FROM TEST LIMIT 1;", dt)));
3271  ASSERT_EQ(15,
3272  v<int64_t>(run_simple_agg("SELECT count(*) from test where DATEDIFF('day', "
3273  "CAST (m AS DATE), o) < -5570;",
3274  dt)));
3275  ASSERT_EQ(1,
3276  v<int64_t>(run_simple_agg("SELECT DATEDIFF('second', m, TIMESTAMP(0) "
3277  "'2014-12-13 22:23:16') FROM test limit 1;",
3278  dt)));
3279  ASSERT_EQ(1000,
3280  v<int64_t>(run_simple_agg("SELECT DATEDIFF('millisecond', m, TIMESTAMP(0) "
3281  "'2014-12-13 22:23:16') FROM test limit 1;",
3282  dt)));
3283  ASSERT_EQ(44000000,
3284  v<int64_t>(run_simple_agg("SELECT DATEDIFF('microsecond', m, TIMESTAMP(0) "
3285  "'2014-12-13 22:23:59') FROM test limit 1;",
3286  dt)));
3287  ASSERT_EQ(34000000000,
3288  v<int64_t>(run_simple_agg("SELECT DATEDIFF('nanosecond', m, TIMESTAMP(0) "
3289  "'2014-12-13 22:23:49') FROM test limit 1;",
3290  dt)));
3291  ASSERT_EQ(-1000,
3292  v<int64_t>(run_simple_agg("SELECT DATEDIFF('millisecond', TIMESTAMP(0) "
3293  "'2014-12-13 22:23:16', m) FROM test limit 1;",
3294  dt)));
3295  ASSERT_EQ(-44000000,
3296  v<int64_t>(run_simple_agg("SELECT DATEDIFF('microsecond', TIMESTAMP(0) "
3297  "'2014-12-13 22:23:59', m) FROM test limit 1;",
3298  dt)));
3299  ASSERT_EQ(-34000000000,
3300  v<int64_t>(run_simple_agg("SELECT DATEDIFF('nanosecond', TIMESTAMP(0) "
3301  "'2014-12-13 22:23:49', m) FROM test limit 1;",
3302  dt)));
3303  // DATEADD tests
3304  ASSERT_EQ(
3305  1,
3306  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, CAST('2017-05-31' AS DATE)) "
3307  "= TIMESTAMP '2017-06-01 0:00:00' from test limit 1;",
3308  dt)));
3309  ASSERT_EQ(
3310  1,
3311  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 2, DATE '2017-05-31') = "
3312  "TIMESTAMP '2017-06-02 0:00:00' from test limit 1;",
3313  dt)));
3314  ASSERT_EQ(
3315  1,
3316  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -1, CAST('2017-05-31' AS DATE)) "
3317  "= TIMESTAMP '2017-05-30 0:00:00' from test limit 1;",
3318  dt)));
3319  ASSERT_EQ(
3320  1,
3321  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -2, DATE '2017-05-31') = "
3322  "TIMESTAMP '2017-05-29 0:00:00' from test limit 1;",
3323  dt)));
3324  ASSERT_EQ(1,
3325  v<int64_t>(run_simple_agg(
3326  "SELECT DATEADD('hour', 1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3327  "'2017-05-31 2:11:11' from test limit 1;",
3328  dt)));
3329  ASSERT_EQ(
3330  1,
3331  v<int64_t>(run_simple_agg(
3332  "SELECT DATEADD('hour', 10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3333  "'2017-05-31 11:11:11' from test limit 1;",
3334  dt)));
3335  ASSERT_EQ(
3336  1,
3337  v<int64_t>(run_simple_agg(
3338  "SELECT DATEADD('hour', -1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3339  "'2017-05-31 0:11:11' from test limit 1;",
3340  dt)));
3341  ASSERT_EQ(
3342  1,
3343  v<int64_t>(run_simple_agg(
3344  "SELECT DATEADD('hour', -10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3345  "'2017-05-30 15:11:11' from test limit 1;",
3346  dt)));
3347  ASSERT_EQ(
3348  1,
3349  v<int64_t>(run_simple_agg(
3350  "SELECT DATEADD('minute', 1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3351  "'2017-05-31 1:12:11' from test limit 1;",
3352  dt)));
3353  ASSERT_EQ(
3354  1,
3355  v<int64_t>(run_simple_agg(
3356  "SELECT DATEADD('minute', 10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3357  "'2017-05-31 1:21:11' from test limit 1;",
3358  dt)));
3359  ASSERT_EQ(
3360  1,
3361  v<int64_t>(run_simple_agg(
3362  "SELECT DATEADD('minute', -1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3363  "'2017-05-31 1:10:11' from test limit 1;",
3364  dt)));
3365  ASSERT_EQ(
3366  1,
3367  v<int64_t>(run_simple_agg(
3368  "SELECT DATEADD('minute', -10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3369  "'2017-05-31 1:01:11' from test limit 1;",
3370  dt)));
3371  ASSERT_EQ(
3372  1,
3373  v<int64_t>(run_simple_agg(
3374  "SELECT DATEADD('second', 1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3375  "'2017-05-31 1:11:12' from test limit 1;",
3376  dt)));
3377  ASSERT_EQ(
3378  1,
3379  v<int64_t>(run_simple_agg(
3380  "SELECT DATEADD('second', 10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3381  "'2017-05-31 1:11:21' from test limit 1;",
3382  dt)));
3383  ASSERT_EQ(
3384  1,
3385  v<int64_t>(run_simple_agg(
3386  "SELECT DATEADD('second', -1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3387  "'2017-05-31 1:11:10' from test limit 1;",
3388  dt)));
3389  ASSERT_EQ(
3390  1,
3391  v<int64_t>(run_simple_agg(
3392  "SELECT DATEADD('second', -10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3393  "'2017-05-31 1:11:01' from test limit 1;",
3394  dt)));
3395 
3396  ASSERT_EQ(1,
3397  v<int64_t>(run_simple_agg(
3398  "SELECT DATEADD('month', 1, DATE '2017-01-10') = TIMESTAMP "
3399  "'2017-02-10 0:00:00' from test limit 1;",
3400  dt)));
3401  ASSERT_EQ(1,
3402  v<int64_t>(run_simple_agg(
3403  "SELECT DATEADD('month', 10, DATE '2017-01-10') = TIMESTAMP "
3404  "'2017-11-10 0:00:00' from test limit 1;",
3405  dt)));
3406  ASSERT_EQ(1,
3407  v<int64_t>(run_simple_agg(
3408  "SELECT DATEADD('month', 1, DATE '2009-01-30') = TIMESTAMP "
3409  "'2009-02-28 0:00:00' from test limit 1;",
3410  dt)));
3411  ASSERT_EQ(1,
3412  v<int64_t>(run_simple_agg(
3413  "SELECT DATEADD('month', 1, DATE '2008-01-30') = TIMESTAMP "
3414  "'2008-02-29 0:00:00' from test limit 1;",
3415  dt)));
3416  ASSERT_EQ(
3417  1,
3418  v<int64_t>(run_simple_agg(
3419  "SELECT DATEADD('month', 1, TIMESTAMP '2009-01-30 1:11:11') = TIMESTAMP "
3420  "'2009-02-28 1:11:11' from test limit 1;",
3421  dt)));
3422  ASSERT_EQ(
3423  1,
3424  v<int64_t>(run_simple_agg(
3425  "SELECT DATEADD('month', -1, TIMESTAMP '2009-03-30 1:11:11') = TIMESTAMP "
3426  "'2009-02-28 1:11:11' from test limit 1;",
3427  dt)));
3428  ASSERT_EQ(
3429  1,
3430  v<int64_t>(run_simple_agg(
3431  "SELECT DATEADD('month', -4, TIMESTAMP '2009-03-30 1:11:11') = TIMESTAMP "
3432  "'2008-11-30 1:11:11' from test limit 1;",
3433  dt)));
3434  ASSERT_EQ(
3435  1,
3436  v<int64_t>(run_simple_agg(
3437  "SELECT DATEADD('month', 5, TIMESTAMP '2009-01-31 1:11:11') = TIMESTAMP "
3438  "'2009-6-30 1:11:11' from test limit 1;",
3439  dt)));
3440  ASSERT_EQ(1,
3441  v<int64_t>(run_simple_agg(
3442  "SELECT DATEADD('year', 1, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3443  "'2009-02-28 1:11:11' from test limit 1;",
3444  dt)));
3445  ASSERT_EQ(
3446  1,
3447  v<int64_t>(run_simple_agg(
3448  "SELECT TIMESTAMPADD(YEAR, 1, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3449  "'2009-02-28 1:11:11' from test limit 1;",
3450  dt)));
3451  ASSERT_EQ(
3452  1,
3453  v<int64_t>(run_simple_agg(
3454  "SELECT TIMESTAMPADD(YEAR, -8, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3455  "'2000-02-29 1:11:11' from test limit 1;",
3456  dt)));
3457  ASSERT_EQ(
3458  1,
3459  v<int64_t>(run_simple_agg(
3460  "SELECT TIMESTAMPADD(YEAR, -8, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3461  "'2000-02-29 1:11:11' from test limit 1;",
3462  dt)));
3463 
3464  ASSERT_EQ(1,
3465  v<int64_t>(run_simple_agg(
3466  "SELECT m = TIMESTAMP '2014-12-13 22:23:15' from test limit 1;", dt)));
3467  ASSERT_EQ(1,
3468  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, m) = TIMESTAMP "
3469  "'2014-12-14 22:23:15' from test limit 1;",
3470  dt)));
3471  ASSERT_EQ(1,
3472  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -1, m) = TIMESTAMP "
3473  "'2014-12-12 22:23:15' from test limit 1;",
3474  dt)));
3475  ASSERT_EQ(1,
3476  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, m) = TIMESTAMP "
3477  "'2014-12-14 22:23:15' from test limit 1;",
3478  dt)));
3479  ASSERT_EQ(1,
3480  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -1, m) = TIMESTAMP "
3481  "'2014-12-12 22:23:15' from test limit 1;",
3482  dt)));
3483  ASSERT_EQ(1,
3484  v<int64_t>(
3485  run_simple_agg("SELECT o = DATE '1999-09-09' from test limit 1;", dt)));
3486  ASSERT_EQ(1,
3487  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, o) = TIMESTAMP "
3488  "'1999-09-10 0:00:00' from test limit 1;",
3489  dt)));
3490  ASSERT_EQ(1,
3491  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -3, o) = TIMESTAMP "
3492  "'1999-09-06 0:00:00' from test limit 1;",
3493  dt)));
3494  /* DATE ADD subseconds to default timestamp(0) */
3495  ASSERT_EQ(
3496  1,
3497  v<int64_t>(run_simple_agg("SELECT DATEADD('millisecond', 1000, m) = TIMESTAMP "
3498  "'2014-12-13 22:23:16' from test limit 1;",
3499  dt)));
3500  ASSERT_EQ(
3501  1,
3502  v<int64_t>(run_simple_agg("SELECT DATEADD('microsecond', 1000000, m) = TIMESTAMP "
3503  "'2014-12-13 22:23:16' from test limit 1;",
3504  dt)));
3505  ASSERT_EQ(1,
3506  v<int64_t>(run_simple_agg(
3507  "SELECT DATEADD('nanosecond', 1000000000, m) = TIMESTAMP "
3508  "'2014-12-13 22:23:16' from test limit 1;",
3509  dt)));
3510  ASSERT_EQ(
3511  1,
3512  v<int64_t>(run_simple_agg("SELECT DATEADD('millisecond', 5123, m) = TIMESTAMP "
3513  "'2014-12-13 22:23:20' from test limit 1;",
3514  dt)));
3515  ASSERT_EQ(1,
3516  v<int64_t>(run_simple_agg(
3517  "SELECT DATEADD('microsecond', 86400000000, m) = TIMESTAMP "
3518  "'2014-12-14 22:23:15' from test limit 1;",
3519  dt)));
3520  ASSERT_EQ(1,
3521  v<int64_t>(run_simple_agg(
3522  "SELECT DATEADD('nanosecond', 86400000000123, m) = TIMESTAMP "
3523  "'2014-12-14 22:23:15' from test limit 1;",
3524  dt)));
3525  ASSERT_EQ(1,
3526  v<int64_t>(run_simple_agg("SELECT DATEADD('weekday', -3, o) = TIMESTAMP "
3527  "'1999-09-06 00:00:00' from test limit 1;",
3528  dt)));
3529  ASSERT_EQ(1,
3530  v<int64_t>(run_simple_agg("SELECT DATEADD('decade', 3, o) = TIMESTAMP "
3531  "'2029-09-09 00:00:00' from test limit 1;",
3532  dt)));
3533  ASSERT_EQ(1,
3534  v<int64_t>(run_simple_agg("SELECT DATEADD('week', 1, o) = TIMESTAMP "
3535  "'1999-09-16 00:00:00' from test limit 1;",
3536  dt)));
3537 
3538  ASSERT_EQ(
3539  1,
3540  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, 1, TIMESTAMP '2009-03-02 "
3541  "1:23:45') = TIMESTAMP '2009-03-03 1:23:45' "
3542  "FROM TEST LIMIT 1;",
3543  dt)));
3544  ASSERT_EQ(
3545  1,
3546  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, -1, TIMESTAMP '2009-03-02 "
3547  "1:23:45') = TIMESTAMP '2009-03-01 1:23:45' "
3548  "FROM TEST LIMIT 1;",
3549  dt)));
3550  ASSERT_EQ(
3551  1,
3552  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, 15, TIMESTAMP '2009-03-02 "
3553  "1:23:45') = TIMESTAMP '2009-03-17 1:23:45' "
3554  "FROM TEST LIMIT 1;",
3555  dt)));
3556  ASSERT_EQ(
3557  1,
3558  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, -15, TIMESTAMP '2009-03-02 "
3559  "1:23:45') = TIMESTAMP '2009-02-15 1:23:45' "
3560  "FROM TEST LIMIT 1;",
3561  dt)));
3562  ASSERT_EQ(
3563  1,
3564  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, 1, TIMESTAMP '2009-03-02 "
3565  "1:23:45') = TIMESTAMP '2009-03-02 2:23:45' "
3566  "FROM TEST LIMIT 1;",
3567  dt)));
3568  ASSERT_EQ(
3569  1,
3570  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, -1, TIMESTAMP '2009-03-02 "
3571  "1:23:45') = TIMESTAMP '2009-03-02 0:23:45' "
3572  "FROM TEST LIMIT 1;",
3573  dt)));
3574  ASSERT_EQ(
3575  1,
3576  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, 15, TIMESTAMP '2009-03-02 "
3577  "1:23:45') = TIMESTAMP '2009-03-02 16:23:45' "
3578  "FROM TEST LIMIT 1;",
3579  dt)));
3580  ASSERT_EQ(
3581  1,
3582  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, -15, TIMESTAMP '2009-03-02 "
3583  "1:23:45') = TIMESTAMP '2009-03-01 10:23:45' "
3584  "FROM TEST LIMIT 1;",
3585  dt)));
3586  ASSERT_EQ(
3587  1,
3588  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(MINUTE, 15, TIMESTAMP '2009-03-02 "
3589  "1:23:45') = TIMESTAMP '2009-03-02 1:38:45' "
3590  "FROM TEST LIMIT 1;",
3591  dt)));
3592  ASSERT_EQ(1,
3593  v<int64_t>(
3594  run_simple_agg("SELECT TIMESTAMPADD(MINUTE, -15, TIMESTAMP '2009-03-02 "
3595  "1:23:45') = TIMESTAMP '2009-03-02 1:08:45' "
3596  "FROM TEST LIMIT 1;",
3597  dt)));
3598  ASSERT_EQ(
3599  1,
3600  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(SECOND, 15, TIMESTAMP '2009-03-02 "
3601  "1:23:45') = TIMESTAMP '2009-03-02 1:24:00' "
3602  "FROM TEST LIMIT 1;",
3603  dt)));
3604  ASSERT_EQ(1,
3605  v<int64_t>(
3606  run_simple_agg("SELECT TIMESTAMPADD(SECOND, -15, TIMESTAMP '2009-03-02 "
3607  "1:23:45') = TIMESTAMP '2009-03-02 1:23:30' "
3608  "FROM TEST LIMIT 1;",
3609  dt)));
3610 
3611  ASSERT_EQ(1,
3612  v<int64_t>(run_simple_agg(
3613  "SELECT TIMESTAMPADD(DAY, 1, m) = TIMESTAMP '2014-12-14 22:23:15' "
3614  "FROM TEST LIMIT 1;",
3615  dt)));
3616  ASSERT_EQ(1,
3617  v<int64_t>(run_simple_agg(
3618  "SELECT TIMESTAMPADD(DAY, -1, m) = TIMESTAMP '2014-12-12 22:23:15' "
3619  "FROM TEST LIMIT 1;",
3620  dt)));
3621  ASSERT_EQ(1,
3622  v<int64_t>(run_simple_agg(
3623  "SELECT TIMESTAMPADD(DAY, 15, m) = TIMESTAMP '2014-12-28 22:23:15' "
3624  "FROM TEST LIMIT 1;",
3625  dt)));
3626  ASSERT_EQ(1,
3627  v<int64_t>(run_simple_agg(
3628  "SELECT TIMESTAMPADD(DAY, -15, m) = TIMESTAMP '2014-11-28 22:23:15' "
3629  "FROM TEST LIMIT 1;",
3630  dt)));
3631  ASSERT_EQ(1,
3632  v<int64_t>(run_simple_agg(
3633  "SELECT TIMESTAMPADD(HOUR, 1, m) = TIMESTAMP '2014-12-13 23:23:15' "
3634  "FROM TEST LIMIT 1;",
3635  dt)));
3636  ASSERT_EQ(1,
3637  v<int64_t>(run_simple_agg(
3638  "SELECT TIMESTAMPADD(HOUR, -1, m) = TIMESTAMP '2014-12-13 21:23:15' "
3639  "FROM TEST LIMIT 1;",
3640  dt)));
3641  ASSERT_EQ(1,
3642  v<int64_t>(run_simple_agg(
3643  "SELECT TIMESTAMPADD(HOUR, 15, m) = TIMESTAMP '2014-12-14 13:23:15' "
3644  "FROM TEST LIMIT 1;",
3645  dt)));
3646  ASSERT_EQ(1,
3647  v<int64_t>(run_simple_agg(
3648  "SELECT TIMESTAMPADD(HOUR, -15, m) = TIMESTAMP '2014-12-13 7:23:15' "
3649  "FROM TEST LIMIT 1;",
3650  dt)));
3651  ASSERT_EQ(1,
3652  v<int64_t>(run_simple_agg(
3653  "SELECT TIMESTAMPADD(MINUTE, 15, m) = TIMESTAMP '2014-12-13 22:38:15' "
3654  "FROM TEST LIMIT 1;",
3655  dt)));
3656  ASSERT_EQ(1,
3657  v<int64_t>(run_simple_agg(
3658  "SELECT TIMESTAMPADD(MINUTE, -15, m) = TIMESTAMP '2014-12-13 22:08:15' "
3659  "FROM TEST LIMIT 1;",
3660  dt)));
3661  ASSERT_EQ(1,
3662  v<int64_t>(run_simple_agg(
3663  "SELECT TIMESTAMPADD(SECOND, 15, m) = TIMESTAMP '2014-12-13 22:23:30' "
3664  "FROM TEST LIMIT 1;",
3665  dt)));
3666  ASSERT_EQ(1,
3667  v<int64_t>(run_simple_agg(
3668  "SELECT TIMESTAMPADD(SECOND, -15, m) = TIMESTAMP '2014-12-13 22:23:00' "
3669  "FROM TEST LIMIT 1;",
3670  dt)));
3671 
3672  ASSERT_EQ(1,
3673  v<int64_t>(run_simple_agg(
3674  "SELECT TIMESTAMPADD(MONTH, 1, m) = TIMESTAMP '2015-01-13 22:23:15' "
3675  "FROM TEST LIMIT 1;",
3676  dt)));
3677  ASSERT_EQ(1,
3678  v<int64_t>(run_simple_agg(
3679  "SELECT TIMESTAMPADD(MONTH, -1, m) = TIMESTAMP '2014-11-13 22:23:15' "
3680  "FROM TEST LIMIT 1;",
3681  dt)));
3682  ASSERT_EQ(1,
3683  v<int64_t>(run_simple_agg(
3684  "SELECT TIMESTAMPADD(MONTH, 5, m) = TIMESTAMP '2015-05-13 22:23:15' "
3685  "FROM TEST LIMIT 1;",
3686  dt)));
3687  ASSERT_EQ(1,
3688  v<int64_t>(run_simple_agg(
3689  "SELECT TIMESTAMPADD(DAY, -5, m) = TIMESTAMP '2014-12-08 22:23:15' "
3690  "FROM TEST LIMIT 1;",
3691  dt)));
3692  ASSERT_EQ(1,
3693  v<int64_t>(run_simple_agg(
3694  "SELECT TIMESTAMPADD(YEAR, 1, m) = TIMESTAMP '2015-12-13 22:23:15' "
3695  "FROM TEST LIMIT 1;",
3696  dt)));
3697  ASSERT_EQ(1,
3698  v<int64_t>(run_simple_agg(
3699  "SELECT TIMESTAMPADD(YEAR, -1, m) = TIMESTAMP '2013-12-13 22:23:15' "
3700  "FROM TEST LIMIT 1;",
3701  dt)));
3702  ASSERT_EQ(1,
3703  v<int64_t>(run_simple_agg(
3704  "SELECT TIMESTAMPADD(YEAR, 5, m) = TIMESTAMP '2019-12-13 22:23:15' "
3705  "FROM TEST LIMIT 1;",
3706  dt)));
3707  ASSERT_EQ(1,
3708  v<int64_t>(run_simple_agg(
3709  "SELECT TIMESTAMPADD(YEAR, -5, m) = TIMESTAMP '2009-12-13 22:23:15' "
3710  "FROM TEST LIMIT 1;",
3711  dt)));
3712  ASSERT_EQ(
3713  0,
3714  v<int64_t>(run_simple_agg("select count(*) from test where TIMESTAMPADD(YEAR, "
3715  "15, CAST(o AS TIMESTAMP)) > m;",
3716  dt)));
3717  ASSERT_EQ(
3718  15,
3719  v<int64_t>(run_simple_agg("select count(*) from test where TIMESTAMPADD(YEAR, "
3720  "16, CAST(o AS TIMESTAMP)) > m;",
3721  dt)));
3722 
3723  ASSERT_EQ(
3724  128885,
3725  v<int64_t>(run_simple_agg(
3726  "SELECT TIMESTAMPDIFF(minute, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3727  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3728  dt)));
3729  ASSERT_EQ(2148,
3730  v<int64_t>(run_simple_agg(
3731  "SELECT TIMESTAMPDIFF(hour, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3732  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3733  dt)));
3734  ASSERT_EQ(89,
3735  v<int64_t>(run_simple_agg(
3736  "SELECT TIMESTAMPDIFF(day, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3737  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3738  dt)));
3739  ASSERT_EQ(3,
3740  v<int64_t>(run_simple_agg(
3741  "SELECT TIMESTAMPDIFF(month, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3742  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3743  dt)));
3744  ASSERT_EQ(
3745  -3,
3746  v<int64_t>(run_simple_agg(
3747  "SELECT TIMESTAMPDIFF(month, TIMESTAMP '2003-05-01 12:05:55', TIMESTAMP "
3748  "'2003-02-01 0:00:00') FROM TEST LIMIT 1;",
3749  dt)));
3750  ASSERT_EQ(
3751  5,
3752  v<int64_t>(run_simple_agg(
3753  "SELECT TIMESTAMPDIFF(month, m, m + INTERVAL '5' MONTH) FROM TEST LIMIT 1;",
3754  dt)));
3755  ASSERT_EQ(
3756  -5,
3757  v<int64_t>(run_simple_agg(
3758  "SELECT TIMESTAMPDIFF(month, m, m - INTERVAL '5' MONTH) FROM TEST LIMIT 1;",
3759  dt)));
3760  ASSERT_EQ(
3761  15,
3762  v<int64_t>(run_simple_agg("select count(*) from test where TIMESTAMPDIFF(YEAR, "
3763  "m, CAST(o AS TIMESTAMP)) < 0;",
3764  dt)));
3765  ASSERT_EQ(1,
3766  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(year, DATE '2018-01-02', "
3767  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3768  dt)));
3769  ASSERT_EQ(14,
3770  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(month, DATE '2018-01-02', "
3771  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3772  dt)));
3773  ASSERT_EQ(426,
3774  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(day, DATE '2018-01-02', "
3775  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3776  dt)));
3777  ASSERT_EQ(60,
3778  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(week, DATE '2018-01-02', "
3779  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3780  dt)));
3781  ASSERT_EQ(613440,
3782  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(minute, DATE '2018-01-02', "
3783  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3784  dt)));
3785  ASSERT_EQ(10224,
3786  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(hour, DATE '2018-01-02', "
3787  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3788  dt)));
3789  ASSERT_EQ(36806400,
3790  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(second, DATE '2018-01-02', "
3791  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3792  dt)));
3793 
3794  ASSERT_EQ(
3795  1418428800L,
3796  v<int64_t>(run_simple_agg("SELECT CAST(m AS date) FROM test LIMIT 1;", dt)));
3797  ASSERT_EQ(1336435200L,
3798  v<int64_t>(run_simple_agg("SELECT CAST(CAST('2012-05-08 20:15:12' AS "
3799  "TIMESTAMP) AS DATE) FROM test LIMIT 1;",
3800  dt)));
3801  ASSERT_EQ(15,
3802  v<int64_t>(run_simple_agg(
3803  "SELECT COUNT(*) FROM test GROUP BY CAST(m AS date);", dt)));
3804  const auto rows = run_multiple_agg(
3805  "SELECT DATE_TRUNC(month, CAST(o AS TIMESTAMP(0))) AS key0, str AS key1, "
3806  "COUNT(*) AS val FROM test GROUP BY "
3807  "key0, key1 ORDER BY val DESC, key1;",
3808  dt);
3809  check_date_trunc_groups(*rows);
3810  const auto one_row = run_multiple_agg(
3811  "SELECT DATE_TRUNC(year, CASE WHEN str = 'foo' THEN m END) d FROM test GROUP BY "
3812  "d "
3813  "HAVING d IS NOT NULL;",
3814  dt);
3815  check_one_date_trunc_group(*one_row, 1388534400);
3816  ASSERT_EQ(0,
3817  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test where "
3818  "DATE '2017-05-30' = DATE '2017-05-31' OR "
3819  "DATE '2017-05-31' = DATE '2017-05-30';",
3820  dt)));
3821  ASSERT_EQ(2 * g_num_rows,
3822  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test where "
3823  "EXTRACT(DOW from TIMESTAMPADD(HOUR, -5, "
3824  "TIMESTAMP '2017-05-31 1:11:11')) = 1 OR "
3825  "EXTRACT(DOW from TIMESTAMPADD(HOUR, -5, "
3826  "TIMESTAMP '2017-05-31 1:11:11')) = 2;",
3827  dt)));
3828  std::vector<std::tuple<std::string, int64_t, int64_t>> date_trunc_queries{
3829  /*TIMESTAMP(0) */
3830  std::make_tuple("year, m", 1388534400L, 20),
3831  std::make_tuple("month, m", 1417392000L, 20),
3832  std::make_tuple("day, m", 1418428800L, 15),
3833  std::make_tuple("hour, m", 1418508000L, 15),
3834  std::make_tuple("minute, m", 1418509380L, 15),
3835  std::make_tuple("second, m", 1418509395L, 15),
3836  std::make_tuple("millennium, m", 978307200L, 20),
3837  std::make_tuple("century, m", 978307200L, 20),
3838  std::make_tuple("decade, m", 1293840000L, 20),
3839  std::make_tuple("week, m", 1417910400L, 15),
3840  std::make_tuple("nanosecond, m", 1418509395L, 15),
3841  std::make_tuple("microsecond, m", 1418509395L, 15),
3842  std::make_tuple("millisecond, m", 1418509395L, 15),
3843  /* TIMESTAMP(3) */
3844  std::make_tuple("year, m_3", 1388534400000L, 20),
3845  std::make_tuple("month, m_3", 1417392000000L, 20),
3846  std::make_tuple("day, m_3", 1418428800000L, 15),
3847  std::make_tuple("hour, m_3", 1418508000000L, 15),
3848  std::make_tuple("minute, m_3", 1418509380000L, 15),
3849  std::make_tuple("second, m_3", 1418509395000L, 15),
3850  std::make_tuple("millennium, m_3", 978307200000L, 20),
3851  std::make_tuple("century, m_3", 978307200000L, 20),
3852  std::make_tuple("decade, m_3", 1293840000000L, 20),
3853  std::make_tuple("week, m_3", 1417910400000L, 15),
3854  std::make_tuple("nanosecond, m_3", 1418509395323L, 15),
3855  std::make_tuple("microsecond, m_3", 1418509395323L, 15),
3856  std::make_tuple("millisecond, m_3", 1418509395323L, 15),
3857  /* TIMESTAMP(6) */
3858  std::make_tuple("year, m_6", 915148800000000L, 10),
3859  std::make_tuple("month, m_6", 930787200000000L, 10),
3860  std::make_tuple("day, m_6", 931651200000000L, 10),
3861  std::make_tuple("hour, m_6", 931701600000000L, 10),
3862  /* std::make_tuple("minute, m_6", 931701720000000L, 10), // Exception with sort
3863  watchdog */
3864  std::make_tuple("second, m_6", 931701773000000L, 10),
3865  std::make_tuple("millennium, m_6", -30578688000000000L, 10),
3866  std::make_tuple("century, m_6", -2177452800000000L, 10),
3867  std::make_tuple("decade, m_6", 662688000000000L, 10),
3868  std::make_tuple("week, m_6", 931651200000000L, 10),
3869  std::make_tuple("nanosecond, m_6", 931701773874533L, 10),
3870  std::make_tuple("microsecond, m_6", 931701773874533L, 10),
3871  std::make_tuple("millisecond, m_6", 931701773874000L, 10),
3872  /* TIMESTAMP(9) */
3873  std::make_tuple("year, m_9", 1136073600000000000L, 10),
3874  std::make_tuple("month, m_9", 1143849600000000000L, 10),
3875  std::make_tuple("day, m_9", 1146009600000000000L, 10),
3876  std::make_tuple("hour, m_9", 1146020400000000000L, 10),
3877  /* std::make_tuple("minute, m_9", 1146023340000000000L, 10), // Exception with
3878  sort watchdog */
3879  std::make_tuple("second, m_9", 1146023344000000000L, 10),
3880  std::make_tuple("millennium, m_9", 978307200000000000L, 20),
3881  std::make_tuple("century, m_9", 978307200000000000L, 20),
3882  std::make_tuple("decade, m_9", 978307200000000000L, 10),
3883  std::make_tuple("week, m_9", 1145750400000000000L, 10),
3884  std::make_tuple("nanosecond, m_9", 1146023344607435125L, 10),
3885  std::make_tuple("microsecond, m_9", 1146023344607435000L, 10),
3886  std::make_tuple("millisecond, m_9", 1146023344607000000L, 10)};
3887  for (auto& query : date_trunc_queries) {
3888  const auto one_row = run_multiple_agg(
3889  "SELECT date_trunc(" + std::get<0>(query) +
3890  ") as key0,COUNT(*) AS val FROM test group by key0 order by key0 "
3891  "limit 1;",
3892  dt);
3894  *one_row, std::get<1>(query), std::get<2>(query));
3895  }
3896  // Compressed DATE - limits test
3897  ASSERT_EQ(4708022400L,
3898  v<int64_t>(run_simple_agg(
3899  "select CAST('2119-03-12' AS DATE) FROM test limit 1;", dt)));
3900  ASSERT_EQ(7998912000L,
3901  v<int64_t>(run_simple_agg("select CAST(CAST('2223-06-24 23:13:57' AS "
3902  "TIMESTAMP) AS DATE) FROM test limit 1;",
3903  dt)));
3904  ASSERT_EQ(1,
3905  v<int64_t>(run_simple_agg("SELECT DATEADD('year', 411, o) = TIMESTAMP "
3906  "'2410-09-12 00:00:00' from test limit 1;",
3907  dt)));
3908  ASSERT_EQ(1,
3909  v<int64_t>(run_simple_agg("SELECT DATEADD('year', -399, o) = TIMESTAMP "
3910  "'1600-08-31 00:00:00' from test limit 1;",
3911  dt)));
3912  ASSERT_EQ(1,
3913  v<int64_t>(run_simple_agg("SELECT DATEADD('month', 6132, o) = TIMESTAMP "
3914  "'2510-09-13 00:00:00' from test limit 1;",
3915  dt)));
3916  ASSERT_EQ(1,
3917  v<int64_t>(run_simple_agg("SELECT DATEADD('month', -1100, o) = TIMESTAMP "
3918  "'1908-01-09 00:00:00' from test limit 1;",
3919  dt)));
3920  ASSERT_EQ(1,
3921  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 312456, o) = TIMESTAMP "
3922  "'2855-03-01 00:00:00' from test limit 1;",
3923  dt)));
3924  ASSERT_EQ(1,
3925  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -23674, o) = TIMESTAMP "
3926  "'1934-11-15 00:00:00' from test limit 1 ;",
3927  dt)));
3928  ASSERT_EQ(
3929  -303,
3930  v<int64_t>(run_simple_agg(
3931  "SELECT DATEDIFF('year', DATE '2302-04-21', o) from test limit 1;", dt)));
3932  ASSERT_EQ(
3933  502,
3934  v<int64_t>(run_simple_agg(
3935  "SELECT DATEDIFF('year', o, DATE '2501-04-21') from test limit 1;", dt)));
3936  ASSERT_EQ(
3937  -4896,
3938  v<int64_t>(run_simple_agg(
3939  "SELECT DATEDIFF('month', DATE '2407-09-01', o) from test limit 1;", dt)));
3940  ASSERT_EQ(
3941  3818,
3942  v<int64_t>(run_simple_agg(
3943  "SELECT DATEDIFF('month', o, DATE '2317-11-01') from test limit 1;", dt)));
3944  ASSERT_EQ(
3945  -86972,
3946  v<int64_t>(run_simple_agg(
3947  "SELECT DATEDIFF('day', DATE '2237-10-23', o) from test limit 1;", dt)));
3948  ASSERT_EQ(
3949  86972,
3950  v<int64_t>(run_simple_agg(
3951  "SELECT DATEDIFF('day', o, DATE '2237-10-23') from test limit 1;", dt)));
3952  ASSERT_EQ(
3953  2617,
3954  v<int64_t>(run_simple_agg(
3955  "SELECT DATEPART('year', CAST ('2617-12-23' as DATE)) from test limit 1;",
3956  dt)));
3957  ASSERT_EQ(
3958  12,
3959  v<int64_t>(run_simple_agg(
3960  "SELECT DATEPART('month', CAST ('2617-12-23' as DATE)) from test limit 1;",
3961  dt)));
3962  ASSERT_EQ(
3963  23,
3964  v<int64_t>(run_simple_agg(
3965  "SELECT DATEPART('day', CAST ('2617-12-23' as DATE)) from test limit 1;",
3966  dt)));
3967  ASSERT_EQ(
3968  0,
3969  v<int64_t>(run_simple_agg(
3970  "SELECT DATEPART('hour', CAST ('2617-12-23' as DATE)) from test limit 1;",
3971  dt)));
3972  ASSERT_EQ(
3973  0,
3974  v<int64_t>(run_simple_agg(
3975  "SELECT DATEPART('minute', CAST ('2617-12-23' as DATE)) from test limit 1;",
3976  dt)));
3977  ASSERT_EQ(
3978  0,
3979  v<int64_t>(run_simple_agg(
3980  "SELECT DATEPART('second', CAST ('2617-12-23' as DATE)) from test limit 1;",
3981  dt)));
3982  ASSERT_EQ(
3983  6,
3984  v<int64_t>(run_simple_agg(
3985  "SELECT DATEPART('weekday', CAST ('2011-12-31' as DATE)) from test limit 1;",
3986  dt)));
3987  ASSERT_EQ(365,
3988  v<int64_t>(run_simple_agg("SELECT DATEPART('dayofyear', CAST ('2011-12-31' "
3989  "as DATE)) from test limit 1;",
3990  dt)));
3991  // Compressed DATE - limits test
3992  ASSERT_EQ(4708022400L,
3993  v<int64_t>(run_simple_agg(
3994  "select CAST('2119-03-12' AS DATE) FROM test limit 1;", dt)));
3995  ASSERT_EQ(7998912000L,
3996  v<int64_t>(run_simple_agg("select CAST(CAST('2223-06-24 23:13:57' AS "
3997  "TIMESTAMP) AS DATE) FROM test limit 1;",
3998  dt)));
3999  ASSERT_EQ(1,
4000  v<int64_t>(run_simple_agg("SELECT DATEADD('year', 411, o) = TIMESTAMP "
4001  "'2410-09-12 00:00:00' from test limit 1;",
4002  dt)));
4003  ASSERT_EQ(1,
4004  v<int64_t>(run_simple_agg("SELECT DATEADD('year', -399, o) = TIMESTAMP "
4005  "'1600-08-31 00:00:00' from test limit 1;",
4006  dt)));
4007  ASSERT_EQ(1,
4008  v<int64_t>(run_simple_agg("SELECT DATEADD('month', 6132, o) = TIMESTAMP "
4009  "'2510-09-13 00:00:00' from test limit 1;",
4010  dt)));
4011  ASSERT_EQ(1,
4012  v<int64_t>(run_simple_agg("SELECT DATEADD('month', -1100, o) = TIMESTAMP "
4013  "'1908-01-09 00:00:00' from test limit 1;",
4014  dt)));
4015  ASSERT_EQ(1,
4016  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 312456, o) = TIMESTAMP "
4017  "'2855-03-01 00:00:00' from test limit 1;",
4018  dt)));
4019  ASSERT_EQ(1,
4020  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -23674, o) = TIMESTAMP "
4021  "'1934-11-15 00:00:00' from test limit 1 ;",
4022  dt)));
4023  ASSERT_EQ(
4024  -303,
4025  v<int64_t>(run_simple_agg(
4026  "SELECT DATEDIFF('year', DATE '2302-04-21', o) from test limit 1;", dt)));
4027  ASSERT_EQ(
4028  502,
4029  v<int64_t>(run_simple_agg(
4030  "SELECT DATEDIFF('year', o, DATE '2501-04-21') from test limit 1;", dt)));
4031  ASSERT_EQ(
4032  -4896,
4033  v<int64_t>(run_simple_agg(
4034  "SELECT DATEDIFF('month', DATE '2407-09-01', o) from test limit 1;", dt)));
4035  ASSERT_EQ(
4036  3818,
4037  v<int64_t>(run_simple_agg(
4038  "SELECT DATEDIFF('month', o, DATE '2317-11-01') from test limit 1;", dt)));
4039  ASSERT_EQ(
4040  -86972,
4041  v<int64_t>(run_simple_agg(
4042  "SELECT DATEDIFF('day', DATE '2237-10-23', o) from test limit 1;", dt)));
4043  ASSERT_EQ(
4044  86972,
4045  v<int64_t>(run_simple_agg(
4046  "SELECT DATEDIFF('day', o, DATE '2237-10-23') from test limit 1;", dt)));
4047  ASSERT_EQ(
4048  2617,
4049  v<int64_t>(run_simple_agg(
4050  "SELECT DATEPART('year', CAST ('2617-12-23' as DATE)) from test limit 1;",
4051  dt)));
4052  ASSERT_EQ(
4053  12,
4054  v<int64_t>(run_simple_agg(
4055  "SELECT DATEPART('month', CAST ('2617-12-23' as DATE)) from test limit 1;",
4056  dt)));
4057  ASSERT_EQ(
4058  23,
4059  v<int64_t>(run_simple_agg(
4060  "SELECT DATEPART('day', CAST ('2617-12-23' as DATE)) from test limit 1;",
4061  dt)));
4062  ASSERT_EQ(
4063  0,
4064  v<int64_t>(run_simple_agg(
4065  "SELECT DATEPART('hour', CAST ('2617-12-23' as DATE)) from test limit 1;",
4066  dt)));
4067  ASSERT_EQ(
4068  0,
4069  v<int64_t>(run_simple_agg(
4070  "SELECT DATEPART('minute', CAST ('2617-12-23' as DATE)) from test limit 1;",
4071  dt)));
4072  ASSERT_EQ(
4073  0,
4074  v<int64_t>(run_simple_agg(
4075  "SELECT DATEPART('second', CAST ('2617-12-23' as DATE)) from test limit 1;",
4076  dt)));
4077  /* Compressed Date ColumnarResults fetch tests*/
4078  ASSERT_EQ(1999,
4079  v<int64_t>(run_simple_agg("select yr from (SELECT EXTRACT(year from o) as "
4080  "yr, o from test order by x) limit 1;",
4081  dt)));
4082  ASSERT_EQ(936835200,
4083  v<int64_t>(run_simple_agg("select dy from (SELECT DATE_TRUNC(day, o) as "
4084  "dy, o from test order by x) limit 1;",
4085  dt)));
4086  ASSERT_EQ(936921600,
4087  v<int64_t>(run_simple_agg("select dy from (SELECT DATEADD('day', 1, o) as "
4088  "dy, o from test order by x) limit 1;",
4089  dt)));
4090  ASSERT_EQ(1,
4091  v<int64_t>(run_simple_agg(
4092  "select dy from (SELECT DATEDIFF('day', o, DATE '1999-09-10') as dy, o "
4093  "from test order by x) limit 1;",
4094  dt)));
4095  }
4096 }
4097 
4098 TEST(Select, In) {
4100  SKIP_NO_GPU();
4101  c("SELECT COUNT(*) FROM test WHERE x IN (7, 8);", dt);
4102  c("SELECT COUNT(*) FROM test WHERE x IN (9, 10);", dt);
4103  c("SELECT COUNT(*) FROM test WHERE z IN (101, 102);", dt);
4104  c("SELECT COUNT(*) FROM test WHERE z IN (201, 202);", dt);
4105  c("SELECT COUNT(*) FROM test WHERE real_str IN ('real_foo', 'real_bar');", dt);
4106  c("SELECT COUNT(*) FROM test WHERE real_str IN ('real_foo', 'real_bar', 'real_baz', "
4107  "'foo');",
4108  dt);
4109  c("SELECT COUNT(*) FROM test WHERE str IN ('foo', 'bar', 'real_foo');", dt);
4110  c("SELECT COUNT(*) FROM test WHERE x IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, "
4111  "14, 15, 16, 17, 18, 19, 20);",
4112  dt);
4113  }
4114 }
4115 
4116 TEST(Select, DivByZero) {
4118  SKIP_NO_GPU();
4119  EXPECT_THROW(run_multiple_agg("SELECT x / 0 FROM test;", dt), std::runtime_error);
4120  EXPECT_THROW(run_multiple_agg("SELECT 1 / 0 FROM test;", dt), std::runtime_error);
4121  EXPECT_THROW(run_multiple_agg("SELECT COUNT(distinct x / 0) FROM test;", dt),
4122  std::runtime_error);
4123  EXPECT_THROW(run_multiple_agg("SELECT f / 0. FROM test;", dt), std::runtime_error);
4124  EXPECT_THROW(run_multiple_agg("SELECT d / 0. FROM test;", dt), std::runtime_error);
4125  EXPECT_THROW(run_multiple_agg("SELECT f / (f - f) FROM test;", dt),
4126  std::runtime_error);
4127  EXPECT_THROW(run_multiple_agg("SELECT COUNT(*) FROM test GROUP BY y / (x - x);", dt),
4128  std::runtime_error);
4129  EXPECT_THROW(
4130  run_multiple_agg("SELECT COUNT(*) FROM test GROUP BY z, y / (x - x);", dt),
4131  std::runtime_error);
4132  EXPECT_THROW(
4133  run_multiple_agg("SELECT COUNT(*) FROM test GROUP BY MOD(y , (x - x));", dt),
4134  std::runtime_error);
4135  EXPECT_THROW(
4137  "SELECT SUM(x) / SUM(CASE WHEN str = 'none' THEN y ELSE 0 END) FROM test;",
4138  dt),
4139  std::runtime_error);
4140  EXPECT_THROW(run_simple_agg("SELECT COUNT(*) FROM test WHERE y / (x - x) = 0;", dt),
4141  std::runtime_error);
4142  ASSERT_EQ(2 * g_num_rows,
4143  v<int64_t>(run_simple_agg(
4144  "SELECT COUNT(*) FROM test WHERE x = x OR y / (x - x) = y;", dt)));
4145  }
4146 }
4147 
4148 TEST(Select, ReturnNullFromDivByZero) {
4150 
4151  g_null_div_by_zero = true;
4153  SKIP_NO_GPU();
4154  c("SELECT x / 0 FROM test;", dt);
4155  c("SELECT 1 / 0 FROM test;", dt);
4156  c("SELECT f / 0. FROM test;", dt);
4157  c("SELECT d / 0. FROM test;", dt);
4158  c("SELECT f / (f - f) FROM test;", dt);
4159  c("SELECT COUNT(*) FROM test GROUP BY y / (x - x);", dt);
4160  c("SELECT COUNT(*) n FROM test GROUP BY z, y / (x - x) ORDER BY n ASC;", dt);
4161  c("SELECT SUM(x) / SUM(CASE WHEN str = 'none' THEN y ELSE 0 END) FROM test;", dt);
4162  c("SELECT COUNT(*) FROM test WHERE y / (x - x) = 0;", dt);
4163  c("SELECT COUNT(*) FROM test WHERE x = x OR y / (x - x) = y;", dt);
4164  }
4165 }
4166 
4167 TEST(Select, ConstantFolding) {
4169  SKIP_NO_GPU();
4170  c("SELECT 1 + 2 FROM test limit 1;", dt);
4171  c("SELECT 1 + 2.3 FROM test limit 1;", dt);
4172  c("SELECT 2.3 + 1 FROM test limit 1;", dt);
4173  c("SELECT 2 * 3 FROM test limit 1;", dt);
4174  c("SELECT 604 * 575 FROM test limit 1;", dt);
4175  c("SELECT 604 * (75 + 500) FROM test limit 1;", dt);
4176  c("SELECT 604 * (5 * 115) FROM test limit 1;", dt);
4177  c("SELECT 100000 + (1 - 604 * 575) FROM test limit 1;", dt);
4178  c("SELECT 1 + 604 * 575 FROM test limit 1;", dt);
4179  c("SELECT 2 + (1 - 604 * 575) FROM test limit 1;", dt);
4180  c("SELECT t + 604 * 575 FROM test limit 1;", dt); // mul is folded in BIGINT
4181  c("SELECT z + 604 * 575 FROM test limit 1;", dt);
4182  c("SELECT 9.1 + 2.9999999999 FROM test limit 1;", dt);
4183  c("SELECT -9.1 - 2.9999999999 FROM test limit 1;", dt);
4184  c("SELECT -(9.1 + 99.22) FROM test limit 1;", dt);
4185  c("SELECT 3/2 FROM test limit 1;", dt);
4186  c("SELECT 3/2.0 FROM test limit 1;", dt);
4187  c("SELECT 11.1 * 2.22 FROM test limit 1;", dt);
4188  c("SELECT 1.01 * 1.00001 FROM test limit 1;", dt);
4189  c("SELECT 11.1 * 2.222222222 FROM test limit 1;", dt);
4190  c("SELECT 9.99 * 9999.9 FROM test limit 1;", dt);
4191  c("SELECT 9.22337203685477 * 9.223 FROM test limit 1;", dt);
4192  c("SELECT 3.0+8 from test limit 1;", dt);
4193  c("SELECT 3.0*8 from test limit 1;", dt);
4194  c("SELECT 1.79769e+308 * 0.1 FROM test limit 1;", dt);
4195  c("SELECT COUNT(*) FROM test WHERE 3.0+8 < 30;", dt);
4196  c("SELECT COUNT(*) FROM test WHERE 3.0*8 > 30.01;", dt);
4197  c("SELECT COUNT(*) FROM test WHERE 3.0*8 > 30.0001;", dt);
4198  c("SELECT COUNT(*) FROM test WHERE ff + 3.0*8 < 60.0/2;", dt);
4199  c("SELECT COUNT(*) FROM test WHERE t > 0 AND t = t;", dt);
4200  c("SELECT COUNT(*) FROM test WHERE t > 0 AND t <> t;", dt);
4201  c("SELECT COUNT(*) FROM test WHERE t > 0 OR t = t;", dt);
4202  c("SELECT COUNT(*) FROM test WHERE t > 0 OR t <> t;", dt);
4203  c("SELECT COUNT(*) FROM test where (604=575) OR (33.0<>12 AND 2.0001e+4>20000.9) "
4204  "OR (NOT t>=t OR f<>f OR (x=x AND x-x=0));",
4205  dt);
4206  }
4207 }
4208 
4209 TEST(Select, OverflowAndUnderFlow) {
4211  SKIP_NO_GPU();
4212  c("SELECT COUNT(*) FROM test WHERE z + 32600 > 0;", dt);
4213  c("SELECT COUNT(*) FROM test WHERE z + 32666 > 0;", dt);
4214  c("SELECT COUNT(*) FROM test WHERE -32670 - z < 0;", dt);
4215  c("SELECT COUNT(*) FROM test WHERE (z + 16333) * 2 > 0;", dt);
4216  EXPECT_THROW(
4217  run_multiple_agg("SELECT COUNT(*) FROM test WHERE x + 2147483640 > 0;", dt),
4218  std::runtime_error);
4219  EXPECT_THROW(
4220  run_multiple_agg("SELECT COUNT(*) FROM test WHERE -x - 2147483642 < 0;", dt),
4221  std::runtime_error);
4222  c("SELECT COUNT(*) FROM test WHERE t + 9223372036854774000 > 0;", dt);
4223  EXPECT_THROW(run_multiple_agg(
4224  "SELECT COUNT(*) FROM test WHERE t + 9223372036854775000 > 0;", dt),
4225  std::runtime_error);
4226  EXPECT_THROW(run_multiple_agg(
4227  "SELECT COUNT(*) FROM test WHERE -t - 9223372036854775000 < 0;", dt),
4228  std::runtime_error);
4229  EXPECT_THROW(run_multiple_agg("SELECT COUNT(*) FROM test WHERE ofd + x - 2 > 0;", dt),
4230  std::runtime_error);
4231  EXPECT_THROW(run_multiple_agg(
4232  "SELECT COUNT(*) FROM test WHERE ufd * 3 - ofd * 1024 < -2;", dt),
4233  std::runtime_error);
4234  EXPECT_THROW(run_multiple_agg("SELECT COUNT(*) FROM test WHERE ofd * 2 > 0;", dt),
4235  std::runtime_error);
4236  EXPECT_THROW(run_multiple_agg("SELECT COUNT(*) FROM test WHERE ofq + 1 > 0;", dt),
4237  std::runtime_error);
4238  EXPECT_THROW(
4240  "SELECT COUNT(*) FROM test WHERE -ufq - 9223372036854775000 > 0;", dt),
4241  std::runtime_error);
4242  EXPECT_THROW(
4243  run_multiple_agg("SELECT COUNT(*) FROM test WHERE -92233720368547758 - ofq <= 0;",
4244  dt),
4245  std::runtime_error);
4246  c("SELECT cast((z - -32666) * 0.000190 as int) as key0, COUNT(*) AS val FROM test "
4247  "WHERE (z >= -32666 AND z < 31496) GROUP BY key0 HAVING key0 >= 0 AND key0 < 12 "
4248  "ORDER BY val DESC LIMIT 50 OFFSET 0;",
4249  dt);
4250  EXPECT_THROW(run_multiple_agg("SELECT dd * 2000000000000000 FROM test LIMIT 5;", dt),
4251  std::runtime_error);
4252  c("SELECT dd * 200000000000000 FROM test ORDER BY dd ASC LIMIT 5;",
4253  dt); // overflow avoided through decimal mul optimization
4254  c("SELECT COUNT(*) FROM test WHERE dd + 2.0000000000000009 > 110.0;",
4255  dt); // no overflow in the cast
4256  EXPECT_THROW(
4258  "SELECT COUNT(*) FROM test WHERE dd + 2.00000000000000099 > 110.0;", dt),
4259  std::runtime_error); // overflow in the cast due to higher precision
4260  c("SELECT dd / 2.00000009 FROM test ORDER BY dd ASC LIMIT 1;",
4261  dt); // dividend still fits after cast and division upscaling
4262  EXPECT_THROW(run_multiple_agg("SELECT dd / 2.000000099 FROM test LIMIT 1;", dt),
4263  std::runtime_error); // dividend overflows after cast and division
4264  // upscaling due to higher precision
4265  c("SELECT (dd - 40.6364668888) / 2 FROM test ORDER BY dd ASC LIMIT 1;",
4266  dt); // decimal div by const optimization avoids overflow
4267  c("SELECT (dd - 40.6364668888) / x FROM test ORDER BY dd ASC LIMIT 1;",
4268  dt); // decimal div by int cast optimization avoids overflow
4269  c("SELECT (dd - 40.63646688) / dd FROM test ORDER BY dd ASC LIMIT 1;",
4270  dt); // dividend still fits after upscaling from cast and division
4271  EXPECT_THROW(run_multiple_agg("select (dd-40.6364668888)/dd from test limit 1;", dt),
4272  std::runtime_error); // dividend overflows on upscaling on a slightly
4273  // higher precision, test detection
4274  EXPECT_THROW(run_multiple_agg("SELECT CAST(x * 10000 AS SMALLINT) FROM test;", dt),
4275  std::runtime_error);
4276  EXPECT_THROW(run_multiple_agg("SELECT CAST(y * 1000 AS SMALLINT) FROM test;", dt),
4277  std::runtime_error);
4278  EXPECT_THROW(run_multiple_agg("SELECT CAST(x * -10000 AS SMALLINT) FROM test;", dt),
4279  std::runtime_error);
4280  EXPECT_THROW(run_multiple_agg("SELECT CAST(y * -1000 AS SMALLINT) FROM test;", dt),
4281  std::runtime_error);
4282  c("SELECT cast((cast(z as int) - -32666) *0.000190 as int) as key0, "
4283  "COUNT(*) AS val FROM test WHERE (z >= -32666 AND z < 31496) "
4284  "GROUP BY key0 HAVING key0 >= 0 AND key0 < 12 ORDER BY val "
4285  "DESC LIMIT 50 OFFSET 0;",
4286  dt);
4287  c("select -1 * dd as expr from test order by expr asc;", dt);
4288  c("select dd * -1 as expr from test order by expr asc;", dt);
4289  c("select (dd - 1000000111.10) * dd as expr from test order by expr asc;", dt);
4290  c("select dd * (dd - 1000000111.10) as expr from test order by expr asc;", dt);
4291  // avoiding overflows in decimal compares against higher precision literals:
4292  // truncate literals based on the other side's precision, e.g. for d which is
4293  // DECIMAL(14,2)
4294  c("select count(*) from big_decimal_range_test where (d > 4.955357142857142);",
4295  dt); // compare with 4.955
4296  c("select count(*) from big_decimal_range_test where (d >= 4.955357142857142);",
4297  dt); // compare with 4.955
4298  c("select count(*) from big_decimal_range_test where (d < 4.955357142857142);",
4299  dt); // compare with 4.955
4300  c("select count(*) from big_decimal_range_test where (d <= 4.955357142857142);",
4301  dt); // compare with 4.955
4302  c("select count(*) from big_decimal_range_test where (d >= 4.950357142857142);",
4303  dt); // compare with 4.951
4304  c("select count(*) from big_decimal_range_test where (d < 4.950357142857142);",
4305  dt); // compare with 4.951
4306  c("select count(*) from big_decimal_range_test where (d < 59016609.300000056);",
4307  dt); // compare with 59016609.301
4308  c("select count(*) from test where (t*123456 > 9681668.33071388567);",
4309  dt); // compare with 9681668.3
4310  c("select count(*) from test where (x*12345678 < 9681668.33071388567);",
4311  dt); // compare with 9681668.3
4312  c("select count(*) from test where (z*12345678 < 9681668.33071388567);",
4313  dt); // compare with 9681668.3
4314  c("select count(*) from test where dd <= 111.222;", dt);
4315  c("select count(*) from test where dd >= -15264923.533545015;", dt);
4316  // avoiding overflows with constant folding and pushed down casts
4317  c("select count(*) + (604*575) from test;", dt);
4318  c("select count(*) - (604*575) from test;", dt);
4319  c("select count(*) * (604*575) from test;", dt);
4320  c("select (604*575) / count(*) from test;", dt);
4321  c("select (400604+575) / count(*) from test;", dt);
4322  c("select cast(count(*) as DOUBLE) + (604*575) from test;", dt);
4323  c("select cast(count(*) as DOUBLE) - (604*575) from test;", dt);
4324  c("select cast(count(*) as DOUBLE) * (604*575) from test;", dt);
4325  c("select (604*575) / cast(count(*) as DOUBLE) from test;", dt);
4326  c("select (12345-123456789012345) / cast(count(*) as DOUBLE) from test;", dt);
4327  ASSERT_EQ(
4328  0,
4329  v<int64_t>(run_simple_agg("SELECT COUNT(CAST(EXTRACT(QUARTER FROM CAST(NULL AS "
4330  "TIMESTAMP)) AS BIGINT) - 1) FROM test;",
4331  dt)));
4332  }
4333 }
4334 
4335 TEST(Select, BooleanColumn) {
4337  SKIP_NO_GPU();
4338  ASSERT_EQ(g_num_rows + g_num_rows / 2,
4339  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE b;", dt)));
4340  ASSERT_EQ(g_num_rows / 2,
4341  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE NOT b;", dt)));
4342  ASSERT_EQ(
4343  g_num_rows + g_num_rows / 2,
4344  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE x < 8 AND b;", dt)));
4345  ASSERT_EQ(0,
4346  v<int64_t>(run_simple_agg(
4347  "SELECT COUNT(*) FROM test WHERE x < 8 AND NOT b;", dt)));
4348  ASSERT_EQ(5,
4349  v<int64_t>(
4350  run_simple_agg("SELECT COUNT(*) FROM test WHERE x > 7 OR false;", dt)));
4351  ASSERT_EQ(7,
4352  v<int64_t>(run_simple_agg(
4353  "SELECT MAX(x) FROM test WHERE b = CAST('t' AS boolean);", dt)));
4354  ASSERT_EQ(3 * g_num_rows,
4355  v<int64_t>(run_simple_agg(
4356  " SELECT SUM(2 *(CASE when x = 7 then 1 else 0 END)) FROM test;", dt)));
4357  c("SELECT COUNT(*) AS n FROM test GROUP BY x = 7, b ORDER BY n;", dt);
4358  }
4359 }
4360 
4361 TEST(Select, UnsupportedCast) {
4363  SKIP_NO_GPU();
4364  EXPECT_THROW(run_multiple_agg("SELECT CAST(x AS VARCHAR) FROM test;", dt),
4365  std::runtime_error);
4366  EXPECT_THROW(run_multiple_agg("SELECT CAST(f AS VARCHAR) FROM test;", dt),
4367  std::runtime_error);
4368  EXPECT_THROW(run_multiple_agg("SELECT CAST(d AS VARCHAR) FROM test;", dt),
4369  std::runtime_error);
4370  EXPECT_THROW(run_multiple_agg("SELECT CAST(f AS DECIMAL) FROM test;", dt),
4371  std::runtime_error);
4372  }
4373 }
4374 
4375 TEST(Select, CastFromLiteral) {
4377  SKIP_NO_GPU();
4378  c("SELECT CAST(2.3 AS TINYINT) FROM test;", dt);
4379  c("SELECT CAST(2.3 AS SMALLINT) FROM test;", dt);
4380  c("SELECT CAST(2.3 AS INT) FROM test;", dt);
4381  c("SELECT CAST(2.3 AS BIGINT) FROM test;", dt);
4382  c("SELECT CAST(2.3 AS FLOAT) FROM test;", dt);
4383  c("SELECT CAST(2.3 AS DOUBLE) FROM test;", dt);
4384  c("SELECT CAST(2.3 AS DECIMAL(2, 1)) FROM test;", dt);
4385  c("SELECT CAST(2.3 AS NUMERIC(2, 1)) FROM test;", dt);
4386  c("SELECT CAST(CAST(10 AS float) / CAST(3600 as float) AS float) FROM test LIMIT 1;",
4387  dt);
4388  c("SELECT CAST(CAST(10 AS double) / CAST(3600 as double) AS double) FROM test LIMIT "
4389  "1;",
4390  dt);
4391  c("SELECT z from test where z = -78;", dt);
4392  }
4393 }
4394 
4395 TEST(Select, CastFromNull) {
4397  SKIP_NO_GPU();
4398  c("SELECT CAST(NULL AS TINYINT) FROM test;", dt);
4399  c("SELECT CAST(NULL AS SMALLINT) FROM test;", dt);
4400  c("SELECT CAST(NULL AS INT) FROM test;", dt);
4401  c("SELECT CAST(NULL AS BIGINT) FROM test;", dt);
4402  c("SELECT CAST(NULL AS FLOAT) FROM test;", dt);
4403  c("SELECT CAST(NULL AS DOUBLE) FROM test;", dt);
4404  c("SELECT CAST(NULL AS DECIMAL) FROM test;", dt);
4405  c("SELECT CAST(NULL AS NUMERIC) FROM test;", dt);
4406  }
4407 }
4408 
4409 TEST(Select, DropSecondaryDB) {
4410  run_ddl_statement("CREATE DATABASE SECONDARY_DB;");
4411  run_ddl_statement("DROP DATABASE SECONDARY_DB;");
4412 }
4413 
4414 TEST(Select, CastDecimalToDecimal) {
4415  run_ddl_statement("DROP TABLE IF EXISTS decimal_to_decimal_test;");
4416  run_ddl_statement("create table decimal_to_decimal_test (id INT, val DECIMAL(10,5));");
4417  run_multiple_agg("insert into decimal_to_decimal_test VALUES (1, 456.78956)",
4419  run_multiple_agg("insert into decimal_to_decimal_test VALUES (2, 456.12345)",
4421  run_multiple_agg("insert into decimal_to_decimal_test VALUES (-1, -456.78956)",
4423  run_multiple_agg("insert into decimal_to_decimal_test VALUES (-2, -456.12345)",
4425 
4427  SKIP_NO_GPU();
4428 
4429  ASSERT_TRUE(
4430  approx_eq(456.78956,
4431  v<double>(run_simple_agg(
4432  "SELECT val FROM decimal_to_decimal_test WHERE id = 1;", dt))));
4433  ASSERT_TRUE(
4434  approx_eq(-456.78956,
4435  v<double>(run_simple_agg(
4436  "SELECT val FROM decimal_to_decimal_test WHERE id = -1;", dt))));
4437  ASSERT_TRUE(
4438  approx_eq(456.12345,
4439  v<double>(run_simple_agg(
4440  "SELECT val FROM decimal_to_decimal_test WHERE id = 2;", dt))));
4441  ASSERT_TRUE(
4442  approx_eq(-456.12345,
4443  v<double>(run_simple_agg(
4444  "SELECT val FROM decimal_to_decimal_test WHERE id = -2;", dt))));
4445 
4446  ASSERT_TRUE(
4447  approx_eq(456.7896,
4448  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,4)) FROM "
4449  "decimal_to_decimal_test WHERE id = 1;",
4450  dt))));
4451  ASSERT_TRUE(
4452  approx_eq(-456.7896,
4453  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,4)) FROM "
4454  "decimal_to_decimal_test WHERE id = -1;",
4455  dt))));
4456  ASSERT_TRUE(
4457  approx_eq(456.123,
4458  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,4)) FROM "
4459  "decimal_to_decimal_test WHERE id = 2;",
4460  dt))));
4461  ASSERT_TRUE(
4462  approx_eq(-456.123,
4463  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,4)) FROM "
4464  "decimal_to_decimal_test WHERE id = -2;",
4465  dt))));
4466 
4467  ASSERT_TRUE(
4468  approx_eq(456.790,
4469  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,3)) FROM "
4470  "decimal_to_decimal_test WHERE id = 1;",
4471  dt))));
4472  ASSERT_TRUE(
4473  approx_eq(-456.790,
4474  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,3)) FROM "
4475  "decimal_to_decimal_test WHERE id = -1;",
4476  dt))));
4477  ASSERT_TRUE(
4478  approx_eq(456.1234,
4479  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,3)) FROM "
4480  "decimal_to_decimal_test WHERE id = 2;",
4481  dt))));
4482  ASSERT_TRUE(
4483  approx_eq(-456.1234,
4484  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,3)) FROM "
4485  "decimal_to_decimal_test WHERE id = -2;",
4486  dt))));
4487 
4488  ASSERT_TRUE(
4489  approx_eq(456.79,
4490  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,2)) FROM "
4491  "decimal_to_decimal_test WHERE id = 1;",
4492  dt))));
4493  ASSERT_TRUE(
4494  approx_eq(-456.79,
4495  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,2)) FROM "
4496  "decimal_to_decimal_test WHERE id = -1;",
4497  dt))));
4498  ASSERT_TRUE(
4499  approx_eq(456.12,
4500  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,2)) FROM "
4501  "decimal_to_decimal_test WHERE id = 2;",
4502  dt))));
4503  ASSERT_TRUE(
4504  approx_eq(-456.12,
4505  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,2)) FROM "
4506  "decimal_to_decimal_test WHERE id = -2;",
4507  dt))));
4508 
4509  ASSERT_TRUE(
4510  approx_eq(456.8,
4511  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,1)) FROM "
4512  "decimal_to_decimal_test WHERE id = 1;",
4513  dt))));
4514  ASSERT_TRUE(
4515  approx_eq(-456.8,
4516  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,1)) FROM "
4517  "decimal_to_decimal_test WHERE id = -1;",
4518  dt))));
4519  ASSERT_TRUE(
4520  approx_eq(456.1,
4521  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,1)) FROM "
4522  "decimal_to_decimal_test WHERE id = 2;",
4523  dt))));
4524  ASSERT_TRUE(
4525  approx_eq(-456.1,
4526  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,1)) FROM "
4527  "decimal_to_decimal_test WHERE id = -2;",
4528  dt))));
4529  ASSERT_TRUE(
4530  approx_eq(457,
4531  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,0)) FROM "
4532  "decimal_to_decimal_test WHERE id = 1;",
4533  dt))));
4534  ASSERT_TRUE(
4535  approx_eq(-457,
4536  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,0)) FROM "
4537  "decimal_to_decimal_test WHERE id = -1;",
4538  dt))));
4539  ASSERT_TRUE(
4540  approx_eq(456,
4541  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,0)) FROM "
4542  "decimal_to_decimal_test WHERE id = 2;",
4543  dt))));
4544  ASSERT_TRUE(
4545  approx_eq(-456,
4546  v<double>(run_simple_agg("SELECT CAST(val AS DECIMAL(10,0)) FROM "
4547  "decimal_to_decimal_test WHERE id = -2;",
4548  dt))));
4549 
4550  ASSERT_EQ(457,
4551  v<int64_t>(run_simple_agg(
4552  "SELECT CAST(val AS BIGINT) FROM decimal_to_decimal_test WHERE id = 1;",
4553  dt)));
4554  ASSERT_EQ(
4555  -457,
4556  v<int64_t>(run_simple_agg(
4557  "SELECT CAST(val AS BIGINT) FROM decimal_to_decimal_test WHERE id = -1;",
4558  dt)));
4559  ASSERT_EQ(456,
4560  v<int64_t>(run_simple_agg(
4561  "SELECT CAST(val AS BIGINT) FROM decimal_to_decimal_test WHERE id = 2;",
4562  dt)));
4563  ASSERT_EQ(
4564  -456,
4565  v<int64_t>(run_simple_agg(
4566  "SELECT CAST(val AS BIGINT) FROM decimal_to_decimal_test WHERE id = -2;",
4567  dt)));
4568  }
4569 }
4570 
4571 TEST(Select, ColumnWidths) {
4573  SKIP_NO_GPU();
4574  c("SELECT DISTINCT x FROM test_inner ORDER BY x;", dt);
4575  c("SELECT DISTINCT y FROM test_inner ORDER BY y;", dt);
4576  c("SELECT DISTINCT xx FROM test_inner ORDER BY xx;", dt);
4577  c("SELECT x, xx, y FROM test_inner GROUP BY x, xx, y ORDER BY x, xx, y;", dt);
4578  c("SELECT x, xx, y FROM test_inner GROUP BY x, xx, y ORDER BY x, xx, y;", dt);
4579  c("SELECT DISTINCT str from test_inner ORDER BY str;", dt);
4580  c("SELECT DISTINCT t FROM test ORDER BY t;", dt);
4581  c("SELECT DISTINCT t, z FROM test GROUP BY t, z ORDER BY t, z;", dt);
4582  c("SELECT fn from test where fn < -100.7 ORDER BY fn;", dt);
4583  c("SELECT fixed_str, SUM(f)/SUM(t) FROM test WHERE fixed_str IN ('foo','bar') GROUP "
4584  "BY fixed_str ORDER BY "
4585  "fixed_str;",
4586  dt);
4587  }
4588 }
4589 
4590 TEST(Select, TimeInterval) {
4592  SKIP_NO_GPU();
4593  ASSERT_EQ(
4594  60 * 60 * 1000L,
4595  v<int64_t>(run_simple_agg("SELECT INTERVAL '1' HOUR FROM test LIMIT 1;", dt)));
4596  ASSERT_EQ(
4597  24 * 60 * 60 * 1000L,
4598  v<int64_t>(run_simple_agg("SELECT INTERVAL '1' DAY FROM test LIMIT 1;", dt)));
4599  ASSERT_EQ(1L,
4600  v<int64_t>(run_simple_agg(
4601  "SELECT (INTERVAL '1' YEAR)/12 FROM test order by o LIMIT 1;", dt)));
4602  ASSERT_EQ(
4603  1L,
4604  v<int64_t>(run_simple_agg(
4605  "SELECT INTERVAL '1' MONTH FROM test group by m order by m LIMIT 1;", dt)));
4606  ASSERT_EQ(
4607  2 * g_num_rows,
4608  v<int64_t>(run_simple_agg(
4609  "SELECT COUNT(*) FROM test WHERE INTERVAL '1' MONTH < INTERVAL '2' MONTH;",
4610  dt)));
4611  ASSERT_EQ(
4612  2 * g_num_rows,
4613  v<int64_t>(run_simple_agg(
4614  "SELECT COUNT(*) FROM test WHERE INTERVAL '1' DAY < INTERVAL '2' DAY;", dt)));
4615  ASSERT_EQ(2 * g_num_rows,
4616  v<int64_t>(run_simple_agg(
4617  "SELECT COUNT(*) FROM test GROUP BY INTERVAL '1' DAY;", dt)));
4618  ASSERT_EQ(3 * 60 * 60 * 1000L,
4619  v<int64_t>(
4620  run_simple_agg("SELECT 3 * INTERVAL '1' HOUR FROM test LIMIT 1;", dt)));
4621  ASSERT_EQ(3 * 60 * 60 * 1000L,
4622  v<int64_t>(
4623  run_simple_agg("SELECT INTERVAL '1' HOUR * 3 FROM test LIMIT 1;", dt)));
4624  ASSERT_EQ(7L,
4625  v<int64_t>(run_simple_agg(
4626  "SELECT INTERVAL '1' MONTH * x FROM test WHERE x <> 8 LIMIT 1;", dt)));
4627  ASSERT_EQ(7L,
4628  v<int64_t>(run_simple_agg(
4629  "SELECT x * INTERVAL '1' MONTH FROM test WHERE x <> 8 LIMIT 1;", dt)));
4630  ASSERT_EQ(42L,
4631  v<int64_t>(run_simple_agg(
4632  "SELECT INTERVAL '1' MONTH * y FROM test WHERE y <> 43 LIMIT 1;", dt)));
4633  ASSERT_EQ(42L,
4634  v<int64_t>(run_simple_agg(
4635  "SELECT y * INTERVAL '1' MONTH FROM test WHERE y <> 43 LIMIT 1;", dt)));
4636  ASSERT_EQ(
4637  1002L,
4638  v<int64_t>(run_simple_agg(
4639  "SELECT INTERVAL '1' MONTH * t FROM test WHERE t <> 1001 LIMIT 1;", dt)));
4640  ASSERT_EQ(
4641  1002L,
4642  v<int64_t>(run_simple_agg(
4643  "SELECT t * INTERVAL '1' MONTH FROM test WHERE t <> 1001 LIMIT 1;", dt)));
4644  ASSERT_EQ(
4645  3L,
4646  v<int64_t>(run_simple_agg(
4647  "SELECT INTERVAL '1' MONTH + INTERVAL '2' MONTH FROM test LIMIT 1;", dt)));
4648  ASSERT_EQ(
4649  1388534400L,
4650  v<int64_t>(run_simple_agg("SELECT CAST(m AS date) + CAST(TRUNCATE(-1 * "
4651  "(EXTRACT(DOY FROM m) - 1), 0) AS INTEGER) * INTERVAL "
4652  "'1' DAY AS g FROM test GROUP BY g;",
4653  dt)));
4654  ASSERT_EQ(
4655  1417392000L,
4656  v<int64_t>(run_simple_agg("SELECT CAST(m AS date) + CAST(TRUNCATE(-1 * "
4657  "(EXTRACT(DAY FROM m) - 1), 0) AS INTEGER) * INTERVAL "
4658  "'1' DAY AS g FROM test GROUP BY g;",
4659  dt)));
4660  ASSERT_EQ(1418508000L,
4661  v<int64_t>(run_simple_agg("SELECT CAST(m AS date) + EXTRACT(HOUR FROM m) * "
4662  "INTERVAL '1' HOUR AS g FROM test GROUP BY g;",
4663  dt)));
4664  ASSERT_EQ(
4665  1388534400L,
4666  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(SQL_TSI_DAY, CAST(TRUNCATE(-1 * "
4667  "(EXTRACT(DOY from m) - 1), 0) AS INTEGER), "
4668  "CAST(m AS DATE)) AS g FROM test GROUP BY g;",
4669  dt)));
4670  ASSERT_EQ(
4671  1417392000L,
4672  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(SQL_TSI_DAY, CAST(TRUNCATE(-1 * "
4673  "(EXTRACT(DAY from m) - 1), 0) AS INTEGER), "
4674  "CAST(m AS DATE)) AS g FROM test GROUP BY g;",
4675  dt)));
4676  ASSERT_EQ(1418508000L,
4677  v<int64_t>(run_simple_agg(
4678  "SELECT TIMESTAMPADD(SQL_TSI_HOUR, EXTRACT(HOUR from "
4679  "m), CAST(m AS DATE)) AS g FROM test GROUP BY g order by g;",
4680  dt)));
4681 
4682  ASSERT_EQ(1,
4683  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' + INTERVAL '1' YEAR) = "
4684  "DATE '2009-01-31' from test limit 1;",
4685  dt)));
4686  ASSERT_EQ(1,
4687  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' + INTERVAL '5' YEAR) = "
4688  "DATE '2013-01-31' from test limit 1;",
4689  dt)));
4690  ASSERT_EQ(1,
4691  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' - INTERVAL '1' YEAR) = "
4692  "DATE '2007-01-31' from test limit 1;",
4693  dt)));
4694  ASSERT_EQ(1,
4695  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' - INTERVAL '4' YEAR) = "
4696  "DATE '2004-01-31' from test limit 1;",
4697  dt)));
4698  ASSERT_EQ(1,
4699  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' + INTERVAL '1' MONTH) "
4700  "= DATE '2008-02-29' from test limit 1;",
4701  dt)));
4702  ASSERT_EQ(1,
4703  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' + INTERVAL '5' MONTH) "
4704  "= DATE '2008-06-30' from test limit 1;",
4705  dt)));
4706  ASSERT_EQ(1,
4707  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' - INTERVAL '1' MONTH) "
4708  "= DATE '2007-12-31' from test limit 1;",
4709  dt)));
4710  ASSERT_EQ(1,
4711  v<int64_t>(run_simple_agg("SELECT (DATE '2008-1-31' - INTERVAL '4' MONTH) "
4712  "= DATE '2007-09-30' from test limit 1;",
4713  dt)));
4714  ASSERT_EQ(1,
4715  v<int64_t>(run_simple_agg("SELECT (DATE '2008-2-28' + INTERVAL '1' DAY) = "
4716  "DATE '2008-02-29' from test limit 1;",
4717  dt)));
4718  ASSERT_EQ(1,
4719  v<int64_t>(run_simple_agg("SELECT (DATE '2009-2-28' + INTERVAL '1' DAY) = "
4720  "DATE '2009-03-01' from test limit 1;",
4721  dt)));
4722  ASSERT_EQ(1,
4723  v<int64_t>(run_simple_agg("SELECT (DATE '2008-2-28' + INTERVAL '4' DAY) = "
4724  "DATE '2008-03-03' from test limit 1;",
4725  dt)));
4726  ASSERT_EQ(1,
4727  v<int64_t>(run_simple_agg("SELECT (DATE '2009-2-28' + INTERVAL '4' DAY) = "
4728  "DATE '2009-03-04' from test limit 1;",
4729  dt)));
4730  ASSERT_EQ(1,
4731  v<int64_t>(run_simple_agg("SELECT (DATE '2008-03-01' - INTERVAL '1' DAY) = "
4732  "DATE '2008-02-29' from test limit 1;",
4733  dt)));
4734  ASSERT_EQ(1,
4735  v<int64_t>(run_simple_agg("SELECT (DATE '2009-03-01' - INTERVAL '1' DAY) = "
4736  "DATE '2009-02-28' from test limit 1;",
4737  dt)));
4738  ASSERT_EQ(1,
4739  v<int64_t>(run_simple_agg("SELECT (DATE '2008-03-03' - INTERVAL '4' DAY) = "
4740  "DATE '2008-02-28' from test limit 1;",
4741  dt)));
4742  ASSERT_EQ(1,
4743  v<int64_t>(run_simple_agg("SELECT (DATE '2009-03-04' - INTERVAL '4' DAY) = "
4744  "DATE '2009-02-28' from test limit 1;",
4745  dt)));
4746  ASSERT_EQ(1,
4747  v<int64_t>(run_simple_agg(
4748  "SELECT m = TIMESTAMP '2014-12-13 22:23:15' from test limit 1;", dt)));
4749  ASSERT_EQ(1,
4750  v<int64_t>(run_simple_agg("SELECT (m + INTERVAL '1' SECOND) = TIMESTAMP "
4751  "'2014-12-13 22:23:16' from test limit 1;",
4752  dt)));
4753  ASSERT_EQ(1,
4754  v<int64_t>(run_simple_agg("SELECT (m + INTERVAL '1' MINUTE) = TIMESTAMP "
4755  "'2014-12-13 22:24:15' from test limit 1;",
4756  dt)));
4757  ASSERT_EQ(1,
4758  v<int64_t>(run_simple_agg("SELECT (m + INTERVAL '1' HOUR) = TIMESTAMP "
4759  "'2014-12-13 23:23:15' from test limit 1;",
4760  dt)));
4761  ASSERT_EQ(1,
4762  v<int64_t>(run_simple_agg("SELECT (m + INTERVAL '2' DAY) = TIMESTAMP "
4763  "'2014-12-15 22:23:15' from test limit 1;",
4764  dt)));
4765  ASSERT_EQ(1,
4766  v<int64_t>(run_simple_agg("SELECT (m + INTERVAL '1' MONTH) = TIMESTAMP "
4767  "'2015-01-13 22:23:15' from test limit 1;",
4768  dt)));
4769  ASSERT_EQ(1,
4770  v<int64_t>(run_simple_agg("SELECT (m + INTERVAL '1' YEAR) = TIMESTAMP "
4771  "'2015-12-13 22:23:15' from test limit 1;",
4772  dt)));
4773  ASSERT_EQ(
4774  1,
4775  v<int64_t>(run_simple_agg("SELECT (m - 5 * INTERVAL '1' SECOND) = TIMESTAMP "
4776  "'2014-12-13 22:23:10' from test limit 1;",
4777  dt)));
4778  ASSERT_EQ(
4779  1,
4780  v<int64_t>(run_simple_agg("SELECT (m - x * INTERVAL '1' MINUTE) = TIMESTAMP "
4781  "'2014-12-13 22:16:15' from test limit 1;",
4782  dt)));
4783  ASSERT_EQ(
4784  1,
4785  v<int64_t>(run_simple_agg("SELECT (m - 2 * x * INTERVAL '1' HOUR) = TIMESTAMP "
4786  "'2014-12-13 8:23:15' from test limit 1;",
4787  dt)));
4788  ASSERT_EQ(1,
4789  v<int64_t>(run_simple_agg("SELECT (m - x * INTERVAL '1' DAY) = TIMESTAMP "
4790  "'2014-12-06 22:23:15' from test limit 1;",
4791  dt)));
4792  ASSERT_EQ(1,
4793  v<int64_t>(run_simple_agg("SELECT (m - x * INTERVAL '1' MONTH) = TIMESTAMP "
4794  "'2014-05-13 22:23:15' from test limit 1;",
4795  dt)));
4796  ASSERT_EQ(1,
4797  v<int64_t>(run_simple_agg("SELECT (m - x * INTERVAL '1' YEAR) = TIMESTAMP "
4798  "'2007-12-13 22:23:15' from test limit 1;",
4799  dt)));
4800  ASSERT_EQ(1,
4801  v<int64_t>(run_simple_agg(
4802  "SELECT (m - INTERVAL '5' DAY + INTERVAL '2' HOUR - x * INTERVAL '2' "
4803  "SECOND) +"
4804  "(x - 1) * INTERVAL '1' MONTH - x * INTERVAL '10' YEAR = "
4805  "TIMESTAMP '1945-06-09 00:23:01' from test limit 1;",
4806  dt)));
4807  ASSERT_EQ(
4808  0,
4809  v<int64_t>(run_simple_agg(
4810  "select count(*) from test where m < CAST (o AS TIMESTAMP) + INTERVAL '10' "
4811  "YEAR AND m > CAST(o AS TIMESTAMP) - INTERVAL '10' YEAR;",
4812  dt)));
4813  ASSERT_EQ(
4814  15,
4815  v<int64_t>(run_simple_agg(
4816  "select count(*) from test where m < CAST (o AS TIMESTAMP) + INTERVAL '16' "
4817  "YEAR AND m > CAST(o AS TIMESTAMP) - INTERVAL '16' YEAR;",
4818  dt)));
4819 
4820  ASSERT_EQ(1,
4821  v<int64_t>(
4822  run_simple_agg("SELECT o = DATE '1999-09-09' from test limit 1;", dt)));
4823  ASSERT_EQ(1,
4824  v<int64_t>(run_simple_agg(
4825  "SELECT (o + INTERVAL '10' DAY) = DATE '1999-09-19' from test limit 1;",
4826  dt)));
4827  }
4828 }
4829 
4830 TEST(Select, UnsupportedNodes) {
4832  SKIP_NO_GPU();
4833  EXPECT_THROW(run_multiple_agg("SELECT 1 + 2;", dt), std::runtime_error);
4834  // MAT No longer throws a logicalValues gets a regular parse error'
4835  // EXPECT_THROW(run_multiple_agg("SELECT *;", dt), std::runtime_error);
4836  EXPECT_THROW(run_multiple_agg("SELECT x, COUNT(*) FROM test GROUP BY ROLLUP(x);", dt),
4837  std::runtime_error);
4838  }
4839 }
4840 
4841 TEST(Select, UnsupportedMultipleArgAggregate) {
4843  SKIP_NO_GPU();
4844  EXPECT_THROW(run_multiple_agg("SELECT COUNT(distinct x, y) FROM test;", dt),
4845  std::runtime_error);
4846  }
4847 }
4848 
4849 namespace Importer_NS {
4850 
4851 ArrayDatum StringToArray(const std::string& s,
4852  const SQLTypeInfo& ti,
4853  const CopyParams& copy_params);
4854 bool parseStringArray(const std::string& s,
4855  const CopyParams& copy_params,
4856  std::vector<std::string>& string_vec);
4857 
4858 } // namespace Importer_NS
4859 
4860 namespace {
4861 
4862 const size_t g_array_test_row_count{20};
4863 
4864 void import_array_test(const std::string& table_name) {
4865  CHECK_EQ(size_t(0), g_array_test_row_count % 4);
4866  auto& cat = QR::get()->getSession()->getCatalog();
4867  const auto td = cat.getMetadataForTable(table_name);
4868  CHECK(td);
4869  auto loader = QR::get()->getLoader(td);
4870  std::vector<std::unique_ptr<Importer_NS::TypedImportBuffer>> import_buffers;
4871  const auto col_descs =
4872  cat.getAllColumnMetadataForTable(td->tableId, false, false, false);
4873  for (const auto cd : col_descs) {
4874  import_buffers.emplace_back(new Importer_NS::TypedImportBuffer(
4875  cd,
4876  cd->columnType.get_compression() == kENCODING_DICT
4877  ? cat.getMetadataForDict(cd->columnType.get_comp_param())->stringDict.get()
4878  : nullptr));
4879  }
4880  Importer_NS::CopyParams copy_params;
4881  copy_params.array_begin = '{';
4882  copy_params.array_end = '}';
4883  for (size_t row_idx = 0; row_idx < g_array_test_row_count; ++row_idx) {
4884  for (const auto& import_buffer : import_buffers) {
4885  const auto& ti = import_buffer->getTypeInfo();
4886  switch (ti.get_type()) {
4887  case kINT:
4888  import_buffer->addInt(7 + row_idx);
4889  break;
4890  case kARRAY: {
4891  const auto& elem_ti = ti.get_elem_type();
4892  std::vector<std::string> array_elems;
4893  switch (elem_ti.get_type()) {
4894  case kBOOLEAN: {
4895  for (size_t i = 0; i < 3; ++i) {
4896  if (row_idx % 2) {
4897  array_elems.emplace_back("T");
4898  array_elems.emplace_back("F");
4899  } else {
4900  array_elems.emplace_back("F");
4901  array_elems.emplace_back("T");
4902  }
4903  }
4904  break;
4905  }
4906  case kTINYINT:
4907  for (size_t i = 0; i < 3; ++i) {
4908  array_elems.push_back(std::to_string(row_idx + i + 1));
4909  }
4910  break;
4911  case kSMALLINT:
4912  for (size_t i = 0; i < 3; ++i) {
4913  array_elems.push_back(std::to_string(row_idx + i + 1));
4914  }
4915  break;
4916  case kINT:
4917  for (size_t i = 0; i < 3; ++i) {
4918  array_elems.push_back(std::to_string((row_idx + i + 1) * 10));
4919  }
4920  break;
4921  case kBIGINT:
4922  for (size_t i = 0; i < 3; ++i) {
4923  array_elems.push_back(std::to_string((row_idx + i + 1) * 100));
4924  }
4925  break;
4926  case kTEXT:
4927  for (size_t i = 0; i < 3; ++i) {
4928  array_elems.emplace_back(2, 'a' + row_idx + i);
4929  }
4930  break;
4931  case kFLOAT:
4932  for (size_t i = 0; i < 3; ++i) {
4933  array_elems.emplace_back(std::to_string(row_idx + i + 1) + "." +
4934  std::to_string(row_idx + i + 1));
4935  }
4936  break;
4937  case kDOUBLE:
4938  for (size_t i = 0; i < 3; ++i) {
4939  array_elems.emplace_back(std::to_string(11 * (row_idx + i + 1)) + "." +
4940  std::to_string(row_idx + i + 1));
4941  }
4942  break;
4943  default:
4944  CHECK(false);
4945  }
4946  if (elem_ti.is_string()) {
4947  import_buffer->addDictEncodedStringArray({array_elems});
4948  } else {
4949  auto arr_str = "{" + boost::algorithm::join(array_elems, ",") + "}";
4950  import_buffer->addArray(StringToArray(arr_str, ti, copy_params));
4951  }
4952  break;
4953  }
4954  case kTEXT:
4955  import_buffer->addString("real_str" + std::to_string(row_idx));
4956  break;
4957  default:
4958  CHECK(false);
4959  }
4960  }
4961  }
4962  loader->load(import_buffers, g_array_test_row_count);
4963 }
4964 
4966  const std::string drop_old_gpu_sort_test{"DROP TABLE IF EXISTS gpu_sort_test;"};
4967  run_ddl_statement(drop_old_gpu_sort_test);
4968  g_sqlite_comparator.query(drop_old_gpu_sort_test);
4969  std::string create_query(
4970  "CREATE TABLE gpu_sort_test (x bigint, y int, z smallint, t tinyint)");
4971  run_ddl_statement(create_query + " WITH (fragment_size=2);");
4972  g_sqlite_comparator.query(create_query + ";");
4973  TestHelpers::ValuesGenerator gen("gpu_sort_test");
4974  for (size_t i = 0; i < 4; ++i) {
4975  const auto insert_query = gen(2, 2, 2, 2);
4977  g_sqlite_comparator.query(insert_query);
4978  }
4979  for (size_t i = 0; i < 6; ++i) {
4980  const auto insert_query = gen(16000, 16000, 16000, 127)