BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
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.
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:
fix-with-ties-deparse.patchtext/x-patch; charset=UTF-8; name=fix-with-ties-deparse.patchDownload+10-0
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)
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
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:
fix-upgrade-test.patchtext/x-diff; charset=us-asciiDownload+1-0
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)
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