Skip to content

Commit 5935092

Browse files
david-rowleydavid-rowley
committed
Fix poorly written regression test
bd10ec5 added code to allow redundant functionally dependent GROUP BY columns to be removed using unique indexes and NOT NULL constraints as proofs of functional dependency. In that commit, I (David) added a test to ensure that when there are multiple indexes available to remove columns that we pick the index that allows us to remove the most columns. This test was faulty as it assumed the t3 table's primary key index was valid to use as functional dependency proof, but that's not the case since that's defined as deferrable. Here we adjust the tests added by that commit to use the t2 table instead. That's defined with a non-deferrable primary key. Author: songjinzhou <tsinghualucky912@foxmail.com> Author: David Rowley <dgrowleyml@gmail.com> Reviewed-by: Japin Li <japinli@hotmail.com> Discussion: https://postgr.es/m/tencent_CD414C79D39668455DF80D35143B87634C08@qq.com
1 parent 217919d commit 5935092

File tree

2 files changed

+38
-38
lines changed

2 files changed

+38
-38
lines changed

‎src/test/regress/expected/aggregates.out

Lines changed: 25 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1448,71 +1448,71 @@ explain (costs off) select * from p_t1 group by a,b,c,d;
14481448
-> Seq Scan on p_t1_2
14491449
(5 rows)
14501450

1451-
create unique index t3_c_uidx on t3(c);
1451+
create unique index t2_z_uidx on t2(z);
14521452
-- Ensure we don't remove any columns from the GROUP BY for a unique
14531453
-- index on a NULLable column.
1454-
explain (costs off) select b,c from t3 group by b,c;
1454+
explain (costs off) select y,z from t2 group by y,z;
14551455
QUERY PLAN
14561456
----------------------
14571457
HashAggregate
1458-
Group Key: b, c
1459-
-> Seq Scan on t3
1458+
Group Key: y, z
1459+
-> Seq Scan on t2
14601460
(3 rows)
14611461

14621462
-- Make the column NOT NULL and ensure we remove the redundant column
1463-
alter table t3 alter column c set not null;
1464-
explain (costs off) select b,c from t3 group by b,c;
1463+
alter table t2 alter column z set not null;
1464+
explain (costs off) select y,z from t2 group by y,z;
14651465
QUERY PLAN
14661466
----------------------
14671467
HashAggregate
1468-
Group Key: c
1469-
-> Seq Scan on t3
1468+
Group Key: z
1469+
-> Seq Scan on t2
14701470
(3 rows)
14711471

14721472
-- When there are multiple supporting unique indexes and the GROUP BY contains
14731473
-- columns to cover all of those, ensure we pick the index with the least
14741474
-- number of columns so that we can remove more columns from the GROUP BY.
1475-
explain (costs off) select a,b,c from t3 group by a,b,c;
1475+
explain (costs off) select x,y,z from t2 group by x,y,z;
14761476
QUERY PLAN
14771477
----------------------
14781478
HashAggregate
1479-
Group Key: c
1480-
-> Seq Scan on t3
1479+
Group Key: z
1480+
-> Seq Scan on t2
14811481
(3 rows)
14821482

14831483
-- As above but try ordering the columns differently to ensure we get the
14841484
-- same result.
1485-
explain (costs off) select a,b,c from t3 group by c,a,b;
1485+
explain (costs off) select x,y,z from t2 group by z,x,y;
14861486
QUERY PLAN
14871487
----------------------
14881488
HashAggregate
1489-
Group Key: c
1490-
-> Seq Scan on t3
1489+
Group Key: z
1490+
-> Seq Scan on t2
14911491
(3 rows)
14921492

14931493
-- Ensure we don't use a partial index as proof of functional dependency
1494-
drop index t3_c_uidx;
1495-
create index t3_c_uidx on t3 (c) where c > 0;
1496-
explain (costs off) select b,c from t3 group by b,c;
1494+
drop index t2_z_uidx;
1495+
create index t2_z_uidx on t2 (z) where z > 0;
1496+
explain (costs off) select y,z from t2 group by y,z;
14971497
QUERY PLAN
14981498
----------------------
14991499
HashAggregate
1500-
Group Key: b, c
1501-
-> Seq Scan on t3
1500+
Group Key: y, z
1501+
-> Seq Scan on t2
15021502
(3 rows)
15031503

