feat(spanner/spansql): support EXISTS in query parsing (#12439)

ngalaiko · rahul2393 · web-flow · commit f5cb67b104e4 · 2025-06-16T10:50:57.000+05:30
Co-authored-by: rahul2393 &lt;irahul@google.com&gt;
diff --git a/spanner/spansql/parser.go b/spanner/spansql/parser.go
@@ -4310,6 +4310,7 @@ ascending order of precedence:
 	parseExpr
 	orParser
 	andParser
+	parseExistsOp
 	parseIsOp
 	parseInOp
 	parseComparisonOp
@@ -4418,7 +4419,7 @@ func newBinArithParser(opStr string, op ArithOperator, nextPrec func(*parser) (E
 
 func (p *parser) parseLogicalNot() (Expr, *parseError) {
 	if !p.eat("NOT") {
-		return p.parseIsOp()
+		return p.parseExistsOp()
 	}
 	be, err := p.parseBoolExpr()
 	if err != nil {
@@ -4427,6 +4428,27 @@ func (p *parser) parseLogicalNot() (Expr, *parseError) {
 	return LogicalOp{Op: Not, RHS: be}, nil
 }
 
+func (p *parser) parseExistsOp() (Expr, *parseError) {
+	debugf("parseExistsOp: %v", p)
+
+	if !p.eat("EXISTS") {
+		return p.parseIsOp()
+	}
+
+	if err := p.expect("("); err != nil {
+		return nil, err
+	}
+	subq, err := p.parseQuery()
+	if err != nil {
+		return nil, err
+	}
+	if err := p.expect(")"); err != nil {
+		return nil, err
+	}
+
+	return ExistsOp{Subquery: subq}, nil
+}
+
 func (p *parser) parseIsOp() (Expr, *parseError) {
 	debugf("parseIsOp: %v", p)
 
diff --git a/spanner/spansql/parser_test.go b/spanner/spansql/parser_test.go
@@ -340,6 +340,31 @@ func TestParseQuery(t *testing.T) {
 				},
 			},
 		},
+		{
+			`SELECT * FROM A WHERE EXISTS (SELECT * FROM B)`,
+			Query{
+				Select: Select{
+					List: []Expr{Star},
+					From: []SelectFrom{
+						SelectFromTable{
+							Table: "A",
+						},
+					},
+					Where: ExistsOp{
+						Subquery: Query{
+							Select: Select{
+								List: []Expr{Star},
+								From: []SelectFrom{
+									SelectFromTable{
+										Table: "B",
+									},
+								},
+							},
+						},
+					},
+				},
+			},
+		},
 	}
 	for _, test := range tests {
 		got, err := ParseQuery(test.in)
diff --git a/spanner/spansql/sql.go b/spanner/spansql/sql.go
@@ -1048,6 +1048,13 @@ func (co ComparisonOp) addSQL(sb *strings.Builder) {
 	}
 }
 
+func (eo ExistsOp) SQL() string { return buildSQL(eo) }
+func (eo ExistsOp) addSQL(sb *strings.Builder) {
+	sb.WriteString("EXISTS (")
+	eo.Subquery.addSQL(sb)
+	sb.WriteString(")")
+}
+
 func (io InOp) SQL() string { return buildSQL(io) }
 func (io InOp) addSQL(sb *strings.Builder) {
 	io.LHS.addSQL(sb)
diff --git a/spanner/spansql/sql_test.go b/spanner/spansql/sql_test.go
@@ -1216,6 +1216,35 @@ func TestSQL(t *testing.T) {
 			`SELECT A, B AS banana FROM Table WHERE C < "whelp" AND D IS NOT NULL ORDER BY OCol DESC LIMIT 1000`,
 			reparseQuery,
 		},
+		{
+			Query{
+				Select: Select{
+					List: []Expr{Star},
+					From: []SelectFrom{
+						SelectFromTable{
+							Table: "A",
+						},
+					},
+					Where: LogicalOp{
+						Op: Not,
+						RHS: ExistsOp{
+							Subquery: Query{
+								Select: Select{
+									List: []Expr{Star},
+									From: []SelectFrom{
+										SelectFromTable{
+											Table: "B",
+										},
+									},
+								},
+							},
+						},
+					},
+				},
+			},
+			`SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B)`,
+			reparseQuery,
+		},
 		{
 			Query{
 				Select: Select{
diff --git a/spanner/spansql/types.go b/spanner/spansql/types.go
@@ -808,6 +808,13 @@ const (
 	NotBetween
 )
 
+type ExistsOp struct {
+	Subquery Query
+}
+
+func (ExistsOp) isBoolExpr() {} // usually
+func (ExistsOp) isExpr()     {}
+
 type InOp struct {
 	LHS    Expr
 	Neg    bool

Original file line number	Diff line number	Diff line change
`@@ -1048,6 +1048,13 @@ func (co ComparisonOp) addSQL(sb *strings.Builder) {`
`1048`	`1048`	`}`
`1049`	`1049`	`}`
`1050`	`1050`
	`1051`	`+func (eo ExistsOp) SQL() string { return buildSQL(eo) }`
	`1052`	`+func (eo ExistsOp) addSQL(sb *strings.Builder) {`
	`1053`	`+ sb.WriteString("EXISTS (")`
	`1054`	`+ eo.Subquery.addSQL(sb)`
	`1055`	`+ sb.WriteString(")")`
	`1056`	`+}`
	`1057`	`+`
`1051`	`1058`	`func (io InOp) SQL() string { return buildSQL(io) }`
`1052`	`1059`	`func (io InOp) addSQL(sb *strings.Builder) {`
`1053`	`1060`	`io.LHS.addSQL(sb)`