src/test/singlenode_regress/input/singlenode_compatibility_cbdb.source

--
-- Test compatibility in CBDB singlenode mode.
--
-- In singlenode mode, certain clauses such as scatter by and
-- distributed by become meaningless, but for the sake of
-- compatibility, we retain the grammar and treat them as ignored.
-- This file aims to verify the correct functionality of all these
-- SQL statements.

-- test ignorance of `distributed by`
create table l (i int, j int) distributed by (i);
create table r (i int, j int) distributed by (j);
insert into l select i % 7, i % 13 from generate_series(1, 1000) i;
insert into r select i % 13, i % 7 from generate_series(1, 1000) i;

explain select count(*) from l;
select count(*) from l;
explain select count(*) from l, r where l.i = r.i;
select count(*) from l, r where l.i = r.i;

alter table l set distributed by (j);

create table ao1(x int, y int) with(appendonly=true) distributed by (x);
create table aocs1(x int, y int) with(appendonly=true, orientation=column) distributed by (x);

insert into ao1 select i, i from generate_series(1, 1200000) g(i);
analyze ao1;
insert into aocs1 select i, i from generate_series(1, 1200000) g(i);
analyze aocs1;

explain(costs off) select count(*) from ao1;
select count(*) from ao1;

explain(costs off) select count(*) from aocs1;
select count(*) from aocs1;

-- test ignorance of `scatter by`
CREATE TABLE example(a int, b text) DISTRIBUTED by (a);
COPY example FROM stdin;
1	 value1.1/4
2	 value2.1/3
3	 value3.1/2
4	 value4.1/1
1	 value1.2/4
2	 value2.2/3
3	 value3.2/2
1	 value1.3/4
2	 value2.3/3
1	 value1.4/4
\.

CREATE FUNCTION multiset_func(a anytable) RETURNS SETOF example
    AS '@abs_builddir@/regress@DLSUFFIX@', 'multiset_example' LANGUAGE C READS SQL DATA;

explain SELECT * FROM multiset_func( TABLE( SELECT * FROM example ORDER BY a, b SCATTER by a) )  order by a, b;
SELECT * FROM multiset_func( TABLE( SELECT * FROM example ORDER BY a, b SCATTER by a) )  order by a, b;

drop table l;
drop table r;
drop table ao1;
drop table aocs1;
drop function multiset_func;
drop table example;