At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat
<[email protected]> wrote in
<CAFjFpRf6Q0B9m2qqsQjw9vTyh8r2S=fg2sub360mg3cbxq1...@mail.gmail.com>
> On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <[email protected]>
> 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...
--
