[OLINGO-1312] Olingo 2.0.x JPA generating unexecutable SQL for HANA in presence of NCLOB columns

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: V2 2.0.10
• Component/s: odata2-jpa
• Assignee: Unassigned
• Reporter: Patrick Haller
• Created: 24/Oct/18 14:42
• Updated: 24/Oct/18 16:31

Description

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

GET http://myservice/api/odata/Childs

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

Details

We have entities defined like

public class Parent
{
     @Id
     private String uuid;

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

and

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;
}

 

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

OlingoJPAProcessor generates

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 ?

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

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)
...