BUG #18239: select position ('' in 'A') returns 1

On Sunday, December 10, 2023, PG Bug reporting form <noreply@postgresql.org>
wrote:

The following bug has been logged on the website:

Bug reference: 18239
Logged by: Luc Van Linden
Email address: lucvanlind.en@gmail.com
PostgreSQL version: 15.3
Operating system: windows 10 64 bit
Description:

We simplified the bug, or maybe undocumented behavior to its simplest
testcase.

select position ('' in 'A') returns 1

Thus, case a field has an empty string while, checking positions in another
string, it seems to always fit the position 1.

Not sure if this is intended or not, at least we experience this as a bug.

Just as an empty set is a subset of all sets the empty string is a
substring within all strings.

I do not believe we document or refer to this external rule anywhere though.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, December 10, 2023, PG Bug reporting form <noreply@postgresql.org>
wrote:

select position ('' in 'A') returns 1
Thus, case a field has an empty string while, checking positions in another
string, it seems to always fit the position 1.

Just as an empty set is a subset of all sets the empty string is a
substring within all strings.

Yes. This behavior is explicitly required by the SQL standard:

2) If <string position expression> is specified, then

Case:

a) If the first <string value expression> has a length of 0
(zero), then the result is 1 (one).

b) [ otherwise, ... ]

I do not believe we document or refer to this external rule anywhere though.

No, our documentation for position() is just one sentence. With the
new function-table layout it'd be possible to add a second example
to exhibit this behavior, but I'm not sure it's worth the trouble.
Personally I don't find this surprising.

regards, tom lane