[jira] [Commented] (HAWQ-1483) cache lookup failure

["Radar Lei (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HAWQ-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16072149#comment-16072149 ] 

Radar Lei commented on HAWQ-1483:
---------------------------------

Verified this issue still exist on HAWQ 2.2.0.0-incubating version.

> cache lookup failure
> --------------------
>
>                 Key: HAWQ-1483
>                 URL: https://issues.apache.org/jira/browse/HAWQ-1483
>             Project: Apache HAWQ
>          Issue Type: Bug
>            Reporter: Rahul Iyer
>            Assignee: Radar Lei
>
> I'm getting a failure when performing a distinct count with another immutable aggregate. We found this issue when running MADlib on HAWQ 2.0.0. Please find below a simple repro. 
> Setup: 
> {code}
> CREATE TABLE example_data(
>     id SERIAL,
>     outlook text,
>     temperature float8,
>     humidity float8,
>     windy text,
>     class text) ;
> COPY example_data (outlook, temperature, humidity, windy, class) FROM stdin DELIMITER ',' NULL '?' ;
> sunny, 85, 85, false, Don't Play
> sunny, 80, 90, true, Don't Play
> overcast, 83, 78, false, Play
> rain, 70, 96, false, Play
> rain, 68, 80, false, Play
> rain, 65, 70, true, Don't Play
> overcast, 64, 65, true, Play
> sunny, 72, 95, false, Don't Play
> sunny, 69, 70, false, Play
> rain, 75, 80, false, Play
> sunny, 75, 70, true, Play
> overcast, 72, 90, true, Play
> overcast, 81, 75, false, Play
> rain, 71, 80, true, Don't Play
> \.
> create function grt_sfunc(agg_state point, el float8)
> returns point
> immutable
> language plpgsql
> as $$
> declare
>   greatest_sum float8;
>   current_sum float8;
> begin
>   current_sum := agg_state[0] + el;
>   if agg_state[1] < current_sum then
>     greatest_sum := current_sum;
>   else
>     greatest_sum := agg_state[1];
>   end if;
>   return point(current_sum, greatest_sum);
> end;
> $$;
> create function grt_finalfunc(agg_state point)
> returns float8
> immutable
> strict
> language plpgsql
> as $$
> begin
>   return agg_state[1];
> end;
> $$;
> create aggregate greatest_running_total (float8)
> (
>     sfunc = grt_sfunc,
>     stype = point,
>     finalfunc = grt_finalfunc
> );
> {code}
> Error: 
> {code}
> select count(distinct outlook), greatest_running_total(humidity::integer) from example_data;
> {code} 
> {code}
> ERROR:  cache lookup failed for function 0 (fmgr.c:223)
> {code}
> Execution goes through if I remove the {{distinct}} or if I add another column for the {{count(distinct)}}. 
> {code:sql}
> select count(distinct outlook) as c1, count(distinct windy) as c2, greatest_running_total(humidity) from example_data;
> {code}
> {code}
>  c1 | c2 | greatest_running_total
> ----+----+------------------------
>   3 |  2 |
> (1 row)
> {code}
> {code:sql}
> select count(outlook) as c1, greatest_running_total(humidity) from example_data;
> {code}
> {code}
>  count | greatest_running_total
> -------+------------------------
>     14 |
> (1 row)
> {code}
> It's an older build - I don't have the resources at present to test this on the latest HAWQ. 
> {code}
> select version();
>                                                                                     version
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.2.15 (Greenplum Database 4.2.0 build 1) (HAWQ 2.0.0.0 build 22126) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Apr 25 2016 09:52:54
> (1 row)
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)