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: Open
Resolution: None
Priority: 5
Submitted By: frederik sauer (fredsa)
Assigned to: Nobody/Anonymous (nobody)
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.



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

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

Reply via email to