code reorg for Transactionw!
[csql.git] / test / sql / Join / exp.test070.ksh
blob9c36771a0c0a657ba59e8f19d11019f0062b0292
1 echo create table t1(f1 int,f2 int);
2 Statement Executed
3 echo create index idx1 on t1(f1,f2) hash;
4 Statement Executed
5 echo create table t2(f1 int,f2 int);
6 Statement Executed
7 echo insert into t1 values(101,100);
8 Statement Executed: Rows Affected = 1
9 echo insert into t1 values(303,333);
10 Statement Executed: Rows Affected = 1
11 echo insert into t1 values(505,555);
12 Statement Executed: Rows Affected = 1
13 echo insert into t2 values(505,100);
14 Statement Executed: Rows Affected = 1
15 echo insert into t2 values(100,101);
16 Statement Executed: Rows Affected = 1
17 echo insert into t2 values(404,444);
18 Statement Executed: Rows Affected = 1
19 echo insert into t2 values(303,333);
20 Statement Executed: Rows Affected = 1
21 echo insert into t2 values(101,100);
22 Statement Executed: Rows Affected = 1
23 echo select * from t1;
24 ---------------------------------------------------------
25 t1.f1 t1.f2
26 ---------------------------------------------------------
27 101 100
28 303 333
29 505 555
31 echo select * from t2;
32 ---------------------------------------------------------
33 t2.f1 t2.f2
34 ---------------------------------------------------------
35 505 100
36 100 101
37 404 444
38 303 333
39 101 100
41 echo select * from t1,t2;
42 ---------------------------------------------------------
43 t1.f1 t1.f2 t2.f1 t2.f2
44 ---------------------------------------------------------
45 101 100 505 100
46 101 100 100 101
47 101 100 404 444
48 101 100 303 333
49 101 100 101 100
50 303 333 505 100
51 303 333 100 101
52 303 333 404 444
53 303 333 303 333
54 303 333 101 100
55 505 555 505 100
56 505 555 100 101
57 505 555 404 444
58 505 555 303 333
59 505 555 101 100
61 echo select * from t1,t2 where t1.f1=t2.f1 and t1.f2=t2.f2;
62 ---------------------------------------------------------
63 t1.f1 t1.f2 t2.f1 t2.f2
64 ---------------------------------------------------------
65 101 100 101 100
66 303 333 303 333
68 echo select * from t1,t2 where t1.f2=t2.f2 and t1.f1=t2.f1;
69 ---------------------------------------------------------
70 t1.f1 t1.f2 t2.f1 t2.f2
71 ---------------------------------------------------------
72 101 100 101 100
73 303 333 303 333
75 echo select * from t1,t2 where t1.f1>t2.f1 and t1.f2>t2.f2;
76 ---------------------------------------------------------
77 t1.f1 t1.f2 t2.f1 t2.f2
78 ---------------------------------------------------------
79 303 333 100 101
80 303 333 101 100
81 505 555 100 101
82 505 555 404 444
83 505 555 303 333
84 505 555 101 100
86 echo select * from t1,t2 where t1.f2<t2.f2 and t1.f1<=t2.f1;
87 ---------------------------------------------------------
88 t1.f1 t1.f2 t2.f1 t2.f2
89 ---------------------------------------------------------
90 101 100 404 444
91 101 100 303 333
92 303 333 404 444
94 echo select * from t1,t2 where t1.f1=t2.f1 or t1.f2=t2.f2;
95 ---------------------------------------------------------
96 t1.f1 t1.f2 t2.f1 t2.f2
97 ---------------------------------------------------------
98 101 100 505 100
99 101 100 101 100
100 303 333 303 333
101 505 555 505 100
103 echo select * from t1,t2 where t1.f2=t2.f2 or t1.f1=t2.f1;
104 ---------------------------------------------------------
105 t1.f1 t1.f2 t2.f1 t2.f2
106 ---------------------------------------------------------
107 101 100 505 100
108 101 100 101 100
109 303 333 303 333
110 505 555 505 100
112 echo select * from t1,t2 where t1.f1>t2.f1 or t1.f2>=t2.f2;
113 ---------------------------------------------------------
114 t1.f1 t1.f2 t2.f1 t2.f2
115 ---------------------------------------------------------
116 101 100 505 100
117 101 100 100 101
118 101 100 101 100
119 303 333 505 100
120 303 333 100 101
121 303 333 303 333
122 303 333 101 100
123 505 555 505 100
124 505 555 100 101
125 505 555 404 444
126 505 555 303 333
127 505 555 101 100
129 echo select * from t1,t2 where t1.f2<t2.f2 or t1.f1<t2.f1;
130 ---------------------------------------------------------
131 t1.f1 t1.f2 t2.f1 t2.f2
132 ---------------------------------------------------------
133 101 100 505 100
134 101 100 100 101
135 101 100 404 444
136 101 100 303 333
137 303 333 505 100
138 303 333 404 444
140 echo select * from t1,t2 where t1.f1>t2.f1 or t1.f2>t2.f2;
141 ---------------------------------------------------------
142 t1.f1 t1.f2 t2.f1 t2.f2
143 ---------------------------------------------------------
144 101 100 100 101
145 303 333 505 100
146 303 333 100 101
147 303 333 101 100
148 505 555 505 100
149 505 555 100 101
150 505 555 404 444
151 505 555 303 333
152 505 555 101 100
154 echo select * from t1,t2 where t1.f1=t2.f1 and not(t1.f2=t2.f2);
155 ---------------------------------------------------------
156 t1.f1 t1.f2 t2.f1 t2.f2
157 ---------------------------------------------------------
158 505 555 505 100
160 echo select * from t1,t2 where t1.f2=t2.f2 and not(t1.f1=t2.f1);
161 ---------------------------------------------------------
162 t1.f1 t1.f2 t2.f1 t2.f2
163 ---------------------------------------------------------
164 101 100 505 100
166 echo select * from t1,t2 where not(t1.f1>t2.f1) and t1.f2>t2.f2;
167 ---------------------------------------------------------
168 t1.f1 t1.f2 t2.f1 t2.f2
169 ---------------------------------------------------------
170 303 333 505 100
171 505 555 505 100
173 echo select * from t1,t2 where not(t1.f2<t2.f2) and not(t1.f1<t2.f1);
174 ---------------------------------------------------------
175 t1.f1 t1.f2 t2.f1 t2.f2
176 ---------------------------------------------------------
177 101 100 101 100
178 303 333 100 101
179 303 333 303 333
180 303 333 101 100
181 505 555 505 100
182 505 555 100 101
183 505 555 404 444
184 505 555 303 333
185 505 555 101 100
187 echo select * from t1,t2 where not(t1.f1=t2.f1) or not(t1.f2=t2.f2);
188 ---------------------------------------------------------
189 t1.f1 t1.f2 t2.f1 t2.f2
190 ---------------------------------------------------------
191 101 100 505 100
192 101 100 100 101
193 101 100 404 444
194 101 100 303 333
195 303 333 505 100
196 303 333 100 101
197 303 333 404 444
198 303 333 101 100
199 505 555 505 100
200 505 555 100 101
201 505 555 404 444
202 505 555 303 333
203 505 555 101 100
205 Statement Executed
206 Statement Executed