*** empty log message ***
[csql.git] / test / tools / csql / exp.test094.ksh
blob7a46bc30f996abde419e4332d1c6fe3ecdb9c120
1 echo CREATE TABLE T1(f1 INT AUTO_INCREMENT,f2 INT);
2 Statement Executed
3 echo INSERT INTO T1 values(100,1);
4 Statement Executed: Rows Affected = 1
5 echo INSERT INTO T1(f2) values(10);
6 Statement Executed: Rows Affected = 1
7 echo INSERT INTO T1(f2) values(30);
8 Statement Executed: Rows Affected = 1
9 echo INSERT INTO T1(f2) values(40);
10 Statement Executed: Rows Affected = 1
11 echo INSERT INTO T1(f1,f2) values(NULL,40);
12 Statement Executed: Rows Affected = 1
13 echo INSERT INTO T1(f1,f2) values(100,40);
14 Statement execute failed with error -21
15 echo SELECT * FROM T1;
16 ---------------------------------------------------------
17 T1.f1 T1.f2
18 ---------------------------------------------------------
19 100 1
20 101 10
21 102 30
22 103 40
23 104 40
25 echo CREATE TABLE T2(f1 INT AUTO_INCREMENT,f2 CHAR(10));
26 Statement Executed
27 echo INSERT INTO T2 values(10,'Papu');
28 Statement Executed: Rows Affected = 1
29 echo INSERT INTO T2(f2) values('Jitu');
30 Statement Executed: Rows Affected = 1
31 echo INSERT INTO T2(f2) values('kishor');
32 Statement Executed: Rows Affected = 1
33 echo INSERT INTO T2(f1,f2) values(NULL,'Nihar');
34 Statement Executed: Rows Affected = 1
35 echo INSERT INTO T2(f1,f2) values(100,'Suna');
36 Statement Executed: Rows Affected = 1
37 echo SELECT * FROM T2;
38 ---------------------------------------------------------
39 T2.f1 T2.f2
40 ---------------------------------------------------------
41 10 Papu
42 11 Jitu
43 12 kishor
44 13 Nihar
45 100 Suna
47 echo CREATE TABLE T3(f1 CHAR(10),f2 int auto_increment);
48 Statement Executed
49 echo INSERT INTO T3 values('Papu',10);
50 Statement Executed: Rows Affected = 1
51 echo INSERT INTO T3(f1) values('Jitu');
52 Statement Executed: Rows Affected = 1
53 echo INSERT INTO T3(f1) values('kishor');
54 Statement Executed: Rows Affected = 1
55 echo INSERT INTO T3(f1,f2) values('Nihar',NULL);
56 Statement Executed: Rows Affected = 1
57 echo INSERT INTO T3(f2,f1) values(100,NULL);
58 Statement Executed: Rows Affected = 1
59 echo INSERT INTO T3(f1,f2) values(NULL,NULL);
60 Statement Executed: Rows Affected = 1
61 echo SELECT * FROM T3;
62 ---------------------------------------------------------
63 T3.f1 T3.f2
64 ---------------------------------------------------------
65 Papu 10
66 Jitu 11
67 kishor 12
68 Nihar 13
69 NULL 100
70 NULL 101
72 echo CREATE TABLE T4(f1 INT AUTO_INCREMENT,f2 INT);
73 Statement Executed
74 echo INSERT INTO T4 VALUES(1,2);
75 Statement Executed: Rows Affected = 1
76 echo INSERT INTO T4 VALUES(NULL,2);
77 Statement Executed: Rows Affected = 1
78 echo INSERT INTO T4 VALUES(NULL,3);
79 Statement Executed: Rows Affected = 1
80 echo INSERT INTO T4 VALUES(NULL,2);
81 Statement Executed: Rows Affected = 1
82 echo INSERT INTO T4 VALUES(NULL,3);
83 Statement Executed: Rows Affected = 1
84 echo SELECT * FROM T4;
85 ---------------------------------------------------------
86 T4.f1 T4.f2
87 ---------------------------------------------------------
88 1 2
89 2 2
90 3 3
91 4 2
92 5 3
94 echo UPDATE T4 SET f2=20 WHERE f2=2;
95 Statement Executed: Rows Affected = 3
96 echo SELECT * FROM T4;
97 ---------------------------------------------------------
98 T4.f1 T4.f2
99 ---------------------------------------------------------
100 1 20
101 2 20
102 3 3
103 4 20
104 5 3
106 echo UPDATE T4 SET f2=200,f1=0 WHERE f2=20;
107 Statement prepare failed with error -33
108 echo SELECT * FROM T4;
109 ---------------------------------------------------------
110 T4.f1 T4.f2
111 ---------------------------------------------------------
112 1 20
113 2 20
114 3 3
115 4 20
116 5 3
118 echo UPDATE T4 SET f2=300,f1=30 WHERE f1<=2;
119 Statement prepare failed with error -33
120 echo SELECT * FROM T4;
121 ---------------------------------------------------------
122 T4.f1 T4.f2
123 ---------------------------------------------------------
124 1 20
125 2 20
126 3 3
127 4 20
128 5 3
130 echo CREATE TABLE T5(f1 INT AUTO_INCREMENT,f2 INT NOT NULL);
131 Statement Executed
132 echo INSERT INTO T5 VALUES(NULL,2);
133 Statement Executed: Rows Affected = 1
134 echo INSERT INTO T5 VALUES(NULL,NULL);
135 Statement execute failed with error -22
136 echo INSERT INTO T5 VALUES(NULL,3);
137 Statement Executed: Rows Affected = 1
138 echo INSERT INTO T5 VALUES(NULL,2);
139 Statement Executed: Rows Affected = 1
140 echo INSERT INTO T5 VALUES(NULL,3);
141 Statement Executed: Rows Affected = 1
142 echo SELECT * FROM T5;
143 ---------------------------------------------------------
144 T5.f1 T5.f2
145 ---------------------------------------------------------
146 1 2
147 3 3
148 4 2
149 5 3
151 echo DELETE FROM T5 WHERE f2=2;
152 Statement Executed: Rows Affected = 2
153 echo SELECT * FROM T5;
154 ---------------------------------------------------------
155 T5.f1 T5.f2
156 ---------------------------------------------------------
157 3 3
158 5 3
160 echo ROLLBACK;
161 echo SELECT * FROM T5;
162 ---------------------------------------------------------
163 T5.f1 T5.f2
164 ---------------------------------------------------------
165 3 3
166 5 3
168 echo DELETE FROM T5 WHERE f1<=3 AND f1!=2;
169 Statement Executed: Rows Affected = 1
170 echo SELECT * FROM T5;
171 ---------------------------------------------------------
172 T5.f1 T5.f2
173 ---------------------------------------------------------
174 5 3
176 echo INSERT INTO T5 VALUES(3,30);
177 Statement Executed: Rows Affected = 1
178 echo SELECT * FROM T5;
179 ---------------------------------------------------------
180 T5.f1 T5.f2
181 ---------------------------------------------------------
182 3 30
183 5 3
185 echo DELETE FROM T5;
186 Statement Executed: Rows Affected = 2
187 echo SELECT * FROM T5;
188 ---------------------------------------------------------
189 T5.f1 T5.f2
190 ---------------------------------------------------------
192 echo CREATE TABLE T6(f1 INT AUTO_INCREMENT,f2 INT);
193 Statement Executed
194 echo INSERT INTO T6 VALUES(1,2);
195 Statement Executed: Rows Affected = 1
196 echo INSERT INTO T6 VALUES(NULL,2);
197 Statement Executed: Rows Affected = 1
198 echo INSERT INTO T6 VALUES(NULL,3);
199 Statement Executed: Rows Affected = 1
200 echo INSERT INTO T6 VALUES(NULL,2);
201 Statement Executed: Rows Affected = 1
202 echo INSERT INTO T6 VALUES(NULL,3);
203 Statement Executed: Rows Affected = 1
204 echo SELECT * FROM T6;
205 ---------------------------------------------------------
206 T6.f1 T6.f2
207 ---------------------------------------------------------
208 1 2
209 2 2
210 3 3
211 4 2
212 5 3
214 echo UPDATE T6 SET f2=20 WHERE f2=2;
215 Statement Executed: Rows Affected = 3
216 echo SELECT * FROM T6;
217 ---------------------------------------------------------
218 T6.f1 T6.f2
219 ---------------------------------------------------------
220 1 20
221 2 20
222 3 3
223 4 20
224 5 3
226 echo UPDATE T6 SET f2=200,f1=0 WHERE f2=20;
227 Statement prepare failed with error -33
228 echo SELECT * FROM T6;
229 ---------------------------------------------------------
230 T6.f1 T6.f2
231 ---------------------------------------------------------
232 1 20
233 2 20
234 3 3
235 4 20
236 5 3
238 echo UPDATE T6 SET f2=300,f1=30 WHERE f1<=2;
239 Statement prepare failed with error -33
240 echo SELECT * FROM T6;
241 ---------------------------------------------------------
242 T6.f1 T6.f2
243 ---------------------------------------------------------
244 1 20
245 2 20
246 3 3
247 4 20
248 5 3
250 echo DELETE FROM T6 WHERE f2=2;
251 Statement Executed: Rows Affected = 0
252 echo SELECT * FROM T6;
253 ---------------------------------------------------------
254 T6.f1 T6.f2
255 ---------------------------------------------------------
256 1 20
257 2 20
258 3 3
259 4 20
260 5 3
262 echo ROLLBACK;
263 echo SELECT * FROM T6;
264 ---------------------------------------------------------
265 T6.f1 T6.f2
266 ---------------------------------------------------------
267 1 20
268 2 20
269 3 3
270 4 20
271 5 3
273 echo DELETE FROM T6 WHERE f1<=3 AND f1!=2;
274 Statement Executed: Rows Affected = 2
275 echo SELECT * FROM T6;
276 ---------------------------------------------------------
277 T6.f1 T6.f2
278 ---------------------------------------------------------
279 2 20
280 4 20
281 5 3
283 echo INSERT INTO T6 VALUES(3,30);
284 Statement Executed: Rows Affected = 1
285 echo SELECT * FROM T6;
286 ---------------------------------------------------------
287 T6.f1 T6.f2
288 ---------------------------------------------------------
289 3 30
290 2 20
291 4 20
292 5 3
294 echo DELETE FROM T6;
295 Statement Executed: Rows Affected = 4
296 echo SELECT * FROM T6;
297 ---------------------------------------------------------
298 T6.f1 T6.f2
299 ---------------------------------------------------------
301 echo CREATE TABLE T7(f1 INT AUTO_INCREMENT,f2 INT,f3 INT);
302 Statement Executed
303 echo INSERT INTO T7 VALUES(1,2,3);
304 Statement Executed: Rows Affected = 1
305 echo SELECT * FROM T7;
306 ---------------------------------------------------------
307 T7.f1 T7.f2 T7.f3
308 ---------------------------------------------------------
309 1 2 3
311 echo UPDATE T7 SET f3=30,f2=20,f1=0 where f1=1;
312 Statement prepare failed with error -33
313 echo UPDATE T7 SET f3=30,f2=20 where f1=1;
314 Statement Executed: Rows Affected = 1
315 echo SELECT * FROM T7;
316 ---------------------------------------------------------
317 T7.f1 T7.f2 T7.f3
318 ---------------------------------------------------------
319 1 20 30
321 echo INSERT INTO T7 VALUES(2,4,3);
322 Statement Executed: Rows Affected = 1
323 echo DELETE FROM T7 WHERE f3<=3;
324 Statement Executed: Rows Affected = 1
325 echo CREATE TABLE T9(f1 INT AUTO_INCREMENT,f2 INT,f3 INT);
326 Statement Executed
327 echo CREATE INDEX idx1 ON T9(f1,f2) UNIQUE;
328 Statement Executed
329 echo INSERT INTO T9 VALUES(1,2,3);
330 Statement Executed: Rows Affected = 1
331 echo SELECT * FROM T9;
332 ---------------------------------------------------------
333 T9.f1 T9.f2 T9.f3
334 ---------------------------------------------------------
335 1 2 3
337 echo UPDATE T9 SET f3=30,f2=20,f1=0 where f1=1;
338 Statement prepare failed with error -21
339 echo UPDATE T9 SET f3=30,f2=20 where f1=1;
340 Statement prepare failed with error -21
341 echo SELECT * FROM T9;
342 ---------------------------------------------------------
343 T9.f1 T9.f2 T9.f3
344 ---------------------------------------------------------
345 1 2 3
347 echo INSERT INTO T9 VALUES(2,4,3);
348 Statement Executed: Rows Affected = 1
349 echo DELETE FROM T9 WHERE f3<=3;
350 Statement Executed: Rows Affected = 2
351 echo CREATE TABLE T10(f1 INT AUTO_INCREMENT,f2 INT,f3 INT,PRIMARY KEY(f3,f1));
352 Statement Executed
353 echo INSERT INTO T10 VALUES(1,2,3);
354 Statement Executed: Rows Affected = 1
355 echo SELECT * FROM T10;
356 ---------------------------------------------------------
357 T10.f1 T10.f2 T10.f3
358 ---------------------------------------------------------
359 1 2 3
361 echo UPDATE T10 SET f3=30,f2=20,f1=0 where f1=1;
362 Statement prepare failed with error -21
363 echo UPDATE T10 SET f3=30,f2=20 where f1=1;
364 Statement prepare failed with error -21
365 echo SELECT * FROM T10;
366 ---------------------------------------------------------
367 T10.f1 T10.f2 T10.f3
368 ---------------------------------------------------------
369 1 2 3
371 echo INSERT INTO T10 VALUES(2,4,3);
372 Statement Executed: Rows Affected = 1
373 echo DELETE FROM T10 WHERE f3<=3;
374 Statement Executed: Rows Affected = 2
375 Statement Executed
376 Statement Executed
377 Statement Executed
378 Statement Executed
379 Statement Executed
380 Statement Executed
381 Statement Executed
382 Statement Executed
383 Statement Executed