func TestSqlQuerySplitQueryInvalidQuery(t *testing.T) {
db := setUpSQLQueryTest()
db.AddQuery("SELECT MIN(pk), MAX(pk) FROM test_table", &mproto.QueryResult{
Fields: []mproto.Field{
mproto.Field{Name: "pk", Type: mproto.VT_LONG},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("100")),
},
},
})
db.AddQuery("SELECT pk FROM test_table LIMIT 0", &mproto.QueryResult{
Fields: []mproto.Field{
mproto.Field{Name: "pk", Type: mproto.VT_LONG},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
},
},
})
testUtils := newTestUtils()
config := testUtils.newQueryServiceConfig()
sqlQuery := NewSqlQuery(config)
dbconfigs := testUtils.newDBConfigs()
err := sqlQuery.allowQueries(nil, &dbconfigs, []SchemaOverride{}, testUtils.newMysqld(&dbconfigs))
if err != nil {
t.Fatalf("allowQueries failed: %v", err)
}
defer sqlQuery.disallowQueries()
ctx := context.Background()
query := proto.SplitQueryRequest{
Query: proto.BoundQuery{
// add limit clause to make SplitQuery fail
Sql: "select * from test_table where count > :count limit 1000",
BindVariables: nil,
},
SplitCount: 10,
SessionID: sqlQuery.sessionID,
}
reply := proto.SplitQueryResult{
Queries: []proto.QuerySplit{
proto.QuerySplit{
Query: proto.BoundQuery{
Sql: "",
BindVariables: nil,
},
RowCount: 10,
},
},
}
if err := sqlQuery.SplitQuery(ctx, nil, &query, &reply); err == nil {
t.Fatalf("SqlQuery.SplitQuery should fail")
}
}
func TestTabletServerSplitQuery(t *testing.T) {
db := setUpTabletServerTest()
db.AddQuery("SELECT MIN(pk), MAX(pk) FROM test_table", &mproto.QueryResult{
Fields: []mproto.Field{
mproto.Field{Name: "pk", Type: mproto.VT_LONG},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("100")),
},
},
})
db.AddQuery("SELECT pk FROM test_table LIMIT 0", &mproto.QueryResult{
Fields: []mproto.Field{
mproto.Field{Name: "pk", Type: mproto.VT_LONG},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
},
},
})
testUtils := newTestUtils()
config := testUtils.newQueryServiceConfig()
tsv := NewTabletServer(config)
dbconfigs := testUtils.newDBConfigs(db)
err := tsv.StartService(nil, &dbconfigs, []SchemaOverride{}, testUtils.newMysqld(&dbconfigs))
if err != nil {
t.Fatalf("StartService failed: %v", err)
}
defer tsv.StopService()
ctx := context.Background()
query := proto.SplitQueryRequest{
Query: proto.BoundQuery{
Sql: "select * from test_table where count > :count",
BindVariables: nil,
},
SplitCount: 10,
SessionID: tsv.sessionID,
}
reply := proto.SplitQueryResult{
Queries: []proto.QuerySplit{
proto.QuerySplit{
Query: proto.BoundQuery{
Sql: "",
BindVariables: nil,
},
RowCount: 10,
},
},
}
if err := tsv.SplitQuery(ctx, nil, &query, &reply); err != nil {
t.Fatalf("TabletServer.SplitQuery should success: %v, but get error: %v",
query, err)
}
}
func (qs *QuerySplitter) splitBoundariesUintColumn(pkMinMax *sqltypes.Result) ([]sqltypes.Value, error) {
boundaries := []sqltypes.Value{}
if pkMinMax == nil || len(pkMinMax.Rows) != 1 || pkMinMax.Rows[0][0].IsNull() || pkMinMax.Rows[0][1].IsNull() {
return boundaries, nil
}
minNumeric := sqltypes.MakeNumeric(pkMinMax.Rows[0][0].Raw())
maxNumeric := sqltypes.MakeNumeric(pkMinMax.Rows[0][1].Raw())
min, err := minNumeric.ParseUint64()
if err != nil {
return nil, err
}
max, err := maxNumeric.ParseUint64()
if err != nil {
return nil, err
}
interval := (max - min) / uint64(qs.splitCount)
if interval == 0 {
return nil, err
}
qs.rowCount = int64(interval)
for i := uint64(1); i < uint64(qs.splitCount); i++ {
v, err := sqltypes.BuildValue(min + interval*i)
if err != nil {
return nil, err
}
boundaries = append(boundaries, v)
}
return boundaries, nil
}
func createTestTableBaseShowTable(tableName string) []sqltypes.Value {
return []sqltypes.Value{
sqltypes.MakeString([]byte(tableName)),
sqltypes.MakeString([]byte("USER TABLE")),
sqltypes.MakeNumeric([]byte("1427325875")),
sqltypes.MakeString([]byte("")),
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("2")),
sqltypes.MakeNumeric([]byte("3")),
sqltypes.MakeNumeric([]byte("4")),
}
}
func createTestTableUpdatedStats(tableName string) []sqltypes.Value {
return []sqltypes.Value{
sqltypes.MakeString([]byte(tableName)),
sqltypes.MakeString([]byte("USER TABLE")),
sqltypes.MakeNumeric([]byte("0")),
sqltypes.MakeString([]byte("")),
sqltypes.MakeNumeric([]byte("4")),
sqltypes.MakeNumeric([]byte("5")),
sqltypes.MakeNumeric([]byte("6")),
sqltypes.MakeNumeric([]byte("7")),
}
}
func (qs *QuerySplitter) parseInt(pkMinMax *mproto.QueryResult) ([]sqltypes.Value, error) {
boundaries := []sqltypes.Value{}
minNumeric := sqltypes.MakeNumeric(pkMinMax.Rows[0][0].Raw())
maxNumeric := sqltypes.MakeNumeric(pkMinMax.Rows[0][1].Raw())
if pkMinMax.Rows[0][0].Raw()[0] == '-' {
// signed values, use int64
min, err := minNumeric.ParseInt64()
if err != nil {
return nil, err
}
max, err := maxNumeric.ParseInt64()
if err != nil {
return nil, err
}
interval := (max - min) / int64(qs.splitCount)
if interval == 0 {
return nil, err
}
qs.rowCount = interval
for i := int64(1); i < int64(qs.splitCount); i++ {
v, err := sqltypes.BuildValue(min + interval*i)
if err != nil {
return nil, err
}
boundaries = append(boundaries, v)
}
return boundaries, nil
}
// unsigned values, use uint64
min, err := minNumeric.ParseUint64()
if err != nil {
return nil, err
}
max, err := maxNumeric.ParseUint64()
if err != nil {
return nil, err
}
interval := (max - min) / uint64(qs.splitCount)
if interval == 0 {
return nil, err
}
qs.rowCount = int64(interval)
for i := uint64(1); i < uint64(qs.splitCount); i++ {
v, err := sqltypes.BuildValue(min + interval*i)
if err != nil {
return nil, err
}
boundaries = append(boundaries, v)
}
return boundaries, nil
}
func TestCodexBuildKey(t *testing.T) {
testUtils := newTestUtils()
newKey := buildKey([]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("2")),
})
testUtils.checkEqual(t, "1.2", newKey)
newKey = buildKey([]sqltypes.Value{
sqltypes.MakeString([]byte("a")),
sqltypes.NULL,
})
testUtils.checkEqual(t, "", newKey)
}
func TestQueryExecutorPlanInsertSubQuery(t *testing.T) {
db := setUpQueryExecutorTest()
query := "insert into test_table(pk) select pk from test_table where pk = 1 limit 1000"
want := &mproto.QueryResult{
Rows: [][]sqltypes.Value{},
}
db.AddQuery(query, want)
selectQuery := "select pk from test_table where pk = 1 limit 1000"
db.AddQuery(selectQuery, &mproto.QueryResult{
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{sqltypes.MakeNumeric([]byte("2"))},
},
})
insertQuery := "insert into test_table(pk) values (2) /* _stream test_table (pk ) (2 ); */"
db.AddQuery(insertQuery, &mproto.QueryResult{})
ctx := context.Background()
sqlQuery := newTestSQLQuery(ctx, enableRowCache|enableStrict)
qre := newTestQueryExecutor(ctx, sqlQuery, query, newTransaction(sqlQuery))
defer sqlQuery.disallowQueries()
defer testCommitHelper(t, sqlQuery, qre)
checkPlanID(t, planbuilder.PLAN_INSERT_SUBQUERY, qre.plan.PlanId)
got, err := qre.Execute()
if err != nil {
t.Fatalf("qre.Execute() = %v, want nil", err)
}
if !reflect.DeepEqual(got, want) {
t.Fatalf("got: %v, want: %v", got, want)
}
}
func TestQueryExecutorPlanInsertSubQueryAutoCommmit(t *testing.T) {
db := setUpQueryExecutorTest()
query := "insert into test_table(pk) select pk from test_table where pk = 1 limit 1000"
want := &mproto.QueryResult{
Rows: [][]sqltypes.Value{},
}
db.AddQuery(query, want)
selectQuery := "select pk from test_table where pk = 1 limit 1000"
db.AddQuery(selectQuery, &mproto.QueryResult{
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{sqltypes.MakeNumeric([]byte("2"))},
},
})
insertQuery := "insert into test_table(pk) values (2) /* _stream test_table (pk ) (2 ); */"
db.AddQuery(insertQuery, &mproto.QueryResult{})
ctx := context.Background()
tsv := newTestTabletServer(ctx, enableRowCache|enableStrict, db)
qre := newTestQueryExecutor(ctx, tsv, query, 0)
defer tsv.StopService()
checkPlanID(t, planbuilder.PlanInsertSubquery, qre.plan.PlanID)
got, err := qre.Execute()
if err != nil {
t.Fatalf("qre.Execute() = %v, want nil", err)
}
if !reflect.DeepEqual(got, want) {
t.Fatalf("got: %v, want: %v", got, want)
}
}
// makeValueString returns a string that contains all the passed-in rows
// as an insert SQL command's parameters.
func makeValueString(fields []*query.Field, rows [][]sqltypes.Value) string {
buf := bytes.Buffer{}
for i, row := range rows {
if i > 0 {
buf.Write([]byte(",("))
} else {
buf.WriteByte('(')
}
for j, value := range row {
if j > 0 {
buf.WriteByte(',')
}
// convert value back to its original type
if !value.IsNull() {
switch {
case sqltypes.IsIntegral(fields[j].Type):
value = sqltypes.MakeNumeric(value.Raw())
case sqltypes.IsFloat(fields[j].Type):
value = sqltypes.MakeFractional(value.Raw())
}
}
value.EncodeSQL(&buf)
}
buf.WriteByte(')')
}
return buf.String()
}
func TestQueryExecutorPlanInsertSubQuery(t *testing.T) {
db := setUpQueryExecutorTest()
testUtils := &testUtils{}
fields := []mproto.Field{
mproto.Field{Name: "pk", Type: mproto.VT_LONG},
}
query := "insert into test_table(pk) select pk from test_table where pk = 1 limit 1000"
expected := &mproto.QueryResult{
Fields: fields,
Rows: [][]sqltypes.Value{},
}
db.AddQuery(query, expected)
selectQuery := "select pk from test_table where pk = 1 limit 1000"
db.AddQuery(selectQuery, &mproto.QueryResult{
Fields: fields,
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{sqltypes.MakeNumeric([]byte("2"))},
},
})
insertQuery := "insert into test_table(pk) values (2) /* _stream test_table (pk ) (2 ); */"
db.AddQuery(insertQuery, &mproto.QueryResult{
Fields: fields,
})
qre, sqlQuery := newTestQueryExecutor(
query, context.Background(), enableRowCache|enableTx|enableStrict)
defer sqlQuery.disallowQueries()
defer testCommitHelper(t, sqlQuery, qre)
checkPlanID(t, planbuilder.PLAN_INSERT_SUBQUERY, qre.plan.PlanId)
testUtils.checkEqual(t, expected, qre.Execute())
}
func (vc *vcursor) Execute(query *tproto.BoundQuery) (*sqltypes.Result, error) {
vc.query = query
if vc.mustFail {
return nil, errors.New("execute failed")
}
switch {
case strings.HasPrefix(query.Sql, "select"):
if vc.result != nil {
return vc.result, nil
}
result := &sqltypes.Result{
Fields: []*querypb.Field{{
Type: sqltypes.Int32,
}},
RowsAffected: uint64(vc.numRows),
}
for i := 0; i < vc.numRows; i++ {
result.Rows = append(result.Rows, []sqltypes.Value{
sqltypes.MakeNumeric([]byte(fmt.Sprintf("%d", i+1))),
})
}
return result, nil
case strings.HasPrefix(query.Sql, "insert"):
return &sqltypes.Result{InsertID: 1}, nil
case strings.HasPrefix(query.Sql, "delete"):
return &sqltypes.Result{}, nil
}
panic("unexpected")
}
func TestCodexResolveListArg(t *testing.T) {
testUtils := newTestUtils()
tableInfo := createTableInfo("Table",
[]string{"pk1", "pk2", "col1"},
[]string{"int", "varbinary(128)", "int"},
[]string{"pk1", "pk2"})
key := "var"
bindVariables := make(map[string]interface{})
bindVariables[key] = []interface{}{fmt.Errorf("error is not supported")}
_, err := resolveListArg(tableInfo.GetPKColumn(0), "::"+key, bindVariables)
testUtils.checkTabletError(t, err, ErrFail, "")
bindVariables[key] = []interface{}{"1"}
_, err = resolveListArg(tableInfo.GetPKColumn(0), "::"+key, bindVariables)
testUtils.checkTabletError(t, err, ErrFail, "type mismatch")
bindVariables[key] = []interface{}{10}
result, err := resolveListArg(tableInfo.GetPKColumn(0), "::"+key, bindVariables)
if err != nil {
t.Fatalf("should not get an error, but got error: %v", err)
}
testUtils.checkEqual(t, []sqltypes.Value{sqltypes.MakeNumeric([]byte("10"))}, result)
}
// makeValueString returns a string that contains all the passed-in rows
// as an insert SQL command's parameters.
func makeValueString(fields []mproto.Field, rows [][]sqltypes.Value) string {
buf := bytes.Buffer{}
for i, row := range rows {
if i > 0 {
buf.Write([]byte(",("))
} else {
buf.WriteByte('(')
}
for j, value := range row {
if j > 0 {
buf.WriteByte(',')
}
// convert value back to its original type
if !value.IsNull() {
switch fields[j].Type {
case mproto.VT_TINY, mproto.VT_SHORT, mproto.VT_LONG, mproto.VT_LONGLONG, mproto.VT_INT24:
value = sqltypes.MakeNumeric(value.Raw())
case mproto.VT_FLOAT, mproto.VT_DOUBLE:
value = sqltypes.MakeFractional(value.Raw())
}
}
value.EncodeSql(&buf)
}
buf.WriteByte(')')
}
return buf.String()
}
// Split will split the rows into subset for each distribution
func (rs *RowSplitter) Split(result [][][]sqltypes.Value, rows [][]sqltypes.Value) error {
if rs.Type == key.KIT_UINT64 {
for _, row := range rows {
v := sqltypes.MakeNumeric(row[rs.ValueIndex].Raw())
i, err := v.ParseUint64()
if err != nil {
return fmt.Errorf("Non numerical value: %v", err)
}
k := key.Uint64Key(i).KeyspaceId()
for i, kr := range rs.KeyRanges {
if kr.Contains(k) {
result[i] = append(result[i], row)
break
}
}
}
} else {
for _, row := range rows {
k := key.KeyspaceId(row[rs.ValueIndex].Raw())
for i, kr := range rs.KeyRanges {
if kr.Contains(k) {
result[i] = append(result[i], row)
break
}
}
}
}
return nil
}
func TestQueryResult(t *testing.T) {
want := "\xcb\x00\x00\x00\x04Fields\x009\x00\x00\x00\x030\x001\x00\x00\x00\x05Name\x00\x04\x00\x00\x00\x00name\x12Type\x00\x01\x00\x00\x00\x00\x00\x00\x00\x12Flags\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x12RowsAffected\x00\x02\x00\x00\x00\x00\x00\x00\x00\x12InsertId\x00\x03\x00\x00\x00\x00\x00\x00\x00\x04Rows\x00 \x00\x00\x00\x040\x00\x18\x00\x00\x00\x050\x00\x01\x00\x00\x00\x001\x051\x00\x02\x00\x00\x00\x00aa\x00\x00\x03Err\x002\x00\x00\x00\x12Code\x00\xe8\x03\x00\x00\x00\x00\x00\x00\x05Message\x00\x11\x00\x00\x00\x00failed due to err\x00\x00"
custom := QueryResult{
Fields: []*query.Field{{"name", sqltypes.Int8}},
RowsAffected: 2,
InsertId: 3,
Rows: [][]sqltypes.Value{
{sqltypes.MakeNumeric([]byte("1")), sqltypes.MakeString([]byte("aa"))},
},
Err: &RPCError{1000, "failed due to err"},
}
encoded, err := bson.Marshal(&custom)
if err != nil {
t.Error(err)
}
got := string(encoded)
if want != got {
t.Errorf("want\n%#v, got\n%#v", want, got)
}
var unmarshalled QueryResult
err = bson.Unmarshal(encoded, &unmarshalled)
if err != nil {
t.Error(err)
}
if unmarshalled.Err == nil {
t.Errorf("want %#v, got %#v", custom.Err, unmarshalled.Err)
} else {
if *custom.Err != *unmarshalled.Err {
t.Errorf("want %#v, got %#v", custom.Err, unmarshalled.Err)
}
}
if custom.RowsAffected != unmarshalled.RowsAffected {
t.Errorf("want %v, got %#v", custom.RowsAffected, unmarshalled.RowsAffected)
}
if custom.InsertId != unmarshalled.InsertId {
t.Errorf("want %v, got %#v", custom.InsertId, unmarshalled.InsertId)
}
if custom.Fields[0].Name != unmarshalled.Fields[0].Name {
t.Errorf("want %v, got %#v", custom.Fields[0].Name, unmarshalled.Fields[0].Name)
}
if custom.Fields[0].Type != unmarshalled.Fields[0].Type {
t.Errorf("want %v, got %#v", custom.Fields[0].Type, unmarshalled.Fields[0].Type)
}
if !bytes.Equal(custom.Rows[0][0].Raw(), unmarshalled.Rows[0][0].Raw()) {
t.Errorf("want %s, got %s", custom.Rows[0][0].Raw(), unmarshalled.Rows[0][0].Raw())
}
if !bytes.Equal(custom.Rows[0][1].Raw(), unmarshalled.Rows[0][1].Raw()) {
t.Errorf("want %s, got %s", custom.Rows[0][0].Raw(), unmarshalled.Rows[0][0].Raw())
}
extra, err := bson.Marshal(&extraQueryResult{})
if err != nil {
t.Error(err)
}
err = bson.Unmarshal(extra, &unmarshalled)
if err != nil {
t.Error(err)
}
}
func TestFractionals(t *testing.T) {
client := framework.NewClient()
defer client.Execute("delete from vitess_fracts", nil)
_, err := client.Execute(
"insert into vitess_fracts values(:id, :deci, :num, :f, :d)",
map[string]interface{}{
"id": 1,
"deci": "1.99",
"num": "2.99",
"f": 3.99,
"d": 4.99,
},
)
if err != nil {
t.Error(err)
return
}
qr, err := client.Execute("select * from vitess_fracts where id = 1", nil)
if err != nil {
t.Error(err)
return
}
want := sqltypes.Result{
Fields: []*querypb.Field{
{
Name: "id",
Type: sqltypes.Int32,
}, {
Name: "deci",
Type: sqltypes.Decimal,
}, {
Name: "num",
Type: sqltypes.Decimal,
}, {
Name: "f",
Type: sqltypes.Float32,
}, {
Name: "d",
Type: sqltypes.Float64,
},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeFractional([]byte("1.99")),
sqltypes.MakeFractional([]byte("2.99")),
sqltypes.MakeFractional([]byte("3.99")),
sqltypes.MakeFractional([]byte("4.99")),
},
},
}
if !reflect.DeepEqual(*qr, want) {
t.Errorf("Execute: \n%#v, want \n%#v", *qr, want)
}
}
func TestMiscTypes(t *testing.T) {
client := framework.NewClient()
defer client.Execute("delete from vitess_misc", nil)
_, err := client.Execute(
"insert into vitess_misc values(:id, :b, :d, :dt, :t)",
map[string]interface{}{
"id": 1,
"b": "\x01",
"d": "2012-01-01",
"dt": "2012-01-01 15:45:45",
"t": "15:45:45",
},
)
if err != nil {
t.Error(err)
return
}
qr, err := client.Execute("select * from vitess_misc where id = 1", nil)
if err != nil {
t.Error(err)
return
}
want := sqltypes.Result{
Fields: []*querypb.Field{
{
Name: "id",
Type: sqltypes.Int32,
}, {
Name: "b",
Type: sqltypes.Bit,
}, {
Name: "d",
Type: sqltypes.Date,
}, {
Name: "dt",
Type: sqltypes.Datetime,
}, {
Name: "t",
Type: sqltypes.Time,
},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeString([]byte("\x01")),
sqltypes.MakeString([]byte("2012-01-01")),
sqltypes.MakeString([]byte("2012-01-01 15:45:45")),
sqltypes.MakeString([]byte("15:45:45")),
},
},
}
if !reflect.DeepEqual(*qr, want) {
t.Errorf("Execute: \n%#v, want \n%#v", *qr, want)
}
}
func TestQueryExecutorPlanPKIn(t *testing.T) {
db := setUpQueryExecutorTest()
query := "select * from test_table where pk in (1, 2, 3) limit 1000"
expandedQuery := "select pk, name, addr from test_table where pk in (1, 2, 3)"
want := &mproto.QueryResult{
Fields: getTestTableFields(),
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("20")),
sqltypes.MakeNumeric([]byte("30")),
},
},
}
db.AddQuery(query, want)
db.AddQuery(expandedQuery, want)
db.AddQuery("select * from test_table where 1 != 1", &mproto.QueryResult{
Fields: getTestTableFields(),
})
ctx := context.Background()
sqlQuery := newTestSQLQuery(ctx, enableRowCache|enableSchemaOverrides|enableStrict)
qre := newTestQueryExecutor(ctx, sqlQuery, query, 0)
defer sqlQuery.disallowQueries()
checkPlanID(t, planbuilder.PLAN_PK_IN, qre.plan.PlanId)
got, err := qre.Execute()
if err != nil {
t.Fatalf("qre.Execute() = %v, want nil", err)
}
if !reflect.DeepEqual(got, want) {
t.Fatalf("got: %v, want: %v", got, want)
}
cachedQuery := "select pk, name, addr from test_table where pk in (1)"
db.AddQuery(cachedQuery, &mproto.QueryResult{
Fields: getTestTableFields(),
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("20")),
sqltypes.MakeNumeric([]byte("30")),
},
},
})
nonCachedQuery := "select pk, name, addr from test_table where pk in (2, 3)"
db.AddQuery(nonCachedQuery, &mproto.QueryResult{})
db.AddQuery(cachedQuery, want)
// run again, this time pk=1 should hit the rowcache
got, err = qre.Execute()
if err != nil {
t.Fatalf("qre.Execute() = %v, want nil", err)
}
if !reflect.DeepEqual(got, want) {
t.Fatalf("got: %v, want: %v", got, want)
}
}
func BuildValue(bytes []byte, fieldType uint32) sqltypes.Value {
switch fieldType {
case C.MYSQL_TYPE_DECIMAL, C.MYSQL_TYPE_FLOAT, C.MYSQL_TYPE_DOUBLE, C.MYSQL_TYPE_NEWDECIMAL:
return sqltypes.MakeFractional(bytes)
case C.MYSQL_TYPE_TIMESTAMP:
return sqltypes.MakeString(bytes)
}
// The below condition represents the following list of values:
// C.MYSQL_TYPE_TINY, C.MYSQL_TYPE_SHORT, C.MYSQL_TYPE_LONG, C.MYSQL_TYPE_LONGLONG, C.MYSQL_TYPE_INT24, C.MYSQL_TYPE_YEAR:
if fieldType <= C.MYSQL_TYPE_INT24 || fieldType == C.MYSQL_TYPE_YEAR {
return sqltypes.MakeNumeric(bytes)
}
return sqltypes.MakeString(bytes)
}
// BuildValue returns a sqltypes.Value from the passed in fields
func BuildValue(bytes []byte, fieldType uint32) sqltypes.Value {
if bytes == nil {
return sqltypes.NULL
}
switch fieldType {
case typeDecimal, TypeFloat, TypeDouble, TypeNewDecimal:
return sqltypes.MakeFractional(bytes)
case TypeTimestamp:
return sqltypes.MakeString(bytes)
}
// The below condition represents the following list of values:
// TypeTiny, TypeShort, TypeLong, TypeLonglong, TypeInt24, TypeYear:
if fieldType <= TypeInt24 || fieldType == TypeYear {
return sqltypes.MakeNumeric(bytes)
}
return sqltypes.MakeString(bytes)
}
func createTableInfo(
name string, colNames []string, colTypes []query.Type, pKeys []string) TableInfo {
table := schema.NewTable(name)
for i, colName := range colNames {
colType := colTypes[i]
defaultVal := sqltypes.Value{}
if sqltypes.IsIntegral(colType) {
defaultVal = sqltypes.MakeNumeric([]byte("0"))
} else if colType == sqltypes.VarBinary {
defaultVal = sqltypes.MakeString([]byte(""))
}
table.AddColumn(colName, colType, defaultVal, "")
}
tableInfo := TableInfo{Table: table}
tableInfo.SetPK(pKeys)
return tableInfo
}
func createTableInfo(
name string, colNames []string, colTypes []string, pKeys []string) TableInfo {
table := schema.NewTable(name)
for i, colName := range colNames {
colType := colTypes[i]
defaultVal := sqltypes.Value{}
if strings.Contains(colType, "int") {
defaultVal = sqltypes.MakeNumeric([]byte("0"))
} else if strings.HasPrefix(colType, "varbinary") {
defaultVal = sqltypes.MakeString([]byte(""))
}
table.AddColumn(colName, colType, defaultVal, "")
}
tableInfo := TableInfo{Table: table}
tableInfo.SetPK(pKeys)
return tableInfo
}
// AddColumn adds a column to the Table.
func (ta *Table) AddColumn(name string, columnType querypb.Type, defval sqltypes.Value, extra string) {
index := len(ta.Columns)
ta.Columns = append(ta.Columns, TableColumn{Name: name})
ta.Columns[index].Type = columnType
if extra == "auto_increment" {
ta.Columns[index].IsAuto = true
// Ignore default value, if any
return
}
if defval.IsNull() {
return
}
if sqltypes.IsIntegral(ta.Columns[index].Type) {
ta.Columns[index].Default = sqltypes.MakeNumeric(defval.Raw())
} else {
ta.Columns[index].Default = sqltypes.MakeString(defval.Raw())
}
}
func TestQueryExecutorPlanPKIn(t *testing.T) {
db := setUpQueryExecutorTest()
testUtils := &testUtils{}
query := "select * from test_table where pk in (1, 2, 3) limit 1000"
expandedQuery := "select pk, name, addr from test_table where pk in (1, 2, 3)"
expected := &mproto.QueryResult{
Fields: getTestTableFields(),
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("20")),
sqltypes.MakeNumeric([]byte("30")),
},
},
}
db.AddQuery(query, expected)
db.AddQuery(expandedQuery, expected)
db.AddQuery("select * from test_table where 1 != 1", &mproto.QueryResult{
Fields: getTestTableFields(),
})
qre, sqlQuery := newTestQueryExecutor(
query, context.Background(), enableRowCache|enableStrict|enableSchemaOverrides)
defer sqlQuery.disallowQueries()
checkPlanID(t, planbuilder.PLAN_PK_IN, qre.plan.PlanId)
testUtils.checkEqual(t, expected, qre.Execute())
cachedQuery := "select pk, name, addr from test_table where pk in (1)"
db.AddQuery(cachedQuery, &mproto.QueryResult{
Fields: getTestTableFields(),
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeNumeric([]byte("20")),
sqltypes.MakeNumeric([]byte("30")),
},
},
})
nonCachedQuery := "select pk, name, addr from test_table where pk in (2, 3)"
db.AddQuery(nonCachedQuery, &mproto.QueryResult{})
db.AddQuery(cachedQuery, expected)
// run again, this time pk=1 should hit the rowcache
testUtils.checkEqual(t, expected, qre.Execute())
}
func TestStarParam(t *testing.T) {
buf := NewTrackedBuffer(nil)
buf.Fprintf("select * from a where id in (%a)", "*")
pq := buf.ParsedQuery()
listvars := []sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeString([]byte("aa")),
}
bytes, err := pq.GenerateQuery(nil, listvars)
if err != nil {
t.Errorf("generate failed: %v", err)
return
}
got := string(bytes)
want := "select * from a where id in (1, 'aa')"
if got != want {
t.Errorf("got %s, want %s", got, want)
}
}
func TestCharaterSet(t *testing.T) {
qr, err := framework.NewClient().Execute("select * from vitess_test where intval=1", nil)
if err != nil {
t.Error(err)
return
}
want := mproto.QueryResult{
Fields: []mproto.Field{
{
Name: "intval",
Type: 3,
Flags: 0,
}, {
Name: "floatval",
Type: 4,
Flags: 0,
}, {
Name: "charval",
Type: 253,
Flags: 0,
}, {
Name: "binval",
Type: 253,
Flags: mysql.FlagBinary,
},
},
RowsAffected: 1,
Rows: [][]sqltypes.Value{
[]sqltypes.Value{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeFractional([]byte("1.12345")),
sqltypes.MakeString([]byte("\xc2\xa2")),
sqltypes.MakeString([]byte("\x00\xff")),
},
},
}
if !reflect.DeepEqual(*qr, want) {
t.Errorf("Execute: \n%#v, want \n%#v", *qr, want)
}
}
func (rc *RowCache) decodeRow(b []byte) (row []sqltypes.Value) {
rowlen := pack.Uint32(b)
data := b[4+rowlen*4:]
row = make([]sqltypes.Value, rowlen)
for i := range row {
length := pack.Uint32(b[4+i*4:])
if length == 0xFFFFFFFF {
continue
}
if length > uint32(len(data)) {
// Corrupt data
return nil
}
if rc.tableInfo.Columns[i].Category == schema.CAT_NUMBER {
row[i] = sqltypes.MakeNumeric(data[:length])
} else {
row[i] = sqltypes.MakeString(data[:length])
}
data = data[length:]
}
return row
}
func (ta *Table) AddColumn(name string, columnType string, defval sqltypes.Value, extra string) {
index := len(ta.Columns)
ta.Columns = append(ta.Columns, TableColumn{Name: name})
if strings.Contains(columnType, "int") {
ta.Columns[index].Category = CAT_NUMBER
} else if strings.HasPrefix(columnType, "varbinary") {
ta.Columns[index].Category = CAT_VARBINARY
} else {
ta.Columns[index].Category = CAT_OTHER
}
if extra == "auto_increment" {
ta.Columns[index].IsAuto = true
// Ignore default value, if any
return
}
if defval.IsNull() {
return
}
if ta.Columns[index].Category == CAT_NUMBER {
ta.Columns[index].Default = sqltypes.MakeNumeric(defval.Raw())
} else {
ta.Columns[index].Default = sqltypes.MakeString(defval.Raw())
}
}
func TestDeleteEqual(t *testing.T) {
router, sbc, _, sbclookup := createRouterEnv()
sbc.setResults([]*sqltypes.Result{&sqltypes.Result{
Fields: []*querypb.Field{
{"id", sqltypes.Int32},
{"name", sqltypes.VarChar},
},
RowsAffected: 1,
InsertID: 0,
Rows: [][]sqltypes.Value{{
sqltypes.MakeNumeric([]byte("1")),
sqltypes.MakeString([]byte("myname")),
}},
}})
_, err := routerExec(router, "delete from user where id = 1", nil)
if err != nil {
t.Error(err)
}
wantQueries := []tproto.BoundQuery{{
Sql: "select id, name from user where id = 1 for update",
BindVariables: map[string]interface{}{},
}, {
Sql: "delete from user where id = 1 /* vtgate:: keyspace_id:166b40b44aba4bd6 */",
BindVariables: map[string]interface{}{
"keyspace_id": "\x16k@\xb4J\xbaK\xd6",
},
}}
if !reflect.DeepEqual(sbc.Queries, wantQueries) {
t.Errorf("sbc.Queries: %+v, want %+v\n", sbc.Queries, wantQueries)
}
wantQueries = []tproto.BoundQuery{{
Sql: "delete from user_idx where id in ::id",
BindVariables: map[string]interface{}{
"id": []interface{}{int64(1)},
},
}, {
Sql: "delete from name_user_map where name in ::name and user_id = :user_id",
BindVariables: map[string]interface{}{
"user_id": int64(1),
"name": []interface{}{"myname"},
},
}}
if !reflect.DeepEqual(sbclookup.Queries, wantQueries) {
t.Errorf("sbclookup.Queries: %+v, want %+v\n", sbclookup.Queries, wantQueries)
}
sbc.Queries = nil
sbclookup.Queries = nil
sbc.setResults([]*sqltypes.Result{&sqltypes.Result{}})
_, err = routerExec(router, "delete from user where id = 1", nil)
if err != nil {
t.Error(err)
}
wantQueries = []tproto.BoundQuery{{
Sql: "select id, name from user where id = 1 for update",
BindVariables: map[string]interface{}{},
}, {
Sql: "delete from user where id = 1 /* vtgate:: keyspace_id:166b40b44aba4bd6 */",
BindVariables: map[string]interface{}{
"keyspace_id": "\x16k@\xb4J\xbaK\xd6",
},
}}
if !reflect.DeepEqual(sbc.Queries, wantQueries) {
t.Errorf("sbc.Queries: %+v, want %+v\n", sbc.Queries, wantQueries)
}
if sbclookup.Queries != nil {
t.Errorf("sbclookup.Queries: %+v, want nil\n", sbclookup.Queries)
}
sbc.Queries = nil
sbclookup.Queries = nil
sbclookup.setResults([]*sqltypes.Result{&sqltypes.Result{}})
_, err = routerExec(router, "delete from music where id = 1", nil)
if err != nil {
t.Error(err)
}
wantQueries = []tproto.BoundQuery{{
Sql: "select user_id from music_user_map where music_id = :music_id",
BindVariables: map[string]interface{}{
"music_id": int64(1),
},
}}
if !reflect.DeepEqual(sbclookup.Queries, wantQueries) {
t.Errorf("sbclookup.Queries: %+v, want %+v\n", sbclookup.Queries, wantQueries)
}
if sbc.Queries != nil {
t.Errorf("sbc.Queries: %+v, want nil\n", sbc.Queries)
}
}
