fix(tools/bigquery-execute-sql): ensure invoke always returns a non-null value #925

Genesis929 · 2025-07-17T22:12:29Z

Added a dry run step to identify the query type (e.g., SELECT, DML), which allows the tool to correctly handle the query's output.
The recommended high-level client, cloud.google.com/go/bigquery, does not expose the statement type from a dry run. To circumvent this limitation, the low-level BigQuery REST API client (google.golang.org/api/bigquery/v2) was added to gain access to these necessary details.

fixes: #915

Yuan325

Thank you! Some nit suggestions. The issuer also mentioned that SELECT * from empty table returns error message as well? Do we want to add a check for nil before returning results from SELECT statement?

internal/tools/bigquery/bigqueryexecutesql/bigqueryexecutesql.go

tests/bigquery/bigquery_integration_test.go

Genesis929 · 2025-07-18T00:32:23Z

Thank you! Some nit suggestions. The issuer also mentioned that SELECT * from empty table returns error message as well? Do we want to add a check for nil before returning results from SELECT statement?

A checker added.

Yuan325

Quick nit, otherwise LGTM, thank you again!

internal/tools/bigquery/bigqueryexecutesql/bigqueryexecutesql.go

…ccess.

Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>

Updated dry run in execute sql to also include a location, fix the potential issue in PR #925.

🤖 I have created a release *beep* *boop* --- ## [0.10.0](v0.9.0...v0.10.0) (2025-07-25) ### Features * Add `Map` parameters support ([#928](#928)) ([4468bc9](4468bc9)) * Add Dataplex source and tool ([#847](#847)) ([30c16a5](30c16a5)) * Add Looker source and tool ([#923](#923)) ([c67e01b](c67e01b)) * Add support for null optional parameter ([#802](#802)) ([a817b12](a817b12)), closes [#736](#736) * **prebuilt/alloydb-admin-config:** Add alloydb control plane as a prebuilt config ([#937](#937)) ([0b28b72](0b28b72)) * **prebuilt/mysql,prebuilt/mssql:** Add generic mysql and mssql prebuilt tools ([#983](#983)) ([c600c30](c600c30)) * **server/mcp:** Support MCP version 2025-06-18 ([#898](#898)) ([313d3ca](313d3ca)) * **sources/mssql:** Add support for encrypt connection parameter ([#874](#874)) ([14a868f](14a868f)) * **sources/firestore:** Add Firestore as Source ([#786](#786)) ([2bb790e](2bb790e)) * **sources/mongodb:** Add MongoDB Source ([#969](#969)) ([74dbd61](74dbd61)) * **tools/alloydb-wait-for-operation:** Add wait for operation tool with exponential backoff ([#920](#920)) ([3f6ec29](3f6ec29)) * **tools/mongodb-aggregate:** Add MongoDB `aggregate` Tools ([#977](#977)) ([bd399bb](bd399bb)) * **tools/mongodb-delete:** Add MongoDB `delete` Tools ([#974](#974)) ([78e9752](78e9752)) * **tools/mongodb-find:** Add MongoDB `find` Tools ([#970](#970)) ([a747475](a747475)) * **tools/mongodb-insert:** Add MongoDB `insert` Tools ([#975](#975)) ([4c63f0c](4c63f0c)) * **tools/mongodb-update:** Add MongoDB `update` Tools ([#972](#972)) ([dfde52c](dfde52c)) * **tools/neo4j-execute-cypher:** Add neo4j-execute-cypher for Neo4j sources ([#946](#946)) ([81d0505](81d0505)) * **tools/neo4j-schema:** Add neo4j-schema tool ([#978](#978)) ([be7db3d](be7db3d)) * **tools/wait:** Create wait for tool ([#885](#885)) ([ed5ef4c](ed5ef4c)) ### Bug Fixes * Fix document preview pipeline for forked PRs ([#950](#950)) ([481cc60](481cc60)) * **prebuilt/firestore:** Mark database field as required in the firestore prebuilt tools ([#959](#959)) ([15417d4](15417d4)) * **prebuilt/cloud-sql-mssql:** Correct source reference for execute_sql tool in cloud-sql-mssql.yaml prebuilt config ([#938](#938)) ([d16728e](d16728e)) * **prebuilt/cloud-sql-mysql:** Update list_table tool ([#924](#924)) ([2083ba5](2083ba5)) * Replace 'float' with 'number' in McpManifest ([#985](#985)) ([59e23e1](59e23e1)) * **server/api:** Add logger to context in tool invoke handler ([#891](#891)) ([8ce311f](8ce311f)) * **sources/looker:** Add agent tag to Looker API calls. ([#966](#966)) ([f55dd6f](f55dd6f)) * **tools/bigquery-execute-sql:** Ensure invoke always returns a non-null value ([#925](#925)) ([9a55b80](9a55b80)) * **tools/mysqlsql:** Unmarshal json data from database during invoke ([#979](#979)) ([ccc3498](ccc3498)), closes [#840](#840) --- This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please). --------- Co-authored-by: release-please[bot] <55107282+release-please[bot]@users.noreply.github.com> Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>

