# Cross-engine unknown-field error contract ## Background and goal Data Proxy SQL requests that reference columns absent from a cluster's actual schema currently surface as generic HTTP 500 responses. The same deterministic client error is reported differently by BigQuery, Athena, MaxCompute, and Azure Synapse. The goal is to classify only reliably identified unknown-field failures and expose one safe, machine-readable HTTP 400 contract across the canonical and legacy single and batch SQL APIs. ## Target contract - Single-query failures return `InvalidArgument`, `details.reason=UNKNOWN_FIELD`, a sorted and deduplicated `details.fields` list, and `details.retryable=false`. - Canonical endpoints keep the shared `APIError` envelope. Legacy/internal/public `/query` endpoints keep the existing `error` field and add `code`, `requestId`, and `details`. - Batch execution remains all-or-nothing. If every failure is an unknown-field error, the response is HTTP 400 with indexed query details. A mixed unknown-field/internal failure remains HTTP 500. - Upstream messages, rewritten SQL, physical table names, cloud project/dataset names, and storage paths are never returned. ## Implementation - Add a shared engine error type and strict field normalization. - BigQuery accepts only structured `invalidQuery` errors with known unknown-name message shapes. - Athena accepts only `COLUMN_NOT_FOUND` or the strict `Column '' cannot be resolved` shape, without changing existing table repair behavior. - MaxCompute accepts only the strict `ODPS-0130071 ... column cannot be resolved` shape because its SDK exposes task failure text rather than a structured semantic error. - Azure Synapse accepts only SQL Server error 207 and strict `Invalid column name ''` messages, including entries in `mssql.Error.All`. - Preserve indexed errors in batch execution instead of flattening concurrent failures into one string. - Map the typed errors in canonical and legacy single/batch handlers and add stable observability error codes. - Update generated OpenAPI documentation for the affected SQL routes. ## Exclusions and compatibility - Do not call `table-schemas` before each query and do not remove fields or rewrite query semantics on behalf of callers. - Do not classify general syntax, type, permission, resource, partition, UDF, Glue, or internal failures as unknown fields. - DuckDB, Metrics, Loki, Activities, and non-SQL APIs are unchanged. - Existing successful responses and batch all-or-nothing behavior remain unchanged. ## Tests and verification - Cover positive, wrapped, multi-field, deduplication, sorting, and strict negative cases for all four engines. - Cover single-query propagation, indexed batch aggregation, mixed batch failures, and Athena repair precedence. - Cover canonical and legacy single/batch HTTP bodies, status codes, trace IDs, compatibility fields, and non-leakage. - Run focused `pkg/engine`, `pkg/service`, and `pkg/handlers` tests, formatting checks, Swagger generation consistency checks, and `git diff --check` from the `clinic_data_service` module. - In staging, verify unknown and valid fields on every available cloud engine, then replay the original GCP Slow Query request. ## Production follow-up: BigQuery suggestion messages Production validation of the feature branch confirmed that the new code was running, but the original GCP Slow Query and individual `tidb_cpu_time` / `tikv_cpu_time` queries still returned HTTP 500. A valid `time, digest` control query returned HTTP 200, and system logs showed that BigQuery table refresh completed before query execution failed. The failure was therefore isolated to BigQuery unknown-field classification rather than deployment, routing, table management, or the query data source. BigQuery can place a suggestion between the unknown identifier and source location, for example `Unrecognized name: ; Did you mean ...? at [...]`. The original classifier accepted only whitespace followed by `at`, so it missed this structured `invalidQuery` variant. The follow-up implementation: - accepts a semicolon after a strictly validated identifier while continuing to reject dotted, quoted, path-like, or otherwise unsafe identifiers; - requires the primary `status.Err()` to classify as unknown-field before collecting additional fields from `status.Errors`; - preserves other primary errors as HTTP 500 even when supplemental errors mention unknown fields; - replaces unclassified BigQuery failures with a safe typed diagnostic containing only query ID, reason, error count, fixed message-shape enum, and suggestion presence; - preserves standard context cancellation and deadline errors so existing HTTP 499/504 behavior remains unchanged; - never stores or logs the original BigQuery message, suggested field, SQL, table, project, dataset, or storage path. Acceptance requires valid fields to return HTTP 200 and the two production unknown fields plus the original combined query to return HTTP 400 with `UNKNOWN_FIELD` and `retryable=false` after redeployment. ## Second production follow-up: GetQueryResults errors After deploying the first BigQuery follow-up, the valid-field control query returned HTTP 200 but both individual unknown fields and the combined query still returned HTTP 500. The new safe diagnostics consistently reported a BigQuery Query ID with `reason=unknown` and `message_shape=empty`. SDK control-flow inspection confirmed that `job.Wait()` calls `GetQueryResults` for query jobs. Invalid-field failures can therefore return from `job.Wait()` as `*googleapi.Error` before the code reaches `status.Err()`. The original and first follow-up classifiers handled only `*bigquery.Error` / `bigquery.MultiError`, so they could not classify the actual production error type. The second follow-up: - classifies a Wait error only when it is a structured `*googleapi.Error` with HTTP 400, non-empty detail items, every item has reason `invalidQuery`, and every message yields a strictly safe unknown identifier; - keeps mixed reasons, syntax errors, non-400 responses, missing details, and unsafe identifiers as HTTP 500; - records the fixed error source `googleapi` or `bigquery` with the existing bounded diagnostics, while never retaining the Google API body or complete message; - continues to preserve context cancellation and deadline errors and leaves successful queries and table refresh behavior unchanged. ## Deployment impact and release boundary - Affected module: `clinic_data_service`; packages `pkg/engine`, `pkg/service`, and `pkg/handlers`; entrypoint `clinic_data_service/cmd/api`; image `clinic/data-service`. - Required deployment: Clinic CD component `clinic-data-service`, app-delivery application `clinic-data-service`, and `Deployment/clinic-data-service`; staging first, prod-cloud only with separate authorization. - No deployment is required for `clinic`, `internal-api`, `task-manager`, Kong, NGM, or cloud-server. No app-delivery configuration change is required. - Do not merge or release automatically.