?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
adding test scripts
[csql.git] / test / sql / Order / exp.test009.ksh
blobdc36b12eb98ac8370bbcc1a8c6c3bb4bc3de4af5
1 Statement Executed
2 Statement Executed: Rows Affected = 1
3 Statement Executed: Rows Affected = 1
4 Statement Executed: Rows Affected = 1
5 Statement Executed: Rows Affected = 1
6 Statement Executed: Rows Affected = 1
7 Statement Executed: Rows Affected = 1
8 Statement Executed: Rows Affected = 1
9 Statement Executed: Rows Affected = 1
10 Statement Executed: Rows Affected = 1
11 Statement Executed: Rows Affected = 1
12 Statement Executed: Rows Affected = 1
13 Statement Executed
14 Statement Executed: Rows Affected = 1
15 Statement Executed: Rows Affected = 1
16 Statement Executed: Rows Affected = 1
17 Statement Executed: Rows Affected = 1
18 Statement Executed: Rows Affected = 1
19 Statement Executed: Rows Affected = 1
20 Statement Executed: Rows Affected = 1
21 Statement Executed: Rows Affected = 1
22 Statement Executed: Rows Affected = 1
23 Statement Executed: Rows Affected = 1
24 Statement Executed: Rows Affected = 1
25 Statement Executed
26 Statement Executed: Rows Affected = 1
27 Statement Executed: Rows Affected = 1
28 Statement Executed: Rows Affected = 1
29 Statement Executed: Rows Affected = 1
30 Statement Executed: Rows Affected = 1
31 Statement Executed: Rows Affected = 1
32 Statement Executed: Rows Affected = 1
33 Statement Executed: Rows Affected = 1
34 Statement Executed: Rows Affected = 1
35 Statement Executed: Rows Affected = 1
36 Statement Executed: Rows Affected = 1
37 echo SELECT * FROM t1;
38 ---------------------------------------------------------
39 t1.f1 t1.f2 t1.f3 t1.f4
40 ---------------------------------------------------------
41 3 AAA 2.500000 2008/7/1
42 2 AAA 2.500000 2008/7/2
43 4 GGG 4.500000 2008/7/3
44 1 DEF 1.500000 2008/7/1
45 6 CCC 6.500000 2008/7/2
46 5 HHH 5.500000 2008/7/3
47 2 AAA 2.500000 2008/7/1
48 6 CCC 6.500000 2008/7/4
49 3 ABB 2.600000 2008/7/5
50 2 BBB 1.600000 2008/7/4
51 1 DDD 1.600000 2008/7/1
52
53 echo SELECT * FROM t2;
54 ---------------------------------------------------------
55 t2.f1 t2.f2 t2.f3 t2.f4
56 ---------------------------------------------------------
57 1 DEF 1.500000 2008/7/1
58 9 QQQ 9.500000 2008/7/2
59 3 MMM 2.500000 2008/7/3
60 4 GGG 4.600000 2008/7/1
61 7 PPP 7.500000 2008/7/2
62 8 NNN 8.500000 2008/7/3
63 9 QQQ 9.500000 2008/7/1
64 8 AAA 8.500000 2008/7/4
65 7 AAA 7.500000 2008/7/5
66 9 BBB 9.800000 2008/7/4
67 8 EEE 8.800000 2008/7/1
68
69 echo SELECT * FROM t3;
70 ---------------------------------------------------------
71 t3.f1 t3.f2 t3.f3 t3.f4
72 ---------------------------------------------------------
73 1 DEF 1.500000 2008/7/1
74 2 AAA 2.500000 2008/7/2
75 7 PPP 7.500000 2008/7/3
76 8 NNN 8.500000 2008/7/4
77 9 QQQ 9.500000 2008/7/1
78 10 NNN 10.500000 2008/7/2
79 1 DEF 1.500000 2008/7/3
80 2 AAA 2.500000 2008/7/4
81 7 PPP 7.500000 2008/7/1
82 8 NNN 8.500000 2008/7/2
83 10 NNN 10.500000 2008/7/4
84
85 echo SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 ORDER BY t1.f1;
86 ---------------------------------------------------------
87 t1.f1 t1.f2 t1.f3 t1.f4 t2.f1 t2.f2 t2.f3 t2.f4
88 ---------------------------------------------------------
89 1 DDD 1.600000 2008/7/1 1 DEF 1.500000 2008/7/1
90 1 DEF 1.500000 2008/7/1 1 DEF 1.500000 2008/7/1
91 2 BBB 1.600000 2008/7/4 NULL NULL NULL NULL
92 2 AAA 2.500000 2008/7/1 NULL NULL NULL NULL
93 2 AAA 2.500000 2008/7/2 NULL NULL NULL NULL
94 3 ABB 2.600000 2008/7/5 3 MMM 2.500000 2008/7/3
95 3 AAA 2.500000 2008/7/1 3 MMM 2.500000 2008/7/3
96 4 GGG 4.500000 2008/7/3 4 GGG 4.600000 2008/7/1
97 5 HHH 5.500000 2008/7/3 NULL NULL NULL NULL
98 6 CCC 6.500000 2008/7/4 NULL NULL NULL NULL
99 6 CCC 6.500000 2008/7/2 NULL NULL NULL NULL
100
101 echo SELECT t1.f1, t2.f1, t3.f1 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 LEFT JOIN t3 ON t2.f1 = t3.f1 ORDER BY t1.f1;
102 ---------------------------------------------------------
103 t1.f1 t2.f1 t3.f1
104 ---------------------------------------------------------
105 1 1 1
106 1 1 1
107 1 1 1
108 1 1 1
109 2 NULL NULL
110 2 NULL NULL
111 2 NULL NULL
112 3 3 NULL
113 3 3 NULL
114 4 4 NULL
115 5 NULL NULL
116 6 NULL NULL
117 6 NULL NULL
118
119 echo SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 ORDER BY t1.f1,t2.f1;
120 ---------------------------------------------------------
121 t1.f1 t1.f2 t1.f3 t1.f4 t2.f1 t2.f2 t2.f3 t2.f4
122 ---------------------------------------------------------
123 1 DDD 1.600000 2008/7/1 1 DEF 1.500000 2008/7/1
124 1 DEF 1.500000 2008/7/1 1 DEF 1.500000 2008/7/1
125 2 BBB 1.600000 2008/7/4 NULL NULL NULL NULL
126 2 AAA 2.500000 2008/7/1 NULL NULL NULL NULL
127 2 AAA 2.500000 2008/7/2 NULL NULL NULL NULL
128 3 ABB 2.600000 2008/7/5 3 MMM 2.500000 2008/7/3
129 3 AAA 2.500000 2008/7/1 3 MMM 2.500000 2008/7/3
130 4 GGG 4.500000 2008/7/3 4 GGG 4.600000 2008/7/1
131 5 HHH 5.500000 2008/7/3 NULL NULL NULL NULL
132 6 CCC 6.500000 2008/7/4 NULL NULL NULL NULL
133 6 CCC 6.500000 2008/7/2 NULL NULL NULL NULL
134
135 echo SELECT t1.f1, t3.f1, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 LEFT JOIN t3 ON t3.f1 = t1.f1 ORDER BY t1.f1,t3.f1,t2.f1;
136 ---------------------------------------------------------
137 t1.f1 t3.f1 t2.f1
138 ---------------------------------------------------------
139 1 1 1
140 1 1 1
141 1 1 1
142 1 1 1
143 2 2 NULL
144 2 2 NULL
145 2 2 NULL
146 2 2 NULL
147 2 2 NULL
148 2 2 NULL
149 3 NULL 3
150 3 NULL 3
151 4 NULL 4
152 5 NULL NULL
153 6 NULL NULL
154 6 NULL NULL
155
156 echo SELECT t1.f1, t3.f1, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 LEFT JOIN t3 ON t3.f1 = t1.f1 ORDER BY t1.f1 DESC,t3.f1 DESC,t2.f1 DESC;
157 ---------------------------------------------------------
158 t1.f1 t3.f1 t2.f1
159 ---------------------------------------------------------
160 6 NULL NULL
161 6 NULL NULL
162 5 NULL NULL
163 4 NULL 4
164 3 NULL 3
165 3 NULL 3
166 2 2 NULL
167 2 2 NULL
168 2 2 NULL
169 2 2 NULL
170 2 2 NULL
171 2 2 NULL
172 1 1 1
173 1 1 1
174 1 1 1
175 1 1 1
176
177 echo SELECT t1.f1, t2.f1, t1.f4, t2.f4 FROM t1 LEFT JOIN t2 ON t1.f1 < 2 OR t1.f3 < 2.5 ORDER BY t1.f4, t2.f4;
178 ---------------------------------------------------------
179 t1.f1 t2.f1 t1.f4 t2.f4
180 ---------------------------------------------------------
181 3 NULL 2008/7/1 NULL
182 1 8 2008/7/1 2008/7/1
183 1 9 2008/7/1 2008/7/1
184 1 4 2008/7/1 2008/7/1
185 1 1 2008/7/1 2008/7/1
186 1 8 2008/7/1 2008/7/1
187 1 9 2008/7/1 2008/7/1
188 1 4 2008/7/1 2008/7/1
189 1 1 2008/7/1 2008/7/1
190 1 7 2008/7/1 2008/7/2
191 1 9 2008/7/1 2008/7/2
192 1 7 2008/7/1 2008/7/2
193 1 9 2008/7/1 2008/7/2
194 1 8 2008/7/1 2008/7/3
195 1 3 2008/7/1 2008/7/3
196 1 8 2008/7/1 2008/7/3
197 1 3 2008/7/1 2008/7/3
198 1 9 2008/7/1 2008/7/4
199 1 8 2008/7/1 2008/7/4
200 1 9 2008/7/1 2008/7/4
201 1 8 2008/7/1 2008/7/4
202 1 7 2008/7/1 2008/7/5
203 1 7 2008/7/1 2008/7/5
204 2 NULL 2008/7/1 NULL
205 2 NULL 2008/7/2 NULL
206 6 NULL 2008/7/2 NULL
207 4 NULL 2008/7/3 NULL
208 5 NULL 2008/7/3 NULL
209 2 8 2008/7/4 2008/7/1
210 2 9 2008/7/4 2008/7/1
211 2 4 2008/7/4 2008/7/1
212 2 1 2008/7/4 2008/7/1
213 2 7 2008/7/4 2008/7/2
214 2 9 2008/7/4 2008/7/2
215 2 8 2008/7/4 2008/7/3
216 2 3 2008/7/4 2008/7/3
217 2 9 2008/7/4 2008/7/4
218 2 8 2008/7/4 2008/7/4
219 2 7 2008/7/4 2008/7/5
220 6 NULL 2008/7/4 NULL
221 3 NULL 2008/7/5 NULL
222
223 echo SELECT t1.f1, t2.f1, t1.f4, t2.f4 FROM t1 LEFT JOIN t2 ON t1.f1 < 2 OR t1.f3 < 2.5 ORDER BY t1.f1, t1.f4 DESC ;
224 ---------------------------------------------------------
225 t1.f1 t2.f1 t1.f4 t2.f4
226 ---------------------------------------------------------
227 1 8 2008/7/1 2008/7/1
228 1 9 2008/7/1 2008/7/4
229 1 7 2008/7/1 2008/7/5
230 1 8 2008/7/1 2008/7/4
231 1 9 2008/7/1 2008/7/1
232 1 8 2008/7/1 2008/7/3
233 1 7 2008/7/1 2008/7/2
234 1 4 2008/7/1 2008/7/1
235 1 3 2008/7/1 2008/7/3
236 1 9 2008/7/1 2008/7/2
237 1 1 2008/7/1 2008/7/1
238 1 8 2008/7/1 2008/7/1
239 1 9 2008/7/1 2008/7/4
240 1 7 2008/7/1 2008/7/5
241 1 8 2008/7/1 2008/7/4
242 1 9 2008/7/1 2008/7/1
243 1 8 2008/7/1 2008/7/3
244 1 7 2008/7/1 2008/7/2
245 1 4 2008/7/1 2008/7/1
246 1 3 2008/7/1 2008/7/3
247 1 9 2008/7/1 2008/7/2
248 1 1 2008/7/1 2008/7/1
249 2 8 2008/7/4 2008/7/1
250 2 9 2008/7/4 2008/7/4
251 2 7 2008/7/4 2008/7/5
252 2 8 2008/7/4 2008/7/4
253 2 9 2008/7/4 2008/7/1
254 2 8 2008/7/4 2008/7/3
255 2 7 2008/7/4 2008/7/2
256 2 4 2008/7/4 2008/7/1
257 2 3 2008/7/4 2008/7/3
258 2 9 2008/7/4 2008/7/2
259 2 1 2008/7/4 2008/7/1
260 2 NULL 2008/7/2 NULL
261 2 NULL 2008/7/1 NULL
262 3 NULL 2008/7/5 NULL
263 3 NULL 2008/7/1 NULL
264 4 NULL 2008/7/3 NULL
265 5 NULL 2008/7/3 NULL
266 6 NULL 2008/7/4 NULL
267 6 NULL 2008/7/2 NULL
268
269 echo SELECT t1.f1, t2.f4, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 < 2 OR t2.f1 < 2 ORDER BY t1.f1, t2.f4 ;
270 ---------------------------------------------------------
271 t1.f1 t2.f4 t2.f1
272 ---------------------------------------------------------
273 1 2008/7/1 8
274 1 2008/7/1 9
275 1 2008/7/1 4
276 1 2008/7/1 1
277 1 2008/7/1 8
278 1 2008/7/1 9
279 1 2008/7/1 4
280 1 2008/7/1 1
281 1 2008/7/2 7
282 1 2008/7/2 9
283 1 2008/7/2 7
284 1 2008/7/2 9
285 1 2008/7/3 8
286 1 2008/7/3 3
287 1 2008/7/3 8
288 1 2008/7/3 3
289 1 2008/7/4 9
290 1 2008/7/4 8
291 1 2008/7/4 9
292 1 2008/7/4 8
293 1 2008/7/5 7
294 1 2008/7/5 7
295 2 2008/7/1 1
296 2 2008/7/1 1
297 2 2008/7/1 1
298 3 2008/7/1 1
299 3 2008/7/1 1
300 4 2008/7/1 1
301 5 2008/7/1 1
302 6 2008/7/1 1
303 6 2008/7/1 1
304
305 echo SELECT t1.f1, t2.f4, t2.f1 FROM t1 LEFT JOIN t2 ON t1.f1 < 2 OR t2.f1 < 2 ORDER BY t1.f1 DESC, t2.f4 ;
306 ---------------------------------------------------------
307 t1.f1 t2.f4 t2.f1
308 ---------------------------------------------------------
309 6 2008/7/1 1
310 6 2008/7/1 1
311 5 2008/7/1 1
312 4 2008/7/1 1
313 3 2008/7/1 1
314 3 2008/7/1 1
315 2 2008/7/1 1
316 2 2008/7/1 1
317 2 2008/7/1 1
318 1 2008/7/1 8
319 1 2008/7/1 9
320 1 2008/7/1 4
321 1 2008/7/1 1
322 1 2008/7/1 8
323 1 2008/7/1 9
324 1 2008/7/1 4
325 1 2008/7/1 1
326 1 2008/7/2 7
327 1 2008/7/2 9
328 1 2008/7/2 7
329 1 2008/7/2 9
330 1 2008/7/3 8
331 1 2008/7/3 3
332 1 2008/7/3 8
333 1 2008/7/3 3
334 1 2008/7/4 9
335 1 2008/7/4 8
336 1 2008/7/4 9
337 1 2008/7/4 8
338 1 2008/7/5 7
339 1 2008/7/5 7
340
341 Statement Executed
342 Statement Executed
343 Statement Executed
Main memory database SQL engine (cvs import)