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