Repository: src-d/go-mysql-server
Pull request #731 (2019-05-28 15:57:28)

Make boolean expression compatible with MySQL
Signed-off-by: kuba-- <kuba@sourced.tech>
Fixes: https://github.com/src-d/go-mysql-server/issues/726

kuba--: Every string is `false`. Number, Time, Duration are converted into `int` (float is round), `0` is `false`, otherwise `true`.

Patch:
diff --git a/sql/expression/boolean.go b/sql/expression/boolean.go
index 5030f8a..caef111 100644
--- a/sql/expression/boolean.go
+++ b/sql/expression/boolean.go
@@ -2,6 +2,7 @@ package expression
 
 import (
 	"fmt"
+	"reflect"
 
 	"gopkg.in/src-d/go-mysql-server.v0/sql"
 )
@@ -32,7 +33,19 @@ func (e *Not) Eval(ctx *sql.Context, row sql.Row) (interface{}, error) {
 		return nil, nil
 	}
 
-	return !v.(bool), nil
+	b, ok := v.(bool)
+	if !ok {
+		v, _ = e.Type().Convert(v)
+		if v == nil {
+			return nil, nil
+		}
+
+		if b, ok = v.(bool); !ok {
+			return nil, sql.ErrInvalidType.New(reflect.TypeOf(v).String())
+		}
+	}
+
+	return !b, nil
 }
 
 func (e *Not) String() string {
diff --git a/sql/type.go b/sql/type.go
index bd07721..bb7305e 100644
--- a/sql/type.go
+++ b/sql/type.go
@@ -4,6 +4,7 @@ import (
 	"bytes"
 	"encoding/json"
 	"fmt"
+	"math"
 	"reflect"
 	"strconv"
 	"strings"
@@ -593,11 +594,38 @@ func (t booleanT) SQL(v interface{}) sqltypes.Value {
 
 // Convert implements Type interface.
 func (t booleanT) Convert(v interface{}) (interface{}, error) {
-	val, err := cast.ToBoolE(v)
-	if err != nil {
-		return nil, ErrConvertToSQL.New(t)
+	switch b := v.(type) {
+	case bool:
+		return b, nil
+	case int, int64, int32, int16, int8, uint, uint64, uint32, uint16, uint8:
+		if b != 0 {
+			return true, nil
+		}
+		return false, nil
+	case time.Duration:
+		if int64(b) != 0 {
+			return true, nil
+		}
+		return false, nil
+	case time.Time:
+		if b.UnixNano() != 0 {
+			return true, nil
+		}
+		return false, nil
+	case float32, float64:
+		if int(math.Round(v.(float64))) != 0 {
+			return true, nil
+		}
+		return false, nil
+	case string:
+		return false, fmt.Errorf("unable to cast string to bool")
+
+	case nil:
+		return nil, fmt.Errorf("unable to cast nil to bool")
+
+	default:
+		return nil, fmt.Errorf("unable to cast %#v of type %T to bool", v, v)
 	}
-	return val, nil
 }
 
 // Compare implements Type interface.


Test patch:
diff --git a/engine_test.go b/engine_test.go
index cc6c34f..5867a7a 100644
--- a/engine_test.go
+++ b/engine_test.go
@@ -1202,6 +1202,22 @@ var queries = []struct {
 		"SELECT i, COUNT(i) AS `COUNT(i)` FROM (SELECT i FROM mytable) t GROUP BY i ORDER BY i, `COUNT(i)` DESC",
 		[]sql.Row{{int64(1), int64(1)}, {int64(2), int64(1)}, {int64(3), int64(1)}},
 	},
+	{
+		"SELECT i FROM mytable WHERE NOT s ORDER BY 1 DESC",
+		[]sql.Row{
+			{int64(3)},
+			{int64(2)},
+			{int64(1)},
+		},
+	},
+	{
+		"SELECT i FROM mytable WHERE NOT(NOT i) ORDER BY 1 DESC",
+		[]sql.Row{
+			{int64(3)},
+			{int64(2)},
+			{int64(1)},
+		},
+	},
 }
 
 func TestQueries(t *testing.T) {
diff --git a/sql/expression/boolean_test.go b/sql/expression/boolean_test.go
index cb69b0d..d9ece9c 100644
--- a/sql/expression/boolean_test.go
+++ b/sql/expression/boolean_test.go
@@ -2,6 +2,7 @@ package expression
 
 import (
 	"testing"
+	"time"
 
 	"github.com/stretchr/testify/require"
 	"gopkg.in/src-d/go-mysql-server.v0/sql"
@@ -14,4 +15,9 @@ func TestNot(t *testing.T) {
 	require.False(eval(t, e, sql.NewRow(true)).(bool))
 	require.True(eval(t, e, sql.NewRow(false)).(bool))
 	require.Nil(eval(t, e, sql.NewRow(nil)))
+	require.False(eval(t, e, sql.NewRow(1)).(bool))
+	require.True(eval(t, e, sql.NewRow(0)).(bool))
+	require.False(eval(t, e, sql.NewRow(time.Now())).(bool))
+	require.False(eval(t, e, sql.NewRow(time.Second)).(bool))
+	require.True(eval(t, e, sql.NewRow("any string always false")).(bool))
 }