[Bug]: MySQL CTEs queries failed.

[felix-appsmith]

Is there an existing issue for this?

• [X] I have searched the existing issues

Description

Currently, CTE queries do not work in Appsmith here I show an example:

I have this CTE query:

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

and it gives me this output:

affectedRows
1

The output you should receive is this:

name	salary
felix	4,500

Workaround

currently there is this workaround for this problem:

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

Steps To Reproduce

1. Make sure your database is higher than version 8.0 or version 8.0 because CTE is not available in earlier versions.

2. Create a simple table with some data.

3. create your CTE query you can use this as a template.

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

4. Run your query and see the wrong result .

Public Sample App

No response

Issue video log

No response

Version

Cloud Appsmith v1.9.3.2-SNAPSHOT

[sumitsum]

Removing the critical lable because:

• There is a simple work around mentioned in the description.
• This is a not something that used to work before and has broken now. AFAIK, CTE were not supported before as well i.e this does not seem like something that broke due to the recent release.

[phantom943]

It's too bad that such an old and helpful feature as CTEs is not supported here (being treated not as a select query, but rather as an update query). Upvoting this.

[GreenFlux]

@sumitsum @rohan-arthur another user is requesting support for CTE queries. https://discord.com/channels/725602949748752515/1201470680735166527

[GreenFlux]

+1 for CTE support. I just ran into this while helping a customer with their PoC. It's not a blocker because of the workaround mentioned above, the but query is much easier to read and edit as a CTE when there are multiple subqueries. cc @rohan-arthur