Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

incorrect results for scalar function locate(in_str, search_str, occurrence) #3563

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2014-09-05 15:19:04 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)

Last updated: 2014-10-31 14:14:43 +0100

Comment 20138

Date: 2014-09-05 15:19:04 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

the third parameter of the locate(n_str, search_str, occurrence) function is not working correctly

Reproducible: Always

Steps to Reproduce:

  1. start MonetDB 5 server v11.17.21 "Jan2014-SP3"
  2. start mclient
  3. enter SQL queries:
    select locate('@','joeuser@mydatabase@test', 1) as string_position;
    select locate('@','joeuser@mydatabase@test', 2) as string_position;
    select locate('@','joeuser@mydatabase@test', 3) as string_position;
    select locate('@','joeuser@mydatabase@test', 4) as string_position;
    select locate('@','joeuser@mydatabase@test', 8) as string_position;
    select locate('@','joeuser@mydatabase@test', 9) as string_position;
    select locate('@','joeuser@mydatabase@test', -1) as string_position;
    select locate('@','joeuser@mydatabase@test', -2) as string_position;
    select locate('@','joeuser@mydatabase@test', -3) as string_position;
    select locate('@','joeuser@mydatabase@test', -4) as string_position;

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>select locate('@','joeuser@mydatabase@test', 1) as string_position;
+-----------------+
| string_position |
+=================+
| 1 |
+-----------------+
1 tuple (0.814ms)
sql>select locate('@','joeuser@mydatabase@test', 2) as string_position;
+-----------------+
| string_position |
+=================+
| 1 |
+-----------------+
1 tuple (0.438ms)
sql>select locate('@','joeuser@mydatabase@test', 3) as string_position;
+-----------------+
| string_position |
+=================+
| 1 |
+-----------------+
1 tuple (0.394ms)
sql>select locate('@','joeuser@mydatabase@test', 4) as string_position;
+-----------------+
| string_position |
+=================+
| 1 |
+-----------------+
1 tuple (0.400ms)
sql>select locate('@','joeuser@mydatabase@test', 8) as string_position;
+-----------------+
| string_position |
+=================+
| 1 |
+-----------------+
1 tuple (0.389ms)
sql>select locate('@','joeuser@mydatabase@test', 9) as string_position;
+-----------------+
| string_position |
+=================+
| 1 |
+-----------------+
1 tuple (0.335ms)

sql>select locate('@','joeuser@mydatabase@test', -1) as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.677ms)
sql>select locate('@','joeuser@mydatabase@test', -2) as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.227ms)
sql>select locate('@','joeuser@mydatabase@test', -3) as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.409ms)
sql>select locate('@','joeuser@mydatabase@test', -4) as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.410ms)
sql>

Expected Results:

sql>select locate('@','joeuser@mydatabase@test', 1) as string_position;
+-----------------+
| string_position |
+=================+
| 8|
+-----------------+
1 tuple (0.814ms)
sql>select locate('@','joeuser@mydatabase@test', 2) as string_position;
+-----------------+
| string_position |
+=================+
| 19 |
+-----------------+
1 tuple (0.438ms)
sql>select locate('@','joeuser@mydatabase@test', 3) as string_position;
+-----------------+
| string_position |
+=================+
| null |
+-----------------+
1 tuple (0.394ms)
sql>select locate('@','joeuser@mydatabase@test', 4) as string_position;
+-----------------+
| string_position |
+=================+
| null |
+-----------------+
1 tuple (0.400ms)

sql>select locate('@','joeuser@mydatabase@test', -1) as string_position;
+-----------------+
| string_position |
+=================+
| 19 |
+-----------------+
1 tuple (0.677ms)
sql>select locate('@','joeuser@mydatabase@test', -2) as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.227ms)
sql>select locate('@','joeuser@mydatabase@test', -3) as string_position;
+-----------------+
| string_position |
+=================+
| null |
+-----------------+
1 tuple (0.409ms)
sql>select locate('@','joeuser@mydatabase@test', -4) as string_position;
+-----------------+
| string_position |
+=================+
| null |
+-----------------+
1 tuple (0.410ms)
sql>

locate (a, b) seems to be equivalent to locate (a, b, 0)

sql>select locate('@','joeuser@mydatabase@test', 0) as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.396ms)

sql>select locate('@','joeuser@mydatabase@test') as string_position;
+-----------------+
| string_position |
+=================+
| 8 |
+-----------------+
1 tuple (0.722ms)

Comment 20146

Date: 2014-09-11 16:55:43 +0200
From: MonetDB Mercurial Repository <>

Changeset 65a27b2b9306 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=65a27b2b9306

Changeset description:

Fix for bug #3563: don't confuse needle and haystack.

Comment 20147

Date: 2014-09-11 16:56:07 +0200
From: @sjoerdmullender

I agree that something is wrong with the locate implementation when a third argument is provided.
I do not agree with your expected output.
First of all, there is no clear definition of the locate function that I could find, but all seem to agree that the third argument is an 1-based offset into the "haystack" string (searching for a needle in the haystack). It is never (that I saw) the count of the match you're looking for (what you seem to imply).
What the different implementations do not agree about is what happens with a negative offset. I have chosen to follow Microsoft (since the locate function seems to come from ODBC). A negative or zero offset means to start searching from the start of the string.

To get the second location of the substring, you would have to do something like:
select locate('@','joeuser@mydatabase@test',locate('@','joeuser@mydatabase@test')+1);

Comment 20152

Date: 2014-09-12 17:26:13 +0200
From: Martin van Dinther <<martin.van.dinther>>

Thanks for the fix.
You are right that the third parameter should be the start to search from, not the occurrence.

An extension for this function could be to use a negative start_from_position value to search backwards in the sourcestring starting at the position length(sourcestring) - start_from_position. It makes the function more functional is is more efficient than reversing the str to search in.
This behavior is for instance supported in Oracle's INSTR() and DB2's LOCATE_IN_STRING() for their third start parameter.
See: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm
and http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0054098.html?cp=SSEPGG_9.7.0%2F2-10-3-2-80

Both these functions also support the occurrence as a optional fourth parameter.

Comment 20388

Date: 2014-10-31 14:14:43 +0100
From: @sjoerdmullender

Oct2014 has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant