pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18929: After the view is created, executed query against the view definition, reported syntax error.
7+ messages / 4 participants
[nested] [flat]
* BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-15 04:04 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2025-05-15 04:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 707330139@qq.com
The following bug has been logged on the website:
Bug reference: 18929
Logged by: yonghao lee
Email address: 707330139@qq.com
PostgreSQL version: 17.5
Operating system: linux
Description:
Hello PostgreSQL team,
I found an error in the view definition SQL given when looking at the view
description using \d+. Here are the detailed steps for this problem.
executed query:
postgres=# CREATE TABLE onek (thousand int4);
CREATE TABLE
postgres=# CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek
WHERE thousand < 995 ORDER BY thousand FETCH FIRST (NULL::bigint +
1::bigint) ROWS WITH TIES;
CREATE VIEW
postgres=# \d+ limit_thousand_v_3
View "public.limit_thousand_v_3"
Column | Type | Collation | Nullable | Default | Storage | Description
----------+---------+-----------+----------+---------+---------+-------------
thousand | integer | | | | plain |
View definition:
SELECT thousand
FROM onek
WHERE thousand < 995
ORDER BY thousand
FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
postgres=# SELECT thousand
postgres-# FROM onek
postgres-# WHERE thousand < 995
postgres-# ORDER BY thousand
postgres-# FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
ERROR: syntax error at or near "::"
LINE 5: FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
^
I think that executing the view definition query will be successful.
I'm happy to provide more info if I can/ if needed.
Thanks,
yonghao lee,
Highgo software.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-15 14:41 Heikki Linnakangas <hlinnaka@iki.fi>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2025-05-15 14:41 UTC (permalink / raw)
To: 707330139@qq.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
On 15/05/2025 07:04, PG Bug reporting form wrote:
> postgres=# \d+ limit_thousand_v_3
> View "public.limit_thousand_v_3"
> Column | Type | Collation | Nullable | Default | Storage | Description
> ----------+---------+-----------+----------+---------+---------+-------------
> thousand | integer | | | | plain |
> View definition:
> SELECT thousand
> FROM onek
> WHERE thousand < 995
> ORDER BY thousand
> FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
> postgres=# SELECT thousand
> postgres-# FROM onek
> postgres-# WHERE thousand < 995
> postgres-# ORDER BY thousand
> postgres-# FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
> ERROR: syntax error at or near "::"
> LINE 5: FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
> ^
Agreed that's a bug. Attached is a simple patch to add parens to all
deparsed FETCH FIRST n ROWS WITH TIES expressions.
It would be nice to skip the parens for simple literals, but
unfortunately it's non-trivial to distinguish Consts that are printed as
simple literals from more complicated cases that are printed as
typecasts, like '123'::bigint. XMLEXISTS is another expression type that
takes a 'c_expr' in the the grammar, and it also just always adds the
parens.
I'll commit this fix, unless someone comes up with something more
sophisticated.
--
Heikki Linnakangas
Neon (https://neon.tech)
Attachments:
[text/x-patch] fix-with-ties-deparse.patch (1021B, ../../349f86e9-6922-4fcc-9725-b8a6f0667bea@iki.fi/2-fix-with-ties-deparse.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 467b08198b8..474ca7e9ae3 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5956,9 +5956,19 @@ get_select_query_def(Query *query, deparse_context *context)
{
if (query->limitOption == LIMIT_OPTION_WITH_TIES)
{
+ /*
+ * The limitCount arg is a c_expr, so it needs parens. Simple
+ * literals and function expressions would not need parens, but
+ * unfortunately it's hard to tell if a Const will be printed as a
+ * simple literal like 123 or as a typecast expression, like
+ * '-123'::int4. The grammar accepts the former without quoting,
+ * but not the latter.
+ */
appendContextKeyword(context, " FETCH FIRST ",
-PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
+ appendStringInfoChar(buf, '(');
get_rule_expr(query->limitCount, context, false);
+ appendStringInfoChar(buf, ')');
appendStringInfoString(buf, " ROWS WITH TIES");
}
else
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-19 15:52 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2025-05-19 15:52 UTC (permalink / raw)
To: 707330139@qq.com; pgsql-bugs@lists.postgresql.org
On 15/05/2025 17:41, Heikki Linnakangas wrote:
> On 15/05/2025 07:04, PG Bug reporting form wrote:
>> postgres=# \d+ limit_thousand_v_3
>> View "public.limit_thousand_v_3"
>> Column | Type | Collation | Nullable | Default | Storage |
>> Description
>> ----------+---------+-----------+----------+---------+---------
>> +-------------
>> thousand | integer | | | | plain |
>> View definition:
>> SELECT thousand
>> FROM onek
>> WHERE thousand < 995
>> ORDER BY thousand
>> FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>> postgres=# SELECT thousand
>> postgres-# FROM onek
>> postgres-# WHERE thousand < 995
>> postgres-# ORDER BY thousand
>> postgres-# FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>> ERROR: syntax error at or near "::"
>> LINE 5: FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>> ^
>
> Agreed that's a bug. Attached is a simple patch to add parens to all
> deparsed FETCH FIRST n ROWS WITH TIES expressions.
>
> It would be nice to skip the parens for simple literals, but
> unfortunately it's non-trivial to distinguish Consts that are printed as
> simple literals from more complicated cases that are printed as
> typecasts, like '123'::bigint. XMLEXISTS is another expression type that
> takes a 'c_expr' in the the grammar, and it also just always adds the
> parens.
>
> I'll commit this fix, unless someone comes up with something more
> sophisticated.
Committed, thanks for the report.
--
Heikki Linnakangas
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-20 04:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-05-20 04:31 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: 707330139@qq.com; pgsql-bugs@lists.postgresql.org
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> Committed, thanks for the report.
In the no-good-deed-goes-unpunished department: it appears from
buildfarm reports that we need to do something about output
mismatches in cross-version-upgrade tests due to this commit.
I didn't yet look more closely.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-20 05:02 Michael Paquier <michael@paquier.xyz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Paquier @ 2025-05-20 05:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; 707330139@qq.com; pgsql-bugs@lists.postgresql.org
On Tue, May 20, 2025 at 12:31:26AM -0400, Tom Lane wrote:
> In the no-good-deed-goes-unpunished department: it appears from
> buildfarm reports that we need to do something about output
> mismatches in cross-version-upgrade tests due to this commit.
> I didn't yet look more closely.
Yes, the buildfarm is unhappy.
It seems to me that this needs an update of @_unused_view_qualifiers
in AdjustUpgrade.pm to update public.limit_thousand_v_5's qual, like
in the attached. This is needed in v16 and newer branches.
--
Michael
Attachments:
[text/x-diff] fix-upgrade-test.patch (645B, ../../aCwM3RSlrD2mk1mT@paquier.xyz/2-fix-upgrade-test.patch)
download | inline diff:
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index 609275e2c26d..1725fe2f9487 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -538,6 +538,7 @@ my @_unused_view_qualifiers = (
{ obj => 'VIEW public.limit_thousand_v_2', qual => 'onek' },
{ obj => 'VIEW public.limit_thousand_v_3', qual => 'onek' },
{ obj => 'VIEW public.limit_thousand_v_4', qual => 'onek' },
+ { obj => 'VIEW public.limit_thousand_v_5', qual => 'onek' },
# Since 14
{ obj => 'MATERIALIZED VIEW public.compressmv', qual => 'cmdata1' });
[application/pgp-signature] signature.asc (832B, ../../aCwM3RSlrD2mk1mT@paquier.xyz/3-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-20 07:45 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2025-05-20 07:45 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 707330139@qq.com; pgsql-bugs@lists.postgresql.org
On 20/05/2025 08:02, Michael Paquier wrote:
> On Tue, May 20, 2025 at 12:31:26AM -0400, Tom Lane wrote:
>> In the no-good-deed-goes-unpunished department: it appears from
>> buildfarm reports that we need to do something about output
>> mismatches in cross-version-upgrade tests due to this commit.
>> I didn't yet look more closely.
>
> Yes, the buildfarm is unhappy.
>
> It seems to me that this needs an update of @_unused_view_qualifiers
> in AdjustUpgrade.pm to update public.limit_thousand_v_5's qual, like
> in the attached. This is needed in v16 and newer branches.
Committed the same fix. Sorry for not crediting you in the commit
message, I reached the same conclusion and committed before I saw this
discussion. Thanks for looking!
--
Heikki Linnakangas
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
@ 2025-05-20 08:44 Michael Paquier <michael@paquier.xyz>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Paquier @ 2025-05-20 08:44 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 707330139@qq.com; pgsql-bugs@lists.postgresql.org
On Tue, May 20, 2025 at 10:45:07AM +0300, Heikki Linnakangas wrote:
> Committed the same fix. Sorry for not crediting you in the commit message,
> I reached the same conclusion and committed before I saw this discussion.
> Thanks for looking!
No problem. Thanks for committing the fix.
> Neon (https://neon.tech)
Congrats on this part.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../aCxBAyHmQovWOF_-@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-05-20 08:44 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-15 04:04 BUG #18929: After the view is created, executed query against the view definition, reported syntax error. PG Bug reporting form <noreply@postgresql.org>
2025-05-15 14:41 ` Heikki Linnakangas <hlinnaka@iki.fi>
2025-05-19 15:52 ` Heikki Linnakangas <hlinnaka@iki.fi>
2025-05-20 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-05-20 05:02 ` Michael Paquier <michael@paquier.xyz>
2025-05-20 07:45 ` Heikki Linnakangas <hlinnaka@iki.fi>
2025-05-20 08:44 ` Michael Paquier <michael@paquier.xyz>
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