BUG #18632: Whether you need to consider modifying the array's handling of delimiters？

On 2024-09-25 09:57 +0200, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 18632
Logged by: Man Zeng
Email address: zengman@halodbtech.com
PostgreSQL version: 14.10
Operating system: centos-8
Description:

Hi, I found a problem with array separator handling.
The current handling of delimiters is not quite as expected (not very
flexible).
The test SQL and results are shown below.

[postgres@halo-centos-8-release ~]$ psql
psql (14.10)
Type "help" for help.

postgres=# CREATE OR REPLACE FUNCTION arrayfunc()
postgres-# RETURNS _varchar
postgres-# AS $$
postgres$# SELECT '{''a,3'',''b'',''c''}'::_varchar;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=# -- array cstring
postgres=# SELECT arrayfunc();
arrayfunc
-----------------
{'a,3','b','c'}
(1 row)

postgres=# -- length is 4
postgres=# SELECT array_length(arrayfunc(), 1);
array_length
--------------
4
(1 row)

postgres=# -- first element
postgres=# SELECT (arrayfunc())[1];
arrayfunc
-----------
'a
(1 row)

postgres=# -- second element
postgres=# SELECT (arrayfunc())[2];
arrayfunc
-----------
3'
(1 row)

You need to double-quote elements that contain the separator:

SELECT '{"''a,3''",''b'',''c''}'::varchar[];

That's also documented in the first paragraph of
https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT

So, not a bug.

postgres=# -- other
postgres=# SELECT (arrayfunc())[3];
arrayfunc
-----------
'b'
(1 row)

postgres=# SELECT (arrayfunc())[4];
arrayfunc
-----------
'c'
(1 row)

postgres=# -- The following SQL tests are as expected
postgres=# CREATE OR REPLACE FUNCTION arrayfunc2()
postgres-# RETURNS _varchar
postgres-# AS $$
postgres$# SELECT '{''a-3'',''b'',''c''}'::_varchar;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=# -- array cstring
postgres=# SELECT arrayfunc2();
arrayfunc2
-----------------
{'a-3','b','c'}
(1 row)

postgres=# -- length is 3
postgres=# SELECT array_length(arrayfunc2(), 1);
array_length
--------------
3
(1 row)

postgres=# -- first element
postgres=# SELECT (arrayfunc2())[1];
arrayfunc2
------------
'a-3'
(1 row)

So should we consider modifying "array_in" to enhance the handling of
separators to be more consistent with people's expectations?

--
Erik

（Sorry, there was a garbled situation in the last email）
Thank you. I know that the way you said can be handled normally.

postgres=# SELECT ('{''a-3'',''a,3''}'::varchar[]);
    varchar    
---------------
 {'a-3','a,3'}
(1 row)

I wonder if we need to modify array_in so that ''a,3'' and ''a-3'' behave the same and have a uniform style.
Would it be better?

postgres=# SELECT ('{''a-3'',''a,3''}'::varchar[])[1];
 varchar
---------
 'a-3'
(1 row)

postgres=# SELECT ('{''a-3'',''a,3''}'::varchar[])[2];
 varchar
---------
 'a
(1 row)

Erik Wienhold<ewie@ewie.name&gt;&nbsp;在 2024年9月25日 周三 16:32 写道：

On 2024-09-25 09:57 +0200, PG Bug reporting form wrote:
&gt; The following bug has been logged on the website:
&gt;
&gt; Bug reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18632
&gt; Logged by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Man Zeng
&gt; Email address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; zengman@halodbtech.com
&gt; PostgreSQL version: 14.10
&gt; Operating system:&nbsp;&nbsp; centos-8
&gt; Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&gt;
&gt; Hi, I found a problem with array separator handling.
&gt; The current handling of delimiters is not quite as expected (not very
&gt; flexible).
&gt; The test SQL and results are shown below.
&gt;
&gt; [postgres@halo-centos-8-release ~]$ psql
&gt; psql (14.10)
&gt; Type "help" for help.
&gt;
&gt; postgres=# CREATE OR REPLACE FUNCTION arrayfunc()
&gt; postgres-# RETURNS _varchar
&gt; postgres-# AS $$
&gt; postgres$#&nbsp;&nbsp; SELECT '{''a,3'',''b'',''c''}'::_varchar;
&gt; postgres$# $$ LANGUAGE SQL;
&gt; CREATE FUNCTION
&gt; postgres=# -- array cstring
&gt; postgres=# SELECT arrayfunc();
&gt;&nbsp;&nbsp;&nbsp;&nbsp; arrayfunc&nbsp;&nbsp;&nbsp;
&gt; -----------------
&gt;&nbsp; {'a,3','b','c'}
&gt; (1 row)
&gt;
&gt; postgres=# -- length is 4
&gt; postgres=# SELECT array_length(arrayfunc(), 1);
&gt;&nbsp; array_length
&gt; --------------
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
&gt; (1 row)
&gt;
&gt; postgres=# -- first element
&gt; postgres=# SELECT (arrayfunc())[1];
&gt;&nbsp; arrayfunc
&gt; -----------
&gt;&nbsp; 'a
&gt; (1 row)
&gt;
&gt; postgres=# -- second element
&gt; postgres=# SELECT (arrayfunc())[2];
&gt;&nbsp; arrayfunc
&gt; -----------
&gt;&nbsp; 3'
&gt; (1 row)

You need to double-quote elements that contain the separator:

&nbsp;&nbsp;&nbsp; SELECT '{"''a,3''",''b'',''c''}'::varchar[];

That's also documented in the first paragraph of
https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT

So, not a bug.

&gt; postgres=# -- other
&gt; postgres=# SELECT (arrayfunc())[3];
&gt;&nbsp; arrayfunc
&gt; -----------
&gt;&nbsp; 'b'
&gt; (1 row)
&gt;
&gt; postgres=# SELECT (arrayfunc())[4];
&gt;&nbsp; arrayfunc
&gt; -----------
&gt;&nbsp; 'c'
&gt; (1 row)
&gt;
&gt; postgres=# -- The following SQL tests are as expected
&gt; postgres=# CREATE OR REPLACE FUNCTION arrayfunc2()
&gt; postgres-# RETURNS _varchar
&gt; postgres-# AS $$
&gt; postgres$#&nbsp;&nbsp; SELECT '{''a-3'',''b'',''c''}'::_varchar;
&gt; postgres$# $$ LANGUAGE SQL;
&gt; CREATE FUNCTION
&gt; postgres=# -- array cstring
&gt; postgres=# SELECT arrayfunc2();
&gt;&nbsp;&nbsp;&nbsp; arrayfunc2&nbsp;&nbsp;&nbsp;
&gt; -----------------
&gt;&nbsp; {'a-3','b','c'}
&gt; (1 row)
&gt;
&gt; postgres=# -- length is 3
&gt; postgres=# SELECT array_length(arrayfunc2(), 1);
&gt;&nbsp; array_length
&gt; --------------
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3
&gt; (1 row)
&gt;
&gt; postgres=# -- first element
&gt; postgres=# SELECT (arrayfunc2())[1];
&gt;&nbsp; arrayfunc2
&gt; ------------
&gt;&nbsp; 'a-3'
&gt; (1 row)
&gt;
&gt; So should we consider modifying "array_in" to enhance the handling of
&gt; separators to be more consistent with people's expectations?
&gt;

--
Erik

曾满:

I wonder if we need to modify array_in so that ''a,3'' and ''a-3''
behave the same and have a uniform style.

You are still using single quotes, but two of them. You need to use
**double** quotes, not two single quotes.

''a,3'' is different from "a,3".

With true double quotes:

postgres=# SELECT unnest('{"a-3","a,3"}'::varchar[]);
unnest
--------
a-3
a,3
(2 rows)

Best,

Wolfgang

On Wednesday, September 25, 2024, PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 18632
Logged by: Man Zeng
Email address: zengman@halodbtech.com
PostgreSQL version: 14.10
Operating system: centos-8
Description:

Hi, I found a problem with array separator handling.
The current handling of delimiters is not quite as expected (not very
flexible).

So should we consider modifying "array_in" to enhance the handling of
separators to be more consistent with people's expectations?

A design being inflexible or not meeting people’s expectations is not a
bug. The system is behaving as documented. And we are not going to be
redefining how valid code is parsed here.

David J.

Ok, you're right, there really isn't any problem from a separator point of view

----------Reply to Message----------
On Wed, Sep 25, 2024 20:33 PM David G. Johnston<david.g.johnston@gmail.com&gt; wrote:

On Wednesday, September 25, 2024, PG Bug reporting form <noreply@postgresql.org&gt; wrote:
The following bug has been logged on the website:

Bug reference:&nbsp; &nbsp; &nbsp; 18632
Logged by:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Man Zeng
Email address:&nbsp; &nbsp; &nbsp; zengman@halodbtech.com
PostgreSQL version: 14.10
Operating system:&nbsp; &nbsp;centos-8
Description:&nbsp; &nbsp; &nbsp; &nbsp;

Hi, I found a problem with array separator handling.
The current handling of delimiters is not quite as expected (not very
flexible).

So should we consider modifying "array_in" to enhance the handling of
separators to be more consistent with people's expectations?

A design being inflexible or not meeting people’s expectations is not a bug.&nbsp; The system is behaving as documented.&nbsp; And we are not going to be redefining how valid code is parsed here.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wednesday, September 25, 2024, PG Bug reporting form <
noreply@postgresql.org> wrote:

So should we consider modifying "array_in" to enhance the handling of
separators to be more consistent with people's expectations?

A design being inflexible or not meeting people’s expectations is not a
bug. The system is behaving as documented. And we are not going to be
redefining how valid code is parsed here.

Yeah, I seriously doubt that we could change array_in's behavior
for single quotes (to wit, that they are not special in any way)
without breaking existing applications.

regards, tom lane

Okay, thank you for your guidance, and thank you again.

----------Reply to Message----------
On Wed, Sep 25, 2024 22:51 PM Tom Lane<tgl@sss.pgh.pa.us&gt; wrote:

"David G. Johnston" <david.g.johnston@gmail.com&gt; writes:
&gt; On Wednesday, September 25, 2024, PG Bug reporting form <
&gt; noreply@postgresql.org&gt; wrote:
&gt;&gt; So should we consider modifying "array_in" to enhance the handling of
&gt;&gt; separators to be more consistent with people's expectations?

&gt; A design being inflexible or not meeting people’s expectations is not a
&gt; bug.&nbsp; The system is behaving as documented.&nbsp; And we are not going to be
&gt; redefining how valid code is parsed here.

Yeah, I seriously doubt that we could change array_in's behavior
for single quotes (to wit, that they are not special in any way)
without breaking existing applications.

regards, tom lane