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!

 

0
9 comments

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

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

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

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

0

It wasn’t helpful.

DataGrip still doesn’t accept my CTE

It thinks that the end of my CTE is the end of all query.

 

0
Osomik, can you provide a sample of your query, including your SQL dialect and the settings you have in File | Settings | Database | Query Execution
0

settings: 

Dialect: IBM Db2 LUW

Query looks like:

 

when the query is short it works as expected, but when it long (for ex. more then 100 rows) the DataGrip doesn't understand it.

Also want to mention, tha in anothe ide (DBeaver) the same qury works well, without any questions

0

I've tried to change settings, but it doesn't help

0
May I ask you to include the complete query example causing problems either by embedding it to your message or uploading it onto our FTP https://uploads.jetbrains.com/ (include the upload id)
0

Please sign in to leave a comment.