Repository: cloudspannerecosystem/memefish (go)

Issue:
Support WITH expression
`WITH` expression is not yet supported.

## Syntax

https://cloud.google.com/spanner/docs/reference/standard-sql/operators#with_expression

```
WITH(variable_assignment[, ...], result_expression)

variable_assignment:
  variable_name AS expression
```

```
$ go run ./tools/parse --mode query 'SELECT WITH(s AS SUM(input), c AS COUNT(input), s/c) FROM UNNEST([1.0, 2.0, 3.0]) AS input;'
2024/10/04 01:34:52 syntax error::1:8: unexpected token: WITH

  1:  SELECT WITH(s AS SUM(input), c AS COUNT(input), s/c) FROM UNNEST([1.0, 2.0, 3.0]) AS input;
             ^~~~
exit status 1
```


## Reference

In ZetaSQL
* https://github.com/google/zetasql/blob/194cd32b5d766d60e3ca442651d792c7fe54ea74/zetasql/parser/gen_parse_tree.py#L9811-L9826
* https://github.com/google/zetasql/blob/194cd32b5d766d60e3ca442651d792c7fe54ea74/zetasql/parser/bison_parser.y#L7680-L7704

Pull request:
Implement WITH expression
This PR implements `WITH` expression

There is no breaking changes.

## Related issues

- close #134 

Patch:
diff --git a/ast/ast.go b/ast/ast.go
index 19980c7..c731c2c 100644
--- a/ast/ast.go
+++ b/ast/ast.go
@@ -172,6 +172,7 @@ func (CallExpr) isExpr()              {}
 func (CountStarExpr) isExpr()         {}
 func (CastExpr) isExpr()              {}
 func (ExtractExpr) isExpr()           {}
+func (WithExpr) isExpr()              {}
 func (ReplaceFieldsExpr) isExpr()     {}
 func (CaseExpr) isExpr()              {}
 func (IfExpr) isExpr()                {}
