?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
adding test scripts
[csql.git] / test / sql / Distinct / exp.test005.ksh
blobc1c9d6dc892b60ca4d6a1b5ca53c349a8ade28a5
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
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 echo select * from t1;
20 ---------------------------------------------------------
21 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5 t1.f6 t1.f7 t1.f8 t1.f9
22 ---------------------------------------------------------
23 10 100 1000 Bijay 1000.000000 100000.000000 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
24 11 110 1100 Jitendr 2000.000000 200000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
25 12 120 1000 Nihar 3000.000000 300000.000000 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
26 13 130 1500 Kishor 4000.000000 400000.000000 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0
27 14 100 1000 Praba 5000.000000 500000.000000 2009/3/1 11:59:58.0 2009/3/1 11:59:59.0
28
29 echo select * from t2;
30 ---------------------------------------------------------
31 t2.f1 t2.f2 t2.f3 t2.f4 t2.f5 t2.f6 t2.f7 t2.f8 t2.f9
32 ---------------------------------------------------------
33 13 130 1500 Kishor 4000.000000 400000.000000 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0
34 16 120 1000 Sanjay 6500.000000 700000.000000 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0
35 10 100 1000 Bijay 1000.000000 100000.000000 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
36 17 130 1100 Arindam 8000.000000 800000.000000 2009/3/4 11:59:58.0 2009/3/4 11:59:59.0
37 11 110 1100 Jitendr 2000.000000 200000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
38
39 echo select * from t3;
40 ---------------------------------------------------------
41 t3.f1 t3.f2 t3.f3 t3.f4 t3.f5 t3.f6 t3.f7 t3.f8 t3.f9
42 ---------------------------------------------------------
43 12 120 1000 Nihar 3000.000000 300000.000000 2009/3/3 11:59:57.0 2009/3/3 11:59:59.0
44 14 100 1000 Praba 4000.000000 400000.000000 2009/3/1 11:59:55.0 2009/3/4 11:59:59.0
45 13 130 1500 Kishor 4000.000000 400000.000000 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0
46 11 110 1100 Jitendr 2000.000000 200000.000000 2009/3/2 11:59:58.0 2009/3/2 11:59:59.0
47 16 120 1000 Sanjay 6000.000000 600000.000000 2009/3/3 11:59:55.0 2009/3/3 11:59:59.0
48
49 echo select distinct t1.f1, t2.f1 from t1 left join t2 on t1.f1 = t2.f1 ;
50 ---------------------------------------------------------
51 t1.f1 t2.f1
52 ---------------------------------------------------------
53 10 10
54 11 11
55 12 NULL
56 13 13
57 14 NULL
58
59 echo select distinct t1.f2, t2.f2 from t1 left join t2 on t1.f2 = t2.f2 ;
60 ---------------------------------------------------------
61 t1.f2 t2.f2
62 ---------------------------------------------------------
63 100 100
64 110 110
65 120 120
66 130 130
67
68 echo select distinct t1.f3, t2.f3 from t1 left join t2 on t1.f3 = t2.f3 ;
69 ---------------------------------------------------------
70 t1.f3 t2.f3
71 ---------------------------------------------------------
72 1000 1000
73 1100 1100
74 1500 1500
75
76 echo select distinct t1.f4, t2.f4 from t1 left join t2 on t1.f4 = t2.f4 ;
77 ---------------------------------------------------------
78 t1.f4 t2.f4
79 ---------------------------------------------------------
80 Bijay Bijay
81 Jitendr Jitendr
82 Nihar NULL
83 Kishor Kishor
84 Praba NULL
85
86 echo select distinct t1.f5, t2.f5 from t1 left join t2 on t1.f5 = t2.f5 ;
87 ---------------------------------------------------------
88 t1.f5 t2.f5
89 ---------------------------------------------------------
90 1000.000000 1000.000000
91 2000.000000 2000.000000
92 3000.000000 NULL
93 4000.000000 4000.000000
94 5000.000000 NULL
95
96 echo select distinct t1.f6, t2.f6 from t1 left join t2 on t1.f6 = t2.f6 ;
97 ---------------------------------------------------------
98 t1.f6 t2.f6
99 ---------------------------------------------------------
100 100000.000000 100000.000000
101 200000.000000 200000.000000
102 300000.000000 NULL
103 400000.000000 400000.000000
104 500000.000000 NULL
105
106 echo select distinct t1.f7, t2.f7 from t1 left join t2 on t1.f7 = t2.f7 ;
107 ---------------------------------------------------------
108 t1.f7 t2.f7
109 ---------------------------------------------------------
110 2009/3/1 2009/3/1
111 2009/3/2 2009/3/2
112 2009/3/3 2009/3/3
113 2009/3/4 2009/3/4
114
115 echo select distinct t1.f8, t2.f8 from t1 left join t2 on t1.f8 = t2.f8 ;
116 ---------------------------------------------------------
117 t1.f8 t2.f8
118 ---------------------------------------------------------
119 11:59:59.0 11:59:59.0
120 11:59:58.0 11:59:58.0
121 11:59:57.0 NULL
122
123 echo select distinct t1.f9, t2.f9 from t1 left join t2 on t1.f9 = t2.f9 ;
124 ---------------------------------------------------------
125 t1.f9 t2.f9
126 ---------------------------------------------------------
127 2009/3/1 11:59:59.0 2009/3/1 11:59:59.0
128 2009/3/2 11:59:59.0 2009/3/2 11:59:59.0
129 2009/3/3 11:59:59.0 2009/3/3 11:59:59.0
130 2009/3/4 11:59:59.0 2009/3/4 11:59:59.0
131
132 echo select distinct t1.f1, t1.f2 from t1 left join t2 on t1.f1 = t2.f1 ;
133 ---------------------------------------------------------
134 t1.f1 t1.f2
135 ---------------------------------------------------------
136 10 100
137 11 110
138 12 120
139 13 130
140 14 100
141
142 echo select distinct t1.f1, t1.f2 from t1 left join t2 on t1.f2 = t2.f2 ;
143 ---------------------------------------------------------
144 t1.f1 t1.f2
145 ---------------------------------------------------------
146 10 100
147 11 110
148 12 120
149 13 130
150 14 100
151
152 echo select distinct t1.f1, t1.f3 from t1 left join t2 on t1.f3 = t2.f3 ;
153 ---------------------------------------------------------
154 t1.f1 t1.f3
155 ---------------------------------------------------------
156 10 1000
157 11 1100
158 12 1000
159 13 1500
160 14 1000
161
162 echo select distinct t1.f1, t1.f4 from t1 left join t2 on t1.f4 = t2.f4 ;
163 ---------------------------------------------------------
164 t1.f1 t1.f4
165 ---------------------------------------------------------
166 10 Bijay
167 11 Jitendr
168 12 Nihar
169 13 Kishor
170 14 Praba
171
172 echo select distinct t1.f1, t1.f5 from t1 left join t2 on t1.f5 = t2.f5 ;
173 ---------------------------------------------------------
174 t1.f1 t1.f5
175 ---------------------------------------------------------
176 10 1000.000000
177 11 2000.000000
178 12 3000.000000
179 13 4000.000000
180 14 5000.000000
181
182 echo select distinct t1.f1, t1.f6 from t1 left join t2 on t1.f6 = t2.f6 ;
183 ---------------------------------------------------------
184 t1.f1 t1.f6
185 ---------------------------------------------------------
186 10 100000.000000
187 11 200000.000000
188 12 300000.000000
189 13 400000.000000
190 14 500000.000000
191
192 echo select distinct t1.f1, t1.f7 from t1 left join t2 on t1.f7 = t2.f7 ;
193 ---------------------------------------------------------
194 t1.f1 t1.f7
195 ---------------------------------------------------------
196 10 2009/3/1
197 11 2009/3/2
198 12 2009/3/3
199 13 2009/3/4
200 14 2009/3/1
201
202 echo select distinct t1.f1, t1.f8 from t1 left join t2 on t1.f8 = t2.f8 ;
203 ---------------------------------------------------------
204 t1.f1 t1.f8
205 ---------------------------------------------------------
206 10 11:59:59.0
207 11 11:59:58.0
208 12 11:59:57.0
209 13 11:59:59.0
210 14 11:59:58.0
211
212 echo select distinct t1.f1, t1.f9 from t1 left join t2 on t1.f9 = t2.f9 ;
213 ---------------------------------------------------------
214 t1.f1 t1.f9
215 ---------------------------------------------------------
216 10 2009/3/1 11:59:59.0
217 11 2009/3/2 11:59:59.0
218 12 2009/3/3 11:59:59.0
219 13 2009/3/4 11:59:59.0
220 14 2009/3/1 11:59:59.0
221
222 echo select distinct t1.f1, t1.f2, t2.f1, t2.f2 from t1 left join t2 on t1.f1 = t2.f1 ;
223 ---------------------------------------------------------
224 t1.f1 t1.f2 t2.f1 t2.f2
225 ---------------------------------------------------------
226 10 100 10 100
227 11 110 11 110
228 12 120 NULL NULL
229 13 130 13 130
230 14 100 NULL NULL
231
232 echo select distinct t1.f1, t1.f2, t2.f1, t2.f2 from t1 left join t2 on t1.f2 = t2.f2 ;
233 ---------------------------------------------------------
234 t1.f1 t1.f2 t2.f1 t2.f2
235 ---------------------------------------------------------
236 10 100 10 100
237 11 110 11 110
238 12 120 16 120
239 13 130 13 130
240 13 130 17 130
241 14 100 10 100
242
243 echo select distinct t1.f1, t1.f3, t2.f1, t2.f3 from t1 left join t2 on t1.f3 = t2.f3 ;
244 ---------------------------------------------------------
245 t1.f1 t1.f3 t2.f1 t2.f3
246 ---------------------------------------------------------
247 10 1000 16 1000
248 10 1000 10 1000
249 11 1100 17 1100
250 11 1100 11 1100
251 12 1000 16 1000
252 12 1000 10 1000
253 13 1500 13 1500
254 14 1000 16 1000
255 14 1000 10 1000
256
257 echo select distinct t1.f1, t1.f4, t2.f1, t2.f4 from t1 left join t2 on t1.f4 = t2.f4 ;
258 ---------------------------------------------------------
259 t1.f1 t1.f4 t2.f1 t2.f4
260 ---------------------------------------------------------
261 10 Bijay 10 Bijay
262 11 Jitendr 11 Jitendr
263 12 Nihar NULL NULL
264 13 Kishor 13 Kishor
265 14 Praba NULL NULL
266
267 echo select distinct t1.f1, t1.f5, t2.f1, t2.f5 from t1 left join t2 on t1.f5 = t2.f5 ;
268 ---------------------------------------------------------
269 t1.f1 t1.f5 t2.f1 t2.f5
270 ---------------------------------------------------------
271 10 1000.000000 10 1000.000000
272 11 2000.000000 11 2000.000000
273 12 3000.000000 NULL NULL
274 13 4000.000000 13 4000.000000
275 14 5000.000000 NULL NULL
276
277 echo select distinct t1.f1, t1.f6, t2.f1, t2.f6 from t1 left join t2 on t1.f6 = t2.f6 ;
278 ---------------------------------------------------------
279 t1.f1 t1.f6 t2.f1 t2.f6
280 ---------------------------------------------------------
281 10 100000.000000 10 100000.000000
282 11 200000.000000 11 200000.000000
283 12 300000.000000 NULL NULL
284 13 400000.000000 13 400000.000000
285 14 500000.000000 NULL NULL
286
287 echo select distinct t1.f1, t1.f7, t2.f1, t2.f7 from t1 left join t2 on t1.f7 = t2.f7 ;
288 ---------------------------------------------------------
289 t1.f1 t1.f7 t2.f1 t2.f7
290 ---------------------------------------------------------
291 10 2009/3/1 10 2009/3/1
292 11 2009/3/2 11 2009/3/2
293 12 2009/3/3 16 2009/3/3
294 13 2009/3/4 13 2009/3/4
295 13 2009/3/4 17 2009/3/4
296 14 2009/3/1 10 2009/3/1
297
298 echo select distinct t1.f1, t1.f8, t2.f1, t2.f8 from t1 left join t2 on t1.f8 = t2.f8 ;
299 ---------------------------------------------------------
300 t1.f1 t1.f8 t2.f1 t2.f8
301 ---------------------------------------------------------
302 10 11:59:59.0 13 11:59:59.0
303 10 11:59:59.0 16 11:59:59.0
304 10 11:59:59.0 10 11:59:59.0
305 11 11:59:58.0 17 11:59:58.0
306 11 11:59:58.0 11 11:59:58.0
307 12 11:59:57.0 NULL NULL
308 13 11:59:59.0 13 11:59:59.0
309 13 11:59:59.0 16 11:59:59.0
310 13 11:59:59.0 10 11:59:59.0
311 14 11:59:58.0 17 11:59:58.0
312 14 11:59:58.0 11 11:59:58.0
313
314 echo select distinct t1.f1, t1.f9, t2.f1, t2.f9 from t1 left join t2 on t1.f9 = t2.f9 ;
315 ---------------------------------------------------------
316 t1.f1 t1.f9 t2.f1 t2.f9
317 ---------------------------------------------------------
318 10 2009/3/1 11:59:59.0 10 2009/3/1 11:59:59.0
319 11 2009/3/2 11:59:59.0 11 2009/3/2 11:59:59.0
320 12 2009/3/3 11:59:59.0 16 2009/3/3 11:59:59.0
321 13 2009/3/4 11:59:59.0 13 2009/3/4 11:59:59.0
322 13 2009/3/4 11:59:59.0 17 2009/3/4 11:59:59.0
323 14 2009/3/1 11:59:59.0 10 2009/3/1 11:59:59.0
324
325 Statement Executed
326 Statement Executed
327 Statement Executed
Main memory database SQL engine (cvs import)