🤖 I have created a release *beep* *boop* --- ## [0.10.0](v0.9.0...v0.10.0) (2025-07-25) ### Features * Add `Map` parameters support ([#928](#928)) ([4468bc9](4468bc9)) * Add Dataplex source and tool ([#847](#847)) ([30c16a5](30c16a5)) * Add Looker source and tool ([#923](#923)) ([c67e01b](c67e01b)) * Add support for null optional parameter ([#802](#802)) ([a817b12](a817b12)), closes [#736](#736) * **prebuilt/alloydb-admin-config:** Add alloydb control plane as a prebuilt config ([#937](#937)) ([0b28b72](0b28b72)) * **prebuilt/mysql,prebuilt/mssql:** Add generic mysql and mssql prebuilt tools ([#983](#983)) ([c600c30](c600c30)) * **server/mcp:** Support MCP version 2025-06-18 ([#898](#898)) ([313d3ca](313d3ca)) * **sources/mssql:** Add support for encrypt connection parameter ([#874](#874)) ([14a868f](14a868f)) * **sources/firestore:** Add Firestore as Source ([#786](#786)) ([2bb790e](2bb790e)) * **sources/mongodb:** Add MongoDB Source ([#969](#969)) ([74dbd61](74dbd61)) * **tools/alloydb-wait-for-operation:** Add wait for operation tool with exponential backoff ([#920](#920)) ([3f6ec29](3f6ec29)) * **tools/mongodb-aggregate:** Add MongoDB `aggregate` Tools ([#977](#977)) ([bd399bb](bd399bb)) * **tools/mongodb-delete:** Add MongoDB `delete` Tools ([#974](#974)) ([78e9752](78e9752)) * **tools/mongodb-find:** Add MongoDB `find` Tools ([#970](#970)) ([a747475](a747475)) * **tools/mongodb-insert:** Add MongoDB `insert` Tools ([#975](#975)) ([4c63f0c](4c63f0c)) * **tools/mongodb-update:** Add MongoDB `update` Tools ([#972](#972)) ([dfde52c](dfde52c)) * **tools/neo4j-execute-cypher:** Add neo4j-execute-cypher for Neo4j sources ([#946](#946)) ([81d0505](81d0505)) * **tools/neo4j-schema:** Add neo4j-schema tool ([#978](#978)) ([be7db3d](be7db3d)) * **tools/wait:** Create wait for tool ([#885](#885)) ([ed5ef4c](ed5ef4c)) ### Bug Fixes * Fix document preview pipeline for forked PRs ([#950](#950)) ([481cc60](481cc60)) * **prebuilt/firestore:** Mark database field as required in the firestore prebuilt tools ([#959](#959)) ([15417d4](15417d4)) * **prebuilt/cloud-sql-mssql:** Correct source reference for execute_sql tool in cloud-sql-mssql.yaml prebuilt config ([#938](#938)) ([d16728e](d16728e)) * **prebuilt/cloud-sql-mysql:** Update list_table tool ([#924](#924)) ([2083ba5](2083ba5)) * Replace 'float' with 'number' in McpManifest ([#985](#985)) ([59e23e1](59e23e1)) * **server/api:** Add logger to context in tool invoke handler ([#891](#891)) ([8ce311f](8ce311f)) * **sources/looker:** Add agent tag to Looker API calls. ([#966](#966)) ([f55dd6f](f55dd6f)) * **tools/bigquery-execute-sql:** Ensure invoke always returns a non-null value ([#925](#925)) ([9a55b80](9a55b80)) * **tools/mysqlsql:** Unmarshal json data from database during invoke ([#979](#979)) ([ccc3498](ccc3498)), closes [#840](#840) --- This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please). --------- Co-authored-by: release-please[bot] <55107282+release-please[bot]@users.noreply.github.com> Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com> c45390e

