Content-Type: multipart/mixed; boundary="------------dd1fcPV7O4xu2s42CRDOtmuB"
Message-ID: <349f86e9-6922-4fcc-9725-b8a6f0667bea@iki.fi>
Date: Thu, 15 May 2025 17:41:42 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18929: After the view is created, executed query against the
 view definition, reported syntax error.
To: 707330139@qq.com, pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>
References: <18929-077d6b7093b176e2@postgresql.org>
Content-Language: en-US
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: <18929-077d6b7093b176e2@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/349f86e9-6922-4fcc-9725-b8a6f0667bea%40iki.fi>
Precedence: bulk

This is a multi-part message in MIME format.
--------------dd1fcPV7O4xu2s42CRDOtmuB
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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)

--------------dd1fcPV7O4xu2s42CRDOtmuB
Content-Type: text/x-patch; charset=UTF-8; name="fix-with-ties-deparse.patch"
Content-Disposition: attachment; filename="fix-with-ties-deparse.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9ydWxldXRpbHMuYyBiL3NyYy9i
YWNrZW5kL3V0aWxzL2FkdC9ydWxldXRpbHMuYwppbmRleCA0NjdiMDgxOThiOC4uNDc0Y2E3
ZTlhZTMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9ydWxldXRpbHMuYwor
KysgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvcnVsZXV0aWxzLmMKQEAgLTU5NTYsOSArNTk1
NiwxOSBAQCBnZXRfc2VsZWN0X3F1ZXJ5X2RlZihRdWVyeSAqcXVlcnksIGRlcGFyc2VfY29u
dGV4dCAqY29udGV4dCkKIAl7CiAJCWlmIChxdWVyeS0+bGltaXRPcHRpb24gPT0gTElNSVRf
T1BUSU9OX1dJVEhfVElFUykKIAkJeworCQkJLyoKKwkJCSAqIFRoZSBsaW1pdENvdW50IGFy
ZyBpcyBhIGNfZXhwciwgc28gaXQgbmVlZHMgcGFyZW5zLiBTaW1wbGUKKwkJCSAqIGxpdGVy
YWxzIGFuZCBmdW5jdGlvbiBleHByZXNzaW9ucyB3b3VsZCBub3QgbmVlZCBwYXJlbnMsIGJ1
dAorCQkJICogdW5mb3J0dW5hdGVseSBpdCdzIGhhcmQgdG8gdGVsbCBpZiBhIENvbnN0IHdp
bGwgYmUgcHJpbnRlZCBhcyBhCisJCQkgKiBzaW1wbGUgbGl0ZXJhbCBsaWtlIDEyMyBvciBh
cyBhIHR5cGVjYXN0IGV4cHJlc3Npb24sIGxpa2UKKwkJCSAqICctMTIzJzo6aW50NC4gVGhl
IGdyYW1tYXIgYWNjZXB0cyB0aGUgZm9ybWVyIHdpdGhvdXQgcXVvdGluZywKKwkJCSAqIGJ1
dCBub3QgdGhlIGxhdHRlci4KKwkJCSAqLwogCQkJYXBwZW5kQ29udGV4dEtleXdvcmQoY29u
dGV4dCwgIiBGRVRDSCBGSVJTVCAiLAogCQkJCQkJCQkgLVBSRVRUWUlOREVOVF9TVEQsIFBS
RVRUWUlOREVOVF9TVEQsIDApOworCQkJYXBwZW5kU3RyaW5nSW5mb0NoYXIoYnVmLCAnKCcp
OwogCQkJZ2V0X3J1bGVfZXhwcihxdWVyeS0+bGltaXRDb3VudCwgY29udGV4dCwgZmFsc2Up
OworCQkJYXBwZW5kU3RyaW5nSW5mb0NoYXIoYnVmLCAnKScpOwogCQkJYXBwZW5kU3RyaW5n
SW5mb1N0cmluZyhidWYsICIgUk9XUyBXSVRIIFRJRVMiKTsKIAkJfQogCQllbHNlCg==

--------------dd1fcPV7O4xu2s42CRDOtmuB--