| ==== |
| ---- 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 |
| ===== |