Research all possible hint positions

[apstndb]

Research all hint position, it would be documented or undocumented(only in ZetaSQL) .

query

statement hints

query_statement:
  [ statement_hint_expr ]
  [ table_hint_expr ]
  [ join_hint_expr ]
  query_expr

Actually, multiple hint structures are not permitted. It can only be merged.

$ gcloud spanner databases execute-sql ${SPANNER_DATABASE} --sql '@{OPTIMIZER_VERSION=latest} @{FORCE_INDEX=_BASE_TABLE} SELECT 1'                                 
ERROR: (gcloud.spanner.databases.execute-sql) INVALID_ARGUMENT: Syntax error: Unexpected \"@\" [at 1:29]\n@{OPTIMIZER_VERSION=latest} @{FORCE_INDEX=_BASE_TABLE} SELECT 1\n                            ^
- '@type': type.googleapis.com/google.rpc.LocalizedMessage
  locale: en-US
  message: |-
    Syntax error: Unexpected "@" [at 1:29]
    @{OPTIMIZER_VERSION=latest} @{FORCE_INDEX=_BASE_TABLE} SELECT 1
                                ^

$ gcloud spanner databases execute-sql ${SPANNER_DATABASE} --sql '@{OPTIMIZER_VERSION=latest,FORCE_INDEX=_BASE_TABLE} SELECT 1'
(Unspecified)
1

table hints

https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#from_clause

from_item:
  {
    table_name [ table_hint_expr ] [ as_alias ]
    | { join_operation | ( join_operation ) }
    | ( query_expr ) [ table_hint_expr ] [ as_alias ]
    | field_path
    | unnest_operator
    | cte_name [ table_hint_expr ] [ as_alias ]
    | graph_table_operator [ as_alias ]
  }

https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#unnest_operator

unnest_operator:
  {
    UNNEST( array ) [ as_alias ]
    | array_path [ as_alias ]
  }
  [ table_hint_expr ]
  [ WITH OFFSET [ as_alias ] ]

join hints

https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#join_types

join_operation:
  { cross_join_operation | condition_join_operation }

cross_join_operation:
  from_item cross_join_operator [ join_hint_expr ] from_item

condition_join_operation:
  from_item condition_join_operator [ join_hint_expr ] from_item join_condition

function hints

https://cloud.google.com/spanner/docs/reference/standard-sql/functions-reference#disable_inline

function_name() @{DISABLE_INLINE = TRUE}

graph hints

• TODO: It should be implemented in Implement Spanner Graph queries #99

Pipe hints

TODO

DML hints

INSERT statement doesn't support any hints.

https://cloud.google.com/spanner/docs/reference/standard-sql/dml-syntax#insert-statement

Hints of DELETE statement

Supports statement hints and table hints.

[statement_hint_expr] DELETE [FROM] table_name [table_hint_expr] [[AS] alias] WHERE condition [return_clause];

Hints of UPDATE statement

UPDATE statement supports statement hints and table hints

https://cloud.google.com/spanner/docs/reference/standard-sql/dml-syntax#update-statement

[statement_hint_expr] UPDATE table_name [table_hint_expr] [[AS] alias]
SET update_item [, ...]
WHERE condition [return_clause];

Current compatibility

Statement hints of query are implemented

$ go run github.com/cloudspannerecosystem/memefish/tools/parse@latest --mode statement 
    '@{OPTIMIZER_VERSION=latest} SELECT *'

Table hints on table name are implemented

$ go run github.com/cloudspannerecosystem/memefish/tools/parse@latest --mode query \
   'SELECT * FROM Singers@{FORCE_INDEX=_BASE_TABLE}'

Acutually, table hint with subquery seems to be invalid in real Spanner instance.

$ gcloud spanner databases execute-sql ${SPANNER_DATABASE} --sql 'SELECT * FROM (SELECT * FROM Singers)@{FORCE_INDEX=_BASE_TABLE}'                                  
ERROR: (gcloud.spanner.databases.execute-sql) INVALID_ARGUMENT: Syntax error: Expected end of input but got \"@\" [at 1:38]\nSELECT * FROM (SELECT * FROM Singers)@{FORCE_INDEX=_BASE_TABLE}\n                                     ^
- '@type': type.googleapis.com/google.rpc.LocalizedMessage
  locale: en-US
  message: |-
    Syntax error: Expected end of input but got "@" [at 1:38]
    SELECT * FROM (SELECT * FROM Singers)@{FORCE_INDEX=_BASE_TABLE}
                                         ^

Table hints with CTE name and UNNEST is valid, but I couldn't find valid values

$ gcloud spanner databases execute-sql ${SPANNER_DATABASE} \
    --sql 'WITH cte AS (SELECT * FROM Singers) SELECT * FROM cte@{FORCE_INDEX=_BASE_TABLE}'    
ERROR: (gcloud.spanner.databases.execute-sql) INVALID_ARGUMENT: Unsupported hint: FORCE_INDEX.

$ gcloud spanner databases execute-sql ${SPANNER_DATABASE} \
    --sql 'SELECT * FROM UNNEST([1, 2, 3])@{GROUPBY_SCAN_OPTIMIZATION=TRUE}'                
ERROR: (gcloud.spanner.databases.execute-sql) INVALID_ARGUMENT: Unsupported hint: GROUPBY_SCAN_OPTIMIZATION.

JOIN hints are implemented

$ go run github.com/cloudspannerecosystem/memefish/tools/parse@latest --mode statement \
    'SELECT * FROM Singers CROSS JOIN@{JOIN_METHOD=APPLY_JOIN} Albums'

$ go run github.com/cloudspannerecosystem/memefish/tools/parse@latest --mode statement \
    'SELECT * FROM Singers JOIN@{JOIN_METHOD=APPLY_JOIN} Albums USING (SingerId)'

DML hints are not implemented.

• Support DML with statements hints #133
• Support table hints in DML statements #156

Function hints are not implemented.

• Support function hints #198

Undocument hint positions

Group hints

They seem to be functional so it is better to be implemented.
https://github.com/GoogleCloudPlatform/cloud-spanner-emulator/blob/7f22a69c0d331a980bc6be6c0d3c6117827b16de/tests/conformance/cases/query_hints.cc#L139-L146

  ZETASQL_EXPECT_OK(
      Query("SELECT 1 FROM Users GROUP @{group_method=hash_group} BY Name"));
  ZETASQL_EXPECT_OK(
      Query("SELECT 1 FROM Users GROUP @{group_method=stream_group} BY Name"));
  ZETASQL_EXPECT_OK(
      Query("SELECT 1 FROM Users GROUP @{group_type=hash_group} BY Name"));
  ZETASQL_EXPECT_OK(
      Query("SELECT 1 FROM Users GROUP @{group_type=stream_group} BY Name"));