glassfish
  1. glassfish
  2. GLASSFISH-433

Incorrect JDBC escape syntax for outer joins?

    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: All
      Platform: All

    • Issuezilla Id:
      433

      Description

      I think
      "oracle/toplink/essentials/internal/expressions/SQLSelectStatement.java"
      generates incorrect JDBC statements with "outer joins" for some database
      servers (PostgreSQL, Derby, etc. ).

      The method "appendFromClauseForOuterJoin" has the statement:
      ...
      if (session.getPlatform().shouldUseJDBCOuterJoinSyntax()) {
      writer.write("{'oj ");
      }
      ...

      This work with Oracle's JDBC drivers, but other JDBC drivers for PostgreSQL,
      Derby, etc. that also support JDBC escape syntax for outer joins, don't work
      because they expect the syntax "{oj " (without the tilde).

      Some references:
      http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/statement.html
      (section 4.1.5, "oj" keyword)

      http://jdbc.postgresql.org/documentation/81/outer-joins-escape.html
      http://db.apache.org/derby/docs/dev/ref/rrefjdbc37244.html

      Thanks.

        Activity

        Hide
        marina vatkina added a comment -

        Please attach the test case that reproduces this problem. Which query are you
        using to access escape syntax for outer join?

        Show
        marina vatkina added a comment - Please attach the test case that reproduces this problem. Which query are you using to access escape syntax for outer join?
        Hide
        ycswyw added a comment -

        Abstract code:
        --------------
        // to find the provinces where registered companies are providing
        // an specified service (parameter "serviceId")

        Query query = entityManager.createQuery("SELECT DISTINCT OBJECT(province) FROM
        Company company JOIN company.city city JOIN city.province province WHERE
        company.service.id = :serviceId");
        query.setParameter("serviceId", serviceId);
        List<Province> provinces = query.getResultList();

        If the entityManager is linked with a PostgreSQL connection,
        the previous query generates the next "JDBC" statement:

        SELECT DISTINCT t0.ID, t0.NAME FROM

        {'oj COMPANY t2 LEFT OUTER JOIN CITY t1 ON (t1.ID = t2.CITY)}

        , SERVICE t3, PROVINCE t0 WHERE (((t1.PROVINCE
        = t0.ID) AND (t3.ID = 1)) AND (t3.ID = t2.SERVICE))

        But the statement doesn't work with PostgreSQL's JDBC driver, because of a
        "org.postgresql.util.PSQLException": ERROR: syntax error at or near "{"
        Error Code: 0

        Without the tilde, the statement works fine.

        Domain objects:
        ---------------

        @Entity
        public class Company implements java.io.Serializable
        {
        Integer id;
        String name;
        Service service;
        City city;

        @Id
        @Column(name="ID")
        @GeneratedValue(strategy=GenerationType.TABLE)
        public Integer getId()

        { return id; }

        public
        void setId(Integer id)
        { this.id = id; }



        @Column(name="NAME")
        public String getName()
        { return name; }

        public void setName(String name)
        { this.name = name; }


        @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
        @JoinColumn(name = "SERVICE")
        public Service getService()
        { return service; }

        public void setService(Service service)
        { this.service = service; }

        @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch =
        FetchType.EAGER)
        @JoinColumn(name = "CITY")
        public City getCity()
        { return city; }

        public void setCity(City city)
        { this.city = city; }

        }





        @Entity
        public class Service implements java.io.Serializable
        {
        Integer id;
        String name;

        @Id
        @Column(name = "ID")
        @GeneratedValue(strategy=GenerationType.TABLE)
        public Integer getId()
        { return id; }

        public void setId(Integer id)

        { this.id = id; }

        @Column(name = "NAME", nullable = false, length = 50)
        public String getName()
        { return name; }

        public void setName(String str)
        { name = str; }

        }


        @Entity
        public class City implements java.io.Serializable
        {
        Integer id;
        String name;
        Province province;

        @Id
        @Column(name = "ID")
        @GeneratedValue(strategy=GenerationType.TABLE)
        public Integer getId()
        { return id; }

        public void setId(Integer id)
        { this.id = id; }

        @Column(name = "NAME", nullable = false, length = 50)
        public String getName()

        { return name; }

        public void setName(String str)
        { name = str; }


        @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch =
        FetchType.EAGER)
        @JoinColumn(name = "PROVINCE")
        public Province getProvince()
        { return province; }

        public void setProvince(Province province)
        { this.province = province; }

        }



        @Entity
        public class Province implements java.io.Serializable
        {
        Integer id;
        String name;

        @Id
        @Column(name = "ID")
        @GeneratedValue(strategy=GenerationType.TABLE)
        public Integer getId()
        { return id; }

        public void setId(Integer id)
        { this.id = id; }

        @Column(name = "NAME", nullable = false, length = 50)
        public String getName()
        { return name; }

        public void setName(String str)

        { name = str; }

        }

        Show
        ycswyw added a comment - Abstract code: -------------- // to find the provinces where registered companies are providing // an specified service (parameter "serviceId") Query query = entityManager.createQuery("SELECT DISTINCT OBJECT(province) FROM Company company JOIN company.city city JOIN city.province province WHERE company.service.id = :serviceId"); query.setParameter("serviceId", serviceId); List<Province> provinces = query.getResultList(); If the entityManager is linked with a PostgreSQL connection, the previous query generates the next "JDBC" statement: SELECT DISTINCT t0.ID, t0.NAME FROM {'oj COMPANY t2 LEFT OUTER JOIN CITY t1 ON (t1.ID = t2.CITY)} , SERVICE t3, PROVINCE t0 WHERE (((t1.PROVINCE = t0.ID) AND (t3.ID = 1)) AND (t3.ID = t2.SERVICE)) But the statement doesn't work with PostgreSQL's JDBC driver, because of a "org.postgresql.util.PSQLException": ERROR: syntax error at or near "{" Error Code: 0 Without the tilde, the statement works fine. Domain objects: --------------- @Entity public class Company implements java.io.Serializable { Integer id; String name; Service service; City city; @Id @Column(name="ID") @GeneratedValue(strategy=GenerationType.TABLE) public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } @Column(name="NAME") public String getName() { return name; } public void setName(String name) { this.name = name; } @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL) @JoinColumn(name = "SERVICE") public Service getService() { return service; } public void setService(Service service) { this.service = service; } @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER) @JoinColumn(name = "CITY") public City getCity() { return city; } public void setCity(City city) { this.city = city; } } @Entity public class Service implements java.io.Serializable { Integer id; String name; @Id @Column(name = "ID") @GeneratedValue(strategy=GenerationType.TABLE) public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } @Column(name = "NAME", nullable = false, length = 50) public String getName() { return name; } public void setName(String str) { name = str; } } @Entity public class City implements java.io.Serializable { Integer id; String name; Province province; @Id @Column(name = "ID") @GeneratedValue(strategy=GenerationType.TABLE) public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } @Column(name = "NAME", nullable = false, length = 50) public String getName() { return name; } public void setName(String str) { name = str; } @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER) @JoinColumn(name = "PROVINCE") public Province getProvince() { return province; } public void setProvince(Province province) { this.province = province; } } @Entity public class Province implements java.io.Serializable { Integer id; String name; @Id @Column(name = "ID") @GeneratedValue(strategy=GenerationType.TABLE) public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } @Column(name = "NAME", nullable = false, length = 50) public String getName() { return name; } public void setName(String str) { name = str; } }
        Hide
        marina vatkina added a comment -

        Tom,

        This bug is caused by the following code in SQLSelectStatement:

        if (session.getPlatform().shouldUseJDBCOuterJoinSyntax()) {
        writer.write("

        {'oj "); }

        Now, DatabasePlatform returns true for this question:
        public boolean shouldUseJDBCOuterJoinSyntax()

        { return true; }

        But all other platforms, other than Postgress override it with 'false' and do
        not use this syntax.

        Does it make sense to change the default to false?

        thanks,
        -marina

        Show
        marina vatkina added a comment - Tom, This bug is caused by the following code in SQLSelectStatement: if (session.getPlatform().shouldUseJDBCOuterJoinSyntax()) { writer.write(" {'oj "); } Now, DatabasePlatform returns true for this question: public boolean shouldUseJDBCOuterJoinSyntax() { return true; } But all other platforms, other than Postgress override it with 'false' and do not use this syntax. Does it make sense to change the default to false? thanks, -marina
        Hide
        marina vatkina added a comment -

        It needs to be fixed for PostgreSQLPlatform. We can readdress the overall use of
        shouldUseJDBCOuterJoinSyntax() later

        Show
        marina vatkina added a comment - It needs to be fixed for PostgreSQLPlatform. We can readdress the overall use of shouldUseJDBCOuterJoinSyntax() later
        Hide
        pramodgo added a comment -

        Have sent the code changes for review.

        Show
        pramodgo added a comment - Have sent the code changes for review.
        Hide
        pramodgo added a comment -

        Have checked in the changes for Postgresql platform

        Show
        pramodgo added a comment - Have checked in the changes for Postgresql platform
        Hide
        mf125085 added a comment -

        Hi ycswyw,

        we're currently looking into this issue, please see issue 812. Do you know,
        which Oracle versions require "{'oj "?

        Thanks,

        – markus.

        Show
        mf125085 added a comment - Hi ycswyw, we're currently looking into this issue, please see issue 812. Do you know, which Oracle versions require "{'oj "? Thanks, – markus.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: