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'
---- RESULTS
'Function has been created.'
====
---- QUERY
create function $DATABASE.fn(int) RETURNS double
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
# Test IF NOT EXISTS
create function if not exists $DATABASE.fn(int) RETURNS double
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
---- RESULTS
'Function already exists.'
====
---- 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
 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)
---- RESULTS
'Function has been dropped.'
====
---- 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
drop function if exists fn()
---- RESULTS
'Function does not exist.'
====
---- 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
====
---- QUERY
# Create a function that has the same name as built-in function without FQN.
CREATE FUNCTION sin(int) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
# Make sure the custom sin function was created successfully.
SHOW FUNCTIONS IN $DATABASE;
---- RESULTS: VERIFY_IS_SUBSET
'INT','sin(INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Query using custom and built-in sin functions.
SELECT $DATABASE.sin(0), sin(0);
---- RESULTS:
NULL,0
---- TYPES
INT, DOUBLE
====
---- QUERY
# Drop a function that has the same name as built-in function without FQN.
DROP FUNCTION sin(int);
====
---- QUERY
# Make sure the custom sin function was dropped successfully.
SHOW FUNCTIONS IN $DATABASE;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS: VERIFY_IS_NOT_IN
'INT','sin(INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Create a function that has the same name as built-in function with FQN.
CREATE FUNCTION $DATABASE.sin(int) RETURNS int
LOCATION '$FILESYSTEM_PREFIX/test-warehouse/libTestUdfs.so' SYMBOL='Fn'
====
---- QUERY
# Make sure the custom sin function was created successfully.
SHOW FUNCTIONS IN $DATABASE;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS: VERIFY_IS_SUBSET
'INT','sin(INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
====
---- QUERY
# Query using custom sin and built-in sin functions.
SELECT $DATABASE.sin(0), sin(0);
---- RESULTS:
NULL,0
---- TYPES
INT, DOUBLE
====
---- QUERY
# Drop a function that has the same name as built-in function with FQN.
DROP FUNCTION $DATABASE.sin(int);
====
---- QUERY
# Make sure the custom sin function was dropped successfully.
SHOW FUNCTIONS IN $DATABASE;
---- LABELS
return type, signature, binary type, is persistent
---- RESULTS: VERIFY_IS_NOT_IN
'INT','sin(INT)','NATIVE','true'
---- TYPES
STRING, STRING, STRING, STRING
=====