As we've been merging our code with 9.6, a couple developers have had
one-off failures in the join.sql and aggregates.sql test because the tables
T1, T2 and T3 have the wrong definitions.

Digging into it, I found that both files create the tables T1, T2, and T3
for a short period of time and then drop them. Since the parallel_schedule
file puts these two files into the same group, they can run concurrently.
If it happens that the the two files hit the T1, T2, T3 tests at the same
time, then you see the table definition problems.

I took the easy way of solving this and renamed the tables in
aggregates.sql to AGG1, AGG2, and AGG3. (Picked on aggregates.sql since it
had the T1, T2, T3 tests added in 9.6.)

Doug
- Salesforce
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
***************
*** 957,1023 **** LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
  --
  -- Test removal of redundant GROUP BY columns
  --
! create temp table t1 (a int, b int, c int, d int, primary key (a, b));
! create temp table t2 (x int, y int, z int, primary key (x, y));
! create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
  -- Non-primary-key columns can be removed from GROUP BY
! explain (costs off) select * from t1 group by a,b,c,d;
!       QUERY PLAN      
! ----------------------
   HashAggregate
     Group Key: a, b
!    ->  Seq Scan on t1
  (3 rows)
  
  -- No removal can happen if the complete PK is not present in GROUP BY
! explain (costs off) select a,c from t1 group by a,c,d;
!       QUERY PLAN      
! ----------------------
   HashAggregate
     Group Key: a, c, d
!    ->  Seq Scan on t1
  (3 rows)
  
  -- Test removal across multiple relations
  explain (costs off) select *
! from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
! group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
!                       QUERY PLAN                       
! -------------------------------------------------------
   Group
!    Group Key: t1.a, t1.b, t2.x, t2.y
     ->  Merge Join
!          Merge Cond: ((t1.a = t2.x) AND (t1.b = t2.y))
!          ->  Index Scan using t1_pkey on t1
!          ->  Index Scan using t2_pkey on t2
  (6 rows)
  
! -- Test case where t1 can be optimized but not t2
! explain (costs off) select t1.*,t2.x,t2.z
! from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
! group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
!                       QUERY PLAN                       
! -------------------------------------------------------
   HashAggregate
!    Group Key: t1.a, t1.b, t2.x, t2.z
     ->  Merge Join
!          Merge Cond: ((t1.a = t2.x) AND (t1.b = t2.y))
!          ->  Index Scan using t1_pkey on t1
!          ->  Index Scan using t2_pkey on t2
  (6 rows)
  
  -- Cannot optimize when PK is deferrable
! explain (costs off) select * from t3 group by a,b,c;
!       QUERY PLAN      
! ----------------------
   HashAggregate
     Group Key: a, b, c
!    ->  Seq Scan on t3
  (3 rows)
  
! drop table t1;
! drop table t2;
! drop table t3;
  --
  -- Test combinations of DISTINCT and/or ORDER BY
  --
--- 957,1023 ----
  --
  -- Test removal of redundant GROUP BY columns
  --
! create temp table agg1 (a int, b int, c int, d int, primary key (a, b));
! create temp table agg2 (x int, y int, z int, primary key (x, y));
! create temp table agg3 (a int, b int, c int, primary key(a, b) deferrable);
  -- Non-primary-key columns can be removed from GROUP BY
! explain (costs off) select * from agg1 group by a,b,c,d;
!        QUERY PLAN       
! ------------------------
   HashAggregate
     Group Key: a, b
!    ->  Seq Scan on agg1
  (3 rows)
  
  -- No removal can happen if the complete PK is not present in GROUP BY
! explain (costs off) select a,c from agg1 group by a,c,d;
!        QUERY PLAN       
! ------------------------
   HashAggregate
     Group Key: a, c, d
!    ->  Seq Scan on agg1
  (3 rows)
  
  -- Test removal across multiple relations
  explain (costs off) select *
! from agg1 inner join agg2 on agg1.a = agg2.x and agg1.b = agg2.y
! group by agg1.a,agg1.b,agg1.c,agg1.d,agg2.x,agg2.y,agg2.z;
!                           QUERY PLAN                           
! ---------------------------------------------------------------
   Group
!    Group Key: agg1.a, agg1.b, agg2.x, agg2.y
     ->  Merge Join
