Repository: src-d/go-mysql-server
Pull request #555 (2018-11-20 22:10:04)

Implement ifnull and nullif functions.
Signed-off-by: kuba-- <kuba@sourced.tech>

Closes: https://github.com/src-d/go-mysql-server/issues/542
Fixes: https://github.com/src-d/go-mysql-server/issues/546

This PR fixes conversion from NULL to any type and implements new functions: `NULLIF` and `IFNULL`.


Patch:
diff --git a/README.md b/README.md
index 5332499..f755501 100644
--- a/README.md
+++ b/README.md
@@ -50,14 +50,21 @@ go get gopkg.in/src-d/go-mysql-server.v0
 
 We are continuously adding more functionality to go-mysql-server. We support a subset of what is supported in MySQL, to see what is currently included check the [SUPPORTED](./SUPPORTED.md) file.
 
-# Third-party clients
+## Third-party clients
 
 We support and actively test against certain third-party clients to ensure compatibility between them and go-mysql-server. You can check out the list of supported third party clients in the [SUPPORTED_CLIENTS](./SUPPORTED_CLIENTS.md) file along with some examples on how to connect to go-mysql-server using them.
 
 ## Custom functions
 
+- `COUNT(expr)`: Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement.
+- `MIN(expr)`: Returns the minimum value of expr.
+- `MAX(expr)`: Returns the maximum value of expr.
+- `AVG(expr)`: Returns the average value of expr.
+- `SUM(expr)`: Returns the sum of expr.
 - `IS_BINARY(blob)`: Returns whether a BLOB is a binary file or not.
-- `SUBSTRING(str, pos)`, `SUBSTRING(str, pos, len)`: Return a substring from the provided string.
+- `SUBSTRING(str, pos)`, `SUBSTRING(str, pos, len)` : Return a substring from the provided string.
+- `SUBSTR(str, pos)`, `SUBSTR(str, pos, len)` : Return a substring from the provided string.
+- `MID(str, pos)`, `MID(str, pos, len)` : Return a substring from the provided string.
 - Date and Timestamp functions: `YEAR(date)`, `MONTH(date)`, `DAY(date)`, `WEEKDAY(date)`, `HOUR(date)`, `MINUTE(date)`, `SECOND(date)`, `DAYOFWEEK(date)`, `DAYOFYEAR(date)`.
 - `ARRAY_LENGTH(json)`: If the json representation is an array, this function returns its size.
 - `SPLIT(str,sep)`: Receives a string and a separator and returns the parts of the string split by the separator as a JSON array of strings.
@@ -70,6 +77,23 @@ We support and actively test against certain third-party clients to ensure compa
 - `ROUND(number, decimals)`: Round the `number` to `decimals` decimal places.
 - `CONNECTION_ID()`: Return the current connection ID.
 - `SOUNDEX(str)`: Returns the soundex of a string.
+- `JSON_EXTRACT(json_doc, path, ...)`:  Extracts data from a json document using json paths.
+- `LN(X)`: Return the natural logarithm of X.
+- `LOG2(X)`: Returns the base-2 logarithm of X.
+- `LOG10(X)`: Returns the base-10 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.
+- `RPAD(str, len, padstr)`: Returns the string str, right-padded with the string padstr to a length of len characters.
+- `LPAD(str, len, padstr)`: Return the string argument, left-padded with the specified string.
+- `SQRT(X)`: Returns the square root of a nonnegative number X.
+- `POW(X, Y)`, `POWER(X, Y)`: Returns the value of X raised to the power of Y.
+- `TRIM(str)`: Returns the string str with all spaces removed.
+- `LTRIM(str)`: Returns the string str with leading space characters removed.
+- `RTRIM(str)`: Returns the string str with trailing space characters removed.
+- `REVERSE(str)`: Returns the string str with the order of the characters reversed.
+- `REPEAT(str, count)`: Returns a string consisting of the string str repeated count times.
+- `REPLACE(str,from_str,to_str)`: Returns the string str with all occurrences of the string from_str replaced by the string to_str.
+- `IFNULL(expr1, expr2)`: If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
+- `NULLIF(expr1, expr2)`: Returns NULL if expr1 = expr2 is true, otherwise returns expr1.
 
 ## Example
 
diff --git a/sql/expression/function/ifnull.go b/sql/expression/function/ifnull.go
new file mode 100644
index 0000000..95c10e3
--- /dev/null
+++ b/sql/expression/function/ifnull.go
@@ -0,0 +1,81 @@
+package function
+
+import (
+	"fmt"
+
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+	"gopkg.in/src-d/go-mysql-server.v0/sql/expression"
+)
+
+// IfNull function returns the specified value IF the expression is NULL, otherwise return the expression.
+type IfNull struct {
+	expression.BinaryExpression
+}
+
+// NewIfNull returns a new IFNULL UDF
+func NewIfNull(ex, value sql.Expression) sql.Expression {
+	return &IfNull{
+		expression.BinaryExpression{
+			Left:  ex,
+			Right: value,
+		},
+	}
+}
+
+// Eval implements the Expression interface.
+func (f *IfNull) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
+	left, err := f.Left.Eval(ctx, row)
+	if err != nil {
+		return nil, err
+	}
+	if left != nil {
+		return left, nil
+	}
+
+	right, err := f.Right.Eval(ctx, row)
+	if err != nil {
+		return nil, err
+	}
+	return right, nil
+}
+
+// Type implements the Expression interface.
+func (f *IfNull) Type() sql.Type {
+	if sql.IsNull(f.Left) {
+		if sql.IsNull(f.Right) {
+			return sql.Null
+		}
+		return f.Right.Type()
+	}
+	return f.Left.Type()
+}
+
+// IsNullable implements the Expression interface.
+func (f *IfNull) IsNullable() bool {
+	if sql.IsNull(f.Left) {
+		if sql.IsNull(f.Right) {
+			return true
+		}
+		return f.Right.IsNullable()
+	}
+	return f.Left.IsNullable()
+}
+
+func (f *IfNull) String() string {
+	return fmt.Sprintf("ifnull(%s, %s)", f.Left, f.Right)
+}
+
+// TransformUp implements the Expression interface.
+func (f *IfNull) TransformUp(fn sql.TransformExprFunc) (sql.Expression, error) {
+	left, err := f.Left.TransformUp(fn)
+	if err != nil {
+		return nil, err
+	}
+
+	right, err := f.Right.TransformUp(fn)
+	if err != nil {
+		return nil, err
+	}
+
+	return fn(NewIfNull(left, right))
+}
diff --git a/sql/expression/function/nullif.go b/sql/expression/function/nullif.go
new file mode 100644
index 0000000..9307470
--- /dev/null
+++ b/sql/expression/function/nullif.go
@@ -0,0 +1,73 @@
+package function
+
+import (
+	"fmt"
+
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+	"gopkg.in/src-d/go-mysql-server.v0/sql/expression"
+)
+
+// NullIf function compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned.
+type NullIf struct {
+	expression.BinaryExpression
+}
+
+// NewNullIf returns a new NULLIF UDF
+func NewNullIf(ex1, ex2 sql.Expression) sql.Expression {
+	return &NullIf{
+		expression.BinaryExpression{
+			Left:  ex1,
+			Right: ex2,
+		},
+	}
+}
+
+// Eval implements the Expression interface.
+func (f *NullIf) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
+	if sql.IsNull(f.Left) && sql.IsNull(f.Right) {
+		return sql.Null, nil
+	}
+
+	val, err := expression.NewEquals(f.Left, f.Right).Eval(ctx, row)
+	if err != nil {
+		return nil, err
+	}
+	if b, ok := val.(bool); ok && b {
+		return sql.Null, nil
+	}
+
+	return f.Left.Eval(ctx, row)
+}
+
+// Type implements the Expression interface.
+func (f *NullIf) Type() sql.Type {
+	if sql.IsNull(f.Left) {
+		return sql.Null
+	}
+
+	return f.Left.Type()
+}
+
+// IsNullable implements the Expression interface.
+func (f *NullIf) IsNullable() bool {
+	return true
+}
+
+func (f *NullIf) String() string {
+	return fmt.Sprintf("nullif(%s, %s)", f.Left, f.Right)
+}
+
+// TransformUp implements the Expression interface.
+func (f *NullIf) TransformUp(fn sql.TransformExprFunc) (sql.Expression, error) {
+	left, err := f.Left.TransformUp(fn)
+	if err != nil {
+		return nil, err
+	}
+
+	right, err := f.Right.TransformUp(fn)
+	if err != nil {
+		return nil, err
+	}
+
+	return fn(NewNullIf(left, right))
+}
diff --git a/sql/expression/function/registry.go b/sql/expression/function/registry.go
index f1bcd32..71b475c 100644
--- a/sql/expression/function/registry.go
+++ b/sql/expression/function/registry.go
@@ -41,16 +41,16 @@ var Defaults = sql.Functions{
 	"split":         sql.Function2(NewSplit),
 	"concat":        sql.FunctionN(NewConcat),
 	"concat_ws":     sql.FunctionN(NewConcatWithSeparator),
+	"coalesce":      sql.FunctionN(NewCoalesce),
 	"lower":         sql.Function1(NewLower),
 	"upper":         sql.Function1(NewUpper),
 	"ceiling":       sql.Function1(NewCeil),
 	"ceil":          sql.Function1(NewCeil),
 	"floor":         sql.Function1(NewFloor),
 	"round":         sql.FunctionN(NewRound),
-	"coalesce":      sql.FunctionN(NewCoalesce),
-	"json_extract":  sql.FunctionN(NewJSONExtract),
 	"connection_id": sql.Function0(NewConnectionID),
 	"soundex":       sql.Function1(NewSoundex),
+	"json_extract":  sql.FunctionN(NewJSONExtract),
 	"ln":            sql.Function1(NewLogBaseFunc(float64(math.E))),
 	"log2":          sql.Function1(NewLogBaseFunc(float64(2))),
 	"log10":         sql.Function1(NewLogBaseFunc(float64(10))),
@@ -66,4 +66,6 @@ var Defaults = sql.Functions{
 	"reverse":       sql.Function1(NewReverse),
 	"repeat":        sql.Function2(NewRepeat),
 	"replace":       sql.Function3(NewReplace),
+	"ifnull":        sql.Function2(NewIfNull),
+	"nullif":        sql.Function2(NewNullIf),
 }
diff --git a/sql/type.go b/sql/type.go
index ef3e000..a22eda1 100644
--- a/sql/type.go
+++ b/sql/type.go
@@ -253,6 +253,11 @@ func (t nullT) Compare(a interface{}, b interface{}) (int, error) {
 	return 0, nil
 }
 
+// IsNull returns true if expression is nil or is Null Type, otherwise false.
+func IsNull(ex Expression) bool {
+	return ex == nil || ex.Type() == Null
+}
+
 type numberT struct {
 	t query.Type
 }
@@ -264,6 +269,10 @@ func (t numberT) Type() query.Type {
 
 // SQL implements Type interface.
 func (t numberT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	switch t.t {
 	case sqltypes.Int32:
 		return sqltypes.MakeTrusted(t.t, strconv.AppendInt(nil, cast.ToInt64(v), 10))
@@ -383,6 +392,10 @@ var TimestampLayouts = []string{
 
 // SQL implements Type interface.
 func (t timestampT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	time := MustConvert(t, v).(time.Time)
 	return sqltypes.MakeTrusted(
 		sqltypes.Timestamp,
@@ -451,6 +464,10 @@ func (t dateT) Type() query.Type {
 }
 
 func (t dateT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	time := MustConvert(t, v).(time.Time)
 	return sqltypes.MakeTrusted(
 		sqltypes.Timestamp,
@@ -500,6 +517,10 @@ func (t textT) Type() query.Type {
 
 // SQL implements Type interface.
 func (t textT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	return sqltypes.MakeTrusted(sqltypes.Text, []byte(MustConvert(t, v).(string)))
 }
 
@@ -524,6 +545,10 @@ func (t booleanT) Type() query.Type {
 
 // SQL implements Type interface.
 func (t booleanT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	b := []byte{'0'}
 	if cast.ToBool(v) {
 		b[0] = '1'
@@ -561,6 +586,10 @@ func (t blobT) Type() query.Type {
 
 // SQL implements Type interface.
 func (t blobT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	return sqltypes.MakeTrusted(sqltypes.Blob, MustConvert(t, v).([]byte))
 }
 
@@ -596,6 +625,9 @@ func (t jsonT) Type() query.Type {
 
 // SQL implements Type interface.
 func (t jsonT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
 	return sqltypes.MakeTrusted(sqltypes.TypeJSON, MustConvert(t, v).([]byte))
 }
 
@@ -624,6 +656,10 @@ func (t tupleT) Type() query.Type {
 }
 
 func (t tupleT) SQL(v interface{}) sqltypes.Value {
+	if _, ok := v.(nullT); ok {
+		return sqltypes.NULL
+	}
+
 	panic("unable to convert tuple type to SQL")
 }
 


Test patch:
diff --git a/engine_test.go b/engine_test.go
index dacc1ae..8976d0b 100644
--- a/engine_test.go
+++ b/engine_test.go
@@ -700,6 +700,72 @@ var queries = []struct {
 			{"tabletest", "InnoDB", "10", "Fixed", int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), int64(0), nil, nil, nil, "utf8_bin", nil, nil},
 		},
 	},
+	{
+		`SELECT NULL`,
+		[]sql.Row{
+			{nil},
+		},
+	},
+	{
+		`SELECT nullif('abc', NULL)`,
+		[]sql.Row{
+			{"abc"},
+		},
+	},
+	{
+		`SELECT nullif(NULL, NULL)`,
+		[]sql.Row{
+			{sql.Null},
+		},
+	},
+	{
+		`SELECT nullif(NULL, 123)`,
+		[]sql.Row{
+			{nil},
+		},
+	},
+	{
+		`SELECT nullif(123, 123)`,
+		[]sql.Row{
+			{sql.Null},
+		},
+	},
+	{
+		`SELECT nullif(123, 321)`,
+		[]sql.Row{
+			{int64(123)},
+		},
+	},
+	{
+		`SELECT ifnull(123, NULL)`,
+		[]sql.Row{
+			{int64(123)},
+		},
+	},
+	{
+		`SELECT ifnull(NULL, NULL)`,
+		[]sql.Row{
+			{nil},
+		},
+	},
+	{
+		`SELECT ifnull(NULL, 123)`,
+		[]sql.Row{
+			{int64(123)},
+		},
+	},
+	{
+		`SELECT ifnull(123, 123)`,
+		[]sql.Row{
+			{int64(123)},
+		},
+	},
+	{
+		`SELECT ifnull(123, 321)`,
+		[]sql.Row{
+			{int64(123)},
+		},
+	},
 }
 
 func TestQueries(t *testing.T) {
diff --git a/sql/expression/function/ifnull_test.go b/sql/expression/function/ifnull_test.go
new file mode 100644
index 0000000..481975b
--- /dev/null
+++ b/sql/expression/function/ifnull_test.go
@@ -0,0 +1,36 @@
+package function
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+	"gopkg.in/src-d/go-mysql-server.v0/sql/expression"
+)
+
+func TestIfNull(t *testing.T) {
+	testCases := []struct {
+		expression interface{}
+		value      interface{}
+		expected   interface{}
+	}{
+		{"foo", "bar", "foo"},
+		{"foo", "foo", "foo"},
+		{nil, "foo", "foo"},
+		{"foo", nil, "foo"},
+		{nil, nil, nil},
+		{"", nil, ""},
+	}
+
+	f := NewIfNull(
+		expression.NewGetField(0, sql.Text, "expression", true),
+		expression.NewGetField(1, sql.Text, "value", true),
+	)
+	require.Equal(t, sql.Text, f.Type())
+
+	for _, tc := range testCases {
+		v, err := f.Eval(sql.NewEmptyContext(), sql.NewRow(tc.expression, tc.value))
+		require.NoError(t, err)
+		require.Equal(t, tc.expected, v)
+	}
+}
diff --git a/sql/expression/function/nullif_test.go b/sql/expression/function/nullif_test.go
new file mode 100644
index 0000000..24dd839
--- /dev/null
+++ b/sql/expression/function/nullif_test.go
@@ -0,0 +1,36 @@
+package function
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+	"gopkg.in/src-d/go-mysql-server.v0/sql"
+	"gopkg.in/src-d/go-mysql-server.v0/sql/expression"
+)
+
+func TestNullIf(t *testing.T) {
+	testCases := []struct {
+		ex1      interface{}
+		ex2      interface{}
+		expected interface{}
+	}{
+		{"foo", "bar", "foo"},
+		{"foo", "foo", sql.Null},
+		{nil, "foo", nil},
+		{"foo", nil, "foo"},
+		{nil, nil, nil},
+		{"", nil, ""},
+	}
+
+	f := NewNullIf(
+		expression.NewGetField(0, sql.Text, "ex1", true),
+		expression.NewGetField(1, sql.Text, "ex2", true),
+	)
+	require.Equal(t, sql.Text, f.Type())
+
+	for _, tc := range testCases {
+		v, err := f.Eval(sql.NewEmptyContext(), sql.NewRow(tc.ex1, tc.ex2))
+		require.NoError(t, err)
+		require.Equal(t, tc.expected, v)
+	}
+}
diff --git a/sql/type_test.go b/sql/type_test.go
index 1a32e3e..0d4a01f 100644
--- a/sql/type_test.go
+++ b/sql/type_test.go
@@ -9,6 +9,14 @@ import (
 	"gopkg.in/src-d/go-vitess.v1/vt/proto/query"
 )
 
+func TestIsNull(t *testing.T) {
+	require.True(t, IsNull(nil))
+
+	n := numberT{sqltypes.Uint64}
+	require.Equal(t, sqltypes.NULL, n.SQL(Null))
+	require.Equal(t, sqltypes.NewUint64(0), n.SQL(uint64(0)))
+}
+
 func TestText(t *testing.T) {
 	convert(t, Text, "", "")
 	convert(t, Text, 1, "1")