OmniSciDB  04ee39c94c
ExecuteTest.cpp File Reference
#include "TestHelpers.h"
#include "../Import/Importer.h"
#include "../Parser/parser.h"
#include "../QueryEngine/ArrowResultSet.h"
#include "../QueryEngine/Descriptors/RelAlgExecutionDescriptor.h"
#include "../QueryEngine/Execute.h"
#include "../QueryEngine/ResultSetReductionJIT.h"
#include "../QueryRunner/QueryRunner.h"
#include "../Shared/ConfigResolve.h"
#include "../Shared/TimeGM.h"
#include "../Shared/scope.h"
#include "../SqliteConnector/SqliteConnector.h"
#include "DistributedLoader.h"
#include <gtest/gtest.h>
#include <boost/algorithm/string.hpp>
#include <boost/any.hpp>
#include <boost/program_options.hpp>
#include <cmath>
#include <sstream>
+ Include dependency graph for ExecuteTest.cpp:

Go to the source code of this file.

Classes

struct  anonymous_namespace{ExecuteTest.cpp}::ShardInfo
 
struct  anonymous_namespace{ExecuteTest.cpp}::SharedDictionaryInfo
 
class  anonymous_namespace{ExecuteTest.cpp}::SQLiteComparator
 
class  JoinTest
 

Namespaces

 anonymous_namespace{ExecuteTest.cpp}
 
 Importer_NS
 

Macros

#define BASE_PATH   "./tmp"
 
#define SKIP_NO_GPU()
 
#define SKIP_ALL_ON_AGGREGATOR()
 
#define SKIP_ON_AGGREGATOR(EXP)
 

Typedefs

using QR = QueryRunner::QueryRunner
 

Functions

size_t anonymous_namespace{ExecuteTest.cpp}::choose_shard_count ()
 
std::string anonymous_namespace{ExecuteTest.cpp}::build_create_table_statement (const std::string &columns_definition, const std::string &table_name, const ShardInfo &shard_info, const std::vector< SharedDictionaryInfo > &shared_dict_info, const size_t fragment_size, const bool delete_support=true, const bool replicated=false)
 
std::shared_ptr< ResultSetanonymous_namespace{ExecuteTest.cpp}::run_multiple_agg (const string &query_str, const ExecutorDeviceType device_type, const bool allow_loop_joins)
 
std::shared_ptr< ResultSetanonymous_namespace{ExecuteTest.cpp}::run_multiple_agg (const string &query_str, const ExecutorDeviceType device_type)
 
TargetValue anonymous_namespace{ExecuteTest.cpp}::run_simple_agg (const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
 
TargetValue anonymous_namespace{ExecuteTest.cpp}::get_first_target (const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true)
 
void anonymous_namespace{ExecuteTest.cpp}::run_ddl_statement (const std::string &create_table_stmt)
 
bool anonymous_namespace{ExecuteTest.cpp}::skip_tests (const ExecutorDeviceType device_type)
 
bool anonymous_namespace{ExecuteTest.cpp}::approx_eq (const double v, const double target, const double eps=0.01)
 
int anonymous_namespace{ExecuteTest.cpp}::parse_fractional_seconds (uint sfrac, int ntotal, SQLTypeInfo &ti)
 
void anonymous_namespace{ExecuteTest.cpp}::c (const std::string &query_string, const ExecutorDeviceType device_type)
 
void anonymous_namespace{ExecuteTest.cpp}::c (const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
 
void anonymous_namespace{ExecuteTest.cpp}::cta (const std::string &query_string, const ExecutorDeviceType device_type)
 
void anonymous_namespace{ExecuteTest.cpp}::c_arrow (const std::string &query_string, const ExecutorDeviceType device_type)
 
bool validate_statement_syntax (const std::string &stmt)
 
void anonymous_namespace{ExecuteTest.cpp}::validate_storage_options (const std::pair< std::string, bool > type_meta, const std::pair< std::string, std::vector< std::string >> values)
 
 TEST (Create, StorageOptions)
 
 TEST (Insert, ShardedTableWithGeo)
 
 TEST (Insert, NullArrayNullEmpty)
 
 TEST (Insert, DictBoundary)
 
 TEST (KeyForString, KeyForString)
 
 TEST (Select, IsTrue)
 
 TEST (Select, NullGroupBy)
 
 TEST (Select, FilterAndSimpleAggregation)
 
 TEST (Select, AggregateOnEmptyTable)
 
 TEST (Select, LimitAndOffset)
 
 TEST (Select, FloatAndDoubleTests)
 
 TEST (Select, FilterShortCircuit)
 
 TEST (Select, FilterAndMultipleAggregation)
 
 TEST (Select, GroupBy)
 
 TEST (Select, FilterAndGroupBy)
 
 TEST (Select, GroupByBoundariesAndNull)
 
 TEST (Select, Arrays)
 
 TEST (Select, FilterCastToDecimal)
 
 TEST (Select, FilterAndGroupByMultipleAgg)
 
 TEST (Select, GroupByKeylessAndNotKeyless)
 
 TEST (Select, Having)
 
 TEST (Select, CountDistinct)
 
 TEST (Select, ApproxCountDistinct)
 
 TEST (Select, ScanNoAggregation)
 
 TEST (Select, OrderBy)
 
 TEST (Select, TopKHeap)
 
 TEST (Select, ComplexQueries)
 
 TEST (Select, MultiStepQueries)
 
 TEST (Select, GroupByPushDownFilterIntoExprRange)
 
 TEST (Select, GroupByExprNoFilterNoAggregate)
 
 TEST (Select, DistinctProjection)
 
 TEST (Select, Case)
 
 TEST (Select, Strings)
 
 TEST (Select, SharedDictionary)
 
 TEST (Select, StringCompare)
 
 TEST (Select, StringsNoneEncoding)
 
void anonymous_namespace{ExecuteTest.cpp}::check_date_trunc_groups (const ResultSet &rows)
 
void anonymous_namespace{ExecuteTest.cpp}::check_one_date_trunc_group (const ResultSet &rows, const int64_t ref_ts)
 
void anonymous_namespace{ExecuteTest.cpp}::check_one_date_trunc_group_with_agg (const ResultSet &rows, const int64_t ref_ts, const int64_t ref_agg)
 
 TEST (Select, Time)
 
 TEST (Select, In)
 
 TEST (Select, DivByZero)
 
 TEST (Select, ReturnNullFromDivByZero)
 
 TEST (Select, ConstantFolding)
 
 TEST (Select, OverflowAndUnderFlow)
 
 TEST (Select, BooleanColumn)
 
 TEST (Select, UnsupportedCast)
 
 TEST (Select, CastFromLiteral)
 
 TEST (Select, CastFromNull)
 
 TEST (Select, DropSecondaryDB)
 
 TEST (Select, CastDecimalToDecimal)
 
 TEST (Select, ColumnWidths)
 
 TEST (Select, TimeInterval)
 
 TEST (Select, UnsupportedNodes)
 
 TEST (Select, UnsupportedMultipleArgAggregate)
 
ArrayDatum Importer_NS::StringToArray (const std::string &s, const SQLTypeInfo &ti, const CopyParams &copy_params)
 
bool Importer_NS::parseStringArray (const std::string &s, const CopyParams &copy_params, std::vector< std::string > &string_vec)
 
void anonymous_namespace{ExecuteTest.cpp}::import_array_test (const std::string &table_name)
 
void anonymous_namespace{ExecuteTest.cpp}::import_gpu_sort_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_query_rewrite_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_big_decimal_range_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_decimal_compression_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_subquery_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_text_group_by_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_join_test (bool with_delete_support)
 
void anonymous_namespace{ExecuteTest.cpp}::import_hash_join_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_coalesce_cols_join_test (const int id, bool with_delete_support)
 
void anonymous_namespace{ExecuteTest.cpp}::import_emp_table ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_dept_table ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_geospatial_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_geospatial_join_test (const bool replicate_inner_table=false)
 
void anonymous_namespace{ExecuteTest.cpp}::import_logical_size_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_empty_table_test ()
 
void anonymous_namespace{ExecuteTest.cpp}::import_test_table_with_lots_of_columns ()
 
 TEST (Select, ArrayUnnest)
 
 TEST (Select, ArrayIndex)
 
 TEST (Select, ArrayCountDistinct)
 
 TEST (Select, ArrayAnyAndAll)
 
 TEST (Select, ArrayUnsupported)
 
 TEST (Select, ExpressionRewrite)
 
 TEST (Select, OrRewrite)
 
 TEST (Select, GpuSort)
 
 TEST (Select, GroupByConstrainedByInQueryRewrite)
 
 TEST (Select, RedundantGroupBy)
 
 TEST (Select, BigDecimalRange)
 
 TEST (Select, DecimalCompression)
 
 TEST (Update, DecimalOverflow)
 
 TEST (Drop, AfterDrop)
 
 TEST (Alter, AfterAlterTableName)
 
 TEST (Alter, AfterAlterColumnName)
 
 TEST (Alter, AfterAlterGeoColumnName)
 
 TEST (Select, Empty)
 
 TEST (Select, Subqueries)
 
 TEST (Select, Joins_Arrays)
 
 TEST (Select, Joins_ShardedEmptyTable)
 
 TEST (Select, Joins_EmptyTable)
 
 TEST (Select, Joins_ImplicitJoins)
 
 TEST (Select, Joins_DifferentIntegerTypes)
 
 TEST (Select, Joins_FilterPushDown)
 
 TEST (Select, Joins_InnerJoin_TwoTables)
 
void anonymous_namespace{ExecuteTest.cpp}::validate_shard_agg (const ResultSet &rows, const std::vector< std::pair< int64_t, int64_t >> &expected)
 
 TEST (Select, AggregationOnAsymmetricShards)
 
 TEST (Select, Joins_InnerJoin_Sharded)
 
 TEST (Select, Joins_Negative_ShardKey)
 
 TEST (Select, Joins_InnerJoin_AtLeastThreeTables)
 
 TEST (Select, Joins_InnerJoin_Filters)
 
 TEST (Select, Joins_LeftOuterJoin)
 
 TEST (Select, Joins_LeftJoin_Filters)
 
 TEST (Select, Joins_MultiCompositeColumns)
 
 TEST (Select, Joins_BuildHashTable)
 
 TEST (Select, Joins_CoalesceColumns)
 
 TEST (Select, Joins_ComplexQueries)
 
 TEST (Select, Joins_TimeAndDate)
 
 TEST (Select, Joins_OneOuterExpression)
 
 TEST (Select, Joins_Subqueries)
 
 TEST_F (JoinTest, EmptyJoinTables)
 
 TEST (Select, Joins_MultipleOuterExpressions)
 
 TEST (Select, RuntimeFunctions)
 
 TEST (Select, TextGroupBy)
 
 TEST (Select, UnsupportedExtensions)
 
 TEST (Select, UnsupportedSortOfIntermediateResult)
 
 TEST (Select, Views)
 
 TEST (Select, Views_With_Subquery)
 
 TEST (Select, CreateTableAsSelect)
 
 TEST (Select, PgShim)
 
 TEST (Select, CaseInsensitive)
 
 TEST (Select, Deserialization)
 
 TEST (Select, DesugarTransform)
 
 TEST (Select, ArrowOutput)
 
 TEST (Select, WatchdogTest)
 
 TEST (Select, PuntToCPU)
 
 TEST (Select, TimestampMeridiesEncoding)
 
 TEST (Select, TimestampPrecisionMeridiesEncoding)
 
 TEST (Select, DateTimeZones)
 
 TEST (Select, TimestampPrecision)
 
 TEST (Select, TimestampPrecisionFormat)
 
void anonymous_namespace{ExecuteTest.cpp}::validate_timestamp_agg (const ResultSet &row, const int64_t expected_ts, const double expected_mean, const int64_t expected_count)
 
 TEST (Select, TimestampCastAggregates)
 
 TEST (Truncate, Count)
 
 TEST (Update, VarlenSmartSwitch)
 
 TEST (Update, Text)
 
 TEST (Update, TextINVariant)
 
 TEST (Update, TextEncodingDict16)
 
 TEST (Update, TextEncodingDict8)
 
 TEST (Update, MultiColumnInteger)
 
 TEST (Update, TimestampUpdate)
 
 TEST (Update, TimeUpdate)
 
 TEST (Update, DateUpdate)
 
 TEST (Update, FloatUpdate)
 
 TEST (Update, IntegerUpdate)
 
 TEST (Update, DoubleUpdate)
 
 TEST (Update, SmallIntUpdate)
 
 TEST (Update, BigIntUpdate)
 
 TEST (Update, DecimalUpdate)
 
 TEST (Update, JoinCacheInvalidationTest)
 
 TEST (Delete, WithoutVacuumAttribute)
 
 TEST (Update, ImplicitCastToDate4)
 
 TEST (Update, ImplicitCastToDate2)
 
 TEST (Update, ImplicitCastToEncodedString)
 
 TEST (Update, ImplicitCastToNoneEncodedString)
 
 TEST (Update, ImplicitCastToNumericTypes)
 
 TEST (Update, ImplicitCastToTime4)
 
 TEST (Update, ImplicitCastToTime8)
 
 TEST (Update, ImplicitCastToTimestamp8)
 
 TEST (Update, ImplicitCastToTimestamp4)
 
 TEST (Update, ShardedTableShardKeyTest)
 
 TEST (Update, UsingDateColumns)
 
 TEST (Delete, ShardedTableDeleteTest)
 
 TEST (Delete, JoinCacheInvalidationTest)
 
 TEST (Delete, IntraFragment)
 
 TEST (Join, InnerJoin_TwoTables)
 
 TEST (Join, InnerJoin_AtLeastThreeTables)
 
 TEST (Join, InnerJoin_Filters)
 
 TEST (Join, LeftOuterJoin)
 
 TEST (Join, LeftJoin_Filters)
 
 TEST (Join, MultiCompositeColumns)
 
 TEST (Join, BuildHashTable)
 
 TEST (Join, ComplexQueries)
 
 TEST (Join, OneOuterExpression)
 
 TEST (Join, MultipleOuterExpressions)
 
 TEST (Delete, ExtraFragment)
 
 TEST (Delete, Joins_ImplicitJoins)
 
 TEST (Create, Delete)
 
 TEST (Select, GeoSpatial_Basics)
 
 TEST (Select, GeoSpatial_Projection)
 
 TEST (Select, GeoSpatial_GeoJoin)
 
 TEST (Rounding, ROUND)
 
 TEST (Select, Sample)
 
void shard_key_test_runner (const std::string &shard_key_col, const ExecutorDeviceType dt)
 
 TEST (Select, ShardKeyDDL)
 
 TEST (Create, DaysEncodingDDL)
 
 TEST (Select, DatesDaysEncodingTest)
 
 TEST (Select, WindowFunctionRank)
 
 TEST (Select, WindowFunctionOneRowPartitions)
 
 TEST (Select, WindowFunctionEmptyPartitions)
 
 TEST (Select, WindowFunctionPercentRank)
 
 TEST (Select, WindowFunctionTile)
 
 TEST (Select, WindowFunctionCumeDist)
 
 TEST (Select, WindowFunctionLag)
 
 TEST (Select, WindowFunctionFirst)
 
 TEST (Select, WindowFunctionLead)
 
 TEST (Select, WindowFunctionLast)
 
 TEST (Select, WindowFunctionAggregate)
 
 TEST (Select, WindowFunctionAggregateNoOrder)
 
 TEST (Select, WindowFunctionSum)
 
 TEST (Select, EmptyString)
 
 TEST (Select, MultiStepColumnarization)
 
 TEST (Select, LogicalSizedColumns)
 
 TEST (Select, GroupEmptyBlank)
 
int anonymous_namespace{ExecuteTest.cpp}::create_sharded_join_table (const std::string &table_name, size_t fragment_size, size_t num_rows, const ShardInfo &shard_info, bool with_delete_support=true)
 
int anonymous_namespace{ExecuteTest.cpp}::create_and_populate_window_func_table ()
 
int anonymous_namespace{ExecuteTest.cpp}::create_and_populate_rounding_table ()
 
int anonymous_namespace{ExecuteTest.cpp}::create_and_populate_tables (bool with_delete_support=true)
 
int anonymous_namespace{ExecuteTest.cpp}::create_views ()
 
int anonymous_namespace{ExecuteTest.cpp}::create_as_select ()
 
int anonymous_namespace{ExecuteTest.cpp}::create_as_select_empty ()
 
void anonymous_namespace{ExecuteTest.cpp}::drop_tables ()
 
void anonymous_namespace{ExecuteTest.cpp}::drop_views ()
 
int main (int argc, char **argv)
 

Variables

bool g_aggregator {false}
 
int g_test_against_columnId_gap
 
bool g_enable_smem_group_by
 
bool g_allow_cpu_retry
 
bool g_enable_watchdog
 
bool g_skip_intermediate_count
 
unsigned g_trivial_loop_join_threshold
 
bool g_enable_overlaps_hashjoin
 
double g_gpu_mem_limit_percent
 
bool g_enable_window_functions
 
bool g_enable_bump_allocator
 
size_t g_leaf_count
 
bool anonymous_namespace{ExecuteTest.cpp}::g_hoist_literals {true}
 
size_t anonymous_namespace{ExecuteTest.cpp}::g_shard_count {0}
 
bool anonymous_namespace{ExecuteTest.cpp}::g_use_row_iterator {true}
 
size_t anonymous_namespace{ExecuteTest.cpp}::g_num_leafs {1}
 
bool anonymous_namespace{ExecuteTest.cpp}::g_keep_test_data {false}
 
const ssize_t anonymous_namespace{ExecuteTest.cpp}::g_num_rows {10}
 
SQLiteComparator anonymous_namespace{ExecuteTest.cpp}::g_sqlite_comparator
 
const size_t anonymous_namespace{ExecuteTest.cpp}::g_array_test_row_count {20}
 

Macro Definition Documentation

◆ BASE_PATH

#define BASE_PATH   "./tmp"

Definition at line 40 of file ExecuteTest.cpp.

Referenced by main().

◆ SKIP_ALL_ON_AGGREGATOR

#define SKIP_ALL_ON_AGGREGATOR ( )
Value:
if (g_aggregator) { \
LOG(ERROR) << "Tests not valid in distributed mode"; \
return; \
}
bool g_aggregator
Definition: ExecuteTest.cpp:46

Definition at line 472 of file ExecuteTest.cpp.

Referenced by TEST(), and TEST_F().

◆ SKIP_NO_GPU

#define SKIP_NO_GPU ( )
Value:
if (skip_tests(dt)) { \
CHECK(dt == ExecutorDeviceType::GPU); \
LOG(WARNING) << "GPU not available, skipping GPU tests"; \
continue; \
}
bool skip_tests(const ExecutorDeviceType device_type)

Definition at line 465 of file ExecuteTest.cpp.

Referenced by TEST(), and TEST_F().

◆ SKIP_ON_AGGREGATOR

#define SKIP_ON_AGGREGATOR (   EXP)
Value:
if (!g_aggregator) { \
EXP; \
}
bool g_aggregator
Definition: ExecuteTest.cpp:46

Definition at line 478 of file ExecuteTest.cpp.

Referenced by main(), and TEST().

Typedef Documentation

◆ QR

Definition at line 63 of file ExecuteTest.cpp.

Function Documentation

◆ main()

int main ( int  argc,
char **  argv 
)

Definition at line 16548 of file ExecuteTest.cpp.

References BASE_PATH, anonymous_namespace{ExecuteTest.cpp}::choose_shard_count(), ResultSetReductionJIT::clearCache(), anonymous_namespace{ExecuteTest.cpp}::create_and_populate_tables(), anonymous_namespace{ExecuteTest.cpp}::create_as_select(), anonymous_namespace{ExecuteTest.cpp}::create_as_select_empty(), create_views(), drop_tables(), drop_views(), logger::ERROR, g_aggregator, g_bigint_count, g_enable_bump_allocator, g_enable_columnar_output, g_enable_smem_group_by, g_enable_window_functions, g_from_table_reordering, anonymous_namespace{ExecuteTest.cpp}::g_hoist_literals, anonymous_namespace{ExecuteTest.cpp}::g_keep_test_data, anonymous_namespace{ExecuteTest.cpp}::g_shard_count, g_test_against_columnId_gap, QueryRunner::QueryRunner::get(), logger::LogOptions::get_options(), QueryRunner::QueryRunner::init(), logger::init(), LOG, logger::LogOptions::max_files_, Executor::nukeCacheOfExecutors(), QueryRunner::QueryRunner::reset(), run, QueryRunner::QueryRunner::setIRFilename(), and SKIP_ON_AGGREGATOR.

16548  {
16549  testing::InitGoogleTest(&argc, argv);
16550  namespace po = boost::program_options;
16551 
16552  po::options_description desc("Options");
16553 
16554  // these two are here to allow passing correctly google testing parameters
16555  desc.add_options()("gtest_list_tests", "list all test");
16556  desc.add_options()("gtest_filter", "filters tests, use --help for details");
16557 
16558  desc.add_options()("disable-literal-hoisting", "Disable literal hoisting");
16559  desc.add_options()("with-sharding", "Create sharded tables");
16560  desc.add_options()("from-table-reordering",
16561  po::value<bool>(&g_from_table_reordering)
16562  ->default_value(g_from_table_reordering)
16563  ->implicit_value(true),
16564  "Enable automatic table reordering in FROM clause");
16565  desc.add_options()("bigint-count",
16566  po::value<bool>(&g_bigint_count)
16567  ->default_value(g_bigint_count)
16568  ->implicit_value(false),
16569  "Use 64-bit count");
16570  desc.add_options()("disable-shared-mem-group-by",
16571  po::value<bool>(&g_enable_smem_group_by)
16572  ->default_value(g_enable_smem_group_by)
16573  ->implicit_value(false),
16574  "Enable/disable using GPU shared memory for GROUP BY.");
16575  desc.add_options()("enable-columnar-output",
16576  po::value<bool>(&g_enable_columnar_output)
16577  ->default_value(g_enable_columnar_output)
16578  ->implicit_value(true),
16579  "Enable/disable using columnar output format.");
16580  desc.add_options()("enable-bump-allocator",
16581  po::value<bool>(&g_enable_bump_allocator)
16582  ->default_value(g_enable_bump_allocator)
16583  ->implicit_value(true),
16584  "Enable the bump allocator for projection queries on GPU.");
16585  desc.add_options()("keep-data", "Don't drop tables at the end of the tests");
16586  desc.add_options()(
16587  "use-existing-data",
16588  "Don't create and drop tables and only run select tests (it implies --keep-data).");
16589  desc.add_options()("dump-ir",
16590  po::value<std::string>(),
16591  "Dump IR for all executed queries to file. Currently only supports "
16592  "single node tests.");
16593 
16594  desc.add_options()(
16595  "test-help",
16596  "Print all ExecuteTest specific options (for gtest options use `--help`).");
16597 
16598  logger::LogOptions log_options(argv[0]);
16599  log_options.max_files_ = 0; // stderr only by default
16600  desc.add(log_options.get_options());
16601 
16602  po::variables_map vm;
16603  po::store(po::command_line_parser(argc, argv).options(desc).run(), vm);
16604  po::notify(vm);
16605 
16606  if (vm.count("test-help")) {
16607  std::cout << "Usage: ExecuteTest" << std::endl << std::endl;
16608  std::cout << desc << std::endl;
16609  return 0;
16610  }
16611 
16612  logger::init(log_options);
16613 
16614  if (vm.count("disable-literal-hoisting")) {
16615  g_hoist_literals = false;
16616  }
16617 
16619 
16621  if (vm.count("with-sharding")) {
16623  }
16624  if (vm.count("dump-ir")) {
16625  const auto filename = vm["dump-ir"].as<std::string>();
16626  QR::get()->setIRFilename(filename);
16627  }
16628 
16629  if (vm.count("keep-data")) {
16630  g_keep_test_data = true;
16631  }
16632 
16633  // insert artificial gap of columnId so as to test against the gap w/o
16634  // need of ALTER ADD/DROP COLUMN before doing query test.
16635  // Note: Temporarily disabling for distributed tests.
16637 
16638  const bool use_existing_data = vm.count("use-existing-data");
16639  int err{0};
16640  if (use_existing_data) {
16641  testing::GTEST_FLAG(filter) = "Select*";
16642  } else {
16644  if (!err) {
16645  err = create_views();
16646  }
16647  if (!err) {
16649  }
16650  if (!err) {
16652  }
16653  }
16654  if (err) {
16655  return err;
16656  }
16657 
16658  try {
16659  err = RUN_ALL_TESTS();
16660  } catch (const std::exception& e) {
16661  LOG(ERROR) << e.what();
16662  }
16663 
16665  if (!use_existing_data && !g_keep_test_data) {
16666  drop_tables();
16667  drop_views();
16668  }
16670  QR::reset();
16671  return err;
16672 }
#define BASE_PATH
Definition: ExecuteTest.cpp:40
#define LOG(tag)
Definition: Logger.h:182
bool g_enable_window_functions
Definition: Execute.cpp:91
bool g_aggregator
Definition: ExecuteTest.cpp:46
static QueryRunner * init(const char *db_path, const std::string &udf_filename="", const size_t max_gpu_mem=0, const int reserved_gpu_mem=256<< 20)
Definition: QueryRunner.h:70
void drop_views(std::string prefix, int max)
bool g_enable_columnar_output
Definition: Execute.cpp:84
bool g_from_table_reordering
Definition: Execute.cpp:75
void init(LogOptions const &log_opts)
Definition: Logger.cpp:260
bool g_bigint_count
void drop_tables(std::string prefix, int max)
virtual void setIRFilename(const std::string &filename)
Definition: QueryRunner.h:149
static QueryRunner * get()
Definition: QueryRunner.h:115
#define SKIP_ON_AGGREGATOR(EXP)
bool g_enable_bump_allocator
Definition: Execute.cpp:96
static bool run
int g_test_against_columnId_gap
Definition: Catalog.cpp:72
bool g_enable_smem_group_by
static void nukeCacheOfExecutors()
Definition: Execute.h:349
int create_and_populate_tables(bool with_delete_support=true)
void create_views(std::string prefix, int max)
+ Here is the call graph for this function:

◆ shard_key_test_runner()

void shard_key_test_runner ( const std::string &  shard_key_col,
const ExecutorDeviceType  dt 
)

Definition at line 14716 of file ExecuteTest.cpp.

References run_ddl_statement(), and run_multiple_agg().

Referenced by TEST().

14717  {
14718  run_ddl_statement("drop table if exists shard_key_ddl_test;");
14720  "CREATE TABLE shard_key_ddl_test (x INTEGER, y TEXT ENCODING DICT(32), pt "
14721  "POINT, z DOUBLE, a BIGINT NOT NULL, poly POLYGON, b SMALLINT, ts timestamp, t "
14722  "time, dt date, SHARD KEY(" +
14723  shard_key_col + ")) WITH (shard_count = 4)");
14724 
14726  "INSERT INTO shard_key_ddl_test VALUES (1, 'foo', 'POINT(1 1)', 1.0, 1, "
14727  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1, '12-aug-83 06:14:23' , '05:15:43', "
14728  "'11-07-1973')",
14729  dt);
14731  "INSERT INTO shard_key_ddl_test VALUES (2, 'bar', 'POINT(2 2)', 2.0, 2, "
14732  "'POLYGON((0 0, 1 1, 20 20, 3 3))', 2, '1-dec-93 07:23:23' , '06:15:43', "
14733  "'10-07-1975')",
14734  dt);
14736  "INSERT INTO shard_key_ddl_test VALUES (3, 'hello', 'POINT(3 3)', 3.0, 3, "
14737  "'POLYGON((0 0, 1 1, 2 2, 30 30))', 3, '1-feb-65 05:15:27' , '13:15:43', "
14738  "'9-07-1977')",
14739  dt);
14740 }
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ TEST() [1/183]

