MySQL Bugs: #3179: concatenation with '' and '%' fails using MAXDB in Oracle Mode

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:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

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?

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

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

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".