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-bitSimplified 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-bitSimplified 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
Attachments:
fix_unknown_resolve_behavior_in_union.patchtext/x-patch; charset=us-asciiDownload
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
:
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 LLVMversion
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
Show quoted text
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center