TEST ( Create  ,
StorageOptions   
)

Definition at line 515 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::choose_shard_count(), CPU, GPU, SKIP_NO_GPU, to_string(), and anonymous_namespace{ExecuteTest.cpp}::validate_storage_options().

Referenced by TEST().

515  {
517  SKIP_NO_GPU();
518  const auto shard_count = choose_shard_count();
519  static const std::map<std::pair<std::string, bool>,
520  std::pair<std::string, std::vector<std::string>>>
521  params{
522  {{"fragment_size"s, true}, {"", {"-1", "0"}}},
523  {{"fragment_size"s, false},
524  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
525  {{"max_rows"s, true}, {"", {"-1", "0"}}},
526  {{"max_rows"s, false},
527  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
528  {{"page_size"s, true}, {"", {"-1", "0"}}},
529  {{"page_size"s, false},
530  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
531  {{"max_chunk_size"s, true}, {"", {"-1", "0"}}},
532  {{"max_chunk_size"s, false},
533  {"", {"2097152", "4194304", "10485760", "2147483648"}}},
534  {{"partitions"s, true}, {"", {"'No'", "'null'", "'-1'"}}},
535  {{"partitions"s, false}, {"", {"'SHARDED'", "'REPLICATED'"}}},
536  {{""s, true}, {", SHARD KEY(id)", {"2"}}},
537  {{"shard_count"s, true}, {"", {std::to_string(shard_count)}}},
538  {{"shard_count"s, false}, {", SHARD KEY(id)", {std::to_string(shard_count)}}},
539  {{"vacuum"s, true}, {"", {"'-1'", "'0'", "'null'"}}},
540  {{"vacuum"s, false}, {"", {"'IMMEDIATE'", "'delayed'"}}},
541  {{"sort_column"s, true}, {"", {"'arsenal'", "'barca'", "'city'"}}},
542  {{"sort_column"s, false}, {"", {"'id'", "'val'"}}}};
543 
544  for (auto& elem : params) {
545  validate_storage_options(elem.first, elem.second);
546  }
547  }
548 }
std::string to_string(char const *&&v)
void validate_storage_options(const std::pair< std::string, bool > type_meta, const std::pair< std::string, std::vector< std::string >> values)
#define SKIP_NO_GPU()
+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ TEST() [2/183]

TEST ( Insert  ,
ShardedTableWithGeo   
)

Definition at line 550 of file ExecuteTest.cpp.

References CPU, GPU, run_ddl_statement(), run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

550  {
552  SKIP_NO_GPU();
553  run_ddl_statement("DROP TABLE IF EXISTS table_with_geo_and_shard_key;");
554  EXPECT_NO_THROW(
555  run_ddl_statement("CREATE TABLE table_with_geo_and_shard_key (x Int, poly "
556  "POLYGON, b SMALLINT, SHARD KEY(b)) WITH (shard_count = 4);"));
557 
558  EXPECT_NO_THROW(
559  run_multiple_agg("INSERT INTO table_with_geo_and_shard_key VALUES (1, "
560  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 0);",
561  dt));
562  EXPECT_NO_THROW(run_multiple_agg(
563  "INSERT INTO table_with_geo_and_shard_key (x, poly, b) VALUES (1, "
564  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1);",
565  dt));
566  EXPECT_NO_THROW(run_multiple_agg(
567  "INSERT INTO table_with_geo_and_shard_key (b, poly, x) VALUES (2, "
568  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1);",
569  dt));
570  EXPECT_NO_THROW(run_multiple_agg(
571  "INSERT INTO table_with_geo_and_shard_key (x, b, poly) VALUES (1, 3, "
572  "'POLYGON((0 0, 1 1, 2 2, 3 3))');",
573  dt));
574  EXPECT_NO_THROW(
575  run_multiple_agg("INSERT INTO table_with_geo_and_shard_key (poly, x, b) VALUES ("
576  "'POLYGON((0 0, 1 1, 2 2, 3 3))', 1, 4);",
577  dt));
578 
579  ASSERT_EQ(5,
580  v<int64_t>(run_simple_agg(
581  "SELECT count(*) FROM table_with_geo_and_shard_key;", dt)));
582  }
583 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [3/183]

TEST ( Insert  ,
NullArrayNullEmpty   
)

Definition at line 585 of file ExecuteTest.cpp.

References TestHelpers::compare_array(), CPU, GPU, run_ddl_statement(), run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

585  {
586  const char* create_table_array_with_nulls =
587  R"(create table table_array_with_nulls (i smallint, sia smallint[], fa2 float[2]);)";
589  SKIP_NO_GPU();
590  run_ddl_statement("DROP TABLE IF EXISTS table_array_empty;");
591  EXPECT_NO_THROW(run_ddl_statement("create table table_array_empty (val int[]);"));
592  EXPECT_NO_THROW(run_multiple_agg("INSERT INTO table_array_empty VALUES({});", dt));
593  EXPECT_NO_THROW(run_simple_agg("SELECT * from table_array_empty;", dt));
594  ASSERT_EQ(0,
595  v<int64_t>(run_simple_agg(
596  "SELECT CARDINALITY(val) from table_array_empty limit 1;", dt)));
597 
598  run_ddl_statement("DROP TABLE IF EXISTS table_array_fixlen_text;");
599  EXPECT_NO_THROW(
600  run_ddl_statement("create table table_array_fixlen_text (strings text[2]);"));
601  EXPECT_THROW(
602  run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES(NULL);", dt),
603  std::runtime_error);
604  EXPECT_THROW(run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES({});", dt),
605  std::runtime_error);
606  EXPECT_NO_THROW(
607  run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES({NULL,NULL});", dt));
608  EXPECT_NO_THROW(
609  run_multiple_agg("INSERT INTO table_array_fixlen_text VALUES({'a','b'});", dt));
610  ASSERT_EQ(
611  1,
612  v<int64_t>(run_simple_agg(
613  "SELECT count(*) FROM table_array_fixlen_text WHERE strings[1] IS NOT NULL;",
614  dt)));
615  ASSERT_EQ(
616  1,
617  v<int64_t>(run_simple_agg(
618  "SELECT count(*) FROM table_array_fixlen_text WHERE strings[2] IS NULL;",
619  dt)));
620 
621  run_ddl_statement("DROP TABLE IF EXISTS table_array_with_nulls;");
622  EXPECT_NO_THROW(run_ddl_statement(create_table_array_with_nulls));
623  EXPECT_NO_THROW(
624  run_multiple_agg("INSERT INTO table_array_with_nulls "
625  "VALUES(1, {1,1}, ARRAY[1.0,1.0]);",
626  dt));
627  EXPECT_NO_THROW(
628  run_multiple_agg("INSERT INTO table_array_with_nulls "
629  "VALUES(2, {NULL,2}, {NULL,2.0});",
630  dt));
631  EXPECT_NO_THROW(
632  run_multiple_agg("INSERT INTO table_array_with_nulls "
633  "VALUES(3, {3,NULL}, {3.0, NULL});",
634  dt));
635  EXPECT_NO_THROW(
636  run_multiple_agg("INSERT INTO table_array_with_nulls "
637  "VALUES(4, {NULL,NULL}, {NULL,NULL});",
638  dt));
639  EXPECT_NO_THROW(
640  run_multiple_agg("INSERT INTO table_array_with_nulls "
641  "VALUES(5, NULL, NULL);",
642  dt));
643  EXPECT_NO_THROW(
644  run_multiple_agg("INSERT INTO table_array_with_nulls "
645  "VALUES(6, {}, NULL);",
646  dt));
647  EXPECT_NO_THROW(
648  run_multiple_agg("INSERT INTO table_array_with_nulls "
649  "VALUES(7, {NULL,NULL}, {NULL,NULL});",
650  dt));
651 
652  ASSERT_EQ(1,
653  v<int64_t>(
654  run_simple_agg("SELECT MIN(sia[1]) FROM table_array_with_nulls;", dt)));
655  ASSERT_EQ(3,
656  v<int64_t>(
657  run_simple_agg("SELECT MAX(sia[1]) FROM table_array_with_nulls;", dt)));
658  ASSERT_EQ(
659  5,
660  v<int64_t>(run_simple_agg(
661  "SELECT count(*) FROM table_array_with_nulls WHERE sia[2] IS NULL;", dt)));
662  ASSERT_EQ(
663  3.0,
664  v<float>(run_simple_agg("SELECT MAX(fa2[1]) FROM table_array_with_nulls;", dt)));
665  ASSERT_EQ(
666  2.0,
667  v<float>(run_simple_agg("SELECT MAX(fa2[2]) FROM table_array_with_nulls;", dt)));
668  ASSERT_EQ(2,
669  v<int64_t>(run_simple_agg(
670  "SELECT count(*) FROM table_array_with_nulls WHERE fa2[1] IS NOT NULL;",
671  dt)));
672  ASSERT_EQ(1,
673  v<int64_t>(run_simple_agg(
674  "SELECT count(*) FROM table_array_with_nulls WHERE sia IS NULL;", dt)));
675  ASSERT_EQ(
676  5,
677  v<int64_t>(run_simple_agg(
678  "SELECT count(*) FROM table_array_with_nulls WHERE fa2 IS NOT NULL;", dt)));
679  ASSERT_EQ(1,
680  v<int64_t>(run_simple_agg(
681  "SELECT count(*) FROM table_array_with_nulls WHERE CARDINALITY(sia)=0;",
682  dt)));
683  ASSERT_EQ(5,
684  v<int64_t>(run_simple_agg(
685  "SELECT count(*) FROM table_array_with_nulls WHERE CARDINALITY(sia)=2;",
686  dt)));
687  ASSERT_EQ(
688  1,
689  v<int64_t>(run_simple_agg(
690  "SELECT count(*) FROM table_array_with_nulls WHERE CARDINALITY(sia) IS NULL;",
691  dt)));
692 
693  // Simple lazy projection
695  run_simple_agg("SELECT sia FROM table_array_with_nulls WHERE i = 5;", dt),
696  std::vector<int64_t>({}));
697 
698  // Simple non-lazy projection
700  run_simple_agg("SELECT sia FROM table_array_with_nulls WHERE sia IS NULL;", dt),
701  std::vector<int64_t>({}));
702  }
703 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void compare_array(const TargetValue &r, const std::vector< T > &arr, const double tol=-1.)
Definition: TestHelpers.h:33
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [4/183]

TEST ( Insert  ,
DictBoundary   
)

Definition at line 705 of file ExecuteTest.cpp.

References CPU, GPU, run_ddl_statement(), run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), SKIP_NO_GPU, and to_string().

705  {
707  SKIP_NO_GPU();
708  run_ddl_statement("DROP TABLE IF EXISTS table_with_small_dict;");
709  EXPECT_NO_THROW(run_ddl_statement(
710  "CREATE TABLE table_with_small_dict (i INT, t TEXT ENCODING DICT(8));"));
711 
712  for (int cVal = 0; cVal < 280; cVal++) {
713  string insString = "INSERT INTO table_with_small_dict VALUES (" +
714  std::to_string(cVal) + ", '" + std::to_string(cVal) + "');";
715  EXPECT_NO_THROW(run_multiple_agg(insString, dt));
716  }
717 
718  ASSERT_EQ(
719  280,
720  v<int64_t>(run_simple_agg("SELECT count(*) FROM table_with_small_dict;", dt)));
721  ASSERT_EQ(255,
722  v<int64_t>(run_simple_agg(
723  "SELECT count(distinct t) FROM table_with_small_dict;", dt)));
724  ASSERT_EQ(25,
725  v<int64_t>(run_simple_agg(
726  "SELECT count(*) FROM table_with_small_dict WHERE t IS NULL;", dt)));
727  }
728 }
std::string to_string(char const *&&v)
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [5/183]

TEST ( KeyForString  ,
KeyForString   
)

Definition at line 730 of file ExecuteTest.cpp.

References CPU, GPU, run_ddl_statement(), run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

