Skip to content

Commit d19f797

Browse files
anicollanicoll
authored
feat(spansql): support subquery in View Join (#13266)
This feature simply adds the ability to parse DDL statements with subquery's as the test validates. I think it resolved this issue as well The highlights are the following: * Added ScalarSubquery type * Added SQL generation methods * Updated parseLit() to detect and parse subqueries Related: #8519
1 parent 66cc9bb commit d19f797

File tree

5 files changed

+230
-2
lines changed

5 files changed

+230
-2
lines changed

‎go.work.sum‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -509,6 +509,7 @@ golang.org/x/mod v0.9.0 h1:KENHtAZL2y3NLMYZeHY9DW8HW8V+kQyJsY/V9JlKvCs=
509509
golang.org/x/mod v0.11.0 h1:bUO06HqtnRcc/7l71XBe4WcqTZ+3AH1J59zWDDwLKgU=
510510
golang.org/x/mod v0.16.0/go.mod h1:hTbmBsO62+eylJbnUtE2MGJUyE7QWk4xUqPFrRgJ+7c=
511511
golang.org/x/mod v0.21.0/go.mod h1:6SkKJ3Xj0I0BrPOZoBy3bdMptDDU9oJrpohJ3eWZ1fY=
512+
golang.org/x/mod v0.28.0 h1:gQBtGhjxykdjY9YhZpSlZIsbnaE2+PgjfLWUQTnoZ1U=
512513
golang.org/x/mod v0.28.0/go.mod h1:yfB/L0NOf/kmEbXjzCPOx1iK1fRutOydrCMsqRhEBxI=
513514
golang.org/x/net v0.0.0-20180906233101-161cd47e91fd/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
514515
golang.org/x/net v0.0.0-20181023162649-9b4f9f5ad519/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
@@ -551,6 +552,7 @@ golang.org/x/tools v0.10.0 h1:tvDr/iQoUqNdohiYm0LmmKcBk+q86lb9EprIUFhHHGg=
551552
golang.org/x/tools v0.16.0/go.mod h1:kYVVN6I1mBNoB1OX+noeBjbRk4IUEPa7JJ+TJMEooJ0=
552553
golang.org/x/tools v0.19.0/go.mod h1:qoJWxmGSIBmAeriMx19ogtrEPrGtDbPK634QFIcLAhc=
553554
golang.org/x/tools v0.26.0/go.mod h1:TPVVj70c7JJ3WCazhD8OdXcZg/og+b9+tH/KxylGwH0=
555+
golang.org/x/tools v0.37.0 h1:DVSRzp7FwePZW356yEAChSdNcQo6Nsp+fex1SUW09lE=
554556
golang.org/x/tools v0.37.0/go.mod h1:MBN5QPQtLMHVdvsbtarmTNukZDdgwdwlO5qGacAzF0w=
555557
gonum.org/v1/gonum v0.14.0/go.mod h1:AoWeoz0becf9QMWtE8iWXNXc27fK4fNeHNf/oMejGfU=
556558
gonum.org/v1/gonum v0.15.1/go.mod h1:eZTZuRFrzu5pcyjN5wJhcIhnUdNijYxX1T2IcrOGY0o=

‎spanner/spansql/parser.go‎

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -90,7 +90,7 @@ func ParseDML(filename, s string) (*DML, error) {
9090
return dml, nil
9191
}
9292

93-
func parseStatements(stmts statements, filename string, s string) error {
93+
func parseStatements(stmts statements, filename, s string) error {
9494
p := newParser(filename, s)
9595

9696
stmts.setFilename(filename)
@@ -4655,8 +4655,26 @@ func (p *parser) parseLit() (Expr, *parseError) {
46554655
return BytesLiteral(tok.string), nil
46564656
}
46574657

4658-
// Handle parenthesized expressions.
4658+
// Handle parenthesized expressions and scalar subqueries.
4659+
// NOTE: The opening "(" has already been consumed by p.next() above (line 4638).
4660+
// The parser is now positioned right after the "(", ready to parse the contents.
46594661
if tok.value == "(" {
4662+
// Look ahead to see if this is a subquery like: (SELECT ...)
4663+
// p.sniff("SELECT") peeks at the next token without consuming it.
4664+
if p.sniff("SELECT") {
4665+
// Parse the subquery starting from the current position (after the "(")
4666+
q, err := p.parseQuery()
4667+
if err != nil {
4668+
return nil, err
4669+
}
4670+
if err := p.expect(")"); err != nil {
4671+
return nil, err
4672+
}
4673+
return ScalarSubquery{Query: q}, nil
4674+
}
4675+
4676+
// Regular parenthesized expression like: (1 + 2)
4677+
// Parse the inner expression starting from the current position (after the "(")
46604678
e, err := p.parseExpr()
46614679
if err != nil {
46624680
return nil, err

‎spanner/spansql/parser_test.go‎

Lines changed: 195 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -365,6 +365,65 @@ func TestParseQuery(t *testing.T) {
365365
},
366366
},
367367
},
368+
// Scalar subqueries in SELECT list - ensures parseLit handles (SELECT ...) correctly
369+
{
370+
`SELECT (SELECT MAX(id) FROM t1), name FROM t2`,
371+
Query{
372+
Select: Select{
373+
List: []Expr{
374+
ScalarSubquery{
375+
Query: Query{
376+
Select: Select{
377+
List: []Expr{Func{Name: "MAX", Args: []Expr{ID("id")}}},
378+
From: []SelectFrom{SelectFromTable{Table: "t1"}},
379+
},
380+
},
381+
},
382+
ID("name"),
383+
},
384+
From: []SelectFrom{SelectFromTable{Table: "t2"}},
385+
},
386+
},
387+
},
388+
// Scalar subquery in WHERE clause
389+
{
390+
`SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users)`,
391+
Query{
392+
Select: Select{
393+
List: []Expr{Star},
394+
From: []SelectFrom{SelectFromTable{Table: "users"}},
395+
Where: ComparisonOp{
396+
Op: Gt,
397+
LHS: ID("age"),
398+
RHS: ScalarSubquery{
399+
Query: Query{
400+
Select: Select{
401+
List: []Expr{Func{Name: "AVG", Args: []Expr{ID("age")}}},
402+
From: []SelectFrom{SelectFromTable{Table: "users"}},
403+
},
404+
},
405+
},
406+
},
407+
},
408+
},
409+
},
410+
// Parenthesized expression in SELECT list - ensures parseLit handles (expr) correctly
411+
{
412+
`SELECT (1 + 2) * 3, name FROM t`,
413+
Query{
414+
Select: Select{
415+
List: []Expr{
416+
ArithOp{
417+
LHS: Paren{Expr: ArithOp{LHS: IntegerLiteral(1), Op: Add, RHS: IntegerLiteral(2)}},
418+
Op: Mul,
419+
RHS: IntegerLiteral(3),
420+
},
421+
ID("name"),
422+
},
423+
From: []SelectFrom{SelectFromTable{Table: "t"}},
424+
},
425+
},
426+
},
368427
}
369428
for _, test := range tests {
370429
got, err := ParseQuery(test.in)
@@ -634,6 +693,53 @@ func TestParseExpr(t *testing.T) {
634693
// Reserved keywords.
635694
{`TRUE AND FALSE`, LogicalOp{LHS: True, Op: And, RHS: False}},
636695
{`NULL`, Null},
696+
697+
// Parenthesized expressions - test that parseLit correctly handles the opening paren.
698+
// The opening "(" is consumed by p.next(), then parseExpr is called for the contents.
699+
{`(1)`, Paren{Expr: IntegerLiteral(1)}},
700+
{`(1 + 2)`, Paren{Expr: ArithOp{LHS: IntegerLiteral(1), Op: Add, RHS: IntegerLiteral(2)}}},
701+
{`((1 + 2))`, Paren{Expr: Paren{Expr: ArithOp{LHS: IntegerLiteral(1), Op: Add, RHS: IntegerLiteral(2)}}}},
702+
{`(1 + 2) * 3`, ArithOp{LHS: Paren{Expr: ArithOp{LHS: IntegerLiteral(1), Op: Add, RHS: IntegerLiteral(2)}}, Op: Mul, RHS: IntegerLiteral(3)}},
703+
{`((1 + 2) * 3)`, Paren{Expr: ArithOp{LHS: Paren{Expr: ArithOp{LHS: IntegerLiteral(1), Op: Add, RHS: IntegerLiteral(2)}}, Op: Mul, RHS: IntegerLiteral(3)}}},
704+
{`(A AND B) OR C`, LogicalOp{LHS: Paren{Expr: LogicalOp{LHS: ID("A"), Op: And, RHS: ID("B")}}, Op: Or, RHS: ID("C")}},
705+
{`(TRUE)`, Paren{Expr: True}},
706+
{`(NULL)`, Paren{Expr: Null}},
707+
708+
// Scalar subqueries - test that parseLit correctly distinguishes (SELECT ...) from (expr).
709+
// When p.sniff("SELECT") is true after consuming "(", parseQuery is called instead of parseExpr.
710+
{
711+
`(SELECT 1)`,
712+
ScalarSubquery{
713+
Query: Query{
714+
Select: Select{
715+
List: []Expr{IntegerLiteral(1)},
716+
},
717+
},
718+
},
719+
},
720+
{
721+
`(SELECT MAX(x) FROM t)`,
722+
ScalarSubquery{
723+
Query: Query{
724+
Select: Select{
725+
List: []Expr{Func{Name: "MAX", Args: []Expr{ID("x")}}},
726+
From: []SelectFrom{SelectFromTable{Table: "t"}},
727+
},
728+
},
729+
},
730+
},
731+
{
732+
`(SELECT COUNT(*) FROM users WHERE active = TRUE)`,
733+
ScalarSubquery{
734+
Query: Query{
735+
Select: Select{
736+
List: []Expr{Func{Name: "COUNT", Args: []Expr{Star}}},
737+
From: []SelectFrom{SelectFromTable{Table: "users"}},
738+
Where: ComparisonOp{LHS: ID("active"), Op: Eq, RHS: True},
739+
},
740+
},
741+
},
742+
},
637743
}
638744
for _, test := range tests {
639745
p := newParser("test-file", test.in)
@@ -2415,6 +2521,95 @@ func TestParseDDL(t *testing.T) {
24152521
},
24162522
},
24172523
},
2524+
{
2525+
`CREATE OR REPLACE VIEW Transaction SQL SECURITY INVOKER AS SELECT
2526+
ID, Name, Amount, AccountID, PaymentID
2527+
FROM
2528+
Transactions as t
2529+
JOIN Accounts as acc ON t.AccountID = acc.ID
2530+
JOIN Payment as p ON t.PaymentID = p.ID
2531+
AND p.EventSequence = (
2532+
SELECT MAX(p2.EventSequence)
2533+
FROM Payment as p2
2534+
WHERE p2.ID = p.ID
2535+
)`,
2536+
&DDL{
2537+
Filename: "filename",
2538+
List: []DDLStmt{
2539+
&CreateView{
2540+
Name: "Transaction",
2541+
OrReplace: true,
2542+
SecurityType: Invoker,
2543+
Query: Query{
2544+
Select: Select{
2545+
List: []Expr{ID("ID"), ID("Name"), ID("Amount"), ID("AccountID"), ID("PaymentID")},
2546+
From: []SelectFrom{
2547+
SelectFromJoin{
2548+
Type: InnerJoin,
2549+
LHS: SelectFromJoin{
2550+
Type: InnerJoin,
2551+
LHS: SelectFromTable{
2552+
Table: "Transactions",
2553+
Alias: "t",
2554+
},
2555+
RHS: SelectFromTable{
2556+
Table: "Accounts",
2557+
Alias: "acc",
2558+
},
2559+
On: ComparisonOp{
2560+
LHS: PathExp{"t", "AccountID"},
2561+
Op: Eq,
2562+
RHS: PathExp{"acc", "ID"},
2563+
},
2564+
},
2565+
RHS: SelectFromTable{
2566+
Table: "Payment",
2567+
Alias: "p",
2568+
},
2569+
On: LogicalOp{
2570+
LHS: ComparisonOp{
2571+
LHS: PathExp{"t", "PaymentID"},
2572+
Op: Eq,
2573+
RHS: PathExp{"p", "ID"},
2574+
},
2575+
Op: And,
2576+
RHS: ComparisonOp{
2577+
LHS: PathExp{"p", "EventSequence"},
2578+
Op: Eq,
2579+
RHS: ScalarSubquery{
2580+
Query: Query{
2581+
Select: Select{
2582+
List: []Expr{
2583+
Func{
2584+
Name: "MAX",
2585+
Args: []Expr{PathExp{"p2", "EventSequence"}},
2586+
},
2587+
},
2588+
From: []SelectFrom{
2589+
SelectFromTable{
2590+
Table: "Payment",
2591+
Alias: "p2",
2592+
},
2593+
},
2594+
Where: ComparisonOp{
2595+
LHS: PathExp{"p2", "ID"},
2596+
Op: Eq,
2597+
RHS: PathExp{"p", "ID"},
2598+
},
2599+
},
2600+
},
2601+
},
2602+
},
2603+
},
2604+
},
2605+
},
2606+
},
2607+
},
2608+
Position: line(1),
2609+
},
2610+
},
2611+
},
2612+
},
24182613
}
24192614
for _, test := range tests {
24202615
got, err := ParseDDL("filename", test.in)

‎spanner/spansql/sql.go‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1084,6 +1084,13 @@ func (eo ExistsOp) addSQL(sb *strings.Builder) {
10841084
sb.WriteString(")")
10851085
}
10861086

1087+
func (ss ScalarSubquery) SQL() string { return buildSQL(ss) }
1088+
func (ss ScalarSubquery) addSQL(sb *strings.Builder) {
1089+
sb.WriteString("(")
1090+
ss.Query.addSQL(sb)
1091+
sb.WriteString(")")
1092+
}
1093+
10871094
func (io InOp) SQL() string { return buildSQL(io) }
10881095
func (io InOp) addSQL(sb *strings.Builder) {
10891096
io.LHS.addSQL(sb)

‎spanner/spansql/types.go‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -815,6 +815,12 @@ type ExistsOp struct {
815815
func (ExistsOp) isBoolExpr() {} // usually
816816
func (ExistsOp) isExpr() {}
817817

818+
type ScalarSubquery struct {
819+
Query Query
820+
}
821+
822+
func (ScalarSubquery) isExpr() {}
823+
818824
type InOp struct {
819825
LHS Expr
820826
Neg bool

0 commit comments

Comments
 (0)