🤖 I have created a release *beep* *boop* --- ## [0.10.0](googleapis/genai-toolbox@v0.9.0...v0.10.0) (2025-07-25) ### Features * Add `Map` parameters support ([googleapis#928](googleapis#928)) ([4468bc9](googleapis@4468bc9)) * Add Dataplex source and tool ([googleapis#847](googleapis#847)) ([30c16a5](googleapis@30c16a5)) * Add Looker source and tool ([googleapis#923](googleapis#923)) ([c67e01b](googleapis@c67e01b)) * Add support for null optional parameter ([googleapis#802](googleapis#802)) ([a817b12](googleapis@a817b12)), closes [googleapis#736](googleapis#736) * **prebuilt/alloydb-admin-config:** Add alloydb control plane as a prebuilt config ([googleapis#937](googleapis#937)) ([0b28b72](googleapis@0b28b72)) * **prebuilt/mysql,prebuilt/mssql:** Add generic mysql and mssql prebuilt tools ([googleapis#983](googleapis#983)) ([c600c30](googleapis@c600c30)) * **server/mcp:** Support MCP version 2025-06-18 ([googleapis#898](googleapis#898)) ([313d3ca](googleapis@313d3ca)) * **sources/mssql:** Add support for encrypt connection parameter ([googleapis#874](googleapis#874)) ([14a868f](googleapis@14a868f)) * **sources/firestore:** Add Firestore as Source ([googleapis#786](googleapis#786)) ([2bb790e](googleapis@2bb790e)) * **sources/mongodb:** Add MongoDB Source ([googleapis#969](googleapis#969)) ([74dbd61](googleapis@74dbd61)) * **tools/alloydb-wait-for-operation:** Add wait for operation tool with exponential backoff ([googleapis#920](googleapis#920)) ([3f6ec29](googleapis@3f6ec29)) * **tools/mongodb-aggregate:** Add MongoDB `aggregate` Tools ([googleapis#977](googleapis#977)) ([bd399bb](googleapis@bd399bb)) * **tools/mongodb-delete:** Add MongoDB `delete` Tools ([googleapis#974](googleapis#974)) ([78e9752](googleapis@78e9752)) * **tools/mongodb-find:** Add MongoDB `find` Tools ([googleapis#970](googleapis#970)) ([a747475](googleapis@a747475)) * **tools/mongodb-insert:** Add MongoDB `insert` Tools ([googleapis#975](googleapis#975)) ([4c63f0c](googleapis@4c63f0c)) * **tools/mongodb-update:** Add MongoDB `update` Tools ([googleapis#972](googleapis#972)) ([dfde52c](googleapis@dfde52c)) * **tools/neo4j-execute-cypher:** Add neo4j-execute-cypher for Neo4j sources ([googleapis#946](googleapis#946)) ([81d0505](googleapis@81d0505)) * **tools/neo4j-schema:** Add neo4j-schema tool ([googleapis#978](googleapis#978)) ([be7db3d](googleapis@be7db3d)) * **tools/wait:** Create wait for tool ([googleapis#885](googleapis#885)) ([ed5ef4c](googleapis@ed5ef4c)) ### Bug Fixes * Fix document preview pipeline for forked PRs ([googleapis#950](googleapis#950)) ([481cc60](googleapis@481cc60)) * **prebuilt/firestore:** Mark database field as required in the firestore prebuilt tools ([googleapis#959](googleapis#959)) ([15417d4](googleapis@15417d4)) * **prebuilt/cloud-sql-mssql:** Correct source reference for execute_sql tool in cloud-sql-mssql.yaml prebuilt config ([googleapis#938](googleapis#938)) ([d16728e](googleapis@d16728e)) * **prebuilt/cloud-sql-mysql:** Update list_table tool ([googleapis#924](googleapis#924)) ([2083ba5](googleapis@2083ba5)) * Replace 'float' with 'number' in McpManifest ([googleapis#985](googleapis#985)) ([59e23e1](googleapis@59e23e1)) * **server/api:** Add logger to context in tool invoke handler ([googleapis#891](googleapis#891)) ([8ce311f](googleapis@8ce311f)) * **sources/looker:** Add agent tag to Looker API calls. ([googleapis#966](googleapis#966)) ([f55dd6f](googleapis@f55dd6f)) * **tools/bigquery-execute-sql:** Ensure invoke always returns a non-null value ([googleapis#925](googleapis#925)) ([9a55b80](googleapis@9a55b80)) * **tools/mysqlsql:** Unmarshal json data from database during invoke ([googleapis#979](googleapis#979)) ([ccc3498](googleapis@ccc3498)), closes [googleapis#840](googleapis#840) --- This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please). --------- Co-authored-by: release-please[bot] <55107282+release-please[bot]@users.noreply.github.com> Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com> c45390e

slilichenko · 2025-07-25T18:23:52Z

internal/tools/bigquery/bigqueryexecutesql/bigqueryexecutesql.go

@@ -121,18 +125,46 @@ func (t Tool) Invoke(ctx context.Context, params tools.ParamValues) (any, error)
 		return nil, fmt.Errorf("unable to get cast %s", sliceParams[0])
 	}

+	dryRunJob, err := dryRunQuery(ctx, t.RestService, t.Client.Project(), sql)


It doesn't look like you pass the parameters to the dry run. Wouldn't a parameterized query fail in this case?

It also seems pretty costly to do dry run for every sql statement. Dry runs against external tables without metadata caching can have latencies of minutes. Suggest reconsidering this approach and trying to find out the type of the statement from the actual job execution. If that's not feasible - consider caching.

Also, test how it would work with "EXECUTE IMMEDIATE" statement - the dry run might return an unexpected result.

This is for bigquery-execute-sql so there is no parameter input. I'm also working on a dry-run function for bigquery-sql, which need a dry-run that supports parameters. I already have a local branch for that, but due to rest api requires us to handle parameter types manually, I'm still working on the tests to make sure it works as expected for all data types we support.

OK, sorry - I missed that this is not for the bigquery-sql tool. I would still consider not using dry-run if at all possible.

Thanks! The "EXECUTE IMMEDIATE" statement seems to be an issue, it's type is something else. Will try figure a way to make this work.

Genesis929 changed the title ~~fix: make bigquery-execute-sql always return a non-null value when su…~~ fix: make bigquery-execute-sql always return a non-null value Jul 17, 2025

Genesis929 marked this pull request as ready for review July 17, 2025 22:26

Genesis929 requested a review from a team as a code owner July 17, 2025 22:26

blunderbuss-gcf bot assigned kurtisvg Jul 17, 2025

Genesis929 added the tests: run Label to trigger Github Action tests. label Jul 17, 2025

github-actions bot removed tests: run Label to trigger Github Action tests. labels Jul 17, 2025

Genesis929 changed the title ~~fix: make bigquery-execute-sql always return a non-null value~~ fix: : ensure bigquery-execute-sql always returns a non-null value Jul 17, 2025

Genesis929 added the tests: run Label to trigger Github Action tests. label Jul 17, 2025

