Debian Codegraph @

mysql-test/suite/x/t/insert_sql_o.test

raw
1 ########### ../t/insert_sql_o.test ###########
2 ### #
3 ### This test runs aims to run INSERT operation statements with SQL #
4 ### Test covers #
5 ### - insert with different operators #
6 ### - insert multiple rows #
7 ### - insert with rollback transaction #
8 ### #
9 ########################################################################
10 #
11
12 --echo ==============================================
13 --echo SQL INSERT SCENARIOS
14 --echo ==============================================
15 --echo
16
17 ## Preamble
18 --echo ================================================================================
19 --echo PREAMBLE
20 --echo ================================================================================
21 --source include/xplugin_preamble.inc
22 create user insertsqluser@localhost identified by 'insertsqluser';
23 grant all on *.* to insertsqluser@localhost;
24
25 ## TEST STARTS HERE
26 --echo ================================================================================
27 --echo TEST START
28 --echo ================================================================================
29 --write_file $MYSQL_TMP_DIR/mysqlx-insert_sql.xpl
30 -->sql
31 DROP SCHEMA if EXISTS mysqlxplugin;
32 CREATE SCHEMA mysqlxplugin;
33 USE mysqlxplugin;
34
35 CREATE TABLE Categories (
36 CategoryID int NOT NULL AUTO_INCREMENT,
37 CategoryName varchar(100),
38 CategoryDescription varchar(200),
39 CategoryIMEI tinyint,
40 CategoryDecimal decimal(5,2),
41 PRIMARY key (CategoryID)
42 );
43
44 INSERT into Categories values (1,'Sports','Sports related category',1,235.15);
45
46 START TRANSACTION;
47 INSERT into Categories values (20,'Sports','Sports related category',1,235.15);
48 SELECT * FROM Categories;
49 ROLLBACK;
50 SELECT * FROM Categories;
51
52 INSERT into Categories values (2,'Entertaiment','Entertaiment related category',2,235.15);
53
54 INSERT INTO Categories
55 (CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
56 VALUES
57 (7, 'Videogames1','Videogames1 related category',7,235.15),
58 (8, 'Videogames2','Videogames2 related category',8,235.15),
59 (9, 'Videogames3','Videogames3 related category',9,235.15),
60 (10, 'Videogames4','Videogames4 related category',10,235.15);
61
62 ## Corner Values
63 ## Insert Quotes
64 INSERT into Categories values (3,'"Quotes"','Quote Categories',3,235.15);
65
66 ## Insert NULL values
67 INSERT into Categories values (NULL,NULL,NULL,NULL,NULL);
68
69 ## Insert Corner decimal value
70 INSERT into Categories values (6,'Kitchen','Kitchen related category',6,999.99);
71 INSERT into Categories values (12,'Kitchen','Kitchen related category',11,-999.99);
72
73 ## Insert Corner tiny int value
74 INSERT into Categories values (13,'Kitchen','Kitchen related category',127,156.99);
75 INSERT into Categories values (14,'Kitchen','Kitchen related category',-128,156.99);
76 -->endsql
77 EOF
78
79 --exec $MYSQLXTEST -u insertsqluser --password='insertsqluser' --file=$MYSQL_TMP_DIR/mysqlx-insert_sql.xpl 2>&1
80 --remove_file $MYSQL_TMP_DIR/mysqlx-insert_sql.xpl
81
82 --echo .
83 --echo #---------- START NEGATIVE TEST ----------#
84 --echo .
85
86 ## ******************************************
87 ## 2. Negative Test starts here |
88 ## |
89 ## ******************************************
90
91 --write_file $MYSQL_TMP_DIR/mysqlx-insert_sql_negative.xpl
92 -->sql
93
94 USE mysqlxplugin;
95
96 ## Negative Testing
97 ## Missing insert parameter
98 -->endsql
99 -->expecterror 1136
100 -->sql
101 INSERT into Categories values (3,'Home');
102
103 ## Repeat ID
104 -->endsql
105 -->expecterror 1062
106 -->sql
107 INSERT into Categories values (1,'Home','Home related category',1,235.15);
108
109 ## Correspondence between data and field types
110 -->endsql
111 -->expecterror 1366
112 -->sql
113 INSERT into Categories values ('5','Kitchen','Kitchen related category','TinyInt',999.00);
114 -->endsql
115 -->expecterror 1366
116 -->sql
117 INSERT into Categories values ('5','Kitchen','Kitchen related category',4,'Decimal');
118
119 ## Data types corner value plus 1
120 -->endsql
121 -->expecterror 1264
122 -->sql
123 INSERT into Categories values ('5','Kitchen','Kitchen related category',4,1000.00);
124 -->endsql
125 -->expecterror 1264
126 -->sql
127 INSERT into Categories values ('5','Kitchen','Kitchen related category',4,-1000.00);
128 -->endsql
129 -->expecterror 1264
130 -->sql
131 INSERT into Categories values ('5','Kitchen','Kitchen related category',128,999.00);
132 -->endsql
133 -->expecterror 1264
134 -->sql
135 INSERT into Categories values ('5','Kitchen','Kitchen related category',-129,999.00);
136 -->endsql
137 ## Cleanup
138 -->echo ================================================================================
139 -->echo CLEAN UP
140 -->echo ================================================================================
141 -->sql
142 DROP SCHEMA if EXISTS mysqlxplugin;
143 DROP USER insertsqluser@localhost;
144 -->endsql
145 EOF
146
147 --replace_regex /categories.*\./Categories./
148 --exec $MYSQLXTEST -u insertsqluser --password='insertsqluser' --file=$MYSQL_TMP_DIR/mysqlx-insert_sql_negative.xpl 2>&1
149
150 ## Cleanup
151 --source ../include/xplugin_cleanup.inc
152
153