adding test scripts
[csql.git] / test / sql / Optimizer / exp.test010.ksh
blob8e283ebae3f729847f1bc995c57e538586225dce
1 echo create table t1(f1 int, f2 int, f3 int, f4 int);
2 Statement Executed
3 echo create index hashintf1f2f3 on t1(f2,f3,f1) hash;
4 Statement Executed
5 echo explain plan select * from t1;
6 <TABLE-NODE>
7 <NAME> t1 </NAME>
8 <ScanType> TableScan </ScanType>
9 </TABLE-NODE>
10 echo explain plan select * from t1 where f1 = 2;
11 <TABLE-NODE>
12 <NAME> t1 </NAME>
13 <ScanType> TableScan </ScanType>
14 <PREDICATE>
15 <FieldName1> f1 </FieldName1>
16 <CompOp> Equals </CompOp>
17 <OperandPtr> VALUE </OperandPtr>
18 </PREDICATE>
19 </TABLE-NODE>
20 echo explain plan select * from t1 where f1 = 2 and f2=2;
21 <TABLE-NODE>
22 <NAME> t1 </NAME>
23 <ScanType> TableScan </ScanType>
24 <PREDICATE>
25 <LogOp> AND </LogOp>
26 <PRED-LEFT>
27 <PREDICATE>
28 <FieldName1> f1 </FieldName1>
29 <CompOp> Equals </CompOp>
30 <OperandPtr> VALUE </OperandPtr>
31 </PREDICATE>
32 </PRED-LEFT>
33 <PRED-RIGHT>
34 <PREDICATE>
35 <FieldName1> f2 </FieldName1>
36 <CompOp> Equals </CompOp>
37 <OperandPtr> VALUE </OperandPtr>
38 </PREDICATE>
39 </PRED-RIGHT>
40 </PREDICATE>
41 </TABLE-NODE>
42 echo explain plan select * from t1 where f1 = 2 and f2=2 and f3=2;
43 <TABLE-NODE>
44 <NAME> t1 </NAME>
45 <ScanType> TableScan </ScanType>
46 <PREDICATE>
47 <LogOp> AND </LogOp>
48 <PRED-LEFT>
49 <PREDICATE>
50 <LogOp> AND </LogOp>
51 <PRED-LEFT>
52 <PREDICATE>
53 <FieldName1> f1 </FieldName1>
54 <CompOp> Equals </CompOp>
55 <OperandPtr> VALUE </OperandPtr>
56 </PREDICATE>
57 </PRED-LEFT>
58 <PRED-RIGHT>
59 <PREDICATE>
60 <FieldName1> f2 </FieldName1>
61 <CompOp> Equals </CompOp>
62 <OperandPtr> VALUE </OperandPtr>
63 </PREDICATE>
64 </PRED-RIGHT>
65 </PREDICATE>
66 </PRED-LEFT>
67 <PRED-RIGHT>
68 <PREDICATE>
69 <FieldName1> f3 </FieldName1>
70 <CompOp> Equals </CompOp>
71 <OperandPtr> VALUE </OperandPtr>
72 </PREDICATE>
73 </PRED-RIGHT>
74 </PREDICATE>
75 </TABLE-NODE>
76 echo explain plan select * from t1 where f1 = 2 or f2=2 or f3=2;
77 <TABLE-NODE>
78 <NAME> t1 </NAME>
79 <ScanType> TableScan </ScanType>
80 <PREDICATE>
81 <LogOp> OR </LogOp>
82 <PRED-LEFT>
83 <PREDICATE>
84 <LogOp> OR </LogOp>
85 <PRED-LEFT>
86 <PREDICATE>
87 <FieldName1> f1 </FieldName1>
88 <CompOp> Equals </CompOp>
89 <OperandPtr> VALUE </OperandPtr>
90 </PREDICATE>
91 </PRED-LEFT>
92 <PRED-RIGHT>
93 <PREDICATE>
94 <FieldName1> f2 </FieldName1>
95 <CompOp> Equals </CompOp>
96 <OperandPtr> VALUE </OperandPtr>
97 </PREDICATE>
98 </PRED-RIGHT>
99 </PREDICATE>
100 </PRED-LEFT>
101 <PRED-RIGHT>
102 <PREDICATE>
103 <FieldName1> f3 </FieldName1>
104 <CompOp> Equals </CompOp>
105 <OperandPtr> VALUE </OperandPtr>
106 </PREDICATE>
107 </PRED-RIGHT>
108 </PREDICATE>
109 </TABLE-NODE>
110 echo explain plan select * from t1 where f1 = 2 and f2=2 or f3=2;
111 <TABLE-NODE>
112 <NAME> t1 </NAME>
113 <ScanType> TableScan </ScanType>
114 <PREDICATE>
115 <LogOp> OR </LogOp>
116 <PRED-LEFT>
117 <PREDICATE>
118 <LogOp> AND </LogOp>
119 <PRED-LEFT>
120 <PREDICATE>
121 <FieldName1> f1 </FieldName1>
122 <CompOp> Equals </CompOp>
123 <OperandPtr> VALUE </OperandPtr>
124 </PREDICATE>
125 </PRED-LEFT>
126 <PRED-RIGHT>
127 <PREDICATE>
128 <FieldName1> f2 </FieldName1>
129 <CompOp> Equals </CompOp>
130 <OperandPtr> VALUE </OperandPtr>
131 </PREDICATE>
132 </PRED-RIGHT>
133 </PREDICATE>
134 </PRED-LEFT>
135 <PRED-RIGHT>
136 <PREDICATE>
137 <FieldName1> f3 </FieldName1>
138 <CompOp> Equals </CompOp>
139 <OperandPtr> VALUE </OperandPtr>
140 </PREDICATE>
141 </PRED-RIGHT>
142 </PREDICATE>
143 </TABLE-NODE>
144 echo explain plan select * from t1 where f1 = 2 and f2=2 and not(f3=2);
145 <TABLE-NODE>
146 <NAME> t1 </NAME>
147 <ScanType> TableScan </ScanType>
148 <PREDICATE>
149 <LogOp> AND </LogOp>
150 <PRED-LEFT>
151 <PREDICATE>
152 <LogOp> AND </LogOp>
153 <PRED-LEFT>
154 <PREDICATE>
155 <FieldName1> f1 </FieldName1>
156 <CompOp> Equals </CompOp>
157 <OperandPtr> VALUE </OperandPtr>
158 </PREDICATE>
159 </PRED-LEFT>
160 <PRED-RIGHT>
161 <PREDICATE>
162 <FieldName1> f2 </FieldName1>
163 <CompOp> Equals </CompOp>
164 <OperandPtr> VALUE </OperandPtr>
165 </PREDICATE>
166 </PRED-RIGHT>
167 </PREDICATE>
168 </PRED-LEFT>
169 <PRED-RIGHT>
170 <PREDICATE>
171 <LogOp> NOT </LogOp>
172 <PRED-LEFT>
173 <PREDICATE>
174 <FieldName1> f3 </FieldName1>
175 <CompOp> Equals </CompOp>
176 <OperandPtr> VALUE </OperandPtr>
177 </PREDICATE>
178 </PRED-LEFT>
179 </PREDICATE>
180 </PRED-RIGHT>
181 </PREDICATE>
182 </TABLE-NODE>
183 echo explain plan select * from t1 where f1 in(1,2,3) and f2=2 and not(f3=2);
184 <TABLE-NODE>
185 <NAME> t1 </NAME>
186 <ScanType> TableScan </ScanType>
187 <PREDICATE>
188 <LogOp> AND </LogOp>
189 <PRED-LEFT>
190 <PREDICATE>
191 <LogOp> AND </LogOp>
192 <PRED-LEFT>
193 <PREDICATE>
194 <LogOp> OR </LogOp>
195 <PRED-LEFT>
196 <PREDICATE>
197 <LogOp> OR </LogOp>
198 <PRED-LEFT>
199 <PREDICATE>
200 <FieldName1> f1 </FieldName1>
201 <CompOp> Equals </CompOp>
202 <OperandPtr> VALUE </OperandPtr>
203 </PREDICATE>
204 </PRED-LEFT>
205 <PRED-RIGHT>
206 <PREDICATE>
207 <FieldName1> f1 </FieldName1>
208 <CompOp> Equals </CompOp>
209 <OperandPtr> VALUE </OperandPtr>
210 </PREDICATE>
211 </PRED-RIGHT>
212 </PREDICATE>
213 </PRED-LEFT>
214 <PRED-RIGHT>
215 <PREDICATE>
216 <FieldName1> f1 </FieldName1>
217 <CompOp> Equals </CompOp>
218 <OperandPtr> VALUE </OperandPtr>
219 </PREDICATE>
220 </PRED-RIGHT>
221 </PREDICATE>
222 </PRED-LEFT>
223 <PRED-RIGHT>
224 <PREDICATE>
225 <FieldName1> f2 </FieldName1>
226 <CompOp> Equals </CompOp>
227 <OperandPtr> VALUE </OperandPtr>
228 </PREDICATE>
229 </PRED-RIGHT>
230 </PREDICATE>
231 </PRED-LEFT>
232 <PRED-RIGHT>
233 <PREDICATE>
234 <LogOp> NOT </LogOp>
235 <PRED-LEFT>
236 <PREDICATE>
237 <FieldName1> f3 </FieldName1>
238 <CompOp> Equals </CompOp>
239 <OperandPtr> VALUE </OperandPtr>
240 </PREDICATE>
241 </PRED-LEFT>
242 </PREDICATE>
243 </PRED-RIGHT>
244 </PREDICATE>
245 </TABLE-NODE>
246 echo explain plan select * from t1 where f1 in(1,2,3) and (f2 between 2 and 4) and not(f3=2);
247 <TABLE-NODE>
248 <NAME> t1 </NAME>
249 <ScanType> TableScan </ScanType>
250 <PREDICATE>
251 <LogOp> AND </LogOp>
252 <PRED-LEFT>
253 <PREDICATE>
254 <LogOp> AND </LogOp>
255 <PRED-LEFT>
256 <PREDICATE>
257 <LogOp> OR </LogOp>
258 <PRED-LEFT>
259 <PREDICATE>
260 <LogOp> OR </LogOp>
261 <PRED-LEFT>
262 <PREDICATE>
263 <FieldName1> f1 </FieldName1>
264 <CompOp> Equals </CompOp>
265 <OperandPtr> VALUE </OperandPtr>
266 </PREDICATE>
267 </PRED-LEFT>
268 <PRED-RIGHT>
269 <PREDICATE>
270 <FieldName1> f1 </FieldName1>
271 <CompOp> Equals </CompOp>
272 <OperandPtr> VALUE </OperandPtr>
273 </PREDICATE>
274 </PRED-RIGHT>
275 </PREDICATE>
276 </PRED-LEFT>
277 <PRED-RIGHT>
278 <PREDICATE>
279 <FieldName1> f1 </FieldName1>
280 <CompOp> Equals </CompOp>
281 <OperandPtr> VALUE </OperandPtr>
282 </PREDICATE>
283 </PRED-RIGHT>
284 </PREDICATE>
285 </PRED-LEFT>
286 <PRED-RIGHT>
287 <PREDICATE>
288 <FieldName1> f2 </FieldName1>
289 <CompOp> GreaterThanEquals </CompOp>
290 <OperandPtr> VALUE </OperandPtr>
291 <Comp2Op> LessThanEquals </Comp2Op>
292 <Operand2Ptr> VALUE </Operand2Ptr>
293 </PREDICATE>
294 </PRED-RIGHT>
295 </PREDICATE>
296 </PRED-LEFT>
297 <PRED-RIGHT>
298 <PREDICATE>
299 <LogOp> NOT </LogOp>
300 <PRED-LEFT>
301 <PREDICATE>
302 <FieldName1> f3 </FieldName1>
303 <CompOp> Equals </CompOp>
304 <OperandPtr> VALUE </OperandPtr>
305 </PREDICATE>
306 </PRED-LEFT>
307 </PREDICATE>
308 </PRED-RIGHT>
309 </PREDICATE>
310 </TABLE-NODE>
311 echo explain plan select * from t1 where f1 in(1,2,3) or (f2 between 2 and 4) or not(f3=2);
312 <TABLE-NODE>
313 <NAME> t1 </NAME>
314 <ScanType> TableScan </ScanType>
315 <PREDICATE>
316 <LogOp> OR </LogOp>
317 <PRED-LEFT>
318 <PREDICATE>
319 <LogOp> OR </LogOp>
320 <PRED-LEFT>
321 <PREDICATE>
322 <LogOp> OR </LogOp>
323 <PRED-LEFT>
324 <PREDICATE>
325 <LogOp> OR </LogOp>
326 <PRED-LEFT>
327 <PREDICATE>
328 <FieldName1> f1 </FieldName1>
329 <CompOp> Equals </CompOp>
330 <OperandPtr> VALUE </OperandPtr>
331 </PREDICATE>
332 </PRED-LEFT>
333 <PRED-RIGHT>
334 <PREDICATE>
335 <FieldName1> f1 </FieldName1>
336 <CompOp> Equals </CompOp>
337 <OperandPtr> VALUE </OperandPtr>
338 </PREDICATE>
339 </PRED-RIGHT>
340 </PREDICATE>
341 </PRED-LEFT>
342 <PRED-RIGHT>
343 <PREDICATE>
344 <FieldName1> f1 </FieldName1>
345 <CompOp> Equals </CompOp>
346 <OperandPtr> VALUE </OperandPtr>
347 </PREDICATE>
348 </PRED-RIGHT>
349 </PREDICATE>
350 </PRED-LEFT>
351 <PRED-RIGHT>
352 <PREDICATE>
353 <FieldName1> f2 </FieldName1>
354 <CompOp> GreaterThanEquals </CompOp>
355 <OperandPtr> VALUE </OperandPtr>
356 <Comp2Op> LessThanEquals </Comp2Op>
357 <Operand2Ptr> VALUE </Operand2Ptr>
358 </PREDICATE>
359 </PRED-RIGHT>
360 </PREDICATE>
361 </PRED-LEFT>
362 <PRED-RIGHT>
363 <PREDICATE>
364 <LogOp> NOT </LogOp>
365 <PRED-LEFT>
366 <PREDICATE>
367 <FieldName1> f3 </FieldName1>
368 <CompOp> Equals </CompOp>
369 <OperandPtr> VALUE </OperandPtr>
370 </PREDICATE>
371 </PRED-LEFT>
372 </PREDICATE>
373 </PRED-RIGHT>
374 </PREDICATE>
375 </TABLE-NODE>
376 echo explain plan select * from t1 where f1 in(1,2,3) or (f2 between 2 and 4) and not(f3=2);
377 <TABLE-NODE>
378 <NAME> t1 </NAME>
379 <ScanType> TableScan </ScanType>
380 <PREDICATE>
381 <LogOp> OR </LogOp>
382 <PRED-LEFT>
383 <PREDICATE>
384 <LogOp> OR </LogOp>
385 <PRED-LEFT>
386 <PREDICATE>
387 <LogOp> OR </LogOp>
388 <PRED-LEFT>
389 <PREDICATE>
390 <FieldName1> f1 </FieldName1>
391 <CompOp> Equals </CompOp>
392 <OperandPtr> VALUE </OperandPtr>
393 </PREDICATE>
394 </PRED-LEFT>
395 <PRED-RIGHT>
396 <PREDICATE>
397 <FieldName1> f1 </FieldName1>
398 <CompOp> Equals </CompOp>
399 <OperandPtr> VALUE </OperandPtr>
400 </PREDICATE>
401 </PRED-RIGHT>
402 </PREDICATE>
403 </PRED-LEFT>
404 <PRED-RIGHT>
405 <PREDICATE>
406 <FieldName1> f1 </FieldName1>
407 <CompOp> Equals </CompOp>
408 <OperandPtr> VALUE </OperandPtr>
409 </PREDICATE>
410 </PRED-RIGHT>
411 </PREDICATE>
412 </PRED-LEFT>
413 <PRED-RIGHT>
414 <PREDICATE>
415 <LogOp> AND </LogOp>
416 <PRED-LEFT>
417 <PREDICATE>
418 <FieldName1> f2 </FieldName1>
419 <CompOp> GreaterThanEquals </CompOp>
420 <OperandPtr> VALUE </OperandPtr>
421 <Comp2Op> LessThanEquals </Comp2Op>
422 <Operand2Ptr> VALUE </Operand2Ptr>
423 </PREDICATE>
424 </PRED-LEFT>
425 <PRED-RIGHT>
426 <PREDICATE>
427 <LogOp> NOT </LogOp>
428 <PRED-LEFT>
429 <PREDICATE>
430 <FieldName1> f3 </FieldName1>
431 <CompOp> Equals </CompOp>
432 <OperandPtr> VALUE </OperandPtr>
433 </PREDICATE>
434 </PRED-LEFT>
435 </PREDICATE>
436 </PRED-RIGHT>
437 </PREDICATE>
438 </PRED-RIGHT>
439 </PREDICATE>
440 </TABLE-NODE>
441 Statement Executed