adding test scripts
[csql.git] / test / sql / Join / exp.test082.ksh
blobb53ba68f4357ed8a48a1e3f05fb6a42165d07bf1
1 echo create table t1(f1 int,f2 int);
2 Statement Executed
3 echo insert into t1 values(1,10);
4 Statement Executed: Rows Affected = 1
5 echo insert into t1 values(2,NULL);
6 Statement Executed: Rows Affected = 1
7 echo insert into t1 values(3,30);
8 Statement Executed: Rows Affected = 1
9 echo insert into t1 values(5,50);
10 Statement Executed: Rows Affected = 1
11 echo create table t2(f1 int,f2 int);
12 Statement Executed
13 echo insert into t2 values(1,NULL);
14 Statement Executed: Rows Affected = 1
15 echo insert into t2 values(2,20);
16 Statement Executed: Rows Affected = 1
17 echo insert into t2 values(3,30);
18 Statement Executed: Rows Affected = 1
19 echo insert into t2 values(4,40);
20 Statement Executed: Rows Affected = 1
21 echo select * from t1;
22 ---------------------------------------------------------
23 t1.f1 t1.f2
24 ---------------------------------------------------------
25 1 10
26 2 NULL
27 3 30
28 5 50
30 echo select * from t2;
31 ---------------------------------------------------------
32 t2.f1 t2.f2
33 ---------------------------------------------------------
34 1 NULL
35 2 20
36 3 30
37 4 40
39 echo select * from t1,t2;
40 ---------------------------------------------------------
41 t1.f1 t1.f2 t2.f1 t2.f2
42 ---------------------------------------------------------
43 1 10 1 NULL
44 1 10 2 20
45 1 10 3 30
46 1 10 4 40
47 2 NULL 1 NULL
48 2 NULL 2 20
49 2 NULL 3 30
50 2 NULL 4 40
51 3 30 1 NULL
52 3 30 2 20
53 3 30 3 30
54 3 30 4 40
55 5 50 1 NULL
56 5 50 2 20
57 5 50 3 30
58 5 50 4 40
60 echo select * from t1,t2 where t1.f1=t2.f1 and t1.f2=t2.f2;
61 ---------------------------------------------------------
62 t1.f1 t1.f2 t2.f1 t2.f2
63 ---------------------------------------------------------
64 3 30 3 30
66 echo select * from t1,t2 where t1.f2=t2.f2 and t1.f1=t2.f1;
67 ---------------------------------------------------------
68 t1.f1 t1.f2 t2.f1 t2.f2
69 ---------------------------------------------------------
70 3 30 3 30
72 echo select * from t1,t2 where t1.f1>t2.f1 and t1.f2>t2.f2;
73 ---------------------------------------------------------
74 t1.f1 t1.f2 t2.f1 t2.f2
75 ---------------------------------------------------------
76 3 30 2 20
77 5 50 2 20
78 5 50 3 30
79 5 50 4 40
81 echo select * from t1,t2 where t1.f2<t2.f2 and t1.f1<=t2.f1;
82 ---------------------------------------------------------
83 t1.f1 t1.f2 t2.f1 t2.f2
84 ---------------------------------------------------------
85 1 10 2 20
86 1 10 3 30
87 1 10 4 40
88 3 30 4 40
90 echo select * from t1,t2 where t1.f1=t2.f1 or t1.f2=t2.f2;
91 ---------------------------------------------------------
92 t1.f1 t1.f2 t2.f1 t2.f2
93 ---------------------------------------------------------
94 1 10 1 NULL
95 2 NULL 2 20
96 3 30 3 30
98 echo select * from t1,t2 where t1.f2=t2.f2 or t1.f1=t2.f1;
99 ---------------------------------------------------------
100 t1.f1 t1.f2 t2.f1 t2.f2
101 ---------------------------------------------------------
102 1 10 1 NULL
103 2 NULL 2 20
104 3 30 3 30
106 echo select * from t1,t2 where t1.f1>t2.f1 or t1.f2>=t2.f2;
107 ---------------------------------------------------------
108 t1.f1 t1.f2 t2.f1 t2.f2
109 ---------------------------------------------------------
110 2 NULL 1 NULL
111 3 30 1 NULL
112 3 30 2 20
113 3 30 3 30
114 5 50 1 NULL
115 5 50 2 20
116 5 50 3 30
117 5 50 4 40
119 echo select * from t1,t2 where t1.f2<t2.f2 or t1.f1<t2.f1;
120 ---------------------------------------------------------
121 t1.f1 t1.f2 t2.f1 t2.f2
122 ---------------------------------------------------------
123 1 10 2 20
124 1 10 3 30
125 1 10 4 40
126 2 NULL 3 30
127 2 NULL 4 40
128 3 30 4 40
130 echo select * from t1,t2 where t1.f1>t2.f1 or t1.f2>t2.f2;
131 ---------------------------------------------------------
132 t1.f1 t1.f2 t2.f1 t2.f2
133 ---------------------------------------------------------
134 2 NULL 1 NULL
135 3 30 1 NULL
136 3 30 2 20
137 5 50 1 NULL
138 5 50 2 20
139 5 50 3 30
140 5 50 4 40
142 echo select * from t1,t2 where t1.f1=t2.f1 and not(t1.f2=t2.f2);
143 ---------------------------------------------------------
144 t1.f1 t1.f2 t2.f1 t2.f2
145 ---------------------------------------------------------
147 echo select * from t1,t2 where t1.f2=t2.f2 and not(t1.f1=t2.f1);
148 ---------------------------------------------------------
149 t1.f1 t1.f2 t2.f1 t2.f2
150 ---------------------------------------------------------
152 echo select * from t1,t2 where not(t1.f1>t2.f1) and t1.f2>t2.f2;
153 ---------------------------------------------------------
154 t1.f1 t1.f2 t2.f1 t2.f2
155 ---------------------------------------------------------
157 echo select * from t1,t2 where not(t1.f2<t2.f2) and not(t1.f1<t2.f1);
158 ---------------------------------------------------------
159 t1.f1 t1.f2 t2.f1 t2.f2
160 ---------------------------------------------------------
161 3 30 2 20
162 3 30 3 30
163 5 50 2 20
164 5 50 3 30
165 5 50 4 40
167 echo select * from t1,t2 where not(t1.f1=t2.f1) or not(t1.f2=t2.f2);
168 ---------------------------------------------------------
169 t1.f1 t1.f2 t2.f1 t2.f2
170 ---------------------------------------------------------
171 1 10 2 20
172 1 10 3 30
173 1 10 4 40
174 2 NULL 1 NULL
175 2 NULL 3 30
176 2 NULL 4 40
177 3 30 1 NULL
178 3 30 2 20
179 3 30 4 40
180 5 50 1 NULL
181 5 50 2 20
182 5 50 3 30
183 5 50 4 40
185 Statement Executed
186 Statement Executed