func TestStatements(t *testing.T) {
testutil.TestSelect(t, `select 1;`,
[][]driver.Value{{int64(1)}},
)
// - yy func evaluates
// - projection (user_id, email)
testutil.TestSelect(t, `select user_id, email FROM users WHERE yy(reg_date) > 10;`,
[][]driver.Value{{"9Ip1aKbeZe2njCDM", "*****@*****.**"}},
)
// - ensure we can evaluate against "NULL"
// - extra paren in where
// - `db`.`col` syntax
testutil.TestSelect(t, "SELECT user_id FROM users WHERE (`users.user_id` != NULL)",
[][]driver.Value{{"hT2impsabc345c"}, {"9Ip1aKbeZe2njCDM"}, {"hT2impsOPUREcVPc"}},
)
testutil.TestSelect(t, "SELECT email FROM users WHERE interests != NULL)",
[][]driver.Value{{"*****@*****.**"}, {"*****@*****.**"}},
)
return
// TODO:
testutil.TestSelect(t, "SELECT COUNT(*) AS count FROM users WHERE (`users.user_id` != NULL)",
[][]driver.Value{{3}},
)
// requires aggregations, note also lack of group-by
testutil.TestSelect(t, "SELECT AVG(CHAR_LENGTH(CAST(`user`.`email` AS CHAR))) AS `len` FROM `users`",
[][]driver.Value{{14.5}},
)
}
func TestSchemaShowStatements(t *testing.T) {
// TODO: this test needs the "databases" ie system-schema not current-info-schema
testutil.TestSelect(t, `show databases;`,
[][]driver.Value{{"mockcsv"}},
)
// - rewrite show tables -> "use schema; select name from schema.tables;"
testutil.TestSelect(t, `show tables;`,
[][]driver.Value{{"orders"}, {"users"}},
)
testutil.TestSelect(t, `show tables from mockcsv;`,
[][]driver.Value{{"orders"}, {"users"}},
)
testutil.TestSelect(t, `show tables in mockcsv;`,
[][]driver.Value{{"orders"}, {"users"}},
)
// TODO: we need to detect other schemas? and error on non-existent schemas?
//testutil.TestSelectErr(t, `show tables from non_existent;`, nil)
// show table create
createStmt := "CREATE TABLE `users` (\n" +
" `user_id` varchar(255) DEFAULT NULL,\n" +
" `email` varchar(255) DEFAULT NULL,\n" +
" `interests` varchar(255) DEFAULT NULL,\n" +
" `reg_date` datetime DEFAULT NULL,\n" +
" `referral_count` bigint DEFAULT NULL\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
testutil.TestSelect(t, `show create table users;`,
[][]driver.Value{{"users", createStmt}},
)
// - rewrite show tables -> "use schema; select Table, Table_Type from schema.tables;"
testutil.TestSelect(t, `show full tables;`,
[][]driver.Value{{"orders", "BASE TABLE"}, {"users", "BASE TABLE"}},
)
testutil.TestSelect(t, `show tables like "us%";`,
[][]driver.Value{{"users"}},
)
testutil.TestSelect(t, `show full tables like "us%";`,
[][]driver.Value{{"users", "BASE TABLE"}},
)
testutil.TestSelect(t, `show full tables from mockcsv like "us%";`,
[][]driver.Value{{"users", "BASE TABLE"}},
)
// SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
testutil.TestSelect(t, `show columns from users;`,
[][]driver.Value{
{"user_id", "string", "", "", "", ""},
{"email", "string", "", "", "", ""},
{"interests", "string", "", "", "", ""},
{"reg_date", "time", "", "", "", ""},
{"referral_count", "int", "", "", "", ""},
},
)
testutil.TestSelect(t, `show columns FROM users FROM mockcsv;`,
[][]driver.Value{
{"user_id", "string", "", "", "", ""},
{"email", "string", "", "", "", ""},
{"interests", "string", "", "", "", ""},
{"reg_date", "time", "", "", "", ""},
{"referral_count", "int", "", "", "", ""},
},
)
testutil.TestSelect(t, `show columns from users WHERE Field Like "email";`,
[][]driver.Value{
{"email", "string", "", "", "", ""},
},
)
testutil.TestSelect(t, `show full columns from users WHERE Field Like "email";`,
[][]driver.Value{
{"email", "string", "", "", "", "", "", "", ""},
},
)
testutil.TestSelect(t, `show columns from users Like "user%";`,
[][]driver.Value{
{"user_id", "string", "", "", "", ""},
},
)
// VARIABLES
testutil.TestSelect(t, `show global variables like 'max_allowed*';`,
[][]driver.Value{
{"max_allowed_packet", int64(datasource.MaxAllowedPacket)},
},
)
// DESCRIBE
testutil.TestSelect(t, `describe users;`,
[][]driver.Value{
{"user_id", "string", "", "", "", ""},
{"email", "string", "", "", "", ""},
{"interests", "string", "", "", "", ""},
{"reg_date", "time", "", "", "", ""},
{"referral_count", "int", "", "", "", ""},
},
)
}
