glassfish
  1. glassfish
  2. GLASSFISH-5158

CONCAT must be translated into || instead of +

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 9.1.1
    • Fix Version/s: 9.1.1_dev
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: All

    • Issuezilla Id:
      5,158
    • Status Whiteboard:
      Hide

      911Approved

      Show
      911Approved

      Description

      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.

        Activity

        Hide
        tware added a comment -

        Making prioirty P3 because of workaround.

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

        Show
        tware added a comment - Making prioirty P3 because of workaround. Workaround is to create a subclass of SQLAnyWherePlatform that includes the change and use the platform.
        Hide
        mkarg added a comment -

        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.

        Show
        mkarg added a comment - 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.
        Hide
        mkarg added a comment -

        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

        Show
        mkarg added a comment - 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
        Hide
        tware added a comment -

        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/

        Show
        tware added a comment - 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/
        Hide
        mkarg added a comment -
            • Issue 5158 has been confirmed by votes. ***
        Show
        mkarg added a comment - Issue 5158 has been confirmed by votes. ***
        Hide
        harpreet added a comment -

        Approved for v2.1

        Show
        harpreet added a comment - Approved for v2.1
        Hide
        Mitesh Meswani added a comment -

        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

        Show
        Mitesh Meswani added a comment - 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

          People

          • Assignee:
            tware
            Reporter:
            mkarg
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: