Common Table Expressions not working for MySQL data source

Question and Answers Data Source
1

Hi,

When I use a CTE in a query with a MySQL data source. It doesn’t work.
Just returns

affectedRows: 0

Screenshot 2023-01-20 at 9.53.47 AM
Screenshot 2023-01-20 at 9.53.47 AM689×625 26.4 KB

1 Like
2

Hello! Are you on cloud or self-hosted? If you are self-hosting Appsmith, what version are you using?

3

Hello, unfortunately the MySQL CTEs queries are failing and this was reported as a bug, you can track it at the following link:[Bug]: MySQL CTEs queries failed. · Issue #19959 · appsmithorg/appsmith · GitHub
As a workaround for this problem you can use this alternative to the CTEs, it provides the same result.

SELECT * FROM (
  SELECT * FROM employees WHERE id = 2
) AS cte_name;