Summary: When entering a query that has a relatively complex LET, the index recommendation is malformed.
- In query workbench, enter a query like below
- Click "Advice"
- Observe the index recommendation
Index recommendation is:
(I attached as screenshot and a text file since Jira keeps mangling the formatting because of the backticks)
When trying to execute that recommendation, there is a syntax error.
No syntax error, index executes correctly. It appears there are multiple issues with this index, including multiple names separated by commas, and some parenthesis misplacement (at least).
select p.file.fullName, score
from familyPhotos p
let score = IFMISSING(p.ranking.scores.emma,0) + IFMISSING(p.ranking.scores.matt,0) + IFMISSING(p.ranking.scores.ali,0) + IFMISSING(p.ranking.scores.matthew,0)
where score > 0
order by score asc, p.file.fullName asc LIMIT 50;