Persistent 400 error on spectacles sql test

[hanakogallagher]

I've been seeing the same error on all of my LookML PRs:

Couldn't retrieve compiled SQL. Failed to retrieve compiled SQL for explore 'analytics_2/analytics_monthly_bk_efficiency_metrics' with query '653705'. Please try again. Received the following from Looker: "The filter "account_group" is not allowed."

Run in verbose mode (-v) or check your log file to see the full response from the Looker API. For support, please create an issue at https://github.com/spectacles-ci/spectacles/issues

Spectacles received a 400 response code from the Looker API with the following details:
   {
  "message": "The filter \"account_group\" is not allowed.",
  "documentation_url":
   "https://cloud.google.com/looker/docs/r/err/4.0/400/get/queries/:query_id/run/:result
   _format"
}
Error: Process completed with exit code 101.

My team is running Looker version 24.0.47 and Spectacles version 2.4.6.

I reached out to Looker support but they haven't been able to help me troubleshoot. Would appreciate any suggestions for how to resolve this error!

[DylanBaker]

Hey @hanakogallagher.

Thanks for flagging this. This is a slightly odd error that seems to be caused by changes to version 24.0 of Looker. We will likely make some changes in the future to handle this more gracefully but are currently waiting on more info about the long term behaviour.

To fix this error, you'll need to update the filter in the explore to account^_group. This is because underscores technically need to be escaped in filters (though Looker doesn't advertise this enough and for some reason only raises the error via the API.) It is similar to the issue raised in this post.

I think that change should resolve the error, but let me know if that's not the case.

[hanakogallagher]

Thanks @DylanBaker ! I tried this, but I got a new error message "The filter "account^_group" is not allowed." lol

For more context, the account_type parameter is defined in the view file like this:

  parameter: account_type {
    type: unquoted
    default_value: "account_group"
    allowed_value: {
      label: "Account Group"
      value: "account_group"
    }
    allowed_value: {
      label: "Account"
      value: "account"
    }
  }

And then filtered in the model file like this:

always_filter: {
    filters: [fct_monthly_bk_efficiency_metrics.account_type: "account_group"]
  }

I tried adding the ^ character for each instance of account_group but wasn't able to resolve the spectacles error. Let me know if you have any thoughts about what to try next.

[DylanBaker]

That's really odd. This may be one we need to ask you to take up with Looker support, but hopefully we can give you a reliable reproduction which should point you in the right direction.

In the API explorer in your Looker instance, could you please run the "Run Query" endpoint with query ID 653705 and response format of sql please? This should hopefully reproduce the error.

[hanakogallagher]

Yes, that produced the same error:

{
  "message": "{\"message\":\"The filter \\\"account_group\\\" is not allowed.\",\"documentation_url\":\"https://cloud.google.com/looker/docs/r/err/4.0/400/get/queries/:query_id/run/:result_format\"}",
  "documentation_url": "https://cloud.google.com/looker/docs/r/err/internal/400/get/core/:version/*"
}

I have an open ticket so I'll pass it along to Looker support.

[hanakogallagher]

Sharing the latest response from Looker support:

I told them not to worry about rolling back our Looker version for now. Let me know if you have any updates relating to the error or any other suggestions!

[DylanBaker]

Hi Hanako. Not to pass the buck back to Looker, but I don't really agree with their response. If they are returning an error via their API that you are not able to resolve in your codebase, that is something they should be able to answer and address. I'm keen to help as much as possible, but this is basically an unexplained error being returned via the API (given the one solution we have didn't work) and they should be able to explain what is going on.

Would you be able to share the ticket ID from Looker? We can likely escalate this on our side with them. Thanks!

[hanakogallagher]

Yes I believe my support ticket is #603056! I originally submitted it on 2/27/24.

[hanakogallagher]

Hey @DylanBaker ! Any updates on this error? We are still seeing the same message on all new PRs.

[DylanBaker]

Hey @hanakogallagher. No immediate updates on our side unfortunately, though we are exploring changing Spectacles to use the json_bi response format for queries, which might well resolve this issue. This should be in the next month or two. Was there any further response from Looker?

[hanakogallagher]

Got it, thanks for the update! I haven't heard anything else from Looker support. :/