[ 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} 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 {{GET [http://myservice/api/odata/Childs]}} h2. 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)}} {{...}} > 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} > 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)