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
