-- -- Set up -- create schema bfv_joins; set search_path='bfv_joins'; create table x (a int, b int, c int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 x values (generate_series(1,10), generate_series(1,10), generate_series(1,10)); create table y (a int, b int, c int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 y (select * from x); CREATE TABLE t1 (a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. CREATE TABLE t2 (a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 t1 VALUES (1,1),(2,1),(3,NULL); INSERT INTO t2 VALUES (2,3); CREATE FUNCTION func_x(x int) RETURNS int AS $$ BEGIN RETURN $1 +1; END $$ LANGUAGE plpgsql; create table z(x int) distributed by (x); CREATE TABLE bfv_joins_foo AS SELECT i as a, i+1 as b from generate_series(1,10)i; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. CREATE TABLE bfv_joins_bar AS SELECT i as c, i+1 as d from generate_series(1,10)i; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. CREATE TABLE t AS SELECT bfv_joins_foo.a,bfv_joins_foo.b,bfv_joins_bar.d FROM bfv_joins_foo,bfv_joins_bar WHERE bfv_joins_foo.a = bfv_joins_bar.d; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. CREATE FUNCTION my_equality(a int, b int) RETURNS BOOL AS $$ SELECT $1 < $2 $$ LANGUAGE SQL; create table x_non_part (a int, b int, c int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 x_non_part select i%3, i, i from generate_series(1,10) i; create table x_part (e int, f int, g int) partition by range(e) (start(1) end(5) every(1), default partition extra); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'e' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "x_part_1_prt_extra" for table "x_part" NOTICE: CREATE TABLE will create partition "x_part_1_prt_2" for table "x_part" NOTICE: CREATE TABLE will create partition "x_part_1_prt_3" for table "x_part" NOTICE: CREATE TABLE will create partition "x_part_1_prt_4" for table "x_part" NOTICE: CREATE TABLE will create partition "x_part_1_prt_5" for table "x_part" insert into x_part select generate_series(1,10), generate_series(1,10) * 3, generate_series(1,10)%6; analyze x_non_part; analyze x_part; -- -- Test with more null-filtering conditions for LOJ transformation in Orca -- SELECT * from x left join y on True where y.a > 0; a | b | c | a | b | c ----+----+----+----+----+---- 1 | 1 | 1 | 8 | 8 | 8 2 | 2 | 2 | 8 | 8 | 8 1 | 1 | 1 | 9 | 9 | 9 2 | 2 | 2 | 9 | 9 | 9 1 | 1 | 1 | 10 | 10 | 10 2 | 2 | 2 | 10 | 10 | 10 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 1 | 1 | 1 1 | 1 | 1 | 2 | 2 | 2 2 | 2 | 2 | 2 | 2 | 2 1 | 1 | 1 | 3 | 3 | 3 2 | 2 | 2 | 3 | 3 | 3 1 | 1 | 1 | 4 | 4 | 4 2 | 2 | 2 | 4 | 4 | 4 1 | 1 | 1 | 5 | 5 | 5 2 | 2 | 2 | 5 | 5 | 5 1 | 1 | 1 | 6 | 6 | 6 2 | 2 | 2 | 6 | 6 | 6 1 | 1 | 1 | 7 | 7 | 7 2 | 2 | 2 | 7 | 7 | 7 3 | 3 | 3 | 1 | 1 | 1 4 | 4 | 4 | 1 | 1 | 1 5 | 5 | 5 | 1 | 1 | 1 6 | 6 | 6 | 1 | 1 | 1 7 | 7 | 7 | 1 | 1 | 1 3 | 3 | 3 | 2 | 2 | 2 4 | 4 | 4 | 2 | 2 | 2 5 | 5 | 5 | 2 | 2 | 2 6 | 6 | 6 | 2 | 2 | 2 7 | 7 | 7 | 2 | 2 | 2 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 3 | 3 | 3 5 | 5 | 5 | 3 | 3 | 3 6 | 6 | 6 | 3 | 3 | 3 7 | 7 | 7 | 3 | 3 | 3 3 | 3 | 3 | 4 | 4 | 4 4 | 4 | 4 | 4 | 4 | 4 5 | 5 | 5 | 4 | 4 | 4 6 | 6 | 6 | 4 | 4 | 4 7 | 7 | 7 | 4 | 4 | 4 3 | 3 | 3 | 5 | 5 | 5 4 | 4 | 4 | 5 | 5 | 5 5 | 5 | 5 | 5 | 5 | 5 6 | 6 | 6 | 5 | 5 | 5 7 | 7 | 7 | 5 | 5 | 5 3 | 3 | 3 | 6 | 6 | 6 4 | 4 | 4 | 6 | 6 | 6 5 | 5 | 5 | 6 | 6 | 6 6 | 6 | 6 | 6 | 6 | 6 7 | 7 | 7 | 6 | 6 | 6 3 | 3 | 3 | 7 | 7 | 7 4 | 4 | 4 | 7 | 7 | 7 5 | 5 | 5 | 7 | 7 | 7 6 | 6 | 6 | 7 | 7 | 7 7 | 7 | 7 | 7 | 7 | 7 3 | 3 | 3 | 8 | 8 | 8 4 | 4 | 4 | 8 | 8 | 8 5 | 5 | 5 | 8 | 8 | 8 6 | 6 | 6 | 8 | 8 | 8 7 | 7 | 7 | 8 | 8 | 8 3 | 3 | 3 | 9 | 9 | 9 4 | 4 | 4 | 9 | 9 | 9 5 | 5 | 5 | 9 | 9 | 9 6 | 6 | 6 | 9 | 9 | 9 7 | 7 | 7 | 9 | 9 | 9 3 | 3 | 3 | 10 | 10 | 10 4 | 4 | 4 | 10 | 10 | 10 5 | 5 | 5 | 10 | 10 | 10 6 | 6 | 6 | 10 | 10 | 10 7 | 7 | 7 | 10 | 10 | 10 8 | 8 | 8 | 1 | 1 | 1 9 | 9 | 9 | 1 | 1 | 1 10 | 10 | 10 | 1 | 1 | 1 8 | 8 | 8 | 2 | 2 | 2 9 | 9 | 9 | 2 | 2 | 2 10 | 10 | 10 | 2 | 2 | 2 8 | 8 | 8 | 3 | 3 | 3 9 | 9 | 9 | 3 | 3 | 3 10 | 10 | 10 | 3 | 3 | 3 8 | 8 | 8 | 4 | 4 | 4 9 | 9 | 9 | 4 | 4 | 4 10 | 10 | 10 | 4 | 4 | 4 8 | 8 | 8 | 5 | 5 | 5 9 | 9 | 9 | 5 | 5 | 5 10 | 10 | 10 | 5 | 5 | 5 8 | 8 | 8 | 6 | 6 | 6 9 | 9 | 9 | 6 | 6 | 6 10 | 10 | 10 | 6 | 6 | 6 8 | 8 | 8 | 7 | 7 | 7 9 | 9 | 9 | 7 | 7 | 7 10 | 10 | 10 | 7 | 7 | 7 8 | 8 | 8 | 8 | 8 | 8 9 | 9 | 9 | 8 | 8 | 8 10 | 10 | 10 | 8 | 8 | 8 8 | 8 | 8 | 9 | 9 | 9 9 | 9 | 9 | 9 | 9 | 9 10 | 10 | 10 | 9 | 9 | 9 8 | 8 | 8 | 10 | 10 | 10 9 | 9 | 9 | 10 | 10 | 10 10 | 10 | 10 | 10 | 10 | 10 (100 rows) SELECT * from x left join y on True where y.a > 0 and y.b > 0; a | b | c | a | b | c ----+----+----+----+----+---- 8 | 8 | 8 | 8 | 8 | 8 9 | 9 | 9 | 8 | 8 | 8 10 | 10 | 10 | 8 | 8 | 8 8 | 8 | 8 | 9 | 9 | 9 9 | 9 | 9 | 9 | 9 | 9 10 | 10 | 10 | 9 | 9 | 9 8 | 8 | 8 | 10 | 10 | 10 9 | 9 | 9 | 10 | 10 | 10 10 | 10 | 10 | 10 | 10 | 10 8 | 8 | 8 | 1 | 1 | 1 9 | 9 | 9 | 1 | 1 | 1 10 | 10 | 10 | 1 | 1 | 1 8 | 8 | 8 | 2 | 2 | 2 9 | 9 | 9 | 2 | 2 | 2 10 | 10 | 10 | 2 | 2 | 2 8 | 8 | 8 | 3 | 3 | 3 9 | 9 | 9 | 3 | 3 | 3 10 | 10 | 10 | 3 | 3 | 3 8 | 8 | 8 | 4 | 4 | 4 9 | 9 | 9 | 4 | 4 | 4 10 | 10 | 10 | 4 | 4 | 4 8 | 8 | 8 | 5 | 5 | 5 9 | 9 | 9 | 5 | 5 | 5 10 | 10 | 10 | 5 | 5 | 5 8 | 8 | 8 | 6 | 6 | 6 9 | 9 | 9 | 6 | 6 | 6 10 | 10 | 10 | 6 | 6 | 6 8 | 8 | 8 | 7 | 7 | 7 9 | 9 | 9 | 7 | 7 | 7 10 | 10 | 10 | 7 | 7 | 7 1 | 1 | 1 | 8 | 8 | 8 2 | 2 | 2 | 8 | 8 | 8 1 | 1 | 1 | 9 | 9 | 9 2 | 2 | 2 | 9 | 9 | 9 1 | 1 | 1 | 10 | 10 | 10 2 | 2 | 2 | 10 | 10 | 10 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 1 | 1 | 1 1 | 1 | 1 | 2 | 2 | 2 2 | 2 | 2 | 2 | 2 | 2 1 | 1 | 1 | 3 | 3 | 3 2 | 2 | 2 | 3 | 3 | 3 1 | 1 | 1 | 4 | 4 | 4 2 | 2 | 2 | 4 | 4 | 4 1 | 1 | 1 | 5 | 5 | 5 2 | 2 | 2 | 5 | 5 | 5 1 | 1 | 1 | 6 | 6 | 6 2 | 2 | 2 | 6 | 6 | 6 1 | 1 | 1 | 7 | 7 | 7 2 | 2 | 2 | 7 | 7 | 7 3 | 3 | 3 | 8 | 8 | 8 4 | 4 | 4 | 8 | 8 | 8 5 | 5 | 5 | 8 | 8 | 8 6 | 6 | 6 | 8 | 8 | 8 7 | 7 | 7 | 8 | 8 | 8 3 | 3 | 3 | 9 | 9 | 9 4 | 4 | 4 | 9 | 9 | 9 5 | 5 | 5 | 9 | 9 | 9 6 | 6 | 6 | 9 | 9 | 9 7 | 7 | 7 | 9 | 9 | 9 3 | 3 | 3 | 10 | 10 | 10 4 | 4 | 4 | 10 | 10 | 10 5 | 5 | 5 | 10 | 10 | 10 6 | 6 | 6 | 10 | 10 | 10 7 | 7 | 7 | 10 | 10 | 10 3 | 3 | 3 | 1 | 1 | 1 4 | 4 | 4 | 1 | 1 | 1 5 | 5 | 5 | 1 | 1 | 1 6 | 6 | 6 | 1 | 1 | 1 7 | 7 | 7 | 1 | 1 | 1 3 | 3 | 3 | 2 | 2 | 2 4 | 4 | 4 | 2 | 2 | 2 5 | 5 | 5 | 2 | 2 | 2 6 | 6 | 6 | 2 | 2 | 2 7 | 7 | 7 | 2 | 2 | 2 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 3 | 3 | 3 5 | 5 | 5 | 3 | 3 | 3 6 | 6 | 6 | 3 | 3 | 3 7 | 7 | 7 | 3 | 3 | 3 3 | 3 | 3 | 4 | 4 | 4 4 | 4 | 4 | 4 | 4 | 4 5 | 5 | 5 | 4 | 4 | 4 6 | 6 | 6 | 4 | 4 | 4 7 | 7 | 7 | 4 | 4 | 4 3 | 3 | 3 | 5 | 5 | 5 4 | 4 | 4 | 5 | 5 | 5 5 | 5 | 5 | 5 | 5 | 5 6 | 6 | 6 | 5 | 5 | 5 7 | 7 | 7 | 5 | 5 | 5 3 | 3 | 3 | 6 | 6 | 6 4 | 4 | 4 | 6 | 6 | 6 5 | 5 | 5 | 6 | 6 | 6 6 | 6 | 6 | 6 | 6 | 6 7 | 7 | 7 | 6 | 6 | 6 3 | 3 | 3 | 7 | 7 | 7 4 | 4 | 4 | 7 | 7 | 7 5 | 5 | 5 | 7 | 7 | 7 6 | 6 | 6 | 7 | 7 | 7 7 | 7 | 7 | 7 | 7 | 7 (100 rows) SELECT * from x left join y on True where y.a in (1,2,3); a | b | c | a | b | c ----+----+----+---+---+--- 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 1 | 1 | 1 1 | 1 | 1 | 2 | 2 | 2 2 | 2 | 2 | 2 | 2 | 2 1 | 1 | 1 | 3 | 3 | 3 2 | 2 | 2 | 3 | 3 | 3 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 3 | 3 | 3 5 | 5 | 5 | 3 | 3 | 3 6 | 6 | 6 | 3 | 3 | 3 7 | 7 | 7 | 3 | 3 | 3 3 | 3 | 3 | 1 | 1 | 1 4 | 4 | 4 | 1 | 1 | 1 5 | 5 | 5 | 1 | 1 | 1 6 | 6 | 6 | 1 | 1 | 1 7 | 7 | 7 | 1 | 1 | 1 3 | 3 | 3 | 2 | 2 | 2 4 | 4 | 4 | 2 | 2 | 2 5 | 5 | 5 | 2 | 2 | 2 6 | 6 | 6 | 2 | 2 | 2 7 | 7 | 7 | 2 | 2 | 2 8 | 8 | 8 | 3 | 3 | 3 9 | 9 | 9 | 3 | 3 | 3 10 | 10 | 10 | 3 | 3 | 3 8 | 8 | 8 | 1 | 1 | 1 9 | 9 | 9 | 1 | 1 | 1 10 | 10 | 10 | 1 | 1 | 1 8 | 8 | 8 | 2 | 2 | 2 9 | 9 | 9 | 2 | 2 | 2 10 | 10 | 10 | 2 | 2 | 2 (30 rows) SELECT * from x left join y on True where y.a = y.b ; a | b | c | a | b | c ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 1 | 1 | 1 1 | 1 | 1 | 2 | 2 | 2 2 | 2 | 2 | 2 | 2 | 2 1 | 1 | 1 | 3 | 3 | 3 2 | 2 | 2 | 3 | 3 | 3 1 | 1 | 1 | 4 | 4 | 4 2 | 2 | 2 | 4 | 4 | 4 1 | 1 | 1 | 5 | 5 | 5 2 | 2 | 2 | 5 | 5 | 5 1 | 1 | 1 | 6 | 6 | 6 2 | 2 | 2 | 6 | 6 | 6 1 | 1 | 1 | 7 | 7 | 7 2 | 2 | 2 | 7 | 7 | 7 1 | 1 | 1 | 8 | 8 | 8 2 | 2 | 2 | 8 | 8 | 8 1 | 1 | 1 | 9 | 9 | 9 2 | 2 | 2 | 9 | 9 | 9 1 | 1 | 1 | 10 | 10 | 10 2 | 2 | 2 | 10 | 10 | 10 3 | 3 | 3 | 1 | 1 | 1 4 | 4 | 4 | 1 | 1 | 1 5 | 5 | 5 | 1 | 1 | 1 6 | 6 | 6 | 1 | 1 | 1 7 | 7 | 7 | 1 | 1 | 1 3 | 3 | 3 | 2 | 2 | 2 4 | 4 | 4 | 2 | 2 | 2 5 | 5 | 5 | 2 | 2 | 2 6 | 6 | 6 | 2 | 2 | 2 7 | 7 | 7 | 2 | 2 | 2 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 3 | 3 | 3 5 | 5 | 5 | 3 | 3 | 3 6 | 6 | 6 | 3 | 3 | 3 7 | 7 | 7 | 3 | 3 | 3 3 | 3 | 3 | 4 | 4 | 4 4 | 4 | 4 | 4 | 4 | 4 5 | 5 | 5 | 4 | 4 | 4 6 | 6 | 6 | 4 | 4 | 4 7 | 7 | 7 | 4 | 4 | 4 3 | 3 | 3 | 5 | 5 | 5 4 | 4 | 4 | 5 | 5 | 5 5 | 5 | 5 | 5 | 5 | 5 6 | 6 | 6 | 5 | 5 | 5 7 | 7 | 7 | 5 | 5 | 5 3 | 3 | 3 | 6 | 6 | 6 4 | 4 | 4 | 6 | 6 | 6 5 | 5 | 5 | 6 | 6 | 6 6 | 6 | 6 | 6 | 6 | 6 7 | 7 | 7 | 6 | 6 | 6 3 | 3 | 3 | 7 | 7 | 7 4 | 4 | 4 | 7 | 7 | 7 5 | 5 | 5 | 7 | 7 | 7 6 | 6 | 6 | 7 | 7 | 7 7 | 7 | 7 | 7 | 7 | 7 3 | 3 | 3 | 8 | 8 | 8 4 | 4 | 4 | 8 | 8 | 8 5 | 5 | 5 | 8 | 8 | 8 6 | 6 | 6 | 8 | 8 | 8 7 | 7 | 7 | 8 | 8 | 8 3 | 3 | 3 | 9 | 9 | 9 4 | 4 | 4 | 9 | 9 | 9 5 | 5 | 5 | 9 | 9 | 9 6 | 6 | 6 | 9 | 9 | 9 7 | 7 | 7 | 9 | 9 | 9 3 | 3 | 3 | 10 | 10 | 10 4 | 4 | 4 | 10 | 10 | 10 5 | 5 | 5 | 10 | 10 | 10 6 | 6 | 6 | 10 | 10 | 10 7 | 7 | 7 | 10 | 10 | 10 8 | 8 | 8 | 1 | 1 | 1 9 | 9 | 9 | 1 | 1 | 1 10 | 10 | 10 | 1 | 1 | 1 8 | 8 | 8 | 2 | 2 | 2 9 | 9 | 9 | 2 | 2 | 2 10 | 10 | 10 | 2 | 2 | 2 8 | 8 | 8 | 3 | 3 | 3 9 | 9 | 9 | 3 | 3 | 3 10 | 10 | 10 | 3 | 3 | 3 8 | 8 | 8 | 4 | 4 | 4 9 | 9 | 9 | 4 | 4 | 4 10 | 10 | 10 | 4 | 4 | 4 8 | 8 | 8 | 5 | 5 | 5 9 | 9 | 9 | 5 | 5 | 5 10 | 10 | 10 | 5 | 5 | 5 8 | 8 | 8 | 6 | 6 | 6 9 | 9 | 9 | 6 | 6 | 6 10 | 10 | 10 | 6 | 6 | 6 8 | 8 | 8 | 7 | 7 | 7 9 | 9 | 9 | 7 | 7 | 7 10 | 10 | 10 | 7 | 7 | 7 8 | 8 | 8 | 8 | 8 | 8 9 | 9 | 9 | 8 | 8 | 8 10 | 10 | 10 | 8 | 8 | 8 8 | 8 | 8 | 9 | 9 | 9 9 | 9 | 9 | 9 | 9 | 9 10 | 10 | 10 | 9 | 9 | 9 8 | 8 | 8 | 10 | 10 | 10 9 | 9 | 9 | 10 | 10 | 10 10 | 10 | 10 | 10 | 10 | 10 (100 rows) SELECT * from x left join y on True where y.a is NULL; a | b | c | a | b | c ---+---+---+---+---+--- (0 rows) SELECT * from x left join y on True where y.a is NOT NULL; a | b | c | a | b | c ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 1 | 1 | 1 1 | 1 | 1 | 2 | 2 | 2 2 | 2 | 2 | 2 | 2 | 2 1 | 1 | 1 | 3 | 3 | 3 2 | 2 | 2 | 3 | 3 | 3 1 | 1 | 1 | 4 | 4 | 4 2 | 2 | 2 | 4 | 4 | 4 1 | 1 | 1 | 5 | 5 | 5 2 | 2 | 2 | 5 | 5 | 5 1 | 1 | 1 | 6 | 6 | 6 2 | 2 | 2 | 6 | 6 | 6 1 | 1 | 1 | 7 | 7 | 7 2 | 2 | 2 | 7 | 7 | 7 1 | 1 | 1 | 8 | 8 | 8 2 | 2 | 2 | 8 | 8 | 8 1 | 1 | 1 | 9 | 9 | 9 2 | 2 | 2 | 9 | 9 | 9 1 | 1 | 1 | 10 | 10 | 10 2 | 2 | 2 | 10 | 10 | 10 3 | 3 | 3 | 1 | 1 | 1 4 | 4 | 4 | 1 | 1 | 1 5 | 5 | 5 | 1 | 1 | 1 6 | 6 | 6 | 1 | 1 | 1 7 | 7 | 7 | 1 | 1 | 1 3 | 3 | 3 | 2 | 2 | 2 4 | 4 | 4 | 2 | 2 | 2 5 | 5 | 5 | 2 | 2 | 2 6 | 6 | 6 | 2 | 2 | 2 7 | 7 | 7 | 2 | 2 | 2 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 3 | 3 | 3 5 | 5 | 5 | 3 | 3 | 3 6 | 6 | 6 | 3 | 3 | 3 7 | 7 | 7 | 3 | 3 | 3 3 | 3 | 3 | 4 | 4 | 4 4 | 4 | 4 | 4 | 4 | 4 5 | 5 | 5 | 4 | 4 | 4 6 | 6 | 6 | 4 | 4 | 4 7 | 7 | 7 | 4 | 4 | 4 3 | 3 | 3 | 5 | 5 | 5 4 | 4 | 4 | 5 | 5 | 5 5 | 5 | 5 | 5 | 5 | 5 6 | 6 | 6 | 5 | 5 | 5 7 | 7 | 7 | 5 | 5 | 5 3 | 3 | 3 | 6 | 6 | 6 4 | 4 | 4 | 6 | 6 | 6 5 | 5 | 5 | 6 | 6 | 6 6 | 6 | 6 | 6 | 6 | 6 7 | 7 | 7 | 6 | 6 | 6 3 | 3 | 3 | 7 | 7 | 7 4 | 4 | 4 | 7 | 7 | 7 5 | 5 | 5 | 7 | 7 | 7 6 | 6 | 6 | 7 | 7 | 7 7 | 7 | 7 | 7 | 7 | 7 3 | 3 | 3 | 8 | 8 | 8 4 | 4 | 4 | 8 | 8 | 8 5 | 5 | 5 | 8 | 8 | 8 6 | 6 | 6 | 8 | 8 | 8 7 | 7 | 7 | 8 | 8 | 8 3 | 3 | 3 | 9 | 9 | 9 4 | 4 | 4 | 9 | 9 | 9 5 | 5 | 5 | 9 | 9 | 9 6 | 6 | 6 | 9 | 9 | 9 7 | 7 | 7 | 9 | 9 | 9 3 | 3 | 3 | 10 | 10 | 10 4 | 4 | 4 | 10 | 10 | 10 5 | 5 | 5 | 10 | 10 | 10 6 | 6 | 6 | 10 | 10 | 10 7 | 7 | 7 | 10 | 10 | 10 8 | 8 | 8 | 1 | 1 | 1 9 | 9 | 9 | 1 | 1 | 1 10 | 10 | 10 | 1 | 1 | 1 8 | 8 | 8 | 2 | 2 | 2 9 | 9 | 9 | 2 | 2 | 2 10 | 10 | 10 | 2 | 2 | 2 8 | 8 | 8 | 3 | 3 | 3 9 | 9 | 9 | 3 | 3 | 3 10 | 10 | 10 | 3 | 3 | 3 8 | 8 | 8 | 4 | 4 | 4 9 | 9 | 9 | 4 | 4 | 4 10 | 10 | 10 | 4 | 4 | 4 8 | 8 | 8 | 5 | 5 | 5 9 | 9 | 9 | 5 | 5 | 5 10 | 10 | 10 | 5 | 5 | 5 8 | 8 | 8 | 6 | 6 | 6 9 | 9 | 9 | 6 | 6 | 6 10 | 10 | 10 | 6 | 6 | 6 8 | 8 | 8 | 7 | 7 | 7 9 | 9 | 9 | 7 | 7 | 7 10 | 10 | 10 | 7 | 7 | 7 8 | 8 | 8 | 8 | 8 | 8 9 | 9 | 9 | 8 | 8 | 8 10 | 10 | 10 | 8 | 8 | 8 8 | 8 | 8 | 9 | 9 | 9 9 | 9 | 9 | 9 | 9 | 9 10 | 10 | 10 | 9 | 9 | 9 8 | 8 | 8 | 10 | 10 | 10 9 | 9 | 9 | 10 | 10 | 10 10 | 10 | 10 | 10 | 10 | 10 (100 rows) SELECT * from x left join y on True where y.a is NULL and Y.b > 0; a | b | c | a | b | c ---+---+---+---+---+--- (0 rows) SELECT * from x left join y on True where func_x(y.a) > 0; a | b | c | a | b | c ----+----+----+----+----+---- 1 | 1 | 1 | 1 | 1 | 1 1 | 1 | 1 | 2 | 2 | 2 1 | 1 | 1 | 8 | 8 | 8 1 | 1 | 1 | 9 | 9 | 9 1 | 1 | 1 | 10 | 10 | 10 1 | 1 | 1 | 3 | 3 | 3 1 | 1 | 1 | 4 | 4 | 4 1 | 1 | 1 | 5 | 5 | 5 1 | 1 | 1 | 6 | 6 | 6 1 | 1 | 1 | 7 | 7 | 7 2 | 2 | 2 | 1 | 1 | 1 2 | 2 | 2 | 2 | 2 | 2 2 | 2 | 2 | 8 | 8 | 8 2 | 2 | 2 | 9 | 9 | 9 2 | 2 | 2 | 10 | 10 | 10 2 | 2 | 2 | 3 | 3 | 3 2 | 2 | 2 | 4 | 4 | 4 2 | 2 | 2 | 5 | 5 | 5 2 | 2 | 2 | 6 | 6 | 6 2 | 2 | 2 | 7 | 7 | 7 3 | 3 | 3 | 1 | 1 | 1 3 | 3 | 3 | 2 | 2 | 2 3 | 3 | 3 | 8 | 8 | 8 3 | 3 | 3 | 9 | 9 | 9 3 | 3 | 3 | 10 | 10 | 10 3 | 3 | 3 | 3 | 3 | 3 3 | 3 | 3 | 4 | 4 | 4 3 | 3 | 3 | 5 | 5 | 5 3 | 3 | 3 | 6 | 6 | 6 3 | 3 | 3 | 7 | 7 | 7 4 | 4 | 4 | 1 | 1 | 1 4 | 4 | 4 | 2 | 2 | 2 4 | 4 | 4 | 8 | 8 | 8 4 | 4 | 4 | 9 | 9 | 9 4 | 4 | 4 | 10 | 10 | 10 4 | 4 | 4 | 3 | 3 | 3 4 | 4 | 4 | 4 | 4 | 4 4 | 4 | 4 | 5 | 5 | 5 4 | 4 | 4 | 6 | 6 | 6 4 | 4 | 4 | 7 | 7 | 7 5 | 5 | 5 | 1 | 1 | 1 5 | 5 | 5 | 2 | 2 | 2 5 | 5 | 5 | 8 | 8 | 8 5 | 5 | 5 | 9 | 9 | 9 5 | 5 | 5 | 10 | 10 | 10 5 | 5 | 5 | 3 | 3 | 3 5 | 5 | 5 | 4 | 4 | 4 5 | 5 | 5 | 5 | 5 | 5 5 | 5 | 5 | 6 | 6 | 6 5 | 5 | 5 | 7 | 7 | 7 6 | 6 | 6 | 1 | 1 | 1 6 | 6 | 6 | 2 | 2 | 2 6 | 6 | 6 | 8 | 8 | 8 6 | 6 | 6 | 9 | 9 | 9 6 | 6 | 6 | 10 | 10 | 10 6 | 6 | 6 | 3 | 3 | 3 6 | 6 | 6 | 4 | 4 | 4 6 | 6 | 6 | 5 | 5 | 5 6 | 6 | 6 | 6 | 6 | 6 6 | 6 | 6 | 7 | 7 | 7 7 | 7 | 7 | 1 | 1 | 1 7 | 7 | 7 | 2 | 2 | 2 7 | 7 | 7 | 8 | 8 | 8 7 | 7 | 7 | 9 | 9 | 9 7 | 7 | 7 | 10 | 10 | 10 7 | 7 | 7 | 3 | 3 | 3 7 | 7 | 7 | 4 | 4 | 4 7 | 7 | 7 | 5 | 5 | 5 7 | 7 | 7 | 6 | 6 | 6 7 | 7 | 7 | 7 | 7 | 7 8 | 8 | 8 | 1 | 1 | 1 8 | 8 | 8 | 2 | 2 | 2 8 | 8 | 8 | 8 | 8 | 8 8 | 8 | 8 | 9 | 9 | 9 8 | 8 | 8 | 10 | 10 | 10 8 | 8 | 8 | 3 | 3 | 3 8 | 8 | 8 | 4 | 4 | 4 8 | 8 | 8 | 5 | 5 | 5 8 | 8 | 8 | 6 | 6 | 6 8 | 8 | 8 | 7 | 7 | 7 9 | 9 | 9 | 1 | 1 | 1 9 | 9 | 9 | 2 | 2 | 2 9 | 9 | 9 | 8 | 8 | 8 9 | 9 | 9 | 9 | 9 | 9 9 | 9 | 9 | 10 | 10 | 10 9 | 9 | 9 | 3 | 3 | 3 9 | 9 | 9 | 4 | 4 | 4 9 | 9 | 9 | 5 | 5 | 5 9 | 9 | 9 | 6 | 6 | 6 9 | 9 | 9 | 7 | 7 | 7 10 | 10 | 10 | 1 | 1 | 1 10 | 10 | 10 | 2 | 2 | 2 10 | 10 | 10 | 8 | 8 | 8 10 | 10 | 10 | 9 | 9 | 9 10 | 10 | 10 | 10 | 10 | 10 10 | 10 | 10 | 3 | 3 | 3 10 | 10 | 10 | 4 | 4 | 4 10 | 10 | 10 | 5 | 5 | 5 10 | 10 | 10 | 6 | 6 | 6 10 | 10 | 10 | 7 | 7 | 7 (100 rows) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t1.b IS DISTINCT FROM t2.b; a | b | a | b ---+---+---+--- 1 | 1 | | 2 | 1 | 2 | 3 (2 rows) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t1.b IS DISTINCT FROM NULL; a | b | a | b ---+---+---+--- 1 | 1 | | 2 | 1 | 2 | 3 (2 rows) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t2.b IS DISTINCT FROM NULL; a | b | a | b ---+---+---+--- 2 | 1 | 2 | 3 (1 row) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t2.b IS NOT DISTINCT FROM NULL; a | b | a | b ---+---+---+--- 1 | 1 | | 3 | | | (2 rows) SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t1.b IS NOT DISTINCT FROM NULL; a | b | a | b ---+---+---+--- 3 | | | (1 row) -- Test for unexpected NLJ qual -- explain select 1 as mrs_t1 where 1 <= ALL (select x from z); QUERY PLAN ------------------------------------------------------------------------------------------------ Result (cost=0.00..882688.07 rows=1 width=4) -> Nested Loop Left Anti Semi (Not-In) Join (cost=0.00..882688.07 rows=1 width=1) Join Filter: true -> Result (cost=0.00..0.00 rows=1 width=1) -> Materialize (cost=0.00..431.00 rows=1 width=1) -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=1) -> Seq Scan on z (cost=0.00..431.00 rows=1 width=1) Filter: 1 > x Settings: optimizer=on Optimizer status: PQO version 1.621 (10 rows) -- -- Test for wrong results in window functions under joins #1 -- select * from (SELECT bfv_joins_bar.*, AVG(t.b) OVER(PARTITION BY t.a ORDER BY t.b desc) AS e FROM t,bfv_joins_bar) bfv_joins_foo, t where e < 10 order by 1, 2, 3, 4, 5, 6; c | d | e | a | b | d ----+----+--------------------+----+----+---- 1 | 2 | 3.0000000000000000 | 2 | 3 | 2 1 | 2 | 3.0000000000000000 | 3 | 4 | 3 1 | 2 | 3.0000000000000000 | 4 | 5 | 4 1 | 2 | 3.0000000000000000 | 5 | 6 | 5 1 | 2 | 3.0000000000000000 | 6 | 7 | 6 1 | 2 | 3.0000000000000000 | 7 | 8 | 7 1 | 2 | 3.0000000000000000 | 8 | 9 | 8 1 | 2 | 3.0000000000000000 | 9 | 10 | 9 1 | 2 | 3.0000000000000000 | 10 | 11 | 10 1 | 2 | 4.0000000000000000 | 2 | 3 | 2 1 | 2 | 4.0000000000000000 | 3 | 4 | 3 1 | 2 | 4.0000000000000000 | 4 | 5 | 4 1 | 2 | 4.0000000000000000 | 5 | 6 | 5 1 | 2 | 4.0000000000000000 | 6 | 7 | 6 1 | 2 | 4.0000000000000000 | 7 | 8 | 7 1 | 2 | 4.0000000000000000 | 8 | 9 | 8 1 | 2 | 4.0000000000000000 | 9 | 10 | 9 1 | 2 | 4.0000000000000000 | 10 | 11 | 10 1 | 2 | 5.0000000000000000 | 2 | 3 | 2 1 | 2 | 5.0000000000000000 | 3 | 4 | 3 1 | 2 | 5.0000000000000000 | 4 | 5 | 4 1 | 2 | 5.0000000000000000 | 5 | 6 | 5 1 | 2 | 5.0000000000000000 | 6 | 7 | 6 1 | 2 | 5.0000000000000000 | 7 | 8 | 7 1 | 2 | 5.0000000000000000 | 8 | 9 | 8 1 | 2 | 5.0000000000000000 | 9 | 10 | 9 1 | 2 | 5.0000000000000000 | 10 | 11 | 10 1 | 2 | 6.0000000000000000 | 2 | 3 | 2 1 | 2 | 6.0000000000000000 | 3 | 4 | 3 1 | 2 | 6.0000000000000000 | 4 | 5 | 4 1 | 2 | 6.0000000000000000 | 5 | 6 | 5 1 | 2 | 6.0000000000000000 | 6 | 7 | 6 1 | 2 | 6.0000000000000000 | 7 | 8 | 7 1 | 2 | 6.0000000000000000 | 8 | 9 | 8 1 | 2 | 6.0000000000000000 | 9 | 10 | 9 1 | 2 | 6.0000000000000000 | 10 | 11 | 10 1 | 2 | 7.0000000000000000 | 2 | 3 | 2 1 | 2 | 7.0000000000000000 | 3 | 4 | 3 1 | 2 | 7.0000000000000000 | 4 | 5 | 4 1 | 2 | 7.0000000000000000 | 5 | 6 | 5 1 | 2 | 7.0000000000000000 | 6 | 7 | 6 1 | 2 | 7.0000000000000000 | 7 | 8 | 7 1 | 2 | 7.0000000000000000 | 8 | 9 | 8 1 | 2 | 7.0000000000000000 | 9 | 10 | 9 1 | 2 | 7.0000000000000000 | 10 | 11 | 10 1 | 2 | 8.0000000000000000 | 2 | 3 | 2 1 | 2 | 8.0000000000000000 | 3 | 4 | 3 1 | 2 | 8.0000000000000000 | 4 | 5 | 4 1 | 2 | 8.0000000000000000 | 5 | 6 | 5 1 | 2 | 8.0000000000000000 | 6 | 7 | 6 1 | 2 | 8.0000000000000000 | 7 | 8 | 7 1 | 2 | 8.0000000000000000 | 8 | 9 | 8 1 | 2 | 8.0000000000000000 | 9 | 10 | 9 1 | 2 | 8.0000000000000000 | 10 | 11 | 10 1 | 2 | 9.0000000000000000 | 2 | 3 | 2 1 | 2 | 9.0000000000000000 | 3 | 4 | 3 1 | 2 | 9.0000000000000000 | 4 | 5 | 4 1 | 2 | 9.0000000000000000 | 5 | 6 | 5 1 | 2 | 9.0000000000000000 | 6 | 7 | 6 1 | 2 | 9.0000000000000000 | 7 | 8 | 7 1 | 2 | 9.0000000000000000 | 8 | 9 | 8 1 | 2 | 9.0000000000000000 | 9 | 10 | 9 1 | 2 | 9.0000000000000000 | 10 | 11 | 10 2 | 3 | 3.0000000000000000 | 2 | 3 | 2 2 | 3 | 3.0000000000000000 | 3 | 4 | 3 2 | 3 | 3.0000000000000000 | 4 | 5 | 4 2 | 3 | 3.0000000000000000 | 5 | 6 | 5 2 | 3 | 3.0000000000000000 | 6 | 7 | 6 2 | 3 | 3.0000000000000000 | 7 | 8 | 7 2 | 3 | 3.0000000000000000 | 8 | 9 | 8 2 | 3 | 3.0000000000000000 | 9 | 10 | 9 2 | 3 | 3.0000000000000000 | 10 | 11 | 10 2 | 3 | 4.0000000000000000 | 2 | 3 | 2 2 | 3 | 4.0000000000000000 | 3 | 4 | 3 2 | 3 | 4.0000000000000000 | 4 | 5 | 4 2 | 3 | 4.0000000000000000 | 5 | 6 | 5 2 | 3 | 4.0000000000000000 | 6 | 7 | 6 2 | 3 | 4.0000000000000000 | 7 | 8 | 7 2 | 3 | 4.0000000000000000 | 8 | 9 | 8 2 | 3 | 4.0000000000000000 | 9 | 10 | 9 2 | 3 | 4.0000000000000000 | 10 | 11 | 10 2 | 3 | 5.0000000000000000 | 2 | 3 | 2 2 | 3 | 5.0000000000000000 | 3 | 4 | 3 2 | 3 | 5.0000000000000000 | 4 | 5 | 4 2 | 3 | 5.0000000000000000 | 5 | 6 | 5 2 | 3 | 5.0000000000000000 | 6 | 7 | 6 2 | 3 | 5.0000000000000000 | 7 | 8 | 7 2 | 3 | 5.0000000000000000 | 8 | 9 | 8 2 | 3 | 5.0000000000000000 | 9 | 10 | 9 2 | 3 | 5.0000000000000000 | 10 | 11 | 10 2 | 3 | 6.0000000000000000 | 2 | 3 | 2 2 | 3 | 6.0000000000000000 | 3 | 4 | 3 2 | 3 | 6.0000000000000000 | 4 | 5 | 4 2 | 3 | 6.0000000000000000 | 5 | 6 | 5 2 | 3 | 6.0000000000000000 | 6 | 7 | 6 2 | 3 | 6.0000000000000000 | 7 | 8 | 7 2 | 3 | 6.0000000000000000 | 8 | 9 | 8 2 | 3 | 6.0000000000000000 | 9 | 10 | 9 2 | 3 | 6.0000000000000000 | 10 | 11 | 10 2 | 3 | 7.0000000000000000 | 2 | 3 | 2 2 | 3 | 7.0000000000000000 | 3 | 4 | 3 2 | 3 | 7.0000000000000000 | 4 | 5 | 4 2 | 3 | 7.0000000000000000 | 5 | 6 | 5 2 | 3 | 7.0000000000000000 | 6 | 7 | 6 2 | 3 | 7.0000000000000000 | 7 | 8 | 7 2 | 3 | 7.0000000000000000 | 8 | 9 | 8 2 | 3 | 7.0000000000000000 | 9 | 10 | 9 2 | 3 | 7.0000000000000000 | 10 | 11 | 10 2 | 3 | 8.0000000000000000 | 2 | 3 | 2 2 | 3 | 8.0000000000000000 | 3 | 4 | 3 2 | 3 | 8.0000000000000000 | 4 | 5 | 4 2 | 3 | 8.0000000000000000 | 5 | 6 | 5 2 | 3 | 8.0000000000000000 | 6 | 7 | 6 2 | 3 | 8.0000000000000000 | 7 | 8 | 7 2 | 3 | 8.0000000000000000 | 8 | 9 | 8 2 | 3 | 8.0000000000000000 | 9 | 10 | 9 2 | 3 | 8.0000000000000000 | 10 | 11 | 10 2 | 3 | 9.0000000000000000 | 2 | 3 | 2 2 | 3 | 9.0000000000000000 | 3 | 4 | 3 2 | 3 | 9.0000000000000000 | 4 | 5 | 4 2 | 3 | 9.0000000000000000 | 5 | 6 | 5 2 | 3 | 9.0000000000000000 | 6 | 7 | 6 2 | 3 | 9.0000000000000000 | 7 | 8 | 7 2 | 3 | 9.0000000000000000 | 8 | 9 | 8 2 | 3 | 9.0000000000000000 | 9 | 10 | 9 2 | 3 | 9.0000000000000000 | 10 | 11 | 10 3 | 4 | 3.0000000000000000 | 2 | 3 | 2 3 | 4 | 3.0000000000000000 | 3 | 4 | 3 3 | 4 | 3.0000000000000000 | 4 | 5 | 4 3 | 4 | 3.0000000000000000 | 5 | 6 | 5 3 | 4 | 3.0000000000000000 | 6 | 7 | 6 3 | 4 | 3.0000000000000000 | 7 | 8 | 7 3 | 4 | 3.0000000000000000 | 8 | 9 | 8 3 | 4 | 3.0000000000000000 | 9 | 10 | 9 3 | 4 | 3.0000000000000000 | 10 | 11 | 10 3 | 4 | 4.0000000000000000 | 2 | 3 | 2 3 | 4 | 4.0000000000000000 | 3 | 4 | 3 3 | 4 | 4.0000000000000000 | 4 | 5 | 4 3 | 4 | 4.0000000000000000 | 5 | 6 | 5 3 | 4 | 4.0000000000000000 | 6 | 7 | 6 3 | 4 | 4.0000000000000000 | 7 | 8 | 7 3 | 4 | 4.0000000000000000 | 8 | 9 | 8 3 | 4 | 4.0000000000000000 | 9 | 10 | 9 3 | 4 | 4.0000000000000000 | 10 | 11 | 10 3 | 4 | 5.0000000000000000 | 2 | 3 | 2 3 | 4 | 5.0000000000000000 | 3 | 4 | 3 3 | 4 | 5.0000000000000000 | 4 | 5 | 4 3 | 4 | 5.0000000000000000 | 5 | 6 | 5 3 | 4 | 5.0000000000000000 | 6 | 7 | 6 3 | 4 | 5.0000000000000000 | 7 | 8 | 7 3 | 4 | 5.0000000000000000 | 8 | 9 | 8 3 | 4 | 5.0000000000000000 | 9 | 10 | 9 3 | 4 | 5.0000000000000000 | 10 | 11 | 10 3 | 4 | 6.0000000000000000 | 2 | 3 | 2 3 | 4 | 6.0000000000000000 | 3 | 4 | 3 3 | 4 | 6.0000000000000000 | 4 | 5 | 4 3 | 4 | 6.0000000000000000 | 5 | 6 | 5 3 | 4 | 6.0000000000000000 | 6 | 7 | 6 3 | 4 | 6.0000000000000000 | 7 | 8 | 7 3 | 4 | 6.0000000000000000 | 8 | 9 | 8 3 | 4 | 6.0000000000000000 | 9 | 10 | 9 3 | 4 | 6.0000000000000000 | 10 | 11 | 10 3 | 4 | 7.0000000000000000 | 2 | 3 | 2 3 | 4 | 7.0000000000000000 | 3 | 4 | 3 3 | 4 | 7.0000000000000000 | 4 | 5 | 4 3 | 4 | 7.0000000000000000 | 5 | 6 | 5 3 | 4 | 7.0000000000000000 | 6 | 7 | 6 3 | 4 | 7.0000000000000000 | 7 | 8 | 7 3 | 4 | 7.0000000000000000 | 8 | 9 | 8 3 | 4 | 7.0000000000000000 | 9 | 10 | 9 3 | 4 | 7.0000000000000000 | 10 | 11 | 10 3 | 4 | 8.0000000000000000 | 2 | 3 | 2 3 | 4 | 8.0000000000000000 | 3 | 4 | 3 3 | 4 | 8.0000000000000000 | 4 | 5 | 4 3 | 4 | 8.0000000000000000 | 5 | 6 | 5 3 | 4 | 8.0000000000000000 | 6 | 7 | 6 3 | 4 | 8.0000000000000000 | 7 | 8 | 7 3 | 4 | 8.0000000000000000 | 8 | 9 | 8 3 | 4 | 8.0000000000000000 | 9 | 10 | 9 3 | 4 | 8.0000000000000000 | 10 | 11 | 10 3 | 4 | 9.0000000000000000 | 2 | 3 | 2 3 | 4 | 9.0000000000000000 | 3 | 4 | 3 3 | 4 | 9.0000000000000000 | 4 | 5 | 4 3 | 4 | 9.0000000000000000 | 5 | 6 | 5 3 | 4 | 9.0000000000000000 | 6 | 7 | 6 3 | 4 | 9.0000000000000000 | 7 | 8 | 7 3 | 4 | 9.0000000000000000 | 8 | 9 | 8 3 | 4 | 9.0000000000000000 | 9 | 10 | 9 3 | 4 | 9.0000000000000000 | 10 | 11 | 10 4 | 5 | 3.0000000000000000 | 2 | 3 | 2 4 | 5 | 3.0000000000000000 | 3 | 4 | 3 4 | 5 | 3.0000000000000000 | 4 | 5 | 4 4 | 5 | 3.0000000000000000 | 5 | 6 | 5 4 | 5 | 3.0000000000000000 | 6 | 7 | 6 4 | 5 | 3.0000000000000000 | 7 | 8 | 7 4 | 5 | 3.0000000000000000 | 8 | 9 | 8 4 | 5 | 3.0000000000000000 | 9 | 10 | 9 4 | 5 | 3.0000000000000000 | 10 | 11 | 10 4 | 5 | 4.0000000000000000 | 2 | 3 | 2 4 | 5 | 4.0000000000000000 | 3 | 4 | 3 4 | 5 | 4.0000000000000000 | 4 | 5 | 4 4 | 5 | 4.0000000000000000 | 5 | 6 | 5 4 | 5 | 4.0000000000000000 | 6 | 7 | 6 4 | 5 | 4.0000000000000000 | 7 | 8 | 7 4 | 5 | 4.0000000000000000 | 8 | 9 | 8 4 | 5 | 4.0000000000000000 | 9 | 10 | 9 4 | 5 | 4.0000000000000000 | 10 | 11 | 10 4 | 5 | 5.0000000000000000 | 2 | 3 | 2 4 | 5 | 5.0000000000000000 | 3 | 4 | 3 4 | 5 | 5.0000000000000000 | 4 | 5 | 4 4 | 5 | 5.0000000000000000 | 5 | 6 | 5 4 | 5 | 5.0000000000000000 | 6 | 7 | 6 4 | 5 | 5.0000000000000000 | 7 | 8 | 7 4 | 5 | 5.0000000000000000 | 8 | 9 | 8 4 | 5 | 5.0000000000000000 | 9 | 10 | 9 4 | 5 | 5.0000000000000000 | 10 | 11 | 10 4 | 5 | 6.0000000000000000 | 2 | 3 | 2 4 | 5 | 6.0000000000000000 | 3 | 4 | 3 4 | 5 | 6.0000000000000000 | 4 | 5 | 4 4 | 5 | 6.0000000000000000 | 5 | 6 | 5 4 | 5 | 6.0000000000000000 | 6 | 7 | 6 4 | 5 | 6.0000000000000000 | 7 | 8 | 7 4 | 5 | 6.0000000000000000 | 8 | 9 | 8 4 | 5 | 6.0000000000000000 | 9 | 10 | 9 4 | 5 | 6.0000000000000000 | 10 | 11 | 10 4 | 5 | 7.0000000000000000 | 2 | 3 | 2 4 | 5 | 7.0000000000000000 | 3 | 4 | 3 4 | 5 | 7.0000000000000000 | 4 | 5 | 4 4 | 5 | 7.0000000000000000 | 5 | 6 | 5 4 | 5 | 7.0000000000000000 | 6 | 7 | 6 4 | 5 | 7.0000000000000000 | 7 | 8 | 7 4 | 5 | 7.0000000000000000 | 8 | 9 | 8 4 | 5 | 7.0000000000000000 | 9 | 10 | 9 4 | 5 | 7.0000000000000000 | 10 | 11 | 10 4 | 5 | 8.0000000000000000 | 2 | 3 | 2 4 | 5 | 8.0000000000000000 | 3 | 4 | 3 4 | 5 | 8.0000000000000000 | 4 | 5 | 4 4 | 5 | 8.0000000000000000 | 5 | 6 | 5 4 | 5 | 8.0000000000000000 | 6 | 7 | 6 4 | 5 | 8.0000000000000000 | 7 | 8 | 7 4 | 5 | 8.0000000000000000 | 8 | 9 | 8 4 | 5 | 8.0000000000000000 | 9 | 10 | 9 4 | 5 | 8.0000000000000000 | 10 | 11 | 10 4 | 5 | 9.0000000000000000 | 2 | 3 | 2 4 | 5 | 9.0000000000000000 | 3 | 4 | 3 4 | 5 | 9.0000000000000000 | 4 | 5 | 4 4 | 5 | 9.0000000000000000 | 5 | 6 | 5 4 | 5 | 9.0000000000000000 | 6 | 7 | 6 4 | 5 | 9.0000000000000000 | 7 | 8 | 7 4 | 5 | 9.0000000000000000 | 8 | 9 | 8 4 | 5 | 9.0000000000000000 | 9 | 10 | 9 4 | 5 | 9.0000000000000000 | 10 | 11 | 10 5 | 6 | 3.0000000000000000 | 2 | 3 | 2 5 | 6 | 3.0000000000000000 | 3 | 4 | 3 5 | 6 | 3.0000000000000000 | 4 | 5 | 4 5 | 6 | 3.0000000000000000 | 5 | 6 | 5 5 | 6 | 3.0000000000000000 | 6 | 7 | 6 5 | 6 | 3.0000000000000000 | 7 | 8 | 7 5 | 6 | 3.0000000000000000 | 8 | 9 | 8 5 | 6 | 3.0000000000000000 | 9 | 10 | 9 5 | 6 | 3.0000000000000000 | 10 | 11 | 10 5 | 6 | 4.0000000000000000 | 2 | 3 | 2 5 | 6 | 4.0000000000000000 | 3 | 4 | 3 5 | 6 | 4.0000000000000000 | 4 | 5 | 4 5 | 6 | 4.0000000000000000 | 5 | 6 | 5 5 | 6 | 4.0000000000000000 | 6 | 7 | 6 5 | 6 | 4.0000000000000000 | 7 | 8 | 7 5 | 6 | 4.0000000000000000 | 8 | 9 | 8 5 | 6 | 4.0000000000000000 | 9 | 10 | 9 5 | 6 | 4.0000000000000000 | 10 | 11 | 10 5 | 6 | 5.0000000000000000 | 2 | 3 | 2 5 | 6 | 5.0000000000000000 | 3 | 4 | 3 5 | 6 | 5.0000000000000000 | 4 | 5 | 4 5 | 6 | 5.0000000000000000 | 5 | 6 | 5 5 | 6 | 5.0000000000000000 | 6 | 7 | 6 5 | 6 | 5.0000000000000000 | 7 | 8 | 7 5 | 6 | 5.0000000000000000 | 8 | 9 | 8 5 | 6 | 5.0000000000000000 | 9 | 10 | 9 5 | 6 | 5.0000000000000000 | 10 | 11 | 10 5 | 6 | 6.0000000000000000 | 2 | 3 | 2 5 | 6 | 6.0000000000000000 | 3 | 4 | 3 5 | 6 | 6.0000000000000000 | 4 | 5 | 4 5 | 6 | 6.0000000000000000 | 5 | 6 | 5 5 | 6 | 6.0000000000000000 | 6 | 7 | 6 5 | 6 | 6.0000000000000000 | 7 | 8 | 7 5 | 6 | 6.0000000000000000 | 8 | 9 | 8 5 | 6 | 6.0000000000000000 | 9 | 10 | 9 5 | 6 | 6.0000000000000000 | 10 | 11 | 10 5 | 6 | 7.0000000000000000 | 2 | 3 | 2 5 | 6 | 7.0000000000000000 | 3 | 4 | 3 5 | 6 | 7.0000000000000000 | 4 | 5 | 4 5 | 6 | 7.0000000000000000 | 5 | 6 | 5 5 | 6 | 7.0000000000000000 | 6 | 7 | 6 5 | 6 | 7.0000000000000000 | 7 | 8 | 7 5 | 6 | 7.0000000000000000 | 8 | 9 | 8 5 | 6 | 7.0000000000000000 | 9 | 10 | 9 5 | 6 | 7.0000000000000000 | 10 | 11 | 10 5 | 6 | 8.0000000000000000 | 2 | 3 | 2 5 | 6 | 8.0000000000000000 | 3 | 4 | 3 5 | 6 | 8.0000000000000000 | 4 | 5 | 4 5 | 6 | 8.0000000000000000 | 5 | 6 | 5 5 | 6 | 8.0000000000000000 | 6 | 7 | 6 5 | 6 | 8.0000000000000000 | 7 | 8 | 7 5 | 6 | 8.0000000000000000 | 8 | 9 | 8 5 | 6 | 8.0000000000000000 | 9 | 10 | 9 5 | 6 | 8.0000000000000000 | 10 | 11 | 10 5 | 6 | 9.0000000000000000 | 2 | 3 | 2 5 | 6 | 9.0000000000000000 | 3 | 4 | 3 5 | 6 | 9.0000000000000000 | 4 | 5 | 4 5 | 6 | 9.0000000000000000 | 5 | 6 | 5 5 | 6 | 9.0000000000000000 | 6 | 7 | 6 5 | 6 | 9.0000000000000000 | 7 | 8 | 7 5 | 6 | 9.0000000000000000 | 8 | 9 | 8 5 | 6 | 9.0000000000000000 | 9 | 10 | 9 5 | 6 | 9.0000000000000000 | 10 | 11 | 10 6 | 7 | 3.0000000000000000 | 2 | 3 | 2 6 | 7 | 3.0000000000000000 | 3 | 4 | 3 6 | 7 | 3.0000000000000000 | 4 | 5 | 4 6 | 7 | 3.0000000000000000 | 5 | 6 | 5 6 | 7 | 3.0000000000000000 | 6 | 7 | 6 6 | 7 | 3.0000000000000000 | 7 | 8 | 7 6 | 7 | 3.0000000000000000 | 8 | 9 | 8 6 | 7 | 3.0000000000000000 | 9 | 10 | 9 6 | 7 | 3.0000000000000000 | 10 | 11 | 10 6 | 7 | 4.0000000000000000 | 2 | 3 | 2 6 | 7 | 4.0000000000000000 | 3 | 4 | 3 6 | 7 | 4.0000000000000000 | 4 | 5 | 4 6 | 7 | 4.0000000000000000 | 5 | 6 | 5 6 | 7 | 4.0000000000000000 | 6 | 7 | 6 6 | 7 | 4.0000000000000000 | 7 | 8 | 7 6 | 7 | 4.0000000000000000 | 8 | 9 | 8 6 | 7 | 4.0000000000000000 | 9 | 10 | 9 6 | 7 | 4.0000000000000000 | 10 | 11 | 10 6 | 7 | 5.0000000000000000 | 2 | 3 | 2 6 | 7 | 5.0000000000000000 | 3 | 4 | 3 6 | 7 | 5.0000000000000000 | 4 | 5 | 4 6 | 7 | 5.0000000000000000 | 5 | 6 | 5 6 | 7 | 5.0000000000000000 | 6 | 7 | 6 6 | 7 | 5.0000000000000000 | 7 | 8 | 7 6 | 7 | 5.0000000000000000 | 8 | 9 | 8 6 | 7 | 5.0000000000000000 | 9 | 10 | 9 6 | 7 | 5.0000000000000000 | 10 | 11 | 10 6 | 7 | 6.0000000000000000 | 2 | 3 | 2 6 | 7 | 6.0000000000000000 | 3 | 4 | 3 6 | 7 | 6.0000000000000000 | 4 | 5 | 4 6 | 7 | 6.0000000000000000 | 5 | 6 | 5 6 | 7 | 6.0000000000000000 | 6 | 7 | 6 6 | 7 | 6.0000000000000000 | 7 | 8 | 7 6 | 7 | 6.0000000000000000 | 8 | 9 | 8 6 | 7 | 6.0000000000000000 | 9 | 10 | 9 6 | 7 | 6.0000000000000000 | 10 | 11 | 10 6 | 7 | 7.0000000000000000 | 2 | 3 | 2 6 | 7 | 7.0000000000000000 | 3 | 4 | 3 6 | 7 | 7.0000000000000000 | 4 | 5 | 4 6 | 7 | 7.0000000000000000 | 5 | 6 | 5 6 | 7 | 7.0000000000000000 | 6 | 7 | 6 6 | 7 | 7.0000000000000000 | 7 | 8 | 7 6 | 7 | 7.0000000000000000 | 8 | 9 | 8 6 | 7 | 7.0000000000000000 | 9 | 10 | 9 6 | 7 | 7.0000000000000000 | 10 | 11 | 10 6 | 7 | 8.0000000000000000 | 2 | 3 | 2 6 | 7 | 8.0000000000000000 | 3 | 4 | 3 6 | 7 | 8.0000000000000000 | 4 | 5 | 4 6 | 7 | 8.0000000000000000 | 5 | 6 | 5 6 | 7 | 8.0000000000000000 | 6 | 7 | 6 6 | 7 | 8.0000000000000000 | 7 | 8 | 7 6 | 7 | 8.0000000000000000 | 8 | 9 | 8 6 | 7 | 8.0000000000000000 | 9 | 10 | 9 6 | 7 | 8.0000000000000000 | 10 | 11 | 10 6 | 7 | 9.0000000000000000 | 2 | 3 | 2 6 | 7 | 9.0000000000000000 | 3 | 4 | 3 6 | 7 | 9.0000000000000000 | 4 | 5 | 4 6 | 7 | 9.0000000000000000 | 5 | 6 | 5 6 | 7 | 9.0000000000000000 | 6 | 7 | 6 6 | 7 | 9.0000000000000000 | 7 | 8 | 7 6 | 7 | 9.0000000000000000 | 8 | 9 | 8 6 | 7 | 9.0000000000000000 | 9 | 10 | 9 6 | 7 | 9.0000000000000000 | 10 | 11 | 10 7 | 8 | 3.0000000000000000 | 2 | 3 | 2 7 | 8 | 3.0000000000000000 | 3 | 4 | 3 7 | 8 | 3.0000000000000000 | 4 | 5 | 4 7 | 8 | 3.0000000000000000 | 5 | 6 | 5 7 | 8 | 3.0000000000000000 | 6 | 7 | 6 7 | 8 | 3.0000000000000000 | 7 | 8 | 7 7 | 8 | 3.0000000000000000 | 8 | 9 | 8 7 | 8 | 3.0000000000000000 | 9 | 10 | 9 7 | 8 | 3.0000000000000000 | 10 | 11 | 10 7 | 8 | 4.0000000000000000 | 2 | 3 | 2 7 | 8 | 4.0000000000000000 | 3 | 4 | 3 7 | 8 | 4.0000000000000000 | 4 | 5 | 4 7 | 8 | 4.0000000000000000 | 5 | 6 | 5 7 | 8 | 4.0000000000000000 | 6 | 7 | 6 7 | 8 | 4.0000000000000000 | 7 | 8 | 7 7 | 8 | 4.0000000000000000 | 8 | 9 | 8 7 | 8 | 4.0000000000000000 | 9 | 10 | 9 7 | 8 | 4.0000000000000000 | 10 | 11 | 10 7 | 8 | 5.0000000000000000 | 2 | 3 | 2 7 | 8 | 5.0000000000000000 | 3 | 4 | 3 7 | 8 | 5.0000000000000000 | 4 | 5 | 4 7 | 8 | 5.0000000000000000 | 5 | 6 | 5 7 | 8 | 5.0000000000000000 | 6 | 7 | 6 7 | 8 | 5.0000000000000000 | 7 | 8 | 7 7 | 8 | 5.0000000000000000 | 8 | 9 | 8 7 | 8 | 5.0000000000000000 | 9 | 10 | 9 7 | 8 | 5.0000000000000000 | 10 | 11 | 10 7 | 8 | 6.0000000000000000 | 2 | 3 | 2 7 | 8 | 6.0000000000000000 | 3 | 4 | 3 7 | 8 | 6.0000000000000000 | 4 | 5 | 4 7 | 8 | 6.0000000000000000 | 5 | 6 | 5 7 | 8 | 6.0000000000000000 | 6 | 7 | 6 7 | 8 | 6.0000000000000000 | 7 | 8 | 7 7 | 8 | 6.0000000000000000 | 8 | 9 | 8 7 | 8 | 6.0000000000000000 | 9 | 10 | 9 7 | 8 | 6.0000000000000000 | 10 | 11 | 10 7 | 8 | 7.0000000000000000 | 2 | 3 | 2 7 | 8 | 7.0000000000000000 | 3 | 4 | 3 7 | 8 | 7.0000000000000000 | 4 | 5 | 4 7 | 8 | 7.0000000000000000 | 5 | 6 | 5 7 | 8 | 7.0000000000000000 | 6 | 7 | 6 7 | 8 | 7.0000000000000000 | 7 | 8 | 7 7 | 8 | 7.0000000000000000 | 8 | 9 | 8 7 | 8 | 7.0000000000000000 | 9 | 10 | 9 7 | 8 | 7.0000000000000000 | 10 | 11 | 10 7 | 8 | 8.0000000000000000 | 2 | 3 | 2 7 | 8 | 8.0000000000000000 | 3 | 4 | 3 7 | 8 | 8.0000000000000000 | 4 | 5 | 4 7 | 8 | 8.0000000000000000 | 5 | 6 | 5 7 | 8 | 8.0000000000000000 | 6 | 7 | 6 7 | 8 | 8.0000000000000000 | 7 | 8 | 7 7 | 8 | 8.0000000000000000 | 8 | 9 | 8 7 | 8 | 8.0000000000000000 | 9 | 10 | 9 7 | 8 | 8.0000000000000000 | 10 | 11 | 10 7 | 8 | 9.0000000000000000 | 2 | 3 | 2 7 | 8 | 9.0000000000000000 | 3 | 4 | 3 7 | 8 | 9.0000000000000000 | 4 | 5 | 4 7 | 8 | 9.0000000000000000 | 5 | 6 | 5 7 | 8 | 9.0000000000000000 | 6 | 7 | 6 7 | 8 | 9.0000000000000000 | 7 | 8 | 7 7 | 8 | 9.0000000000000000 | 8 | 9 | 8 7 | 8 | 9.0000000000000000 | 9 | 10 | 9 7 | 8 | 9.0000000000000000 | 10 | 11 | 10 8 | 9 | 3.0000000000000000 | 2 | 3 | 2 8 | 9 | 3.0000000000000000 | 3 | 4 | 3 8 | 9 | 3.0000000000000000 | 4 | 5 | 4 8 | 9 | 3.0000000000000000 | 5 | 6 | 5 8 | 9 | 3.0000000000000000 | 6 | 7 | 6 8 | 9 | 3.0000000000000000 | 7 | 8 | 7 8 | 9 | 3.0000000000000000 | 8 | 9 | 8 8 | 9 | 3.0000000000000000 | 9 | 10 | 9 8 | 9 | 3.0000000000000000 | 10 | 11 | 10 8 | 9 | 4.0000000000000000 | 2 | 3 | 2 8 | 9 | 4.0000000000000000 | 3 | 4 | 3 8 | 9 | 4.0000000000000000 | 4 | 5 | 4 8 | 9 | 4.0000000000000000 | 5 | 6 | 5 8 | 9 | 4.0000000000000000 | 6 | 7 | 6 8 | 9 | 4.0000000000000000 | 7 | 8 | 7 8 | 9 | 4.0000000000000000 | 8 | 9 | 8 8 | 9 | 4.0000000000000000 | 9 | 10 | 9 8 | 9 | 4.0000000000000000 | 10 | 11 | 10 8 | 9 | 5.0000000000000000 | 2 | 3 | 2 8 | 9 | 5.0000000000000000 | 3 | 4 | 3 8 | 9 | 5.0000000000000000 | 4 | 5 | 4 8 | 9 | 5.0000000000000000 | 5 | 6 | 5 8 | 9 | 5.0000000000000000 | 6 | 7 | 6 8 | 9 | 5.0000000000000000 | 7 | 8 | 7 8 | 9 | 5.0000000000000000 | 8 | 9 | 8 8 | 9 | 5.0000000000000000 | 9 | 10 | 9 8 | 9 | 5.0000000000000000 | 10 | 11 | 10 8 | 9 | 6.0000000000000000 | 2 | 3 | 2 8 | 9 | 6.0000000000000000 | 3 | 4 | 3 8 | 9 | 6.0000000000000000 | 4 | 5 | 4 8 | 9 | 6.0000000000000000 | 5 | 6 | 5 8 | 9 | 6.0000000000000000 | 6 | 7 | 6 8 | 9 | 6.0000000000000000 | 7 | 8 | 7 8 | 9 | 6.0000000000000000 | 8 | 9 | 8 8 | 9 | 6.0000000000000000 | 9 | 10 | 9 8 | 9 | 6.0000000000000000 | 10 | 11 | 10 8 | 9 | 7.0000000000000000 | 2 | 3 | 2 8 | 9 | 7.0000000000000000 | 3 | 4 | 3 8 | 9 | 7.0000000000000000 | 4 | 5 | 4 8 | 9 | 7.0000000000000000 | 5 | 6 | 5 8 | 9 | 7.0000000000000000 | 6 | 7 | 6 8 | 9 | 7.0000000000000000 | 7 | 8 | 7 8 | 9 | 7.0000000000000000 | 8 | 9 | 8 8 | 9 | 7.0000000000000000 | 9 | 10 | 9 8 | 9 | 7.0000000000000000 | 10 | 11 | 10 8 | 9 | 8.0000000000000000 | 2 | 3 | 2 8 | 9 | 8.0000000000000000 | 3 | 4 | 3 8 | 9 | 8.0000000000000000 | 4 | 5 | 4 8 | 9 | 8.0000000000000000 | 5 | 6 | 5 8 | 9 | 8.0000000000000000 | 6 | 7 | 6 8 | 9 | 8.0000000000000000 | 7 | 8 | 7 8 | 9 | 8.0000000000000000 | 8 | 9 | 8 8 | 9 | 8.0000000000000000 | 9 | 10 | 9 8 | 9 | 8.0000000000000000 | 10 | 11 | 10 8 | 9 | 9.0000000000000000 | 2 | 3 | 2 8 | 9 | 9.0000000000000000 | 3 | 4 | 3 8 | 9 | 9.0000000000000000 | 4 | 5 | 4 8 | 9 | 9.0000000000000000 | 5 | 6 | 5 8 | 9 | 9.0000000000000000 | 6 | 7 | 6 8 | 9 | 9.0000000000000000 | 7 | 8 | 7 8 | 9 | 9.0000000000000000 | 8 | 9 | 8 8 | 9 | 9.0000000000000000 | 9 | 10 | 9 8 | 9 | 9.0000000000000000 | 10 | 11 | 10 9 | 10 | 3.0000000000000000 | 2 | 3 | 2 9 | 10 | 3.0000000000000000 | 3 | 4 | 3 9 | 10 | 3.0000000000000000 | 4 | 5 | 4 9 | 10 | 3.0000000000000000 | 5 | 6 | 5 9 | 10 | 3.0000000000000000 | 6 | 7 | 6 9 | 10 | 3.0000000000000000 | 7 | 8 | 7 9 | 10 | 3.0000000000000000 | 8 | 9 | 8 9 | 10 | 3.0000000000000000 | 9 | 10 | 9 9 | 10 | 3.0000000000000000 | 10 | 11 | 10 9 | 10 | 4.0000000000000000 | 2 | 3 | 2 9 | 10 | 4.0000000000000000 | 3 | 4 | 3 9 | 10 | 4.0000000000000000 | 4 | 5 | 4 9 | 10 | 4.0000000000000000 | 5 | 6 | 5 9 | 10 | 4.0000000000000000 | 6 | 7 | 6 9 | 10 | 4.0000000000000000 | 7 | 8 | 7 9 | 10 | 4.0000000000000000 | 8 | 9 | 8 9 | 10 | 4.0000000000000000 | 9 | 10 | 9 9 | 10 | 4.0000000000000000 | 10 | 11 | 10 9 | 10 | 5.0000000000000000 | 2 | 3 | 2 9 | 10 | 5.0000000000000000 | 3 | 4 | 3 9 | 10 | 5.0000000000000000 | 4 | 5 | 4 9 | 10 | 5.0000000000000000 | 5 | 6 | 5 9 | 10 | 5.0000000000000000 | 6 | 7 | 6 9 | 10 | 5.0000000000000000 | 7 | 8 | 7 9 | 10 | 5.0000000000000000 | 8 | 9 | 8 9 | 10 | 5.0000000000000000 | 9 | 10 | 9 9 | 10 | 5.0000000000000000 | 10 | 11 | 10 9 | 10 | 6.0000000000000000 | 2 | 3 | 2 9 | 10 | 6.0000000000000000 | 3 | 4 | 3 9 | 10 | 6.0000000000000000 | 4 | 5 | 4 9 | 10 | 6.0000000000000000 | 5 | 6 | 5 9 | 10 | 6.0000000000000000 | 6 | 7 | 6 9 | 10 | 6.0000000000000000 | 7 | 8 | 7 9 | 10 | 6.0000000000000000 | 8 | 9 | 8 9 | 10 | 6.0000000000000000 | 9 | 10 | 9 9 | 10 | 6.0000000000000000 | 10 | 11 | 10 9 | 10 | 7.0000000000000000 | 2 | 3 | 2 9 | 10 | 7.0000000000000000 | 3 | 4 | 3 9 | 10 | 7.0000000000000000 | 4 | 5 | 4 9 | 10 | 7.0000000000000000 | 5 | 6 | 5 9 | 10 | 7.0000000000000000 | 6 | 7 | 6 9 | 10 | 7.0000000000000000 | 7 | 8 | 7 9 | 10 | 7.0000000000000000 | 8 | 9 | 8 9 | 10 | 7.0000000000000000 | 9 | 10 | 9 9 | 10 | 7.0000000000000000 | 10 | 11 | 10 9 | 10 | 8.0000000000000000 | 2 | 3 | 2 9 | 10 | 8.0000000000000000 | 3 | 4 | 3 9 | 10 | 8.0000000000000000 | 4 | 5 | 4 9 | 10 | 8.0000000000000000 | 5 | 6 | 5 9 | 10 | 8.0000000000000000 | 6 | 7 | 6 9 | 10 | 8.0000000000000000 | 7 | 8 | 7 9 | 10 | 8.0000000000000000 | 8 | 9 | 8 9 | 10 | 8.0000000000000000 | 9 | 10 | 9 9 | 10 | 8.0000000000000000 | 10 | 11 | 10 9 | 10 | 9.0000000000000000 | 2 | 3 | 2 9 | 10 | 9.0000000000000000 | 3 | 4 | 3 9 | 10 | 9.0000000000000000 | 4 | 5 | 4 9 | 10 | 9.0000000000000000 | 5 | 6 | 5 9 | 10 | 9.0000000000000000 | 6 | 7 | 6 9 | 10 | 9.0000000000000000 | 7 | 8 | 7 9 | 10 | 9.0000000000000000 | 8 | 9 | 8 9 | 10 | 9.0000000000000000 | 9 | 10 | 9 9 | 10 | 9.0000000000000000 | 10 | 11 | 10 10 | 11 | 3.0000000000000000 | 2 | 3 | 2 10 | 11 | 3.0000000000000000 | 3 | 4 | 3 10 | 11 | 3.0000000000000000 | 4 | 5 | 4 10 | 11 | 3.0000000000000000 | 5 | 6 | 5 10 | 11 | 3.0000000000000000 | 6 | 7 | 6 10 | 11 | 3.0000000000000000 | 7 | 8 | 7 10 | 11 | 3.0000000000000000 | 8 | 9 | 8 10 | 11 | 3.0000000000000000 | 9 | 10 | 9 10 | 11 | 3.0000000000000000 | 10 | 11 | 10 10 | 11 | 4.0000000000000000 | 2 | 3 | 2 10 | 11 | 4.0000000000000000 | 3 | 4 | 3 10 | 11 | 4.0000000000000000 | 4 | 5 | 4 10 | 11 | 4.0000000000000000 | 5 | 6 | 5 10 | 11 | 4.0000000000000000 | 6 | 7 | 6 10 | 11 | 4.0000000000000000 | 7 | 8 | 7 10 | 11 | 4.0000000000000000 | 8 | 9 | 8 10 | 11 | 4.0000000000000000 | 9 | 10 | 9 10 | 11 | 4.0000000000000000 | 10 | 11 | 10 10 | 11 | 5.0000000000000000 | 2 | 3 | 2 10 | 11 | 5.0000000000000000 | 3 | 4 | 3 10 | 11 | 5.0000000000000000 | 4 | 5 | 4 10 | 11 | 5.0000000000000000 | 5 | 6 | 5 10 | 11 | 5.0000000000000000 | 6 | 7 | 6 10 | 11 | 5.0000000000000000 | 7 | 8 | 7 10 | 11 | 5.0000000000000000 | 8 | 9 | 8 10 | 11 | 5.0000000000000000 | 9 | 10 | 9 10 | 11 | 5.0000000000000000 | 10 | 11 | 10 10 | 11 | 6.0000000000000000 | 2 | 3 | 2 10 | 11 | 6.0000000000000000 | 3 | 4 | 3 10 | 11 | 6.0000000000000000 | 4 | 5 | 4 10 | 11 | 6.0000000000000000 | 5 | 6 | 5 10 | 11 | 6.0000000000000000 | 6 | 7 | 6 10 | 11 | 6.0000000000000000 | 7 | 8 | 7 10 | 11 | 6.0000000000000000 | 8 | 9 | 8 10 | 11 | 6.0000000000000000 | 9 | 10 | 9 10 | 11 | 6.0000000000000000 | 10 | 11 | 10 10 | 11 | 7.0000000000000000 | 2 | 3 | 2 10 | 11 | 7.0000000000000000 | 3 | 4 | 3 10 | 11 | 7.0000000000000000 | 4 | 5 | 4 10 | 11 | 7.0000000000000000 | 5 | 6 | 5 10 | 11 | 7.0000000000000000 | 6 | 7 | 6 10 | 11 | 7.0000000000000000 | 7 | 8 | 7 10 | 11 | 7.0000000000000000 | 8 | 9 | 8 10 | 11 | 7.0000000000000000 | 9 | 10 | 9 10 | 11 | 7.0000000000000000 | 10 | 11 | 10 10 | 11 | 8.0000000000000000 | 2 | 3 | 2 10 | 11 | 8.0000000000000000 | 3 | 4 | 3 10 | 11 | 8.0000000000000000 | 4 | 5 | 4 10 | 11 | 8.0000000000000000 | 5 | 6 | 5 10 | 11 | 8.0000000000000000 | 6 | 7 | 6 10 | 11 | 8.0000000000000000 | 7 | 8 | 7 10 | 11 | 8.0000000000000000 | 8 | 9 | 8 10 | 11 | 8.0000000000000000 | 9 | 10 | 9 10 | 11 | 8.0000000000000000 | 10 | 11 | 10 10 | 11 | 9.0000000000000000 | 2 | 3 | 2 10 | 11 | 9.0000000000000000 | 3 | 4 | 3 10 | 11 | 9.0000000000000000 | 4 | 5 | 4 10 | 11 | 9.0000000000000000 | 5 | 6 | 5 10 | 11 | 9.0000000000000000 | 6 | 7 | 6 10 | 11 | 9.0000000000000000 | 7 | 8 | 7 10 | 11 | 9.0000000000000000 | 8 | 9 | 8 10 | 11 | 9.0000000000000000 | 9 | 10 | 9 10 | 11 | 9.0000000000000000 | 10 | 11 | 10 (630 rows) -- -- Test for wrong results in window functions under joins #2 -- select * from ( SELECT cup.*, SUM(t.d) OVER(PARTITION BY t.b) FROM ( SELECT bfv_joins_bar.*, AVG(t.b) OVER(PARTITION BY t.a ORDER BY t.b desc) AS e FROM t,bfv_joins_bar ) AS cup, t WHERE cup.e < 10 GROUP BY cup.c,cup.d, cup.e ,t.d, t.b) i order by 1, 2, 3, 4; c | d | e | sum ----+----+--------------------+----- 1 | 2 | 3.0000000000000000 | 140 1 | 2 | 3.0000000000000000 | 210 1 | 2 | 3.0000000000000000 | 280 1 | 2 | 3.0000000000000000 | 350 1 | 2 | 3.0000000000000000 | 420 1 | 2 | 3.0000000000000000 | 490 1 | 2 | 3.0000000000000000 | 560 1 | 2 | 3.0000000000000000 | 630 1 | 2 | 3.0000000000000000 | 700 1 | 2 | 4.0000000000000000 | 140 1 | 2 | 4.0000000000000000 | 210 1 | 2 | 4.0000000000000000 | 280 1 | 2 | 4.0000000000000000 | 350 1 | 2 | 4.0000000000000000 | 420 1 | 2 | 4.0000000000000000 | 490 1 | 2 | 4.0000000000000000 | 560 1 | 2 | 4.0000000000000000 | 630 1 | 2 | 4.0000000000000000 | 700 1 | 2 | 5.0000000000000000 | 140 1 | 2 | 5.0000000000000000 | 210 1 | 2 | 5.0000000000000000 | 280 1 | 2 | 5.0000000000000000 | 350 1 | 2 | 5.0000000000000000 | 420 1 | 2 | 5.0000000000000000 | 490 1 | 2 | 5.0000000000000000 | 560 1 | 2 | 5.0000000000000000 | 630 1 | 2 | 5.0000000000000000 | 700 1 | 2 | 6.0000000000000000 | 140 1 | 2 | 6.0000000000000000 | 210 1 | 2 | 6.0000000000000000 | 280 1 | 2 | 6.0000000000000000 | 350 1 | 2 | 6.0000000000000000 | 420 1 | 2 | 6.0000000000000000 | 490 1 | 2 | 6.0000000000000000 | 560 1 | 2 | 6.0000000000000000 | 630 1 | 2 | 6.0000000000000000 | 700 1 | 2 | 7.0000000000000000 | 140 1 | 2 | 7.0000000000000000 | 210 1 | 2 | 7.0000000000000000 | 280 1 | 2 | 7.0000000000000000 | 350 1 | 2 | 7.0000000000000000 | 420 1 | 2 | 7.0000000000000000 | 490 1 | 2 | 7.0000000000000000 | 560 1 | 2 | 7.0000000000000000 | 630 1 | 2 | 7.0000000000000000 | 700 1 | 2 | 8.0000000000000000 | 140 1 | 2 | 8.0000000000000000 | 210 1 | 2 | 8.0000000000000000 | 280 1 | 2 | 8.0000000000000000 | 350 1 | 2 | 8.0000000000000000 | 420 1 | 2 | 8.0000000000000000 | 490 1 | 2 | 8.0000000000000000 | 560 1 | 2 | 8.0000000000000000 | 630 1 | 2 | 8.0000000000000000 | 700 1 | 2 | 9.0000000000000000 | 140 1 | 2 | 9.0000000000000000 | 210 1 | 2 | 9.0000000000000000 | 280 1 | 2 | 9.0000000000000000 | 350 1 | 2 | 9.0000000000000000 | 420 1 | 2 | 9.0000000000000000 | 490 1 | 2 | 9.0000000000000000 | 560 1 | 2 | 9.0000000000000000 | 630 1 | 2 | 9.0000000000000000 | 700 2 | 3 | 3.0000000000000000 | 140 2 | 3 | 3.0000000000000000 | 210 2 | 3 | 3.0000000000000000 | 280 2 | 3 | 3.0000000000000000 | 350 2 | 3 | 3.0000000000000000 | 420 2 | 3 | 3.0000000000000000 | 490 2 | 3 | 3.0000000000000000 | 560 2 | 3 | 3.0000000000000000 | 630 2 | 3 | 3.0000000000000000 | 700 2 | 3 | 4.0000000000000000 | 140 2 | 3 | 4.0000000000000000 | 210 2 | 3 | 4.0000000000000000 | 280 2 | 3 | 4.0000000000000000 | 350 2 | 3 | 4.0000000000000000 | 420 2 | 3 | 4.0000000000000000 | 490 2 | 3 | 4.0000000000000000 | 560 2 | 3 | 4.0000000000000000 | 630 2 | 3 | 4.0000000000000000 | 700 2 | 3 | 5.0000000000000000 | 140 2 | 3 | 5.0000000000000000 | 210 2 | 3 | 5.0000000000000000 | 280 2 | 3 | 5.0000000000000000 | 350 2 | 3 | 5.0000000000000000 | 420 2 | 3 | 5.0000000000000000 | 490 2 | 3 | 5.0000000000000000 | 560 2 | 3 | 5.0000000000000000 | 630 2 | 3 | 5.0000000000000000 | 700 2 | 3 | 6.0000000000000000 | 140 2 | 3 | 6.0000000000000000 | 210 2 | 3 | 6.0000000000000000 | 280 2 | 3 | 6.0000000000000000 | 350 2 | 3 | 6.0000000000000000 | 420 2 | 3 | 6.0000000000000000 | 490 2 | 3 | 6.0000000000000000 | 560 2 | 3 | 6.0000000000000000 | 630 2 | 3 | 6.0000000000000000 | 700 2 | 3 | 7.0000000000000000 | 140 2 | 3 | 7.0000000000000000 | 210 2 | 3 | 7.0000000000000000 | 280 2 | 3 | 7.0000000000000000 | 350 2 | 3 | 7.0000000000000000 | 420 2 | 3 | 7.0000000000000000 | 490 2 | 3 | 7.0000000000000000 | 560 2 | 3 | 7.0000000000000000 | 630 2 | 3 | 7.0000000000000000 | 700 2 | 3 | 8.0000000000000000 | 140 2 | 3 | 8.0000000000000000 | 210 2 | 3 | 8.0000000000000000 | 280 2 | 3 | 8.0000000000000000 | 350 2 | 3 | 8.0000000000000000 | 420 2 | 3 | 8.0000000000000000 | 490 2 | 3 | 8.0000000000000000 | 560 2 | 3 | 8.0000000000000000 | 630 2 | 3 | 8.0000000000000000 | 700 2 | 3 | 9.0000000000000000 | 140 2 | 3 | 9.0000000000000000 | 210 2 | 3 | 9.0000000000000000 | 280 2 | 3 | 9.0000000000000000 | 350 2 | 3 | 9.0000000000000000 | 420 2 | 3 | 9.0000000000000000 | 490 2 | 3 | 9.0000000000000000 | 560 2 | 3 | 9.0000000000000000 | 630 2 | 3 | 9.0000000000000000 | 700 3 | 4 | 3.0000000000000000 | 140 3 | 4 | 3.0000000000000000 | 210 3 | 4 | 3.0000000000000000 | 280 3 | 4 | 3.0000000000000000 | 350 3 | 4 | 3.0000000000000000 | 420 3 | 4 | 3.0000000000000000 | 490 3 | 4 | 3.0000000000000000 | 560 3 | 4 | 3.0000000000000000 | 630 3 | 4 | 3.0000000000000000 | 700 3 | 4 | 4.0000000000000000 | 140 3 | 4 | 4.0000000000000000 | 210 3 | 4 | 4.0000000000000000 | 280 3 | 4 | 4.0000000000000000 | 350 3 | 4 | 4.0000000000000000 | 420 3 | 4 | 4.0000000000000000 | 490 3 | 4 | 4.0000000000000000 | 560 3 | 4 | 4.0000000000000000 | 630 3 | 4 | 4.0000000000000000 | 700 3 | 4 | 5.0000000000000000 | 140 3 | 4 | 5.0000000000000000 | 210 3 | 4 | 5.0000000000000000 | 280 3 | 4 | 5.0000000000000000 | 350 3 | 4 | 5.0000000000000000 | 420 3 | 4 | 5.0000000000000000 | 490 3 | 4 | 5.0000000000000000 | 560 3 | 4 | 5.0000000000000000 | 630 3 | 4 | 5.0000000000000000 | 700 3 | 4 | 6.0000000000000000 | 140 3 | 4 | 6.0000000000000000 | 210 3 | 4 | 6.0000000000000000 | 280 3 | 4 | 6.0000000000000000 | 350 3 | 4 | 6.0000000000000000 | 420 3 | 4 | 6.0000000000000000 | 490 3 | 4 | 6.0000000000000000 | 560 3 | 4 | 6.0000000000000000 | 630 3 | 4 | 6.0000000000000000 | 700 3 | 4 | 7.0000000000000000 | 140 3 | 4 | 7.0000000000000000 | 210 3 | 4 | 7.0000000000000000 | 280 3 | 4 | 7.0000000000000000 | 350 3 | 4 | 7.0000000000000000 | 420 3 | 4 | 7.0000000000000000 | 490 3 | 4 | 7.0000000000000000 | 560 3 | 4 | 7.0000000000000000 | 630 3 | 4 | 7.0000000000000000 | 700 3 | 4 | 8.0000000000000000 | 140 3 | 4 | 8.0000000000000000 | 210 3 | 4 | 8.0000000000000000 | 280 3 | 4 | 8.0000000000000000 | 350 3 | 4 | 8.0000000000000000 | 420 3 | 4 | 8.0000000000000000 | 490 3 | 4 | 8.0000000000000000 | 560 3 | 4 | 8.0000000000000000 | 630 3 | 4 | 8.0000000000000000 | 700 3 | 4 | 9.0000000000000000 | 140 3 | 4 | 9.0000000000000000 | 210 3 | 4 | 9.0000000000000000 | 280 3 | 4 | 9.0000000000000000 | 350 3 | 4 | 9.0000000000000000 | 420 3 | 4 | 9.0000000000000000 | 490 3 | 4 | 9.0000000000000000 | 560 3 | 4 | 9.0000000000000000 | 630 3 | 4 | 9.0000000000000000 | 700 4 | 5 | 3.0000000000000000 | 140 4 | 5 | 3.0000000000000000 | 210 4 | 5 | 3.0000000000000000 | 280 4 | 5 | 3.0000000000000000 | 350 4 | 5 | 3.0000000000000000 | 420 4 | 5 | 3.0000000000000000 | 490 4 | 5 | 3.0000000000000000 | 560 4 | 5 | 3.0000000000000000 | 630 4 | 5 | 3.0000000000000000 | 700 4 | 5 | 4.0000000000000000 | 140 4 | 5 | 4.0000000000000000 | 210 4 | 5 | 4.0000000000000000 | 280 4 | 5 | 4.0000000000000000 | 350 4 | 5 | 4.0000000000000000 | 420 4 | 5 | 4.0000000000000000 | 490 4 | 5 | 4.0000000000000000 | 560 4 | 5 | 4.0000000000000000 | 630 4 | 5 | 4.0000000000000000 | 700 4 | 5 | 5.0000000000000000 | 140 4 | 5 | 5.0000000000000000 | 210 4 | 5 | 5.0000000000000000 | 280 4 | 5 | 5.0000000000000000 | 350 4 | 5 | 5.0000000000000000 | 420 4 | 5 | 5.0000000000000000 | 490 4 | 5 | 5.0000000000000000 | 560 4 | 5 | 5.0000000000000000 | 630 4 | 5 | 5.0000000000000000 | 700 4 | 5 | 6.0000000000000000 | 140 4 | 5 | 6.0000000000000000 | 210 4 | 5 | 6.0000000000000000 | 280 4 | 5 | 6.0000000000000000 | 350 4 | 5 | 6.0000000000000000 | 420 4 | 5 | 6.0000000000000000 | 490 4 | 5 | 6.0000000000000000 | 560 4 | 5 | 6.0000000000000000 | 630 4 | 5 | 6.0000000000000000 | 700 4 | 5 | 7.0000000000000000 | 140 4 | 5 | 7.0000000000000000 | 210 4 | 5 | 7.0000000000000000 | 280 4 | 5 | 7.0000000000000000 | 350 4 | 5 | 7.0000000000000000 | 420 4 | 5 | 7.0000000000000000 | 490 4 | 5 | 7.0000000000000000 | 560 4 | 5 | 7.0000000000000000 | 630 4 | 5 | 7.0000000000000000 | 700 4 | 5 | 8.0000000000000000 | 140 4 | 5 | 8.0000000000000000 | 210 4 | 5 | 8.0000000000000000 | 280 4 | 5 | 8.0000000000000000 | 350 4 | 5 | 8.0000000000000000 | 420 4 | 5 | 8.0000000000000000 | 490 4 | 5 | 8.0000000000000000 | 560 4 | 5 | 8.0000000000000000 | 630 4 | 5 | 8.0000000000000000 | 700 4 | 5 | 9.0000000000000000 | 140 4 | 5 | 9.0000000000000000 | 210 4 | 5 | 9.0000000000000000 | 280 4 | 5 | 9.0000000000000000 | 350 4 | 5 | 9.0000000000000000 | 420 4 | 5 | 9.0000000000000000 | 490 4 | 5 | 9.0000000000000000 | 560 4 | 5 | 9.0000000000000000 | 630 4 | 5 | 9.0000000000000000 | 700 5 | 6 | 3.0000000000000000 | 140 5 | 6 | 3.0000000000000000 | 210 5 | 6 | 3.0000000000000000 | 280 5 | 6 | 3.0000000000000000 | 350 5 | 6 | 3.0000000000000000 | 420 5 | 6 | 3.0000000000000000 | 490 5 | 6 | 3.0000000000000000 | 560 5 | 6 | 3.0000000000000000 | 630 5 | 6 | 3.0000000000000000 | 700 5 | 6 | 4.0000000000000000 | 140 5 | 6 | 4.0000000000000000 | 210 5 | 6 | 4.0000000000000000 | 280 5 | 6 | 4.0000000000000000 | 350 5 | 6 | 4.0000000000000000 | 420 5 | 6 | 4.0000000000000000 | 490 5 | 6 | 4.0000000000000000 | 560 5 | 6 | 4.0000000000000000 | 630 5 | 6 | 4.0000000000000000 | 700 5 | 6 | 5.0000000000000000 | 140 5 | 6 | 5.0000000000000000 | 210 5 | 6 | 5.0000000000000000 | 280 5 | 6 | 5.0000000000000000 | 350 5 | 6 | 5.0000000000000000 | 420 5 | 6 | 5.0000000000000000 | 490 5 | 6 | 5.0000000000000000 | 560 5 | 6 | 5.0000000000000000 | 630 5 | 6 | 5.0000000000000000 | 700 5 | 6 | 6.0000000000000000 | 140 5 | 6 | 6.0000000000000000 | 210 5 | 6 | 6.0000000000000000 | 280 5 | 6 | 6.0000000000000000 | 350 5 | 6 | 6.0000000000000000 | 420 5 | 6 | 6.0000000000000000 | 490 5 | 6 | 6.0000000000000000 | 560 5 | 6 | 6.0000000000000000 | 630 5 | 6 | 6.0000000000000000 | 700 5 | 6 | 7.0000000000000000 | 140 5 | 6 | 7.0000000000000000 | 210 5 | 6 | 7.0000000000000000 | 280 5 | 6 | 7.0000000000000000 | 350 5 | 6 | 7.0000000000000000 | 420 5 | 6 | 7.0000000000000000 | 490 5 | 6 | 7.0000000000000000 | 560 5 | 6 | 7.0000000000000000 | 630 5 | 6 | 7.0000000000000000 | 700 5 | 6 | 8.0000000000000000 | 140 5 | 6 | 8.0000000000000000 | 210 5 | 6 | 8.0000000000000000 | 280 5 | 6 | 8.0000000000000000 | 350 5 | 6 | 8.0000000000000000 | 420 5 | 6 | 8.0000000000000000 | 490 5 | 6 | 8.0000000000000000 | 560 5 | 6 | 8.0000000000000000 | 630 5 | 6 | 8.0000000000000000 | 700 5 | 6 | 9.0000000000000000 | 140 5 | 6 | 9.0000000000000000 | 210 5 | 6 | 9.0000000000000000 | 280 5 | 6 | 9.0000000000000000 | 350 5 | 6 | 9.0000000000000000 | 420 5 | 6 | 9.0000000000000000 | 490 5 | 6 | 9.0000000000000000 | 560 5 | 6 | 9.0000000000000000 | 630 5 | 6 | 9.0000000000000000 | 700 6 | 7 | 3.0000000000000000 | 140 6 | 7 | 3.0000000000000000 | 210 6 | 7 | 3.0000000000000000 | 280 6 | 7 | 3.0000000000000000 | 350 6 | 7 | 3.0000000000000000 | 420 6 | 7 | 3.0000000000000000 | 490 6 | 7 | 3.0000000000000000 | 560 6 | 7 | 3.0000000000000000 | 630 6 | 7 | 3.0000000000000000 | 700 6 | 7 | 4.0000000000000000 | 140 6 | 7 | 4.0000000000000000 | 210 6 | 7 | 4.0000000000000000 | 280 6 | 7 | 4.0000000000000000 | 350 6 | 7 | 4.0000000000000000 | 420 6 | 7 | 4.0000000000000000 | 490 6 | 7 | 4.0000000000000000 | 560 6 | 7 | 4.0000000000000000 | 630 6 | 7 | 4.0000000000000000 | 700 6 | 7 | 5.0000000000000000 | 140 6 | 7 | 5.0000000000000000 | 210 6 | 7 | 5.0000000000000000 | 280 6 | 7 | 5.0000000000000000 | 350 6 | 7 | 5.0000000000000000 | 420 6 | 7 | 5.0000000000000000 | 490 6 | 7 | 5.0000000000000000 | 560 6 | 7 | 5.0000000000000000 | 630 6 | 7 | 5.0000000000000000 | 700 6 | 7 | 6.0000000000000000 | 140 6 | 7 | 6.0000000000000000 | 210 6 | 7 | 6.0000000000000000 | 280 6 | 7 | 6.0000000000000000 | 350 6 | 7 | 6.0000000000000000 | 420 6 | 7 | 6.0000000000000000 | 490 6 | 7 | 6.0000000000000000 | 560 6 | 7 | 6.0000000000000000 | 630 6 | 7 | 6.0000000000000000 | 700 6 | 7 | 7.0000000000000000 | 140 6 | 7 | 7.0000000000000000 | 210 6 | 7 | 7.0000000000000000 | 280 6 | 7 | 7.0000000000000000 | 350 6 | 7 | 7.0000000000000000 | 420 6 | 7 | 7.0000000000000000 | 490 6 | 7 | 7.0000000000000000 | 560 6 | 7 | 7.0000000000000000 | 630 6 | 7 | 7.0000000000000000 | 700 6 | 7 | 8.0000000000000000 | 140 6 | 7 | 8.0000000000000000 | 210 6 | 7 | 8.0000000000000000 | 280 6 | 7 | 8.0000000000000000 | 350 6 | 7 | 8.0000000000000000 | 420 6 | 7 | 8.0000000000000000 | 490 6 | 7 | 8.0000000000000000 | 560 6 | 7 | 8.0000000000000000 | 630 6 | 7 | 8.0000000000000000 | 700 6 | 7 | 9.0000000000000000 | 140 6 | 7 | 9.0000000000000000 | 210 6 | 7 | 9.0000000000000000 | 280 6 | 7 | 9.0000000000000000 | 350 6 | 7 | 9.0000000000000000 | 420 6 | 7 | 9.0000000000000000 | 490 6 | 7 | 9.0000000000000000 | 560 6 | 7 | 9.0000000000000000 | 630 6 | 7 | 9.0000000000000000 | 700 7 | 8 | 3.0000000000000000 | 140 7 | 8 | 3.0000000000000000 | 210 7 | 8 | 3.0000000000000000 | 280 7 | 8 | 3.0000000000000000 | 350 7 | 8 | 3.0000000000000000 | 420 7 | 8 | 3.0000000000000000 | 490 7 | 8 | 3.0000000000000000 | 560 7 | 8 | 3.0000000000000000 | 630 7 | 8 | 3.0000000000000000 | 700 7 | 8 | 4.0000000000000000 | 140 7 | 8 | 4.0000000000000000 | 210 7 | 8 | 4.0000000000000000 | 280 7 | 8 | 4.0000000000000000 | 350 7 | 8 | 4.0000000000000000 | 420 7 | 8 | 4.0000000000000000 | 490 7 | 8 | 4.0000000000000000 | 560 7 | 8 | 4.0000000000000000 | 630 7 | 8 | 4.0000000000000000 | 700 7 | 8 | 5.0000000000000000 | 140 7 | 8 | 5.0000000000000000 | 210 7 | 8 | 5.0000000000000000 | 280 7 | 8 | 5.0000000000000000 | 350 7 | 8 | 5.0000000000000000 | 420 7 | 8 | 5.0000000000000000 | 490 7 | 8 | 5.0000000000000000 | 560 7 | 8 | 5.0000000000000000 | 630 7 | 8 | 5.0000000000000000 | 700 7 | 8 | 6.0000000000000000 | 140 7 | 8 | 6.0000000000000000 | 210 7 | 8 | 6.0000000000000000 | 280 7 | 8 | 6.0000000000000000 | 350 7 | 8 | 6.0000000000000000 | 420 7 | 8 | 6.0000000000000000 | 490 7 | 8 | 6.0000000000000000 | 560 7 | 8 | 6.0000000000000000 | 630 7 | 8 | 6.0000000000000000 | 700 7 | 8 | 7.0000000000000000 | 140 7 | 8 | 7.0000000000000000 | 210 7 | 8 | 7.0000000000000000 | 280 7 | 8 | 7.0000000000000000 | 350 7 | 8 | 7.0000000000000000 | 420 7 | 8 | 7.0000000000000000 | 490 7 | 8 | 7.0000000000000000 | 560 7 | 8 | 7.0000000000000000 | 630 7 | 8 | 7.0000000000000000 | 700 7 | 8 | 8.0000000000000000 | 140 7 | 8 | 8.0000000000000000 | 210 7 | 8 | 8.0000000000000000 | 280 7 | 8 | 8.0000000000000000 | 350 7 | 8 | 8.0000000000000000 | 420 7 | 8 | 8.0000000000000000 | 490 7 | 8 | 8.0000000000000000 | 560 7 | 8 | 8.0000000000000000 | 630 7 | 8 | 8.0000000000000000 | 700 7 | 8 | 9.0000000000000000 | 140 7 | 8 | 9.0000000000000000 | 210 7 | 8 | 9.0000000000000000 | 280 7 | 8 | 9.0000000000000000 | 350 7 | 8 | 9.0000000000000000 | 420 7 | 8 | 9.0000000000000000 | 490 7 | 8 | 9.0000000000000000 | 560 7 | 8 | 9.0000000000000000 | 630 7 | 8 | 9.0000000000000000 | 700 8 | 9 | 3.0000000000000000 | 140 8 | 9 | 3.0000000000000000 | 210 8 | 9 | 3.0000000000000000 | 280 8 | 9 | 3.0000000000000000 | 350 8 | 9 | 3.0000000000000000 | 420 8 | 9 | 3.0000000000000000 | 490 8 | 9 | 3.0000000000000000 | 560 8 | 9 | 3.0000000000000000 | 630 8 | 9 | 3.0000000000000000 | 700 8 | 9 | 4.0000000000000000 | 140 8 | 9 | 4.0000000000000000 | 210 8 | 9 | 4.0000000000000000 | 280 8 | 9 | 4.0000000000000000 | 350 8 | 9 | 4.0000000000000000 | 420 8 | 9 | 4.0000000000000000 | 490 8 | 9 | 4.0000000000000000 | 560 8 | 9 | 4.0000000000000000 | 630 8 | 9 | 4.0000000000000000 | 700 8 | 9 | 5.0000000000000000 | 140 8 | 9 | 5.0000000000000000 | 210 8 | 9 | 5.0000000000000000 | 280 8 | 9 | 5.0000000000000000 | 350 8 | 9 | 5.0000000000000000 | 420 8 | 9 | 5.0000000000000000 | 490 8 | 9 | 5.0000000000000000 | 560 8 | 9 | 5.0000000000000000 | 630 8 | 9 | 5.0000000000000000 | 700 8 | 9 | 6.0000000000000000 | 140 8 | 9 | 6.0000000000000000 | 210 8 | 9 | 6.0000000000000000 | 280 8 | 9 | 6.0000000000000000 | 350 8 | 9 | 6.0000000000000000 | 420 8 | 9 | 6.0000000000000000 | 490 8 | 9 | 6.0000000000000000 | 560 8 | 9 | 6.0000000000000000 | 630 8 | 9 | 6.0000000000000000 | 700 8 | 9 | 7.0000000000000000 | 140 8 | 9 | 7.0000000000000000 | 210 8 | 9 | 7.0000000000000000 | 280 8 | 9 | 7.0000000000000000 | 350 8 | 9 | 7.0000000000000000 | 420 8 | 9 | 7.0000000000000000 | 490 8 | 9 | 7.0000000000000000 | 560 8 | 9 | 7.0000000000000000 | 630 8 | 9 | 7.0000000000000000 | 700 8 | 9 | 8.0000000000000000 | 140 8 | 9 | 8.0000000000000000 | 210 8 | 9 | 8.0000000000000000 | 280 8 | 9 | 8.0000000000000000 | 350 8 | 9 | 8.0000000000000000 | 420 8 | 9 | 8.0000000000000000 | 490 8 | 9 | 8.0000000000000000 | 560 8 | 9 | 8.0000000000000000 | 630 8 | 9 | 8.0000000000000000 | 700 8 | 9 | 9.0000000000000000 | 140 8 | 9 | 9.0000000000000000 | 210 8 | 9 | 9.0000000000000000 | 280 8 | 9 | 9.0000000000000000 | 350 8 | 9 | 9.0000000000000000 | 420 8 | 9 | 9.0000000000000000 | 490 8 | 9 | 9.0000000000000000 | 560 8 | 9 | 9.0000000000000000 | 630 8 | 9 | 9.0000000000000000 | 700 9 | 10 | 3.0000000000000000 | 140 9 | 10 | 3.0000000000000000 | 210 9 | 10 | 3.0000000000000000 | 280 9 | 10 | 3.0000000000000000 | 350 9 | 10 | 3.0000000000000000 | 420 9 | 10 | 3.0000000000000000 | 490 9 | 10 | 3.0000000000000000 | 560 9 | 10 | 3.0000000000000000 | 630 9 | 10 | 3.0000000000000000 | 700 9 | 10 | 4.0000000000000000 | 140 9 | 10 | 4.0000000000000000 | 210 9 | 10 | 4.0000000000000000 | 280 9 | 10 | 4.0000000000000000 | 350 9 | 10 | 4.0000000000000000 | 420 9 | 10 | 4.0000000000000000 | 490 9 | 10 | 4.0000000000000000 | 560 9 | 10 | 4.0000000000000000 | 630 9 | 10 | 4.0000000000000000 | 700 9 | 10 | 5.0000000000000000 | 140 9 | 10 | 5.0000000000000000 | 210 9 | 10 | 5.0000000000000000 | 280 9 | 10 | 5.0000000000000000 | 350 9 | 10 | 5.0000000000000000 | 420 9 | 10 | 5.0000000000000000 | 490 9 | 10 | 5.0000000000000000 | 560 9 | 10 | 5.0000000000000000 | 630 9 | 10 | 5.0000000000000000 | 700 9 | 10 | 6.0000000000000000 | 140 9 | 10 | 6.0000000000000000 | 210 9 | 10 | 6.0000000000000000 | 280 9 | 10 | 6.0000000000000000 | 350 9 | 10 | 6.0000000000000000 | 420 9 | 10 | 6.0000000000000000 | 490 9 | 10 | 6.0000000000000000 | 560 9 | 10 | 6.0000000000000000 | 630 9 | 10 | 6.0000000000000000 | 700 9 | 10 | 7.0000000000000000 | 140 9 | 10 | 7.0000000000000000 | 210 9 | 10 | 7.0000000000000000 | 280 9 | 10 | 7.0000000000000000 | 350 9 | 10 | 7.0000000000000000 | 420 9 | 10 | 7.0000000000000000 | 490 9 | 10 | 7.0000000000000000 | 560 9 | 10 | 7.0000000000000000 | 630 9 | 10 | 7.0000000000000000 | 700 9 | 10 | 8.0000000000000000 | 140 9 | 10 | 8.0000000000000000 | 210 9 | 10 | 8.0000000000000000 | 280 9 | 10 | 8.0000000000000000 | 350 9 | 10 | 8.0000000000000000 | 420 9 | 10 | 8.0000000000000000 | 490 9 | 10 | 8.0000000000000000 | 560 9 | 10 | 8.0000000000000000 | 630 9 | 10 | 8.0000000000000000 | 700 9 | 10 | 9.0000000000000000 | 140 9 | 10 | 9.0000000000000000 | 210 9 | 10 | 9.0000000000000000 | 280 9 | 10 | 9.0000000000000000 | 350 9 | 10 | 9.0000000000000000 | 420 9 | 10 | 9.0000000000000000 | 490 9 | 10 | 9.0000000000000000 | 560 9 | 10 | 9.0000000000000000 | 630 9 | 10 | 9.0000000000000000 | 700 10 | 11 | 3.0000000000000000 | 140 10 | 11 | 3.0000000000000000 | 210 10 | 11 | 3.0000000000000000 | 280 10 | 11 | 3.0000000000000000 | 350 10 | 11 | 3.0000000000000000 | 420 10 | 11 | 3.0000000000000000 | 490 10 | 11 | 3.0000000000000000 | 560 10 | 11 | 3.0000000000000000 | 630 10 | 11 | 3.0000000000000000 | 700 10 | 11 | 4.0000000000000000 | 140 10 | 11 | 4.0000000000000000 | 210 10 | 11 | 4.0000000000000000 | 280 10 | 11 | 4.0000000000000000 | 350 10 | 11 | 4.0000000000000000 | 420 10 | 11 | 4.0000000000000000 | 490 10 | 11 | 4.0000000000000000 | 560 10 | 11 | 4.0000000000000000 | 630 10 | 11 | 4.0000000000000000 | 700 10 | 11 | 5.0000000000000000 | 140 10 | 11 | 5.0000000000000000 | 210 10 | 11 | 5.0000000000000000 | 280 10 | 11 | 5.0000000000000000 | 350 10 | 11 | 5.0000000000000000 | 420 10 | 11 | 5.0000000000000000 | 490 10 | 11 | 5.0000000000000000 | 560 10 | 11 | 5.0000000000000000 | 630 10 | 11 | 5.0000000000000000 | 700 10 | 11 | 6.0000000000000000 | 140 10 | 11 | 6.0000000000000000 | 210 10 | 11 | 6.0000000000000000 | 280 10 | 11 | 6.0000000000000000 | 350 10 | 11 | 6.0000000000000000 | 420 10 | 11 | 6.0000000000000000 | 490 10 | 11 | 6.0000000000000000 | 560 10 | 11 | 6.0000000000000000 | 630 10 | 11 | 6.0000000000000000 | 700 10 | 11 | 7.0000000000000000 | 140 10 | 11 | 7.0000000000000000 | 210 10 | 11 | 7.0000000000000000 | 280 10 | 11 | 7.0000000000000000 | 350 10 | 11 | 7.0000000000000000 | 420 10 | 11 | 7.0000000000000000 | 490 10 | 11 | 7.0000000000000000 | 560 10 | 11 | 7.0000000000000000 | 630 10 | 11 | 7.0000000000000000 | 700 10 | 11 | 8.0000000000000000 | 140 10 | 11 | 8.0000000000000000 | 210 10 | 11 | 8.0000000000000000 | 280 10 | 11 | 8.0000000000000000 | 350 10 | 11 | 8.0000000000000000 | 420 10 | 11 | 8.0000000000000000 | 490 10 | 11 | 8.0000000000000000 | 560 10 | 11 | 8.0000000000000000 | 630 10 | 11 | 8.0000000000000000 | 700 10 | 11 | 9.0000000000000000 | 140 10 | 11 | 9.0000000000000000 | 210 10 | 11 | 9.0000000000000000 | 280 10 | 11 | 9.0000000000000000 | 350 10 | 11 | 9.0000000000000000 | 420 10 | 11 | 9.0000000000000000 | 490 10 | 11 | 9.0000000000000000 | 560 10 | 11 | 9.0000000000000000 | 630 10 | 11 | 9.0000000000000000 | 700 (630 rows) -- -- Test for wrong results in window functions under joins #3 -- select * from ( WITH t(a,b,d) as (SELECT bfv_joins_foo.a,bfv_joins_foo.b,bfv_joins_bar.d FROM bfv_joins_foo,bfv_joins_bar WHERE bfv_joins_foo.a = bfv_joins_bar.d ) SELECT cup.*, SUM(t.d) OVER(PARTITION BY t.b) FROM ( SELECT bfv_joins_bar.*, AVG(t.b) OVER(PARTITION BY t.a ORDER BY t.b desc) AS e FROM t,bfv_joins_bar ) as cup, t WHERE cup.e < 10 GROUP BY cup.c,cup.d, cup.e ,t.d,t.b) i order by 1, 2, 3, 4; c | d | e | sum ----+----+--------------------+----- 1 | 2 | 3.0000000000000000 | 140 1 | 2 | 3.0000000000000000 | 210 1 | 2 | 3.0000000000000000 | 280 1 | 2 | 3.0000000000000000 | 350 1 | 2 | 3.0000000000000000 | 420 1 | 2 | 3.0000000000000000 | 490 1 | 2 | 3.0000000000000000 | 560 1 | 2 | 3.0000000000000000 | 630 1 | 2 | 3.0000000000000000 | 700 1 | 2 | 4.0000000000000000 | 140 1 | 2 | 4.0000000000000000 | 210 1 | 2 | 4.0000000000000000 | 280 1 | 2 | 4.0000000000000000 | 350 1 | 2 | 4.0000000000000000 | 420 1 | 2 | 4.0000000000000000 | 490 1 | 2 | 4.0000000000000000 | 560 1 | 2 | 4.0000000000000000 | 630 1 | 2 | 4.0000000000000000 | 700 1 | 2 | 5.0000000000000000 | 140 1 | 2 | 5.0000000000000000 | 210 1 | 2 | 5.0000000000000000 | 280 1 | 2 | 5.0000000000000000 | 350 1 | 2 | 5.0000000000000000 | 420 1 | 2 | 5.0000000000000000 | 490 1 | 2 | 5.0000000000000000 | 560 1 | 2 | 5.0000000000000000 | 630 1 | 2 | 5.0000000000000000 | 700 1 | 2 | 6.0000000000000000 | 140 1 | 2 | 6.0000000000000000 | 210 1 | 2 | 6.0000000000000000 | 280 1 | 2 | 6.0000000000000000 | 350 1 | 2 | 6.0000000000000000 | 420 1 | 2 | 6.0000000000000000 | 490 1 | 2 | 6.0000000000000000 | 560 1 | 2 | 6.0000000000000000 | 630 1 | 2 | 6.0000000000000000 | 700 1 | 2 | 7.0000000000000000 | 140 1 | 2 | 7.0000000000000000 | 210 1 | 2 | 7.0000000000000000 | 280 1 | 2 | 7.0000000000000000 | 350 1 | 2 | 7.0000000000000000 | 420 1 | 2 | 7.0000000000000000 | 490 1 | 2 | 7.0000000000000000 | 560 1 | 2 | 7.0000000000000000 | 630 1 | 2 | 7.0000000000000000 | 700 1 | 2 | 8.0000000000000000 | 140 1 | 2 | 8.0000000000000000 | 210 1 | 2 | 8.0000000000000000 | 280 1 | 2 | 8.0000000000000000 | 350 1 | 2 | 8.0000000000000000 | 420 1 | 2 | 8.0000000000000000 | 490 1 | 2 | 8.0000000000000000 | 560 1 | 2 | 8.0000000000000000 | 630 1 | 2 | 8.0000000000000000 | 700 1 | 2 | 9.0000000000000000 | 140 1 | 2 | 9.0000000000000000 | 210 1 | 2 | 9.0000000000000000 | 280 1 | 2 | 9.0000000000000000 | 350 1 | 2 | 9.0000000000000000 | 420 1 | 2 | 9.0000000000000000 | 490 1 | 2 | 9.0000000000000000 | 560 1 | 2 | 9.0000000000000000 | 630 1 | 2 | 9.0000000000000000 | 700 2 | 3 | 3.0000000000000000 | 140 2 | 3 | 3.0000000000000000 | 210 2 | 3 | 3.0000000000000000 | 280 2 | 3 | 3.0000000000000000 | 350 2 | 3 | 3.0000000000000000 | 420 2 | 3 | 3.0000000000000000 | 490 2 | 3 | 3.0000000000000000 | 560 2 | 3 | 3.0000000000000000 | 630 2 | 3 | 3.0000000000000000 | 700 2 | 3 | 4.0000000000000000 | 140 2 | 3 | 4.0000000000000000 | 210 2 | 3 | 4.0000000000000000 | 280 2 | 3 | 4.0000000000000000 | 350 2 | 3 | 4.0000000000000000 | 420 2 | 3 | 4.0000000000000000 | 490 2 | 3 | 4.0000000000000000 | 560 2 | 3 | 4.0000000000000000 | 630 2 | 3 | 4.0000000000000000 | 700 2 | 3 | 5.0000000000000000 | 140 2 | 3 | 5.0000000000000000 | 210 2 | 3 | 5.0000000000000000 | 280 2 | 3 | 5.0000000000000000 | 350 2 | 3 | 5.0000000000000000 | 420 2 | 3 | 5.0000000000000000 | 490 2 | 3 | 5.0000000000000000 | 560 2 | 3 | 5.0000000000000000 | 630 2 | 3 | 5.0000000000000000 | 700 2 | 3 | 6.0000000000000000 | 140 2 | 3 | 6.0000000000000000 | 210 2 | 3 | 6.0000000000000000 | 280 2 | 3 | 6.0000000000000000 | 350 2 | 3 | 6.0000000000000000 | 420 2 | 3 | 6.0000000000000000 | 490 2 | 3 | 6.0000000000000000 | 560 2 | 3 | 6.0000000000000000 | 630 2 | 3 | 6.0000000000000000 | 700 2 | 3 | 7.0000000000000000 | 140 2 | 3 | 7.0000000000000000 | 210 2 | 3 | 7.0000000000000000 | 280 2 | 3 | 7.0000000000000000 | 350 2 | 3 | 7.0000000000000000 | 420 2 | 3 | 7.0000000000000000 | 490 2 | 3 | 7.0000000000000000 | 560 2 | 3 | 7.0000000000000000 | 630 2 | 3 | 7.0000000000000000 | 700 2 | 3 | 8.0000000000000000 | 140 2 | 3 | 8.0000000000000000 | 210 2 | 3 | 8.0000000000000000 | 280 2 | 3 | 8.0000000000000000 | 350 2 | 3 | 8.0000000000000000 | 420 2 | 3 | 8.0000000000000000 | 490 2 | 3 | 8.0000000000000000 | 560 2 | 3 | 8.0000000000000000 | 630 2 | 3 | 8.0000000000000000 | 700 2 | 3 | 9.0000000000000000 | 140 2 | 3 | 9.0000000000000000 | 210 2 | 3 | 9.0000000000000000 | 280 2 | 3 | 9.0000000000000000 | 350 2 | 3 | 9.0000000000000000 | 420 2 | 3 | 9.0000000000000000 | 490 2 | 3 | 9.0000000000000000 | 560 2 | 3 | 9.0000000000000000 | 630 2 | 3 | 9.0000000000000000 | 700 3 | 4 | 3.0000000000000000 | 140 3 | 4 | 3.0000000000000000 | 210 3 | 4 | 3.0000000000000000 | 280 3 | 4 | 3.0000000000000000 | 350 3 | 4 | 3.0000000000000000 | 420 3 | 4 | 3.0000000000000000 | 490 3 | 4 | 3.0000000000000000 | 560 3 | 4 | 3.0000000000000000 | 630 3 | 4 | 3.0000000000000000 | 700 3 | 4 | 4.0000000000000000 | 140 3 | 4 | 4.0000000000000000 | 210 3 | 4 | 4.0000000000000000 | 280 3 | 4 | 4.0000000000000000 | 350 3 | 4 | 4.0000000000000000 | 420 3 | 4 | 4.0000000000000000 | 490 3 | 4 | 4.0000000000000000 | 560 3 | 4 | 4.0000000000000000 | 630 3 | 4 | 4.0000000000000000 | 700 3 | 4 | 5.0000000000000000 | 140 3 | 4 | 5.0000000000000000 | 210 3 | 4 | 5.0000000000000000 | 280 3 | 4 | 5.0000000000000000 | 350 3 | 4 | 5.0000000000000000 | 420 3 | 4 | 5.0000000000000000 | 490 3 | 4 | 5.0000000000000000 | 560 3 | 4 | 5.0000000000000000 | 630 3 | 4 | 5.0000000000000000 | 700 3 | 4 | 6.0000000000000000 | 140 3 | 4 | 6.0000000000000000 | 210 3 | 4 | 6.0000000000000000 | 280 3 | 4 | 6.0000000000000000 | 350 3 | 4 | 6.0000000000000000 | 420 3 | 4 | 6.0000000000000000 | 490 3 | 4 | 6.0000000000000000 | 560 3 | 4 | 6.0000000000000000 | 630 3 | 4 | 6.0000000000000000 | 700 3 | 4 | 7.0000000000000000 | 140 3 | 4 | 7.0000000000000000 | 210 3 | 4 | 7.0000000000000000 | 280 3 | 4 | 7.0000000000000000 | 350 3 | 4 | 7.0000000000000000 | 420 3 | 4 | 7.0000000000000000 | 490 3 | 4 | 7.0000000000000000 | 560 3 | 4 | 7.0000000000000000 | 630 3 | 4 | 7.0000000000000000 | 700 3 | 4 | 8.0000000000000000 | 140 3 | 4 | 8.0000000000000000 | 210 3 | 4 | 8.0000000000000000 | 280 3 | 4 | 8.0000000000000000 | 350 3 | 4 | 8.0000000000000000 | 420 3 | 4 | 8.0000000000000000 | 490 3 | 4 | 8.0000000000000000 | 560 3 | 4 | 8.0000000000000000 | 630 3 | 4 | 8.0000000000000000 | 700 3 | 4 | 9.0000000000000000 | 140 3 | 4 | 9.0000000000000000 | 210 3 | 4 | 9.0000000000000000 | 280 3 | 4 | 9.0000000000000000 | 350 3 | 4 | 9.0000000000000000 | 420 3 | 4 | 9.0000000000000000 | 490 3 | 4 | 9.0000000000000000 | 560 3 | 4 | 9.0000000000000000 | 630 3 | 4 | 9.0000000000000000 | 700 4 | 5 | 3.0000000000000000 | 140 4 | 5 | 3.0000000000000000 | 210 4 | 5 | 3.0000000000000000 | 280 4 | 5 | 3.0000000000000000 | 350 4 | 5 | 3.0000000000000000 | 420 4 | 5 | 3.0000000000000000 | 490 4 | 5 | 3.0000000000000000 | 560 4 | 5 | 3.0000000000000000 | 630 4 | 5 | 3.0000000000000000 | 700 4 | 5 | 4.0000000000000000 | 140 4 | 5 | 4.0000000000000000 | 210 4 | 5 | 4.0000000000000000 | 280 4 | 5 | 4.0000000000000000 | 350 4 | 5 | 4.0000000000000000 | 420 4 | 5 | 4.0000000000000000 | 490 4 | 5 | 4.0000000000000000 | 560 4 | 5 | 4.0000000000000000 | 630 4 | 5 | 4.0000000000000000 | 700 4 | 5 | 5.0000000000000000 | 140 4 | 5 | 5.0000000000000000 | 210 4 | 5 | 5.0000000000000000 | 280 4 | 5 | 5.0000000000000000 | 350 4 | 5 | 5.0000000000000000 | 420 4 | 5 | 5.0000000000000000 | 490 4 | 5 | 5.0000000000000000 | 560 4 | 5 | 5.0000000000000000 | 630 4 | 5 | 5.0000000000000000 | 700 4 | 5 | 6.0000000000000000 | 140 4 | 5 | 6.0000000000000000 | 210 4 | 5 | 6.0000000000000000 | 280 4 | 5 | 6.0000000000000000 | 350 4 | 5 | 6.0000000000000000 | 420 4 | 5 | 6.0000000000000000 | 490 4 | 5 | 6.0000000000000000 | 560 4 | 5 | 6.0000000000000000 | 630 4 | 5 | 6.0000000000000000 | 700 4 | 5 | 7.0000000000000000 | 140 4 | 5 | 7.0000000000000000 | 210 4 | 5 | 7.0000000000000000 | 280 4 | 5 | 7.0000000000000000 | 350 4 | 5 | 7.0000000000000000 | 420 4 | 5 | 7.0000000000000000 | 490 4 | 5 | 7.0000000000000000 | 560 4 | 5 | 7.0000000000000000 | 630 4 | 5 | 7.0000000000000000 | 700 4 | 5 | 8.0000000000000000 | 140 4 | 5 | 8.0000000000000000 | 210 4 | 5 | 8.0000000000000000 | 280 4 | 5 | 8.0000000000000000 | 350 4 | 5 | 8.0000000000000000 | 420 4 | 5 | 8.0000000000000000 | 490 4 | 5 | 8.0000000000000000 | 560 4 | 5 | 8.0000000000000000 | 630 4 | 5 | 8.0000000000000000 | 700 4 | 5 | 9.0000000000000000 | 140 4 | 5 | 9.0000000000000000 | 210 4 | 5 | 9.0000000000000000 | 280 4 | 5 | 9.0000000000000000 | 350 4 | 5 | 9.0000000000000000 | 420 4 | 5 | 9.0000000000000000 | 490 4 | 5 | 9.0000000000000000 | 560 4 | 5 | 9.0000000000000000 | 630 4 | 5 | 9.0000000000000000 | 700 5 | 6 | 3.0000000000000000 | 140 5 | 6 | 3.0000000000000000 | 210 5 | 6 | 3.0000000000000000 | 280 5 | 6 | 3.0000000000000000 | 350 5 | 6 | 3.0000000000000000 | 420 5 | 6 | 3.0000000000000000 | 490 5 | 6 | 3.0000000000000000 | 560 5 | 6 | 3.0000000000000000 | 630 5 | 6 | 3.0000000000000000 | 700 5 | 6 | 4.0000000000000000 | 140 5 | 6 | 4.0000000000000000 | 210 5 | 6 | 4.0000000000000000 | 280 5 | 6 | 4.0000000000000000 | 350 5 | 6 | 4.0000000000000000 | 420 5 | 6 | 4.0000000000000000 | 490 5 | 6 | 4.0000000000000000 | 560 5 | 6 | 4.0000000000000000 | 630 5 | 6 | 4.0000000000000000 | 700 5 | 6 | 5.0000000000000000 | 140 5 | 6 | 5.0000000000000000 | 210 5 | 6 | 5.0000000000000000 | 280 5 | 6 | 5.0000000000000000 | 350 5 | 6 | 5.0000000000000000 | 420 5 | 6 | 5.0000000000000000 | 490 5 | 6 | 5.0000000000000000 | 560 5 | 6 | 5.0000000000000000 | 630 5 | 6 | 5.0000000000000000 | 700 5 | 6 | 6.0000000000000000 | 140 5 | 6 | 6.0000000000000000 | 210 5 | 6 | 6.0000000000000000 | 280 5 | 6 | 6.0000000000000000 | 350 5 | 6 | 6.0000000000000000 | 420 5 | 6 | 6.0000000000000000 | 490 5 | 6 | 6.0000000000000000 | 560 5 | 6 | 6.0000000000000000 | 630 5 | 6 | 6.0000000000000000 | 700 5 | 6 | 7.0000000000000000 | 140 5 | 6 | 7.0000000000000000 | 210 5 | 6 | 7.0000000000000000 | 280 5 | 6 | 7.0000000000000000 | 350 5 | 6 | 7.0000000000000000 | 420 5 | 6 | 7.0000000000000000 | 490 5 | 6 | 7.0000000000000000 | 560 5 | 6 | 7.0000000000000000 | 630 5 | 6 | 7.0000000000000000 | 700 5 | 6 | 8.0000000000000000 | 140 5 | 6 | 8.0000000000000000 | 210 5 | 6 | 8.0000000000000000 | 280 5 | 6 | 8.0000000000000000 | 350 5 | 6 | 8.0000000000000000 | 420 5 | 6 | 8.0000000000000000 | 490 5 | 6 | 8.0000000000000000 | 560 5 | 6 | 8.0000000000000000 | 630 5 | 6 | 8.0000000000000000 | 700 5 | 6 | 9.0000000000000000 | 140 5 | 6 | 9.0000000000000000 | 210 5 | 6 | 9.0000000000000000 | 280 5 | 6 | 9.0000000000000000 | 350 5 | 6 | 9.0000000000000000 | 420 5 | 6 | 9.0000000000000000 | 490 5 | 6 | 9.0000000000000000 | 560 5 | 6 | 9.0000000000000000 | 630 5 | 6 | 9.0000000000000000 | 700 6 | 7 | 3.0000000000000000 | 140 6 | 7 | 3.0000000000000000 | 210 6 | 7 | 3.0000000000000000 | 280 6 | 7 | 3.0000000000000000 | 350 6 | 7 | 3.0000000000000000 | 420 6 | 7 | 3.0000000000000000 | 490 6 | 7 | 3.0000000000000000 | 560 6 | 7 | 3.0000000000000000 | 630 6 | 7 | 3.0000000000000000 | 700 6 | 7 | 4.0000000000000000 | 140 6 | 7 | 4.0000000000000000 | 210 6 | 7 | 4.0000000000000000 | 280 6 | 7 | 4.0000000000000000 | 350 6 | 7 | 4.0000000000000000 | 420 6 | 7 | 4.0000000000000000 | 490 6 | 7 | 4.0000000000000000 | 560 6 | 7 | 4.0000000000000000 | 630 6 | 7 | 4.0000000000000000 | 700 6 | 7 | 5.0000000000000000 | 140 6 | 7 | 5.0000000000000000 | 210 6 | 7 | 5.0000000000000000 | 280 6 | 7 | 5.0000000000000000 | 350 6 | 7 | 5.0000000000000000 | 420 6 | 7 | 5.0000000000000000 | 490 6 | 7 | 5.0000000000000000 | 560 6 | 7 | 5.0000000000000000 | 630 6 | 7 | 5.0000000000000000 | 700 6 | 7 | 6.0000000000000000 | 140 6 | 7 | 6.0000000000000000 | 210 6 | 7 | 6.0000000000000000 | 280 6 | 7 | 6.0000000000000000 | 350 6 | 7 | 6.0000000000000000 | 420 6 | 7 | 6.0000000000000000 | 490 6 | 7 | 6.0000000000000000 | 560 6 | 7 | 6.0000000000000000 | 630 6 | 7 | 6.0000000000000000 | 700 6 | 7 | 7.0000000000000000 | 140 6 | 7 | 7.0000000000000000 | 210 6 | 7 | 7.0000000000000000 | 280 6 | 7 | 7.0000000000000000 | 350 6 | 7 | 7.0000000000000000 | 420 6 | 7 | 7.0000000000000000 | 490 6 | 7 | 7.0000000000000000 | 560 6 | 7 | 7.0000000000000000 | 630 6 | 7 | 7.0000000000000000 | 700 6 | 7 | 8.0000000000000000 | 140 6 | 7 | 8.0000000000000000 | 210 6 | 7 | 8.0000000000000000 | 280 6 | 7 | 8.0000000000000000 | 350 6 | 7 | 8.0000000000000000 | 420 6 | 7 | 8.0000000000000000 | 490 6 | 7 | 8.0000000000000000 | 560 6 | 7 | 8.0000000000000000 | 630 6 | 7 | 8.0000000000000000 | 700 6 | 7 | 9.0000000000000000 | 140 6 | 7 | 9.0000000000000000 | 210 6 | 7 | 9.0000000000000000 | 280 6 | 7 | 9.0000000000000000 | 350 6 | 7 | 9.0000000000000000 | 420 6 | 7 | 9.0000000000000000 | 490 6 | 7 | 9.0000000000000000 | 560 6 | 7 | 9.0000000000000000 | 630 6 | 7 | 9.0000000000000000 | 700 7 | 8 | 3.0000000000000000 | 140 7 | 8 | 3.0000000000000000 | 210 7 | 8 | 3.0000000000000000 | 280 7 | 8 | 3.0000000000000000 | 350 7 | 8 | 3.0000000000000000 | 420 7 | 8 | 3.0000000000000000 | 490 7 | 8 | 3.0000000000000000 | 560 7 | 8 | 3.0000000000000000 | 630 7 | 8 | 3.0000000000000000 | 700 7 | 8 | 4.0000000000000000 | 140 7 | 8 | 4.0000000000000000 | 210 7 | 8 | 4.0000000000000000 | 280 7 | 8 | 4.0000000000000000 | 350 7 | 8 | 4.0000000000000000 | 420 7 | 8 | 4.0000000000000000 | 490 7 | 8 | 4.0000000000000000 | 560 7 | 8 | 4.0000000000000000 | 630 7 | 8 | 4.0000000000000000 | 700 7 | 8 | 5.0000000000000000 | 140 7 | 8 | 5.0000000000000000 | 210 7 | 8 | 5.0000000000000000 | 280 7 | 8 | 5.0000000000000000 | 350 7 | 8 | 5.0000000000000000 | 420 7 | 8 | 5.0000000000000000 | 490 7 | 8 | 5.0000000000000000 | 560 7 | 8 | 5.0000000000000000 | 630 7 | 8 | 5.0000000000000000 | 700 7 | 8 | 6.0000000000000000 | 140 7 | 8 | 6.0000000000000000 | 210 7 | 8 | 6.0000000000000000 | 280 7 | 8 | 6.0000000000000000 | 350 7 | 8 | 6.0000000000000000 | 420 7 | 8 | 6.0000000000000000 | 490 7 | 8 | 6.0000000000000000 | 560 7 | 8 | 6.0000000000000000 | 630 7 | 8 | 6.0000000000000000 | 700 7 | 8 | 7.0000000000000000 | 140 7 | 8 | 7.0000000000000000 | 210 7 | 8 | 7.0000000000000000 | 280 7 | 8 | 7.0000000000000000 | 350 7 | 8 | 7.0000000000000000 | 420 7 | 8 | 7.0000000000000000 | 490 7 | 8 | 7.0000000000000000 | 560 7 | 8 | 7.0000000000000000 | 630 7 | 8 | 7.0000000000000000 | 700 7 | 8 | 8.0000000000000000 | 140 7 | 8 | 8.0000000000000000 | 210 7 | 8 | 8.0000000000000000 | 280 7 | 8 | 8.0000000000000000 | 350 7 | 8 | 8.0000000000000000 | 420 7 | 8 | 8.0000000000000000 | 490 7 | 8 | 8.0000000000000000 | 560 7 | 8 | 8.0000000000000000 | 630 7 | 8 | 8.0000000000000000 | 700 7 | 8 | 9.0000000000000000 | 140 7 | 8 | 9.0000000000000000 | 210 7 | 8 | 9.0000000000000000 | 280 7 | 8 | 9.0000000000000000 | 350 7 | 8 | 9.0000000000000000 | 420 7 | 8 | 9.0000000000000000 | 490 7 | 8 | 9.0000000000000000 | 560 7 | 8 | 9.0000000000000000 | 630 7 | 8 | 9.0000000000000000 | 700 8 | 9 | 3.0000000000000000 | 140 8 | 9 | 3.0000000000000000 | 210 8 | 9 | 3.0000000000000000 | 280 8 | 9 | 3.0000000000000000 | 350 8 | 9 | 3.0000000000000000 | 420 8 | 9 | 3.0000000000000000 | 490 8 | 9 | 3.0000000000000000 | 560 8 | 9 | 3.0000000000000000 | 630 8 | 9 | 3.0000000000000000 | 700 8 | 9 | 4.0000000000000000 | 140 8 | 9 | 4.0000000000000000 | 210 8 | 9 | 4.0000000000000000 | 280 8 | 9 | 4.0000000000000000 | 350 8 | 9 | 4.0000000000000000 | 420 8 | 9 | 4.0000000000000000 | 490 8 | 9 | 4.0000000000000000 | 560 8 | 9 | 4.0000000000000000 | 630 8 | 9 | 4.0000000000000000 | 700 8 | 9 | 5.0000000000000000 | 140 8 | 9 | 5.0000000000000000 | 210 8 | 9 | 5.0000000000000000 | 280 8 | 9 | 5.0000000000000000 | 350 8 | 9 | 5.0000000000000000 | 420 8 | 9 | 5.0000000000000000 | 490 8 | 9 | 5.0000000000000000 | 560 8 | 9 | 5.0000000000000000 | 630 8 | 9 | 5.0000000000000000 | 700 8 | 9 | 6.0000000000000000 | 140 8 | 9 | 6.0000000000000000 | 210 8 | 9 | 6.0000000000000000 | 280 8 | 9 | 6.0000000000000000 | 350 8 | 9 | 6.0000000000000000 | 420 8 | 9 | 6.0000000000000000 | 490 8 | 9 | 6.0000000000000000 | 560 8 | 9 | 6.0000000000000000 | 630 8 | 9 | 6.0000000000000000 | 700 8 | 9 | 7.0000000000000000 | 140 8 | 9 | 7.0000000000000000 | 210 8 | 9 | 7.0000000000000000 | 280 8 | 9 | 7.0000000000000000 | 350 8 | 9 | 7.0000000000000000 | 420 8 | 9 | 7.0000000000000000 | 490 8 | 9 | 7.0000000000000000 | 560 8 | 9 | 7.0000000000000000 | 630 8 | 9 | 7.0000000000000000 | 700 8 | 9 | 8.0000000000000000 | 140 8 | 9 | 8.0000000000000000 | 210 8 | 9 | 8.0000000000000000 | 280 8 | 9 | 8.0000000000000000 | 350 8 | 9 | 8.0000000000000000 | 420 8 | 9 | 8.0000000000000000 | 490 8 | 9 | 8.0000000000000000 | 560 8 | 9 | 8.0000000000000000 | 630 8 | 9 | 8.0000000000000000 | 700 8 | 9 | 9.0000000000000000 | 140 8 | 9 | 9.0000000000000000 | 210 8 | 9 | 9.0000000000000000 | 280 8 | 9 | 9.0000000000000000 | 350 8 | 9 | 9.0000000000000000 | 420 8 | 9 | 9.0000000000000000 | 490 8 | 9 | 9.0000000000000000 | 560 8 | 9 | 9.0000000000000000 | 630 8 | 9 | 9.0000000000000000 | 700 9 | 10 | 3.0000000000000000 | 140 9 | 10 | 3.0000000000000000 | 210 9 | 10 | 3.0000000000000000 | 280 9 | 10 | 3.0000000000000000 | 350 9 | 10 | 3.0000000000000000 | 420 9 | 10 | 3.0000000000000000 | 490 9 | 10 | 3.0000000000000000 | 560 9 | 10 | 3.0000000000000000 | 630 9 | 10 | 3.0000000000000000 | 700 9 | 10 | 4.0000000000000000 | 140 9 | 10 | 4.0000000000000000 | 210 9 | 10 | 4.0000000000000000 | 280 9 | 10 | 4.0000000000000000 | 350 9 | 10 | 4.0000000000000000 | 420 9 | 10 | 4.0000000000000000 | 490 9 | 10 | 4.0000000000000000 | 560 9 | 10 | 4.0000000000000000 | 630 9 | 10 | 4.0000000000000000 | 700 9 | 10 | 5.0000000000000000 | 140 9 | 10 | 5.0000000000000000 | 210 9 | 10 | 5.0000000000000000 | 280 9 | 10 | 5.0000000000000000 | 350 9 | 10 | 5.0000000000000000 | 420 9 | 10 | 5.0000000000000000 | 490 9 | 10 | 5.0000000000000000 | 560 9 | 10 | 5.0000000000000000 | 630 9 | 10 | 5.0000000000000000 | 700 9 | 10 | 6.0000000000000000 | 140 9 | 10 | 6.0000000000000000 | 210 9 | 10 | 6.0000000000000000 | 280 9 | 10 | 6.0000000000000000 | 350 9 | 10 | 6.0000000000000000 | 420 9 | 10 | 6.0000000000000000 | 490 9 | 10 | 6.0000000000000000 | 560 9 | 10 | 6.0000000000000000 | 630 9 | 10 | 6.0000000000000000 | 700 9 | 10 | 7.0000000000000000 | 140 9 | 10 | 7.0000000000000000 | 210 9 | 10 | 7.0000000000000000 | 280 9 | 10 | 7.0000000000000000 | 350 9 | 10 | 7.0000000000000000 | 420 9 | 10 | 7.0000000000000000 | 490 9 | 10 | 7.0000000000000000 | 560 9 | 10 | 7.0000000000000000 | 630 9 | 10 | 7.0000000000000000 | 700 9 | 10 | 8.0000000000000000 | 140 9 | 10 | 8.0000000000000000 | 210 9 | 10 | 8.0000000000000000 | 280 9 | 10 | 8.0000000000000000 | 350 9 | 10 | 8.0000000000000000 | 420 9 | 10 | 8.0000000000000000 | 490 9 | 10 | 8.0000000000000000 | 560 9 | 10 | 8.0000000000000000 | 630 9 | 10 | 8.0000000000000000 | 700 9 | 10 | 9.0000000000000000 | 140 9 | 10 | 9.0000000000000000 | 210 9 | 10 | 9.0000000000000000 | 280 9 | 10 | 9.0000000000000000 | 350 9 | 10 | 9.0000000000000000 | 420 9 | 10 | 9.0000000000000000 | 490 9 | 10 | 9.0000000000000000 | 560 9 | 10 | 9.0000000000000000 | 630 9 | 10 | 9.0000000000000000 | 700 10 | 11 | 3.0000000000000000 | 140 10 | 11 | 3.0000000000000000 | 210 10 | 11 | 3.0000000000000000 | 280 10 | 11 | 3.0000000000000000 | 350 10 | 11 | 3.0000000000000000 | 420 10 | 11 | 3.0000000000000000 | 490 10 | 11 | 3.0000000000000000 | 560 10 | 11 | 3.0000000000000000 | 630 10 | 11 | 3.0000000000000000 | 700 10 | 11 | 4.0000000000000000 | 140 10 | 11 | 4.0000000000000000 | 210 10 | 11 | 4.0000000000000000 | 280 10 | 11 | 4.0000000000000000 | 350 10 | 11 | 4.0000000000000000 | 420 10 | 11 | 4.0000000000000000 | 490 10 | 11 | 4.0000000000000000 | 560 10 | 11 | 4.0000000000000000 | 630 10 | 11 | 4.0000000000000000 | 700 10 | 11 | 5.0000000000000000 | 140 10 | 11 | 5.0000000000000000 | 210 10 | 11 | 5.0000000000000000 | 280 10 | 11 | 5.0000000000000000 | 350 10 | 11 | 5.0000000000000000 | 420 10 | 11 | 5.0000000000000000 | 490 10 | 11 | 5.0000000000000000 | 560 10 | 11 | 5.0000000000000000 | 630 10 | 11 | 5.0000000000000000 | 700 10 | 11 | 6.0000000000000000 | 140 10 | 11 | 6.0000000000000000 | 210 10 | 11 | 6.0000000000000000 | 280 10 | 11 | 6.0000000000000000 | 350 10 | 11 | 6.0000000000000000 | 420 10 | 11 | 6.0000000000000000 | 490 10 | 11 | 6.0000000000000000 | 560 10 | 11 | 6.0000000000000000 | 630 10 | 11 | 6.0000000000000000 | 700 10 | 11 | 7.0000000000000000 | 140 10 | 11 | 7.0000000000000000 | 210 10 | 11 | 7.0000000000000000 | 280 10 | 11 | 7.0000000000000000 | 350 10 | 11 | 7.0000000000000000 | 420 10 | 11 | 7.0000000000000000 | 490 10 | 11 | 7.0000000000000000 | 560 10 | 11 | 7.0000000000000000 | 630 10 | 11 | 7.0000000000000000 | 700 10 | 11 | 8.0000000000000000 | 140 10 | 11 | 8.0000000000000000 | 210 10 | 11 | 8.0000000000000000 | 280 10 | 11 | 8.0000000000000000 | 350 10 | 11 | 8.0000000000000000 | 420 10 | 11 | 8.0000000000000000 | 490 10 | 11 | 8.0000000000000000 | 560 10 | 11 | 8.0000000000000000 | 630 10 | 11 | 8.0000000000000000 | 700 10 | 11 | 9.0000000000000000 | 140 10 | 11 | 9.0000000000000000 | 210 10 | 11 | 9.0000000000000000 | 280 10 | 11 | 9.0000000000000000 | 350 10 | 11 | 9.0000000000000000 | 420 10 | 11 | 9.0000000000000000 | 490 10 | 11 | 9.0000000000000000 | 560 10 | 11 | 9.0000000000000000 | 630 10 | 11 | 9.0000000000000000 | 700 (630 rows) -- -- Query on partitioned table with range join predicate on part key causes fallback to planner -- select * from x_part, x_non_part where a > e; e | f | g | a | b | c ---+---+---+---+---+--- 1 | 3 | 1 | 2 | 2 | 2 1 | 3 | 1 | 2 | 5 | 5 1 | 3 | 1 | 2 | 8 | 8 (3 rows) select * from x_part, x_non_part where a <> e; e | f | g | a | b | c ----+----+---+---+----+---- 1 | 3 | 1 | 2 | 2 | 2 1 | 3 | 1 | 0 | 3 | 3 1 | 3 | 1 | 2 | 5 | 5 1 | 3 | 1 | 0 | 6 | 6 1 | 3 | 1 | 2 | 8 | 8 1 | 3 | 1 | 0 | 9 | 9 2 | 6 | 2 | 1 | 1 | 1 2 | 6 | 2 | 0 | 3 | 3 2 | 6 | 2 | 1 | 4 | 4 2 | 6 | 2 | 0 | 6 | 6 2 | 6 | 2 | 1 | 7 | 7 2 | 6 | 2 | 0 | 9 | 9 2 | 6 | 2 | 1 | 10 | 10 8 | 24 | 2 | 1 | 1 | 1 8 | 24 | 2 | 2 | 2 | 2 8 | 24 | 2 | 0 | 3 | 3 8 | 24 | 2 | 1 | 4 | 4 8 | 24 | 2 | 2 | 5 | 5 8 | 24 | 2 | 0 | 6 | 6 8 | 24 | 2 | 1 | 7 | 7 8 | 24 | 2 | 2 | 8 | 8 8 | 24 | 2 | 0 | 9 | 9 8 | 24 | 2 | 1 | 10 | 10 9 | 27 | 3 | 1 | 1 | 1 9 | 27 | 3 | 2 | 2 | 2 9 | 27 | 3 | 0 | 3 | 3 9 | 27 | 3 | 1 | 4 | 4 9 | 27 | 3 | 2 | 5 | 5 9 | 27 | 3 | 0 | 6 | 6 9 | 27 | 3 | 1 | 7 | 7 9 | 27 | 3 | 2 | 8 | 8 9 | 27 | 3 | 0 | 9 | 9 9 | 27 | 3 | 1 | 10 | 10 10 | 30 | 4 | 1 | 1 | 1 10 | 30 | 4 | 2 | 2 | 2 10 | 30 | 4 | 0 | 3 | 3 10 | 30 | 4 | 1 | 4 | 4 10 | 30 | 4 | 2 | 5 | 5 10 | 30 | 4 | 0 | 6 | 6 10 | 30 | 4 | 1 | 7 | 7 10 | 30 | 4 | 2 | 8 | 8 10 | 30 | 4 | 0 | 9 | 9 10 | 30 | 4 | 1 | 10 | 10 5 | 15 | 5 | 1 | 1 | 1 5 | 15 | 5 | 2 | 2 | 2 5 | 15 | 5 | 0 | 3 | 3 5 | 15 | 5 | 1 | 4 | 4 5 | 15 | 5 | 2 | 5 | 5 5 | 15 | 5 | 0 | 6 | 6 5 | 15 | 5 | 1 | 7 | 7 5 | 15 | 5 | 2 | 8 | 8 5 | 15 | 5 | 0 | 9 | 9 5 | 15 | 5 | 1 | 10 | 10 6 | 18 | 0 | 1 | 1 | 1 6 | 18 | 0 | 2 | 2 | 2 6 | 18 | 0 | 0 | 3 | 3 6 | 18 | 0 | 1 | 4 | 4 6 | 18 | 0 | 2 | 5 | 5 6 | 18 | 0 | 0 | 6 | 6 6 | 18 | 0 | 1 | 7 | 7 6 | 18 | 0 | 2 | 8 | 8 6 | 18 | 0 | 0 | 9 | 9 6 | 18 | 0 | 1 | 10 | 10 7 | 21 | 1 | 1 | 1 | 1 7 | 21 | 1 | 2 | 2 | 2 7 | 21 | 1 | 0 | 3 | 3 7 | 21 | 1 | 1 | 4 | 4 7 | 21 | 1 | 2 | 5 | 5 7 | 21 | 1 | 0 | 6 | 6 7 | 21 | 1 | 1 | 7 | 7 7 | 21 | 1 | 2 | 8 | 8 7 | 21 | 1 | 0 | 9 | 9 7 | 21 | 1 | 1 | 10 | 10 3 | 9 | 3 | 1 | 1 | 1 3 | 9 | 3 | 2 | 2 | 2 3 | 9 | 3 | 0 | 3 | 3 3 | 9 | 3 | 1 | 4 | 4 3 | 9 | 3 | 2 | 5 | 5 3 | 9 | 3 | 0 | 6 | 6 3 | 9 | 3 | 1 | 7 | 7 3 | 9 | 3 | 2 | 8 | 8 3 | 9 | 3 | 0 | 9 | 9 3 | 9 | 3 | 1 | 10 | 10 4 | 12 | 4 | 1 | 1 | 1 4 | 12 | 4 | 2 | 2 | 2 4 | 12 | 4 | 0 | 3 | 3 4 | 12 | 4 | 1 | 4 | 4 4 | 12 | 4 | 2 | 5 | 5 4 | 12 | 4 | 0 | 6 | 6 4 | 12 | 4 | 1 | 7 | 7 4 | 12 | 4 | 2 | 8 | 8 4 | 12 | 4 | 0 | 9 | 9 4 | 12 | 4 | 1 | 10 | 10 (93 rows) select * from x_part, x_non_part where a <= e; e | f | g | a | b | c ----+----+---+---+----+---- 1 | 3 | 1 | 1 | 1 | 1 1 | 3 | 1 | 0 | 3 | 3 1 | 3 | 1 | 1 | 4 | 4 1 | 3 | 1 | 0 | 6 | 6 1 | 3 | 1 | 1 | 7 | 7 1 | 3 | 1 | 0 | 9 | 9 1 | 3 | 1 | 1 | 10 | 10 2 | 6 | 2 | 1 | 1 | 1 2 | 6 | 2 | 2 | 2 | 2 2 | 6 | 2 | 0 | 3 | 3 2 | 6 | 2 | 1 | 4 | 4 2 | 6 | 2 | 2 | 5 | 5 2 | 6 | 2 | 0 | 6 | 6 2 | 6 | 2 | 1 | 7 | 7 2 | 6 | 2 | 2 | 8 | 8 2 | 6 | 2 | 0 | 9 | 9 2 | 6 | 2 | 1 | 10 | 10 5 | 15 | 5 | 1 | 1 | 1 5 | 15 | 5 | 2 | 2 | 2 5 | 15 | 5 | 0 | 3 | 3 5 | 15 | 5 | 1 | 4 | 4 5 | 15 | 5 | 2 | 5 | 5 5 | 15 | 5 | 0 | 6 | 6 5 | 15 | 5 | 1 | 7 | 7 5 | 15 | 5 | 2 | 8 | 8 5 | 15 | 5 | 0 | 9 | 9 5 | 15 | 5 | 1 | 10 | 10 6 | 18 | 0 | 1 | 1 | 1 6 | 18 | 0 | 2 | 2 | 2 6 | 18 | 0 | 0 | 3 | 3 6 | 18 | 0 | 1 | 4 | 4 6 | 18 | 0 | 2 | 5 | 5 6 | 18 | 0 | 0 | 6 | 6 6 | 18 | 0 | 1 | 7 | 7 6 | 18 | 0 | 2 | 8 | 8 6 | 18 | 0 | 0 | 9 | 9 6 | 18 | 0 | 1 | 10 | 10 7 | 21 | 1 | 1 | 1 | 1 7 | 21 | 1 | 2 | 2 | 2 7 | 21 | 1 | 0 | 3 | 3 7 | 21 | 1 | 1 | 4 | 4 7 | 21 | 1 | 2 | 5 | 5 7 | 21 | 1 | 0 | 6 | 6 7 | 21 | 1 | 1 | 7 | 7 7 | 21 | 1 | 2 | 8 | 8 7 | 21 | 1 | 0 | 9 | 9 7 | 21 | 1 | 1 | 10 | 10 3 | 9 | 3 | 1 | 1 | 1 3 | 9 | 3 | 2 | 2 | 2 3 | 9 | 3 | 0 | 3 | 3 3 | 9 | 3 | 1 | 4 | 4 3 | 9 | 3 | 2 | 5 | 5 3 | 9 | 3 | 0 | 6 | 6 3 | 9 | 3 | 1 | 7 | 7 3 | 9 | 3 | 2 | 8 | 8 3 | 9 | 3 | 0 | 9 | 9 3 | 9 | 3 | 1 | 10 | 10 4 | 12 | 4 | 1 | 1 | 1 4 | 12 | 4 | 2 | 2 | 2 4 | 12 | 4 | 0 | 3 | 3 4 | 12 | 4 | 1 | 4 | 4 4 | 12 | 4 | 2 | 5 | 5 4 | 12 | 4 | 0 | 6 | 6 4 | 12 | 4 | 1 | 7 | 7 4 | 12 | 4 | 2 | 8 | 8 4 | 12 | 4 | 0 | 9 | 9 4 | 12 | 4 | 1 | 10 | 10 8 | 24 | 2 | 1 | 1 | 1 8 | 24 | 2 | 2 | 2 | 2 8 | 24 | 2 | 0 | 3 | 3 8 | 24 | 2 | 1 | 4 | 4 8 | 24 | 2 | 2 | 5 | 5 8 | 24 | 2 | 0 | 6 | 6 8 | 24 | 2 | 1 | 7 | 7 8 | 24 | 2 | 2 | 8 | 8 8 | 24 | 2 | 0 | 9 | 9 8 | 24 | 2 | 1 | 10 | 10 9 | 27 | 3 | 1 | 1 | 1 9 | 27 | 3 | 2 | 2 | 2 9 | 27 | 3 | 0 | 3 | 3 9 | 27 | 3 | 1 | 4 | 4 9 | 27 | 3 | 2 | 5 | 5 9 | 27 | 3 | 0 | 6 | 6 9 | 27 | 3 | 1 | 7 | 7 9 | 27 | 3 | 2 | 8 | 8 9 | 27 | 3 | 0 | 9 | 9 9 | 27 | 3 | 1 | 10 | 10 10 | 30 | 4 | 1 | 1 | 1 10 | 30 | 4 | 2 | 2 | 2 10 | 30 | 4 | 0 | 3 | 3 10 | 30 | 4 | 1 | 4 | 4 10 | 30 | 4 | 2 | 5 | 5 10 | 30 | 4 | 0 | 6 | 6 10 | 30 | 4 | 1 | 7 | 7 10 | 30 | 4 | 2 | 8 | 8 10 | 30 | 4 | 0 | 9 | 9 10 | 30 | 4 | 1 | 10 | 10 (97 rows) select * from x_part left join x_non_part on (a > e); e | f | g | a | b | c ----+----+---+---+---+--- 8 | 24 | 2 | | | 9 | 27 | 3 | | | 10 | 30 | 4 | | | 1 | 3 | 1 | 2 | 2 | 2 1 | 3 | 1 | 2 | 5 | 5 1 | 3 | 1 | 2 | 8 | 8 2 | 6 | 2 | | | 5 | 15 | 5 | | | 6 | 18 | 0 | | | 7 | 21 | 1 | | | 3 | 9 | 3 | | | 4 | 12 | 4 | | | (12 rows) select * from x_part right join x_non_part on (a > e); e | f | g | a | b | c ---+---+---+---+----+---- | | | 1 | 1 | 1 1 | 3 | 1 | 2 | 2 | 2 | | | 0 | 3 | 3 | | | 1 | 4 | 4 1 | 3 | 1 | 2 | 5 | 5 | | | 0 | 6 | 6 | | | 1 | 7 | 7 1 | 3 | 1 | 2 | 8 | 8 | | | 0 | 9 | 9 | | | 1 | 10 | 10 (10 rows) select * from x_part join x_non_part on (my_equality(a,e)); e | f | g | a | b | c ----+----+---+---+----+---- 1 | 3 | 1 | 0 | 3 | 3 1 | 3 | 1 | 0 | 6 | 6 1 | 3 | 1 | 0 | 9 | 9 2 | 6 | 2 | 1 | 1 | 1 2 | 6 | 2 | 0 | 3 | 3 2 | 6 | 2 | 1 | 4 | 4 2 | 6 | 2 | 0 | 6 | 6 2 | 6 | 2 | 1 | 7 | 7 2 | 6 | 2 | 0 | 9 | 9 2 | 6 | 2 | 1 | 10 | 10 5 | 15 | 5 | 1 | 1 | 1 5 | 15 | 5 | 2 | 2 | 2 5 | 15 | 5 | 0 | 3 | 3 5 | 15 | 5 | 1 | 4 | 4 5 | 15 | 5 | 2 | 5 | 5 5 | 15 | 5 | 0 | 6 | 6 5 | 15 | 5 | 1 | 7 | 7 5 | 15 | 5 | 2 | 8 | 8 5 | 15 | 5 | 0 | 9 | 9 5 | 15 | 5 | 1 | 10 | 10 6 | 18 | 0 | 1 | 1 | 1 6 | 18 | 0 | 2 | 2 | 2 6 | 18 | 0 | 0 | 3 | 3 6 | 18 | 0 | 1 | 4 | 4 6 | 18 | 0 | 2 | 5 | 5 6 | 18 | 0 | 0 | 6 | 6 6 | 18 | 0 | 1 | 7 | 7 6 | 18 | 0 | 2 | 8 | 8 6 | 18 | 0 | 0 | 9 | 9 6 | 18 | 0 | 1 | 10 | 10 7 | 21 | 1 | 1 | 1 | 1 7 | 21 | 1 | 2 | 2 | 2 7 | 21 | 1 | 0 | 3 | 3 7 | 21 | 1 | 1 | 4 | 4 7 | 21 | 1 | 2 | 5 | 5 7 | 21 | 1 | 0 | 6 | 6 7 | 21 | 1 | 1 | 7 | 7 7 | 21 | 1 | 2 | 8 | 8 7 | 21 | 1 | 0 | 9 | 9 7 | 21 | 1 | 1 | 10 | 10 3 | 9 | 3 | 1 | 1 | 1 3 | 9 | 3 | 2 | 2 | 2 3 | 9 | 3 | 0 | 3 | 3 3 | 9 | 3 | 1 | 4 | 4 3 | 9 | 3 | 2 | 5 | 5 3 | 9 | 3 | 0 | 6 | 6 3 | 9 | 3 | 1 | 7 | 7 3 | 9 | 3 | 2 | 8 | 8 3 | 9 | 3 | 0 | 9 | 9 3 | 9 | 3 | 1 | 10 | 10 4 | 12 | 4 | 1 | 1 | 1 4 | 12 | 4 | 2 | 2 | 2 4 | 12 | 4 | 0 | 3 | 3 4 | 12 | 4 | 1 | 4 | 4 4 | 12 | 4 | 2 | 5 | 5 4 | 12 | 4 | 0 | 6 | 6 4 | 12 | 4 | 1 | 7 | 7 4 | 12 | 4 | 2 | 8 | 8 4 | 12 | 4 | 0 | 9 | 9 4 | 12 | 4 | 1 | 10 | 10 8 | 24 | 2 | 1 | 1 | 1 8 | 24 | 2 | 2 | 2 | 2 8 | 24 | 2 | 0 | 3 | 3 8 | 24 | 2 | 1 | 4 | 4 8 | 24 | 2 | 2 | 5 | 5 8 | 24 | 2 | 0 | 6 | 6 8 | 24 | 2 | 1 | 7 | 7 8 | 24 | 2 | 2 | 8 | 8 8 | 24 | 2 | 0 | 9 | 9 8 | 24 | 2 | 1 | 10 | 10 9 | 27 | 3 | 1 | 1 | 1 9 | 27 | 3 | 2 | 2 | 2 9 | 27 | 3 | 0 | 3 | 3 9 | 27 | 3 | 1 | 4 | 4 9 | 27 | 3 | 2 | 5 | 5 9 | 27 | 3 | 0 | 6 | 6 9 | 27 | 3 | 1 | 7 | 7 9 | 27 | 3 | 2 | 8 | 8 9 | 27 | 3 | 0 | 9 | 9 9 | 27 | 3 | 1 | 10 | 10 10 | 30 | 4 | 1 | 1 | 1 10 | 30 | 4 | 2 | 2 | 2 10 | 30 | 4 | 0 | 3 | 3 10 | 30 | 4 | 1 | 4 | 4 10 | 30 | 4 | 2 | 5 | 5 10 | 30 | 4 | 0 | 6 | 6 10 | 30 | 4 | 1 | 7 | 7 10 | 30 | 4 | 2 | 8 | 8 10 | 30 | 4 | 0 | 9 | 9 10 | 30 | 4 | 1 | 10 | 10 (90 rows) -- Bug-fix verification for MPP-25537: PANIC when bitmap index used in ORCA select CREATE TABLE mpp25537_facttable1 ( col1 integer, wk_id smallint, id integer ) with (appendonly=true, orientation=column, compresstype=zlib, compresslevel=5) partition by range (wk_id) ( start (1::smallint) END (20::smallint) inclusive every (1), default partition dflt ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1' as the Greenplum Database 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. NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_dflt" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_2" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_3" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_4" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_5" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_6" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_7" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_8" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_9" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_10" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_11" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_12" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_13" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_14" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_15" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_16" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_17" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_18" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_19" for table "mpp25537_facttable1" NOTICE: CREATE TABLE will create partition "mpp25537_facttable1_1_prt_20" for table "mpp25537_facttable1" insert into mpp25537_facttable1 select col1, col1, col1 from (select generate_series(1,20) col1) a; CREATE TABLE mpp25537_dimdate ( wk_id smallint, col2 date ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'wk_id' as the Greenplum Database 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 mpp25537_dimdate select col1, current_date - col1 from (select generate_series(1,20,2) col1) a; CREATE TABLE mpp25537_dimtabl1 ( id integer, col2 integer ); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database 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 mpp25537_dimtabl1 select col1, col1 from (select generate_series(1,20,3) col1) a; CREATE INDEX idx_mpp25537_facttable1 on mpp25537_facttable1 (id); NOTICE: building index for child partition "mpp25537_facttable1_1_prt_dflt" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_2" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_3" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_4" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_5" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_6" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_7" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_8" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_9" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_10" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_11" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_12" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_13" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_14" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_15" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_16" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_17" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_18" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_19" NOTICE: building index for child partition "mpp25537_facttable1_1_prt_20" set optimizer_analyze_root_partition to on; ANALYZE mpp25537_facttable1; ANALYZE mpp25537_dimdate; ANALYZE mpp25537_dimtabl1; SELECT count(*) FROM mpp25537_facttable1 ft, mpp25537_dimdate dt, mpp25537_dimtabl1 dt1 WHERE ft.wk_id = dt.wk_id AND ft.id = dt1.id; count ------- 4 (1 row) -- -- This threw an error at one point: -- ERROR: FULL JOIN is only supported with merge-joinable join conditions -- create table fjtest_a (aid oid); create table fjtest_b (bid oid); create table fjtest_c (cid oid); insert into fjtest_a values (0), (1), (2); insert into fjtest_b values (0), (2), (3); insert into fjtest_c values (0), (3), (4); select * from ( select * from fjtest_a a, fjtest_b b where (aid = bid) ) s full outer join fjtest_c on (s.aid = cid); aid | bid | cid -----+-----+----- 0 | 0 | 0 2 | 2 | | | 3 | | 4 (4 rows) -- Do not push down any implied predicates to the Left Outer Join CREATE TABLE member(member_id int NOT NULL, group_id int NOT NULL) DISTRIBUTED BY(member_id); CREATE TABLE member_group(group_id int NOT NULL) DISTRIBUTED BY(group_id); CREATE TABLE region(region_id char(4), county_name varchar(25)) DISTRIBUTED BY(region_id); CREATE TABLE member_subgroup(subgroup_id int NOT NULL, group_id int NOT NULL, subgroup_name text) DISTRIBUTED RANDOMLY; INSERT INTO region SELECT i, i FROM generate_series(1, 200) i; INSERT INTO member_group SELECT i FROM generate_series(1, 15) i; INSERT INTO member SELECT i, i%15 FROM generate_series(1, 10000) i; --start_ignore ANALYZE member; ANALYZE member_group; ANALYZE region; ANALYZE member_subgroup; --end_ignore EXPLAIN(COSTS OFF) SELECT member.member_id FROM member INNER JOIN member_group ON member.group_id = member_group.group_id INNER JOIN member_subgroup ON member_group.group_id = member_subgroup.group_id LEFT OUTER JOIN region ON (member_group.group_id IN (12,13,14,15) AND member_subgroup.subgroup_name = region.county_name); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice5; segments: 3) -> Hash Left Join Hash Cond: (member_subgroup.subgroup_name = (region.county_name)::text) Join Filter: (member_group."group_id" = ANY ('{12,13,14,15}'::integer[])) -> Redistribute Motion 3:3 (slice3; segments: 3) Hash Key: member_subgroup.subgroup_name -> Hash Join Hash Cond: ((member."group_id" = member_group."group_id") AND (member_subgroup."group_id" = member_group."group_id")) -> Redistribute Motion 3:3 (slice2; segments: 3) Hash Key: member."group_id" -> Hash Join Hash Cond: (member."group_id" = member_subgroup."group_id") -> Seq Scan on member -> Hash -> Broadcast Motion 3:3 (slice1; segments: 3) -> Seq Scan on member_subgroup -> Hash -> Seq Scan on member_group -> Hash -> Redistribute Motion 3:3 (slice4; segments: 3) Hash Key: (region.county_name)::text -> Seq Scan on region Optimizer: PQO version 2.69.0 (23 rows) -- Test colocated equijoins on coerced distribution keys CREATE TABLE coercejoin (a varchar(10), b varchar(10)) DISTRIBUTED BY (a); -- Positive test, the join should be colocated as the implicit cast from the -- parse rewrite is a relabeling (varchar::text). EXPLAIN (costs off) SELECT * FROM coercejoin a, coercejoin b WHERE a.a=b.a; QUERY PLAN ------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Hash Join Hash Cond: ((coercejoin.a)::text = (coercejoin_1.a)::text) -> Seq Scan on coercejoin -> Hash -> Seq Scan on coercejoin coercejoin_1 (7 rows) -- Negative test, the join should not be colocated since the cast is a coercion -- which cannot guarantee that the coerced value would hash to the same segment -- as the uncoerced tuple. EXPLAIN (costs off) SELECT * FROM coercejoin a, coercejoin b WHERE a.a::numeric=b.a::numeric; QUERY PLAN ------------------------------------------------------------------------------- Gather Motion 3:1 (slice3; segments: 3) -> Hash Join Hash Cond: ((coercejoin.a)::numeric = (coercejoin_1.a)::numeric) -> Redistribute Motion 3:3 (slice1; segments: 3) Hash Key: (coercejoin.a)::numeric -> Seq Scan on coercejoin -> Hash -> Redistribute Motion 3:3 (slice2; segments: 3) Hash Key: (coercejoin_1.a)::numeric -> Seq Scan on coercejoin coercejoin_1 (11 rows) -- -- Test NLJ with join conds on distr keys using equality, IS DISTINCT FROM & IS NOT DISTINCT FROM exprs -- create table nlj1 (a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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. create table nlj2 (a int, b int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database 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 nlj1 values (1, 1), (NULL, NULL); insert into nlj2 values (1, 5), (NULL, 6); set optimizer_enable_hashjoin=off; set enable_hashjoin=off; set enable_mergejoin=off; set enable_nestloop=on; explain select * from nlj1, nlj2 where nlj1.a = nlj2.a; QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324032.38 rows=2 width=16) -> Nested Loop (cost=0.00..1324032.38 rows=1 width=16) Join Filter: (nlj1.a = nlj2.a) -> Seq Scan on nlj1 (cost=0.00..431.00 rows=1 width=8) -> Seq Scan on nlj2 (cost=0.00..431.00 rows=1 width=8) Optimizer: PQO version 3.2.0 (6 rows) select * from nlj1, nlj2 where nlj1.a = nlj2.a; a | b | a | b ---+---+---+--- 1 | 1 | 1 | 5 (1 row) explain select * from nlj1, nlj2 where nlj1.a is not distinct from nlj2.a; QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1324032.40 rows=3 width=16) -> Nested Loop (cost=0.00..1324032.40 rows=1 width=16) Join Filter: (NOT (nlj1.a IS DISTINCT FROM nlj2.a)) -> Seq Scan on nlj1 (cost=0.00..431.00 rows=1 width=8) -> Seq Scan on nlj2 (cost=0.00..431.00 rows=1 width=8) Optimizer: PQO version 3.2.0 (6 rows) select * from nlj1, nlj2 where nlj1.a is not distinct from nlj2.a; a | b | a | b ---+---+---+--- | | | 6 1 | 1 | 1 | 5 (2 rows) explain select * from nlj1, (select NULL a, b from nlj2) other where nlj1.a is not distinct from other.a; QUERY PLAN ------------------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1324032.53 rows=4 width=16) -> Nested Loop (cost=0.00..1324032.53 rows=2 width=16) Join Filter: (NOT (nlj1.a IS DISTINCT FROM "outer".a)) -> Seq Scan on nlj1 (cost=0.00..431.00 rows=1 width=8) -> Materialize (cost=0.00..431.00 rows=1 width=8) -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8) Hash Key: "outer".a -> Result (cost=0.00..431.00 rows=1 width=8) -> Result (cost=0.00..431.00 rows=1 width=8) -> Seq Scan on nlj2 (cost=0.00..431.00 rows=1 width=4) Optimizer: PQO version 3.2.0 (11 rows) select * from nlj1, (select NULL a, b from nlj2) other where nlj1.a is not distinct from other.a; a | b | a | b ---+---+---+--- | | | 5 | | | 6 (2 rows) explain select * from nlj1, nlj2 where nlj1.a is distinct from nlj2.a; QUERY PLAN --------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1324032.90 rows=2 width=16) -> Nested Loop (cost=0.00..1324032.90 rows=1 width=16) Join Filter: (nlj1.a IS DISTINCT FROM nlj2.a) -> Seq Scan on nlj1 (cost=0.00..431.00 rows=1 width=8) -> Materialize (cost=0.00..431.00 rows=2 width=8) -> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..431.00 rows=2 width=8) -> Seq Scan on nlj2 (cost=0.00..431.00 rows=1 width=8) Optimizer: PQO version 3.2.0 (8 rows) select * from nlj1, nlj2 where nlj1.a is distinct from nlj2.a; a | b | a | b ---+---+---+--- | | 1 | 5 1 | 1 | | 6 (2 rows) reset optimizer_enable_hashjoin; reset enable_hashjoin; reset enable_mergejoin; reset enable_nestloop; -- -- At one point, we didn't ensure that the outer side of a NestLoop path -- was rescannable, if the NestLoop was used on the inner side of another -- NestLoop. -- -- See https://github.com/greenplum-db/gpdb/issues/6769. -- create table a (i int4); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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. create table b (i int4); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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. create table c (i int4, j int4); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database 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 a select g from generate_series(1,1) g; insert into b select g from generate_series(1,1) g; insert into c select g, g from generate_series(1, 100) g; create index on c (i,j); -- In order to get the plan we want, Index Scan on 'c' must appear -- much cheaper than a Seq Scan. In order to keep this test quick and small, -- we don't want to actually create a huge table, so cheat a little and -- force that stats to make it look big to the planner. set allow_system_table_mods = on; update pg_class set reltuples=10000000 where oid ='c'::regclass; set enable_hashjoin=off; set enable_mergejoin=off; set enable_nestloop=on; -- the plan should look something like this: -- -- QUERY PLAN -- --------------------------------------------------------------------------- -- Gather Motion 3:1 (slice3; segments: 3) -- -> Nested Loop [1] -- -> Broadcast Motion 3:3 (slice1; segments: 3) -- -> Seq Scan on b -- -> Materialize [6] -- -> Nested Loop [2] -- Join Filter: (b.i = a.i) -- -> Materialize [5] -- -> Broadcast Motion 3:3 (slice2; segments: 3) [3] -- -> Seq Scan on a -- -> Index Only Scan using c_i_j_idx on c -- Index Cond: (j = (a.i + b.i)) [4] -- Optimizer: Postgres query optimizer -- (14 rows) -- -- The crucal parts are: -- -- * Nested Loop join on the inner side of another Nested Loop join [1], [2] -- -- * Motion on the outer side of the inner Nested Loop join (the Broadcast -- Motion on top of "Seq Scan on a" [3]) -- -- * An Index scan in the innermost path, which uses an executor parameter -- from the outermost path ("b.i", in the Index Cond) [4] -- -- There must be a Materialize node on top of the "Broadcast Motion -> Seq Scan" -- path [5]. Otherwise, when the outermost scan on 'b' produces a new row, and -- the outer Nested Loop calls Rescan on its inner side, the Motion node would -- be rescanned. Note that the Materialize node at [6] does *not* shield the -- Motion node from rescanning! That Materialize node is rescanned, when the -- executor parameter 'b.i' changes. explain (costs off) select * from a, b, c where b.i = a.i and (a.i + b.i) = c.j; QUERY PLAN --------------------------------------------------------- Gather Motion 3:1 (slice2; segments: 3) -> Nested Loop Join Filter: true -> Broadcast Motion 3:3 (slice1; segments: 3) -> Hash Join Hash Cond: (a.i = b.i) -> Seq Scan on a -> Hash -> Seq Scan on b -> Index Scan using c_i_j_idx on c Index Cond: (j = (a.i + b.i)) Optimizer: PQO version 3.21.0 (12 rows) select * from a, b, c where b.i = a.i and (a.i + b.i) = c.j; i | i | i | j ---+---+---+--- 1 | 1 | 2 | 2 (1 row) reset enable_hashjoin; reset enable_mergejoin; reset enable_nestloop; -- Clean up. None of the objects we create are very interesting to keep around. reset search_path; set client_min_messages='warning'; drop schema bfv_joins cascade;