Re: position('' in '') returns 1 instead of 0 - Mailing list pgsql-bugs

> > While running an ODBC test suite against PostgreSQL 7.1, the test
> > software reported that the ODBC command locate("", "") failed.
> > It expected the result to be zero and it got a 1.
> Evidently your ODBC test suite hasn't read the spec.  See 4.2.2.2 in
> either SQL92 or SQL99:
>          <position expression> determines the first position, if any, at
>          which one string, S1, occurs within another, S2. If S1 is of length
>          zero, then it occurs at position 1 for any value of S2. If S1 does
>          not occur in S2, then zero is returned.
> Whether this is useful or not is perhaps debatable, but it's indisputably
> what the standard says to do.

So now the question is what the ODBC standard says locate() should
return. Perhaps it is not a one-to-one mapping to position(), and we
will need an ODBC-specific helper function in the driver or backend to
implement it.

Terry, can you research the expected behavior of locate() to confirm
that your test suite is accurate wrt the expected result?

                     - Thomas