public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: jian he <[email protected]>
Cc: Kim Johan Andersson <[email protected]>
Cc: [email protected]
Subject: Re: [PATCH] Add support function for containment operators
Date: Thu, 19 Oct 2023 18:01:40 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxGmodA7Uc+Y4wE-A95BHLamJ3YxDtRYbXjNF=b8xExz+w@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CACJufxGmodA7Uc+Y4wE-A95BHLamJ3YxDtRYbXjNF=b8xExz+w@mail.gmail.com>

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 = text,
     SUBTYPE_OPCLASS = 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  
  ════
   a
   c
   ch
  (3 rows)

That was correct.

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

                       QUERY PLAN                     
  ════════════════════════════════════════════════════
   Seq Scan on tx  (cost=0.00..30.40 rows=7 width=32)
     Filter: ((t >= '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 >= 'a' AND t < 'd';

   t 
  ═══
   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 ~>=~ 'a' AND t ~<~ 'd';

   t  
  ════
   a
   c
   ch
  (3 rows)

Yours,
Laurenz Albe

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [PATCH] Add support function for containment operators
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox