Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5935092

Browse files
committed
Fix poorly written regression test
bd10ec5 added code to allow redundant functionally dependent GROUP BYcolumns to be removed using unique indexes and NOT NULL constraints asproofs of functional dependency. In that commit, I (David) added a testto ensure that when there are multiple indexes available to remove columnsthat we pick the index that allows us to remove the most columns. Thistest was faulty as it assumed the t3 table's primary key index was validto use as functional dependency proof, but that's not the case sincethat'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 parent217919d commit5935092

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 indext3_c_uidx ont3(c);
1451+
create unique indext2_z_uidx ont2(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) selectb,c fromt3 group byb,c;
1454+
explain (costs off) selecty,z fromt2 group byy,z;
14551455
QUERY PLAN
14561456
----------------------
14571457
HashAggregate
1458-
Group Key:b, c
1459-
-> Seq Scan ont3
1458+
Group Key:y, z
1459+
-> Seq Scan ont2
14601460
(3 rows)
14611461

14621462
-- Make the column NOT NULL and ensure we remove the redundant column
1463-
alter tablet3 alter columnc set not null;
1464-
explain (costs off) selectb,c fromt3 group byb,c;
1463+
alter tablet2 alter columnz set not null;
1464+
explain (costs off) selecty,z fromt2 group byy,z;
14651465
QUERY PLAN
14661466
----------------------
14671467
HashAggregate
1468-
Group Key:c
1469-
-> Seq Scan ont3
1468+
Group Key:z
1469+
-> Seq Scan ont2
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) selecta,b,c fromt3 group bya,b,c;
1475+
explain (costs off) selectx,y,z fromt2 group byx,y,z;
14761476
QUERY PLAN
14771477
----------------------
14781478
HashAggregate
1479-
Group Key:c
1480-
-> Seq Scan ont3
1479+
Group Key:z
1480+
-> Seq Scan ont2
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) selecta,b,c fromt3 group byc,a,b;
1485+
explain (costs off) selectx,y,z fromt2 group byz,x,y;
14861486
QUERY PLAN
14871487
----------------------
14881488
HashAggregate
1489-
Group Key:c
1490-
-> Seq Scan ont3
1489+
Group Key:z
1490+
-> Seq Scan ont2
14911491
(3 rows)
14921492

14931493
-- Ensure we don't use a partial index as proof of functional dependency
1494-
drop indext3_c_uidx;
1495-
create indext3_c_uidx ont3 (c) wherec > 0;
1496-
explain (costs off) selectb,c fromt3 group byb,c;
1494+
drop indext2_z_uidx;
1495+
create indext2_z_uidx ont2 (z) wherez > 0;
1496+
explain (costs off) selecty,z fromt2 group byy,z;
14971497
QUERY PLAN
14981498
----------------------
14991499
HashAggregate
1500-
Group Key:b, c
1501-
-> Seq Scan ont3
1500+
Group Key:y, z
1501+
-> Seq Scan ont2
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 indext3_c_uidx;
1508-
alter tablet3 alter columnc drop not null;
1509-
create unique indext3_c_uidx ont3(c) nulls not distinct;
1510-
explain (costs off) selectb,c fromt3 group byb,c;
1507+
drop indext2_z_uidx;
1508+
alter tablet2 alter columnz drop not null;
1509+
create unique indext2_z_uidx ont2(z) nulls not distinct;
1510+
explain (costs off) selecty,z fromt2 group byy,z;
15111511
QUERY PLAN
15121512
----------------------
15131513
HashAggregate
1514-
Group Key:c
1515-
-> Seq Scan ont3
1514+
Group Key:z
1515+
-> Seq Scan ont2
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_t1group by a,b,c,d;
509509

510-
createunique indext3_c_uidxont3(c);
510+
createunique indext2_z_uidxont2(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)selectb,cfromt3group byb,c;
514+
explain (costs off)selecty,zfromt2group byy,z;
515515

516516
-- Make the column NOT NULL and ensure we remove the redundant column
517-
altertablet3 alter columncsetnot null;
518-
explain (costs off)selectb,cfromt3group byb,c;
517+
altertablet2 alter columnzsetnot null;
518+
explain (costs off)selecty,zfromt2group byy,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)selecta,b,cfromt3group bya,b,c;
523+
explain (costs off)selectx,y,zfromt2group byx,y,z;
524524

525525
-- As above but try ordering the columns differently to ensure we get the
526526
-- same result.
527-
explain (costs off)selecta,b,cfromt3group byc,a,b;
527+
explain (costs off)selectx,y,zfromt2group byz,x,y;
528528

529529
-- Ensure we don't use a partial index as proof of functional dependency
530-
dropindext3_c_uidx;
531-
createindext3_c_uidxont3 (c)wherec>0;
532-
explain (costs off)selectb,cfromt3group byb,c;
530+
dropindext2_z_uidx;
531+
createindext2_z_uidxont2 (z)wherez>0;
532+
explain (costs off)selecty,zfromt2group byy,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-
dropindext3_c_uidx;
538-
altertablet3 alter columnc dropnot null;
539-
createunique indext3_c_uidxont3(c) nulls not distinct;
540-
explain (costs off)selectb,cfromt3group byb,c;
537+
dropindext2_z_uidx;
538+
altertablet2 alter columnz dropnot null;
539+
createunique indext2_z_uidxont2(z) nulls not distinct;
540+
explain (costs off)selecty,zfromt2group byy,z;
541541

542542
droptable t1 cascade;
543543
droptable t2;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp