From: Heikki Linnakangas <hlinnaka@iki.fi>
To: 707330139@qq.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
Date: Thu, 15 May 2025 17:41:42 +0300
Message-ID: <349f86e9-6922-4fcc-9725-b8a6f0667bea@iki.fi> (raw)
In-Reply-To: <18929-077d6b7093b176e2@postgresql.org>
References: <18929-077d6b7093b176e2@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.cindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: hlinnaka@iki.fi, noreply@postgresql.org
Subject: Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.
In-Reply-To: <349f86e9-6922-4fcc-9725-b8a6f0667bea@iki.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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