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: Accepted 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-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 _______________________________________________________________ Have big pipes? SourceForge.net is looking for download mirrors. We supply the hardware. You get the recognition. Email Us: [EMAIL PROTECTED] _______________________________________________ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
