search

cloudspannerecosystem / memefish

memefish is the foundation to analyze Spanner SQL
https://cloudspannerecosystem.dev/memefish/
MIT License
76 stars 19 forks source link

Support EXCEPT and REPLACE in SelectItem #184

Open apstndb opened 2 weeks ago

apstndb commented 2 weeks ago 
select_all:
  [ expression. ]*
  [ EXCEPT ( column_name [, ...] ) ]
  [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

EXCEPT

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

$ go run ./tools/parse --mode query "SELECT * EXCEPT (n) FROM (SELECT 1 AS n, "foo" AS s)"                    
2024/10/28 00:12:11 syntax error: :1:17: expected token: ALL, DISTINCT, but: (

  1:  SELECT * EXCEPT (n) FROM (SELECT 1 AS n, foo AS s)

REPLACE

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

$ go run ./tools/parse --mode query "SELECT * REPLACE (2 AS n) FROM (SELECT 1 AS n, "foo" AS s)"
2024/10/28 00:12:44 syntax error: :1:10: expected token: <eof>, but: <ident>

  1:  SELECT * REPLACE (2 AS n) FROM (SELECT 1 AS n, foo AS s)
               ^~~~~~~

Note:

Official syntax docs says AS is optional, but actually AS can't be omitted

spanner> SELECT * REPLACE (2 n) FROM (SELECT 1 AS n, "foo" AS s);
ERROR: spanner: code = "InvalidArgument", desc = "Syntax error: Expected keyword AS but got identifier \\\"n\\\" [at 1:21]\\nSELECT * REPLACE (2 n) FROM (SELECT 1 AS n, \\\"foo\\\" AS s)\\n                    ^"