As an Appsmith user, I encountered an issue with using CTEs in a query with a MySQL data source. It simply returned affectedRows: 0. It appears to be a known bug and there is currently no fix for it. However, I was provided a workaround using an alternative query that provides the same result.
The reported issue is that using Common Table Expressions (CTEs) in a MySQL data source query in Appsmith is not working and produces a zero result. A bug has been reported and is being tracked on GitHub. As a workaround, you can use a subquery to achieve the same result.
Here is an example of the alternative syntax in MySQL:
SELECT * FROM (
SELECT * FROM employees WHERE id = 2
) AS cte_name;
This query selects all columns from the employees table where the id is equal to 2. The subquery is given an alias "cte_name" and the outer query selects all columns from it. This provides the same functionality as a CTE query.