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

From: 曾满 <zengman(at)halodbtech(dot)com>
To: Erik Wienhold <ewie(at)ewie(dot)name>, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Re: BUG #18632: Whether you need to consider modifying the array's handling of delimiters?
Date: 2024-09-25 09:16:51
Message-ID: tencent_7DAA8DBD2F7167F20912BFB6@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

(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[]);
&nbsp; &nbsp; varchar &nbsp; &nbsp;
---------------
&nbsp;{'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];
&nbsp;varchar
---------
&nbsp;'a-3'
(1 row)

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

Erik Wienhold<ewie(at)ewie(dot)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(at)halodbtech(dot)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(at)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tender Wang 2024-09-25 09:28:42 Re: BUG #18630: Incorrect memory access inside ReindexIsProcessingIndex() on VACUUM
Previous Message 曾满 2024-09-25 08:54:41 回复:Re: BUG #18632: Whether you need to consider modifying the array's handling of delimiters?