730  {
732  SKIP_NO_GPU();
733  run_ddl_statement("drop table if exists kfs;");
734  EXPECT_NO_THROW(run_ddl_statement(
735  "create table kfs(ts text encoding dict(8), ss text encoding "
736  "dict(16), ws text encoding dict, ns text not null encoding dict, sa text[]);"));
737  EXPECT_NO_THROW(
738  run_multiple_agg("insert into kfs values('0', '0', '0', '0', {'0','0'});",
740  EXPECT_NO_THROW(
741  run_multiple_agg("insert into kfs values('1', '1', '1', '1', {'1','1'});",
743  EXPECT_NO_THROW(
744  run_multiple_agg("insert into kfs values(null, null, null, '2', {'2','2'});",
746  ASSERT_EQ(3, v<int64_t>(run_simple_agg("select count(*) from kfs;", dt)));
747  ASSERT_EQ(2,
748  v<int64_t>(run_simple_agg(
749  "select count(*) from kfs where key_for_string(ts) is not null;", dt)));
750  ASSERT_EQ(2,
751  v<int64_t>(run_simple_agg(
752  "select count(*) from kfs where key_for_string(ss) is not null;", dt)));
753  ASSERT_EQ(2,
754  v<int64_t>(run_simple_agg(
755  "select count(*) from kfs where key_for_string(ws) is not null;", dt)));
756  ASSERT_EQ(3,
757  v<int64_t>(run_simple_agg(
758  "select count(*) from kfs where key_for_string(ns) is not null;", dt)));
759  ASSERT_EQ(
760  3,
761  v<int64_t>(run_simple_agg(
762  "select count(*) from kfs where key_for_string(sa[1]) is not null;", dt)));
763  ASSERT_EQ(
764  2,
765  v<int64_t>(run_simple_agg(
766  "select count(*) from kfs where key_for_string(ts) = key_for_string(ss);",
767  dt)));
768  ASSERT_EQ(
769  2,
770  v<int64_t>(run_simple_agg(
771  "select count(*) from kfs where key_for_string(ss) = key_for_string(ws);",
772  dt)));
773  ASSERT_EQ(
774  2,
775  v<int64_t>(run_simple_agg(
776  "select count(*) from kfs where key_for_string(ws) = key_for_string(ts);",
777  dt)));
778  ASSERT_EQ(
779  2,
780  v<int64_t>(run_simple_agg(
781  "select count(*) from kfs where key_for_string(ws) = key_for_string(ns);",
782  dt)));
783  ASSERT_EQ(
784  2,
785  v<int64_t>(run_simple_agg(
786  "select count(*) from kfs where key_for_string(ws) = key_for_string(sa[1]);",
787  dt)));
788  ASSERT_EQ(0,
789  v<int64_t>(run_simple_agg("select min(key_for_string(ts)) from kfs;", dt)));
790  ASSERT_EQ(0,
791  v<int64_t>(run_simple_agg("select min(key_for_string(ss)) from kfs;", dt)));
792  ASSERT_EQ(0,
793  v<int64_t>(run_simple_agg("select min(key_for_string(ws)) from kfs;", dt)));
794  ASSERT_EQ(0,
795  v<int64_t>(run_simple_agg("select min(key_for_string(ns)) from kfs;", dt)));
796  ASSERT_EQ(
797  0, v<int64_t>(run_simple_agg("select min(key_for_string(sa[1])) from kfs;", dt)));
798  ASSERT_EQ(
799  0, v<int64_t>(run_simple_agg("select min(key_for_string(sa[2])) from kfs;", dt)));
800  ASSERT_EQ(1,
801  v<int64_t>(run_simple_agg("select max(key_for_string(ts)) from kfs;", dt)));
802  ASSERT_EQ(1,
803  v<int64_t>(run_simple_agg("select max(key_for_string(ss)) from kfs;", dt)));
804  ASSERT_EQ(1,
805  v<int64_t>(run_simple_agg("select max(key_for_string(ws)) from kfs;", dt)));
806  ASSERT_EQ(2,
807  v<int64_t>(run_simple_agg("select max(key_for_string(ns)) from kfs;", dt)));
808  ASSERT_EQ(
809  2, v<int64_t>(run_simple_agg("select max(key_for_string(sa[1])) from kfs;", dt)));
810  ASSERT_EQ(
811  2, v<int64_t>(run_simple_agg("select max(key_for_string(sa[2])) from kfs;", dt)));
812  ASSERT_EQ(
813  2, v<int64_t>(run_simple_agg("select count(key_for_string(ts)) from kfs;", dt)));
814  ASSERT_EQ(
815  2, v<int64_t>(run_simple_agg("select count(key_for_string(ss)) from kfs;", dt)));
816  ASSERT_EQ(
817  2, v<int64_t>(run_simple_agg("select count(key_for_string(ws)) from kfs;", dt)));
818  ASSERT_EQ(
819  3, v<int64_t>(run_simple_agg("select count(key_for_string(ns)) from kfs;", dt)));
820  ASSERT_EQ(
821  3,
822  v<int64_t>(run_simple_agg("select count(key_for_string(sa[1])) from kfs;", dt)));
823  ASSERT_EQ(
824  3,
825  v<int64_t>(run_simple_agg("select count(key_for_string(sa[2])) from kfs;", dt)));
826  }
827 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [6/183]

TEST ( Select  ,
IsTrue   
)

Definition at line 829 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, anonymous_namespace{ExecuteTest.cpp}::g_sqlite_comparator, GPU, anonymous_namespace{ExecuteTest.cpp}::SQLiteComparator::query(), run_ddl_statement(), run_multiple_agg(), and SKIP_NO_GPU.

829  {
831  SKIP_NO_GPU();
832 
833  run_ddl_statement("DROP TABLE IF EXISTS table_bool_test;");
834  g_sqlite_comparator.query("DROP TABLE IF EXISTS table_bool_test;");
835 
836  run_ddl_statement("CREATE TABLE table_bool_test (id INT, val BOOLEAN);");
837  g_sqlite_comparator.query("CREATE TABLE table_bool_test (id INT, val BOOLEAN);");
838 
839  run_multiple_agg("INSERT INTO table_bool_test VALUES(1, NULL);", dt);
840  g_sqlite_comparator.query("INSERT INTO table_bool_test VALUES(1, NULL);");
841  run_multiple_agg("INSERT INTO table_bool_test VALUES(2, 'true');", dt);
842  g_sqlite_comparator.query("INSERT INTO table_bool_test VALUES(2, 'true');");
843  run_multiple_agg("INSERT INTO table_bool_test VALUES(3, 'false');", dt);
844  g_sqlite_comparator.query("INSERT INTO table_bool_test VALUES(3, 'false');");
845 
846  // the case statement is due to return type mismatch with sqllite
847  c("select id, (case when (val is true) then 't' else 'f' end) from "
848  "table_bool_test "
849  "order by id;",
850  "select id, (case when (val is 'true') then 't' else 'f' end) from "
851  "table_bool_test "
852  "order by id;",
853  dt);
854  c("select id, (case when (val is not true) then 't' else 'f' end) from "
855  "table_bool_test order by id;",
856  "select id, (case when (val is not 'true') then 't' else 'f' end) from "
857  "table_bool_test order by id;",
858  dt);
859  c("select id, (case when (val is false) then 't' else 'f' end) from "
860  "table_bool_test "
861  "order by id;",
862  "select id, (case when (val is 'false') then 't' else 'f' end) from "
863  "table_bool_test "
864  "order by id;",
865  dt);
866  c("select id, (case when (val is not false) then 't' else 'f' end) from "
867  "table_bool_test order by id;",
868  "select id, (case when (val is not 'false') then 't' else 'f' end) from "
869  "table_bool_test order by id;",
870  dt);
871  }
872 }
void query(const std::string &query_string)
#define SKIP_NO_GPU()
void run_ddl_statement(std::string ddl)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [7/183]

TEST ( Select  ,
NullGroupBy   
)

Definition at line 874 of file ExecuteTest.cpp.

References CPU, GPU, run_ddl_statement(), run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

874  {
876  SKIP_NO_GPU();
877  run_ddl_statement("DROP TABLE IF EXISTS table_null_group_by;");
878  run_ddl_statement("CREATE TABLE table_null_group_by (val TEXT);");
879  run_multiple_agg("INSERT INTO table_null_group_by VALUES( NULL );", dt);
880  run_simple_agg("SELECT val FROM table_null_group_by GROUP BY val;", dt);
881 
882  run_ddl_statement("DROP TABLE IF EXISTS table_null_group_by;");
883  run_ddl_statement("CREATE TABLE table_null_group_by (val DOUBLE);");
884  run_multiple_agg("INSERT INTO table_null_group_by VALUES( NULL );", dt);
885  run_simple_agg("SELECT val FROM table_null_group_by GROUP BY val;", dt);
886  }
887 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void run_ddl_statement(std::string ddl)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [8/183]

TEST ( Select  ,
FilterAndSimpleAggregation   
)

Definition at line 889 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, g_enable_smem_group_by, anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), SKIP_NO_GPU, and SKIP_ON_AGGREGATOR.

889  {
891  SKIP_NO_GPU();
892  c("SELECT COUNT(*) FROM test;", dt);
893  c("SELECT COUNT(f) FROM test;", dt);
894  c("SELECT MIN(x) FROM test;", dt);
895  c("SELECT MAX(x) FROM test;", dt);
896  c("SELECT MIN(z) FROM test;", dt);
897  c("SELECT MAX(z) FROM test;", dt);
898  c("SELECT MIN(t) FROM test;", dt);
899  c("SELECT MAX(t) FROM test;", dt);
900  c("SELECT MIN(ff) FROM test;", dt);
901  c("SELECT MIN(fn) FROM test;", dt);
902  c("SELECT SUM(ff) FROM test;", dt);
903  c("SELECT SUM(fn) FROM test;", dt);
904  c("SELECT SUM(x + y) FROM test;", dt);
905  c("SELECT SUM(x + y + z) FROM test;", dt);
906  c("SELECT SUM(x + y + z + t) FROM test;", dt);
907  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8;", dt);
908  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102;", dt);
909  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 OR (z > 100 AND z < 103);", dt);
910  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
911  "1000 AND t < 1002;",
912  dt);
913  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 OR (z > 100 AND z < 103);", dt);
914  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 OR (z > 100 AND z < 102) OR (t > "
915  "1000 AND t < 1003);",
916  dt);
917  c("SELECT COUNT(*) FROM test WHERE x <> 7;", dt);
918  c("SELECT COUNT(*) FROM test WHERE z <> 102;", dt);
919  c("SELECT COUNT(*) FROM test WHERE t <> 1002;", dt);
920  c("SELECT COUNT(*) FROM test WHERE x + y = 49;", dt);
921  c("SELECT COUNT(*) FROM test WHERE x + y + z = 150;", dt);
922  c("SELECT COUNT(*) FROM test WHERE x + y + z + t = 1151;", dt);
923  c("SELECT SUM(x + y) FROM test WHERE x + y = 49;", dt);
924  c("SELECT SUM(x + y + z) FROM test WHERE x + y = 49;", dt);
925  c("SELECT SUM(x + y + z + t) FROM test WHERE x + y = 49;", dt);
926  c("SELECT COUNT(*) FROM test WHERE x - y = -35;", dt);
927  c("SELECT COUNT(*) FROM test WHERE x - y + z = 66;", dt);
928  c("SELECT COUNT(*) FROM test WHERE x - y + z + t = 1067;", dt);
929  c("SELECT COUNT(*) FROM test WHERE y - x = 35;", dt);
930  c("SELECT 'Hello', 'World', 7 FROM test WHERE x <> 7;", dt);
931  c("SELECT 'Total', COUNT(*) FROM test WHERE x <> 7;", dt);
932  c("SELECT SUM(2 * x) FROM test WHERE x = 7;", dt);
933  c("SELECT SUM(2 * x + z) FROM test WHERE x = 7;", dt);
934  c("SELECT SUM(x + y) FROM test WHERE x - y = -35;", dt);
935  c("SELECT SUM(x + y) FROM test WHERE y - x = 35;", dt);
936  c("SELECT SUM(x + y - z) FROM test WHERE y - x = 35;", dt);
937  c("SELECT SUM(x * y + 15) FROM test WHERE x + y + 1 = 50;", dt);
938  c("SELECT SUM(x * y + 15) FROM test WHERE x + y + z + 1 = 151;", dt);
939  c("SELECT SUM(x * y + 15) FROM test WHERE x + y + z + t + 1 = 1152;", dt);
940  c("SELECT SUM(z) FROM test WHERE z IS NOT NULL;", dt);
941  c("SELECT MIN(x * y + 15) FROM test WHERE x + y + 1 = 50;", dt);
942  c("SELECT MIN(x * y + 15) FROM test WHERE x + y + z + 1 = 151;", dt);
943  c("SELECT MIN(x * y + 15) FROM test WHERE x + y + z + t + 1 = 1152;", dt);
944  c("SELECT MAX(x * y + 15) FROM test WHERE x + y + 1 = 50;", dt);
945  c("SELECT MAX(x * y + 15) FROM test WHERE x + y + z + 1 = 151;", dt);
946  c("SELECT MAX(x * y + 15) FROM test WHERE x + y + z + t + 1 = 1152;", dt);
947  c("SELECT MIN(x) FROM test WHERE x = 7;", dt);
948  c("SELECT MIN(z) FROM test WHERE z = 101;", dt);
949  c("SELECT MIN(t) FROM test WHERE t = 1001;", dt);
950  c("SELECT AVG(x + y) FROM test;", dt);
951  c("SELECT AVG(x + y + z) FROM test;", dt);
952  c("SELECT AVG(x + y + z + t) FROM test;", dt);
953  c("SELECT AVG(y) FROM test WHERE x > 6 AND x < 8;", dt);
954  c("SELECT AVG(y) FROM test WHERE z > 100 AND z < 102;", dt);
955  c("SELECT AVG(y) FROM test WHERE t > 1000 AND t < 1002;", dt);
956  c("SELECT MIN(dd) FROM test;", dt);
957  c("SELECT MAX(dd) FROM test;", dt);
958  c("SELECT SUM(dd) FROM test;", dt);
959  c("SELECT AVG(dd) FROM test;", dt);
960  c("SELECT AVG(dd) FROM test WHERE x > 6 AND x < 8;", dt);
961  c("SELECT COUNT(*) FROM test WHERE dd > 100;", dt);
962  c("SELECT COUNT(*) FROM test WHERE dd > 200;", dt);
963  c("SELECT COUNT(*) FROM test WHERE dd > 300;", dt);
964  c("SELECT COUNT(*) FROM test WHERE dd > 111.0;", dt);
965  c("SELECT COUNT(*) FROM test WHERE dd > 111.1;", dt);
966  c("SELECT COUNT(*) FROM test WHERE dd > 222.2;", dt);
967  c("SELECT MAX(x + dd) FROM test;", dt);
968  c("SELECT MAX(x + 2 * dd), MIN(x + 2 * dd) FROM test;", dt);
969  c("SELECT COUNT(*) FROM test WHERE dd > CAST(111.0 AS decimal(10, 2));", dt);
970  c("SELECT COUNT(*) FROM test WHERE dd > CAST(222.0 AS decimal(10, 2));", dt);
971  c("SELECT COUNT(*) FROM test WHERE dd > CAST(333.0 AS decimal(10, 2));", dt);
972  c("SELECT MIN(dd * dd) FROM test;", dt);
973  c("SELECT MAX(dd * dd) FROM test;", dt);
974  c("SELECT COUNT(*) FROM test WHERE u IS NOT NULL;", dt);
975  c("SELECT AVG(u * f) FROM test;", dt);
976  c("SELECT AVG(u * d) FROM test;", dt);
977  c("SELECT SUM(-y) FROM test;", dt);
978  c("SELECT SUM(-z) FROM test;", dt);
979  c("SELECT SUM(-t) FROM test;", dt);
980  c("SELECT SUM(-dd) FROM test;", dt);
981  c("SELECT SUM(-f) FROM test;", dt);
982  c("SELECT SUM(-d) FROM test;", dt);
983  c("SELECT SUM(dd * 0.99) FROM test;", dt);
984  c("SELECT COUNT(*) FROM test WHERE 1<>2;", dt);
985  c("SELECT COUNT(*) FROM test WHERE 1=1;", dt);
986  c("SELECT COUNT(*) FROM test WHERE 22 > 33;", dt);
987  c("SELECT COUNT(*) FROM test WHERE ff < 23.0/4.0 AND 22 < 33;", dt);
988  c("SELECT COUNT(*) FROM test WHERE x + 3*8/2 < 35 + y - 20/5;", dt);
989  c("SELECT x + 2 * 10/4 + 3 AS expr FROM test WHERE x + 3*8/2 < 35 + y - 20/5 ORDER "
990  "BY expr ASC;",
991  dt);
992  c("SELECT COUNT(*) FROM test WHERE ff + 3.0*8 < 20.0/5;", dt);
993  c("SELECT COUNT(*) FROM test WHERE x < y AND 0=1;", dt);
994  c("SELECT COUNT(*) FROM test WHERE x < y AND 1=1;", dt);
995  c("SELECT COUNT(*) FROM test WHERE x < y OR 1<1;", dt);
996  c("SELECT COUNT(*) FROM test WHERE x < y OR 1=1;", dt);
997  c("SELECT COUNT(*) FROM test WHERE x < 35 AND x < y AND 1=1 AND 0=1;", dt);
998  c("SELECT COUNT(*) FROM test WHERE 1>2 AND x < 35 AND x < y AND y < 10;", dt);
1000  c("SELECT COUNT(*) FROM test WHERE x < y GROUP BY x HAVING 0=1;", dt));
1001  c("SELECT COUNT(*) FROM test WHERE x < y GROUP BY x HAVING 1=1;", dt);
1002  c("SELECT COUNT(*) FROM test WHERE ofq >= 0 OR ofq IS NULL;", dt);
1003  c("SELECT COUNT(*) AS val FROM test WHERE (test.dd = 0.5 OR test.dd = 3);", dt);
1004  c("SELECT MAX(dd_notnull * 1) FROM test;", dt);
1005  c("SELECT x, COUNT(*) AS n FROM test GROUP BY x, ufd ORDER BY x, n;", dt);
1006  c("SELECT MIN(x), MAX(x) FROM test WHERE real_str LIKE '%nope%';", dt);
1007  c("SELECT COUNT(*) FROM test WHERE (x > 7 AND y / (x - 7) < 44);", dt);
1008  c("SELECT x, AVG(ff) AS val FROM test GROUP BY x ORDER BY val;", dt);
1009  c("SELECT x, MAX(fn) as val FROM test WHERE fn IS NOT NULL GROUP BY x ORDER BY val;",
1010  dt);
1011  c("SELECT MAX(dn) FROM test WHERE dn IS NOT NULL;", dt);
1012  c("SELECT x, MAX(dn) as val FROM test WHERE dn IS NOT NULL GROUP BY x ORDER BY val;",
1013  dt);
1014  c("SELECT COUNT(*) as val FROM test GROUP BY x, y, ufd ORDER BY val;", dt);
1015  ASSERT_NEAR(
1016  static_cast<double>(-1000.3),
1017  v<double>(run_simple_agg(
1018  "SELECT AVG(fn) AS val FROM test GROUP BY rowid ORDER BY val LIMIT 1;", dt)),
1019  static_cast<double>(0.2));
1020  c("SELECT COUNT(*) FROM test WHERE d = 2.2", dt);
1021  c("SELECT COUNT(*) FROM test WHERE fx + 1 IS NULL;", dt);
1022  c("SELECT COUNT(ss) FROM test;", dt);
1023  c("SELECT COUNT(*) FROM test WHERE null IS NULL;", dt);
1024  c("SELECT COUNT(*) FROM test WHERE null_str IS NULL;", dt);
1025  c("SELECT COUNT(*) FROM test WHERE null IS NOT NULL;", 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 o1 = '1999-09-08';", dt);
1029  c("SELECT COUNT(*) FROM test WHERE o1 <> '1999-09-08';", dt);
1030  c("SELECT COUNT(*) FROM test WHERE o >= CAST('1999-09-09' AS DATE);", 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 o2 = '1999-09-08';", dt);
1034  c("SELECT COUNT(*) FROM test WHERE o2 <> '1999-09-08';", dt);
1035  c("SELECT COUNT(*) FROM test WHERE o1 = o2;", dt);
1036  c("SELECT COUNT(*) FROM test WHERE o1 <> o2;", dt);
1037  ASSERT_EQ(19,
1038  v<int64_t>(run_simple_agg("SELECT rowid FROM test WHERE rowid = 19;", dt)));
1039  ASSERT_EQ(
1040  2 * g_num_rows,
1041  v<int64_t>(run_simple_agg("SELECT MAX(rowid) - MIN(rowid) + 1 FROM test;", dt)));
1042  ASSERT_EQ(
1043  15,
1044  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) = 0;", dt)));
1045  ASSERT_EQ(
1046  0,
1047  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) = 7;", dt)));
1048  ASSERT_EQ(5,
1049  v<int64_t>(
1050  run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) <> 0;", dt)));
1051  ASSERT_EQ(20,
1052  v<int64_t>(
1053  run_simple_agg("SELECT COUNT(*) FROM test WHERE MOD(x, 7) <> 7;", dt)));
1054  c("SELECT MIN(x) FROM test WHERE x <> 7 AND x <> 8;", dt);
1055  c("SELECT MIN(x) FROM test WHERE z <> 101 AND z <> 102;", dt);
1056  c("SELECT MIN(x) FROM test WHERE t <> 1001 AND t <> 1002;", dt);
1057  ASSERT_NEAR(static_cast<double>(0.5),
1058  v<double>(run_simple_agg("SELECT STDDEV_POP(x) FROM test;", dt)),
1059  static_cast<double>(0.2));
1060  ASSERT_NEAR(static_cast<double>(0.5),
1061  v<double>(run_simple_agg("SELECT STDDEV_SAMP(x) FROM test;", dt)),
1062  static_cast<double>(0.2));
1063  ASSERT_NEAR(static_cast<double>(0.2),
1064  v<double>(run_simple_agg("SELECT VAR_POP(x) FROM test;", dt)),
1065  static_cast<double>(0.1));
1066  ASSERT_NEAR(static_cast<double>(0.2),
1067  v<double>(run_simple_agg("SELECT VAR_SAMP(x) FROM test;", dt)),
1068  static_cast<double>(0.1));
1069  ASSERT_NEAR(static_cast<double>(92.0),
1070  v<double>(run_simple_agg("SELECT STDDEV_POP(dd) FROM test;", dt)),
1071  static_cast<double>(2.0));
1072  ASSERT_NEAR(static_cast<double>(94.5),
1073  v<double>(run_simple_agg("SELECT STDDEV_SAMP(dd) FROM test;", dt)),
1074  static_cast<double>(1.0));
1075  ASSERT_NEAR(
1076  static_cast<double>(94.5),
1077  v<double>(run_simple_agg("SELECT POWER(((SUM(dd * dd) - SUM(dd) * SUM(dd) / "
1078  "COUNT(dd)) / (COUNT(dd) - 1)), 0.5) FROM test;",
1079  dt)),
1080  static_cast<double>(1.0));
1081  ASSERT_NEAR(static_cast<double>(8485.0),
1082  v<double>(run_simple_agg("SELECT VAR_POP(dd) FROM test;", dt)),
1083  static_cast<double>(10.0));
1084  ASSERT_NEAR(static_cast<double>(8932.0),
1085  v<double>(run_simple_agg("SELECT VAR_SAMP(dd) FROM test;", dt)),
1086  static_cast<double>(10.0));
1087  ASSERT_EQ(20,
1088  v<int64_t>(run_simple_agg(
1089  "SELECT COUNT(*) FROM test HAVING STDDEV_POP(x) < 1.0;", dt)));
1090  ASSERT_EQ(20,
1091  v<int64_t>(run_simple_agg(
1092  "SELECT COUNT(*) FROM test HAVING STDDEV_POP(x) * 5 < 3.0;", dt)));
1093  ASSERT_NEAR(
1094  static_cast<double>(0.65),
1095  v<double>(run_simple_agg("SELECT stddev(x) + VARIANCE(x) FROM test;", dt)),
1096  static_cast<double>(0.10));
1097  ASSERT_NEAR(static_cast<float>(0.5),
1098  v<float>(run_simple_agg("SELECT STDDEV_POP_FLOAT(x) FROM test;", dt)),
1099  static_cast<float>(0.2));
1100  ASSERT_NEAR(static_cast<float>(0.5),
1101  v<float>(run_simple_agg("SELECT STDDEV_SAMP_FLOAT(x) FROM test;", dt)),
1102  static_cast<float>(0.2));
1103  ASSERT_NEAR(static_cast<float>(0.2),
1104  v<float>(run_simple_agg("SELECT VAR_POP_FLOAT(x) FROM test;", dt)),
1105  static_cast<float>(0.1));
1106  ASSERT_NEAR(static_cast<float>(0.2),
1107  v<float>(run_simple_agg("SELECT VAR_SAMP_FLOAT(x) FROM test;", dt)),
1108  static_cast<float>(0.1));
1109  ASSERT_NEAR(static_cast<float>(92.0),
1110  v<float>(run_simple_agg("SELECT STDDEV_POP_FLOAT(dd) FROM test;", dt)),
1111  static_cast<float>(2.0));
1112  ASSERT_NEAR(static_cast<float>(94.5),
1113  v<float>(run_simple_agg("SELECT STDDEV_SAMP_FLOAT(dd) FROM test;", dt)),
1114  static_cast<float>(1.0));
1115  ASSERT_NEAR(
1116  static_cast<double>(94.5),
1117  v<double>(run_simple_agg("SELECT POWER(((SUM(dd * dd) - SUM(dd) * SUM(dd) / "
1118  "COUNT(dd)) / (COUNT(dd) - 1)), 0.5) FROM test;",
1119  dt)),
1120  static_cast<double>(1.0));
1121  ASSERT_NEAR(static_cast<float>(8485.0),
1122  v<float>(run_simple_agg("SELECT VAR_POP_FLOAT(dd) FROM test;", dt)),
1123  static_cast<float>(10.0));
1124  ASSERT_NEAR(static_cast<float>(8932.0),
1125  v<float>(run_simple_agg("SELECT VAR_SAMP_FLOAT(dd) FROM test;", dt)),
1126  static_cast<float>(10.0));
1127  ASSERT_EQ(20,
1128  v<int64_t>(run_simple_agg(
1129  "SELECT COUNT(*) FROM test HAVING STDDEV_POP_FLOAT(x) < 1.0;", dt)));
1130  ASSERT_EQ(
1131  20,
1132  v<int64_t>(run_simple_agg(
1133  "SELECT COUNT(*) FROM test HAVING STDDEV_POP_FLOAT(x) * 5 < 3.0;", dt)));
1134  ASSERT_NEAR(static_cast<float>(0.65),
1135  v<float>(run_simple_agg(
1136  "SELECT stddev_FLOAT(x) + VARIANCE_float(x) FROM test;", dt)),
1137  static_cast<float>(0.10));
1138  ASSERT_NEAR(static_cast<double>(0.125),
1139  v<double>(run_simple_agg("SELECT COVAR_POP(x, y) FROM test;", dt)),
1140  static_cast<double>(0.001));
1141  ASSERT_NEAR(static_cast<float>(0.125),
1142  v<float>(run_simple_agg("SELECT COVAR_POP_FLOAT(x, y) FROM test;", dt)),
1143  static_cast<float>(0.001));
1144  ASSERT_NEAR(
1145  static_cast<double>(0.125), // covar_pop expansion
1146  v<double>(run_simple_agg("SELECT avg(x * y) - avg(x) * avg(y) FROM test;", dt)),
1147  static_cast<double>(0.001));
1148  ASSERT_NEAR(static_cast<double>(0.131),
1149  v<double>(run_simple_agg("SELECT COVAR_SAMP(x, y) FROM test;", dt)),
1150  static_cast<double>(0.001));
1151  ASSERT_NEAR(static_cast<double>(0.131),
1152  v<double>(run_simple_agg("SELECT COVAR_SAMP_FLOAT(x, y) FROM test;", dt)),
1153  static_cast<double>(0.001));
1154  ASSERT_NEAR(
1155  static_cast<double>(0.131), // covar_samp expansion
1156  v<double>(run_simple_agg(
1157  "SELECT ((sum(x * y) - sum(x) * avg(y)) / (count(x) - 1)) FROM test;", dt)),
1158  static_cast<double>(0.001));
1159  ASSERT_NEAR(static_cast<double>(0.58),
1160  v<double>(run_simple_agg("SELECT CORRELATION(x, y) FROM test;", dt)),
1161  static_cast<double>(0.01));
1162  ASSERT_NEAR(static_cast<float>(0.58),
1163  v<float>(run_simple_agg("SELECT CORRELATION_FLOAT(x, y) FROM test;", dt)),
1164  static_cast<float>(0.01));
1165  ASSERT_NEAR(static_cast<double>(0.58),
1166  v<double>(run_simple_agg("SELECT CORR(x, y) FROM test;", dt)),
1167  static_cast<double>(0.01));
1168  ASSERT_NEAR(static_cast<float>(0.58),
1169  v<float>(run_simple_agg("SELECT CORR_FLOAT(x, y) FROM test;", dt)),
1170  static_cast<float>(0.01));
1171  ASSERT_NEAR(static_cast<double>(0.33),
1172  v<double>(run_simple_agg("SELECT POWER(CORR(x, y), 2) FROM test;", dt)),
1173  static_cast<double>(0.01));
1174  ASSERT_NEAR(static_cast<double>(0.58), // corr expansion
1175  v<double>(run_simple_agg("SELECT (avg(x * y) - avg(x) * avg(y)) /"
1176  "(stddev_pop(x) * stddev_pop(y)) FROM test;",
1177  dt)),
1178  static_cast<double>(0.01));
1179 
1180  // == Tests related to GPU shared-memory support
1182  c("SELECT COUNT(*) FROM test GROUP BY x ORDER BY x DESC;", dt);
1183  c("SELECT y, COUNT(*) FROM test GROUP BY y ORDER BY y DESC;", dt);
1184  c("SELECT str, COUNT(*) FROM test GROUP BY str ORDER BY str DESC;", dt);
1185  c("SELECT COUNT(*), z FROM test where x = 7 GROUP BY z ORDER BY z DESC;", dt);
1186  c("SELECT z as z0, z as z1, COUNT(*) FROM test GROUP BY z0, z1 ORDER BY z0 DESC;",
1187  dt);
1188  ;
1189  }
1190  }
1191 }
#define SKIP_ON_AGGREGATOR(EXP)
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
bool g_enable_smem_group_by
+ Here is the call graph for this function:

◆ TEST() [9/183]

TEST ( Select  ,
AggregateOnEmptyTable   
)

Definition at line 1193 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1193  {
1195  SKIP_NO_GPU();
1196  c("SELECT AVG(x), AVG(y), AVG(z), AVG(t), AVG(f), AVG(d) from empty_test_table;", dt);
1197  c("SELECT MIN(x), MIN(y), MIN(z), MIN(t), MIN(f), MIN(d) from empty_test_table;", dt);
1198  c("SELECT MAX(x), MAX(y), MAX(z), MAX(t), MAX(f), MAX(d) from empty_test_table;", dt);
1199  c("SELECT SUM(x), SUM(y), SUM(z), SUM(t), SUM(f), SUM(d) from empty_test_table;", dt);
1200  c("SELECT COUNT(x), COUNT(y), COUNT(z), COUNT(t), COUNT(f), COUNT(d) from "
1201  "empty_test_table;",
1202  dt);
1203  // skipped fragment
1204  c("SELECT AVG(x), AVG(y), AVG(z), AVG(t), AVG(f), AVG(d) from empty_test_table "
1205  "where id > 5;",
1206  dt);
1207  c("SELECT MIN(x), MIN(y), MIN(z), MIN(t), MIN(f), MIN(d) from empty_test_table where "
1208  "id > 5;",
1209  dt);
1210  c("SELECT MAX(x), MAX(y), MAX(z), MAX(t), MAX(f), MAX(d) from empty_test_table where "
1211  "id > 5;",
1212  dt);
1213  c("SELECT SUM(x), SUM(y), SUM(z), SUM(t), SUM(f), SUM(d) from empty_test_table where "
1214  "id > 5;",
1215  dt);
1216  c("SELECT COUNT(x), COUNT(y), COUNT(z), COUNT(t), COUNT(f), COUNT(d) from "
1217  "empty_test_table where id > 5;",
1218  dt);
1219  }
1220 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [10/183]

TEST ( Select  ,
LimitAndOffset   
)

Definition at line 1222 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CHECK, CPU, anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, run_multiple_agg(), and SKIP_NO_GPU.

1222  {
1223  CHECK(g_num_rows >= 4);
1225  SKIP_NO_GPU();
1226  {
1227  const auto rows = run_multiple_agg("SELECT * FROM test LIMIT 5;", dt);
1228  ASSERT_EQ(size_t(5), rows->rowCount());
1229  }
1230  {
1231  const auto rows = run_multiple_agg("SELECT * FROM test LIMIT 5 OFFSET 3;", dt);
1232  ASSERT_EQ(size_t(5), rows->rowCount());
1233  }
1234  {
1235  const auto rows =
1236  run_multiple_agg("SELECT * FROM test WHERE x <> 8 LIMIT 3 OFFSET 1;", dt);
1237  ASSERT_EQ(size_t(3), rows->rowCount());
1238  }
1239  c("SELECT str FROM (SELECT str, SUM(y) as total_y FROM test GROUP BY str ORDER BY "
1240  "total_y DESC, "
1241  "str LIMIT 1);",
1242  dt);
1243  EXPECT_THROW(run_multiple_agg("SELECT * FROM test LIMIT 0;", dt), std::runtime_error);
1244  }
1245 }
#define CHECK(condition)
Definition: Logger.h:187
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [11/183]

TEST ( Select  ,
FloatAndDoubleTests   
)

Definition at line 1247 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

1247  {
1249  SKIP_NO_GPU();
1250  c("SELECT MIN(f) FROM test;", dt);
1251  c("SELECT MAX(f) FROM test;", dt);
1252  c("SELECT AVG(f) FROM test;", dt);
1253  c("SELECT MIN(d) FROM test;", dt);
1254  c("SELECT MAX(d) FROM test;", dt);
1255  c("SELECT AVG(d) FROM test;", dt);
1256  c("SELECT SUM(f) FROM test;", dt);
1257  c("SELECT SUM(d) FROM test;", dt);
1258  c("SELECT SUM(f + d) FROM test;", dt);
1259  c("SELECT AVG(x * f) FROM test;", dt);
1260  c("SELECT AVG(z - 200) FROM test;", dt);
1261  c("SELECT SUM(CAST(x AS FLOAT)) FROM test;", dt);
1262  c("SELECT SUM(CAST(x AS FLOAT)) FROM test GROUP BY z;", dt);
1263  c("SELECT AVG(CAST(x AS FLOAT)) FROM test;", dt);
1264  c("SELECT AVG(CAST(x AS FLOAT)) FROM test GROUP BY y;", dt);
1265  c("SELECT COUNT(*) FROM test WHERE f > 1.0 AND f < 1.2;", dt);
1266  c("SELECT COUNT(*) FROM test WHERE f > 1.101 AND f < 1.299;", dt);
1267  c("SELECT COUNT(*) FROM test WHERE f > 1.201 AND f < 1.4;", dt);
1268  c("SELECT COUNT(*) FROM test WHERE f > 1.0 AND f < 1.2 AND d > 2.0 AND d < 2.4;", dt);
1269  c("SELECT COUNT(*) FROM test WHERE f > 1.0 AND f < 1.2 OR (d > 2.0 AND d < 3.0);",
1270  dt);
1271  c("SELECT SUM(x + y) FROM test WHERE f > 1.0 AND f < 1.2;", dt);
1272  c("SELECT SUM(x + y) FROM test WHERE d + f > 3.0 AND d + f < 4.0;", dt);
1273  c("SELECT SUM(f + d) FROM test WHERE x - y = -35;", dt);
1274  c("SELECT SUM(f + d) FROM test WHERE x + y + 1 = 50;", dt);
1275  c("SELECT SUM(f * d + 15) FROM test WHERE x + y + 1 = 50;", dt);
1276  c("SELECT MIN(x), AVG(x * y), MAX(y + 7), AVG(x * f + 15), COUNT(*) FROM test WHERE "
1277  "x + y > 47 AND x + y < 51;",
1278  dt);
1279  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(y) > 42.0 "
1280  "ORDER BY n;",
1281  dt);
1282  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(f) > 1.09 "
1283  "ORDER BY n;",
1284  dt);
1285  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(f) > 1.09 "
1286  "AND AVG(y) > 42.0 ORDER BY n;",
1287  dt);
1288  c("SELECT AVG(d), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(d) > 2.2 "
1289  "AND AVG(y) > 42.0 ORDER BY n;",
1290  dt);
1291  c("SELECT AVG(f), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING AVG(d) > 2.2 "
1292  "AND AVG(y) > 42.0 ORDER BY n;",
1293  dt);
1294  c("SELECT AVG(f) + AVG(d), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING "
1295  "AVG(f) + AVG(d) > 3.0 ORDER BY n;",
1296  dt);
1297  c("SELECT AVG(f) + AVG(d), MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING "
1298  "AVG(f) + AVG(d) > 3.5 ORDER BY n;",
1299  dt);
1300  c("SELECT f + d AS s, x * y FROM test ORDER by s DESC;", dt);
1301  c("SELECT COUNT(*) AS n FROM test GROUP BY f ORDER BY n;", dt);
1302  c("SELECT f, COUNT(*) FROM test GROUP BY f HAVING f > 1.25;", dt);
1303  c("SELECT COUNT(*) AS n FROM test GROUP BY d ORDER BY n;", dt);
1304  c("SELECT MIN(x + y) AS n FROM test WHERE x + y > 47 AND x + y < 53 GROUP BY f + 1, "
1305  "f + d ORDER BY n;",
1306  dt);
1307  c("SELECT f + d AS s FROM test GROUP BY s ORDER BY s DESC;", dt);
1308  c("SELECT f + 1 AS s, AVG(u * f) FROM test GROUP BY s ORDER BY s DESC;", dt);
1309  c("SELECT (CAST(dd AS float) * 0.5) AS key FROM test GROUP BY key ORDER BY key DESC;",
1310  dt);
1311  c("SELECT (CAST(dd AS double) * 0.5) AS key FROM test GROUP BY key ORDER BY key "
1312  "DESC;",
1313  dt);
1314 
1315  c("SELECT fn FROM test ORDER BY fn ASC NULLS FIRST;",
1316  "SELECT fn FROM test ORDER BY fn ASC;",
1317  dt);
1318  c("SELECT fn FROM test WHERE fn < 0 OR fn IS NULL ORDER BY fn ASC NULLS FIRST;",
1319  "SELECT fn FROM test WHERE fn < 0 OR fn IS NULL ORDER BY fn ASC;",
1320  dt);
1321  ASSERT_NEAR(static_cast<double>(1.3),
1322  v<double>(run_simple_agg("SELECT AVG(f) AS n FROM test WHERE x = 7 GROUP "
1323  "BY z HAVING AVG(y) + STDDEV(y) "
1324  "> 42.0 ORDER BY n + VARIANCE(y);",
1325  dt)),
1326  static_cast<double>(0.1));
1327  ASSERT_NEAR(
1328  static_cast<double>(92.0),
1329  v<double>(run_simple_agg("SELECT STDDEV_POP(dd) AS n FROM test ORDER BY n;", dt)),
1330  static_cast<double>(1.0));
1331  }
1332 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [12/183]

TEST ( Select  ,
FilterShortCircuit   
)

Definition at line 1334 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1334  {
1336  SKIP_NO_GPU();
1337  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1338  "1000 AND UNLIKELY(t < 1002);",
1339  dt);
1340  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1341  "1000 AND t > 1000 AND t > 1001 "
1342  "AND t > 1002 AND t > 1003 AND t > 1004 AND UNLIKELY(t < 1002);",
1343  dt);
1344  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1345  "1000 AND t > 1000 AND t > 1001 "
1346  "AND t > 1002 AND t > 1003 AND t > 1004 AND t > 1005 AND UNLIKELY(t < 1002);",
1347  dt);
1348  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND z > 100 AND z < 102 AND t > "
1349  "1000 AND t > 1000 AND t > 1001 "
1350  "AND t > 1002 AND t > 1003 AND UNLIKELY(t < 111) AND (str LIKE 'f__%%');",
1351  dt);
1352  c("SELECT COUNT(*) FROM test WHERE x > 6 AND x < 8 AND UNLIKELY(z < 200) AND z > 100 "
1353  "AND z < 102 AND t > 1000 AND "
1354  "t > 1000 AND t > 1001 AND UNLIKELY(t < 1111 AND t > 1100) AND (str LIKE 'f__%%') "
1355  "AND t > 1002 AND t > 1003;",
1356  dt);
1357  c("SELECT COUNT(*) FROM test WHERE UNLIKELY(x IN (7, 8, 9, 10)) AND y > 42;", dt);
1358  }
1359 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [13/183]

TEST ( Select  ,
FilterAndMultipleAggregation   
)

Definition at line 1361 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1361  {
1363  SKIP_NO_GPU();
1364  c("SELECT AVG(x), AVG(y) FROM test;", dt);
1365  c("SELECT MIN(x), AVG(x * y), MAX(y + 7), COUNT(*) FROM test WHERE x + y > 47 AND x "
1366  "+ y < 51;",
1367  dt);
1368  c("SELECT str, AVG(x), COUNT(*) as xx, COUNT(*) as countval FROM test GROUP BY str "
1369  "ORDER BY str;",
1370  dt);
1371  }
1372 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [14/183]

