$expand fails with MySQL

[HansLoibner]

Hello,

we are using:
<processor.version>2.1.0-SNAPSHOT</processor.version>
<java.version>17</java.version>
<eclipselink.version>4.0.2</eclipselink.version>
<jpa.version>3.1.0</jpa.version>

An $expand request: http://localhost:9010/OERP/SalesQuotationTables?$expand=CustTableEntity
results in the response:

{
    "error": {
        "code": null,
        "message": "Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException\r\nInternal Exception: java.sql.SQLSyntaxErrorException: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'\r\nError Code: 1235\r\nCall: SELECT DISTINCT E1.modifiedDateTime S0, E1.thirdPartyCommission S1, E1.custAccount S2, E1.invoiceAccount S3, E1.custName S4, E1.deliveryAddressRecId S5, E1.contactEmail S6, E1.taxGroup S7, E1.contactPerson S8, E1.dlvTerm S9, E1.paymentTerm S10, E1.dataAreaId S11, E1.modifiedBy S12, E1.vatNum S13, E1.salesGroup S14, E1.languageId S15, E1.recId S16, E1.currencyCode S17, E1.dlvMode S18, E1.profitPercent S19, E1.createdBy S20, E1.createdDateTime S21 FROM CustTable E1 WHERE (E1.custAccount) IN (SELECT E0.custAccount S0 FROM SalesQuotationTable E0 LIMIT 2147483647 OFFSET 0) ORDER BY E1.custAccount ASC\r\nQuery: DataReadQuery(sql=\"SELECT DISTINCT E1.modifiedDateTime S0, E1.thirdPartyCommission S1, E1.custAccount S2, E1.invoiceAccount S3, E1.custName S4, E1.deliveryAddressRecId S5, E1.contactEmail S6, E1.taxGroup S7, E1.contactPerson S8, E1.dlvTerm S9, E1.paymentTerm S10, E1.dataAreaId S11, E1.modifiedBy S12, E1.vatNum S13, E1.salesGroup S14, E1.languageId S15, E1.recId S16, E1.currencyCode S17, E1.dlvMode S18, E1.profitPercent S19, E1.createdBy S20, E1.createdDateTime S21 FROM CustTable E1 WHERE (E1.custAccount) IN (SELECT E0.custAccount S0 FROM SalesQuotationTable E0 LIMIT 2147483647 OFFSET 0) ORDER BY E1.custAccount ASC\")"
    }
}

Looking in the MySQL query:
SELECT DISTINCT E1.modifiedDateTime S0, E1.thirdPartyCommission S1, E1.custAccount S2, E1.invoiceAccount S3, E1.custName S4, E1.deliveryAddressRecId S5, E1.contactEmail S6, E1.taxGroup S7, E1.contactPerson S8, E1.dlvTerm S9, E1.paymentTerm S10, E1.dataAreaId S11, E1.modifiedBy S12, E1.vatNum S13, E1.salesGroup S14, E1.languageId S15, E1.recId S16, E1.currencyCode S17, E1.dlvMode S18, E1.profitPercent S19, E1.createdBy S20, E1.createdDateTime S21 FROM CustTable E1 WHERE (E1.custAccount) IN ( SELECT E0.custAccount S0 FROM SalesQuotationTable E0 LIMIT 2147483647 OFFSET 0 ) ORDER BY E1.custAccount ASC

You can see that there is a subquery which uses LIMIT and OFFSET. This is not valid, it is a known "bug" in MySQL.

Any idea to work around this? Are we doing something wrong?

Thanks, Hans

[HansLoibner]

Related code snippets:

@Entity(name = "SalesQuotationTable") @Table(name = "SalesQuotationTable") public class SalesQuotationTableEntity { @Column(name = "custAccount", length = 10) private String custAccount; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "custAccount", referencedColumnName = "custAccount", insertable = false, updatable = false) private CustTableEntity custTableEntity; }

@Entity(name = "CustTable") @Table(name = "CustTable") public class CustTableEntity { @Column(name = "custAccount", length = 10) private String custAccount; }

[wog48]

First things first: The LIMIT and OFFSET is only generated in case module odata-jpa-processor-cb is used. The JPA processor works fine also without it. Please remove it form your pom.xml.

The basic idea using sub-queries for expands is to avoid a generation of large select statement. This would be a risk using the keys from the result of the super originated query. As JPA does not support LIMIT and OFFSET for sub-queries, an own criteria builder was written, which generates parameterized native queries. In case this is accessible, it is used. By removing odata-jpa-processor-cb from the pom, it is no longer in the class pass.

I keep the issue open as a anchor to thing about a solution with odata-jpa-processor-cb

[wog48]

The latest changes in version 2.2.0 should solve the problem with OFFSET and LIMIT. Please be aware that other like OData indexof, which is converted by default into SQL locate, and OData substring, by default converted into SQL substring, may cause issues, too. In case you need them, an implementation of ProcessorSqlPatternProvider needs to be provided via the session context. You can use JPADerbySqlPatternProvider or JPAPostgresqlSqlPatternProvider as an example.