Repository: src-d/go-mysql-server (go)

Issue:
Implement show fields from
Implement `SHOW FIELDS FROM <table>` statement.

See: https://github.com/src-d/gitbase/issues/361#issuecomment-427285397

Pull request:
 sql: implement SHOW FIELDS statement
Depends on #417 
Closes #404 

Caveats:

- Vitess doesn't support EXTENDED, so we don't do that either
- We don't have collations, so I set as default utf8_bin, which is what we're doing

Patch:
diff --git a/sql/parse/parse.go b/sql/parse/parse.go
index 74e695a..540d321 100644
--- a/sql/parse/parse.go
+++ b/sql/parse/parse.go
@@ -167,6 +167,37 @@ func convertShow(s *sqlparser.Show) (sql.Node, error) {
 		return plan.NewShowTables(&sql.UnresolvedDatabase{}), nil
 	case sqlparser.KeywordString(sqlparser.DATABASES):
 		return plan.NewShowDatabases(), nil
+	case sqlparser.KeywordString(sqlparser.FIELDS), sqlparser.KeywordString(sqlparser.COLUMNS):
+		// TODO(erizocosmico): vitess parser does not support EXTENDED.
+		table := plan.NewUnresolvedTable(s.OnTable.Name.String())
+		full := s.ShowTablesOpt.Full != ""
+
+		var node sql.Node = plan.NewShowColumns(full, table)
+
+		if s.ShowTablesOpt != nil && s.ShowTablesOpt.Filter != nil {
+			if s.ShowTablesOpt.Filter.Like != "" {
+				pattern := expression.NewLiteral(s.ShowTablesOpt.Filter.Like, sql.Text)
+
+				node = plan.NewFilter(
+					expression.NewLike(
+						expression.NewUnresolvedColumn("Field"),
+						pattern,
+					),
+					node,
+				)
+			}
+
+			if s.ShowTablesOpt.Filter.Filter != nil {
+				filter, err := exprToExpression(s.ShowTablesOpt.Filter.Filter)
+				if err != nil {
+					return nil, err
+				}
+
+				node = plan.NewFilter(filter, node)
+			}
+		}
+
+		return node, nil
 	default:
 		unsupportedShow := fmt.Sprintf("SHOW %s", s.Type)
 		return nil, ErrUnsupportedFeature.New(unsupportedShow)
@@ -600,14 +631,13 @@ func exprToExpression(e sqlparser.Expr) (sql.Expression, error) {
 	case *sqlparser.NullVal:
 		return expression.NewLiteral(nil, sql.Null), nil
 	case *sqlparser.ColName:
-		// TODO: add handling of case sensitiveness.
 		if !v.Qualifier.IsEmpty() {
 			return expression.NewUnresolvedQualifiedColumn(
 				v.Qualifier.Name.String(),
-				v.Name.Lowered(),
+				v.Name.String(),
 			), nil
 		}
-		return expression.NewUnresolvedColumn(v.Name.Lowered()), nil
+		return expression.NewUnresolvedColumn(v.Name.String()), nil
 	case *sqlparser.FuncExpr:
 		exprs, err := selectExprsToExpressions(v.Exprs)
 		if err != nil {
diff --git a/sql/plan/showcolumns.go b/sql/plan/showcolumns.go
new file mode 100644
index 0000000..ac5eba4
--- /dev/null
+++ b/sql/plan/showcolumns.go
@@ -0,0 +1,136 @@
+package plan
+
+import (
+	"fmt"
+
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+)
+
+// ShowColumns shows the columns details of a table.
+type ShowColumns struct {
+	UnaryNode
+	Full bool
+}
+
+const defaultCollation = "utf8_bin"
+
+var (
+	showColumnsSchema = sql.Schema{
+		{Name: "Field", Type: sql.Text},
+		{Name: "Type", Type: sql.Text},
+		{Name: "Null", Type: sql.Text},
+		{Name: "Key", Type: sql.Text},
+		{Name: "Default", Type: sql.Text, Nullable: true},
+		{Name: "Extra", Type: sql.Text},
+	}
+
+	showColumnsFullSchema = sql.Schema{
+		{Name: "Field", Type: sql.Text},
+		{Name: "Type", Type: sql.Text},
+		{Name: "Collation", Type: sql.Text, Nullable: true},
+		{Name: "Null", Type: sql.Text},
+		{Name: "Key", Type: sql.Text},
+		{Name: "Default", Type: sql.Text, Nullable: true},
+		{Name: "Extra", Type: sql.Text},
+		{Name: "Privileges", Type: sql.Text},
+		{Name: "Comment", Type: sql.Text},
+	}
+)
+
+// NewShowColumns creates a new ShowColumns node.
+func NewShowColumns(full bool, child sql.Node) *ShowColumns {
+	return &ShowColumns{UnaryNode{Child: child}, full}
+}
+
+var _ sql.Node = (*ShowColumns)(nil)
+
+// Schema implements the sql.Node interface.
+func (s *ShowColumns) Schema() sql.Schema {
+	if s.Full {
+		return showColumnsFullSchema
+	}
+	return showColumnsSchema
+}
+
+// RowIter creates a new ShowColumns node.
+func (s *ShowColumns) RowIter(ctx *sql.Context) (sql.RowIter, error) {
+	span, ctx := ctx.Span("plan.ShowColumns")
+
+	schema := s.Child.Schema()
+	var rows = make([]sql.Row, len(schema))
+	for i, col := range schema {
+		var row sql.Row
+		var collation interface{}
+		if col.Type == sql.Text {
+			collation = defaultCollation
+		}
+
+		var null = "NO"
+		if col.Nullable {
+			null = "YES"
+		}
+
+		var defaultVal string
+		if col.Default != nil {
+			defaultVal = fmt.Sprint(col.Default)
+		}
+
+		if s.Full {
+			row = sql.Row{
+				col.Name,
+				col.Type.String(),
+				collation,
+				null,
+				"", // Key
+				defaultVal,
+				"", // Extra
+				"", // Privileges
+				"", // Comment
+			}
+		} else {
+			row = sql.Row{
+				col.Name,
+				col.Type.String(),
+				null,
+				"", // Key
+				defaultVal,
+				"", // Extra
+			}
+		}
+
+		rows[i] = row
+	}
+
+	return sql.NewSpanIter(span, sql.RowsToRowIter(rows...)), nil
+}
+
+// TransformUp creates a new ShowColumns node.
+func (s *ShowColumns) TransformUp(f sql.TransformNodeFunc) (sql.Node, error) {
+	child, err := s.Child.TransformUp(f)
+	if err != nil {
+		return nil, err
+	}
+
+	return f(NewShowColumns(s.Full, child))
+}
+
+// TransformExpressionsUp creates a new ShowColumns node.
+func (s *ShowColumns) TransformExpressionsUp(f sql.TransformExprFunc) (sql.Node, error) {
+	child, err := s.Child.TransformExpressionsUp(f)
+	if err != nil {
+		return nil, err
+	}
+
+	return NewShowColumns(s.Full, child), nil
+}
+
+func (s *ShowColumns) String() string {
+	tp := sql.NewTreePrinter()
+	if s.Full {
+		_ = tp.WriteNode("ShowColumns(full)")
+	} else {
+		_ = tp.WriteNode("ShowColumns")
+	}
+	_ = tp.WriteChildren(s.Child.String())
+	return tp.String()
+}


Test patch:
diff --git a/engine_test.go b/engine_test.go
index ab6c794..b8cab89 100644
--- a/engine_test.go
+++ b/engine_test.go
@@ -342,6 +342,32 @@ var queries = []struct {
 			{"first row"},
 		},
 	},
+	{
+		`SHOW COLUMNS FROM mytable`,
+		[]sql.Row{
+			{"i", "INT64", "NO", "", "", ""},
+			{"s", "TEXT", "NO", "", "", ""},
+		},
+	},
+	{
+		`SHOW COLUMNS FROM mytable WHERE Field = 'i'`,
+		[]sql.Row{
+			{"i", "INT64", "NO", "", "", ""},
+		},
+	},
+	{
+		`SHOW COLUMNS FROM mytable LIKE 'i'`,
+		[]sql.Row{
+			{"i", "INT64", "NO", "", "", ""},
+		},
+	},
+	{
+		`SHOW FULL COLUMNS FROM mytable`,
+		[]sql.Row{
+			{"i", "INT64", nil, "NO", "", "", "", "", ""},
+			{"s", "TEXT", "utf8_bin", "NO", "", "", "", "", ""},
+		},
+	},
 }
 
 func TestQueries(t *testing.T) {
diff --git a/sql/parse/parse_test.go b/sql/parse/parse_test.go
index 03b8cc1..ca8c224 100644
--- a/sql/parse/parse_test.go
+++ b/sql/parse/parse_test.go
@@ -746,6 +746,22 @@ var fixtures = map[string]sql.Node{
 			plan.NewUnresolvedTable("foo"),
 		),
 	),
+	`SHOW FIELDS FROM foo`:       plan.NewShowColumns(false, plan.NewUnresolvedTable("foo")),
+	`SHOW FULL COLUMNS FROM foo`: plan.NewShowColumns(true, plan.NewUnresolvedTable("foo")),
+	`SHOW FIELDS FROM foo WHERE Field = 'bar'`: plan.NewFilter(
+		expression.NewEquals(
+			expression.NewUnresolvedColumn("Field"),
+			expression.NewLiteral("bar", sql.Text),
+		),
+		plan.NewShowColumns(false, plan.NewUnresolvedTable("foo")),
+	),
+	`SHOW FIELDS FROM foo LIKE 'bar'`: plan.NewFilter(
+		expression.NewLike(
+			expression.NewUnresolvedColumn("Field"),
+			expression.NewLiteral("bar", sql.Text),
+		),
+		plan.NewShowColumns(false, plan.NewUnresolvedTable("foo")),
+	),
 }
 
 func TestParse(t *testing.T) {
diff --git a/sql/plan/showcolumns_test.go b/sql/plan/showcolumns_test.go
new file mode 100644
index 0000000..c65450e
--- /dev/null
+++ b/sql/plan/showcolumns_test.go
@@ -0,0 +1,56 @@
+package plan
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gopkg.in/src-d/go-mysql-server.v0/mem"
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+)
+
+func TestShowColumns(t *testing.T) {
+	require := require.New(t)
+
+	table := NewResolvedTable(mem.NewTable("foo", sql.Schema{
+		{Name: "a", Type: sql.Text},
+		{Name: "b", Type: sql.Int64, Nullable: true},
+		{Name: "c", Type: sql.Int64, Default: int64(1)},
+	}))
+
+	iter, err := NewShowColumns(false, table).RowIter(sql.NewEmptyContext())
+	require.NoError(err)
+
+	rows, err := sql.RowIterToRows(iter)
+	require.NoError(err)
+
+	expected := []sql.Row{
+		sql.Row{"a", "TEXT", "NO", "", "", ""},
+		sql.Row{"b", "INT64", "YES", "", "", ""},
+		sql.Row{"c", "INT64", "NO", "", "1", ""},
+	}
+
+	require.Equal(expected, rows)
+}
+func TestShowColumnsFull(t *testing.T) {
+	require := require.New(t)
+
+	table := NewResolvedTable(mem.NewTable("foo", sql.Schema{
+		{Name: "a", Type: sql.Text},
+		{Name: "b", Type: sql.Int64, Nullable: true},
+		{Name: "c", Type: sql.Int64, Default: int64(1)},
+	}))
+
+	iter, err := NewShowColumns(true, table).RowIter(sql.NewEmptyContext())
+	require.NoError(err)
+
+	rows, err := sql.RowIterToRows(iter)
+	require.NoError(err)
+
+	expected := []sql.Row{
+		sql.Row{"a", "TEXT", "utf8_bin", "NO", "", "", "", "", ""},
+		sql.Row{"b", "INT64", nil, "YES", "", "", "", "", ""},
+		sql.Row{"c", "INT64", nil, "NO", "", "1", "", "", ""},
+	}
+
+	require.Equal(expected, rows)
+}