| ==== |
| ---- QUERY |
| # Regression test for IMPALA-938 |
| select smallint_col, int_col, (cast("1970-01-01" as timestamp) + interval smallint_col days) |
| from functional.alltypes where smallint_col = 1 limit 1 |
| ---- RESULTS |
| 1,1,1970-01-02 00:00:00 |
| ---- TYPES |
| smallint, int, timestamp |
| ==== |
| ---- QUERY |
| # check that add_months alias is working |
| select ADD_MONTHS(cast('2013-02-18 16:46:00.01' as timestamp), 1) |
| ---- RESULTS |
| 2013-03-18 16:46:00.010000000 |
| ---- TYPES |
| timestamp |
| ==== |
| ---- QUERY |
| # test extract with non-constant field name |
| select b.unit, extract(a.ts, b.unit) from |
| (values(cast('2013-02-18 16:46:00.01' as timestamp) ts)) a |
| cross join |
| (values('year' unit), ('month'), ('day'), ('hour'), ('minute'), ('second'), |
| ('millisecond'), ('epoch' )) b |
| ---- RESULTS |
| 'year',2013 |
| 'month',2 |
| 'day',18 |
| 'hour',16 |
| 'minute',46 |
| 'second',0 |
| 'millisecond',10 |
| 'epoch',1361205960 |
| ---- TYPES |
| string, int |
| ==== |
| ---- QUERY |
| # EXTRACT fields from timestamp |
| select EXTRACT(timestamp_col, 'yEar'), EXTRACT(timestamp_col, 'MilliSECond') |
| from alltypesagg order by id limit 5 |
| ---- RESULTS |
| 2010,0 |
| 2010,0 |
| 2010,0 |
| 2010,100 |
| 2010,300 |
| ---- TYPES |
| int, int |
| ==== |
| ---- QUERY |
| # IS NULL predicate |
| select count(*) from alltypesagg |
| ---- RESULTS |
| 11000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where tinyint_col is null |
| ---- RESULTS |
| 2000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where tinyint_col is not null |
| ---- RESULTS |
| 9000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # boolean test: IS [NOT] (TRUE | FALSE | UNKNOWN) |
| select count(*) from alltypesagg where (int_col < 100) is unknown; |
| ---- RESULTS |
| 20 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where (int_col < 100) is true; |
| ---- RESULTS |
| 1080 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where (int_col < 100) is false; |
| ---- RESULTS |
| 9900 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where (int_col < 100) is not unknown; |
| ---- RESULTS |
| 10980 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where (int_col < 100) is not true; |
| ---- RESULTS |
| 9920 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where (int_col < 100) is not false; |
| ---- RESULTS |
| 1100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # = |
| select count(*) from alltypesagg where tinyint_col = 1 |
| ---- RESULTS |
| 1000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col = 1 |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col = 1 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col = 10 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where float_col = cast(1.1 as float) |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col = 10.1 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where date_string_col = '01/01/10' |
| ---- RESULTS |
| 1100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # <> |
| select count(*) from alltypesagg where tinyint_col <> 1 |
| ---- RESULTS |
| 8000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col <> 1 |
| ---- RESULTS |
| 10700 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col <> 1 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col <> 1 |
| ---- RESULTS |
| 10980 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where float_col <> cast(1.1 as float) |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col <> 10.1 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # < |
| select count(*) from alltypesagg where tinyint_col < 2 |
| ---- RESULTS |
| 1000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col < 2 |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col < 2 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col < 20 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where float_col < 2.2 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col < 20.2 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # > |
| select count(*) from alltypesagg where tinyint_col > 1 |
| ---- RESULTS |
| 8000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col > 1 |
| ---- RESULTS |
| 10700 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col > 1 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col > 10 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where float_col > cast(1.1 as float) |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col > 10.1 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # <= |
| select count(*) from alltypesagg where tinyint_col <= 1 |
| ---- RESULTS |
| 1000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col <= 1 |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col <= 1 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col <= 10 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where float_col <= cast(1.1 as float) |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col <= 10.1 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # >= |
| select count(*) from alltypesagg where tinyint_col >= 2 |
| ---- RESULTS |
| 8000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col >= 2 |
| ---- RESULTS |
| 10700 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col >= 2 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col >= 20 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where float_col >= 2.2 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col >= 20.2 |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Type synonym check: DOUBLE = REAL |
| select count(*) from alltypesagg where double_col >= 20.2 and cast(double_col as double) = cast(double_col as real) |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Type synonym check: INT = INTEGER |
| select count(*) from alltypesagg where double_col >= 20.2 and cast(double_col as int) = cast(double_col as integer) |
| ---- RESULTS |
| 10970 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select |
| SUM(tinyint_col) + SUM(smallint_col), |
| SUM(tinyint_col + smallint_col), |
| SUM(tinyint_col) + SUM(smallint_col) + SUM(int_col), |
| SUM(tinyint_col + smallint_col + int_col), |
| SUM(tinyint_col) + SUM(smallint_col) + SUM(int_col) + SUM(bigint_col), |
| SUM(tinyint_col + smallint_col + int_col + bigint_col), |
| ROUND(SUM(tinyint_col) + SUM(smallint_col) + SUM(int_col) + SUM(bigint_col) + |
| SUM(float_col), 4), |
| ROUND(SUM(tinyint_col + smallint_col + int_col + bigint_col + float_col), 4), |
| ROUND(SUM(tinyint_col) + SUM(smallint_col) + SUM(int_col) + SUM(bigint_col) + |
| SUM(float_col) + SUM(double_col), 4), |
| ROUND(SUM(tinyint_col + smallint_col + int_col + bigint_col + float_col + double_col), 4) |
| from alltypesaggnonulls |
| ---- TYPES |
| bigint, bigint, bigint, bigint, bigint, bigint, double, double, double, double |
| ---- RESULTS |
| 540000,540000,5535000,5535000,55485000,55485000,60979499.9998,60979499.9998,111428999.9998,111428999.9998 |
| ==== |
| ---- QUERY |
| select |
| -1 * SUM(tinyint_col) - SUM(smallint_col), |
| SUM(-1 * tinyint_col - smallint_col), |
| -1 * SUM(tinyint_col) - SUM(smallint_col) - SUM(int_col), |
| SUM(-1 * tinyint_col - smallint_col - int_col), |
| -1 * SUM(tinyint_col) - SUM(smallint_col) - SUM(int_col) - SUM(bigint_col), |
| SUM(-1 * tinyint_col - smallint_col - int_col - bigint_col), |
| ROUND(-1 * SUM(tinyint_col) - SUM(smallint_col) - SUM(int_col) - |
| SUM(bigint_col) - SUM(float_col), 4), |
| ROUND(SUM(-1 * tinyint_col - smallint_col - int_col - bigint_col - float_col), 4), |
| ROUND(-1 * SUM(tinyint_col) - SUM(smallint_col) - SUM(int_col) - |
| SUM(bigint_col) - SUM(float_col) - SUM(double_col), 4), |
| ROUND(SUM(-1 * tinyint_col - smallint_col - int_col - |
| bigint_col - float_col - double_col), 4) |
| from alltypesaggnonulls |
| ---- TYPES |
| bigint, bigint, bigint, bigint, bigint, bigint, double, double, double, double |
| ---- RESULTS |
| -540000,-540000,-5535000,-5535000,-55485000,-55485000,-60979499.9998,-60979499.9998,-111428999.9998,-111428999.9998 |
| ==== |
| ---- QUERY |
| select |
| MAX(tinyint_col) * MAX(smallint_col), |
| MAX(tinyint_col * smallint_col), |
| MAX(tinyint_col) * MAX(smallint_col) * MAX(int_col), |
| MAX(tinyint_col * smallint_col * int_col), |
| MAX(tinyint_col) * MAX(smallint_col) * MAX(int_col) * MAX(bigint_col), |
| MAX(tinyint_col * smallint_col * int_col * bigint_col), |
| ROUND(MAX(tinyint_col) * MAX(smallint_col) * MAX(int_col) * |
| MAX(bigint_col) * MAX(float_col), 4), |
| ROUND(MAX(tinyint_col * smallint_col * int_col * bigint_col * float_col), 4), |
| ROUND(MAX(tinyint_col) * MAX(smallint_col) * MAX(int_col) * |
| MAX(bigint_col) * MAX(float_col) * MAX(double_col), 4), |
| ROUND(MAX(tinyint_col * smallint_col * int_col * bigint_col * float_col * double_col), 4) |
| from alltypesaggnonulls |
| ---- TYPES |
| int, int, bigint, bigint, bigint, bigint, double, double, double, double |
| ---- RESULTS |
| 891,891,890109,890109,8892188910,8892188910,9771626610293.455,9771626610293.455,9.859473533519994e+16,9.859473533519994e+16 |
| ==== |
| ---- QUERY |
| # LIKE exprs w/ the like/regex pattern coming from a column |
| select * from LikeTbl |
| ---- RESULTS |
| 'NULL','%','NULL','.*','X' |
| '','%','','.*','X' |
| 'beginning of line','begin%','not begin%','^begin.*','^not begin.*' |
| 'eight','%eight%','n%eight%','.*eight.*','n.*eight.*' |
| 'end of line','%line','%line end','.*line$','.*line end$' |
| 'five','%five%','n%five%','.*five.*','n.*five.*' |
| 'four','%four%','n%four%','.*four.*','n.*four.*' |
| 'middle of line','%of%','%of','^.*of.*$','.*of$' |
| 'nine','%nine%','n%nine%','.*nine.*','n.*nine.*' |
| 'one','%one%','n%one%','.*one.*','n.*one.*' |
| 'seven','%seven%','n%seven%','.*seven.*','n.*seven.*' |
| 'six','%six%','n%six%','.*six.*','n.*six.*' |
| 'ten','%ten%','n%ten%','.*ten.*','n.*ten.*' |
| 'three','%three%','n%three%','.*three.*','n.*three.*' |
| 'two','%two%','n%two%','.*two.*','n.*two.*' |
| ---- TYPES |
| string, string, string, string, string |
| ==== |
| ---- QUERY |
| select str_col, match_like_col from LikeTbl |
| where str_col LIKE match_like_col |
| ---- RESULTS |
| 'beginning of line','begin%' |
| 'eight','%eight%' |
| 'end of line','%line' |
| 'five','%five%' |
| 'four','%four%' |
| 'middle of line','%of%' |
| 'nine','%nine%' |
| 'one','%one%' |
| 'seven','%seven%' |
| 'six','%six%' |
| 'ten','%ten%' |
| 'three','%three%' |
| 'two','%two%' |
| '','%' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_like_col from LikeTbl |
| where str_col NOT LIKE match_like_col |
| ---- RESULTS |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_like_col from LikeTbl |
| where str_col LIKE no_match_like_col |
| ---- RESULTS |
| '','%' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, no_match_like_col from LikeTbl |
| where str_col NOT LIKE no_match_like_col |
| ---- RESULTS |
| 'beginning of line','not begin%' |
| 'eight','n%eight%' |
| 'end of line','%line end' |
| 'five','n%five%' |
| 'four','n%four%' |
| 'middle of line','%of' |
| 'nine','n%nine%' |
| 'one','n%one%' |
| 'seven','n%seven%' |
| 'six','n%six%' |
| 'ten','n%ten%' |
| 'three','n%three%' |
| 'two','n%two%' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where str_col REGEXP match_regex_col |
| ---- RESULTS |
| 'beginning of line','^begin.*' |
| 'eight','.*eight.*' |
| 'end of line','.*line$' |
| 'five','.*five.*' |
| 'four','.*four.*' |
| 'middle of line','^.*of.*$' |
| 'nine','.*nine.*' |
| 'one','.*one.*' |
| 'seven','.*seven.*' |
| 'six','.*six.*' |
| 'ten','.*ten.*' |
| 'three','.*three.*' |
| 'two','.*two.*' |
| '','.*' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, no_match_regex_col from LikeTbl |
| where str_col REGEXP no_match_regex_col |
| ---- RESULTS |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where str_col NOT REGEXP match_regex_col |
| ---- RESULTS |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, no_match_regex_col from LikeTbl |
| where str_col NOT REGEXP no_match_regex_col |
| ---- RESULTS |
| 'beginning of line','^not begin.*' |
| 'eight','n.*eight.*' |
| 'end of line','.*line end$' |
| 'five','n.*five.*' |
| 'four','n.*four.*' |
| 'middle of line','.*of$' |
| 'nine','n.*nine.*' |
| 'one','n.*one.*' |
| 'seven','n.*seven.*' |
| 'six','n.*six.*' |
| 'ten','n.*ten.*' |
| 'three','n.*three.*' |
| 'two','n.*two.*' |
| '','X' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select 1+2 |
| ---- RESULTS |
| 3 |
| ---- TYPES |
| smallint |
| ==== |
| ---- QUERY |
| select timestamp_col, to_date(timestamp_col), year(timestamp_col), month(timestamp_col), |
| dayofmonth(timestamp_col), dayofyear(timestamp_col), weekofyear(timestamp_col), |
| hour(timestamp_col), minute(timestamp_col), second(timestamp_col) |
| from alltypessmall |
| ---- RESULTS |
| 2009-01-01 00:00:00,'2009-01-01',2009,1,1,1,1,0,0,0 |
| 2009-01-01 00:01:00,'2009-01-01',2009,1,1,1,1,0,1,0 |
| 2009-01-01 00:02:00.100000000,'2009-01-01',2009,1,1,1,1,0,2,0 |
| 2009-01-01 00:03:00.300000000,'2009-01-01',2009,1,1,1,1,0,3,0 |
| 2009-01-01 00:04:00.600000000,'2009-01-01',2009,1,1,1,1,0,4,0 |
| 2009-01-01 00:05:00.100000000,'2009-01-01',2009,1,1,1,1,0,5,0 |
| 2009-01-01 00:06:00.150000000,'2009-01-01',2009,1,1,1,1,0,6,0 |
| 2009-01-01 00:07:00.210000000,'2009-01-01',2009,1,1,1,1,0,7,0 |
| 2009-01-01 00:08:00.280000000,'2009-01-01',2009,1,1,1,1,0,8,0 |
| 2009-01-01 00:09:00.360000000,'2009-01-01',2009,1,1,1,1,0,9,0 |
| 2009-01-02 00:10:00.450000000,'2009-01-02',2009,1,2,2,1,0,10,0 |
| 2009-01-02 00:11:00.450000000,'2009-01-02',2009,1,2,2,1,0,11,0 |
| 2009-01-02 00:12:00.460000000,'2009-01-02',2009,1,2,2,1,0,12,0 |
| 2009-01-02 00:13:00.480000000,'2009-01-02',2009,1,2,2,1,0,13,0 |
| 2009-01-02 00:14:00.510000000,'2009-01-02',2009,1,2,2,1,0,14,0 |
| 2009-01-02 00:15:00.550000000,'2009-01-02',2009,1,2,2,1,0,15,0 |
| 2009-01-02 00:16:00.600000000,'2009-01-02',2009,1,2,2,1,0,16,0 |
| 2009-01-02 00:17:00.660000000,'2009-01-02',2009,1,2,2,1,0,17,0 |
| 2009-01-02 00:18:00.730000000,'2009-01-02',2009,1,2,2,1,0,18,0 |
| 2009-01-02 00:19:00.810000000,'2009-01-02',2009,1,2,2,1,0,19,0 |
| 2009-01-03 00:20:00.900000000,'2009-01-03',2009,1,3,3,1,0,20,0 |
| 2009-01-03 00:21:00.900000000,'2009-01-03',2009,1,3,3,1,0,21,0 |
| 2009-01-03 00:22:00.910000000,'2009-01-03',2009,1,3,3,1,0,22,0 |
| 2009-01-03 00:23:00.930000000,'2009-01-03',2009,1,3,3,1,0,23,0 |
| 2009-01-03 00:24:00.960000000,'2009-01-03',2009,1,3,3,1,0,24,0 |
| 2009-02-01 00:00:00,'2009-02-01',2009,2,1,32,5,0,0,0 |
| 2009-02-01 00:01:00,'2009-02-01',2009,2,1,32,5,0,1,0 |
| 2009-02-01 00:02:00.100000000,'2009-02-01',2009,2,1,32,5,0,2,0 |
| 2009-02-01 00:03:00.300000000,'2009-02-01',2009,2,1,32,5,0,3,0 |
| 2009-02-01 00:04:00.600000000,'2009-02-01',2009,2,1,32,5,0,4,0 |
| 2009-02-01 00:05:00.100000000,'2009-02-01',2009,2,1,32,5,0,5,0 |
| 2009-02-01 00:06:00.150000000,'2009-02-01',2009,2,1,32,5,0,6,0 |
| 2009-02-01 00:07:00.210000000,'2009-02-01',2009,2,1,32,5,0,7,0 |
| 2009-02-01 00:08:00.280000000,'2009-02-01',2009,2,1,32,5,0,8,0 |
| 2009-02-01 00:09:00.360000000,'2009-02-01',2009,2,1,32,5,0,9,0 |
| 2009-02-02 00:10:00.450000000,'2009-02-02',2009,2,2,33,6,0,10,0 |
| 2009-02-02 00:11:00.450000000,'2009-02-02',2009,2,2,33,6,0,11,0 |
| 2009-02-02 00:12:00.460000000,'2009-02-02',2009,2,2,33,6,0,12,0 |
| 2009-02-02 00:13:00.480000000,'2009-02-02',2009,2,2,33,6,0,13,0 |
| 2009-02-02 00:14:00.510000000,'2009-02-02',2009,2,2,33,6,0,14,0 |
| 2009-02-02 00:15:00.550000000,'2009-02-02',2009,2,2,33,6,0,15,0 |
| 2009-02-02 00:16:00.600000000,'2009-02-02',2009,2,2,33,6,0,16,0 |
| 2009-02-02 00:17:00.660000000,'2009-02-02',2009,2,2,33,6,0,17,0 |
| 2009-02-02 00:18:00.730000000,'2009-02-02',2009,2,2,33,6,0,18,0 |
| 2009-02-02 00:19:00.810000000,'2009-02-02',2009,2,2,33,6,0,19,0 |
| 2009-02-03 00:20:00.900000000,'2009-02-03',2009,2,3,34,6,0,20,0 |
| 2009-02-03 00:21:00.900000000,'2009-02-03',2009,2,3,34,6,0,21,0 |
| 2009-02-03 00:22:00.910000000,'2009-02-03',2009,2,3,34,6,0,22,0 |
| 2009-02-03 00:23:00.930000000,'2009-02-03',2009,2,3,34,6,0,23,0 |
| 2009-02-03 00:24:00.960000000,'2009-02-03',2009,2,3,34,6,0,24,0 |
| 2009-03-01 00:00:00,'2009-03-01',2009,3,1,60,9,0,0,0 |
| 2009-03-01 00:01:00,'2009-03-01',2009,3,1,60,9,0,1,0 |
| 2009-03-01 00:02:00.100000000,'2009-03-01',2009,3,1,60,9,0,2,0 |
| 2009-03-01 00:03:00.300000000,'2009-03-01',2009,3,1,60,9,0,3,0 |
| 2009-03-01 00:04:00.600000000,'2009-03-01',2009,3,1,60,9,0,4,0 |
| 2009-03-01 00:05:00.100000000,'2009-03-01',2009,3,1,60,9,0,5,0 |
| 2009-03-01 00:06:00.150000000,'2009-03-01',2009,3,1,60,9,0,6,0 |
| 2009-03-01 00:07:00.210000000,'2009-03-01',2009,3,1,60,9,0,7,0 |
| 2009-03-01 00:08:00.280000000,'2009-03-01',2009,3,1,60,9,0,8,0 |
| 2009-03-01 00:09:00.360000000,'2009-03-01',2009,3,1,60,9,0,9,0 |
| 2009-03-02 00:10:00.450000000,'2009-03-02',2009,3,2,61,10,0,10,0 |
| 2009-03-02 00:11:00.450000000,'2009-03-02',2009,3,2,61,10,0,11,0 |
| 2009-03-02 00:12:00.460000000,'2009-03-02',2009,3,2,61,10,0,12,0 |
| 2009-03-02 00:13:00.480000000,'2009-03-02',2009,3,2,61,10,0,13,0 |
| 2009-03-02 00:14:00.510000000,'2009-03-02',2009,3,2,61,10,0,14,0 |
| 2009-03-02 00:15:00.550000000,'2009-03-02',2009,3,2,61,10,0,15,0 |
| 2009-03-02 00:16:00.600000000,'2009-03-02',2009,3,2,61,10,0,16,0 |
| 2009-03-02 00:17:00.660000000,'2009-03-02',2009,3,2,61,10,0,17,0 |
| 2009-03-02 00:18:00.730000000,'2009-03-02',2009,3,2,61,10,0,18,0 |
| 2009-03-02 00:19:00.810000000,'2009-03-02',2009,3,2,61,10,0,19,0 |
| 2009-03-03 00:20:00.900000000,'2009-03-03',2009,3,3,62,10,0,20,0 |
| 2009-03-03 00:21:00.900000000,'2009-03-03',2009,3,3,62,10,0,21,0 |
| 2009-03-03 00:22:00.910000000,'2009-03-03',2009,3,3,62,10,0,22,0 |
| 2009-03-03 00:23:00.930000000,'2009-03-03',2009,3,3,62,10,0,23,0 |
| 2009-03-03 00:24:00.960000000,'2009-03-03',2009,3,3,62,10,0,24,0 |
| 2009-04-01 00:00:00,'2009-04-01',2009,4,1,91,14,0,0,0 |
| 2009-04-01 00:01:00,'2009-04-01',2009,4,1,91,14,0,1,0 |
| 2009-04-01 00:02:00.100000000,'2009-04-01',2009,4,1,91,14,0,2,0 |
| 2009-04-01 00:03:00.300000000,'2009-04-01',2009,4,1,91,14,0,3,0 |
| 2009-04-01 00:04:00.600000000,'2009-04-01',2009,4,1,91,14,0,4,0 |
| 2009-04-01 00:05:00.100000000,'2009-04-01',2009,4,1,91,14,0,5,0 |
| 2009-04-01 00:06:00.150000000,'2009-04-01',2009,4,1,91,14,0,6,0 |
| 2009-04-01 00:07:00.210000000,'2009-04-01',2009,4,1,91,14,0,7,0 |
| 2009-04-01 00:08:00.280000000,'2009-04-01',2009,4,1,91,14,0,8,0 |
| 2009-04-01 00:09:00.360000000,'2009-04-01',2009,4,1,91,14,0,9,0 |
| 2009-04-02 00:10:00.450000000,'2009-04-02',2009,4,2,92,14,0,10,0 |
| 2009-04-02 00:11:00.450000000,'2009-04-02',2009,4,2,92,14,0,11,0 |
| 2009-04-02 00:12:00.460000000,'2009-04-02',2009,4,2,92,14,0,12,0 |
| 2009-04-02 00:13:00.480000000,'2009-04-02',2009,4,2,92,14,0,13,0 |
| 2009-04-02 00:14:00.510000000,'2009-04-02',2009,4,2,92,14,0,14,0 |
| 2009-04-02 00:15:00.550000000,'2009-04-02',2009,4,2,92,14,0,15,0 |
| 2009-04-02 00:16:00.600000000,'2009-04-02',2009,4,2,92,14,0,16,0 |
| 2009-04-02 00:17:00.660000000,'2009-04-02',2009,4,2,92,14,0,17,0 |
| 2009-04-02 00:18:00.730000000,'2009-04-02',2009,4,2,92,14,0,18,0 |
| 2009-04-02 00:19:00.810000000,'2009-04-02',2009,4,2,92,14,0,19,0 |
| 2009-04-03 00:20:00.900000000,'2009-04-03',2009,4,3,93,14,0,20,0 |
| 2009-04-03 00:21:00.900000000,'2009-04-03',2009,4,3,93,14,0,21,0 |
| 2009-04-03 00:22:00.910000000,'2009-04-03',2009,4,3,93,14,0,22,0 |
| 2009-04-03 00:23:00.930000000,'2009-04-03',2009,4,3,93,14,0,23,0 |
| 2009-04-03 00:24:00.960000000,'2009-04-03',2009,4,3,93,14,0,24,0 |
| ---- TYPES |
| timestamp, string, int, int, int, int, int, int, int, int |
| ==== |
| ---- QUERY |
| # IN predicate |
| select NULL in (1, 2, 3) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL in (1, NULL, 3) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 in (2, NULL, 1) |
| ---- RESULTS |
| true |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 in (1, NULL, 2) |
| ---- RESULTS |
| true |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 in (2, NULL, 3) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 in (2, 3, 4) |
| ---- RESULTS |
| false |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL not in (1, 2, 3) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL not in (1, NULL, 3) |
| ---- RESULTS |
| NULL |
| ==== |
| ---- QUERY |
| select 1 not in (2, NULL, 1) |
| ---- RESULTS |
| false |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 not in (1, NULL, 2) |
| ---- RESULTS |
| false |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 not in (2, NULL, 3) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select 1 not in (2, 3, 4) |
| ---- RESULTS |
| true |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| # IN predicate with NULLs and other types |
| select NULL in ('a', NULL, 'b') |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL not in ('a', NULL, 'b') |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL not in (1.0, NULL, 2.0) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL in (1.0, NULL, 2.0) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL in (true, NULL, false) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select NULL not in (true, NULL, false) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select true in (NULL, false) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| select true not in (NULL, false) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| # In predicate with strings |
| select int_col, string_col from alltypesagg |
| where string_col in ('212', '768', '1242', '124141', '0000', '99999') |
| order by int_col limit 20 |
| ---- RESULTS |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 212,'212' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| 768,'768' |
| ---- TYPES |
| int,string |
| ==== |
| ---- QUERY |
| # IMPALA-2495: In predicate containing a TupleIsNullPredicate |
| select count(id) from functional.alltypestiny t1 |
| left join (select coalesce(1, 10) as int_col |
| from functional.alltypessmall) t2 |
| on t1.id = t2.int_col |
| where t2.int_col in (t2.int_col, 10) |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where true in (bool_col, tinyint_col) |
| ---- RESULTS |
| 7000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where true not in (bool_col, tinyint_col) |
| ---- RESULTS |
| 4000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where 1 in (tinyint_col, smallint_col, int_col, bigint_col) |
| ---- RESULTS |
| 1000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where 1 not in (tinyint_col, smallint_col, int_col, bigint_col) |
| ---- RESULTS |
| 8000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where 10.1 in (tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col) |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where 10.1 not in (tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col) |
| ---- RESULTS |
| 8990 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where '01/01/10' in (date_string_col, string_col, 'abc') |
| ---- RESULTS |
| 1100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where '01/01/10' not in (date_string_col, string_col, 'abc') |
| ---- RESULTS |
| 9900 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where cast('2010-01-01 00:00:00' as timestamp) in (timestamp_col) |
| ---- RESULTS |
| 2 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where cast('2010-01-01 00:00:00' as timestamp) not in (timestamp_col) |
| ---- RESULTS |
| 10998 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1810 |
| SELECT tinyint_col, id FROM alltypestiny t1 WHERE t1.tinyint_col IN (154.58, t1.id) |
| ---- RESULTS |
| 0,0 |
| 1,1 |
| ---- TYPES |
| tinyint,int |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1810 |
| SELECT month, t1.tinyint_col FROM alltypestiny t1 |
| WHERE t1.month NOT IN (355.8, t1.tinyint_col) |
| ---- RESULTS |
| 2,0 |
| 2,1 |
| 3,0 |
| 3,1 |
| 1,0 |
| 4,0 |
| 4,1 |
| ---- TYPES |
| int,tinyint |
| ==== |
| ---- QUERY |
| select month, tinyint_col, int_col, float_col FROM alltypestiny |
| WHERE month IN (cast(tinyint_col as decimal(34,0)), int_col + 1, 12345, float_col) |
| ---- RESULTS |
| 2,1,1,1.1 |
| 1,0,0,0 |
| 1,1,1,1.1 |
| ---- TYPES |
| int,tinyint,int,float |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1949 |
| select bool_col = (tinyint_col in (1,2)) from alltypestiny |
| ---- RESULTS |
| false |
| false |
| false |
| false |
| false |
| false |
| false |
| false |
| ---- TYPES |
| boolean |
| ==== |
| ---- QUERY |
| # CASE expr - Basic functionality |
| select date_string_col, count(*) from alltypesagg where case date_string_col when "01/06/10" then (true) |
| else (false) end group by 1 |
| ---- RESULTS |
| '01/06/10',1100 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col, count(*) from alltypesagg where case date_string_col when "01/06/10" then (false) |
| else (true) end group by 1 |
| ---- RESULTS |
| '01/08/10',1100 |
| '01/09/10',1100 |
| '01/02/10',1100 |
| '01/01/10',1100 |
| '01/03/10',1100 |
| '01/04/10',1100 |
| '01/10/10',1100 |
| '01/07/10',1100 |
| '01/05/10',1100 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| # CASE expr - then expr will return NULL |
| select date_string_col, count(*) from alltypesagg where case date_string_col when "01/06/10" then (NULL) |
| else (false) end is null group by 1 |
| ---- RESULTS |
| '01/06/10',1100 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col, count(*) from alltypesagg where case date_string_col when NULL then (NULL) else |
| (false) end is null group by 1 |
| ---- RESULTS |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select tinyint_col, count(*) from alltypesagg where case tinyint_col when 5 then true when 6 then |
| true else NULL end group by 1 |
| ---- RESULTS |
| 6,1000 |
| 5,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # CASE expr - test multiple when exprs, ensure NULL when expr does not return |
| # corresponding then expr |
| select tinyint_col, count(*) from alltypesagg where case tinyint_col when NULL then true else false end group by 1 |
| ---- RESULTS |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # CASE expr - test return else works with NULL |
| select tinyint_col, count(*) from alltypesagg where case tinyint_col when 1 then true else NULL end |
| is null group by 1 |
| ---- RESULTS |
| 3,1000 |
| NULL,2000 |
| 2,1000 |
| 4,1000 |
| 8,1000 |
| 6,1000 |
| 5,1000 |
| 7,1000 |
| 9,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # CASE expr - test that case statement without else expr returns NULL on no match |
| select tinyint_col, count(*) from alltypesagg where case tinyint_col when 1 then true end |
| is null group by 1 |
| ---- RESULTS |
| 3,1000 |
| NULL,2000 |
| 2,1000 |
| 4,1000 |
| 8,1000 |
| 6,1000 |
| 5,1000 |
| 7,1000 |
| 9,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| select tinyint_col, count(*) from alltypesagg where case when (tinyint_col = 1) then true when |
| (tinyint_col = 2) then true else false end group by 1 |
| ---- RESULTS |
| 2,1000 |
| 1,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # CASE expr - Test basic functionality with strings |
| select date_string_col, count(*) from alltypesagg where case date_string_col when "01/06/10" then true |
| when "01/02/10" then false when "01/08/10" then true end group by 1 |
| ---- RESULTS |
| '01/08/10',1100 |
| '01/06/10',1100 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col, sum(case date_string_col when "01/06/10" then 2 else 0 end) from alltypesagg group by 1 |
| ---- RESULTS |
| '01/08/10',0 |
| '01/09/10',0 |
| '01/02/10',0 |
| '01/06/10',2200 |
| '01/01/10',0 |
| '01/03/10',0 |
| '01/04/10',0 |
| '01/10/10',0 |
| '01/07/10',0 |
| '01/05/10',0 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col, sum(case date_string_col when "01/06/10" then 2 when "01/02/10" then 1 else 0 end) |
| from alltypesagg group by 1 |
| ---- RESULTS |
| '01/08/10',0 |
| '01/09/10',0 |
| '01/02/10',1100 |
| '01/06/10',2200 |
| '01/01/10',0 |
| '01/03/10',0 |
| '01/04/10',0 |
| '01/10/10',0 |
| '01/07/10',0 |
| '01/05/10',0 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select bool_col, count(*) from alltypesagg where(case bool_col when true then "true" when false |
| then "false" end = "true") group by 1 |
| ---- RESULTS |
| true,6000 |
| ---- TYPES |
| BOOLEAN, BIGINT |
| ==== |
| ---- QUERY |
| # CASE expr - test when other expr types |
| select id, count(*) from alltypesagg where case id when 8999 then cast(1 as int) else |
| cast(0 as int) end = 1 group by 1 |
| ---- RESULTS |
| 8999,1 |
| ---- TYPES |
| INT, BIGINT |
| ==== |
| ---- QUERY |
| select bool_col, count(*) from alltypesagg where case bool_col when false then true else false end group by 1 |
| ---- RESULTS |
| false,5000 |
| ---- TYPES |
| BOOLEAN, BIGINT |
| ==== |
| ---- QUERY |
| select smallint_col, count(*) from alltypesagg where case smallint_col when 90 then cast(1 as smallint) |
| else cast(0 as smallint) end = 1 group by 1 |
| ---- RESULTS |
| 90,200 |
| ---- TYPES |
| SMALLINT, BIGINT |
| ==== |
| ---- QUERY |
| select int_col, count(*) from alltypesagg where case int_col when 90 then cast(1 as int) |
| else cast(0 as int) end = 1 group by 1 |
| ---- RESULTS |
| 90,20 |
| ---- TYPES |
| INT, BIGINT |
| ==== |
| ---- QUERY |
| select bigint_col, count(*) from alltypesagg where case bigint_col when 90 then cast(1 as bigint) |
| else cast(0 as bigint) end = 1 group by 1 |
| ---- RESULTS |
| 90,10 |
| ---- TYPES |
| BIGINT, BIGINT |
| ==== |
| ---- QUERY |
| select float_col, count(*) from alltypesagg where case float_col when 1089 then cast(1.0 as float) |
| else cast(0.0 as float) end = 1.0 group by 1 |
| ---- RESULTS |
| 1089,20 |
| ---- TYPES |
| FLOAT, BIGINT |
| ==== |
| ---- QUERY |
| select double_col, count(*) from alltypesagg where case double_col when 9999 then cast(1.0 as double) |
| else cast(0.0 as double) end = 1.0 group by 1 |
| ---- RESULTS |
| 9999,20 |
| ---- TYPES |
| DOUBLE, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col, count(*) from alltypesagg where case date_string_col when "01/08/10" then "true" |
| else "false" end = "true" group by 1 |
| ---- RESULTS |
| '01/08/10',1100 |
| ---- TYPES |
| STRING, BIGINT |
| ==== |
| ---- QUERY |
| select timestamp_col, count(*) from alltypesagg where case timestamp_col when |
| "2010-01-08 18:02:05.100000000" then true else false end group by 1 |
| ---- RESULTS |
| 2010-01-08 18:02:05.100000000,1 |
| ---- TYPES |
| TIMESTAMP, BIGINT |
| ==== |
| ---- QUERY |
| select decode(id, 1, 100) from alltypesagg where day = 1 and id < 3 |
| ---- RESULTS |
| 100 |
| NULL |
| NULL |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| select id |
| from alltypesagg |
| where day = 1 and id < 3 and decode(tinyint_col, null, 4) is not null |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| select distinct decode(id, tinyint_col, round(float_col)) from alltypestiny |
| ---- RESULTS |
| NULL |
| 0 |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select decode(tinyint_col, null, -2), count(*) |
| from alltypesagg |
| where day = 1 and decode(smallint_col, 11, -1, id) < 10 |
| group by 1 |
| ---- RESULTS |
| -2,1 |
| NULL,19 |
| ---- TYPES |
| TINYINT,BIGINT |
| ==== |
| ---- QUERY |
| # BETWEEN predicate |
| select count(*) from alltypesagg where tinyint_col between 1 and 2 |
| ---- RESULTS |
| 2000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where tinyint_col not between 1 and 2 |
| ---- RESULTS |
| 7000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col between 1 and 20 |
| ---- RESULTS |
| 2200 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where smallint_col not between 1 and 20 |
| ---- RESULTS |
| 8600 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col between 1 and 200 |
| ---- RESULTS |
| 2200 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where int_col not between 1 and 200 |
| ---- RESULTS |
| 8780 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col between 1 and 2000 |
| ---- RESULTS |
| 2200 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where bigint_col not between 1 and 2000 |
| ---- RESULTS |
| 8780 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where float_col between cast(1.0 as float) and cast(110.0 as float) |
| ---- RESULTS |
| 1100 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg |
| where float_col not between cast(1.0 as float) and cast(110.0 as float) |
| ---- RESULTS |
| 9880 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col between 1.0 and 110.0 |
| ---- RESULTS |
| 110 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where double_col not between 1.0 and 110.0 |
| ---- RESULTS |
| 10870 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where date_string_col |
| between '01/01/10' and '01/05/10' |
| ---- RESULTS |
| 5500 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where date_string_col |
| not between '01/01/10' and '01/05/10' |
| ---- RESULTS |
| 5500 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where timestamp_col |
| between cast('2010-01-01 00:00:00' as timestamp) |
| and cast('2010-01-01 01:40:00' as timestamp) |
| ---- RESULTS |
| 110 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from alltypesagg where timestamp_col |
| not between cast('2010-01-01 00:00:00' as timestamp) |
| and cast('2010-01-01 01:40:00' as timestamp) |
| ---- RESULTS |
| 10890 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Test pid() function, this should only return one pid. If pid() were not implemented |
| # correctly via the global state variable, this could return multiple pids. |
| select pid() p from functional.alltypes |
| union distinct select pid() p from functional.alltypes |
| group by p |
| ---- RESULTS |
| # Matches a single pid |
| row_regex: \d+ |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| # Test NOW() function. |
| select now() |
| ---- RESULTS |
| # Matches a single date of the form 'yyyy-MM-dd HH:mm:ss' |
| # or 'yyyy-MM-dd HH:mm:ss.SSSSSS' |
| row_regex: \d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})? |
| ---- TYPES |
| timestamp |
| ==== |
| ---- QUERY |
| select now(), now(), now(), now() |
| ---- RESULTS |
| # Matches four identical timestamps separated by commas. |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| ---- TYPES |
| timestamp, timestamp, timestamp, timestamp |
| ==== |
| ---- QUERY |
| select now(), now(), now(), now() from alltypestiny |
| ---- RESULTS |
| # Matches four identical timestamps separated by commas. |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1,\1,\1 |
| ---- TYPES |
| timestamp, timestamp, timestamp, timestamp |
| ==== |
| ---- QUERY |
| # Check that now() returns the same value when evaluated on different nodes |
| # (alltypessmall is partitioned) |
| select min(now()), max(now()) from alltypessmall |
| ---- RESULTS |
| # Matches two identical timestamps separated by commas. |
| row_regex: (\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}(\.\d{9})?),\1 |
| ---- TYPES |
| timestamp, timestamp |
| ==== |
| ---- QUERY |
| # Timestamp arithmetic tests (lowest timestamp_col has value 2009-01-01 00:00:00). |
| # Add/sub years, non-function-call like version. |
| select timestamp_col + interval 10 years, |
| interval 10 years + timestamp_col, |
| timestamp_col - interval 10 years |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2019-01-01 00:00:00,2019-01-01 00:00:00,1999-01-01 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub years, function-call like version. |
| select date_add(timestamp_col, interval 10 years), |
| date_sub(timestamp_col, interval 10 years) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2019-01-01 00:00:00,1999-01-01 00:00:00 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub months, non-function-call like version. |
| select timestamp_col + interval 13 months, |
| timestamp_col + interval 1 month, |
| interval 13 months + timestamp_col, |
| interval 1 month + timestamp_col, |
| timestamp_col - interval 13 months, |
| timestamp_col - interval 1 month |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2010-02-01 00:00:00,2009-02-01 00:00:00,2010-02-01 00:00:00,2009-02-01 00:00:00,2007-12-01 00:00:00,2008-12-01 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub months, function-call like version. |
| select date_add(timestamp_col, interval 13 months), |
| date_add(timestamp_col, interval 1 month), |
| date_sub(timestamp_col, interval 13 months), |
| date_sub(timestamp_col, interval 1 month) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2010-02-01 00:00:00,2009-02-01 00:00:00,2007-12-01 00:00:00,2008-12-01 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub weeks, non-function-call like version. |
| select timestamp_col + interval 2 weeks, |
| timestamp_col + interval 53 weeks, |
| interval 2 weeks + timestamp_col, |
| interval 53 weeks + timestamp_col, |
| timestamp_col - interval 2 weeks, |
| timestamp_col - interval 53 weeks |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-15 00:00:00,2010-01-07 00:00:00,2009-01-15 00:00:00,2010-01-07 00:00:00,2008-12-18 00:00:00,2007-12-27 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub weeks, function-call like version. |
| select date_add(timestamp_col, interval 2 weeks), |
| date_add(timestamp_col, interval 53 weeks), |
| date_sub(timestamp_col, interval 2 weeks), |
| date_sub(timestamp_col, interval 53 weeks) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-15 00:00:00,2010-01-07 00:00:00,2008-12-18 00:00:00,2007-12-27 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub days, non-function-call like version. |
| select timestamp_col + interval 10 days, |
| timestamp_col + interval 10 days, |
| interval 10 days + timestamp_col, |
| interval 10 days + timestamp_col, |
| timestamp_col - interval 10 days, |
| timestamp_col - interval 365 days |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-11 00:00:00,2009-01-11 00:00:00,2009-01-11 00:00:00,2009-01-11 00:00:00,2008-12-22 00:00:00,2008-01-02 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub days, function-call like version. |
| select date_add(timestamp_col, interval 10 days), |
| date_add(timestamp_col, interval 10 days), |
| date_sub(timestamp_col, interval 10 days), |
| date_sub(timestamp_col, interval 365 days) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-11 00:00:00,2009-01-11 00:00:00,2008-12-22 00:00:00,2008-01-02 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub hours, non-function-call like version. |
| select timestamp_col + interval 25 hours, |
| interval 25 hours + timestamp_col, |
| timestamp_col - interval 25 hours |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:00:00,2009-01-02 01:00:00,2008-12-30 23:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub hours, function-call like version. |
| select date_add(timestamp_col, interval 25 hours), |
| date_sub(timestamp_col, interval 25 hours) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:00:00,2008-12-30 23:00:00 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub minutes (1533 minutes are 25h33m), non-function-call like version. |
| select timestamp_col + interval 1533 minutes, |
| interval 1533 minutes + timestamp_col, |
| timestamp_col - interval 1533 minutes |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:33:00,2009-01-02 01:33:00,2008-12-30 22:27:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub minutes (1533 minutes are 25h33m), function-call like version. |
| select date_add(timestamp_col, interval 1533 minutes), |
| date_sub(timestamp_col, interval 1533 minutes) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:33:00,2008-12-30 22:27:00 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub seconds (90033 seconds are 25h33s), non-function-call like version. |
| select timestamp_col + interval 90033 seconds, |
| interval 90033 seconds + timestamp_col, |
| timestamp_col - interval 90033 seconds |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:00:33,2009-01-02 01:00:33,2008-12-30 22:59:27 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub seconds (90033 seconds are 25h33s), function-call like version. |
| select date_add(timestamp_col, interval 90033 seconds), |
| date_sub(timestamp_col, interval 90033 seconds) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:00:33,2008-12-30 22:59:27 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub milliseconds (90000033 seconds are 25h33ms), non-function-call like version. |
| select timestamp_col + interval 90000033 milliseconds, |
| interval 90000033 milliseconds + timestamp_col, |
| timestamp_col - interval 90000033 milliseconds |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:00:00.033000000,2009-01-02 01:00:00.033000000,2008-12-30 22:59:59.967000000 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub milliseconds (90000033 seconds are 25h33ms), function-call like version. |
| select date_add(timestamp_col, interval 90000033 milliseconds), |
| date_sub(timestamp_col, interval 90000033 milliseconds) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-02 01:00:00.033000000,2008-12-30 22:59:59.967000000 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub microseconds, non-function-call like version. |
| select timestamp_col + interval 1033 microseconds, |
| interval 1033 microseconds + timestamp_col, |
| timestamp_col - interval 1033 microseconds |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-01 00:00:00.001033000,2009-01-01 00:00:00.001033000,2008-12-31 23:59:59.998967000 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub microseconds, function-call like version. |
| select date_add(timestamp_col, interval 1033 microseconds), |
| date_sub(timestamp_col, interval 1033 microseconds) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-01 00:00:00.001033000,2008-12-31 23:59:59.998967000 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub nanoseconds, non-function-call like version. |
| select timestamp_col + interval 1033 nanoseconds, |
| interval 1033 nanoseconds + timestamp_col, |
| timestamp_col - interval 1033 nanoseconds |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-01 00:00:00.000001033,2009-01-01 00:00:00.000001033,2008-12-31 23:59:59.999998967 |
| ---- TYPES |
| timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Add/sub nanoseconds, function-call like version. |
| select date_add(timestamp_col, interval 1033 nanoseconds), |
| date_sub(timestamp_col, interval 1033 nanoseconds) |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-01 00:00:00.000001033,2008-12-31 23:59:59.999998967 |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Chaining of arithmetic operations (only non-function-call like version). |
| select timestamp_col + interval 10 years + interval 2 months + interval 5 days, |
| interval 10 years + timestamp_col + interval 2 months + interval 5 days, |
| timestamp_col + interval 10 years - interval 2 months + interval 5 days, |
| interval 10 years + timestamp_col - interval 2 months + interval 5 days, |
| timestamp_col - interval 10 years - interval 2 months - interval 5 days |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2019-03-06 00:00:00,2019-03-06 00:00:00,2018-11-06 00:00:00,2018-11-06 00:00:00,1998-10-27 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| select timestamp_col + interval 10 hours + interval 2 minutes + interval 5 seconds |
| + interval 3 milliseconds + interval 3 microseconds + interval 3 nanoseconds, |
| interval 10 hours + timestamp_col + interval 2 minutes + interval 5 seconds |
| + interval 3 milliseconds + interval 3 microseconds + interval 3 nanoseconds, |
| timestamp_col + interval 10 hours - interval 2 minutes + interval 5 seconds |
| - interval 3 milliseconds + interval 3 microseconds - interval 3 nanoseconds, |
| interval 10 hours + timestamp_col + interval 2 minutes + interval 5 seconds |
| - interval 3 milliseconds + interval 3 microseconds - interval 3 nanoseconds, |
| timestamp_col - interval 10 hours - interval 2 minutes - interval 5 seconds |
| - interval 3 milliseconds - interval 3 microseconds - interval 3 nanoseconds |
| from alltypes order by id limit 1 |
| ---- RESULTS |
| 2009-01-01 10:02:05.003003003,2009-01-01 10:02:05.003003003,2009-01-01 09:58:04.997002997,2009-01-01 10:02:04.997002997,2008-12-31 13:57:54.996996997 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Test corner cases and also timestamp arithmetic without from clause. |
| select cast("2012-02-29 00:00:00" as timestamp) + interval 1 year, |
| cast("2013-02-28 00:00:00" as timestamp) - interval 1 year, |
| cast("2012-01-01 00:00:00" as timestamp) + interval 365 days, |
| cast("2013-01-01 00:00:00" as timestamp) - interval 366 days |
| ---- RESULTS |
| 2013-02-28 00:00:00,2012-02-28 00:00:00,2012-12-31 00:00:00,2012-01-01 00:00:00 |
| ---- TYPES |
| timestamp,timestamp,timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Test overflow (2147483647 is MAX_INT). |
| select cast("2012-01-01 00:00:00" as timestamp) + interval 2147483647 years, |
| cast("2012-01-01 00:00:00" as timestamp) - interval 2147483647 years |
| ---- RESULTS |
| NULL,NULL |
| ---- TYPES |
| timestamp,timestamp |
| ==== |
| ---- QUERY |
| # Timestamp arithmetic inside a predicate. |
| select year, month from alltypes |
| where year = year((cast('2012-01-01 09:10:11' as timestamp) - interval 3 years)) |
| and month = month((cast('2012-01-01 09:10:11' as timestamp) + interval 3 months)) |
| limit 3 |
| ---- RESULTS |
| 2009,4 |
| 2009,4 |
| 2009,4 |
| ---- TYPES |
| int,int |
| ==== |
| ---- QUERY |
| # Test NULLs in second operand |
| select count(*) from alltypesagg |
| where (cast('2012-01-01 09:10:11' as timestamp) + interval tinyint_col seconds) IS NULL |
| ---- RESULTS |
| 2000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Test NULLs in first operand |
| select count(*) from alltypesagg |
| where (cast('2012-01-01 09:10:11' as timestamp) + interval tinyint_col seconds) |
| + interval 10 years IS NULL |
| ---- RESULTS |
| 2000 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select round(float_col, 3), round(float_col, 4) from functional.alltypestiny limit 2; |
| ---- RESULTS |
| 0.000,0.0000 |
| 1.100,1.1000 |
| ---- TYPES |
| double,double |
| ==== |
| ---- QUERY |
| # Test that round outputs correct number of decimal places despite floating-point |
| # imprecisions |
| select round(cast(1.1 as float), 2), round(cast(1.2 as float), 4), |
| round(cast(1.111 as double), 2); |
| ---- RESULTS |
| 1.1,1.2,1.11 |
| ---- TYPES |
| double,double,double |
| ==== |
| ---- QUERY |
| # Test a fix of codegen/non-codegen exprs(IMPALA-350) |
| # Test having a thread-safe expr followed by a non thread-safe one and |
| # vice versa. |
| select count(*) from alltypes where string_col REGEXP '^1' and id < 10 |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test a fix of codegen/non-codegen exprs(IMPALA-350) |
| select count(*) from alltypes where id < 10 and string_col REGEXP '^1' |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test a fix of codegen/non-codegen for floating-point modulo(IMPALA-391) |
| select float_col, double_col, float_col % double_col, |
| double_col % NULL, NULL % double_col from alltypessmall limit 10 |
| ---- RESULTS |
| 0,0,NULL,NULL,NULL |
| 1.100000023841858,10.1,1.100000023841858,NULL,NULL |
| 2.200000047683716,20.2,2.200000047683716,NULL,NULL |
| 3.299999952316284,30.3,3.299999952316284,NULL,NULL |
| 4.400000095367432,40.4,4.400000095367432,NULL,NULL |
| 5.5,50.5,5.5,NULL,NULL |
| 6.599999904632568,60.6,6.599999904632568,NULL,NULL |
| 7.699999809265137,70.7,7.699999809265137,NULL,NULL |
| 8.800000190734863,80.8,8.800000190734863,NULL,NULL |
| 9.899999618530273,90.90000000000001,9.899999618530273,NULL,NULL |
| ---- TYPES |
| FLOAT,DOUBLE,DOUBLE,DOUBLE,DOUBLE |
| ==== |
| ---- QUERY |
| select unix_timestamp(tmp.val, tmp.fmt) from (values |
| ('1970-01-01 00:00:00' as val, 'yyyy-MM-dd HH:mm:ss' as fmt), |
| ('01:01:01 02/1971/02', 'HH:mm:ss dd/yyyy/MM'), |
| ('1972||mar||03||030303', 'yyyy||MMM||dd||HHmmss')) as tmp |
| ---- RESULTS |
| 0 |
| 34304461 |
| 68439783 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select from_unixtime(tmp.val, tmp.fmt) from (values |
| (0 as val, 'yyyy-MM-dd HH:mm:ss' as fmt), |
| (34304461, 'HH:mm:ss dd/yyyy/MM'), |
| (68439783, 'yyyy||MMM||dd||HHmmss')) as tmp |
| ---- RESULTS |
| '1970-01-01 00:00:00' |
| '01:01:01 02/1971/02' |
| '1972||Mar||03||030303' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select to_timestamp(tmp.val) from (values |
| (0 as val), |
| (34304461), |
| (68439783)) as tmp |
| ---- RESULTS |
| 1970-01-01 00:00:00 |
| 1971-02-02 01:01:01 |
| 1972-03-03 03:03:03 |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| select to_timestamp(tmp.val, tmp.fmt) from (values |
| ('1970-01-01 00:00:00' as val, 'yyyy-MM-dd HH:mm:ss' as fmt), |
| ('01:01:01 02/1971/02', 'HH:mm:ss dd/yyyy/MM'), |
| ('1972||Mar||03||030303', 'yyyy||MMM||dd||HHmmss')) as tmp |
| ---- RESULTS |
| 1970-01-01 00:00:00 |
| 1971-02-02 01:01:01 |
| 1972-03-03 03:03:03 |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| select from_timestamp(tmp.val, tmp.fmt) from (values |
| ('1970-01-01 00:00:00' as val, 'yyyy-MM-dd HH:mm:ss' as fmt), |
| ('1971-02-02 01:01:01', 'yyyy-MM-dd HH:mm:ss'), |
| ('1972-03-03 03:03:03', 'yyyy-MM-dd HH:mm:ss')) as tmp |
| ---- RESULTS |
| '1970-01-01 00:00:00' |
| '1971-02-02 01:01:01' |
| '1972-03-03 03:03:03' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select 1.1 * 1.1 + cast(1.1 as float) |
| ---- RESULTS |
| 2.31000002 |
| ---- TYPES |
| DECIMAL |
| ==== |
| ---- QUERY |
| select 1.1 * 1.1 + cast(1.1 as decimal(2,1)) |
| ---- RESULTS |
| 2.31 |
| ---- TYPES |
| decimal |
| ==== |
| ---- QUERY |
| select 1.1 * 1.1 + 1.1 |
| ---- RESULTS |
| 2.31 |
| ---- TYPES |
| decimal |
| ==== |
| ---- QUERY |
| select 1.1 * 1.1 + float_col from functional.alltypestiny limit 2; |
| ---- RESULTS |
| 1.21000000 |
| 2.31000002 |
| ---- TYPES |
| DECIMAL |
| ==== |
| ---- QUERY |
| select 1.1 * 1.1 + c3 from functional.decimal_tiny limit 2; |
| ---- RESULTS |
| 1.21 |
| 1.31 |
| ---- TYPES |
| decimal |
| ==== |
| ---- QUERY |
| # Test weird log values (these are annoying to check in expr-test) |
| select log(1,2), log(1,1), log(0,2), log(2,0), log(0,0); |
| ---- RESULTS |
| Infinity,NaN,-0,-Infinity,Nan |
| ---- TYPES |
| double,double,double,double,double |
| ==== |
| ---- QUERY |
| # Test that abs() promotes the type of the paramter IMPALA-4513 |
| select abs(cast(1 as int)), abs(cast(1 as smallint)), |
| abs(cast(1 as tinyint)), abs(cast(8589934592 as bigint)), |
| abs(cast(-1.3 as double)), abs(cast(-1.3 as float)), |
| abs(cast(-1.32223 as decimal(8,3))) |
| ---- RESULTS |
| 1,1,1,8589934592,1.3,1.299999952316284,1.322 |
| ---- TYPES |
| bigint, int, smallint, bigint, double, float, decimal |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-1508 |
| select timestamp_col FROM alltypesagg |
| ORDER BY timestamp_col + INTERVAL bigint_col YEAR ASC limit 1 |
| ---- RESULTS |
| 2010-01-01 00:01:00 |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test Expr::GetConstant() |
| select int_col, bigint_col from functional_avro_snap.alltypestiny |
| where abs(cast(int_col as decimal(8,0))) > |
| negative(abs(cast(bigint_col as decimal(20, 0)))) |
| ---- RESULTS |
| 1,10 |
| 1,10 |
| 1,10 |
| 1,10 |
| ---- TYPES |
| INT,BIGINT |
| ==== |
| ---- QUERY |
| # Test for IMPALA-1756 (Builtin) |
| select int_col from functional.alltypes where regexp_replace('abc', '*', 'cde') = 'cde' |
| limit 10 |
| ---- TYPES |
| INT |
| ---- RESULTS |
| ---- CATCH |
| AnalysisException: Error evaluating "regexp_replace('abc', '*', 'cde') = 'cde'" |
| CAUSED BY: InternalException: Could not compile regexp pattern: * |
| Error: no argument for repetition operator: * |
| ==== |
| ---- QUERY |
| # Test for IMPALA-1756 (UDF) |
| select int_col from functional.alltypes where |
| madlib_vector_get(0, madlib_vector(1.0, 2.0, NULL)) = 1.0 limit 10 |
| ---- TYPES |
| INT |
| ---- RESULTS |
| ---- CATCH |
| AnalysisException: Error evaluating "madlib_vector_get(0, madlib_vector(1.0, 2.0, NULL)) = 1.0" |
| CAUSED BY: InternalException: madlib vector entry 2 is NULL |
| ==== |
| ---- QUERY |
| select * from (select 'abc' as x, '*' as y, 'def' as z from functional.alltypestiny) v |
| where regexp_replace(x, y, z) = 'cde' |
| ---- RESULTS |
| ---- CATCH |
| AnalysisException: Error evaluating "regexp_replace('abc', '*', 'def') = 'cde'" |
| CAUSED BY: InternalException: Could not compile regexp pattern: * |
| Error: no argument for repetition operator: * |
| ==== |
| ---- QUERY |
| select sum(length(replace(y, x, 'bbbbbbbbbbb'))) |
| from (select cast(round(float_col) AS STRING) as x, string_col as y |
| from functional.alltypes) v; |
| ---- TYPES |
| BIGINT |
| ---- RESULTS |
| 43800 |
| ==== |
| ---- QUERY |
| select sum(length(replace(y, '0', x))) |
| from (select cast(round(float_col) AS STRING) as x, string_col as y |
| from functional.alltypes) v; |
| ---- TYPES |
| BIGINT |
| ---- RESULTS |
| 7300 |
| ==== |
| ---- QUERY |
| select sum(length(concat(replace(y, '0', x), replace(y, '0', x)))) |
| from (select cast(round(float_col) AS STRING) as x, string_col as y |
| from functional.alltypes) v; |
| ---- TYPES |
| BIGINT |
| ---- RESULTS |
| 14600 |
| ==== |
| ---- QUERY |
| # Test for factorial operator |
| select distinct int_col, int_col! from functional.alltypes order by 1 |
| ---- RESULTS |
| 0,1 |
| 1,1 |
| 2,2 |
| 3,6 |
| 4,24 |
| 5,120 |
| 6,720 |
| 7,5040 |
| 8,40320 |
| 9,362880 |
| ---- TYPES |
| INT,BIGINT |
| ==== |
| ---- QUERY |
| # Test for IMPALA-2141: Tests for propogation of error for a UDF with constant parameters |
| # in the select list. |
| select madlib_vector(1.0, 2.0, NULL); |
| ---- RESULTS |
| ---- CATCH |
| madlib vector entry 2 is NULL |
| ==== |
| ---- QUERY |
| # Test countset |
| select tinyint_col, countset(tinyint_col), countset(tinyint_col, 0), |
| smallint_col, countset(smallint_col), countset(smallint_col, 0), |
| int_col, countset(int_col), countset(int_col, 0), |
| bigint_col, countset(bigint_col), countset(bigint_col, 0) |
| from alltypestiny |
| where id <= 1 |
| order by id |
| ---- RESULTS |
| 0,0,8,0,0,16,0,0,32,0,0,64 |
| 1,1,7,1,1,15,1,1,31,10,2,62 |
| ---- TYPES |
| TINYINT,INT,INT,SMALLINT,INT,INT,INT,INT,INT,BIGINT,INT,INT |
| ==== |
| ---- QUERY |
| # Test basic bitwise ops |
| select bitand(tinyint_col, int_col), |
| bitor(smallint_col, bigint_col), |
| bitxor(tinyint_col, smallint_col), |
| bitxor(int_col, bigint_col), |
| bitxor(int_col, bitnot(int_col)), |
| bitnot(tinyint_col) |
| from alltypes |
| where id <= 3 |
| order by id |
| ---- RESULTS |
| 0,0,0,0,-1,-1 |
| 1,11,0,11,-1,-2 |
| 2,22,0,22,-1,-3 |
| 3,31,0,29,-1,-4 |
| ---- TYPES |
| INT,BIGINT,SMALLINT,BIGINT,INT,TINYINT |
| ==== |
| ---- QUERY |
| # Test getbit and setbit |
| select bigint_col, |
| getbit(bigint_col,0), |
| getbit(bigint_col,1), |
| getbit(bigint_col,int_col), |
| setbit(bigint_col,0), |
| setbit(bigint_col,1,0) |
| from alltypes |
| where id <= 3 |
| order by id |
| ---- RESULTS |
| 0,0,0,0,1,0 |
| 10,0,1,1,11,8 |
| 20,0,0,1,21,20 |
| 30,0,1,1,31,28 |
| ---- TYPES |
| BIGINT,TINYINT,TINYINT,TINYINT,BIGINT,BIGINT |
| ==== |
| ---- QUERY |
| # Test shifts and rotates |
| select int_col, |
| shiftright(int_col,1), |
| shiftleft(int_col,2), |
| rotateleft(int_col,30), |
| rotateright(int_col,2) |
| from alltypes |
| where id <= 3 |
| order by id |
| ---- RESULTS |
| 0,0,0,0,0 |
| 1,0,4,1073741824,1073741824 |
| 2,1,8,-2147483648,-2147483648 |
| 3,1,12,-1073741824,-1073741824 |
| ---- TYPES |
| INT,INT,INT,INT,INT |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where REGEXP_LIKE(str_col, match_regex_col) |
| ---- RESULTS |
| 'beginning of line','^begin.*' |
| 'eight','.*eight.*' |
| 'end of line','.*line$' |
| 'five','.*five.*' |
| 'four','.*four.*' |
| 'middle of line','^.*of.*$' |
| 'nine','.*nine.*' |
| 'one','.*one.*' |
| 'seven','.*seven.*' |
| 'six','.*six.*' |
| 'ten','.*ten.*' |
| 'three','.*three.*' |
| 'two','.*two.*' |
| '','.*' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, no_match_regex_col from LikeTbl |
| where REGEXP_LIKE(str_col, no_match_regex_col) |
| ---- RESULTS |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where NOT REGEXP_LIKE(str_col, match_regex_col) |
| ---- RESULTS |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@'), match_like_col |
| from (values('one\ntwo' as str), ('\n')) as tmp, LikeTbl |
| where tmp.str LIKE match_like_col |
| ---- RESULTS |
| 'one@two','%one%' |
| 'one@two','%two%' |
| 'one@two','%' |
| 'one@two','%' |
| '@','%' |
| '@','%' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, no_match_regex_col from LikeTbl |
| where NOT REGEXP_LIKE(str_col, no_match_regex_col) |
| ---- RESULTS |
| 'beginning of line','^not begin.*' |
| 'eight','n.*eight.*' |
| 'end of line','.*line end$' |
| 'five','n.*five.*' |
| 'four','n.*four.*' |
| 'middle of line','.*of$' |
| 'nine','n.*nine.*' |
| 'one','n.*one.*' |
| 'seven','n.*seven.*' |
| 'six','n.*six.*' |
| 'ten','n.*ten.*' |
| 'three','n.*three.*' |
| 'two','n.*two.*' |
| '','X' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where REGEXP_LIKE(upper(str_col), lower(match_regex_col), 'i') |
| ---- RESULTS |
| 'beginning of line','^begin.*' |
| 'eight','.*eight.*' |
| 'end of line','.*line$' |
| 'five','.*five.*' |
| 'four','.*four.*' |
| 'middle of line','^.*of.*$' |
| 'nine','.*nine.*' |
| 'one','.*one.*' |
| 'seven','.*seven.*' |
| 'six','.*six.*' |
| 'ten','.*ten.*' |
| 'three','.*three.*' |
| 'two','.*two.*' |
| '','.*' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where REGEXP_LIKE(str_col, match_regex_col, 'c') |
| ---- RESULTS |
| 'beginning of line','^begin.*' |
| 'eight','.*eight.*' |
| 'end of line','.*line$' |
| 'five','.*five.*' |
| 'four','.*four.*' |
| 'middle of line','^.*of.*$' |
| 'nine','.*nine.*' |
| 'one','.*one.*' |
| 'seven','.*seven.*' |
| 'six','.*six.*' |
| 'ten','.*ten.*' |
| 'three','.*three.*' |
| 'two','.*two.*' |
| '','.*' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select str_col, match_regex_col from LikeTbl |
| where REGEXP_LIKE(upper(str_col), lower(match_regex_col), 'c') |
| ---- RESULTS |
| '','.*' |
| ---- TYPES |
| string, string |
| ==== |
| ---- QUERY |
| select tmp.str from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like(tmp.str, '^multi.*$') |
| ---- RESULTS |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@') from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like(tmp.str, '^multi.*$', 'n') |
| ---- RESULTS |
| 'multi@field' |
| 'multi@field@two' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@') from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like(tmp.str, '^fiel.*$', 'n') |
| ---- RESULTS |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@') from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like(tmp.str, '^fiel.*$', 'm') |
| ---- RESULTS |
| 'multi@field' |
| 'multi@field@two' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@') from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like(tmp.str, '^fiel.*$', 'mn') |
| ---- RESULTS |
| 'multi@field' |
| 'multi@field@two' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@') from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like('', '', '') |
| ---- RESULTS |
| 'testfield' |
| 'multi@field' |
| 'multi@field@two' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select translate(tmp.str, '\n', '@') from (values |
| ('testfield' as str), |
| ('multi\nfield'), |
| ('multi\nfield\ntwo')) as tmp |
| where regexp_like('', '') |
| ---- RESULTS |
| 'testfield' |
| 'multi@field' |
| 'multi@field@two' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select split_part(tmp.str, tmp.delim, pos) from (values |
| ('abc@@def@@ghi' as str, '@@' as delim, 1 as pos), |
| ('@@123@@456', '@@', 1), |
| ('@@123@@456', '@@', 2), |
| ('@@', '@@', 1), |
| ('abc~!~def~!~!~ghi', '~!~', 3), |
| ('abc', '@@', 1), |
| ('xyz!!qwe', '!!', 500), |
| (NULL, NULL, 1), |
| ('abc', NULL, 2), |
| (NULL, '@@', 1), |
| (NULL, '!!', 1) |
| ) as tmp |
| ---- RESULTS |
| 'abc' |
| '' |
| '123' |
| '' |
| '!~ghi' |
| 'abc' |
| '' |
| 'NULL' |
| 'NULL' |
| 'NULL' |
| 'NULL' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # Regression test for IMPALA-2290: non-constant args to btrim in parallel context |
| # (i.e. predicates evaluated by scans) |
| select count(*) from tpch_parquet.lineitem where btrim(l_comment, l_comment) != "" |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test for IMPALA-1746: QueryExecState doesn't check for query cancellation or errors |
| select dayofyear(trunc('2014-11-11', string_col)) from functional.alltypestiny limit 1; |
| ---- CATCH |
| Invalid Truncate Unit: 0 |
| ==== |
| ---- QUERY |
| select regexp_match_count(tmp.str, tmp.`pattern`) from (values |
| ('aaa' as str, 'a' as `pattern`), |
| ('aaa', 'aa'), |
| ('aaaa', 'aa'), |
| ('', ''), |
| ('', '.*'), |
| ('abxcy1234a', 'a.x'), |
| ('abxcy1234a', 'a.x.*a'), |
| ('abxcy1234a', 'a.x.*k'), |
| ('aaa123a', 'a*'), |
| ('aaa123a', 'a?'), |
| ('a.x.y.*a', 'a\.x\.y\.\*a'), |
| ('0123456789', '.*'), |
| ('0123456789', '.+'), |
| ('0123456789', '.?'), |
| ('abcab', '(a|ab|abc)'), |
| ('abc123efg', '[\\d]'), |
| ('abc123efg', '[\\d]+'), |
| ('abc123efg', '[\^\\d]'), |
| ('a1b2c3d4e5!!!', '[\\w\\d]'), |
| ('a1b2c3d4e5!!!', '\\w\\d')) as tmp |
| ---- RESULTS |
| 3 |
| 1 |
| 2 |
| 1 |
| 1 |
| 1 |
| 1 |
| 0 |
| 6 |
| 8 |
| 1 |
| 2 |
| 1 |
| 11 |
| 2 |
| 3 |
| 1 |
| 6 |
| 10 |
| 5 |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| select regexp_match_count(tmp.str, tmp.`pattern`, tmp.start_pos, tmp.params) from (values |
| ('aaa' as str, 'A' as `pattern`, 1 as start_pos, 'i' as params), |
| ('aaa', 'A', 1, 'c'), |
| ('this\nis\nnewline', '.*', 1, ''), |
| ('this\nis\nnewline', '.*', 1, 'n'), |
| ('IPhone\nIPad\nIPod', '^I.*$', 1, ''), |
| ('IPhone\nIPad\nIPod', '^I.*$', 1, 'n'), |
| ('IPhone\nIPad\nIPod', '^I.*$', 1, 'm'), |
| ('iPhone\niPad\niPod', '^I.*$', 1, 'in'), |
| ('iPhone\niPad\niPod', '^I.*$', 1, 'cin'), |
| ('iPhone\niPad\niPod', '^I.*$', 1, 'im'), |
| ('iPhone\niPad\niPod', '^I.*$', 1, 'imn'), |
| ('aaa', 'a', 3, ''), |
| ('aaa', 'a', 4, ''), |
| ('aaa', 'a*', 4, '')) as tmp |
| ---- RESULTS |
| 3 |
| 0 |
| 6 |
| 2 |
| 0 |
| 1 |
| 3 |
| 1 |
| 1 |
| 3 |
| 1 |
| 1 |
| 0 |
| 1 |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| select regexp_match_count(tmp.str, tmp.`pattern`, tmp.start_pos, tmp.params) from (values |
| ('a' as str, 'a' as `pattern`, -1 as start_pos, '' as params), |
| ('foobar', 'foobar', 1, 'i'), |
| ('iPhone\niPad\niPod', '^I.*$', 1, 'imn')) as tmp |
| ---- CATCH |
| Illegal starting position -1 |
| ==== |
| ---- QUERY |
| select regexp_match_count(tmp.str, tmp.`pattern`, tmp.start_pos, tmp.params) from (values |
| ('a' as str, 'a' as `pattern`, 1 as start_pos, 'xyz' as params), |
| ('foobar', 'foobar', 1, 'i'), |
| ('iPhone\niPad\niPod', '^I.*$', 1, 'imn')) as tmp |
| ---- CATCH |
| Illegal match parameter x |
| ==== |
| ---- QUERY |
| select regexp_escape(tmp.str) from (values |
| ('a.b\\c+d*e?f[g]h$i(j)k{l}m=n!o<p>q|r:s-t' as str)) as tmp |
| ---- RESULTS |
| 'a\\.b\\\\c\\+d\\*e\\?f\\[g\\]h\\$i\\(j\\)k\\{l\\}m\\=n\\!o\\<p\\>q\\|r\\:s\\-t' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select regexp_extract(regexp_escape(tmp.str), |
| tmp.`pattern`, tmp.index) from (values |
| ('Hello\\world' as str, '([[:alpha:]]+)(\\\\\\\\)([[:alpha:]]+)' as `pattern`, 2 as index) |
| ) as tmp |
| ---- RESULTS |
| '\\\\' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| select regexp_extract(regexp_escape(tmp.str), |
| tmp.`pattern`, tmp.index) from (values |
| ('Hello\\world' as str, '([[:alpha:]]+)(\\\\\\\\)([[:alpha:]]+)' as `pattern`, 3 as index) |
| ) as tmp |
| ---- RESULTS |
| 'world' |
| ---- TYPES |
| string |
| ==== |
| ---- QUERY |
| # IMPALA-2147: IS [NOT] DISTINCT FROM and "<=>" |
| select NULL <=> NULL |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select NULL <=> 1 |
| ---- RESULTS |
| false |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select NULL <=> "foo" |
| ---- RESULTS |
| false |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select NULL IS DISTINCT FROM NULL |
| ---- RESULTS |
| false |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select NULL IS DISTINCT FROM 3.14 |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select cast(0 as bigint) IS DISTINCT FROM NULL |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select 2.78 IS DISTINCT FROM 3.14 |
| ---- RESULTS |
| true |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| select 2.78 IS NOT DISTINCT FROM 3.14 |
| ---- RESULTS |
| false |
| ---- TYPES |
| BOOLEAN |
| ==== |
| ---- QUERY |
| # IMPALA-2184: Codegen does not catch exceptions in FROM_UNIXTIME() |
| # Make sure codegen is enabled |
| set EXEC_SINGLE_NODE_ROWS_THRESHOLD=0; |
| select min(from_unixtime(ts)) |
| from (values (1429705108399870 as ts), (1429704907057354)) invalid_timestamps; |
| ---- RESULTS |
| 'NULL' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # IMPALA-2184/IMPALA-2868 |
| select CAST(1312822084003 as timestamp) FROM alltypestiny GROUP BY 1 |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| timestamp |
| ==== |
| ---- QUERY |
| # base64 encoding/decoding |
| select count(*) from functional.alltypes |
| where length(string_col) > 0 && |
| length(base64encode(string_col)) <= length(string_col) |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # base64 encoding/decoding |
| select count (*) from functional.alltypes |
| where base64decode(base64encode(string_col)) IS DISTINCT FROM string_col |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # base64 decoding a string of invalid length (must be divisible by 4) |
| select base64decode('foo') |
| ---- RESULTS |
| 'NULL' |
| ---- TYPES |
| STRING |
| ---- ERRORS |
| UDF WARNING: Invalid base64 string; input length is 3, which is not a multiple of 4. |
| ==== |
| ---- QUERY |
| # base64 decoding a string with invalid characters |
| select base64decode('abc%') |
| ---- RESULTS |
| 'NULL' |
| ---- TYPES |
| STRING |
| ---- ERRORS |
| UDF WARNING: Could not base64 decode input in space 4; actual output length 0 |
| ==== |
| ---- QUERY |
| # IMPALA-1731: test parsing infinity values |
| select cast('inf' as double), cast('InFinity' as float), |
| cast('inf ' as float), cast(' infinity ' as double), |
| cast('infinite' as double), cast('1.23inf' as double), cast('1inf' as float) |
| ---- RESULTS |
| Infinity,Infinity,Infinity,Infinity,NULL,NULL,NULL |
| ---- TYPES |
| double,float,float,double,double,double,float |
| ==== |
| ---- QUERY |
| # IMPALA-1731: test parsing NaN values |
| select cast('nan' as double), cast('NaN' as float), cast(' nan ' as double), |
| cast('nana' as double), cast('1.23nan' as double), cast('1nan' as float) |
| ---- RESULTS |
| NaN,NaN,NaN,NULL,NULL,NULL |
| ---- TYPES |
| double,float,double,double,double,float |
| ==== |
| ---- QUERY |
| # IMPALA-3868: test parsing float with multiple dots |
| select cast('1.23' as double), cast('.1.23' as float), cast('123.456.' as double), |
| cast('1.23.456' as double), cast('1.23.4.5' as float), cast('0..e' as double) |
| ---- RESULTS |
| 1.23,NULL,NULL,NULL,NULL,NULL |
| ---- TYPES |
| double,float,double,double,float,double |
| ==== |
| ---- QUERY |
| # IMPALA-4058: reverse assumed memory was 16-byte aligned; reverse 16, 17, 32, 33, and 64 |
| # byte strings |
| select reverse('123456789abcdef0'), reverse('123456789abcdef01'), |
| reverse('123456789abcdef0!@#$%^&*(ABCDEF)'), |
| reverse('123456789abcdef0!@#$%^&*(ABCDEF)`'), |
| reverse('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ345678901234') |
| ---- RESULTS |
| '0fedcba987654321','10fedcba987654321',')FEDCBA(*&^%$#@!0fedcba987654321','`)FEDCBA(*&^%$#@!0fedcba987654321','432109876543ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba' |
| ---- TYPES |
| STRING,STRING,STRING,STRING,STRING |
| ==== |
| ---- QUERY |
| # IMPALA-4196: Regression test for built-in bit-byte-functions with codegen |
| select count(shiftleft(int_col, 1)) from functional_parquet.alltypes |
| ---- RESULTS |
| 7300 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Test constant timestamp expressions in various query clauses. Exercises |
| # timestamp literals when run with expr rewrites enabled. |
| select count(now()) c, avg(cast('2016-11-22 16:40:00.00' as timestamp)) a, |
| cast('2016-11-22 16:40:00.00' as timestamp) as g |
| from functional_parquet.alltypes |
| where timestamp_col < cast('2013-02-18 20:46:00.01' as timestamp) |
| group by g |
| having avg(cast('2016-11-22 16:40:00.00' as timestamp)) = |
| cast('2016-11-22 16:40:00.00' as timestamp) |
| order by c, cast('2016-11-22 16:40:00.00' as timestamp) |
| ---- RESULTS |
| 7300,2016-11-22 16:40:00,2016-11-22 16:40:00 |
| ---- TYPES |
| BIGINT, TIMESTAMP, TIMESTAMP |
| ==== |
| ---- QUERY |
| # Constant timestamp expressions in a join condition / runtime filter as well |
| # as a select node. |
| select count(*) from ( |
| select a.timestamp_col from |
| functional_parquet.alltypessmall a |
| inner join functional_parquet.alltypestiny b |
| on cast('2013-02-18 20:46:00.01' as timestamp) + interval a.id years = |
| cast('2013-02-18 20:46:00.01' as timestamp) + interval b.id years |
| order by a.timestamp_col limit 10 |
| ) v |
| where timestamp_col < cast('2013-02-18 20:46:00.01' as timestamp) |
| ---- RESULTS |
| 8 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-4739: rewrites in HAVING clause |
| select tinyint_col, count(*) cnt |
| from functional_parquet.alltypesagg |
| group by 1 |
| having count(*) > 1000 or count(*) > 1000 |
| and count(*) between 1500 and 2500 |
| ---- TYPES |
| TINYINT, BIGINT |
| ---- RESULTS |
| NULL,2000 |
| ==== |
| ---- QUERY |
| # IMPALA-4550: Regression test for proper cast analysis after slot substitution within a |
| # no-op explicit cast. |
| select /* +straight_join */ a.id |
| from functional.alltypestiny a |
| join functional.alltypestiny b on a.string_col = b.timestamp_col |
| where (cast(a.string_col as string) > 'a'); |
| ---- RESULTS |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test from_utc_timestamp() returning out-of-range result. |
| select from_utc_timestamp(CAST("1400-01-01 05:00:00" as TIMESTAMP), "PST") |
| from alltypes |
| limit 1 |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ---- ERRORS |
| UDF WARNING: Timestamp '1400-01-01 05:00:00' did not convert to a valid local time in timezone 'PST' |
| ==== |
| ---- QUERY |
| # Test from_utc_timestamp() returning out-of-range result. |
| select to_utc_timestamp(CAST("1400-01-01 05:00:00" as TIMESTAMP), "JST") |
| from alltypes |
| limit 1 |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ---- ERRORS |
| UDF WARNING: Timestamp '1400-01-01 05:00:00' in timezone 'JST' could not be converted to UTC |
| ==== |
| ---- QUERY |
| # Test out-of-range value handling when adding dates. |
| select CAST('9999-12-31 21:00:00' AS TIMESTAMP) + INTERVAL 1 DAYS |
| from alltypes |
| limit 1 |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ---- ERRORS |
| UDF WARNING: Cannot add interval 1: Year is out of valid range: 1400..9999 |
| ==== |
| ---- QUERY |
| # Test out-of-range value handling when subtracting dates. |
| select CAST('1400-01-01 21:00:00' AS TIMESTAMP) - INTERVAL 1 DAYS |
| from alltypes |
| limit 1 |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ---- ERRORS |
| UDF WARNING: Cannot subtract interval 1: Year is out of valid range: 1400..9999 |
| ==== |
| ---- QUERY |
| # Test <1400 date handling during double to timestamp conversion. |
| select CAST(CAST(CAST('1400-01-01' AS TIMESTAMP) AS DOUBLE) - 0.1 AS TIMESTAMP) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test >=10000 date handling during double to timestamp conversion. |
| select CAST(CAST(CAST('9999-12-31 23:59:59' AS TIMESTAMP) AS DOUBLE) + 1.1 AS TIMESTAMP) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test <1400 date handling during bigint to timestamp conversion. |
| select CAST(CAST(CAST('1400-01-01' AS TIMESTAMP) AS BIGINT) - 1 AS TIMESTAMP) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test >=10000 date handling during bigint to timestamp conversion. |
| select CAST(CAST(CAST('9999-12-31 23:59:59' AS TIMESTAMP) AS BIGINT) + 1 AS TIMESTAMP) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test <1400 date handling during microsecond unix time to timestamp conversion. |
| select unix_micros_to_utc_timestamp( |
| CAST(CAST('1400-01-01' AS TIMESTAMP) AS BIGINT) * 1000000 - 1) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test >=10000 date handling during microsecond unix time to timestamp conversion. |
| select unix_micros_to_utc_timestamp( |
| (CAST(CAST('9999-12-31 23:59:59' AS TIMESTAMP) AS BIGINT) + 1) * 1000000 + 1) |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test <1400 date handling during string to timestamp conversion. |
| select CAST("1399-12-31 23:59:59.99999" AS TIMESTAMP); |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test >=10000 date handling during string to timestamp conversion. |
| select CAST("10000-01-01" AS TIMESTAMP); |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test leap year checking during string to timestamp conversion. |
| select CAST("1900-02-29" AS TIMESTAMP); |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # Test invalid format handling during string to timestamp conversion. |
| select CAST("not a timestamp" AS TIMESTAMP); |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| TIMESTAMP |
| ==== |
| ---- QUERY |
| # IMPALA-4574: UUID() is not a constant expression. |
| select count(*) from functional.alltypestiny group by concat(uuid(), "_test") |
| ---- RESULTS |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-4716: Tests that decode with 'when' values of different types is analyzed |
| # correctly when expr rewrites are enabled. |
| select decode(0, 1, 0, id, 1, 2) a from functional.alltypestiny order by a |
| ---- RESULTS |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| # Test expr rewrite behavior of IF with constant conditions. |
| select if(true, id * 10, 0) from functional.alltypestiny where id = if(false, 0, 1) |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # Test expr rewrite behavior of CompoundPredicate with constant conditions. |
| select id, null and id = 0 from functional.alltypestiny where id < 2 or false |
| ---- RESULTS |
| 0,NULL |
| 1,false |
| ---- TYPES |
| INT,BOOLEAN |
| ==== |
| ---- QUERY |
| # Test expr rewrite behavior of CASE with constant conditions. |
| select id, case id when null then null when 1 then 10 when true then 20 end |
| from functional.alltypestiny |
| where id = case when false then 0 when 1 = 1 then 1 else 2 end |
| ---- RESULTS |
| 1,10 |
| ---- TYPES |
| INT,TINYINT |
| ==== |
| ---- QUERY |
| # IMPALA-4849: verify that DISTINCT with a CASE that is rewritten analyzes correctly. |
| select distinct case when true then id else 0 end from functional.alltypestiny |
| ---- RESULTS |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # IMPALA-5125: test behavior when an agg function could be eliminated by expr rewrites. |
| select if (true, 0, sum(id)) from functional.alltypestiny |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # IMPALA-5725: test coalesce() is not rewritten when its first parameter is a non-nullable |
| # Kudu column, but can have NULL values due to an outer join. |
| select coalesce(b.id, a.id), b.id, a.id |
| from functional_kudu.alltypes a left join functional_kudu.alltypestiny b on a.id = b.id |
| where a.id = 100 |
| ---- RESULTS |
| 100,NULL,100 |
| ---- TYPES |
| INT,INT,INT |
| ==== |
| ---- QUERY |
| # IMPALA-5725: test coalesce() is not rewritten when its first parameter is an HDFS |
| # partition col with no NULL partitions, but can have NULL values due to an outer join. |
| select coalesce(b.year, a.id), b.id, a.id |
| from functional.alltypes a left join functional.alltypestiny b on a.id = b.id |
| where a.id = 100 |
| ---- RESULTS |
| 100,NULL,100 |
| ---- TYPES |
| INT,INT,INT |
| ==== |
| ---- QUERY |
| select id from functional.alltypes |
| where id = case cast('nan' as double) when cast('nan' as double) then 0 else 1 end |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # Test expr rewrite behavior of RemoveRedundantStringCast with timestamp column |
| select id, timestamp_col from functional.alltypestiny |
| where cast(timestamp_col as string) = '2009-02-01 00:00:00' |
| ---- RESULTS |
| 2,2009-02-01 00:00:00 |
| ---- TYPES |
| int, timestamp |
| ==== |
| ---- QUERY |
| select |
| murmur_hash(bool_col), murmur_hash(tinyint_col), murmur_hash(smallint_col), |
| murmur_hash(int_col), murmur_hash(bigint_col), murmur_hash(float_col), |
| murmur_hash(double_col), murmur_hash(date_string_col), murmur_hash(string_col), |
| murmur_hash(timestamp_col), murmur_hash(year), murmur_hash(month) |
| from functional.alltypes where id = 7 |
| ---- RESULTS |
| 6351753276682545529,-8688181892109895221,5243888771994935971,988560926123810380,7108101660231151623,-8653637999116590182,-6387622242983883150,3788918177590065252,-7446916648201533712,7077699884854357665,7331012058162401363,-780611581681153783 |
| ---- TYPES |
| BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT , BIGINT |
| ==== |