Index Issues

[kpenningroth-0504]

I noticed that when running a query such as this, the mcp server fails: "Can you give me the number of documents where the city is equal to austin for the customers collection?"

The query params are:
"bucket_name": "mcp_demo",
"scope_name": "inventory",
"query": "SELECT COUNT(*) AS count FROM mcp_demo.inventory.customers WHERE city = 'Austin'"

But then it says there is no index for it, then it creates a primary and secondary index, but it never succeeds.

When the data is in an analytics collection, in an analytics namespace, it functions fine.

[nithishr]

Hi @kpenningroth-0504,
The issue is related to how the query is generated.
The MCP server runs the queries in scoped mode (scope.query() rather than cluster.query())
In this case, if the query is rewritten as follows, it should work
SELECT COUNT(*) AS count FROM customers WHERE city = 'Austin'"

How was this query generated?
Was it by an LLM? If so, do you remember which one & the client you were using for it?

[kpenningroth-0504]

Correct that it was an LLM @nithishr, I was using the mcp server with Cursor. I believe I was using Cursor chat's auto model feature to select the fastest one. So not sure which model. But is it possible to put a validation for any SQL++ queries that we are executing them in scoped mode, not cluster? I notice there is a function for cluster scoped queries as well in the tools.

[nithishr]

I have not seen this scenario occurring frequently. At times it happens, but the LLM quickly realizes that it is a scoped query & makes the changes.
I have created a PR #82 that should improve things by providing explicit examples to the LLM in the tool description.
We validate the queries to ensure they are syntactically correct using a parser, but this does not verify if the query is scoped correctly.
We have a cluster query mode as well, but this is mostly used for tools (system collections or metrics) internally where required. If you require this feature, we could expose it as a tool. It is useful only in a limited set of use cases where you want to access data from multiple buckets.