github-actions bot removed the tests: run Label to trigger Github Action tests. label Jul 17, 2025

Yuan325 reviewed Jul 18, 2025

View reviewed changes

Genesis929 added the tests: run Label to trigger Github Action tests. label Jul 18, 2025

github-actions bot removed the tests: run Label to trigger Github Action tests. label Jul 18, 2025

Yuan325 approved these changes Jul 18, 2025

View reviewed changes

internal/tools/bigquery/bigqueryexecutesql/bigqueryexecutesql.go Outdated Show resolved Hide resolved

Yuan325 changed the title ~~fix: : ensure bigquery-execute-sql always returns a non-null value~~ fix(tools/bigquery-execute-sql): ensure invoke always returns a non-null value Jul 18, 2025

kurtisvg approved these changes Jul 18, 2025

View reviewed changes

Genesis929 and others added 7 commits July 18, 2025 09:56

fix: make bigquery-execute-sql always return a non-null value when su…

b320dd2

…ccess.

update creds

24244ab

Update tests/bigquery/bigquery_integration_test.go

c1d248c

Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>

Update tests/bigquery/bigquery_integration_test.go

9f3c673

Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>

Update tests/bigquery/bigquery_integration_test.go

b975a48

Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>

update empty rows case

1a8c403

add comment to dry run

99f4101

kurtisvg force-pushed the execute_sql_return_fix branch from bd97b93 to 99f4101 Compare July 18, 2025 15:56

kurtisvg enabled auto-merge (squash) July 18, 2025 15:56

Update internal/tools/bigquery/bigqueryexecutesql/bigqueryexecutesql.go

e0c7992

Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>

Genesis929 added the tests: run Label to trigger Github Action tests. label Jul 18, 2025

github-actions bot removed the tests: run Label to trigger Github Action tests. label Jul 18, 2025

kurtisvg merged commit 9a55b80 into googleapis:main Jul 18, 2025
10 checks passed

release-please bot mentioned this pull request Jul 18, 2025

chore(main): release 0.10.0 #886

Merged

Genesis929 mentioned this pull request Jul 22, 2025

chore: fix dry run location #947

Merged

Genesis929 added a commit that referenced this pull request Jul 22, 2025

chore: fix dry run location (#947)

9334368

Updated dry run in execute sql to also include a location, fix the potential issue in PR #925.

BrewTestBot mentioned this pull request Jul 25, 2025

mcp-toolbox 0.10.0 Homebrew/homebrew-core#231171

Merged

slilichenko reviewed Jul 25, 2025

View reviewed changes

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

fix(tools/bigquery-execute-sql): ensure invoke always returns a non-null value #925

fix(tools/bigquery-execute-sql): ensure invoke always returns a non-null value #925

Uh oh!

Genesis929 commented Jul 17, 2025 •

edited

Loading

Uh oh!

Yuan325 left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Genesis929 commented Jul 18, 2025

Uh oh!

Yuan325 left a comment

Uh oh!

Uh oh!

Uh oh!

slilichenko Jul 25, 2025

Uh oh!

Genesis929 Jul 28, 2025 •

edited

Loading

Uh oh!

slilichenko Jul 28, 2025

Uh oh!

Genesis929 Jul 29, 2025 •

edited

Loading

Uh oh!

Uh oh!

fix(tools/bigquery-execute-sql): ensure invoke always returns a non-null value #925

fix(tools/bigquery-execute-sql): ensure invoke always returns a non-null value #925

Uh oh!

Conversation

Genesis929 commented Jul 17, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Yuan325 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Genesis929 commented Jul 18, 2025

Uh oh!

Yuan325 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

slilichenko Jul 25, 2025

Choose a reason for hiding this comment

Uh oh!

Genesis929 Jul 28, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

slilichenko Jul 28, 2025

Choose a reason for hiding this comment

Uh oh!

Genesis929 Jul 29, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Genesis929 commented Jul 17, 2025 •

edited

Loading

Genesis929 Jul 28, 2025 •

edited

Loading

Genesis929 Jul 29, 2025 •

edited

Loading