Repository: genjidb/genji (go)

Issue:
IN doesn't work with parentheses
```
genji> select 1 IN (1, 2, 3);
found ,, expected ) at line 1, char 15
genji>
```

```
sqlite> select 1 IN (1, 2, 3);
1
sqlite>
```

Other example:

```sql
SELECT * FROM foo WHERE a IN (1, 2, 3)
```

This is a valid SQL expression, but currently, we only support this:

```sql
SELECT * FROM foo WHERE a IN [1, 2, 3]
```

This might be more complex than just parsing parentheses when we are in an IN operator.

I think we need to parse parentheses in two different ways:

### Single expression: `(expr)`

Example:

```sql
SELECT (1);
-> 1
SELECT (1 + 1);
-> 2
```

This is already supported, the parser [returns](https://github.com/genjidb/genji/blob/9da693665fe93d3fcfdffe3312a48712fc40023f/sql/parser/expr.go#L204-L212) an [expr.Parentheses](https://github.com/genjidb/genji/blob/9da693665fe93d3fcfdffe3312a48712fc40023f/sql/query/expr/expr.go#L127:6) expression.

### List of expressions: `(expr, expr, ...)`

Example:

```sql
SELECT (1, 2);
-> [1, 2]
SELECT (1 + 1, 3 > 5);
-> [2, false]
```

If we have more than one element in that list of parentheses, this must be parsed as an [expr.LiteralExprList](https://github.com/genjidb/genji/blob/9da693665fe93d3fcfdffe3312a48712fc40023f/sql/query/expr/literal.go#L75). 
There is already a function that can parse this kind of expression: https://github.com/genjidb/genji/blob/9da693665fe93d3fcfdffe3312a48712fc40023f/sql/parser/expr.go#L418:18

Pull request:
Fix parentheses parsing
Fixes #207.

Patch:
diff --git a/cmd/genji/go.mod b/cmd/genji/go.mod
index 8804bbea..73fdfa77 100644
--- a/cmd/genji/go.mod
+++ b/cmd/genji/go.mod
@@ -7,9 +7,12 @@ require (
 	github.com/c-bata/go-prompt v0.2.5
 	github.com/dgraph-io/badger/v2 v2.2007.2
 	github.com/genjidb/genji v0.9.0
-	github.com/genjidb/genji/engine/badgerengine v0.8.0
+	github.com/genjidb/genji/engine/badgerengine v0.9.0
 	github.com/stretchr/testify v1.6.1
 	github.com/urfave/cli/v2 v2.2.0
 )
 
-replace github.com/genjidb/genji v0.9.0 => ../../
+replace (
+	github.com/genjidb/genji v0.9.0 => ../../
+	github.com/genjidb/genji/engine/badgerengine v0.9.0 => ../../engine/badgerengine
+)
diff --git a/cmd/genji/shell/command.go b/cmd/genji/shell/command.go
index 852f8c0c..3334b716 100644
--- a/cmd/genji/shell/command.go
+++ b/cmd/genji/shell/command.go
@@ -52,43 +52,49 @@ func runTablesCmd(db *genji.DB, cmd []string) error {
 
 // displayTableIndex prints all indexes that the given table contains.
 func displayTableIndex(db *genji.DB, tableName string) error {
-	err := db.View(func(tx *genji.Tx) error {
-		t, err := tx.GetTable(tableName)
-		if err != nil {
-			return err
-		}
+	ctx := context.Background()
 
-		indexes, err := t.Indexes()
-		if err != nil {
+	res, err := db.Query(ctx, "SELECT * FROM __genji_indexes WHERE table_name = ?", tableName)
+	if err != nil {
+		return err
+	}
+	defer res.Close()
+
+	return res.Iterate(func(d document.Document) error {
+		var index database.IndexConfig
+
+		if err := index.ScanDocument(d); err != nil {
 			return err
 		}
 
-		for _, idx := range indexes {
-			fmt.Printf("%s on %s(%s)\n", idx.Opts.IndexName, idx.Opts.TableName, idx.Opts.Path)
-		}
+		fmt.Printf("%s ON %s (%s)\n", index.IndexName, index.TableName, index.Path)
 
 		return nil
 	})
-
-	return err
 }
 
 // displayAllIndexes shows all indexes that the database contains.
 func displayAllIndexes(db *genji.DB) error {
-	err := db.View(func(tx *genji.Tx) error {
-		indexes, err := tx.ListIndexes()
-		if err != nil {
+	ctx := context.Background()
+
+	res, err := db.Query(ctx, "SELECT * FROM __genji_indexes")
+	if err != nil {
+		return err
+	}
+	defer res.Close()
+
+	return res.Iterate(func(d document.Document) error {
+		var index database.IndexConfig
+
+		if err := index.ScanDocument(d); err != nil {
 			return err
 		}
 
-		for _, idx := range indexes {
-			fmt.Printf("%s on %s(%s)\n", idx.IndexName, idx.TableName, idx.Path)
-		}
+		fmt.Printf("%s ON %s (%s)\n", index.IndexName, index.TableName, index.Path)
 
 		return nil
 	})
 
-	return err
 }
 
 // runIndexesCmd executes all indexes of the database or all indexes of the given table.
diff --git a/database/config.go b/database/config.go
index 8e68e1ff..636e0627 100644
--- a/database/config.go
+++ b/database/config.go
@@ -316,6 +316,22 @@ func (t *tableInfoStore) loadAllTableInfo(tx engine.Transaction) error {
 			},
 		},
 	}
+
+	t.tableInfos[indexStoreName] = TableInfo{
+		storeName: []byte(indexStoreName),
+		readOnly: true,
+		FieldConstraints: []FieldConstraint{
+			{
+				Path: document.ValuePath{
+					document.ValuePathFragment{
+						FieldName: "index_name",
+					},
+				},
+				IsPrimaryKey: true,
+			},
+		},
+	}
+
 	return nil
 }
 
diff --git a/sql/parser/expr.go b/sql/parser/expr.go
index 2bd89a15..0b676235 100644
--- a/sql/parser/expr.go
+++ b/sql/parser/expr.go
@@ -206,10 +206,23 @@ func (p *Parser) parseUnaryExpr() (expr.Expr, error) {
 		if err != nil {
 			return nil, err
 		}
-		if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != scanner.RPAREN {
-			return nil, newParseError(scanner.Tokstr(tok, lit), []string{")"}, pos)
+
+		tok, pos, lit := p.ScanIgnoreWhitespace()
+		switch tok {
+		case scanner.RPAREN:
+			return expr.Parentheses{E: e}, nil
+		case scanner.COMMA:
+			exprList, err := p.parseExprListUntil(scanner.RPAREN)
+			if err != nil {
+				return nil, err
+			}
+
+			// prepend first parsed expression
+			exprList = append([]expr.Expr{e}, exprList...)
+			return exprList, nil
 		}
-		return expr.Parentheses{E: e}, nil
+
+		return nil, newParseError(scanner.Tokstr(tok, lit), []string{")", ","}, pos)
 	default:
 		return nil, newParseError(scanner.Tokstr(tok, lit), []string{"identifier", "string", "number", "bool"}, pos)
 	}
@@ -415,12 +428,7 @@ LOOP:
 	return vPath, nil
 }
 
-func (p *Parser) parseExprList(leftToken, rightToken scanner.Token) (expr.LiteralExprList, error) {
-	// Parse ( or [ token.
-	if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != leftToken {
-		return nil, newParseError(scanner.Tokstr(tok, lit), []string{leftToken.String()}, pos)
-	}
-
+func (p *Parser) parseExprListUntil(rightToken scanner.Token) (expr.LiteralExprList, error) {
 	var exprList expr.LiteralExprList
 	var expr expr.Expr
 	var err error
@@ -448,6 +456,15 @@ func (p *Parser) parseExprList(leftToken, rightToken scanner.Token) (expr.Litera
 	return exprList, nil
 }
 
+func (p *Parser) parseExprList(leftToken, rightToken scanner.Token) (expr.LiteralExprList, error) {
+	// Parse ( or [ token.
+	if tok, pos, lit := p.ScanIgnoreWhitespace(); tok != leftToken {
+		return nil, newParseError(scanner.Tokstr(tok, lit), []string{leftToken.String()}, pos)
+	}
+
+	return p.parseExprListUntil(rightToken)
+}
+
 // parseFunction parses a function call.
 // a function is an identifier followed by a parenthesis,
 // an optional coma-separated list of expressions and a closing parenthesis.
@@ -475,7 +492,7 @@ func (p *Parser) parseFunction() (expr.Expr, error) {
 
 	// Check if the function is called without arguments.
 	if tok, _, _ := p.ScanIgnoreWhitespace(); tok == scanner.RPAREN {
-		return expr.GetFunc(fname)
+		return p.functions.GetFunc(fname)
 	}
 	p.Unscan()
 
@@ -501,7 +518,7 @@ func (p *Parser) parseFunction() (expr.Expr, error) {
 		return nil, newParseError(scanner.Tokstr(tok, lit), []string{")"}, pos)
 	}
 
-	return expr.GetFunc(fname, exprs...)
+	return p.functions.GetFunc(fname, exprs...)
 }
 
 // parseCastExpression parses a string of the form CAST(expr AS type).
diff --git a/sql/parser/options.go b/sql/parser/options.go
new file mode 100644
index 00000000..a98d5a92
--- /dev/null
+++ b/sql/parser/options.go
@@ -0,0 +1,15 @@
+package parser
+
+import "github.com/genjidb/genji/sql/query/expr"
+
+// Options of the SQL parser.
+type Options struct {
+	// A map of builtin SQL functions.
+	Functions expr.Functions
+}
+
+func defaultOptions() *Options {
+	return &Options{
+		Functions: expr.NewFunctions(),
+	}
+}
diff --git a/sql/parser/parser.go b/sql/parser/parser.go
index 5ec87bcc..c6c84e67 100644
--- a/sql/parser/parser.go
+++ b/sql/parser/parser.go
@@ -19,11 +19,21 @@ type Parser struct {
 	orderedParams int
 	namedParams   int
 	buf           *bytes.Buffer
+	functions     expr.Functions
 }
 
 // NewParser returns a new instance of Parser.
 func NewParser(r io.Reader) *Parser {
-	return &Parser{s: scanner.NewBufScanner(r)}
+	return NewParserWithOptions(r, nil)
+}
+
+// NewParserWithOptions returns a new instance of Parser using given Options.
+func NewParserWithOptions(r io.Reader, opts *Options) *Parser {
+	if opts == nil {
+		opts = defaultOptions()
+	}
+
+	return &Parser{s: scanner.NewBufScanner(r), functions: opts.Functions}
 }
 
 // ParseQuery parses a query string and returns its AST representation.
diff --git a/sql/query/expr/function.go b/sql/query/expr/function.go
index ae743a7b..d005c0b1 100644
--- a/sql/query/expr/function.go
+++ b/sql/query/expr/function.go
@@ -9,42 +9,67 @@ import (
 	"github.com/genjidb/genji/document"
 )
 
-var functions = map[string]func(args ...Expr) (Expr, error){
-	"pk": func(args ...Expr) (Expr, error) {
-		if len(args) != 0 {
-			return nil, fmt.Errorf("pk() takes no arguments")
-		}
-		return new(PKFunc), nil
-	},
-	"count": func(args ...Expr) (Expr, error) {
-		if len(args) != 1 {
-			return nil, fmt.Errorf("COUNT() takes 1 argument")
-		}
-		return &CountFunc{Expr: args[0]}, nil
-	},
-	"min": func(args ...Expr) (Expr, error) {
-		if len(args) != 1 {
-			return nil, fmt.Errorf("MIN() takes 1 argument")
-		}
-		return &MinFunc{Expr: args[0]}, nil
-	},
-	"max": func(args ...Expr) (Expr, error) {
-		if len(args) != 1 {
-			return nil, fmt.Errorf("MAX() takes 1 argument")
-		}
-		return &MaxFunc{Expr: args[0]}, nil
-	},
-	"sum": func(args ...Expr) (Expr, error) {
-		if len(args) != 1 {
-			return nil, fmt.Errorf("SUM() takes 1 argument")
-		}
-		return &SumFunc{Expr: args[0]}, nil
-	},
+// Functions represents a map of builtin SQL functions.
+type Functions struct {
+	m map[string]func(args ...Expr) (Expr, error)
+}
+
+// BuiltinFunctions returns default map of builtin functions.
+func BuiltinFunctions() map[string]func(args ...Expr) (Expr, error) {
+	return map[string]func(args ...Expr) (Expr, error){
+		"pk": func(args ...Expr) (Expr, error) {
+			if len(args) != 0 {
+				return nil, fmt.Errorf("pk() takes no arguments")
+			}
+			return new(PKFunc), nil
+		},
+		"count": func(args ...Expr) (Expr, error) {
+			if len(args) != 1 {
+				return nil, fmt.Errorf("COUNT() takes 1 argument")
+			}
+			return &CountFunc{Expr: args[0]}, nil
+		},
+		"min": func(args ...Expr) (Expr, error) {
+			if len(args) != 1 {
+				return nil, fmt.Errorf("MIN() takes 1 argument")
+			}
+			return &MinFunc{Expr: args[0]}, nil
+		},
+		"max": func(args ...Expr) (Expr, error) {
+			if len(args) != 1 {
+				return nil, fmt.Errorf("MAX() takes 1 argument")
+			}
+			return &MaxFunc{Expr: args[0]}, nil
+		},
+		"sum": func(args ...Expr) (Expr, error) {
+			if len(args) != 1 {
+				return nil, fmt.Errorf("SUM() takes 1 argument")
+			}
+			return &SumFunc{Expr: args[0]}, nil
+		},
+		"avg": func(args ...Expr) (Expr, error) {
+			if len(args) != 1 {
+				return nil, fmt.Errorf("AVG() takes 1 argument")
+			}
+			return &AvgFunc{Expr: args[0]}, nil
+		},
+	}
+}
+
+func NewFunctions() Functions {
+	return Functions{
+		m: BuiltinFunctions(),
+	}
+}
+
+// AddFunc adds function to the map.
+func (f Functions) AddFunc(name string, fn func(args ...Expr) (Expr, error)) {
+	f.m[name] = fn
 }
 
 // GetFunc return a function expression by name.
-func GetFunc(name string, args ...Expr) (Expr, error) {
-	fn, ok := functions[strings.ToLower(name)]
+func (f Functions) GetFunc(name string, args ...Expr) (Expr, error) {
+	fn, ok := f.m[strings.ToLower(name)]
 	if !ok {
 		return nil, fmt.Errorf("no such function: %q", name)
 	}
@@ -511,3 +536,91 @@ func (s *SumAggregator) Aggregate(fb *document.FieldBuffer) error {
 
 	return nil
 }
+
+// AvgFunc is the AVG aggregator function.
+type AvgFunc struct {
+	Expr  Expr
+	Alias string
+}
+
+// Eval extracts the average value from the given document and returns it.
+func (s *AvgFunc) Eval(ctx EvalStack) (document.Value, error) {
+	return ctx.Document.GetByField(s.String())
+}
+
+// SetAlias implements the planner.AggregatorBuilder interface.
+func (s *AvgFunc) SetAlias(alias string) {
+	s.Alias = alias
+}
+
+// NewAggregator implements the planner.AggregatorBuilder interface.
+func (s *AvgFunc) NewAggregator(group document.Value) document.Aggregator {
+	return &AvgAggregator{
+		Fn: s,
+	}
+}
+
+// IsEqual compares this expression with the other expression and returns
+// true if they are equal.
+func (s *AvgFunc) IsEqual(other Expr) bool {
+	if other == nil {
+		return false
+	}
+
+	o, ok := other.(*AvgFunc)
+	if !ok {
+		return false
+	}
+
+	return Equal(s.Expr, o.Expr)
+}
+
+// String returns the alias if non-zero, otherwise it returns a string representation
+// of the average expression.
+func (s *AvgFunc) String() string {
+	if s.Alias != "" {
+		return s.Alias
+	}
+
+	return fmt.Sprintf("AVG(%v)", s.Expr)
+}
+
+// AvgAggregator is an aggregator that returns the average non-null value.
+type AvgAggregator struct {
+	Fn      *AvgFunc
+	Avg     float64
+	Counter int64
+}
+
+// Add stores the average value of all non-NULL numeric values in the group.
+func (s *AvgAggregator) Add(d document.Document) error {
+	v, err := s.Fn.Expr.Eval(EvalStack{
+		Document: d,
+	})
+	if err != nil && err != document.ErrFieldNotFound {
+		return err
+	}
+
+	switch v.Type {
+	case document.IntegerValue:
+		s.Avg += float64(v.V.(int64))
+	case document.DoubleValue:
+		s.Avg += v.V.(float64)
+	default:
+		return nil
+	}
+	s.Counter++
+
+	return nil
+}
+
+// Aggregate adds a field to the given buffer with the maximum value.
+func (s *AvgAggregator) Aggregate(fb *document.FieldBuffer) error {
+	if s.Counter == 0 {
+		fb.Add(s.Fn.String(), document.NewDoubleValue(0))
+	} else {
+		fb.Add(s.Fn.String(), document.NewDoubleValue(s.Avg/float64(s.Counter)))
+	}
+
+	return nil
+}


Test patch:
diff --git a/sql/parser/select_test.go b/sql/parser/select_test.go
index 499ca633..f3a54f98 100644
--- a/sql/parser/select_test.go
+++ b/sql/parser/select_test.go
@@ -24,6 +24,40 @@ func TestParserSelect(t *testing.T) {
 				}, "")),
 			false,
 		},
+		{"NoTableWithTuple", "SELECT (1, 2)",
+			planner.NewTree(planner.NewProjectionNode(nil,
+				[]planner.ProjectedField{
+					planner.ProjectedExpr{Expr: expr.LiteralExprList{
+						expr.IntegerValue(1),
+						expr.IntegerValue(2),
+					}, ExprName: "(1, 2)"},
+				}, "")),
+			false,
+		},
+		{"NoTableWithBrackets", "SELECT [1, 2]",
+			planner.NewTree(planner.NewProjectionNode(nil,
+				[]planner.ProjectedField{
+					planner.ProjectedExpr{Expr: expr.LiteralExprList{
+						expr.IntegerValue(1),
+						expr.IntegerValue(2),
+					}, ExprName: "[1, 2]"},
+				}, "")),
+			false,
+		},
+		{"NoTableWithINOperator", "SELECT 1 in (1, 2), 3",
+			planner.NewTree(planner.NewProjectionNode(nil,
+				[]planner.ProjectedField{
+					planner.ProjectedExpr{
+						Expr: expr.In(expr.IntegerValue(1), expr.LiteralExprList{
+							expr.IntegerValue(1),
+							expr.IntegerValue(2),
+						}),
+						ExprName: "1 in (1, 2)",
+					},
+					planner.ProjectedExpr{Expr: expr.IntegerValue(3), ExprName: "3"},
+				}, "")),
+			false,
+		},
 		{"NoCond", "SELECT * FROM test",
 			planner.NewTree(
 				planner.NewProjectionNode(