Category: Data Source
Updated

This solution is summarized from an archived support forum post. This information may have changed. If you notice an error, please let us know in Discord.

Common Table Expressions not working for MySQL data source

Issue

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.

Resolution

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.