[HAWQ-1483] cache lookup failure

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Cannot Reproduce
• Fix Version/s: 2.4.0.0
• Assignee: Radar Da Lei
• Reporter: Rahul Iyer
• Created: 09/Jun/17 20:41
• Updated: 26/Jul/18 02:57
• Resolved: 26/Jul/18 02:57

Description

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:

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
);

Error:

select count(distinct outlook), greatest_running_total(humidity::integer) from example_data;

ERROR:  cache lookup failed for function 0 (fmgr.c:223)

Execution goes through if I remove the distinct or if I add another column for the count(distinct).

select count(distinct outlook) as c1, count(distinct windy) as c2, greatest_running_total(humidity) from example_data;

 c1 | c2 | greatest_running_total
----+----+------------------------
  3 |  2 |
(1 row)

select count(outlook) as c1, greatest_running_total(humidity) from example_data;

 count | greatest_running_total
-------+------------------------
    14 |
(1 row)

It's an older build - I don't have the resources at present to test this on the latest HAWQ.

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)