!          Merge Cond: ((agg1.a = agg2.x) AND (agg1.b = agg2.y))
!          ->  Index Scan using agg1_pkey on agg1
!          ->  Index Scan using agg2_pkey on agg2
  (6 rows)
  
! -- Test case where agg1 can be optimized but not agg2
! explain (costs off) select agg1.*,agg2.x,agg2.z
! from agg1 inner join agg2 on agg1.a = agg2.x and agg1.b = agg2.y
! group by agg1.a,agg1.b,agg1.c,agg1.d,agg2.x,agg2.z;
!                           QUERY PLAN                           
! ---------------------------------------------------------------
   HashAggregate
!    Group Key: agg1.a, agg1.b, agg2.x, agg2.z
     ->  Merge Join
!          Merge Cond: ((agg1.a = agg2.x) AND (agg1.b = agg2.y))
!          ->  Index Scan using agg1_pkey on agg1
!          ->  Index Scan using agg2_pkey on agg2
  (6 rows)
  
  -- Cannot optimize when PK is deferrable
! explain (costs off) select * from agg3 group by a,b,c;
!        QUERY PLAN       
! ------------------------
   HashAggregate
     Group Key: a, b, c
!    ->  Seq Scan on agg3
  (3 rows)
  
! drop table agg1;
! drop table agg2;
! drop table agg3;
  --
  -- Test combinations of DISTINCT and/or ORDER BY
  --
*** a/src/test/regress/sql/aggregates.sql
--- b/src/test/regress/sql/aggregates.sql
***************
*** 339,370 **** select (select max(min(unique1)) from int8_tbl) from tenk1;
  -- Test removal of redundant GROUP BY columns
  --
  
! create temp table t1 (a int, b int, c int, d int, primary key (a, b));
! create temp table t2 (x int, y int, z int, primary key (x, y));
! create temp table t3 (a int, b int, c int, primary key(a, b) deferrable);
  
  -- Non-primary-key columns can be removed from GROUP BY
! explain (costs off) select * from t1 group by a,b,c,d;
  
  -- No removal can happen if the complete PK is not present in GROUP BY
! explain (costs off) select a,c from t1 group by a,c,d;
  
  -- Test removal across multiple relations
  explain (costs off) select *
! from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
! group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.y,t2.z;
  
! -- Test case where t1 can be optimized but not t2
! explain (costs off) select t1.*,t2.x,t2.z
! from t1 inner join t2 on t1.a = t2.x and t1.b = t2.y
! group by t1.a,t1.b,t1.c,t1.d,t2.x,t2.z;
  
  -- Cannot optimize when PK is deferrable
! explain (costs off) select * from t3 group by a,b,c;
  
! drop table t1;
! drop table t2;
! drop table t3;
  
  --
  -- Test combinations of DISTINCT and/or ORDER BY
--- 339,370 ----
  -- Test removal of redundant GROUP BY columns
  --
  
! create temp table agg1 (a int, b int, c int, d int, primary key (a, b));
! create temp table agg2 (x int, y int, z int, primary key (x, y));
! create temp table agg3 (a int, b int, c int, primary key(a, b) deferrable);
  
  -- Non-primary-key columns can be removed from GROUP BY
! explain (costs off) select * from agg1 group by a,b,c,d;
  
  -- No removal can happen if the complete PK is not present in GROUP BY
! explain (costs off) select a,c from agg1 group by a,c,d;
  
  -- Test removal across multiple relations
  explain (costs off) select *
! from agg1 inner join agg2 on agg1.a = agg2.x and agg1.b = agg2.y
! group by agg1.a,agg1.b,agg1.c,agg1.d,agg2.x,agg2.y,agg2.z;
  
! -- Test case where agg1 can be optimized but not agg2
! explain (costs off) select agg1.*,agg2.x,agg2.z
! from agg1 inner join agg2 on agg1.a = agg2.x and agg1.b = agg2.y
! group by agg1.a,agg1.b,agg1.c,agg1.d,agg2.x,agg2.z;
  
  -- Cannot optimize when PK is deferrable
! explain (costs off) select * from agg3 group by a,b,c;
  
! drop table agg1;
! drop table agg2;
! drop table agg3;
  
  --
  -- Test combinations of DISTINCT and/or ORDER BY
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to