CONCAT must be translated into || instead of +

[glassfishrobot]

The SQLAnyWhere Platform currently translates the JPA function "CONCAT(a, b)" into the SQL term "a + b". Actually this is wrong and potentially leads to crashes: In case "a" contains a string and "b" contains a number, the SQL command will crash because the DBMS tries to convert the string into a number (and not vice versa as one might expect).

To fix it, the SQLAnyWhere Platform must override the CONCAT translation into the correct form. Instead of "a + b" it must produce "a || b". The difference between the "plus" and the "||" operator is that "plus" is a numeric operating (all operands are first converted to numbers as soon as one operand is a number, what does not work with non-numeric strings), while "||" is a string operator (all operands are first converted to strings, what even works for numbers).

I am the author of the SQLAnyWhere Platform, but I do not have CSV commit rights, unfortunately. So I need to ask you to fix that for me. The fix should be as easy as adding the CONCAT override and such is done in a few minutes. Thanks. Unfortunately this fix is really urgent because it will be a showstopper for people using CONCAT with numbers. There is no workaround.

Environment

Operating System: All Platform: All

Affected Versions

[9.1.1]

[glassfishrobot]

• Issue Imported From: https://github.com/javaee/glassfish/issues/5158
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @yaminikb
• Original Issue Closed By:@glassfishrobot

[glassfishrobot]

@glassfishrobot Commented tware said: Making prioirty P3 because of workaround.

Workaround is to create a subclass of SQLAnyWherePlatform that includes the change and use the platform.

[glassfishrobot]

@glassfishrobot Commented mkarg said: The proposed workaround is not possible, since the class is final to prevent faults injected by faulty subclasses:

public final class SQLAnyWherePlatform extends DatabasePlatform {

The workaround will be to write a complete new platform as a copy of this one, and handle the bug in that one.

[glassfishrobot]

@glassfishrobot Commented mkarg said: I have developed a fix. Please be so kind and add it to TopLink Essentials and EclipseLink, so it will be contained in the next releases:

In the file SQLAnyWherePlatform.java please add the following two changes.

(CHANGE 1) In the class "SQLAnyWherePlatform" add this new method:

private static final ExpressionOperator createConcatOperator()

{ return ExpressionOperator.simpleLogicalNoParens(ExpressionOperator.Concat, "||"); }

(CHANGE 2) In the already existing method "initializePlatformOperators()" add:

... this.addOperator(SqlAnyWherePlatform.createConcatOperator()); ...

I have tried out this fix in a real world scenario and it is working correctly.

Thanks Markus

[glassfishrobot]

@glassfishrobot Commented tware said: FYI: For any code you submit to appear in EclipseLink, for legal reasons, it must also be submitted in the Eclipse bug system.

A link to the bug database can be found on the left side of the following page:

http://www.eclipse.org/eclipselink/

[glassfishrobot]

@glassfishrobot Commented mkarg said:

• • • Issue 5158 has been confirmed by votes. ***

[glassfishrobot]

@glassfishrobot Commented harpreet said: Approved for v2.1

[glassfishrobot]

@glassfishrobot Commented mm110999 said: Checked in changes proposed by Markus. Following is the log

Checking in java/oracle/toplink/essentials/platform/database/SQLAnyWherePlatform .java; /cvs/glassfish/entity-persistence/src/java/oracle/toplink/essentials/platform/da tabase/SQLAnyWherePlatform.java,v <-- SQLAnyWherePlatform.java new revision: 1.6.6.2; previous revision: 1.6.6.1 done

[glassfishrobot]

@glassfishrobot Commented Was assigned to tware

[glassfishrobot]

@glassfishrobot Commented This issue was imported from java.net JIRA GLASSFISH-5158

[glassfishrobot]

@glassfishrobot Commented Reported by mkarg

[glassfishrobot]

@glassfishrobot Commented Marked as fixed on Monday, September 8th 2008, 5:29:14 am