postgrespro.git / summary / log / commit / refs

commit    823e739d4a7257cf0ca58fc6eff3c4cec308fccf
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Thu Jan 02 00:31:41 2020 +0000

    Test GROUP BY matching of join columns that are type-coerced by USING.
    
    If we have, say, an int column that is left-joined to a bigint column
    with USING, the merged column is the int column promoted to bigint.
    GROUP BY's tests for whether grouping on the merged column allows a
    reference to the underlying column, or vice versa, should know about
    that relationship --- and they do.  But I nearly broke this case with
    an ill-advised optimization, so the lack of any test coverage for it
    seems like a bad idea.


 src/test/regress/expected/aggregates.out | 26 ++++++++++++++++++++++++++
 src/test/regress/sql/aggregates.sql      | 15 +++++++++++++++
 2 files changed, 41 insertions(+)

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index d091ae4c6e..f457b5b150 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1194,6 +1194,32 @@ drop table t2;
 drop table t3;
 drop table p_t1;
 --
+-- Test GROUP BY matching of join columns that are type-coerced due to USING
+--
+create temp table t1(f1 int, f2 bigint);
+create temp table t2(f1 bigint, f22 bigint);
+select f1 from t1 left join t2 using (f1) group by f1;
+ f1 
+----
+(0 rows)
+
+select f1 from t1 left join t2 using (f1) group by t1.f1;
+ f1 
+----
+(0 rows)
+
+select t1.f1 from t1 left join t2 using (f1) group by t1.f1;
+ f1 
+----
+(0 rows)
+
+-- only this one should fail:
+select t1.f1 from t1 left join t2 using (f1) group by f1;
+ERROR:  column "t1.f1" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: select t1.f1 from t1 left join t2 using (f1) group by f1;
+               ^
+drop table t1, t2;
+--
 -- Test combinations of DISTINCT and/or ORDER BY
 --
 select array_agg(a order by b)
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 17fb256aec..3e593f2d61 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -432,6 +432,21 @@ drop table t2;
 drop table t3;
 drop table p_t1;
 
+--
+-- Test GROUP BY matching of join columns that are type-coerced due to USING
+--
+
+create temp table t1(f1 int, f2 bigint);
+create temp table t2(f1 bigint, f22 bigint);
+
+select f1 from t1 left join t2 using (f1) group by f1;
+select f1 from t1 left join t2 using (f1) group by t1.f1;
+select t1.f1 from t1 left join t2 using (f1) group by t1.f1;
+-- only this one should fail:
+select t1.f1 from t1 left join t2 using (f1) group by f1;
+
+drop table t1, t2;
+
 --
 -- Test combinations of DISTINCT and/or ORDER BY
 --


[parent: c5f3b53b0ef2]