[
https://issues.apache.org/jira/browse/HAWQ-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16555476#comment-16555476
]
Radar Lei commented on HAWQ-1483:
---------------------------------
Can not reproduce in latest 2.3.0.0 version, should already been fixed.
> 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
> Priority: Major
> Fix For: 2.4.0.0-incubating
>
>
> 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
(v7.6.3#76005)
