Message-ID: <a78242587cd008096a61de9e858202d47413dda0.camel@cybertec.at>
Subject: Re: [PATCH] Add support function for containment operators
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: jian he <jian.universality@gmail.com>
Cc: Kim Johan Andersson <kimjand@kimmet.dk>, 
	pgsql-hackers@lists.postgresql.org
Date: Thu, 19 Oct 2023 18:01:40 +0200
In-Reply-To: <CACJufxGmodA7Uc+Y4wE-A95BHLamJ3YxDtRYbXjNF=b8xExz+w@mail.gmail.com>
References: <94f64d1f-b8c0-b0c5-98bc-0793a34e0851@kimmet.dk>
	 <4e6efd382b3195eb7ea438b7219cc94d9399c0bd.camel@cybertec.at>
	 <147ac793f2c3a9a57764fba84988a4b5e843443b.camel@cybertec.at>
	 <4cdc292e-3234-a03d-107d-31cf8b721cd4@kimmet.dk>
	 <36ea800b7b537c89d92bcd109b3858c72c16a14c.camel@cybertec.at>
	 <CACJufxGmodA7Uc+Y4wE-A95BHLamJ3YxDtRYbXjNF=b8xExz+w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/a78242587cd008096a61de9e858202d47413dda0.camel%40cybertec.at>
Precedence: bulk

On Fri, 2023-10-13 at 14:26 +0800, jian he wrote:
> Collation problem seems solved.

I didn't review your patch in detail, there is still a problem
with my example:

  CREATE TYPE textrange AS RANGE (
     SUBTYPE =3D text,
     SUBTYPE_OPCLASS =3D text_pattern_ops
  );

  CREATE TABLE tx (t text COLLATE "cs-CZ-x-icu");

  INSERT INTO tx VALUES ('a'), ('c'), ('d'), ('ch');

  SELECT * FROM tx WHERE t <@ textrange('a', 'd');

   t =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90
   a
   c
   ch
  (3 rows)

That was correct.

  EXPLAIN SELECT * FROM tx WHERE t <@ textrange('a', 'd');

                       QUERY PLAN                    =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90
   Seq Scan on tx  (cost=3D0.00..30.40 rows=3D7 width=3D32)
     Filter: ((t >=3D 'a'::text) AND (t < 'd'::text))
  (2 rows)

But that was weird.  The operators seem wrong.  Look at that
query:

  SELECT * FROM tx WHERE t >=3D 'a' AND t < 'd';

   t=20
  =E2=95=90=E2=95=90=E2=95=90
   a
   c
  (2 rows)

But the execution plan is identical...

I am not sure what is the problem here, but in my opinion the
operators shown in the execution plan should be like this:

  SELECT * FROM tx WHERE t ~>=3D~ 'a' AND t ~<~ 'd';

   t =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90
   a
   c
   ch
  (3 rows)

Yours,
Laurenz Albe