@@ -1145,9 +1146,10 @@ type SubscriptSpecifierKeyword struct {
 //		{{.NullHandling | sqlOpt}}
 //		{{.Having | sqlOpt}}
 //	)
+//	{{.Hint | sqlOpt}}
 type CallExpr struct {
 	// pos = Func.pos
-	// end = Rparen + 1
+	// end = Hint.end || Rparen + 1
 
 	Rparen token.Pos // position of ")"
 
@@ -1157,6 +1159,7 @@ type CallExpr struct {
 	NamedArgs    []*NamedArg
 	NullHandling NullHandlingModifier // optional
 	Having       HavingModifier       // optional
+	Hint         *Hint                // optional
 }
 
 // ExprArg is argument of the generic function call.
@@ -1327,6 +1330,31 @@ type AtTimeZone struct {
 	Expr Expr
 }
 
+// WithExprVar is "name AS expr" node in WITH expression.
+//
+//	{{.Name | sql}} AS {{.Expr | sql}}
+type WithExprVar struct {
+	// pos = Name.pos
+	// end = Expr.end
+
+	Name *Ident
+	Expr Expr
+}
+
+// WithExpr is WITH expression node.
+//
+//	WITH({{.Vars | sqlJoin ", "}}, {{.Expr | sql}})
+type WithExpr struct {
+	// pos = With
+	// end = Rparen + 1
+
+	With   token.Pos // position of "WITH" keyword
+	Rparen token.Pos // position of ")"
+
+	Vars []*WithExprVar // len(Vars) > 0
+	Expr Expr
+}
+
 // CastExpr is CAST/SAFE_CAST call expression node.
 //
 //	{{if .Safe}}SAFE_{{end}}CAST({{.Expr | sql}} AS {{.Type | sql}})
diff --git a/ast/pos.go b/ast/pos.go
index 0c26d41..bbfeb4c 100644
--- a/ast/pos.go
+++ b/ast/pos.go
@@ -395,7 +395,7 @@ func (c *CallExpr) Pos() token.Pos {
 }
 
 func (c *CallExpr) End() token.Pos {
-	return posAdd(c.Rparen, 1)
+	return posChoice(nodeEnd(wrapNode(c.Hint)), posAdd(c.Rparen, 1))
 }
 
 func (e *ExprArg) Pos() token.Pos {
@@ -510,6 +510,22 @@ func (a *AtTimeZone) End() token.Pos {
 	return nodeEnd(wrapNode(a.Expr))
 }
 
+func (w *WithExprVar) Pos() token.Pos {
+	return nodePos(wrapNode(w.Name))
+}
+
+func (w *WithExprVar) End() token.Pos {
+	return nodeEnd(wrapNode(w.Expr))
+}
+
+func (w *WithExpr) Pos() token.Pos {
+	return w.With
+}
+
+func (w *WithExpr) End() token.Pos {
+	return posAdd(w.Rparen, 1)
+}
+
 func (c *CastExpr) Pos() token.Pos {
 	return c.Cast
 }
diff --git a/ast/sql.go b/ast/sql.go
index 0ab1eea..06e1e52 100644
--- a/ast/sql.go
+++ b/ast/sql.go
@@ -80,7 +80,7 @@ func exprPrec(e Expr) prec {
 	case *CallExpr, *CountStarExpr, *CastExpr, *ExtractExpr, *CaseExpr, *IfExpr, *ParenExpr, *ScalarSubQuery,
 		*ArraySubQuery, *ExistsSubQuery, *Param, *Ident, *Path, *ArrayLiteral, *TupleStructLiteral, *TypedStructLiteral,
 		*TypelessStructLiteral, *NullLiteral, *BoolLiteral, *IntLiteral, *FloatLiteral, *StringLiteral, *BytesLiteral,
-		*DateLiteral, *TimestampLiteral, *NumericLiteral, *JSONLiteral:
+		*DateLiteral, *TimestampLiteral, *NumericLiteral, *JSONLiteral, *WithExpr:
 		return precLit
 	case *IndexExpr, *SelectorExpr:
 		return precSelector
@@ -503,7 +503,8 @@ func (c *CallExpr) SQL() string {
 		sqlJoin(c.NamedArgs, ", ") +
 		sqlOpt(" ", c.NullHandling, "") +
 		sqlOpt(" ", c.Having, "") +
-		")"
+		")" +
+		sqlOpt(" ", c.Hint, "")
 }
 
 func (l *LambdaArg) SQL() string {
@@ -564,6 +565,12 @@ func (r *ReplaceFieldsExpr) SQL() string {
 	return "REPLACE_FIELDS(" + r.Expr.SQL() + ", " + sqlJoin(r.Fields, ", ") + ")"
 }
 
+func (n *WithExprVar) SQL() string { return n.Name.SQL() + " AS " + n.Expr.SQL() }
+
+func (w *WithExpr) SQL() string {
+	return "WITH(" + sqlJoin(w.Vars, ", ") + ", " + w.Expr.SQL() + ")"
+}
+
 func (c *CastExpr) SQL() string {
 	return strOpt(c.Safe, "SAFE_") + "CAST(" + c.Expr.SQL() + " AS " + c.Type.SQL() + ")"
 }
diff --git a/parser.go b/parser.go
index d41120a..cf3f223 100644
--- a/parser.go
+++ b/parser.go
@@ -1451,6 +1451,8 @@ func (p *Parser) parseLit() ast.Expr {
 		return p.parseExistsSubQuery()
 	case "EXTRACT":
 		return p.parseExtractExpr()
+	case "WITH":
+		return p.parseWithExpr()
 	case "ARRAY":
 		return p.parseArrayLiteralOrSubQuery()
 	case "STRUCT":
@@ -1553,6 +1555,8 @@ func (p *Parser) parseCall(id token.Token) ast.Expr {
 	having := p.tryParseHavingModifier()
 
 	rparen := p.expect(")").Pos
+	hint := p.tryParseHint()
+
 	return &ast.CallExpr{
 		Rparen:       rparen,
 		Func:         fn,
@@ -1561,6 +1565,7 @@ func (p *Parser) parseCall(id token.Token) ast.Expr {
 		NamedArgs:    namedArgs,
 		NullHandling: nullHandling,
 		Having:       having,
+		Hint:         hint,
 	}
 }
 
@@ -1921,6 +1926,47 @@ func (p *Parser) tryParseAtTimeZone() *ast.AtTimeZone {
 	}
 }
 
+func (p *Parser) parseWithExprVar() *ast.WithExprVar {
+	name := p.parseIdent()
+	p.expect("AS")
+	expr := p.parseExpr()
+
+	return &ast.WithExprVar{
+		Expr: expr,
+		Name: name,
+	}
+}
+
+func (p *Parser) lookaheadWithExprVar() bool {
+	lexer := p.Lexer.Clone()
+	defer func() {
+		p.Lexer = lexer
+	}()
+
+	p.parseIdent()
+	return p.Token.Kind == "AS"
+}
+
+func (p *Parser) parseWithExpr() *ast.WithExpr {
+	with := p.expect("WITH").Pos
+	p.expect("(")
+
+	var vars []*ast.WithExprVar
+	for p.lookaheadWithExprVar() {
+		vars = append(vars, p.parseWithExprVar())
+		p.expect(",")
+	}
+
+	expr := p.parseExpr()
+	rparen := p.expect(")").Pos
+	return &ast.WithExpr{
+		With:   with,
+		Rparen: rparen,
+		Vars:   vars,
+		Expr:   expr,
+	}
+}
+
 func (p *Parser) parseParenExpr() ast.Expr {
 	paren := p.Token
 


Test patch:
diff --git a/testdata/input/query/select_function_hints.sql b/testdata/input/query/select_function_hints.sql
new file mode 100644
index 0000000..6998ca9
--- /dev/null
+++ b/testdata/input/query/select_function_hints.sql
@@ -0,0 +1,5 @@
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/functions-reference#function_hints
+SELECT
+    SUBSTRING(CAST(x AS STRING), 2, 5) AS w,
+    SUBSTRING(CAST(x AS STRING), 3, 7) AS y
+FROM (SELECT SHA512(z) @{DISABLE_INLINE = TRUE} AS x FROM t)
\ No newline at end of file
diff --git a/testdata/input/query/select_with.sql b/testdata/input/query/select_with.sql
new file mode 100644
index 0000000..8df183e
--- /dev/null
+++ b/testdata/input/query/select_with.sql
@@ -0,0 +1,5 @@
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/operators#with_expression
+SELECT WITH(a AS '123',       -- a is '123'
+    b AS CONCAT(a, '456'),    -- b is '123456'
+    c AS '789',               -- c is '789'
+    CONCAT(b, c)) AS result   -- b + c is '123456789'
\ No newline at end of file
diff --git a/testdata/result/query/select_function_hints.sql.txt b/testdata/result/query/select_function_hints.sql.txt
new file mode 100644
index 0000000..0aeff39
--- /dev/null
+++ b/testdata/result/query/select_function_hints.sql.txt
@@ -0,0 +1,185 @@
+--- select_function_hints.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/functions-reference#function_hints
+SELECT
+    SUBSTRING(CAST(x AS STRING), 2, 5) AS w,
+    SUBSTRING(CAST(x AS STRING), 3, 7) AS y
+FROM (SELECT SHA512(z) @{DISABLE_INLINE = TRUE} AS x FROM t)
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  99,
+    Results: []ast.SelectItem{
+      &ast.Alias{
+        Expr: &ast.CallExpr{
+          Rparen: 143,
+          Func:   &ast.Ident{
+            NamePos: 110,
+            NameEnd: 119,
+            Name:    "SUBSTRING",
+          },
+          Args: []ast.Arg{
+            &ast.ExprArg{
+              Expr: &ast.CastExpr{
+                Cast:   120,
+                Rparen: 136,
+                Expr:   &ast.Ident{
+                  NamePos: 125,
+                  NameEnd: 126,
+                  Name:    "x",
+                },
+                Type: &ast.SimpleType{
+                  NamePos: 130,
+                  Name:    "STRING",
+                },
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 139,
+                ValueEnd: 140,
+                Base:     10,
+                Value:    "2",
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 142,
+                ValueEnd: 143,
+                Base:     10,
+                Value:    "5",
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    145,
+          Alias: &ast.Ident{
+            NamePos: 148,
+            NameEnd: 149,
+            Name:    "w",
+          },
+        },
+      },
+      &ast.Alias{
+        Expr: &ast.CallExpr{
+          Rparen: 188,
+          Func:   &ast.Ident{
+            NamePos: 155,
+            NameEnd: 164,
+            Name:    "SUBSTRING",
+          },
+          Args: []ast.Arg{
+            &ast.ExprArg{
+              Expr: &ast.CastExpr{
+                Cast:   165,
+                Rparen: 181,
+                Expr:   &ast.Ident{
+                  NamePos: 170,
+                  NameEnd: 171,
+                  Name:    "x",
+                },
+                Type: &ast.SimpleType{
+                  NamePos: 175,
+                  Name:    "STRING",
+                },
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 184,
+                ValueEnd: 185,
+                Base:     10,
+                Value:    "3",
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 187,
+                ValueEnd: 188,
+                Base:     10,
+                Value:    "7",
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    190,
+          Alias: &ast.Ident{
+            NamePos: 193,
+            NameEnd: 194,
+            Name:    "y",
+          },
+        },
+      },
+    },
+    From: &ast.From{
+      From:   195,
+      Source: &ast.SubQueryTableExpr{
+        Lparen: 200,
+        Rparen: 254,
+        Query:  &ast.Select{
+          Select:  201,
+          Results: []ast.SelectItem{
+            &ast.Alias{
+              Expr: &ast.CallExpr{
+                Rparen: 216,
+                Func:   &ast.Ident{
+                  NamePos: 208,
+                  NameEnd: 214,
+                  Name:    "SHA512",
+                },
+                Args: []ast.Arg{
+                  &ast.ExprArg{
+                    Expr: &ast.Ident{
+                      NamePos: 215,
+                      NameEnd: 216,
+                      Name:    "z",
+                    },
+                  },
+                },
+                Hint: &ast.Hint{
+                  Atmark:  218,
+                  Rbrace:  241,
+                  Records: []*ast.HintRecord{
+                    &ast.HintRecord{
+                      Key: &ast.Ident{
+                        NamePos: 220,
+                        NameEnd: 234,
+                        Name:    "DISABLE_INLINE",
+                      },
+                      Value: &ast.BoolLiteral{
+                        ValuePos: 237,
+                        Value:    true,
+                      },
+                    },
+                  },
+                },
+              },
+              As: &ast.AsAlias{
+                As:    243,
+                Alias: &ast.Ident{
+                  NamePos: 246,
+                  NameEnd: 247,
+                  Name:    "x",
+                },
+              },
+            },
+          },
+          From: &ast.From{
+            From:   248,
+            Source: &ast.TableName{
+              Table: &ast.Ident{
+                NamePos: 253,
+                NameEnd: 254,
+                Name:    "t",
+              },
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT SUBSTRING(CAST(x AS STRING), 2, 5) AS w, SUBSTRING(CAST(x AS STRING), 3, 7) AS y FROM (SELECT SHA512(z) @{DISABLE_INLINE=TRUE} AS x FROM t)
diff --git a/testdata/result/query/select_with.sql.txt b/testdata/result/query/select_with.sql.txt
new file mode 100644
index 0000000..58348b5
--- /dev/null
+++ b/testdata/result/query/select_with.sql.txt
@@ -0,0 +1,112 @@
+--- select_with.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/operators#with_expression
+SELECT WITH(a AS '123',       -- a is '123'
+    b AS CONCAT(a, '456'),    -- b is '123456'
+    c AS '789',               -- c is '789'
+    CONCAT(b, c)) AS result   -- b + c is '123456789'
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  90,
+    Results: []ast.SelectItem{
+      &ast.Alias{
+        Expr: &ast.WithExpr{
+          With:   97,
+          Rparen: 241,
+          Vars:   []*ast.WithExprVar{
+            &ast.WithExprVar{
+              Name: &ast.Ident{
+                NamePos: 102,
+                NameEnd: 103,
+                Name:    "a",
+              },
+              Expr: &ast.StringLiteral{
+                ValuePos: 107,
+                ValueEnd: 112,
+                Value:    "123",
+              },
+            },
+            &ast.WithExprVar{
+              Name: &ast.Ident{
+                NamePos: 138,
+                NameEnd: 139,
+                Name:    "b",
+              },
+              Expr: &ast.CallExpr{
+                Rparen: 158,
+                Func:   &ast.Ident{
+                  NamePos: 143,
+                  NameEnd: 149,
+                  Name:    "CONCAT",
+                },
+                Args: []ast.Arg{
+                  &ast.ExprArg{
+                    Expr: &ast.Ident{
+                      NamePos: 150,
+                      NameEnd: 151,
+                      Name:    "a",
+                    },
+                  },
+                  &ast.ExprArg{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 153,
+                      ValueEnd: 158,
+                      Value:    "456",
+                    },
+                  },
+                },
+              },
+            },
+            &ast.WithExprVar{
+              Name: &ast.Ident{
+                NamePos: 185,
+                NameEnd: 186,
+                Name:    "c",
+              },
+              Expr: &ast.StringLiteral{
+                ValuePos: 190,
+                ValueEnd: 195,
+                Value:    "789",
+              },
+            },
+          },
+          Expr: &ast.CallExpr{
+            Rparen: 240,
+            Func:   &ast.Ident{
+              NamePos: 229,
+              NameEnd: 235,
+              Name:    "CONCAT",
+            },
+            Args: []ast.Arg{
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 236,
+                  NameEnd: 237,
+                  Name:    "b",
+                },
+              },
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 239,
+                  NameEnd: 240,
+                  Name:    "c",
+                },
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    243,
+          Alias: &ast.Ident{
+            NamePos: 246,
+            NameEnd: 252,
+            Name:    "result",
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT WITH(a AS "123", b AS CONCAT(a, "456"), c AS "789", CONCAT(b, c)) AS result
diff --git a/testdata/result/statement/select_function_hints.sql.txt b/testdata/result/statement/select_function_hints.sql.txt
new file mode 100644
index 0000000..0aeff39
--- /dev/null
+++ b/testdata/result/statement/select_function_hints.sql.txt
@@ -0,0 +1,185 @@
+--- select_function_hints.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/functions-reference#function_hints
+SELECT
+    SUBSTRING(CAST(x AS STRING), 2, 5) AS w,
+    SUBSTRING(CAST(x AS STRING), 3, 7) AS y
+FROM (SELECT SHA512(z) @{DISABLE_INLINE = TRUE} AS x FROM t)
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  99,
+    Results: []ast.SelectItem{
+      &ast.Alias{
+        Expr: &ast.CallExpr{
+          Rparen: 143,
+          Func:   &ast.Ident{
+            NamePos: 110,
+            NameEnd: 119,
+            Name:    "SUBSTRING",
+          },
+          Args: []ast.Arg{
+            &ast.ExprArg{
+              Expr: &ast.CastExpr{
+                Cast:   120,
+                Rparen: 136,
+                Expr:   &ast.Ident{
+                  NamePos: 125,
+                  NameEnd: 126,
+                  Name:    "x",
+                },
+                Type: &ast.SimpleType{
+                  NamePos: 130,
+                  Name:    "STRING",
+                },
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 139,
+                ValueEnd: 140,
+                Base:     10,
+                Value:    "2",
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 142,
+                ValueEnd: 143,
+                Base:     10,
+                Value:    "5",
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    145,
+          Alias: &ast.Ident{
+            NamePos: 148,
+            NameEnd: 149,
+            Name:    "w",
+          },
+        },
+      },
+      &ast.Alias{
+        Expr: &ast.CallExpr{
+          Rparen: 188,
+          Func:   &ast.Ident{
+            NamePos: 155,
+            NameEnd: 164,
+            Name:    "SUBSTRING",
+          },
+          Args: []ast.Arg{
+            &ast.ExprArg{
+              Expr: &ast.CastExpr{
+                Cast:   165,
+                Rparen: 181,
+                Expr:   &ast.Ident{
+                  NamePos: 170,
+                  NameEnd: 171,
+                  Name:    "x",
+                },
+                Type: &ast.SimpleType{
+                  NamePos: 175,
+                  Name:    "STRING",
+                },
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 184,
+                ValueEnd: 185,
+                Base:     10,
+                Value:    "3",
+              },
+            },
+            &ast.ExprArg{
+              Expr: &ast.IntLiteral{
+                ValuePos: 187,
+                ValueEnd: 188,
+                Base:     10,
+                Value:    "7",
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    190,
+          Alias: &ast.Ident{
+            NamePos: 193,
+            NameEnd: 194,
+            Name:    "y",
+          },
+        },
+      },
+    },
+    From: &ast.From{
+      From:   195,
+      Source: &ast.SubQueryTableExpr{
+        Lparen: 200,
+        Rparen: 254,
+        Query:  &ast.Select{
+          Select:  201,
+          Results: []ast.SelectItem{
+            &ast.Alias{
+              Expr: &ast.CallExpr{
+                Rparen: 216,
+                Func:   &ast.Ident{
+                  NamePos: 208,
+                  NameEnd: 214,
+                  Name:    "SHA512",
+                },
+                Args: []ast.Arg{
+                  &ast.ExprArg{
+                    Expr: &ast.Ident{
+                      NamePos: 215,
+                      NameEnd: 216,
+                      Name:    "z",
+                    },
+                  },
+                },
+                Hint: &ast.Hint{
+                  Atmark:  218,
+                  Rbrace:  241,
+                  Records: []*ast.HintRecord{
+                    &ast.HintRecord{
+                      Key: &ast.Ident{
+                        NamePos: 220,
+                        NameEnd: 234,
+                        Name:    "DISABLE_INLINE",
+                      },
+                      Value: &ast.BoolLiteral{
+                        ValuePos: 237,
+                        Value:    true,
+                      },
+                    },
+                  },
+                },
+              },
+              As: &ast.AsAlias{
+                As:    243,
+                Alias: &ast.Ident{
+                  NamePos: 246,
+                  NameEnd: 247,
+                  Name:    "x",
+                },
+              },
+            },
+          },
+          From: &ast.From{
+            From:   248,
+            Source: &ast.TableName{
+              Table: &ast.Ident{
+                NamePos: 253,
+                NameEnd: 254,
+                Name:    "t",
+              },
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT SUBSTRING(CAST(x AS STRING), 2, 5) AS w, SUBSTRING(CAST(x AS STRING), 3, 7) AS y FROM (SELECT SHA512(z) @{DISABLE_INLINE=TRUE} AS x FROM t)
diff --git a/testdata/result/statement/select_with.sql.txt b/testdata/result/statement/select_with.sql.txt
new file mode 100644
index 0000000..58348b5
--- /dev/null
+++ b/testdata/result/statement/select_with.sql.txt
@@ -0,0 +1,112 @@
+--- select_with.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/operators#with_expression
+SELECT WITH(a AS '123',       -- a is '123'
+    b AS CONCAT(a, '456'),    -- b is '123456'
+    c AS '789',               -- c is '789'
+    CONCAT(b, c)) AS result   -- b + c is '123456789'
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  90,
+    Results: []ast.SelectItem{
+      &ast.Alias{
+        Expr: &ast.WithExpr{
+          With:   97,
+          Rparen: 241,
+          Vars:   []*ast.WithExprVar{
+            &ast.WithExprVar{
+              Name: &ast.Ident{
+                NamePos: 102,
+                NameEnd: 103,
+                Name:    "a",
+              },
+              Expr: &ast.StringLiteral{
+                ValuePos: 107,
+                ValueEnd: 112,
+                Value:    "123",
+              },
+            },
+            &ast.WithExprVar{
+              Name: &ast.Ident{
+                NamePos: 138,
+                NameEnd: 139,
+                Name:    "b",
+              },
+              Expr: &ast.CallExpr{
+                Rparen: 158,
+                Func:   &ast.Ident{
+                  NamePos: 143,
+                  NameEnd: 149,
+                  Name:    "CONCAT",
+                },
+                Args: []ast.Arg{
+                  &ast.ExprArg{
+                    Expr: &ast.Ident{
+                      NamePos: 150,
+                      NameEnd: 151,
+                      Name:    "a",
+                    },
+                  },
+                  &ast.ExprArg{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 153,
+                      ValueEnd: 158,
+                      Value:    "456",
+                    },
+                  },
+                },
+              },
+            },
+            &ast.WithExprVar{
+              Name: &ast.Ident{
+                NamePos: 185,
+                NameEnd: 186,
+                Name:    "c",
+              },
+              Expr: &ast.StringLiteral{
+                ValuePos: 190,
+                ValueEnd: 195,
+                Value:    "789",
+              },
+            },
+          },
+          Expr: &ast.CallExpr{
+            Rparen: 240,
+            Func:   &ast.Ident{
+              NamePos: 229,
+              NameEnd: 235,
+              Name:    "CONCAT",
+            },
+            Args: []ast.Arg{
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 236,
+                  NameEnd: 237,
+                  Name:    "b",
+                },
+              },
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 239,
+                  NameEnd: 240,
+                  Name:    "c",
+                },
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    243,
+          Alias: &ast.Ident{
+            NamePos: 246,
+            NameEnd: 252,
+            Name:    "result",
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT WITH(a AS "123", b AS CONCAT(a, "456"), c AS "789", CONCAT(b, c)) AS result