Thread: [GENERAL] is this a known bug in 9.6?
Hi,
this is a stock PGDG 9.6:
postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select x from (select x from i union all select y from j) b;
x
---
1
2
(2 rows)
postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select max(x) from (select x from i union all select y from j) b;
ERROR: could not find plan for CTE "i"
x
---
1
2
(2 rows)
postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select max(x) from (select x from i union all select y from j) b;
ERROR: could not find plan for CTE "i"
The same on 9.5:
postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select x from (select x from i union all select y from j) b;
x
---
1
2
(2 rows)
postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select max(x) from (select x from i union all select y from j) b;
max
-----
2
(1 row)
x
---
1
2
(2 rows)
postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select max(x) from (select x from i union all select y from j) b;
max
-----
2
(1 row)
Is this a bug or is my assumption that this should work wrong?
Both the aggregate and the UNION are required to trigger the bug:
postgres=# with i(x) as (values (1::int)) select x from (select x from i union all select 3::int) b;
x
---
1
3
(2 rows)
postgres=# with i(x) as (values (1::int)) select max(x) from (select x from i) b;
max
-----
1
(1 row)
postgres=# with i(x) as (values (1::int)) select max(x) from (select x from i union all select 3::int) b;
ERROR: could not find plan for CTE "i"
x
---
1
3
(2 rows)
postgres=# with i(x) as (values (1::int)) select max(x) from (select x from i) b;
max
-----
1
(1 row)
postgres=# with i(x) as (values (1::int)) select max(x) from (select x from i union all select 3::int) b;
ERROR: could not find plan for CTE "i"
Thanks,
Torsten
=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select
> x from (select x from i union all select y from j) b;
> x
> ---
> 1
> 2
> (2 rows)
> postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select
> max(x) from (select x from i union all select y from j) b;
> ERROR: could not find plan for CTE "i"
Yup, sure looks like a bug to me, especially since it seems to work as
expected before 9.5. No idea offhand what broke it.
regards, tom lane
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Dienstag, 13. Dezember 2016 16:32
> To: Torsten Förtsch
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] is this a known bug in 9.6?
>
> =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> > postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int))
> > select x from (select x from i union all select y from j) b; x
> > ---
> > 1
> > 2
> > (2 rows)
>
> > postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int))
> > select
> > max(x) from (select x from i union all select y from j) b;
> > ERROR: could not find plan for CTE "i"
>
> Yup, sure looks like a bug to me, especially since it seems to work as
> expected before 9.5. No idea offhand what broke it.
>
> regards, tom lane
This is strange: using another aggreg. function works:
with i as (select 1::int x),
j as (select 1::int x)
select
count(x) from (select x from i union all select x from j) b;
---
2
with i as (select 1::int x),
j as (select 1::int x)
select
max(x) from (select x from i union all select x from j) b;
---
ERROR: could not find plan for CTE "i"
regards,
Marc Mamin
Thanks for confirming.
Here are a few more examples that also work:
with i(x) as (values (1::int)) select x from (select x from i union all select 3::int) b order by x desc limit 1;
with i(x) as (values (1::int)) select max(x) from (select x from i union select 3::int) b;
It also works with EXCEPT or INTERSECT, both with or without ALL.
The UNION ALL version fails with MIN and MAX but it works with all other aggregates that I have tested.
On Tue, Dec 13, 2016 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Torsten F\xC3=B6rtsch <tfoertsch123@gmail.com> writes:
> postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select
> x from (select x from i union all select y from j) b;
> x
> ---
> 1
> 2
> (2 rows)
> postgres=# with i(x) as (values (1::int)), j(y) as (values (2::int)) select
> max(x) from (select x from i union all select y from j) b;
> ERROR: could not find plan for CTE "i"
Yup, sure looks like a bug to me, especially since it seems to work as
expected before 9.5. No idea offhand what broke it.
regards, tom lane
I wrote:
> Yup, sure looks like a bug to me, especially since it seems to work as
> expected before 9.5. No idea offhand what broke it.
The answer is, I broke it, through some ill-advised neatnik-ism :-(,
ie clearing a field I thought would be unused but it wasn't.
Fix pushed. Thanks for the report!
regards, tom lane
Thanks Tom
On Tue, Dec 13, 2016 at 7:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
> Yup, sure looks like a bug to me, especially since it seems to work as
> expected before 9.5. No idea offhand what broke it.
The answer is, I broke it, through some ill-advised neatnik-ism :-(,
ie clearing a field I thought would be unused but it wasn't.
Fix pushed. Thanks for the report!
regards, tom lane