Repository: cloudspannerecosystem/memefish
Pull request #256 (2025-01-06 09:51:13)

Add `astcatalog` and use it in `gen-ast-pos`
This PR introduces a new internal utility package named `astcatalog`.
Package `astcatalog` provides a catalog of AST node types obtained from `ast/ast.go`.

This PR rewrites `gen-ast-pos` to use `astcatalog` instead of `regexp` matching, and this PR also fixes some errors in `ast/ast.go` docs.

- - -

Important changes for contributors:

- I decide to mark `ast.go` and `ast_*.go` as conventional. This means that these files are used for automatic generation (e.g., `gen-ast-pos`), and so we need to take care of the structures of these files.
  * Thus, I renamed `const.go` to `ast_const.go`.
- The conventions to be followed are written at the top of these files.



Patch:
diff --git a/ast/ast.go b/ast/ast.go
index c2cf618..5708a22 100644
--- a/ast/ast.go
+++ b/ast/ast.go
@@ -32,9 +32,20 @@
 //	(PosVar, NodeVar, NodeSliceVar, and BoolVar are derived by its struct definition.)
 package ast
 
-// This file must contain only AST definitions.
-// We use the following go:generate directive for generating pos.go. Thus, all AST definitions must have pos and end lines.
-//go:generate go run ../tools/gen-ast-pos/main.go -infile ast.go -outfile pos.go
+// NOTE: ast.go and ast_*.go are used for automatic generation, so these files are conventional.
+
+// NOTE: This file defines AST nodes and they are used for automatic generation,
+//       so this file is conventional.
+//
+// Conventions:
+//
+//   - Each node interface (except for Node) should have isXXX method (XXX must be a name of the interface itself).
+//   - `isXXX` methods should be defined after the interface definition
+//     and the receiver should be the non-pointer node struct type.
+//   - Each node struct should have pos and end comments.
+//   - Each node struct should have template lines in its doc comment.
+
+//go:generate go run ../tools/gen-ast-pos/main.go -astfile ast.go -constfile ast_const.go -outfile pos.go
 
 import (
 	"github.com/cloudspannerecosystem/memefish/token"
@@ -562,7 +573,7 @@ type BadNode struct {
 
 // BadStatement is a BadNode for Statement.
 //
-// {{.BadNode | sql}}
+//	{{.BadNode | sql}}
 type BadStatement struct {
 	// pos = BadNode.pos
 	// end = BadNode.end
@@ -572,7 +583,7 @@ type BadStatement struct {
 
 // BadQueryExpr is a BadNode for QueryExpr.
 //
-// {{.BadNode | sql}}
+//	{{.BadNode | sql}}
 type BadQueryExpr struct {
 	// pos = BadNode.pos
 	// end = BadNode.end
@@ -582,7 +593,7 @@ type BadQueryExpr struct {
 
 // BadExpr is a BadNode for Expr.
 //
-// {{.BadNode | sql}}
+//	{{.BadNode | sql}}
 type BadExpr struct {
 	// pos = BadNode.pos
 	// end = BadNode.end
@@ -592,7 +603,7 @@ type BadExpr struct {
 
 // BadType is a BadNode for Type.
 //
-// {{.BadNode | sql}}
+//	{{.BadNode | sql}}
 type BadType struct {
 	// pos = BadNode.pos
 	// end = BadNode.end
@@ -602,7 +613,7 @@ type BadType struct {
 
 // BadDDL is a BadNode for DDL.
 //
-// {{.BadNode | sql}}
+//	{{.BadNode | sql}}
 type BadDDL struct {
 	// pos = BadNode.pos
 	// end = BadNode.end
@@ -612,7 +623,7 @@ type BadDDL struct {
 
 // BadDML is a BadNode for DML.
 //
-// {{.BadNode | sql}}
+//	{{.BadNode | sql}}
 type BadDML struct {
 	// pos = BadNode.pos
 	// end = BadNode.end
@@ -768,6 +779,8 @@ type AsTypeName struct {
 }
 
 // FromQuery is FROM query expression node.
+//
+//	FROM {{.From | sql}}
 type FromQuery struct {
 	// pos = From.pos
 	// end = From.end
@@ -1163,7 +1176,11 @@ type Join struct {
 	Method      JoinMethod
 	Hint        *Hint // optional
 	Left, Right TableExpr
-	Cond        JoinCondition // nil when Op is CrossJoin, otherwise it must be set.
+
+	// nil when Op is CrossJoin
+	// optional when Right is PathTableExpr or Unnest
+	// otherwise it must be set.
+	Cond JoinCondition
 }
 
 // On is ON condition of JOIN expression.
@@ -1993,8 +2010,8 @@ type BracedConstructorFieldValue interface {
 	isBracedConstructorFieldValue()
 }
 
-func (*BracedConstructor) isBracedConstructorFieldValue()               {}
-func (*BracedConstructorFieldValueExpr) isBracedConstructorFieldValue() {}
+func (BracedConstructor) isBracedConstructorFieldValue()               {}
+func (BracedConstructorFieldValueExpr) isBracedConstructorFieldValue() {}
 
 // NewConstructor represents NEW operator which creates a protocol buffer using a parenthesized list of arguments.
 //
@@ -3019,7 +3036,6 @@ type CreateIndex struct {
 //	ON {{.TableName | sql}}({{.ColumnName | sql}})
 //	{{if .Where}}WHERE {{.Where | sql}}{{end}}
 //	{{.Options | sql}}
-
 type CreateVectorIndex struct {
 	// pos = Create
 	// end = Options.end
diff --git a/ast/const.go b/ast/ast_const.go
similarity index 93%
rename from ast/const.go
rename to ast/ast_const.go
index d72748d..eb62932 100644
--- a/ast/const.go
+++ b/ast/ast_const.go
@@ -1,5 +1,13 @@
 package ast
 
+// NOTE: This file defines constants used in AST nodes and they are used for automatic generation,
+//       so this file is conventional.
+//
+// Convention:
+//
+//   - Each const types should be defined as a string type.
+//   - Each value is defined as a string literal.
+
 // AllOrDistinct represents ALL or DISTINCT in SELECT or set operations, etc.
 // If it is optional, it may be an empty string, so handle it according to the context.
 type AllOrDistinct string
diff --git a/parser.go b/parser.go
index 68e81e4..f759615 100644
--- a/parser.go
+++ b/parser.go
@@ -953,18 +953,16 @@ func (p *Parser) parseTableExpr(toplevel bool) ast.TableExpr {
 		hint := p.tryParseHint()
 		right := p.parseSimpleTableExpr()
 
-		if op == ast.CrossJoin || op == ast.CommaJoin {
-			join = &ast.Join{
-				Op:     op,
-				Method: method,
-				Hint:   hint,
-				Left:   join,
-				Right:  right,
+		var cond ast.JoinCondition
+		if op != ast.CrossJoin && op != ast.CommaJoin {
+			switch right.(type) {
+			case *ast.PathTableExpr, *ast.Unnest:
+				cond = p.tryParseJoinCondition()
+			default:
+				cond = p.parseJoinCondition()
 			}
-			continue
 		}
 
-		cond := p.parseJoinCondition()
 		join = &ast.Join{
 			Op:     op,
 			Method: method,
@@ -1155,6 +1153,13 @@ func (p *Parser) parseJoinCondition() ast.JoinCondition {
 	panic(p.errorfAtToken(&p.Token, "expected token: ON, USING, but: %s", p.Token.Kind))
 }
 
+func (p *Parser) tryParseJoinCondition() ast.JoinCondition {
+	if p.Token.Kind != "ON" && p.Token.Kind != "USING" {
+		return nil
+	}
+	return p.parseJoinCondition()
+}
+
 func (p *Parser) parseOn() *ast.On {
 	pos := p.expect("ON").Pos
 	expr := p.parseExpr()
@@ -2229,6 +2234,10 @@ func (p *Parser) lookaheadWithExprVar() bool {
 		p.Lexer = lexer
 	}()
 
+	if p.Token.Kind != token.TokenIdent {
+		return false
+	}
+
 	p.parseIdent()
 	return p.Token.Kind == "AS"
 }
diff --git a/tools/astcatalog/main.go b/tools/astcatalog/main.go
new file mode 100644
index 0000000..bdaac2a
--- /dev/null
+++ b/tools/astcatalog/main.go
@@ -0,0 +1,46 @@
+package main
+
+import (
+	"flag"
+	"fmt"
+	"log"
+
+	"github.com/MakeNowJust/heredoc/v2"
+	"github.com/cloudspannerecosystem/memefish/tools/util/astcatalog"
+	"github.com/k0kubun/pp/v3"
+)
+
+var usage = heredoc.Doc(`
+	Usage of tools/astcatalog
+
+	An utility to show the AST catalog.
+
+	Example:
+
+	  $ go run ./tools/astcatalog/main.go -astfile ast/ast.go -constfile ast/ast_const.go
+	    Print the AST catalog of ast/ast.go and ast/ast_const.go.
+
+	Flags:
+`)
+
+var (
+	astfile   = flag.String("astfile", "ast/ast.go", "path to ast/ast.go")
+	constfile = flag.String("constfile", "ast/ast_const.go", "path to ast/ast_const.go")
+)
+
+func main() {
+	flag.Usage = func() {
+		fmt.Print(usage)
+		flag.PrintDefaults()
+	}
+
+	flag.Parse()
+
+	catalog, err := astcatalog.Load(*astfile, *constfile)
+	if err != nil {
+		log.Fatalf("failed to load: %v", err)
+	}
+	pprinter := pp.New()
+	pprinter.SetOmitEmpty(true)
+	_, _ = pprinter.Println(catalog)
+}
diff --git a/tools/gen-ast-pos/main.go b/tools/gen-ast-pos/main.go
index 2bcee65..b146e41 100644
--- a/tools/gen-ast-pos/main.go
+++ b/tools/gen-ast-pos/main.go
@@ -6,11 +6,11 @@ import (
 	"fmt"
 	"log"
 	"os"
-	"regexp"
-	"strings"
+	"sort"
 	"unicode"
 
 	"github.com/MakeNowJust/heredoc/v2"
+	"github.com/cloudspannerecosystem/memefish/tools/util/astcatalog"
 	"github.com/cloudspannerecosystem/memefish/tools/util/poslang"
 )
 
@@ -22,7 +22,7 @@ var (
 
 		Example:
 
-		  $ go run ./tools/gen-ast-pos/main.go -infile ast/ast.go
+		  $ go run ./tools/gen-ast-pos/main.go -astfile ast/ast.go -constfile ast/ast_const.go
 		        Print the generated ast/pos.go to stdout.
 
 		Flags:
@@ -39,21 +39,11 @@ var (
 )
 
 var (
-	infile  = flag.String("infile", "", "input filename")
-	outfile = flag.String("outfile", "", "output filename (if it is not specified, the result is printed to stdout.)")
+	astfile   = flag.String("astfile", "ast/ast.go", "path to ast/ast.go")
+	constfile = flag.String("constfile", "ast/ast_const.go", "path to ast/ast_const.go")
+	outfile   = flag.String("outfile", "", "output filename (if it is not specified, the result is printed to stdout.)")
 )
 
-var (
-	reNameLine = regexp.MustCompile(`^\s*type\s+(\w+)\s+struct\s*\{`)
-	rePosLine  = regexp.MustCompile(`^\s*//\s*pos\s*=\s*(.*)`)
-	reEndLine  = regexp.MustCompile(`^\s*//\s*end\s*=\s*(.*)`)
-)
-
-type astNode struct {
-	name             string
-	posExpr, endExpr poslang.PosExpr
-}
-
 func main() {
 	flag.Usage = func() {
 		fmt.Print(usage)
@@ -62,59 +52,42 @@ func main() {
 
 	flag.Parse()
 
-	source, err := os.ReadFile(*infile)
+	catalog, err := astcatalog.Load(*astfile, *constfile)
 	if err != nil {
 		log.Fatal(err)
 	}
 
-	var nodes []*astNode
-
-	for _, line := range strings.Split(string(source), "\n") {
-		if m := reNameLine.FindStringSubmatch(line); m != nil {
-			name := m[1]
-			nodes = append(nodes, &astNode{name: name})
-			continue
-		}
-
-		if m := rePosLine.FindStringSubmatch(line); m != nil {
-			e := m[1]
-			expr, err := poslang.Parse(e)
-			if err != nil {
-				log.Printf("Error at node %s, pos = %s", nodes[len(nodes)-1].name, e)
-				log.Fatal(err)
-			}
-			nodes[len(nodes)-1].posExpr = expr
-			continue
-		}
-
-		if m := reEndLine.FindStringSubmatch(line); m != nil {
-			e := m[1]
-			expr, err := poslang.Parse(e)
-			if err != nil {
-				log.Printf("Error at node %s, end = %s", nodes[len(nodes)-1].name, e)
-				log.Fatal(err)
-			}
-			nodes[len(nodes)-1].endExpr = expr
-			continue
-		}
+	structs := make([]*astcatalog.NodeStructDef, 0, len(catalog.Structs))
+	for _, structDef := range catalog.Structs {
+		structs = append(structs, structDef)
 	}
+	sort.Slice(structs, func(i, j int) bool {
+		return structs[i].SourcePos < structs[j].SourcePos
+	})
 
 	var buffer bytes.Buffer
 	buffer.WriteString(prologue)
 
-	for _, node := range nodes {
-		x := string(unicode.ToLower(rune(node.name[0])))
-		if node.posExpr == nil || node.endExpr == nil {
-			log.Fatalf("pos/end is not defined: node %s", node.name)
+	for _, structDef := range structs {
+		x := string(unicode.ToLower(rune(structDef.Name[0])))
+
+		posExpr, err := poslang.Parse(structDef.Pos)
+		if err != nil {
+			log.Fatalf("error on parsing pos: %v", err)
+		}
+
+		endExpr, err := poslang.Parse(structDef.End)
+		if err != nil {
+			log.Fatalf("error on parsing pos: %v", err)
 		}
 
 		fmt.Fprintln(&buffer)
-		fmt.Fprintf(&buffer, "func (%s *%s) Pos() token.Pos {\n", x, node.name)
-		fmt.Fprintf(&buffer, "\treturn %s\n", node.posExpr.PosExprToGo(x))
+		fmt.Fprintf(&buffer, "func (%s *%s) Pos() token.Pos {\n", x, structDef.Name)
+		fmt.Fprintf(&buffer, "\treturn %s\n", posExpr.PosExprToGo(x))
 		fmt.Fprintf(&buffer, "}\n")
 		fmt.Fprintln(&buffer)
-		fmt.Fprintf(&buffer, "func (%s *%s) End() token.Pos {\n", x, node.name)
-		fmt.Fprintf(&buffer, "\treturn %s\n", node.endExpr.PosExprToGo(x))
+		fmt.Fprintf(&buffer, "func (%s *%s) End() token.Pos {\n", x, structDef.Name)
+		fmt.Fprintf(&buffer, "\treturn %s\n", endExpr.PosExprToGo(x))
 		fmt.Fprintf(&buffer, "}\n")
 	}
 
diff --git a/tools/parse/main.go b/tools/parse/main.go
index f3a3c4f..0667894 100644
--- a/tools/parse/main.go
+++ b/tools/parse/main.go
@@ -11,11 +11,12 @@ import (
 	"unicode"
 
 	"github.com/MakeNowJust/heredoc/v2"
+	"github.com/k0kubun/pp/v3"
+
 	"github.com/cloudspannerecosystem/memefish"
 	"github.com/cloudspannerecosystem/memefish/ast"
 	"github.com/cloudspannerecosystem/memefish/token"
 	"github.com/cloudspannerecosystem/memefish/tools/util/poslang"
-	"github.com/k0kubun/pp/v3"
 )
 
 var usage = heredoc.Doc(`
@@ -29,6 +30,10 @@ var usage = heredoc.Doc(`
 	        Show the parse result of "SELECT 1 AS X".
 	  $ go run ./tools/parse/main.go -mode expr "(SELECT 1) + 2"
 	        Parse "(SELECT 1) + 2" on the expression mode.
+	  $ go run ./tools/parse/main.go -mode expr --output unparse "(SELECT 1) + 2"
+	        Parse "(SELECT 1) + 2" on the expression mode and show only unparsed SQL.
+	  $ go run ./tools/parse/main.go -mode expr --output ast "(SELECT 1) + 2"
+	        Parse "(SELECT 1) + 2" on the expression mode and show only AST.
 	  $ go run ./tools/parse/main.go -pos "Query.end" "SELECT 1 AS x"
 	        Evaluate the POS expression "Query.end" on "SELECT 1 AS x"
 	  $ go run ./tools/parse/main.go -pos "As.end" -dig "Query.Results.0" "SELECT 1 AS x"
@@ -42,6 +47,7 @@ var (
 	logging = flag.Bool("logging", false, "enable log (default: false)")
 	dig     = flag.String("dig", "", "digging the result node before printing")
 	pos     = flag.String("pos", "", "POS expression")
+	output  = flag.String("output", "ast,unparse", "comma separated outputs: {ast|unparse}")
 )
 
 func main() {
@@ -124,16 +130,22 @@ func main() {
 		}
 	}
 
-	fmt.Println("--- AST")
-	pprinter := pp.New()
-	pprinter.SetOmitEmpty(true)
-	_, _ = pprinter.Println(node)
-	fmt.Println()
-
-	fmt.Println("--- SQL")
-	fmt.Println(node.SQL())
+	for _, elem := range strings.Split(*output, ",") {
+		switch elem {
+		case "ast":
+			fmt.Println("--- AST")
+			pprinter := pp.New()
+			pprinter.SetOmitEmpty(true)
+			_, _ = pprinter.Println(node)
+		case "unparse":
+			fmt.Println("--- SQL")
+			fmt.Println(node.SQL())
+		}
+	}
 
 	if *pos != "" {
+		fmt.Println()
+
 		fmt.Println("--- POS")
 
 		expr, err := poslang.Parse(*pos)
diff --git a/tools/util/astcatalog/catalog.go b/tools/util/astcatalog/catalog.go
new file mode 100644
index 0000000..f6d7faa
--- /dev/null
+++ b/tools/util/astcatalog/catalog.go
@@ -0,0 +1,94 @@
+package astcatalog
+
+import (
+	"go/token"
+)
+
+// Catalog is a catalog of AST types.
+type Catalog struct {
+	Structs    map[NodeStructType]*NodeStructDef
+	Interfaces map[NodeInterfaceType]*NodeInterfaceDef
+	Consts     map[ConstType]*ConstDef
+}
+
+// NodeStructDef is a definition of node structs in ast/ast.go.
+type NodeStructDef struct {
+	SourcePos  token.Pos
+	Name       string
+	Doc        string
+	Tmpl       string
+	Pos, End   string
+	Fields     []*FieldDef
+	Implements []NodeInterfaceType
+}
+
+// FieldDef is a field definition of node structs in ast/ast.go.
+type FieldDef struct {
+	Name    string
+	Type    Type
+	Comment string
+}
+
+// NodeInterfaceDef is a definition of node interfaces in ast/ast.go.
+type NodeInterfaceDef struct {
+	SourcePos   token.Pos
+	Name        string
+	Implemented []NodeStructType
+}
+
+// ConstDef is a definition of const types in ast/ast_const.go
+type ConstDef struct {
+	SourcePos token.Pos
+	Name      string
+	Values    []*ConstValueDef
+}
+
+// ConstValueDef is a value definition of const types in ast/ast_const.go.
+type ConstValueDef struct {
+	Name  string
+	Value string
+}
+
+// Type represents types used in Catalog.
+type Type interface {
+	isType()
+}
+
+func (SliceType) isType()         {}
+func (PointerType) isType()       {}
+func (NodeStructType) isType()    {}
+func (NodeInterfaceType) isType() {}
+func (PrimitiveType) isType()     {}
+func (ConstType) isType()         {}
+
+// SliceType is a slice type.
+type SliceType struct {
+	Type Type
+}
+
+// PointerType is a pointer type.
+type PointerType struct {
+	Type Type
+}
+
+// NodeStructType is a type name of node structs defined in ast/ast.go.
+type NodeStructType string
+
+// NodeInterfaceType is a type name of node interfaces defined in ast/ast.go.
+type NodeInterfaceType string
+
+// ConstType is a type name of const types defined in ast/ast_const.go.
+type ConstType string
+
+// PrimitiveType is a type name which is neither a node pointer, a node interface, nor a const types.
+type PrimitiveType string
+
+// PrimitiveType values.
+const (
+	BoolType       PrimitiveType = "bool"
+	ByteType       PrimitiveType = "byte"
+	IntType        PrimitiveType = "int"
+	StringType     PrimitiveType = "string"
+	TokenPosType   PrimitiveType = "token.Pos"
+	TokenTokenType PrimitiveType = "token.Token"
+)
diff --git a/tools/util/astcatalog/load.go b/tools/util/astcatalog/load.go
new file mode 100644
index 0000000..16f96d8
--- /dev/null
+++ b/tools/util/astcatalog/load.go
@@ -0,0 +1,299 @@
+package astcatalog
+
+import (
+	"fmt"
+	"go/ast"
+	"go/parser"
+	"go/token"
+	"regexp"
+	"strings"
+)
+
+// Load loads a catalog from the given AST files.
+func Load(astFilename, astConstFilename string) (*Catalog, error) {
+	fset := token.NewFileSet()
+
+	consts, err := loadConsts(fset, astConstFilename)
+	if err != nil {
+		return nil, err
+	}
+
+	structs, interfaces, err := loadStructs(fset, astFilename, consts)
+	if err != nil {
+		return nil, err
+	}
+
+	catalog := &Catalog{
+		Structs:    structs,
+		Interfaces: interfaces,
+		Consts:     consts,
+	}
+	return catalog, nil
+}
+
+func loadConsts(fset *token.FileSet, filename string) (map[ConstType]*ConstDef, error) {
+	f, err := parser.ParseFile(fset, filename, nil, parser.ParseComments)
+	if err != nil {
+		return nil, fmt.Errorf("failed to parse '%s': %w", filename, err)
+	}
+
+	consts := make(map[ConstType]*ConstDef)
+
+	for _, decl := range f.Decls {
+		switch d := decl.(type) {
+		case *ast.GenDecl:
+			for _, spec := range d.Specs {
+				switch s := spec.(type) {
+				case *ast.TypeSpec:
+					name := ConstType(s.Name.Name)
+					if _, ok := consts[name]; ok {
+						return nil, fmt.Errorf("duplicated const: %s", name)
+					}
+
+					consts[name] = &ConstDef{
+						SourcePos: d.Pos(),
+						Name:      s.Name.Name,
+					}
+				case *ast.ValueSpec:
+					if s.Type == nil {
+						return nil, fmt.Errorf("unexpected value spec: %#v", s)
+					}
+					ty, err := loadType(s.Type, nil, consts)
+					if err != nil {
+						return nil, err
+					}
+					name, ok := ty.(ConstType)
+					if !ok {
+						return nil, fmt.Errorf("unexpected type: %#v", s.Type)
+					}
+
+					constDef, ok := consts[name]
+					if !ok {
+						return nil, fmt.Errorf("unknown const: %s", name)
+					}
+
+					if len(s.Values) != 1 {
+						return nil, fmt.Errorf("unexpected values: %#v", s.Values)
+					}
+					lit, ok := s.Values[0].(*ast.BasicLit)
+					if !(ok && lit.Kind == token.STRING) {
+						return nil, fmt.Errorf("unexpected value: %#v", s.Values[0])
+					}
+					v := strings.Trim(lit.Value, "\"")
+
+					for _, name := range s.Names {
+						constDef.Values = append(constDef.Values, &ConstValueDef{
+							Name:  name.Name,
+							Value: v,
+						})
+					}
+				}
+			}
+		}
+	}
+
+	return consts, nil
+}
+
+// Regular expressions to extract pos/end and template comments.
+var (
+	rePosLine   = regexp.MustCompile(`(?m)^\s*pos\s*=\s*(.*)`)
+	reEndLine   = regexp.MustCompile(`(?m)^\s*end\s*=\s*(.*)`)
+	reTmplLines = regexp.MustCompile(`(?m)((?:^\t.*\n)+)+`)
+)
+
+func loadStructs(fset *token.FileSet, filename string, consts map[ConstType]*ConstDef) (map[NodeStructType]*NodeStructDef, map[NodeInterfaceType]*NodeInterfaceDef, error) {
+	f, err := parser.ParseFile(fset, filename, nil, parser.ParseComments)
+	if err != nil {
+		return nil, nil, fmt.Errorf("failed to parse '%s': %w", filename, err)
+	}
+
+	structs := make(map[NodeStructType]*NodeStructDef)
+	interfaces := make(map[NodeInterfaceType]*NodeInterfaceDef)
+
+	commentMap := ast.NewCommentMap(fset, f, f.Comments)
+
+	for _, decl := range f.Decls {
+		switch d := decl.(type) {
+		case *ast.GenDecl:
+			for _, spec := range d.Specs {
+				switch s := spec.(type) {
+				case *ast.TypeSpec:
+					switch t := s.Type.(type) {
+					case *ast.StructType:
+						name := NodeStructType(s.Name.Name)
+						if _, ok := structs[name]; !ok {
+							structs[name] = &NodeStructDef{
+								Name: s.Name.Name,
+							}
+						}
+
+						structDef := structs[name]
+						structDef.SourcePos = s.Pos()
+						structDef.Doc = d.Doc.Text()
+
+						if m := reTmplLines.FindAllStringSubmatch(structDef.Doc, -1); m != nil {
+							structDef.Tmpl = m[len(m)-1][0]
+						} else {
+							return nil, nil, fmt.Errorf("no template found: %s", name)
+						}
+
+						comments := commentMap.Filter(t).Comments()
+						if len(comments) == 0 {
+							return nil, nil, fmt.Errorf("no pos/end comment found: %s", name)
+						}
+
+						// We assume the first comment group in the struct should a pos/end comment.
+						posComment := comments[0].Text()
+						if m := rePosLine.FindStringSubmatch(posComment); m != nil {
+							structDef.Pos = m[1]
+						} else {
+							return nil, nil, fmt.Errorf("no pos comment found: %s", name)
+						}
+						if m := reEndLine.FindStringSubmatch(posComment); m != nil {
+							structDef.End = m[1]
+						} else {
+							return nil, nil, fmt.Errorf("no end coment found: %s", name)
+						}
+
+						for _, f := range t.Fields.List {
+							ty, err := loadType(f.Type, interfaces, consts)
+							if err != nil {
+								return nil, nil, err
+							}
+
+							comment := ""
+							comments := commentMap.Filter(f).Comments()
+							for _, c := range comments {
+								if f.End() < c.Pos() {
+									comment = c.Text()
+									break
+								}
+							}
+							for _, name := range f.Names {
+								structDef.Fields = append(structDef.Fields, &FieldDef{
+									Name:    name.Name,
+									Type:    ty,
+									Comment: comment,
+								})
+							}
+						}
+					case *ast.InterfaceType:
+						name := NodeInterfaceType(s.Name.Name)
+						if _, ok := interfaces[name]; ok {
+							return nil, nil, fmt.Errorf("duplicated interface: %s", name)
+						}
+
+						// Node interface is special, so we skip it.
+						if name == "Node" {
+							continue
+						}
+
+						interfaces[name] = &NodeInterfaceDef{
+							SourcePos: s.Pos(),
+							Name:      string(name),
+						}
+					default:
+						return nil, nil, fmt.Errorf("unexpected spec: %#v", t)
+					}
+				}
+			}
+
+		case *ast.FuncDecl:
+			if d.Recv == nil || len(d.Recv.List) != 1 {
+				return nil, nil, fmt.Errorf("unexpected func decl: %#v", d)
+			}
+
+			recv, err := loadType(d.Recv.List[0].Type, interfaces, consts)
+			if err != nil {
+				return nil, nil, err
+			}
+
+			structName, ok := recv.(NodeStructType)
+			if !ok {
+				return nil, nil, fmt.Errorf("unexpected receiver type: %#v", recv)
+			}
+
+			funcName := d.Name.Name
+			cutName, found := strings.CutPrefix(funcName, "is")
+			if !found {
+				return nil, nil, fmt.Errorf("unexpected func name: %s", funcName)
+			}
+
+			interfaceName := NodeInterfaceType(cutName)
+			interfaceDef, ok := interfaces[interfaceName]
+			if !ok {
+				return nil, nil, fmt.Errorf("unknown interface: %s", interfaceName)
+			}
+
+			interfaceDef.Implemented = append(interfaceDef.Implemented, structName)
+
+			if _, ok := structs[structName]; !ok {
+				structs[structName] = &NodeStructDef{
+					Name: string(structName),
+				}
+			}
+
+			structDef := structs[structName]
+			structDef.Implements = append(structDef.Implements, interfaceName)
+		}
+	}
+
+	return structs, interfaces, nil
+}
+
+func loadType(t ast.Expr, interfaces map[NodeInterfaceType]*NodeInterfaceDef, consts map[ConstType]*ConstDef) (Type, error) {
+	switch t := t.(type) {
+	case *ast.Ident:
+		switch t.Name {
+		case "bool":
+			return BoolType, nil
+		case "byte":
+			return ByteType, nil
+		case "int":
+			return IntType, nil
+		case "string":
+			return StringType, nil
+		default:
+			if _, ok := consts[ConstType(t.Name)]; ok {
+				return ConstType(t.Name), nil
+			}
+			if _, ok := interfaces[NodeInterfaceType(t.Name)]; ok {
+				return NodeInterfaceType(t.Name), nil
+			}
+			return NodeStructType(t.Name), nil
+		}
+	case *ast.SelectorExpr:
+		if x, ok := t.X.(*ast.Ident); !(ok && x.Name == "token") {
+			return nil, fmt.Errorf("unexpected selector expr: %#v", t)
+		}
+		switch t.Sel.Name {
+		case "Pos":
+			return TokenPosType, nil
+		case "Token":
+			return TokenTokenType, nil
+		default:
+			return nil, fmt.Errorf("unexpected selector name: %#v", t)
+		}
+	case *ast.StarExpr:
+		ty, err := loadType(t.X, interfaces, consts)
+		if err != nil {
+			return nil, err
+		}
+
+		return PointerType{Type: ty}, nil
+	case *ast.ArrayType:
+		if t.Len != nil {
+			return nil, fmt.Errorf("unexpected array type: %#v", t)
+		}
+
+		ty, err := loadType(t.Elt, interfaces, consts)
+		if err != nil {
+			return nil, err
+		}
+
+		return SliceType{Type: ty}, nil
+	default:
+		return nil, fmt.Errorf("unexpected type: %#v", t)
+	}
+}


Test patch:
diff --git a/ast/ast_test.go b/ast/ast_test.go
deleted file mode 100644
index cc8ee6d..0000000
--- a/ast/ast_test.go
+++ /dev/null
@@ -1,214 +0,0 @@
-package ast
-
-import (
-	"testing"
-)
-
-func TestStatement(t *testing.T) {
-	Statement(&QueryStatement{}).isStatement()
-	Statement(&CreateDatabase{}).isStatement()
-	Statement(&AlterDatabase{}).isStatement()
-	Statement(&CreateTable{}).isStatement()
-	Statement(&AlterTable{}).isStatement()
-	Statement(&DropTable{}).isStatement()
-	Statement(&CreateIndex{}).isStatement()
-	Statement(&AlterIndex{}).isStatement()
-	Statement(&DropIndex{}).isStatement()
-	Statement(&CreateView{}).isStatement()
-	Statement(&DropView{}).isStatement()
-	Statement(&CreateChangeStream{}).isStatement()
-	Statement(&AlterChangeStream{}).isStatement()
-	Statement(&DropChangeStream{}).isStatement()
-	Statement(&CreateRole{}).isStatement()
-	Statement(&DropRole{}).isStatement()
-	Statement(&Grant{}).isStatement()
-	Statement(&Revoke{}).isStatement()
-	Statement(&CreateSequence{}).isStatement()
-	Statement(&AlterSequence{}).isStatement()
-	Statement(&DropSequence{}).isStatement()
-	Statement(&CreateVectorIndex{}).isStatement()
-	Statement(&DropVectorIndex{}).isStatement()
-	Statement(&Insert{}).isStatement()
-	Statement(&Delete{}).isStatement()
-	Statement(&Update{}).isStatement()
-}
-
-func TestQueryExpr(t *testing.T) {
-	QueryExpr(&Select{}).isQueryExpr()
-	QueryExpr(&SubQuery{}).isQueryExpr()
-	QueryExpr(&CompoundQuery{}).isQueryExpr()
-}
-
-func TestSelectItem(t *testing.T) {
-	SelectItem(&Star{}).isSelectItem()
-	SelectItem(&DotStar{}).isSelectItem()
-	SelectItem(&Alias{}).isSelectItem()
-	SelectItem(&ExprSelectItem{}).isSelectItem()
-}
-
-func TestSelectAs(t *testing.T) {
-	SelectAs(&AsStruct{}).isSelectAs()
-	SelectAs(&AsValue{}).isSelectAs()
-	SelectAs(&AsTypeName{}).isSelectAs()
-}
-
-func TestTableExpr(t *testing.T) {
-	TableExpr(&Unnest{}).isTableExpr()
-	TableExpr(&TableName{}).isTableExpr()
-	TableExpr(&SubQueryTableExpr{}).isTableExpr()
-	TableExpr(&ParenTableExpr{}).isTableExpr()
-	TableExpr(&Join{}).isTableExpr()
-}
-
-func TestJoinCondition(t *testing.T) {
-	JoinCondition(&On{}).isJoinCondition()
-	JoinCondition(&Using{}).isJoinCondition()
-}
-
-func TestExpr(t *testing.T) {
-	Expr(&BinaryExpr{}).isExpr()
-	Expr(&UnaryExpr{}).isExpr()
-	Expr(&InExpr{}).isExpr()
-	Expr(&IsNullExpr{}).isExpr()
-	Expr(&IsBoolExpr{}).isExpr()
-	Expr(&BetweenExpr{}).isExpr()
-	Expr(&SelectorExpr{}).isExpr()
-	Expr(&IndexExpr{}).isExpr()
-	Expr(&CallExpr{}).isExpr()
-	Expr(&CountStarExpr{}).isExpr()
-	Expr(&CastExpr{}).isExpr()
-	Expr(&ExtractExpr{}).isExpr()
-	Expr(&CaseExpr{}).isExpr()
-	Expr(&ParenExpr{}).isExpr()
-	Expr(&ScalarSubQuery{}).isExpr()
-	Expr(&ArraySubQuery{}).isExpr()
-	Expr(&ExistsSubQuery{}).isExpr()
-	Expr(&Param{}).isExpr()
-	Expr(&Ident{}).isExpr()
-	Expr(&Path{}).isExpr()
-	Expr(&ArrayLiteral{}).isExpr()
-	Expr(&TypedStructLiteral{}).isExpr()
-	Expr(&NullLiteral{}).isExpr()
-	Expr(&BoolLiteral{}).isExpr()
-	Expr(&IntLiteral{}).isExpr()
-	Expr(&FloatLiteral{}).isExpr()
-	Expr(&StringLiteral{}).isExpr()
-	Expr(&BytesLiteral{}).isExpr()
-	Expr(&DateLiteral{}).isExpr()
-	Expr(&TimestampLiteral{}).isExpr()
-	Expr(&NumericLiteral{}).isExpr()
-}
-
-func TestArg(t *testing.T) {
-	Arg(&IntervalArg{}).isArg()
-	Arg(&ExprArg{}).isArg()
-	Arg(&SequenceArg{}).isArg()
-}
-
-func TestInCondition(t *testing.T) {
-	InCondition(&UnnestInCondition{}).isInCondition()
-	InCondition(&SubQueryInCondition{}).isInCondition()
-	InCondition(&ValuesInCondition{}).isInCondition()
-}
-
-func TestType(t *testing.T) {
-	Type(&SimpleType{}).isType()
-	Type(&ArrayType{}).isType()
-	Type(&StructType{}).isType()
-}
-
-func TestIntValue(t *testing.T) {
-	IntValue(&Param{}).isIntValue()
-	IntValue(&IntLiteral{}).isIntValue()
-	IntValue(&CastIntValue{}).isIntValue()
-}
-
-func TestNumValue(t *testing.T) {
-	NumValue(&Param{}).isNumValue()
-	NumValue(&IntLiteral{}).isNumValue()
-	NumValue(&FloatLiteral{}).isNumValue()
-	NumValue(&CastNumValue{}).isNumValue()
-}
-
-func TestStringValue(t *testing.T) {
-	StringValue(&Param{}).isStringValue()
-	StringValue(&StringLiteral{}).isStringValue()
-}
-
-func TestDDL(t *testing.T) {
-	DDL(&CreateDatabase{}).isDDL()
-	DDL(&AlterDatabase{}).isDDL()
-	DDL(&CreateTable{}).isDDL()
-	DDL(&AlterTable{}).isDDL()
-	DDL(&DropTable{}).isDDL()
-	DDL(&CreateIndex{}).isDDL()
-	DDL(&AlterIndex{}).isDDL()
-	DDL(&DropIndex{}).isDDL()
-	DDL(&CreateSearchIndex{}).isDDL()
-	DDL(&DropSearchIndex{}).isDDL()
-	DDL(&AlterSearchIndex{}).isDDL()
-	DDL(&CreateView{}).isDDL()
-	DDL(&DropView{}).isDDL()
-	DDL(&CreateChangeStream{}).isDDL()
-	DDL(&AlterChangeStream{}).isDDL()
-	DDL(&DropChangeStream{}).isDDL()
-	DDL(&CreateRole{}).isDDL()
-	DDL(&DropRole{}).isDDL()
-	DDL(&Grant{}).isDDL()
-	DDL(&Revoke{}).isDDL()
-	DDL(&CreateSequence{}).isDDL()
-	DDL(&AlterSequence{}).isDDL()
-	DDL(&DropSequence{}).isDDL()
-	DDL(&CreateVectorIndex{}).isDDL()
-	DDL(&DropVectorIndex{}).isDDL()
-}
-
-func TestConstraint(t *testing.T) {
-	Constraint(&ForeignKey{}).isConstraint()
-	Constraint(&Check{}).isConstraint()
-}
-
-func TestTableAlteration(t *testing.T) {
-	TableAlteration(&AddColumn{}).isTableAlteration()
-	TableAlteration(&AddTableConstraint{}).isTableAlteration()
-	TableAlteration(&DropColumn{}).isTableAlteration()
-	TableAlteration(&DropConstraint{}).isTableAlteration()
-	TableAlteration(&SetOnDelete{}).isTableAlteration()
-	TableAlteration(&AlterColumn{}).isTableAlteration()
-}
-
-func TestPrivilege(t *testing.T) {
-	Privilege(&PrivilegeOnTable{}).isPrivilege()
-	Privilege(&SelectPrivilegeOnView{}).isPrivilege()
-	Privilege(&ExecutePrivilegeOnTableFunction{}).isPrivilege()
-	Privilege(&RolePrivilege{}).isPrivilege()
-}
-
-func TestTablePrivilege(t *testing.T) {
-	TablePrivilege(&SelectPrivilege{}).isTablePrivilege()
-	TablePrivilege(&InsertPrivilege{}).isTablePrivilege()
-	TablePrivilege(&UpdatePrivilege{}).isTablePrivilege()
-	TablePrivilege(&DeletePrivilege{}).isTablePrivilege()
-}
-
-func TestSchemaType(t *testing.T) {
-	SchemaType(&ScalarSchemaType{}).isSchemaType()
-	SchemaType(&SizedSchemaType{}).isSchemaType()
-	SchemaType(&ArraySchemaType{}).isSchemaType()
-}
-
-func TestIndexAlteration(t *testing.T) {
-	IndexAlteration(&AddStoredColumn{}).isIndexAlteration()
-	IndexAlteration(&DropStoredColumn{}).isIndexAlteration()
-}
-
-func TestDML(t *testing.T) {
-	DML(&Insert{}).isDML()
-	DML(&Delete{}).isDML()
-	DML(&Update{}).isDML()
-}
-
-func TestInsertInput(t *testing.T) {
-	InsertInput(&ValuesInput{}).isInsertInput()
-	InsertInput(&SubQueryInput{}).isInsertInput()
-}
diff --git a/testdata/input/query/select_from_inner_join_path_table_expr.sql b/testdata/input/query/select_from_inner_join_path_table_expr.sql
new file mode 100644
index 0000000..c2e250c
--- /dev/null
+++ b/testdata/input/query/select_from_inner_join_path_table_expr.sql
@@ -0,0 +1,13 @@
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT A.name, item
+FROM
+  UNNEST(
+    [
+      STRUCT(
+        'first' AS name,
+        [1, 2, 3, 4] AS items),
+      STRUCT(
+          'second' AS name,
+        [] AS items)]) AS A
+    INNER JOIN
+  A.items AS item
diff --git a/testdata/input/query/select_from_join_unnest.sql b/testdata/input/query/select_from_join_unnest.sql
new file mode 100644
index 0000000..5db9305
--- /dev/null
+++ b/testdata/input/query/select_from_join_unnest.sql
@@ -0,0 +1,12 @@
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT *
+FROM
+  Roster
+    JOIN
+  UNNEST(
+      ARRAY(
+        SELECT AS STRUCT *
+      FROM PlayerStats
+      WHERE PlayerStats.OpponentID = Roster.SchoolID
+    )) AS PlayerMatches
+  ON PlayerMatches.LastName = 'Buchanan'
diff --git a/testdata/input/query/select_from_left_join_path_table_expr.sql b/testdata/input/query/select_from_left_join_path_table_expr.sql
new file mode 100644
index 0000000..e6aab19
--- /dev/null
+++ b/testdata/input/query/select_from_left_join_path_table_expr.sql
@@ -0,0 +1,13 @@
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
+FROM
+  UNNEST(
+    [
+      STRUCT(
+        'first' AS name,
+        [1, 2, 3, 4] AS items),
+      STRUCT(
+          'second' AS name,
+        [] AS items)]) AS A
+    LEFT JOIN
+  A.items AS item
diff --git a/testdata/input/query/select_from_where_order_by_with.sql b/testdata/input/query/select_from_where_order_by_with.sql
new file mode 100644
index 0000000..e81cd9f
--- /dev/null
+++ b/testdata/input/query/select_from_where_order_by_with.sql
@@ -0,0 +1,12 @@
+-- https://cloud.google.com/spanner/docs/full-text-search/ranked-search#score_multiple_columns
+SELECT AlbumId
+FROM Albums
+WHERE SEARCH(Title_Tokens, @p1) AND SEARCH(Studio_Tokens, @p2)
+ORDER BY WITH(
+  TitleScore AS SCORE(Title_Tokens, @p1) * @titleweight,
+  StudioScore AS SCORE(Studio_Tokens, @p2) * @studioweight,
+  DaysOld AS (UNIX_MICROS(CURRENT_TIMESTAMP()) - ReleaseTimestamp) / 8.64e+10,
+  FreshnessBoost AS (1 + @freshnessweight * GREATEST(0, 30 - DaysOld) / 30),
+  PopularityBoost AS (1 + IF(HasGrammy, @grammyweight, 0)),
+  (TitleScore + StudioScore) * FreshnessBoost * PopularityBoost)
+LIMIT 2
\ No newline at end of file
diff --git a/testdata/result/query/select_from_inner_join_path_table_expr.sql.txt b/testdata/result/query/select_from_inner_join_path_table_expr.sql.txt
new file mode 100644
index 0000000..bd1a9ff
--- /dev/null
+++ b/testdata/result/query/select_from_inner_join_path_table_expr.sql.txt
@@ -0,0 +1,196 @@
+--- select_from_inner_join_path_table_expr.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT A.name, item
+FROM
+  UNNEST(
+    [
+      STRUCT(
+        'first' AS name,
+        [1, 2, 3, 4] AS items),
+      STRUCT(
+          'second' AS name,
+        [] AS items)]) AS A
+    INNER JOIN
+  A.items AS item
+
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  93,
+    Results: []ast.SelectItem{
+      &ast.ExprSelectItem{
+        Expr: &ast.Path{
+          Idents: []*ast.Ident{
+            &ast.Ident{
+              NamePos: 100,
+              NameEnd: 101,
+              Name:    "A",
+            },
+            &ast.Ident{
+              NamePos: 102,
+              NameEnd: 106,
+              Name:    "name",
+            },
+          },
+        },
+      },
+      &ast.ExprSelectItem{
+        Expr: &ast.Ident{
+          NamePos: 108,
+          NameEnd: 112,
+          Name:    "item",
+        },
+      },
+    },
+    From: &ast.From{
+      From:   113,
+      Source: &ast.Join{
+        Op:   "INNER JOIN",
+        Left: &ast.Unnest{
+          Unnest: 120,
+          Rparen: 268,
+          Expr:   &ast.ArrayLiteral{
+            Array:  -1,
+            Lbrack: 132,
+            Rbrack: 267,
+            Values: []ast.Expr{
+              &ast.TypelessStructLiteral{
+                Struct: 140,
+                Rparen: 202,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 156,
+                      ValueEnd: 163,
+                      Value:    "first",
+                    },
+                    As: &ast.AsAlias{
+                      As:    164,
+                      Alias: &ast.Ident{
+                        NamePos: 167,
+                        NameEnd: 171,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 181,
+                      Rbrack: 192,
+                      Values: []ast.Expr{
+                        &ast.IntLiteral{
+                          ValuePos: 182,
+                          ValueEnd: 183,
+                          Base:     10,
+                          Value:    "1",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 185,
+                          ValueEnd: 186,
+                          Base:     10,
+                          Value:    "2",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 188,
+                          ValueEnd: 189,
+                          Base:     10,
+                          Value:    "3",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 191,
+                          ValueEnd: 192,
+                          Base:     10,
+                          Value:    "4",
+                        },
+                      },
+                    },
+                    As: &ast.AsAlias{
+                      As:    194,
+                      Alias: &ast.Ident{
+                        NamePos: 197,
+                        NameEnd: 202,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+              &ast.TypelessStructLiteral{
+                Struct: 211,
+                Rparen: 266,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 229,
+                      ValueEnd: 237,
+                      Value:    "second",
+                    },
+                    As: &ast.AsAlias{
+                      As:    238,
+                      Alias: &ast.Ident{
+                        NamePos: 241,
+                        NameEnd: 245,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 255,
+                      Rbrack: 256,
+                    },
+                    As: &ast.AsAlias{
+                      As:    258,
+                      Alias: &ast.Ident{
+                        NamePos: 261,
+                        NameEnd: 266,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    270,
+            Alias: &ast.Ident{
+              NamePos: 273,
+              NameEnd: 274,
+              Name:    "A",
+            },
+          },
+        },
+        Right: &ast.PathTableExpr{
+          Path: &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 292,
+                NameEnd: 293,
+                Name:    "A",
+              },
+              &ast.Ident{
+                NamePos: 294,
+                NameEnd: 299,
+                Name:    "items",
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    300,
+            Alias: &ast.Ident{
+              NamePos: 303,
+              NameEnd: 307,
+              Name:    "item",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT A.name, item FROM UNNEST([STRUCT("first" AS name, [1, 2, 3, 4] AS items), STRUCT("second" AS name, [] AS items)]) AS A INNER JOIN A.items AS item
diff --git a/testdata/result/query/select_from_join_unnest.sql.txt b/testdata/result/query/select_from_join_unnest.sql.txt
new file mode 100644
index 0000000..8838628
--- /dev/null
+++ b/testdata/result/query/select_from_join_unnest.sql.txt
@@ -0,0 +1,138 @@
+--- select_from_join_unnest.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT *
+FROM
+  Roster
+    JOIN
+  UNNEST(
+      ARRAY(
+        SELECT AS STRUCT *
+      FROM PlayerStats
+      WHERE PlayerStats.OpponentID = Roster.SchoolID
+    )) AS PlayerMatches
+  ON PlayerMatches.LastName = 'Buchanan'
+
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  93,
+    Results: []ast.SelectItem{
+      &ast.Star{
+        Star: 100,
+      },
+    },
+    From: &ast.From{
+      From:   102,
+      Source: &ast.Join{
+        Op:   "INNER JOIN",
+        Left: &ast.TableName{
+          Table: &ast.Ident{
+            NamePos: 109,
+            NameEnd: 115,
+            Name:    "Roster",
+          },
+        },
+        Right: &ast.Unnest{
+          Unnest: 127,
+          Rparen: 256,
+          Expr:   &ast.ArraySubQuery{
+            Array:  141,
+            Rparen: 255,
+            Query:  &ast.Select{
+              Select: 156,
+              As:     &ast.AsStruct{
+                As:     163,
+                Struct: 166,
+              },
+              Results: []ast.SelectItem{
+                &ast.Star{
+                  Star: 173,
+                },
+              },
+              From: &ast.From{
+                From:   181,
+                Source: &ast.TableName{
+                  Table: &ast.Ident{
+                    NamePos: 186,
+                    NameEnd: 197,
+                    Name:    "PlayerStats",
+                  },
+                },
+              },
+              Where: &ast.Where{
+                Where: 204,
+                Expr:  &ast.BinaryExpr{
+                  Op:   "=",
+                  Left: &ast.Path{
+                    Idents: []*ast.Ident{
+                      &ast.Ident{
+                        NamePos: 210,
+                        NameEnd: 221,
+                        Name:    "PlayerStats",
+                      },
+                      &ast.Ident{
+                        NamePos: 222,
+                        NameEnd: 232,
+                        Name:    "OpponentID",
+                      },
+                    },
+                  },
+                  Right: &ast.Path{
+                    Idents: []*ast.Ident{
+                      &ast.Ident{
+                        NamePos: 235,
+                        NameEnd: 241,
+                        Name:    "Roster",
+                      },
+                      &ast.Ident{
+                        NamePos: 242,
+                        NameEnd: 250,
+                        Name:    "SchoolID",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    258,
+            Alias: &ast.Ident{
+              NamePos: 261,
+              NameEnd: 274,
+              Name:    "PlayerMatches",
+            },
+          },
+        },
+        Cond: &ast.On{
+          On:   277,
+          Expr: &ast.BinaryExpr{
+            Op:   "=",
+            Left: &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 280,
+                  NameEnd: 293,
+                  Name:    "PlayerMatches",
+                },
+                &ast.Ident{
+                  NamePos: 294,
+                  NameEnd: 302,
+                  Name:    "LastName",
+                },
+              },
+            },
+            Right: &ast.StringLiteral{
+              ValuePos: 305,
+              ValueEnd: 315,
+              Value:    "Buchanan",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT * FROM Roster INNER JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM PlayerStats WHERE PlayerStats.OpponentID = Roster.SchoolID)) AS PlayerMatches ON PlayerMatches.LastName = "Buchanan"
diff --git a/testdata/result/query/select_from_left_join_path_table_expr.sql.txt b/testdata/result/query/select_from_left_join_path_table_expr.sql.txt
new file mode 100644
index 0000000..ab10840
--- /dev/null
+++ b/testdata/result/query/select_from_left_join_path_table_expr.sql.txt
@@ -0,0 +1,236 @@
+--- select_from_left_join_path_table_expr.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
+FROM
+  UNNEST(
+    [
+      STRUCT(
+        'first' AS name,
+        [1, 2, 3, 4] AS items),
+      STRUCT(
+          'second' AS name,
+        [] AS items)]) AS A
+    LEFT JOIN
+  A.items AS item
+
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  93,
+    Results: []ast.SelectItem{
+      &ast.ExprSelectItem{
+        Expr: &ast.Path{
+          Idents: []*ast.Ident{
+            &ast.Ident{
+              NamePos: 100,
+              NameEnd: 101,
+              Name:    "A",
+            },
+            &ast.Ident{
+              NamePos: 102,
+              NameEnd: 106,
+              Name:    "name",
+            },
+          },
+        },
+      },
+      &ast.ExprSelectItem{
+        Expr: &ast.Ident{
+          NamePos: 108,
+          NameEnd: 112,
+          Name:    "item",
+        },
+      },
+      &ast.Alias{
+        Expr: &ast.CallExpr{
+          Rparen: 134,
+          Func:   &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 114,
+                NameEnd: 126,
+                Name:    "ARRAY_LENGTH",
+              },
+            },
+          },
+          Args: []ast.Arg{
+            &ast.ExprArg{
+              Expr: &ast.Path{
+                Idents: []*ast.Ident{
+                  &ast.Ident{
+                    NamePos: 127,
+                    NameEnd: 128,
+                    Name:    "A",
+                  },
+                  &ast.Ident{
+                    NamePos: 129,
+                    NameEnd: 134,
+                    Name:    "items",
+                  },
+                },
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    -1,
+          Alias: &ast.Ident{
+            NamePos: 136,
+            NameEnd: 155,
+            Name:    "item_count_for_name",
+          },
+        },
+      },
+    },
+    From: &ast.From{
+      From:   156,
+      Source: &ast.Join{
+        Op:   "LEFT OUTER JOIN",
+        Left: &ast.Unnest{
+          Unnest: 163,
+          Rparen: 311,
+          Expr:   &ast.ArrayLiteral{
+            Array:  -1,
+            Lbrack: 175,
+            Rbrack: 310,
+            Values: []ast.Expr{
+              &ast.TypelessStructLiteral{
+                Struct: 183,
+                Rparen: 245,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 199,
+                      ValueEnd: 206,
+                      Value:    "first",
+                    },
+                    As: &ast.AsAlias{
+                      As:    207,
+                      Alias: &ast.Ident{
+                        NamePos: 210,
+                        NameEnd: 214,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 224,
+                      Rbrack: 235,
+                      Values: []ast.Expr{
+                        &ast.IntLiteral{
+                          ValuePos: 225,
+                          ValueEnd: 226,
+                          Base:     10,
+                          Value:    "1",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 228,
+                          ValueEnd: 229,
+                          Base:     10,
+                          Value:    "2",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 231,
+                          ValueEnd: 232,
+                          Base:     10,
+                          Value:    "3",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 234,
+                          ValueEnd: 235,
+                          Base:     10,
+                          Value:    "4",
+                        },
+                      },
+                    },
+                    As: &ast.AsAlias{
+                      As:    237,
+                      Alias: &ast.Ident{
+                        NamePos: 240,
+                        NameEnd: 245,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+              &ast.TypelessStructLiteral{
+                Struct: 254,
+                Rparen: 309,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 272,
+                      ValueEnd: 280,
+                      Value:    "second",
+                    },
+                    As: &ast.AsAlias{
+                      As:    281,
+                      Alias: &ast.Ident{
+                        NamePos: 284,
+                        NameEnd: 288,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 298,
+                      Rbrack: 299,
+                    },
+                    As: &ast.AsAlias{
+                      As:    301,
+                      Alias: &ast.Ident{
+                        NamePos: 304,
+                        NameEnd: 309,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    313,
+            Alias: &ast.Ident{
+              NamePos: 316,
+              NameEnd: 317,
+              Name:    "A",
+            },
+          },
+        },
+        Right: &ast.PathTableExpr{
+          Path: &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 334,
+                NameEnd: 335,
+                Name:    "A",
+              },
+              &ast.Ident{
+                NamePos: 336,
+                NameEnd: 341,
+                Name:    "items",
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    342,
+            Alias: &ast.Ident{
+              NamePos: 345,
+              NameEnd: 349,
+              Name:    "item",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name FROM UNNEST([STRUCT("first" AS name, [1, 2, 3, 4] AS items), STRUCT("second" AS name, [] AS items)]) AS A LEFT OUTER JOIN A.items AS item
diff --git a/testdata/result/query/select_from_where_order_by_with.sql.txt b/testdata/result/query/select_from_where_order_by_with.sql.txt
new file mode 100644
index 0000000..d5bfcd5
--- /dev/null
+++ b/testdata/result/query/select_from_where_order_by_with.sql.txt
@@ -0,0 +1,409 @@
+--- select_from_where_order_by_with.sql
+-- https://cloud.google.com/spanner/docs/full-text-search/ranked-search#score_multiple_columns
+SELECT AlbumId
+FROM Albums
+WHERE SEARCH(Title_Tokens, @p1) AND SEARCH(Studio_Tokens, @p2)
+ORDER BY WITH(
+  TitleScore AS SCORE(Title_Tokens, @p1) * @titleweight,
+  StudioScore AS SCORE(Studio_Tokens, @p2) * @studioweight,
+  DaysOld AS (UNIX_MICROS(CURRENT_TIMESTAMP()) - ReleaseTimestamp) / 8.64e+10,
+  FreshnessBoost AS (1 + @freshnessweight * GREATEST(0, 30 - DaysOld) / 30),
+  PopularityBoost AS (1 + IF(HasGrammy, @grammyweight, 0)),
+  (TitleScore + StudioScore) * FreshnessBoost * PopularityBoost)
+LIMIT 2
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Query{
+    Query: &ast.Select{
+      Select:  95,
+      Results: []ast.SelectItem{
+        &ast.ExprSelectItem{
+          Expr: &ast.Ident{
+            NamePos: 102,
+            NameEnd: 109,
+            Name:    "AlbumId",
+          },
+        },
+      },
+      From: &ast.From{
+        From:   110,
+        Source: &ast.TableName{
+          Table: &ast.Ident{
+            NamePos: 115,
+            NameEnd: 121,
+            Name:    "Albums",
+          },
+        },
+      },
+      Where: &ast.Where{
+        Where: 122,
+        Expr:  &ast.BinaryExpr{
+          Op:   "AND",
+          Left: &ast.CallExpr{
+            Rparen: 152,
+            Func:   &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 128,
+                  NameEnd: 134,
+                  Name:    "SEARCH",
+                },
+              },
+            },
+            Args: []ast.Arg{
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 135,
+                  NameEnd: 147,
+                  Name:    "Title_Tokens",
+                },
+              },
+              &ast.ExprArg{
+                Expr: &ast.Param{
+                  Atmark: 149,
+                  Name:   "p1",
+                },
+              },
+            },
+          },
+          Right: &ast.CallExpr{
+            Rparen: 183,
+            Func:   &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 158,
+                  NameEnd: 164,
+                  Name:    "SEARCH",
+                },
+              },
+            },
+            Args: []ast.Arg{
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 165,
+                  NameEnd: 178,
+                  Name:    "Studio_Tokens",
+                },
+              },
+              &ast.ExprArg{
+                Expr: &ast.Param{
+                  Atmark: 180,
+                  Name:   "p2",
+                },
+              },
+            },
+          },
+        },
+      },
+    },
+    OrderBy: &ast.OrderBy{
+      Order: 185,
+      Items: []*ast.OrderByItem{
+        &ast.OrderByItem{
+          DirPos: -1,
+          Expr:   &ast.WithExpr{
+            With:   194,
+            Rparen: 596,
+            Vars:   []*ast.WithExprVar{
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 202,
+                  NameEnd: 212,
+                  Name:    "TitleScore",
+                },
+                Expr: &ast.BinaryExpr{
+                  Op:   "*",
+                  Left: &ast.CallExpr{
+                    Rparen: 239,
+                    Func:   &ast.Path{
+                      Idents: []*ast.Ident{
+                        &ast.Ident{
+                          NamePos: 216,
+                          NameEnd: 221,
+                          Name:    "SCORE",
+                        },
+                      },
+                    },
+                    Args: []ast.Arg{
+                      &ast.ExprArg{
+                        Expr: &ast.Ident{
+                          NamePos: 222,
+                          NameEnd: 234,
+                          Name:    "Title_Tokens",
+                        },
+                      },
+                      &ast.ExprArg{
+                        Expr: &ast.Param{
+                          Atmark: 236,
+                          Name:   "p1",
+                        },
+                      },
+                    },
+                  },
+                  Right: &ast.Param{
+                    Atmark: 243,
+                    Name:   "titleweight",
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 259,
+                  NameEnd: 270,
+                  Name:    "StudioScore",
+                },
+                Expr: &ast.BinaryExpr{
+                  Op:   "*",
+                  Left: &ast.CallExpr{
+                    Rparen: 298,
+                    Func:   &ast.Path{
+                      Idents: []*ast.Ident{
+                        &ast.Ident{
+                          NamePos: 274,
+                          NameEnd: 279,
+                          Name:    "SCORE",
+                        },
+                      },
+                    },
+                    Args: []ast.Arg{
+                      &ast.ExprArg{
+                        Expr: &ast.Ident{
+                          NamePos: 280,
+                          NameEnd: 293,
+                          Name:    "Studio_Tokens",
+                        },
+                      },
+                      &ast.ExprArg{
+                        Expr: &ast.Param{
+                          Atmark: 295,
+                          Name:   "p2",
+                        },
+                      },
+                    },
+                  },
+                  Right: &ast.Param{
+                    Atmark: 302,
+                    Name:   "studioweight",
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 319,
+                  NameEnd: 326,
+                  Name:    "DaysOld",
+                },
+                Expr: &ast.BinaryExpr{
+                  Op:   "/",
+                  Left: &ast.ParenExpr{
+                    Lparen: 330,
+                    Rparen: 382,
+                    Expr:   &ast.BinaryExpr{
+                      Op:   "-",
+                      Left: &ast.CallExpr{
+                        Rparen: 362,
+                        Func:   &ast.Path{
+                          Idents: []*ast.Ident{
+                            &ast.Ident{
+                              NamePos: 331,
+                              NameEnd: 342,
+                              Name:    "UNIX_MICROS",
+                            },
+                          },
+                        },
+                        Args: []ast.Arg{
+                          &ast.ExprArg{
+                            Expr: &ast.CallExpr{
+                              Rparen: 361,
+                              Func:   &ast.Path{
+                                Idents: []*ast.Ident{
+                                  &ast.Ident{
+                                    NamePos: 343,
+                                    NameEnd: 360,
+                                    Name:    "CURRENT_TIMESTAMP",
+                                  },
+                                },
+                              },
+                            },
+                          },
+                        },
+                      },
+                      Right: &ast.Ident{
+                        NamePos: 366,
+                        NameEnd: 382,
+                        Name:    "ReleaseTimestamp",
+                      },
+                    },
+                  },
+                  Right: &ast.FloatLiteral{
+                    ValuePos: 386,
+                    ValueEnd: 394,
+                    Value:    "8.64e+10",
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 398,
+                  NameEnd: 412,
+                  Name:    "FreshnessBoost",
+                },
+                Expr: &ast.ParenExpr{
+                  Lparen: 416,
+                  Rparen: 470,
+                  Expr:   &ast.BinaryExpr{
+                    Op:   "+",
+                    Left: &ast.IntLiteral{
+                      ValuePos: 417,
+                      ValueEnd: 418,
+                      Base:     10,
+                      Value:    "1",
+                    },
+                    Right: &ast.BinaryExpr{
+                      Op:   "/",
+                      Left: &ast.BinaryExpr{
+                        Op:   "*",
+                        Left: &ast.Param{
+                          Atmark: 421,
+                          Name:   "freshnessweight",
+                        },
+                        Right: &ast.CallExpr{
+                          Rparen: 464,
+                          Func:   &ast.Path{
+                            Idents: []*ast.Ident{
+                              &ast.Ident{
+                                NamePos: 440,
+                                NameEnd: 448,
+                                Name:    "GREATEST",
+                              },
+                            },
+                          },
+                          Args: []ast.Arg{
+                            &ast.ExprArg{
+                              Expr: &ast.IntLiteral{
+                                ValuePos: 449,
+                                ValueEnd: 450,
+                                Base:     10,
+                                Value:    "0",
+                              },
+                            },
+                            &ast.ExprArg{
+                              Expr: &ast.BinaryExpr{
+                                Op:   "-",
+                                Left: &ast.IntLiteral{
+                                  ValuePos: 452,
+                                  ValueEnd: 454,
+                                  Base:     10,
+                                  Value:    "30",
+                                },
+                                Right: &ast.Ident{
+                                  NamePos: 457,
+                                  NameEnd: 464,
+                                  Name:    "DaysOld",
+                                },
+                              },
+                            },
+                          },
+                        },
+                      },
+                      Right: &ast.IntLiteral{
+                        ValuePos: 468,
+                        ValueEnd: 470,
+                        Base:     10,
+                        Value:    "30",
+                      },
+                    },
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 475,
+                  NameEnd: 490,
+                  Name:    "PopularityBoost",
+                },
+                Expr: &ast.ParenExpr{
+                  Lparen: 494,
+                  Rparen: 530,
+                  Expr:   &ast.BinaryExpr{
+                    Op:   "+",
+                    Left: &ast.IntLiteral{
+                      ValuePos: 495,
+                      ValueEnd: 496,
+                      Base:     10,
+                      Value:    "1",
+                    },
+                    Right: &ast.IfExpr{
+                      If:     499,
+                      Rparen: 529,
+                      Expr:   &ast.Ident{
+                        NamePos: 502,
+                        NameEnd: 511,
+                        Name:    "HasGrammy",
+                      },
+                      TrueResult: &ast.Param{
+                        Atmark: 513,
+                        Name:   "grammyweight",
+                      },
+                      ElseResult: &ast.IntLiteral{
+                        ValuePos: 528,
+                        ValueEnd: 529,
+                        Base:     10,
+                        Value:    "0",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+            Expr: &ast.BinaryExpr{
+              Op:   "*",
+              Left: &ast.BinaryExpr{
+                Op:   "*",
+                Left: &ast.ParenExpr{
+                  Lparen: 535,
+                  Rparen: 560,
+                  Expr:   &ast.BinaryExpr{
+                    Op:   "+",
+                    Left: &ast.Ident{
+                      NamePos: 536,
+                      NameEnd: 546,
+                      Name:    "TitleScore",
+                    },
+                    Right: &ast.Ident{
+                      NamePos: 549,
+                      NameEnd: 560,
+                      Name:    "StudioScore",
+                    },
+                  },
+                },
+                Right: &ast.Ident{
+                  NamePos: 564,
+                  NameEnd: 578,
+                  Name:    "FreshnessBoost",
+                },
+              },
+              Right: &ast.Ident{
+                NamePos: 581,
+                NameEnd: 596,
+                Name:    "PopularityBoost",
+              },
+            },
+          },
+        },
+      },
+    },
+    Limit: &ast.Limit{
+      Limit: 598,
+      Count: &ast.IntLiteral{
+        ValuePos: 604,
+        ValueEnd: 605,
+        Base:     10,
+        Value:    "2",
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT AlbumId FROM Albums WHERE SEARCH(Title_Tokens, @p1) AND SEARCH(Studio_Tokens, @p2) ORDER BY WITH(TitleScore AS SCORE(Title_Tokens, @p1) * @titleweight, StudioScore AS SCORE(Studio_Tokens, @p2) * @studioweight, DaysOld AS (UNIX_MICROS(CURRENT_TIMESTAMP()) - ReleaseTimestamp) / 8.64e+10, FreshnessBoost AS (1 + @freshnessweight * GREATEST(0, 30 - DaysOld) / 30), PopularityBoost AS (1 + IF(HasGrammy, @grammyweight, 0)), (TitleScore + StudioScore) * FreshnessBoost * PopularityBoost) LIMIT 2
diff --git a/testdata/result/statement/select_from_inner_join_path_table_expr.sql.txt b/testdata/result/statement/select_from_inner_join_path_table_expr.sql.txt
new file mode 100644
index 0000000..bd1a9ff
--- /dev/null
+++ b/testdata/result/statement/select_from_inner_join_path_table_expr.sql.txt
@@ -0,0 +1,196 @@
+--- select_from_inner_join_path_table_expr.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT A.name, item
+FROM
+  UNNEST(
+    [
+      STRUCT(
+        'first' AS name,
+        [1, 2, 3, 4] AS items),
+      STRUCT(
+          'second' AS name,
+        [] AS items)]) AS A
+    INNER JOIN
+  A.items AS item
+
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  93,
+    Results: []ast.SelectItem{
+      &ast.ExprSelectItem{
+        Expr: &ast.Path{
+          Idents: []*ast.Ident{
+            &ast.Ident{
+              NamePos: 100,
+              NameEnd: 101,
+              Name:    "A",
+            },
+            &ast.Ident{
+              NamePos: 102,
+              NameEnd: 106,
+              Name:    "name",
+            },
+          },
+        },
+      },
+      &ast.ExprSelectItem{
+        Expr: &ast.Ident{
+          NamePos: 108,
+          NameEnd: 112,
+          Name:    "item",
+        },
+      },
+    },
+    From: &ast.From{
+      From:   113,
+      Source: &ast.Join{
+        Op:   "INNER JOIN",
+        Left: &ast.Unnest{
+          Unnest: 120,
+          Rparen: 268,
+          Expr:   &ast.ArrayLiteral{
+            Array:  -1,
+            Lbrack: 132,
+            Rbrack: 267,
+            Values: []ast.Expr{
+              &ast.TypelessStructLiteral{
+                Struct: 140,
+                Rparen: 202,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 156,
+                      ValueEnd: 163,
+                      Value:    "first",
+                    },
+                    As: &ast.AsAlias{
+                      As:    164,
+                      Alias: &ast.Ident{
+                        NamePos: 167,
+                        NameEnd: 171,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 181,
+                      Rbrack: 192,
+                      Values: []ast.Expr{
+                        &ast.IntLiteral{
+                          ValuePos: 182,
+                          ValueEnd: 183,
+                          Base:     10,
+                          Value:    "1",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 185,
+                          ValueEnd: 186,
+                          Base:     10,
+                          Value:    "2",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 188,
+                          ValueEnd: 189,
+                          Base:     10,
+                          Value:    "3",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 191,
+                          ValueEnd: 192,
+                          Base:     10,
+                          Value:    "4",
+                        },
+                      },
+                    },
+                    As: &ast.AsAlias{
+                      As:    194,
+                      Alias: &ast.Ident{
+                        NamePos: 197,
+                        NameEnd: 202,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+              &ast.TypelessStructLiteral{
+                Struct: 211,
+                Rparen: 266,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 229,
+                      ValueEnd: 237,
+                      Value:    "second",
+                    },
+                    As: &ast.AsAlias{
+                      As:    238,
+                      Alias: &ast.Ident{
+                        NamePos: 241,
+                        NameEnd: 245,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 255,
+                      Rbrack: 256,
+                    },
+                    As: &ast.AsAlias{
+                      As:    258,
+                      Alias: &ast.Ident{
+                        NamePos: 261,
+                        NameEnd: 266,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    270,
+            Alias: &ast.Ident{
+              NamePos: 273,
+              NameEnd: 274,
+              Name:    "A",
+            },
+          },
+        },
+        Right: &ast.PathTableExpr{
+          Path: &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 292,
+                NameEnd: 293,
+                Name:    "A",
+              },
+              &ast.Ident{
+                NamePos: 294,
+                NameEnd: 299,
+                Name:    "items",
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    300,
+            Alias: &ast.Ident{
+              NamePos: 303,
+              NameEnd: 307,
+              Name:    "item",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT A.name, item FROM UNNEST([STRUCT("first" AS name, [1, 2, 3, 4] AS items), STRUCT("second" AS name, [] AS items)]) AS A INNER JOIN A.items AS item
diff --git a/testdata/result/statement/select_from_join_unnest.sql.txt b/testdata/result/statement/select_from_join_unnest.sql.txt
new file mode 100644
index 0000000..8838628
--- /dev/null
+++ b/testdata/result/statement/select_from_join_unnest.sql.txt
@@ -0,0 +1,138 @@
+--- select_from_join_unnest.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT *
+FROM
+  Roster
+    JOIN
+  UNNEST(
+      ARRAY(
+        SELECT AS STRUCT *
+      FROM PlayerStats
+      WHERE PlayerStats.OpponentID = Roster.SchoolID
+    )) AS PlayerMatches
+  ON PlayerMatches.LastName = 'Buchanan'
+
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  93,
+    Results: []ast.SelectItem{
+      &ast.Star{
+        Star: 100,
+      },
+    },
+    From: &ast.From{
+      From:   102,
+      Source: &ast.Join{
+        Op:   "INNER JOIN",
+        Left: &ast.TableName{
+          Table: &ast.Ident{
+            NamePos: 109,
+            NameEnd: 115,
+            Name:    "Roster",
+          },
+        },
+        Right: &ast.Unnest{
+          Unnest: 127,
+          Rparen: 256,
+          Expr:   &ast.ArraySubQuery{
+            Array:  141,
+            Rparen: 255,
+            Query:  &ast.Select{
+              Select: 156,
+              As:     &ast.AsStruct{
+                As:     163,
+                Struct: 166,
+              },
+              Results: []ast.SelectItem{
+                &ast.Star{
+                  Star: 173,
+                },
+              },
+              From: &ast.From{
+                From:   181,
+                Source: &ast.TableName{
+                  Table: &ast.Ident{
+                    NamePos: 186,
+                    NameEnd: 197,
+                    Name:    "PlayerStats",
+                  },
+                },
+              },
+              Where: &ast.Where{
+                Where: 204,
+                Expr:  &ast.BinaryExpr{
+                  Op:   "=",
+                  Left: &ast.Path{
+                    Idents: []*ast.Ident{
+                      &ast.Ident{
+                        NamePos: 210,
+                        NameEnd: 221,
+                        Name:    "PlayerStats",
+                      },
+                      &ast.Ident{
+                        NamePos: 222,
+                        NameEnd: 232,
+                        Name:    "OpponentID",
+                      },
+                    },
+                  },
+                  Right: &ast.Path{
+                    Idents: []*ast.Ident{
+                      &ast.Ident{
+                        NamePos: 235,
+                        NameEnd: 241,
+                        Name:    "Roster",
+                      },
+                      &ast.Ident{
+                        NamePos: 242,
+                        NameEnd: 250,
+                        Name:    "SchoolID",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    258,
+            Alias: &ast.Ident{
+              NamePos: 261,
+              NameEnd: 274,
+              Name:    "PlayerMatches",
+            },
+          },
+        },
+        Cond: &ast.On{
+          On:   277,
+          Expr: &ast.BinaryExpr{
+            Op:   "=",
+            Left: &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 280,
+                  NameEnd: 293,
+                  Name:    "PlayerMatches",
+                },
+                &ast.Ident{
+                  NamePos: 294,
+                  NameEnd: 302,
+                  Name:    "LastName",
+                },
+              },
+            },
+            Right: &ast.StringLiteral{
+              ValuePos: 305,
+              ValueEnd: 315,
+              Value:    "Buchanan",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT * FROM Roster INNER JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM PlayerStats WHERE PlayerStats.OpponentID = Roster.SchoolID)) AS PlayerMatches ON PlayerMatches.LastName = "Buchanan"
diff --git a/testdata/result/statement/select_from_left_join_path_table_expr.sql.txt b/testdata/result/statement/select_from_left_join_path_table_expr.sql.txt
new file mode 100644
index 0000000..ab10840
--- /dev/null
+++ b/testdata/result/statement/select_from_left_join_path_table_expr.sql.txt
@@ -0,0 +1,236 @@
+--- select_from_left_join_path_table_expr.sql
+-- https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#correlated_join
+SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
+FROM
+  UNNEST(
+    [
+      STRUCT(
+        'first' AS name,
+        [1, 2, 3, 4] AS items),
+      STRUCT(
+          'second' AS name,
+        [] AS items)]) AS A
+    LEFT JOIN
+  A.items AS item
+
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Select{
+    Select:  93,
+    Results: []ast.SelectItem{
+      &ast.ExprSelectItem{
+        Expr: &ast.Path{
+          Idents: []*ast.Ident{
+            &ast.Ident{
+              NamePos: 100,
+              NameEnd: 101,
+              Name:    "A",
+            },
+            &ast.Ident{
+              NamePos: 102,
+              NameEnd: 106,
+              Name:    "name",
+            },
+          },
+        },
+      },
+      &ast.ExprSelectItem{
+        Expr: &ast.Ident{
+          NamePos: 108,
+          NameEnd: 112,
+          Name:    "item",
+        },
+      },
+      &ast.Alias{
+        Expr: &ast.CallExpr{
+          Rparen: 134,
+          Func:   &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 114,
+                NameEnd: 126,
+                Name:    "ARRAY_LENGTH",
+              },
+            },
+          },
+          Args: []ast.Arg{
+            &ast.ExprArg{
+              Expr: &ast.Path{
+                Idents: []*ast.Ident{
+                  &ast.Ident{
+                    NamePos: 127,
+                    NameEnd: 128,
+                    Name:    "A",
+                  },
+                  &ast.Ident{
+                    NamePos: 129,
+                    NameEnd: 134,
+                    Name:    "items",
+                  },
+                },
+              },
+            },
+          },
+        },
+        As: &ast.AsAlias{
+          As:    -1,
+          Alias: &ast.Ident{
+            NamePos: 136,
+            NameEnd: 155,
+            Name:    "item_count_for_name",
+          },
+        },
+      },
+    },
+    From: &ast.From{
+      From:   156,
+      Source: &ast.Join{
+        Op:   "LEFT OUTER JOIN",
+        Left: &ast.Unnest{
+          Unnest: 163,
+          Rparen: 311,
+          Expr:   &ast.ArrayLiteral{
+            Array:  -1,
+            Lbrack: 175,
+            Rbrack: 310,
+            Values: []ast.Expr{
+              &ast.TypelessStructLiteral{
+                Struct: 183,
+                Rparen: 245,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 199,
+                      ValueEnd: 206,
+                      Value:    "first",
+                    },
+                    As: &ast.AsAlias{
+                      As:    207,
+                      Alias: &ast.Ident{
+                        NamePos: 210,
+                        NameEnd: 214,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 224,
+                      Rbrack: 235,
+                      Values: []ast.Expr{
+                        &ast.IntLiteral{
+                          ValuePos: 225,
+                          ValueEnd: 226,
+                          Base:     10,
+                          Value:    "1",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 228,
+                          ValueEnd: 229,
+                          Base:     10,
+                          Value:    "2",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 231,
+                          ValueEnd: 232,
+                          Base:     10,
+                          Value:    "3",
+                        },
+                        &ast.IntLiteral{
+                          ValuePos: 234,
+                          ValueEnd: 235,
+                          Base:     10,
+                          Value:    "4",
+                        },
+                      },
+                    },
+                    As: &ast.AsAlias{
+                      As:    237,
+                      Alias: &ast.Ident{
+                        NamePos: 240,
+                        NameEnd: 245,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+              &ast.TypelessStructLiteral{
+                Struct: 254,
+                Rparen: 309,
+                Values: []ast.TypelessStructLiteralArg{
+                  &ast.Alias{
+                    Expr: &ast.StringLiteral{
+                      ValuePos: 272,
+                      ValueEnd: 280,
+                      Value:    "second",
+                    },
+                    As: &ast.AsAlias{
+                      As:    281,
+                      Alias: &ast.Ident{
+                        NamePos: 284,
+                        NameEnd: 288,
+                        Name:    "name",
+                      },
+                    },
+                  },
+                  &ast.Alias{
+                    Expr: &ast.ArrayLiteral{
+                      Array:  -1,
+                      Lbrack: 298,
+                      Rbrack: 299,
+                    },
+                    As: &ast.AsAlias{
+                      As:    301,
+                      Alias: &ast.Ident{
+                        NamePos: 304,
+                        NameEnd: 309,
+                        Name:    "items",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    313,
+            Alias: &ast.Ident{
+              NamePos: 316,
+              NameEnd: 317,
+              Name:    "A",
+            },
+          },
+        },
+        Right: &ast.PathTableExpr{
+          Path: &ast.Path{
+            Idents: []*ast.Ident{
+              &ast.Ident{
+                NamePos: 334,
+                NameEnd: 335,
+                Name:    "A",
+              },
+              &ast.Ident{
+                NamePos: 336,
+                NameEnd: 341,
+                Name:    "items",
+              },
+            },
+          },
+          As: &ast.AsAlias{
+            As:    342,
+            Alias: &ast.Ident{
+              NamePos: 345,
+              NameEnd: 349,
+              Name:    "item",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name FROM UNNEST([STRUCT("first" AS name, [1, 2, 3, 4] AS items), STRUCT("second" AS name, [] AS items)]) AS A LEFT OUTER JOIN A.items AS item
diff --git a/testdata/result/statement/select_from_where_order_by_with.sql.txt b/testdata/result/statement/select_from_where_order_by_with.sql.txt
new file mode 100644
index 0000000..d5bfcd5
--- /dev/null
+++ b/testdata/result/statement/select_from_where_order_by_with.sql.txt
@@ -0,0 +1,409 @@
+--- select_from_where_order_by_with.sql
+-- https://cloud.google.com/spanner/docs/full-text-search/ranked-search#score_multiple_columns
+SELECT AlbumId
+FROM Albums
+WHERE SEARCH(Title_Tokens, @p1) AND SEARCH(Studio_Tokens, @p2)
+ORDER BY WITH(
+  TitleScore AS SCORE(Title_Tokens, @p1) * @titleweight,
+  StudioScore AS SCORE(Studio_Tokens, @p2) * @studioweight,
+  DaysOld AS (UNIX_MICROS(CURRENT_TIMESTAMP()) - ReleaseTimestamp) / 8.64e+10,
+  FreshnessBoost AS (1 + @freshnessweight * GREATEST(0, 30 - DaysOld) / 30),
+  PopularityBoost AS (1 + IF(HasGrammy, @grammyweight, 0)),
+  (TitleScore + StudioScore) * FreshnessBoost * PopularityBoost)
+LIMIT 2
+--- AST
+&ast.QueryStatement{
+  Query: &ast.Query{
+    Query: &ast.Select{
+      Select:  95,
+      Results: []ast.SelectItem{
+        &ast.ExprSelectItem{
+          Expr: &ast.Ident{
+            NamePos: 102,
+            NameEnd: 109,
+            Name:    "AlbumId",
+          },
+        },
+      },
+      From: &ast.From{
+        From:   110,
+        Source: &ast.TableName{
+          Table: &ast.Ident{
+            NamePos: 115,
+            NameEnd: 121,
+            Name:    "Albums",
+          },
+        },
+      },
+      Where: &ast.Where{
+        Where: 122,
+        Expr:  &ast.BinaryExpr{
+          Op:   "AND",
+          Left: &ast.CallExpr{
+            Rparen: 152,
+            Func:   &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 128,
+                  NameEnd: 134,
+                  Name:    "SEARCH",
+                },
+              },
+            },
+            Args: []ast.Arg{
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 135,
+                  NameEnd: 147,
+                  Name:    "Title_Tokens",
+                },
+              },
+              &ast.ExprArg{
+                Expr: &ast.Param{
+                  Atmark: 149,
+                  Name:   "p1",
+                },
+              },
+            },
+          },
+          Right: &ast.CallExpr{
+            Rparen: 183,
+            Func:   &ast.Path{
+              Idents: []*ast.Ident{
+                &ast.Ident{
+                  NamePos: 158,
+                  NameEnd: 164,
+                  Name:    "SEARCH",
+                },
+              },
+            },
+            Args: []ast.Arg{
+              &ast.ExprArg{
+                Expr: &ast.Ident{
+                  NamePos: 165,
+                  NameEnd: 178,
+                  Name:    "Studio_Tokens",
+                },
+              },
+              &ast.ExprArg{
+                Expr: &ast.Param{
+                  Atmark: 180,
+                  Name:   "p2",
+                },
+              },
+            },
+          },
+        },
+      },
+    },
+    OrderBy: &ast.OrderBy{
+      Order: 185,
+      Items: []*ast.OrderByItem{
+        &ast.OrderByItem{
+          DirPos: -1,
+          Expr:   &ast.WithExpr{
+            With:   194,
+            Rparen: 596,
+            Vars:   []*ast.WithExprVar{
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 202,
+                  NameEnd: 212,
+                  Name:    "TitleScore",
+                },
+                Expr: &ast.BinaryExpr{
+                  Op:   "*",
+                  Left: &ast.CallExpr{
+                    Rparen: 239,
+                    Func:   &ast.Path{
+                      Idents: []*ast.Ident{
+                        &ast.Ident{
+                          NamePos: 216,
+                          NameEnd: 221,
+                          Name:    "SCORE",
+                        },
+                      },
+                    },
+                    Args: []ast.Arg{
+                      &ast.ExprArg{
+                        Expr: &ast.Ident{
+                          NamePos: 222,
+                          NameEnd: 234,
+                          Name:    "Title_Tokens",
+                        },
+                      },
+                      &ast.ExprArg{
+                        Expr: &ast.Param{
+                          Atmark: 236,
+                          Name:   "p1",
+                        },
+                      },
+                    },
+                  },
+                  Right: &ast.Param{
+                    Atmark: 243,
+                    Name:   "titleweight",
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 259,
+                  NameEnd: 270,
+                  Name:    "StudioScore",
+                },
+                Expr: &ast.BinaryExpr{
+                  Op:   "*",
+                  Left: &ast.CallExpr{
+                    Rparen: 298,
+                    Func:   &ast.Path{
+                      Idents: []*ast.Ident{
+                        &ast.Ident{
+                          NamePos: 274,
+                          NameEnd: 279,
+                          Name:    "SCORE",
+                        },
+                      },
+                    },
+                    Args: []ast.Arg{
+                      &ast.ExprArg{
+                        Expr: &ast.Ident{
+                          NamePos: 280,
+                          NameEnd: 293,
+                          Name:    "Studio_Tokens",
+                        },
+                      },
+                      &ast.ExprArg{
+                        Expr: &ast.Param{
+                          Atmark: 295,
+                          Name:   "p2",
+                        },
+                      },
+                    },
+                  },
+                  Right: &ast.Param{
+                    Atmark: 302,
+                    Name:   "studioweight",
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 319,
+                  NameEnd: 326,
+                  Name:    "DaysOld",
+                },
+                Expr: &ast.BinaryExpr{
+                  Op:   "/",
+                  Left: &ast.ParenExpr{
+                    Lparen: 330,
+                    Rparen: 382,
+                    Expr:   &ast.BinaryExpr{
+                      Op:   "-",
+                      Left: &ast.CallExpr{
+                        Rparen: 362,
+                        Func:   &ast.Path{
+                          Idents: []*ast.Ident{
+                            &ast.Ident{
+                              NamePos: 331,
+                              NameEnd: 342,
+                              Name:    "UNIX_MICROS",
+                            },
+                          },
+                        },
+                        Args: []ast.Arg{
+                          &ast.ExprArg{
+                            Expr: &ast.CallExpr{
+                              Rparen: 361,
+                              Func:   &ast.Path{
+                                Idents: []*ast.Ident{
+                                  &ast.Ident{
+                                    NamePos: 343,
+                                    NameEnd: 360,
+                                    Name:    "CURRENT_TIMESTAMP",
+                                  },
+                                },
+                              },
+                            },
+                          },
+                        },
+                      },
+                      Right: &ast.Ident{
+                        NamePos: 366,
+                        NameEnd: 382,
+                        Name:    "ReleaseTimestamp",
+                      },
+                    },
+                  },
+                  Right: &ast.FloatLiteral{
+                    ValuePos: 386,
+                    ValueEnd: 394,
+                    Value:    "8.64e+10",
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 398,
+                  NameEnd: 412,
+                  Name:    "FreshnessBoost",
+                },
+                Expr: &ast.ParenExpr{
+                  Lparen: 416,
+                  Rparen: 470,
+                  Expr:   &ast.BinaryExpr{
+                    Op:   "+",
+                    Left: &ast.IntLiteral{
+                      ValuePos: 417,
+                      ValueEnd: 418,
+                      Base:     10,
+                      Value:    "1",
+                    },
+                    Right: &ast.BinaryExpr{
+                      Op:   "/",
+                      Left: &ast.BinaryExpr{
+                        Op:   "*",
+                        Left: &ast.Param{
+                          Atmark: 421,
+                          Name:   "freshnessweight",
+                        },
+                        Right: &ast.CallExpr{
+                          Rparen: 464,
+                          Func:   &ast.Path{
+                            Idents: []*ast.Ident{
+                              &ast.Ident{
+                                NamePos: 440,
+                                NameEnd: 448,
+                                Name:    "GREATEST",
+                              },
+                            },
+                          },
+                          Args: []ast.Arg{
+                            &ast.ExprArg{
+                              Expr: &ast.IntLiteral{
+                                ValuePos: 449,
+                                ValueEnd: 450,
+                                Base:     10,
+                                Value:    "0",
+                              },
+                            },
+                            &ast.ExprArg{
+                              Expr: &ast.BinaryExpr{
+                                Op:   "-",
+                                Left: &ast.IntLiteral{
+                                  ValuePos: 452,
+                                  ValueEnd: 454,
+                                  Base:     10,
+                                  Value:    "30",
+                                },
+                                Right: &ast.Ident{
+                                  NamePos: 457,
+                                  NameEnd: 464,
+                                  Name:    "DaysOld",
+                                },
+                              },
+                            },
+                          },
+                        },
+                      },
+                      Right: &ast.IntLiteral{
+                        ValuePos: 468,
+                        ValueEnd: 470,
+                        Base:     10,
+                        Value:    "30",
+                      },
+                    },
+                  },
+                },
+              },
+              &ast.WithExprVar{
+                Name: &ast.Ident{
+                  NamePos: 475,
+                  NameEnd: 490,
+                  Name:    "PopularityBoost",
+                },
+                Expr: &ast.ParenExpr{
+                  Lparen: 494,
+                  Rparen: 530,
+                  Expr:   &ast.BinaryExpr{
+                    Op:   "+",
+                    Left: &ast.IntLiteral{
+                      ValuePos: 495,
+                      ValueEnd: 496,
+                      Base:     10,
+                      Value:    "1",
+                    },
+                    Right: &ast.IfExpr{
+                      If:     499,
+                      Rparen: 529,
+                      Expr:   &ast.Ident{
+                        NamePos: 502,
+                        NameEnd: 511,
+                        Name:    "HasGrammy",
+                      },
+                      TrueResult: &ast.Param{
+                        Atmark: 513,
+                        Name:   "grammyweight",
+                      },
+                      ElseResult: &ast.IntLiteral{
+                        ValuePos: 528,
+                        ValueEnd: 529,
+                        Base:     10,
+                        Value:    "0",
+                      },
+                    },
+                  },
+                },
+              },
+            },
+            Expr: &ast.BinaryExpr{
+              Op:   "*",
+              Left: &ast.BinaryExpr{
+                Op:   "*",
+                Left: &ast.ParenExpr{
+                  Lparen: 535,
+                  Rparen: 560,
+                  Expr:   &ast.BinaryExpr{
+                    Op:   "+",
+                    Left: &ast.Ident{
+                      NamePos: 536,
+                      NameEnd: 546,
+                      Name:    "TitleScore",
+                    },
+                    Right: &ast.Ident{
+                      NamePos: 549,
+                      NameEnd: 560,
+                      Name:    "StudioScore",
+                    },
+                  },
+                },
+                Right: &ast.Ident{
+                  NamePos: 564,
+                  NameEnd: 578,
+                  Name:    "FreshnessBoost",
+                },
+              },
+              Right: &ast.Ident{
+                NamePos: 581,
+                NameEnd: 596,
+                Name:    "PopularityBoost",
+              },
+            },
+          },
+        },
+      },
+    },
+    Limit: &ast.Limit{
+      Limit: 598,
+      Count: &ast.IntLiteral{
+        ValuePos: 604,
+        ValueEnd: 605,
+        Base:     10,
+        Value:    "2",
+      },
+    },
+  },
+}
+
+--- SQL
+SELECT AlbumId FROM Albums WHERE SEARCH(Title_Tokens, @p1) AND SEARCH(Studio_Tokens, @p2) ORDER BY WITH(TitleScore AS SCORE(Title_Tokens, @p1) * @titleweight, StudioScore AS SCORE(Studio_Tokens, @p2) * @studioweight, DaysOld AS (UNIX_MICROS(CURRENT_TIMESTAMP()) - ReleaseTimestamp) / 8.64e+10, FreshnessBoost AS (1 + @freshnessweight * GREATEST(0, 30 - DaysOld) / 30), PopularityBoost AS (1 + IF(HasGrammy, @grammyweight, 0)), (TitleScore + StudioScore) * FreshnessBoost * PopularityBoost) LIMIT 2