Обсуждение: Postgres 10 problem with UNION ALL of null value in "subselect"

On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <martin.swiech@gmail.com> wrote:
> Hi folks,
>
> I got some complex query which works on PostgreSQL 9.6 , but fails on
> PostgreSQL 10.
>
> Version of PostgreSQL:
> PostgreSQL 10.3 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version
> 7.0.0 (clang-700.1.76), 64-bit
>
> Simplified core of the problematic query looks like this:
> ```
> select * from (
>    select 1::integer as a
> ) t1
> union all
> select * from (
>    select null as a
> ) t2;
> ```
>
> It fails with this error message:
> ```
> ERROR:  UNION types integer and text cannot be matched
> LINE 5: select * from (
>                ^
> SQL state: 42804
> Character: 66
> ```
>

The error disappears if we go one commit before
1e7c4bb0049732ece651d993d03bb6772e5d281a, the error disappears. But
that's I think expected with that commit.

We can work around this problem by casting null to integer like null::integer.


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote in
<CAFjFpRf6Q0B9m2qqsQjw9vTyh8r2S=FG2SUb360Mg3CbxQ1ciA@mail.gmail.com>
> On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <martin.swiech@gmail.com> wrote:
> > Hi folks,
> >
> > I got some complex query which works on PostgreSQL 9.6 , but fails on
> > PostgreSQL 10.
> >
> > Version of PostgreSQL:
> > PostgreSQL 10.3 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version
> > 7.0.0 (clang-700.1.76), 64-bit
> >
> > Simplified core of the problematic query looks like this:
> > ```
> > select * from (
> >    select 1::integer as a
> > ) t1
> > union all
> > select * from (
> >    select null as a
> > ) t2;
> > ```
> >
> > It fails with this error message:
> > ```
> > ERROR:  UNION types integer and text cannot be matched
> > LINE 5: select * from (
> >                ^
> > SQL state: 42804
> > Character: 66
> > ```
> >
> 
> The error disappears if we go one commit before
> 1e7c4bb0049732ece651d993d03bb6772e5d281a, the error disappears. But
> that's I think expected with that commit.
> 
> We can work around this problem by casting null to integer like null::integer.

I think the wanted behavior is not resolving unknown for all FROM
clauses under union.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index e1478805c2..feb340b23e 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -473,11 +473,12 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
     pstate->p_lateral_active = r->lateral;
 
     /*
-     * Analyze and transform the subquery.
+     * Analyze and transform the subquery. Don't resolve unknowns if the
+     * parent is told so.
      */
     query = parse_sub_analyze(r->subquery, pstate, NULL,
                               isLockedRefname(pstate, r->alias->aliasname),
-                              true);
+                              pstate->p_resolve_unknowns);
 
     /* Restore state */
     pstate->p_lateral_active = false;
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 92d427a690..7ec4bf23f6 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -124,6 +124,16 @@ SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
    2
 (2 rows)
 
+-- Check that unknown type is not resolved for only FROM under union
+SELECT * FROM (SELECT 1 AS A WHERE '1' = (SELECT '1' AS X)) t1
+UNION ALL
+SELECT * FROM (SELECT '1' AS A) t2;
+ a 
+---
+ 1
+ 1
+(2 rows)
+
 --
 -- Try testing from tables...
 --
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index eed7c8d34b..1ba62b1c1b 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -40,6 +40,11 @@ SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
 
 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
 
+-- Check that unknown type is not resolved for only FROM under union
+SELECT * FROM (SELECT 1 AS A WHERE '1' = (SELECT '1' AS X)) t1
+UNION ALL
+SELECT * FROM (SELECT '1' AS A) t2;
+
 --
 -- Try testing from tables...
 --