olingo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Patrick Haller (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (OLINGO-1312) Olingo 2.0.x JPA generating unexecutable SQL for HANA in presence of NCLOB columns
Date Wed, 24 Oct 2018 16:32:00 GMT

     [ https://issues.apache.org/jira/browse/OLINGO-1312?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Patrick Haller updated OLINGO-1312:
-----------------------------------
    Description: 
h2. Summary

In presence of NCLOB columns, Olingo JPA Processor generates an unsupported SELECT DISTINCT
... SQL when reading child entity sets connected to a parent entity with

{code}GET http://myservice/api/odata/Childs{code}

We believe a different SQL needs to be generated to avoid the cartesian multiplication during
joining the dependent entities.


h2. Details

We have entities defined like

{code}public class Parent
{
     @Id
     private String uuid;

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true,
mappedBy = "parent")
    private List<Child> children = new ArrayList<>();
}
{code}

and

{code}
public class Child
{
     @Id
    private String uuid;

    @ManyToOne
    @JoinColumn(name = "parent_uuid", nullable = false)
    private Parent parent;
 

    @Column(length = 255, nullable = false)
    @Size(max = 255)
    private String title;

    @Column(length = -1)
    @Lob
    private String description;
}
{code}
 

The *Child.description* column is defined as a @Lob (NCLOB in SAP HANA).

OlingoJPAProcessor generates

{code}
SELECT DISTINCT t1.uuid AS a1, t1.description AS a2, t1.title AS a3, t1.parent_uuid AS a4
    FROM parent t0,
         child t1
    WHERE t0.uuid = t1.parent_uuid))
    ORDER BY t1.uuid LIMIT ? OFFSET ?
{code}

from this.

Eventually, this leads to 

*com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [264]: invalid datatype:
LOB type in distinct select clause:*
 *SQL State: HY000, Error Code: 264*

{code}
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:191)
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:42)
at com.sap.db.jdbc.packet.HReplyPacket._buildExceptionChain(HReplyPacket.java:976)
at com.sap.db.jdbc.packet.HReplyPacket.getSQLExceptionChain(HReplyPacket.java:157)
at com.sap.db.jdbc.packet.HPartInfo.getSQLExceptionChain(HPartInfo.java:39)
at com.sap.db.jdbc.ConnectionSapDB._receive(ConnectionSapDB.java:3437)
at com.sap.db.jdbc.ConnectionSapDB.exchange(ConnectionSapDB.java:1575)
at com.sap.db.jdbc.PreparedStatementSapDB._prepare(PreparedStatementSapDB.java:2816)
...
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:480)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAPage$JPAPageBuilder.buildFromQuery(JPAPage.java:117)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAPage$JPAPageBuilder.build(JPAPage.java:95)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAProcessorImpl.handlePaging(JPAProcessorImpl.java:443)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAProcessorImpl.process(JPAProcessorImpl.java:165)
...
{code}

  was:
h2. Summary

In presence of NCLOB columns, Olingo JPA Processor generates an unsupported SELECT DISTINCT
... SQL when reading child entity sets connected to a parent entity with

{code}GET [http://myservice/api/odata/Childs]{code}

We believe a different SQL needs to be generated to avoid the cartesian multiplication during
joining the dependent entities.


h2. Details

We have entities defined like

{code}public class Parent
{
     @Id
     private String uuid;

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true,
mappedBy = "parent")
    private List<Child> children = new ArrayList<>();
}
{code}

and

{code}
public class Child
{
     @Id
    private String uuid;

    @ManyToOne
    @JoinColumn(name = "parent_uuid", nullable = false)
    private Parent parent;
 

    @Column(length = 255, nullable = false)
    @Size(max = 255)
    private String title;

    @Column(length = -1)
    @Lob
    private String description;
}
{code}
 

The *Child.description* column is defined as a @Lob (NCLOB in SAP HANA).

OlingoJPAProcessor generates

{code}
SELECT DISTINCT t1.uuid AS a1, t1.description AS a2, t1.title AS a3, t1.parent_uuid AS a4
    FROM parent t0,
         child t1
    WHERE t0.uuid = t1.parent_uuid))
    ORDER BY t1.uuid LIMIT ? OFFSET ?
{code}

from this.

Eventually, this leads to 

*com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [264]: invalid datatype:
LOB type in distinct select clause:*
 *SQL State: HY000, Error Code: 264*

