Horton

Thread

Commits

  1. Fix use of incorrect TupleTableSlot in DISTINCT aggregates

  2. Improve performance of ORDER BY / DISTINCT aggregates

  1. BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type

    The Post Office <noreply@postgresql.org> — 2023-12-30T10:17:40Z

    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

  2. Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-12-30T16:13:15Z

    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

  3. Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type

    David Rowley <dgrowleyml@gmail.com> — 2023-12-31T03:24:54Z

    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

  4. Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type

    David Rowley <dgrowleyml@gmail.com> — 2024-01-04T07:43:00Z

    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