Thread: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
From
PG Bug reporting form
Date:
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.
Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
From
Heikki Linnakangas
Date:
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)
Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
From
Michael Paquier
Date:
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
Attachment
Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
From
Heikki Linnakangas
Date:
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)
Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
From
Michael Paquier
Date:
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