testdata/workloads/functional-query/queries/QueryTest/exprs.test

====
---- 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, bigint
====
---- 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
bigint, bigint
====
---- 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, cast(round(float_col) as bigint)) 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 to_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
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
====
---- QUERY
# IMPALA-5315: Test support for non zero-padded date/time strings cast as timestamp
select cast('2001-1-21 12:5:30' as timestamp)
---- RESULTS
2001-01-21 12:05:30
---- TYPES
timestamp
====
---- QUERY
select cast('2001-1-2 1:5:3.123' as timestamp)
---- RESULTS
2001-01-02 01:05:03.123000000
---- TYPES
timestamp
====
---- QUERY
select cast('1:5:3' as timestamp)
---- RESULTS
01:05:03
---- TYPES
timestamp
====
---- QUERY
select cast('1:5:3.1234567' as timestamp)
---- RESULTS
01:05:03.123456700
---- TYPES
timestamp
====
---- QUERY
select cast('2001-1-2' as timestamp)
---- RESULTS
2001-01-02 00:00:00
---- TYPES
timestamp
====
---- QUERY
# IMPALA-6771: Test that the in-predicate set does not have default initialized
# values that can result in wrong results. For a string column the default initialized
# value is an empty string.
select count(*) from functional.alltypes
where regexp_replace(string_col, '1', '')
in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
---- RESULTS
6570
---- TYPES
bigint
====
---- QUERY
# test coordinator() function in a real query
select coordinator(), count(*) from functional.alltypesagg where int_col = 1
---- TYPES
string, bigint
====
---- QUERY
# IMPALA-376: Test get_json_object builtin function
select get_json_object(t.json, '$.a.b.c') from (values (
  ('{"a": {"b": {"c": 1}}}' as json),
  ('{"a": {"b": {"c": 2}}}'),
  ('{"a": {"b": {"c": 3}}}')
)) t
---- RESULTS
'1'
'2'
'3'
---- TYPES
STRING
====
---- QUERY
select get_json_object(t.json, '$.a'),
  get_json_object(t.json, '$.b'),
  get_json_object(t.json, '$.c')
from (values (
  ('{"a":1, "b":2, "c":3}' as json),
  ('{"b":2, "c":3}'),
  ('{"c":3}')
)) t
---- RESULTS
'1','2','3'
'NULL','2','3'
'NULL','NULL','3'
---- TYPES
STRING,STRING,STRING
====
---- QUERY
select get_json_object(t.json, '$[0]'),
  get_json_object(t.json, '$[1]'),
  get_json_object(t.json, '$[2]')
from (values (
  ('["a", "b", "c"]' as json),
  ('["b", "c"]'),
  ('["c"]')
)) t
---- RESULTS
'a','b','c'
'b','c','NULL'
'c','NULL','NULL'
---- TYPES
STRING,STRING,STRING
====
---- QUERY
select count(*) from (values (
  ('{"a": {"b": {"c": 1}}}' as json),
  ('{"a": {"b": {"c": 2}}}'),
  ('{"a": {"b": {"c": 3}}}')
)) t
where cast(get_json_object(t.json, '$.a.b.c') as int) > 1
---- RESULTS
2
---- TYPES
BIGINT
====
---- QUERY
# IMPALA-6661 - NaN should not evaluate as the same as any other NaN via <=>
WITH W AS (SELECT T.*, CAST(SQRT(X) AS FLOAT)  P, CAST(SQRT(Y) AS FLOAT) Q
FROM (VALUES((CAST(-1.0 AS FLOAT) X, CAST(-1.0 AS FLOAT) Y), (-1.0, 0), (0, -1.0), (0, 0))) T )
SELECT * FROM W WHERE W.Q<=>W.P
---- RESULTS
0,0,0,0
---- TYPES
FLOAT, FLOAT, FLOAT, FLOAT
====
---- QUERY
# IMPALA-9023 - verify that case when true appears in where clause will not throw IllegalStateException
select id, bool_col from functional.alltypessmall where case when true then id = 50 END
---- RESULTS
50,true
---- TYPES
int, boolean
====
---- QUERY
select distinct year from functional.alltypes where case when true = true then year < 2010 when false then year > 2010 end;
---- RESULTS
2009
---- TYPES
int
====
---- QUERY
select distinct year from functional.alltypes where case when year = 2010 then month < 5 when year = 2009 then month < 4 end AND year = 2010;
---- RESULTS
2010
---- TYPES
int
====