Rahul Iyer created HAWQ-1483: -------------------------------- Summary: 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.3.15#6346)