Bugs item #551102, was opened at 2002-05-01 12:05
You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=551102&group_id=22866

Category: JBossCMP
Group: v3.0 Rabbit Hole
Status: Closed
Resolution: Fixed
Priority: 5
Submitted By: Frederik Sauer (fredsa)
Assigned to: Dain Sundstrom (dsundstrom)
Summary: LOCATE function in Oracle / PostgreSQL

Initial Comment:
Please also refer to bug#541507 where the postgres
mapping of LOCATE was changed to:
(coalesce(nullif(position(?2 in substring(?1 from ?3)),
0) + ?3 -1, 0))

The current Oracle mapping is: instr(?1, ?2, ?3)

There are two issue I want to describe:

1) The ordering of first two parameters in the LOCATE
function

2) How the mapped function deals with the empty string
as the substring to be searched for.


Here goes:
1) For some reason I'm unable to locate the definitive
EJB 2.0 specification which includes a description of
the parameters to the LOCATE EJB-QL function. (Help
anyone?)

I did find plenty of derivative works which all either
don't specify the meaning of the first two string
parameters or explicity say that the function searches
for occurences of the second string parameter in the
first string parameter. In other words the current
PostgreSQL mapping would need ?1 and ?2 switched, but
the ordering in the Oracle mapping is correct.

Since I did locate locate this information in the
actual EJB-QL specification I don't want to claim that
the PostgreSQL parameter ordering is wrong and the
Oracle ordering is correct, but since they are
different I will claim that one of them is wrong :-).


2a) The Oracle INSTR() SQL function considers a search
for the the empty string (i.e. '') as futile and
returns 0 (zero) to indicate the empty string was not
found, regardless of what the string being searched is.

The PostgreSQL POSITION() SQL function considers the
empty string to be found, regardless of the length of
the string being searched.

I'm sorry I again do not have a good reference to the
EJB specification to know which behavior is correct for
the LOCATE() EJB-QL function.

In my opinion the Oracle behavior is more useful,
particularly since null string values get passed in as
empty strings to the JDBC drivers. So, searching for a
null in a string 'Any text' should not in my opinion
return a match. The result of the search for '' in 'Any
text' could be debated. Currently if a pass either null
or '' as a parameter to a custom finder method both
become '' in the final SQL statement.

Assuming the Oracle behavior is preferable, then the
current mapping for PostgreSQL of:
(coalesce(nullif(position(?2 in substring(?1 from ?3)),
0) + ?3 -1, 0)) would need to become
(coalesce(nullif(position(nullif(?2,'') in substring(?1
from ?3)), 0) + ?3 -1, 0)).

Note: depending on the thoughts on 1) this PostgreSQL
mapping should be further changed by swapping ?1 and ?2.


2b) If the empty string is passed as either the first
or second parameter to the Oracle INSTR() SQL function,
the function returns null. Since the EJB-QL function
LOCATE() is supposed to return and integer, the Oracle
function needs to be changed from: instr(?1, ?2, ?3) to
nvl(instr(?1, ?2, ?3),0)

Note: depending on the thoughts on 1) this Oracle
mapping should be further changed by swapping ?1 and ?2.



----------------------------------------------------------------------

>Comment By: Frederik Sauer (fredsa)
Date: 2002-06-06 10:38

Message:
Logged In: YES 
user_id=111914

This does leave the behavior when searching for the empty
string (i.e. '') or searching in an empty string different
between the two databases. For example:

PostgreSQL: position('' in 'test') -> 1
Oracle: instr('test', '') -> NULL

Should search for or on the empty string be considered
outside the spec and thus the behavior "undefined"?

----------------------------------------------------------------------

Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-06-05 23:36

Message:
Logged In: YES 
user_id=251431

Ok I did some research.  The proper argument order is as 
follows:

locate(search_string,  string_to_be_searched, start_position)

Also if any of the arguments are null the function should 
return null, which means the Postgres mapping is wrong. The 
new mappings follow:

Oracle:  
   instr(?2, ?1, ?3)

PostgreSQL:
   (CASE position(?1 in substring(?2 from ?3)) WHEN 0 THEN 
0 ELSE position(?1 in substring(?2 from ?3)) + ?3 - 1 END)

The PostgreSQL mapping is long an possibly inefficent, but is 
the only sql I could come up with that is spec compliant.




----------------------------------------------------------------------

Comment By: Frederik Sauer (fredsa)
Date: 2002-05-14 12:16

Message:
Logged In: YES 
user_id=111914

>From a SQL perspective it certainly would be consitent to
expect a NULL result from a function if any of the
parameters to that function are NULL.

As for the result when searching for the empty string: any
recollection or references as to whether this should result
in a match or not? That is, what are the expected results for:

LOCATE('', 'text to be searched')
LOCATE('substring to search for', '')
LOCATE('', '')


I'd be happy to determine the proper LOCATE() mappings for
PostgreSQL and Oracle and test them if we can decide what
the correct results are for both NULL parameters and empty
string parameters.

----------------------------------------------------------------------

Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-05-14 10:56

Message:
Logged In: YES 
user_id=251431

To find this you have to play follow the footnotes.  The 
ejb-ql spec as defined in JDBC and JDBC says as defined in 
ODBC.

This looks good to me except for one thing. I believe that 
locate on a null string will result in null, but an empty 
string will result in false.  I remember this in one of the 
specs, but I could be wrong.

----------------------------------------------------------------------

Comment By: Frederik Sauer (fredsa)
Date: 2002-05-14 10:44

Message:
Logged In: YES 
user_id=111914

Dain,

Thanks for the ODBC reference link. I could never find where
it was stated that EJB-QL was based on ODBC, but I believe I
found my answer in that both EJB-QL and ODBC are based on
SQL-92.

As for the new mappings of LOCATE:
PostgreSQL mapping should be changed to:

(coalesce(nullif(position(nullif(?1,'') in substring(?2 from
?3)), 0) + ?3 -1, 0))

in order to prevent an empty search string ?1 from causing a
false match.



The Oracle mapping should be changed to:

nvl(instr(?2, ?1, ?3), 0)

in order to correct both the ordering of parameters and to
insure that the function always returns an integer, even if
any of the parameters are empty strings (or null).


I tested this on Oracle8, but am pretty sure that instr()
works identically on Oracle7 and Oracle9 as well.

Fred

----------------------------------------------------------------------

Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-05-13 22:35

Message:
Logged In: YES 
user_id=251431

The definitive reference is at microsoft:

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/odbc/htm/odbcstring_functions.asp

Is this mapping for Oracle 9, 8 or 7?  What is the 
conclusion? This is too much text for me to digest.

----------------------------------------------------------------------

You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=551102&group_id=22866

_______________________________________________________________

Don't miss the 2002 Sprint PCS Application Developer's Conference
August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm

_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development

Reply via email to