note: this is fine-isssh in test, and CheckQuerResults doesn't really allow you to do this. But in general we really try to avoid trivially concatenating SQL query string, this is a recipe for SQL injections which is really really bad. We usually use placeholder here for them.

nit: let's be a bit more specific on the test name, this tests is really testing execution of the builtin when it's being set up on a remote node via DistSQL. So i'm thinking something along the line of TestResetIndexUsageStatsOnRemoteSQLNode

Let's scale this down to 3 nodes? 9 nodes feels quite wasteful for a test like this

also this tests case is slightly overkill 😛 , all you need is a query that causes a filter to be pushed down into remote node, so let's see if we can simplify this query a bit ?

Hmm I've removed some columns, although this test case won't fail if I don't have data inserted into the tables. Or are there any existing test or internal tables I can use that are already populated?

Interesting, turned out it was a bit hard to do this if there's very little data 😅

Here's a reliable reprod

CREATE TABLE t (k INT);
INSERT INTO t SELECT generate_series(1, 30);

--- The following ALTER TABLE forces the range to locate on different nodes, which forces the optmizer to plan
---  the DistSQL operation that executes builtins on the remote node
ALTER TABLE t SPLIT AT VALUES (10), (20);
ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 1, 1;
ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 2, 15;
ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 3, 25;

--- the query that will crash because of the bug
SELECT count(*) FROM t WHERE crdb_internal.reset_index_usage_stats();

This results in a query plan looking like this

and reliably crashes:

root@127.0.0.1:26257/movr> SELECT count(*) FROM t WHERE crdb_internal.reset_index_usage_stats();
ERROR: internal error: crdb_internal.reset_index_usage_stats(): index usage stats controller not set
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/sem/builtins/builtins.go:6070: func306()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/eval.go:4221: Eval()
github.com/cockroachdb/cockroach/pkg/sql/execinfrapb/expr.go:202: RunFilter()
github.com/cockroachdb/cockroach/pkg/sql/execinfrapb/expr.go:191: EvalFilter()
github.com/cockroachdb/cockroach/pkg/sql/rowexec/filterer.go:95: Next()
github.com/cockroachdb/cockroach/pkg/sql/colexec/columnarizer.go:222: Next()
github.com/cockroachdb/cockroach/pkg/sql/colexec/count.go:51: Next()
github.com/cockroachdb/cockroach/pkg/sql/colexec/colexecutils/deselector.go:63: Next()
github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/outbox.go:285: func1()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:91: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/outbox.go:276: sendBatches()
github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/outbox.go:410: runWithStream()
github.com/cockroachdb/cockroach/pkg/sql/colflow/colrpc/outbox.go:216: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:737: func1()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:1331: 1()
runtime/asm_amd64.s:1371: goexit()

This assumes we have a 3-node cluster

Ah I see, thanks!

Dd you mean that I replace my query with this one, or was this just an answer to my question? I'm not sure if it's actually any simpler 🤔

Let's replace it with the new query, because I realized the one we have right now is not a reliable reprod, it depends on the amount of data in the table and table statistics used by the optimizer. (i.e. sometimes when I run this query it won't fail)

Hmm, when I replace the query, the test case doesn't fail for me even when the index usage stats controller isn't being set.

Also, when I try running this on the cli, I get the following error:

ERROR: unimplemented: operation is unsupported in multi-tenancy mode SQLSTATE: 0A000 HINT: You have attempted to use a feature that is not yet implemented. See: https://go.crdb.dev/issue-v/54254/v22.1

Hmm interesting, seems like the new master changed a few things ... let me check

Found the problem:

CREATE TABLE t (k INT PRIMARY KEY); -- <- `k` here should be the primary key
INSERT INTO t SELECT generate_series(1, 30);

ALTER TABLE t SPLIT AT VALUES (10), (20);
ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 1, 1;
ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 2, 15;
ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 3, 25;

SELECT count(*) FROM t WHERE crdb_internal.reset_index_usage_stats();

Actually this was caused by the fact that I didn't make the k a primary key, so the range split was effectively no-op.