{code}
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:191)
at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:42)
at com.sap.db.jdbc.packet.HReplyPacket._buildExceptionChain(HReplyPacket.java:976)
at com.sap.db.jdbc.packet.HReplyPacket.getSQLExceptionChain(HReplyPacket.java:157)
at com.sap.db.jdbc.packet.HPartInfo.getSQLExceptionChain(HPartInfo.java:39)
at com.sap.db.jdbc.ConnectionSapDB._receive(ConnectionSapDB.java:3437)
at com.sap.db.jdbc.ConnectionSapDB.exchange(ConnectionSapDB.java:1575)
at com.sap.db.jdbc.PreparedStatementSapDB._prepare(PreparedStatementSapDB.java:2816)
...
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:480)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAPage$JPAPageBuilder.buildFromQuery(JPAPage.java:117)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAPage$JPAPageBuilder.build(JPAPage.java:95)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAProcessorImpl.handlePaging(JPAProcessorImpl.java:443)
at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAProcessorImpl.process(JPAProcessorImpl.java:165)
...
{code}


> Olingo 2.0.x JPA generating unexecutable SQL for HANA in presence of NCLOB columns
> ----------------------------------------------------------------------------------
>
>                 Key: OLINGO-1312
>                 URL: https://issues.apache.org/jira/browse/OLINGO-1312
>             Project: Olingo
>          Issue Type: Bug
>          Components: odata2-jpa
>    Affects Versions: V2 2.0.10
>            Reporter: Patrick Haller
>            Priority: Major
>
> h2. Summary
> In presence of NCLOB columns, Olingo JPA Processor generates an unsupported SELECT DISTINCT
... SQL when reading child entity sets connected to a parent entity with
> {code}GET http://myservice/api/odata/Childs{code}
> We believe a different SQL needs to be generated to avoid the cartesian multiplication
during joining the dependent entities.
> h2. Details
> We have entities defined like
> {code}public class Parent
> {
>      @Id
>      private String uuid;
>     @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval =
true, mappedBy = "parent")
>     private List<Child> children = new ArrayList<>();
> }
> {code}
> and
> {code}
> public class Child
> {
>      @Id
>     private String uuid;
>     @ManyToOne
>     @JoinColumn(name = "parent_uuid", nullable = false)
>     private Parent parent;
>  
>     @Column(length = 255, nullable = false)
>     @Size(max = 255)
>     private String title;
>     @Column(length = -1)
>     @Lob
>     private String description;
> }
> {code}
>  
> The *Child.description* column is defined as a @Lob (NCLOB in SAP HANA).
> OlingoJPAProcessor generates
> {code}
> SELECT DISTINCT t1.uuid AS a1, t1.description AS a2, t1.title AS a3, t1.parent_uuid AS
a4
>     FROM parent t0,
>          child t1
>     WHERE t0.uuid = t1.parent_uuid))
>     ORDER BY t1.uuid LIMIT ? OFFSET ?
> {code}
> from this.
> Eventually, this leads to 
> *com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [264]: invalid datatype:
LOB type in distinct select clause:*
>  *SQL State: HY000, Error Code: 264*
> {code}
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB._newInstance(SQLExceptionSapDB.java:191)
> at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.newInstance(SQLExceptionSapDB.java:42)
> at com.sap.db.jdbc.packet.HReplyPacket._buildExceptionChain(HReplyPacket.java:976)
> at com.sap.db.jdbc.packet.HReplyPacket.getSQLExceptionChain(HReplyPacket.java:157)
> at com.sap.db.jdbc.packet.HPartInfo.getSQLExceptionChain(HPartInfo.java:39)
> at com.sap.db.jdbc.ConnectionSapDB._receive(ConnectionSapDB.java:3437)
> at com.sap.db.jdbc.ConnectionSapDB.exchange(ConnectionSapDB.java:1575)
> at com.sap.db.jdbc.PreparedStatementSapDB._prepare(PreparedStatementSapDB.java:2816)
> ...
> at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
> at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:480)
> at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAPage$JPAPageBuilder.buildFromQuery(JPAPage.java:117)
> at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAPage$JPAPageBuilder.build(JPAPage.java:95)
> at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAProcessorImpl.handlePaging(JPAProcessorImpl.java:443)
> at org.apache.olingo.odata2.jpa.processor.core.access.data.JPAProcessorImpl.process(JPAProcessorImpl.java:165)
> ...
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message