src/test/regress/expected/bfv_dd_multicolumn.out

--
-- Direct Dispatch Test when optimizer is on
--
-- start_ignore
set optimizer_log=on;
set optimizer_force_multistage_agg=on;
-- end_ignore
set test_print_direct_dispatch_info=on; 
set gp_autostats_mode = 'None';
-- composite keys
create table dd_multicol_1(a int, b int) distributed by (a,b);
INFO:  Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
INFO:  Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
create table dd_multicol_2(a int, b int) distributed by (b,a);
INFO:  Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
INFO:  Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
insert into dd_multicol_1 select g, g%2 from generate_series(1, 100) g;
INFO:  (slice 0) Dispatch command to ALL contents: 0 1 2
INFO:  (slice 1) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
INFO:  Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
insert into dd_multicol_1 values(null, null);
INFO:  (slice 0) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
insert into dd_multicol_1 values(1, null);
INFO:  (slice 0) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
insert into dd_multicol_1 values(null, 1);
INFO:  (slice 0) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
analyze dd_multicol_1;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
insert into dd_multicol_2 select g, g%2 from generate_series(1, 100) g;
INFO:  (slice 0) Dispatch command to ALL contents: 0 1 2
INFO:  (slice 1) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
INFO:  Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
insert into dd_multicol_2 values(null, null);
INFO:  (slice 0) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
insert into dd_multicol_2 values(1, null);
INFO:  (slice 0) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
insert into dd_multicol_2 values(null, 1);
INFO:  (slice 0) Dispatch command to SINGLE content
INFO:  Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
-- composite distr key
select * from dd_multicol_1 where a in (1,3) and b in (1,2);
INFO:  (slice 1) Dispatch command to PARTIAL contents: 1 2
 a | b 
---+---
 1 | 1
 3 | 1
(2 rows)

select * from dd_multicol_1 where a = 1 and b in (1,2);
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
 1 | 1
(1 row)

select * from dd_multicol_1 where (a=1 and b=1) or (a=2 and b=2);
INFO:  (slice 1) Dispatch command to PARTIAL contents: 1 0
 a | b 
---+---
 1 | 1
(1 row)

select * from dd_multicol_1 where (a=1 or a=3) and (b=1 or b=2);
INFO:  (slice 1) Dispatch command to PARTIAL contents: 1 2
 a | b 
---+---
 1 | 1
 3 | 1
(2 rows)

select * from dd_multicol_1 where a is null or a=1;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
 a | b 
---+---
 1 | 1
 1 |  
   |  
   | 1
(4 rows)

select * from dd_multicol_1 where (a is null or a=1) and b=2;
INFO:  (slice 1) Dispatch command to PARTIAL contents: 0 1
 a | b 
---+---
(0 rows)

select * from dd_multicol_1 where (a,b) in ((1,2),(3,4)); 
INFO:  (slice 1) Dispatch command to PARTIAL contents: 1 2
 a | b 
---+---
(0 rows)

-- composite distr key: projections
select b from dd_multicol_1 where (a=1 or a=3) and (b=1 or b=2);
INFO:  (slice 1) Dispatch command to PARTIAL contents: 1 2
 b 
---
 1
 1
(2 rows)

select count(*) from dd_multicol_1;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
 count 
-------
   103
(1 row)

select count(*) from dd_multicol_2;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
 count 
-------
   103
(1 row)

-- simple predicates
select * from dd_multicol_1 where a=1 and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
 1 | 1
(1 row)

select * from dd_multicol_1 where a=1 and b=2;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
(0 rows)

select * from dd_multicol_1 where b=1 and a=2;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
(0 rows)

select * from dd_multicol_1 where a is null and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
   | 1
(1 row)

select * from dd_multicol_1 where a is null and b is null;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
   |  
(1 row)

select * from dd_multicol_2 where a=1 and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
 1 | 1
(1 row)

select * from dd_multicol_2 where a=1 and b=2;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
(0 rows)

select * from dd_multicol_2 where b=1 and a=2;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
(0 rows)

select * from dd_multicol_2 where a is null and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
   | 1
(1 row)

select * from dd_multicol_2 where a is null and b is null;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | b 
---+---
   |  
(1 row)

-- projections
select b from dd_multicol_1 where a=1 and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 b 
---
 1
(1 row)

 
select a+b from dd_multicol_1 where a=1 and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 ?column? 
----------
        2
(1 row)

select 'one' from dd_multicol_1 where a=1 and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 ?column? 
----------
 one
(1 row)

select a, 'one' from dd_multicol_1 where a=1 and b=1;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | ?column? 
---+----------
 1 | one
(1 row)

-- group by and sort
select a, count(*) from dd_multicol_1 where a=1 and b=1 group by a,b;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | count 
---+-------
 1 |     1
(1 row)

select a, count(*) from dd_multicol_1 where a=1 and b=1 group by a,b  order by a,b;
INFO:  (slice 1) Dispatch command to SINGLE content
 a | count 
---+-------
 1 |     1
(1 row)

-- incomplete specification
select * from dd_multicol_1 where a=1;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
 a | b 
---+---
 1 | 1
 1 |  
(2 rows)

select * from dd_multicol_1 where a>80 and b=1;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
 a  | b 
----+---
 83 | 1
 93 | 1
 97 | 1
 81 | 1
 87 | 1
 91 | 1
 95 | 1
 85 | 1
 89 | 1
 99 | 1
(10 rows)

select * from dd_multicol_1 where a<=5 and b>0;
INFO:  (slice 1) Dispatch command to ALL contents: 0 1 2
 a | b 
---+---
 1 | 1
 5 | 1
 3 | 1
(3 rows)

reset optimizer_force_multistage_agg;