Hi everyone, I was wondering if perhaps this group could help. 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;

What failures? “ERROR: UNEXPECTED_ERROR, null” by chance? We're suddenly getting this in production on a query with a whole lot of CTEs.

Yeah, we just started getting these in production as well as of like 20 minutes ago. Last week, we started seeing the same queries error in their sandboxes

I have so many queries with CTEs, I don't think it's even possible to rewrite the most complex ones any other way

We submitted a ticket to them, but haven't heard back

We don't either...

This has to be affecting enough people who do have support that they are hearing it from their customers pretty aggressively. We're working on rushing out a patch fix right now.

The issue is not just with CTE's, I'm also getting UNEXPECTED_ERROR with trivial queries, e.g.

SELECT DISTINCT lastmodifieddate FROM currencyrate

The currencyrate query works for me, knock on wood

The currencyrate query works for me via the SuiteQL Query Tool, but not with the SuiteQL REST API

We had an issue last week in the sandbox where we'd get outdated data if we did the REST API but accurate data in the in the query tool, but it resolved mysteriously the next day

@Riaz Virani any recollection what feature you chose that this relates to, when you submitted your ticket?

I didn't directly submit the ticket. We had to give the information to someone on the business side who opened the ticket. We advised them that the issue was related to SuiteQL if that's an option to select

sadly not, I found one that was Database Error tho 🤷‍♀️ I mean, technically that is what it told me

Yeah, normally an error we get has some description. We get nothing from this whether we use the SuiteQL tool or hit it directly via the REST API

I am also seeing it across multiple clients. Can you please let me know if you get a defect ID. I plan to open cases as well.

@Michelle Beutler Just got this. We have filed the following defect on your behalf: Defect 765188 : SuiteScripts > SuiteQL > Common Table Expressions (CTEs) > Unexpected Errors The urgency level assigned to your defect is U2. We understand this is a critical defect.

Thank you!!

Has anyone seen any updates on the defect? currently rather crippling when its effecting basic querys

They thanked me for my patience while they investigate 🙃 Which I take to mean "We're not really sure what we broke yet"

I did speak to support, they decided my issue was completely unrelated and opened it as a U3, even though its clearly the same issue