Postgres 10 problem with UNION ALL of null value in "subselect"

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres 10 problem with UNION ALL of null value in "subselect"

Martin Swiech
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

Reply | Threaded
Open this post in threaded view
|

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

Ashutosh Bapat
On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

Kyotaro HORIGUCHI-2
At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat <[hidden email]> wrote in <CAFjFpRf6Q0B9m2qqsQjw9vTyh8r2S=[hidden email]>

> On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <[hidden email]> 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...
 --
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

Pavel Stehule


2018-04-19 5:01 GMT+02:00 Kyotaro HORIGUCHI <[hidden email]>:
At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat <[hidden email]> wrote in <CAFjFpRf6Q0B9m2qqsQjw9vTyh8r2S=[hidden email]>
> On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <[hidden email]> 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.


+1

Pavel
 
regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Previous Thread Next Thread