Error with Redshift CTEs, comments with an apostrophe and field filters

Bug reports
1

I ran into a really strange error that only occurs with the combination of (at least as far as I was able to narrow it down):

  • Redshift (I was not able to replicate the error with an otherwise identical query against a postgres database)
  • a field filter in a CTE where any comment with an apostrophe proceeds the field filter
  • Something must be selected in the field filter
  • followed by at least one additional CTE

Here’s a repro case: (note: {{category}} is a field filter variable mapping to a table column set to category)

WITH cte as (
select 'hi' from events_staging
 -- this is my error's case
where {{ category }}
)

, cte_two as (select 'hi')

select * from cte

Things that make it run ok:

  • Remove the apostrophe in the comment
  • Escape the apostrophe in the comment with \
  • Delete the comment
  • Delete cte_two
  • Deselect the {{category}} variable
  • Move the comment anywhere after {{category}}

The only ‘interesting’ thing I saw that did not fix the query was just commenting on the line with cte_two.

2

Hi @TheAlex
Nice details. I think you’re seeing some combination of these issues:
https://github.com/metabase/metabase/issues/11755
https://github.com/metabase/metabase/issues/2653
If you don’t think it fits into one of those issues, then please open a new issue on it.

3

Thanks @flamber - I don’t think it’s the same with a quick scan of either of those issues, I’ll log an issue after some more investigation. Also, sorry I didn’t include the actual error message, it’s from redshift:

[{:status :failed,
:class java.sql.SQLException,
:error "[Amazon](500310) Invalid operation: syntax error at or near \")\" \nPosition: 406;",
...

Is there a way in metabase to see the queries that are actually executed against the database after the variables are interpolated or the query is prepared?

https://github.com/metabase/metabase/pull/8674 (stripping comments) ought to fix this specific issue.

4

@TheAlex Look in your browser developer Network-tab, when you run the query, then you’ll see a request and you can look in the response to see the query. I tend to prefer looking on the database log, since the query Metabase creates is send to the driver and that might change something before sending it to the database.

Correct, removing the comments before sending the query would fix it, but it’s difficult to parse and remove text from every type of syntax without accidentally breaking something, so I think that’s why the PR hasn’t been merged yet.