Bug #3179 concatenation with '' and '%' fails using MAXDB in Oracle Mode
Submitted: 15 Mar 2004 11:51 Modified: 3 Feb 2005 14:40
Reporter: John Dunn Email Updates:
Status: No Feedback Impact on me:
None 
Category:MaxDB Severity:S2 (Serious)
Version:7.5 OS:Microsoft Windows (Windows 2000)
Assigned to: Assigned Account CPU Architecture:Any

[15 Mar 2004 11:51] John Dunn
Description:
The following statement in an EJB returns all data from a table in Oracle when the bind variable is null

select * from <some_table> where <some_field like> ? || '%'

the same statement returns no data in MAXDB using Oracle mode, even tried:

select * from <some_table> where <some_field like> nvl(?,'') || '%'

Finally hacked it to work using:

select * from <some_table> where <some_field like> nvl(?,'%') || '%'

How to repeat:
Repeatably executes as above and from command prompt

Suggested fix:
Null behavior in concatenation using Oracle differs from MAXDB in Oracle mode. Who's right?
[3 Jan 2005 12:52] Ulf Wendel
Hi John,

I need you help with this. Your first query is:

select * from <some_table> where <some_field like> ? || '%'

with ? = NULL. This expands to "LIKE NULL || '%'". The MaxDB documentation mentions that concatenations that contain NULL values result in NULL (http://dev.mysql.com/doc/maxdb/en/71/81736eb9b311d2a97100a0c9449261/frameset.htm), so we get LIKE NULL. This should be the same like Oracle. Oracle tells me in the 10g SQL documentation: "All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs."

NULL means undefined and you can't compare undefined to anything, so the result of you query should be empty. Similar with Oracle: "Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null." The 10g documentation gives me more details about like. "If esc_char is not specified, then there is no default escape character. If any of char1, char2, or esc_char is null, then the result is unknown." and later on: "A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null."

Looks like Oracle does something weird when replacing ?. If it would really replace ? by NULL, then there should be no result. And that's the behaviour you have observed with MaxDB.

Regards,
Ulf
[3 Jan 2005 14:40] John Dunn
Michel:

There appears to be a difference in the way that MAXDB treats an "empty string" and Oracle's treatment. Using the jdbc interface, when the value of a String bind variable is "", it is an empty string but not "null". When "" is concatenated to "%", the result is ( and should be ) "%"; however, in MAXDB it appears as null or undefined. MAXDB is confusing an empty string as a null string. Although languages such as C do not provide a distinction between an empty string and a null string, languages such as JAVA do.

This is repeatable behavior and I have made changes to my code templates to allow for the difference in behavior between MaxDB and Oracle.

Perhaps this bug should just be closed.

thanks,

John Dunn
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".