Thread

1. position('' in '') returns 1 instead of 0

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-04-18T20:25:33Z

   Terry Carlin (terry@greatbridge.com) reports a bug with a severity of 3
   The lower the number the more severe it is.
   
   Short Description
   position('' in '') returns 1 instead of 0
   
   Long Description
   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.  When I looked at what was being sent over to postgresql, it boiled down to a position('' in '')  Entering the command select position(''' in '') into psql gives a 1 also. 
   if you also do select position('' in 'abc') it also returns 1.
   This returns a 1.  Since the length('') returns 0, it seems to me that position('' in '') should return either a zero or NULL as there is no position 1 in the string ''.
   
   Sample Code
   select position('' in '');
   
   select length('');
   
   select position('' in 'abc');
   
   No file was uploaded with this report
   
   
   

2. Re: position('' in '') returns 1 instead of 0

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-04-18T21:04:58Z

   > > 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
   
   

3. Re: position('' in '') returns 1 instead of 0

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-18T21:26:29Z

   Terry Carlin (terry@greatbridge.com) writes:
   > 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.
   
   			regards, tom lane
   
   

4. Re: position('' in '') returns 1 instead of 0

   Peter Eisentraut <peter_e@gmx.net> — 2001-04-18T21:56:57Z

   > Terry Carlin (terry@greatbridge.com) reports a bug with a severity of 3
   > The lower the number the more severe it is.
   >
   > Short Description
   > position('' in '') returns 1 instead of 0
   
   This is in compliance with the SQL standard.  (SQL 1999, clause 6.17,
   general rule 2 a), if you care.)
   
   > 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.
   
   Maybe the locate() function should be implemented differently than
   position().
   
   -- 
   Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter
   
   
   

5. Re: position('' in '') returns 1 instead of 0

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-18T22:11:54Z

   Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
   > So now the question is what the ODBC standard says locate() should
   > return.
   
   Wups.  I neglected to notice the disconnect between the message subject
   and body... but you are right, "locate" might not be the same as
   "position" :-(
   
   			regards, tom lane
   
   

6. Re: Re: position('' in '') returns 1 instead of 0

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-04-19T04:31:16Z

   Tom Lane wrote:
   > 
   > Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
   > > So now the question is what the ODBC standard says locate() should
   > > return.
   > 
   > Wups.  I neglected to notice the disconnect between the message subject
   > and body... but you are right, "locate" might not be the same as
   > "position" :-(
   > 
   
   Hmm where is locate() changed to position() ? 
   ISTM neither psqlodbc driver nor pg backend changes the
   function call locate() to position(.. in ..).
   
   regards,
   Hiroshi Inoue
   
   

7. Re: position('' in '') returns 1 instead of 0

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-04-19T13:20:43Z

   > Hmm where is locate() changed to position() ?
   > ISTM neither psqlodbc driver nor pg backend changes the
   > function call locate() to position(.. in ..).
   
   There is a mapping table in the driver of ODBC function calls to
   PostgreSQL function calls. In this case, locate() is transformed into
   strpos(), which is probably the underlying function implementing
   position() also.
   
   Terry, any word on locate() vs position() specs?
   
                            - Thomas
   
   

8. Re: Re: position('' in '') returns 1 instead of 0

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-04-19T23:32:41Z

   Terry Carlin wrote:
   > 
   > I was wrong about the locate() function in the odbc driver.  Thomas
   > Lockhart pointed out that it mapped locate in the driver to strpos in
   > postgres.  This tweaked a memory.  What happens with the locate() odbc
   > function in the odbc test suite is that it calls locate with the
   > following arguments:  locate(string, string, position).  When this maps
   > over to the strpos function, it fails as the strpos function only expects
   > arguments like: strpos(string, string).
   > 
   > benchmark=# \df strpos
   >         List of functions
   >  Result  | Function | Arguments
   > ---------+----------+------------
   >  integer | strpos   | text, text
   > (1 row)
   > 
   > I had added a piece of functionality to the odbc.sql script that
   > overloaded the strpos function like this:
   > 
   > CREATE FUNCTION strpos(text,text,integer)
   >     RETURNS integer
   >     AS 'SELECT position($1 in substring($2 from $3))'
   >     LANGUAGE 'SQL';
   > 
   
   Oh that explains it.
   However the spec of locate() isn't clear to me.
   
   LOCATE(string_exp1, string_exp2[, start]) (ODBC 1.0)
   
   Returns the starting position of the first occurrence of string_exp1
   within string_exp2. The search for the first occurrence of string_exp1
   begins with the first character position in string_exp2 unless the
   optional argument, start, is specified. If start is specified, the
   search begins with the character position indicated by the value of
   start. The first character position in string_exp2 is indicated by the
   value 1. If string_exp1 is not found within string_exp2, the value 0 is
   returned. 
   
   regards,
   Hiroshi Inoue