Aggregate functions not supported about redshift-udfs

[ekta1007]

Similar issues. Noted my use case here.
http://stackoverflow.com/questions/35191955/passing-returning-a-list-array-as-a-parameter-return-type-to-a-udf-in-redshif

I isolated the problem. Redshift UDF's won't let me pass an** entire column even if I map it to "anyelement" datatype**. I could use LISTAGG(that I could treat in python by breaking the string list) , but I see overflow exceptions , since it maps to varchar. I noticed you used VARCHAR as arguments to these functions :agg_init_blank_varchar, agg_agg_numbers_to_list,agg_agg_numbers_to_list .

What's my best bet ?

-- simple example 
create or replace function test1(y anyelement ) 
RETURNS anyelement
STABLE 
AS $$
  val=[]
  for y1 in y :
      if y1 >3
          val.append(y1)  :
      else :
          val.append(y1)
    return val
$$LANGUAGE plpythonu ;

select price, test1(price) from my_table  where price >0 limit 5; 
-- This suggests it works row by row 
price    test1
364.99    364.99
144.99    144.99
53.99    53.99
19.99    0
209.99    209.99

--I could use listagg , but it overflows since , getting all the prices for a day - throws a Result size exceeds LISTAGG limit

select listagg(price,',')
from
(
select price
from my_table
where price >0 and cast(created_on as date)='20151012'
)

[ekta1007]

Unrelated to the functions themselves, but it is possible to nest UDF's within each other ?
Following is a code for computing confidence intervals in A/B tests

set search_path to public;
create function cumnormdist(x float)
  returns float
IMMUTABLE AS $$
  import math
  b1 = 0.319381530
  b2 = -0.356563782
  b3 = 1.781477937
  b4 = -1.821255978
  b5 = 1.330274429
  p = 0.2316419
  c = 0.39894228
  h=math.exp(-x * x / 2.0)
  if(x >= 0.0) :
    t = 1.0 / ( 1.0 + p * x )
    return (1.0 - c * h * t *( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ))
  else :
    t = 1.0 / ( 1.0 - p * x );
    return ( c * h * t *( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ))
$$ language plpythonu;


set search_path to public;
create or replace function conversion(experience_total float,experience_conversions float)
  returns float
IMMUTABLE AS $$
  return experience_conversions*1.0/experience_total
$$ language plpythonu;


create or replace function zscore(total_c float,conversions_c float,total_t float,conversions_t float )
  returns float
IMMUTABLE AS $$
  import math
  z = conversion(total_t,conversions_t )-conversion(total_c,conversions_c) # Difference in means
  s =(conversion(total_t,conversions_t)*(1-conversion(total_t,conversions_t)))/total_t+(conversion(total_c,conversions_c)*(1-conversion(total_c,conversions_c)))/total_c
  return float(z)/float(math.sqrt(s))
$$ language plpythonu;

create or replace function confidence(total_c float,conversions_c float,total_t float,conversions_t float )
  returns float
IMMUTABLE AS $$
  import math
  return **(1-float(cumnormdist(zscore(total_c float,conversions_c float,total_t float,conversions_t float )),4))*100.00**
$$ language plpythonu;

Note that cumnormdist & zscore are udf's themselves.

[AntoineAugusti]

What's the state of this issue? This currently throws an error today on a Redshift cluster with the error message ERROR: User Defined Aggregate is not supported. 😢

[davidthewatson]

Yeah, me too. As of 10/24/16 all of the tests on aggregate functions are failing:

Making function agg_init_blank_varchar
Making function agg_finalize_varchar
Making function agg_agg_numbers_to_list
Making function json_array_first
Making function json_array_last
Making function json_array_nth
Making function json_array_sort
Making function json_array_reverse
Making function json_array_pop
Making function json_array_push
Making function json_array_concat
Making aggregate json_array_agg
Error: ERROR:  User Defined Aggregate is not supported.
Making function mysql_year
Making function mysql_month
Making function mysql_day
Making function mysql_hour
Making function mysql_minute
Making function mysql_second
Making function mysql_yearweek
Making function now
Making function posix_timestamp
Making function email_name
Making function email_domain
Making function url_protocol
Making function url_domain
Making function url_path
Making function url_param
Making function split_count
Making function titlecase
Making function str_multiply
Making function str_index
Making function str_rindex
Making function str_count
Making function agg_agg_comma_concat
Making aggregate comma_concat
Error: ERROR:  User Defined Aggregate is not supported.
Making function format_num
Making function agg_finalize_harmonic_mean
Making aggregate harmonic_mean
Error: ERROR:  User Defined Aggregate is not supported.
Making function agg_finalize_second_max
Making aggregate second_max
Error: ERROR:  User Defined Aggregate is not supported.
Making function experiment_result_p_value
Testing function agg_init_blank_varchar.
Testing function agg_finalize_varchar.
Testing function agg_agg_numbers_to_list......
Testing function json_array_first.......
Testing function json_array_last.......
Testing function json_array_nth..........
Testing function json_array_sort........
Testing function json_array_reverse.......
Testing function json_array_pop.......
Testing function json_array_push.......
Testing function json_array_concat.......
Testing aggregate json_array_agg
Error on {:rows=>["foo", "bar", "baz"], :expect=>"[\"foo\", \"bar\", \"baz\"]", :example=>true}: ERROR:  function json_array_agg(character varying) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Error on {:rows=>["foo"], :expect=>"[\"foo\"]", :example=>true}: ERROR:  function json_array_agg(character varying) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Testing function mysql_year...
Testing function mysql_month...
Testing function mysql_day...
Testing function mysql_hour...
Testing function mysql_minute...
Testing function mysql_second...
Testing function mysql_yearweek...
Testing function now.
Testing function posix_timestamp.
Testing function email_name....
Testing function email_domain....
Testing function url_protocol.....
Testing function url_domain....
Testing function url_path....
Testing function url_param....
Testing function split_count...
Testing function titlecase....
Testing function str_multiply.....
Testing function str_index.....
Testing function str_rindex......
Testing function str_count......
Testing function agg_agg_comma_concat......
Testing aggregate comma_concat
Error on {:rows=>["foo", "bar", "baz"], :expect=>"foo,bar,baz", :example=>true}: ERROR:  function comma_concat(character varying) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Error on {:rows=>["foo"], :expect=>"foo", :example=>true}: ERROR:  function comma_concat(character varying) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Testing function format_num......
Testing function agg_finalize_harmonic_mean......
Testing aggregate harmonic_mean
Error on {:rows=>[3.5, 4.5, 5.5], :expect=>4.34937238493724, :example=>true}: ERROR:  function harmonic_mean(double precision) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Error on {:rows=>[6, 10, 20], :expect=>9.47368421052632, :example=>true}: ERROR:  function harmonic_mean(integer) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Testing function agg_finalize_second_max......
Testing aggregate second_max
Error on {:rows=>[3.5, 4.5, 5.5], :expect=>4.5, :example=>true}: ERROR:  function second_max(double precision) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Error on {:rows=>[6, -10, 20, 50], :expect=>20, :example=>true}: ERROR:  function second_max(integer) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Testing function experiment_result_p_value....

Is this code still being supported by periscope?

[mopatches]

Hey @davidthewatson! Sorry about the tests failing - we should remove the aggregate functions form the corpus. If you make a PR to do that I'll merge it in. Thanks for raising the issue!

[davidthewatson]

Hey @mopatches No problem! Here's the PR:

#6

Thanks!

[mopatches]

Merged! Thank you @davidthewatson :)