SuiteQL query is failing in sandbox but succeeding in production
Two days ago, all of our SuiteQL queries that used the pagination technique described by Tim Dietrich on his blog started failing in our sandbox environments. However, those exact queries still work fine in our production environment. The error we get has no context unlike other errors we see with malformed SuiteQL where it gives us a hint as to what’s wrong. We then tried to simplify the query to reproduce the error and got down to this for illustration:
WITH CteCustomer AS (SELECT c.id AS customer_id FROM customer c WHERE c.id = 'XXXXXXXX'), CteInvoiceKeys AS (SELECT trx.id AS transaction_id FROM TRANSACTION trx INNER JOIN CteCustomer ctec ON trx.entity = ctec.customer_id) SELECT CteCustomer.customer_id, CteInvoiceKeys.transaction_id FROM CteCustomer INNER JOIN CteInvoiceKeys ON CteCustomer.customer_id = CteInvoiceKeys.entity;
We find that if we ever join to a CTE from another CTE and in the final query, it now fails in sandbox but still works in production. Is anyone else having this issue?
Our current guess is that Oracle experiments with potentially future builds in their sandboxes before they roll out changes in production and there is some low level change in their query engine that’s now causing this error.
We have the same problem in production. Did you solve it?
