pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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>
2020-04-02 01:00 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext David G. Johnston <david.g.johnston@gmail.com>
2020-04-02 01:04 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Tom Lane <tgl@sss.pgh.pa.us>
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-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>
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-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:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-02 20:53 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
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-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:04 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-02 20:53 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-02 21:46 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext 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-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:04 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Tom Lane <tgl@sss.pgh.pa.us>
2020-04-02 20:53 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2020-04-02 21:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-05 10:41 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext 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-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:04 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Tom Lane <tgl@sss.pgh.pa.us>
2020-04-02 20:53 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2020-04-02 21:46 ` Re: BUG #16333: position() function not equivalent to strpos() function when comparing citext Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-05 10:41 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
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