Repository: proullon/ramsql (go)

Issue:
Can not use NOT IN in SELECT statements
`SELECT` statements do not accept the `NOT IN` syntax.

If a `SELECT` statement is like the following:

    SELECT foo
    FROM bar
    WHERE baz NOT IN ('qux', 'shme')

Then the parser issues an error like the following:

    Syntax error near baz not in

Pull request:
Support the NOT IN syntax in SELECT statements
`NOT IN` was not accepted as a valid condition by the `parseCondition` method.

I changed the method to accept `NOT` as a valid operator, with the `IN` operation nested right within.

The AST branch generated by parsing `baz NOT IN ('qux', 'shme')` now looks like the following:

    baz
    | NOT
    | | IN
    | | | 'qux'
    | | | 'shme'

I also made changes to support the `NOT IN` operation in the execution phase using the new `notInOperator` function.

Closes #48 

Patch:
diff --git a/engine/distinct.go b/engine/distinct.go
new file mode 100644
index 0000000..09b4c02
--- /dev/null
+++ b/engine/distinct.go
@@ -0,0 +1,110 @@
+package engine
+
+import (
+	"github.com/proullon/ramsql/engine/log"
+	"github.com/proullon/ramsql/engine/protocol"
+)
+
+type distinct struct {
+	realConn protocol.EngineConn
+	seen     seen
+	len      int
+}
+
+func distinctedConn(conn protocol.EngineConn, len int) protocol.EngineConn {
+	return &distinct{
+		realConn: conn,
+		len:      len,
+		seen:     make(seen),
+	}
+}
+
+// Not needed
+func (l *distinct) ReadStatement() (string, error) {
+	log.Debug("limit.ReadStatement: should not be used\n")
+	return "", nil
+}
+
+// Not needed
+func (l *distinct) WriteResult(last int64, ra int64) error {
+	log.Debug("limit.WriteResult: should not be used\n")
+	return nil
+}
+
+func (l *distinct) WriteError(err error) error {
+	return l.realConn.WriteError(err)
+}
+
+func (l *distinct) WriteRowHeader(header []string) error {
+	if l.len > 0 {
+		// Postgres returns only columns outside of DISTINCT ON
+		return l.realConn.WriteRowHeader(header[l.len:])
+	}
+	return l.realConn.WriteRowHeader(header)
+}
+
+func (l *distinct) WriteRow(row []string) error {
+	if l.len > 0 {
+		if l.seen.exists(row[:l.len]) {
+			return nil
+		}
+		// Postgres returns only columns outside of DISTINCT ON
+		return l.realConn.WriteRow(row[l.len:])
+	} else {
+		if l.seen.exists(row) {
+			return nil
+		}
+		return l.realConn.WriteRow(row)
+	}
+}
+
+func (l *distinct) WriteRowEnd() error {
+	return l.realConn.WriteRowEnd()
+}
+
+func (l *distinct) equalRows(a, b []string) bool {
+	if l.len > 0 {
+		if len(a) < l.len || len(b) < l.len {
+			return false
+		}
+
+		for idx := 0; idx < l.len; idx++ {
+			if a[idx] != b[idx] {
+				return false
+			}
+		}
+
+		return true
+	}
+
+	if len(a) != len(b) {
+		return false
+	}
+	for idx := range a {
+		if a[idx] != b[idx] {
+			return false
+		}
+	}
+
+	return true
+}
+
+type seen map[string]seen
+
+func (s seen) exists(r []string) bool {
+	if c, ok := s[r[0]]; ok {
+		if len(r) == 1 {
+			return true
+		}
+
+		return c.exists(r[1:])
+	}
+
+	s[r[0]] = make(seen)
+	if len(r) == 1 {
+		return false
+	}
+
+	// does not exists, but we want to populate the tree fully
+	return s[r[0]].exists(r[1:])
+}
diff --git a/engine/insert.go b/engine/insert.go
index 7c82ef9..5e399c6 100644
--- a/engine/insert.go
+++ b/engine/insert.go
@@ -4,6 +4,7 @@ import (
 	"errors"
 	"fmt"
 	"strconv"
+	"strings"
 	"time"
 
 	"github.com/proullon/ramsql/engine/log"
@@ -24,7 +25,6 @@ import (
         |-> pierre.roullon@gmail.com
 */
 func insertIntoTableExecutor(e *Engine, insertDecl *parser.Decl, conn protocol.EngineConn) error {
-
 	// Get table and concerned attributes and write lock it
 	r, attributes, err := getRelation(e, insertDecl.Decl[0])
 	if err != nil {
@@ -86,8 +86,6 @@ func getRelation(e *Engine, intoDecl *parser.Decl) (*Relation, []*parser.Decl, e
 	return r, intoDecl.Decl[0].Decl, nil
 }
 
-type f func() interface{}
-
 func insert(r *Relation, attributes []*parser.Decl, values []*parser.Decl, returnedID string) (int64, error) {
 	var assigned = false
 	var id int64
@@ -95,19 +93,34 @@ func insert(r *Relation, attributes []*parser.Decl, values []*parser.Decl, retur
 
 	// Create tuple
 	t := NewTuple()
+
+
 	for attrindex, attr := range r.table.attributes {
 		assigned = false
 
 		for x, decl := range attributes {
-
 			if attr.name == decl.Lexeme && attr.autoIncrement == false {
 				// Before adding value in tuple, check it's not a builtin func or arithmetic operation
 				switch values[x].Token {
 				case parser.NowToken:
 					t.Append(time.Now().Format(parser.DateLongFormat))
 				default:
-					t.Append(values[x].Lexeme)
-
+					switch strings.ToLower(attr.typeName) {
+					case "int64", "int":
+						val, err := strconv.ParseInt(values[x].Lexeme, 10, 64)
+						if err != nil {
+							return 0, err
+						}
+						t.Append(val)
+					case "numeric", "decimal":
+						val, err := strconv.ParseFloat(values[x].Lexeme, 64)
+						if err != nil {
+							return 0, err
+						}
+						t.Append(val)
+					default:
+						t.Append(values[x].Lexeme)
+					}
 				}
 				valuesindex = x
 				assigned = true
diff --git a/engine/operator.go b/engine/operator.go
index f8cd6b8..1b5940e 100644
--- a/engine/operator.go
+++ b/engine/operator.go
@@ -38,7 +38,7 @@ func convToDate(t interface{}) (time.Time, error) {
 		log.Debug("convToDate> unexpected type %T\n", t)
 		return time.Time{}, fmt.Errorf("unexpected internal type %T", t)
 	case string:
-		d, err :=parser.ParseDate(string(t))
+		d, err := parser.ParseDate(string(t))
 		if err != nil {
 			return time.Time{}, fmt.Errorf("cannot parse date %v", t)
 		}
@@ -205,6 +205,10 @@ func inOperator(leftValue Value, rightValue Value) bool {
 	return false
 }
 
+func notInOperator(leftValue Value, rightValue Value) bool {
+	return !inOperator(leftValue, rightValue)
+}
+
 func isNullOperator(leftValue Value, rightValue Value) bool {
 	return leftValue.v == nil
 }
diff --git a/engine/orderby.go b/engine/orderby.go
index 0e93b60..99b29d8 100644
--- a/engine/orderby.go
+++ b/engine/orderby.go
@@ -2,13 +2,10 @@ package engine
 
 import (
 	"fmt"
-	"sort"
-	"strconv"
-	"time"
-
 	"github.com/proullon/ramsql/engine/log"
 	"github.com/proullon/ramsql/engine/parser"
 	"github.com/proullon/ramsql/engine/protocol"
+	"sort"
 )
 
 //    |-> order
@@ -16,7 +13,6 @@ import (
 //        |-> desc
 func orderbyExecutor(attr *parser.Decl, tables []*Table) (selectFunctor, error) {
 	f := &orderbyFunctor{}
-	f.buffer = make(map[int64][][]string)
 
 	// first subdecl should be attribute
 	if len(attr.Decl) < 1 {
@@ -27,21 +23,28 @@ func orderbyExecutor(attr *parser.Decl, tables []*Table) (selectFunctor, error)
 	if len(tables) < 1 {
 		return nil, fmt.Errorf("cannot guess the table of attribute %s for order", attr.Decl[0].Lexeme)
 	}
-	if len(attr.Decl[0].Decl) > 0 {
-		f.orderby = attr.Decl[0].Decl[0].Lexeme + "." + attr.Decl[0].Lexeme
-	} else {
-		f.orderby = tables[0].name + "." + attr.Decl[0].Lexeme
-	}
-	// if second subdecl is present, it's either asc or desc
-	// default is asc anyway
-	if len(attr.Decl) == 2 && attr.Decl[1].Token == parser.AscToken {
-		f.asc = true
+
+	for _, d := range attr.Decl {
+		var desc bool
+		if len(d.Decl) >= 1 && d.Decl[0].Lexeme == "desc" {
+			desc = true
+		}
+
+		f.orderBy = append(f.orderBy, &orderBy{
+			column: tables[0].name + "." + d.Lexeme,
+			desc:   desc,
+		})
 	}
 
-	log.Debug("orderbyExecutor> you must order by '%s', asc: %v\n", f.orderby, f.asc)
 	return f, nil
 }
 
+type orderBy struct {
+	column     string
+	desc       bool
+	comparator comparator
+}
+
 // ok so our buffer is a map of INDEX -> slice of ROW
 // let's say we can only order by integer values
 // and yeah we can have multiple row with one value, order is then random
@@ -50,10 +53,8 @@ type orderbyFunctor struct {
 	conn       protocol.EngineConn
 	attributes []string
 	alias      []string
-	orderby    string
-	asc        bool
-	buffer     map[int64][][]string
 	order      orderer
+	orderBy    []*orderBy
 }
 
 func (f *orderbyFunctor) Init(e *Engine, conn protocol.EngineConn, attr []string, alias []string) error {
@@ -66,22 +67,14 @@ func (f *orderbyFunctor) Init(e *Engine, conn protocol.EngineConn, attr []string
 }
 
 func (f *orderbyFunctor) FeedVirtualRow(vrow virtualRow) error {
-
-	// search key
-	val, ok := vrow[f.orderby]
-	if !ok {
-		return fmt.Errorf("could not find ordering attribute %s in virtual row", f.orderby)
-	}
-
 	if f.order == nil { // first time
-		o, err := initOrderer(val, f.attributes)
-		if err != nil {
-			return err
-		}
+		o := &genericOrderer{orderBy: f.orderBy}
+		o.init(f.attributes)
+
 		f.order = o
 	}
 
-	return f.order.Feed(val, vrow)
+	return f.order.Feed(Value{}, vrow)
 }
 
 func (f *orderbyFunctor) Done() error {
@@ -92,10 +85,8 @@ func (f *orderbyFunctor) Done() error {
 		return f.conn.WriteRowEnd()
 	}
 
-	if f.asc {
-		f.order.Sort()
-	} else {
-		f.order.SortReverse()
+	if err := f.order.Sort(); err != nil {
+		return err
 	}
 
 	err := f.order.Write(f.conn)
@@ -106,201 +97,205 @@ func (f *orderbyFunctor) Done() error {
 	return f.conn.WriteRowEnd()
 }
 
-type sortSlice []int64
-
-func (s sortSlice) Less(i, j int) bool {
-	return s[i] < s[j]
-}
-
-func (s sortSlice) Swap(i, j int) {
-	s[i], s[j] = s[j], s[i]
-}
-
-func (s sortSlice) Len() int {
-	return len(s)
-}
-
 type orderer interface {
 	Feed(key Value, vrow virtualRow) error
 	Sort() error
-	SortReverse() error
 	Write(conn protocol.EngineConn) error
 }
 
-func initOrderer(val Value, attr []string) (orderer, error) {
-	log.Debug("initOrder: %v\n", val)
-	_, err := strconv.ParseInt(fmt.Sprintf("%v", val.v), 10, 64)
-	if err == nil {
-		log.Debug("initOrderer> key is in fact an integer\n")
-		i := &intOrderer{}
-		i.init(attr)
-		return i, nil
-	}
-
-	/* OK SO
-	 * Is the key an integer, a string or a date ?
-	 */
-	switch v := val.v.(type) {
-	case string:
-		s := &stringOrderer{}
-		s.init(attr)
-		return s, nil
-	case int, int64:
-		i := &intOrderer{}
-		i.init(attr)
-		return i, nil
-	/*case time.Time:
-	d := dateOrderer{}
-	d.init()
-	return d*/
-	default:
-		return nil, fmt.Errorf("cannot order %T with value %v", val.v, v)
-	}
-}
-
-type stringOrderer struct {
-	buffer     map[string][][]string
+type genericOrderer struct {
+	buffer     map[string][][]interface{}
 	attributes []string
 	keys       []string
+	orderBy    []*orderBy
 }
 
-func (i *stringOrderer) init(attr []string) {
-	i.buffer = make(map[string][][]string)
-	i.attributes = attr
+func (o *genericOrderer) init(attr []string) {
+	o.buffer = make(map[string][][]interface{})
+	o.attributes = attr
 }
 
-func (i *stringOrderer) Feed(val Value, vrow virtualRow) error {
-	var row []string
-
-	key, ok := val.v.(string)
-	if !ok {
-		return fmt.Errorf("error ordering because of value %v", val.v)
-	}
+func (o *genericOrderer) Feed(_ Value, vrow virtualRow) error {
+	var row []interface{}
 
-	for _, attr := range i.attributes {
-		val, ok := vrow[attr]
-		if !ok {
-			return fmt.Errorf("could not select attribute %s", attr)
+	var key string
+	for _, ob := range o.orderBy {
+		if key != "" {
+			key += "."
 		}
-		row = append(row, fmt.Sprintf("%v", val.v))
-	}
-
-	// now instead of writing row, we will find the ordering key and put in in our buffer
-	i.buffer[key] = append(i.buffer[key], row)
-	return nil
-}
-
-func (i *stringOrderer) Sort() error {
-	// now we have to sort our key
-	i.keys = make([]string, len(i.buffer))
-	var index int64
-	for k := range i.buffer {
-		i.keys[index] = k
-		index++
-	}
-
-	sort.Sort(sort.StringSlice(i.keys))
-	return nil
-}
-
-func (i *stringOrderer) SortReverse() error {
-	// now we have to sort our key
-	i.keys = make([]string, len(i.buffer))
-	var index int64
-	for k := range i.buffer {
-		i.keys[index] = k
-		index++
-	}
 
-	sort.Sort(sort.Reverse(sort.StringSlice(i.keys)))
-	return nil
-}
+		key += fmt.Sprint(vrow[ob.column].v)
 
-func (i *stringOrderer) Write(conn protocol.EngineConn) error {
-	// now write ordered rows
-	for _, key := range i.keys {
-		rows := i.buffer[key]
-		for index := range rows {
-			err := conn.WriteRow(rows[index])
-			if err != nil {
-				return err
-			}
+		if ob.comparator != nil {
+			continue
 		}
-	}
-	return nil
-}
-
-type intOrderer struct {
-	buffer     map[int64][][]string
-	attributes []string
-	keys       []int64
-}
 
-func (i *intOrderer) init(attr []string) {
-	i.buffer = make(map[int64][][]string)
-	i.attributes = attr
-}
-
-func (i *intOrderer) Feed(val Value, vrow virtualRow) error {
-	var row []string
-	var key int64
-	var err error
-
-	key, err = strconv.ParseInt(fmt.Sprintf("%v", val.v), 10, 64)
-	if err != nil {
-		return fmt.Errorf("error ordering because of value %v", val.v)
+		// TODO: refactor using generics once possible
+		switch vrow[ob.column].v.(type) {
+		case string:
+			ob.comparator = func(desc bool) comparator {
+				return func(i interface{}, j interface{}) int {
+					if _, ok := i.(string); !ok {
+						panic(fmt.Sprintf("unsupported type, expected string but got %T", i))
+					}
+
+					a := i.(string)
+					b := j.(string)
+					if desc {
+						switch {
+						case a > b:
+							return 1
+						case a < b:
+							return -1
+						case a == b:
+							return 0
+						}
+					} else {
+						switch {
+						case a < b:
+							return 1
+						case a > b:
+							return -1
+						case a == b:
+							return 0
+						}
+					}
+					return 0
+				}
+			}(ob.desc)
+		case int64:
+			ob.comparator = func(desc bool) comparator {
+				return func(i interface{}, j interface{}) int {
+					if _, ok := i.(int64); !ok {
+						panic(fmt.Sprintf("unsupported type, expected int64 but got %T %v", i, i))
+					}
+
+					a := i.(int64)
+					b := j.(int64)
+					if desc {
+						switch {
+						case a > b:
+							return 1
+						case a < b:
+							return -1
+						case a == b:
+							return 0
+						}
+					} else {
+						switch {
+						case a < b:
+							return 1
+						case a > b:
+							return -1
+						case a == b:
+							return 0
+						}
+					}
+					return 0
+				}
+			}(ob.desc)
+		case float64:
+			ob.comparator = func(desc bool) comparator {
+				return func(i interface{}, j interface{}) int {
+					if _, ok := i.(float64); !ok {
+						panic(fmt.Sprintf("unsupported type, expected float64 but got %T %v", i, i))
+					}
+
+					a := i.(float64)
+					b := j.(float64)
+					if desc {
+						switch {
+						case a > b:
+							return 1
+						case a < b:
+							return -1
+						case a == b:
+							return 0
+						}
+					} else {
+						switch {
+						case a < b:
+							return 1
+						case a > b:
+							return -1
+						case a == b:
+							return 0
+						}
+					}
+					return 0
+				}
+			}(ob.desc)
+		//default:
+		//	panic(fmt.Sprintf("wrong type %T for column %s", vrow[ob.column].v, ob.column))
+		}
 	}
 
-	for _, attr := range i.attributes {
+	for _, attr := range o.attributes {
 		val, ok := vrow[attr]
 		if !ok {
 			return fmt.Errorf("could not select attribute %s", attr)
 		}
-		row = append(row, fmt.Sprintf("%v", val.v))
+		row = append(row, val.v)
 	}
 
 	// now instead of writing row, we will find the ordering key and put in in our buffer
-	i.buffer[key] = append(i.buffer[key], row)
+	o.buffer[key] = append(o.buffer[key], row)
 	return nil
 }
 
-func (i *intOrderer) Sort() error {
-	// now we have to sort our key
-	i.keys = make([]int64, len(i.buffer))
-	var index int64
-	for k := range i.buffer {
-		i.keys[index] = k
-		index++
+func (o *genericOrderer) Sort() error {
+	o.keys = make([]string, len(o.buffer))
+	var idx int
+	for k := range o.buffer {
+		o.keys[idx] = k
+		idx++
 	}
 
-	sort.Sort(sortSlice(i.keys))
-	return nil
-}
+	sort.Slice(o.keys, func(a, b int) bool {
+		for _, ob := range o.orderBy {
+			keyA := o.keys[a]
+			keyB := o.keys[b]
 
-func (i *intOrderer) SortReverse() error {
-	// now we have to sort our key
-	i.keys = make([]int64, len(i.buffer))
-	var index int64
-	for k := range i.buffer {
-		i.keys[index] = k
-		index++
-	}
+			var idx int
+			for i, attr := range o.attributes {
+				if attr == ob.column {
+					idx = i
+				}
+			}
+
+			switch ob.comparator(o.buffer[keyA][0][idx], o.buffer[keyB][0][idx]) {
+			case 1:
+				return true
+			case 0:
+				continue
+			case -1:
+				return false
+			}
+		}
+
+		return false
+	})
 
-	sort.Sort(sort.Reverse(sortSlice(i.keys)))
 	return nil
 }
 
-func (i *intOrderer) Write(conn protocol.EngineConn) error {
+func (o *genericOrderer) Write(conn protocol.EngineConn) error {
 	// now write ordered rows
-	for _, key := range i.keys {
-		rows := i.buffer[key]
+	for _, key := range o.keys {
+		rows := o.buffer[key]
 		for index := range rows {
-			conn.WriteRow(rows[index])
+			var row []string
+			for _, elem := range rows[index] {
+				row = append(row, fmt.Sprint(elem))
+			}
+			err := conn.WriteRow(row)
+			if err != nil {
+				return err
+			}
 		}
 	}
+
 	return nil
 }
 
-type dateOrderer struct {
-	buffer map[time.Time][][]string
-}
+type comparator func(i interface{}, j interface{}) int
diff --git a/engine/parser/create.go b/engine/parser/create.go
index 27669ea..7a0eade 100644
--- a/engine/parser/create.go
+++ b/engine/parser/create.go
@@ -104,6 +104,7 @@ func (p *parser) parseTable(tokens []Token) (*Decl, error) {
 		}
 		tableDecl.Add(newAttribute)
 
+
 		newAttributeType, err := p.parseType()
 		if err != nil {
 			return nil, err
diff --git a/engine/parser/lexer.go b/engine/parser/lexer.go
index fcf906d..3a9bbf9 100644
--- a/engine/parser/lexer.go
+++ b/engine/parser/lexer.go
@@ -42,6 +42,7 @@ const (
 	TruncateToken
 	DropToken
 	GrantToken
+	DistinctToken
 
 	// Second order Token
 
@@ -141,6 +142,7 @@ func (l *lexer) lex(instruction []byte) ([]Token, error) {
 	matchers = append(matchers, l.MatchTruncateToken)
 	matchers = append(matchers, l.MatchDropToken)
 	matchers = append(matchers, l.MatchGrantToken)
+	matchers = append(matchers, l.MatchDistinctToken)
 	// Second order Matcher
 	matchers = append(matchers, l.MatchTableToken)
 	matchers = append(matchers, l.MatchFromToken)
@@ -186,8 +188,6 @@ func (l *lexer) lex(instruction []byte) ([]Token, error) {
 
 	var r bool
 	for l.pos < l.instructionLen {
-		// fmt.Printf("Tokens : %v\n\n", l.tokens)
-
 		r = false
 		for _, m := range matchers {
 			if r = m(); r == true {
@@ -329,6 +329,10 @@ func (l *lexer) MatchSelectToken() bool {
 	return l.Match([]byte("select"), SelectToken)
 }
 
+func (l *lexer) MatchDistinctToken() bool {
+	return l.Match([]byte("distinct"), DistinctToken)
+}
+
 func (l *lexer) MatchInsertToken() bool {
 	return l.Match([]byte("insert"), InsertToken)
 }
@@ -431,6 +435,12 @@ func (l *lexer) MatchNumberToken() bool {
 	for i < l.instructionLen && unicode.IsDigit(rune(l.instruction[i])) {
 		i++
 	}
+	if i < l.instructionLen && l.instruction[i] == '.' {
+		i++
+		for i < l.instructionLen && unicode.IsDigit(rune(l.instruction[i])) {
+			i++
+		}
+	}
 
 	if i != l.pos {
 		t := Token{
diff --git a/engine/parser/parser.go b/engine/parser/parser.go
index dd07d84..3834174 100644
--- a/engine/parser/parser.go
+++ b/engine/parser/parser.go
@@ -70,8 +70,6 @@ func (p *parser) parse(tokens []Token) ([]Instruction, error) {
 	p.tokenLen = len(tokens)
 	p.index = 0
 	for p.hasNext() {
-		// fmt.Printf("Token index : %d\n", p.index)
-
 		// Found a new instruction
 		if tokens[p.index].Token == SemicolonToken {
 			p.index++
@@ -186,7 +184,7 @@ func (p *parser) parseUpdate() (*Instruction, error) {
 			break
 		}
 
-		attributeDecl, err := p.parseCondition()
+		attributeDecl, err := p.parseAttribution()
 		if err != nil {
 			return nil, err
 		}
@@ -338,36 +336,29 @@ func (p *parser) parseOrderBy(selectDecl *Decl) error {
 		return err
 	}
 
-	// parse attribute now
-	attrDecl, err := p.parseAttribute()
-	if err != nil {
-		return err
-	}
-	orderDecl.Add(attrDecl)
-
-	// Parse multiple ordering
-	for p.cur().Token == CommaToken {
-		_, err := p.consumeToken(CommaToken)
-		if err != nil {
-			return nil
-		}
-
+	for {
 		// parse attribute now
 		attrDecl, err := p.parseAttribute()
 		if err != nil {
 			return err
 		}
 		orderDecl.Add(attrDecl)
-	}
 
-	// ASC ? DESC ? nothing ?
-	t := p.cur().Token
-	if t == AscToken || t == DescToken {
-		decl, err := p.consumeToken(AscToken, DescToken)
-		if err != nil {
-			return err
+		if p.is(AscToken, DescToken) {
+			decl, err := p.consumeToken(AscToken, DescToken)
+			if err != nil {
+				return err
+			}
+			attrDecl.Add(decl)
+		}
+
+		if !p.is(CommaToken) {
+			break
+		}
+
+		if _, err = p.consumeToken(CommaToken); err != nil {
+			return nil
 		}
-		orderDecl.Add(decl)
 	}
 
 	return nil
@@ -464,7 +455,7 @@ func (p *parser) parseAttribute() (*Decl, error) {
 		}
 	}
 
-	// shoud be a StringToken here
+	// should be a StringToken here
 	// If there is a point after, it's a table name,
 	// if not, it's the attribute
 	if !p.is(StringToken, StarToken) {
@@ -510,7 +501,7 @@ func (p *parser) parseQuotedToken() (*Decl, error) {
 	quoted := false
 	quoteToken := DoubleQuoteToken
 
-	if p.is(DoubleQuoteToken) || p.is(BacktickToken){
+	if p.is(DoubleQuoteToken) || p.is(BacktickToken) {
 		quoted = true
 		quoteToken = p.cur().Token
 		if err := p.next(); err != nil {
@@ -536,6 +527,42 @@ func (p *parser) parseQuotedToken() (*Decl, error) {
 	return decl, nil
 }
 
+func (p *parser) parseAttribution() (*Decl, error) {
+
+	// Attribute
+	attributeDecl, err := p.parseAttribute()
+	if err != nil {
+		return nil, err
+	}
+
+	// Equals operator
+	if p.cur().Token == EqualityToken {
+		decl, err := p.consumeToken(p.cur().Token)
+		if err != nil {
+			return nil, err
+		}
+		attributeDecl.Add(decl)
+	}
+
+	// Value
+	if p.cur().Token == NullToken {
+		log.Debug("parseAttribution: NullToken\n")
+		nullDecl, err := p.consumeToken(NullToken)
+		if err != nil {
+			return nil, err
+		}
+		attributeDecl.Add(nullDecl)
+	} else {
+		valueDecl, err := p.parseValue()
+		if err != nil {
+			return nil, err
+		}
+		attributeDecl.Add(valueDecl)
+	}
+
+	return attributeDecl, nil
+}
+
 func (p *parser) parseCondition() (*Decl, error) {
 
 	// We may have the WHERE 1 condition
@@ -574,6 +601,24 @@ func (p *parser) parseCondition() (*Decl, error) {
 		}
 		attributeDecl.Add(inDecl)
 		return attributeDecl, nil
+	case NotToken:
+		notDecl, err := p.consumeToken(p.cur().Token)
+		if err != nil {
+			return nil, err
+		}
+
+		if p.cur().Token != InToken {
+			return nil, fmt.Errorf("expected IN after NOT")
+		}
+
+		inDecl, err := p.parseIn()
+		if err != nil {
+			return nil, err
+		}
+		notDecl.Add(inDecl)
+
+		attributeDecl.Add(notDecl)
+		return attributeDecl, nil
 	case IsToken:
 		log.Debug("parseCondition: IsToken\n")
 		decl, err := p.consumeToken(IsToken)
@@ -836,7 +881,6 @@ func (p *parser) cur() Token {
 }
 
 func (p *parser) consumeToken(tokenTypes ...int) (*Decl, error) {
-
 	if !p.is(tokenTypes...) {
 		return nil, p.syntaxError()
 	}
diff --git a/engine/parser/select.go b/engine/parser/select.go
index fb485e9..1e59617 100644
--- a/engine/parser/select.go
+++ b/engine/parser/select.go
@@ -20,28 +20,65 @@ func (p *parser) parseSelect(tokens []Token) (*Instruction, error) {
 		return nil, fmt.Errorf("SELECT token must be followed by attributes to select")
 	}
 
+	var (
+		distinctDecl *Decl
+		distinctOpen bool
+	)
+	if p.is(DistinctToken) {
+		distinctDecl, err = p.consumeToken(DistinctToken)
+		if err != nil {
+			return nil, err
+		}
+
+		if p.is(OnToken) {
+			if err := p.next(); err != nil {
+				return nil, err
+			}
+			if !p.is(BracketOpeningToken) {
+				return nil, fmt.Errorf("Syntax error near %v, opening bracket expected\n", tokens[p.index])
+			}
+			if err := p.next(); err != nil {
+				return nil, err
+			}
+			distinctOpen = true
+		}
+
+		selectDecl.Add(distinctDecl)
+	}
+
 	for {
-		if p.is(CountToken) {
+		switch {
+		case p.is(CountToken):
 			attrDecl, err := p.parseBuiltinFunc()
 			if err != nil {
 				return nil, err
 			}
 			selectDecl.Add(attrDecl)
-		} else {
+		default:
 			attrDecl, err := p.parseAttribute()
 			if err != nil {
 				return nil, err
 			}
+			if distinctOpen {
+				distinctDecl.Add(attrDecl)
+			}
 			selectDecl.Add(attrDecl)
 		}
 
-		// If comma, loop again.
-		if p.is(CommaToken) {
+		switch {
+		case distinctOpen && p.is(BracketClosingToken):
+			if err := p.next(); err != nil {
+				return nil, err
+			}
+			distinctOpen = false
+			continue
+		case p.is(CommaToken):
 			if err := p.next(); err != nil {
 				return nil, err
 			}
 			continue
 		}
+
 		break
 	}
 
diff --git a/engine/select.go b/engine/select.go
index 63ac478..bc91bb8 100644
--- a/engine/select.go
+++ b/engine/select.go
@@ -123,6 +123,8 @@ func selectExecutor(e *Engine, selectDecl *parser.Decl, conn protocol.EngineConn
 				return fmt.Errorf("wrong offset value: %s", err)
 			}
 			conn = offsetedConn(conn, offset)
+		case parser.DistinctToken:
+			conn = distinctedConn(conn, len(selectDecl.Decl[i].Decl))
 		}
 	}
 
@@ -143,7 +145,7 @@ func selectExecutor(e *Engine, selectDecl *parser.Decl, conn protocol.EngineConn
 	}
 
 	if len(functors) == 0 {
-		// Instanciate a new select functor
+		// Instantiate a new select functor
 		functors, err = getSelectFunctors(selectDecl)
 		if err != nil {
 			return err
@@ -164,7 +166,7 @@ type selectFunctor interface {
 	Done() error
 }
 
-// getSelectFunctors instanciate new functors for COUNT, MAX, MIN, AVG, ... and default select functor that return rows to client
+// getSelectFunctors instantiate new functors for COUNT, MAX, MIN, AVG, ... and default select functor that return rows to client
 // If a functor is specified, no attribute can be selected ?
 func getSelectFunctors(attr *parser.Decl) ([]selectFunctor, error) {
 	var functors []selectFunctor
@@ -275,6 +277,22 @@ func inExecutor(inDecl *parser.Decl, p *Predicate) error {
 	return nil
 }
 
+func notInExecutor(inDecl *parser.Decl, p *Predicate) error {
+	inDecl.Stringy(0)
+
+	p.Operator = notInOperator
+
+	// Put everything in a []string
+	var values []string
+	for i := range inDecl.Decl {
+		log.Debug("notIinExecutor: Appending [%s]", inDecl.Decl[i].Lexeme)
+		values = append(values, inDecl.Decl[i].Lexeme)
+	}
+	p.RightValue.v = values
+
+	return nil
+}
+
 func isExecutor(isDecl *parser.Decl, p *Predicate) error {
 	isDecl.Stringy(0)
 
@@ -387,6 +405,16 @@ func whereExecutor2(e *Engine, decl []*parser.Decl, fromTableName string) (Predi
 		return p, nil
 	}
 
+	// Handle NOT IN keywords
+	if cond.Decl[0].Token == parser.NotToken && cond.Decl[0].Decl[0].Token == parser.InToken {
+		err := notInExecutor(cond.Decl[0].Decl[0], p)
+		if err != nil {
+			return nil, err
+		}
+		p.LeftValue.table = fromTableName
+		return p, nil
+	}
+
 	// Handle IS NULL and IS NOT NULL
 	if cond.Decl[0].Token == parser.IsToken {
 		err := isExecutor(cond.Decl[0], p)
@@ -450,6 +478,9 @@ func whereExecutor(whereDecl *parser.Decl, fromTableName string) ([]Predicate, e
 		case parser.InToken:
 			log.Debug("whereExecutor: it's IN\n")
 			break
+		case parser.NotToken:
+			log.Debug("whereExecutor: it's NOT\n")
+			break
 		case parser.IsToken:
 			log.Debug("whereExecutor: it's IS token\n")
 			log.Debug("whereExecutor: %+v\n", cond.Decl[0])
@@ -474,6 +505,17 @@ func whereExecutor(whereDecl *parser.Decl, fromTableName string) ([]Predicate, e
 			continue
 		}
 
+		// Handle NOT IN keywords
+		if cond.Decl[0].Token == parser.NotToken && cond.Decl[0].Decl[0].Token == parser.InToken {
+			err := notInExecutor(cond.Decl[0].Decl[0], &p)
+			if err != nil {
+				return nil, err
+			}
+			p.LeftValue.table = tableName
+			predicates = append(predicates, p)
+			continue
+		}
+
 		// Handle IS NULL and IS NOT NULL
 		if cond.Decl[0].Token == parser.IsToken {
 			err := isExecutor(cond.Decl[0], &p)
diff --git a/engine/table.go b/engine/table.go
index 45deedd..2d8a2d6 100644
--- a/engine/table.go
+++ b/engine/table.go
@@ -85,6 +85,7 @@ func createTableExecutor(e *Engine, tableDecl *parser.Decl, conn protocol.Engine
 		if err != nil {
 			return err
 		}
+
 		i++
 	}
 
diff --git a/engine/update.go b/engine/update.go
index 12f83a0..ffafa4e 100644
--- a/engine/update.go
+++ b/engine/update.go
@@ -84,7 +84,11 @@ func setExecutor(setDecl *parser.Decl) (map[string]interface{}, error) {
 	values := make(map[string]interface{})
 
 	for _, attr := range setDecl.Decl {
-		values[attr.Lexeme] = attr.Decl[1].Lexeme
+		if attr.Decl[1].Token == parser.NullToken {
+			values[attr.Lexeme] = nil
+		} else {
+			values[attr.Lexeme] = attr.Decl[1].Lexeme
+		}
 	}
 
 	return values, nil


Test patch:
diff --git a/driver/driver_test.go b/driver/driver_test.go
index 0b0eecb..5fad4f4 100644
--- a/driver/driver_test.go
+++ b/driver/driver_test.go
@@ -305,7 +305,7 @@ func TestCompareDateGT(t *testing.T) {
 
 	query := "SELECT dat FROM comp WHERE dat > '2018-03-03'"
 
-	rows, err := db.Query(query, )
+	rows, err := db.Query(query)
 	if err != nil {
 		t.Fatalf("sql.Query: %s", err)
 	}
@@ -362,7 +362,7 @@ func TestCompareDateLT(t *testing.T) {
 
 	query := "SELECT dat FROM comp WHERE dat < '2019-03-03'"
 
-	rows, err := db.Query(query, )
+	rows, err := db.Query(query)
 	if err != nil {
 		t.Fatalf("sql.Query: %s", err)
 	}
@@ -552,7 +552,6 @@ func TestEqualAndDistinct(t *testing.T) {
 	if nb != 2 {
 		t.Fatalf("Expected 2 rows, got %d", nb)
 	}
-
 }
 
 func TestGreaterThanOrEqualAndLessThanOrEqual(t *testing.T) {
@@ -952,3 +951,62 @@ func TestJSON(t *testing.T) {
 		t.Fatalf("Unexpected values (second unmarshal): %+v\n", s)
 	}
 }
+
+func TestDistinct(t *testing.T) {
+	log.UseTestLogger(t)
+
+	batch := []string{
+		`CREATE TABLE user (name TEXT, surname TEXT, age INT);`,
+		`INSERT INTO user (name, surname, age) VALUES ('Foo', 'Bar', 20);`,
+		`INSERT INTO user (name, surname, age) VALUES ('John', 'Doe', 20);`,
+		`INSERT INTO user (name, surname, age) VALUES ('Jane', 'Doe', 40);`,
+		`INSERT INTO user (name, surname, age) VALUES ('Joe', 'Doe', 10);`,
+		`INSERT INTO user (name, surname, age) VALUES ('Homer', 'Simpson', 40);`,
+		`INSERT INTO user (name, surname, age) VALUES ('Marge', 'Simpson', 40);`,
+		`INSERT INTO user (name, surname, age) VALUES ('Bruce', 'Wayne', 3333);`,
+	}
+
+	db, err := sql.Open("ramsql", "TestDistinct")
+	if err != nil {
+		t.Fatalf("sql.Open : Error : %s\n", err)
+	}
+	defer db.Close()
+
+	for _, b := range batch {
+		_, err = db.Exec(b)
+		if err != nil {
+			t.Fatalf("sql.Exec: Error: %s\n", err)
+		}
+	}
+
+	testDistinct := func(t *testing.T, query string, exp int, dest ...interface{}) {
+		rows, err := db.Query(query)
+		if err != nil {
+			t.Fatalf("sql.Query: %s", err)
+		}
+
+		var got int
+		for rows.Next() {
+			got++
+			if err := rows.Scan(dest...); err != nil {
+				t.Fatal(err)
+			}
+		}
+		if err := rows.Err(); err != nil {
+			t.Fatal(err)
+		}
+
+		if got != exp {
+			t.Fatalf("Expected %d rows, got %d", exp, got)
+		}
+	}
+
+	t.Run("distinct", func(t *testing.T) {
+		var surname string
+		testDistinct(t, `SELECT DISTINCT surname FROM user`, 4, &surname)
+	})
+	t.Run("distinct-on", func(t *testing.T) {
+		var name string
+		testDistinct(t, `SELECT DISTINCT ON (surname, age) name FROM user`, 6, &name)
+	})
+}
diff --git a/driver/in_test.go b/driver/in_test.go
index 5c746a0..1a6d08d 100644
--- a/driver/in_test.go
+++ b/driver/in_test.go
@@ -60,3 +60,57 @@ func TestIn(t *testing.T) {
 	}
 
 }
+
+func TestNotIn(t *testing.T) {
+	log.UseTestLogger(t)
+
+	batch := []string{
+		`CREATE TABLE user (name TEXT, surname TEXT, age INT);`,
+		`INSERT INTO user (name, surname, age) VALUES (Foo, Bar, 20);`,
+		`INSERT INTO user (name, surname, age) VALUES (John, Doe, 32);`,
+		`INSERT INTO user (name, surname, age) VALUES (Jane, Doe, 33);`,
+		`INSERT INTO user (name, surname, age) VALUES (Joe, Doe, 10);`,
+		`INSERT INTO user (name, surname, age) VALUES (Homer, Simpson, 40);`,
+		`INSERT INTO user (name, surname, age) VALUES (Marge, Simpson, 40);`,
+		`INSERT INTO user (name, surname, age) VALUES (Bruce, Wayne, 3333);`,
+	}
+
+	db, err := sql.Open("ramsql", "TestNotIn")
+	if err != nil {
+		t.Fatalf("sql.Open : Error : %s\n", err)
+	}
+	defer db.Close()
+
+	for _, b := range batch {
+		_, err = db.Exec(b)
+		if err != nil {
+			t.Fatalf("sql.Exec: Error: %s\n", err)
+		}
+	}
+
+	query := `SELECT * FROM user WHERE user.surname NOT IN ('Doe', 'Simpson')`
+
+	rows, err := db.Query(query)
+	if err != nil {
+		t.Fatalf("sql.Query: %s", err)
+	}
+
+	var nb int
+	for rows.Next() {
+		var name, surname string
+		var age int
+		if err := rows.Scan(&name, &surname, &age); err != nil {
+			t.Fatalf("Cannot scan row: %s", err)
+		}
+		if surname == "Doe" || surname == "Simpson" {
+			t.Fatalf("Unwanted row: %s %s %d", name, surname, age)
+		}
+
+		nb++
+	}
+
+	if nb != 2 {
+		t.Fatalf("Expected 2 rows, got %d", nb)
+	}
+
+}
diff --git a/engine/orderby_test.go b/engine/orderby_test.go
index b4b9140..5ae635d 100644
--- a/engine/orderby_test.go
+++ b/engine/orderby_test.go
@@ -2,9 +2,9 @@ package engine_test
 
 import (
 	"database/sql"
-	"testing"
-
+	"fmt"
 	"github.com/proullon/ramsql/engine/log"
+	"testing"
 )
 
 func TestOrderByInt(t *testing.T) {
@@ -231,3 +231,72 @@ func TestOrderByIntEmpty(t *testing.T) {
 	defer rows.Close()
 
 }
+
+func TestOrderByMultipleStrings(t *testing.T) {
+	log.UseTestLogger(t)
+
+	db, err := sql.Open("ramsql", "TestOrderByMultipleStrings")
+	if err != nil {
+		t.Fatalf("sql.Open : Error : %s\n", err)
+	}
+
+	defer func() {
+		if err := db.Close(); err != nil {
+			t.Error(err)
+		}
+	}()
+
+	batch := []string{
+		`CREATE TABLE user (name TEXT, surname TEXT, age INT64, savings DECIMAL);`,
+		`INSERT INTO user (name, surname, age, savings) VALUES (Joe, Angel, 11, 125.215);`,
+		`INSERT INTO user (name, surname, age, savings) VALUES (Joe, Angel, 11, 1.1);`,
+		`INSERT INTO user (name, surname, age, savings) VALUES (Joe, Zebra, 32, 0.921);`,
+		`INSERT INTO user (name, surname, age, savings) VALUES (Anna, Angel, 33, 0);`,
+		`INSERT INTO user (name, surname, age, savings) VALUES (Anna, Zebra, 9, 25);`,
+	}
+
+	for _, b := range batch {
+		_, err = db.Exec(b)
+		if err != nil {
+			t.Fatalf("sql.Exec: Error: %s", err)
+		}
+	}
+
+	query := `SELECT name, surname, age, savings FROM user ORDER BY name ASC, age DESC, savings ASC`
+	rows, err := db.Query(query)
+	if err != nil {
+		t.Fatalf("Cannot select and order by age: %s", err)
+	}
+
+	exp := [][]string{
+		{"Anna", "33", "0"},
+		{"Anna", "9", "25"},
+		{"Joe", "32", "0.921"},
+		{"Joe", "11", "1.1"},
+		{"Joe", "11", "125.215"},
+	}
+	var (
+		got           [][]string
+		name, surname string
+		age           int64
+		savings       float64
+	)
+	for rows.Next() {
+		err = rows.Scan(&name, &surname, &age, &savings)
+		if err != nil {
+			t.Fatalf("Cannot scan row: %s\n", err)
+		}
+		got = append(got, []string{name, fmt.Sprint(age), fmt.Sprint(savings)})
+	}
+
+	if len(exp) != len(got) {
+		t.Fatalf("length mismatch, expected %d but got %d", len(exp), len(got))
+	}
+	for i := 0; i < len(exp); i++ {
+		for j := 0; j < len(exp[i]); j++ {
+			if exp[i][j] != got[i][j] {
+				t.Fatalf("data mismatch at %d/%d, expeced %v but got %v", i, j, exp[i][j], got[i][j])
+			}
+		}
+	}
+}
diff --git a/engine/update_test.go b/engine/update_test.go
index cfa3056..73804dc 100644
--- a/engine/update_test.go
+++ b/engine/update_test.go
@@ -161,3 +161,57 @@ func TestUpdateNotNull(t *testing.T) {
 	}
 
 }
+
+func TestUpdateToNull(t *testing.T) {
+	log.UseTestLogger(t)
+
+	db, err := sql.Open("ramsql", "TestUpdateToNull")
+	if err != nil {
+		t.Fatalf("sql.Open : Error : %s\n", err)
+	}
+	defer db.Close()
+
+	_, err = db.Exec("CREATE TABLE account (id INT AUTOINCREMENT, email TEXT, creation_date TIMESTAMP WITH TIME ZONE)")
+	if err != nil {
+		t.Fatalf("sql.Exec: Error: %s\n", err)
+	}
+
+	_, err = db.Exec("INSERT INTO account ('email') VALUES ('foo@bar.com')")
+	if err != nil {
+		t.Fatalf("Cannot insert into table account: %s", err)
+	}
+
+	row1 := db.QueryRow("SELECT email FROM account WHERE id = 1")
+	if row1 == nil {
+		t.Fatalf("sql.Query failed")
+	}
+
+	var email1 *string
+	err = row1.Scan(&email1)
+	if err != nil {
+		t.Fatalf("row.Scan: %s", err)
+	}
+	if email1 == nil {
+		t.Fatalf("expected 'foo@bar.com' email, but got NULL")
+	}
+
+	_, err = db.Exec("UPDATE account SET email = NULL WHERE id = 1")
+	if err != nil {
+		t.Fatalf("Cannot update table account: %s", err)
+	}
+
+	row2 := db.QueryRow("SELECT email FROM account WHERE id = 1")
+	if row2 == nil {
+		t.Fatalf("sql.Query failed")
+	}
+
+	var email2 *string
+	err = row2.Scan(&email2)
+	if err != nil {
+		t.Fatalf("row.Scan: %s", err)
+	}
+	if email2 != nil {
+		t.Fatalf("expected NULL email, but got '%v'", *email2)
+	}
+
+}