ceph/src/s3select/test/s3select_test.cpp

   1 #include "s3select_test.h"
   2
   3 TEST(TestS3SElect, s3select_vs_C)
   4 {
   5 //purpose: validate correct processing of arithmetical expression, it is done by running the same expression
   6 // in C program.
   7 // the test validate that syntax and execution-tree (including precedence rules) are done correctly
   8
   9   for(int y=0; y<10; y++)
  10   {
  11     gen_expr g;
  12     std::string exp = g.generate();
  13     std::string c_result = run_expression_in_C_prog( exp.c_str() );
  14
  15     char* err=0;
  16     double  c_dbl_res = strtod(c_result.c_str(), &err);
  17
  18     std::string input_query = "select " + exp + " from stdin;" ;
  19     std::string s3select_res = run_s3select(input_query);
  20
  21     double  s3select_dbl_res = strtod(s3select_res.c_str(), &err);
  22
  23     //std::cout << exp << " " << s3select_dbl_res << " " << s3select_res << " " << c_dbl_res/s3select_dbl_res << std::endl;
  24     //std::cout << exp << std::endl;
  25
  26     ASSERT_EQ(c_dbl_res, s3select_dbl_res);
  27   }
  28 }
  29
  30 TEST(TestS3SElect, ParseQuery)
  31 {
  32   //TODO syntax issues ?
  33   //TODO error messeges ?
  34
  35   s3select s3select_syntax;
  36
  37   run_s3select(std::string("select (1+1) from stdin;"));
  38
  39   ASSERT_EQ(0, 0);
  40 }
  41
  42 TEST(TestS3SElect, int_compare_operator)
  43 {
  44   value a10(10), b11(11), c10(10);
  45
  46   ASSERT_EQ( a10 < b11, true );
  47   ASSERT_EQ( a10 > b11, false );
  48   ASSERT_EQ( a10 >= c10, true );
  49   ASSERT_EQ( a10 <= c10, true );
  50   ASSERT_EQ( a10 != b11, true );
  51   ASSERT_EQ( a10 == b11, false );
  52   ASSERT_EQ( a10 == c10, true );
  53 }
  54
  55 TEST(TestS3SElect, float_compare_operator)
  56 {
  57   value a10(10.1), b11(11.2), c10(10.1);
  58
  59   ASSERT_EQ( a10 < b11, true );
  60   ASSERT_EQ( a10 > b11, false );
  61   ASSERT_EQ( a10 >= c10, true );
  62   ASSERT_EQ( a10 <= c10, true );
  63   ASSERT_EQ( a10 != b11, true );
  64   ASSERT_EQ( a10 == b11, false );
  65   ASSERT_EQ( a10 == c10, true );
  66
  67 }
  68
  69 TEST(TestS3SElect, string_compare_operator)
  70 {
  71   value s1("abc"), s2("def"), s3("abc");
  72
  73   ASSERT_EQ( s1 < s2, true );
  74   ASSERT_EQ( s1 > s2, false );
  75   ASSERT_EQ( s1 <= s3, true );
  76   ASSERT_EQ( s1 >= s3, true );
  77   ASSERT_EQ( s1 != s2, true );
  78   ASSERT_EQ( s1 == s3, true );
  79   ASSERT_EQ( s1 == s2, false );
  80 }
  81
  82 TEST(TestS3SElect, arithmetic_operator)
  83 {
  84   value a(1), b(2), c(3), d(4);
  85
  86   ASSERT_EQ( (a+b).i64(), 3 );
  87
  88   ASSERT_EQ( (value(0)-value(2)*value(4)).i64(), -8 );
  89   ASSERT_EQ( (value(1.23)-value(0.1)*value(2)).dbl(), 1.03 );
  90
  91   a=int64_t(1); //a+b modify a
  92   ASSERT_EQ( ( (a+b) * (c+d) ).i64(), 21 );
  93 }
  94
  95 TEST(TestS3SElect, intnan_compare_operator)
  96 {
  97   value a10(10), b11(11), c10(10), d, e;
  98   d.set_nan();
  99   e.set_nan();
 100   ASSERT_EQ( d > b11, false );
 101   ASSERT_EQ( d >= c10, false );
 102   ASSERT_EQ( d < a10, false );
 103   ASSERT_EQ( d <= b11, false );
 104   ASSERT_EQ( d != a10, true );
 105   ASSERT_EQ( d != e, true );
 106   ASSERT_EQ( d == a10, false );
 107 }
 108
 109 TEST(TestS3SElect, floatnan_compare_operator)
 110 {
 111   value a10(10.1), b11(11.2), c10(10.1), d, e;
 112   d.set_nan();
 113   e.set_nan();
 114   ASSERT_EQ( d > b11, false );
 115   ASSERT_EQ( d >= c10, false );
 116   ASSERT_EQ( d < a10, false );
 117   ASSERT_EQ( d <= b11, false );
 118   ASSERT_EQ( d != a10, true );
 119   ASSERT_EQ( d != e, true );
 120   ASSERT_EQ( d == a10, false );
 121 }
 122
 123 TEST(TestS3SElect, null_arithmetic_operator)
 124 {
 125   const char *cnull = "null";
 126   value a(7), d, e(0);
 127   d.setnull();
 128
 129   ASSERT_EQ(*(a - d).to_string(), *cnull );
 130   ASSERT_EQ(*(a * d).to_string(), *cnull );
 131   ASSERT_EQ(*(a / d).to_string(), *cnull );
 132   ASSERT_EQ(*(a / e).to_string(), *cnull );
 133   ASSERT_EQ(*(d + a).to_string(), *cnull );
 134   ASSERT_EQ(*(d - a).to_string(), *cnull );
 135   ASSERT_EQ(*(d * a).to_string(), *cnull );
 136   ASSERT_EQ(*(d / a).to_string(), *cnull );
 137   ASSERT_EQ(*(e / a).to_string(), *cnull );
 138 }
 139
 140 TEST(TestS3SElect, nan_arithmetic_operator)
 141 {
 142   value a(7), d, y(0);
 143   d.set_nan();
 144   float b = ((a + d).dbl() );
 145   float c = ((a - d).dbl() );
 146   float v = ((a * d).dbl() );
 147   float w = ((a / d).dbl() );
 148   float x = ((d / y).dbl() );
 149   float r = ((d + a).dbl() );
 150   float z = ((d - a).dbl() );
 151   float u = ((d * a).dbl() );
 152   float t = ((d / a).dbl() );
 153   EXPECT_FALSE(b <= b);
 154   EXPECT_FALSE(c <= c);
 155   EXPECT_FALSE(v <= v);
 156   EXPECT_FALSE(w <= w);
 157   EXPECT_FALSE(x <= x);
 158   EXPECT_FALSE(r <= r);
 159   EXPECT_FALSE(z <= z);
 160   EXPECT_FALSE(u <= u);
 161   EXPECT_FALSE(t <= t);
 162 }
 163
 164 TEST(TestS3selectFunctions, to_timestamp)
 165 {
 166     std::string timestamp = "2007T";
 167     std::string out_timestamp = "2007-01-01T00:00:00+00:00";
 168     std::string input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 169     auto s3select_res = run_s3select(input_query);
 170     EXPECT_EQ(s3select_res, out_timestamp);
 171
 172     timestamp = "2007-09-17T";
 173     out_timestamp = "2007-09-17T00:00:00+00:00";
 174     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 175     s3select_res = run_s3select(input_query);
 176     EXPECT_EQ(s3select_res, out_timestamp);
 177
 178     timestamp = "2007-09-17T17:56Z";
 179     out_timestamp = "2007-09-17T17:56:00Z";
 180     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 181     s3select_res = run_s3select(input_query);
 182     EXPECT_EQ(s3select_res, out_timestamp);
 183
 184     timestamp = "2007-09-17T17:56:05Z";
 185     out_timestamp = "2007-09-17T17:56:05Z";
 186     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 187     s3select_res = run_s3select(input_query);
 188     EXPECT_EQ(s3select_res, out_timestamp);
 189
 190     timestamp = "2007-09-17T17:56:05.234Z";
 191     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 192         out_timestamp = "2007-09-17T17:56:05.234000000Z";
 193     #else
 194         out_timestamp = "2007-09-17T17:56:05.234000Z";
 195     #endif
 196     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 197     s3select_res = run_s3select(input_query);
 198     EXPECT_EQ(s3select_res, out_timestamp);
 199
 200     timestamp = "2007-09-17T17:56+12:08";
 201     out_timestamp = "2007-09-17T17:56:00+12:08";
 202     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 203     s3select_res = run_s3select(input_query);
 204     EXPECT_EQ(s3select_res, out_timestamp);
 205
 206     timestamp = "2007-09-17T17:56:05-05:30";
 207     out_timestamp = "2007-09-17T17:56:05-05:30";
 208     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 209     s3select_res = run_s3select(input_query);
 210     EXPECT_EQ(s3select_res, out_timestamp);
 211
 212     timestamp = "2007-09-17T17:56:05.234+02:44";
 213     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 214         out_timestamp = "2007-09-17T17:56:05.234000000+02:44";
 215     #else
 216         out_timestamp = "2007-09-17T17:56:05.234000+02:44";
 217     #endif
 218     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 219     s3select_res = run_s3select(input_query);
 220     EXPECT_EQ(s3select_res, out_timestamp);
 221
 222     timestamp = "2007-09-17T17:56:05.00234+02:44";
 223     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 224         out_timestamp = "2007-09-17T17:56:05.002340000+02:44";
 225     #else
 226         out_timestamp = "2007-09-17T17:56:05.002340+02:44";
 227     #endif
 228     input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 229     s3select_res = run_s3select(input_query);
 230     EXPECT_EQ(s3select_res, out_timestamp);
 231
 232     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 233         timestamp = "2007-09-17T17:56:05.012345678-00:45";
 234         out_timestamp = "2007-09-17T17:56:05.012345678-00:45";
 235         input_query = "select to_timestamp(\'" + timestamp + "\') from stdin;" ;
 236         s3select_res = run_s3select(input_query);
 237         EXPECT_EQ(s3select_res, out_timestamp);
 238     #endif
 239
 240 }
 241
 242 TEST(TestS3selectFunctions, date_diff)
 243 {
 244     std::string input_query = "select date_diff(year, to_timestamp(\'2009-09-17T17:56:06.234Z\'), to_timestamp(\'2007-09-17T19:30:05.234Z\')) from stdin;" ;
 245     auto s3select_res = run_s3select(input_query);
 246     EXPECT_EQ(s3select_res, "-1");
 247
 248     input_query = "select date_diff(month, to_timestamp(\'2009-09-17T17:56:06.234Z\'), to_timestamp(\'2007-09-17T19:30:05.234Z\')) from stdin;" ;
 249     s3select_res = run_s3select(input_query);
 250     EXPECT_EQ(s3select_res, "-23");
 251
 252     input_query = "select date_diff(day, to_timestamp(\'2009-09-17T17:56:06.234Z\'), to_timestamp(\'2007-09-17T19:30:05.234Z\')) from stdin;" ;
 253     s3select_res = run_s3select(input_query);
 254     EXPECT_EQ(s3select_res, "-730");
 255
 256     input_query = "select date_diff(hour, to_timestamp(\'2007-09-17T17:56:06.234Z\'), to_timestamp(\'2009-09-17T19:30:05.234Z\')) from stdin;" ;
 257     s3select_res = run_s3select(input_query);
 258     EXPECT_EQ(s3select_res, "17545");
 259
 260     input_query = "select date_diff(hour, to_timestamp(\'2009-09-17T19:30:05.234Z\'), to_timestamp(\'2007-09-17T17:56:06.234Z\')) from stdin;" ;
 261     s3select_res = run_s3select(input_query);
 262     EXPECT_EQ(s3select_res, "-17545");
 263
 264     input_query = "select date_diff(minute, to_timestamp(\'2007-09-17T17:56:06.234Z\'), to_timestamp(\'2009-09-17T19:30:05.234Z\')) from stdin;" ;
 265     s3select_res = run_s3select(input_query);
 266     EXPECT_EQ(s3select_res, "1052733");
 267
 268     input_query = "select date_diff(minute, to_timestamp(\'2009-09-17T19:30:05.234Z\'), to_timestamp(\'2007-09-17T17:56:06.234Z\')) from stdin;" ;
 269     s3select_res = run_s3select(input_query);
 270     EXPECT_EQ(s3select_res, "-1052733");
 271
 272     input_query = "select date_diff(second, to_timestamp(\'2009-09-17T17:56:06.234Z\'), to_timestamp(\'2009-09-17T19:30:05.234Z\')) from stdin;" ;
 273     s3select_res = run_s3select(input_query);
 274     EXPECT_EQ(s3select_res, "5639");
 275
 276     input_query = "select date_diff(hour, to_timestamp(\'2007-09-17T17:56:06.234-03:45\'), to_timestamp(\'2007-09-17T17:56:06.234+13:30\')) from stdin;" ;
 277     s3select_res = run_s3select(input_query);
 278     EXPECT_EQ(s3select_res, "-17");
 279
 280     input_query = "select date_diff(hour, to_timestamp(\'2007-09-17T17:56:06.234+03:45\'), to_timestamp(\'2007-09-17T17:56:06.234+13:30\')) from stdin;" ;
 281     s3select_res = run_s3select(input_query);
 282     EXPECT_EQ(s3select_res, "-9");
 283
 284     input_query = "select date_diff(hour, to_timestamp(\'2007-09-17T17:56:06.234Z\'), to_timestamp(\'2007-09-17T17:56:06.234+13:30\')) from stdin;" ;
 285     s3select_res = run_s3select(input_query);
 286     EXPECT_EQ(s3select_res, "-13");
 287
 288     input_query = "select date_diff(hour, to_timestamp(\'2007-09-17T17:56:06.234+14:00\'), to_timestamp(\'2007-09-17T17:56:06.234Z\')) from stdin;" ;
 289     s3select_res = run_s3select(input_query);
 290     EXPECT_EQ(s3select_res, "14");
 291
 292     input_query = "select date_diff(minute, to_timestamp(\'2007-09-17T17:56:06.234-03:45\'), to_timestamp(\'2007-09-17T17:56:06.234+13:30\')) from stdin;" ;
 293     s3select_res = run_s3select(input_query);
 294     EXPECT_EQ(s3select_res, "-1035");
 295
 296     input_query = "select date_diff(minute, to_timestamp(\'2007-09-17T17:56:06.234+03:45\'), to_timestamp(\'2007-09-17T17:56:06.234+13:30\')) from stdin;" ;
 297     s3select_res = run_s3select(input_query);
 298     EXPECT_EQ(s3select_res, "-585");
 299
 300     input_query = "select date_diff(minute, to_timestamp(\'2007-09-17T17:56:06.234Z\'), to_timestamp(\'2007-09-17T17:56:06.234+13:30\')) from stdin;" ;
 301     s3select_res = run_s3select(input_query);
 302     EXPECT_EQ(s3select_res, "-810");
 303
 304     input_query = "select date_diff(minute, to_timestamp(\'2007-09-17T17:56:06.234+14:00\'), to_timestamp(\'2007-09-17T17:56:06.234Z\')) from stdin;" ;
 305     s3select_res = run_s3select(input_query);
 306     EXPECT_EQ(s3select_res, "840");
 307
 308     input_query = "select date_diff(hour, to_timestamp(\'2007-09-17T17:56:06.234+14:00\'), to_timestamp(\'2007-09-17T03:56:06.234Z\')) from stdin;" ;
 309     s3select_res = run_s3select(input_query);
 310     EXPECT_EQ(s3select_res, "0");
 311 }
 312
 313 TEST(TestS3selectFunctions, date_add)
 314 {
 315     std::string input_query = "select date_add(year, 2, to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 316     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 317         std::string expected_res = "2011-09-17T17:56:06.234567000Z";
 318     #else
 319         std::string expected_res = "2011-09-17T17:56:06.234567Z";
 320     #endif
 321     auto s3select_res = run_s3select(input_query);
 322     EXPECT_EQ(s3select_res, expected_res);
 323
 324     input_query = "select date_add(month, -5, to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 325     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 326         expected_res = "2009-04-17T17:56:06.234567000Z";
 327     #else
 328         expected_res = "2009-04-17T17:56:06.234567Z";
 329     #endif
 330     s3select_res = run_s3select(input_query);
 331     EXPECT_EQ(s3select_res, expected_res);
 332
 333     input_query = "select date_add(day, 3, to_timestamp(\'2009-09-17T17:56:06.234567-09:15\')) from stdin;" ;
 334     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 335         expected_res = "2009-09-20T17:56:06.234567000-09:15";
 336     #else
 337         expected_res = "2009-09-20T17:56:06.234567-09:15";
 338     #endif
 339     s3select_res = run_s3select(input_query);
 340     EXPECT_EQ(s3select_res, expected_res);
 341
 342     input_query = "select date_add(hour, 1, to_timestamp(\'2007-09-17T17:56:06.234567Z\')) from stdin;" ;
 343     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 344         expected_res = "2007-09-17T18:56:06.234567000Z";
 345     #else
 346         expected_res = "2007-09-17T18:56:06.234567Z";
 347     #endif
 348     s3select_res = run_s3select(input_query);
 349     EXPECT_EQ(s3select_res, expected_res);
 350
 351     input_query = "select date_add(minute, 14, to_timestamp(\'2007-09-17T17:56:06.234567+11:00\')) from stdin;" ;
 352     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 353         expected_res = "2007-09-17T18:10:06.234567000+11:00";
 354     #else
 355         expected_res = "2007-09-17T18:10:06.234567+11:00";
 356     #endif
 357     s3select_res = run_s3select(input_query);
 358     EXPECT_EQ(s3select_res, expected_res);
 359
 360     input_query = "select date_add(second, -26, to_timestamp(\'2009-09-17T17:56:06.234567-00:30\')) from stdin;" ;
 361     #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
 362         expected_res = "2009-09-17T17:55:40.234567000-00:30";
 363     #else
 364         expected_res = "2009-09-17T17:55:40.234567-00:30";
 365     #endif
 366     s3select_res = run_s3select(input_query);
 367     EXPECT_EQ(s3select_res, expected_res);
 368
 369     input_query = "select date_add(month, 1,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 370     expected_res = "2007-10-17T17:57:06Z";
 371     s3select_res = run_s3select(input_query);
 372     EXPECT_EQ(s3select_res, expected_res);
 373
 374     input_query = "select date_add(month, 3,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 375     expected_res = "2007-12-17T17:57:06Z";
 376     s3select_res = run_s3select(input_query);
 377     EXPECT_EQ(s3select_res, expected_res);
 378
 379     input_query = "select date_add(month, 4,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 380     expected_res = "2008-01-17T17:57:06Z";
 381     s3select_res = run_s3select(input_query);
 382     EXPECT_EQ(s3select_res, expected_res);
 383
 384     input_query = "select date_add(month, 15,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 385     expected_res = "2008-12-17T17:57:06Z";
 386     s3select_res = run_s3select(input_query);
 387     EXPECT_EQ(s3select_res, expected_res);
 388
 389     input_query = "select date_add(month, -1,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 390     expected_res = "2007-08-17T17:57:06Z";
 391     s3select_res = run_s3select(input_query);
 392     EXPECT_EQ(s3select_res, expected_res);
 393
 394     input_query = "select date_add(month, -8,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 395     expected_res = "2007-01-17T17:57:06Z";
 396     s3select_res = run_s3select(input_query);
 397     EXPECT_EQ(s3select_res, expected_res);
 398
 399     input_query = "select date_add(month, -9,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 400     expected_res = "2006-12-17T17:57:06Z";
 401     s3select_res = run_s3select(input_query);
 402     EXPECT_EQ(s3select_res, expected_res);
 403
 404     input_query = "select date_add(month, -10,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 405     expected_res = "2006-11-17T17:57:06Z";
 406     s3select_res = run_s3select(input_query);
 407     EXPECT_EQ(s3select_res, expected_res);
 408
 409     input_query = "select date_add(month, -15,to_timestamp(\'2007-09-17T17:57:06Z\')) from stdin;" ;
 410     expected_res = "2006-06-17T17:57:06Z";
 411     s3select_res = run_s3select(input_query);
 412     EXPECT_EQ(s3select_res, expected_res);
 413 }
 414
 415 TEST(TestS3selectFunctions, extract)
 416 {
 417     std::string input_query = "select extract(year from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 418     auto s3select_res = run_s3select(input_query);
 419     EXPECT_EQ(s3select_res, "2009");
 420
 421     input_query = "select extract(month from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 422     s3select_res = run_s3select(input_query);
 423     EXPECT_EQ(s3select_res, "9");
 424
 425     input_query = "select extract(day from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 426     s3select_res = run_s3select(input_query);
 427     EXPECT_EQ(s3select_res, "17");
 428
 429     input_query = "select extract(week from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 430     s3select_res = run_s3select(input_query);
 431     EXPECT_EQ(s3select_res, "38");
 432
 433     input_query = "select extract(hour from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 434     s3select_res = run_s3select(input_query);
 435     EXPECT_EQ(s3select_res, "17");
 436
 437     input_query = "select extract(minute from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 438     s3select_res = run_s3select(input_query);
 439     EXPECT_EQ(s3select_res, "56");
 440
 441     input_query = "select extract(second from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 442     s3select_res = run_s3select(input_query);
 443     EXPECT_EQ(s3select_res, "6");
 444
 445     input_query = "select extract(timezone_hour from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 446     s3select_res = run_s3select(input_query);
 447     EXPECT_EQ(s3select_res, "0");
 448
 449     input_query = "select extract(timezone_hour from to_timestamp(\'2009-09-17T17:56:06.234567-07:45\')) from stdin;" ;
 450     s3select_res = run_s3select(input_query);
 451     EXPECT_EQ(s3select_res, "-7");
 452
 453     input_query = "select extract(timezone_hour from to_timestamp(\'2009-09-17T17:56:06.234567+07:45\')) from stdin;" ;
 454     s3select_res = run_s3select(input_query);
 455     EXPECT_EQ(s3select_res, "7");
 456
 457     input_query = "select extract(timezone_minute from to_timestamp(\'2009-09-17T17:56:06.234567Z\')) from stdin;" ;
 458     s3select_res = run_s3select(input_query);
 459     EXPECT_EQ(s3select_res, "0");
 460
 461     input_query = "select extract(timezone_minute from to_timestamp(\'2009-09-17T17:56:06.234567-07:45\')) from stdin;" ;
 462     s3select_res = run_s3select(input_query);
 463     EXPECT_EQ(s3select_res, "-45");
 464
 465     input_query = "select extract(timezone_minute from to_timestamp(\'2009-09-17T17:56:06.234567+07:45\')) from stdin;" ;
 466     s3select_res = run_s3select(input_query);
 467     EXPECT_EQ(s3select_res, "45");
 468 }
 469
 470 TEST(TestS3selectFunctions, to_string)
 471 {
 472     std::string input_query = "select to_string(to_timestamp(\'2009-09-17T17:56:06.234567Z\'), \'yyyyMMdd-H:m:s\') from stdin;" ;
 473     auto s3select_res = run_s3select(input_query);
 474     EXPECT_EQ(s3select_res, "20090917-17:56:6");
 475
 476     input_query = "select to_string(to_timestamp(\'2009-03-17T17:56:06.234567Z\'), \'yydaMMMM h m s.n\') from stdin;" ;
 477     s3select_res = run_s3select(input_query);
 478     EXPECT_EQ(s3select_res, "0917PMMarch 5 56 6.234567000");
 479
 480     input_query = "select to_string(to_timestamp(\'2009-03-07T01:08:06.234567Z\'), \'yyyyyy yyyy yyy yy y MMMMM MMMM MMM MM M dd dTHH H hh h : mm m ss s SSSSSSSSSS SSSSSS SSS SS S n - a X XX XXX XXXX XXXXX x xx xxx xxxx xxxxx\') from stdin;" ;
 481     s3select_res = run_s3select(input_query);
 482     EXPECT_EQ(s3select_res, "002009 2009 2009 09 2009 M March Mar 03 3 07 7T01 1 01 1 : 08 8 06 6 2345670000 234567 234 23 2 234567000 - AM Z Z Z Z Z +00 +0000 +00:00 +0000 +00:00");
 483
 484     input_query = "select to_string(to_timestamp(\'2009-03-07T01:08:06.234567-04:25\'), \'X XX XXX XXXX XXXXX x xx xxx xxxx xxxxx\') from stdin;" ;
 485     s3select_res = run_s3select(input_query);
 486     EXPECT_EQ(s3select_res, "-0425 -0425 -04:25 -0425 -04:25 -0425 -0425 -04:25 -0425 -04:25");
 487
 488     input_query = "select to_string(to_timestamp(\'2009-03-07T01:08:06.234567+12:05\'), \'X XX XXX XXXX XXXXX x xx xxx xxxx xxxxx\') from stdin;" ;
 489     s3select_res = run_s3select(input_query);
 490     EXPECT_EQ(s3select_res, "+1205 +1205 +12:05 +1205 +12:05 +1205 +1205 +12:05 +1205 +12:05");
 491
 492     input_query = "select to_string(to_timestamp(\'2009-03-07T01:08:06.2345+00:00\'), \'n SSS SSSSSS SSSSSSSSS SSSSSSSSSSS\') from stdin;" ;
 493     s3select_res = run_s3select(input_query);
 494     EXPECT_EQ(s3select_res, "234500000 234 234500 234500000 23450000000");
 495
 496     input_query = "select to_string(to_timestamp(\'2009-03-07T01:08:06.002345Z\'), \'n SSS SSSSSS SSSSSSSSS SSSSSSSSSSS\') from stdin;" ;
 497     s3select_res = run_s3select(input_query);
 498     EXPECT_EQ(s3select_res, "2345000 002 002345 002345000 00234500000");
 499
 500     input_query = "select to_string(to_timestamp(\'2009-03-07T01:08:06Z\'), \'n SSS SSSSSS SSSSSSSSS SSSSSSSSSSS\') from stdin;" ;
 501     s3select_res = run_s3select(input_query);
 502     EXPECT_EQ(s3select_res, "0 000 000000 000000000 00000000000");
 503 }
 504
 505 TEST(TestS3selectFunctions, utcnow)
 506 {
 507     const boost::posix_time::ptime now(boost::posix_time::second_clock::universal_time());
 508     const std::string input_query = "select utcnow() from stdin;" ;
 509     auto s3select_res = run_s3select(input_query);
 510     const boost::posix_time::ptime res_now;
 511     ASSERT_EQ(s3select_res, boost::posix_time::to_iso_extended_string(now) + "+00:00");
 512 }
 513
 514 TEST(TestS3selectFunctions, add)
 515 {
 516     const std::string input_query = "select add(-5, 0.5) from stdin;" ;
 517           auto s3select_res = run_s3select(input_query);
 518     ASSERT_EQ(s3select_res, std::string("-4.5"));
 519 }
 520
 521 void generate_fix_columns_csv(std::string& out, size_t size) {
 522   std::stringstream ss;
 523   for (auto i = 0U; i < size; ++i) {
 524     ss << 1 << "," << 2 << "," << 3 << "," << 4 << "," << 5 << std::endl;
 525   }
 526   out = ss.str();
 527 }
 528
 529 void generate_rand_csv(std::string& out, size_t size) {
 530   // schema is: int, float, string, string
 531   std::stringstream ss;
 532   for (auto i = 0U; i < size; ++i) {
 533     ss << rand()%1000 << "," << rand()%1000 << "," << rand()%1000 << "," << "foo"+std::to_string(i) << "," << std::to_string(i)+"bar" << std::endl;
 534   }
 535   out = ss.str();
 536 }
 537
 538 void generate_csv(std::string& out, size_t size) {
 539   // schema is: int, float, string, string
 540   std::stringstream ss;
 541   for (auto i = 0U; i < size; ++i) {
 542     ss << i << "," << i/10.0 << "," << "foo"+std::to_string(i) << "," << std::to_string(i)+"bar" << std::endl;
 543   }
 544   out = ss.str();
 545 }
 546
 547 void generate_csv_escape(std::string& out, size_t size) {
 548   // schema is: int, float, string, string
 549   std::stringstream ss;
 550   for (auto i = 0U; i < size; ++i) {
 551     ss << "_ar" << "," << "aeio_" << "," << "foo"+std::to_string(i) << "," << std::to_string(i)+"bar" << std::endl;
 552   }
 553   out = ss.str();
 554 }
 555
 556 void generate_columns_csv(std::string& out, size_t size) {
 557   std::stringstream ss;
 558
 559   for (auto i = 0U; i < size; ++i) {
 560     ss << i << "," << i+1 << "," << i << "," << i << "," << i << "," << i << "," << i << "," << i << "," << i << "," << i << std::endl;
 561   }
 562   out = ss.str();
 563 }
 564
 565 void generate_rand_columns_csv(std::string& out, size_t size) {
 566   std::stringstream ss;
 567   auto r = [](){return rand()%1000;};
 568
 569   for (auto i = 0U; i < size; ++i) {
 570     ss << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << std::endl;
 571   }
 572   out = ss.str();
 573 }
 574
 575 void generate_rand_columns_csv_with_null(std::string& out, size_t size) {
 576   std::stringstream ss;
 577   auto r = [](){ int x=rand()%1000;if (x<100) return std::string(""); else return std::to_string(x);};
 578
 579   for (auto i = 0U; i < size; ++i) {
 580     ss << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << "," << r() << std::endl;
 581   }
 582   out = ss.str();
 583 }
 584
 585 void generate_csv_trim(std::string& out, size_t size) {
 586   // schema is: int, float, string, string
 587   std::stringstream ss;
 588   for (auto i = 0U; i < size; ++i) {
 589     ss << "     aeiou     " << "," << std::endl;
 590   }
 591   out = ss.str();
 592 }
 593
 594 void generate_csv_like(std::string& out, size_t size) {
 595   // schema is: int, float, string, string
 596   std::stringstream ss;
 597   auto r = [](){ int x=rand()%1000;if (x<500) return std::string("hai"); else return std::string("fooaeioubrs");};
 598   for (auto i = 0U; i < size; ++i) {
 599     ss << r() << "," << std::endl;
 600   }
 601   out = ss.str();
 602 }
 603
 604 void generate_rand_columns_csv_datetime(std::string& out, size_t size) {
 605   std::stringstream ss;
 606   auto year = [](){return rand()%100 + 1900;};
 607   auto month = [](){return 1 + rand()%12;};
 608   auto day = [](){return 1 + rand()%28;};
 609   auto hours = [](){return rand()%24;};
 610   auto minutes = [](){return rand()%60;};
 611   auto seconds = [](){return rand()%60;};
 612
 613   for (auto i = 0U; i < size; ++i) {
 614     ss << year() << "-" << std::setw(2) << std::setfill('0')<< month() << "-" << std::setw(2) << std::setfill('0')<< day() << "T" <<std::setw(2) << std::setfill('0')<< hours() << ":" << std::setw(2) << std::setfill('0')<< minutes() << ":" << std::setw(2) << std::setfill('0')<<seconds() << "Z" << "," << std::endl;
 615   }
 616   out = ss.str();
 617 }
 618
 619 void generate_rand_csv_datetime_to_string(std::string& out, std::string& result, size_t size, bool const_frmt = true) {
 620   std::stringstream ss_out, ss_res;
 621   std::string format = "yyyysMMMMMdddSSSSSSSSSSSMMMM HHa:m -:-";
 622   std::string months[12] = {"January", "February", "March","April", "May", "June", "July", "August", "September", "October", "November", "December"};
 623   auto year = [](){return rand()%100 + 1900;};
 624   auto month = [](){return 1 + rand()%12;};
 625   auto day = [](){return 1 + rand()%28;};
 626   auto hours = [](){return rand()%24;};
 627   auto minutes = [](){return rand()%60;};
 628   auto seconds = [](){return rand()%60;};
 629   auto fracation_sec = [](){return rand()%1000000;};
 630
 631   for (auto i = 0U; i < size; ++i)
 632   {
 633     auto yr = year();
 634     auto mnth = month();
 635     auto dy = day();
 636     auto hr = hours();
 637     auto mint = minutes();
 638     auto sec = seconds();
 639     auto frac_sec = fracation_sec();
 640
 641     if (const_frmt)
 642     {
 643       ss_out << yr << "-" << std::setw(2) << std::setfill('0') << mnth << "-" << std::setw(2) << std::setfill('0') << dy << "T" <<std::setw(2) << std::setfill('0') << hr << ":" << std::setw(2) << std::setfill('0') << mint << ":" << std::setw(2) << std::setfill('0') <<sec << "." << frac_sec << "Z" << std::endl;
 644
 645       ss_res << yr << sec << months[mnth-1].substr(0, 1) << std::setw(2) << std::setfill('0') << dy << dy << frac_sec << std::string(11 - std::to_string(frac_sec).length(), '0') << months[mnth-1] << " " << std::setw(2) << std::setfill('0') << hr << (hr < 12 ? "AM" : "PM") << ":" << mint << " -:-" << std::endl;
 646     }
 647     else
 648     {
 649       switch(rand()%5)
 650       {
 651         case 0:
 652             format = "yyyysMMMMMdddSSSSSSSSSSSMMMM HHa:m -:-";
 653             ss_res << yr << sec << months[mnth-1].substr(0, 1) << std::setw(2) << std::setfill('0') << dy << dy << frac_sec << std::string(11 - std::to_string(frac_sec).length(), '0') << months[mnth-1] << " " << std::setw(2) << std::setfill('0') << hr << (hr < 12 ? "AM" : "PM") << ":" << mint << " -:-" << std::endl;
 654             break;
 655         case 1:
 656             format = "aMMhh";
 657             ss_res << (hr < 12 ? "AM" : "PM") << std::setw(2) << std::setfill('0') << mnth << std::setw(2) << std::setfill('0') << (hr%12 == 0 ? 12 : hr%12) << std::endl;
 658             break;
 659         case 2:
 660             format = "y M d ABCDEF";
 661             ss_res << yr << " " << mnth << " " << dy << " ABCDEF" << std::endl;
 662             break;
 663         case 3:
 664             format = "W h:MMMM";
 665             ss_res << "W " << (hr%12 == 0 ? 12 : hr%12) << ":" << months[mnth-1] << std::endl;
 666             break;
 667         case 4:
 668             format = "H:m:s";
 669             ss_res << hr << ":" << mint << ":" << sec << std::endl;
 670             break;
 671       }
 672
 673       ss_out << yr << "-" << std::setw(2) << std::setfill('0') << mnth << "-" << std::setw(2) << std::setfill('0') << dy << "T" <<std::setw(2) << std::setfill('0') << hr << ":" << std::setw(2) << std::setfill('0') << mint << ":" << std::setw(2) << std::setfill('0') <<sec << "." << frac_sec << "Z" << "," << format << std::endl;
 674     }
 675   }
 676   out = ss_out.str();
 677   result = ss_res.str();
 678 }
 679
 680 TEST(TestS3selectFunctions, sum)
 681 {
 682   std::string input;
 683   size_t size = 128;
 684   generate_columns_csv(input, size);
 685   const std::string input_query_1 = "select sum(int(_1)), sum(float(_2)) from stdin;";
 686
 687   std::string s3select_result_1 = run_s3select(input_query_1,input);
 688
 689   ASSERT_EQ(s3select_result_1,"8128,8256");
 690 }
 691
 692 TEST(TestS3selectFunctions, between)
 693 {
 694   std::string input;
 695   size_t size = 128;
 696   generate_rand_columns_csv(input, size);
 697   const std::string input_query_1 = "select count(0) from stdin where int(_1) between int(_2) and int(_3);";
 698
 699   std::string s3select_result_1 = run_s3select(input_query_1,input);
 700
 701   const std::string input_query_2 = "select count(0) from stdin where int(_1) >= int(_2) and int(_1) <= int(_3);";
 702
 703   std::string s3select_result_2 = run_s3select(input_query_1,input);
 704
 705   ASSERT_EQ(s3select_result_1,s3select_result_2);
 706 }
 707
 708
 709 TEST(TestS3selectFunctions, not_between)
 710 {
 711   std::string input;
 712   size_t size = 128;
 713   generate_rand_columns_csv(input, size);
 714   const std::string input_query_1 = "select count(0) from stdin where int(_1) not  between int(_2) and int(_3);";
 715
 716   std::string s3select_result_1 = run_s3select(input_query_1,input);
 717
 718   const std::string input_query_2 = "select count(0) from stdin where int(_1) < int(_2) or int(_1) > int(_3);";
 719
 720   std::string s3select_result_2 = run_s3select(input_query_2,input);
 721
 722   ASSERT_EQ(s3select_result_1,s3select_result_2);
 723 }
 724
 725 TEST(TestS3selectFunctions, count)
 726 {
 727   std::string input;
 728   size_t size = 128;
 729   generate_columns_csv(input, size);
 730   const std::string input_query_1 = "select count(0) from stdin;";
 731
 732   std::string s3select_result_1 = run_s3select(input_query_1,input);
 733
 734   ASSERT_EQ(s3select_result_1,"128");
 735 }
 736
 737 TEST(TestS3selectFunctions, no_args)
 738 {//note: engine throw an exception(and description), currently it is not catch in this test-app
 739 #if 0
 740   std::string input;
 741   size_t size = 128;
 742   generate_columns_csv(input, size);
 743   std::string input_query_1 = "select min() from stdin;";
 744
 745   std::string s3select_result_1 = run_s3select(input_query_1,input);
 746
 747   ASSERT_EQ(s3select_result_1,"");
 748
 749   input_query_1 = "select max() from stdin;";
 750
 751   s3select_result_1 = run_s3select(input_query_1,input);
 752
 753   ASSERT_EQ(s3select_result_1,"");
 754
 755   input_query_1 = "select avg() from stdin;";
 756
 757   s3select_result_1 = run_s3select(input_query_1,input);
 758
 759   ASSERT_EQ(s3select_result_1,"");
 760
 761   input_query_1 = "select sum() from stdin;";
 762
 763   s3select_result_1 = run_s3select(input_query_1,input);
 764
 765   ASSERT_EQ(s3select_result_1,"");
 766 #endif
 767 }
 768
 769 TEST(TestS3selectFunctions, empty_group_upon_aggtegation)
 770 {
 771
 772   std::string input;
 773   size_t size = 128;
 774   generate_columns_csv(input, size);
 775   std::string input_query_1 = "select min(cast(_1 as int)) from stdin where 1 = 0;";
 776
 777   std::string s3select_result_1 = run_s3select(input_query_1,input);
 778
 779   ASSERT_EQ(s3select_result_1,"null");
 780
 781   input_query_1 = "select max(cast(_1 as int)) from stdin where 1 = 0;";
 782
 783   s3select_result_1 = run_s3select(input_query_1,input);
 784
 785   ASSERT_EQ(s3select_result_1,"null");
 786
 787   input_query_1 = "select sum(cast(_1 as int)) from stdin where 1 = 0;";
 788
 789   s3select_result_1 = run_s3select(input_query_1,input);
 790
 791   ASSERT_EQ(s3select_result_1,"null");
 792
 793   input_query_1 = "select avg(cast(_1 as int)) from stdin where 1 = 0;";
 794
 795   s3select_result_1 = run_s3select(input_query_1,input);
 796
 797   ASSERT_EQ(s3select_result_1,"null");
 798 }
 799
 800 TEST(TestS3selectFunctions, min)
 801 {
 802   std::string input;
 803   size_t size = 128;
 804   generate_columns_csv(input, size);
 805   const std::string input_query_1 = "select min(int(_1)), min(float(_2)) from stdin;";
 806
 807   std::string s3select_result_1 = run_s3select(input_query_1,input);
 808
 809   ASSERT_EQ(s3select_result_1,"0,1");
 810 }
 811
 812 TEST(TestS3selectFunctions, max)
 813 {
 814   std::string input;
 815   size_t size = 128;
 816   generate_columns_csv(input, size);
 817   const std::string input_query_1 = "select max(int(_1)), max(float(_2)) from stdin;";
 818
 819   std::string s3select_result_1 = run_s3select(input_query_1,input);
 820
 821   ASSERT_EQ(s3select_result_1,"127,128");
 822 }
 823
 824 int count_string(std::string in,std::string substr)
 825 {
 826     int count = 0;
 827     size_t nPos = in.find(substr, 0); // first occurrence
 828     while(nPos != std::string::npos)
 829     {
 830         count++;
 831         nPos = in.find(substr, nPos + 1);
 832     }
 833
 834     return count;
 835 }
 836
 837 void test_single_column_single_row(const char* input_query,const char* expected_result,const char * error_description = 0)
 838 {
 839     s3select s3select_syntax;
 840     auto status = s3select_syntax.parse_query(input_query);
 841     if(strcmp(expected_result,"#failure#") == 0 && status != 0)
 842     {
 843         ASSERT_TRUE(true);
 844         return;
 845     }
 846     else if (status != 0)
 847     {
 848         EXPECT_TRUE(false) << "fail to parse query: " << input_query;
 849         return;
 850     }
 851
 852     s3selectEngine::csv_object s3_csv_object(&s3select_syntax);
 853     std::string s3select_result;
 854     std::string json_result;
 855     std::string input;
 856     size_t size = 1;
 857     generate_csv(input, size);
 858
 859     std::string js = convert_to_json(input.c_str(), input.size());
 860     std::string query = input_query;
 861     const char* json_query = convert_query(query);
 862     run_json_query(json_query, js, json_result);
 863
 864 #ifdef _ARROW_EXIST
 865     csv_to_parquet(input);
 866     std::string parquet_result;
 867     run_query_on_parquet_file(input_query,PARQUET_FILENAME,parquet_result);
 868 #endif
 869
 870     s3_csv_object.m_csv_defintion.redundant_column = false;
 871     status = s3_csv_object.run_s3select_on_object(s3select_result, input.c_str(), input.size(),
 872         false, // dont skip first line
 873         false, // dont skip last line
 874         true   // aggregate call
 875         );
 876
 877     if(strcmp(expected_result,"#failure#") == 0)
 878     {
 879       if (status==0 && s3select_result.compare("#failure#")==0)
 880       {
 881           ASSERT_TRUE(false);
 882       }
 883       ASSERT_EQ(s3_csv_object.get_error_description(),error_description);
 884       return;
 885     }
 886
 887     ASSERT_EQ(status, 0);
 888 #ifdef _ARROW_EXIST
 889     parquet_csv_report_error(parquet_result,s3select_result);
 890 #endif
 891     json_csv_report_error(json_result, s3select_result);
 892     ASSERT_EQ(s3select_result, std::string(expected_result));
 893 }
 894
 895 TEST(TestS3selectFunctions, syntax_1)
 896 {
 897     //where not not (1<11) is not null;  syntax failure ; with parentheses it pass syntax i.e. /not (not (1<11)) is not null;/
 898     //where not 1<11  is null; syntax failure ; with parentheses it pass syntax i.e. not (1<11) is null;
 899     //where not (1); AST failure , expression result,any result implictly define true/false result
 900     //where not (1+1); AST failure
 901     //where not(not (1<11)) ; OK
 902     //where (not (1<11)) ; OK
 903     //where not (1<11) ; OK
 904   test_single_column_single_row("select count(0) from stdin where not (not (1<11)) is not null;","0");
 905   test_single_column_single_row("select count(0) from stdin where ((not (1<11)) is not null);","1");
 906   test_single_column_single_row("select count(0) from stdin where not(not (1<11));","1");
 907   test_single_column_single_row("select count(0) from stdin where not (1<11);","0");
 908   test_single_column_single_row("select count(0) from stdin where 1=1 or 2=2 and 4=4 and 2=4;","1");
 909   test_single_column_single_row("select count(0) from stdin where 2=2 and 4=4 and 2=4 or 1=1;","1");
 910 }
 911
 912 TEST(TestS3selectFunctions, binop_constant)
 913 {
 914     //bug-fix for expresion with constant value on the left side(the bug change the constant values between rows)
 915     s3select s3select_syntax;
 916     const std::string input_query = "select 10+1,20-12,2*3,128/2,29%5,2^10 from stdin;";
 917     auto status = s3select_syntax.parse_query(input_query.c_str());
 918     ASSERT_EQ(status, 0);
 919     s3selectEngine::csv_object s3_csv_object(&s3select_syntax);
 920     std::string s3select_result;
 921     std::string input;
 922     size_t size = 128;
 923     generate_csv(input, size);
 924     status = s3_csv_object.run_s3select_on_object(s3select_result, input.c_str(), input.size(),
 925         false, // dont skip first line
 926         false, // dont skip last line
 927         true   // aggregate call
 928         );
 929     ASSERT_EQ(status, 0);
 930
 931     int count = count_string(s3select_result,"11,8,6,64,4,1024");
 932     ASSERT_EQ(count,size);
 933 }
 934
 935 TEST(TestS3selectOperator, add)
 936 {
 937     const std::string input_query = "select -5 + 0.5 + -0.25 from stdin;" ;
 938     auto s3select_res = run_s3select(input_query);
 939     ASSERT_EQ(s3select_res, std::string("-4.75"));
 940 }
 941
 942 TEST(TestS3selectOperator, sub)
 943 {
 944     const std::string input_query = "select -5 - 0.5 - -0.25 from stdin;" ;
 945           auto s3select_res = run_s3select(input_query);
 946     ASSERT_EQ(s3select_res, std::string("-5.25"));
 947 }
 948
 949 TEST(TestS3selectOperator, mul)
 950 {
 951     const std::string input_query = "select -5 * (0.5 - -0.25) from stdin;" ;
 952           auto s3select_res = run_s3select(input_query);
 953     ASSERT_EQ(s3select_res, std::string("-3.75"));
 954 }
 955
 956 TEST(TestS3selectOperator, div)
 957 {
 958     const std::string input_query = "select -5 / (0.5 - -0.25) from stdin;" ;
 959           auto s3select_res = run_s3select(input_query);
 960     ASSERT_EQ(s3select_res, std::string("-6.666666666666667"));
 961 }
 962
 963 TEST(TestS3selectOperator, pow)
 964 {
 965     const std::string input_query = "select 5 ^ (0.5 - -0.25) from stdin;" ;
 966           auto s3select_res = run_s3select(input_query);
 967     ASSERT_EQ(s3select_res, std::string("3.34370152488211"));
 968 }
 969
 970 TEST(TestS3selectOperator, not_operator)
 971 {
 972     const std::string input_query = "select \"true\" from stdin where not ( (1+4) = 2 ) and (not(1 > (5*6)));" ;
 973           auto s3select_res = run_s3select(input_query);
 974     ASSERT_EQ(s3select_res, std::string("true"));
 975 }
 976
 977 TEST(TestS3SElect, from_stdin)
 978 {
 979     s3select s3select_syntax;
 980     const std::string input_query = "select * from stdin;";
 981     auto status = s3select_syntax.parse_query(input_query.c_str());
 982     ASSERT_EQ(status, 0);
 983     s3selectEngine::csv_object s3_csv_object(&s3select_syntax);
 984     std::string s3select_result;
 985     std::string input;
 986     size_t size = 128;
 987     generate_csv(input, size);
 988     std::string input_copy = input;
 989     status = s3_csv_object.run_s3select_on_object(s3select_result, input.c_str(), input.size(),
 990         false, // dont skip first line
 991         false, // dont skip last line
 992         true   // aggregate call
 993         );
 994     ASSERT_EQ(status, 0);
 995 }
 996
 997 TEST(TestS3SElect, from_valid_object)
 998 {
 999     s3select s3select_syntax;
1000     const std::string input_query = "select * from /objectname;";
1001     auto status = s3select_syntax.parse_query(input_query.c_str());
1002     ASSERT_EQ(status, 0);
1003     s3selectEngine::csv_object s3_csv_object(&s3select_syntax);
1004     std::string s3select_result;
1005     std::string input;
1006     size_t size = 128;
1007     generate_csv(input, size);
1008     status = s3_csv_object.run_s3select_on_object(s3select_result, input.c_str(), input.size(),
1009         false, // dont skip first line
1010         false, // dont skip last line
1011         true   // aggregate call
1012         );
1013     ASSERT_EQ(status, 0);
1014 }
1015
1016 TEST(TestS3SElect, from_invalid_object)
1017 {
1018     s3select s3select_syntax;
1019     const std::string input_query = "select sum(1) from file.txt;";
1020     auto status = s3select_syntax.parse_query(input_query.c_str());
1021     ASSERT_EQ(status, -1);
1022     auto s3select_res = run_s3select(input_query);
1023     ASSERT_EQ(s3select_res,failure_sign);
1024 }
1025
1026 TEST(TestS3selectFunctions, avg)
1027 {
1028   std::string input;
1029   size_t size = 128;
1030   generate_columns_csv(input, size);
1031   const std::string input_query_1 = "select avg(int(_1)) from stdin;";
1032
1033   std::string s3select_result_1 = run_s3select(input_query_1,input);
1034
1035   ASSERT_EQ(s3select_result_1,"63.5");
1036 }
1037
1038 TEST(TestS3selectFunctions, avgzero)
1039 {
1040     s3select s3select_syntax;
1041     const std::string input_query = "select avg(int(_1)) from stdin;";
1042     auto status = s3select_syntax.parse_query(input_query.c_str());
1043     ASSERT_EQ(status, 0);
1044     s3selectEngine::csv_object s3_csv_object(&s3select_syntax);
1045     std::string s3select_result;
1046     std::string input;
1047     size_t size = 0;
1048     generate_csv(input, size);
1049     status = s3_csv_object.run_s3select_on_object(s3select_result, input.c_str(), input.size(),
1050         false, // dont skip first line
1051         false, // dont skip last line
1052         true   // aggregate call
1053         );
1054     ASSERT_EQ(status, 0);
1055     ASSERT_EQ(s3select_result, std::string("null"));
1056 }
1057
1058 TEST(TestS3selectFunctions, floatavg)
1059 {
1060   std::string input;
1061   size_t size = 128;
1062   generate_columns_csv(input, size);
1063
1064   const std::string input_query_1 = "select avg(float(_1)) from stdin;";
1065
1066   std::string s3select_result_1 = run_s3select(input_query_1,input);
1067
1068   ASSERT_EQ(s3select_result_1,"63.5");
1069 }
1070
1071 TEST(TestS3selectFunctions, case_when_condition_multiplerows)
1072 {
1073   std::string input;
1074   size_t size = 10000;
1075   generate_rand_columns_csv(input, size);
1076   const std::string input_query = "select case when cast(_3 as int)>99 and cast(_3 as int)<1000 then \"case-1-1\" else \"case-2-2\" end from s3object;";
1077
1078   std::string s3select_result = run_s3select(input_query,input);
1079
1080   const std::string input_query_2 = "select case when char_length(_3)=3 then \"case-1-1\" else \"case-2-2\" end from s3object;";
1081
1082   std::string s3select_result_2 = run_s3select(input_query_2,input);
1083
1084   ASSERT_EQ(s3select_result,s3select_result_2);
1085 }
1086
1087 TEST(TestS3selectFunctions, case_value_multiplerows)
1088 {
1089   std::string input;
1090   size_t size = 10000;
1091   generate_rand_columns_csv(input, size);
1092   std::string input_query = "select case cast(_1 as int) when cast(_2 as int) then \"case-1-1\" else \"case-2-2\" end from s3object;";
1093
1094   std::string s3select_result = run_s3select(input_query,input);
1095
1096   const std::string input_query_2 = "select case when cast(_1 as int) = cast(_2 as int) then \"case-1-1\" else \"case-2-2\" end from s3object;";
1097
1098   std::string s3select_result_2 = run_s3select(input_query_2,input);
1099
1100   ASSERT_EQ(s3select_result,s3select_result_2);
1101
1102   //the following test query, validates correct build of the AST.
1103   //the query contain various combinations, such as nested case-when-else, aggregation for case-when
1104   //binary-operation with aggregation function.
1105   input_query.assign("select 2+ sum(case when int(_1)>100 then (case when int(_1)>100 then 1 else 0 end) else 0 end) + \
1106                                           sum(case when int(_1)<=100 then (case when int(_1)<=100 then 1 else 0 end) else 0 end) , \
1107                                           count(0) , \
1108                                           sum(case when int(_2)>100 then (case when int(_2)>100 then 1 else 0 end) else 0 end) + \
1109                                           sum(case when int(_2)<=100 then (case when int(_2)<=100 then 1 else 0 end) else 0 end) + 1  from s3object;");
1110
1111   s3select_result = run_s3select(input_query,input);
1112
1113   std::string expected_result = std::to_string(size+2) + "," + std::to_string(size) + "," + std::to_string(size+1);
1114   ASSERT_EQ(s3select_result,expected_result);
1115
1116
1117   //aggregation function on top of nested case-when, case-when statement contains binary operation.
1118   input_query.assign("select sum(case when int(_2)>100 then (case when int(_2)>100 then 1 else 0 end)*2 else 0 end+1) + \
1119                   sum(case when int(_2)<=100 then (case when int(_2)<=100 then 1 else 0 end)*2 else 0 end) from s3object;");
1120
1121   s3select_result = run_s3select(input_query,input);
1122
1123   expected_result = std::to_string(size*3);
1124   ASSERT_EQ(s3select_result,expected_result);
1125 }
1126
1127 TEST(TestS3selectFunctions, nested_call_aggregate_with_non_aggregate )
1128 {
1129   std::string input;
1130   size_t size = 128;
1131
1132   generate_fix_columns_csv(input, size);
1133
1134   const std::string input_query = "select sum(cast(_1 as int)),max(cast(_3 as int)),substring('abcdefghijklm',(2-1)*3+sum(cast(_1 as int))/sum(cast(_1 as int))+1,(count(0) + count(0))/count(0)) from stdin;";
1135
1136   std::string s3select_result = run_s3select(input_query,input);
1137
1138   ASSERT_EQ(s3select_result,"128,3,ef");
1139 }
1140
1141 TEST(TestS3selectFunctions, cast_1 )
1142 {
1143   std::string input;
1144   size_t size = 10000;
1145   generate_rand_columns_csv(input, size);
1146   const std::string input_query = "select count(0) from s3object where cast(_3 as int)>99 and cast(_3 as int)<1000;";
1147
1148   std::string s3select_result = run_s3select(input_query,input);
1149
1150   const std::string input_query_2 = "select count(0) from s3object where char_length(_3)=3;";
1151
1152   std::string s3select_result_2 = run_s3select(input_query_2,input);
1153
1154   ASSERT_EQ(s3select_result,s3select_result_2);
1155 }
1156
1157 TEST(TestS3selectFunctions, null_column )
1158 {
1159   std::string input;
1160   size_t size = 10000;
1161
1162   generate_rand_columns_csv_with_null(input, size);
1163
1164   const std::string input_query = "select count(0) from s3object where _3 is null;";
1165
1166   std::string s3select_result = run_s3select(input_query,input);
1167
1168   ASSERT_NE(s3select_result,failure_sign);
1169
1170   const std::string input_query_2 = "select count(0) from s3object where nullif(_3,null) is null;";
1171
1172   std::string s3select_result_2 = run_s3select(input_query_2,input);
1173
1174   ASSERT_NE(s3select_result_2,failure_sign);
1175
1176   ASSERT_EQ(s3select_result,s3select_result_2);
1177 }
1178
1179 TEST(TestS3selectFunctions, count_operation)
1180 {
1181   std::string input;
1182   size_t size = 10000;
1183   generate_rand_columns_csv(input, size);
1184   const std::string input_query = "select count(0) from s3object;";
1185
1186   std::string s3select_result = run_s3select(input_query,input);
1187
1188   ASSERT_NE(s3select_result,failure_sign);
1189
1190   ASSERT_EQ(s3select_result,"10000");
1191 }
1192
1193 TEST(TestS3selectFunctions, nullif_expressions)
1194 {
1195   std::string input;
1196   size_t size = 10000;
1197   generate_rand_columns_csv(input, size);
1198   const std::string input_query_1 = "select count(0) from s3object where nullif(_1,_2) is null;";
1199
1200   std::string s3select_result_1 = run_s3select(input_query_1,input);
1201
1202   ASSERT_NE(s3select_result_1,failure_sign);
1203
1204   const std::string input_query_2 = "select count(0) from s3object where _1 = _2;";
1205
1206   std::string s3select_result_2 = run_s3select(input_query_2,input);
1207
1208   ASSERT_EQ(s3select_result_1, s3select_result_2);
1209
1210   const std::string input_query_3 = "select count(0) from s3object where not nullif(_1,_2) is null;";
1211
1212   std::string s3select_result_3 = run_s3select(input_query_3,input);
1213
1214   ASSERT_NE(s3select_result_3,failure_sign);
1215
1216   const std::string input_query_4 = "select count(0) from s3object where _1 != _2;";
1217
1218   std::string s3select_result_4 = run_s3select(input_query_4,input);
1219
1220   ASSERT_EQ(s3select_result_3, s3select_result_4);
1221
1222   const std::string input_query_5 = "select count(0) from s3object where nullif(_1,_2) = _1 ;";
1223
1224   std::string s3select_result_5 = run_s3select(input_query_5,input);
1225
1226   ASSERT_NE(s3select_result_5,failure_sign);
1227
1228   const std::string input_query_6 = "select count(0) from s3object where _1 != _2;";
1229
1230   std::string s3select_result_6 = run_s3select(input_query_6,input);
1231
1232   ASSERT_EQ(s3select_result_5, s3select_result_6);
1233 }
1234
1235 TEST(TestS3selectFunctions, lower_upper_expressions)
1236 {
1237   std::string input;
1238   size_t size = 1;
1239   generate_csv(input, size);
1240   const std::string input_query_1 = "select lower(\"AB12cd$$\") from s3object;";
1241
1242   std::string s3select_result_1 = run_s3select(input_query_1,input);
1243
1244   ASSERT_NE(s3select_result_1,failure_sign);
1245
1246   ASSERT_EQ(s3select_result_1, "ab12cd$$\n");
1247
1248   const std::string input_query_2 = "select upper(\"ab12CD$$\") from s3object;";
1249
1250   std::string s3select_result_2 = run_s3select(input_query_2,input);
1251
1252   ASSERT_NE(s3select_result_2,failure_sign);
1253
1254   ASSERT_EQ(s3select_result_2, "AB12CD$$\n");
1255 }
1256
1257 TEST(TestS3selectFunctions, in_expressions)
1258 {
1259   std::string input;
1260   size_t size = 10000;
1261   generate_rand_columns_csv(input, size);
1262   const std::string input_query_1 = "select int(_1) from s3object where int(_1) in(1);";
1263
1264   std::string s3select_result_1 = run_s3select(input_query_1,input);
1265
1266   ASSERT_NE(s3select_result_1,failure_sign);
1267
1268   const std::string input_query_2 = "select int(_1) from s3object where int(_1) = 1;";
1269
1270   std::string s3select_result_2 = run_s3select(input_query_2,input);
1271
1272   ASSERT_EQ(s3select_result_1, s3select_result_2);
1273
1274   const std::string input_query_3 = "select int(_1) from s3object where int(_1) in(1,0);";
1275
1276   std::string s3select_result_3 = run_s3select(input_query_3,input);
1277
1278   ASSERT_NE(s3select_result_3,failure_sign);
1279
1280   const std::string input_query_4 = "select int(_1) from s3object where int(_1) = 1 or int(_1) = 0;";
1281
1282   std::string s3select_result_4 = run_s3select(input_query_4,input);
1283
1284   ASSERT_EQ(s3select_result_3, s3select_result_4);
1285
1286   const std::string input_query_5 = "select int(_2) from s3object where int(_2) in(1,0,2);";
1287
1288   std::string s3select_result_5 = run_s3select(input_query_5,input);
1289
1290   ASSERT_NE(s3select_result_5,failure_sign);
1291
1292   const std::string input_query_6 = "select int(_2) from s3object where int(_2) = 1 or int(_2) = 0 or int(_2) = 2;";
1293
1294   std::string s3select_result_6 = run_s3select(input_query_6,input);
1295
1296   ASSERT_EQ(s3select_result_5, s3select_result_6);
1297
1298   const std::string input_query_7 = "select int(_2) from s3object where int(_2)*2 in(int(_3)*2,int(_4)*3,int(_5)*5);";
1299
1300   std::string s3select_result_7 = run_s3select(input_query_7,input);
1301
1302   ASSERT_NE(s3select_result_7,failure_sign);
1303
1304   const std::string input_query_8 = "select int(_2) from s3object where int(_2)*2 = int(_3)*2 or int(_2)*2 = int(_4)*3 or int(_2)*2 = int(_5)*5;";
1305
1306   std::string s3select_result_8 = run_s3select(input_query_8,input);
1307
1308   ASSERT_EQ(s3select_result_7, s3select_result_8);
1309
1310   const std::string input_query_9 = "select int(_1) from s3object where character_length(_1) = 2 and substring(_1,2,1) in (\"3\");";
1311
1312   std::string s3select_result_9 = run_s3select(input_query_9,input);
1313
1314   ASSERT_NE(s3select_result_9,failure_sign);
1315
1316   const std::string input_query_10 = "select int(_1) from s3object where _1 like \"_3\";";
1317
1318   const char* json_query_10 = "select int(_1.c1) from s3object[*].root where _1.c1 like \"_3\";";
1319
1320   std::string s3select_result_10 = run_s3select(input_query_10,input,json_query_10);
1321
1322   ASSERT_EQ(s3select_result_9, s3select_result_10);
1323 }
1324
1325 TEST(TestS3selectFunctions, test_coalesce_expressions)
1326 {
1327   std::string input;
1328   size_t size = 10000;
1329   generate_rand_columns_csv(input, size);
1330
1331   const std::string input_query_1 = "select count(0) from s3object where char_length(_3)>2 and char_length(_4)>2 and cast(substring(_3,1,2) as int) = cast(substring(_4,1,2) as int);";
1332
1333   std::string s3select_result_1 = run_s3select(input_query_1,input);
1334
1335   ASSERT_NE(s3select_result_1,failure_sign);
1336
1337   const std::string input_query_2 = "select count(0) from s3object where cast(_3 as int)>99 and cast(_4 as int)>99 and coalesce(nullif(cast(substring(_3,1,2) as int),cast(substring(_4,1,2) as int)),7) = 7;";
1338
1339   std::string s3select_result_2 = run_s3select(input_query_2,input);
1340
1341   ASSERT_EQ(s3select_result_1, s3select_result_2);
1342
1343   const std::string input_query_3 = "select coalesce(nullif(_5,_5),nullif(_1,_1),_2) from s3object;";
1344
1345   std::string s3select_result_3 = run_s3select(input_query_3,input);
1346
1347   ASSERT_NE(s3select_result_3,failure_sign);
1348
1349   const std::string input_query_4 = "select coalesce(_2) from s3object;";
1350
1351   std::string s3select_result_4 = run_s3select(input_query_4,input);
1352
1353   ASSERT_EQ(s3select_result_3, s3select_result_4);
1354 }
1355
1356 TEST(TestS3selectFunctions, test_cast_expressions)
1357 {
1358   std::string input;
1359   size_t size = 10000;
1360   generate_rand_columns_csv(input, size);
1361   const std::string input_query_1 = "select count(0) from s3object where cast(_3 as int)>999;";
1362
1363   std::string s3select_result_1 = run_s3select(input_query_1,input);
1364
1365   ASSERT_NE(s3select_result_1,failure_sign);
1366
1367   const std::string input_query_2 = "select count(0) from s3object where char_length(_3)>3;";
1368
1369   std::string s3select_result_2 = run_s3select(input_query_2,input);
1370
1371   ASSERT_EQ(s3select_result_1, s3select_result_2);
1372
1373   const std::string input_query_3 = "select count(0) from s3object where char_length(_3)=3;";
1374
1375   std::string s3select_result_3 = run_s3select(input_query_3,input);
1376
1377   ASSERT_NE(s3select_result_3,failure_sign);
1378
1379   const std::string input_query_4 = "select count(0) from s3object where cast(_3 as int)>99 and cast(_3 as int)<1000;";
1380
1381   std::string s3select_result_4 = run_s3select(input_query_4,input);
1382
1383   ASSERT_EQ(s3select_result_3, s3select_result_4);
1384
1385   //testing the decimal operator for precision setting
1386   const std::string input_query_5 = "select cast(1.123456789 as decimal(9,1)) from s3object limit 1;";
1387
1388   std::string s3select_result_5 = run_s3select(input_query_5,input);
1389
1390   ASSERT_EQ(s3select_result_5, "1.123456789\n");
1391 }
1392
1393 TEST(TestS3selectFunctions, test_version)
1394 {
1395   std::string input;
1396   size_t size = 1;
1397   generate_rand_columns_csv(input, size);
1398   const std::string input_query_1 = "select version() from stdin;";
1399
1400   std::string s3select_result_1 = run_s3select(input_query_1,input);
1401
1402   ASSERT_NE(s3select_result_1,failure_sign);
1403
1404   ASSERT_EQ(s3select_result_1, "41.a\n");
1405 }
1406
1407 TEST(TestS3selectFunctions, multirow_datetime_to_string_constant)
1408 {
1409   std::string input, expected_res;
1410   std::string format = "yyyysMMMMMdddSSSSSSSSSSSMMMM HHa:m -:-";
1411   size_t size = 100;
1412
1413   generate_rand_csv_datetime_to_string(input, expected_res, size);
1414
1415   const std::string input_query = "select to_string(to_timestamp(_1), \'" + format + "\') from s3object;";
1416   std::string s3select_result = run_s3select(input_query, input);
1417   EXPECT_EQ(s3select_result, expected_res);
1418 }
1419
1420 TEST(TestS3selectFunctions, multirow_datetime_to_string_dynamic)
1421 {
1422   std::string input, expected_res;
1423   size_t size = 100;
1424
1425   generate_rand_csv_datetime_to_string(input, expected_res, size, false);
1426
1427   const std::string input_query = "select to_string(to_timestamp(_1), _2) from s3object;";
1428   std::string s3select_result = run_s3select(input_query, input);
1429   EXPECT_EQ(s3select_result, expected_res);
1430 }
1431
1432 TEST(TestS3selectFunctions, backtick_on_timestamp)
1433 {
1434   const std::string input = "1994-11-21T11:49:23Z\n";
1435   const std::string input_query = "select count(0) from s3object where cast(_1 as timestamp) = `1994-11-21T11:49:23Z`;";
1436   std::string s3select_result = run_s3select(input_query, input);
1437   EXPECT_EQ(s3select_result, "1");
1438 }
1439
1440 TEST(TestS3selectFunctions, test_date_time_expressions)
1441 {
1442   std::string input;
1443   size_t size = 10000;
1444   generate_rand_columns_csv_datetime(input, size);
1445   const std::string input_query_1 = "select count(0) from s3object where extract(year from to_timestamp(_1)) > 1950 and extract(year from to_timestamp(_1)) < 1960;";
1446
1447   std::string s3select_result_1 = run_s3select(input_query_1,input);
1448
1449   ASSERT_NE(s3select_result_1,failure_sign);
1450
1451   const std::string input_query_2 = "select count(0) from s3object where int(substring(_1,1,4))>1950 and int(substring(_1,1,4))<1960;";
1452
1453   std::string s3select_result_2 = run_s3select(input_query_2,input);
1454
1455   ASSERT_EQ(s3select_result_1, s3select_result_2);
1456
1457   const std::string input_query_3 = "select count(0) from s3object where date_diff(month,to_timestamp(_1),date_add(month,2,to_timestamp(_1)) ) = 2;";
1458
1459   std::string s3select_result_3 = run_s3select(input_query_3,input);
1460
1461   ASSERT_NE(s3select_result_3,failure_sign);
1462
1463   const std::string input_query_4 = "select count(0) from s3object;";
1464
1465   std::string s3select_result_4 = run_s3select(input_query_4,input);
1466
1467   ASSERT_NE(s3select_result_4,failure_sign);
1468
1469   ASSERT_EQ(s3select_result_3, s3select_result_4);
1470
1471   const std::string input_query_5 = "select count(0) from  stdin where date_diff(year,to_timestamp(_1),date_add(day, 366 ,to_timestamp(_1))) = 1;";
1472
1473   std::string s3select_result_5 = run_s3select(input_query_5,input);
1474
1475   ASSERT_EQ(s3select_result_5, s3select_result_4);
1476
1477   const std::string input_query_6 = "select count(0) from  stdin where date_diff(hour,utcnow(),date_add(day,1,utcnow())) = 24;";
1478
1479   std::string s3select_result_6 = run_s3select(input_query_6,input);
1480
1481   ASSERT_EQ(s3select_result_6, s3select_result_4);
1482
1483   std::string input_query_7 = "select extract(year from to_timestamp(_1)) from stdin;";
1484   std::string s3select_result_7 = run_s3select(input_query_7, input);
1485   ASSERT_NE(s3select_result_7, failure_sign);
1486   std::string input_query_8 = "select substring(_1, 1, 4) from stdin;";
1487   std::string s3select_result_8 = run_s3select(input_query_8, input);
1488   ASSERT_NE(s3select_result_8, failure_sign);
1489   EXPECT_EQ(s3select_result_7, s3select_result_8);
1490
1491   std::string input_query_9 = "select to_timestamp(_1) from stdin where extract(month from to_timestamp(_1)) = 5;";
1492   std::string s3select_result_9 = run_s3select(input_query_9, input);
1493   ASSERT_NE(s3select_result_9, failure_sign);
1494   std::string input_query_10 = "select substring(_1, 1, char_length(_1)) from stdin where _1 like \'____-05%\';";
1495   std::string s3select_result_10 = run_s3select(input_query_10, input);
1496   ASSERT_NE(s3select_result_10, failure_sign);
1497   EXPECT_EQ(s3select_result_9, s3select_result_10);
1498
1499   std::string input_query_11 = "select _1 from stdin where extract(month from to_timestamp(_1)) = 5 or extract(month from to_timestamp(_1)) = 6;";
1500   std::string s3select_result_11 = run_s3select(input_query_11,input);
1501   ASSERT_NE(s3select_result_11, failure_sign);
1502   std::string input_query_12 = "select _1 from stdin where to_string(to_timestamp(_1), 'MMMM') in ('May', 'June');";
1503   std::string s3select_result_12 = run_s3select(input_query_12,input);
1504   ASSERT_NE(s3select_result_12, failure_sign);
1505   EXPECT_EQ(s3select_result_11, s3select_result_12);
1506
1507   std::string input_query_13 = "select to_string(to_timestamp(_1), 'y,M,H,m') from stdin where cast(to_string(to_timestamp(_1), 'd') as int) >= 1 and cast(to_string(to_timestamp(_1), 'd') as int) <= 10;";
1508   std::string s3select_result_13 = run_s3select(input_query_13, input);
1509   ASSERT_NE(s3select_result_13, failure_sign);
1510   std::string input_query_14 = "select extract(year from to_timestamp(_1)), extract(month from to_timestamp(_1)), extract(hour from to_timestamp(_1)), extract(minute from to_timestamp(_1)) from stdin where  int(substring(_1, 9, 2)) between 1 and 10;";
1511   std::string s3select_result_14 = run_s3select(input_query_14, input);
1512   ASSERT_NE(s3select_result_14, failure_sign);
1513   EXPECT_EQ(s3select_result_13, s3select_result_14);
1514
1515   std::string input_query_15 = "select to_string(to_timestamp(_1), 'y,M,H,m') from stdin where cast(to_string(to_timestamp(_1), 'd') as int) < 1 or cast(to_string(to_timestamp(_1), 'd') as int) > 10;";
1516   std::string s3select_result_15 = run_s3select(input_query_15, input);
1517   ASSERT_NE(s3select_result_15, failure_sign);
1518   std::string input_query_16 = "select extract(year from to_timestamp(_1)), extract(month from to_timestamp(_1)), extract(hour from to_timestamp(_1)), extract(minute from to_timestamp(_1)) from stdin where  int(substring(_1, 9, 2)) not between 1 and 10;";
1519   std::string s3select_result_16 = run_s3select(input_query_16, input);
1520   ASSERT_NE(s3select_result_16, failure_sign);
1521   EXPECT_EQ(s3select_result_15, s3select_result_16);
1522 }
1523
1524 TEST(TestS3selectFunctions, test_like_expressions)
1525 {
1526   std::string input, input1;
1527   size_t size = 10000;
1528   generate_csv(input, size);
1529   const std::string input_query_1 = "select count(0) from stdin where _4 like \"%ar\";";
1530
1531   std::string s3select_result_1 = run_s3select(input_query_1,input);
1532
1533   ASSERT_NE(s3select_result_1,failure_sign);
1534
1535   const std::string input_query_2 = "select count(0) from stdin where substring(_4,char_length(_4),1) = \"r\" and substring(_4,char_length(_4)-1,1) = \"a\";";
1536
1537   std::string s3select_result_2 = run_s3select(input_query_2,input);
1538
1539   ASSERT_EQ(s3select_result_1, s3select_result_2);
1540
1541   generate_csv_like(input1, size);
1542
1543   const std::string input_query_3 = "select count(0) from stdin where _1 like \"%aeio%\";";
1544
1545   std::string s3select_result_3 = run_s3select(input_query_3,input1);
1546
1547   ASSERT_NE(s3select_result_3,failure_sign);
1548
1549   const std::string input_query_4 = "select count(0) from stdin where substring(_1,4,4) = \"aeio\";";
1550
1551   std::string s3select_result_4 = run_s3select(input_query_4,input1);
1552
1553   ASSERT_EQ(s3select_result_3, s3select_result_4);
1554
1555   const std::string input_query_5 = "select count(0) from stdin where _1 like \"%r[r-s]\";";
1556
1557   std::string s3select_result_5 = run_s3select(input_query_5,input);
1558
1559   ASSERT_NE(s3select_result_5,failure_sign);
1560
1561   const std::string input_query_6 = "select count(0) from stdin where substring(_1,char_length(_1),1) between \"r\" and \"s\" and substring(_1,char_length(_1)-1,1) = \"r\";";
1562
1563   std::string s3select_result_6 = run_s3select(input_query_6,input);
1564
1565   ASSERT_EQ(s3select_result_5, s3select_result_6);
1566
1567   const std::string input_query_7 = "select count(0) from stdin where _1 like \"%br_\";";
1568
1569   std::string s3select_result_7 = run_s3select(input_query_7,input);
1570
1571   ASSERT_NE(s3select_result_7,failure_sign);
1572
1573   const std::string input_query_8 = "select count(0) from stdin where substring(_1,char_length(_1)-1,1) = \"r\" and substring(_1,char_length(_1)-2,1) = \"b\";";
1574
1575   std::string s3select_result_8 = run_s3select(input_query_8,input);
1576
1577   ASSERT_EQ(s3select_result_7, s3select_result_8);
1578
1579   const std::string input_query_9 = "select count(0) from stdin where _1 like \"f%s\";";
1580
1581   std::string s3select_result_9 = run_s3select(input_query_9,input);
1582
1583   ASSERT_NE(s3select_result_9,failure_sign);
1584
1585   const std::string input_query_10 = "select count(0) from stdin where substring(_1,char_length(_1),1) = \"s\" and substring(_1,1,1) = \"f\";";
1586
1587   std::string s3select_result_10 = run_s3select(input_query_10,input);
1588
1589   ASSERT_EQ(s3select_result_9, s3select_result_10);
1590 }
1591
1592 TEST(TestS3selectFunctions, test_when_then_else_expressions)
1593 {
1594   std::string input;
1595   size_t size = 10000;
1596   generate_rand_columns_csv(input, size);
1597   const std::string input_query_1 = "select case when cast(_1 as int)>100 and cast(_1 as int)<200 then \"a\" when cast(_1 as int)>200 and cast(_1 as int)<300 then \"b\" else \"c\" end from s3object;";
1598
1599   std::string s3select_result_1 = run_s3select(input_query_1,input);
1600
1601   ASSERT_NE(s3select_result_1,failure_sign);
1602
1603   int count1 = std::count(s3select_result_1.begin(), s3select_result_1.end(),'a') ;
1604   int count2 = std::count(s3select_result_1.begin(), s3select_result_1.end(), 'b');
1605   int count3 = std::count(s3select_result_1.begin(), s3select_result_1.end(), 'c');
1606
1607   const std::string input_query_2 = "select count(0) from s3object where  cast(_1 as int)>100 and cast(_1 as int)<200;";
1608
1609   std::string s3select_result_2 = run_s3select(input_query_2,input);
1610
1611   ASSERT_NE(s3select_result_2,failure_sign);
1612
1613   ASSERT_EQ(stoi(s3select_result_2), count1);
1614
1615   const std::string input_query_3 = "select count(0) from s3object where  cast(_1 as int)>200 and cast(_1 as int)<300;";
1616
1617   std::string s3select_result_3 = run_s3select(input_query_3,input);
1618
1619   ASSERT_NE(s3select_result_3,failure_sign);
1620
1621   ASSERT_EQ(stoi(s3select_result_3), count2);
1622
1623   const std::string input_query_4 = "select count(0) from s3object where  cast(_1 as int)<=100 or cast(_1 as int)>=300 or cast(_1 as int)=200;";
1624
1625   std::string s3select_result_4 = run_s3select(input_query_4,input);
1626
1627   ASSERT_NE(s3select_result_4,failure_sign);
1628
1629   ASSERT_EQ(stoi(s3select_result_4), count3);
1630 }
1631
1632 TEST(TestS3selectFunctions, test_case_value_when_then_else_expressions)
1633 {
1634   std::string input;
1635   size_t size = 10000;
1636   generate_rand_columns_csv(input, size);
1637   const std::string input_query_1 = "select case cast(_1 as int) + 1 when 2 then \"a\" when 3  then \"b\" else \"c\" end from s3object;";
1638
1639   std::string s3select_result_1 = run_s3select(input_query_1,input);
1640
1641   ASSERT_NE(s3select_result_1,failure_sign);
1642
1643   int count1 = std::count(s3select_result_1.begin(), s3select_result_1.end(),'a') ;
1644   int count2 = std::count(s3select_result_1.begin(), s3select_result_1.end(), 'b');
1645   int count3 = std::count(s3select_result_1.begin(), s3select_result_1.end(), 'c');
1646
1647   const std::string input_query_2 = "select count(0) from s3object where  cast(_1 as int) + 1 = 2;";
1648
1649   std::string s3select_result_2 = run_s3select(input_query_2,input);
1650
1651   ASSERT_NE(s3select_result_2,failure_sign);
1652
1653   ASSERT_EQ(stoi(s3select_result_2), count1);
1654
1655   const std::string input_query_3 = "select count(0) from s3object where  cast(_1 as int) + 1 = 3;";
1656
1657   std::string s3select_result_3 = run_s3select(input_query_3,input);
1658
1659   ASSERT_NE(s3select_result_3,failure_sign);
1660
1661   ASSERT_EQ(stoi(s3select_result_3), count2);
1662
1663   const std::string input_query_4 = "select count(0) from s3object where  cast(_1 as int) + 1 < 2 or cast(_1 as int) + 1 > 3;";
1664
1665   std::string s3select_result_4 = run_s3select(input_query_4,input);
1666
1667   ASSERT_NE(s3select_result_4,failure_sign);
1668
1669   ASSERT_EQ(stoi(s3select_result_4), count3);
1670 }
1671
1672 TEST(TestS3selectFunctions, test_trim_expressions)
1673 {
1674   std::string input;
1675   size_t size = 10000;
1676   generate_csv_trim(input, size);
1677   const std::string input_query_1 = "select count(0) from stdin where trim(_1) = \"aeiou\";";
1678
1679   std::string s3select_result_1 = run_s3select(input_query_1,input);
1680
1681   ASSERT_NE(s3select_result_1,failure_sign);
1682
1683   const std::string input_query_2 = "select count(0) from stdin where substring(_1 from 6 for 5) = \"aeiou\";";
1684
1685   std::string s3select_result_2 = run_s3select(input_query_2,input);
1686
1687   ASSERT_EQ(s3select_result_1, s3select_result_2);
1688
1689   const std::string input_query_3 = "select count(0) from stdin where trim(both from _1) = \"aeiou\";";
1690
1691   std::string s3select_result_3 = run_s3select(input_query_3,input);
1692
1693   ASSERT_NE(s3select_result_3,failure_sign);
1694
1695   const std::string input_query_4 = "select count(0) from stdin where substring(_1,6,5) = \"aeiou\";";
1696
1697   std::string s3select_result_4 = run_s3select(input_query_4,input);
1698
1699   ASSERT_EQ(s3select_result_3, s3select_result_4);
1700 }
1701
1702 TEST(TestS3selectFunctions, truefalse)
1703 {
1704   test_single_column_single_row("select 2 from s3object where true or false;","2\n");
1705   test_single_column_single_row("select 2 from s3object where true or true;","2\n");
1706   test_single_column_single_row("select 2 from s3object where null or true ;","2\n");
1707   test_single_column_single_row("select 2 from s3object where true and true;","2\n");
1708   test_single_column_single_row("select 2 from s3object where true = true ;","2\n");
1709   test_single_column_single_row("select 2 from stdin where 1<2 = true;","2\n");
1710   test_single_column_single_row("select 2 from stdin where 1=1 = true;","2\n");
1711   test_single_column_single_row("select 2 from stdin where false=false = true;","2\n");
1712   test_single_column_single_row("select 2 from s3object where false or true;","2\n");
1713   test_single_column_single_row("select true,false from s3object where false = false;","true,false\n");
1714   test_single_column_single_row("select count(0) from s3object where not (1>2) = true;","1");
1715   test_single_column_single_row("select count(0) from s3object where not (1>2) = (not false);","1");
1716   test_single_column_single_row("select (true or false) from s3object;","true\n");
1717   test_single_column_single_row("select (true and true) from s3object;","true\n");
1718   test_single_column_single_row("select (true and null) from s3object;","null\n");
1719   test_single_column_single_row("select (false or false) from s3object;","false\n");
1720   test_single_column_single_row("select (not true) from s3object;","false\n");
1721   test_single_column_single_row("select (not 1 > 2) from s3object;","true\n");
1722   test_single_column_single_row("select (not 1 > 2) as a1,cast(a1 as int)*4 from s3object;","true,4\n");
1723   test_single_column_single_row("select (1 > 2) from s3object;","false\n");
1724   test_single_column_single_row("select case when (nullif(3,3) is null) = true then \"case-1-1\" else \"case-2-2\"  end, case when (\"a\" in (\"a\",\"b\")) = true then \"case-3-3\" else \"case-4-4\" end, case when 1>3 then \"case_5_5\" else \"case-6-6\" end from s3object where (3*3 = 9);","case-1-1,case-3-3,case-6-6\n");
1725 }
1726
1727 TEST(TestS3selectFunctions, boolcast)
1728 {
1729   test_single_column_single_row("select cast(5 as bool) from s3object;","true\n");
1730   test_single_column_single_row("select cast(0 as bool) from s3object;","false\n");
1731   test_single_column_single_row("select cast(true as bool) from s3object;","true\n");
1732   test_single_column_single_row("select cast('a' as bool) from s3object;","false\n");
1733 }
1734
1735 TEST(TestS3selectFunctions, floatcast)
1736 {
1737   test_single_column_single_row("select cast('1234a' as float) from s3object;","#failure#","extra characters after the number");
1738   test_single_column_single_row("select cast('a1234' as float) from s3object;","#failure#","text cannot be converted to a number");
1739   test_single_column_single_row("select cast('999e+999' as float) from s3object;","#failure#","converted value would fall out of the range of the result type!");
1740 }
1741
1742 TEST(TestS3selectFunctions, intcast)
1743 {
1744   test_single_column_single_row("select cast('1234a' as int) from s3object;","#failure#","extra characters after the number");
1745   test_single_column_single_row("select cast('a1234' as int) from s3object;","#failure#","text cannot be converted to a number");
1746   test_single_column_single_row("select cast('9223372036854775808' as int) from s3object;","#failure#","converted value would fall out of the range of the result type!");
1747   test_single_column_single_row("select cast('-9223372036854775809' as int) from s3object;","#failure#","converted value would fall out of the range of the result type!");
1748 }
1749
1750 TEST(TestS3selectFunctions, predicate_as_projection_column)
1751 {
1752   std::string input;
1753   size_t size = 10000;
1754   generate_rand_columns_csv(input, size);
1755   const std::string input_query = "select (int(_2) between int(_3) and int(_4)) from s3object where int(_2)>int(_3) and int(_2)<int(_4);";
1756
1757   std::string s3select_result = run_s3select(input_query,input);
1758
1759   ASSERT_NE(s3select_result,failure_sign);
1760
1761   auto count = std::count(s3select_result.begin(), s3select_result.end(), '0');
1762
1763   ASSERT_EQ(count,0);
1764
1765   const std::string input_query_1 = "select (nullif(_1,_2) is null) from s3object where _1 = _2;";
1766
1767   std::string s3select_result_1 = run_s3select(input_query_1,input);
1768
1769   ASSERT_NE(s3select_result_1,failure_sign);
1770
1771   auto count_1 = std::count(s3select_result_1.begin(), s3select_result_1.end(), '0');
1772
1773   ASSERT_EQ(count_1,0);
1774
1775   const std::string input_query_2 = "select (nullif(_1,_2) is not null) from s3object where _1 != _2;";
1776
1777   std::string s3select_result_2 = run_s3select(input_query_2,input);
1778
1779   ASSERT_NE(s3select_result_2,failure_sign);
1780
1781   auto count_2 = std::count(s3select_result_2.begin(), s3select_result_2.end(), '0');
1782
1783   ASSERT_EQ(count_2,0);
1784
1785   const std::string input_query_3 = "select (_1 like \"_3\") from s3object where character_length(_1) = 2 and substring(_1,2,1) in (\"3\");";
1786
1787   const char* json_query_3 = "select (_1.c1 like \"_3\") from s3object[*].root where character_length(_1.c1) = 2 and substring(_1.c1,2,1) in (\"3\");";
1788
1789   std::string s3select_result_3 = run_s3select(input_query_3,input, json_query_3);
1790
1791   ASSERT_NE(s3select_result_3,failure_sign);
1792
1793   auto count_3 = std::count(s3select_result_3.begin(), s3select_result_3.end(), '0');
1794
1795   ASSERT_EQ(count_3,0);
1796
1797   const std::string input_query_4 = "select (int(_1) in (1)) from s3object where int(_1) = 1;";
1798
1799   std::string s3select_result_4 = run_s3select(input_query_4,input);
1800
1801   ASSERT_NE(s3select_result_4,failure_sign);
1802
1803   auto count_4 = std::count(s3select_result_4.begin(), s3select_result_4.end(), '0');
1804
1805   ASSERT_EQ(count_4,0);
1806 }
1807
1808 TEST(TestS3selectFunctions, truefalse_multirows_expressions)
1809 {
1810   std::string input, input1;
1811   size_t size = 10000;
1812   generate_rand_columns_csv(input, size);
1813   const std::string input_query_1 = "select count(0) from s3object where cast(_3 as int)>999 = true;";
1814
1815   std::string s3select_result_1 = run_s3select(input_query_1,input);
1816
1817   ASSERT_NE(s3select_result_1,failure_sign);
1818
1819   const std::string input_query_2 = "select count(0) from s3object where char_length(_3)>3 = true;";
1820
1821   std::string s3select_result_2 = run_s3select(input_query_2,input);
1822
1823   ASSERT_EQ(s3select_result_1, s3select_result_2);
1824
1825   const std::string input_query_3 = "select count(0) from s3object where char_length(_3)=3 = true;";
1826
1827   std::string s3select_result_3 = run_s3select(input_query_3,input);
1828
1829   ASSERT_NE(s3select_result_3,failure_sign);
1830
1831   const std::string input_query_4 = "select count(0) from s3object where cast(_3 as int)>99 = true and cast(_3 as int)<1000 = true;";
1832
1833   std::string s3select_result_4 = run_s3select(input_query_4,input);
1834
1835   ASSERT_EQ(s3select_result_3, s3select_result_4);
1836
1837   generate_rand_columns_csv_with_null(input1, size);
1838
1839   const std::string input_query_5 = "select count(0) from s3object where (_3 is null) = true;";
1840
1841   std::string s3select_result_5 = run_s3select(input_query_5,input1);
1842
1843   ASSERT_NE(s3select_result_5,failure_sign);
1844
1845   const std::string input_query_6 = "select count(0) from s3object where (nullif(_3,null) is null) = true;";
1846
1847   std::string s3select_result_6 = run_s3select(input_query_6,input1);
1848
1849   ASSERT_NE(s3select_result_6,failure_sign);
1850
1851   ASSERT_EQ(s3select_result_5,s3select_result_6);
1852 }
1853
1854 TEST(TestS3selectFunctions, truefalse_date_time_expressions)
1855 {
1856   std::string input;
1857   size_t size = 10000;
1858   generate_rand_columns_csv_datetime(input, size);
1859   const std::string input_query_1 = "select count(0) from s3object where extract(year from to_timestamp(_1)) > 1950 = true and extract(year from to_timestamp(_1)) < 1960 = true;";
1860
1861   std::string s3select_result_1 = run_s3select(input_query_1,input);
1862
1863   ASSERT_NE(s3select_result_1,failure_sign);
1864
1865   const std::string input_query_2 = "select count(0) from s3object where int(substring(_1,1,4))>1950 = true and int(substring(_1,1,4))<1960 = true;";
1866
1867   std::string s3select_result_2 = run_s3select(input_query_2,input);
1868
1869   ASSERT_EQ(s3select_result_1, s3select_result_2);
1870 }
1871
1872 TEST(TestS3selectFunctions, truefalse_trim_expressions)
1873 {
1874   std::string input;
1875   size_t size = 10000;
1876   generate_csv_trim(input, size);
1877   const std::string input_query_1 = "select count(0) from stdin where trim(_1) = \"aeiou\" = true;";
1878
1879   std::string s3select_result_1 = run_s3select(input_query_1,input);
1880
1881   ASSERT_NE(s3select_result_1,failure_sign);
1882
1883   const std::string input_query_2 = "select count(0) from stdin where substring(_1 from 6 for 5) = \"aeiou\" = true;";
1884
1885   std::string s3select_result_2 = run_s3select(input_query_2,input);
1886
1887   ASSERT_EQ(s3select_result_1, s3select_result_2);
1888 }
1889
1890 TEST(TestS3selectFunctions, tuefalse_like_expressions)
1891 {
1892   std::string input, input1;
1893   size_t size = 10000;
1894   generate_csv(input, size);
1895   const std::string input_query_1 = "select count(0) from stdin where (_4 like \"%ar\") = true;";
1896
1897   std::string s3select_result_1 = run_s3select(input_query_1,input);
1898
1899   ASSERT_NE(s3select_result_1,failure_sign);
1900
1901   const std::string input_query_2 = "select count(0) from stdin where (substring(_4,char_length(_4),1) = \"r\") = true and (substring(_4,char_length(_4)-1,1) = \"a\") = true;";
1902
1903   std::string s3select_result_2 = run_s3select(input_query_2,input);
1904
1905   ASSERT_EQ(s3select_result_1, s3select_result_2);
1906
1907   generate_csv_like(input1, size);
1908
1909   const std::string input_query_3 = "select count(0) from stdin where (_1 like \"%aeio%\") = true;";
1910
1911   std::string s3select_result_3 = run_s3select(input_query_3,input1);
1912
1913   ASSERT_NE(s3select_result_3,failure_sign);
1914
1915   const std::string input_query_4 = "select count(0) from stdin where (substring(_1,4,4) = \"aeio\") = true;";
1916
1917   std::string s3select_result_4 = run_s3select(input_query_4,input1);
1918
1919   ASSERT_EQ(s3select_result_3, s3select_result_4);
1920
1921   const std::string input_query_5 = "select count(0) from stdin where (_1 like \"%r[r-s]\") = true;";
1922
1923   std::string s3select_result_5 = run_s3select(input_query_5,input1);
1924
1925   ASSERT_NE(s3select_result_5,failure_sign);
1926
1927   const std::string input_query_6 = "select count(0) from stdin where (substring(_1,char_length(_1),1) between \"r\" and \"s\") = true and (substring(_1,char_length(_1)-1,1) = \"r\") = true;";
1928
1929   std::string s3select_result_6 = run_s3select(input_query_6,input1);
1930
1931   ASSERT_EQ(s3select_result_5, s3select_result_6);
1932
1933   const std::string input_query_7 = "select count(0) from stdin where (_1 like \"%br_\") = true;";
1934
1935   std::string s3select_result_7 = run_s3select(input_query_7,input);
1936
1937   ASSERT_NE(s3select_result_7,failure_sign);
1938
1939   const std::string input_query_8 = "select count(0) from stdin where (substring(_1,char_length(_1)-1,1) = \"r\") = true and (substring(_1,char_length(_1)-2,1) = \"b\") = true;";
1940
1941   std::string s3select_result_8 = run_s3select(input_query_8,input);
1942
1943   ASSERT_EQ(s3select_result_7, s3select_result_8);
1944
1945   const std::string input_query_9 = "select count(0) from stdin where (_1 like \"%r[r-s]\") = false;";
1946
1947   std::string s3select_result_9 = run_s3select(input_query_9,input1);
1948
1949   ASSERT_NE(s3select_result_9,failure_sign);
1950
1951   const std::string input_query_10 = "select count(0) from stdin where (substring(_1,char_length(_1),1) not between \"r\" and \"s\") = true or (substring(_1,char_length(_1)-1,1) = \"r\") = false;";
1952
1953   std::string s3select_result_10 = run_s3select(input_query_10,input1);
1954
1955   ASSERT_EQ(s3select_result_9, s3select_result_10);
1956 }
1957
1958 TEST(TestS3selectFunctions, truefalse_coalesce_expressions)
1959 {
1960   std::string input;
1961   size_t size = 10000;
1962   generate_rand_columns_csv(input, size);
1963   const std::string input_query_1 = "select count(0) from s3object where char_length(_3)>2 and char_length(_4)>2 = true and cast(substring(_3,1,2) as int) = cast(substring(_4,1,2) as int) = true;";
1964
1965   std::string s3select_result_1 = run_s3select(input_query_1,input);
1966
1967   ASSERT_NE(s3select_result_1,failure_sign);
1968
1969   const std::string input_query_2 = "select count(0) from s3object where cast(_3 as int)>99 = true and cast(_4 as int)>99 = true and (coalesce(nullif(cast(substring(_3,1,2) as int),cast(substring(_4,1,2) as int)),7) = 7) = true;";
1970
1971   std::string s3select_result_2 = run_s3select(input_query_2,input);
1972
1973   ASSERT_EQ(s3select_result_1, s3select_result_2);
1974 }
1975
1976 TEST(TestS3selectFunctions, truefalse_in_expressions)
1977 {
1978   std::string input;
1979   size_t size = 10000;
1980   generate_rand_columns_csv(input, size);
1981   const std::string input_query_1 = "select int(_1) from s3object where (int(_1) in(1)) = true;";
1982
1983   std::string s3select_result_1 = run_s3select(input_query_1,input);
1984
1985   ASSERT_NE(s3select_result_1,failure_sign);
1986
1987   const std::string input_query_2 = "select int(_1) from s3object where int(_1) = 1 = true;";
1988
1989   std::string s3select_result_2 = run_s3select(input_query_2,input);
1990
1991   ASSERT_EQ(s3select_result_1, s3select_result_2);
1992
1993   const std::string input_query_7 = "select int(_2) from s3object where (int(_2)*2 in(int(_3)*2,int(_4)*3,int(_5)*5)) = true;";
1994
1995   std::string s3select_result_7 = run_s3select(input_query_7,input);
1996
1997   ASSERT_NE(s3select_result_7,failure_sign);
1998
1999   const std::string input_query_8 = "select int(_2) from s3object where int(_2)*2 = int(_3)*2 = true or int(_2)*2 = int(_4)*3 = true or int(_2)*2 = int(_5)*5 = true;";
2000
2001   std::string s3select_result_8 = run_s3select(input_query_8,input);
2002
2003   ASSERT_EQ(s3select_result_7, s3select_result_8);
2004
2005   const std::string input_query_9 = "select int(_1) from s3object where character_length(_1) = 2 = true and (substring(_1,2,1) in (\"3\")) = true;";
2006
2007   std::string s3select_result_9 = run_s3select(input_query_9,input);
2008
2009   ASSERT_NE(s3select_result_9,failure_sign);
2010
2011   const std::string input_query_10 = "select int(_1) from s3object where (_1 like \"_3\") = true;";
2012
2013   const char* json_query_10 = "select int(_1.c1) from s3object[*].root where (_1.c1 like \"_3\") = true;";
2014
2015   std::string s3select_result_10 = run_s3select(input_query_10,input,json_query_10);
2016
2017   ASSERT_EQ(s3select_result_9, s3select_result_10);
2018 }
2019
2020 TEST(TestS3selectFunctions, truefalse_alias_expressions)
2021 {
2022   std::string input;
2023   size_t size = 100;
2024   generate_rand_columns_csv(input, size);
2025   const std::string input_query_1 = "select (int(_1) > int(_2)) as a1 from s3object where a1 = true ;";
2026
2027   std::string s3select_result_1 = run_s3select(input_query_1,input);
2028
2029   ASSERT_NE(s3select_result_1,failure_sign);
2030
2031   const std::string input_query_2 = "select (int(_1) > int(_2)) from s3object where int(_1) > int(_2) = true;";
2032
2033   std::string s3select_result_2 = run_s3select(input_query_2,input);
2034
2035   ASSERT_EQ(s3select_result_1, s3select_result_2);
2036 }
2037 TEST(TestS3selectFunctions, charlength)
2038 {
2039 test_single_column_single_row( "select char_length(\"abcde\") from stdin;","5\n");
2040 }
2041
2042 TEST(TestS3selectFunctions, characterlength)
2043 {
2044 test_single_column_single_row( "select character_length(\"abcde\") from stdin;","5\n");
2045 }
2046
2047 TEST(TestS3selectFunctions, emptystring)
2048 {
2049 test_single_column_single_row( "select char_length(\"\") from stdin;","0\n");
2050 }
2051
2052 TEST(TestS3selectFunctions, lower)
2053 {
2054 test_single_column_single_row( "select lower(\"ABcD12#$e\") from stdin;","abcd12#$e\n");
2055 }
2056
2057 TEST(TestS3selectFunctions, upper)
2058 {
2059 test_single_column_single_row( "select upper(\"abCD12#$e\") from stdin;","ABCD12#$E\n");
2060 }
2061
2062 TEST(TestS3selectFunctions, mod)
2063 {
2064 test_single_column_single_row( "select 5%2 from stdin;","1\n");
2065 }
2066
2067 TEST(TestS3selectFunctions, modzero)
2068 {
2069 test_single_column_single_row( "select 0%2 from stdin;","0\n");
2070 }
2071
2072 TEST(TestS3selectFunctions, nullif)
2073 {
2074 test_single_column_single_row( "select nullif(5,3) from stdin;","5\n");
2075 }
2076
2077 TEST(TestS3selectFunctions, nullifeq)
2078 {
2079 test_single_column_single_row( "select nullif(5,5) from stdin;","null\n");
2080 }
2081
2082 TEST(TestS3selectFunctions, nullifnull)
2083 {
2084 test_single_column_single_row( "select nullif(null,null) from stdin;","null\n");
2085 }
2086
2087 TEST(TestS3selectFunctions, nullifintnull)
2088 {
2089 test_single_column_single_row( "select nullif(7, null) from stdin;","7\n");
2090 }
2091
2092 TEST(TestS3selectFunctions, nullifintstring)
2093 {
2094 test_single_column_single_row( "select nullif(5, \"hello\") from stdin;","5\n");
2095 }
2096
2097 TEST(TestS3selectFunctions, nullifstring)
2098 {
2099 test_single_column_single_row( "select nullif(\"james\",\"bond\") from stdin;","james\n");
2100 }
2101
2102 TEST(TestS3selectFunctions, nullifeqstring)
2103 {
2104 test_single_column_single_row( "select nullif(\"redhat\",\"redhat\") from stdin;","null\n");
2105 }
2106
2107 TEST(TestS3selectFunctions, nullifnumericeq)
2108 {
2109 test_single_column_single_row( "select nullif(1, 1.0) from stdin;","null\n");
2110 }
2111
2112 TEST(TestS3selectFunctions, nulladdition)
2113 {
2114 test_single_column_single_row( "select 1 + null from stdin;","null\n");
2115 }
2116
2117 TEST(TestS3selectFunctions, isnull)
2118 {
2119 test_single_column_single_row( "select \"true\" from stdin where nullif(1,1) is null;" ,"true\n");
2120 }
2121
2122 TEST(TestS3selectFunctions, isnullnot)
2123 {
2124 test_single_column_single_row( "select \"true\" from stdin where not nullif(1,2) is null;" ,"true\n");
2125 }
2126
2127 TEST(TestS3selectFunctions, isnull1)
2128 {
2129 test_single_column_single_row( "select \"true\" from stdin where 7 + null is null;" ,"true\n");
2130 }
2131
2132 TEST(TestS3selectFunctions, isnull2)
2133 {
2134 test_single_column_single_row( "select \"true\" from stdin where null + 7 is null;" ,"true\n");
2135 }
2136
2137 TEST(TestS3selectFunctions, isnull3)
2138 {
2139 test_single_column_single_row( "select \"true\" from stdin where (null > 1) is null;" ,"true\n");
2140 }
2141
2142 TEST(TestS3selectFunctions, isnull4)
2143 {
2144 test_single_column_single_row( "select \"true\" from stdin where (1 <= null) is null;" ,"true\n");
2145 }
2146
2147 TEST(TestS3selectFunctions, isnull5)
2148 {
2149 test_single_column_single_row( "select \"true\" from stdin where (null > 2 and 1 = 0) is not null;" ,"true\n");
2150 }
2151
2152 TEST(TestS3selectFunctions, isnull6)
2153 {
2154 test_single_column_single_row( "select \"true\" from stdin where (null>2 and 2>1) is  null;" ,"true\n");
2155 }
2156
2157 TEST(TestS3selectFunctions, isnull7)
2158 {
2159 test_single_column_single_row( "select \"true\" from stdin where (null>2 or null<=3) is  null;" ,"true\n");
2160 }
2161
2162 TEST(TestS3selectFunctions, isnull8)
2163 {
2164 test_single_column_single_row( "select \"true\" from stdin where (5<4 or null<=3) is  null;" ,"true\n");
2165 }
2166
2167 TEST(TestS3selectFunctions, isnull9)
2168 {
2169 test_single_column_single_row( "select \"true\" from stdin where (null<=3 or 5<3) is  null;" ,"true\n");
2170 }
2171
2172 TEST(TestS3selectFunctions, isnull10)
2173 {
2174 test_single_column_single_row( "select \"true\" from stdin where (null<=3 or 5>3) ;" ,"true\n");
2175 }
2176
2177 TEST(TestS3selectFunctions, nullnot)
2178 {
2179 test_single_column_single_row( "select \"true\" from stdin where not (null>0 and 7<3) ;" ,"true\n");
2180 }
2181
2182 TEST(TestS3selectFunctions, nullnot1)
2183 {
2184 test_single_column_single_row( "select \"true\" from stdin where not  (null>0 or 4>3) and (7<1) ;" ,"true\n");
2185 }
2186
2187 TEST(TestS3selectFunctions, isnull11)
2188 {
2189 test_single_column_single_row( "select \"true\" from stdin where (5>3 or null<1) ;" ,"true\n");
2190 }
2191
2192 TEST(TestS3selectFunctions, likeop)
2193 {
2194 test_single_column_single_row( "select \"true\" from stdin where \"qwertyabcde\" like \"%abcde\";" ,"true\n");
2195 }
2196
2197 TEST(TestS3selectFunctions, likeopfalse)
2198 {
2199 test_single_column_single_row( "select \"true\" from stdin where not  \"qwertybcde\" like \"%abcde\";" ,"true\n");
2200 }
2201
2202 TEST(TestS3selectFunctions, likeop1)
2203 {
2204 test_single_column_single_row( "select \"true\" from stdin where \"qwertyabcdeqwerty\" like \"%abcde%\";" ,"true\n");
2205 }
2206
2207 TEST(TestS3selectFunctions, likeop1false)
2208 {
2209 test_single_column_single_row( "select \"true\" from stdin where not \"qwertyabcdqwerty\" like \"%abcde%\";" ,"true\n");
2210 }
2211
2212 TEST(TestS3selectFunctions, likeop2)
2213 {
2214 test_single_column_single_row( "select \"true\" from stdin where \"abcdeqwerty\" like \"abcde%\";" ,"true\n");
2215 }
2216
2217 TEST(TestS3selectFunctions, likeop2false)
2218 {
2219 test_single_column_single_row( "select \"true\" from stdin where not  \"abdeqwerty\" like \"abcde%\";" ,"true\n");
2220 }
2221
2222 TEST(TestS3selectFunctions, likeop6)
2223 {
2224 test_single_column_single_row( "select \"true\" from stdin where \"abqwertyde\" like \"ab%de\";" ,"true\n");
2225 }
2226
2227 TEST(TestS3selectFunctions, likeop3false)
2228 {
2229 test_single_column_single_row( "select \"true\" from stdin where not \"aabcde\" like \"_bcde\";" ,"true\n");
2230 }
2231
2232 TEST(TestS3selectFunctions, likeop3mix)
2233 {
2234 test_single_column_single_row( "select \"true\" from stdin where  \"aabbccdef\" like \"_ab%\";" ,"true\n");
2235 }
2236
2237 TEST(TestS3selectFunctions, likeop4mix)
2238 {
2239 test_single_column_single_row( "select \"true\" from stdin where \"aabbccdef\" like \"%de_\";" ,"true\n");
2240 }
2241
2242 TEST(TestS3selectFunctions, likeop4)
2243 {
2244 test_single_column_single_row( "select \"true\" from stdin where \"abcde\" like \"abc_e\";" ,"true\n");
2245 }
2246
2247 TEST(TestS3selectFunctions, likeop4false)
2248 {
2249 test_single_column_single_row( "select \"true\" from stdin where not  \"abcccddyddyde\" like \"abc_e\";" ,"true\n");
2250 }
2251
2252 TEST(TestS3selectFunctions, likeop5)
2253 {
2254 test_single_column_single_row( "select \"true\" from stdin where \"ebcde\" like \"[d-f]bcde\";" ,"true\n");
2255 }
2256
2257 TEST(TestS3selectFunctions, likeop5false)
2258 {
2259 test_single_column_single_row( "select \"true\" from stdin where not  \"abcde\" like \"[d-f]bcde\";" ,"true\n");
2260 }
2261
2262 TEST(TestS3selectFunctions, likeopdynamic)
2263 {
2264 test_single_column_single_row( "select \"true\" from stdin where \"abcde\" like substring(\"abcdefg\",1,5);" ,"true\n");
2265 }
2266
2267 TEST(TestS3selectFunctions, likeop5not)
2268 {
2269 test_single_column_single_row( "select \"true\" from stdin where \"abcde\" like \"[^d-f]bcde\";" ,"true\n");
2270 }
2271
2272 TEST(TestS3selectFunctions, likeop7)
2273 {
2274 test_single_column_single_row( "select \"true\" from stdin where \"qwertyabcde\" like \"%%%%abcde\";" ,"true\n");
2275 }
2276
2277 TEST(TestS3selectFunctions, likeop8beginning)
2278 {
2279 test_single_column_single_row( "select \"true\" from stdin where \"abcde\" like \"[abc]%\";" ,"true\n");
2280 }
2281
2282 TEST(TestS3selectFunctions, likeop8false)
2283 {
2284 test_single_column_single_row( "select \"true\" from stdin where not \"dabc\" like \"[abc]%\";" ,"true\n");
2285 }
2286
2287 TEST(TestS3selectFunctions, likeop8end)
2288 {
2289 test_single_column_single_row( "select \"true\" from stdin where \"xyza\" like \"%[abc]\";" ,"true\n");
2290 }
2291
2292 TEST(TestS3selectFunctions, inoperator)
2293 {
2294 test_single_column_single_row( "select \"true\" from stdin where \"a\" in (\"b\", \"a\");" ,"true\n");
2295 }
2296
2297 TEST(TestS3selectFunctions, inoperatorfalse)
2298 {
2299 test_single_column_single_row( "select \"true\" from stdin where not \"a\" in (\"b\", \"c\");" ,"true\n");
2300 }
2301
2302 TEST(TestS3selectFunctions, inoperatormore)
2303 {
2304 test_single_column_single_row( "select \"true\" from stdin where \"a\" in (\"b\", \"a\", \"d\", \"e\", \"f\");" ,"true\n");
2305 }
2306
2307 TEST(TestS3selectFunctions, inoperatormixtype)
2308 {
2309 test_single_column_single_row( "select \"true\" from stdin where 10 in (5.0*2.0, 12+1, 9+1.2, 22/2, 12-3);" ,"true\n");
2310 }
2311
2312 TEST(TestS3selectFunctions, mix)
2313 {
2314 test_single_column_single_row( "select \"true\" from stdin where   \"abcde\" like \"abc_e\" and 10 in (5.0*2.0, 12+1) and nullif(2,2) is null;" ,"true\n");
2315 }
2316
2317 TEST(TestS3selectFunctions, case_when_then_else)
2318 {
2319 test_single_column_single_row( "select  case when (1+1+1*1=(2+1)*3)  then \"case-1-1\" when ((4*3)=(12)) then \"case-1-2\" else \"case-else-1\" end , case when 1+1*7=(2+1)*3  then \"case-2-1\" when ((4*3)=(12)+1) then \"case-2-2\" else \"case-else-2\" end from stdin where (3*3=9);" ,"case-1-2,case-else-2\n");
2320 }
2321
2322 TEST(TestS3selectFunctions, simple_case_when)
2323 {
2324 test_single_column_single_row( "select  case 2+1 when (3+4) then \"case-1-1\" when 3 then \"case-3\" else \"case-else-1\" end from stdin;","case-3\n");
2325 }
2326
2327 TEST(TestS3selectFunctions, nested_case)
2328 {
2329 test_single_column_single_row( "select case when ((3+4) = (7 *1)) then \"case-1-1\" else \"case-2-2\" end, case 1+3 when 2+3 then \"case-1-2\" else \"case-2-1\"  end from stdin where (3*3 = 9);","case-1-1,case-2-1\n");
2330 }
2331
2332 TEST(TestS3selectFunctions, substr11)
2333 {
2334 test_single_column_single_row( "select substring(\"01234567890\",2*0+1,1.53*0+3) from stdin ;" ,"012\n");
2335 }
2336
2337 TEST(TestS3selectFunctions, substr12)
2338 {
2339 test_single_column_single_row( "select substring(\"01234567890\",2*0+1,1+2.0) from stdin ;" ,"012\n");
2340 }
2341
2342 TEST(TestS3selectFunctions, substr13)
2343 {
2344 test_single_column_single_row( "select substring(\"01234567890\",2.5*2+1,1+2) from stdin ;" ,"567\n");
2345 }
2346
2347 TEST(TestS3selectFunctions, substr14)
2348 {
2349 test_single_column_single_row( "select substring(\"123456789\",0) from stdin ;" ,"123456789\n");
2350 }
2351
2352 TEST(TestS3selectFunctions, substr15)
2353 {
2354 test_single_column_single_row( "select substring(\"123456789\",-4) from stdin ;" ,"123456789\n");
2355 }
2356
2357 TEST(TestS3selectFunctions, substr16)
2358 {
2359 test_single_column_single_row( "select substring(\"123456789\",0,100) from stdin ;" ,"123456789\n");
2360 }
2361
2362 TEST(TestS3selectFunctions, substr17)
2363 {
2364 test_single_column_single_row( "select substring(\"12345\",0,5) from stdin ;" ,"1234\n");
2365 }
2366
2367 TEST(TestS3selectFunctions, substr18)
2368 {
2369 test_single_column_single_row( "select substring(\"12345\",-1,5) from stdin ;" ,"123\n");
2370 }
2371
2372 TEST(TestS3selectFunctions, substr19)
2373 {
2374 test_single_column_single_row( "select substring(\"123456789\" from 0) from stdin ;" ,"123456789\n");
2375 }
2376
2377 TEST(TestS3selectFunctions, substr20)
2378 {
2379 test_single_column_single_row( "select substring(\"123456789\" from -4) from stdin ;" ,"123456789\n");
2380 }
2381
2382 TEST(TestS3selectFunctions, substr21)
2383 {
2384 test_single_column_single_row( "select substring(\"123456789\" from 0 for 100) from stdin ;" ,"123456789\n");
2385 }
2386
2387 TEST(TestS3selectFunctions, substr22)
2388 {
2389 test_single_column_single_row( "select \"true\" from stdin where 5 = cast(substring(\"523\",1,1) as int);" ,"true\n");
2390 }
2391
2392 TEST(TestS3selectFunctions, substr23)
2393 {
2394 test_single_column_single_row( "select \"true\" from stdin where cast(substring(\"523\",1,1) as int) > cast(substring(\"123\",1,1) as int)  ;" ,"true\n");
2395 }
2396
2397 TEST(TestS3selectFunctions, coalesce)
2398 {
2399 test_single_column_single_row( "select coalesce(5,3) from stdin;","5\n");
2400 }
2401
2402 TEST(TestS3selectFunctions, coalesceallnull)
2403 {
2404 test_single_column_single_row( "select coalesce(nullif(5,5),nullif(1,1.0)) from stdin;","null\n");
2405 }
2406
2407 TEST(TestS3selectFunctions, coalesceanull)
2408 {
2409 test_single_column_single_row( "select coalesce(nullif(5,5),nullif(1,1.0),2) from stdin;","2\n");
2410 }
2411
2412 TEST(TestS3selectFunctions, coalescewhere)
2413 {
2414 test_single_column_single_row( "select \"true\" from stdin where  coalesce(nullif(7.0,7),nullif(4,4.0),6) = 6;" ,"true\n");
2415 }
2416
2417 TEST(TestS3selectFunctions, castint)
2418 {
2419 test_single_column_single_row( "select cast(5.123 as int) from stdin ;" ,"5\n");
2420 }
2421
2422 TEST(TestS3selectFunctions, castfloat)
2423 {
2424 test_single_column_single_row( "select cast(1.234 as FLOAT) from stdin ;" ,"1.234\n");
2425 }
2426
2427 TEST(TestS3selectFunctions, castfloatoperation)
2428 {
2429 test_single_column_single_row( "select cast(1.234 as float) + cast(1.235 as float) from stdin ;" ,"2.4690000000000003\n");
2430 }
2431
2432 TEST(TestS3selectFunctions, caststring)
2433 {
2434 test_single_column_single_row( "select cast(1234 as string) from stdin ;" ,"1234\n");
2435 }
2436
2437 TEST(TestS3selectFunctions, caststring1)
2438 {
2439 test_single_column_single_row( "select cast('12hddd' as int) from stdin ;" ,"#failure#","extra characters after the number");
2440 }
2441
2442 TEST(TestS3selectFunctions, caststring2)
2443 {
2444 test_single_column_single_row( "select cast('124' as int) + 1 from stdin ;" ,"125\n");
2445 }
2446
2447 TEST(TestS3selectFunctions, castsubstr)
2448 {
2449 test_single_column_single_row( "select substring(cast(cast(\"1234567\" as int) as string),2,2) from stdin ;" ,"23\n");
2450 }
2451
2452 TEST(TestS3selectFunctions, casttimestamp)
2453 {
2454 test_single_column_single_row( "select cast('2010-01-15T13:30:10Z' as timestamp)  from stdin ;" ,"2010-01-15T13:30:10Z\n");
2455 }
2456
2457 TEST(TestS3selectFunctions, castdateadd)
2458 {
2459 test_single_column_single_row( "select date_add(day, 2, cast('2010-01-15T13:30:10Z' as timestamp)) from stdin ;" ,"2010-01-17T13:30:10Z\n");
2460 }
2461
2462 TEST(TestS3selectFunctions, castdatediff)
2463 {
2464 test_single_column_single_row( "select date_diff(year,cast('2010-01-15T13:30:10Z' as timestamp), cast('2020-01-15T13:30:10Z' as timestamp)) from stdin ;" ,"10\n");
2465 }
2466
2467 TEST(TestS3selectFunctions, trim)
2468 {
2469 test_single_column_single_row( "select trim(\"   \twelcome\t   \") from stdin ;" ,"\twelcome\t\n");
2470 }
2471
2472 TEST(TestS3selectFunctions, trim1)
2473 {
2474 test_single_column_single_row( "select trim(\"   foobar   \") from stdin ;" ,"foobar\n");
2475 }
2476
2477 TEST(TestS3selectFunctions, trim2)
2478 {
2479 test_single_column_single_row( "select trim(trailing from \"   foobar   \") from stdin ;" ,"   foobar\n");
2480 }
2481
2482 TEST(TestS3selectFunctions, trim3)
2483 {
2484 test_single_column_single_row( "select trim(leading from \"   foobar   \") from stdin ;" ,"foobar   \n");
2485 }
2486
2487 TEST(TestS3selectFunctions, trim4)
2488 {
2489 test_single_column_single_row( "select trim(both from \"   foobar   \") from stdin ;" ,"foobar\n");
2490 }
2491
2492 TEST(TestS3selectFunctions, trim5)
2493 {
2494 test_single_column_single_row( "select trim(from \"   foobar   \") from stdin ;" ,"foobar\n");
2495 }
2496
2497 TEST(TestS3selectFunctions, trim6)
2498 {
2499 test_single_column_single_row( "select trim(both \"12\" from  \"1112211foobar22211122\") from stdin ;" ,"foobar\n");
2500 }
2501
2502 TEST(TestS3selectFunctions, trim7)
2503 {
2504 test_single_column_single_row( "select substring(trim(both from '   foobar   '),2,3) from stdin ;" ,"oob\n");
2505 }
2506
2507 TEST(TestS3selectFunctions, trim8)
2508 {
2509 test_single_column_single_row( "select substring(trim(both '12' from '1112211foobar22211122'),1,6) from stdin ;" ,"foobar\n");
2510 }
2511
2512 TEST(TestS3selectFunctions, trim9)
2513 {
2514 test_single_column_single_row( "select cast(trim(both \"12\" from \"111221134567822211122\") as int) + 5 from stdin ;" ,"345683\n");
2515 }
2516
2517 TEST(TestS3selectFunctions, trimefalse)
2518 {
2519 test_single_column_single_row( "select cast(trim(both from \"12\" \"111221134567822211122\") as int) + 5 from stdin ;" ,"#failure#","");
2520 }
2521
2522 TEST(TestS3selectFunctions, trim10)
2523 {
2524 test_single_column_single_row( "select trim(trim(leading from \"   foobar   \")) from stdin ;" ,"foobar\n");
2525 }
2526
2527 TEST(TestS3selectFunctions, trim11)
2528 {
2529 test_single_column_single_row( "select trim(trailing from trim(leading from \"   foobar   \")) from stdin ;" ,"foobar\n");
2530 }
2531
2532 TEST(TestS3selectFunctions, likescape)
2533 {
2534   test_single_column_single_row("select \"true\" from stdin where  \"abc_defgh\" like \"abc$_defgh\" escape \"$\";","true\n");
2535   test_single_column_single_row("select \"true\" from s3object where  \"j_kerhai\" like \"j#_%\" escape \"#\";","true\n");
2536   test_single_column_single_row("select \"true\" from s3object where  \"jok_ai\" like \"%#_ai\" escape \"#\";","true\n");
2537   test_single_column_single_row("select \"true\" from s3object where  \"jo_aibc\" like \"%#_ai%\" escape \"#\";","true\n");
2538   test_single_column_single_row("select \"true\" from s3object where  \"jok%abc\" like \"jok$%abc\" escape \"$\";","true\n");
2539   test_single_column_single_row("select \"true\" from s3object where  \"ab%%a\" like \"ab$%%a\" escape \"$\";","true\n");
2540   test_single_column_single_row("select \"true\" from s3object where  \"_a_\" like \"=_a=_\" escape \"=\";","true\n");
2541   test_single_column_single_row("select \"true\" from s3object where  \"abc#efgh\" like \"abc##efgh\" escape \"#\";","true\n");
2542   test_single_column_single_row("select \"true\" from s3object where  \"%abs%\" like \"#%abs#%\" escape \"#\";","true\n");
2543   test_single_column_single_row("select \"true\" from s3object where  \"abc##efgh\" like \"abc####efgh\" escape \"#\";","true\n");
2544 }
2545
2546 TEST(TestS3selectFunctions, likescapedynamic)
2547 {
2548 test_single_column_single_row( "select \"true\" from s3object where  \"abc#efgh\" like substring(\"abc##efghi\",1,9) escape \"#\";" ,"true\n");
2549 test_single_column_single_row( "select \"true\" from s3object where  \"abcdefgh\" like substring(\"abcd%abc\",1,5);" ,"true\n");
2550 test_single_column_single_row( "select \"true\" from s3object where  substring(\"abcde\",1,5) like \"abcd_\" ;" ,"true\n");
2551 test_single_column_single_row( "select \"true\" from s3object where  substring(\"abcde\",1,5) like substring(\"abcd_ab\",1,5) ;" ,"true\n");
2552 }
2553
2554 TEST(TestS3selectFunctions, test_escape_expressions)
2555 {
2556   std::string input, input1;
2557   size_t size = 10000;
2558   generate_csv_escape(input, size);
2559   const std::string input_query_1 = "select count(0) from stdin where _1 like \"%_ar\" escape \"%\";";
2560
2561   std::string s3select_result_1 = run_s3select(input_query_1,input);
2562
2563   ASSERT_NE(s3select_result_1,failure_sign);
2564
2565   const std::string input_query_2 = "select count(0) from stdin where substring(_1,char_length(_1),1) = \"r\" and substring(_1,char_length(_1)-1,1) = \"a\" and substring(_1,char_length(_1)-2,1) = \"_\";";
2566
2567   std::string s3select_result_2 = run_s3select(input_query_2,input);
2568
2569   ASSERT_EQ(s3select_result_1, s3select_result_2);
2570
2571   const std::string input_query_3 = "select count(0) from stdin where _2 like \"%aeio$_\" escape \"$\";";
2572
2573   std::string s3select_result_3 = run_s3select(input_query_3,input);
2574
2575   ASSERT_NE(s3select_result_3,failure_sign);
2576
2577   const std::string input_query_4 = "select count(0) from stdin where substring(_2,1,5) = \"aeio_\";";
2578
2579   std::string s3select_result_4 = run_s3select(input_query_4,input);
2580
2581   ASSERT_EQ(s3select_result_3, s3select_result_4);
2582 }
2583
2584 void generate_csv_multirow(std::string& out, int loop = 1) {
2585   // schema is: int, float, string, string
2586   std::stringstream ss;
2587   for(int i = 0; i < loop; i++)
2588   {
2589     ss << "1,42926,7334,5.5,Brandise,Letsou,Brandise.Letsou@yopmail.com,worker,2020-10-26T11:21:30.397Z" << std::endl;
2590     ss << "2,21169,3648,9.0,Zaria,Weinreb,Zaria.Weinreb@yopmail.com,worker,2009-12-02T01:22:45.8327+09:45" << std::endl;
2591     ss << "3,35581,9091,2.1,Bibby,Primalia,Bibby.Primalia@yopmail.com,doctor,2001-02-27T23:18:23.446633-12:00" << std::endl;
2592     ss << "4,38388,7345,4.7,Damaris,Arley,Damaris.Arley@yopmail.com,firefighter,1995-08-24T01:40:00+12:30" << std::endl;
2593     ss << "5,42802,6464,7.0,Georgina,Georas,Georgina.Georas@yopmail.com,worker,2013-01-30T05:27:59.2Z" << std::endl;
2594     ss << "6,45582,52863,0.1,Kelly,Hamil,Kelly.Hamil@yopmail.com,police officer,1998-03-31T17:25-01:05" << std::endl;
2595     ss << "7,8548,7665,3.6,Claresta,Flita,Claresta.Flita@yopmail.com,doctor,2007-10-10T22:00:30Z" << std::endl;
2596     ss << "8,22633,528,5.3,Bibby,Virgin,Bibby.Virgin@yopmail.com,developer,2020-06-30T11:07:01.23323-00:30" << std::endl;
2597     ss << "9,38439,5645,2.8,Mahalia,Aldric,Mahalia.Aldric@yopmail.com,doctor,2019-04-20T20:21:22.23+05:15" << std::endl;
2598     ss << "10,6611,7287,1.0,Pamella,Sibyls,Pamella.Sibyls@yopmail.com,police officer,2000-09-13T14:41Z" << std::endl;
2599   }
2600   out = ss.str();
2601 }
2602
2603 TEST(TestS3selectFunctions, limit)
2604 {
2605   std::string input_csv, input_query, expected_res;
2606   generate_csv_multirow(input_csv, 2);
2607
2608   input_query = "select _1 from stdin limit 0;";
2609   expected_res = "";
2610   std::cout << "Running query: 1 (when limit is zero)" << std::endl;
2611   auto s3select_res = run_s3select(input_query, input_csv);
2612   EXPECT_EQ(s3select_res, expected_res);
2613
2614   input_query = "select _1 from stdin limit 8;";
2615   expected_res = "1\n2\n3\n4\n5\n6\n7\n8\n";
2616   std::cout << "Running query: 2 (non-aggregate query, limit clause only)" << std::endl;
2617   s3select_res = run_s3select(input_query, input_csv);
2618   EXPECT_EQ(s3select_res, expected_res);
2619
2620   input_query = "select _1 from stdin where _2 > _3 limit 8;";
2621   expected_res = "7\n";
2622   std::cout << "Running query: 3 (non-aggregate_query, where + limit clause)" << std::endl;
2623   s3select_res = run_s3select(input_query, input_csv);
2624   EXPECT_EQ(s3select_res, expected_res);
2625
2626   input_query = "select _1 from stdin where _2 > _3 limit 7;";
2627   expected_res = "7\n";
2628   std::cout << "Running query: 4 (non-aggregate_query, where + limit clause)" << std::endl;
2629   s3select_res = run_s3select(input_query, input_csv);
2630   EXPECT_EQ(s3select_res, expected_res);
2631
2632   input_query = "select _1 from stdin where _2 > _3 limit 6;";
2633   expected_res = "";
2634   std::cout << "Running query: 5 (non-aggregate_query, where + limit clause)" << std::endl;
2635   s3select_res = run_s3select(input_query, input_csv);
2636   EXPECT_EQ(s3select_res, expected_res);
2637
2638   input_query = "select count(0) from stdin limit 9;";
2639   expected_res = "9";
2640   std::cout << "Running query: 6 (aggregate query, limit clause only)" << std::endl;
2641   s3select_res = run_s3select(input_query, input_csv);
2642   EXPECT_EQ(s3select_res, expected_res);
2643
2644   input_query = "select count(0) from stdin where _2 > _3 limit 8;";
2645   expected_res = "1";
2646   std::cout << "Running query: 7 (aggregate_query, where + limit clause)" << std::endl;
2647   s3select_res = run_s3select(input_query, input_csv);
2648   EXPECT_EQ(s3select_res, expected_res);
2649
2650   input_query = "select count(0) from stdin where _2 > _3 limit 7;";
2651   expected_res = "1";
2652   std::cout << "Running query: 8 (aggregate_query, where + limit clause)" << std::endl;
2653   s3select_res = run_s3select(input_query, input_csv);
2654   EXPECT_EQ(s3select_res, expected_res);
2655
2656   input_query = "select count(0) from stdin where _2 > _3 limit 6;";
2657   expected_res = "0";
2658   std::cout << "Running query: 9 (aggregate_query, where + limit clause)" << std::endl;
2659   s3select_res = run_s3select(input_query, input_csv);
2660   EXPECT_EQ(s3select_res, expected_res);
2661
2662   generate_csv_multirow(input_csv, 10000);
2663
2664   input_query = "select count(0) from stdin limit 90000;";
2665   expected_res = "90000";
2666   std::cout << "Running query: 10 (aggregate_query, limit clause only, with Large input)" << std::endl;
2667   s3select_res = run_s3select(input_query, input_csv);
2668   EXPECT_EQ(s3select_res, expected_res);
2669
2670   input_query = "select count(0) from stdin where _2 > _3 limit 90000;";
2671   expected_res = "9000";
2672   std::cout << "Running query: 11 (aggregate_query, where + limit clause, with Large input)" << std::endl;
2673   s3select_res = run_s3select(input_query, input_csv);
2674   EXPECT_EQ(s3select_res, expected_res);
2675
2676   std::string json_input=R"(
2677 {
2678 "firstName": "Joe",
2679 "lastName": "Jackson",
2680 "gender": "male",
2681 "age": 20,
2682 "address": {
2683 "streetAddress": "101",
2684 "city": "San Diego",
2685 "state": "CA"
2686 },
2687
2688 "phoneNumbers": [
2689 { "type": "home1", "number": "734928_1","addr": 11 },
2690 { "type": "home2", "number": "734928_2","addr": 22 },
2691 { "type": "home3", "number": "734928_3","addr": 33 },
2692 { "type": "home4", "number": "734928_4","addr": 44 },
2693 { "type": "home5", "number": "734928_5","addr": 55 },
2694 { "type": "home6", "number": "734928_6","addr": 66 },
2695 { "type": "home7", "number": "734928_7","addr": 77 },
2696 { "type": "home8", "number": "734928_8","addr": 88 },
2697 { "type": "home9", "number": "734928_9","addr": 99 },
2698 { "type": "home10", "number": "734928_10","addr": 100 },
2699 { "type": "home11", "number": "734928_11","addr": 101 },
2700 { "type": "home12", "number": "734928_12","addr": 102 },
2701 { "type": "home13", "number": "734928_13","addr": 103 },
2702 { "type": "home14", "number": "734928_14","addr": 104 },
2703 { "type": "home15", "number": "734928_15","addr": 105 }
2704 ],
2705
2706 "key_after_array": "XXX",
2707
2708 "description" : {
2709   "main_desc" : "value_1",
2710   "second_desc" : "value_2"
2711 }
2712
2713 }
2714 )";
2715
2716   const char* input_query_json = "select _1.addr from s3object[*].phoneNumbers limit 0;";
2717   expected_res = "";
2718   std::cout << "Running query: 12 (json, limit is zero)" << std::endl;
2719   run_json_query(input_query_json, json_input, s3select_res);
2720   EXPECT_EQ(s3select_res, expected_res);
2721
2722   input_query_json = "select _1.addr from s3object[*].phoneNumbers limit 5;";
2723   expected_res = "11\n22\n33\n44\n55\n";
2724   std::cout << "Running query: 13 (json, non-aggregate query, limit clause only)" << std::endl;
2725   run_json_query(input_query_json, json_input, s3select_res);
2726   EXPECT_EQ(s3select_res, expected_res);
2727
2728   input_query_json = "select _1.addr from s3object[*].phoneNumbers where _1.type like \"%1%\" limit 12;";
2729   expected_res = "11\n100\n101\n102\n";
2730   std::cout << "Running query: 14 (json, non-aggregate query, where + limit clause)" << std::endl;
2731   run_json_query(input_query_json, json_input, s3select_res);
2732   EXPECT_EQ(s3select_res, expected_res);
2733
2734   input_query_json = "select count(0) from s3object[*].phoneNumbers limit 9;";
2735   expected_res = "9";
2736   std::cout << "Running query: 15 (json, aggregate query, limit clause only, limit reached)" << std::endl;
2737   run_json_query(input_query_json, json_input, s3select_res);
2738   EXPECT_EQ(s3select_res, expected_res);
2739
2740   input_query_json = "select count(0) from s3object[*].phoneNumbers limit 18;";
2741   expected_res = "15";
2742   std::cout << "Running query: 16 (json, aggregate query, limit clause only, end of stream)" << std::endl;
2743   run_json_query(input_query_json, json_input, s3select_res);
2744   EXPECT_EQ(s3select_res, expected_res);
2745
2746   input_query_json = "select count(0) from s3object[*].phoneNumbers where _1.type like \"%1_\" limit 10;";
2747   expected_res = "1";
2748   std::cout << "Running query: 17 (json, aggregate query, where + limit clause)" << std::endl;
2749   run_json_query(input_query_json, json_input, s3select_res);
2750   EXPECT_EQ(s3select_res, expected_res);
2751 }
2752
2753 TEST(TestS3selectFunctions, nested_query_single_row_result)
2754 {
2755   std::string input_csv, input_query, expected_res;
2756   generate_csv_multirow(input_csv);
2757
2758   input_query = "select to_string(to_timestamp(\'2009-09-17T17:56:06.234567Z\'), substring(\' athmywopgss-nghjkl\', 3, 10)) from stdin;";
2759   expected_res = "t5562009wopg06";
2760   std::cout << "Running query: 1" << std::endl;
2761   auto s3select_res = run_s3select(input_query);
2762   EXPECT_EQ(s3select_res, expected_res);
2763
2764   input_query = "select to_timestamp(upper(\'2009-09-17t17:56:06.234567z\')) from stdin;";
2765   #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
2766     expected_res = "2009-09-17T17:56:06.234567000Z";
2767   #else
2768     expected_res = "2009-09-17T17:56:06.234567Z";
2769   #endif
2770   std::cout << "Running query: 2" << std::endl;
2771   s3select_res = run_s3select(input_query);
2772   EXPECT_EQ(s3select_res, expected_res);
2773
2774   input_query = "select count(0) from stdin where extract( year from to_timestamp(_9)) < 2010;";
2775   expected_res = "6";
2776   std::cout << "Running query: 3" << std::endl;
2777   s3select_res = run_s3select(input_query, input_csv);
2778   EXPECT_EQ(s3select_res, expected_res);
2779
2780 }
2781
2782 TEST(TestS3selectFunctions, nested_query_multirow_result)
2783 {
2784   std::string input_csv, input_query, expected_res;
2785   generate_csv_multirow(input_csv);
2786
2787   input_query = "select to_string(to_timestamp(_9), substring(\' athmywopgssMMMMdXXXXX-nghjkl\', 2, 25)) from stdin;";
2788   expected_res = "AMt11212020wopg30October26Z-397000000g11\nAMt1222009wopg45December2+09:45-832700000g1\nPMt11182001wopg23February27-12:00-446633000g11\nAMt1401995wopg00August24+12:30-0g1\nAMt5272013wopg59January30Z-200000000g5\nPMt5251998wopg00March31-01:05-0g5\nPMt1002007wopg30October10Z-0g10\nAMt1172020wopg01June30-00:30-233230000g11\nPMt8212019wopg22April20+05:15-230000000g8\nPMt2412000wopg00September13Z-0g2\n";
2789   std::cout << "Running query: 1" << std::endl;
2790   auto s3select_res = run_s3select(input_query, input_csv);
2791   EXPECT_EQ(s3select_res, expected_res);
2792
2793   input_query = "select to_timestamp(upper(lower(_9))) from stdin;";
2794   #if BOOST_DATE_TIME_POSIX_TIME_STD_CONFIG
2795     expected_res = "2020-10-26T11:21:30.397000000Z\n2009-12-02T01:22:45.832700000+09:45\n2001-02-27T23:18:23.446633000-12:00\n1995-08-24T01:40:00+12:30\n2013-01-30T05:27:59.200000000Z\n1998-03-31T17:25:00-01:05\n2007-10-10T22:00:30Z\n2020-06-30T11:07:01.233230000-00:30\n2019-04-20T20:21:22.230000000+05:15\n2000-09-13T14:41:00Z\n";
2796   #else
2797     expected_res = "2020-10-26T11:21:30.397000Z\n2009-12-02T01:22:45.832700+09:45\n2001-02-27T23:18:23.446633-12:00\n1995-08-24T01:40:00+12:30\n2013-01-30T05:27:59.200000Z\n1998-03-31T17:25:00-01:05\n2007-10-10T22:00:30Z\n2020-06-30T11:07:01.233230-00:30\n2019-04-20T20:21:22.230000+05:15\n2000-09-13T14:41:00Z\n";
2798   #endif
2799   std::cout << "Running query: 2" << std::endl;
2800   s3select_res = run_s3select(input_query, input_csv);
2801   EXPECT_EQ(s3select_res, expected_res);
2802
2803   input_query = "select count(0) from s3object where extract( year from to_timestamp(_9)) > 2010;";
2804   expected_res = "4";
2805   std::cout << "Running query: 3" << std::endl;
2806   s3select_res = run_s3select(input_query, input_csv);
2807   EXPECT_EQ(s3select_res, expected_res);
2808
2809   input_query = "select _9 from s3object where extract( year from to_timestamp(_9)) > 2010;";
2810   expected_res = "2020-10-26T11:21:30.397Z\n2013-01-30T05:27:59.2Z\n2020-06-30T11:07:01.23323-00:30\n2019-04-20T20:21:22.23+05:15\n";
2811   std::cout << "Running query: 4" << std::endl;
2812   s3select_res = run_s3select(input_query, input_csv);
2813   EXPECT_EQ(s3select_res, expected_res);
2814
2815   input_query = "select _2 from s3object where _2 like \"%11%\";";
2816   expected_res = "21169\n6611\n";
2817   std::cout << "Running query: 5" << std::endl;
2818   s3select_res = run_s3select(input_query, input_csv);
2819   EXPECT_EQ(s3select_res, expected_res);
2820
2821   input_query = "select _5 from s3object where _3 like \"__8\";";
2822   expected_res = "Bibby\n";
2823   std::cout << "Running query: 6" << std::endl;
2824   s3select_res = run_s3select(input_query, input_csv);
2825   EXPECT_EQ(s3select_res, expected_res);
2826
2827   input_query = "select _2 from s3object where _2 like \"%11\";";
2828   expected_res = "6611\n";
2829   std::cout << "Running query: 7" << std::endl;
2830   s3select_res = run_s3select(input_query, input_csv);
2831   EXPECT_EQ(s3select_res, expected_res);
2832 }
2833
2834 TEST(TestS3selectFunctions, opserialization_expressions)
2835 {
2836   std::string input;
2837   size_t size = 10;
2838   generate_rand_columns_csv(input, size);
2839
2840   char a[5] = {'@', '#', '$', '%'};
2841   char b[4] = {'!', '^', '&', '*'};
2842   char x = a[rand() % 4];
2843   char y = b[rand() % 4];
2844
2845   const std::string input_query = "select * from s3object ;";
2846
2847   run_s3select_test_opserialization(input_query, input, &x, &y);
2848
2849   const std::string input_query_1 = "select int(_1) from s3object where  nullif(_1, _2) is not null;";
2850
2851   std::string s3select_result_1 = run_s3select_opserialization_quot(input_query_1,input, true);
2852
2853   const std::string input_query_2 = "select int(_1) from s3object where int(_1) != int(_2);";
2854
2855   std::string s3select_result_2 = run_s3select(input_query_2,input);
2856
2857   std::string s3select_result_2_final = string_to_quot(s3select_result_2);
2858
2859   ASSERT_EQ(s3select_result_1, s3select_result_2_final);
2860
2861   const std::string input_query_3 = "select int(_1) from s3object where int(_1) != int(_2);";
2862
2863   std::string s3select_result_3 = run_s3select_opserialization_quot(input_query_3,input);
2864
2865   ASSERT_NE(s3select_result_1, s3select_result_3);
2866
2867   const std::string input_query_4 = "select int(_1) from s3object where  nullif(_1, _2) is not null;";
2868
2869   std::string s3select_result_4 = run_s3select_opserialization_quot(input_query_4,input, true, x);
2870
2871   const std::string input_query_5 = "select int(_1) from s3object where int(_1) != int(_2);";
2872
2873   std::string s3select_result_5 = run_s3select(input_query_5,input);
2874
2875   std::string s3select_result_5_final = string_to_quot(s3select_result_5, x);
2876
2877   ASSERT_EQ(s3select_result_4, s3select_result_5_final);
2878
2879   ASSERT_NE(s3select_result_4, s3select_result_1);
2880 }
2881
2882 void generate_csv_quote_and_escape(std::string& out, char quote = '"', char escp_ch = '\\') {
2883   std::stringstream ss;
2884
2885   ss << "1" << "," << "   1   " << "," << quote << "Apple" << "," << ":" << "," << "fruit" << quote << "," << "Apple" << "," << ":" << "," << "fruit" << std::endl;
2886   ss << std::endl;
2887   ss << "2" << "," << " 2" << "," << "Apple" << quote << "," << ":" << ","<< quote << "fruit" << "," << "Apple" << "," << ":" << "," << "fruit" << std::endl;
2888   ss << "                                               " << std::endl;
2889   ss << "#3" << "," << "#3      " << "," << "Apple" << quote << "," << quote << ":" << quote << "," <<  quote << "fruit" << "," << "Apple" << "," << ":" << "," << "fruit" << std::endl;
2890   ss << "4" << "," << "         4 " << "," << quote << quote << "Apple" << quote << "," << ":" << quote << quote << "," << quote << "fruit" << "," << "Apple" << "," << ":" << "," << "fruit" << std::endl;
2891   ss << "5" << "," << "5        " << "," << "Apple" << escp_ch <<"," << ":" << escp_ch << "," << "fruit" << "," << "Apple" << "," << ":" << "," << "fruit" << std::endl;
2892
2893   out = ss.str();
2894 }
2895
2896 TEST(TestS3selectFunctions, csv_quote_string_and_escape_char)
2897 {
2898   std::string input, s3select_result_1, s3select_result_2, s3select_result_3;
2899   csv_object::csv_defintions csv;
2900   generate_csv_quote_and_escape(input);
2901   s3select s3select_syntax1, s3select_syntax2, s3select_syntax3;
2902
2903   const std::string input_query_1 = "select _3 from s3object;";
2904   int status = s3select_syntax1.parse_query(input_query_1.c_str());
2905   ASSERT_EQ(status, 0);
2906
2907   s3selectEngine::csv_object s3_csv_object_first(&s3select_syntax1, csv);
2908   s3_csv_object_first.run_s3select_on_object(s3select_result_1, input.c_str(), input.size(), false, false, true);
2909
2910   const std::string input_query_2 = "select _4,_5,_6 from s3object;";
2911   status = s3select_syntax2.parse_query(input_query_2.c_str());
2912   ASSERT_EQ(status, 0);
2913
2914   s3selectEngine::csv_object s3_csv_object_second(&s3select_syntax2, csv);
2915   s3_csv_object_second.run_s3select_on_object(s3select_result_2, input.c_str(), input.size(), false, false, true);
2916
2917   EXPECT_EQ(s3select_result_1, s3select_result_2);
2918
2919   csv.escape_char = '\0';
2920   csv.quot_char = '\0';
2921
2922   const std::string input_query_3 = "select * from s3object;";
2923   status = s3select_syntax3.parse_query(input_query_3.c_str());
2924   ASSERT_EQ(status, 0);
2925
2926   s3selectEngine::csv_object s3_csv_object_third(&s3select_syntax3, csv);
2927   s3_csv_object_third.run_s3select_on_object(s3select_result_3, input.c_str(), input.size(), false, false, true);
2928
2929   EXPECT_EQ(s3select_result_3, input);
2930 }
2931
2932 TEST(TestS3selectFunctions, csv_comment_line_and_trim_char)
2933 {
2934   std::string input;
2935   std::string s3select_result_1, s3select_result_2;
2936   generate_csv_quote_and_escape(input);
2937   s3select s3select_syntax;
2938
2939   csv_object::csv_defintions csv;
2940   csv.comment_empty_lines = true;
2941   csv.comment_chars.push_back('#');
2942   csv.trim_chars.push_back(' ');
2943   csv.trim_chars.push_back('\t');
2944
2945   const std::string input_query_1 = "select _1 from s3object;";
2946   int status = s3select_syntax.parse_query(input_query_1.c_str());
2947   ASSERT_EQ(status, 0);
2948
2949   s3selectEngine::csv_object s3_csv_object_first(&s3select_syntax, csv);
2950   s3_csv_object_first.run_s3select_on_object(s3select_result_1, input.c_str(), input.size(), false, false, true);
2951
2952   const std::string input_query_2 = "select _2 from s3object;";
2953   status = s3select_syntax.parse_query(input_query_2.c_str());
2954   ASSERT_EQ(status, 0);
2955
2956   s3selectEngine::csv_object s3_csv_object_second(&s3select_syntax, csv);
2957   s3_csv_object_second.run_s3select_on_object(s3select_result_2, input.c_str(), input.size(), false, false, true);
2958
2959   EXPECT_EQ(s3select_result_1, s3select_result_2);
2960 }
2961
2962 TEST(TestS3selectFunctions, presto_syntax_alignments)
2963 {
2964 /*
2965  * the purpose of this test is to compare 2 queries with different syntax but with the same semantics
2966  * differences are case-insensitive, table-alias, semicolon at the end-of-statement
2967  */
2968
2969   std::string input;
2970   size_t size = 10000;
2971
2972   generate_rand_csv(input, size);
2973   std::string input_for_presto = input;
2974
2975   const std::string input_query = "select _1,_2 from s3object where _1 = _2;";
2976
2977   auto s3select_res = run_s3select(input_query, input);
2978
2979   const std::string input_presto_query = "Select t._1,t._2 fRom s3OBJECT t whEre _1 = _2";
2980
2981   const char* json_query = "select _1.c1, _1.c2 from s3object[*].root where _1.c1 = _1.c2;";
2982
2983   auto s3select_presto_res = run_s3select(input_presto_query, input_for_presto, json_query);
2984
2985   ASSERT_EQ(s3select_res, s3select_presto_res);
2986
2987 }
2988
2989 TEST(TestS3selectFunctions, csv_chunk_processing)
2990 {
2991 //purpose: s3select processes chunk after chunk, doing so it needs to handle the "broken" line issue.
2992 //i.e to identify partial line, save it and later merge it into with the other part on the next chunk.
2993 //
2994 #define STREAM_SIZE 1234
2995   std::string input_object, input_stream, s3select_result;
2996   size_t input_off = 0,input_sz = 0;
2997   int status;
2998   s3selectEngine::csv_object::csv_defintions csv;
2999   csv.use_header_info = false;
3000   s3select s3select_syntax;
3001
3002
3003   std::string input_query = "select * from stdin;";
3004   status = s3select_syntax.parse_query(input_query.c_str());
3005   s3selectEngine::csv_object  s3_csv_object(&s3select_syntax, csv);
3006
3007   generate_rand_csv(input_object, 10000);
3008   size_t size_sum = 0;
3009   std::string result_aggr;
3010
3011         while(input_off<input_object.size())
3012         {
3013                 if ((input_object.size() - input_off) < STREAM_SIZE)
3014                 {
3015                         input_sz = (input_object.size() - input_off);
3016                 }
3017                 else
3018                 {
3019                         input_sz = STREAM_SIZE;
3020                 }
3021
3022                 size_sum += input_sz;
3023                 input_stream.assign(input_object.data() + input_off, input_object.data() + input_off + input_sz);
3024                 input_off += (input_sz);
3025
3026                 status = s3_csv_object.run_s3select_on_stream(s3select_result, input_stream.data(), input_stream.size(), input_object.size());
3027
3028                 if(status<0)
3029                 {
3030                         std::cout << "failure on execution " << std::endl << s3_csv_object.get_error_description() <<  std::endl;
3031                         break;
3032                 }
3033
3034                 if(s3select_result.size()>0)
3035                 {
3036                         result_aggr.append(s3select_result);
3037                         s3select_result.clear();
3038                 }
3039                 s3select_result.clear();
3040         }//while
3041
3042          ASSERT_EQ(result_aggr,input_object);
3043 }
3044
3045 // JSON tests
3046
3047 TEST(TestS3selectFunctions, json_queries)
3048 {
3049   std::string json_input=R"(
3050 {
3051 "firstName": "Joe",
3052 "lastName": "Jackson",
3053 "gender": "male",
3054 "age": "twenty",
3055 "address": {
3056 "streetAddress": "101",
3057 "city": "San Diego",
3058 "state": "CA"
3059 },
3060
3061 "firstName": "Joe_2",
3062 "lastName": "Jackson_2",
3063 "gender": "male",
3064 "age": 21,
3065 "address": {
3066 "streetAddress": "101",
3067 "city": "San Diego",
3068 "state": "CA"
3069 },
3070
3071 "phoneNumbers": [
3072 { "type": "home1", "number": "734928_1","addr": 11 },
3073 { "type": "home2", "number": "734928_2","addr": 22 },
3074 { "type": "home3", "number": "734928_3","addr": 33 },
3075 { "type": "home4", "number": "734928_4","addr": 44 },
3076 { "type": "home5", "number": "734928_5","addr": 55 },
3077 { "type": "home6", "number": "734928_6","addr": 66 },
3078 { "type": "home7", "number": "734928_7","addr": 77 },
3079 { "type": "home8", "number": "734928_8","addr": 88 },
3080 { "type": "home9", "number": "734928_9","addr": 99 },
3081 { "type": "home10", "number": "734928_10","addr": 100 }
3082 ],
3083
3084 "key_after_array": "XXX",
3085
3086 "description" : {
3087   "main_desc" : "value_1",
3088   "second_desc" : "value_2"
3089 }
3090
3091 }
3092 )";
3093
3094   //count JSON structure, from-clause is empty.
3095   std::string result;
3096   const char* input_query = "select count(0) from s3object[*];";
3097   run_json_query(input_query, json_input,result);
3098   ASSERT_EQ(result,"1");
3099
3100   //count JSON structure, from-clause points an array of objects.
3101   input_query = "select count(0) from s3object[*].phoneNumbers;";
3102   run_json_query(input_query, json_input,result);
3103   ASSERT_EQ(result,"10");
3104
3105   //select specific key in array, from-clause points an array of objects.
3106   std::string expected_result=R"(11
3107 22
3108 33
3109 44
3110 55
3111 66
3112 77
3113 88
3114 99
3115 100
3116 )";
3117   input_query = "select _1.addr from s3object[*].phoneNumbers;";
3118   run_json_query(input_query, json_input,result);
3119   ASSERT_EQ(result,expected_result);
3120
3121   //select specific keys in array, operation on fetched value, from-clause is empty.
3122   expected_result=R"(Joe_2,XXX,25,value_1,value_2
3123 )";
3124   input_query = "select _1.firstname,_1.key_after_array,_1.age+4,_1.description.main_desc,_1.description.second_desc from s3object[*];";
3125   run_json_query(input_query, json_input,result);
3126   ASSERT_EQ(result,expected_result);
3127
3128   expected_result=R"(null,null,null
3129 null,null,null
3130 null,null,null
3131 null,null,null
3132 null,null,null
3133 null,null,null
3134 null,null,null
3135 null,null,null
3136 null,null,null
3137 null,null,null
3138 )";
3139   //select non-exists keys, from-clause points on array.
3140   input_query = "select _1.firstname,_1.key_after_array,_1.age+4 from s3object[*].phonenumbers;";
3141   run_json_query(input_query, json_input,result);
3142   ASSERT_EQ(result,expected_result);
3143
3144   expected_result=R"(7349280
3145 )";
3146
3147   //select key, operation on value, with predicate(where-clause), from-clause points on array.
3148   input_query = "select cast(substring(_1.number,1,6) as int) *10 from s3object[*].phonenumbers where _1.type='home2';";
3149   run_json_query(input_query, json_input,result);
3150   ASSERT_EQ(result,expected_result);
3151
3152   expected_result=R"(firstName. : Joe
3153 lastName. : Jackson
3154 gender. : male
3155 age. : twenty
3156 address.streetAddress. : 101
3157 address.city. : San Diego
3158 address.state. : CA
3159 firstName. : Joe_2
3160 lastName. : Jackson_2
3161 gender. : male
3162 age. : 21
3163 address.streetAddress. : 101
3164 address.city. : San Diego
3165 address.state. : CA
3166 phoneNumbers.type. : home1
3167 phoneNumbers.number. : 734928_1
3168 phoneNumbers.addr. : 11
3169 phoneNumbers.type. : home2
3170 phoneNumbers.number. : 734928_2
3171 phoneNumbers.addr. : 22
3172 phoneNumbers.type. : home3
3173 phoneNumbers.number. : 734928_3
3174 phoneNumbers.addr. : 33
3175 phoneNumbers.type. : home4
3176 phoneNumbers.number. : 734928_4
3177 phoneNumbers.addr. : 44
3178 phoneNumbers.type. : home5
3179 phoneNumbers.number. : 734928_5
3180 phoneNumbers.addr. : 55
3181 phoneNumbers.type. : home6
3182 phoneNumbers.number. : 734928_6
3183 phoneNumbers.addr. : 66
3184 phoneNumbers.type. : home7
3185 phoneNumbers.number. : 734928_7
3186 phoneNumbers.addr. : 77
3187 phoneNumbers.type. : home8
3188 phoneNumbers.number. : 734928_8
3189 phoneNumbers.addr. : 88
3190 phoneNumbers.type. : home9
3191 phoneNumbers.number. : 734928_9
3192 phoneNumbers.addr. : 99
3193 phoneNumbers.type. : home10
3194 phoneNumbers.number. : 734928_10
3195 phoneNumbers.addr. : 100
3196 key_after_array. : XXX
3197 description.main_desc. : value_1
3198 description.second_desc. : value_2
3199 #=== 0 ===#
3200 )";
3201
3202   // star-operation on object, empty from-clause
3203   input_query = "select * from s3object[*];";
3204   run_json_query(input_query, json_input,result);
3205   ASSERT_EQ(result,expected_result);
3206
3207   expected_result=R"(phoneNumbers.type. : home2
3208 phoneNumbers.number. : 734928_2
3209 phoneNumbers.addr. : 22
3210 #=== 0 ===#
3211 phoneNumbers.type. : home3
3212 phoneNumbers.number. : 734928_3
3213 phoneNumbers.addr. : 33
3214 #=== 1 ===#
3215 phoneNumbers.type. : home4
3216 phoneNumbers.number. : 734928_4
3217 phoneNumbers.addr. : 44
3218 #=== 2 ===#
3219 phoneNumbers.type. : home5
3220 phoneNumbers.number. : 734928_5
3221 phoneNumbers.addr. : 55
3222 #=== 3 ===#
3223 phoneNumbers.type. : home6
3224 phoneNumbers.number. : 734928_6
3225 phoneNumbers.addr. : 66
3226 #=== 4 ===#
3227 phoneNumbers.type. : home7
3228 phoneNumbers.number. : 734928_7
3229 phoneNumbers.addr. : 77
3230 #=== 5 ===#
3231 phoneNumbers.type. : home8
3232 phoneNumbers.number. : 734928_8
3233 phoneNumbers.addr. : 88
3234 #=== 6 ===#
3235 )";
3236
3237   // star-operation on object, from-clause points on array, with where-clause
3238   input_query = "select * from s3object[*].phonenumbers where _1.addr between 20 and 89;";
3239   run_json_query(input_query, json_input,result);
3240   ASSERT_EQ(result,expected_result);
3241
3242 }
3243
3244 TEST(TestS3selectFunctions, json_queries_with_array)
3245 {
3246   std::string result;
3247   std::string expected_result;
3248   std::string input_query;
3249
3250   std::string INPUT_TEST_ARRAY_NEDICATIONS = R"(
3251 {
3252 "problems": [{
3253     "Diabetes":[{
3254         "medications":[{
3255             "medicationsClasses":[{
3256                 "className":[{
3257                     "associatedDrug":[{
3258                         "name":"asprin",
3259                         "dose":"",
3260                         "strength":"500 mg"
3261                     },
3262                     { "name":"acamol" }
3263                     ],
3264                     "associatedDrug2":[{
3265                         "name":"somethingElse",
3266                         "dose":"",
3267                         "strength":"500 mg"
3268                     }]
3269                 }],
3270                 "className2":[{
3271                     "associatedDrug":[{
3272                         "name":"asprin",
3273                         "dose":"",
3274                         "strength":"500 mg"
3275                     }],
3276                     "associatedDrug2":[{
3277                         "name":"somethingElse",
3278                         "dose":"",
3279                         "strength":"500 mg"
3280                     }]
3281                 }]
3282             }]
3283         }],
3284         "labs":[{
3285             "missing_field": "missing_value"
3286         }]
3287     }],
3288     "Asthma":[{}]
3289 }]
3290 })";
3291
3292   expected_result=R"(acamol
3293 )";
3294
3295   //access a JSON document containing a complex and nested arrays. this query accesses the object="name" at the second element of associatedDrug (within nested arrays)
3296   input_query = "select _1.problems[0].Diabetes[0].medications[0].medicationsClasses[0].className[0].associatedDrug[1].name from s3object[*];";
3297   run_json_query(input_query.c_str(), INPUT_TEST_ARRAY_NEDICATIONS, result);
3298   ASSERT_EQ(result,expected_result);
3299
3300   expected_result=R"(asprin
3301 )";
3302
3303   //access a JSON document containing a complex and nested arrays. this query accesses the object="name" at the first element of associatedDrug (within nested arrays)
3304   input_query = "select _1.problems[0].Diabetes[0].medications[0].medicationsClasses[0].className[0].associatedDrug[0].name from s3object[*];";
3305   run_json_query(input_query.c_str(), INPUT_TEST_ARRAY_NEDICATIONS, result);
3306   ASSERT_EQ(result,expected_result);
3307
3308   expected_result=R"(somethingElse
3309 )";
3310   //access a JSON document containing a complex and nested arrays. this query accesses the object="name" at the first element of associatedDrug2 (within nested arrays)
3311   input_query = "select _1.problems[0].Diabetes[0].medications[0].medicationsClasses[0].className[0].associatedDrug2[0].name from s3object[*];";
3312   run_json_query(input_query.c_str(), INPUT_TEST_ARRAY_NEDICATIONS, result);
3313   ASSERT_EQ(result,expected_result);
3314
3315 }
3316
3317 TEST(TestS3selectFunctions, json_queries_with_multi_dimensional_array)
3318 {
3319   std::string result;
3320   std::string expected_result;
3321   std::string input_query;
3322
3323   //return; //the syntax parser should be modified to accept array[1][2][3]
3324
3325 std::string input_json_data = R"(
3326 {
3327 "firstName": "Joe",
3328 "lastName": "Jackson",
3329 "gender": "male",
3330 "age": "twenty",
3331 "address": {
3332 "streetAddress": "101",
3333 "city": "San Diego",
3334 "state": "CA"
3335 },
3336
3337 "firstName": "Joe_2",
3338 "lastName": "Jackson_2",
3339 "gender": "male",
3340 "age": 21,
3341 "address": {
3342 "streetAddress": "101",
3343 "city": "San Diego",
3344 "state": "CA"
3345 },
3346
3347 "phoneNumbers": [
3348 { "type": "home0", "number": "734928_0", "addr": 0 },
3349 { "type": "home1", "number": "734928_1", "addr": 11 },
3350 { "type": "home2", "number": "734928_2", "addr": 22 },
3351 { "type": "home3", "number": "734928_3", "addr": 33 },
3352 { "type": "home4", "number": "734928_4", "addr": 44 },
3353 { "type": "home5", "number": "734928_5", "addr": 55 },
3354 { "type": "home6", "number": "734928_6", "addr": 66 },
3355 { "type": "home7", "number": "734928_7", "addr": 77 },
3356 { "type": "home8", "number": "734928_8", "addr": 88 },
3357 { "type": "home9", "number": "734928_9", "addr": 99 },
3358 { "type": "home10", "number": "734928_10", "addr": 100 },
3359 "element-11",
3360   [ 11 , 22 ,
3361     [ 44, 55] ,"post 3D" ,
3362     {
3363       "first_key_in_object_in_array" : "value_for_irst_key_in_object_in_array",
3364       "key_in_array" : "value_per_key_in_array"
3365     }
3366   ],
3367   {"classname" : "stam"},
3368   { "associatedDrug":[{
3369                         "name":"asprin",
3370                         "dose":"",
3371                         "strength":"500 mg"
3372                     }],
3373                     "associatedDrug#2":[{
3374                         "name":"somethingElse",
3375                         "dose":"",
3376                         "strength":"500 mg"
3377                     }]
3378 }
3379 ],
3380 "key_after_array": "XXX"
3381 }
3382 )";
3383
3384   expected_result=R"(null
3385 )";
3386   //phoneNumbers[12][2][2] is not a discrete value, should return null
3387   input_query = "select _1.phoneNumbers[12][2][2] from s3object[*];";
3388   run_json_query(input_query.c_str(), input_json_data, result);
3389   ASSERT_EQ(result,expected_result);
3390
3391   //the following tests ia about accessing multi-dimension array
3392   expected_result=R"(55
3393 )";
3394   input_query = "select _1.phoneNumbers[12][2][1] from s3object[*];";
3395   run_json_query(input_query.c_str(), input_json_data, result);
3396   ASSERT_EQ(result,expected_result);
3397
3398
3399   expected_result=R"(post 3D
3400 )";
3401   input_query = "select _1.phoneNumbers[12][3] from s3object[*];";
3402   run_json_query(input_query.c_str(), input_json_data, result);
3403   ASSERT_EQ(result,expected_result);
3404
3405   expected_result=R"(11
3406 )";
3407   input_query = "select _1.phoneNumbers[12][0] from s3object[*];";
3408   run_json_query(input_query.c_str(), input_json_data, result);
3409   ASSERT_EQ(result,expected_result);
3410
3411   expected_result=R"(element-11
3412 )";
3413   input_query = "select _1.phoneNumbers[11] from s3object[*];";
3414   run_json_query(input_query.c_str(), input_json_data, result);
3415   ASSERT_EQ(result,expected_result);
3416
3417 input_json_data = R"(
3418 [
3419   {
3420     "authors": [
3421       {
3422         "id": 2312688602
3423       },
3424       {
3425         "id": 123
3426       }
3427     ],
3428     "wrong" : {"id" : "it-is-wrong"}
3429   }
3430 ]
3431 )";
3432
3433   expected_result=R"(2312688602
3434 )";
3435   input_query = "select _1.authors[0].id from s3object[*];";
3436   run_json_query(input_query.c_str(), input_json_data, result);
3437   ASSERT_EQ(result,expected_result);
3438
3439   expected_result=R"(123
3440 )";
3441   input_query = "select _1.authors[1].id from s3object[*];";
3442   run_json_query(input_query.c_str(), input_json_data, result);
3443   ASSERT_EQ(result,expected_result);
3444
3445
3446  }