TEST ( Select  ,
GroupBy   
)

Definition at line 1374 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, g_big_group_threshold, g_enable_columnar_output, GPU, and SKIP_NO_GPU.

1374  {
1376  SKIP_NO_GPU();
1377 
1378  c("SELECT COUNT(*) FROM test_ranges GROUP BY i, b;", dt);
1379  c("SELECT i, b FROM test_ranges GROUP BY i, b;", dt);
1380 
1381  {
1382  const auto big_group_threshold = g_big_group_threshold;
1383  ScopeGuard reset_big_group_threshold = [&big_group_threshold] {
1384  g_big_group_threshold = big_group_threshold;
1385  };
1387  c("SELECT d, COUNT(*) FROM test GROUP BY d ORDER BY d DESC LIMIT 10;", dt);
1388  }
1389 
1391  // TODO: Fixup the tests below when running with columnar output enabled
1392  continue;
1393  }
1394 
1395  c("SELECT x, y, COUNT(*) FROM test GROUP BY x, y;", dt);
1396  c("SELECT x, y, APPROX_COUNT_DISTINCT(str) FROM test GROUP BY x, y;",
1397  "SELECT x, y, COUNT(distinct str) FROM test GROUP BY x, y;",
1398  dt);
1399  c("SELECT f, ff, APPROX_COUNT_DISTINCT(str) from test group by f, ff ORDER BY f, ff;",
1400  "SELECT f, ff, COUNT(distinct str) FROM test GROUP BY f, ff ORDER BY f, ff;",
1401  dt);
1402  }
1403 }
bool g_enable_columnar_output
Definition: Execute.cpp:84
size_t g_big_group_threshold
Definition: Execute.cpp:90
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [15/183]

TEST ( Select  ,
FilterAndGroupBy   
)

Definition at line 1405 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, run_multiple_agg(), and SKIP_NO_GPU.

1405  {
1407  SKIP_NO_GPU();
1408  c("SELECT MIN(x + y) FROM test WHERE x + y > 47 AND x + y < 53 GROUP BY x, y;", dt);
1409  c("SELECT MIN(x + y) FROM test WHERE x + y > 47 AND x + y < 53 GROUP BY x + 1, x + "
1410  "y;",
1411  dt);
1412  c("SELECT x, y, COUNT(*) FROM test GROUP BY x, y;", dt);
1413  c("SELECT x, dd, COUNT(*) FROM test GROUP BY x, dd ORDER BY x, dd;", dt);
1414  c("SELECT dd AS key1, COUNT(*) AS value1 FROM test GROUP BY key1 HAVING key1 IS NOT "
1415  "NULL ORDER BY key1, value1 "
1416  "DESC "
1417  "LIMIT 12;",
1418  dt);
1419  c("SELECT 'literal_string' AS key0 FROM test GROUP BY key0;", dt);
1420  c("SELECT str, MIN(y) FROM test WHERE y IS NOT NULL GROUP BY str ORDER BY str DESC;",
1421  dt);
1422  c("SELECT x, MAX(z) FROM test WHERE z IS NOT NULL GROUP BY x HAVING x > 7;", dt);
1423  c("SELECT CAST((dd - 0.5) * 2.0 AS int) AS key0, COUNT(*) AS val FROM test WHERE (dd "
1424  ">= 100.0 AND dd < 400.0) "
1425  "GROUP "
1426  "BY key0 HAVING key0 >= 0 AND key0 < 400 ORDER BY val DESC LIMIT 50 OFFSET 0;",
1427  dt);
1428  c("SELECT y, AVG(CASE WHEN x BETWEEN 6 AND 7 THEN x END) FROM test GROUP BY y ORDER "
1429  "BY y;",
1430  dt);
1431  c("SELECT x, AVG(u), COUNT(*) AS n FROM test GROUP BY x ORDER BY n DESC;", dt);
1432  c("SELECT f, ss FROM test GROUP BY f, ss ORDER BY f DESC;", dt);
1433  c("SELECT fx, COUNT(*) FROM test GROUP BY fx HAVING COUNT(*) > 5;", dt);
1434  c("SELECT fx, COUNT(*) n FROM test GROUP BY fx ORDER BY n DESC, fx IS NULL DESC;",
1435  dt);
1436  c("SELECT CASE WHEN x > 8 THEN 100000000 ELSE 42 END AS c, COUNT(*) FROM test GROUP "
1437  "BY c;",
1438  dt);
1439  c("SELECT COUNT(*) FROM test WHERE CAST((CAST(x AS FLOAT) - 0) * 0.2 AS INT) = 1;",
1440  dt);
1441  c("SELECT CAST(CAST(d AS FLOAT) AS INTEGER) AS key, COUNT(*) FROM test GROUP BY key;",
1442  dt);
1443  c("SELECT x * 2 AS x2, COUNT(DISTINCT y) AS n FROM test GROUP BY x2 ORDER BY n DESC;",
1444  dt);
1445  c("SELECT x, COUNT(real_str) FROM test GROUP BY x ORDER BY x DESC;", dt);
1446  c("SELECT str, SUM(y - y) FROM test GROUP BY str ORDER BY str ASC;", dt);
1447  c("SELECT str, SUM(y - y) FROM test WHERE y - y IS NOT NULL GROUP BY str ORDER BY "
1448  "str ASC;",
1449  dt);
1450  c("select shared_dict,m from test where (m >= CAST('2014-12-13 22:23:15' AS "
1451  "TIMESTAMP(0)) and m <= "
1452  "CAST('2014-12-14 22:23:15' AS TIMESTAMP(0))) and CAST(m AS TIMESTAMP(0)) BETWEEN "
1453  "'2014-12-14 22:23:15' AND "
1454  "'2014-12-13 22:23:15' group by shared_dict,m;",
1455  dt);
1456  c("SELECT x, SUM(z) FROM test WHERE z IS NOT NULL GROUP BY x ORDER BY x;", dt);
1457  EXPECT_THROW(run_multiple_agg(
1458  "SELECT x, MIN(real_str) FROM test GROUP BY x ORDER BY x DESC;", dt),
1459  std::runtime_error);
1460  EXPECT_THROW(run_multiple_agg(
1461  "SELECT x, MAX(real_str) FROM test GROUP BY x ORDER BY x DESC;", dt),
1462  std::runtime_error);
1463  EXPECT_THROW(run_multiple_agg("SELECT MIN(str) FROM test GROUP BY x;", dt),
1464  std::runtime_error);
1465  }
1466 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [16/183]

TEST ( Select  ,
GroupByBoundariesAndNull   
)

Definition at line 1468 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, anonymous_namespace{UpdateMetadataTest.cpp}::query, and SKIP_NO_GPU.

1468  {
1470  SKIP_NO_GPU();
1471  {
1472  std::string query(
1473  "SELECT CAST(CASE WHEN x = 7 THEN 2147483647 ELSE null END AS INTEGER) AS "
1474  "col0, COUNT(*) FROM test GROUP BY col0 ORDER BY col0 ASC");
1475  c(query + " NULLS FIRST;", query + ";", dt);
1476  }
1477  {
1478  std::string query(
1479  "SELECT smallint_nulls, COUNT(*) FROM test GROUP BY smallint_nulls ORDER BY "
1480  "smallint_nulls ASC");
1481  c(query + " NULLS FIRST;", query + ";", dt);
1482  }
1483  {
1484  std::string query(
1485  "SELECT CAST(CASE WHEN x = 7 THEN 127 ELSE null END AS TINYINT) AS col0, "
1486  "COUNT(*) FROM test GROUP BY col0 ORDER BY col0 ASC");
1487  c(query + " NULLS FIRST;", query + ";", dt);
1488  }
1489  }
1490 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [17/183]

TEST ( Select  ,
Arrays   
)

Definition at line 1492 of file ExecuteTest.cpp.

References TestHelpers::compare_array(), CPU, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), SKIP_NO_GPU, and SKIP_ON_AGGREGATOR.

1492  {
1494  SKIP_NO_GPU();
1495 
1496  // Simple lazy projection
1497  compare_array(run_simple_agg("SELECT arr_i16 FROM array_test WHERE x = 8;", dt),
1498  std::vector<int64_t>({2, 3, 4}));
1499  compare_array(run_simple_agg("SELECT arr_i32 FROM array_test WHERE x = 8;", dt),
1500  std::vector<int64_t>({20, 30, 40}));
1501  compare_array(run_simple_agg("SELECT arr_i64 FROM array_test WHERE x = 8;", dt),
1502  std::vector<int64_t>({200, 300, 400}));
1503  compare_array(run_simple_agg("SELECT arr_str FROM array_test WHERE x = 8;", dt),
1504  std::vector<std::string>({"bb", "cc", "dd"}));
1505  compare_array(run_simple_agg("SELECT arr_float FROM array_test WHERE x = 8;", dt),
1506  std::vector<float>({2.2, 3.3, 4.4}));
1507  compare_array(run_simple_agg("SELECT arr_double FROM array_test WHERE x = 8;", dt),
1508  std::vector<double>({22.2, 33.3, 44.4}));
1509  compare_array(run_simple_agg("SELECT arr_bool FROM array_test WHERE x = 8;", dt),
1510  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1511 
1512  compare_array(run_simple_agg("SELECT arr3_i8 FROM array_test WHERE x = 8;", dt),
1513  std::vector<int64_t>({2, 3, 4}));
1514  compare_array(run_simple_agg("SELECT arr3_i16 FROM array_test WHERE x = 8;", dt),
1515  std::vector<int64_t>({2, 3, 4}));
1516  compare_array(run_simple_agg("SELECT arr3_i32 FROM array_test WHERE x = 8;", dt),
1517  std::vector<int64_t>({20, 30, 40}));
1518  compare_array(run_simple_agg("SELECT arr3_i64 FROM array_test WHERE x = 8;", dt),
1519  std::vector<int64_t>({200, 300, 400}));
1520  compare_array(run_simple_agg("SELECT arr3_float FROM array_test WHERE x = 8;", dt),
1521  std::vector<float>({2.2, 3.3, 4.4}));
1522  compare_array(run_simple_agg("SELECT arr3_double FROM array_test WHERE x = 8;", dt),
1523  std::vector<double>({22.2, 33.3, 44.4}));
1524  compare_array(run_simple_agg("SELECT arr6_bool FROM array_test WHERE x = 8;", dt),
1525  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1526 
1528  compare_array(
1530  "SELECT ARRAY[1,2,3,5] from array_test WHERE x = 8 limit 8675309;", dt),
1531  std::vector<int64_t>({1, 2, 3, 5})););
1533  run_simple_agg("SELECT ARRAY[2*arr3_i32[1],2*arr3_i32[2],2*arr3_i32[3]] FROM "
1534  "array_test a WHERE x = 8 limit 31337;",
1535  dt),
1536  std::vector<int64_t>({40, 60, 80})));
1537 
1538  // Simple non-lazy projection
1539  compare_array(
1540  run_simple_agg("SELECT arr_i16 FROM array_test WHERE arr_i16[1] = 2;", dt),
1541  std::vector<int64_t>({2, 3, 4}));
1542  compare_array(
1543  run_simple_agg("SELECT arr_i32 FROM array_test WHERE arr_i32[1] = 20;", dt),
1544  std::vector<int64_t>({20, 30, 40}));
1545  compare_array(
1546  run_simple_agg("SELECT arr_i64 FROM array_test WHERE arr_i64[1] = 200;", dt),
1547  std::vector<int64_t>({200, 300, 400}));
1548  compare_array(
1549  run_simple_agg("SELECT arr_str FROM array_test WHERE arr_str[1] = 'bb';", dt),
1550  std::vector<std::string>({"bb", "cc", "dd"}));
1551  // TODO(adb): Calcite is casting the column value to DOUBLE to do the comparison,
1552  // which results in the comparison failing. Is this desired behavior or a bug? Adding
1553  // the CAST below for now to test projection.
1554  compare_array(
1556  "SELECT arr_float FROM array_test WHERE arr_float[1] = CAST(2.2 as FLOAT);",
1557  dt),
1558  std::vector<float>({2.2, 3.3, 4.4}));
1559  compare_array(
1560  run_simple_agg("SELECT arr_double FROM array_test WHERE arr_double[1] = 22.2;",
1561  dt),
1562  std::vector<double>({22.2, 33.3, 44.4}));
1564  "SELECT arr_bool FROM array_test WHERE x < 9 AND arr_bool[1];", dt),
1565  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1566 
1567  compare_array(
1568  run_simple_agg("SELECT arr3_i8 FROM array_test WHERE arr3_i8[1] = 2;", dt),
1569  std::vector<int64_t>({2, 3, 4}));
1570  compare_array(
1571  run_simple_agg("SELECT arr3_i16 FROM array_test WHERE arr3_i16[1] = 2;", dt),
1572  std::vector<int64_t>({2, 3, 4}));
1573  compare_array(
1574  run_simple_agg("SELECT arr3_i32 FROM array_test WHERE arr3_i32[1] = 20;", dt),
1575  std::vector<int64_t>({20, 30, 40}));
1576  compare_array(
1577  run_simple_agg("SELECT arr3_i64 FROM array_test WHERE arr3_i64[1] = 200;", dt),
1578  std::vector<int64_t>({200, 300, 400}));
1579  compare_array(
1581  "SELECT arr3_float FROM array_test WHERE arr3_float[1] = CAST(2.2 AS FLOAT);",
1582  dt),
1583  std::vector<float>({2.2, 3.3, 4.4}));
1584  compare_array(
1585  run_simple_agg("SELECT arr3_double FROM array_test WHERE arr3_double[1] = 22.2;",
1586  dt),
1587  std::vector<double>({22.2, 33.3, 44.4}));
1588  compare_array(
1589  run_simple_agg("SELECT arr6_bool FROM array_test WHERE x < 9 AND arr6_bool[1];",
1590  dt),
1591  std::vector<int64_t>({1, 0, 1, 0, 1, 0}));
1592  }
1593 }
#define SKIP_ON_AGGREGATOR(EXP)
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void compare_array(const TargetValue &r, const std::vector< T > &arr, const double tol=-1.)
Definition: TestHelpers.h:33
+ Here is the call graph for this function:

◆ TEST() [18/183]

TEST ( Select  ,
FilterCastToDecimal   
)

Definition at line 1595 of file ExecuteTest.cpp.

References CPU, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

1595  {
1597  SKIP_NO_GPU();
1598 
1599  ASSERT_EQ(static_cast<int64_t>(5),
1600  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE x > 7.1;", dt)));
1601  ASSERT_EQ(
1602  static_cast<int64_t>(10),
1603  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE y > 42.5;", dt)));
1604  ASSERT_EQ(static_cast<int64_t>(10),
1605  v<int64_t>(run_simple_agg(
1606  "SELECT COUNT(*) FROM test WHERE ufd > -2147483648.0;", dt)));
1607  ASSERT_EQ(static_cast<int64_t>(15),
1608  v<int64_t>(run_simple_agg(
1609  "SELECT COUNT(*) FROM test WHERE ofd > -2147483648;", dt)));
1610  }
1611 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
+ Here is the call graph for this function:

◆ TEST() [19/183]

TEST ( Select  ,
FilterAndGroupByMultipleAgg   
)

Definition at line 1613 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1613  {
1615  SKIP_NO_GPU();
1616  c("SELECT MIN(x + y), COUNT(*), AVG(x + 1) FROM test WHERE x + y > 47 AND x + y < 53 "
1617  "GROUP BY x, y;",
1618  dt);
1619  c("SELECT MIN(x + y), COUNT(*), AVG(x + 1) FROM test WHERE x + y > 47 AND x + y < 53 "
1620  "GROUP BY x + 1, x + y;",
1621  dt);
1622  }
1623 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [20/183]

TEST ( Select  ,
GroupByKeylessAndNotKeyless   
)

Definition at line 1625 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1625  {
1627  SKIP_NO_GPU();
1628  c("SELECT fixed_str FROM test WHERE fixed_str = 'fish' GROUP BY fixed_str;", dt);
1629  c("SELECT AVG(x), fixed_str FROM test WHERE fixed_str = 'fish' GROUP BY fixed_str;",
1630  dt);
1631  c("SELECT AVG(smallint_nulls), fixed_str FROM test WHERE fixed_str = 'foo' GROUP BY "
1632  "fixed_str;",
1633  dt);
1634  c("SELECT null_str, AVG(smallint_nulls) FROM test GROUP BY null_str;", dt);
1635  }
1636 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [21/183]

TEST ( Select  ,
Having   
)

Definition at line 1638 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1638  {
1640  SKIP_NO_GPU();
1641  c("SELECT MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING MAX(x) > 5 ORDER BY n;",
1642  dt);
1643  c("SELECT MAX(y) AS n FROM test WHERE x = 7 GROUP BY z HAVING MAX(x) > 5 ORDER BY n "
1644  "LIMIT 1;",
1645  dt);
1646  c("SELECT MAX(y) AS n FROM test WHERE x > 7 GROUP BY z HAVING MAX(x) < 100 ORDER BY "
1647  "n;",
1648  dt);
1649  c("SELECT z, SUM(y) AS n FROM test WHERE x > 6 GROUP BY z HAVING MAX(x) < 100 ORDER "
1650  "BY n;",
1651  dt);
1652  c("SELECT z, SUM(y) AS n FROM test WHERE x > 6 GROUP BY z HAVING MAX(x) < 100 AND "
1653  "COUNT(*) > 5 ORDER BY n;",
1654  dt);
1655  c("SELECT z, SUM(y) AS n FROM test WHERE x > 6 GROUP BY z HAVING MAX(x) < 100 AND "
1656  "COUNT(*) > 9 ORDER BY n;",
1657  dt);
1658  c("SELECT str, COUNT(*) AS n FROM test GROUP BY str HAVING str IN ('bar', 'baz') "
1659  "ORDER BY str;",
1660  dt);
1661  c("SELECT str, COUNT(*) AS n FROM test GROUP BY str HAVING str LIKE 'ba_' ORDER BY "
1662  "str;",
1663  dt);
1664  c("SELECT ss, COUNT(*) AS n FROM test GROUP BY ss HAVING ss LIKE 'bo_' ORDER BY ss;",
1665  dt);
1666  c("SELECT x, COUNT(*) FROM test WHERE x > 9 GROUP BY x HAVING x > 15;", dt);
1667  c("SELECT x, AVG(y), AVG(y) FROM test GROUP BY x HAVING x >= 0 ORDER BY x;", dt);
1668  c("SELECT AVG(y), x, AVG(y) FROM test GROUP BY x HAVING x >= 0 ORDER BY x;", dt);
1669  c("SELECT x, y, COUNT(*) FROM test WHERE real_str LIKE 'nope%' GROUP BY x, y HAVING "
1670  "x >= 0 AND x < 12 AND y >= 0 "
1671  "AND y < 12 ORDER BY x, y;",
1672  dt);
1673  }
1674 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [22/183]

TEST ( Select  ,
CountDistinct   
)

Definition at line 1676 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, run_multiple_agg(), SKIP_NO_GPU, and SKIP_ON_AGGREGATOR.

1676  {
1678  SKIP_NO_GPU();
1679  c("SELECT COUNT(distinct x) FROM test;", dt);
1680  c("SELECT COUNT(distinct b) FROM test;", dt);
1681  SKIP_ON_AGGREGATOR(c("SELECT COUNT(distinct f) FROM test;",
1682  dt)); // Exception: Cannot use a fast path for COUNT distinct
1683  SKIP_ON_AGGREGATOR(c("SELECT COUNT(distinct d) FROM test;",
1684  dt)); // Exception: Cannot use a fast path for COUNT distinct
1685  c("SELECT COUNT(distinct str) FROM test;", dt);
1686  c("SELECT COUNT(distinct ss) FROM test;", dt);
1687  c("SELECT COUNT(distinct x + 1) FROM test;", dt);
1688  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x) FROM test "
1689  "GROUP BY y ORDER BY n;",
1690  dt);
1691  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x + 1) FROM "
1692  "test GROUP BY y ORDER BY n;",
1693  dt);
1694  c("SELECT COUNT(distinct dd) AS n FROM test GROUP BY y ORDER BY n;", dt);
1695  c("SELECT z, str, AVG(z), COUNT(distinct z) FROM test GROUP BY z, str ORDER BY z, "
1696  "str;",
1697  dt);
1698  c("SELECT AVG(z), COUNT(distinct x) AS dx FROM test GROUP BY y HAVING dx > 1;", dt);
1700  c("SELECT z, str, COUNT(distinct f) FROM test GROUP BY z, str ORDER BY str DESC;",
1701  dt)); // Exception: Cannot use a fast path for COUNT distinct
1702  c("SELECT COUNT(distinct x * (50000 - 1)) FROM test;", dt);
1703  EXPECT_THROW(run_multiple_agg("SELECT COUNT(distinct real_str) FROM test;", dt),
1704  std::runtime_error); // Exception: Strings must be dictionary-encoded
1705  // for COUNT(DISTINCT).
1706  }
1707 }
#define SKIP_ON_AGGREGATOR(EXP)
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [23/183]

TEST ( Select  ,
ApproxCountDistinct   
)

Definition at line 1709 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, run_multiple_agg(), and SKIP_NO_GPU.

1709  {
1711  SKIP_NO_GPU();
1712  c("SELECT APPROX_COUNT_DISTINCT(x) FROM test;",
1713  "SELECT COUNT(distinct x) FROM test;",
1714  dt);
1715  c("SELECT APPROX_COUNT_DISTINCT(x) FROM test_empty;",
1716  "SELECT COUNT(distinct x) FROM test_empty;",
1717  dt);
1718  c("SELECT APPROX_COUNT_DISTINCT(x) FROM test_one_row;",
1719  "SELECT COUNT(distinct x) FROM test_one_row;",
1720  dt);
1721  c("SELECT APPROX_COUNT_DISTINCT(b) FROM test;",
1722  "SELECT COUNT(distinct b) FROM test;",
1723  dt);
1724  c("SELECT APPROX_COUNT_DISTINCT(f) FROM test;",
1725  "SELECT COUNT(distinct f) FROM test;",
1726  dt);
1727  c("SELECT APPROX_COUNT_DISTINCT(d) FROM test;",
1728  "SELECT COUNT(distinct d) FROM test;",
1729  dt);
1730  c("SELECT APPROX_COUNT_DISTINCT(str) FROM test;",
1731  "SELECT COUNT(distinct str) FROM test;",
1732  dt);
1733  c("SELECT APPROX_COUNT_DISTINCT(null_str) FROM test;",
1734  "SELECT COUNT(distinct null_str) FROM test;",
1735  dt);
1736  c("SELECT APPROX_COUNT_DISTINCT(ss) FROM test WHERE ss IS NOT NULL;",
1737  "SELECT COUNT(distinct ss) FROM test;",
1738  dt);
1739  c("SELECT APPROX_COUNT_DISTINCT(x + 1) FROM test;",
1740  "SELECT COUNT(distinct x + 1) FROM test;",
1741  dt);
1742  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x) "
1743  "FROM test GROUP BY y ORDER "
1744  "BY n;",
1745  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x) FROM test "
1746  "GROUP BY y ORDER BY n;",
1747  dt);
1748  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x + "
1749  "1) FROM test GROUP BY y "
1750  "ORDER BY n;",
1751  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x + 1) FROM "
1752  "test GROUP BY y ORDER BY n;",
1753  dt);
1754  c("SELECT APPROX_COUNT_DISTINCT(dd) AS n FROM test GROUP BY y ORDER BY n;",
1755  "SELECT COUNT(distinct dd) AS n FROM test GROUP BY y ORDER BY n;",
1756  dt);
1757  c("SELECT z, str, AVG(z), APPROX_COUNT_DISTINCT(z) FROM test GROUP BY z, str ORDER "
1758  "BY z;",
1759  "SELECT z, str, AVG(z), COUNT(distinct z) FROM test GROUP BY z, str ORDER BY z;",
1760  dt);
1761  c("SELECT APPROX_COUNT_DISTINCT(null_str) AS n FROM test GROUP BY x ORDER BY n;",
1762  "SELECT COUNT(distinct null_str) AS n FROM test GROUP BY x ORDER BY n;",
1763  dt);
1764  c("SELECT z, APPROX_COUNT_DISTINCT(null_str) AS n FROM test GROUP BY z ORDER BY z, "
1765  "n;",
1766  "SELECT z, COUNT(distinct null_str) AS n FROM test GROUP BY z ORDER BY z, n;",
1767  dt);
1768  c("SELECT AVG(z), APPROX_COUNT_DISTINCT(x) AS dx FROM test GROUP BY y HAVING dx > 1;",
1769  "SELECT AVG(z), COUNT(distinct x) AS dx FROM test GROUP BY y HAVING dx > 1;",
1770  dt);
1771  c("SELECT approx_value, exact_value FROM (SELECT APPROX_COUNT_DISTINCT(x) AS "
1772  "approx_value FROM test), (SELECT "
1773  "COUNT(distinct x) AS exact_value FROM test);",
1774  "SELECT approx_value, exact_value FROM (SELECT COUNT(distinct x) AS approx_value "
1775  "FROM test), (SELECT "
1776  "COUNT(distinct x) AS exact_value FROM test);",
1777  dt);
1778  c("SELECT APPROX_COUNT_DISTINCT(x, 1) FROM test;",
1779  "SELECT COUNT(distinct x) FROM test;",
1780  dt);
1781  c("SELECT APPROX_COUNT_DISTINCT(b, 10) FROM test;",
1782  "SELECT COUNT(distinct b) FROM test;",
1783  dt);
1784  c("SELECT APPROX_COUNT_DISTINCT(f, 20) FROM test;",
1785  "SELECT COUNT(distinct f) FROM test;",
1786  dt);
1787  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x, 1) "
1788  "FROM test GROUP BY y ORDER "
1789  "BY n;",
1790  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x) FROM test "
1791  "GROUP BY y ORDER BY n;",
1792  dt);
1793  c("SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, APPROX_COUNT_DISTINCT(x + "
1794  "1, 1) FROM test GROUP BY y "
1795  "ORDER BY n;",
1796  "SELECT COUNT(*), MIN(x), MAX(x), AVG(y), SUM(z) AS n, COUNT(distinct x + 1) FROM "
1797  "test GROUP BY y ORDER BY n;",
1798  dt);
1799  // Test approx_count_distinct buffer allocation with multi-slot targets
1800  // sqlite does not support SAMPLE, grab the first row only
1801  c("SELECT SAMPLE(real_str), str, APPROX_COUNT_DISTINCT(x) FROM test WHERE real_str = "
1802  "'real_bar' GROUP BY str;",
1803  "SELECT real_str, str, COUNT( distinct x) FROM test WHERE real_str = "
1804  "'real_bar' GROUP BY str;",
1805  dt);
1806  c("SELECT SAMPLE(real_str), str, APPROX_COUNT_DISTINCT(x) FROM test WHERE real_str = "
1807  "'real_foo' GROUP BY str;",
1808  "SELECT real_str, str, COUNT(distinct x) FROM test WHERE real_str = "
1809  "'real_foo' GROUP BY str, real_str;",
1810  dt);
1811 
1812  EXPECT_NO_THROW(run_multiple_agg(
1813  "SELECT APPROX_COUNT_DISTINCT(x), SAMPLE(real_str) FROM test GROUP BY x;", dt));
1814  EXPECT_THROW(
1815  run_multiple_agg("SELECT APPROX_COUNT_DISTINCT(real_str) FROM test;", dt),
1816  std::runtime_error);
1817  EXPECT_THROW(run_multiple_agg("SELECT APPROX_COUNT_DISTINCT(x, 0) FROM test;", dt),
1818  std::runtime_error);
1819  }
1820 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [24/183]

TEST ( Select  ,
ScanNoAggregation   
)

Definition at line 1822 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1822  {
1824  SKIP_NO_GPU();
1825  c("SELECT * FROM test ORDER BY x ASC, y ASC;", dt);
1826  c("SELECT t.* FROM test t ORDER BY x ASC, y ASC;", dt);
1827  c("SELECT x, z, t FROM test ORDER BY x ASC, y ASC;", dt);
1828  c("SELECT x, y, x + 1 FROM test ORDER BY x ASC, y ASC;", dt);
1829  c("SELECT x + z, t FROM test WHERE x <> 7 AND y > 42;", dt);
1830  c("SELECT * FROM test WHERE x > 8;", dt);
1831  c("SELECT fx FROM test WHERE fx IS NULL;", dt);
1832  c("SELECT z,t,f,m,d,x,real_str,u,z,y FROM test WHERE z = -78 AND t = "
1833  "1002 AND x >= 8 AND y = 43 AND d > 1.0 AND f > 1.0 AND real_str = 'real_bar' "
1834  "ORDER BY f ASC;",
1835  dt);
1836  c("SELECT * FROM test WHERE d > 2.4 AND real_str IS NOT NULL AND fixed_str IS NULL "
1837  "AND z = 102 AND fn < 0 AND y = 43 AND t >= 0 AND x <> 8;",
1838  dt);
1839  c("SELECT * FROM test WHERE d > 2.4 AND real_str IS NOT NULL AND fixed_str IS NULL "
1840  "AND z = 102 AND fn < 0 AND y = 43 AND t >= 0 AND x = 8;",
1841  dt);
1842  c("SELECT real_str,f,fn,y,d,x,z,str,fixed_str,t,dn FROM test WHERE f IS NOT NULL AND "
1843  "y IS NOT NULL AND str = 'bar' AND x >= 7 AND t < 1003 AND z < 0;",
1844  dt);
1845  c("SELECT t,y,str,real_str,d,fixed_str,dn,fn,z,f,x FROM test WHERE f IS NOT NULL AND "
1846  "y IS NOT NULL AND str = 'baz' AND x >= 7 AND t < 1003 AND f > 1.2 LIMIT 1;",
1847  dt);
1848  c("SELECT fn,real_str,str,z,d,x,fixed_str,dn,y,t,f FROM test WHERE f < 1.4 AND "
1849  "real_str IS NOT NULL AND fixed_str IS NULL AND z = 102 AND dn < 0 AND y = 43;",
1850  dt);
1851  c("SELECT dn,str,y,z,fixed_str,fn,d,real_str,t,f,x FROM test WHERE z < 0 AND f < 2 "
1852  "AND d > 2.0 AND fn IS NOT NULL AND dn < 2000 AND str IS NOT NULL AND fixed_str = "
1853  "'bar' AND real_str = 'real_bar' AND t >= 1001 AND y >= 42 AND x > 7 ORDER BY z, "
1854  "x;",
1855  dt);
1856  c("SELECT z,f,d,str,real_str,x,dn,y,t,fn,fixed_str FROM test WHERE fn IS NULL AND dn "
1857  "IS NULL AND x >= 0 AND real_str = 'real_foo' ORDER BY y;",
1858  dt);
1859  }
1860 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [25/183]

TEST ( Select  ,
OrderBy   
)

Definition at line 1862 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CHECK_EQ, CPU, anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, run_multiple_agg(), and SKIP_NO_GPU.

1862  {
1864  SKIP_NO_GPU();
1865  const auto rows = run_multiple_agg(
1866  "SELECT x, y, z + t, x * y AS m FROM test ORDER BY 3 desc LIMIT 5;", dt);
1867  CHECK_EQ(rows->rowCount(), std::min(size_t(5), static_cast<size_t>(g_num_rows)) + 0);
1868  CHECK_EQ(rows->colCount(), size_t(4));
1869  for (size_t row_idx = 0; row_idx < rows->rowCount(); ++row_idx) {
1870  ASSERT_TRUE(v<int64_t>(rows->getRowAt(row_idx, 0, true)) == 8 ||
1871  v<int64_t>(rows->getRowAt(row_idx, 0, true)) == 7);
1872  ASSERT_EQ(v<int64_t>(rows->getRowAt(row_idx, 1, true)), 43);
1873  ASSERT_EQ(v<int64_t>(rows->getRowAt(row_idx, 2, true)), 1104);
1874  ASSERT_TRUE(v<int64_t>(rows->getRowAt(row_idx, 3, true)) == 344 ||
1875  v<int64_t>(rows->getRowAt(row_idx, 3, true)) == 301);
1876  }
1877  c("SELECT x, COUNT(distinct y) AS n FROM test GROUP BY x ORDER BY n DESC;", dt);
1878  c("SELECT x x1, x, COUNT(*) AS val FROM test GROUP BY x HAVING val > 5 ORDER BY val "
1879  "DESC LIMIT 5;",
1880  dt);
1881  c("SELECT ufd, COUNT(*) n FROM test GROUP BY ufd, str ORDER BY ufd, n;", dt);
1882  c("SELECT -x, COUNT(*) FROM test GROUP BY x ORDER BY x DESC;", dt);
1883  c("SELECT real_str FROM test WHERE real_str LIKE '%real%' ORDER BY real_str ASC;",
1884  dt);
1885  c("SELECT ss FROM test GROUP by ss ORDER BY ss ASC NULLS FIRST;",
1886  "SELECT ss FROM test GROUP by ss ORDER BY ss ASC;",
1887  dt);
1888  c("SELECT str, COUNT(*) n FROM test WHERE x < 0 GROUP BY str ORDER BY n DESC LIMIT "
1889  "5;",
1890  dt);
1891  c("SELECT x FROM test ORDER BY x LIMIT 50;", dt);
1892  c("SELECT x FROM test ORDER BY x LIMIT 5;", dt);
1893  c("SELECT x FROM test ORDER BY x ASC LIMIT 20;", dt);
1894  c("SELECT dd FROM test ORDER BY dd ASC LIMIT 20;", dt);
1895  c("SELECT f FROM test ORDER BY f ASC LIMIT 5;", dt);
1896  c("SELECT f FROM test ORDER BY f ASC LIMIT 20;", dt);
1897  c("SELECT fn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1898  "SELECT fn as k FROM test ORDER BY k ASC LIMIT 5;",
1899  dt);
1900  c("SELECT fn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1901  "SELECT fn as k FROM test ORDER BY k ASC LIMIT 20;",
1902  dt);
1903  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1904  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 5;",
1905  dt);
1906  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1907  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 20;",
1908  dt);
1909  c("SELECT ff as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1910  "SELECT ff as k FROM test ORDER BY k ASC LIMIT 5;",
1911  dt);
1912  c("SELECT ff as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1913  "SELECT ff as k FROM test ORDER BY k ASC LIMIT 20;",
1914  dt);
1915  c("SELECT d as k FROM test ORDER BY k ASC LIMIT 5;", dt);
1916  c("SELECT d as k FROM test ORDER BY k ASC LIMIT 20;", dt);
1917  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1918  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 5;",
1919  dt);
1920  c("SELECT dn as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1921  "SELECT dn as k FROM test ORDER BY k ASC LIMIT 20;",
1922  dt);
1923  c("SELECT ofq AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1924  "SELECT ofq as k FROM test ORDER BY k ASC LIMIT 5;",
1925  dt);
1926  c("SELECT ofq AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1927  "SELECT ofq as k FROM test ORDER BY k ASC LIMIT 20;",
1928  dt);
1929  c("SELECT ufq as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1930  "SELECT ufq as k FROM test ORDER BY k ASC LIMIT 5;",
1931  dt);
1932  c("SELECT ufq as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1933  "SELECT ufq as k FROM test ORDER BY k ASC LIMIT 20;",
1934  dt);
1935  c("SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1936  "SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 5;",
1937  dt);
1938  c("SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1939  "SELECT CAST(ofd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 20;",
1940  dt);
1941  c("SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 5;",
1942  "SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 5;",
1943  dt);
1944  c("SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1945  "SELECT CAST(ufd AS FLOAT) as k FROM test ORDER BY k ASC LIMIT 20;",
1946  dt);
1947  c("SELECT m AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1948  "SELECT m AS k FROM test ORDER BY k ASC LIMIT 20;",
1949  dt);
1950  c("SELECT n AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1951  "SELECT n AS k FROM test ORDER BY k ASC LIMIT 20;",
1952  dt);
1953  c("SELECT o AS k FROM test ORDER BY k ASC NULLS FIRST LIMIT 20;",
1954  "SELECT o AS k FROM test ORDER BY k ASC LIMIT 20;",
1955  dt);
1956  }
1957 }
#define CHECK_EQ(x, y)
Definition: Logger.h:195
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [26/183]

TEST ( Select  ,
TopKHeap   
)

Definition at line 1959 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

1959  {
1961  SKIP_NO_GPU();
1962  c("SELECT str, x FROM proj_top ORDER BY x DESC LIMIT 1;", dt);
1963  }
1964 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [27/183]

TEST ( Select  ,
ComplexQueries   
)

Definition at line 1966 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CHECK, CHECK_EQ, CPU, GPU, run_multiple_agg(), and SKIP_NO_GPU.

1966  {
1968  SKIP_NO_GPU();
1969  c("SELECT COUNT(*) * MAX(y) - SUM(z) FROM test;", dt);
1970  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1971  "AND 200 GROUP BY x, y;",
1972  dt);
1973  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1974  "AND 200 "
1975  "GROUP BY x, y HAVING y > 2 * x AND MIN(y) > MAX(x);",
1976  dt);
1977  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1978  "AND 200 "
1979  "GROUP BY x, y HAVING y > 2 * x AND MIN(y) > MAX(x) + 35;",
1980  dt);
1981  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test WHERE z BETWEEN 100 "
1982  "AND 200 "
1983  "GROUP BY x, y HAVING y > 2 * x AND MIN(y) > MAX(x) + 36;",
1984  dt);
1985  c("SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test "
1986  "WHERE z BETWEEN 100 AND 200 GROUP BY a, y;",
1987  dt);
1988  c("SELECT x, y FROM (SELECT a.str AS str, b.x AS x, a.y AS y FROM test a, join_test "
1989  "b WHERE a.x = b.x) WHERE str = "
1990  "'foo' ORDER BY x LIMIT 1;",
1991  dt);
1992  const auto rows = run_multiple_agg(
1993  "SELECT x + y AS a, COUNT(*) * MAX(y) - SUM(z) AS b FROM test "
1994  "WHERE z BETWEEN 100 AND 200 GROUP BY x, y ORDER BY a DESC LIMIT 2;",
1995  dt);
1996  ASSERT_EQ(rows->rowCount(), size_t(2));
1997  {
1998  auto crt_row = rows->getNextRow(true, true);
1999  CHECK_EQ(size_t(2), crt_row.size());
2000  ASSERT_EQ(v<int64_t>(crt_row[0]), 50);
2001  ASSERT_EQ(v<int64_t>(crt_row[1]), -295);
2002  }
2003  {
2004  auto crt_row = rows->getNextRow(true, true);
2005  CHECK_EQ(size_t(2), crt_row.size());
2006  ASSERT_EQ(v<int64_t>(crt_row[0]), 49);
2007  ASSERT_EQ(v<int64_t>(crt_row[1]), -590);
2008  }
2009  auto empty_row = rows->getNextRow(true, true);
2010  CHECK(empty_row.empty());
2011  }
2012 }
#define CHECK_EQ(x, y)
Definition: Logger.h:195
#define CHECK(condition)
Definition: Logger.h:187
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [28/183]

TEST ( Select  ,
MultiStepQueries   
)

Definition at line 2014 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, g_skip_intermediate_count, GPU, and SKIP_NO_GPU.

2014  {
2016  SKIP_NO_GPU();
2017 
2018  const auto skip_intermediate_count = g_skip_intermediate_count;
2019  ScopeGuard reset_skip_intermediate_count = [&skip_intermediate_count] {
2020  g_skip_intermediate_count = skip_intermediate_count;
2021  };
2022 
2023  c("SELECT z, (z * SUM(x)) / SUM(y) + 1 FROM test GROUP BY z ORDER BY z;", dt);
2024  c("SELECT z,COUNT(*), AVG(x) / SUM(y) + 1 FROM test GROUP BY z ORDER BY z;", dt);
2025  }
2026 }
bool g_skip_intermediate_count
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [29/183]

TEST ( Select  ,
GroupByPushDownFilterIntoExprRange   
)

Definition at line 2028 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, run_multiple_agg(), and SKIP_NO_GPU.

2028  {
2030  SKIP_NO_GPU();
2031  const auto rows = run_multiple_agg(
2032  "SELECT b, COUNT(*) AS n FROM test WHERE b GROUP BY b ORDER BY b", dt);
2033  ASSERT_EQ(
2034  size_t(1),
2035  rows->rowCount()); // Sqlite does not have a boolean type, so do this for now
2036  c("SELECT x, COUNT(*) AS n FROM test WHERE x > 7 GROUP BY x ORDER BY x", dt);
2037  c("SELECT y, COUNT(*) AS n FROM test WHERE y <= 43 GROUP BY y ORDER BY n DESC", dt);
2038  c("SELECT z, COUNT(*) AS n FROM test WHERE z <= 43 AND y > 10 GROUP BY z ORDER BY n "
2039  "DESC",
2040  dt);
2041  c("SELECT t, SUM(y) AS sum_y FROM test WHERE t < 2000 GROUP BY t ORDER BY t DESC",
2042  dt);
2043  c("SELECT t, SUM(y) AS sum_y FROM test WHERE t < 2000 GROUP BY t ORDER BY sum_y", dt);
2044  c("SELECT o, COUNT(*) as n FROM test WHERE o <= '1999-09-09' GROUP BY o ORDER BY n",
2045  dt);
2046  c("SELECT t + x, AVG(x) AS avg_x FROM test WHERE z <= 50 and t < 2000 GROUP BY t + x "
2047  "ORDER BY avg_x DESC",
2048  dt);
2049  }
2050 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [30/183]

TEST ( Select  ,
GroupByExprNoFilterNoAggregate   
)

Definition at line 2052 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

2052  {
2054  SKIP_NO_GPU();
2055  c("SELECT x + y AS a FROM test GROUP BY a ORDER BY a;", dt);
2056  ASSERT_EQ(8,
2057  v<int64_t>(run_simple_agg("SELECT TRUNCATE(x, 0) AS foo FROM test GROUP BY "
2058  "TRUNCATE(x, 0) ORDER BY foo DESC LIMIT 1;",
2059  dt)));
2060  }
2061 }
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [31/183]

TEST ( Select  ,
DistinctProjection   
)

Definition at line 2063 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, and SKIP_NO_GPU.

2063  {
2065  SKIP_NO_GPU();
2066  c("SELECT DISTINCT str FROM test ORDER BY str;", dt);
2067  c("SELECT DISTINCT(str), SUM(x) FROM test WHERE x > 7 GROUP BY str LIMIT 2;", dt);
2068  }
2069 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [32/183]

TEST ( Select  ,
Case   
)

Definition at line 2071 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, g_constrained_by_in_threshold, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

2071  {
2073  SKIP_NO_GPU();
2074  c("SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 WHEN x BETWEEN 8 AND 9 THEN 2 ELSE "
2075  "3 END) FROM test;",
2076  dt);
2077  c("SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 END) FROM test;", dt);
2078  c("SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 WHEN x BETWEEN 8 AND 9 THEN 2 ELSE "
2079  "3 END) "
2080  "FROM test WHERE CASE WHEN y BETWEEN 42 AND 43 THEN 5 ELSE 4 END > 4;",
2081  dt);
2082  ASSERT_EQ(std::numeric_limits<int64_t>::min(),
2083  v<int64_t>(run_simple_agg(
2084  "SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1 WHEN x BETWEEN 8 AND 9 "
2085  "THEN 2 ELSE 3 END) FROM test "
2086  "WHERE CASE WHEN y BETWEEN 44 AND 45 THEN 5 ELSE 4 END > 4;",
2087  dt)));
2088  c("SELECT CASE WHEN x + y > 50 THEN 77 ELSE 88 END AS foo, COUNT(*) FROM test GROUP "
2089  "BY foo ORDER BY foo;",
2090  dt);
2091  ASSERT_EQ(std::numeric_limits<double>::min(),
2092  v<double>(run_simple_agg(
2093  "SELECT SUM(CASE WHEN x BETWEEN 6 AND 7 THEN 1.1 WHEN x BETWEEN 8 AND "
2094  "9 THEN 2.2 ELSE 3.3 END) FROM "
2095  "test WHERE CASE WHEN y BETWEEN 44 AND 45 THEN 5.1 ELSE 3.9 END > 4;",
2096  dt)));
2097  c("SELECT CASE WHEN x BETWEEN 1 AND 3 THEN 'oops 1' WHEN x BETWEEN 4 AND 6 THEN "
2098  "'oops 2' ELSE real_str END c "
2099  "FROM "
2100  "test ORDER BY c ASC;",
2101  dt);
2102 
2103  c("SELECT CASE WHEN x BETWEEN 1 AND 3 THEN 'oops 1' WHEN x BETWEEN 4 AND 6 THEN "
2104  "'oops 2' ELSE str END c FROM "
2105  "test "
2106  "ORDER BY c ASC;",
2107  dt);
2108  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN "
2109  "'eight' ELSE 'ooops' END c FROM "
2110  "test ORDER BY c ASC;",
2111  dt);
2112  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN "
2113  "real_str ELSE 'ooops' END AS g "
2114  "FROM test ORDER BY g ASC;",
2115  dt);
2116  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN str "
2117  "ELSE 'ooops' END c FROM test "
2118  "ORDER BY c ASC;",
2119  dt);
2120  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN 'seven' WHEN x BETWEEN 7 AND 10 THEN "
2121  "'eight' ELSE 'ooops' END c FROM "
2122  "test ORDER BY c ASC;",
2123  dt);
2124  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN str WHEN x BETWEEN 7 AND 10 THEN 'eight' "
2125  "ELSE 'ooops' END AS g, "
2126  "COUNT(*) FROM test GROUP BY g ORDER BY g;",
2127  dt);
2128  c("SELECT y AS key0, SUM(CASE WHEN x > 7 THEN x / (x - 7) ELSE 99 END) FROM test "
2129  "GROUP BY key0 ORDER BY key0;",
2130  dt);
2131  c("SELECT CASE WHEN str IN ('str1', 'str3', 'str8') THEN 'foo' WHEN str IN ('str2', "
2132  "'str4', 'str9') THEN 'bar' "
2133  "ELSE 'baz' END AS bucketed_str, COUNT(*) AS n FROM query_rewrite_test GROUP BY "
2134  "bucketed_str ORDER BY n "
2135  "DESC;",
2136  dt);
2137  c("SELECT CASE WHEN y > 40 THEN x END c, x FROM test ORDER BY c ASC;", dt);
2138  c("SELECT COUNT(CASE WHEN str = 'foo' THEN 1 END) FROM test;", dt);
2139  c("SELECT COUNT(CASE WHEN str = 'foo' THEN 1 ELSE NULL END) FROM test;", dt);
2140  c("SELECT CASE WHEN x BETWEEN 1 AND 3 THEN y ELSE y END AS foobar FROM test ORDER BY "
2141  "foobar DESC;",
2142  dt);
2143  c("SELECT x, AVG(CASE WHEN y BETWEEN 41 AND 42 THEN y END) FROM test GROUP BY x "
2144  "ORDER BY x;",
2145  dt);
2146  c("SELECT x, SUM(CASE WHEN y BETWEEN 41 AND 42 THEN y END) FROM test GROUP BY x "
2147  "ORDER BY x;",
2148  dt);
2149  c("SELECT x, COUNT(CASE WHEN y BETWEEN 41 AND 42 THEN y END) FROM test GROUP BY x "
2150  "ORDER BY x;",
2151  dt);
2152  c("SELECT CASE WHEN x > 8 THEN 'oops' ELSE 'ok' END FROM test LIMIT 1;", dt);
2153  c("SELECT CASE WHEN x < 9 THEN 'ok' ELSE 'oops' END FROM test LIMIT 1;", dt);
2154  c("SELECT CASE WHEN str IN ('foo', 'bar') THEN str END key1, COUNT(*) FROM test "
2155  "GROUP BY str HAVING key1 IS NOT "
2156  "NULL ORDER BY key1;",
2157  dt);
2158 
2159  c("SELECT CASE WHEN str IN ('foo') THEN 'FOO' WHEN str IN ('bar') THEN 'BAR' ELSE "
2160  "'BAZ' END AS g, COUNT(*) "
2161  "FROM test GROUP BY g ORDER BY g DESC;",
2162  dt);
2163  c("SELECT x, COUNT(case when y = 42 then 1 else 0 end) AS n1, COUNT(*) AS n2 FROM "
2164  "test GROUP BY x ORDER BY n2 "
2165  "DESC;",
2166  dt);
2167  c("SELECT CASE WHEN test.str = 'foo' THEN 'foo' ELSE test.str END AS g FROM test "
2168  "GROUP BY g ORDER BY g ASC;",
2169  dt);
2170  c("SELECT COUNT(*) FROM test WHERE CASE WHEN x > 8 THEN 'oops' END = 'oops' OR CASE "
2171  "WHEN x > 8 THEN 'oops' END = 'oops';",
2172  dt);
2173  ASSERT_EQ(
2174  int64_t(1418428800),
2175  v<int64_t>(run_simple_agg(
2176  "SELECT CASE WHEN 1 > 0 THEN DATE_TRUNC(day, m) ELSE DATE_TRUNC(year, m) END "
2177  "AS date_bin FROM test GROUP BY date_bin;",
2178  dt)));
2179  ASSERT_EQ(
2180  int64_t(1388534400),
2181  v<int64_t>(run_simple_agg(
2182  "SELECT CASE WHEN 1 < 0 THEN DATE_TRUNC(day, m) ELSE DATE_TRUNC(year, m) END "
2183  "AS date_bin FROM test GROUP BY date_bin;",
2184  dt)));
2185  c("SELECT COUNT(CASE WHEN str IN ('foo', 'bar') THEN 'foo_bar' END) from test;", dt);
2186  ASSERT_EQ(
2187  int64_t(1),
2188  v<int64_t>(run_simple_agg(
2189  "SELECT MIN(CASE WHEN x BETWEEN 7 AND 8 THEN true ELSE false END) FROM test;",
2190  dt)));
2191  ASSERT_EQ(
2192  int64_t(0),
2193  v<int64_t>(run_simple_agg(
2194  "SELECT MIN(CASE WHEN x BETWEEN 6 AND 7 THEN true ELSE false END) FROM test;",
2195  dt)));
2196  c("SELECT CASE WHEN test.str in ('boo', 'simple', 'case', 'not', 'much', 'to', "
2197  "'see', 'foo_in_case', 'foo', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', "
2198  "'k', 'l') THEN 'foo_in_case' ELSE test.str END AS g FROM test GROUP BY g ORDER BY "
2199  "g ASC;",
2200  dt);
2201  c("SELECT CASE WHEN shared_dict is null THEN 'hello' ELSE 'world' END key0, count(*) "
2202  "val FROM test GROUP BY key0 ORDER BY val;",
2203  dt);
2204  c("WITH distinct_x AS (SELECT x FROM test GROUP BY x) SELECT SUM(CASE WHEN x = 7 "
2205  "THEN -32767 ELSE -1 END) FROM distinct_x",
2206  dt);
2207  c("WITH distinct_x AS (SELECT x FROM test GROUP BY x) SELECT AVG(CASE WHEN x = 7 "
2208  "THEN -32767 ELSE -1 END) FROM distinct_x",
2209  dt);
2210  c("SELECT CASE WHEN x BETWEEN 1 AND 7 THEN '1' WHEN x BETWEEN 8 AND 10 THEN '2' ELSE "
2211  "real_str END AS c FROM test WHERE y IN (43) ORDER BY c ASC;",
2212  dt);
2213 
2214  const auto constrained_by_in_threshold_state = g_constrained_by_in_threshold;
2216  ScopeGuard reset_constrained_by_in_threshold = [&constrained_by_in_threshold_state] {
2217  g_constrained_by_in_threshold = constrained_by_in_threshold_state;
2218  };
2219  c("SELECT fixed_str AS key0, str as key1, count(*) as val FROM test WHERE "
2220  "((fixed_str IN (SELECT fixed_str FROM test GROUP BY fixed_str))) GROUP BY key0, "
2221  "key1 ORDER BY val desc;",
2222  dt);
2223  }
2224 }
size_t g_constrained_by_in_threshold
Definition: Execute.cpp:89
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [33/183]

TEST ( Select  ,
Strings   
)

Definition at line 2226 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), SKIP_NO_GPU, and SKIP_ON_AGGREGATOR.

