pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
5+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2024-01-04 ae69c4fcf1 6298673f4b Fix use of incorrect TupleTableSlot in DISTINCT aggregates
* BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
@ 2023-12-30 10:17 PG Bug reporting form <noreply@postgresql.org>
2023-12-30 16:13 ` 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>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2023-12-30 10:17 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: vojtech.benes@centrum.cz
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
2023-12-30 10:17 BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type PG Bug reporting form <noreply@postgresql.org>
@ 2023-12-30 16:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-12-31 03:24 ` Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-12-30 16:13 UTC (permalink / raw)
To: vojtech.benes@centrum.cz; +Cc: pgsql-bugs@lists.postgresql.org, David Rowley <dgrowleyml@gmail.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
2023-12-30 10:17 BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type PG Bug reporting form <noreply@postgresql.org>
2023-12-30 16:13 ` 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-31 03:24 ` David Rowley <dgrowleyml@gmail.com>
2024-01-04 07:43 ` Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Rowley @ 2023-12-31 03:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: vojtech.benes@centrum.cz; pgsql-bugs@lists.postgresql.org
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
Attachments:
bug_18264_wip_fix.patch (text/plain, 1.3K) download
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 24c2b60c62..9f3d0be748 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4585,6 +4585,8 @@ bool
ExecEvalPreOrderedDistinctMulti(AggState *aggstate, AggStatePerTrans pertrans)
{
ExprContext *tmpcontext = aggstate->tmpcontext;
+ TupleTableSlot *save_outer;
+ TupleTableSlot *save_inner;
for (int i = 0; i < pertrans->numTransInputs; i++)
{
@@ -4596,6 +4598,10 @@ ExecEvalPreOrderedDistinctMulti(AggState *aggstate, AggStatePerTrans pertrans)
pertrans->sortslot->tts_nvalid = pertrans->numInputs;
ExecStoreVirtualTuple(pertrans->sortslot);
+ /* save the previous slots before we over write them. */
+ save_outer = tmpcontext->ecxt_outertuple;
+ save_inner = tmpcontext->ecxt_innertuple;
+
tmpcontext->ecxt_outertuple = pertrans->sortslot;
tmpcontext->ecxt_innertuple = pertrans->uniqslot;
@@ -4607,8 +4613,14 @@ ExecEvalPreOrderedDistinctMulti(AggState *aggstate, AggStatePerTrans pertrans)
pertrans->haslast = true;
ExecCopySlot(pertrans->uniqslot, pertrans->sortslot);
+
+ tmpcontext->ecxt_outertuple = save_outer;
+ tmpcontext->ecxt_innertuple = save_inner;
return true;
}
+
+ tmpcontext->ecxt_outertuple = save_outer;
+ tmpcontext->ecxt_innertuple = save_inner;
return false;
}
* Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
2023-12-30 10:17 BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type PG Bug reporting form <noreply@postgresql.org>
2023-12-30 16:13 ` 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-31 03:24 ` 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-04 07:43 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David Rowley @ 2024-01-04 07:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: vojtech.benes@centrum.cz; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type
@ 2024-01-05 17:39 Vojtěch Beneš <vojtech.benes@centrum.cz>
0 siblings, 0 replies; 5+ messages in thread
From: Vojtěch Beneš @ 2024-01-05 17:39 UTC (permalink / raw)
To: dgrowleyml@gmail.com; tgl@sss.pgh.pa.us; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-01-05 17:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-30 10:17 BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type PG Bug reporting form <noreply@postgresql.org>
2023-12-30 16:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-12-31 03:24 ` David Rowley <dgrowleyml@gmail.com>
2024-01-04 07:43 ` David Rowley <dgrowleyml@gmail.com>
2024-01-05 17:39 Re: BUG #18264: Table has type text, but query expects integer.attribute 1 of type record has wrong type Vojtěch Beneš <vojtech.benes@centrum.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox