glassfish
  1. glassfish
  2. GLASSFISH-380

Outer Join in sybase is broken. Need to switch to use ansi join

    Details

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

      Operating System: Windows XP
      Platform: PC

    • Issuezilla Id:
      380

      Description

      The following 2 queries using Outer Join in Sybase do not yield correct results.

      "SELECT o.id, cust.id from Order o LEFT OUTER JOIN o.customer cust where
      cust.name=?1 ORDER BY o.id"

      Generates this in Sybase

      SELECT t0.ID, t1.ID FROM ORDER_TABLE t0, CUSTOMER_TABLE t1 WHERE ((t1.NAME
      = 'Kellie A. Sanborn') AND (t1.ID =* t0.FK4_FOR_CUSTOMER_TABLE)) ORDER BY t0.ID
      ASC

      Which does not return the correct result set. So I changed it to ansi join and
      worked fine.

      select o.ID, c.ID from ORDER_TABLE o LEFT OUTER JOIN CUSTOMER_TABLE c ON c.ID
      = o.FK4_FOR_CUSTOMER_TABLE where (c.name='Kellie A. Sanborn') order by o.ID

      Similarly,

      "SELECT c.id, s.firstName from Customer c LEFT OUTER JOIN c.spouse s where s.id
      IS NOT NULL AND c.name LIKE ?1"

      Generates this SQL which does not return the expected result set :

      SELECT t0.ID, t1.FIRSTNAME FROM CUSTOMER_TABLE t0, SPOUSE_TABLE t1 WHERE ((NOT
      ((t1.ID IS NULL)) AND (t0.NAME LIKE '%Caruso')) AND (t1.FK7_FOR_CUSTOMER_TABLE
      =* t0.ID))

      I Changed it to the following ansi sql query and then it returned the correct
      result set.

      SELECT c.ID, s.FIRSTNAME FROM CUSTOMER_TABLE c left outer join SPOUSE_TABLE s
      on s.FK7_FOR_CUSTOMER_TABLE = c.ID WHERE
      ((NOT ((s.ID IS NULL)) AND (c.NAME LIKE '%Caruso')))

      Also adding comments from Mitesh

      =========================================

      Hi Michael, Sailaja,

      Sorry for not responding to the mail in the morning. I read it during the call
      and forgot to flag it.

      Markus reminded me that the native outer join (using =*) in sybase is broken.
      We switched the cmp2.x runtime to use ansi join on sybase. It is possible that
      this might also be the issue with native outer join semantics.

      Sailaja,
      Could you please try following query against your dataset and see what result
      you get
      SELECT c.ID, s.FIRSTNAME FROM CUSTOMER_TABLE c left outer join SPOUSE_TABLE s
      on s.FK7_FOR_CUSTOMER_TABLE = c.ID WHERE
      ((NOT ((s.ID IS NULL)) AND (c.NAME LIKE '%Caruso')))

      Thanks,
      Mitesh

      ============================================================

        Activity

        Hide
        marina vatkina added a comment -

        ...

        Show
        marina vatkina added a comment - ...
        Hide
        Mitesh Meswani added a comment -

        reassigning to Markus

        Show
        Mitesh Meswani added a comment - reassigning to Markus
        Hide
        mf125085 added a comment -

        Changed src/java/oracle/toplink/essentials/platform/database/SybasePlatform.java

        ***************

            • 615,617 ****
              public boolean shouldPrintOuterJoinInWhereClause() { ! return false; }
            • 615,617 ----
              public boolean shouldPrintOuterJoinInWhereClause() { ! return true; }

        Checking in
        src/java/oracle/toplink/essentials/platform/database/SybasePlatform.java;
        /cvs/glassfish/entity-persistence/src/java/oracle/toplink/essentials/platform/database/SybasePlatfor
        m.java,v <-- SybasePlatform.java
        new revision: 1.7; previous revision: 1.6
        done

        Show
        mf125085 added a comment - Changed src/java/oracle/toplink/essentials/platform/database/SybasePlatform.java *************** 615,617 **** public boolean shouldPrintOuterJoinInWhereClause() { ! return false; } 615,617 ---- public boolean shouldPrintOuterJoinInWhereClause() { ! return true; } Checking in src/java/oracle/toplink/essentials/platform/database/SybasePlatform.java; /cvs/glassfish/entity-persistence/src/java/oracle/toplink/essentials/platform/database/SybasePlatfor m.java,v <-- SybasePlatform.java new revision: 1.7; previous revision: 1.6 done
        Hide
        mf125085 added a comment -

        Set target milestone.

        Show
        mf125085 added a comment - Set target milestone.

          People

          • Assignee:
            mf125085
            Reporter:
            sailajar
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: