Repository: src-d/go-mysql-server
Pull request #700 (2019-05-03 16:14:56)

add greatest and least
- Adds MySQL `GREATEST` and `LEAST` functions.
- Sort function names in `SUPPORTED.md`.

Signed-off-by: Juanjo Alvarez <juanjo@sourced.tech>

juanjux: Type deduction implemented as suggested plus some factorizing for `NewXXX` and `Eval` to avoid duplicated code. Only missing the tests with a table and columns (currently checking learn how to do that first :)
ajnavarro: @juanjux LGTM after resolving conflicts!

Patch:
diff --git a/README.md b/README.md
index d26fa0c..4e3801b 100644
--- a/README.md
+++ b/README.md
@@ -77,10 +77,12 @@ We support and actively test against certain third-party clients to ensure compa
 |`DAYOFYEAR(date)`|Returns the day of the year of the given date.|
 |`FLOOR(number)`|Return the largest integer value that is less than or equal to `number`.|
 |`FROM_BASE64(str)`|Decodes the base64-encoded string str.|
+|`GREATEST(...)`|Returns the greatest numeric or string value.|
 |`HOUR(date)`|Returns the hours of the given date.|
 |`IFNULL(expr1, expr2)`|If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.|
 |`IS_BINARY(blob)`|Returns whether a BLOB is a binary file or not.|
 |`JSON_EXTRACT(json_doc, path, ...)`|Extracts data from a json document using json paths.|
+|`LEAST(...)`|Returns the smaller numeric or string value.|
 |`LN(X)`|Return the natural logarithm of X.|
 |`LOG(X), LOG(B, X)`|If called with one parameter, this function returns the natural logarithm of X. If called with two parameters, this function returns the logarithm of X to the base B. If X is less than or equal to 0, or if B is less than or equal to 1, then NULL is returned.|
 |`LOG10(X)`|Returns the base-10 logarithm of X.|
diff --git a/SUPPORTED.md b/SUPPORTED.md
index a6f57ad..9cc3570 100644
--- a/SUPPORTED.md
+++ b/SUPPORTED.md
@@ -85,48 +85,50 @@
 
 ## Functions
 - ARRAY_LENGTH
+- CEIL
+- CEILING
+- COALESCE
 - CONCAT
 - CONCAT_WS
+- CONNECTION_ID
+- DATABASE
+- FLOOR
+- FROM_BASE64
+- GREATEST
 - IS_BINARY
-- SPLIT
-- SUBSTRING
 - IS_BINARY
-- LOWER
-- UPPER
-- CEILING
-- CEIL
-- FLOOR
-- ROUND
-- COALESCE
-- CONNECTION_ID
-- SOUNDEX
 - JSON_EXTRACT
-- DATABASE
-- SQRT
+- LEAST
+- LN
+- LOG10
+- LOG2
+- LOWER
+- LPAD
 - POW
 - POWER
+- ROUND
 - RPAD
-- LPAD
-- LN
-- LOG2
-- LOG10
 - SLEEP
+- SOUNDEX
+- SPLIT
+- SQRT
+- SUBSTRING
 - TO_BASE64
-- FROM_BASE64
+- UPPER
 
 ## Time functions
 - DATE
+- DATE_ADD
+- DATE_SUB
 - DAY
-- WEEKDAY
 - DAYOFMONTH
 - DAYOFWEEK
 - DAYOFYEAR
 - HOUR
 - MINUTE
 - MONTH
+- NOW
 - SECOND
+- WEEKDAY
 - YEAR
 - YEARWEEK
-- NOW
-- DATE_ADD
-- DATE_SUB
diff --git a/sql/expression/function/registry.go b/sql/expression/function/registry.go
index 87a33d6..80dfa5c 100644
--- a/sql/expression/function/registry.go
+++ b/sql/expression/function/registry.go
@@ -82,4 +82,6 @@ var Defaults = []sql.Function{
 	sql.Function1{Name: "from_base64", Fn: NewFromBase64},
 	sql.FunctionN{Name: "date_add", Fn: NewDateAdd},
 	sql.FunctionN{Name: "date_sub", Fn: NewDateSub},
+	sql.FunctionN{Name: "greatest", Fn: NewGreatest},
+	sql.FunctionN{Name: "least", Fn: NewLeast},
 }


Test patch:
diff --git a/engine_test.go b/engine_test.go
index 18d1fe7..6a87c88 100644
--- a/engine_test.go
+++ b/engine_test.go
@@ -1068,6 +1068,46 @@ var queries = []struct {
 		`SELECT JSON_EXTRACT('[1, 2, 3]', '$.[0]')`,
 		[]sql.Row{{float64(1)}},
 	},
+	{
+		`SELECT GREATEST(1, 2, 3, 4)`,
+		[]sql.Row{{int64(4)}},
+	},
+	{
+		`SELECT GREATEST(1, 2, "3", 4)`,
+		[]sql.Row{{float64(4)}},
+	},
+	{
+		`SELECT GREATEST(1, 2, "9", "foo999")`,
+		[]sql.Row{{float64(9)}},
+	},
+	{
+		`SELECT GREATEST("aaa", "bbb", "ccc")`,
+		[]sql.Row{{"ccc"}},
+	},
+	{
+		`SELECT GREATEST(i, s) FROM mytable`,
+		[]sql.Row{{float64(1)}, {float64(2)}, {float64(3)}},
+	},
+	{
+		`SELECT LEAST(1, 2, 3, 4)`,
+		[]sql.Row{{int64(1)}},
+	},
+	{
+		`SELECT LEAST(1, 2, "3", 4)`,
+		[]sql.Row{{float64(1)}},
+	},
+	{
+		`SELECT LEAST(1, 2, "9", "foo999")`,
+		[]sql.Row{{float64(1)}},
+	},
+	{
+		`SELECT LEAST("aaa", "bbb", "ccc")`,
+		[]sql.Row{{"aaa"}},
+	},
+	{
+		`SELECT LEAST(i, s) FROM mytable`,
+		[]sql.Row{{float64(1)}, {float64(2)}, {float64(3)}},
+	},
 }
 
 func TestQueries(t *testing.T) {
diff --git a/sql/expression/function/greatest_least.go b/sql/expression/function/greatest_least.go
new file mode 100644
index 0000000..f7bed5e
--- /dev/null
+++ b/sql/expression/function/greatest_least.go
@@ -0,0 +1,336 @@
+package function
+
+import (
+	"fmt"
+	"strconv"
+	"strings"
+
+	"gopkg.in/src-d/go-errors.v1"
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+)
+
+var ErrUintOverflow = errors.NewKind(
+	"Unsigned integer too big to fit on signed integer")
+
+// compEval is used to implement Greatest/Least Eval() using a comparison function
+func compEval(
+	returnType sql.Type,
+	args []sql.Expression,
+	ctx *sql.Context,
+	row sql.Row,
+	cmp compareFn,
+) (interface{}, error) {
+
+	if returnType == sql.Null {
+		return nil, nil
+	}
+
+	var selectedNum float64
+	var selectedString string
+
+	for i, arg := range args {
+		val, err := arg.Eval(ctx, row)
+		if err != nil {
+			return nil, err
+		}
+
+		switch t := val.(type) {
+		case int, int8, int16, int32, int64, uint,
+			uint8, uint16, uint32, uint64:
+			switch x := t.(type) {
+			case int:
+				t = int64(x)
+			case int8:
+				t = int64(x)
+			case int16:
+				t = int64(x)
+			case int32:
+				t = int64(x)
+			case uint:
+				i := int64(x)
+				if i < 0 {
+					return nil, ErrUintOverflow.New()
+				}
+				t = i
+			case uint64:
+				i := int64(x)
+				if i < 0 {
+					return nil, ErrUintOverflow.New()
+				}
+				t = i
+			case uint8:
+				t = int64(x)
+			case uint16:
+				t = int64(x)
+			case uint32:
+				t = int64(x)
+			}
+			ival := t.(int64)
+			if i == 0 || cmp(ival, int64(selectedNum)) {
+				selectedNum = float64(ival)
+			}
+		case float32, float64:
+			if x, ok := t.(float32); ok {
+				t = float64(x)
+			}
+
+			fval := t.(float64)
+			if i == 0 || cmp(fval, float64(selectedNum)) {
+				selectedNum = fval
+			}
+
+		case string:
+			if returnType == sql.Text && (i == 0 || cmp(t, selectedString)) {
+				selectedString = t
+			}
+
+			fval, err := strconv.ParseFloat(t, 64)
+			if err != nil {
+				// MySQL just ignores non numerically convertible string arguments
+				// when mixed with numeric ones
+				continue
+			}
+
+			if i == 0 || cmp(fval, selectedNum) {
+				selectedNum = fval
+			}
+		default:
+			return nil, ErrUnsupportedType.New(t)
+		}
+
+	}
+
+	switch returnType {
+	case sql.Int64:
+		return int64(selectedNum), nil
+	case sql.Text:
+		return selectedString, nil
+	}
+
+	// sql.Float64
+	return float64(selectedNum), nil
+}
+
+// compRetType is used to determine the type from args based on the rules described for
+// Greatest/Least
+func compRetType(args ...sql.Expression) (sql.Type, error) {
+	if len(args) == 0 {
+		return nil, sql.ErrInvalidArgumentNumber.New("LEAST", "1 or more", 0)
+	}
+
+	allString := true
+	allInt := true
+
+	for _, arg := range args {
+		argType := arg.Type()
+		if sql.IsTuple(argType) {
+			return nil, sql.ErrInvalidType.New("tuple")
+		} else if sql.IsNumber(argType) {
+			allString = false
+			if sql.IsDecimal(argType) {
+				allString = false
+				allInt = false
+			}
+		} else if sql.IsText(argType) {
+			allInt = false
+		} else if argType == sql.Null {
+			// When a Null is present the return will always de Null
+			return sql.Null, nil
+		} else {
+			return nil, ErrUnsupportedType.New(argType)
+		}
+	}
+
+	if allString {
+		return sql.Text, nil
+	} else if allInt {
+		return sql.Int64, nil
+	}
+
+	return sql.Float64, nil
+}
+
+// Greatest returns the argument with the greatest numerical or string value. It allows for
+// numeric (ints anf floats) and string arguments and will return the used type
+// when all arguments are of the same type or floats if there are numerically
+// convertible strings or integers mixed with floats. When ints or floats
+// are mixed with non numerically convertible strings, those are ignored.
+type Greatest struct {
+	Args       []sql.Expression
+	returnType sql.Type
+}
+
+// ErrUnsupportedType is returned when an argument to Greatest or Latest is not numeric or string
+var ErrUnsupportedType = errors.NewKind("unsupported type for greatest/latest argument: %T")
+
+// NewGreatest creates a new Greatest UDF
+func NewGreatest(args ...sql.Expression) (sql.Expression, error) {
+	retType, err := compRetType(args...)
+	if err != nil {
+		return nil, err
+	}
+
+	return &Greatest{args, retType}, nil
+}
+
+// Type implements the Expression interface.
+func (f *Greatest) Type() sql.Type { return f.returnType }
+
+// IsNullable implements the Expression interface.
+func (f *Greatest) IsNullable() bool {
+	for _, arg := range f.Args {
+		if arg.IsNullable() {
+			return true
+		}
+	}
+	return false
+}
+
+func (f *Greatest) String() string {
+	var args = make([]string, len(f.Args))
+	for i, arg := range f.Args {
+		args[i] = arg.String()
+	}
+	return fmt.Sprintf("greatest(%s)", strings.Join(args, ", "))
+}
+
+// TransformUp implements the Expression interface.
+func (f *Greatest) TransformUp(fn sql.TransformExprFunc) (sql.Expression, error) {
+	var args = make([]sql.Expression, len(f.Args))
+	for i, arg := range f.Args {
+		a, err := arg.TransformUp(fn)
+		if err != nil {
+			return nil, err
+		}
+		args[i] = a
+	}
+
+	expr, err := NewGreatest(args...)
+	if err != nil {
+		return nil, err
+	}
+
+	return fn(expr)
+}
+
+// Resolved implements the Expression interface.
+func (f *Greatest) Resolved() bool {
+	for _, arg := range f.Args {
+		if !arg.Resolved() {
+			return false
+		}
+	}
+	return true
+}
+
+// Children implements the Expression interface.
+func (f *Greatest) Children() []sql.Expression { return f.Args }
+
+type compareFn func(interface{}, interface{}) bool
+
+func greaterThan(a, b interface{}) bool {
+	switch i := a.(type) {
+	case int64:
+		return i > b.(int64)
+	case float64:
+		return i > b.(float64)
+	case string:
+		return i > b.(string)
+	}
+	panic("Implementation error on greaterThan")
+}
+
+func lessThan(a, b interface{}) bool {
+	switch i := a.(type) {
+	case int64:
+		return i < b.(int64)
+	case float64:
+		return i < b.(float64)
+	case string:
+		return i < b.(string)
+	}
+	panic("Implementation error on lessThan")
+}
+
+// Eval implements the Expression interface.
+func (f *Greatest) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
+	return compEval(f.returnType, f.Args, ctx, row, greaterThan)
+}
+
+// Least returns the argument with the least numerical or string value. It allows for
+// numeric (ints anf floats) and string arguments and will return the used type
+// when all arguments are of the same type or floats if there are numerically
+// convertible strings or integers mixed with floats. When ints or floats
+// are mixed with non numerically convertible strings, those are ignored.
+type Least struct {
+	Args       []sql.Expression
+	returnType sql.Type
+}
+
+// NewLeast creates a new Least UDF
+func NewLeast(args ...sql.Expression) (sql.Expression, error) {
+	retType, err := compRetType(args...)
+	if err != nil {
+		return nil, err
+	}
+
+	return &Least{args, retType}, nil
+}
+
+// Type implements the Expression interface.
+func (f *Least) Type() sql.Type { return f.returnType }
+
+// IsNullable implements the Expression interface.
+func (f *Least) IsNullable() bool {
+	for _, arg := range f.Args {
+		if arg.IsNullable() {
+			return true
+		}
+	}
+	return false
+}
+
+func (f *Least) String() string {
+	var args = make([]string, len(f.Args))
+	for i, arg := range f.Args {
+		args[i] = arg.String()
+	}
+	return fmt.Sprintf("least(%s)", strings.Join(args, ", "))
+}
+
+// TransformUp implements the Expression interface.
+func (f *Least) TransformUp(fn sql.TransformExprFunc) (sql.Expression, error) {
+	var args = make([]sql.Expression, len(f.Args))
+	for i, arg := range f.Args {
+		a, err := arg.TransformUp(fn)
+		if err != nil {
+			return nil, err
+		}
+		args[i] = a
+	}
+
+	expr, err := NewLeast(args...)
+	if err != nil {
+		return nil, err
+	}
+
+	return fn(expr)
+}
+
+// Resolved implements the Expression interface.
+func (f *Least) Resolved() bool {
+	for _, arg := range f.Args {
+		if !arg.Resolved() {
+			return false
+		}
+	}
+	return true
+}
+
+// Children implements the Expression interface.
+func (f *Least) Children() []sql.Expression { return f.Args }
+
+// Eval implements the Expression interface.
+func (f *Least) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
+	return compEval(f.returnType, f.Args, ctx, row, lessThan)
+}
diff --git a/sql/expression/function/greatest_least_test.go b/sql/expression/function/greatest_least_test.go
new file mode 100644
index 0000000..3d2c451
--- /dev/null
+++ b/sql/expression/function/greatest_least_test.go
@@ -0,0 +1,213 @@
+package function
+
+import (
+	"github.com/stretchr/testify/require"
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+	"gopkg.in/src-d/go-mysql-server.v0/sql/expression"
+	"testing"
+	"unsafe"
+)
+
+func TestGreatest(t *testing.T) {
+	testCases := []struct {
+		name     string
+		args     []sql.Expression
+		expected interface{}
+	}{
+		{
+			"null",
+			[]sql.Expression{
+				expression.NewLiteral(nil, sql.Null),
+				expression.NewLiteral(5, sql.Int64),
+				expression.NewLiteral(1, sql.Int64),
+			},
+			nil,
+		},
+		{
+			"negative and all ints",
+			[]sql.Expression{
+				expression.NewLiteral(int64(-1), sql.Int64),
+				expression.NewLiteral(int64(5), sql.Int64),
+				expression.NewLiteral(int64(1), sql.Int64),
+			},
+			int64(5),
+		},
+		{
+			"string mixed",
+			[]sql.Expression{
+				expression.NewLiteral(string("9"), sql.Text),
+				expression.NewLiteral(int64(5), sql.Int64),
+				expression.NewLiteral(int64(1), sql.Int64),
+			},
+			float64(9),
+		},
+		{
+			"unconvertible string mixed ignored",
+			[]sql.Expression{
+				expression.NewLiteral(string("10.5"), sql.Text),
+				expression.NewLiteral(string("foobar"), sql.Int64),
+				expression.NewLiteral(int64(5), sql.Int64),
+				expression.NewLiteral(int64(1), sql.Int64),
+			},
+			float64(10.5),
+		},
+		{
+			"float mixed",
+			[]sql.Expression{
+				expression.NewLiteral(float64(10.0), sql.Float64),
+				expression.NewLiteral(int(5), sql.Int64),
+				expression.NewLiteral(int(1), sql.Int64),
+			},
+			float64(10.0),
+		},
+		{
+			"all strings",
+			[]sql.Expression{
+				expression.NewLiteral("aaa", sql.Text),
+				expression.NewLiteral("bbb", sql.Text),
+				expression.NewLiteral("9999", sql.Text),
+				expression.NewLiteral("", sql.Text),
+			},
+			"bbb",
+		},
+		{
+			"all strings and empty",
+			[]sql.Expression{
+				expression.NewLiteral("aaa", sql.Text),
+				expression.NewLiteral("bbb", sql.Text),
+				expression.NewLiteral("9999", sql.Text),
+				expression.NewLiteral("", sql.Text),
+			},
+			"bbb",
+		},
+	}
+
+	for _, tt := range testCases {
+		t.Run(tt.name, func(t *testing.T) {
+			require := require.New(t)
+
+			f, err := NewGreatest(tt.args...)
+			require.NoError(err)
+
+			output, err := f.Eval(sql.NewEmptyContext(), nil)
+			require.NoError(err)
+			require.Equal(tt.expected, output)
+		})
+	}
+}
+
+func TestGreatestUnsignedOverflow(t *testing.T) {
+	require := require.New(t)
+
+	var x int
+	var gr sql.Expression
+	var err error
+
+	switch unsafe.Sizeof(x) {
+	case 4:
+		gr, err = NewGreatest(
+			expression.NewLiteral(int32(1), sql.Int32),
+			expression.NewLiteral(uint32(4294967295), sql.Uint32),
+		)
+		require.NoError(err)
+	case 8:
+		gr, err = NewGreatest(
+			expression.NewLiteral(int64(1), sql.Int64),
+			expression.NewLiteral(uint64(18446744073709551615), sql.Uint64),
+		)
+		require.NoError(err)
+	default:
+		// non 32/64 bits??
+		return
+	}
+
+	_, err = gr.Eval(sql.NewEmptyContext(), nil)
+	require.EqualError(err, "Unsigned integer too big to fit on signed integer")
+}
+
+func TestLeast(t *testing.T) {
+	testCases := []struct {
+		name     string
+		args     []sql.Expression
+		expected interface{}
+	}{
+		{
+			"null",
+			[]sql.Expression{
+				expression.NewLiteral(nil, sql.Null),
+				expression.NewLiteral(5, sql.Int64),
+				expression.NewLiteral(1, sql.Int64),
+			},
+			nil,
+		},
+		{
+			"negative and all ints",
+			[]sql.Expression{
+				expression.NewLiteral(int64(-1), sql.Int64),
+				expression.NewLiteral(int64(5), sql.Int64),
+				expression.NewLiteral(int64(1), sql.Int64),
+			},
+			int64(-1),
+		},
+		{
+			"string mixed",
+			[]sql.Expression{
+				expression.NewLiteral(string("10"), sql.Text),
+				expression.NewLiteral(int64(5), sql.Int64),
+				expression.NewLiteral(int64(1), sql.Int64),
+			},
+			float64(1),
+		},
+		{
+			"unconvertible string mixed ignored",
+			[]sql.Expression{
+				expression.NewLiteral(string("10.5"), sql.Text),
+				expression.NewLiteral(string("foobar"), sql.Int64),
+				expression.NewLiteral(int64(5), sql.Int64),
+				expression.NewLiteral(int64(1), sql.Int64),
+			},
+			float64(1),
+		},
+		{
+			"float mixed",
+			[]sql.Expression{
+				expression.NewLiteral(float64(10.0), sql.Float64),
+				expression.NewLiteral(int(5), sql.Int64),
+				expression.NewLiteral(int(1), sql.Int64),
+			},
+			float64(1.0),
+		},
+		{
+			"all strings",
+			[]sql.Expression{
+				expression.NewLiteral("aaa", sql.Text),
+				expression.NewLiteral("bbb", sql.Text),
+				expression.NewLiteral("9999", sql.Text),
+			},
+			"9999",
+		},
+		{
+			"all strings and empty",
+			[]sql.Expression{
+				expression.NewLiteral("aaa", sql.Text),
+				expression.NewLiteral("bbb", sql.Text),
+				expression.NewLiteral("9999", sql.Text),
+				expression.NewLiteral("", sql.Text),
+			},
+			"",
+		},
+	}
+
+	for _, tt := range testCases {
+		t.Run(tt.name, func(t *testing.T) {
+			require := require.New(t)
+
+			f, err := NewLeast(tt.args...)
+			require.NoError(err)
+
+			output, err := f.Eval(sql.NewEmptyContext(), nil)
+			require.NoError(err)
+			require.Equal(tt.expected, output)
+		})
+	}
+}