glassfish
  1. glassfish
  2. GLASSFISH-867

Join secondary tables in queries using OUTER JOIN

    Details

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

      Operating System: All
      Platform: All

    • Issuezilla Id:
      867

      Description

      TopLink joins secondary tables in select queries using INNER JOIN

      Assuming an Order entity is mapped the following way:

      @Entity()
      @Table(name="J1Order")
      @SecondaryTable(name="ORDER_SEC")
      @NamedQuery(name="queryOrder", query="select OBJECT(o) from Order o where o.id=:id")
      public class Order {
      ...
      }

      TopLink generates the following SQL for "queryOrder":

      SELECT ...

      FROM ORDER_SEC t1, J1Order t0 WHERE ((t0.ID = ?) AND (t1.ID = t0.ID))

      I.e. the secondary and primary table are joined by INNER JOIN. This means that
      no rows are returned, if the secondary table doesn't have entries for a
      specific ID. To support legacy data, secondary tables should always be joined by
      OUTER JOIN.

        Activity

        Hide
        mf125085 added a comment -

        ...

        Show
        mf125085 added a comment - ...
        Hide
        mf125085 added a comment -

        This functionality is advanced and not easily found by users. It would not be
        used without some 'hand-holding' of the customer in which case they would be
        instructed to make those mappings read-only. TopLink has no logic to detect
        this altered secondary table configuration and raise exceptions if the user is
        not using it appropriately. If the user is using these advances features then
        it is presumed the user has the technical ability to determine appropriate
        behaviour for their data and model.
        Delete behaviour would not be affected.
        --Gordon

        ----Original Message----
        From: Marina.Vatkina@Sun.COM Marina.Vatkina@Sun.COMOn Behalf Of
        Marina Vatkina
        Sent: Thursday, August 03, 2006 3:35 PM
        To: persistence@glassfish.dev.java.net
        Subject: Re: Outer joins for secondary tables?

        Hello Gordon,

        Will TopLink throw an exception if the user changes such entity?
        Or will it allow the database to win (i.e. it'll update successfully
        only those entities that have rows in both tables)?
        How about a delete?

        thanks,
        -marina

        Gordon Yorke wrote:

        >> Hello Markus, This customization is both in TopLink and TopLink Essentials.
        >> It does require use of TopLink API directly and is not exposed through the
        >> JPA. It is the user's responsibility to either treat the class as readonly
        >> or mark all of the mappings read-only (updatable, insertable = false in JPA).
        >> The best way to map this relationship is as a OneToOne.
        >>
        >> --Gordon
        >>
        >> ----Original Message---- From: Markus.Fuchs@Sun.COM
        >> Markus.Fuchs@Sun.COMOn Behalf Of Markus Fuchs Sent: Thursday, August
        >> 03, 2006 2:14 PM To: Gordon Yorke Cc: persistence@glassfish.dev.java.net
        >> Subject: Re: Outer joins for secondary tables?
        >>
        >>
        >> [including a broader audience]
        >>
        >> Hi Gordon,
        >>
        >> Thanks for your information! I have a couple of further questions:
        >>
        >> Gordon Yorke wrote:
        >>
        >>
        >
        >>>> Hello Markus, In TopLink a user can customize the
        >>>> MultipleTableJoinExpression in the DescriptorQueryManager. TopLink uses
        >>>> this expression when joining, updating this expression to use outer joins
        >>>> will cause TopLink to use outerjoins for secondary tables.
        >>>>
        >>>>
        >>>>
        >
        >>
        >> Is this customization a TopLink product feature? I understand it is not part
        >> of TopLink-Essentials.
        >>
        >>
        >
        >>>> This method, however, will only work for read-only objects as TopLink will
        >>>> be unable to update any secondary fields for objects that did not have a
        >>>> row in the secondary table. TopLink has no way of determining if the row
        >>>> was there once the object was built. Having this functionality would be a
        >>>> very large undertaking and would proportionally be of little value.
        >>>>
        >>>>
        >>>>
        >
        >>
        >> Does customizing the MultipleTableJoinExpression make the entity read-only
        >> automatically or is it the user's responsibility to treat that class as
        >> read-only? If the class is read-only automatically, would it be possible to
        >> treat only the fields actually mapped to the secondary table as read-only? In
        >> a JPA context, would it be sufficient to mark the secondary table fields
        >> "updatable=false"?
        >>
        >> Thanks!
        >>
        >> – markus.
        >>
        >>
        >
        >>>> This sort of relationship should be mapped as an OneToOne.
        >>>>
        >>>> --Gordon
        >>>>
        >>>> ----Original Message---- From: Markus.Fuchs@Sun.COM
        >>>> Markus.Fuchs@Sun.COMOn Behalf Of Markus Fuchs Sent: Wednesday,
        >>>> August 02, 2006 7:27 PM To: Tom Ware; Gordon Yorke Subject: Outer joins for
        >>>> secondary tables?
        >>>>
        >>>>
        >>>> Hi Gordon,
        >>>>
        >>>> I remember that TopLink can be switched
        >>>> from generating inner joins for secondary tables to outer
        >>>> joins.
        >>>>
        >>>> Thanks so much,
        >>>>
        >>>> – markus.

        Show
        mf125085 added a comment - This functionality is advanced and not easily found by users. It would not be used without some 'hand-holding' of the customer in which case they would be instructed to make those mappings read-only. TopLink has no logic to detect this altered secondary table configuration and raise exceptions if the user is not using it appropriately. If the user is using these advances features then it is presumed the user has the technical ability to determine appropriate behaviour for their data and model. Delete behaviour would not be affected. --Gordon ---- Original Message ---- From: Marina.Vatkina@Sun.COM Marina.Vatkina@Sun.COM On Behalf Of Marina Vatkina Sent: Thursday, August 03, 2006 3:35 PM To: persistence@glassfish.dev.java.net Subject: Re: Outer joins for secondary tables? Hello Gordon, Will TopLink throw an exception if the user changes such entity? Or will it allow the database to win (i.e. it'll update successfully only those entities that have rows in both tables)? How about a delete? thanks, -marina Gordon Yorke wrote: >> Hello Markus, This customization is both in TopLink and TopLink Essentials. >> It does require use of TopLink API directly and is not exposed through the >> JPA. It is the user's responsibility to either treat the class as readonly >> or mark all of the mappings read-only (updatable, insertable = false in JPA). >> The best way to map this relationship is as a OneToOne. >> >> --Gordon >> >> ---- Original Message ---- From: Markus.Fuchs@Sun.COM >> Markus.Fuchs@Sun.COM On Behalf Of Markus Fuchs Sent: Thursday, August >> 03, 2006 2:14 PM To: Gordon Yorke Cc: persistence@glassfish.dev.java.net >> Subject: Re: Outer joins for secondary tables? >> >> >> [including a broader audience] >> >> Hi Gordon, >> >> Thanks for your information! I have a couple of further questions: >> >> Gordon Yorke wrote: >> >> > >>>> Hello Markus, In TopLink a user can customize the >>>> MultipleTableJoinExpression in the DescriptorQueryManager. TopLink uses >>>> this expression when joining, updating this expression to use outer joins >>>> will cause TopLink to use outerjoins for secondary tables. >>>> >>>> >>>> > >> >> Is this customization a TopLink product feature? I understand it is not part >> of TopLink-Essentials. >> >> > >>>> This method, however, will only work for read-only objects as TopLink will >>>> be unable to update any secondary fields for objects that did not have a >>>> row in the secondary table. TopLink has no way of determining if the row >>>> was there once the object was built. Having this functionality would be a >>>> very large undertaking and would proportionally be of little value. >>>> >>>> >>>> > >> >> Does customizing the MultipleTableJoinExpression make the entity read-only >> automatically or is it the user's responsibility to treat that class as >> read-only? If the class is read-only automatically, would it be possible to >> treat only the fields actually mapped to the secondary table as read-only? In >> a JPA context, would it be sufficient to mark the secondary table fields >> "updatable=false"? >> >> Thanks! >> >> – markus. >> >> > >>>> This sort of relationship should be mapped as an OneToOne. >>>> >>>> --Gordon >>>> >>>> ---- Original Message ---- From: Markus.Fuchs@Sun.COM >>>> Markus.Fuchs@Sun.COM On Behalf Of Markus Fuchs Sent: Wednesday, >>>> August 02, 2006 7:27 PM To: Tom Ware; Gordon Yorke Subject: Outer joins for >>>> secondary tables? >>>> >>>> >>>> Hi Gordon, >>>> >>>> I remember that TopLink can be switched >>>> from generating inner joins for secondary tables to outer >>>> joins. >>>> >>>> Thanks so much, >>>> >>>> – markus.
        Hide
        gyorke added a comment -

        This is not a 'defect'. Secondary tables should not "always" be joined with an
        OUTER JOIN. In the provided example the second table should be mapped as a
        OneToOne. Where the lack of a row defines non-existence. If the secondary
        table is OUTER JOINED any missing rows contain UNDEFINED data. The data is not
        'null' nor can those values be automatically treated as ‘null’ by the
        persistence provider, the values to put in the object are undefined (ie any
        value that is defined to be 'not null' on the database the persistence provider
        can not read that in as 'null'). As well, always performing an OUTER JOIN leads
        to inconsistent update behaviour that will be confusing and extremely difficult
        to diagnose by the vast majority of users.

        If the end user does not wish to map the data as a OneToOne the end user will
        need to update the contents of the secondary table to reflect the required
        defaults for each row in the primary table

        If users request this OUTER JOIN functionality as a must have then the feature
        must be well thought out and designed with configurable behaviours for undefined
        data and writes.

        Show
        gyorke added a comment - This is not a 'defect'. Secondary tables should not "always" be joined with an OUTER JOIN. In the provided example the second table should be mapped as a OneToOne. Where the lack of a row defines non-existence. If the secondary table is OUTER JOINED any missing rows contain UNDEFINED data. The data is not 'null' nor can those values be automatically treated as ‘null’ by the persistence provider, the values to put in the object are undefined (ie any value that is defined to be 'not null' on the database the persistence provider can not read that in as 'null'). As well, always performing an OUTER JOIN leads to inconsistent update behaviour that will be confusing and extremely difficult to diagnose by the vast majority of users. If the end user does not wish to map the data as a OneToOne the end user will need to update the contents of the secondary table to reflect the required defaults for each row in the primary table If users request this OUTER JOIN functionality as a must have then the feature must be well thought out and designed with configurable behaviours for undefined data and writes.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: