Repository: matrixorigin/matrixone
Pull request #2999 (2022-06-14 15:29:27)

logservice: added IsTempError()
**What type of PR is this?**

- [x] API-change
- [ ] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [ ] Code Refactoring

**Which issue(s) this PR fixes:**

issue #

**What this PR does / why we need it:**

As a part of the client library, we provide a function IsTempError(error) bool to allow users to check whether it worth to retry the failed operation.  

**Special notes for your reviewer:**

Not Available

**Additional documentation (e.g. design docs, usage docs, etc.):**

Not Available



Patch:
diff --git a/cmd/generate-config/system_vars_def.toml b/cmd/generate-config/system_vars_def.toml
index 7d110ad22..02eabc04a 100644
--- a/cmd/generate-config/system_vars_def.toml
+++ b/cmd/generate-config/system_vars_def.toml
@@ -394,8 +394,8 @@ scope = ["global"]
 access = ["file"]
 type = "string"
 domain-type = "set"
-values = ["aoe","tae"]
-comment = "default engine is 'aoe'."
+values = ["tae", "aoe"]
+comment = "default engine is 'tae'."
 update-mode = "dynamic"
 
 [[parameter]]
@@ -414,8 +414,8 @@ scope = ["global"]
 access = ["file"]
 type = "bool"
 domain-type = "set"
-values = []
-comment = "default is false. true : use plan2/compile2 false : use stuff in v0.4.0"
+values = ["true", "false"]
+comment = "default is true. true : use plan2/compile2 false : use stuff in v0.4.0"
 update-mode = "dynamic"
 
 [[parameter]]
diff --git a/docs/cn/MatrixOne/MatrixCampEvent.md b/docs/cn/MatrixOne/MatrixCampEvent.md
index f00755dfd..1502d79fa 100644
--- a/docs/cn/MatrixOne/MatrixCampEvent.md
+++ b/docs/cn/MatrixOne/MatrixCampEvent.md
@@ -18,7 +18,7 @@ MatrixOne社区一共准备了4个类别的56个任务，有不同的难度级
 在开始之前，先给MatrixOne项目Star, Fork, Watch一下吧。
 
 1. 选择很重要！加小助手微信“MatrixOrigin001”填写活动注册表<https://www.wjx.top/vm/Ys1rz1I.aspx> 选择你想要完成的函数任务，并加入MatrixOne社区群。
-2. 登陆Github，在你选择的函数issue留下你的comment，比如“I'd like to work on this issue”, 小助手会将相关issue分配给你。
+2. 登录Github，在你选择的函数issue留下你的comment，比如“I'd like to work on this issue”, 小助手会将相关issue分配给你。
 3. 开始只属于你的函数体验任务。如有任何问题，MatrixOne社区群里的技术大牛全程在线支持。
 
 > 请注意：1个开发者可以选择多个挑战任务，但是1个任务只能由1个开发者完成
diff --git a/go.mod b/go.mod
index 4568fc5e7..da4041b36 100644
--- a/go.mod
+++ b/go.mod
@@ -21,7 +21,7 @@ require (
 	github.com/golang/mock v1.6.0
 	github.com/google/btree v1.0.1
 	github.com/google/gofuzz v1.2.0
-	github.com/lni/dragonboat/v4 v4.0.0-20220604123758-e40bf3f57b59
+	github.com/lni/dragonboat/v4 v4.0.0-20220612172217-6ae1d0b2e535
 	github.com/lni/goutils v1.3.1-0.20220604063047-388d67b4dbc4
 	github.com/matrixorigin/matrixcube v0.3.1-0.20220606032431-c944d801f1e5
 	github.com/matrixorigin/simdcsv v0.0.0-20210926114300-591bf748a770
diff --git a/go.sum b/go.sum
index f4a75cda9..b3f27f718 100644
--- a/go.sum
+++ b/go.sum
@@ -472,8 +472,8 @@ github.com/kr/text v0.2.0/go.mod h1:eLer722TekiGuMkidMxC/pM04lWEeraHUUmBw8l2grE=
 github.com/labstack/echo/v4 v4.1.11/go.mod h1:i541M3Fj6f76NZtHSj7TXnyM8n2gaodfvfxNnFqi74g=
 github.com/labstack/echo/v4 v4.5.0/go.mod h1:czIriw4a0C1dFun+ObrXp7ok03xON0N1awStJ6ArI7Y=
 github.com/labstack/gommon v0.3.0/go.mod h1:MULnywXg0yavhxWKc+lOruYdAhDwPK9wf0OL7NoOu+k=
-github.com/lni/dragonboat/v4 v4.0.0-20220604123758-e40bf3f57b59 h1:QQnr5/JOG9upSf0WXP6LVg18vBlbycJbY5egnpyWcKQ=
-github.com/lni/dragonboat/v4 v4.0.0-20220604123758-e40bf3f57b59/go.mod h1:5dd7hX8qUJ+72UVKdZZIBw5UbhZTaGY5PIt/TTdIeSY=
+github.com/lni/dragonboat/v4 v4.0.0-20220612172217-6ae1d0b2e535 h1:tjaMFsEucNN3tOkzT6FHxEWJQZW0skBmK7CXR25bVHo=
+github.com/lni/dragonboat/v4 v4.0.0-20220612172217-6ae1d0b2e535/go.mod h1:5dd7hX8qUJ+72UVKdZZIBw5UbhZTaGY5PIt/TTdIeSY=
 github.com/lni/goutils v1.3.0/go.mod h1:PUPtBAnZlRPUKWUXCsYkIRWubJbtNHpTAee0sczhlf4=
 github.com/lni/goutils v1.3.1-0.20220604063047-388d67b4dbc4 h1:6gzI38ZJmbzZ7oZebXz6jII0uVK+MZ3+ds+7mIt1ioI=
 github.com/lni/goutils v1.3.1-0.20220604063047-388d67b4dbc4/go.mod h1:LIHvF0fflR+zyXUQFQOiHPpKANf3UIr7DFIv5CBPOoU=
diff --git a/pkg/container/types/date.go b/pkg/container/types/date.go
index d5b8a75f7..23ee4ebed 100644
--- a/pkg/container/types/date.go
+++ b/pkg/container/types/date.go
@@ -16,6 +16,7 @@ package types
 
 import (
 	"fmt"
+	"strconv"
 	"time"
 
 	"github.com/matrixorigin/matrixone/pkg/errno"
@@ -201,6 +202,36 @@ func (d Date) Year() uint16 {
 	return year
 }
 
+func (d Date) YearMonth() uint32 {
+	year, month, _, _ := d.Calendar(true)
+	yearStr := fmt.Sprintf("%04d", year)
+	monthStr := fmt.Sprintf("%02d", month)
+	// fmt.Println(yearStr, monthStr, "--------")
+	result, _ := strconv.ParseUint(yearStr+monthStr, 10, 32)
+	// fmt.Println(result)
+	return uint32(result)
+}
+
+var monthToQuarter = map[uint8]uint32{
+	1:  1,
+	2:  1,
+	3:  1,
+	4:  2,
+	5:  2,
+	6:  2,
+	7:  3,
+	8:  3,
+	9:  3,
+	10: 4,
+	11: 4,
+	12: 4,
+}
+
+func (d Date) Quarter() uint32 {
+	_, month, _, _ := d.Calendar(true)
+	return monthToQuarter[month]
+}
+
 func (d Date) Calendar(full bool) (year int32, month, day uint8, yday uint16) {
 	// Account for 400 year cycles.
 	n := d / daysPer400Years
diff --git a/pkg/container/types/interval.go b/pkg/container/types/interval.go
index ebc61eb2e..177ee17df 100644
--- a/pkg/container/types/interval.go
+++ b/pkg/container/types/interval.go
@@ -127,18 +127,18 @@ func IntervalTypeOf(s string) (IntervalType, error) {
 //  2. 1-1 is parsed out as 1, 1 '-' is delim, so is '+', '.' etc.
 //	3. we will not support int32 overflow.
 //
-func parseInts(s string, isxxxMicrosecond bool, typeMaxLength int) ([]int32, error) {
-	ret := make([]int32, 0)
+func parseInts(s string, isxxxMicrosecond bool, typeMaxLength int) ([]int64, error) {
+	ret := make([]int64, 0)
 	numLength := 0
 	cur := -1
 	for _, c := range s {
 		if c >= rune('0') && c <= rune('9') {
 			if cur < 0 {
 				cur = len(ret)
-				ret = append(ret, c-rune('0'))
+				ret = append(ret, int64(c-rune('0')))
 				numLength++
 			} else {
-				ret[cur] = 10*ret[cur] + c - rune('0')
+				ret[cur] = 10*ret[cur] + int64(c-rune('0'))
 				numLength++
 				if ret[cur] < 0 {
 					return nil, errors.New(errno.DataException, "Invalid string interval value")
@@ -153,13 +153,13 @@ func parseInts(s string, isxxxMicrosecond bool, typeMaxLength int) ([]int32, err
 	}
 	if isxxxMicrosecond {
 		if len(ret) == typeMaxLength {
-			ret[len(ret)-1] *= int32(math.Pow10(6 - numLength))
+			ret[len(ret)-1] *= int64(math.Pow10(6 - numLength))
 		}
 	}
 	return ret, nil
 }
 
-func conv(a []int32, mul []int64, rt IntervalType) (int64, IntervalType, error) {
+func conv(a []int64, mul []int64, rt IntervalType) (int64, IntervalType, error) {
 	if len(a) > len(mul) {
 		return 0, IntervalTypeInvalid, errors.New(errno.DataException, "Invalid interval format")
 	}
diff --git a/pkg/logservice/client.go b/pkg/logservice/client.go
index dbcd10412..18a6da867 100644
--- a/pkg/logservice/client.go
+++ b/pkg/logservice/client.go
@@ -27,6 +27,14 @@ var (
 	ErrIncompatibleClient = moerr.NewError(moerr.INVALID_INPUT, "incompatible client")
 )
 
+// IsTempError returns a boolean value indicating whether the specified error is a temp
+// error that worth to be retried, e.g. timeouts, temp network issues, operation can be
+// completed as Raft leader is being elected. Non-temp error means the error is caused
+// by program logics rather than some external factors.
+func IsTempError(err error) bool {
+	return isTempError(err)
+}
+
 type LogServiceClientConfig struct {
 	ReadOnly  bool
 	ShardID   uint64
diff --git a/pkg/logservice/errors.go b/pkg/logservice/errors.go
index 2bdddaeb8..1d7b3f6d3 100644
--- a/pkg/logservice/errors.go
+++ b/pkg/logservice/errors.go
@@ -15,6 +15,8 @@
 package logservice
 
 import (
+	"net"
+
 	"github.com/cockroachdb/errors"
 	"github.com/lni/dragonboat/v4"
 
@@ -45,10 +47,10 @@ func getErrorToCodeMapping() []errorToCode {
 		{dragonboat.ErrShardNotReady, pb.ErrorCode_ShardNotReady, true},
 		{dragonboat.ErrSystemBusy, pb.ErrorCode_ShardNotReady, false},
 		{dragonboat.ErrClosed, pb.ErrorCode_SystemClosed, true},
+		{dragonboat.ErrInvalidRange, pb.ErrorCode_OutOfRange, true},
 
 		{ErrInvalidTruncateIndex, pb.ErrorCode_IndexAlreadyTruncated, true},
 		{ErrNotLeaseHolder, pb.ErrorCode_NotLeaseHolder, true},
-		{ErrOutOfRange, pb.ErrorCode_OutOfRange, true},
 	}
 }
 
@@ -83,3 +85,13 @@ func toError(resp pb.Response) error {
 	// will never reach here
 	return nil
 }
+
+func isTempError(err error) bool {
+	if dragonboat.IsTempError(err) {
+		return true
+	}
+	if _, ok := err.(net.Error); ok {
+		return true
+	}
+	return false
+}
diff --git a/pkg/logservice/service.go b/pkg/logservice/service.go
index 900df86c6..e09367b8a 100644
--- a/pkg/logservice/service.go
+++ b/pkg/logservice/service.go
@@ -141,7 +141,7 @@ func (s *Service) serve(conn net.Conn) {
 				}
 				return
 			}
-			if errors.Is(err, ErrBadMessage) {
+			if errors.Is(err, errBadMessage) {
 				return
 			}
 			operr, ok := err.(net.Error)
diff --git a/pkg/logservice/store.go b/pkg/logservice/store.go
index 4699e5a84..50b2670b9 100644
--- a/pkg/logservice/store.go
+++ b/pkg/logservice/store.go
@@ -35,8 +35,9 @@ import (
 var (
 	ErrInvalidTruncateIndex = moerr.NewError(moerr.INVALID_INPUT, "invalid input")
 	ErrNotLeaseHolder       = moerr.NewError(moerr.INVALID_STATE, "not lease holder")
-	ErrOutOfRange           = moerr.NewError(moerr.INVALID_INPUT, "query out of range")
 	ErrInvalidShardID       = moerr.NewError(moerr.INVALID_INPUT, "invalid shard ID")
+
+	ErrOutOfRange = dragonboat.ErrInvalidRange
 )
 
 type logStoreMeta struct {
diff --git a/pkg/logservice/transport.go b/pkg/logservice/transport.go
index 44ed870af..bc719c467 100644
--- a/pkg/logservice/transport.go
+++ b/pkg/logservice/transport.go
@@ -26,8 +26,7 @@ import (
 )
 
 var (
-	ErrInvalidBuffer   = moerr.NewError(moerr.INVALID_STATE, "invalid buffer size")
-	ErrBadMessage      = moerr.NewError(moerr.INVALID_INPUT, "invalid message")
+	errBadMessage      = moerr.NewError(moerr.INVALID_INPUT, "invalid message")
 	errPoisonReceived  = moerr.NewError(moerr.INVALID_STATE, "poison received")
 	ErrDeadlineNotSet  = moerr.NewError(moerr.INVALID_INPUT, "deadline not set")
 	ErrInvalidDeadline = moerr.NewError(moerr.INVALID_INPUT, "invalid deadline")
@@ -273,7 +272,7 @@ func readMagicNumber(conn net.Conn, buf []byte) error {
 		return errPoisonReceived
 	}
 	if !bytes.Equal(buf, magicNumber[:]) {
-		return ErrBadMessage
+		return errBadMessage
 	}
 	return nil
 }
diff --git a/pkg/sql/plan2/base_binder.go b/pkg/sql/plan2/base_binder.go
index 199bbd825..3d9180fbf 100644
--- a/pkg/sql/plan2/base_binder.go
+++ b/pkg/sql/plan2/base_binder.go
@@ -116,7 +116,7 @@ func (b *baseBinder) baseBindExpr(astExpr tree.Expr, depth int32) (expr *Expr, e
 	case *tree.MaxValue:
 		err = errors.New(errno.SyntaxErrororAccessRuleViolation, fmt.Sprintf("expr max'%v' is not support now", exprImpl))
 	case *tree.VarExpr:
-		err = errors.New(errno.SyntaxErrororAccessRuleViolation, fmt.Sprintf("expr var'%v' is not support now", exprImpl))
+		expr, err = b.baseBindVar(exprImpl, depth)
 	case *tree.StrVal:
 		err = errors.New(errno.SyntaxErrororAccessRuleViolation, fmt.Sprintf("expr str'%v' is not support now", exprImpl))
 	case *tree.ExprList:
@@ -132,6 +132,123 @@ func (b *baseBinder) baseBindExpr(astExpr tree.Expr, depth int32) (expr *Expr, e
 	return
 }
 
+func (b *baseBinder) baseBindVar(astExpr *tree.VarExpr, depth int32) (expr *plan.Expr, err error) {
+	var getVal interface{}
+	getVal, err = b.builder.compCtx.ResolveVariable(astExpr.Name, astExpr.System, astExpr.Global)
+	if err != nil {
+		return nil, err
+	}
+	getIntExpr := func(data int64) *plan.Expr {
+		return &Expr{
+			Expr: &plan.Expr_C{
+				C: &Const{
+					Isnull: false,
+					Value: &plan.Const_Ival{
+						Ival: data,
+					},
+				},
+			},
+			Typ: &plan.Type{
+				Id:       plan.Type_INT64,
+				Nullable: false,
+				Size:     8,
+			},
+		}
+	}
+	getFloatExpr := func(data float64) *plan.Expr {
+		return &Expr{
+			Expr: &plan.Expr_C{
+				C: &Const{
+					Isnull: false,
+					Value: &plan.Const_Dval{
+						Dval: data,
+					},
+				},
+			},
+			Typ: &plan.Type{
+				Id:       plan.Type_FLOAT64,
+				Nullable: false,
+				Size:     8,
+			},
+		}
+	}
+
+	switch val := getVal.(type) {
+	case string:
+		expr = &Expr{
+			Expr: &plan.Expr_C{
+				C: &Const{
+					Isnull: false,
+					Value: &plan.Const_Sval{
+						Sval: val,
+					},
+				},
+			},
+			Typ: &plan.Type{
+				Id:       plan.Type_VARCHAR,
+				Nullable: false,
+				Size:     4,
+				Width:    math.MaxInt32,
+			},
+		}
+	case int:
+		expr = getIntExpr(int64(val))
+	case uint8:
+		expr = getIntExpr(int64(val))
+	case uint16:
+		expr = getIntExpr(int64(val))
+	case uint32:
+		expr = getIntExpr(int64(val))
+	case int8:
+		expr = getIntExpr(int64(val))
+	case int16:
+		expr = getIntExpr(int64(val))
+	case int32:
+		expr = getIntExpr(int64(val))
+	case int64:
+		expr = getIntExpr(val)
+	case uint64:
+		err = errors.New(errno.SyntaxErrororAccessRuleViolation, "decimal var not support now")
+	case float32:
+		expr = getFloatExpr(float64(val))
+	case float64:
+		expr = getFloatExpr(val)
+	case bool:
+		return &Expr{
+			Expr: &plan.Expr_C{
+				C: &Const{
+					Isnull: false,
+					Value: &plan.Const_Bval{
+						Bval: val,
+					},
+				},
+			},
+			Typ: &plan.Type{
+				Id:       plan.Type_BOOL,
+				Nullable: false,
+				Size:     1,
+			},
+		}, nil
+	case nil:
+		expr = &Expr{
+			Expr: &plan.Expr_C{
+				C: &Const{
+					Isnull: true,
+				},
+			},
+			Typ: &plan.Type{
+				Id:       plan.Type_ANY,
+				Nullable: true,
+			},
+		}
+	case types.Decimal64, types.Decimal128:
+		err = errors.New(errno.SyntaxErrororAccessRuleViolation, "decimal var not support now")
+	default:
+		err = errors.New(errno.SyntaxErrororAccessRuleViolation, fmt.Sprintf("type of var %q is not support now", astExpr.Name))
+	}
+	return
+}
+
 func (b *baseBinder) baseBindColRef(astExpr *tree.UnresolvedName, depth int32) (expr *plan.Expr, err error) {
 	col := astExpr.Parts[0]
 	table := astExpr.Parts[1]
@@ -483,24 +600,19 @@ func (b *baseBinder) bindFuncExprImplByPlanExpr(name string, args []*Expr) (*pla
 		if args[0].Typ.Id == plan.Type_DATE && args[1].Typ.Id == plan.Type_INTERVAL {
 			name = "date_add"
 			args, err = resetDateFunctionArgs(args[0], args[1])
-		}
-		if args[0].Typ.Id == plan.Type_INTERVAL && args[1].Typ.Id == plan.Type_DATE {
+		} else if args[0].Typ.Id == plan.Type_INTERVAL && args[1].Typ.Id == plan.Type_DATE {
 			name = "date_add"
 			args, err = resetDateFunctionArgs(args[1], args[0])
-		}
-		if args[0].Typ.Id == plan.Type_DATETIME && args[1].Typ.Id == plan.Type_INTERVAL {
+		} else if args[0].Typ.Id == plan.Type_DATETIME && args[1].Typ.Id == plan.Type_INTERVAL {
 			name = "date_add"
 			args, err = resetDateFunctionArgs(args[0], args[1])
-		}
-		if args[0].Typ.Id == plan.Type_INTERVAL && args[1].Typ.Id == plan.Type_DATETIME {
+		} else if args[0].Typ.Id == plan.Type_INTERVAL && args[1].Typ.Id == plan.Type_DATETIME {
 			name = "date_add"
 			args, err = resetDateFunctionArgs(args[1], args[0])
-		}
-		if args[0].Typ.Id == plan.Type_VARCHAR && args[1].Typ.Id == plan.Type_INTERVAL {
+		} else if args[0].Typ.Id == plan.Type_VARCHAR && args[1].Typ.Id == plan.Type_INTERVAL {
 			name = "date_add"
 			args, err = resetDateFunctionArgs(args[0], args[1])
-		}
-		if args[0].Typ.Id == plan.Type_INTERVAL && args[1].Typ.Id == plan.Type_VARCHAR {
+		} else if args[0].Typ.Id == plan.Type_INTERVAL && args[1].Typ.Id == plan.Type_VARCHAR {
 			name = "date_add"
 			args, err = resetDateFunctionArgs(args[1], args[0])
 		}
@@ -512,12 +624,10 @@ func (b *baseBinder) bindFuncExprImplByPlanExpr(name string, args []*Expr) (*pla
 		if args[0].Typ.Id == plan.Type_DATE && args[1].Typ.Id == plan.Type_INTERVAL {
 			name = "date_sub"
 			args, err = resetDateFunctionArgs(args[0], args[1])
-		}
-		if args[0].Typ.Id == plan.Type_DATETIME && args[1].Typ.Id == plan.Type_INTERVAL {
+		} else if args[0].Typ.Id == plan.Type_DATETIME && args[1].Typ.Id == plan.Type_INTERVAL {
 			name = "date_sub"
 			args, err = resetDateFunctionArgs(args[0], args[1])
-		}
-		if args[0].Typ.Id == plan.Type_VARCHAR && args[1].Typ.Id == plan.Type_INTERVAL {
+		} else if args[0].Typ.Id == plan.Type_VARCHAR && args[1].Typ.Id == plan.Type_INTERVAL {
 			name = "date_sub"
 			args, err = resetDateFunctionArgs(args[0], args[1])
 		}
diff --git a/pkg/sql/plan2/function/builtin/binary/extract.go b/pkg/sql/plan2/function/builtin/binary/extract.go
new file mode 100644
index 000000000..c5850e745
--- /dev/null
+++ b/pkg/sql/plan2/function/builtin/binary/extract.go
@@ -0,0 +1,110 @@
+// Copyright 2022 Matrix Origin
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package binary
+
+import (
+	"errors"
+	"github.com/matrixorigin/matrixone/pkg/container/nulls"
+	"github.com/matrixorigin/matrixone/pkg/container/types"
+	"github.com/matrixorigin/matrixone/pkg/container/vector"
+	"github.com/matrixorigin/matrixone/pkg/encoding"
+	"github.com/matrixorigin/matrixone/pkg/vectorize/extract"
+	"github.com/matrixorigin/matrixone/pkg/vm/process"
+)
+
+/*
+// when implicit cast from varchar to date is ready, get rid of this
+func ExtractFromString(vectors []*vector.Vector, proc *process.Process) (*vector.Vector, error) {
+	left, right := vectors[0], vectors[1]
+	resultType := types.Type{Oid: types.T_uint32, Size: 4}
+	resultElementSize := int(resultType.Size)
+	switch {
+	case left.IsScalar() && right.IsScalar():
+		if left.ConstVectorIsNull() || right.ConstVectorIsNull() {
+			return proc.AllocScalarNullVector(resultType), nil
+		}
+		leftValues, rightValues := left.Col.(*types.Bytes), right.Col.(*types.Bytes)
+		resultVector := vector.NewConst(resultType)
+		resultValues := make([]uint32, 1)
+		unit := string(leftValues.Data)
+		inputDate, err := types.ParseDate(string(rightValues.Get(0)))
+		if err != nil {
+			return nil, errors.New("invalid input")
+		}
+		resultValues, err = extract.ExtractFromDate(unit, []types.Date{inputDate}, resultValues)
+		if err != nil {
+			return nil, errors.New("invalid input")
+		}
+		vector.SetCol(resultVector, resultValues)
+		return resultVector, nil
+	case left.IsScalar() && !right.IsScalar():
+		if left.ConstVectorIsNull() {
+			return proc.AllocScalarNullVector(resultType), nil
+		}
+		leftValues, rightValues := left.Col.(*types.Bytes), right.Col.(*types.Bytes)
+		unit := string(leftValues.Data)
+		resultValues, err := proc.AllocVector(resultType, int64(resultElementSize) * int64(len(rightValues.Lengths)))
+		if
+
+		result, resultNsp, err := extract.ExtractFromInputBytes(unit, rightValues, right.Nsp, )
+
+	default:
+		return nil, errors.New("invalid input")
+	}
+}
+*/
+
+func ExtractFromDate(vectors []*vector.Vector, proc *process.Process) (*vector.Vector, error) {
+	left, right := vectors[0], vectors[1]
+	resultType := types.Type{Oid: types.T_uint32, Size: 4}
+	resultElementSize := int(resultType.Size)
+	switch {
+	case left.IsScalar() && right.IsScalar():
+		if left.ConstVectorIsNull() || right.ConstVectorIsNull() {
+			return proc.AllocScalarNullVector(resultType), nil
+		}
+		leftValues, rightValues := left.Col.(*types.Bytes), right.Col.([]types.Date)
+		resultVector := vector.NewConst(resultType)
+		resultValues := make([]uint32, 1)
+		unit := string(leftValues.Get(0))
+		results, err := extract.ExtractFromDate(unit, rightValues, resultValues)
+		if err != nil {
+			return nil, errors.New("invalid input")
+		}
+		vector.SetCol(resultVector, results)
+		return resultVector, nil
+	case left.IsScalar() && !right.IsScalar():
+		if left.ConstVectorIsNull() {
+			return proc.AllocScalarNullVector(resultType), nil
+		}
+		leftValues, rightValues := left.Col.(*types.Bytes), right.Col.([]types.Date)
+		resultVector, err := proc.AllocVector(resultType, int64(resultElementSize*len(rightValues)))
+		if err != nil {
+			return nil, err
+		}
+		resultValues := encoding.DecodeUint32Slice(resultVector.Data)
+		resultValues = resultValues[:len(rightValues)]
+		unit := string(leftValues.Get(0))
+		results, err := extract.ExtractFromDate(unit, rightValues, resultValues)
+		if err != nil {
+			return nil, err
+		}
+		nulls.Set(resultVector.Nsp, right.Nsp)
+		vector.SetCol(resultVector, results)
+		return resultVector, nil
+	default:
+		return nil, errors.New("invalid input")
+	}
+}
diff --git a/pkg/sql/plan2/function/buitins.go b/pkg/sql/plan2/function/buitins.go
index f95befd29..a06add0be 100644
--- a/pkg/sql/plan2/function/buitins.go
+++ b/pkg/sql/plan2/function/buitins.go
@@ -330,6 +330,17 @@ var builtins = map[int][]Function{
 			Fn:          unary.Exp[float64],
 		},
 	},
+	EXTRACT: {
+		{
+			Index:       0,
+			Flag:        plan.Function_STRICT,
+			Layout:      STANDARD_FUNCTION,
+			Args:        []types.T{types.T_varchar, types.T_date},
+			ReturnTyp:   types.T_uint32,
+			TypeCheckFn: strictTypeCheck,
+			Fn:          binary.ExtractFromDate,
+		},
+	},
 	LENGTH: {
 		{
 			Index:       0,
@@ -1711,26 +1722,29 @@ var builtins = map[int][]Function{
 			Fn:          multi.UTCTimestamp,
 		},
 	},
-	EXTRACT: {
-		{
-			Index:       0,
-			Flag:        plan.Function_STRICT,
-			Layout:      STANDARD_FUNCTION,
-			Args:        []types.T{types.T_varchar, types.T_date},
-			ReturnTyp:   types.T_float64,
-			TypeCheckFn: strictTypeCheck,
-			Fn:          nil,
+	/*
+		EXTRACT: {
+			{
+				Index:       0,
+				Flag:        plan.Function_STRICT,
+				Layout:      STANDARD_FUNCTION,
+				Args:        []types.T{types.T_varchar, types.T_date},
+				ReturnTyp:   types.T_float64,
+				TypeCheckFn: strictTypeCheck,
+				Fn:          nil,
+			},
+			{
+				Index:       1,
+				Flag:        plan.Function_STRICT,
+				Layout:      STANDARD_FUNCTION,
+				Args:        []types.T{types.T_varchar, types.T_datetime},
+				ReturnTyp:   types.T_float64,
+				TypeCheckFn: strictTypeCheck,
+				Fn:          nil,
+			},
 		},
-		{
-			Index:       1,
-			Flag:        plan.Function_STRICT,
-			Layout:      STANDARD_FUNCTION,
-			Args:        []types.T{types.T_varchar, types.T_datetime},
-			ReturnTyp:   types.T_float64,
-			TypeCheckFn: strictTypeCheck,
-			Fn:          nil,
-		},
-	},
+
+	*/
 	DATE_ADD: {
 		{
 			Index:       0,
diff --git a/pkg/sql/plan2/function/operator/cast.go b/pkg/sql/plan2/function/operator/cast.go
index 99f82cc35..2aa8ada65 100644
--- a/pkg/sql/plan2/function/operator/cast.go
+++ b/pkg/sql/plan2/function/operator/cast.go
@@ -414,6 +414,11 @@ func Cast(vs []*vector.Vector, proc *process.Process) (*vector.Vector, error) {
 	if lv.Typ.Oid == types.T_timestamp && rv.Typ.Oid == types.T_datetime {
 		return castTimeStampAsDatetime(lv, rv, proc)
 	}
+
+	if lv.Typ.Oid == types.T_timestamp && rv.Typ.Oid == types.T_varchar {
+		return castTimestampAsVarchar(lv, rv, proc)
+	}
+
 	return nil, errors.New(errno.SyntaxErrororAccessRuleViolation, "parameter types of cast function do not match")
 }
 
@@ -927,6 +932,43 @@ func castTimeStampAsDatetime(lv, rv *vector.Vector, proc *process.Process) (*vec
 	return vec, nil
 }
 
+//  castTimestampAsVarchar : Cast converts timestamp to varchar
+func castTimestampAsVarchar(lv, rv *vector.Vector, proc *process.Process) (*vector.Vector, error) {
+	lvs := lv.Col.([]types.Timestamp)
+	resultType := rv.Typ
+	resultElementSize := int(resultType.Size)
+	if lv.IsScalar() {
+		vec := proc.AllocScalarVector(resultType)
+		rs := &types.Bytes{
+			Data:    []byte{},
+			Offsets: make([]uint32, 1),
+			Lengths: make([]uint32, 1),
+		}
+		if _, err := typecast.TimestampToVarchar(lvs, rs); err != nil {
+			return nil, err
+		}
+		nulls.Set(vec.Nsp, lv.Nsp)
+		vector.SetCol(vec, rs)
+		return vec, nil
+	}
+
+	vec, err := proc.AllocVector(resultType, int64(resultElementSize*len(lvs)))
+	if err != nil {
+		return nil, err
+	}
+	rs := &types.Bytes{
+		Data:    []byte{},
+		Offsets: make([]uint32, len(lvs)),
+		Lengths: make([]uint32, len(lvs)),
+	}
+	if _, err := typecast.TimestampToVarchar(lvs, rs); err != nil {
+		return nil, err
+	}
+	nulls.Set(vec.Nsp, lv.Nsp)
+	vector.SetCol(vec, rs)
+	return vec, nil
+}
+
 // CastStringAsDecimal64: onverts char/varchar as decimal64
 func CastStringAsDecimal64(lv, rv *vector.Vector, proc *process.Process) (*vector.Vector, error) {
 	resultType := rv.Typ
diff --git a/pkg/sql/plan2/function/operators.go b/pkg/sql/plan2/function/operators.go
index 893980a49..8be6c777c 100644
--- a/pkg/sql/plan2/function/operators.go
+++ b/pkg/sql/plan2/function/operators.go
@@ -3720,6 +3720,15 @@ var operators = map[int][]Function{
 			TypeCheckFn: strictTypeCheck,
 			Fn:          operator.Cast,
 		},
+		{
+			Index:       167,
+			Flag:        plan.Function_STRICT,
+			Layout:      CAST_EXPRESSION,
+			Args:        []types.T{types.T_timestamp, types.T_varchar},
+			ReturnTyp:   types.T_varchar,
+			TypeCheckFn: strictTypeCheck,
+			Fn:          operator.Cast,
+		},
 	},
 	CASE: {
 		{
diff --git a/pkg/sql/plan2/mock.go b/pkg/sql/plan2/mock.go
index d71720883..511a25934 100644
--- a/pkg/sql/plan2/mock.go
+++ b/pkg/sql/plan2/mock.go
@@ -15,8 +15,10 @@
 package plan2
 
 import (
+	"errors"
 	"strings"
 
+	"github.com/matrixorigin/matrixone/pkg/container/types"
 	"github.com/matrixorigin/matrixone/pkg/pb/plan"
 	"github.com/matrixorigin/matrixone/pkg/sql/parsers/tree"
 )
@@ -27,7 +29,20 @@ type MockCompilerContext struct {
 }
 
 func (m *MockCompilerContext) ResolveVariable(varName string, isSystemVar, isGlobalVar bool) (interface{}, error) {
-	return nil, nil
+	vars := make(map[string]interface{})
+	vars["str_var"] = "str"
+	vars["int_var"] = 20
+	vars["bool_var"] = false
+	vars["float_var"] = 20.20
+	dec, _ := types.ParseStringToDecimal128("200.001", 2, 2)
+	vars["decimal_var"] = dec
+	vars["null_var"] = nil
+
+	if result, ok := vars[varName]; ok {
+		return result, nil
+	}
+
+	return nil, errors.New("var not found")
 }
 
 type col struct {
diff --git a/pkg/sql/plan2/query_builder.go b/pkg/sql/plan2/query_builder.go
index 1b5c217ab..dbece2768 100644
--- a/pkg/sql/plan2/query_builder.go
+++ b/pkg/sql/plan2/query_builder.go
@@ -83,7 +83,10 @@ func (builder *QueryBuilder) resetNode(nodeId int32) (map[int64][2]int32, error)
 			returnMap[getColMapKey(tag, int32(idx))] = [2]int32{0, int32(idx)}
 		}
 		for _, expr := range node.WhereList {
-			builder.resetPosition(expr, returnMap)
+			err := builder.resetPosition(expr, returnMap)
+			if err != nil {
+				return nil, err
+			}
 		}
 	case plan.Node_JOIN:
 		// TODO deal with using
diff --git a/pkg/vectorize/extract/extract.go b/pkg/vectorize/extract/extract.go
new file mode 100644
index 000000000..5946ab230
--- /dev/null
+++ b/pkg/vectorize/extract/extract.go
@@ -0,0 +1,99 @@
+// Copyright 2022 Matrix Origin
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package extract
+
+import (
+	"errors"
+	"github.com/matrixorigin/matrixone/pkg/container/types"
+)
+
+//func extractFromDate()
+
+var validDateUnit = map[string]struct{}{
+	"year":       {},
+	"month":      {},
+	"day":        {},
+	"year_month": {},
+	"quarter":    {},
+}
+
+/*
+func ExtractFromInputBytes(unit string, inputBytes *types.Bytes, inputNsp *nulls.Nulls, results []uint32) ([]uint32, *nulls.Nulls, error) {
+	resultNsp := new(nulls.Nulls)
+	if _, ok := validDateUnit[unit]; !ok {
+		return []uint32{}, nil, errors.New("invalid unit")
+	}
+	for i := range inputBytes.Lengths {
+		if nulls.Contains(inputNsp, uint64(i)) {
+			nulls.Add(resultNsp, uint64(i))
+			continue
+		}
+		inputValue := string(inputBytes.Get(int64(i)))
+		date, err := types.ParseDate(inputValue)
+		if err != nil {
+			return []uint32{}, nil, errors.New("invalid date string")
+		}
+		results[i] = ExtractFromOneDate(unit, date)
+	}
+	return results, resultNsp, nil
+}
+
+*/
+
+func ExtractFromOneDate(unit string, date types.Date) uint32 {
+	switch unit {
+	case "day":
+		return uint32(date.Day())
+	case "month":
+		return uint32(date.Month())
+	case "quarter":
+		return date.Quarter()
+	case "year":
+		return uint32(date.Year())
+	case "year_month":
+		return date.YearMonth()
+	default:
+		return 0
+	}
+}
+
+func ExtractFromDate(unit string, dates []types.Date, results []uint32) ([]uint32, error) {
+	if _, ok := validDateUnit[unit]; !ok {
+		return []uint32{}, errors.New("invalid unit")
+	}
+	switch unit {
+	case "day":
+		for i, d := range dates {
+			results[i] = uint32(d.Day())
+		}
+	case "month":
+		for i, d := range dates {
+			results[i] = uint32(d.Month())
+		}
+	case "year":
+		for i, d := range dates {
+			results[i] = uint32(d.Year())
+		}
+	case "year_month":
+		for i, d := range dates {
+			results[i] = d.YearMonth()
+		}
+	case "quarter":
+		for i, d := range dates {
+			results[i] = d.Quarter()
+		}
+	}
+	return results, nil
+}
diff --git a/pkg/vectorize/typecast/typecast.go b/pkg/vectorize/typecast/typecast.go
index 36c35d69f..eef94d371 100644
--- a/pkg/vectorize/typecast/typecast.go
+++ b/pkg/vectorize/typecast/typecast.go
@@ -156,6 +156,7 @@ var (
 	Uint64ToDecimal128 = UintToDecimal128[uint64]
 
 	TimestampToDatetime = timestampToDatetime
+	TimestampToVarchar  = timestampToVarchar
 )
 
 func NumericToNumeric[T1, T2 constraints.Integer | constraints.Float](xs []T1, rs []T2) ([]T2, error) {
@@ -243,3 +244,16 @@ func UintToDecimal128[T constraints.Integer](xs []T, rs []types.Decimal128) ([]t
 func timestampToDatetime(xs []types.Timestamp, rs []types.Datetime) ([]types.Datetime, error) {
 	return types.TimestampToDatetime(xs, rs)
 }
+
+func timestampToVarchar(xs []types.Timestamp, rs *types.Bytes) (*types.Bytes, error) {
+	oldLen := uint32(0)
+	for i, x := range xs {
+		//todo: pass precision arg?
+		rs.Data = append(rs.Data, []byte(x.String())...)
+		newLen := uint32(len(rs.Data))
+		rs.Offsets[i] = oldLen
+		rs.Lengths[i] = newLen - oldLen
+		oldLen = newLen
+	}
+	return rs, nil
+}
diff --git a/pkg/vm/engine/tae/db/replay.go b/pkg/vm/engine/tae/db/replay.go
index 9e7e8617e..49251277f 100644
--- a/pkg/vm/engine/tae/db/replay.go
+++ b/pkg/vm/engine/tae/db/replay.go
@@ -2,6 +2,10 @@ package db
 
 import (
 	"bytes"
+	"fmt"
+	"io/ioutil"
+	"os"
+	"path"
 	"sync"
 
 	"github.com/RoaringBitmap/roaring"
@@ -66,6 +70,26 @@ func (replayer *Replayer) PreReplayWal() {
 	}
 }
 
+func (replayer *Replayer) scanFiles() map[uint64]string {
+	files := make(map[uint64]string)
+	infos, err := ioutil.ReadDir(replayer.db.Dir)
+	if err != nil {
+		panic(err)
+	}
+	for _, info := range infos {
+		if info.IsDir() {
+			continue
+		}
+		name := info.Name()
+		id, err := replayer.db.FileFactory.DecodeName(name)
+		if err != nil {
+			continue
+		}
+		files[id] = path.Join(replayer.db.Dir, name)
+	}
+	return files
+}
+
 func (replayer *Replayer) Replay() {
 	if err := replayer.db.Wal.Replay(replayer.OnReplayEntry); err != nil {
 		panic(err)
@@ -77,6 +101,7 @@ func (replayer *Replayer) Replay() {
 }
 
 func (replayer *Replayer) PostReplayWal() {
+	activeSegs := make(map[uint64]*catalog.SegmentEntry)
 	processor := new(catalog.LoopProcessor)
 	processor.DatabaseFn = func(entry *catalog.DBEntry) (err error) {
 		if entry.IsActive() {
@@ -106,9 +131,15 @@ func (replayer *Replayer) PostReplayWal() {
 	}
 	processor.SegmentFn = func(entry *catalog.SegmentEntry) (err error) {
 		if entry.IsActive() {
+			if !entry.GetTable().IsVirtual() {
+				activeSegs[entry.ID] = entry
+			}
 			return
 		}
 		if entry.GetLogIndex().LSN > replayer.db.Wal.GetCheckpointed() {
+			if !entry.GetTable().IsVirtual() {
+				activeSegs[entry.ID] = entry
+			}
 			return
 		}
 		if err = gcSegmentClosure(entry, GCType_Segment)(); err != nil {
@@ -130,6 +161,23 @@ func (replayer *Replayer) PostReplayWal() {
 		return
 	}
 	_ = replayer.db.Catalog.RecurLoop(processor)
+
+	files := replayer.scanFiles()
+	for id := range activeSegs {
+		_, ok := files[id]
+		if !ok {
+			panic(fmt.Errorf("Cannot find segment file for: %d", id))
+		}
+		delete(files, id)
+	}
+	for _, file := range files {
+		logutil.Info("[Replay]", common.OperationField("clean-segment"),
+			common.OperandField(file))
+		if err := os.Remove(file); err != nil {
+			panic(err)
+		}
+	}
+
 	logutil.Info(replayer.db.Catalog.SimplePPString(common.PPL1))
 }
 


Test patch:
diff --git a/pkg/logservice/errors_test.go b/pkg/logservice/errors_test.go
index 8ed7ecd12..76867b330 100644
--- a/pkg/logservice/errors_test.go
+++ b/pkg/logservice/errors_test.go
@@ -18,6 +18,7 @@ import (
 	"errors"
 	"testing"
 
+	"github.com/lni/dragonboat/v4"
 	"github.com/stretchr/testify/assert"
 
 	pb "github.com/matrixorigin/matrixone/pkg/pb/logservice"
@@ -44,3 +45,36 @@ func TestUnknownErrorIsHandled(t *testing.T) {
 	assert.Equal(t, pb.ErrorCode_OtherSystemError, code)
 	assert.Equal(t, err.Error(), str)
 }
+
+func TestIsTempError(t *testing.T) {
+	tests := []struct {
+		err  error
+		temp bool
+	}{
+		{dragonboat.ErrInvalidOperation, false},
+		{dragonboat.ErrInvalidAddress, false},
+		{dragonboat.ErrInvalidSession, false},
+		{dragonboat.ErrTimeoutTooSmall, false},
+		{dragonboat.ErrPayloadTooBig, false},
+		{dragonboat.ErrSystemBusy, true},
+		{dragonboat.ErrShardClosed, true},
+		{dragonboat.ErrShardNotInitialized, true},
+		{dragonboat.ErrTimeout, true},
+		{dragonboat.ErrClosed, true},
+		{dragonboat.ErrCanceled, false},
+		{dragonboat.ErrRejected, false},
+		{dragonboat.ErrShardNotReady, true},
+		{dragonboat.ErrInvalidTarget, false},
+		{dragonboat.ErrInvalidRange, false},
+
+		{ErrDeadlineNotSet, false},
+		{ErrInvalidDeadline, false},
+		{ErrInvalidTruncateIndex, false},
+		{ErrNotLeaseHolder, false},
+		{ErrOutOfRange, false},
+		{ErrInvalidShardID, false},
+	}
+	for _, tt := range tests {
+		assert.Equal(t, tt.temp, isTempError(tt.err))
+	}
+}
diff --git a/pkg/logservice/transport_test.go b/pkg/logservice/transport_test.go
index d51c8be7a..aa8103df2 100644
--- a/pkg/logservice/transport_test.go
+++ b/pkg/logservice/transport_test.go
@@ -234,7 +234,7 @@ func TestReadMagicNumber(t *testing.T) {
 	conn := newTestConn()
 	_, err := conn.Write([]byte{0, 0})
 	assert.NoError(t, err)
-	assert.Equal(t, ErrBadMessage, readMagicNumber(conn, nil))
+	assert.Equal(t, errBadMessage, readMagicNumber(conn, nil))
 
 	conn = newTestConn()
 	_, err = conn.Write(magicNumber[:])
diff --git a/pkg/sql/plan2/build_test.go b/pkg/sql/plan2/build_test.go
index 3efb2faab..d9d51c3db 100644
--- a/pkg/sql/plan2/build_test.go
+++ b/pkg/sql/plan2/build_test.go
@@ -30,7 +30,8 @@ func TestSingleSql(t *testing.T) {
 	// sql := `SELECT * FROM (SELECT relname as Tables_in_mo FROM mo_tables WHERE reldatabase = 'mo') a`
 	// sql := "SELECT nation2.* FROM nation2 natural join region"
 	// sql := `select n_name, avg(N_REGIONKEY) t from NATION where n_name != 'a' group by n_name having avg(N_REGIONKEY) > 10 order by t limit 20`
-	sql := `select distinct(n_name), ((abs(n_regionkey))) from nation`
+	// sql := `select date_add('1997-12-31 23:59:59',INTERVAL 100000 SECOND)`
+	sql := "select @str_var, @int_var, @bool_var, @@global.float_var, @@session.null_var"
 	// stmts, err := mysql.Parse(sql)
 	// if err != nil {
 	// 	t.Fatalf("%+v", err)
@@ -383,6 +384,7 @@ func TestSingleTableSqlBuilder(t *testing.T) {
 
 	// should pass
 	sqls := []string{
+		"SELECT '1900-01-01 00:00:00' + INTERVAL 2147483648 SECOND",
 		"SELECT N_NAME, N_REGIONKEY FROM NATION WHERE N_REGIONKEY > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY LIMIT 10, 20",
 		"SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_REGIONKEY > 0 ORDER BY a DESC", //test alias
 		"SELECT NATION.N_NAME FROM NATION",                                       //test alias
@@ -410,6 +412,10 @@ func TestSingleTableSqlBuilder(t *testing.T) {
 		"SELECT -1",
 		"select date_add('1997-12-31 23:59:59',INTERVAL 100000 SECOND)",
 		"select date_sub('1997-12-31 23:59:59',INTERVAL 2 HOUR)",
+		"select @str_var, @int_var, @bool_var, @float_var, @null_var",
+		"select @str_var, @@global.int_var, @@session.bool_var",
+		"select n_name from nation where n_name != @str_var and n_regionkey > @int_var",
+		"select n_name from nation where n_name != @@global.str_var and n_regionkey > @@session.int_var",
 		"select distinct(n_name), ((abs(n_regionkey))) from nation",
 	}
 	runTestShouldPass(mock, t, sqls, false, false)
@@ -423,6 +429,7 @@ func TestSingleTableSqlBuilder(t *testing.T) {
 		"SELECT N_NAME FROM NATION WHERE ffff(N_REGIONKEY) > 0",             //function name not exist
 		"SELECT NATION.N_NAME FROM NATION a",                                // mysql should error, but i don't think it is necesssary
 		"select n_nationkey, sum(n_nationkey) from nation",
+		"select n_name from nation where n_name != @not_exist_var",
 
 		"SELECT DISTINCT N_NAME FROM NATION GROUP BY N_REGIONKEY", //test distinct with group by
 	}
diff --git a/pkg/sql/plan2/function/builtin/binary/extract_test.go b/pkg/sql/plan2/function/builtin/binary/extract_test.go
new file mode 100644
index 000000000..648d4d09a
--- /dev/null
+++ b/pkg/sql/plan2/function/builtin/binary/extract_test.go
@@ -0,0 +1,81 @@
+// Copyright 2022 Matrix Origin
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package binary
+
+import (
+	"fmt"
+	"github.com/matrixorigin/matrixone/pkg/container/nulls"
+	"github.com/matrixorigin/matrixone/pkg/container/vector"
+	"github.com/matrixorigin/matrixone/pkg/sql/testutil"
+	"github.com/stretchr/testify/require"
+	"testing"
+)
+
+func TestExtractFromDate(t *testing.T) {
+	vector0 := testutil.MakeScalarVarchar("year", 4)
+	vector1 := testutil.MakeDateVector([]string{"2020-01-01", "2021-02-03", "2024-03-04", ""}, []uint64{3})
+	inputVectors := make([]*vector.Vector, 2)
+	inputVectors[0] = vector0
+	inputVectors[1] = vector1
+	proc := testutil.NewProc()
+	outputVector, err := ExtractFromDate(inputVectors, proc)
+	require.NoError(t, err)
+	outputValues, ok := outputVector.Col.([]uint32)
+	require.True(t, ok)
+	require.Equal(t, []uint32{2020, 2021, 2024, 1}, outputValues)
+	require.True(t, nulls.Contains(outputVector.Nsp, uint64(3)))
+
+	vector0 = testutil.MakeScalarVarchar("month", 4)
+	vector1 = testutil.MakeDateVector([]string{"2020-01-01", "2021-02-03", "2024-03-04", ""}, []uint64{3})
+	inputVectors = make([]*vector.Vector, 2)
+	inputVectors[0] = vector0
+	inputVectors[1] = vector1
+	proc = testutil.NewProc()
+	outputVector, err = ExtractFromDate(inputVectors, proc)
+	require.NoError(t, err)
+	outputValues, ok = outputVector.Col.([]uint32)
+	fmt.Println(outputValues)
+	require.True(t, ok)
+	require.Equal(t, []uint32{1, 2, 3, 1}, outputValues)
+	require.True(t, nulls.Contains(outputVector.Nsp, uint64(3)))
+
+	vector0 = testutil.MakeScalarVarchar("day", 4)
+	vector1 = testutil.MakeDateVector([]string{"2020-01-01", "2021-02-03", "2024-03-04", ""}, []uint64{3})
+	inputVectors = make([]*vector.Vector, 2)
+	inputVectors[0] = vector0
+	inputVectors[1] = vector1
+	proc = testutil.NewProc()
+	outputVector, err = ExtractFromDate(inputVectors, proc)
+	require.NoError(t, err)
+	outputValues, ok = outputVector.Col.([]uint32)
+	fmt.Println(outputValues)
+	require.True(t, ok)
+	require.Equal(t, []uint32{1, 3, 4, 1}, outputValues)
+	require.True(t, nulls.Contains(outputVector.Nsp, uint64(3)))
+
+	vector0 = testutil.MakeScalarVarchar("year_month", 4)
+	vector1 = testutil.MakeDateVector([]string{"2020-01-01", "2021-02-03", "2024-03-04", ""}, []uint64{3})
+	inputVectors = make([]*vector.Vector, 2)
+	inputVectors[0] = vector0
+	inputVectors[1] = vector1
+	proc = testutil.NewProc()
+	outputVector, err = ExtractFromDate(inputVectors, proc)
+	require.NoError(t, err)
+	outputValues, ok = outputVector.Col.([]uint32)
+	fmt.Println(outputValues)
+	require.True(t, ok)
+	require.Equal(t, []uint32{202001, 202102, 202403, 101}, outputValues)
+	require.True(t, nulls.Contains(outputVector.Nsp, uint64(3)))
+}
diff --git a/pkg/sql/plan2/function/operator/cast_test.go b/pkg/sql/plan2/function/operator/cast_test.go
index 74294fe6e..69c3c01fe 100644
--- a/pkg/sql/plan2/function/operator/cast_test.go
+++ b/pkg/sql/plan2/function/operator/cast_test.go
@@ -15,6 +15,9 @@
 package operator
 
 import (
+	"testing"
+	"unsafe"
+
 	roaring "github.com/RoaringBitmap/roaring/roaring64"
 	"github.com/matrixorigin/matrixone/pkg/container/nulls"
 	"github.com/matrixorigin/matrixone/pkg/container/types"
@@ -23,8 +26,6 @@ import (
 	"github.com/matrixorigin/matrixone/pkg/vm/process"
 	"github.com/smartystreets/goconvey/convey"
 	"github.com/stretchr/testify/require"
-	"testing"
-	"unsafe"
 )
 
 func TestCastSameType(t *testing.T) {
@@ -1916,6 +1917,63 @@ func TestCastVarcharAsDate(t *testing.T) {
 	})
 }
 
+func TestCastTimestampAsVarchar(t *testing.T) {
+	//Cast converts timestamp to varchar
+	//procs := testutil.NewProc()
+	cases := []struct {
+		name     string
+		vecs     []*vector.Vector
+		proc     *process.Process
+		input    []types.Timestamp
+		expected *types.Bytes
+		isScalar bool
+	}{
+		//{
+		//	name:  "01 - normal test",
+		//	proc:  procs,
+		//	input: []types.Timestamp{66823357574906480},
+		//	expected: &types.Bytes{
+		//		Data:    []byte("2020-06-14 16:24:15.230000"),
+		//		Offsets: []uint32{0},
+		//		Lengths: []uint32{26},
+		//	},
+		//	isScalar: false,
+		//},
+		//{
+		//	name:  "02 - scalar test",
+		//	proc:  procs,
+		//	input: []types.Timestamp{66823357574906480},
+		//	expected: &types.Bytes{
+		//		Data:    []byte("2020-06-14 16:24:15.230000"),
+		//		Offsets: []uint32{0},
+		//		Lengths: []uint32{26},
+		//	},
+		//	isScalar: true,
+		//},
+	}
+
+	for _, c := range cases {
+		t.Run(c.name, func(t *testing.T) {
+			vecs := make([]*vector.Vector, 2)
+			if c.input != nil {
+				vecs[0] = vector.New(types.T_timestamp.ToType())
+				vecs[0].Col = c.input
+				vecs[0].IsConst = c.isScalar
+			} else {
+				vecs[0] = testutil.MakeScalarNull(0)
+			}
+			vecs[1] = vector.New(types.T_varchar.ToType())
+
+			result, err := Cast(vecs, c.proc)
+			if err != nil {
+				t.Fatal(err)
+			}
+			require.Equal(t, c.expected, result.Col.(*types.Bytes))
+			require.Equal(t, c.isScalar, result.IsScalar())
+		})
+	}
+}
+
 func TestCastDecimal64AsDecimal128(t *testing.T) {
 	//Cast converts decimal64 to decimal128
 	makeTempVector := func(left types.Decimal64, leftType types.Type, leftScalar bool, destType types.Type) []*vector.Vector {
diff --git a/pkg/vectorize/extract/extract_test.go b/pkg/vectorize/extract/extract_test.go
new file mode 100644
index 000000000..7366d167e
--- /dev/null
+++ b/pkg/vectorize/extract/extract_test.go
@@ -0,0 +1,63 @@
+// Copyright 2022 Matrix Origin
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package extract
+
+import (
+	"github.com/matrixorigin/matrixone/pkg/container/types"
+	"github.com/stretchr/testify/require"
+	"testing"
+)
+
+func TestExtractFromDate(t *testing.T) {
+	a0, _ := types.ParseDate("2020-01-02")
+	a1, _ := types.ParseDate("2021-03-04")
+	inputs := make([]types.Date, 2)
+	inputs[0] = a0
+	inputs[1] = a1
+	results := make([]uint32, 2)
+	output, err := ExtractFromDate("year", inputs, results)
+	require.NoError(t, err)
+	require.Equal(t, []uint32{2020, 2021}, output)
+
+	a0, _ = types.ParseDate("2020-01-02")
+	a1, _ = types.ParseDate("2021-03-04")
+	inputs = make([]types.Date, 2)
+	inputs[0] = a0
+	inputs[1] = a1
+	results = make([]uint32, 2)
+	output, err = ExtractFromDate("month", inputs, results)
+	require.NoError(t, err)
+	require.Equal(t, []uint32{01, 03}, output)
+
+	a0, _ = types.ParseDate("2020-01-02")
+	a1, _ = types.ParseDate("2021-03-04")
+	inputs = make([]types.Date, 2)
+	inputs[0] = a0
+	inputs[1] = a1
+	results = make([]uint32, 2)
+	output, err = ExtractFromDate("year_month", inputs, results)
+	require.NoError(t, err)
+	require.Equal(t, []uint32{202001, 202103}, output)
+
+	a0, _ = types.ParseDate("2020-01-02")
+	a1, _ = types.ParseDate("2021-03-04")
+	inputs = make([]types.Date, 2)
+	inputs[0] = a0
+	inputs[1] = a1
+	results = make([]uint32, 2)
+	output, err = ExtractFromDate("quarter", inputs, results)
+	require.NoError(t, err)
+	require.Equal(t, []uint32{1, 1}, output)
+}