When we try to build a filter off of AQL text dimensions from the UI, it breaks when attempting to pull different value options in the dropdown. It seems like it’s failing to convert the AQL to SQL (see below).
Pulling the same dimension into a table works just fine. The equivalent SQL dimension works fine for filtering, but we’ve got a situation where we need to cross reference a field in another model, so building a SQL dimension is not a workaround in this case. Inputing the value in manually does work so I believe we have a workaround.
Screenshot attached plus the error text. I’ve also got a very simple test case built out that’s not dependent on our data at all if it would be helpful to share.
Cannot fetch suggestions (Exec SQL failed: [{"location"=>"query", "message"=>"Syntax error: Unexpected keyword WHEN at [19:5]", "reason"=>"invalidQuery"}] SQL query: #standardSQL #standardsql WITH `test` AS ( #standardsql SELECT 1 as ID, 'abc-123' as val UNION ALL SELECT 2 as ID, 'abc-456' as val UNION ALL SELECT 3 as ID, 'xyz-123' as val ) SELECT DISTINCT case( when: test.val like '%abc%', then: 'ABC', when: test.val like '%xyz%', then: 'XYZ', else: null ) AS `t_ga_04bdd4` FROM `test` WHERE case( when: test.val like '%abc%', then: 'ABC', when: test.val like '%xyz%', then: 'XYZ', else: null ) IS NOT NULL LIMIT 100000 ). You can still enter values manually.
Thank you for bringing this to our attention. We’ve confirmed that the issue you’re experiencing with filter value suggestions occurs specifically in the Dev environment (Modeling 4.0).
Our team is working on a fix, and we’ll keep you updated on our progress. Thank you for your patience.
I believe we have come across a leftover issue related to this resolved bug. In development mode, when working with an aql dimension (cross-model reference) as a filter, a warning message is shown, stating "Failed to fetch field suggestions
AQL compilation Failed["Can not resolve model for the reference . “]”
The warning message is shown when a filter (AQL dimension) is added to a visualization, or when a visualization leveraging an AQL dimension filter is opened for editing.
This message is repeated multiple times in cases where multiple AQL dimension filters are used, and where multiple cross-model fields are used in a single AQL dimension (used as a filter). The number of filtered cross-model references matches the number of warning messages.
Clicking on the filters correctly queries the data model and returns the suggested values in the drop-down as expected.
Thanks for bringing the issue to our attention with such a detailed description.
Could you let us know how the AQL dimensions were created (e.g. in model aml file, in visualization as ad-hoc fields,…)? Also, please provide the link to the page where these warnings appear. It would be helpful for us to identify the root cause.
I’m looking forward to hearing from you soon. Thanks.
