src/test/regress/sql/gp_hashagg.sql - cloudberry - Git at Google

 -- tests MPP-2614
 -- two segments no mirrors
 drop table if exists hashagg_test;
 create table hashagg_test (id1 int4, id2 int4, day date, grp text, v int4);
 create index hashagg_test_idx on hashagg_test (id1,id2,day,grp);
 insert into hashagg_test values (1,1,'1/1/2006','there',1);
 insert into hashagg_test values (1,1,'1/2/2006','there',2);
 insert into hashagg_test values (1,1,'1/3/2006','there',3);
 insert into hashagg_test values (1,1,'1/1/2006','hi',2);
 insert into hashagg_test values (1,1,'1/2/2006','hi',3);
 insert into hashagg_test values (1,1,'1/3/2006','hi',4);

 -- this will get the wrong answer (right number of rows, wrong aggregates)
 set enable_seqscan=off;
 select grp,sum(v) from hashagg_test where id1 = 1 and id2 = 1 and day between '1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;

 create index hashagg_test_idx_bm on hashagg_test using bitmap (id1,id2,day,grp);
 set enable_indexscan=off; -- turn off b-tree index
 select grp,sum(v) from hashagg_test where id1 = 1 and id2 = 1 and day between '1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;

 -- correct answer
 set enable_seqscan=on;
 select grp,sum(v) from hashagg_test where id1 = 1 and id2 = 1 and day between '1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;


 -- Test a window-aggregate (median) with a join where the join column
 -- is further constrained by a constant. And the constant is of different
 -- type (int4, while the column is bigint). We had a bug at one point
 -- where this threw an error.

 create table tbl_a (id bigint) distributed by (id);
 create table tbl_b (id bigint, t numeric) distributed by (id);

 insert into tbl_a values (1), (2), (3);
 insert into tbl_b values (1, 50), (1, 100), (1, 150), (2, 200), (4, 400);

 select tbl_a.id, median (t) from tbl_a, tbl_b
 where tbl_a.id = tbl_b.id and tbl_a.id = 1::int4
 group by tbl_a.id ;

 --
 -- Test that the planner doesn't try to use a hash agg for a type that is not hashable.
 --

 -- First create a type to test with.
 CREATE TYPE nohash_int;
 CREATE FUNCTION nohash_int_in(cstring) RETURNS nohash_int IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4in';
 CREATE FUNCTION nohash_int_out(nohash_int) RETURNS cstring IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4out';
 CREATE TYPE nohash_int (INPUT = nohash_int_in, OUTPUT=nohash_int_out, INTERNALLENGTH=4, PASSEDBYVALUE, ALIGNMENT=int4);


 -- Create comparison operators and opclass.
 CREATE FUNCTION nohash_int_cmp(nohash_int, nohash_int) RETURNS integer AS 'btint4cmp' LANGUAGE 'internal' IMMUTABLE STRICT;
 CREATE FUNCTION nohash_int_lt(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4lt';
 CREATE FUNCTION nohash_int_le(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4le';
 CREATE FUNCTION nohash_int_eq(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4eq';
 CREATE FUNCTION nohash_int_ge(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4ge';
 CREATE FUNCTION nohash_int_gt(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4gt';

 CREATE OPERATOR <  (PROCEDURE=nohash_int_lt, LEFTARG=nohash_int, RIGHTARG=nohash_int);
 CREATE OPERATOR <= (PROCEDURE=nohash_int_le, LEFTARG=nohash_int, RIGHTARG=nohash_int);
 CREATE OPERATOR =  (PROCEDURE=nohash_int_eq, LEFTARG=nohash_int, RIGHTARG=nohash_int);
 CREATE OPERATOR >= (PROCEDURE=nohash_int_ge, LEFTARG=nohash_int, RIGHTARG=nohash_int);
 CREATE OPERATOR >  (PROCEDURE=nohash_int_gt, LEFTARG=nohash_int, RIGHTARG=nohash_int);

 CREATE OPERATOR CLASS nohash_int_bt_ops DEFAULT
 	FOR TYPE nohash_int USING btree AS
 	OPERATOR 1  <,
 	OPERATOR 2  <=,
 	OPERATOR 3  =,
 	OPERATOR 4  >=,
 	OPERATOR 5  >,
 	FUNCTION 1  nohash_int_cmp(nohash_int, nohash_int);


 -- A test table
 create table hashagg_test2(normal_int int4, nohash_int nohash_int) distributed randomly;
 insert into hashagg_test2 select g%10, (g%10)::text::nohash_int from generate_series(1, 10000) g;

 -- Prefer hash aggs
 set enable_sort=off;

 -- These should both work. The first is expected to use a hash agg. The second will
 -- use a Sort + Group, because nohash_int type is not hashable.
 select normal_int from hashagg_test2 group by normal_int;
 select nohash_int from hashagg_test2 group by nohash_int;

 reset enable_sort;

 -- We had a bug in the following combine functions where if the combine function
 -- returned a NULL, it didn't set fcinfo->isnull = true. This led to a segfault
 -- when we would spill in the final stage of a two-stage agg inside the serial
 -- function.
 CREATE TABLE test_combinefn_null (a int8, b int, c char(32000));
 INSERT INTO test_combinefn_null SELECT i, (i | 15), i::text FROM generate_series(1, 1024) i;
 ANALYZE test_combinefn_null;
 SET statement_mem='2MB';
 set enable_sort=off;

 -- Test int8_avg_combine()
 SELECT $$
 SELECT
 sum(a) FILTER (WHERE false)
 FROM test_combinefn_null
 GROUP BY b
 HAVING max(c) = '31'
 $$ AS qry \gset
 EXPLAIN (COSTS OFF, VERBOSE) :qry;
 :qry;

 -- Test numeric_poly_combine()
 SELECT $$
 SELECT
 var_pop(a::int) FILTER (WHERE false)
 FROM test_combinefn_null
 GROUP BY b
 HAVING max(c) = '31'
 $$ AS qry \gset
 EXPLAIN (COSTS OFF, VERBOSE) :qry;
 :qry;

 -- Test numeric_avg_combine()
 SELECT $$
 SELECT
 sum(a::numeric) FILTER (WHERE false)
 FROM test_combinefn_null
 GROUP BY b
 HAVING max(c) = '31'
 $$ AS qry \gset
 EXPLAIN (COSTS OFF, VERBOSE) :qry;
 :qry;

 -- Test numeric_combine()
 SELECT $$
 SELECT
 var_pop(a::numeric) FILTER (WHERE false)
 FROM test_combinefn_null
 GROUP BY b
 HAVING max(c) = '31'
 $$ AS qry \gset
 EXPLAIN (COSTS OFF, VERBOSE) :qry;
 :qry;

 drop schema if exists tinc_base_types cascade;

 create schema tinc_base_types;
 set search_path=tinc_base_types;


 CREATE FUNCTION int42_in(cstring)
    RETURNS int42
    AS 'int4in'
    LANGUAGE internal IMMUTABLE STRICT;
 CREATE FUNCTION int42_out(int42)
    RETURNS cstring
    AS 'int4out'
    LANGUAGE internal IMMUTABLE STRICT;
 CREATE TYPE int42 (
    internallength = 4,
    input = int42_in,
    output = int42_out,
    alignment = int4,
    default = 42,
    passedbyvalue
 );

 CREATE TABLE aTABLE(k int, a int42);
 INSERT INTO aTABLE VALUES(1, '21');
 INSERT INTO aTABLE VALUES(2, '22');
 INSERT INTO aTABLE VALUES(3, '23');
 INSERT INTO aTABLE VALUES(4, '24');
 SELECT * FROM aTABLE;

 CREATE OR REPLACE FUNCTION my_lt(int42, int42)
 RETURNS boolean AS
 'int4lt'
 language internal;

 CREATE OR REPLACE FUNCTION my_lteq(int42, int42)
 RETURNS boolean AS
 'int4le'
 language internal;

 CREATE OR REPLACE FUNCTION my_gt(int42, int42)
 RETURNS boolean AS
 'int4gt'
 language internal;

 CREATE OR REPLACE FUNCTION my_gteq(int42, int42)
 RETURNS boolean AS
 'int4ge'
 language internal;

 CREATE OR REPLACE FUNCTION my_eq(int42, int42)
 RETURNS boolean AS
 'int4eq'
 language internal;

 CREATE OPERATOR < (
   leftarg = int42,
   rightarg = int42,
   procedure = my_lt,
   hashes
 );

 CREATE OPERATOR <= (
   leftarg = int42,
   rightarg = int42,
   procedure = my_lteq,
   hashes
 );

 CREATE OPERATOR = (
   leftarg = int42,
   rightarg = int42,
   procedure = my_eq,
   hashes
 );

 CREATE OPERATOR > (
   leftarg = int42,
   rightarg = int42,
   procedure = my_gt,
   hashes
 );

 CREATE OPERATOR >= (
   leftarg = int42,
   rightarg = int42,
   procedure = my_gteq,
   hashes
 );

 CREATE OR REPLACE FUNCTION my_comp_func(int42, int42)
 RETURNS int AS
 $$
 BEGIN
 IF $1 < $2 THEN
   return -1;
 ELSIF $1 = $2 THEN
   return 0;
 ELSE
   return 1;
 END IF;
 END;
 $$ LANGUAGE PLPGSQL;

 CREATE OPERATOR CLASS my_operator_class_comp_type
 DEFAULT
 FOR TYPE int42 USING btree
 AS
 OPERATOR 1 <,
 OPERATOR 2 <=,
 OPERATOR 3 =,
 OPERATOR 4 >=,
 OPERATOR 5 >,
 FUNCTION 1 my_comp_func(int42, int42);

 CREATE OPERATOR CLASS my_operator_class_comp_type_hash_lt
 DEFAULT
 FOR TYPE int42 USING hash
 AS
 OPERATOR 1 <,
 FUNCTION 1 my_comp_func(int42, int42);

 CREATE OPERATOR CLASS my_operator_class_comp_type_hash_eq
 FOR TYPE int42 USING hash
 AS
 OPERATOR 1 =,
 FUNCTION 1 my_comp_func(int42, int42);

 select * from atable group by a,k;
 -- Before fix: This would fail at runtime with:
 --   ERROR: could not find hash function for hash operator XXXXX (execGrouping.c:118)
 -- After fix: ORCA should detect missing hash function at planning time
 --   and either use GroupAgg or report a clear planning error
 SET optimizer_enable_groupagg=off;
 select * from atable group by a,k;

 drop schema if exists tinc_base_types cascade;
	-- tests MPP-2614
	-- two segments no mirrors
	drop table if exists hashagg_test;
	create table hashagg_test (id1 int4, id2 int4, day date, grp text, v int4);
	create index hashagg_test_idx on hashagg_test (id1,id2,day,grp);
	insert into hashagg_test values (1,1,'1/1/2006','there',1);
	insert into hashagg_test values (1,1,'1/2/2006','there',2);
	insert into hashagg_test values (1,1,'1/3/2006','there',3);
	insert into hashagg_test values (1,1,'1/1/2006','hi',2);
	insert into hashagg_test values (1,1,'1/2/2006','hi',3);
	insert into hashagg_test values (1,1,'1/3/2006','hi',4);

	-- this will get the wrong answer (right number of rows, wrong aggregates)
	set enable_seqscan=off;
	select grp,sum(v) from hashagg_test where id1 = 1 and id2 = 1 and day between '1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;

	create index hashagg_test_idx_bm on hashagg_test using bitmap (id1,id2,day,grp);
	set enable_indexscan=off; -- turn off b-tree index
	select grp,sum(v) from hashagg_test where id1 = 1 and id2 = 1 and day between '1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;

	-- correct answer
	set enable_seqscan=on;
	select grp,sum(v) from hashagg_test where id1 = 1 and id2 = 1 and day between '1/1/2006' and '1/31/2006' group by grp order by sum(v) desc;


	-- Test a window-aggregate (median) with a join where the join column
	-- is further constrained by a constant. And the constant is of different
	-- type (int4, while the column is bigint). We had a bug at one point
	-- where this threw an error.

	create table tbl_a (id bigint) distributed by (id);
	create table tbl_b (id bigint, t numeric) distributed by (id);

	insert into tbl_a values (1), (2), (3);
	insert into tbl_b values (1, 50), (1, 100), (1, 150), (2, 200), (4, 400);

	select tbl_a.id, median (t) from tbl_a, tbl_b
	where tbl_a.id = tbl_b.id and tbl_a.id = 1::int4
	group by tbl_a.id ;

	--
	-- Test that the planner doesn't try to use a hash agg for a type that is not hashable.
	--

	-- First create a type to test with.
	CREATE TYPE nohash_int;
	CREATE FUNCTION nohash_int_in(cstring) RETURNS nohash_int IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4in';
	CREATE FUNCTION nohash_int_out(nohash_int) RETURNS cstring IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4out';
	CREATE TYPE nohash_int (INPUT = nohash_int_in, OUTPUT=nohash_int_out, INTERNALLENGTH=4, PASSEDBYVALUE, ALIGNMENT=int4);


	-- Create comparison operators and opclass.
	CREATE FUNCTION nohash_int_cmp(nohash_int, nohash_int) RETURNS integer AS 'btint4cmp' LANGUAGE 'internal' IMMUTABLE STRICT;
	CREATE FUNCTION nohash_int_lt(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4lt';
	CREATE FUNCTION nohash_int_le(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4le';
	CREATE FUNCTION nohash_int_eq(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4eq';
	CREATE FUNCTION nohash_int_ge(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4ge';
	CREATE FUNCTION nohash_int_gt(nohash_int, nohash_int) RETURNS bool IMMUTABLE STRICT LANGUAGE INTERNAL AS 'int4gt';

	CREATE OPERATOR < (PROCEDURE=nohash_int_lt, LEFTARG=nohash_int, RIGHTARG=nohash_int);
	CREATE OPERATOR <= (PROCEDURE=nohash_int_le, LEFTARG=nohash_int, RIGHTARG=nohash_int);
	CREATE OPERATOR = (PROCEDURE=nohash_int_eq, LEFTARG=nohash_int, RIGHTARG=nohash_int);
	CREATE OPERATOR >= (PROCEDURE=nohash_int_ge, LEFTARG=nohash_int, RIGHTARG=nohash_int);
	CREATE OPERATOR > (PROCEDURE=nohash_int_gt, LEFTARG=nohash_int, RIGHTARG=nohash_int);

	CREATE OPERATOR CLASS nohash_int_bt_ops DEFAULT
	FOR TYPE nohash_int USING btree AS
	OPERATOR 1 <,
	OPERATOR 2 <=,
	OPERATOR 3 =,
	OPERATOR 4 >=,
	OPERATOR 5 >,
	FUNCTION 1 nohash_int_cmp(nohash_int, nohash_int);


	-- A test table
	create table hashagg_test2(normal_int int4, nohash_int nohash_int) distributed randomly;
	insert into hashagg_test2 select g%10, (g%10)::text::nohash_int from generate_series(1, 10000) g;

	-- Prefer hash aggs
	set enable_sort=off;

	-- These should both work. The first is expected to use a hash agg. The second will
	-- use a Sort + Group, because nohash_int type is not hashable.
	select normal_int from hashagg_test2 group by normal_int;
	select nohash_int from hashagg_test2 group by nohash_int;

	reset enable_sort;

	-- We had a bug in the following combine functions where if the combine function
	-- returned a NULL, it didn't set fcinfo->isnull = true. This led to a segfault
	-- when we would spill in the final stage of a two-stage agg inside the serial
	-- function.
	CREATE TABLE test_combinefn_null (a int8, b int, c char(32000));
	INSERT INTO test_combinefn_null SELECT i, (i \| 15), i::text FROM generate_series(1, 1024) i;
	ANALYZE test_combinefn_null;
	SET statement_mem='2MB';
	set enable_sort=off;

	-- Test int8_avg_combine()
	SELECT $$
	SELECT
	sum(a) FILTER (WHERE false)
	FROM test_combinefn_null
	GROUP BY b
	HAVING max(c) = '31'
	$$ AS qry \gset
	EXPLAIN (COSTS OFF, VERBOSE) :qry;
	:qry;

	-- Test numeric_poly_combine()
	SELECT $$
	SELECT
	var_pop(a::int) FILTER (WHERE false)
	FROM test_combinefn_null
	GROUP BY b
	HAVING max(c) = '31'
	$$ AS qry \gset
	EXPLAIN (COSTS OFF, VERBOSE) :qry;
	:qry;

	-- Test numeric_avg_combine()
	SELECT $$
	SELECT
	sum(a::numeric) FILTER (WHERE false)
	FROM test_combinefn_null
	GROUP BY b
	HAVING max(c) = '31'
	$$ AS qry \gset
	EXPLAIN (COSTS OFF, VERBOSE) :qry;
	:qry;

	-- Test numeric_combine()
	SELECT $$
	SELECT
	var_pop(a::numeric) FILTER (WHERE false)
	FROM test_combinefn_null
	GROUP BY b
	HAVING max(c) = '31'
	$$ AS qry \gset
	EXPLAIN (COSTS OFF, VERBOSE) :qry;
	:qry;

	drop schema if exists tinc_base_types cascade;

	create schema tinc_base_types;
	set search_path=tinc_base_types;


	CREATE FUNCTION int42_in(cstring)
	RETURNS int42
	AS 'int4in'
	LANGUAGE internal IMMUTABLE STRICT;
	CREATE FUNCTION int42_out(int42)
	RETURNS cstring
	AS 'int4out'
	LANGUAGE internal IMMUTABLE STRICT;
	CREATE TYPE int42 (
	internallength = 4,
	input = int42_in,
	output = int42_out,
	alignment = int4,
	default = 42,
	passedbyvalue
	);

	CREATE TABLE aTABLE(k int, a int42);
	INSERT INTO aTABLE VALUES(1, '21');
	INSERT INTO aTABLE VALUES(2, '22');
	INSERT INTO aTABLE VALUES(3, '23');
	INSERT INTO aTABLE VALUES(4, '24');
	SELECT * FROM aTABLE;

	CREATE OR REPLACE FUNCTION my_lt(int42, int42)
	RETURNS boolean AS
	'int4lt'
	language internal;

	CREATE OR REPLACE FUNCTION my_lteq(int42, int42)
	RETURNS boolean AS
	'int4le'
	language internal;

	CREATE OR REPLACE FUNCTION my_gt(int42, int42)
	RETURNS boolean AS
	'int4gt'
	language internal;

	CREATE OR REPLACE FUNCTION my_gteq(int42, int42)
	RETURNS boolean AS
	'int4ge'
	language internal;

	CREATE OR REPLACE FUNCTION my_eq(int42, int42)
	RETURNS boolean AS
	'int4eq'
	language internal;

	CREATE OPERATOR < (
	leftarg = int42,
	rightarg = int42,
	procedure = my_lt,
	hashes
	);

	CREATE OPERATOR <= (
	leftarg = int42,
	rightarg = int42,
	procedure = my_lteq,
	hashes
	);

	CREATE OPERATOR = (
	leftarg = int42,
	rightarg = int42,
	procedure = my_eq,
	hashes
	);

	CREATE OPERATOR > (
	leftarg = int42,
	rightarg = int42,
	procedure = my_gt,
	hashes
	);

	CREATE OPERATOR >= (
	leftarg = int42,
	rightarg = int42,
	procedure = my_gteq,
	hashes
	);

	CREATE OR REPLACE FUNCTION my_comp_func(int42, int42)
	RETURNS int AS
	$$
	BEGIN
	IF $1 < $2 THEN
	return -1;
	ELSIF $1 = $2 THEN
	return 0;
	ELSE
	return 1;
	END IF;
	END;
	$$ LANGUAGE PLPGSQL;

	CREATE OPERATOR CLASS my_operator_class_comp_type
	DEFAULT
	FOR TYPE int42 USING btree
	AS
	OPERATOR 1 <,
	OPERATOR 2 <=,
	OPERATOR 3 =,
	OPERATOR 4 >=,
	OPERATOR 5 >,
	FUNCTION 1 my_comp_func(int42, int42);

	CREATE OPERATOR CLASS my_operator_class_comp_type_hash_lt
	DEFAULT
	FOR TYPE int42 USING hash
	AS
	OPERATOR 1 <,
	FUNCTION 1 my_comp_func(int42, int42);

	CREATE OPERATOR CLASS my_operator_class_comp_type_hash_eq
	FOR TYPE int42 USING hash
	AS
	OPERATOR 1 =,
	FUNCTION 1 my_comp_func(int42, int42);

	select * from atable group by a,k;
	-- Before fix: This would fail at runtime with:
	-- ERROR: could not find hash function for hash operator XXXXX (execGrouping.c:118)
	-- After fix: ORCA should detect missing hash function at planning time
	-- and either use GroupAgg or report a clear planning error
	SET optimizer_enable_groupagg=off;
	select * from atable group by a,k;

	drop schema if exists tinc_base_types cascade;