2226  {
2228  SKIP_NO_GPU();
2229 
2230  c("SELECT str, COUNT(*) FROM test GROUP BY str HAVING COUNT(*) > 5 ORDER BY str;",
2231  dt);
2232  c("SELECT str, COUNT(*) FROM test WHERE str = 'bar' GROUP BY str HAVING COUNT(*) > 4 "
2233  "ORDER BY str;",
2234  dt);
2235  c("SELECT str, COUNT(*) FROM test WHERE str = 'bar' GROUP BY str HAVING COUNT(*) > 5 "
2236  "ORDER BY str;",
2237  dt);
2238  c("SELECT str, COUNT(*) FROM test where str IS NOT NULL GROUP BY str ORDER BY str;",
2239  dt);
2240  c("SELECT COUNT(*) FROM test WHERE str IS NULL;", dt);
2241  c("SELECT COUNT(*) FROM test WHERE str IS NOT NULL;", dt);
2242  c("SELECT COUNT(*) FROM test WHERE ss IS NULL;", dt);
2243  c("SELECT COUNT(*) FROM test WHERE ss IS NOT NULL;", dt);
2244  c("SELECT COUNT(*) FROM test WHERE str LIKE '%%%';", dt);
2245  c("SELECT COUNT(*) FROM test WHERE str LIKE 'ba%';", dt);
2246  c("SELECT COUNT(*) FROM test WHERE str LIKE '%eal_bar';", dt);
2247  c("SELECT COUNT(*) FROM test WHERE str LIKE '%ba%';", dt);
2248  c("SELECT * FROM test WHERE str LIKE '%' ORDER BY x ASC, y ASC;", dt);
2249  c("SELECT * FROM test WHERE str LIKE 'f%%' ORDER BY x ASC, y ASC;", dt);
2250  c("SELECT * FROM test WHERE str LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2251  c("SELECT * FROM test WHERE ss LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2252  c("SELECT * FROM test WHERE str LIKE '@f%%' ESCAPE '@' ORDER BY x ASC, y ASC;", dt);
2253  c("SELECT COUNT(*) FROM test WHERE str LIKE 'ba_' or str LIKE 'fo_';", dt);
2254  c("SELECT COUNT(*) FROM test WHERE str IS NULL;", dt);
2255  c("SELECT COUNT(*) FROM test WHERE str IS NOT NULL;", dt);
2256  c("SELECT COUNT(*) FROM test WHERE str > 'bar';", dt);
2257  c("SELECT COUNT(*) FROM test WHERE str > 'fo';", dt);
2258  c("SELECT COUNT(*) FROM test WHERE str >= 'bar';", dt);
2259  c("SELECT COUNT(*) FROM test WHERE 'bar' < str;", dt);
2260  c("SELECT COUNT(*) FROM test WHERE 'fo' < str;", 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 <> 'bar';", dt);
2265  c("SELECT COUNT(*) FROM test WHERE 'bar' <> str;", dt);
2266  c("SELECT COUNT(*) FROM test WHERE str = 'foo' OR str = 'bar';", dt);
2267  // The following tests throw Cast from dictionary-encoded string to none-encoded not
2268  // supported for distributed queries in distributed mode
2269  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE str = real_str;", dt));
2270  c("SELECT COUNT(*) FROM test WHERE str <> str;", dt);
2271  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE ss <> str;", dt));
2272  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE ss = str;", dt));
2273  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE LENGTH(str) = 3;", dt));
2274  c("SELECT fixed_str, COUNT(*) FROM test GROUP BY fixed_str HAVING COUNT(*) > 5 ORDER "
2275  "BY fixed_str;",
2276  dt);
2277  c("SELECT fixed_str, COUNT(*) FROM test WHERE fixed_str = 'bar' GROUP BY fixed_str "
2278  "HAVING COUNT(*) > 4 ORDER BY "
2279  "fixed_str;",
2280  dt);
2281  c("SELECT COUNT(*) FROM emp WHERE ename LIKE 'D%%' OR ename = 'Julia';", dt);
2283  ASSERT_EQ(2 * g_num_rows,
2284  v<int64_t>(run_simple_agg(
2285  "SELECT COUNT(*) FROM test WHERE CHAR_LENGTH(str) = 3;",
2286  dt)))); // Cast from dictionary-encoded string to none-encoded not
2287  // supported for distributed queries
2288  ASSERT_EQ(g_num_rows,
2289  v<int64_t>(run_simple_agg(
2290  "SELECT COUNT(*) FROM test WHERE str ILIKE 'f%%';", dt)));
2291  ASSERT_EQ(g_num_rows,
2292  v<int64_t>(run_simple_agg(
2293  "SELECT COUNT(*) FROM test WHERE (str ILIKE 'f%%');", dt)));
2294  ASSERT_EQ(g_num_rows,
2295  v<int64_t>(run_simple_agg(
2296  "SELECT COUNT(*) FROM test WHERE ( str ILIKE 'f%%' );", dt)));
2297  ASSERT_EQ(0,
2298  v<int64_t>(run_simple_agg(
2299  "SELECT COUNT(*) FROM test WHERE str ILIKE 'McDonald''s';", dt)));
2300  ASSERT_EQ("foo",
2301  boost::get<std::string>(v<NullableString>(run_simple_agg(
2302  "SELECT str FROM test WHERE REGEXP_LIKE(str, '^f.?.+');", dt))));
2303  ASSERT_EQ("bar",
2304  boost::get<std::string>(v<NullableString>(run_simple_agg(
2305  "SELECT str FROM test WHERE REGEXP_LIKE(str, '^[a-z]+r$');", dt))));
2306  ASSERT_EQ(2 * g_num_rows,
2307  v<int64_t>(run_simple_agg(
2308  "SELECT COUNT(*) FROM test WHERE str REGEXP '.*';", dt)));
2309  ASSERT_EQ(2 * g_num_rows,
2310  v<int64_t>(run_simple_agg(
2311  "SELECT COUNT(*) FROM test WHERE str REGEXP '...';", dt)));
2312  ASSERT_EQ(2 * g_num_rows,
2313  v<int64_t>(run_simple_agg(
2314  "SELECT COUNT(*) FROM test WHERE str REGEXP '.+.+.+';", dt)));
2315  ASSERT_EQ(2 * g_num_rows,
2316  v<int64_t>(run_simple_agg(
2317  "SELECT COUNT(*) FROM test WHERE str REGEXP '.?.?.?';", dt)));
2318  ASSERT_EQ(2 * g_num_rows,
2319  v<int64_t>(run_simple_agg(
2320  "SELECT COUNT(*) FROM test WHERE str REGEXP 'ba.' or str REGEXP 'fo.';",
2321  dt)));
2322  ASSERT_EQ(2 * g_num_rows,
2323  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE "
2324  "REGEXP_LIKE(str, 'ba.') or str REGEXP 'fo.?';",
2325  dt)));
2326  ASSERT_EQ(2 * g_num_rows,
2327  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE str REGEXP "
2328  "'ba.' or REGEXP_LIKE(str, 'fo.+');",
2329  dt)));
2330  ASSERT_EQ(g_num_rows,
2331  v<int64_t>(run_simple_agg(
2332  "SELECT COUNT(*) FROM test WHERE str REGEXP 'ba.+';", dt)));
2333  ASSERT_EQ(g_num_rows,
2334  v<int64_t>(run_simple_agg(
2335  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(str, '.?ba.*');", dt)));
2336  ASSERT_EQ(
2337  2 * g_num_rows,
2338  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE "
2339  "REGEXP_LIKE(str,'ba.') or REGEXP_LIKE(str, 'fo.+');",
2340  dt)));
2341  ASSERT_EQ(2 * g_num_rows,
2342  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE str REGEXP "
2343  "'ba.' or REGEXP_LIKE(str, 'fo.+');",
2344  dt)));
2345  ASSERT_EQ(2 * g_num_rows,
2346  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE "
2347  "REGEXP_LIKE(str, 'ba.') or str REGEXP 'fo.?';",
2348  dt)));
2349  ASSERT_EQ(2 * g_num_rows,
2350  v<int64_t>(run_simple_agg(
2351  "SELECT COUNT(*) FROM test WHERE str REGEXP 'ba.' or str REGEXP 'fo.';",
2352  dt)));
2353  }
2354 }
#define SKIP_ON_AGGREGATOR(EXP)
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [34/183]

TEST ( Select  ,
SharedDictionary   
)

Definition at line 2356 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, run_ddl_statement(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), SKIP_NO_GPU, and SKIP_ON_AGGREGATOR.

2356  {
2358  SKIP_NO_GPU();
2359 
2360  c("SELECT shared_dict, COUNT(*) FROM test GROUP BY shared_dict HAVING COUNT(*) > 5 "
2361  "ORDER BY shared_dict;",
2362  dt);
2363  c("SELECT shared_dict, COUNT(*) FROM test WHERE shared_dict = 'bar' GROUP BY "
2364  "shared_dict HAVING COUNT(*) > 4 ORDER "
2365  "BY shared_dict;",
2366  dt);
2367  c("SELECT shared_dict, COUNT(*) FROM test WHERE shared_dict = 'bar' GROUP BY "
2368  "shared_dict HAVING COUNT(*) > 5 ORDER "
2369  "BY shared_dict;",
2370  dt);
2371  c("SELECT shared_dict, COUNT(*) FROM test where shared_dict IS NOT NULL GROUP BY "
2372  "shared_dict ORDER BY shared_dict;",
2373  dt);
2374  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NULL;", dt);
2375  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NOT NULL;", dt);
2376  c("SELECT COUNT(*) FROM test WHERE ss IS NULL;", dt);
2377  c("SELECT COUNT(*) FROM test WHERE ss IS NOT NULL;", dt);
2378  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE '%%%';", dt);
2379  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE 'ba%';", dt);
2380  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE '%eal_bar';", dt);
2381  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE '%ba%';", dt);
2382  c("SELECT * FROM test WHERE shared_dict LIKE '%' ORDER BY x ASC, y ASC;", dt);
2383  c("SELECT * FROM test WHERE shared_dict LIKE 'f%%' ORDER BY x ASC, y ASC;", dt);
2384  c("SELECT * FROM test WHERE shared_dict LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2385  c("SELECT * FROM test WHERE ss LIKE 'f%\%' ORDER BY x ASC, y ASC;", dt);
2386  c("SELECT * FROM test WHERE shared_dict LIKE '@f%%' ESCAPE '@' ORDER BY x ASC, y "
2387  "ASC;",
2388  dt);
2389  c("SELECT COUNT(*) FROM test WHERE shared_dict LIKE 'ba_' or shared_dict LIKE 'fo_';",
2390  dt);
2391  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NULL;", dt);
2392  c("SELECT COUNT(*) FROM test WHERE shared_dict IS NOT NULL;", 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 <> 'bar';", dt);
2396  c("SELECT COUNT(*) FROM test WHERE 'bar' <> shared_dict;", dt);
2397  c("SELECT COUNT(*) FROM test WHERE shared_dict = 'foo' OR shared_dict = 'bar';", dt);
2398  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE shared_dict = real_str;", dt));
2399  c("SELECT COUNT(*) FROM test WHERE shared_dict <> shared_dict;", dt);
2400  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'bar';", dt);
2401  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'fo';", dt);
2402  c("SELECT COUNT(*) FROM test WHERE shared_dict >= 'bar';", dt);
2403  c("SELECT COUNT(*) FROM test WHERE 'bar' < shared_dict;", dt);
2404  c("SELECT COUNT(*) FROM test WHERE 'fo' < shared_dict;", dt);
2405  c("SELECT COUNT(*) FROM test WHERE 'bar' <= shared_dict;", dt);
2406  SKIP_ON_AGGREGATOR(c("SELECT COUNT(*) FROM test WHERE LENGTH(shared_dict) = 3;", dt));
2407 
2408  EXPECT_THROW(run_ddl_statement("CREATE TABLE t1(a text, b text, SHARED DICTIONARY "
2409  "(b) REFERENCES t1(a), SHARED "
2410  "DICTIONARY (a) REFERENCES t1(b));"),
2411  std::runtime_error);
2412 
2413  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2414  15,
2415  v<int64_t>(run_simple_agg(
2416  "SELECT COUNT(*) FROM test WHERE CHAR_LENGTH(shared_dict) = 3;", dt))));
2417  ASSERT_EQ(g_num_rows,
2418  v<int64_t>(run_simple_agg(
2419  "SELECT COUNT(*) FROM test WHERE shared_dict ILIKE 'f%%';", dt)));
2420  ASSERT_EQ(g_num_rows,
2421  v<int64_t>(run_simple_agg(
2422  "SELECT COUNT(*) FROM test WHERE (shared_dict ILIKE 'f%%');", dt)));
2423  ASSERT_EQ(g_num_rows,
2424  v<int64_t>(run_simple_agg(
2425  "SELECT COUNT(*) FROM test WHERE ( shared_dict ILIKE 'f%%' );", dt)));
2426  ASSERT_EQ(
2427  0,
2428  v<int64_t>(run_simple_agg(
2429  "SELECT COUNT(*) FROM test WHERE shared_dict ILIKE 'McDonald''s';", dt)));
2430 
2431  ASSERT_EQ(
2432  "foo",
2433  boost::get<std::string>(v<NullableString>(run_simple_agg(
2434  "SELECT shared_dict FROM test WHERE REGEXP_LIKE(shared_dict, '^f.?.+');",
2435  dt))));
2436  ASSERT_EQ(
2437  "baz",
2438  boost::get<std::string>(v<NullableString>(run_simple_agg(
2439  "SELECT shared_dict FROM test WHERE REGEXP_LIKE(shared_dict, '^[a-z]+z$');",
2440  dt))));
2441 
2442  ASSERT_EQ(15,
2443  v<int64_t>(run_simple_agg(
2444  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '.*';", dt)));
2445  ASSERT_EQ(15,
2446  v<int64_t>(run_simple_agg(
2447  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '...';", dt)));
2448  ASSERT_EQ(15,
2449  v<int64_t>(run_simple_agg(
2450  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '.+.+.+';", dt)));
2451  ASSERT_EQ(15,
2452  v<int64_t>(run_simple_agg(
2453  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP '.?.?.?';", dt)));
2454 
2455  ASSERT_EQ(15,
2456  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict "
2457  "REGEXP 'ba.' or shared_dict REGEXP 'fo.';",
2458  dt)));
2459  ASSERT_EQ(15,
2460  v<int64_t>(run_simple_agg(
2461  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict, 'ba.') or "
2462  "shared_dict REGEXP 'fo.?';",
2463  dt)));
2464  ASSERT_EQ(
2465  15,
2466  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict REGEXP "
2467  "'ba.' or REGEXP_LIKE(shared_dict, 'fo.+');",
2468  dt)));
2469  ASSERT_EQ(5,
2470  v<int64_t>(run_simple_agg(
2471  "SELECT COUNT(*) FROM test WHERE shared_dict REGEXP 'ba.+';", dt)));
2472  ASSERT_EQ(
2473  5,
2474  v<int64_t>(run_simple_agg(
2475  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict, '.?ba.*');", dt)));
2476  ASSERT_EQ(15,
2477  v<int64_t>(run_simple_agg(
2478  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict,'ba.') or "
2479  "REGEXP_LIKE(shared_dict, 'fo.+');",
2480  dt)));
2481  ASSERT_EQ(
2482  15,
2483  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict REGEXP "
2484  "'ba.' or REGEXP_LIKE(shared_dict, 'fo.+');",
2485  dt)));
2486  ASSERT_EQ(15,
2487  v<int64_t>(run_simple_agg(
2488  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(shared_dict, 'ba.') or "
2489  "shared_dict REGEXP 'fo.?';",
2490  dt)));
2491  ASSERT_EQ(15,
2492  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE shared_dict "
2493  "REGEXP 'ba.' or shared_dict REGEXP 'fo.';",
2494  dt)));
2495  }
2496 }
#define SKIP_ON_AGGREGATOR(EXP)
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void run_ddl_statement(std::string ddl)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
+ Here is the call graph for this function:

◆ TEST() [35/183]

TEST ( Select  ,
StringCompare   
)

Definition at line 2498 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, GPU, run_multiple_agg(), and SKIP_NO_GPU.

2498  {
2500  SKIP_NO_GPU();
2501  c("SELECT COUNT(*) FROM test WHERE str = 'ba';", dt);
2502  c("SELECT COUNT(*) FROM test WHERE str <> 'ba';", dt);
2503 
2504  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'ba';", dt);
2505  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'bar';", dt);
2506  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'baf';", dt);
2507  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'baz';", dt);
2508  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'bbz';", dt);
2509  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'foo';", dt);
2510  c("SELECT COUNT(*) FROM test WHERE shared_dict < 'foon';", dt);
2511 
2512  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'ba';", dt);
2513  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'bar';", dt);
2514  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'baf';", dt);
2515  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'baz';", dt);
2516  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'bbz';", dt);
2517  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'foo';", dt);
2518  c("SELECT COUNT(*) FROM test WHERE shared_dict > 'foon';", dt);
2519 
2520  c("SELECT COUNT(*) FROM test WHERE real_str <= 'ba';", dt);
2521  c("SELECT COUNT(*) FROM test WHERE real_str <= 'bar';", dt);
2522  c("SELECT COUNT(*) FROM test WHERE real_str <= 'baf';", dt);
2523  c("SELECT COUNT(*) FROM test WHERE real_str <= 'baz';", dt);
2524  c("SELECT COUNT(*) FROM test WHERE real_str <= 'bbz';", dt);
2525  c("SELECT COUNT(*) FROM test WHERE real_str <= 'foo';", dt);
2526  c("SELECT COUNT(*) FROM test WHERE real_str <= 'foon';", dt);
2527 
2528  c("SELECT COUNT(*) FROM test WHERE real_str >= 'ba';", dt);
2529  c("SELECT COUNT(*) FROM test WHERE real_str >= 'bar';", dt);
2530  c("SELECT COUNT(*) FROM test WHERE real_str >= 'baf';", dt);
2531  c("SELECT COUNT(*) FROM test WHERE real_str >= 'baz';", dt);
2532  c("SELECT COUNT(*) FROM test WHERE real_str >= 'bbz';", dt);
2533  c("SELECT COUNT(*) FROM test WHERE real_str >= 'foo';", dt);
2534  c("SELECT COUNT(*) FROM test WHERE real_str >= 'foon';", dt);
2535 
2536  c("SELECT COUNT(*) FROM test WHERE real_str <= 'รครข';", dt);
2537 
2538  c("SELECT COUNT(*) FROM test WHERE 'ba' < shared_dict;", dt);
2539  c("SELECT COUNT(*) FROM test WHERE 'bar' < shared_dict;", dt);
2540  c("SELECT COUNT(*) FROM test WHERE 'ba' > shared_dict;", dt);
2541  c("SELECT COUNT(*) FROM test WHERE 'bar' > shared_dict;", dt);
2542 
2543  EXPECT_THROW(run_multiple_agg("SELECT COUNT(*) FROM test, test_inner WHERE "
2544  "test.shared_dict < test_inner.str",
2545  dt),
2546  std::runtime_error);
2547  }
2548 }
#define SKIP_NO_GPU()
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [36/183]

TEST ( Select  ,
StringsNoneEncoding   
)

Definition at line 2550 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::c(), CPU, anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), SKIP_NO_GPU, and SKIP_ON_AGGREGATOR.

2550  {
2552  SKIP_NO_GPU();
2553 
2554  c("SELECT COUNT(*) FROM test WHERE real_str LIKE 'real_%%%';", dt);
2555  c("SELECT COUNT(*) FROM test WHERE real_str LIKE 'real_ba%';", dt);
2556  c("SELECT COUNT(*) FROM test WHERE real_str LIKE '%eal_bar';", dt);
2557  c("SELECT * FROM test_lots_cols WHERE real_str LIKE '%' ORDER BY x0 ASC;", dt);
2558  c("SELECT * FROM test WHERE real_str LIKE '%' ORDER BY x ASC, y ASC;", dt);
2559  c("SELECT * FROM test WHERE real_str LIKE 'real_f%%' ORDER BY x ASC, y ASC;", dt);
2560  c("SELECT * FROM test WHERE real_str LIKE 'real_f%\%' ORDER BY x ASC, y ASC;", dt);
2561  c("SELECT * FROM test WHERE real_str LIKE 'real_@f%%' ESCAPE '@' ORDER BY x ASC, y "
2562  "ASC;",
2563  dt);
2564  c("SELECT COUNT(*) FROM test WHERE real_str LIKE 'real_ba_' or real_str LIKE "
2565  "'real_fo_';",
2566  dt);
2567  c("SELECT COUNT(*) FROM test WHERE real_str IS NULL;", dt);
2568  c("SELECT COUNT(*) FROM test WHERE real_str IS NOT NULL;", dt);
2569  c("SELECT COUNT(*) FROM test WHERE real_str > 'real_bar';", dt);
2570  c("SELECT COUNT(*) FROM test WHERE real_str > 'real_fo';", dt);
2571  c("SELECT COUNT(*) FROM test WHERE real_str >= 'real_bar';", dt);
2572  c("SELECT COUNT(*) FROM test WHERE 'real_bar' < real_str;", dt);
2573  c("SELECT COUNT(*) FROM test WHERE 'real_fo' < real_str;", 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_bar';", dt);
2578  c("SELECT COUNT(*) FROM test WHERE 'real_bar' <> real_str;", dt);
2579  c("SELECT COUNT(*) FROM test WHERE real_str = 'real_foo' OR real_str = 'real_bar';",
2580  dt);
2581  c("SELECT COUNT(*) FROM test WHERE real_str = real_str;", dt);
2582  c("SELECT COUNT(*) FROM test WHERE real_str <> real_str;", dt);
2583  ASSERT_EQ(g_num_rows,
2584  v<int64_t>(run_simple_agg(
2585  "SELECT COUNT(*) FROM test WHERE real_str ILIKE 'rEaL_f%%';", dt)));
2586  c("SELECT COUNT(*) FROM test WHERE LENGTH(real_str) = 8;", dt);
2587  ASSERT_EQ(2 * g_num_rows,
2588  v<int64_t>(run_simple_agg(
2589  "SELECT COUNT(*) FROM test WHERE CHAR_LENGTH(real_str) = 8;", dt)));
2590  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2591  2 * g_num_rows,
2592  v<int64_t>(run_simple_agg(
2593  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(real_str,'real_.*.*.*');",
2594  dt))));
2595  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2596  g_num_rows,
2597  v<int64_t>(run_simple_agg(
2598  "SELECT COUNT(*) FROM test WHERE real_str REGEXP 'real_ba.*';", dt))));
2600  ASSERT_EQ(2 * g_num_rows,
2601  v<int64_t>(run_simple_agg(
2602  "SELECT COUNT(*) FROM test WHERE real_str REGEXP '.*';", dt))));
2603  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2604  g_num_rows,
2605  v<int64_t>(run_simple_agg(
2606  "SELECT COUNT(*) FROM test WHERE real_str REGEXP 'real_f.*.*';", dt))));
2607  SKIP_ON_AGGREGATOR(ASSERT_EQ(
2608  0,
2609  v<int64_t>(run_simple_agg(
2610  "SELECT COUNT(*) FROM test WHERE real_str REGEXP 'real_f.+\%';", dt))));
2611  EXPECT_THROW(
2612  run_multiple_agg("SELECT COUNT(*) FROM test WHERE real_str LIKE str;", dt),
2613  std::runtime_error);
2614  EXPECT_THROW(run_multiple_agg(
2615  "SELECT COUNT(*) FROM test WHERE REGEXP_LIKE(real_str, str);", dt),
2616  std::runtime_error);
2617  }
2618 }
#define SKIP_ON_AGGREGATOR(EXP)
#define SKIP_NO_GPU()
TargetValue run_simple_agg(const string &query_str, const ExecutorDeviceType device_type, const bool geo_return_geo_tv=true, const bool allow_loop_joins=true)
void c(const std::string &query_string, const std::string &sqlite_query_string, const ExecutorDeviceType device_type)
TQueryResult run_multiple_agg(std::string sql)
+ Here is the call graph for this function:

◆ TEST() [37/183]

TEST ( Select  ,
Time   
)

Definition at line 2684 of file ExecuteTest.cpp.

References anonymous_namespace{ExecuteTest.cpp}::check_date_trunc_groups(), anonymous_namespace{ExecuteTest.cpp}::check_one_date_trunc_group(), anonymous_namespace{ExecuteTest.cpp}::check_one_date_trunc_group_with_agg(), CPU, anonymous_namespace{ExecuteTest.cpp}::cta(), anonymous_namespace{ExecuteTest.cpp}::g_num_rows, GPU, anonymous_namespace{UpdateMetadataTest.cpp}::query, run_multiple_agg(), anonymous_namespace{ExecuteTest.cpp}::run_simple_agg(), and SKIP_NO_GPU.

