src/test/regress/expected/bfv_legacy.out

--
-- SETUP: Helper functions for query plan verification
--
-- The helper functions are written in python.
create or replace language plpython3u;
NOTICE:  extension "plpython3u" already exists, skipping
-- While we're at it, test that CREATE OR REPLACE LANGUAGE works when
-- the language exists already (we had a little bug at one point, where
-- the "OR REPLACE" was not dispatched to segments, and this failed)
create or replace language plpython3u;
NOTICE:  extension "plpython3u" already exists, skipping
--start_ignore
drop schema if exists bfv_legacy cascade;
NOTICE:  schema "bfv_legacy" does not exist, skipping
--end_ignore
create schema bfv_legacy;
set search_path=bfv_legacy;
create or replace function nonzero_width(explain_query text) returns bool as
$$
rv = plpy.execute(explain_query)
#search_text = 'Index Scan'
first_line = rv[0]['QUERY PLAN']
row_width = int(first_line[first_line.find('width=')+6:first_line.rfind(')')])
return row_width > -1
$$
language plpython3u;
create or replace function count_operator(explain_query text, op_name text) returns int as
$$
rv = plpy.execute(explain_query)
result = 0
for i in range(len(rv)):
    cur_line = rv[i]['QUERY PLAN']
    if op_name.lower() in cur_line.lower():
        result = result+1
return result
$$
language plpython3u;
--
-- aggregate width should be non-zero
--
-- SETUP
CREATE TABLE bfv_legacy_t1(c1 int, c2 varchar);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO bfv_legacy_t1 select generate_series(1,1000), 'aaa';
INSERT INTO bfv_legacy_t1 select generate_series(1001,2000), 'bbb';
--aggregate width should be non-zero
select nonzero_width('EXPLAIN SELECT count(*) from (select * from bfv_legacy_t1) as a;');
 nonzero_width 
---------------
 t
(1 row)

--width should be non-zero
select nonzero_width('EXPLAIN SELECT * from (select * from bfv_legacy_t1) as a;');
 nonzero_width 
---------------
 t
(1 row)

select nonzero_width('EXPLAIN SELECT max(a.c1), min(a.c1), avg(a.c1) from (select * from bfv_legacy_t1) as a;');
 nonzero_width 
---------------
 t
(1 row)

select nonzero_width('explain select a.c1 * b.c1, a.c1, b.c1 from bfv_legacy_t1 a, bfv_legacy_t1 b;');
 nonzero_width 
---------------
 t
(1 row)

--
--
--
create table bfv_s (
    c_t       char(2),
    c_o       int4,
    c_p       text,
    c_v       float8
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c_t' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into bfv_s (c_o, c_p, c_v) values (0, 1, 1234.56);
create table bfv_int4_tbl (f1 int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into bfv_int4_tbl values(123456), (-2147483647), (0), (-123456), (2147483647);
update bfv_s set c_v = 11
from bfv_int4_tbl a join bfv_int4_tbl b on (a.f1 = (select f1 from bfv_int4_tbl c where c.f1=b.f1));
update bfv_s set c_v = 11
from bfv_int4_tbl a join bfv_int4_tbl b on (a.f1 = (select f1 from bfv_int4_tbl c where c.f1=b.f1));
--
--
--
-- start_matchsubs
-- m/NOTICE:  Using default RANDOM distribution since no distribution was specified./
-- s/^/GP_IGNORE: /
-- m/HINT:  Consider including the 'DISTRIBUTED BY' clause to determine the distribution of rows./
-- s/^/GP_IGNORE: /
-- end_matchsubs
create table bfv_legacy_A (
col_with_default numeric DEFAULT 0,
col_with_default_drop_default character varying(30) DEFAULT 'test1',
col_with_constraint numeric UNIQUE
) distributed BY (col_with_constraint);
create table bfv_legacy_B as select * from bfv_legacy_A;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'col_with_constraint' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
select localoid::regclass, distkey from gp_distribution_policy p left join pg_class c on (p.localoid = c.oid) where c.relname in ('bfv_legacy_a', 'bfv_legacy_b') order by 1,2;
   localoid   | distkey 
--------------+---------
 bfv_legacy_a | 3
 bfv_legacy_b | 3
(2 rows)