Thread: Postgres 10 problem with UNION ALL of null value in "subselect"
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
```
It worked on PostgreSQL 9.6.
Query without wrapping subselects (t1 and t2) works on both versions of PostgreSQL (9.6 and 10) well:
```
select 1::integer as a
union all
select null as a;
```
Is there some new optimization of query processing in PostgreSQL 10, which needs some "early type determination", but named subselects (t1 and t2) shades the type from first query?
Or could it be some regression bug?
Thanks for answer.
Martin Swiech
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... --
2018-04-19 5:01 GMT+02:00 Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>:
+1
At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote in < CAFjFpRf6Q0B9m2qqsQjw9vTyh8r2S =FG2SUb360Mg3CbxQ1ciA@mail. gmail.com> I think the wanted behavior is not resolving unknown for all FROM> 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.
clauses under union.
+1
Pavel
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center