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

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)

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?

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

曾满:
> 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

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?

"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