2684  {
2686  SKIP_NO_GPU();
2687  // check DATE Formats
2688  ASSERT_EQ(
2689  g_num_rows + g_num_rows / 2,
2690  v<int64_t>(run_simple_agg(
2691  "SELECT COUNT(*) FROM test WHERE CAST('1999-09-10' AS DATE) > o;", dt)));
2692  ASSERT_EQ(
2693  g_num_rows + g_num_rows / 2,
2694  v<int64_t>(run_simple_agg(
2695  "SELECT COUNT(*) FROM test WHERE CAST('10/09/1999' AS DATE) > o;", dt)));
2696  ASSERT_EQ(g_num_rows + g_num_rows / 2,
2697  v<int64_t>(run_simple_agg(
2698  "SELECT COUNT(*) FROM test WHERE CAST('10-Sep-99' AS DATE) > o;", dt)));
2699  ASSERT_EQ(
2700  g_num_rows + g_num_rows / 2,
2701  v<int64_t>(run_simple_agg(
2702  "SELECT COUNT(*) FROM test WHERE CAST('31/Oct/2013' AS DATE) > o;", dt)));
2703  // check TIME FORMATS
2704  ASSERT_EQ(2 * g_num_rows,
2705  v<int64_t>(run_simple_agg(
2706  "SELECT COUNT(*) FROM test WHERE CAST('15:13:15' AS TIME) > n;", dt)));
2707  ASSERT_EQ(2 * g_num_rows,
2708  v<int64_t>(run_simple_agg(
2709  "SELECT COUNT(*) FROM test WHERE CAST('151315' AS TIME) > n;", dt)));
2710 
2711  ASSERT_EQ(
2712  g_num_rows + g_num_rows / 2,
2713  v<int64_t>(run_simple_agg(
2714  "SELECT COUNT(*) FROM test WHERE CAST('1999-09-10' AS DATE) > o;", dt)));
2715  ASSERT_EQ(
2716  0,
2717  v<int64_t>(run_simple_agg(
2718  "SELECT COUNT(*) FROM test WHERE CAST('1999-09-10' AS DATE) <= o;", dt)));
2719  ASSERT_EQ(2 * g_num_rows,
2720  v<int64_t>(run_simple_agg(
2721  "SELECT COUNT(*) FROM test WHERE CAST('15:13:15' AS TIME) > n;", dt)));
2722  ASSERT_EQ(0,
2723  v<int64_t>(run_simple_agg(
2724  "SELECT COUNT(*) FROM test WHERE CAST('15:13:15' AS TIME) <= n;", dt)));
2725  cta("SELECT DATETIME('NOW') FROM test limit 1;", dt);
2726  // these next tests work because all dates are before now 2015-12-8 17:00:00
2727  ASSERT_EQ(
2728  2 * g_num_rows,
2729  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m < NOW();", dt)));
2730  ASSERT_EQ(2 * g_num_rows,
2731  v<int64_t>(run_simple_agg(
2732  "SELECT COUNT(*) FROM test WHERE m > timestamp(0) '2014-12-13T000000';",
2733  dt)));
2734  ASSERT_EQ(g_num_rows + g_num_rows / 2,
2735  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE CAST(o AS "
2736  "TIMESTAMP) > timestamp(0) '1999-09-08T160000';",
2737  dt)));
2738  ASSERT_EQ(0,
2739  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE CAST(o AS "
2740  "TIMESTAMP) > timestamp(0) '1999-09-10T160000';",
2741  dt)));
2742  ASSERT_EQ(14185957950L,
2743  v<int64_t>(run_simple_agg(
2744  "SELECT MAX(EXTRACT(EPOCH FROM m) * 10) FROM test;", dt)));
2745  ASSERT_EQ(20140,
2746  v<int64_t>(run_simple_agg(
2747  "SELECT MAX(EXTRACT(YEAR FROM m) * 10) FROM test;", dt)));
2748  ASSERT_EQ(120,
2749  v<int64_t>(run_simple_agg(
2750  "SELECT MAX(EXTRACT(MONTH FROM m) * 10) FROM test;", dt)));
2751  ASSERT_EQ(140,
2752  v<int64_t>(
2753  run_simple_agg("SELECT MAX(EXTRACT(DAY FROM m) * 10) FROM test;", dt)));
2754  ASSERT_EQ(
2755  22,
2756  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(HOUR FROM m)) FROM test;", dt)));
2757  ASSERT_EQ(
2758  23,
2759  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(MINUTE FROM m)) FROM test;", dt)));
2760  ASSERT_EQ(
2761  15,
2762  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(SECOND FROM m)) FROM test;", dt)));
2763  ASSERT_EQ(
2764  6, v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(DOW FROM m)) FROM test;", dt)));
2765  ASSERT_EQ(
2766  348,
2767  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(DOY FROM m)) FROM test;", dt)));
2768  ASSERT_EQ(
2769  15,
2770  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(HOUR FROM n)) FROM test;", dt)));
2771  ASSERT_EQ(
2772  13,
2773  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(MINUTE FROM n)) FROM test;", dt)));
2774  ASSERT_EQ(
2775  14,
2776  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(SECOND FROM n)) FROM test;", dt)));
2777  ASSERT_EQ(
2778  1999,
2779  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(YEAR FROM o)) FROM test;", dt)));
2780  ASSERT_EQ(
2781  9,
2782  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(MONTH FROM o)) FROM test;", dt)));
2783  ASSERT_EQ(
2784  9, v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(DAY FROM o)) FROM test;", dt)));
2785  ASSERT_EQ(4,
2786  v<int64_t>(run_simple_agg(
2787  "SELECT EXTRACT(DOW FROM o) FROM test WHERE o IS NOT NULL;", dt)));
2788  ASSERT_EQ(252,
2789  v<int64_t>(run_simple_agg(
2790  "SELECT EXTRACT(DOY FROM o) FROM test WHERE o IS NOT NULL;", dt)));
2791  ASSERT_EQ(
2792  936835200L,
2793  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(EPOCH FROM o)) FROM test;", dt)));
2794  ASSERT_EQ(1L,
2795  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(WEEK FROM CAST('2012-01-01 "
2796  "20:15:12' AS TIMESTAMP))) FROM test limit 1;",
2797  dt)));
2798  ASSERT_EQ(10L,
2799  v<int64_t>(run_simple_agg("SELECT MAX(EXTRACT(WEEK FROM CAST('2008-03-03 "
2800  "20:15:12' AS TIMESTAMP))) FROM test limit 1;",
2801  dt)));
2802  // Monday
2803  ASSERT_EQ(1L,
2804  v<int64_t>(run_simple_agg("SELECT EXTRACT(DOW FROM CAST('2008-03-03 "
2805  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2806  dt)));
2807  // Monday
2808  ASSERT_EQ(1L,
2809  v<int64_t>(run_simple_agg("SELECT EXTRACT(ISODOW FROM CAST('2008-03-03 "
2810  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2811  dt)));
2812  // Sunday
2813  ASSERT_EQ(0L,
2814  v<int64_t>(run_simple_agg("SELECT EXTRACT(DOW FROM CAST('2008-03-02 "
2815  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2816  dt)));
2817  // Sunday
2818  ASSERT_EQ(7L,
2819  v<int64_t>(run_simple_agg("SELECT EXTRACT(ISODOW FROM CAST('2008-03-02 "
2820  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2821  dt)));
2822  ASSERT_EQ(15000000000L,
2823  v<int64_t>(run_simple_agg(
2824  "SELECT EXTRACT(nanosecond from m) FROM test limit 1;", dt)));
2825  ASSERT_EQ(15000000L,
2826  v<int64_t>(run_simple_agg(
2827  "SELECT EXTRACT(microsecond from m) FROM test limit 1;", dt)));
2828  ASSERT_EQ(15000L,
2829  v<int64_t>(run_simple_agg(
2830  "SELECT EXTRACT(millisecond from m) FROM test limit 1;", dt)));
2831  ASSERT_EQ(56000000000L,
2832  v<int64_t>(run_simple_agg("SELECT EXTRACT(nanosecond from TIMESTAMP(0) "
2833  "'1999-03-14 23:34:56') FROM test limit 1;",
2834  dt)));
2835  ASSERT_EQ(56000000L,
2836  v<int64_t>(run_simple_agg("SELECT EXTRACT(microsecond from TIMESTAMP(0) "
2837  "'1999-03-14 23:34:56') FROM test limit 1;",
2838  dt)));
2839  ASSERT_EQ(56000L,
2840  v<int64_t>(run_simple_agg("SELECT EXTRACT(millisecond from TIMESTAMP(0) "
2841  "'1999-03-14 23:34:56') FROM test limit 1;",
2842  dt)));
2843  ASSERT_EQ(2005,
2844  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '2005-12-31 "
2845  "23:59:59') from test limit 1;",
2846  dt)));
2847  ASSERT_EQ(1997,
2848  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '1997-01-01 "
2849  "23:59:59') from test limit 1;",
2850  dt)));
2851  ASSERT_EQ(2006,
2852  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '2006-01-01 "
2853  "00:0:00') from test limit 1;",
2854  dt)));
2855  ASSERT_EQ(2014,
2856  v<int64_t>(run_simple_agg("select EXTRACT(year from TIMESTAMP '2014-01-01 "
2857  "00:00:00') from test limit 1;",
2858  dt)));
2859 
2860  // do some DATE_TRUNC tests
2861  /*
2862  * year
2863  * month
2864  * day
2865  * hour
2866  * minute
2867  * second
2868  *
2869  * millennium
2870  * century
2871  * decade
2872  * milliseconds
2873  * microseconds
2874  * week
2875  */
2876  ASSERT_EQ(1325376000L,
2877  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(year, CAST('2012-05-08 "
2878  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2879  dt)));
2880  ASSERT_EQ(1335830400L,
2881  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(month, CAST('2012-05-08 "
2882  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2883  dt)));
2884  ASSERT_EQ(1336435200L,
2885  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(day, CAST('2012-05-08 "
2886  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2887  dt)));
2888  ASSERT_EQ(1336507200L,
2889  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(hour, CAST('2012-05-08 "
2890  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2891  dt)));
2892  ASSERT_EQ(1336508112L,
2893  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(second, CAST('2012-05-08 "
2894  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2895  dt)));
2896  ASSERT_EQ(978307200L,
2897  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millennium, CAST('2012-05-08 "
2898  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2899  dt)));
2900  ASSERT_EQ(978307200L,
2901  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(century, CAST('2012-05-08 "
2902  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2903  dt)));
2904  ASSERT_EQ(1293840000L,
2905  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('2012-05-08 "
2906  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2907  dt)));
2908  ASSERT_EQ(1336508112L,
2909  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millisecond, CAST('2012-05-08 "
2910  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2911  dt)));
2912  ASSERT_EQ(1336508112L,
2913  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(microsecond, CAST('2012-05-08 "
2914  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2915  dt)));
2916  ASSERT_EQ(1336262400L,
2917  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(week, CAST('2012-05-08 "
2918  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2919  dt)));
2920 
2921  ASSERT_EQ(-2114380800L,
2922  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(year, CAST('1903-05-08 "
2923  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2924  dt)));
2925  ASSERT_EQ(-2104012800L,
2926  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(month, CAST('1903-05-08 "
2927  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2928  dt)));
2929  ASSERT_EQ(-2103408000L,
2930  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(day, CAST('1903-05-08 "
2931  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2932  dt)));
2933  ASSERT_EQ(-2103336000L,
2934  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(hour, CAST('1903-05-08 "
2935  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2936  dt)));
2937  ASSERT_EQ(-2103335088L,
2938  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(second, CAST('1903-05-08 "
2939  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2940  dt)));
2941  ASSERT_EQ(-30578688000L,
2942  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millennium, CAST('1903-05-08 "
2943  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2944  dt)));
2945  ASSERT_EQ(-2177452800L,
2946  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(century, CAST('1903-05-08 "
2947  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2948  dt)));
2949  ASSERT_EQ(-2177452800L,
2950  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('1903-05-08 "
2951  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2952  dt)));
2953  ASSERT_EQ(-2103335088L,
2954  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(millisecond, CAST('1903-05-08 "
2955  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2956  dt)));
2957  ASSERT_EQ(-2103335088L,
2958  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(microsecond, CAST('1903-05-08 "
2959  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2960  dt)));
2961  ASSERT_EQ(-2103840000L,
2962  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(week, CAST('1903-05-08 "
2963  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2964  dt)));
2965 
2966  ASSERT_EQ(31536000L,
2967  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('1972-05-08 "
2968  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2969  dt)));
2970  ASSERT_EQ(662688000L,
2971  v<int64_t>(run_simple_agg("SELECT DATE_TRUNC(decade, CAST('2000-05-08 "
2972  "20:15:12' AS TIMESTAMP)) FROM test limit 1;",
2973  dt)));
2974  // test QUARTER
2975  ASSERT_EQ(4,
2976  v<int64_t>(run_simple_agg("select EXTRACT(quarter FROM CAST('2008-11-27 "
2977  "12:12:12' AS timestamp)) FROM test limit 1;",
2978  dt)));
2979  ASSERT_EQ(1,
2980  v<int64_t>(run_simple_agg("select EXTRACT(quarter FROM CAST('2008-03-21 "
2981  "12:12:12' AS timestamp)) FROM test limit 1;",
2982  dt)));
2983  ASSERT_EQ(1199145600L,
2984  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2008-03-21 "
2985  "12:12:12' AS timestamp)) FROM test limit 1;",
2986  dt)));
2987  ASSERT_EQ(1230768000L,
2988  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2009-03-21 "
2989  "12:12:12' AS timestamp)) FROM test limit 1;",
2990  dt)));
2991  ASSERT_EQ(1254355200L,
2992  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2009-11-21 "
2993  "12:12:12' AS timestamp)) FROM test limit 1;",
2994  dt)));
2995  ASSERT_EQ(946684800L,
2996  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('2000-03-21 "
2997  "12:12:12' AS timestamp)) FROM test limit 1;",
2998  dt)));
2999  ASSERT_EQ(-2208988800L,
3000  v<int64_t>(run_simple_agg("select DATE_TRUNC(quarter, CAST('1900-03-21 "
3001  "12:12:12' AS timestamp)) FROM test limit 1;",
3002  dt)));
3003  // test DATE format processing
3004  ASSERT_EQ(1434844800L,
3005  v<int64_t>(run_simple_agg(
3006  "select CAST('2015-06-21' AS DATE) FROM test limit 1;", dt)));
3007  ASSERT_EQ(
3008  g_num_rows + g_num_rows / 2,
3009  v<int64_t>(run_simple_agg(
3010  "SELECT COUNT(*) FROM test WHERE o < CAST('06/21/2015' AS DATE);", dt)));
3011  ASSERT_EQ(g_num_rows + g_num_rows / 2,
3012  v<int64_t>(run_simple_agg(
3013  "SELECT COUNT(*) FROM test WHERE o < CAST('21-Jun-15' AS DATE);", dt)));
3014  ASSERT_EQ(
3015  g_num_rows + g_num_rows / 2,
3016  v<int64_t>(run_simple_agg(
3017  "SELECT COUNT(*) FROM test WHERE o < CAST('21/Jun/2015' AS DATE);", dt)));
3018  ASSERT_EQ(
3019  g_num_rows + g_num_rows / 2,
3020  v<int64_t>(run_simple_agg(
3021  "SELECT COUNT(*) FROM test WHERE o < CAST('1434844800' AS DATE);", dt)));
3022 
3023  // test different input formats
3024  // added new format for customer
3025  ASSERT_EQ(
3026  1434896116L,
3027  v<int64_t>(run_simple_agg(
3028  "select CAST('2015-06-21 14:15:16' AS timestamp) FROM test limit 1;", dt)));
3029  ASSERT_EQ(2 * g_num_rows,
3030  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3031  "CAST('2015-06-21:141516' AS TIMESTAMP);",
3032  dt)));
3033  ASSERT_EQ(
3034  2 * g_num_rows,
3035  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= CAST('21-JUN-15 "
3036  "2.15.16.12345 PM' AS TIMESTAMP);",
3037  dt)));
3038  ASSERT_EQ(
3039  2 * g_num_rows,
3040  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= CAST('21-JUN-15 "
3041  "2.15.16.12345 AM' AS TIMESTAMP);",
3042  dt)));
3043  ASSERT_EQ(2 * g_num_rows,
3044  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3045  "CAST('21-JUN-15 2:15:16 AM' AS TIMESTAMP);",
3046  dt)));
3047 
3048  ASSERT_EQ(2 * g_num_rows,
3049  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3050  "CAST('06/21/2015 14:15:16' AS TIMESTAMP);",
3051  dt)));
3052 
3053  // Support ISO date offset format
3054  ASSERT_EQ(
3055  2 * g_num_rows,
3056  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3057  "CAST('21/Aug/2015:12:13:14 -0600' AS TIMESTAMP);",
3058  dt)));
3059  ASSERT_EQ(2 * g_num_rows,
3060  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3061  "CAST('2015-08-21T12:13:14 -0600' AS TIMESTAMP);",
3062  dt)));
3063  ASSERT_EQ(2 * g_num_rows,
3064  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3065  "CAST('21-Aug-15 12:13:14 -0600' AS TIMESTAMP);",
3066  dt)));
3067  ASSERT_EQ(
3068  2 * g_num_rows,
3069  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3070  "CAST('21/Aug/2015:13:13:14 -0500' AS TIMESTAMP);",
3071  dt)));
3072  ASSERT_EQ(2 * g_num_rows,
3073  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test WHERE m <= "
3074  "CAST('2015-08-21T18:13:14' AS TIMESTAMP);",
3075  dt)));
3076  // add test for quarterday behaviour
3077  ASSERT_EQ(1L,
3078  v<int64_t>(run_simple_agg(
3079  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T04:23:11' AS "
3080  "timestamp)) FROM test limit 1;",
3081  dt)));
3082  ASSERT_EQ(1L,
3083  v<int64_t>(run_simple_agg(
3084  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T00:00:00' AS "
3085  "timestamp)) FROM test limit 1;",
3086  dt)));
3087  ASSERT_EQ(2L,
3088  v<int64_t>(run_simple_agg(
3089  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T08:23:11' AS "
3090  "timestamp)) FROM test limit 1;",
3091  dt)));
3092  ASSERT_EQ(3L,
3093  v<int64_t>(run_simple_agg(
3094  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T14:23:11' AS "
3095  "timestamp)) FROM test limit 1;",
3096  dt)));
3097  ASSERT_EQ(4L,
3098  v<int64_t>(run_simple_agg(
3099  "select EXTRACT (QUARTERDAY FROM CAST('2015-08-21T23:23:11' AS "
3100  "timestamp)) FROM test limit 1;",
3101  dt)));
3102  ASSERT_EQ(1440115200L,
3103  v<int64_t>(run_simple_agg(
3104  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T04:23:11' AS "
3105  "timestamp)) FROM test limit 1;",
3106  dt)));
3107  ASSERT_EQ(1440136800L,
3108  v<int64_t>(run_simple_agg(
3109  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T08:23:11' AS "
3110  "timestamp)) FROM test limit 1;",
3111  dt)));
3112  ASSERT_EQ(1440158400L,
3113  v<int64_t>(run_simple_agg(
3114  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T13:23:11' AS "
3115  "timestamp)) FROM test limit 1;",
3116  dt)));
3117  ASSERT_EQ(1440180000L,
3118  v<int64_t>(run_simple_agg(
3119  "select DATE_TRUNC (QUARTERDAY, CAST('2015-08-21T23:59:59' AS "
3120  "timestamp)) FROM test limit 1;",
3121  dt)));
3122  ASSERT_EQ(2007,
3123  v<int64_t>(run_simple_agg("SELECT DATEPART('year', CAST('2007-10-30 "
3124  "12:15:32' AS TIMESTAMP)) FROM test;",
3125  dt)));
3126  ASSERT_EQ(2007,
3127  v<int64_t>(run_simple_agg("SELECT DATEPART('yyyy', CAST('2007-10-30 "
3128  "12:15:32' AS TIMESTAMP)) FROM test;",
3129  dt)));
3130  ASSERT_EQ(
3131  2007,
3132  v<int64_t>(run_simple_agg(
3133  "SELECT DATEPART('yy', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3134  dt)));
3135  ASSERT_EQ(4,
3136  v<int64_t>(run_simple_agg("SELECT DATEPART('quarter', CAST('2007-10-30 "
3137  "12:15:32' AS TIMESTAMP)) FROM test;",
3138  dt)));
3139  ASSERT_EQ(
3140  4,
3141  v<int64_t>(run_simple_agg(
3142  "SELECT DATEPART('qq', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3143  dt)));
3144  ASSERT_EQ(
3145  4,
3146  v<int64_t>(run_simple_agg(
3147  "SELECT DATEPART('q', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3148  dt)));
3149  ASSERT_EQ(10,
3150  v<int64_t>(run_simple_agg("SELECT DATEPART('month', CAST('2007-10-30 "
3151  "12:15:32' AS TIMESTAMP)) FROM test;",
3152  dt)));
3153  ASSERT_EQ(
3154  10,
3155  v<int64_t>(run_simple_agg(
3156  "SELECT DATEPART('mm', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3157  dt)));
3158  ASSERT_EQ(
3159  10,
3160  v<int64_t>(run_simple_agg(
3161  "SELECT DATEPART('m', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3162  dt)));
3163  ASSERT_EQ(303,
3164  v<int64_t>(run_simple_agg("SELECT DATEPART('dayofyear', CAST('2007-10-30 "
3165  "12:15:32' AS TIMESTAMP)) FROM test;",
3166  dt)));
3167  ASSERT_EQ(
3168  303,
3169  v<int64_t>(run_simple_agg(
3170  "SELECT DATEPART('dy', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3171  dt)));
3172  ASSERT_EQ(
3173  303,
3174  v<int64_t>(run_simple_agg(
3175  "SELECT DATEPART('y', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3176  dt)));
3177  ASSERT_EQ(
3178  30,
3179  v<int64_t>(run_simple_agg(
3180  "SELECT DATEPART('day', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3181  dt)));
3182  ASSERT_EQ(
3183  30,
3184  v<int64_t>(run_simple_agg(
3185  "SELECT DATEPART('dd', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3186  dt)));
3187  ASSERT_EQ(
3188  30,
3189  v<int64_t>(run_simple_agg(
3190  "SELECT DATEPART('d', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3191  dt)));
3192  ASSERT_EQ(12,
3193  v<int64_t>(run_simple_agg("SELECT DATEPART('hour', CAST('2007-10-30 "
3194  "12:15:32' AS TIMESTAMP)) FROM test;",
3195  dt)));
3196  ASSERT_EQ(
3197  12,
3198  v<int64_t>(run_simple_agg(
3199  "SELECT DATEPART('hh', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3200  dt)));
3201  ASSERT_EQ(15,
3202  v<int64_t>(run_simple_agg("SELECT DATEPART('minute', CAST('2007-10-30 "
3203  "12:15:32' AS TIMESTAMP)) FROM test;",
3204  dt)));
3205  ASSERT_EQ(
3206  15,
3207  v<int64_t>(run_simple_agg(
3208  "SELECT DATEPART('mi', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3209  dt)));
3210  ASSERT_EQ(
3211  15,
3212  v<int64_t>(run_simple_agg(
3213  "SELECT DATEPART('n', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3214  dt)));
3215  ASSERT_EQ(32,
3216  v<int64_t>(run_simple_agg("SELECT DATEPART('second', CAST('2007-10-30 "
3217  "12:15:32' AS TIMESTAMP)) FROM test;",
3218  dt)));
3219  ASSERT_EQ(
3220  32,
3221  v<int64_t>(run_simple_agg(
3222  "SELECT DATEPART('ss', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3223  dt)));
3224  ASSERT_EQ(
3225  32,
3226  v<int64_t>(run_simple_agg(
3227  "SELECT DATEPART('s', CAST('2007-10-30 12:15:32' AS TIMESTAMP)) FROM test;",
3228  dt)));
3229  ASSERT_EQ(
3230  32,
3231  v<int64_t>(run_simple_agg(
3232  "SELECT DATEPART('s', TIMESTAMP '2007-10-30 12:15:32') FROM test;", dt)));
3233  ASSERT_EQ(
3234  3,
3235  v<int64_t>(run_simple_agg("SELECT DATEDIFF('year', CAST('2006-01-07 00:00:00' as "
3236  "TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3237  "TIMESTAMP)) FROM TEST LIMIT 1;",
3238  dt)));
3239  ASSERT_EQ(
3240  36,
3241  v<int64_t>(run_simple_agg("SELECT DATEDIFF('month', CAST('2006-01-07 00:00:00' "
3242  "as TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3243  "TIMESTAMP)) FROM TEST LIMIT 1;",
3244  dt)));
3245  ASSERT_EQ(
3246  1096,
3247  v<int64_t>(run_simple_agg("SELECT DATEDIFF('day', CAST('2006-01-07 00:00:00' as "
3248  "TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3249  "TIMESTAMP)) FROM TEST LIMIT 1;",
3250  dt)));
3251  ASSERT_EQ(
3252  12,
3253  v<int64_t>(run_simple_agg("SELECT DATEDIFF('quarter', CAST('2006-01-07 00:00:00' "
3254  "as TIMESTAMP), CAST('2009-01-07 00:00:00' AS "
3255  "TIMESTAMP)) FROM TEST LIMIT 1;",
3256  dt)));
3257  ASSERT_EQ(1,
3258  v<int64_t>(run_simple_agg("SELECT DATEDIFF('day', DATE '2009-2-28', DATE "
3259  "'2009-03-01') FROM TEST LIMIT 1;",
3260  dt)));
3261  ASSERT_EQ(2,
3262  v<int64_t>(run_simple_agg("SELECT DATEDIFF('day', DATE '2008-2-28', DATE "
3263  "'2008-03-01') FROM TEST LIMIT 1;",
3264  dt)));
3265  ASSERT_EQ(-425,
3266  v<int64_t>(run_simple_agg("select DATEDIFF('day', DATE '1971-03-02', DATE "
3267  "'1970-01-01') from test limit 1;",
3268  dt)));
3269  ASSERT_EQ(
3270  1,
3271  v<int64_t>(run_simple_agg(
3272  "SELECT DATEDIFF('day', o, o + INTERVAL '1' DAY) FROM TEST LIMIT 1;", dt)));
3273  ASSERT_EQ(15,
3274  v<int64_t>(run_simple_agg("SELECT count(*) from test where DATEDIFF('day', "
3275  "CAST (m AS DATE), o) < -5570;",
3276  dt)));
3277  ASSERT_EQ(1,
3278  v<int64_t>(run_simple_agg("SELECT DATEDIFF('second', m, TIMESTAMP(0) "
3279  "'2014-12-13 22:23:16') FROM test limit 1;",
3280  dt)));
3281  ASSERT_EQ(1000,
3282  v<int64_t>(run_simple_agg("SELECT DATEDIFF('millisecond', m, TIMESTAMP(0) "
3283  "'2014-12-13 22:23:16') FROM test limit 1;",
3284  dt)));
3285  ASSERT_EQ(44000000,
3286  v<int64_t>(run_simple_agg("SELECT DATEDIFF('microsecond', m, TIMESTAMP(0) "
3287  "'2014-12-13 22:23:59') FROM test limit 1;",
3288  dt)));
3289  ASSERT_EQ(34000000000,
3290  v<int64_t>(run_simple_agg("SELECT DATEDIFF('nanosecond', m, TIMESTAMP(0) "
3291  "'2014-12-13 22:23:49') FROM test limit 1;",
3292  dt)));
3293  ASSERT_EQ(-1000,
3294  v<int64_t>(run_simple_agg("SELECT DATEDIFF('millisecond', TIMESTAMP(0) "
3295  "'2014-12-13 22:23:16', m) FROM test limit 1;",
3296  dt)));
3297  ASSERT_EQ(-44000000,
3298  v<int64_t>(run_simple_agg("SELECT DATEDIFF('microsecond', TIMESTAMP(0) "
3299  "'2014-12-13 22:23:59', m) FROM test limit 1;",
3300  dt)));
3301  ASSERT_EQ(-34000000000,
3302  v<int64_t>(run_simple_agg("SELECT DATEDIFF('nanosecond', TIMESTAMP(0) "
3303  "'2014-12-13 22:23:49', m) FROM test limit 1;",
3304  dt)));
3305  // DATEADD tests
3306  ASSERT_EQ(
3307  1,
3308  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, CAST('2017-05-31' AS DATE)) "
3309  "= TIMESTAMP '2017-06-01 0:00:00' from test limit 1;",
3310  dt)));
3311  ASSERT_EQ(
3312  1,
3313  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 2, DATE '2017-05-31') = "
3314  "TIMESTAMP '2017-06-02 0:00:00' from test limit 1;",
3315  dt)));
3316  ASSERT_EQ(
3317  1,
3318  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -1, CAST('2017-05-31' AS DATE)) "
3319  "= TIMESTAMP '2017-05-30 0:00:00' from test limit 1;",
3320  dt)));
3321  ASSERT_EQ(
3322  1,
3323  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -2, DATE '2017-05-31') = "
3324  "TIMESTAMP '2017-05-29 0:00:00' from test limit 1;",
3325  dt)));
3326  ASSERT_EQ(1,
3327  v<int64_t>(run_simple_agg(
3328  "SELECT DATEADD('hour', 1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3329  "'2017-05-31 2:11:11' from test limit 1;",
3330  dt)));
3331  ASSERT_EQ(
3332  1,
3333  v<int64_t>(run_simple_agg(
3334  "SELECT DATEADD('hour', 10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3335  "'2017-05-31 11:11:11' from test limit 1;",
3336  dt)));
3337  ASSERT_EQ(
3338  1,
3339  v<int64_t>(run_simple_agg(
3340  "SELECT DATEADD('hour', -1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3341  "'2017-05-31 0:11:11' from test limit 1;",
3342  dt)));
3343  ASSERT_EQ(
3344  1,
3345  v<int64_t>(run_simple_agg(
3346  "SELECT DATEADD('hour', -10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3347  "'2017-05-30 15:11:11' from test limit 1;",
3348  dt)));
3349  ASSERT_EQ(
3350  1,
3351  v<int64_t>(run_simple_agg(
3352  "SELECT DATEADD('minute', 1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3353  "'2017-05-31 1:12:11' from test limit 1;",
3354  dt)));
3355  ASSERT_EQ(
3356  1,
3357  v<int64_t>(run_simple_agg(
3358  "SELECT DATEADD('minute', 10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3359  "'2017-05-31 1:21:11' from test limit 1;",
3360  dt)));
3361  ASSERT_EQ(
3362  1,
3363  v<int64_t>(run_simple_agg(
3364  "SELECT DATEADD('minute', -1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3365  "'2017-05-31 1:10:11' from test limit 1;",
3366  dt)));
3367  ASSERT_EQ(
3368  1,
3369  v<int64_t>(run_simple_agg(
3370  "SELECT DATEADD('minute', -10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3371  "'2017-05-31 1:01:11' from test limit 1;",
3372  dt)));
3373  ASSERT_EQ(
3374  1,
3375  v<int64_t>(run_simple_agg(
3376  "SELECT DATEADD('second', 1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3377  "'2017-05-31 1:11:12' from test limit 1;",
3378  dt)));
3379  ASSERT_EQ(
3380  1,
3381  v<int64_t>(run_simple_agg(
3382  "SELECT DATEADD('second', 10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3383  "'2017-05-31 1:11:21' from test limit 1;",
3384  dt)));
3385  ASSERT_EQ(
3386  1,
3387  v<int64_t>(run_simple_agg(
3388  "SELECT DATEADD('second', -1, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3389  "'2017-05-31 1:11:10' from test limit 1;",
3390  dt)));
3391  ASSERT_EQ(
3392  1,
3393  v<int64_t>(run_simple_agg(
3394  "SELECT DATEADD('second', -10, TIMESTAMP '2017-05-31 1:11:11') = TIMESTAMP "
3395  "'2017-05-31 1:11:01' from test limit 1;",
3396  dt)));
3397 
3398  ASSERT_EQ(1,
3399  v<int64_t>(run_simple_agg(
3400  "SELECT DATEADD('month', 1, DATE '2017-01-10') = TIMESTAMP "
3401  "'2017-02-10 0:00:00' from test limit 1;",
3402  dt)));
3403  ASSERT_EQ(1,
3404  v<int64_t>(run_simple_agg(
3405  "SELECT DATEADD('month', 10, DATE '2017-01-10') = TIMESTAMP "
3406  "'2017-11-10 0:00:00' from test limit 1;",
3407  dt)));
3408  ASSERT_EQ(1,
3409  v<int64_t>(run_simple_agg(
3410  "SELECT DATEADD('month', 1, DATE '2009-01-30') = TIMESTAMP "
3411  "'2009-02-28 0:00:00' from test limit 1;",
3412  dt)));
3413  ASSERT_EQ(1,
3414  v<int64_t>(run_simple_agg(
3415  "SELECT DATEADD('month', 1, DATE '2008-01-30') = TIMESTAMP "
3416  "'2008-02-29 0:00:00' from test limit 1;",
3417  dt)));
3418  ASSERT_EQ(
3419  1,
3420  v<int64_t>(run_simple_agg(
3421  "SELECT DATEADD('month', 1, TIMESTAMP '2009-01-30 1:11:11') = TIMESTAMP "
3422  "'2009-02-28 1:11:11' from test limit 1;",
3423  dt)));
3424  ASSERT_EQ(
3425  1,
3426  v<int64_t>(run_simple_agg(
3427  "SELECT DATEADD('month', -1, TIMESTAMP '2009-03-30 1:11:11') = TIMESTAMP "
3428  "'2009-02-28 1:11:11' from test limit 1;",
3429  dt)));
3430  ASSERT_EQ(
3431  1,
3432  v<int64_t>(run_simple_agg(
3433  "SELECT DATEADD('month', -4, TIMESTAMP '2009-03-30 1:11:11') = TIMESTAMP "
3434  "'2008-11-30 1:11:11' from test limit 1;",
3435  dt)));
3436  ASSERT_EQ(
3437  1,
3438  v<int64_t>(run_simple_agg(
3439  "SELECT DATEADD('month', 5, TIMESTAMP '2009-01-31 1:11:11') = TIMESTAMP "
3440  "'2009-6-30 1:11:11' from test limit 1;",
3441  dt)));
3442  ASSERT_EQ(1,
3443  v<int64_t>(run_simple_agg(
3444  "SELECT DATEADD('year', 1, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3445  "'2009-02-28 1:11:11' from test limit 1;",
3446  dt)));
3447  ASSERT_EQ(
3448  1,
3449  v<int64_t>(run_simple_agg(
3450  "SELECT TIMESTAMPADD(YEAR, 1, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3451  "'2009-02-28 1:11:11' from test limit 1;",
3452  dt)));
3453  ASSERT_EQ(
3454  1,
3455  v<int64_t>(run_simple_agg(
3456  "SELECT TIMESTAMPADD(YEAR, -8, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3457  "'2000-02-29 1:11:11' from test limit 1;",
3458  dt)));
3459  ASSERT_EQ(
3460  1,
3461  v<int64_t>(run_simple_agg(
3462  "SELECT TIMESTAMPADD(YEAR, -8, TIMESTAMP '2008-02-29 1:11:11') = TIMESTAMP "
3463  "'2000-02-29 1:11:11' from test limit 1;",
3464  dt)));
3465 
3466  ASSERT_EQ(1,
3467  v<int64_t>(run_simple_agg(
3468  "SELECT m = TIMESTAMP '2014-12-13 22:23:15' from test limit 1;", dt)));
3469  ASSERT_EQ(1,
3470  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, m) = TIMESTAMP "
3471  "'2014-12-14 22:23:15' from test limit 1;",
3472  dt)));
3473  ASSERT_EQ(1,
3474  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -1, m) = TIMESTAMP "
3475  "'2014-12-12 22:23:15' from test limit 1;",
3476  dt)));
3477  ASSERT_EQ(1,
3478  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, m) = TIMESTAMP "
3479  "'2014-12-14 22:23:15' from test limit 1;",
3480  dt)));
3481  ASSERT_EQ(1,
3482  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -1, m) = TIMESTAMP "
3483  "'2014-12-12 22:23:15' from test limit 1;",
3484  dt)));
3485  ASSERT_EQ(1,
3486  v<int64_t>(
3487  run_simple_agg("SELECT o = DATE '1999-09-09' from test limit 1;", dt)));
3488  ASSERT_EQ(1,
3489  v<int64_t>(run_simple_agg("SELECT DATEADD('day', 1, o) = TIMESTAMP "
3490  "'1999-09-10 0:00:00' from test limit 1;",
3491  dt)));
3492  ASSERT_EQ(1,
3493  v<int64_t>(run_simple_agg("SELECT DATEADD('day', -3, o) = TIMESTAMP "
3494  "'1999-09-06 0:00:00' from test limit 1;",
3495  dt)));
3496  /* DATE ADD subseconds to default timestamp(0) */
3497  ASSERT_EQ(
3498  1,
3499  v<int64_t>(run_simple_agg("SELECT DATEADD('millisecond', 1000, m) = TIMESTAMP "
3500  "'2014-12-13 22:23:16' from test limit 1;",
3501  dt)));
3502  ASSERT_EQ(
3503  1,
3504  v<int64_t>(run_simple_agg("SELECT DATEADD('microsecond', 1000000, m) = TIMESTAMP "
3505  "'2014-12-13 22:23:16' from test limit 1;",
3506  dt)));
3507  ASSERT_EQ(1,
3508  v<int64_t>(run_simple_agg(
3509  "SELECT DATEADD('nanosecond', 1000000000, m) = TIMESTAMP "
3510  "'2014-12-13 22:23:16' from test limit 1;",
3511  dt)));
3512  ASSERT_EQ(
3513  1,
3514  v<int64_t>(run_simple_agg("SELECT DATEADD('millisecond', 5123, m) = TIMESTAMP "
3515  "'2014-12-13 22:23:20' from test limit 1;",
3516  dt)));
3517  ASSERT_EQ(1,
3518  v<int64_t>(run_simple_agg(
3519  "SELECT DATEADD('microsecond', 86400000000, m) = TIMESTAMP "
3520  "'2014-12-14 22:23:15' from test limit 1;",
3521  dt)));
3522  ASSERT_EQ(1,
3523  v<int64_t>(run_simple_agg(
3524  "SELECT DATEADD('nanosecond', 86400000000123, m) = TIMESTAMP "
3525  "'2014-12-14 22:23:15' from test limit 1;",
3526  dt)));
3527  ASSERT_EQ(1,
3528  v<int64_t>(run_simple_agg("SELECT DATEADD('weekday', -3, o) = TIMESTAMP "
3529  "'1999-09-06 00:00:00' from test limit 1;",
3530  dt)));
3531  ASSERT_EQ(1,
3532  v<int64_t>(run_simple_agg("SELECT DATEADD('decade', 3, o) = TIMESTAMP "
3533  "'2029-09-09 00:00:00' from test limit 1;",
3534  dt)));
3535  ASSERT_EQ(1,
3536  v<int64_t>(run_simple_agg("SELECT DATEADD('week', 1, o) = TIMESTAMP "
3537  "'1999-09-16 00:00:00' from test limit 1;",
3538  dt)));
3539 
3540  ASSERT_EQ(
3541  1,
3542  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, 1, TIMESTAMP '2009-03-02 "
3543  "1:23:45') = TIMESTAMP '2009-03-03 1:23:45' "
3544  "FROM TEST LIMIT 1;",
3545  dt)));
3546  ASSERT_EQ(
3547  1,
3548  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, -1, TIMESTAMP '2009-03-02 "
3549  "1:23:45') = TIMESTAMP '2009-03-01 1:23:45' "
3550  "FROM TEST LIMIT 1;",
3551  dt)));
3552  ASSERT_EQ(
3553  1,
3554  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, 15, TIMESTAMP '2009-03-02 "
3555  "1:23:45') = TIMESTAMP '2009-03-17 1:23:45' "
3556  "FROM TEST LIMIT 1;",
3557  dt)));
3558  ASSERT_EQ(
3559  1,
3560  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(DAY, -15, TIMESTAMP '2009-03-02 "
3561  "1:23:45') = TIMESTAMP '2009-02-15 1:23:45' "
3562  "FROM TEST LIMIT 1;",
3563  dt)));
3564  ASSERT_EQ(
3565  1,
3566  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, 1, TIMESTAMP '2009-03-02 "
3567  "1:23:45') = TIMESTAMP '2009-03-02 2:23:45' "
3568  "FROM TEST LIMIT 1;",
3569  dt)));
3570  ASSERT_EQ(
3571  1,
3572  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, -1, TIMESTAMP '2009-03-02 "
3573  "1:23:45') = TIMESTAMP '2009-03-02 0:23:45' "
3574  "FROM TEST LIMIT 1;",
3575  dt)));
3576  ASSERT_EQ(
3577  1,
3578  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, 15, TIMESTAMP '2009-03-02 "
3579  "1:23:45') = TIMESTAMP '2009-03-02 16:23:45' "
3580  "FROM TEST LIMIT 1;",
3581  dt)));
3582  ASSERT_EQ(
3583  1,
3584  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(HOUR, -15, TIMESTAMP '2009-03-02 "
3585  "1:23:45') = TIMESTAMP '2009-03-01 10:23:45' "
3586  "FROM TEST LIMIT 1;",
3587  dt)));
3588  ASSERT_EQ(
3589  1,
3590  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(MINUTE, 15, TIMESTAMP '2009-03-02 "
3591  "1:23:45') = TIMESTAMP '2009-03-02 1:38:45' "
3592  "FROM TEST LIMIT 1;",
3593  dt)));
3594  ASSERT_EQ(1,
3595  v<int64_t>(
3596  run_simple_agg("SELECT TIMESTAMPADD(MINUTE, -15, TIMESTAMP '2009-03-02 "
3597  "1:23:45') = TIMESTAMP '2009-03-02 1:08:45' "
3598  "FROM TEST LIMIT 1;",
3599  dt)));
3600  ASSERT_EQ(
3601  1,
3602  v<int64_t>(run_simple_agg("SELECT TIMESTAMPADD(SECOND, 15, TIMESTAMP '2009-03-02 "
3603  "1:23:45') = TIMESTAMP '2009-03-02 1:24:00' "
3604  "FROM TEST LIMIT 1;",
3605  dt)));
3606  ASSERT_EQ(1,
3607  v<int64_t>(
3608  run_simple_agg("SELECT TIMESTAMPADD(SECOND, -15, TIMESTAMP '2009-03-02 "
3609  "1:23:45') = TIMESTAMP '2009-03-02 1:23:30' "
3610  "FROM TEST LIMIT 1;",
3611  dt)));
3612 
3613  ASSERT_EQ(1,
3614  v<int64_t>(run_simple_agg(
3615  "SELECT TIMESTAMPADD(DAY, 1, m) = TIMESTAMP '2014-12-14 22:23:15' "
3616  "FROM TEST LIMIT 1;",
3617  dt)));
3618  ASSERT_EQ(1,
3619  v<int64_t>(run_simple_agg(
3620  "SELECT TIMESTAMPADD(DAY, -1, m) = TIMESTAMP '2014-12-12 22:23:15' "
3621  "FROM TEST LIMIT 1;",
3622  dt)));
3623  ASSERT_EQ(1,
3624  v<int64_t>(run_simple_agg(
3625  "SELECT TIMESTAMPADD(DAY, 15, m) = TIMESTAMP '2014-12-28 22:23:15' "
3626  "FROM TEST LIMIT 1;",
3627  dt)));
3628  ASSERT_EQ(1,
3629  v<int64_t>(run_simple_agg(
3630  "SELECT TIMESTAMPADD(DAY, -15, m) = TIMESTAMP '2014-11-28 22:23:15' "
3631  "FROM TEST LIMIT 1;",
3632  dt)));
3633  ASSERT_EQ(1,
3634  v<int64_t>(run_simple_agg(
3635  "SELECT TIMESTAMPADD(HOUR, 1, m) = TIMESTAMP '2014-12-13 23:23:15' "
3636  "FROM TEST LIMIT 1;",
3637  dt)));
3638  ASSERT_EQ(1,
3639  v<int64_t>(run_simple_agg(
3640  "SELECT TIMESTAMPADD(HOUR, -1, m) = TIMESTAMP '2014-12-13 21:23:15' "
3641  "FROM TEST LIMIT 1;",
3642  dt)));
3643  ASSERT_EQ(1,
3644  v<int64_t>(run_simple_agg(
3645  "SELECT TIMESTAMPADD(HOUR, 15, m) = TIMESTAMP '2014-12-14 13:23:15' "
3646  "FROM TEST LIMIT 1;",
3647  dt)));
3648  ASSERT_EQ(1,
3649  v<int64_t>(run_simple_agg(
3650  "SELECT TIMESTAMPADD(HOUR, -15, m) = TIMESTAMP '2014-12-13 7:23:15' "
3651  "FROM TEST LIMIT 1;",
3652  dt)));
3653  ASSERT_EQ(1,
3654  v<int64_t>(run_simple_agg(
3655  "SELECT TIMESTAMPADD(MINUTE, 15, m) = TIMESTAMP '2014-12-13 22:38:15' "
3656  "FROM TEST LIMIT 1;",
3657  dt)));
3658  ASSERT_EQ(1,
3659  v<int64_t>(run_simple_agg(
3660  "SELECT TIMESTAMPADD(MINUTE, -15, m) = TIMESTAMP '2014-12-13 22:08:15' "
3661  "FROM TEST LIMIT 1;",
3662  dt)));
3663  ASSERT_EQ(1,
3664  v<int64_t>(run_simple_agg(
3665  "SELECT TIMESTAMPADD(SECOND, 15, m) = TIMESTAMP '2014-12-13 22:23:30' "
3666  "FROM TEST LIMIT 1;",
3667  dt)));
3668  ASSERT_EQ(1,
3669  v<int64_t>(run_simple_agg(
3670  "SELECT TIMESTAMPADD(SECOND, -15, m) = TIMESTAMP '2014-12-13 22:23:00' "
3671  "FROM TEST LIMIT 1;",
3672  dt)));
3673 
3674  ASSERT_EQ(1,
3675  v<int64_t>(run_simple_agg(
3676  "SELECT TIMESTAMPADD(MONTH, 1, m) = TIMESTAMP '2015-01-13 22:23:15' "
3677  "FROM TEST LIMIT 1;",
3678  dt)));
3679  ASSERT_EQ(1,
3680  v<int64_t>(run_simple_agg(
3681  "SELECT TIMESTAMPADD(MONTH, -1, m) = TIMESTAMP '2014-11-13 22:23:15' "
3682  "FROM TEST LIMIT 1;",
3683  dt)));
3684  ASSERT_EQ(1,
3685  v<int64_t>(run_simple_agg(
3686  "SELECT TIMESTAMPADD(MONTH, 5, m) = TIMESTAMP '2015-05-13 22:23:15' "
3687  "FROM TEST LIMIT 1;",
3688  dt)));
3689  ASSERT_EQ(1,
3690  v<int64_t>(run_simple_agg(
3691  "SELECT TIMESTAMPADD(DAY, -5, m) = TIMESTAMP '2014-12-08 22:23:15' "
3692  "FROM TEST LIMIT 1;",
3693  dt)));
3694  ASSERT_EQ(1,
3695  v<int64_t>(run_simple_agg(
3696  "SELECT TIMESTAMPADD(YEAR, 1, m) = TIMESTAMP '2015-12-13 22:23:15' "
3697  "FROM TEST LIMIT 1;",
3698  dt)));
3699  ASSERT_EQ(1,
3700  v<int64_t>(run_simple_agg(
3701  "SELECT TIMESTAMPADD(YEAR, -1, m) = TIMESTAMP '2013-12-13 22:23:15' "
3702  "FROM TEST LIMIT 1;",
3703  dt)));
3704  ASSERT_EQ(1,
3705  v<int64_t>(run_simple_agg(
3706  "SELECT TIMESTAMPADD(YEAR, 5, m) = TIMESTAMP '2019-12-13 22:23:15' "
3707  "FROM TEST LIMIT 1;",
3708  dt)));
3709  ASSERT_EQ(1,
3710  v<int64_t>(run_simple_agg(
3711  "SELECT TIMESTAMPADD(YEAR, -5, m) = TIMESTAMP '2009-12-13 22:23:15' "
3712  "FROM TEST LIMIT 1;",
3713  dt)));
3714  ASSERT_EQ(
3715  0,
3716  v<int64_t>(run_simple_agg("select count(*) from test where TIMESTAMPADD(YEAR, "
3717  "15, CAST(o AS TIMESTAMP)) > m;",
3718  dt)));
3719  ASSERT_EQ(
3720  15,
3721  v<int64_t>(run_simple_agg("select count(*) from test where TIMESTAMPADD(YEAR, "
3722  "16, CAST(o AS TIMESTAMP)) > m;",
3723  dt)));
3724 
3725  ASSERT_EQ(
3726  128885,
3727  v<int64_t>(run_simple_agg(
3728  "SELECT TIMESTAMPDIFF(minute, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3729  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3730  dt)));
3731  ASSERT_EQ(2148,
3732  v<int64_t>(run_simple_agg(
3733  "SELECT TIMESTAMPDIFF(hour, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3734  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3735  dt)));
3736  ASSERT_EQ(89,
3737  v<int64_t>(run_simple_agg(
3738  "SELECT TIMESTAMPDIFF(day, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3739  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3740  dt)));
3741  ASSERT_EQ(3,
3742  v<int64_t>(run_simple_agg(
3743  "SELECT TIMESTAMPDIFF(month, TIMESTAMP '2003-02-01 0:00:00', TIMESTAMP "
3744  "'2003-05-01 12:05:55') FROM TEST LIMIT 1;",
3745  dt)));
3746  ASSERT_EQ(
3747  -3,
3748  v<int64_t>(run_simple_agg(
3749  "SELECT TIMESTAMPDIFF(month, TIMESTAMP '2003-05-01 12:05:55', TIMESTAMP "
3750  "'2003-02-01 0:00:00') FROM TEST LIMIT 1;",
3751  dt)));
3752  ASSERT_EQ(
3753  5,
3754  v<int64_t>(run_simple_agg(
3755  "SELECT TIMESTAMPDIFF(month, m, m + INTERVAL '5' MONTH) FROM TEST LIMIT 1;",
3756  dt)));
3757  ASSERT_EQ(
3758  -5,
3759  v<int64_t>(run_simple_agg(
3760  "SELECT TIMESTAMPDIFF(month, m, m - INTERVAL '5' MONTH) FROM TEST LIMIT 1;",
3761  dt)));
3762  ASSERT_EQ(
3763  15,
3764  v<int64_t>(run_simple_agg("select count(*) from test where TIMESTAMPDIFF(YEAR, "
3765  "m, CAST(o AS TIMESTAMP)) < 0;",
3766  dt)));
3767  ASSERT_EQ(1,
3768  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(year, DATE '2018-01-02', "
3769  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3770  dt)));
3771  ASSERT_EQ(14,
3772  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(month, DATE '2018-01-02', "
3773  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3774  dt)));
3775  ASSERT_EQ(426,
3776  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(day, DATE '2018-01-02', "
3777  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3778  dt)));
3779  ASSERT_EQ(60,
3780  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(week, DATE '2018-01-02', "
3781  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3782  dt)));
3783  ASSERT_EQ(613440,
3784  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(minute, DATE '2018-01-02', "
3785  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3786  dt)));
3787  ASSERT_EQ(10224,
3788  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(hour, DATE '2018-01-02', "
3789  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3790  dt)));
3791  ASSERT_EQ(36806400,
3792  v<int64_t>(run_simple_agg("SELECT TIMESTAMPDIFF(second, DATE '2018-01-02', "
3793  "DATE '2019-03-04') FROM TEST LIMIT 1;",
3794  dt)));
3795 
3796  ASSERT_EQ(
3797  1418428800L,
3798  v<int64_t>(run_simple_agg("SELECT CAST(m AS date) FROM test LIMIT 1;", dt)));
3799  ASSERT_EQ(1336435200L,
3800  v<int64_t>(run_simple_agg("SELECT CAST(CAST('2012-05-08 20:15:12' AS "
3801  "TIMESTAMP) AS DATE) FROM test LIMIT 1;",
3802  dt)));
3803  ASSERT_EQ(15,
3804  v<int64_t>(run_simple_agg(
3805  "SELECT COUNT(*) FROM test GROUP BY CAST(m AS date);", dt)));
3806  const auto rows = run_multiple_agg(
3807  "SELECT DATE_TRUNC(month, CAST(o AS TIMESTAMP(0))) AS key0, str AS key1, "
3808  "COUNT(*) AS val FROM test GROUP BY "
3809  "key0, key1 ORDER BY val DESC, key1;",
3810  dt);
3811  check_date_trunc_groups(*rows);
3812  const auto one_row = run_multiple_agg(
3813  "SELECT DATE_TRUNC(year, CASE WHEN str = 'foo' THEN m END) d FROM test GROUP BY "
3814  "d "
3815  "HAVING d IS NOT NULL;",
3816  dt);
3817  check_one_date_trunc_group(*one_row, 1388534400);
3818  ASSERT_EQ(0,
3819  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test where "
3820  "DATE '2017-05-30' = DATE '2017-05-31' OR "
3821  "DATE '2017-05-31' = DATE '2017-05-30';",
3822  dt)));
3823  ASSERT_EQ(2 * g_num_rows,
3824  v<int64_t>(run_simple_agg("SELECT COUNT(*) FROM test where "
3825  "EXTRACT(DOW from TIMESTAMPADD(HOUR, -5, "
3826  "TIMESTAMP '2017-05-31 1:11:11')) = 1 OR "
3827  "EXTRACT(DOW from TIMESTAMPADD(HOUR, -5, "
3828  "TIMESTAMP '2017-05-31 1:11:11')) = 2;",
3829  dt)));
3830  std::vector<std::tuple<std::string, int64_t, int64_t>> date_trunc_queries{
3831  /*TIMESTAMP(0) */
3832  std::make_tuple("year, m", 1388534400L, 20),
3833  std::make_tuple("month, m", 1417392000L, 20),
3834  std::make_tuple("day, m", 1418428800L, 15),
3835  std::make_tuple("hour, m", 1418508000L, 15),
3836  std::make_tuple("minute, m", 1418509380L, 15),
3837  std::make_tuple("second, m", 1418509395L, 15),
3838  std::make_tuple("millennium, m", 978307200L, 20),
3839  std::make_tuple("century, m", 978307200L, 20),
3840  std::make_tuple("decade, m", 1293840000L, 20),
3841  std::make_tuple("week, m", 1417910400L, 15),
3842  std::make_tuple("nanosecond, m", 1418509395L, 15),
3843  std::make_tuple("microsecond, m", 1418509395L, 15),
3844  std::make_tuple("millisecond, m", 1418509395L, 15),
3845  /* TIMESTAMP(3) */
3846  std::make_tuple("year, m_3", 1388534400000L, 20),
3847  std::make_tuple("month, m_3", 1417392000000L, 20),
3848  std::make_tuple("day, m_3", 1418428800000L, 15),
3849  std::make_tuple("hour, m_3", 1418508000000L, 15),
3850  std::make_tuple("minute, m_3", 1418509380000L, 15),
3851  std::make_tuple("second, m_3", 1418509395000L, 15),
3852  std::make_tuple("millennium, m_3", 978307200000L, 20),
3853  std::make_tuple("century, m_3", 978307200000L, 20),
3854  std::make_tuple("decade, m_3", 1293840000000L, 20),
3855  std::make_tuple("week, m_3", 1417910400000L, 15),
3856  std::make_tuple("nanosecond, m_3", 1418509395323L, 15),
3857  std::make_tuple("microsecond, m_3", 1418509395323L, 15),
3858  std::make_tuple("millisecond, m_3", 1418509395323L, 15),
3859  /* TIMESTAMP(6) */
3860  std::make_tuple("year, m_6", 915148800000000L, 10),
3861  std::make_tuple("month, m_6", 930787200000000L, 10),
3862  std::make_tuple("day, m_6", 931651200000000L, 10),
3863  std::make_tuple("hour, m_6", 931701600000000L, 10),
3864  /* std::make_tuple("minute, m_6", 931701720000000L, 10), // Exception with sort
3865  watchdog */
3866  std::make_tuple("second, m_6", 931701773000000L, 10),
3867  std::make_tuple("millennium, m_6", -30578688000000000L, 10),
3868  std::make_tuple("century, m_6", -2177452800000000L, 10),
3869  std::make_tuple("decade, m_6", 662688000000000L, 10),
3870  std::make_tuple("week, m_6", 931651200000000L, 10),
3871  std::make_tuple("nanosecond, m_6", 931701773874533L, 10),
3872  std::make_tuple("microsecond, m_6", 931701773874533L, 10),
3873  std::make_tuple("millisecond, m_6", 931701773874000L, 10),
3874  /* TIMESTAMP(9) */
3875  std::make_tuple("year, m_9", 1136073600000000000L, 10),
3876  std::make_tuple("month, m_9", 1143849600000000000L, 10),
3877  std::make_tuple("day, m_9", 1146009600000000000L, 10),
3878  std::make_tuple("hour, m_9", 1146020400000000000L, 10),
3879  /* std::make_tuple("minute, m_9", 1146023340000000000L, 10), // Exception with
3880  sort watchdog */
3881  std::make_tuple("second, m_9", 1146023344000000000L, 10),
3882  std::make_tuple("millennium, m_9", 978307200000000000L, 20),
3883  std::make_tuple("century, m_9", 978307200000000000L, 20),
3884  std::make_tuple("decade, m_9", 978307200000000000L, 10),
3885  std::make_tuple("week, m_9", 1145750400000000000L, 10),
3886  std::make_tuple("nanosecond, m_9", 1146023344607435125L, 10),
3887  std::make_tuple("microsecond, m_9", 1146023344607435000L, 10),
3888  std::make_tuple("millisecond, m_9", 1146023344607000000L, 10)};