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