Обсуждение: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type

The following bug has been logged on the website:

Bug reference:      18264
Logged by:          Vojtěch Beneš
Email address:      vojtech.benes@centrum.cz
PostgreSQL version: 16.1
Operating system:   Ubuntu 22.04.3 LTS
Description:

One particular query outputs unexpected error instead of result data. This
behaviour is strange while change of order of columns seems to avoid issue.
Also removing DINSTINCT keyword in first column or WHERE statement in second
column also somehow avoid issue. I was able to isolate part that causes
problems and write following test case: 

SELECT
string_agg(DISTINCT 'a', ', ') agg,
sum(
  (
    SELECT sum(1)
    FROM (SELECT id FROM unnest(array[1]) id) B
    WHERE  A.id = B.id
  )
) sum
FROM (SELECT id FROM unnest(array[1]) id) A;

Both psql and pgadmin outputs this:
ERROR:  attribute 1 of type record has wrong type
SQL status: 42804
Detail: Table has type text, but query expects integer.

Behaviour was reproduced in multiple updated clean environments:
PostgreSQL 16.1 (Ubuntu 16.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
PostgreSQL 17devel (Ubuntu 17~~devel-1.pgdg22.04+~20231227.2235.g58054de) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0,
64-bit

Here query works as expected:
PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
PostgreSQL 15.5 (Ubuntu 15.5-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

While simplifiing query for test case I also managed to produce query with
sligthly different kind of behaviour. I believe it is related to case above
and slight change produces same errors:
ERROR:  attribute number 3 exceeds number of columns 2
SQL status: XX000

CREATE TEMPORARY TABLE _tmp_a ON COMMIT DROP AS
SELECT * FROM (
   values (1, false)
) as t(id, bool);

SELECT 
string_agg(distinct 'a', ', '),
sum(
    CASE 
    WHEN B.bool IS NOT FALSE AND B.id IS NOT NULL THEN 0 
    ELSE (
        SELECT sum(1)
        FROM (SELECT id FROM unnest(array[1]) id) C 
        WHERE A.id = C.id
    ) END
)
FROM (SELECT id FROM unnest(array[1]) id) A
CROSS JOIN _tmp_a B

PG Bug reporting form <noreply@postgresql.org> writes:
> One particular query outputs unexpected error instead of result data. This
> behaviour is strange while change of order of columns seems to avoid issue.
> Also removing DINSTINCT keyword in first column or WHERE statement in second
> column also somehow avoid issue. I was able to isolate part that causes
> problems and write following test case: 

> SELECT
> string_agg(DISTINCT 'a', ', ') agg,
> sum(
>   (
>     SELECT sum(1)
>     FROM (SELECT id FROM unnest(array[1]) id) B
>     WHERE  A.id = B.id
>   )
> ) sum
> FROM (SELECT id FROM unnest(array[1]) id) A;

> Both psql and pgadmin outputs this:
> ERROR:  attribute 1 of type record has wrong type
> SQL status: 42804
> Detail: Table has type text, but query expects integer.

Thanks for the report!  Bisecting shows it broke at

1349d2790bf48a4de072931c722f39337e72055e is the first bad commit
commit 1349d2790bf48a4de072931c722f39337e72055e
Author: David Rowley <drowley@postgresql.org>
Date:   Tue Aug 2 23:11:45 2022 +1200

    Improve performance of ORDER BY / DISTINCT aggregates

            regards, tom lane

On Sun, 31 Dec 2023 at 05:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > ERROR:  attribute 1 of type record has wrong type
> > SQL status: 42804
> > Detail: Table has type text, but query expects integer.
>
> Thanks for the report!  Bisecting shows it broke at
> 1349d2790bf48a4de072931c722f39337e72055e is the first bad commit

Yes, thanks for the report, Vojtěch, and for bisecting to find the
breaking commit, Tom.

It looks like the problem is in ExecEvalPreOrderedDistinctMulti(). It
overwrites the tmpcontext's slots but fails to properly restore the
originals again.

The Aggref->presorted == false version of this,
(process_ordered_aggregate_multi()) seems to use the
aggstate->tmpcontext for the same job, so I imagine I must have just
copied that, but just forgot to restore the old slots. It might be
nicer to have a dedicated ExprContext for this, but we've nowhere to
store that in the backbranches as we can't add a new field to that
struct. Maybe something like the attached is ok as a fix.

David

On Sun, 31 Dec 2023 at 16:24, David Rowley <dgrowleyml@gmail.com> wrote:
> It looks like the problem is in ExecEvalPreOrderedDistinctMulti(). It
> overwrites the tmpcontext's slots but fails to properly restore the
> originals again.

I pushed a slightly revised version of this.  I ended up adding a bool
isdistinct local variable so I didn't have to reset the slots in two
places.  It kinda makes it easier to see what the true and false
means, so that might have been a good change regardless of the bug.

Thanks for reporting this Vojtěch and for the reproducer script.

David

I am impressed with how quickly you addressed the issue. I tested queries myself on latest builds and issue is resolved. Also all errors we were getting in more complex queries are gone.

Thank you for your work.

Vojtěch

______________________________________________________________
> Od: "David Rowley" <dgrowleyml@gmail.com>
> Komu: "Tom Lane" <tgl@sss.pgh.pa.us>
> Datum: 04.01.2024 08:43
> Předmět: Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
>

> CC: <pgsql-bugs@lists.postgresql.org> On Sun, 31 Dec 2023 at 16:24, David Rowley <dgrowleyml@gmail.com> wrote:
> It looks like the problem is in ExecEvalPreOrderedDistinctMulti(). It
> overwrites the tmpcontext's slots but fails to properly restore the
> originals again.

I pushed a slightly revised version of this.  I ended up adding a bool
isdistinct local variable so I didn't have to reset the slots in two
places.  It kinda makes it easier to see what the true and false
means, so that might have been a good change regardless of the bug.

Thanks for reporting this Vojtěch and for the reproducer script.

David