testdata/workloads/functional-query/queries/QueryTest/functions-ddl.test

====
---- QUERY
# Add functions and test function overloading and scoping.
create function $DATABASE.fn() RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
create function $DATABASE.fn(int) RETURNS double
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
create function $DATABASE.fn(int, string) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
create function $DATABASE.fn(string, int) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
create function $DATABASE.fn2(int) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn2'
====
---- QUERY
create function $DATABASE.fn2(int, string) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn2'
====
---- QUERY
create function $DATABASE.fn_var_arg(int...) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='VarSum'
====
---- QUERY
create aggregate function $DATABASE.agg_fn(int) RETURNS bigint
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libudasample.so' UPDATE_FN='CountUpdate'
====
---- QUERY
create aggregate function $DATABASE.agg_fn(int, string) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdas.so' UPDATE_FN='TwoArgUpdate'
====
---- QUERY
show functions in $DATABASE
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS
'INT','fn_var_arg(INT...)','NATIVE','true'
'INT','fn2(INT)','NATIVE','true'
'INT','fn2(INT, STRING)','NATIVE','true'
'INT','fn()','NATIVE','true'
'DOUBLE','fn(INT)','NATIVE','true'
'INT','fn(INT, STRING)','NATIVE','true'
'INT','fn(STRING, INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
show aggregate functions in $DATABASE
---- RESULTS
'BIGINT','agg_fn(INT)','NATIVE','true'
'INT','agg_fn(INT, STRING)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Check that none of the functions show up as analytic functions.
show analytic functions in $DATABASE
---- RESULTS
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
show create function $DATABASE.fn_var_arg
---- RESULTS: MULTI_LINE
['CREATE FUNCTION $DATABASE.fn_var_arg(INT...)
 RETURNS INT
 LOCATION '$NAMENODE/test-warehouse/libTestUdfs.so'
 SYMBOL='_Z6VarSumPN10impala_udf15FunctionContextEiPKNS_6IntValE'
']
---- TYPES
STRING
====
---- QUERY
show create aggregate function $DATABASE.agg_fn
---- RESULTS: MULTI_LINE
['CREATE AGGREGATE FUNCTION $DATABASE.agg_fn(INT)
 RETURNS BIGINT
 LOCATION '$NAMENODE/test-warehouse/libudasample.so'
 UPDATE_FN='_Z11CountUpdatePN10impala_udf15FunctionContextERKNS_6IntValEPNS_9BigIntValE'
 INIT_FN='_Z9CountInitPN10impala_udf15FunctionContextEPNS_9BigIntValE'
 MERGE_FN='_Z10CountMergePN10impala_udf15FunctionContextERKNS_9BigIntValEPS2_'
 FINALIZE_FN='_Z13CountFinalizePN10impala_udf15FunctionContextERKNS_9BigIntValE'
CREATE AGGREGATE FUNCTION $DATABASE.agg_fn(INT, STRING)
 RETURNS INT
 LOCATION '$NAMENODE/test-warehouse/libTestUdas.so'
 UPDATE_FN='_Z12TwoArgUpdatePN10impala_udf15FunctionContextERKNS_6IntValERKNS_9StringValEPS2_'
 INIT_FN='_Z10TwoArgInitPN10impala_udf15FunctionContextEPNS_6IntValE'
 MERGE_FN='_Z11TwoArgMergePN10impala_udf15FunctionContextERKNS_6IntValEPS2_'
']
---- TYPES
STRING
====
---- QUERY
show create function _impala_builtins.sin
---- RESULTS: MULTI_LINE
['CREATE FUNCTION _impala_builtins.sin(DOUBLE)
 RETURNS DOUBLE
 LOCATION 'null'
 SYMBOL='_ZN6impala13MathFunctions3SinEPN10impala_udf15FunctionContextERKNS1_9DoubleValE'
']
---- TYPES
STRING
====
---- QUERY
show create aggregate function _impala_builtins.avg
---- RESULTS: MULTI_LINE
['CREATE AGGREGATE FUNCTION _impala_builtins.avg(BIGINT)
 RETURNS DOUBLE
 INTERMEDIATE FIXED_UDA_INTERMEDIATE(16)
 LOCATION 'null'
 UPDATE_FN='_ZN6impala18AggregateFunctions9AvgUpdateIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE'
 INIT_FN='_ZN6impala18AggregateFunctions7AvgInitEPN10impala_udf15FunctionContextEPNS1_9StringValE'
 MERGE_FN='_ZN6impala18AggregateFunctions8AvgMergeEPN10impala_udf15FunctionContextERKNS1_9StringValEPS4_'
 FINALIZE_FN='_ZN6impala18AggregateFunctions11AvgFinalizeEPN10impala_udf15FunctionContextERKNS1_9StringValE'
CREATE AGGREGATE FUNCTION _impala_builtins.avg(DECIMAL(*,*))
 RETURNS DECIMAL(*,*)
 INTERMEDIATE FIXED_UDA_INTERMEDIATE(24)
 LOCATION 'null'
 UPDATE_FN='_ZN6impala18AggregateFunctions16DecimalAvgUpdateEPN10impala_udf15FunctionContextERKNS1_10DecimalValEPNS1_9StringValE'
 INIT_FN='_ZN6impala18AggregateFunctions14DecimalAvgInitEPN10impala_udf15FunctionContextEPNS1_9StringValE'
 MERGE_FN='_ZN6impala18AggregateFunctions15DecimalAvgMergeEPN10impala_udf15FunctionContextERKNS1_9StringValEPS4_'
 FINALIZE_FN='_ZN6impala18AggregateFunctions18DecimalAvgFinalizeEPN10impala_udf15FunctionContextERKNS1_9StringValE'
CREATE AGGREGATE FUNCTION _impala_builtins.avg(DOUBLE)
 RETURNS DOUBLE
 INTERMEDIATE FIXED_UDA_INTERMEDIATE(16)
 LOCATION 'null'
 UPDATE_FN='_ZN6impala18AggregateFunctions9AvgUpdateIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE'
 INIT_FN='_ZN6impala18AggregateFunctions7AvgInitEPN10impala_udf15FunctionContextEPNS1_9StringValE'
 MERGE_FN='_ZN6impala18AggregateFunctions8AvgMergeEPN10impala_udf15FunctionContextERKNS1_9StringValEPS4_'
 FINALIZE_FN='_ZN6impala18AggregateFunctions11AvgFinalizeEPN10impala_udf15FunctionContextERKNS1_9StringValE'
CREATE AGGREGATE FUNCTION _impala_builtins.avg(TIMESTAMP)
 RETURNS TIMESTAMP
 INTERMEDIATE FIXED_UDA_INTERMEDIATE(16)
 LOCATION 'null'
 UPDATE_FN='_ZN6impala18AggregateFunctions18TimestampAvgUpdateEPN10impala_udf15FunctionContextERKNS1_12TimestampValEPNS1_9StringValE'
 INIT_FN='_ZN6impala18AggregateFunctions7AvgInitEPN10impala_udf15FunctionContextEPNS1_9StringValE'
 MERGE_FN='_ZN6impala18AggregateFunctions8AvgMergeEPN10impala_udf15FunctionContextERKNS1_9StringValEPS4_'
 FINALIZE_FN='_ZN6impala18AggregateFunctions20TimestampAvgFinalizeEPN10impala_udf15FunctionContextERKNS1_9StringValE'
']
---- TYPES
STRING
====
---- QUERY
drop function $DATABASE.fn2(int, string)
====
---- QUERY
show functions
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS
'INT','fn_var_arg(INT...)','NATIVE','true'
'INT','fn2(INT)','NATIVE','true'
'INT','fn()','NATIVE','true'
'DOUBLE','fn(INT)','NATIVE','true'
'INT','fn(INT, STRING)','NATIVE','true'
'INT','fn(STRING, INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
drop function if exists $DATABASE.fn2(int, string)
====
---- QUERY
show functions in $DATABASE
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS
'INT','fn_var_arg(INT...)','NATIVE','true'
'INT','fn2(INT)','NATIVE','true'
'INT','fn()','NATIVE','true'
'DOUBLE','fn(INT)','NATIVE','true'
'INT','fn(INT, STRING)','NATIVE','true'
'INT','fn(STRING, INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
show functions in $DATABASE;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS
'INT','fn_var_arg(INT...)','NATIVE','true'
'INT','fn2(INT)','NATIVE','true'
'INT','fn()','NATIVE','true'
'DOUBLE','fn(INT)','NATIVE','true'
'INT','fn(INT, STRING)','NATIVE','true'
'INT','fn(STRING, INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
drop function fn()
====
---- QUERY
show functions;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS
'INT','fn_var_arg(INT...)','NATIVE','true'
'INT','fn2(INT)','NATIVE','true'
'DOUBLE','fn(INT)','NATIVE','true'
'INT','fn(INT, STRING)','NATIVE','true'
'INT','fn(STRING, INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
drop function fn_var_arg(INT...)
====
---- QUERY
show functions;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS
'INT','fn2(INT)','NATIVE','true'
'DOUBLE','fn(INT)','NATIVE','true'
'INT','fn(INT, STRING)','NATIVE','true'
'INT','fn(STRING, INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
drop function agg_fn(int)
====
---- QUERY
show aggregate functions
---- RESULTS
'INT','agg_fn(INT, STRING)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Negative test for showing builtin scalar functions. The result
# should not contain aggregate or analytic functions. Note that
# the result must be non-empty for the test to suceed.
show functions in _impala_builtins;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS: VERIFY_IS_NOT_IN
'DOUBLE','avg(BIGINT)','BUILTIN','true'
'BIGINT','rank()','BUILTIN','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Positive test for showing builtin scalar functions.
show functions in _impala_builtins;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS: VERIFY_IS_SUBSET
'STRING','upper(STRING)','BUILTIN','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Negative test for showing builtin aggregate fuctions. The result should
# not contain scalar or analytic functions.
show aggregate functions in _impala_builtins;
---- RESULTS: VERIFY_IS_NOT_IN
'BIGINT','rank()','BUILTIN','true'
'STRING','upper(STRING)','BUILTIN','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Positive test for showing builtin aggregate functions.
show aggregate functions in _impala_builtins;
---- RESULTS: VERIFY_IS_SUBSET
'STRING','group_concat(STRING)','BUILTIN','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Negative test for showing builtin analytic functions. The result should
# not contain non-analytic aggregate functions or scalar functions.
show analytic functions in _impala_builtins;
---- RESULTS: VERIFY_IS_NOT_IN
'STRING','group_concat(STRING)','BUILTIN','true'
'STRING','upper(STRING)','BUILTIN','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Positive test for showing builtin analytic functions.
show analytic functions in _impala_builtins;
---- RESULTS: VERIFY_IS_SUBSET
'BIGINT','rank()','BUILTIN','true'
---- TYPES
STRING, STRING, STRING, STRING
====