CTE not working as expected

Hi,

I have seen a few of the other CTE related posts but the seem to be for much older versions and none seemed to be fully answered or what i'm experiencing. I am using datagrip w/ a MYSQL db (8.0+) and I have my SQL dialect set to MYSQL. This normally works in other IDE's where I have the same set up so i'm not sure if it's something specific to datagrip or something i'm doing incorrectly.

Here's a picture with the sensitive info taken out and replaced and the error.

 

Not sure if this is something obvious i'm missing or just something lacking in support from the datagrip side. Feels like the first, but any help would be appreciated. Thanks!

 

4 comments
Comment actions Permalink

maybe you need to add semicolon before with statement?

;with stuff_users as (
select id
from user
where email like '%@work_email.com%'
)
select count(id) from stuff_users

 

0
Comment actions Permalink

What if you open a new query console (right-click on database name -> new -> query console) and write a query from scratch? Just checked in latest version, it works fine with CTE in MySQL.

0
Comment actions Permalink

Thank you both for your suggestions! Neither of these worked though unfortunately. :/ I've also tried uninstalling, re-downloading, and connecting to the db again just in case which didn't help. Not sure what it could be if my CTE's work fine in other IDE's and it's also working in datagrip for others, but perhaps an issue with setup.

 

0
Comment actions Permalink

let's try this - File -> Manage IDE settings -> Restore default settings, create data source from scratch and check again.

0

Please sign in to leave a comment.