Google Git
Sign in
apache/cloudberry/refs/heads/main/./src/test/regress/expected/bfv_joins.out
blob: da6e74813182eff91e413837285eec45fb25086f [file]
-- start_matchsubs
-- m/\(runaway_cleaner\.c:\d+\)/
-- s/\(runaway_cleaner\.c:\d+\)/(runaway_cleaner.c:XXX)/
-- m/Used: \d+MB, available \d+MB, red zone: \d+MB/
-- s/Used: \d+MB, available \d+MB, red zone: \d+MB/Used: XXXMB, available XXXMB, red zone: XXXMB/
-- end_matchsubs
--
-- 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 Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into 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 Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into y (select * from x);
CREATE TABLE t1 (a int, b int, c int not null);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE t2 (a int, b int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE t3 (a int not null, b int, c int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,NULL,3);
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 -- Using column(s) named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
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 -- Using column(s) named 'c' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
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 -- Using column(s) named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
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 Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into 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 Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into 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 | 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
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
(100 rows)
SELECT * from x left join y on True where y.a > 0 and y.b > 0;
a | b | c | a | b | c
----+----+----+----+----+----
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 | 1 | 1 | 1
2 | 2 | 2 | 1 | 1 | 1
1 | 1 | 1 | 2 | 2 | 2
2 | 2 | 2 | 2 | 2 | 2
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 | 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 | 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 | 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 | 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 | 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 | 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 in (1,2,3);
a | b | c | a | b | c
----+----+----+---+---+---
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
1 | 1 | 1 | 3 | 3 | 3
2 | 2 | 2 | 3 | 3 | 3
1 | 1 | 1 | 1 | 1 | 1
2 | 2 | 2 | 1 | 1 | 1
1 | 1 | 1 | 2 | 2 | 2
2 | 2 | 2 | 2 | 2 | 2
(30 rows)
SELECT * from x left join y on True where y.a = y.b ;
a | b | c | a | b | c
----+----+----+----+----+----
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
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
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
1 | 1 | 1 | 1 | 1 | 1
2 | 2 | 2 | 1 | 1 | 1
1 | 1 | 1 | 2 | 2 | 2
2 | 2 | 2 | 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
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
(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 | 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 | 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
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
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
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 | 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
(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
----+----+----+----+----+----
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
8 | 8 | 8 | 8 | 8 | 8
8 | 8 | 8 | 9 | 9 | 9
8 | 8 | 8 | 10 | 10 | 10
8 | 8 | 8 | 1 | 1 | 1
8 | 8 | 8 | 2 | 2 | 2
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
9 | 9 | 9 | 8 | 8 | 8
9 | 9 | 9 | 9 | 9 | 9
9 | 9 | 9 | 10 | 10 | 10
9 | 9 | 9 | 1 | 1 | 1
9 | 9 | 9 | 2 | 2 | 2
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
10 | 10 | 10 | 8 | 8 | 8
10 | 10 | 10 | 9 | 9 | 9
10 | 10 | 10 | 10 | 10 | 10
10 | 10 | 10 | 1 | 1 | 1
10 | 10 | 10 | 2 | 2 | 2
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
1 | 1 | 1 | 8 | 8 | 8
1 | 1 | 1 | 9 | 9 | 9
1 | 1 | 1 | 10 | 10 | 10
1 | 1 | 1 | 1 | 1 | 1
1 | 1 | 1 | 2 | 2 | 2
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
2 | 2 | 2 | 8 | 8 | 8
2 | 2 | 2 | 9 | 9 | 9
2 | 2 | 2 | 10 | 10 | 10
2 | 2 | 2 | 1 | 1 | 1
2 | 2 | 2 | 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 | 8 | 8 | 8
3 | 3 | 3 | 9 | 9 | 9
3 | 3 | 3 | 10 | 10 | 10
3 | 3 | 3 | 1 | 1 | 1
3 | 3 | 3 | 2 | 2 | 2
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
4 | 4 | 4 | 8 | 8 | 8
4 | 4 | 4 | 9 | 9 | 9
4 | 4 | 4 | 10 | 10 | 10
4 | 4 | 4 | 1 | 1 | 1
4 | 4 | 4 | 2 | 2 | 2
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
5 | 5 | 5 | 8 | 8 | 8
5 | 5 | 5 | 9 | 9 | 9
5 | 5 | 5 | 10 | 10 | 10
5 | 5 | 5 | 1 | 1 | 1
5 | 5 | 5 | 2 | 2 | 2
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
6 | 6 | 6 | 8 | 8 | 8
6 | 6 | 6 | 9 | 9 | 9
6 | 6 | 6 | 10 | 10 | 10
6 | 6 | 6 | 1 | 1 | 1
6 | 6 | 6 | 2 | 2 | 2
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
7 | 7 | 7 | 8 | 8 | 8
7 | 7 | 7 | 9 | 9 | 9
7 | 7 | 7 | 10 | 10 | 10
7 | 7 | 7 | 1 | 1 | 1
7 | 7 | 7 | 2 | 2 | 2
(100 rows)
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t1.b IS DISTINCT FROM t2.b;
a | b | c | a | b
---+---+---+---+---
2 | 1 | 2 | 2 | 3
1 | 1 | 1 | |
(2 rows)
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t1.b IS DISTINCT FROM NULL;
a | b | c | a | b
---+---+---+---+---
2 | 1 | 2 | 2 | 3
1 | 1 | 1 | |
(2 rows)
SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a WHERE t2.b IS DISTINCT FROM NULL;
a | b | c | a | b
---+---+---+---+---
2 | 1 | 2 | 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 | c | a | b
---+---+---+---+---
1 | 1 | 1 | |
3 | | 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 | c | a | b
---+---+---+---+---
3 | | 3 | |
(1 row)
--- Tests for LOJ with single predicate uses columns of outer child only
explain select t1.* from t1 left outer join t3 on t1.b=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10059962503.02 rows=6068410 width=12)
-> Nested Loop Left Join (cost=10000000000.00..10059881590.89 rows=2022803 width=12)
Join Filter: (t1.b = 1)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=12)
-> Materialize (cost=0.00..1721.83 rows=77900 width=0)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1332.33 rows=77900 width=0)
-> Seq Scan on t3 (cost=0.00..293.67 rows=25967 width=0)
Optimizer: Postgres query optimizer
(8 rows)
select t1.* from t1 left outer join t3 on t1.b=1;
a | b | c
---+---+---
1 | 1 | 1
2 | 1 | 2
3 | | 3
(3 rows)
explain select t1.* from t1 left outer join t3 on t1.c=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10059962503.02 rows=6068410 width=12)
-> Nested Loop Left Join (cost=10000000000.00..10059881590.89 rows=2022803 width=12)
Join Filter: (t1.c = 1)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=12)
-> Materialize (cost=0.00..1721.83 rows=77900 width=0)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1332.33 rows=77900 width=0)
-> Seq Scan on t3 (cost=0.00..293.67 rows=25967 width=0)
Optimizer: Postgres query optimizer
(8 rows)
select t1.* from t1 left outer join t3 on t1.c=1;
a | b | c
---+---+---
1 | 1 | 1
2 | 1 | 2
3 | | 3
(3 rows)
--- Tests for LOJ with null-filtering on self check conditions.
--- make sure that we dont optimize the equality checks of inner table of LOJ.
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10040215447.37 rows=1011401667 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10026730091.81 rows=337133889 width=4)
Filter: ((t3.a = t3.a) IS NULL)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=4)
-> Materialize (cost=0.00..834.64 rows=25967 width=4)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=4)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=4)
Filter: (b > a)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL;
c
---
2
3
1
(3 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t1.c = t1.c IS NULL;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000052373.42 rows=2022803 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10000025402.71 rows=674268 width=4)
-> Seq Scan on t1 (cost=0.00..358.58 rows=26 width=4)
Filter: ((c = c) IS NULL)
-> Materialize (cost=0.00..834.64 rows=25967 width=0)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=0)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=0)
Filter: (b > a)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t1.c = t1.c IS NULL;
c
---
(0 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL and t3.b=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000002619.50 rows=101101 width=4)
-> Nested Loop (cost=10000000000.00..10000001271.49 rows=33700 width=4)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..553.36 rows=1 width=0)
-> Seq Scan on t3 (cost=0.00..553.33 rows=1 width=0)
Filter: ((b > a) AND ((a = a) IS NULL) AND (2 > a) AND (b = 2))
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=4)
Optimizer: Postgres query optimizer
(7 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL and t3.a=2;
c
---
(0 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL and t1.b=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000040573.74 rows=1011402 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10000027088.38 rows=337134 width=4)
Filter: ((t3.a = t3.a) IS NULL)
-> Seq Scan on t1 (cost=0.00..358.58 rows=26 width=4)
Filter: (b = 1)
-> Materialize (cost=0.00..834.64 rows=25967 width=4)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=4)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=4)
Filter: (b > a)
Optimizer: Postgres query optimizer
(10 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL and t1.b=1;
c
---
2
1
(2 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL or t3.a is NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10046958125.15 rows=1517102500 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10026730091.81 rows=505700833 width=4)
Filter: (((t3.a = t3.a) IS NULL) OR (t3.a IS NULL))
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=4)
-> Materialize (cost=0.00..834.64 rows=25967 width=4)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=4)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=4)
Filter: (b > a)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL or t3.a is NULL;
c
---
2
3
1
(3 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL or t3.b=2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10041914602.17 rows=1012413068 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10028415761.26 rows=337471023 width=4)
Filter: (((t3.a = t3.a) IS NULL) OR (t3.b = 2))
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=4)
-> Materialize (cost=0.00..834.64 rows=25967 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=8)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=8)
Filter: (b > a)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL or t3.b=2;
c
---
2
3
1
(3 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL or t1.a=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10041914602.17 rows=1012413068 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10028415761.26 rows=337471023 width=4)
Filter: (((t3.a = t3.a) IS NULL) OR (t1.a = 1))
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=8)
-> Materialize (cost=0.00..834.64 rows=25967 width=4)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=4)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=4)
Filter: (b > a)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN t3 ON t3.b > t3.a WHERE t3.a = t3.a IS NULL or t1.a=1;
c
---
2
3
1
(3 rows)
explain SELECT t.c FROM (select t1.*, t1.a+t1.b as cc from t1)t LEFT OUTER JOIN t3 ON t.cc = t.cc IS NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10110451674.22 rows=3034205000 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10069995607.56 rows=1011401667 width=4)
Join Filter: (((t1.a + t1.b) = (t1.a + t1.b)) IS NULL)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=12)
-> Materialize (cost=0.00..1721.83 rows=77900 width=0)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1332.33 rows=77900 width=0)
-> Seq Scan on t3 (cost=0.00..293.67 rows=25967 width=0)
Optimizer: Postgres query optimizer
(8 rows)
SELECT t.c FROM (select t1.*, t1.a+t1.b as cc from t1)t LEFT OUTER JOIN t3 ON t.cc = t.cc IS NULL;
c
---
2
3
1
(3 rows)
explain SELECT t.c FROM (select t1.*, t1.a+t1.b as cc from t1)t LEFT OUTER JOIN t3 ON t3.a > t3.b where t.cc = t.cc IS NULL;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10000052503.26 rows=2022803 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10000025532.55 rows=674268 width=4)
-> Seq Scan on t1 (cost=0.00..488.42 rows=26 width=4)
Filter: (((a + b) = (a + b)) IS NULL)
-> Materialize (cost=0.00..834.64 rows=25967 width=0)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=0)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=0)
Filter: (a > b)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t.c FROM (select t1.*, t1.a+t1.b as cc from t1)t LEFT OUTER JOIN t3 ON t3.a > t3.b where t.cc = t.cc IS NULL;
c
---
3
(1 row)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN (select t3.*, t3.a+t3.b as cc from t3)t ON t.cc = t.cc IS NULL;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10012810957.32 rows=6068410 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10012730045.19 rows=2022803 width=4)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=4)
-> Materialize (cost=0.00..489.84 rows=78 width=0)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..489.46 rows=78 width=0)
-> Seq Scan on t3 (cost=0.00..488.42 rows=26 width=0)
Filter: (((a + b) = (a + b)) IS NULL)
Optimizer: Postgres query optimizer
(8 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN (select t3.*, t3.a+t3.b as cc from t3)t ON t.cc = t.cc IS NULL;
c
---
2
3
1
(3 rows)
explain SELECT t1.c FROM t1 LEFT OUTER JOIN (select t3.*, t3.a+t3.b as cc from t3)t ON t.b > t.a WHERE t.cc = t.cc IS NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000000.00..10043586786.26 rows=1011401667 width=4)
-> Nested Loop Left Join (cost=10000000000.00..10030101430.70 rows=337133889 width=4)
Filter: (((t3.a + t3.b) = (t3.a + t3.b)) IS NULL)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=4)
-> Materialize (cost=0.00..834.64 rows=25967 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..704.81 rows=25967 width=8)
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=8)
Filter: (b > a)
Optimizer: Postgres query optimizer
(9 rows)
SELECT t1.c FROM t1 LEFT OUTER JOIN (select t3.*, t3.a+t3.b as cc from t3)t ON t.b > t.a WHERE t.cc = t.cc IS NULL;
c
---
2
3
1
(3 rows)
-- Test for eliminating self check condition in subquery
explain SELECT * FROM t1 LEFT JOIN (select t3.b from t3 where t3.a
< t3.a) AS t ON t1.a = t.b;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=639.89..44124.32 rows=2022803 width=16)
-> Hash Left Join (cost=639.89..17153.61 rows=674268 width=16)
Hash Cond: (t1.a = t3.b)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=12)
-> Hash (cost=531.69..531.69 rows=8656 width=4)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..531.69 rows=8656 width=4)
Hash Key: t3.b
-> Seq Scan on t3 (cost=0.00..358.58 rows=8656 width=4)
Filter: (a < a)
Optimizer: Postgres query optimizer
(10 rows)
-- Test for unexpected NLJ qual
--
explain select 1 as mrs_t1 where 1 <= ALL (select x from z);
QUERY PLAN
------------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: (subplan)
SubPlan 1
-> Materialize (cost=0.00..0.01 rows=1 width=4)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=1 width=4)
-> Seq Scan on z (cost=0.00..0.00 rows=1 width=4)
Optimizer status: Postgres query optimizer
(7 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
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
(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
----+----+---+---+---+---
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 | | |
8 | 24 | 2 | | |
9 | 27 | 3 | | |
10 | 30 | 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
----+----+---+---+----+----
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
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
(90 rows)
--
-- 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 (slice1; segments: 3)
-> Hash Join
Hash Cond: (member."group_id" = member_group."group_id")
-> Seq Scan on member
-> Hash
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Hash Right Join
Hash Cond: ((region.county_name)::text = member_subgroup.subgroup_name)
Join Filter: (member_group."group_id" = ANY ('{12,13,14,15}'::integer[]))
-> Redistribute Motion 3:3 (slice3; segments: 3)
Hash Key: region.county_name
-> Seq Scan on region
-> Hash
-> Redistribute Motion 3:3 (slice4; segments: 3)
Hash Key: member_subgroup.subgroup_name
-> Hash Join
Hash Cond: (member_group."group_id" = member_subgroup."group_id")
-> Seq Scan on member_group
-> Hash
-> Redistribute Motion 3:3 (slice5; segments: 3)
Hash Key: member_subgroup."group_id"
-> Seq Scan on member_subgroup
Optimizer: Postgres query optimizer
(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: ((a.a)::text = (b.a)::text)
-> Seq Scan on coercejoin a
-> Hash
-> Seq Scan on coercejoin b
Optimizer: Postgres query optimizer
(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 (slice1; segments: 3)
-> Hash Join
Hash Cond: ((a.a)::numeric = (b.a)::numeric)
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: (a.a)::numeric
-> Seq Scan on coercejoin a
-> Hash
-> Redistribute Motion 3:3 (slice3; segments: 3)
Hash Key: (b.a)::numeric
-> Seq Scan on coercejoin b
Optimizer: Postgres query optimizer
(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 Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table nlj2 (a int, b int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into nlj1 values (1, 1), (NULL, NULL);
insert into nlj2 values (1, 5), (NULL, 6);
analyze nlj1;
analyze nlj2;
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..2.08 rows=3 width=16)
-> Nested Loop (cost=0.00..2.03 rows=1 width=16)
Join Filter: (nlj1.a = nlj2.a)
-> Seq Scan on nlj1 (cost=0.00..1.01 rows=1 width=8)
-> Seq Scan on nlj2 (cost=0.00..1.01 rows=1 width=8)
Optimizer: Postgres query optimizer
(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..4.31 rows=4 width=16)
-> Nested Loop (cost=0.00..4.31 rows=2 width=16)
Join Filter: (NOT (nlj1.a IS DISTINCT FROM nlj2.a))
-> Seq Scan on nlj1 (cost=0.00..2.02 rows=1 width=8)
-> Materialize (cost=0.00..2.13 rows=2 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..2.10 rows=2 width=8)
-> Seq Scan on nlj2 (cost=0.00..2.02 rows=1 width=8)
Optimizer: Postgres query optimizer
(8 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::int a, b from nlj2) other where nlj1.a is not distinct from other.a;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2.14 rows=3 width=16)
-> Nested Loop (cost=0.00..2.10 rows=1 width=16)
Join Filter: (NOT (nlj1.a IS DISTINCT FROM nlj2.a))
-> Seq Scan on nlj1 (cost=0.00..1.01 rows=1 width=8)
-> Materialize (cost=0.00..1.06 rows=3 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.05 rows=3 width=8)
-> Seq Scan on nlj2 (cost=0.00..1.01 rows=1 width=8)
Optimizer: Postgres query optimizer
(8 rows)
select * from nlj1, (select NULL::int 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 (slice1; segments: 3) (cost=0.00..4.31 rows=4 width=16)
-> Nested Loop (cost=0.00..4.31 rows=2 width=16)
Join Filter: (nlj1.a IS DISTINCT FROM nlj2.a)
-> Seq Scan on nlj1 (cost=0.00..2.02 rows=1 width=8)
-> Materialize (cost=0.00..2.13 rows=2 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..2.10 rows=2 width=8)
-> Seq Scan on nlj2 (cost=0.00..2.02 rows=1 width=8)
Optimizer: Postgres query optimizer
(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 Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table b (i int4);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table c (i int4, j int4);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into 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;
analyze a;
analyze b;
analyze c;
create index on c (j, i);
-- 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=1 where oid ='a'::regclass;
update pg_class set relpages=1 where oid ='a'::regclass;
update pg_class set reltuples=10 where oid ='b'::regclass;
update pg_class set relpages=10 where oid ='b'::regclass;
update pg_class set reltuples=10000000 where oid ='c'::regclass;
update pg_class set relpages=100000 where oid ='c'::regclass;
set enable_hashjoin=off;
set enable_mergejoin=off;
set enable_nestloop=on;
set random_page_cost=1;
set join_collapse_limit=1;
set from_collapse_limit=1;
-- the plan should look something like this:
--
-- QUERY PLAN
-- ---------------------------------------------------------------------------
-- Gather Motion 3:1 (slice1; segments: 3)
-- -> Nested Loop [1]
-- -> Broadcast Motion 3:3 (slice2; segments: 3)
-- -> Seq Scan on b
-- -> Materialize [6]
-- -> Nested Loop [2]
-- Join Filter: (b.i = a.i)
-- -> Materialize [5]
-- -> Broadcast Motion 3:3 (slice3; segments: 3) [3]
-- -> Seq Scan on a
-- -> Index Only Scan using c_j_i_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 b, lateral (select * from a, c where b.i = a.i and (a.i + b.i) = c.j) as ac;
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
Join Filter: ((a.i + b.i) = c.j)
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on b
-> Materialize
-> Nested Loop
Join Filter: (b.i = a.i)
-> Materialize
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on a
-> Index Only Scan using c_j_i_idx on c
Index Cond: (j = (a.i + b.i))
Optimizer: Postgres query optimizer
(13 rows)
select * from b, lateral (select * from a, c where b.i = a.i and (a.i + b.i) = c.j) as ac;
i | i | i | j
---+---+---+---
1 | 1 | 2 | 2
(1 row)
-- The above plan will prefetch inner plan and the inner plan refers
-- outerParams. Previously, we do not handle this case correct and forgot
-- to set the Params for nestloop in econtext. The outer Param is a compound
-- data type instead of simple integer, it will lead to PANIC.
-- See Github Issue: https://github.com/greenplum-db/gpdb/issues/9679
-- for details.
create type mytype_prefetch_params as (x int, y int);
alter table b add column mt_col mytype_prefetch_params;
explain select ac.*, b.i from b, lateral (select * from a, c where ((mt_col).x > a.i or b.i = a.i) and (a.i + b.i) = c.j) as ac;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.19..765906.72 rows=24 width=16)
-> Nested Loop (cost=0.19..765906.26 rows=8 width=16)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1.05 rows=1 width=36)
-> Seq Scan on b (cost=0.00..1.01 rows=1 width=36)
-> Materialize (cost=0.19..255301.72 rows=2 width=12)
-> Nested Loop (cost=0.19..255301.70 rows=2 width=12)
Join Filter: (((b.mt_col).x > a.i) OR (b.i = a.i))
-> Materialize (cost=0.00..1.06 rows=1 width=4)
-> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..1.05 rows=1 width=4)
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
-> Index Only Scan using c_j_i_idx on c (cost=0.19..85100.20 rows=1 width=8)
Index Cond: (j = (a.i + b.i))
Optimizer: Postgres query optimizer
(13 rows)
select ac.*, b.i from b, lateral (select * from a, c where ((mt_col).x > a.i or b.i = a.i) and (a.i + b.i) = c.j) as ac;
i | i | j | i
---+---+---+---
1 | 2 | 2 | 1
(1 row)
reset enable_hashjoin;
reset enable_mergejoin;
reset enable_nestloop;
reset join_collapse_limit;
reset from_collapse_limit;
--
-- Mix timestamp and timestamptz in a join. We cannot use a Redistribute
-- Motion, because the cross-datatype = operator between them doesn't belong
-- to any hash operator class. We cannot hash rows in a way that matches would
-- land on the same segment in that case.
--
CREATE TABLE gp_timestamp1 (a int, b timestamp, bb timestamptz) DISTRIBUTED BY (a, b);
CREATE TABLE gp_timestamp2 (c int, d timestamp, dd timestamptz) DISTRIBUTED BY (c, d);
INSERT INTO gp_timestamp1 VALUES
( 9, '2016/11/09', '2016/11/09'),
(10, '2016/11/10', '2016/11/10'),
(11, '2016/11/11', '2016/11/11'),
(12, '2016/11/12', '2016/11/12'),
(13, '2016/11/13', '2016/11/13');
INSERT INTO gp_timestamp2 VALUES
( 9, '2016/11/09', '2016/11/09'),
(10, '2016/11/10', '2016/11/10'),
(11, '2016/11/11', '2016/11/11'),
(12, '2016/11/12', '2016/11/12'),
(13, '2016/11/13', '2016/11/13');
ANALYZE gp_timestamp1;
ANALYZE gp_timestamp2;
SELECT a, b FROM gp_timestamp1 JOIN gp_timestamp2 ON a = c AND b = dd AND b = bb AND b = timestamp '2016/11/11';
a | b
----+--------------------------
11 | Fri Nov 11 00:00:00 2016
(1 row)
-- Similar case, but involving a constant
SELECT a, b FROM gp_timestamp1 JOIN gp_timestamp2 ON a = c AND b = timestamptz '2016/11/11';
a | b
----+--------------------------
11 | Fri Nov 11 00:00:00 2016
(1 row)
-- Similar case. Here, the =(float8, float4) cross-type operator would be
-- hashable using the default hash opclass. But not with the legacy cdbhash
-- opclass.
CREATE TABLE gp_float1 (a int, b real) DISTRIBUTED BY (a, b cdbhash_float4_ops);
CREATE TABLE gp_float2 (c int, d real) DISTRIBUTED BY (c, d cdbhash_float4_ops);
INSERT INTO gp_float1 SELECT i, i FROM generate_series(1, 5) i;
INSERT INTO gp_float1 SELECT i, 3 FROM generate_series(1, 5) i WHERE i <> 3;
INSERT INTO gp_float2 SELECT i, i FROM generate_series(1, 5) i;
ANALYZE gp_float1;
ANALYZE gp_float2;
EXPLAIN SELECT a, b FROM gp_float1 JOIN gp_float2 ON a = c AND b = float8 '3.0';
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice2; segments: 3) (cost=3.18..5.65 rows=5 width=8)
-> Hash Join (cost=3.18..5.55 rows=2 width=8)
Hash Cond: (gp_float2.c = gp_float1.a)
-> Broadcast Motion 3:3 (slice1; segments: 3) (cost=0.00..2.25 rows=5 width=4)
-> Seq Scan on gp_float2 (cost=0.00..2.05 rows=2 width=4)
-> Hash (cost=3.11..3.11 rows=2 width=8)
-> Seq Scan on gp_float1 (cost=0.00..3.11 rows=2 width=8)
Filter: (b = '3'::double precision)
Optimizer: Postgres query optimizer
(9 rows)
-- Another variation: There are two constants in the same equivalence class. One's
-- datatype is compatible with the distribution key, the other's is not. We can
-- redistribute based on the compatible constant.
EXPLAIN SELECT a, b FROM gp_float1 JOIN gp_float2 ON a = c AND b = float8 '3.0' AND b = float4 '3.0';
QUERY PLAN
----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=1.06..2.20 rows=5 width=8)
-> Hash Join (cost=1.06..2.13 rows=2 width=8)
Hash Cond: (gp_float2.c = gp_float1.a)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..1.05 rows=2 width=4)
Hash Key: gp_float2.c, '3'::real
-> Seq Scan on gp_float2 (cost=0.00..1.02 rows=2 width=4)
-> Hash (cost=1.04..1.04 rows=2 width=8)
-> Seq Scan on gp_float1 (cost=0.00..1.04 rows=2 width=8)
Filter: (b = '3'::double precision)
Optimizer: Postgres query optimizer
(10 rows)
-- Testing optimizer_enable_nljoin
SET optimizer_enable_hashjoin=off;
SET optimizer_enable_nljoin=off;
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
QUERY PLAN
------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=618.25..205952.92 rows=6707190 width=20)
-> Hash Join (cost=618.25..116523.72 rows=2235730 width=20)
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=293.67..293.67 rows=25967 width=12)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=12)
Optimizer: Postgres query optimizer
(7 rows)
SET optimizer_enable_nljoin=on;
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a=t2.a;
QUERY PLAN
------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=618.25..205952.92 rows=6707190 width=20)
-> Hash Join (cost=618.25..116523.72 rows=2235730 width=20)
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=293.67..293.67 rows=25967 width=12)
-> Seq Scan on t1 (cost=0.00..293.67 rows=25967 width=12)
Optimizer: Postgres query optimizer
(7 rows)
RESET optimizer_enable_hashjoin;
RESET optimizer_enable_nljoin;
-- Test hashed distribution spec derivation and --
-- motion enforcement given INDF join condition --
-- Outer joins' inner table yields false nulls --
-- colocation if join condition is null-aware --
--start_ignore
drop table o1;
ERROR: table "o1" does not exist
drop table o2;
ERROR: table "o2" does not exist
drop table o3;
ERROR: table "o3" does not exist
--end_ignore
-- Current case add in Derive Combined Hashed Spec For Outer Joins (#14045), f8264ad
-- GPDB will got wrong result in ORCA plan, util merged [FIXME] Wrong results on main branch for INDF query,ce25faf
create table o1 (a1 int, b1 int) distributed by (a1);
create table o2 (a2 int, b2 int) distributed by (a2);
create table o3 (a3 int, b3 int) distributed by (a3);
insert into o1 select i, i from generate_series(1,20) i;
insert into o2 select i, null from generate_series(11,30) i;
insert into o3 values (NULL, 20);
select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3;
a1 | b1 | a2 | b2 | a3 | b3
----+----+----+----+----+----
1 | 1 | | | | 20
12 | 12 | 12 | | |
15 | 15 | 15 | | |
20 | 20 | 20 | | |
2 | 2 | | | | 20
3 | 3 | | | | 20
4 | 4 | | | | 20
7 | 7 | | | | 20
8 | 8 | | | | 20
16 | 16 | 16 | | |
18 | 18 | 18 | | |
19 | 19 | 19 | | |
5 | 5 | | | | 20
6 | 6 | | | | 20
9 | 9 | | | | 20
10 | 10 | | | | 20
11 | 11 | 11 | | |
13 | 13 | 13 | | |
14 | 14 | 14 | | |
17 | 17 | 17 | | |
(20 rows)
select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3 and b2 is distinct from b3;
a1 | b1 | a2 | b2 | a3 | b3
----+----+----+----+----+----
1 | 1 | | | | 20
12 | 12 | 12 | | |
15 | 15 | 15 | | |
20 | 20 | 20 | | |
5 | 5 | | | | 20
6 | 6 | | | | 20
9 | 9 | | | | 20
10 | 10 | | | | 20
11 | 11 | 11 | | |
13 | 13 | 13 | | |
14 | 14 | 14 | | |
17 | 17 | 17 | | |
2 | 2 | | | | 20
3 | 3 | | | | 20
4 | 4 | | | | 20
7 | 7 | | | | 20
8 | 8 | | | | 20
16 | 16 | 16 | | |
18 | 18 | 18 | | |
19 | 19 | 19 | | |
(20 rows)
select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3 and b2 = b3;
a1 | b1 | a2 | b2 | a3 | b3
----+----+----+----+----+----
12 | 12 | 12 | | |
15 | 15 | 15 | | |
20 | 20 | 20 | | |
1 | 1 | | | |
16 | 16 | 16 | | |
18 | 18 | 18 | | |
19 | 19 | 19 | | |
8 | 8 | | | |
4 | 4 | | | |
7 | 7 | | | |
2 | 2 | | | |
3 | 3 | | | |
11 | 11 | 11 | | |
13 | 13 | 13 | | |
14 | 14 | 14 | | |
17 | 17 | 17 | | |
6 | 6 | | | |
5 | 5 | | | |
10 | 10 | | | |
9 | 9 | | | |
(20 rows)
explain select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000679.75..16298130027.03 rows=638277381 width=24)
-> Nested Loop Left Join (cost=10000000679.75..16289619661.95 rows=212759127 width=24)
Join Filter: (NOT (o2.a2 IS DISTINCT FROM o3.a3))
-> Hash Left Join (cost=679.75..128744.45 rows=2471070 width=16)
Hash Cond: (o1.a1 = o2.a2)
-> Seq Scan on o1 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on o2 (cost=0.00..321.00 rows=28700 width=8)
-> Materialize (cost=0.00..1899.50 rows=86100 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8)
-> Seq Scan on o3 (cost=0.00..321.00 rows=28700 width=8)
Optimizer: Postgres query optimizer
(12 rows)
explain select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3 and b2 is distinct from b3;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=10000000679.75..16830019334.16 rows=637639104 width=24)
-> Nested Loop Left Join (cost=10000000679.75..16821517479.45 rows=212546368 width=24)
Join Filter: ((NOT (o2.a2 IS DISTINCT FROM o3.a3)) AND (o2.b2 IS DISTINCT FROM o3.b3))
-> Hash Left Join (cost=679.75..128744.45 rows=2471070 width=16)
Hash Cond: (o1.a1 = o2.a2)
-> Seq Scan on o1 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on o2 (cost=0.00..321.00 rows=28700 width=8)
-> Materialize (cost=0.00..1899.50 rows=86100 width=8)
-> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..1469.00 rows=86100 width=8)
-> Seq Scan on o3 (cost=0.00..321.00 rows=28700 width=8)
Optimizer: Postgres query optimizer
(12 rows)
explain select * from o1 left join o2 on a1 = a2 left join o3 on a2 is not distinct from a3 and b2 = b3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=1933.50..335368.23 rows=7413210 width=24)
-> Hash Right Join (cost=1933.50..236525.43 rows=2471070 width=24)
Hash Cond: (o2.a2 = o1.a1)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=1253.75..108101.98 rows=28700 width=16)
Hash Key: o2.a2
-> Hash Left Join (cost=1253.75..107527.98 rows=28700 width=16)
Hash Cond: (o2.b2 = o3.b3)
Join Filter: (NOT (o2.a2 IS DISTINCT FROM o3.a3))
-> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..895.00 rows=28700 width=8)
Hash Key: o2.b2
-> Seq Scan on o2 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=895.00..895.00 rows=28700 width=8)
-> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..895.00 rows=28700 width=8)
Hash Key: o3.b3
-> Seq Scan on o3 (cost=0.00..321.00 rows=28700 width=8)
-> Hash (cost=321.00..321.00 rows=28700 width=8)
-> Seq Scan on o1 (cost=0.00..321.00 rows=28700 width=8)
Optimizer: Postgres query optimizer
(18 rows)
-- Test hashed distribution spec derived from a self join
truncate o1;
truncate o2;
insert into o1 select i, i from generate_series(1,9) i;
insert into o1 values (NULL, NULL);
insert into o2 select i, NULL from generate_series(11,100) i;
insert into o2 values (NULL, NULL);
analyze o1;
analyze o2;
-- Self join maintains the distribution keys from both children (i.e. the join
-- result produces a combine hash distribution spec)
--
-- Expect no redistribute under the joins
explain select t2.b1 from (select distinct a1 from o1) t1
left outer join (select a1, b1 from o1) t2 on t1.a1 = t2.a1
left outer join o1 t3 on t2.a1 = t3.a1
left outer join o1 t4 on t2.a1 = t4.a1;
QUERY PLAN
-----------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=3.29..4.57 rows=9 width=4)
-> Hash Right Join (cost=3.29..4.45 rows=3 width=4)
Hash Cond: (o1.a1 = o1_1.a1)
-> Hash Left Join (cost=2.15..3.27 rows=3 width=8)
Hash Cond: (o1.a1 = t4.a1)
-> Hash Left Join (cost=1.08..2.15 rows=3 width=8)
Hash Cond: (o1.a1 = t3.a1)
-> Seq Scan on o1 (cost=0.00..1.03 rows=3 width=8)
-> Hash (cost=1.03..1.03 rows=3 width=4)
-> Seq Scan on o1 t3 (cost=0.00..1.03 rows=3 width=4)
-> Hash (cost=1.03..1.03 rows=3 width=4)
-> Seq Scan on o1 t4 (cost=0.00..1.03 rows=3 width=4)
-> Hash (cost=1.10..1.10 rows=3 width=4)
-> HashAggregate (cost=1.04..1.07 rows=3 width=4)
Group Key: o1_1.a1
-> Seq Scan on o1 o1_1 (cost=0.00..1.03 rows=3 width=4)
Optimizer: Postgres-based planner
(17 rows)
select t2.b1 from (select distinct a1 from o1) t1
left outer join (select a1, b1 from o1) t2 on t1.a1 = t2.a1
left outer join o1 t3 on t2.a1 = t3.a1
left outer join o1 t4 on t2.a1 = t4.a1;
b1
----
2
3
4
7
8
1
5
6
9
(10 rows)
-- Self join maintains the distribution keys from both children (i.e. the join
-- result produces a combine hash distribution spec)
--
-- Expect no redistribute under the joins
explain (costs off) select t2.b1 from o1 t3
right outer join (select a1, b1 from o1) t2 on t2.a1 = t3.a1
right outer join o1 t4 on t2.a1 = t4.a1
right outer join (select distinct a1 from o1) t1 on t1.a1 = t2.a1;
QUERY PLAN
-------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Hash Right Join
Hash Cond: (o1.a1 = o1_1.a1)
-> Hash Left Join
Hash Cond: (o1.a1 = t3.a1)
-> Hash Join
Hash Cond: (o1.a1 = t4.a1)
-> Seq Scan on o1
-> Hash
-> Seq Scan on o1 t4
-> Hash
-> Seq Scan on o1 t3
-> Hash
-> HashAggregate
Group Key: o1_1.a1
-> Seq Scan on o1 o1_1
Optimizer: Postgres-based planner
(17 rows)
select t2.b1 from o1 t3
right outer join (select a1, b1 from o1) t2 on t2.a1 = t3.a1
right outer join o1 t4 on t2.a1 = t4.a1
right outer join (select distinct a1 from o1) t1 on t1.a1 = t2.a1;
b1
----
1
5
6
9
2
3
4
7
8
(10 rows)
-- Self join, but the projected distribution key value is changed
--
-- Expect redistribute under the joins
explain select t2.b1 from (select distinct a1+1 as a1 from o1) t1
left outer join o1 t2 on t2.a1 = t1.a1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=1.22..2.41 rows=9 width=4)
-> Hash Right Join (cost=1.22..2.29 rows=3 width=4)
Hash Cond: (t2.a1 = ((o1.a1 + 1)))
-> Seq Scan on o1 t2 (cost=0.00..1.03 rows=3 width=8)
-> Hash (cost=1.18..1.18 rows=3 width=4)
-> Unique (cost=1.14..1.15 rows=3 width=4)
Group Key: ((o1.a1 + 1))
-> Sort (cost=1.14..1.15 rows=3 width=4)
Sort Key: ((o1.a1 + 1))
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..1.11 rows=3 width=4)
Hash Key: ((o1.a1 + 1))
-> Seq Scan on o1 (cost=0.00..1.04 rows=3 width=4)
Optimizer: Postgres-based planner
(13 rows)
select t2.b1 from (select distinct a1+1 as a1 from o1) t1
left outer join o1 t2 on t2.a1 = t1.a1;
b1
----
5
6
9
2
3
4
7
8
(10 rows)
-- Self join, but the joined distribution key value is changed
--
-- Expect redistribute under the joins
explain select t2.b1 from (select distinct a1 from o1) t1
left outer join o1 t2 on t2.a1 = t1.a1+1;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=1.20..2.40 rows=9 width=4)
-> Hash Right Join (cost=1.20..2.28 rows=3 width=4)
Hash Cond: (t2.a1 = (o1.a1 + 1))
-> Seq Scan on o1 t2 (cost=0.00..1.03 rows=3 width=8)
-> Hash (cost=1.16..1.16 rows=3 width=4)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=1.04..1.16 rows=3 width=4)
Hash Key: (o1.a1 + 1)
-> HashAggregate (cost=1.04..1.07 rows=3 width=4)
Group Key: o1.a1
-> Seq Scan on o1 (cost=0.00..1.03 rows=3 width=4)
Optimizer: Postgres-based planner
(11 rows)
select t2.b1 from (select distinct a1 from o1) t1
left outer join o1 t2 on t2.a1 = t1.a1+1;
b1
----
5
6
9
2
3
4
7
8
(10 rows)
-- Test case from community Github PR 13722
create table t_13722(id int, tt timestamp)
distributed by (id);
-- j->jointype == join_lasj_notin
select
t1.*
from
t_13722 t1
where
t1.id not in (select id from t_13722 where id != 4)
and
t1.tt = (select min(tt) from t_13722 where id = t1.id);
id | tt
----+----
(0 rows)
-- j->jointype == join_anti
select
t1.*
from
t_13722 t1
where
not exists (select id from t_13722 where id != 4 and id = t1.id)
and t1.tt = (select min(tt) from t_13722 where id = t1.id);
id | tt
----+----
(0 rows)
drop table t_13722;
-- This test is introduced to verify incorrect result
-- from hash join of char columns is fixed
-- Notice when varchar/text is cast to bpchar and used for
-- comparison, the trailing spaces are ignored
-- When char is cast to varchar/text, it's considered
-- comparison, and the trailing spaces are also ignored
-- Prior to the fix, opclasses belonging to different
-- opfamilies could be grouped as equivalent, and thence
-- deriving incorrect equality hash join conditions
create table foo (varchar_3 varchar(3)) distributed by (varchar_3);
create table bar (char_3 char(3)) distributed by (char_3);
create table baz (text_any text) distributed by (text_any);
insert into foo values ('cd'); -- 0 trailing spaces
insert into bar values ('cd '); -- 1 trailing space
insert into baz values ('cd '); -- 2 trailing spaces
-- varchar cast to bpchar
-- 'cd' matches 'cd', returns 1 row
explain select varchar_3, char_3 from foo join bar on varchar_3=char_3;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=567.25..155836.25 rows=5055210 width=32)
-> Hash Join (cost=567.25..88433.45 rows=1685070 width=32)
Hash Cond: ((foo.varchar_3)::bpchar = bar.char_3)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..745.00 rows=23700 width=16)
Hash Key: foo.varchar_3
-> Seq Scan on foo (cost=0.00..271.00 rows=23700 width=16)
-> Hash (cost=271.00..271.00 rows=23700 width=16)
-> Seq Scan on bar (cost=0.00..271.00 rows=23700 width=16)
Optimizer: Postgres query optimizer
(9 rows)
select varchar_3, char_3 from foo join bar on varchar_3=char_3;
varchar_3 | char_3
-----------+--------
cd | cd
(1 row)
-- char cast to text
-- 'cd' doesn't match 'cd ', returns 0 rows
explain select char_3, text_any from bar join baz on char_3=text_any;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=430.00..168082.25 rows=3754080 width=48)
-> Hash Join (cost=430.00..118027.85 rows=1251360 width=48)
Hash Cond: ((bar.char_3)::text = baz.text_any)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..745.00 rows=23700 width=16)
Hash Key: (bar.char_3)::text
-> Seq Scan on bar (cost=0.00..271.00 rows=23700 width=16)
-> Hash (cost=210.00..210.00 rows=17600 width=32)
-> Seq Scan on baz (cost=0.00..210.00 rows=17600 width=32)
Optimizer: Postgres query optimizer
(9 rows)
select char_3, text_any from bar join baz on char_3=text_any;
char_3 | text_any
--------+----------
(0 rows)
-- foo - bar join: varchar cast to bpchar
-- 'cd' matches 'cd'
-- foo - baz join: no cast
-- 'cd' doesn't match 'cd '
-- returns 0 rows
-- Notice ORCA changes join order to minimize motion
explain select varchar_3, char_3, text_any from foo join bar on varchar_3=char_3
join baz on varchar_3=text_any;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=2141.25..15664266.93 rows=266915088 width=64)
-> Hash Join (cost=2141.25..12105399.09 rows=88971696 width=64)
Hash Cond: ((foo.varchar_3)::text = baz.text_any)
-> Hash Join (cost=567.25..88433.45 rows=1685070 width=32)
Hash Cond: ((foo.varchar_3)::bpchar = bar.char_3)
-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..745.00 rows=23700 width=16)
Hash Key: foo.varchar_3
-> Seq Scan on foo (cost=0.00..271.00 rows=23700 width=16)
-> Hash (cost=271.00..271.00 rows=23700 width=16)
-> Seq Scan on bar (cost=0.00..271.00 rows=23700 width=16)
-> Hash (cost=914.00..914.00 rows=52800 width=32)
-> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..914.00 rows=52800 width=32)
-> Seq Scan on baz (cost=0.00..210.00 rows=17600 width=32)
Optimizer: Postgres query optimizer
(14 rows)
select varchar_3, char_3, text_any from foo join bar on varchar_3=char_3
join baz on varchar_3=text_any;
varchar_3 | char_3 | text_any
-----------+--------+----------
(0 rows)
--
-- Test case for Hash Join rescan after squelched without hashtable built
-- See https://github.com/greenplum-db/gpdb/pull/15590
--
--- Lateral Join
set from_collapse_limit = 1;
set join_collapse_limit = 1;
select 1 from pg_namespace join lateral
(select * from aclexplode(nspacl) x join pg_authid on x.grantee = pg_authid.oid where rolname = current_user) z on true limit 1;
?column?
----------
1
(1 row)
reset from_collapse_limit;
reset join_collapse_limit;
--- NestLoop index join
create table l_table (a int, b int) distributed replicated;
create index l_table_idx on l_table(a);
create table r_table1 (ra1 int, rb1 int) distributed replicated;
create table r_table2 (ra2 int, rb2 int) distributed replicated;
insert into l_table select i % 10 , i from generate_series(1, 10000) i;
insert into r_table1 select i, i from generate_series(1, 1000) i;
insert into r_table2 values(11, 11), (1, 1) ;
analyze l_table;
analyze r_table1;
analyze r_table2;
set optimizer to off;
set enable_nestloop to on;
set enable_bitmapscan to off;
explain select * from r_table2 where ra2 in ( select a from l_table join r_table1 on b = rb1);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=64.56..64.56 rows=10 width=8)
-> Nested Loop Semi Join (cost=24.66..64.56 rows=10 width=8)
-> Seq Scan on r_table2 (cost=0.00..1.02 rows=2 width=8)
-> Hash Join (cost=24.66..62.75 rows=100 width=4)
Hash Cond: (l_table.b = r_table1.rb1)
-> Index Scan using l_table_idx on l_table (cost=0.16..25.62 rows=1000 width=8)
Index Cond: (a = r_table2.ra2)
-> Hash (cost=12.00..12.00 rows=1000 width=4)
-> Seq Scan on r_table1 (cost=0.00..12.00 rows=1000 width=4)
Optimizer: Postgres query optimizer
(10 rows)
select * from r_table2 where ra2 in ( select a from l_table join r_table1 on b = rb1);
ra2 | rb2
-----+-----
1 | 1
(1 row)
reset optimizer;
reset enable_nestloop;
reset enable_bitmapscan;
drop table l_table;
drop table r_table1;
drop table r_table2;
-- Should throw an error during planning: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
-- Falls back on GPORCA, but shouldn't cause GPORCA to crash
CREATE TABLE ext_stats_tbl(c0 name, c2 boolean);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c0' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE STATISTICS IF NOT EXISTS s0 (mcv) ON c2, c0 FROM ext_stats_tbl;
INSERT INTO ext_stats_tbl VALUES('tC', true);
ANALYZE ext_stats_tbl;
explain SELECT 1 FROM ext_stats_tbl t11 FULL JOIN ext_stats_tbl t12 ON t12.c2;
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
-- Check that Postgres Planner can build a plan with 20 joins in reasonable time
do $$
begin
for i in 1..20 loop
execute 'create table tj' || i || '(id int) distributed by (id)';
end loop;
end
$$;
set optimizer to off;
select trunc(extract(epoch from now())) unix_time1 \gset
select *
from tj1
join tj2 on tj1.id = tj2.id
join tj3 on tj2.id = tj3.id
join tj4 on tj3.id = tj4.id
join tj5 on tj4.id = tj5.id
join tj6 on tj5.id = tj6.id
join tj7 on tj6.id = tj7.id
join tj8 on tj7.id = tj8.id
join tj9 on tj8.id = tj9.id
join tj10 on tj9.id = tj10.id
join tj11 on tj10.id = tj11.id
join tj12 on tj11.id = tj12.id
join tj13 on tj12.id = tj13.id
join tj14 on tj13.id = tj14.id
join tj15 on tj14.id = tj15.id
join tj16 on tj15.id = tj16.id
join tj17 on tj16.id = tj17.id
join tj18 on tj17.id = tj18.id
join tj19 on tj18.id = tj19.id
join tj20 on tj19.id = tj20.id;
ERROR: Canceling query because of high VMEM usage. Used: XXXMB, available XXXMB, red zone: XXXMB (runaway_cleaner.c:XXX)
select (trunc(extract(epoch from now())) - :unix_time1) < 100 is_ok;
is_ok
-------
t
(1 row)
reset optimizer;
-- 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;