15041504
-- A unique index defined as NULLS NOT DISTINCT does not need a supporting NOT
15051505
-- NULL constraint on the indexed columns. Ensure the redundant columns are
15061506
-- removed from the GROUP BY for such a table.
1507-
drop index t3_c_uidx;
1508-
alter table t3 alter column c drop not null;
1509-
create unique index t3_c_uidx on t3(c) nulls not distinct;
1510-
explain (costs off) select b,c from t3 group by b,c;
1507+
drop index t2_z_uidx;
1508+
alter table t2 alter column z drop not null;
1509+
create unique index t2_z_uidx on t2(z) nulls not distinct;
1510+
explain (costs off) select y,z from t2 group by y,z;
15111511
QUERY PLAN
15121512
----------------------
15131513
HashAggregate
1514-
Group Key: c
1515-
-> Seq Scan on t3
1514+
Group Key: z
1515+
-> Seq Scan on t2
15161516
(3 rows)
15171517

15181518
drop table t1 cascade;

‎src/test/regress/sql/aggregates.sql

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -507,37 +507,37 @@ create temp table p_t1_2 partition of p_t1 for values in(2);
507507
-- Ensure we can remove non-PK columns for partitioned tables.
508508
explain (costs off) select * from p_t1 group by a,b,c,d;
509509

510-
create unique index t3_c_uidx on t3(c);
510+
create unique index t2_z_uidx on t2(z);
511511

512512
-- Ensure we don't remove any columns from the GROUP BY for a unique
513513
-- index on a NULLable column.
514-
explain (costs off) select b,c from t3 group by b,c;
514+
explain (costs off) select y,z from t2 group by y,z;
515515

516516
-- Make the column NOT NULL and ensure we remove the redundant column
517-
alter table t3 alter column c set not null;
518-
explain (costs off) select b,c from t3 group by b,c;
517+
alter table t2 alter column z set not null;
518+
explain (costs off) select y,z from t2 group by y,z;
519519

520520
-- When there are multiple supporting unique indexes and the GROUP BY contains
521521
-- columns to cover all of those, ensure we pick the index with the least
522522
-- number of columns so that we can remove more columns from the GROUP BY.
523-
explain (costs off) select a,b,c from t3 group by a,b,c;
523+
explain (costs off) select x,y,z from t2 group by x,y,z;
524524

525525
-- As above but try ordering the columns differently to ensure we get the
526526
-- same result.
527-
explain (costs off) select a,b,c from t3 group by c,a,b;
527+
explain (costs off) select x,y,z from t2 group by z,x,y;
528528

529529
-- Ensure we don't use a partial index as proof of functional dependency
530-
drop index t3_c_uidx;
531-
create index t3_c_uidx on t3 (c) where c > 0;
532-
explain (costs off) select b,c from t3 group by b,c;
530+
drop index t2_z_uidx;
531+
create index t2_z_uidx on t2 (z) where z > 0;
532+
explain (costs off) select y,z from t2 group by y,z;
533533

534534
-- A unique index defined as NULLS NOT DISTINCT does not need a supporting NOT
535535
-- NULL constraint on the indexed columns. Ensure the redundant columns are
536536
-- removed from the GROUP BY for such a table.
537-
drop index t3_c_uidx;
538-
alter table t3 alter column c drop not null;
539-
create unique index t3_c_uidx on t3(c) nulls not distinct;
540-
explain (costs off) select b,c from t3 group by b,c;
537+
drop index t2_z_uidx;
538+
alter table t2 alter column z drop not null;
539+
create unique index t2_z_uidx on t2(z) nulls not distinct;
540+
explain (costs off) select y,z from t2 group by y,z;
541541

542542
drop table t1 cascade;
543543
drop table t2;

0 commit comments

Comments
 (0)