pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16333: position() function not equivalent to strpos() function when comparing citext
6+ messages / 4 participants
[nested] [flat]

* BUG #16333: position() function not equivalent to strpos() function when comparing citext
@ 2020-04-01 23:26  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 6+ messages in thread

From: PG Bug reporting form @ 2020-04-01 23:26 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: me@reynolds.tj

The following bug has been logged on the website:

Bug reference:      16333
Logged by:          Tyler Reynolds
Email address:      me@reynolds.tj
PostgreSQL version: 9.6.17
Operating system:   Windows 10
Description:        

To reproduce:

CREATE DATABASE test;
CREATE EXTENSION citext;

SELECT position('foo'::citext IN 'Foobar'::citext) =
strpos('Foobar'::citext, 'foo'::citext) as "positionEqualsStrpos";

Expected: "positionEqualsStrpos" returns TRUE.
Actual: "positionEqualsStrpos" returns FALSE.

Citext does not create an overload for position() supporting citext
parameters, therefore position(a in b) always runs case-sensitive.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext
@ 2020-04-02 01:00  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 6+ messages in thread

From: David G. Johnston @ 2020-04-02 01:00 UTC (permalink / raw)
  To: me@reynolds.tj <me@reynolds.tj>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Wednesday, April 1, 2020, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      16333
> Logged by:          Tyler Reynolds
> Email address:      me@reynolds.tj
> PostgreSQL version: 9.6.17
> Operating system:   Windows 10
> Description:
>
> To reproduce:
>
> CREATE DATABASE test;
> CREATE EXTENSION citext;
>
> SELECT position('foo'::citext IN 'Foobar'::citext) =
> strpos('Foobar'::citext, 'foo'::citext) as "positionEqualsStrpos";
>
> Expected: "positionEqualsStrpos" returns TRUE.
> Actual: "positionEqualsStrpos" returns FALSE.
>
> Citext does not create an overload for position() supporting citext
> parameters, therefore position(a in b) always runs case-sensitive.
>

Nor does it claim to so this isn’t a bug.  The fact that citext doesn’t
work with the SQL functions seems intentional.

David J.

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext
@ 2020-04-02 01:04  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2020-04-02 01:04 UTC (permalink / raw)
  To: me@reynolds.tj; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> SELECT position('foo'::citext IN 'Foobar'::citext) =
> strpos('Foobar'::citext, 'foo'::citext) as "positionEqualsStrpos";

> Citext does not create an overload for position() supporting citext
> parameters, therefore position(a in b) always runs case-sensitive.

Well, the citext documentation specifies which functions have
case-insensitive mappings.  strpos() is listed, position() is not,
so I'd say it's acting precisely as documented.

We could consider adding position() of course, but there's a
backwards-compatibility issue --- at this point, people may have
queries that depend on the current behavior.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext
@ 2020-04-02 20:53  Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Peter Eisentraut @ 2020-04-02 20:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; me@reynolds.tj; +Cc: pgsql-bugs@lists.postgresql.org

On 2020-04-02 03:04, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> SELECT position('foo'::citext IN 'Foobar'::citext) =
>> strpos('Foobar'::citext, 'foo'::citext) as "positionEqualsStrpos";
> 
>> Citext does not create an overload for position() supporting citext
>> parameters, therefore position(a in b) always runs case-sensitive.
> 
> Well, the citext documentation specifies which functions have
> case-insensitive mappings.  strpos() is listed, position() is not,
> so I'd say it's acting precisely as documented.

Arguably, there is a misdesign here, however.  Any function that does 
some kind of text-in-text search where citext could plausbily offer 
case-insensitive behavior will automatically fall back to the 
case-sensitive version if citext doesn't offer its own variant.  The fix 
would technically need to be that citext offers its own variant of every 
potential such function, which is clearly not possible, or that casts 
between text and citext are more restricted, which would make citext 
nearly unusable.

Doesn't seem fixable.  Collations are probably a better way of dealing 
with this.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext
@ 2020-04-02 21:46  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2020-04-02 21:46 UTC (permalink / raw)
  To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: me@reynolds.tj; pgsql-bugs@lists.postgresql.org

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Arguably, there is a misdesign here, however.  Any function that does 
> some kind of text-in-text search where citext could plausbily offer 
> case-insensitive behavior will automatically fall back to the 
> case-sensitive version if citext doesn't offer its own variant.  The fix 
> would technically need to be that citext offers its own variant of every 
> potential such function, which is clearly not possible, or that casts 
> between text and citext are more restricted, which would make citext 
> nearly unusable.

Indeed.  There are some hundreds of built-in functions that take one
or more text arguments; how many of them would need citext variants?

> Doesn't seem fixable.  Collations are probably a better way of dealing 
> with this.

Yeah, now that we can do non-deterministic collations, it seems like
citext is on the road to obsolescence.  Do we have a documentation
example of how to build a simple CI collation?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext
@ 2020-04-05 10:41  Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Peter Eisentraut @ 2020-04-05 10:41 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: me@reynolds.tj; pgsql-bugs@lists.postgresql.org

On 2020-04-02 23:46, Tom Lane wrote:
>> Doesn't seem fixable.  Collations are probably a better way of dealing
>> with this.
> 
> Yeah, now that we can do non-deterministic collations, it seems like
> citext is on the road to obsolescence.  Do we have a documentation
> example of how to build a simple CI collation?

Yes, in PG12 there is a "tip" box in the citext chapter with a link to 
the collation documentation which contains a command to create a 
case-insensitive collation.  So this should be pretty easy to find even 
for casual users.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2020-04-05 10:41 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-01 23:26 BUG #16333: position() function not equivalent to strpos() function when comparing citext PG Bug reporting form <noreply@postgresql.org>
2020-04-02 01:00 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-02 01:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-02 20:53   ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-02 21:46     ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-05 10:41       ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>

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