MySQL - cannot use EXCEPT with CTE's

I am trying to run a query from DataGrip that involves two 8.0 features, namely common table expressions and the EXCEPT keyword. I am running It is very important to note - this exact query can be successfully executed from both a CLI and Python. I get the same result when using the “AWS Driver for Aurora MySQL” as well as the “MySQL” driver.

Here is the query:

WITH abc(id) AS (SELECT abc.id FROM schema_name.abc),
     xyz(id) AS (SELECT xyz.id FROM schema_name.xyz)
SELECT abc.id
FROM abc
EXCEPT
SELECT xyz.id
FROM xyz;

The error returned is:

[2024-09-11 09:26:49] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8.

I'm very comfortable with addressing syntax issues; however, this problem doesn't appear to be related to syntax again pointing to the success from other database clients. This issue appears isolated to DataGrip, even when utilizing the newest version from JetBrains.
 

0

Hi Spencer,

Please follow the progress in https://youtrack.jetbrains.com/issue/DBE-21803 (has a workaround).

0

Thanks Arina, unfortunately the workaround stated in DBE-21803 is not sufficient to run the query as is. 

Though I did find a workaround. When I looked at the “Output”, it shows that it's not grabbing the full query. 

However, if we place the end of the query on the same line as the except - it will run the full query. 

The technique you mentioned is necessary in order for DataGrip to execute the query, but not sufficient for it to recognize that the EXCEPT is not the end of the query.

0
Hi Spenser,

This issue is caused by the incorrect handling of EXCEPT statement. Regardless of the settings in File | Settings | Database | Query Execution, it won't identify the block correctly. The same goes for the statement highlighting. We have therefore reported this issue so our dev team can take care of it and implement a fix.
0

请先登录再写评论。