Implementing subqueries on go-mysql-server

Some queries become hard to read or write without proper support for subqueries. We already have support for them in the FROM statement, but we lack support for them as expressions.

Current problems

No way to pass the current row of the parent query to an expression.
No way to resolve a query using columns from the parent query.

Solution

Add the concept of a parent scope and pass it along using the Context that every single node receives.

Parsing

Convert the currently unsupported node for a subquery to a Subquery node. Should be pretty straightforward.

Changes in Context

Context needs to have two new functionalities:

Providing the parent row, if any. If there is more than 1 ancestor, also access to them.
Providing the parent schema, if any. If there is more than 1 ancestor, also access to them.

type Context struct {
    // ...
    parentRow Row
    parentSchema Schema
}

func (ctx *Context) clone() *Context {
    var newCtx Context
    // clone all other properties

    if ctx.parentRow != nil {
        newCtx.parentRow = make(Row, len(ctx.parentRow))
        copy(newCtx.parentRow, ctx.parentRow)
    }

    if ctx.parentSchema != nil {
        newCtx.parentSchema = make(Schema, len(ctx.parentSchema))
        copy(newCtx.parentSchema, ctx.parentSchema)
    }

    return newCtx
}

func (ctx *Context) WithParentRow(row Row) *Context {
    newCtx := ctx.clone()
    // by appending, we allow access to all ancestors
    newCtx.parentRow = append(newCtx.parentRow, row)
    return newCtx
}

func (ctx *Context) ParentRow() (Row, error) {
    if ctx.parentRow == nil {
        return nil, ErrNoParent
    }
    return ctx.parentRow, nil
}

func (ctx *Context) WithParentSchema(s Schema) *Context {
    newCtx := ctx.clone()
    // by appending, we allow access to all ancestors
    newCtx.parentSchema = append(newCtx.parentSchema, s)
    return newCtx
}

func (ctx *Context) ParentSchema() (Schema, error) {
    if ctx.parentSchema == nil {
        return nil, ErrNoParent
    }
    return ctx.parentSchema, nil
}

Resolution

When resolving a column and failing to do so, there will be a fallback to try and resolve said column using the parent scope, if any. That column will be replaced with GetParentField node, a special node that will retrieve values from the rows in the parent.

type GetParentField struct {
    table string
    name string
    index string
}

Esentially, all rules that convert from an UnresolvedColumn to GetField or similar, must be changed in order to account for this new logic.

A new rule resolve_subqueries must be added where they are resolved after adding the current node schema to the context and pass it down.

Execution

Subquery will be an Expression and will execute as any other, except it will do the following in its Eval method.

func (s *Subquery) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
    iter, err := s.Query.RowIter(ctx.WithParentRow(row))
    if err != nil {
        return nil, err
    }

    rows, err := sql.RowIterToRows(iter)
    if err != nil {
        return nil, err
    }

    switch len(rows) {
    case 0:
        return nil, ErrSubqueryExpectedRows.New()
    case 1:
        return rows[0][0], nil
    default:
        var values = make([]interface{}, len(rows))
        for i, row := range rows {
            values[i] = row[0]
        }

        return values, nil
    }
}

A subquery can either return a single value or a tuple with many values. For further improvement, this could be a generator (we already have that interface) and IN and so on could either take values, a tuple or a generator, etc.

Caveats: How to properly return in Type method if we don't know whether it will return a value or a tuple until Eval? An option could be to use Array instead of Tuple in this case.

And then, GetParentField will execute as follows:

func (f *GetParentField) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
    parentRow, err := ctx.ParentRow()
    if err != nil {
        return nil, err
    }

    if len(parentRow) < f.Index {
        return nil, someerror
    }

    return parentRow[f.Index], nil
}

gitbase-spark-connector-enterprise

Will require changes as well in order to push down subqueries, but the changes should be pretty straight-forward as well.

WDYT @src-d/data-processing?

Support sqlparser.Subquery statement #59

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions