stonedb
stonedb copied to clipboard
Published
20 hours ago •
stoneatom
stoneatom
feature(StoneDB 8.0): Do the basic tests.
Is your feature request related to a problem? Please describe.
Adding some basic tests for StoneDB 8.0, but in StoneDB 5.7 these tests failed.
Describe the solution you'd like
find error and fix it
Describe alternatives you've considered
Additional context
failed：× ； pass：✓
1. rollback test [×]
case：
create table psn(id int primary key, name varchar(10)) engine=TIANMU;
insert into psn values(1,'zhangsan');
insert into psn values(2,'lisi');
insert into psn values(3,'wangwu');
begin;
update psn set name ='zsjjtest' where id = 1;
rollback;
select * from psn;
result：
mysql> create table psn(id int primary key, name varchar(10)) engine=TIANMU;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into psn values(1,'zhangsan');
insert into psn values(2,'lisi');
insert into psn values(3,'wangwu');Query OK, 1 row affected (0.00 sec)
mysql> insert into psn values(2,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into psn values(3,'wangwu');
Query OK, 1 row affected (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update psn set name ='zsjjtest' where id = 1;
mysql> show variables like "autocommit"
-> ;
No connection. Trying to reconnect...
Connection id: 8
Current database: select_tpch
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.02 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "delayed"
-> ;
Empty set (0.01 sec)
mysql> show variables like "%delayed%";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| tianmu_insert_delayed | ON |
+----------------------------+-------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update psn set name ='zsjjtest' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from psn;
+----+----------+
| id | name |
+----+----------+
| 1 | zsjjtest |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
2. autocommit test [×]
case:
set autocommit = 0;
set session transaction isolation level read committed;
update psn set name ='jjj' where id = 2;
select * from psn;
commit;
select * from psn;
result:
mysql> select * from psn;
+----+----------+
| id | name |
+----+----------+
| 1 | zsjjtest |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> update psn set name ='jjj' where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from psn;
+----+----------+
| id | name |
+----+----------+
| 1 | zsjjtest |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from psn;
+----+----------+
| id | name |
+----+----------+
| 1 | zsjjtest |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
3. subquery test [✓]
case:
CREATE TABLE `teachers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=TIANMU AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT into teachers(name,age) VALUES('sjj',18),('zjj',20),('haha',30);
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
score int,
xueke VARCHAR(30),
teachers_id int,
PRIMARY KEY (`id`)
) ENGINE=TIANMU AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT into students(name,age,score,xueke,teachers_id) VALUES('dada',18,80,'数学',1),('xixi',20,90,'语文',1),('闪闪',28,100,'数学',3),('wode',20,90,'语文',5),('zjj',20,90,'语文',3),('sjj',20,90,'英语',2);
select name,age from teachers where id in(select teachers_id from students where name like '%jj');
result:
mysql> CREATE TABLE `teachers` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(255) DEFAULT NULL,
-> `age` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=TIANMU AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> INSERT into teachers(name,age) VALUES('sjj',18),('zjj',20),('haha',30);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE `students` (
`id` int -> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(255) DEFAULT NULL,
-> `age` int DEFAULT NULL,
-> score int,
-> xueke VARCHAR(30),
-> teachers_id int,
-> PRIMARY KEY (`id`)
-> ) ENGINE=TIANMU AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> INSERT into students(name,age,score,xueke,teachers_id) VALUES('dada',18,80,'数学',1),('xixi',20,90,'语文',1),('闪闪',28,100,'数学',3),('wode',20,90,'语文',5),('zjj',20,90,'语文',3),('sjj',20,90,'英语',2);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select name,age from teachers where id in(select teachers_id from students where name like '%jj');
+------+------+
| name | age |
+------+------+
| haha | 30 |
| zjj | 20 |
+------+------+
2 rows in set (0.01 sec)
mysql>
4. unique index [×]
case:
CREATE TABLE `d` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`class` varchar(255) DEFAULT NULL,
`count` int(11) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE key(count)
)ENGINE = TIANMU CHARACTER SET = utf8mb4;
INSERT INTO `d` VALUES (2, '青菜', '蔬菜', 29, '2011-07-02');
INSERT INTO `d` VALUES (1, '青菜', '蔬菜', 29, '2011-07-02');
select * from d;
result:
mysql> CREATE TABLE `d` (
-> `id` int(11) NOT NULL,
-> `name` varchar(255) DEFAULT NULL,
-> `class` varchar(255) DEFAULT NULL,
-> `count` int(11) DEFAULT NULL,
-> `date` date DEFAULT NULL,
-> PRIMARY KEY (id),
-> UNIQUE key(count)
-> )ENGINE = TIANMU CHARACTER SET = utf8mb4;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSERT INTO `d` VALUES (2, '青菜', '蔬菜', 29, '2011-07-02');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `d` VALUES (1, '青菜', '蔬菜', 29, '2011-07-02');
Query OK, 1 row affected (0.00 sec)
mysql> select * from d;
+----+--------+--------+-------+------------+
| id | name | class | count | date |
+----+--------+--------+-------+------------+
| 2 | 青菜 | 蔬菜 | 29 | 2011-07-02 |
| 1 | 青菜 | 蔬菜 | 29 | 2011-07-02 |
+----+--------+--------+-------+------------+
2 rows in set (0.00 sec)
5. primary key auto_increment [✓]
case :
CREATE TABLE study(
ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20) NOT NULL,
AGE INT(10)
)ENGINE=TIANMU;
INSERT INTO study VALUES(1, 'a', 10);
INSERT INTO study VALUES(1, 'a', 11);
select * from study;
result：
mysql> CREATE TABLE study(
-> ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
-> NAME VARCHAR(20) NOT NULL,
-> AGE INT(10)
-> )ENGINE=TIANMU;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> INSERT INTO study VALUES(1, 'a', 10);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO study VALUES(1, 'a', 11);
Query OK, 1 row affected (0.00 sec)
mysql> select * from study;
+----+------+------+
| ID | NAME | AGE |
+----+------+------+
| 1 | a | 10 |
+----+------+------+
1 row in set (0.01 sec)
6. ADD COLUMN [Ã]
case:
CREATE TABLE Students(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
Teacher_id TINYINT,
key (Teacher_id)
)engine=TIANMU;
ALTER TABLE Students ADD COLUMN school_score int(20) comment 'æ绩';
ALTER TABLE Students ADD COLUMN part VARCHAR(20) comment 'é¨é¨';
SELECT name,id from Students GROUP BY Teacher_id HAVING school_score>AVG(school_score);
result:
mysql> CREATE TABLE Students(
id INT -> id INT PRIMARY KEY auto_increment,
-> name VARCHAR(20),
-> Teacher_id TINYINT,
-> key (Teacher_id)
-> )engine=TIANMU;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE Students ADD COLUMN school_score int(20) comment 'æ绩';
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> ALTER TABLE Students ADD COLUMN part VARCHAR(20) comment 'é¨é¨';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT name,id from Students GROUP BY Teacher_id HAVING school_score>AVG(school_score)
-> ;
ERROR 1054 (42S22): Unknown column 'school_score' in 'having clause'
mysql> SELECT name,id from Students GROUP BY Teacher_id HAVING school_score>AVG(school_score);
ERROR 1054 (42S22): Unknown column 'school_score' in 'having clause'
mysql> show create table Students;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Students | CREATE TABLE `Students` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
`Teacher_id` tinyint DEFAULT NULL,
`school_score` int DEFAULT NULL COMMENT 'æ绩',
`part` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'é¨é¨',
PRIMARY KEY (`id`),
KEY `Teacher_id` (`Teacher_id`)
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
7.union all [✓]
case:
CREATE TABLE Students(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
Teacher_id TINYINT,
key (Teacher_id)
)engine=TIANMU;
iNSERT INTO Students (name,Teacher_id) VALUES ("ViewIn1",1);
CREATE TABLE Teachers(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR (20),
card_no INT,
gender boolean,
UNIQUE INDEX(card_no)
)engine=TIANMU;
INSERT INTO Teachers (name,gender,card_no) VALUES ("Lily",0,12344),("Linta",0,14214);
select id from Teachers
union all
select Teacher_id from Students;
result:
mysql> CREATE TABLE Students(
-> id INT PRIMARY KEY auto_increment,
-> name VARCHAR(20),
-> Teacher_id TINYINT,
-> key (Teacher_id)
-> )engine=TIANMU;
Query OK, 0 rows affected (0.03 sec)
mysql> iNSERT INTO Students (name,Teacher_id) VALUES ("ViewIn1",1);
Query OK, 1 row affected (0.02 sec)
mysql> CREATE TABLE Teachers(
-> id TINYINT PRIMARY KEY auto_increment,
-> name VARCHAR (20),
-> card_no INT,
-> gender boolean,
-> UNIQUE INDEX(card_no)
-> )engine=TIANMU;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO Teachers (name,gender,card_no) VALUES ("Lily",0,12344),("Linta",0,14214);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select id from Teachers
-> union all
-> select Teacher_id from Students;
+------+
| id |
+------+
| 1 |
| 2 |
| 1 |
+------+
3 rows in set (0.00 sec)
mysql>
8. inner join、right join、left join [✓]
case:
create table Student3(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10)) engine=TIANMU;
insert into Student3 values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student3 values('02' , '钱电' , '1990-12-21' , '男');
insert into Student3 values('03' , '孙风' , '1990-05-20' , '男');
create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1)) engine=TIANMU;
insert into SC values('01' , '01' , 80);
insert into SC values('02' , '01' , 70);
insert into SC values('03' , '01' , 80);
insert into SC values('04' , '03' , 90);
insert into SC values('05' , '03' , 90);
SELECT Student3.Sid from Student3 LEFT JOIN SC on SC.Sid=Student3.Sid WHERE Student3.Ssex='男';
SELECT Student3.Sid from Student3 RIGHT JOIN SC on SC.Sid=Student3.Sid WHERE Student3.Ssex='男';
SELECT Student3.Sid from Student3 INNER JOIN SC on SC.Sid=Student3.Sid WHERE Student3.Ssex='男';
result:
mysql> create table Student3(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10)) engine=TIANMU;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into Student3 values('01' , '赵雷' , '1990-01-01' , '男');
Query OK, 1 row affected (0.00 sec)
mysql> insert into Student3 values('02' , '钱电' , '1990-12-21' , '男');
Query OK, 1 row affected (0.00 sec)
mysql> insert into Student3 values('03' , '孙风' , '1990-05-20' , '男');
Query OK, 1 row affected (0.00 sec)
mysql> create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1)) engine=TIANMU;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into SC values('01' , '01' , 80);
Query OK, 1 row affected (0.01 sec)
mysql> insert into SC values('02' , '01' , 70);
Query OK, 1 row affected (0.00 sec)
mysql> insert into SC values('03' , '01' , 80);
Query OK, 1 row affected (0.00 sec)
mysql> insert into SC values('04' , '03' , 90);
Query OK, 1 row affected (0.00 sec)
mysql> insert into SC values('05' , '03' , 90);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT Student3.Sid from Student3 LEFT JOIN SC on SC.Sid=Student3.Sid WHERE Student3.Ssex='男'
-> ;
+------+
| Sid |
+------+
| 01 |
| 02 |
| 03 |
+------+
3 rows in set (0.01 sec)
mysql> SELECT Student3.Sid from Student3 RIGHT JOIN SC on SC.Sid=Student3.Sid WHERE Student3.Ssex='男';
+------+
| Sid |
+------+
| 01 |
| 02 |
| 03 |
+------+
3 rows in set (0.00 sec)
mysql> SELECT Student3.Sid from Student3 INNER JOIN SC on SC.Sid=Student3.Sid WHERE Student3.Ssex='男';
+------+
| Sid |
+------+
| 01 |
| 02 |
| 03 |
+------+
3 rows in set (0.01 sec)
mysql>
9. txt load file [✓]
case:
cxdtest.txt:
1 abc 123
2 def 456
3 ghi 789
4 jkl 101
5 mno 112
create table cxd1(id int, name varchar(10), score int(11)) ENGINE=TIANMU;
load data local infile '/cxdtest.txt' into table cxd1 fields terminated by '\t' LINES TERMINATED BY '\n';
reuslt:
mysql> create table cxd1(id int, name varchar(10), score int(11));
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> select * from cxd1;
Empty set (0.01 sec)
mysql> load data local infile '/opt/cxdtest.txt' into table cxd1 fields terminated by '\t' lines terminated by "\n";
Query OK, 5 rows affected (0.06 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from cxd1;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | abc | 123 |
| 2 | def | 456 |
| 3 | ghi | 789 |
| 4 | jkl | 101 |
| 5 | mno | 112 |
+------+------+-------+
5 rows in set (0.00 sec)
mysql>
10.csv load file [✓]
case:
10.csv:
1 abc 123
2 def 456
3 ghi 789
4 jkl 101
5 mno 112
create table cxd1(id int, name varchar(10), score int(11)) ENGINE=TIANMU;
load data local infile '/opt/10.csv' into table cxd1 fields terminated by ',' LINES TERMINATED BY '\n';
result:
mysql> create table cxd1(id int, name varchar(10), score int(11));
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> select * from cxd1;
Empty set (0.00 sec)
mysql> load data local infile '/opt/cxdtest.csv' into table cxd1 fields terminated by ',' LINES TERMINATED BY '\n';
Query OK, 5 rows affected (0.05 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from cxd1;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1 | abc | 123 |
| 2 | def | 456 |
| 3 | ghi | 789 |
| 4 | jkl | 101 |
| 5 | mno | 112 |
+------+------+-------+
5 rows in set (0.00 sec)
mysql>