func (s *testValidatorSuite) TestValidator(c *C) {
cases := []struct {
sql string
inPrepare bool
err error
}{
{"select ?", false, parser.ErrSyntax},
{"select ?", true, nil},
{"create table t(id int not null auto_increment default 2, key (id))", true,
errors.New("Invalid default value for 'id'")},
{"create table t(id int not null default 2 auto_increment, key (id))", true,
errors.New("Invalid default value for 'id'")},
{"create table t(id int not null auto_increment)", true,
errors.New("Incorrect table definition; there can be only one auto column and it must be defined as a key")},
{"create table t(id int not null auto_increment, c int auto_increment, key (id, c))", true,
errors.New("Incorrect table definition; there can be only one auto column and it must be defined as a key")},
{"create table t(id int not null auto_increment, c int, key (c, id))", true,
errors.New("Incorrect table definition; there can be only one auto column and it must be defined as a key")},
{"create table t(id decimal auto_increment, key (id))", true,
errors.New("Incorrect column specifier for column 'id'")},
{"create table t(id float auto_increment, key (id))", true, nil},
}
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
se, err := tidb.CreateSession(store)
c.Assert(err, IsNil)
for _, ca := range cases {
stmts, err1 := tidb.Parse(se.(context.Context), ca.sql)
c.Assert(err1, IsNil)
c.Assert(stmts, HasLen, 1)
stmt := stmts[0]
err = optimizer.Validate(stmt, ca.inPrepare)
c.Assert(terror.ErrorEqual(err, ca.err), IsTrue)
}
}
func (ts *testTypeInferrerSuite) TestInterType(c *C) {
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
defer store.Close()
testKit := testkit.NewTestKit(c, store)
testKit.MustExec("use test")
testKit.MustExec("create table t (c1 int, c2 double, c3 text)")
cases := []struct {
expr string
tp byte
}{
{"c1", mysql.TypeLong},
{"+1", mysql.TypeLonglong},
{"-1", mysql.TypeLonglong},
{"-'1'", mysql.TypeDouble},
{"~1", mysql.TypeLonglong},
{"!true", mysql.TypeLonglong},
{"c1 is true", mysql.TypeLonglong},
{"c2 is null", mysql.TypeLonglong},
{"cast(1 as decimal)", mysql.TypeNewDecimal},
{"1 and 1", mysql.TypeLonglong},
{"1 or 1", mysql.TypeLonglong},
{"1 xor 1", mysql.TypeLonglong},
{"'1' & 2", mysql.TypeLonglong},
{"'1' | 2", mysql.TypeLonglong},
{"'1' ^ 2", mysql.TypeLonglong},
{"'1' << 1", mysql.TypeLonglong},
{"'1' >> 1", mysql.TypeLonglong},
{"1 + '1'", mysql.TypeDouble},
{"1 + 1.1", mysql.TypeNewDecimal},
{"1 div 2", mysql.TypeLonglong},
{"1 > any (select 1)", mysql.TypeLonglong},
{"exists (select 1)", mysql.TypeLonglong},
{"1 in (2, 3)", mysql.TypeLonglong},
{"'abc' like 'abc'", mysql.TypeLonglong},
{"'abc' rlike 'abc'", mysql.TypeLonglong},
{"(1+1)", mysql.TypeLonglong},
}
for _, ca := range cases {
ctx := testKit.Se.(context.Context)
stmts, err := tidb.Parse(ctx, "select "+ca.expr+" from t")
c.Assert(err, IsNil)
c.Assert(stmts, HasLen, 1)
stmt := stmts[0].(*ast.SelectStmt)
is := sessionctx.GetDomain(ctx).InfoSchema()
err = optimizer.ResolveName(stmt, is, ctx)
c.Assert(err, IsNil)
optimizer.InferType(stmt)
tp := stmt.GetResultFields()[0].Column.Tp
c.Assert(tp, Equals, ca.tp, Commentf("for %s", ca.expr))
}
}
func (s *testValidatorSuite) TestValidator(c *C) {
defer testleak.AfterTest(c)()
cases := []struct {
sql string
inPrepare bool
err error
}{
{"select ?", false, parser.ErrSyntax},
{"select ?", true, nil},
{"create table t(id int not null auto_increment default 2, key (id))", true,
errors.New("Invalid default value for 'id'")},
{"create table t(id int not null default 2 auto_increment, key (id))", true,
errors.New("Invalid default value for 'id'")},
{"create table t(id int not null auto_increment)", true,
errors.New("Incorrect table definition; there can be only one auto column and it must be defined as a key")},
{"create table t(id int not null auto_increment, c int auto_increment, key (id, c))", true,
errors.New("Incorrect table definition; there can be only one auto column and it must be defined as a key")},
{"create table t(id int not null auto_increment, c int, key (c, id))", true,
errors.New("Incorrect table definition; there can be only one auto column and it must be defined as a key")},
{"create table t(id decimal auto_increment, key (id))", true,
errors.New("Incorrect column specifier for column 'id'")},
{"create table t(id float auto_increment, key (id))", true, nil},
{"create table t(id int auto_increment) ENGINE=MYISAM", true, nil},
{"create table t(a int primary key, b int, c varchar(10), d char(256));", true, errors.New("Column length too big for column 'd' (max = 255); use BLOB or TEXT instead")},
{"create index ib on t(b,a,b);", true, errors.New("Duplicate column name 'b'")},
{"create table t(c1 int not null primary key, c2 int not null primary key)", true, errors.New("Multiple primary key defined")},
}
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
defer store.Close()
se, err := tidb.CreateSession(store)
c.Assert(err, IsNil)
for _, ca := range cases {
stmts, err1 := tidb.Parse(se.(context.Context), ca.sql)
c.Assert(err1, IsNil)
c.Assert(stmts, HasLen, 1)
stmt := stmts[0]
err = plan.Validate(stmt, ca.inPrepare)
c.Assert(terror.ErrorEqual(err, ca.err), IsTrue)
}
}
func (s *testValidatorSuite) TestValidator(c *C) {
cases := []struct {
sql string
inPrepare bool
err error
}{
{"select ?", false, parser.ErrSyntax},
{"select ?", true, nil},
}
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
se, err := tidb.CreateSession(store)
c.Assert(err, IsNil)
for _, ca := range cases {
stmts, err1 := tidb.Parse(se.(context.Context), ca.sql)
c.Assert(err1, IsNil)
c.Assert(stmts, HasLen, 1)
stmt := stmts[0]
err = optimizer.Validate(stmt, ca.inPrepare)
c.Assert(terror.ErrorEqual(err, ca.err), IsTrue)
}
}
func (ts *testTypeInferrerSuite) TestInferType(c *C) {
store, err := tidb.NewStore(tidb.EngineGoLevelDBMemory)
c.Assert(err, IsNil)
defer store.Close()
testKit := testkit.NewTestKit(c, store)
testKit.MustExec("use test")
testKit.MustExec("create table t (c1 int, c2 double, c3 text)")
cases := []struct {
expr string
tp byte
chs string
}{
{"c1", mysql.TypeLong, charset.CharsetBin},
{"+1", mysql.TypeLonglong, charset.CharsetBin},
{"-1", mysql.TypeLonglong, charset.CharsetBin},
{"-'1'", mysql.TypeDouble, charset.CharsetBin},
{"~1", mysql.TypeLonglong, charset.CharsetBin},
{"!true", mysql.TypeLonglong, charset.CharsetBin},
{"c1 is true", mysql.TypeLonglong, charset.CharsetBin},
{"c2 is null", mysql.TypeLonglong, charset.CharsetBin},
{"isnull(1/0)", mysql.TypeLonglong, charset.CharsetBin},
{"cast(1 as decimal)", mysql.TypeNewDecimal, charset.CharsetBin},
{"1 and 1", mysql.TypeLonglong, charset.CharsetBin},
{"1 or 1", mysql.TypeLonglong, charset.CharsetBin},
{"1 xor 1", mysql.TypeLonglong, charset.CharsetBin},
{"'1' & 2", mysql.TypeLonglong, charset.CharsetBin},
{"'1' | 2", mysql.TypeLonglong, charset.CharsetBin},
{"'1' ^ 2", mysql.TypeLonglong, charset.CharsetBin},
{"'1' << 1", mysql.TypeLonglong, charset.CharsetBin},
{"'1' >> 1", mysql.TypeLonglong, charset.CharsetBin},
{"1 + '1'", mysql.TypeDouble, charset.CharsetBin},
{"1 + 1.1", mysql.TypeNewDecimal, charset.CharsetBin},
{"1 div 2", mysql.TypeLonglong, charset.CharsetBin},
{"1 / 2", mysql.TypeNewDecimal, charset.CharsetBin},
{"1 > any (select 1)", mysql.TypeLonglong, charset.CharsetBin},
{"exists (select 1)", mysql.TypeLonglong, charset.CharsetBin},
{"1 in (2, 3)", mysql.TypeLonglong, charset.CharsetBin},
{"'abc' like 'abc'", mysql.TypeLonglong, charset.CharsetBin},
{"'abc' rlike 'abc'", mysql.TypeLonglong, charset.CharsetBin},
{"(1+1)", mysql.TypeLonglong, charset.CharsetBin},
// Functions
{"version()", mysql.TypeVarString, "utf8"},
{"count(c1)", mysql.TypeLonglong, charset.CharsetBin},
{"abs(1)", mysql.TypeLonglong, charset.CharsetBin},
{"abs(1.1)", mysql.TypeNewDecimal, charset.CharsetBin},
{"abs(cast(\"20150817015609\" as DATETIME))", mysql.TypeDouble, charset.CharsetBin},
{"IF(1>2,2,3)", mysql.TypeLonglong, charset.CharsetBin},
{"IFNULL(1,0)", mysql.TypeLonglong, charset.CharsetBin},
{"POW(2,2)", mysql.TypeDouble, charset.CharsetBin},
{"POWER(2,2)", mysql.TypeDouble, charset.CharsetBin},
{"rand()", mysql.TypeDouble, charset.CharsetBin},
{"curdate()", mysql.TypeDate, charset.CharsetBin},
{"current_date()", mysql.TypeDate, charset.CharsetBin},
{"DATE('2003-12-31 01:02:03')", mysql.TypeDate, charset.CharsetBin},
{"curtime()", mysql.TypeDuration, charset.CharsetBin},
{"current_time()", mysql.TypeDuration, charset.CharsetBin},
{"curtime()", mysql.TypeDuration, charset.CharsetBin},
{"current_timestamp()", mysql.TypeDatetime, charset.CharsetBin},
{"microsecond('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"second('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"minute('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"hour('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"day('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"week('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"month('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"year('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"dayofweek('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"dayofmonth('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"dayofyear('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"weekday('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"weekofyear('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"yearweek('2009-12-31 23:59:59.000010')", mysql.TypeLonglong, charset.CharsetBin},
{"found_rows()", mysql.TypeLonglong, charset.CharsetBin},
{"length('tidb')", mysql.TypeLonglong, charset.CharsetBin},
{"now()", mysql.TypeDatetime, charset.CharsetBin},
{"sysdate()", mysql.TypeDatetime, charset.CharsetBin},
{"dayname('2007-02-03')", mysql.TypeVarString, "utf8"},
{"version()", mysql.TypeVarString, "utf8"},
{"database()", mysql.TypeVarString, "utf8"},
{"user()", mysql.TypeVarString, "utf8"},
{"current_user()", mysql.TypeVarString, "utf8"},
{"CONCAT('T', 'i', 'DB')", mysql.TypeVarString, "utf8"},
{"CONCAT_WS('-', 'T', 'i', 'DB')", mysql.TypeVarString, "utf8"},
{"left('TiDB', 2)", mysql.TypeVarString, "utf8"},
{"lower('TiDB')", mysql.TypeVarString, "utf8"},
{"lcase('TiDB')", mysql.TypeVarString, "utf8"},
{"repeat('TiDB', 3)", mysql.TypeVarString, "utf8"},
{"replace('TiDB', 'D', 'd')", mysql.TypeVarString, "utf8"},
{"upper('TiDB')", mysql.TypeVarString, "utf8"},
{"ucase('TiDB')", mysql.TypeVarString, "utf8"},
{"trim(' TiDB ')", mysql.TypeVarString, "utf8"},
{"ltrim(' TiDB')", mysql.TypeVarString, "utf8"},
{"rtrim('TiDB ')", mysql.TypeVarString, "utf8"},
{"connection_id()", mysql.TypeLonglong, charset.CharsetBin},
{"if(1>2, 2, 3)", mysql.TypeLonglong, charset.CharsetBin},
{"case c1 when null then 2 when 2 then 1.1 else 1 END", mysql.TypeNewDecimal, charset.CharsetBin},
{"case c1 when null then 2 when 2 then 'tidb' else 1.1 END", mysql.TypeVarchar, "utf8"},
{"greatest(1, 2, 3)", mysql.TypeLonglong, charset.CharsetBin},
{"greatest('TiDB', 'D', 'd')", mysql.TypeVarString, "utf8"},
{"greatest(1.1, 2.2)", mysql.TypeNewDecimal, charset.CharsetBin},
{"greatest('TiDB', 3)", mysql.TypeVarString, "utf8"},
{"hex('TiDB')", mysql.TypeVarString, "utf8"},
{"hex(12)", mysql.TypeVarString, "utf8"},
}
for _, ca := range cases {
ctx := testKit.Se.(context.Context)
stmts, err := tidb.Parse(ctx, "select "+ca.expr+" from t")
c.Assert(err, IsNil)
c.Assert(stmts, HasLen, 1)
stmt := stmts[0].(*ast.SelectStmt)
is := sessionctx.GetDomain(ctx).InfoSchema()
err = plan.ResolveName(stmt, is, ctx)
c.Assert(err, IsNil)
plan.InferType(stmt)
tp := stmt.GetResultFields()[0].Column.Tp
chs := stmt.GetResultFields()[0].Column.Charset
c.Assert(tp, Equals, ca.tp, Commentf("Tp for %s", ca.expr))
c.Assert(chs, Equals, ca.chs, Commentf("Charset for %s", ca.expr))
}
}
