Debian Codegraph @

mysql-test/suite/engines/funcs/t/fu_aggregate_avg_number.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3,t4;
3 --enable_warnings
4 CREATE TABLE t1(c1 TINYINT NOT NULL);
5 CREATE TABLE t2(c1 TINYINT NULL);
6 CREATE TABLE t3(c1 TINYINT NOT NULL);
7 CREATE TABLE t4(c1 TINYINT NULL);
8 INSERT INTO t2 (c1) VALUES(NULL);
9 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
10 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
11 SELECT AVG(c1) AS value FROM t1;
12 SELECT AVG(c1) AS value FROM t1 WHERE c1 < 0;
13 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
14 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
15 DROP TABLE t1,t2,t3,t4;
16 CREATE TABLE t1(c1 SMALLINT NOT NULL);
17 CREATE TABLE t2(c1 SMALLINT NULL);
18 CREATE TABLE t3(c1 SMALLINT NOT NULL);
19 CREATE TABLE t4(c1 SMALLINT NULL);
20 INSERT INTO t2 (c1) VALUES(NULL);
21 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
22 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
23 SELECT AVG(c1) AS value FROM t1;
24 SELECT AVG(c1) AS value FROM t1 WHERE c1 < 0;
25 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
26 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
27 DROP TABLE t1,t2,t3,t4;
28 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
29 CREATE TABLE t2(c1 MEDIUMINT NULL);
30 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
31 CREATE TABLE t4(c1 MEDIUMINT NULL);
32 INSERT INTO t2 (c1) VALUES(NULL);
33 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
34 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
35 SELECT AVG(c1) AS value FROM t1;
36 SELECT AVG(c1) AS value FROM t1 WHERE c1 < 0;
37 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
38 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
39 DROP TABLE t1,t2,t3,t4;
40 CREATE TABLE t1(c1 INT NOT NULL);
41 CREATE TABLE t2(c1 INT NULL);
42 CREATE TABLE t3(c1 INT NOT NULL);
43 CREATE TABLE t4(c1 INT NULL);
44 INSERT INTO t2 (c1) VALUES(NULL);
45 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
46 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
47 SELECT AVG(c1) AS value FROM t1;
48 SELECT AVG(c1) AS value FROM t1 WHERE c1 < 0;
49 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
50 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
51 DROP TABLE t1,t2,t3,t4;
52 CREATE TABLE t1(c1 INTEGER NOT NULL);
53 CREATE TABLE t2(c1 INTEGER NULL);
54 CREATE TABLE t3(c1 INTEGER NOT NULL);
55 CREATE TABLE t4(c1 INTEGER NULL);
56 INSERT INTO t2 (c1) VALUES(NULL);
57 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
58 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
59 SELECT AVG(c1) AS value FROM t1;
60 SELECT AVG(c1) AS value FROM t1 WHERE c1 < 0;
61 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
62 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
63 DROP TABLE t1,t2,t3,t4;
64 CREATE TABLE t1(c1 BIGINT NOT NULL);
65 CREATE TABLE t2(c1 BIGINT NULL);
66 CREATE TABLE t3(c1 BIGINT NOT NULL);
67 CREATE TABLE t4(c1 BIGINT NULL);
68 INSERT INTO t2 (c1) VALUES(NULL);
69 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
70 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
71 SELECT AVG(c1) AS value FROM t1;
72 SELECT AVG(c1) AS value FROM t1 WHERE c1 < 0;
73 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
74 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
75 DROP TABLE t1,t2,t3,t4;
76 CREATE TABLE t1(c1 TINYINT NOT NULL);
77 CREATE TABLE t2(c1 TINYINT NULL);
78 CREATE TABLE t3(c1 TINYINT NOT NULL);
79 CREATE TABLE t4(c1 TINYINT NULL);
80 INSERT INTO t2 (c1) VALUES(NULL);
81 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
82 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
83 SELECT AVG(c1) AS value FROM t2;
84 SELECT AVG(c1) AS value FROM t2 WHERE c1 < 0;
85 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
86 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
87 DROP TABLE t1,t2,t3,t4;
88 CREATE TABLE t1(c1 SMALLINT NOT NULL);
89 CREATE TABLE t2(c1 SMALLINT NULL);
90 CREATE TABLE t3(c1 SMALLINT NOT NULL);
91 CREATE TABLE t4(c1 SMALLINT NULL);
92 INSERT INTO t2 (c1) VALUES(NULL);
93 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
94 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
95 SELECT AVG(c1) AS value FROM t2;
96 SELECT AVG(c1) AS value FROM t2 WHERE c1 < 0;
97 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
98 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
99 DROP TABLE t1,t2,t3,t4;
100 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
101 CREATE TABLE t2(c1 MEDIUMINT NULL);
102 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
103 CREATE TABLE t4(c1 MEDIUMINT NULL);
104 INSERT INTO t2 (c1) VALUES(NULL);
105 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
106 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
107 SELECT AVG(c1) AS value FROM t2;
108 SELECT AVG(c1) AS value FROM t2 WHERE c1 < 0;
109 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
110 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
111 DROP TABLE t1,t2,t3,t4;
112 CREATE TABLE t1(c1 INT NOT NULL);
113 CREATE TABLE t2(c1 INT NULL);
114 CREATE TABLE t3(c1 INT NOT NULL);
115 CREATE TABLE t4(c1 INT NULL);
116 INSERT INTO t2 (c1) VALUES(NULL);
117 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
118 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
119 SELECT AVG(c1) AS value FROM t2;
120 SELECT AVG(c1) AS value FROM t2 WHERE c1 < 0;
121 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
122 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
123 DROP TABLE t1,t2,t3,t4;
124 CREATE TABLE t1(c1 INTEGER NOT NULL);
125 CREATE TABLE t2(c1 INTEGER NULL);
126 CREATE TABLE t3(c1 INTEGER NOT NULL);
127 CREATE TABLE t4(c1 INTEGER NULL);
128 INSERT INTO t2 (c1) VALUES(NULL);
129 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
130 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
131 SELECT AVG(c1) AS value FROM t2;
132 SELECT AVG(c1) AS value FROM t2 WHERE c1 < 0;
133 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
134 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
135 DROP TABLE t1,t2,t3,t4;
136 CREATE TABLE t1(c1 BIGINT NOT NULL);
137 CREATE TABLE t2(c1 BIGINT NULL);
138 CREATE TABLE t3(c1 BIGINT NOT NULL);
139 CREATE TABLE t4(c1 BIGINT NULL);
140 INSERT INTO t2 (c1) VALUES(NULL);
141 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
142 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
143 SELECT AVG(c1) AS value FROM t2;
144 SELECT AVG(c1) AS value FROM t2 WHERE c1 < 0;
145 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
146 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
147 DROP TABLE t1,t2,t3,t4;
148 CREATE TABLE t1(c1 TINYINT NOT NULL);
149 CREATE TABLE t2(c1 TINYINT NULL);
150 CREATE TABLE t3(c1 TINYINT NOT NULL);
151 CREATE TABLE t4(c1 TINYINT NULL);
152 INSERT INTO t2 (c1) VALUES(NULL);
153 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
154 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
155 SELECT AVG(c1) AS value FROM t3;
156 SELECT AVG(c1) AS value FROM t3 WHERE c1 < 0;
157 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
158 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
159 DROP TABLE t1,t2,t3,t4;
160 CREATE TABLE t1(c1 SMALLINT NOT NULL);
161 CREATE TABLE t2(c1 SMALLINT NULL);
162 CREATE TABLE t3(c1 SMALLINT NOT NULL);
163 CREATE TABLE t4(c1 SMALLINT NULL);
164 INSERT INTO t2 (c1) VALUES(NULL);
165 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
166 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
167 SELECT AVG(c1) AS value FROM t3;
168 SELECT AVG(c1) AS value FROM t3 WHERE c1 < 0;
169 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
170 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
171 DROP TABLE t1,t2,t3,t4;
172 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
173 CREATE TABLE t2(c1 MEDIUMINT NULL);
174 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
175 CREATE TABLE t4(c1 MEDIUMINT NULL);
176 INSERT INTO t2 (c1) VALUES(NULL);
177 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
178 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
179 SELECT AVG(c1) AS value FROM t3;
180 SELECT AVG(c1) AS value FROM t3 WHERE c1 < 0;
181 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
182 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
183 DROP TABLE t1,t2,t3,t4;
184 CREATE TABLE t1(c1 INT NOT NULL);
185 CREATE TABLE t2(c1 INT NULL);
186 CREATE TABLE t3(c1 INT NOT NULL);
187 CREATE TABLE t4(c1 INT NULL);
188 INSERT INTO t2 (c1) VALUES(NULL);
189 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
190 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
191 SELECT AVG(c1) AS value FROM t3;
192 SELECT AVG(c1) AS value FROM t3 WHERE c1 < 0;
193 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
194 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
195 DROP TABLE t1,t2,t3,t4;
196 CREATE TABLE t1(c1 INTEGER NOT NULL);
197 CREATE TABLE t2(c1 INTEGER NULL);
198 CREATE TABLE t3(c1 INTEGER NOT NULL);
199 CREATE TABLE t4(c1 INTEGER NULL);
200 INSERT INTO t2 (c1) VALUES(NULL);
201 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
202 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
203 SELECT AVG(c1) AS value FROM t3;
204 SELECT AVG(c1) AS value FROM t3 WHERE c1 < 0;
205 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
206 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
207 DROP TABLE t1,t2,t3,t4;
208 CREATE TABLE t1(c1 BIGINT NOT NULL);
209 CREATE TABLE t2(c1 BIGINT NULL);
210 CREATE TABLE t3(c1 BIGINT NOT NULL);
211 CREATE TABLE t4(c1 BIGINT NULL);
212 INSERT INTO t2 (c1) VALUES(NULL);
213 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
214 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
215 SELECT AVG(c1) AS value FROM t3;
216 SELECT AVG(c1) AS value FROM t3 WHERE c1 < 0;
217 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
218 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
219 DROP TABLE t1,t2,t3,t4;
220 CREATE TABLE t1(c1 TINYINT NOT NULL);
221 CREATE TABLE t2(c1 TINYINT NULL);
222 CREATE TABLE t3(c1 TINYINT NOT NULL);
223 CREATE TABLE t4(c1 TINYINT NULL);
224 INSERT INTO t2 (c1) VALUES(NULL);
225 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
226 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
227 SELECT AVG(c1) AS value FROM t4;
228 SELECT AVG(c1) AS value FROM t4 WHERE c1 < 0;
229 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
230 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
231 DROP TABLE t1,t2,t3,t4;
232 CREATE TABLE t1(c1 SMALLINT NOT NULL);
233 CREATE TABLE t2(c1 SMALLINT NULL);
234 CREATE TABLE t3(c1 SMALLINT NOT NULL);
235 CREATE TABLE t4(c1 SMALLINT NULL);
236 INSERT INTO t2 (c1) VALUES(NULL);
237 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
238 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
239 SELECT AVG(c1) AS value FROM t4;
240 SELECT AVG(c1) AS value FROM t4 WHERE c1 < 0;
241 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
242 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
243 DROP TABLE t1,t2,t3,t4;
244 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
245 CREATE TABLE t2(c1 MEDIUMINT NULL);
246 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
247 CREATE TABLE t4(c1 MEDIUMINT NULL);
248 INSERT INTO t2 (c1) VALUES(NULL);
249 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
250 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
251 SELECT AVG(c1) AS value FROM t4;
252 SELECT AVG(c1) AS value FROM t4 WHERE c1 < 0;
253 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
254 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
255 DROP TABLE t1,t2,t3,t4;
256 CREATE TABLE t1(c1 INT NOT NULL);
257 CREATE TABLE t2(c1 INT NULL);
258 CREATE TABLE t3(c1 INT NOT NULL);
259 CREATE TABLE t4(c1 INT NULL);
260 INSERT INTO t2 (c1) VALUES(NULL);
261 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
262 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
263 SELECT AVG(c1) AS value FROM t4;
264 SELECT AVG(c1) AS value FROM t4 WHERE c1 < 0;
265 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
266 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
267 DROP TABLE t1,t2,t3,t4;
268 CREATE TABLE t1(c1 INTEGER NOT NULL);
269 CREATE TABLE t2(c1 INTEGER NULL);
270 CREATE TABLE t3(c1 INTEGER NOT NULL);
271 CREATE TABLE t4(c1 INTEGER NULL);
272 INSERT INTO t2 (c1) VALUES(NULL);
273 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
274 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
275 SELECT AVG(c1) AS value FROM t4;
276 SELECT AVG(c1) AS value FROM t4 WHERE c1 < 0;
277 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
278 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
279 DROP TABLE t1,t2,t3,t4;
280 CREATE TABLE t1(c1 BIGINT NOT NULL);
281 CREATE TABLE t2(c1 BIGINT NULL);
282 CREATE TABLE t3(c1 BIGINT NOT NULL);
283 CREATE TABLE t4(c1 BIGINT NULL);
284 INSERT INTO t2 (c1) VALUES(NULL);
285 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
286 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
287 SELECT AVG(c1) AS value FROM t4;
288 SELECT AVG(c1) AS value FROM t4 WHERE c1 < 0;
289 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
290 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
291 DROP TABLE t1,t2,t3,t4;
292 CREATE TABLE t1(c1 TINYINT NOT NULL);
293 CREATE TABLE t2(c1 TINYINT NULL);
294 CREATE TABLE t3(c1 TINYINT NOT NULL);
295 CREATE TABLE t4(c1 TINYINT NULL);
296 INSERT INTO t2 (c1) VALUES(NULL);
297 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
298 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
299 SELECT AVG(c1) AS value FROM t1;
300 SELECT AVG(c1) AS value FROM t1 WHERE c1 <= 0;
301 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
302 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
303 DROP TABLE t1,t2,t3,t4;
304 CREATE TABLE t1(c1 SMALLINT NOT NULL);
305 CREATE TABLE t2(c1 SMALLINT NULL);
306 CREATE TABLE t3(c1 SMALLINT NOT NULL);
307 CREATE TABLE t4(c1 SMALLINT NULL);
308 INSERT INTO t2 (c1) VALUES(NULL);
309 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
310 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
311 SELECT AVG(c1) AS value FROM t1;
312 SELECT AVG(c1) AS value FROM t1 WHERE c1 <= 0;
313 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
314 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
315 DROP TABLE t1,t2,t3,t4;
316 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
317 CREATE TABLE t2(c1 MEDIUMINT NULL);
318 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
319 CREATE TABLE t4(c1 MEDIUMINT NULL);
320 INSERT INTO t2 (c1) VALUES(NULL);
321 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
322 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
323 SELECT AVG(c1) AS value FROM t1;
324 SELECT AVG(c1) AS value FROM t1 WHERE c1 <= 0;
325 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
326 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
327 DROP TABLE t1,t2,t3,t4;
328 CREATE TABLE t1(c1 INT NOT NULL);
329 CREATE TABLE t2(c1 INT NULL);
330 CREATE TABLE t3(c1 INT NOT NULL);
331 CREATE TABLE t4(c1 INT NULL);
332 INSERT INTO t2 (c1) VALUES(NULL);
333 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
334 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
335 SELECT AVG(c1) AS value FROM t1;
336 SELECT AVG(c1) AS value FROM t1 WHERE c1 <= 0;
337 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
338 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
339 DROP TABLE t1,t2,t3,t4;
340 CREATE TABLE t1(c1 INTEGER NOT NULL);
341 CREATE TABLE t2(c1 INTEGER NULL);
342 CREATE TABLE t3(c1 INTEGER NOT NULL);
343 CREATE TABLE t4(c1 INTEGER NULL);
344 INSERT INTO t2 (c1) VALUES(NULL);
345 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
346 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
347 SELECT AVG(c1) AS value FROM t1;
348 SELECT AVG(c1) AS value FROM t1 WHERE c1 <= 0;
349 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
350 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
351 DROP TABLE t1,t2,t3,t4;
352 CREATE TABLE t1(c1 BIGINT NOT NULL);
353 CREATE TABLE t2(c1 BIGINT NULL);
354 CREATE TABLE t3(c1 BIGINT NOT NULL);
355 CREATE TABLE t4(c1 BIGINT NULL);
356 INSERT INTO t2 (c1) VALUES(NULL);
357 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
358 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
359 SELECT AVG(c1) AS value FROM t1;
360 SELECT AVG(c1) AS value FROM t1 WHERE c1 <= 0;
361 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
362 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
363 DROP TABLE t1,t2,t3,t4;
364 CREATE TABLE t1(c1 TINYINT NOT NULL);
365 CREATE TABLE t2(c1 TINYINT NULL);
366 CREATE TABLE t3(c1 TINYINT NOT NULL);
367 CREATE TABLE t4(c1 TINYINT NULL);
368 INSERT INTO t2 (c1) VALUES(NULL);
369 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
370 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
371 SELECT AVG(c1) AS value FROM t2;
372 SELECT AVG(c1) AS value FROM t2 WHERE c1 <= 0;
373 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
374 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
375 DROP TABLE t1,t2,t3,t4;
376 CREATE TABLE t1(c1 SMALLINT NOT NULL);
377 CREATE TABLE t2(c1 SMALLINT NULL);
378 CREATE TABLE t3(c1 SMALLINT NOT NULL);
379 CREATE TABLE t4(c1 SMALLINT NULL);
380 INSERT INTO t2 (c1) VALUES(NULL);
381 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
382 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
383 SELECT AVG(c1) AS value FROM t2;
384 SELECT AVG(c1) AS value FROM t2 WHERE c1 <= 0;
385 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
386 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
387 DROP TABLE t1,t2,t3,t4;
388 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
389 CREATE TABLE t2(c1 MEDIUMINT NULL);
390 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
391 CREATE TABLE t4(c1 MEDIUMINT NULL);
392 INSERT INTO t2 (c1) VALUES(NULL);
393 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
394 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
395 SELECT AVG(c1) AS value FROM t2;
396 SELECT AVG(c1) AS value FROM t2 WHERE c1 <= 0;
397 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
398 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
399 DROP TABLE t1,t2,t3,t4;
400 CREATE TABLE t1(c1 INT NOT NULL);
401 CREATE TABLE t2(c1 INT NULL);
402 CREATE TABLE t3(c1 INT NOT NULL);
403 CREATE TABLE t4(c1 INT NULL);
404 INSERT INTO t2 (c1) VALUES(NULL);
405 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
406 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
407 SELECT AVG(c1) AS value FROM t2;
408 SELECT AVG(c1) AS value FROM t2 WHERE c1 <= 0;
409 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
410 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
411 DROP TABLE t1,t2,t3,t4;
412 CREATE TABLE t1(c1 INTEGER NOT NULL);
413 CREATE TABLE t2(c1 INTEGER NULL);
414 CREATE TABLE t3(c1 INTEGER NOT NULL);
415 CREATE TABLE t4(c1 INTEGER NULL);
416 INSERT INTO t2 (c1) VALUES(NULL);
417 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
418 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
419 SELECT AVG(c1) AS value FROM t2;
420 SELECT AVG(c1) AS value FROM t2 WHERE c1 <= 0;
421 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
422 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
423 DROP TABLE t1,t2,t3,t4;
424 CREATE TABLE t1(c1 BIGINT NOT NULL);
425 CREATE TABLE t2(c1 BIGINT NULL);
426 CREATE TABLE t3(c1 BIGINT NOT NULL);
427 CREATE TABLE t4(c1 BIGINT NULL);
428 INSERT INTO t2 (c1) VALUES(NULL);
429 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
430 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
431 SELECT AVG(c1) AS value FROM t2;
432 SELECT AVG(c1) AS value FROM t2 WHERE c1 <= 0;
433 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
434 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
435 DROP TABLE t1,t2,t3,t4;
436 CREATE TABLE t1(c1 TINYINT NOT NULL);
437 CREATE TABLE t2(c1 TINYINT NULL);
438 CREATE TABLE t3(c1 TINYINT NOT NULL);
439 CREATE TABLE t4(c1 TINYINT NULL);
440 INSERT INTO t2 (c1) VALUES(NULL);
441 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
442 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
443 SELECT AVG(c1) AS value FROM t3;
444 SELECT AVG(c1) AS value FROM t3 WHERE c1 <= 0;
445 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
446 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
447 DROP TABLE t1,t2,t3,t4;
448 CREATE TABLE t1(c1 SMALLINT NOT NULL);
449 CREATE TABLE t2(c1 SMALLINT NULL);
450 CREATE TABLE t3(c1 SMALLINT NOT NULL);
451 CREATE TABLE t4(c1 SMALLINT NULL);
452 INSERT INTO t2 (c1) VALUES(NULL);
453 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
454 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
455 SELECT AVG(c1) AS value FROM t3;
456 SELECT AVG(c1) AS value FROM t3 WHERE c1 <= 0;
457 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
458 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
459 DROP TABLE t1,t2,t3,t4;
460 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
461 CREATE TABLE t2(c1 MEDIUMINT NULL);
462 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
463 CREATE TABLE t4(c1 MEDIUMINT NULL);
464 INSERT INTO t2 (c1) VALUES(NULL);
465 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
466 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
467 SELECT AVG(c1) AS value FROM t3;
468 SELECT AVG(c1) AS value FROM t3 WHERE c1 <= 0;
469 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
470 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
471 DROP TABLE t1,t2,t3,t4;
472 CREATE TABLE t1(c1 INT NOT NULL);
473 CREATE TABLE t2(c1 INT NULL);
474 CREATE TABLE t3(c1 INT NOT NULL);
475 CREATE TABLE t4(c1 INT NULL);
476 INSERT INTO t2 (c1) VALUES(NULL);
477 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
478 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
479 SELECT AVG(c1) AS value FROM t3;
480 SELECT AVG(c1) AS value FROM t3 WHERE c1 <= 0;
481 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
482 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
483 DROP TABLE t1,t2,t3,t4;
484 CREATE TABLE t1(c1 INTEGER NOT NULL);
485 CREATE TABLE t2(c1 INTEGER NULL);
486 CREATE TABLE t3(c1 INTEGER NOT NULL);
487 CREATE TABLE t4(c1 INTEGER NULL);
488 INSERT INTO t2 (c1) VALUES(NULL);
489 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
490 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
491 SELECT AVG(c1) AS value FROM t3;
492 SELECT AVG(c1) AS value FROM t3 WHERE c1 <= 0;
493 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
494 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
495 DROP TABLE t1,t2,t3,t4;
496 CREATE TABLE t1(c1 BIGINT NOT NULL);
497 CREATE TABLE t2(c1 BIGINT NULL);
498 CREATE TABLE t3(c1 BIGINT NOT NULL);
499 CREATE TABLE t4(c1 BIGINT NULL);
500 INSERT INTO t2 (c1) VALUES(NULL);
501 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
502 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
503 SELECT AVG(c1) AS value FROM t3;
504 SELECT AVG(c1) AS value FROM t3 WHERE c1 <= 0;
505 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
506 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
507 DROP TABLE t1,t2,t3,t4;
508 CREATE TABLE t1(c1 TINYINT NOT NULL);
509 CREATE TABLE t2(c1 TINYINT NULL);
510 CREATE TABLE t3(c1 TINYINT NOT NULL);
511 CREATE TABLE t4(c1 TINYINT NULL);
512 INSERT INTO t2 (c1) VALUES(NULL);
513 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
514 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
515 SELECT AVG(c1) AS value FROM t4;
516 SELECT AVG(c1) AS value FROM t4 WHERE c1 <= 0;
517 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
518 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
519 DROP TABLE t1,t2,t3,t4;
520 CREATE TABLE t1(c1 SMALLINT NOT NULL);
521 CREATE TABLE t2(c1 SMALLINT NULL);
522 CREATE TABLE t3(c1 SMALLINT NOT NULL);
523 CREATE TABLE t4(c1 SMALLINT NULL);
524 INSERT INTO t2 (c1) VALUES(NULL);
525 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
526 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
527 SELECT AVG(c1) AS value FROM t4;
528 SELECT AVG(c1) AS value FROM t4 WHERE c1 <= 0;
529 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
530 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
531 DROP TABLE t1,t2,t3,t4;
532 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
533 CREATE TABLE t2(c1 MEDIUMINT NULL);
534 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
535 CREATE TABLE t4(c1 MEDIUMINT NULL);
536 INSERT INTO t2 (c1) VALUES(NULL);
537 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
538 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
539 SELECT AVG(c1) AS value FROM t4;
540 SELECT AVG(c1) AS value FROM t4 WHERE c1 <= 0;
541 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
542 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
543 DROP TABLE t1,t2,t3,t4;
544 CREATE TABLE t1(c1 INT NOT NULL);
545 CREATE TABLE t2(c1 INT NULL);
546 CREATE TABLE t3(c1 INT NOT NULL);
547 CREATE TABLE t4(c1 INT NULL);
548 INSERT INTO t2 (c1) VALUES(NULL);
549 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
550 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
551 SELECT AVG(c1) AS value FROM t4;
552 SELECT AVG(c1) AS value FROM t4 WHERE c1 <= 0;
553 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
554 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
555 DROP TABLE t1,t2,t3,t4;
556 CREATE TABLE t1(c1 INTEGER NOT NULL);
557 CREATE TABLE t2(c1 INTEGER NULL);
558 CREATE TABLE t3(c1 INTEGER NOT NULL);
559 CREATE TABLE t4(c1 INTEGER NULL);
560 INSERT INTO t2 (c1) VALUES(NULL);
561 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
562 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
563 SELECT AVG(c1) AS value FROM t4;
564 SELECT AVG(c1) AS value FROM t4 WHERE c1 <= 0;
565 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
566 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
567 DROP TABLE t1,t2,t3,t4;
568 CREATE TABLE t1(c1 BIGINT NOT NULL);
569 CREATE TABLE t2(c1 BIGINT NULL);
570 CREATE TABLE t3(c1 BIGINT NOT NULL);
571 CREATE TABLE t4(c1 BIGINT NULL);
572 INSERT INTO t2 (c1) VALUES(NULL);
573 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
574 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
575 SELECT AVG(c1) AS value FROM t4;
576 SELECT AVG(c1) AS value FROM t4 WHERE c1 <= 0;
577 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
578 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
579 DROP TABLE t1,t2,t3,t4;
580 CREATE TABLE t1(c1 TINYINT NOT NULL);
581 CREATE TABLE t2(c1 TINYINT NULL);
582 CREATE TABLE t3(c1 TINYINT NOT NULL);
583 CREATE TABLE t4(c1 TINYINT NULL);
584 INSERT INTO t2 (c1) VALUES(NULL);
585 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
586 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
587 SELECT AVG(c1) AS value FROM t1;
588 SELECT AVG(c1) AS value FROM t1 WHERE c1 = 0;
589 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
590 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
591 DROP TABLE t1,t2,t3,t4;
592 CREATE TABLE t1(c1 SMALLINT NOT NULL);
593 CREATE TABLE t2(c1 SMALLINT NULL);
594 CREATE TABLE t3(c1 SMALLINT NOT NULL);
595 CREATE TABLE t4(c1 SMALLINT NULL);
596 INSERT INTO t2 (c1) VALUES(NULL);
597 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
598 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
599 SELECT AVG(c1) AS value FROM t1;
600 SELECT AVG(c1) AS value FROM t1 WHERE c1 = 0;
601 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
602 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
603 DROP TABLE t1,t2,t3,t4;
604 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
605 CREATE TABLE t2(c1 MEDIUMINT NULL);
606 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
607 CREATE TABLE t4(c1 MEDIUMINT NULL);
608 INSERT INTO t2 (c1) VALUES(NULL);
609 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
610 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
611 SELECT AVG(c1) AS value FROM t1;
612 SELECT AVG(c1) AS value FROM t1 WHERE c1 = 0;
613 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
614 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
615 DROP TABLE t1,t2,t3,t4;
616 CREATE TABLE t1(c1 INT NOT NULL);
617 CREATE TABLE t2(c1 INT NULL);
618 CREATE TABLE t3(c1 INT NOT NULL);
619 CREATE TABLE t4(c1 INT NULL);
620 INSERT INTO t2 (c1) VALUES(NULL);
621 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
622 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
623 SELECT AVG(c1) AS value FROM t1;
624 SELECT AVG(c1) AS value FROM t1 WHERE c1 = 0;
625 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
626 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
627 DROP TABLE t1,t2,t3,t4;
628 CREATE TABLE t1(c1 INTEGER NOT NULL);
629 CREATE TABLE t2(c1 INTEGER NULL);
630 CREATE TABLE t3(c1 INTEGER NOT NULL);
631 CREATE TABLE t4(c1 INTEGER NULL);
632 INSERT INTO t2 (c1) VALUES(NULL);
633 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
634 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
635 SELECT AVG(c1) AS value FROM t1;
636 SELECT AVG(c1) AS value FROM t1 WHERE c1 = 0;
637 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
638 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
639 DROP TABLE t1,t2,t3,t4;
640 CREATE TABLE t1(c1 BIGINT NOT NULL);
641 CREATE TABLE t2(c1 BIGINT NULL);
642 CREATE TABLE t3(c1 BIGINT NOT NULL);
643 CREATE TABLE t4(c1 BIGINT NULL);
644 INSERT INTO t2 (c1) VALUES(NULL);
645 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
646 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
647 SELECT AVG(c1) AS value FROM t1;
648 SELECT AVG(c1) AS value FROM t1 WHERE c1 = 0;
649 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
650 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
651 DROP TABLE t1,t2,t3,t4;
652 CREATE TABLE t1(c1 TINYINT NOT NULL);
653 CREATE TABLE t2(c1 TINYINT NULL);
654 CREATE TABLE t3(c1 TINYINT NOT NULL);
655 CREATE TABLE t4(c1 TINYINT NULL);
656 INSERT INTO t2 (c1) VALUES(NULL);
657 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
658 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
659 SELECT AVG(c1) AS value FROM t2;
660 SELECT AVG(c1) AS value FROM t2 WHERE c1 = 0;
661 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
662 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
663 DROP TABLE t1,t2,t3,t4;
664 CREATE TABLE t1(c1 SMALLINT NOT NULL);
665 CREATE TABLE t2(c1 SMALLINT NULL);
666 CREATE TABLE t3(c1 SMALLINT NOT NULL);
667 CREATE TABLE t4(c1 SMALLINT NULL);
668 INSERT INTO t2 (c1) VALUES(NULL);
669 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
670 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
671 SELECT AVG(c1) AS value FROM t2;
672 SELECT AVG(c1) AS value FROM t2 WHERE c1 = 0;
673 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
674 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
675 DROP TABLE t1,t2,t3,t4;
676 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
677 CREATE TABLE t2(c1 MEDIUMINT NULL);
678 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
679 CREATE TABLE t4(c1 MEDIUMINT NULL);
680 INSERT INTO t2 (c1) VALUES(NULL);
681 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
682 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
683 SELECT AVG(c1) AS value FROM t2;
684 SELECT AVG(c1) AS value FROM t2 WHERE c1 = 0;
685 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
686 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
687 DROP TABLE t1,t2,t3,t4;
688 CREATE TABLE t1(c1 INT NOT NULL);
689 CREATE TABLE t2(c1 INT NULL);
690 CREATE TABLE t3(c1 INT NOT NULL);
691 CREATE TABLE t4(c1 INT NULL);
692 INSERT INTO t2 (c1) VALUES(NULL);
693 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
694 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
695 SELECT AVG(c1) AS value FROM t2;
696 SELECT AVG(c1) AS value FROM t2 WHERE c1 = 0;
697 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
698 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
699 DROP TABLE t1,t2,t3,t4;
700 CREATE TABLE t1(c1 INTEGER NOT NULL);
701 CREATE TABLE t2(c1 INTEGER NULL);
702 CREATE TABLE t3(c1 INTEGER NOT NULL);
703 CREATE TABLE t4(c1 INTEGER NULL);
704 INSERT INTO t2 (c1) VALUES(NULL);
705 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
706 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
707 SELECT AVG(c1) AS value FROM t2;
708 SELECT AVG(c1) AS value FROM t2 WHERE c1 = 0;
709 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
710 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
711 DROP TABLE t1,t2,t3,t4;
712 CREATE TABLE t1(c1 BIGINT NOT NULL);
713 CREATE TABLE t2(c1 BIGINT NULL);
714 CREATE TABLE t3(c1 BIGINT NOT NULL);
715 CREATE TABLE t4(c1 BIGINT NULL);
716 INSERT INTO t2 (c1) VALUES(NULL);
717 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
718 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
719 SELECT AVG(c1) AS value FROM t2;
720 SELECT AVG(c1) AS value FROM t2 WHERE c1 = 0;
721 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
722 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
723 DROP TABLE t1,t2,t3,t4;
724 CREATE TABLE t1(c1 TINYINT NOT NULL);
725 CREATE TABLE t2(c1 TINYINT NULL);
726 CREATE TABLE t3(c1 TINYINT NOT NULL);
727 CREATE TABLE t4(c1 TINYINT NULL);
728 INSERT INTO t2 (c1) VALUES(NULL);
729 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
730 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
731 SELECT AVG(c1) AS value FROM t3;
732 SELECT AVG(c1) AS value FROM t3 WHERE c1 = 0;
733 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
734 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
735 DROP TABLE t1,t2,t3,t4;
736 CREATE TABLE t1(c1 SMALLINT NOT NULL);
737 CREATE TABLE t2(c1 SMALLINT NULL);
738 CREATE TABLE t3(c1 SMALLINT NOT NULL);
739 CREATE TABLE t4(c1 SMALLINT NULL);
740 INSERT INTO t2 (c1) VALUES(NULL);
741 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
742 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
743 SELECT AVG(c1) AS value FROM t3;
744 SELECT AVG(c1) AS value FROM t3 WHERE c1 = 0;
745 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
746 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
747 DROP TABLE t1,t2,t3,t4;
748 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
749 CREATE TABLE t2(c1 MEDIUMINT NULL);
750 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
751 CREATE TABLE t4(c1 MEDIUMINT NULL);
752 INSERT INTO t2 (c1) VALUES(NULL);
753 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
754 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
755 SELECT AVG(c1) AS value FROM t3;
756 SELECT AVG(c1) AS value FROM t3 WHERE c1 = 0;
757 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
758 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
759 DROP TABLE t1,t2,t3,t4;
760 CREATE TABLE t1(c1 INT NOT NULL);
761 CREATE TABLE t2(c1 INT NULL);
762 CREATE TABLE t3(c1 INT NOT NULL);
763 CREATE TABLE t4(c1 INT NULL);
764 INSERT INTO t2 (c1) VALUES(NULL);
765 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
766 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
767 SELECT AVG(c1) AS value FROM t3;
768 SELECT AVG(c1) AS value FROM t3 WHERE c1 = 0;
769 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
770 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
771 DROP TABLE t1,t2,t3,t4;
772 CREATE TABLE t1(c1 INTEGER NOT NULL);
773 CREATE TABLE t2(c1 INTEGER NULL);
774 CREATE TABLE t3(c1 INTEGER NOT NULL);
775 CREATE TABLE t4(c1 INTEGER NULL);
776 INSERT INTO t2 (c1) VALUES(NULL);
777 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
778 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
779 SELECT AVG(c1) AS value FROM t3;
780 SELECT AVG(c1) AS value FROM t3 WHERE c1 = 0;
781 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
782 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
783 DROP TABLE t1,t2,t3,t4;
784 CREATE TABLE t1(c1 BIGINT NOT NULL);
785 CREATE TABLE t2(c1 BIGINT NULL);
786 CREATE TABLE t3(c1 BIGINT NOT NULL);
787 CREATE TABLE t4(c1 BIGINT NULL);
788 INSERT INTO t2 (c1) VALUES(NULL);
789 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
790 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
791 SELECT AVG(c1) AS value FROM t3;
792 SELECT AVG(c1) AS value FROM t3 WHERE c1 = 0;
793 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
794 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
795 DROP TABLE t1,t2,t3,t4;
796 CREATE TABLE t1(c1 TINYINT NOT NULL);
797 CREATE TABLE t2(c1 TINYINT NULL);
798 CREATE TABLE t3(c1 TINYINT NOT NULL);
799 CREATE TABLE t4(c1 TINYINT NULL);
800 INSERT INTO t2 (c1) VALUES(NULL);
801 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
802 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
803 SELECT AVG(c1) AS value FROM t4;
804 SELECT AVG(c1) AS value FROM t4 WHERE c1 = 0;
805 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
806 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
807 DROP TABLE t1,t2,t3,t4;
808 CREATE TABLE t1(c1 SMALLINT NOT NULL);
809 CREATE TABLE t2(c1 SMALLINT NULL);
810 CREATE TABLE t3(c1 SMALLINT NOT NULL);
811 CREATE TABLE t4(c1 SMALLINT NULL);
812 INSERT INTO t2 (c1) VALUES(NULL);
813 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
814 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
815 SELECT AVG(c1) AS value FROM t4;
816 SELECT AVG(c1) AS value FROM t4 WHERE c1 = 0;
817 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
818 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
819 DROP TABLE t1,t2,t3,t4;
820 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
821 CREATE TABLE t2(c1 MEDIUMINT NULL);
822 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
823 CREATE TABLE t4(c1 MEDIUMINT NULL);
824 INSERT INTO t2 (c1) VALUES(NULL);
825 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
826 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
827 SELECT AVG(c1) AS value FROM t4;
828 SELECT AVG(c1) AS value FROM t4 WHERE c1 = 0;
829 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
830 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
831 DROP TABLE t1,t2,t3,t4;
832 CREATE TABLE t1(c1 INT NOT NULL);
833 CREATE TABLE t2(c1 INT NULL);
834 CREATE TABLE t3(c1 INT NOT NULL);
835 CREATE TABLE t4(c1 INT NULL);
836 INSERT INTO t2 (c1) VALUES(NULL);
837 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
838 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
839 SELECT AVG(c1) AS value FROM t4;
840 SELECT AVG(c1) AS value FROM t4 WHERE c1 = 0;
841 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
842 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
843 DROP TABLE t1,t2,t3,t4;
844 CREATE TABLE t1(c1 INTEGER NOT NULL);
845 CREATE TABLE t2(c1 INTEGER NULL);
846 CREATE TABLE t3(c1 INTEGER NOT NULL);
847 CREATE TABLE t4(c1 INTEGER NULL);
848 INSERT INTO t2 (c1) VALUES(NULL);
849 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
850 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
851 SELECT AVG(c1) AS value FROM t4;
852 SELECT AVG(c1) AS value FROM t4 WHERE c1 = 0;
853 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
854 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
855 DROP TABLE t1,t2,t3,t4;
856 CREATE TABLE t1(c1 BIGINT NOT NULL);
857 CREATE TABLE t2(c1 BIGINT NULL);
858 CREATE TABLE t3(c1 BIGINT NOT NULL);
859 CREATE TABLE t4(c1 BIGINT NULL);
860 INSERT INTO t2 (c1) VALUES(NULL);
861 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
862 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
863 SELECT AVG(c1) AS value FROM t4;
864 SELECT AVG(c1) AS value FROM t4 WHERE c1 = 0;
865 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
866 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
867 DROP TABLE t1,t2,t3,t4;
868 CREATE TABLE t1(c1 TINYINT NOT NULL);
869 CREATE TABLE t2(c1 TINYINT NULL);
870 CREATE TABLE t3(c1 TINYINT NOT NULL);
871 CREATE TABLE t4(c1 TINYINT NULL);
872 INSERT INTO t2 (c1) VALUES(NULL);
873 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
874 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
875 SELECT AVG(c1) AS value FROM t1;
876 SELECT AVG(c1) AS value FROM t1 WHERE c1 >= 0;
877 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
878 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
879 DROP TABLE t1,t2,t3,t4;
880 CREATE TABLE t1(c1 SMALLINT NOT NULL);
881 CREATE TABLE t2(c1 SMALLINT NULL);
882 CREATE TABLE t3(c1 SMALLINT NOT NULL);
883 CREATE TABLE t4(c1 SMALLINT NULL);
884 INSERT INTO t2 (c1) VALUES(NULL);
885 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
886 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
887 SELECT AVG(c1) AS value FROM t1;
888 SELECT AVG(c1) AS value FROM t1 WHERE c1 >= 0;
889 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
890 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
891 DROP TABLE t1,t2,t3,t4;
892 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
893 CREATE TABLE t2(c1 MEDIUMINT NULL);
894 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
895 CREATE TABLE t4(c1 MEDIUMINT NULL);
896 INSERT INTO t2 (c1) VALUES(NULL);
897 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
898 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
899 SELECT AVG(c1) AS value FROM t1;
900 SELECT AVG(c1) AS value FROM t1 WHERE c1 >= 0;
901 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
902 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
903 DROP TABLE t1,t2,t3,t4;
904 CREATE TABLE t1(c1 INT NOT NULL);
905 CREATE TABLE t2(c1 INT NULL);
906 CREATE TABLE t3(c1 INT NOT NULL);
907 CREATE TABLE t4(c1 INT NULL);
908 INSERT INTO t2 (c1) VALUES(NULL);
909 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
910 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
911 SELECT AVG(c1) AS value FROM t1;
912 SELECT AVG(c1) AS value FROM t1 WHERE c1 >= 0;
913 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
914 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
915 DROP TABLE t1,t2,t3,t4;
916 CREATE TABLE t1(c1 INTEGER NOT NULL);
917 CREATE TABLE t2(c1 INTEGER NULL);
918 CREATE TABLE t3(c1 INTEGER NOT NULL);
919 CREATE TABLE t4(c1 INTEGER NULL);
920 INSERT INTO t2 (c1) VALUES(NULL);
921 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
922 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
923 SELECT AVG(c1) AS value FROM t1;
924 SELECT AVG(c1) AS value FROM t1 WHERE c1 >= 0;
925 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
926 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
927 DROP TABLE t1,t2,t3,t4;
928 CREATE TABLE t1(c1 BIGINT NOT NULL);
929 CREATE TABLE t2(c1 BIGINT NULL);
930 CREATE TABLE t3(c1 BIGINT NOT NULL);
931 CREATE TABLE t4(c1 BIGINT NULL);
932 INSERT INTO t2 (c1) VALUES(NULL);
933 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
934 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
935 SELECT AVG(c1) AS value FROM t1;
936 SELECT AVG(c1) AS value FROM t1 WHERE c1 >= 0;
937 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
938 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
939 DROP TABLE t1,t2,t3,t4;
940 CREATE TABLE t1(c1 TINYINT NOT NULL);
941 CREATE TABLE t2(c1 TINYINT NULL);
942 CREATE TABLE t3(c1 TINYINT NOT NULL);
943 CREATE TABLE t4(c1 TINYINT NULL);
944 INSERT INTO t2 (c1) VALUES(NULL);
945 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
946 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
947 SELECT AVG(c1) AS value FROM t2;
948 SELECT AVG(c1) AS value FROM t2 WHERE c1 >= 0;
949 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
950 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
951 DROP TABLE t1,t2,t3,t4;
952 CREATE TABLE t1(c1 SMALLINT NOT NULL);
953 CREATE TABLE t2(c1 SMALLINT NULL);
954 CREATE TABLE t3(c1 SMALLINT NOT NULL);
955 CREATE TABLE t4(c1 SMALLINT NULL);
956 INSERT INTO t2 (c1) VALUES(NULL);
957 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
958 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
959 SELECT AVG(c1) AS value FROM t2;
960 SELECT AVG(c1) AS value FROM t2 WHERE c1 >= 0;
961 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
962 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
963 DROP TABLE t1,t2,t3,t4;
964 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
965 CREATE TABLE t2(c1 MEDIUMINT NULL);
966 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
967 CREATE TABLE t4(c1 MEDIUMINT NULL);
968 INSERT INTO t2 (c1) VALUES(NULL);
969 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
970 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
971 SELECT AVG(c1) AS value FROM t2;
972 SELECT AVG(c1) AS value FROM t2 WHERE c1 >= 0;
973 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
974 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
975 DROP TABLE t1,t2,t3,t4;
976 CREATE TABLE t1(c1 INT NOT NULL);
977 CREATE TABLE t2(c1 INT NULL);
978 CREATE TABLE t3(c1 INT NOT NULL);
979 CREATE TABLE t4(c1 INT NULL);
980 INSERT INTO t2 (c1) VALUES(NULL);
981 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
982 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
983 SELECT AVG(c1) AS value FROM t2;
984 SELECT AVG(c1) AS value FROM t2 WHERE c1 >= 0;
985 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
986 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
987 DROP TABLE t1,t2,t3,t4;
988 CREATE TABLE t1(c1 INTEGER NOT NULL);
989 CREATE TABLE t2(c1 INTEGER NULL);
990 CREATE TABLE t3(c1 INTEGER NOT NULL);
991 CREATE TABLE t4(c1 INTEGER NULL);
992 INSERT INTO t2 (c1) VALUES(NULL);
993 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
994 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
995 SELECT AVG(c1) AS value FROM t2;
996 SELECT AVG(c1) AS value FROM t2 WHERE c1 >= 0;
997 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
998 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
999 DROP TABLE t1,t2,t3,t4;
1000 CREATE TABLE t1(c1 BIGINT NOT NULL);
1001 CREATE TABLE t2(c1 BIGINT NULL);
1002 CREATE TABLE t3(c1 BIGINT NOT NULL);
1003 CREATE TABLE t4(c1 BIGINT NULL);
1004 INSERT INTO t2 (c1) VALUES(NULL);
1005 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1006 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1007 SELECT AVG(c1) AS value FROM t2;
1008 SELECT AVG(c1) AS value FROM t2 WHERE c1 >= 0;
1009 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1010 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1011 DROP TABLE t1,t2,t3,t4;
1012 CREATE TABLE t1(c1 TINYINT NOT NULL);
1013 CREATE TABLE t2(c1 TINYINT NULL);
1014 CREATE TABLE t3(c1 TINYINT NOT NULL);
1015 CREATE TABLE t4(c1 TINYINT NULL);
1016 INSERT INTO t2 (c1) VALUES(NULL);
1017 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1018 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1019 SELECT AVG(c1) AS value FROM t3;
1020 SELECT AVG(c1) AS value FROM t3 WHERE c1 >= 0;
1021 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1022 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1023 DROP TABLE t1,t2,t3,t4;
1024 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1025 CREATE TABLE t2(c1 SMALLINT NULL);
1026 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1027 CREATE TABLE t4(c1 SMALLINT NULL);
1028 INSERT INTO t2 (c1) VALUES(NULL);
1029 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1030 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1031 SELECT AVG(c1) AS value FROM t3;
1032 SELECT AVG(c1) AS value FROM t3 WHERE c1 >= 0;
1033 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1034 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1035 DROP TABLE t1,t2,t3,t4;
1036 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1037 CREATE TABLE t2(c1 MEDIUMINT NULL);
1038 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1039 CREATE TABLE t4(c1 MEDIUMINT NULL);
1040 INSERT INTO t2 (c1) VALUES(NULL);
1041 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1042 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1043 SELECT AVG(c1) AS value FROM t3;
1044 SELECT AVG(c1) AS value FROM t3 WHERE c1 >= 0;
1045 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1046 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1047 DROP TABLE t1,t2,t3,t4;
1048 CREATE TABLE t1(c1 INT NOT NULL);
1049 CREATE TABLE t2(c1 INT NULL);
1050 CREATE TABLE t3(c1 INT NOT NULL);
1051 CREATE TABLE t4(c1 INT NULL);
1052 INSERT INTO t2 (c1) VALUES(NULL);
1053 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1054 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1055 SELECT AVG(c1) AS value FROM t3;
1056 SELECT AVG(c1) AS value FROM t3 WHERE c1 >= 0;
1057 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1058 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1059 DROP TABLE t1,t2,t3,t4;
1060 CREATE TABLE t1(c1 INTEGER NOT NULL);
1061 CREATE TABLE t2(c1 INTEGER NULL);
1062 CREATE TABLE t3(c1 INTEGER NOT NULL);
1063 CREATE TABLE t4(c1 INTEGER NULL);
1064 INSERT INTO t2 (c1) VALUES(NULL);
1065 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1066 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1067 SELECT AVG(c1) AS value FROM t3;
1068 SELECT AVG(c1) AS value FROM t3 WHERE c1 >= 0;
1069 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1070 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1071 DROP TABLE t1,t2,t3,t4;
1072 CREATE TABLE t1(c1 BIGINT NOT NULL);
1073 CREATE TABLE t2(c1 BIGINT NULL);
1074 CREATE TABLE t3(c1 BIGINT NOT NULL);
1075 CREATE TABLE t4(c1 BIGINT NULL);
1076 INSERT INTO t2 (c1) VALUES(NULL);
1077 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1078 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1079 SELECT AVG(c1) AS value FROM t3;
1080 SELECT AVG(c1) AS value FROM t3 WHERE c1 >= 0;
1081 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1082 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1083 DROP TABLE t1,t2,t3,t4;
1084 CREATE TABLE t1(c1 TINYINT NOT NULL);
1085 CREATE TABLE t2(c1 TINYINT NULL);
1086 CREATE TABLE t3(c1 TINYINT NOT NULL);
1087 CREATE TABLE t4(c1 TINYINT NULL);
1088 INSERT INTO t2 (c1) VALUES(NULL);
1089 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1090 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1091 SELECT AVG(c1) AS value FROM t4;
1092 SELECT AVG(c1) AS value FROM t4 WHERE c1 >= 0;
1093 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1094 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1095 DROP TABLE t1,t2,t3,t4;
1096 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1097 CREATE TABLE t2(c1 SMALLINT NULL);
1098 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1099 CREATE TABLE t4(c1 SMALLINT NULL);
1100 INSERT INTO t2 (c1) VALUES(NULL);
1101 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1102 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1103 SELECT AVG(c1) AS value FROM t4;
1104 SELECT AVG(c1) AS value FROM t4 WHERE c1 >= 0;
1105 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1106 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1107 DROP TABLE t1,t2,t3,t4;
1108 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1109 CREATE TABLE t2(c1 MEDIUMINT NULL);
1110 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1111 CREATE TABLE t4(c1 MEDIUMINT NULL);
1112 INSERT INTO t2 (c1) VALUES(NULL);
1113 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1114 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1115 SELECT AVG(c1) AS value FROM t4;
1116 SELECT AVG(c1) AS value FROM t4 WHERE c1 >= 0;
1117 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1118 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1119 DROP TABLE t1,t2,t3,t4;
1120 CREATE TABLE t1(c1 INT NOT NULL);
1121 CREATE TABLE t2(c1 INT NULL);
1122 CREATE TABLE t3(c1 INT NOT NULL);
1123 CREATE TABLE t4(c1 INT NULL);
1124 INSERT INTO t2 (c1) VALUES(NULL);
1125 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1126 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1127 SELECT AVG(c1) AS value FROM t4;
1128 SELECT AVG(c1) AS value FROM t4 WHERE c1 >= 0;
1129 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1130 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1131 DROP TABLE t1,t2,t3,t4;
1132 CREATE TABLE t1(c1 INTEGER NOT NULL);
1133 CREATE TABLE t2(c1 INTEGER NULL);
1134 CREATE TABLE t3(c1 INTEGER NOT NULL);
1135 CREATE TABLE t4(c1 INTEGER NULL);
1136 INSERT INTO t2 (c1) VALUES(NULL);
1137 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1138 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1139 SELECT AVG(c1) AS value FROM t4;
1140 SELECT AVG(c1) AS value FROM t4 WHERE c1 >= 0;
1141 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1142 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1143 DROP TABLE t1,t2,t3,t4;
1144 CREATE TABLE t1(c1 BIGINT NOT NULL);
1145 CREATE TABLE t2(c1 BIGINT NULL);
1146 CREATE TABLE t3(c1 BIGINT NOT NULL);
1147 CREATE TABLE t4(c1 BIGINT NULL);
1148 INSERT INTO t2 (c1) VALUES(NULL);
1149 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1150 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1151 SELECT AVG(c1) AS value FROM t4;
1152 SELECT AVG(c1) AS value FROM t4 WHERE c1 >= 0;
1153 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1154 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1155 DROP TABLE t1,t2,t3,t4;
1156 CREATE TABLE t1(c1 TINYINT NOT NULL);
1157 CREATE TABLE t2(c1 TINYINT NULL);
1158 CREATE TABLE t3(c1 TINYINT NOT NULL);
1159 CREATE TABLE t4(c1 TINYINT NULL);
1160 INSERT INTO t2 (c1) VALUES(NULL);
1161 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1162 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1163 SELECT AVG(c1) AS value FROM t1;
1164 SELECT AVG(c1) AS value FROM t1 WHERE c1 != 0;
1165 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1166 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1167 DROP TABLE t1,t2,t3,t4;
1168 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1169 CREATE TABLE t2(c1 SMALLINT NULL);
1170 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1171 CREATE TABLE t4(c1 SMALLINT NULL);
1172 INSERT INTO t2 (c1) VALUES(NULL);
1173 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1174 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1175 SELECT AVG(c1) AS value FROM t1;
1176 SELECT AVG(c1) AS value FROM t1 WHERE c1 != 0;
1177 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1178 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1179 DROP TABLE t1,t2,t3,t4;
1180 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1181 CREATE TABLE t2(c1 MEDIUMINT NULL);
1182 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1183 CREATE TABLE t4(c1 MEDIUMINT NULL);
1184 INSERT INTO t2 (c1) VALUES(NULL);
1185 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1186 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1187 SELECT AVG(c1) AS value FROM t1;
1188 SELECT AVG(c1) AS value FROM t1 WHERE c1 != 0;
1189 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1190 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1191 DROP TABLE t1,t2,t3,t4;
1192 CREATE TABLE t1(c1 INT NOT NULL);
1193 CREATE TABLE t2(c1 INT NULL);
1194 CREATE TABLE t3(c1 INT NOT NULL);
1195 CREATE TABLE t4(c1 INT NULL);
1196 INSERT INTO t2 (c1) VALUES(NULL);
1197 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1198 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1199 SELECT AVG(c1) AS value FROM t1;
1200 SELECT AVG(c1) AS value FROM t1 WHERE c1 != 0;
1201 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1202 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1203 DROP TABLE t1,t2,t3,t4;
1204 CREATE TABLE t1(c1 INTEGER NOT NULL);
1205 CREATE TABLE t2(c1 INTEGER NULL);
1206 CREATE TABLE t3(c1 INTEGER NOT NULL);
1207 CREATE TABLE t4(c1 INTEGER NULL);
1208 INSERT INTO t2 (c1) VALUES(NULL);
1209 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1210 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1211 SELECT AVG(c1) AS value FROM t1;
1212 SELECT AVG(c1) AS value FROM t1 WHERE c1 != 0;
1213 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1214 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1215 DROP TABLE t1,t2,t3,t4;
1216 CREATE TABLE t1(c1 BIGINT NOT NULL);
1217 CREATE TABLE t2(c1 BIGINT NULL);
1218 CREATE TABLE t3(c1 BIGINT NOT NULL);
1219 CREATE TABLE t4(c1 BIGINT NULL);
1220 INSERT INTO t2 (c1) VALUES(NULL);
1221 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1222 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1223 SELECT AVG(c1) AS value FROM t1;
1224 SELECT AVG(c1) AS value FROM t1 WHERE c1 != 0;
1225 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1226 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1227 DROP TABLE t1,t2,t3,t4;
1228 CREATE TABLE t1(c1 TINYINT NOT NULL);
1229 CREATE TABLE t2(c1 TINYINT NULL);
1230 CREATE TABLE t3(c1 TINYINT NOT NULL);
1231 CREATE TABLE t4(c1 TINYINT NULL);
1232 INSERT INTO t2 (c1) VALUES(NULL);
1233 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1234 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1235 SELECT AVG(c1) AS value FROM t2;
1236 SELECT AVG(c1) AS value FROM t2 WHERE c1 != 0;
1237 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1238 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1239 DROP TABLE t1,t2,t3,t4;
1240 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1241 CREATE TABLE t2(c1 SMALLINT NULL);
1242 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1243 CREATE TABLE t4(c1 SMALLINT NULL);
1244 INSERT INTO t2 (c1) VALUES(NULL);
1245 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1246 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1247 SELECT AVG(c1) AS value FROM t2;
1248 SELECT AVG(c1) AS value FROM t2 WHERE c1 != 0;
1249 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1250 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1251 DROP TABLE t1,t2,t3,t4;
1252 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1253 CREATE TABLE t2(c1 MEDIUMINT NULL);
1254 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1255 CREATE TABLE t4(c1 MEDIUMINT NULL);
1256 INSERT INTO t2 (c1) VALUES(NULL);
1257 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1258 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1259 SELECT AVG(c1) AS value FROM t2;
1260 SELECT AVG(c1) AS value FROM t2 WHERE c1 != 0;
1261 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1262 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1263 DROP TABLE t1,t2,t3,t4;
1264 CREATE TABLE t1(c1 INT NOT NULL);
1265 CREATE TABLE t2(c1 INT NULL);
1266 CREATE TABLE t3(c1 INT NOT NULL);
1267 CREATE TABLE t4(c1 INT NULL);
1268 INSERT INTO t2 (c1) VALUES(NULL);
1269 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1270 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1271 SELECT AVG(c1) AS value FROM t2;
1272 SELECT AVG(c1) AS value FROM t2 WHERE c1 != 0;
1273 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1274 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1275 DROP TABLE t1,t2,t3,t4;
1276 CREATE TABLE t1(c1 INTEGER NOT NULL);
1277 CREATE TABLE t2(c1 INTEGER NULL);
1278 CREATE TABLE t3(c1 INTEGER NOT NULL);
1279 CREATE TABLE t4(c1 INTEGER NULL);
1280 INSERT INTO t2 (c1) VALUES(NULL);
1281 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1282 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1283 SELECT AVG(c1) AS value FROM t2;
1284 SELECT AVG(c1) AS value FROM t2 WHERE c1 != 0;
1285 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1286 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1287 DROP TABLE t1,t2,t3,t4;
1288 CREATE TABLE t1(c1 BIGINT NOT NULL);
1289 CREATE TABLE t2(c1 BIGINT NULL);
1290 CREATE TABLE t3(c1 BIGINT NOT NULL);
1291 CREATE TABLE t4(c1 BIGINT NULL);
1292 INSERT INTO t2 (c1) VALUES(NULL);
1293 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1294 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1295 SELECT AVG(c1) AS value FROM t2;
1296 SELECT AVG(c1) AS value FROM t2 WHERE c1 != 0;
1297 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1298 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1299 DROP TABLE t1,t2,t3,t4;
1300 CREATE TABLE t1(c1 TINYINT NOT NULL);
1301 CREATE TABLE t2(c1 TINYINT NULL);
1302 CREATE TABLE t3(c1 TINYINT NOT NULL);
1303 CREATE TABLE t4(c1 TINYINT NULL);
1304 INSERT INTO t2 (c1) VALUES(NULL);
1305 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1306 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1307 SELECT AVG(c1) AS value FROM t3;
1308 SELECT AVG(c1) AS value FROM t3 WHERE c1 != 0;
1309 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1310 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1311 DROP TABLE t1,t2,t3,t4;
1312 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1313 CREATE TABLE t2(c1 SMALLINT NULL);
1314 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1315 CREATE TABLE t4(c1 SMALLINT NULL);
1316 INSERT INTO t2 (c1) VALUES(NULL);
1317 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1318 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1319 SELECT AVG(c1) AS value FROM t3;
1320 SELECT AVG(c1) AS value FROM t3 WHERE c1 != 0;
1321 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1322 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1323 DROP TABLE t1,t2,t3,t4;
1324 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1325 CREATE TABLE t2(c1 MEDIUMINT NULL);
1326 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1327 CREATE TABLE t4(c1 MEDIUMINT NULL);
1328 INSERT INTO t2 (c1) VALUES(NULL);
1329 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1330 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1331 SELECT AVG(c1) AS value FROM t3;
1332 SELECT AVG(c1) AS value FROM t3 WHERE c1 != 0;
1333 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1334 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1335 DROP TABLE t1,t2,t3,t4;
1336 CREATE TABLE t1(c1 INT NOT NULL);
1337 CREATE TABLE t2(c1 INT NULL);
1338 CREATE TABLE t3(c1 INT NOT NULL);
1339 CREATE TABLE t4(c1 INT NULL);
1340 INSERT INTO t2 (c1) VALUES(NULL);
1341 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1342 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1343 SELECT AVG(c1) AS value FROM t3;
1344 SELECT AVG(c1) AS value FROM t3 WHERE c1 != 0;
1345 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1346 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1347 DROP TABLE t1,t2,t3,t4;
1348 CREATE TABLE t1(c1 INTEGER NOT NULL);
1349 CREATE TABLE t2(c1 INTEGER NULL);
1350 CREATE TABLE t3(c1 INTEGER NOT NULL);
1351 CREATE TABLE t4(c1 INTEGER NULL);
1352 INSERT INTO t2 (c1) VALUES(NULL);
1353 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1354 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1355 SELECT AVG(c1) AS value FROM t3;
1356 SELECT AVG(c1) AS value FROM t3 WHERE c1 != 0;
1357 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1358 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1359 DROP TABLE t1,t2,t3,t4;
1360 CREATE TABLE t1(c1 BIGINT NOT NULL);
1361 CREATE TABLE t2(c1 BIGINT NULL);
1362 CREATE TABLE t3(c1 BIGINT NOT NULL);
1363 CREATE TABLE t4(c1 BIGINT NULL);
1364 INSERT INTO t2 (c1) VALUES(NULL);
1365 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1366 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1367 SELECT AVG(c1) AS value FROM t3;
1368 SELECT AVG(c1) AS value FROM t3 WHERE c1 != 0;
1369 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1370 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1371 DROP TABLE t1,t2,t3,t4;
1372 CREATE TABLE t1(c1 TINYINT NOT NULL);
1373 CREATE TABLE t2(c1 TINYINT NULL);
1374 CREATE TABLE t3(c1 TINYINT NOT NULL);
1375 CREATE TABLE t4(c1 TINYINT NULL);
1376 INSERT INTO t2 (c1) VALUES(NULL);
1377 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1378 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1379 SELECT AVG(c1) AS value FROM t4;
1380 SELECT AVG(c1) AS value FROM t4 WHERE c1 != 0;
1381 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1382 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1383 DROP TABLE t1,t2,t3,t4;
1384 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1385 CREATE TABLE t2(c1 SMALLINT NULL);
1386 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1387 CREATE TABLE t4(c1 SMALLINT NULL);
1388 INSERT INTO t2 (c1) VALUES(NULL);
1389 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1390 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1391 SELECT AVG(c1) AS value FROM t4;
1392 SELECT AVG(c1) AS value FROM t4 WHERE c1 != 0;
1393 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1394 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1395 DROP TABLE t1,t2,t3,t4;
1396 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1397 CREATE TABLE t2(c1 MEDIUMINT NULL);
1398 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1399 CREATE TABLE t4(c1 MEDIUMINT NULL);
1400 INSERT INTO t2 (c1) VALUES(NULL);
1401 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1402 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1403 SELECT AVG(c1) AS value FROM t4;
1404 SELECT AVG(c1) AS value FROM t4 WHERE c1 != 0;
1405 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1406 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1407 DROP TABLE t1,t2,t3,t4;
1408 CREATE TABLE t1(c1 INT NOT NULL);
1409 CREATE TABLE t2(c1 INT NULL);
1410 CREATE TABLE t3(c1 INT NOT NULL);
1411 CREATE TABLE t4(c1 INT NULL);
1412 INSERT INTO t2 (c1) VALUES(NULL);
1413 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1414 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1415 SELECT AVG(c1) AS value FROM t4;
1416 SELECT AVG(c1) AS value FROM t4 WHERE c1 != 0;
1417 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1418 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1419 DROP TABLE t1,t2,t3,t4;
1420 CREATE TABLE t1(c1 INTEGER NOT NULL);
1421 CREATE TABLE t2(c1 INTEGER NULL);
1422 CREATE TABLE t3(c1 INTEGER NOT NULL);
1423 CREATE TABLE t4(c1 INTEGER NULL);
1424 INSERT INTO t2 (c1) VALUES(NULL);
1425 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1426 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1427 SELECT AVG(c1) AS value FROM t4;
1428 SELECT AVG(c1) AS value FROM t4 WHERE c1 != 0;
1429 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1430 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1431 DROP TABLE t1,t2,t3,t4;
1432 CREATE TABLE t1(c1 BIGINT NOT NULL);
1433 CREATE TABLE t2(c1 BIGINT NULL);
1434 CREATE TABLE t3(c1 BIGINT NOT NULL);
1435 CREATE TABLE t4(c1 BIGINT NULL);
1436 INSERT INTO t2 (c1) VALUES(NULL);
1437 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1438 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1439 SELECT AVG(c1) AS value FROM t4;
1440 SELECT AVG(c1) AS value FROM t4 WHERE c1 != 0;
1441 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1442 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1443 DROP TABLE t1,t2,t3,t4;
1444 CREATE TABLE t1(c1 TINYINT NOT NULL);
1445 CREATE TABLE t2(c1 TINYINT NULL);
1446 CREATE TABLE t3(c1 TINYINT NOT NULL);
1447 CREATE TABLE t4(c1 TINYINT NULL);
1448 INSERT INTO t2 (c1) VALUES(NULL);
1449 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1450 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1451 SELECT AVG(c1) AS value FROM t1;
1452 SELECT AVG(c1) AS value FROM t1 WHERE c1 <> 0;
1453 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1454 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1455 DROP TABLE t1,t2,t3,t4;
1456 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1457 CREATE TABLE t2(c1 SMALLINT NULL);
1458 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1459 CREATE TABLE t4(c1 SMALLINT NULL);
1460 INSERT INTO t2 (c1) VALUES(NULL);
1461 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1462 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1463 SELECT AVG(c1) AS value FROM t1;
1464 SELECT AVG(c1) AS value FROM t1 WHERE c1 <> 0;
1465 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1466 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1467 DROP TABLE t1,t2,t3,t4;
1468 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1469 CREATE TABLE t2(c1 MEDIUMINT NULL);
1470 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1471 CREATE TABLE t4(c1 MEDIUMINT NULL);
1472 INSERT INTO t2 (c1) VALUES(NULL);
1473 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1474 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1475 SELECT AVG(c1) AS value FROM t1;
1476 SELECT AVG(c1) AS value FROM t1 WHERE c1 <> 0;
1477 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1478 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1479 DROP TABLE t1,t2,t3,t4;
1480 CREATE TABLE t1(c1 INT NOT NULL);
1481 CREATE TABLE t2(c1 INT NULL);
1482 CREATE TABLE t3(c1 INT NOT NULL);
1483 CREATE TABLE t4(c1 INT NULL);
1484 INSERT INTO t2 (c1) VALUES(NULL);
1485 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1486 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1487 SELECT AVG(c1) AS value FROM t1;
1488 SELECT AVG(c1) AS value FROM t1 WHERE c1 <> 0;
1489 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1490 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1491 DROP TABLE t1,t2,t3,t4;
1492 CREATE TABLE t1(c1 INTEGER NOT NULL);
1493 CREATE TABLE t2(c1 INTEGER NULL);
1494 CREATE TABLE t3(c1 INTEGER NOT NULL);
1495 CREATE TABLE t4(c1 INTEGER NULL);
1496 INSERT INTO t2 (c1) VALUES(NULL);
1497 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1498 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1499 SELECT AVG(c1) AS value FROM t1;
1500 SELECT AVG(c1) AS value FROM t1 WHERE c1 <> 0;
1501 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1502 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1503 DROP TABLE t1,t2,t3,t4;
1504 CREATE TABLE t1(c1 BIGINT NOT NULL);
1505 CREATE TABLE t2(c1 BIGINT NULL);
1506 CREATE TABLE t3(c1 BIGINT NOT NULL);
1507 CREATE TABLE t4(c1 BIGINT NULL);
1508 INSERT INTO t2 (c1) VALUES(NULL);
1509 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1510 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1511 SELECT AVG(c1) AS value FROM t1;
1512 SELECT AVG(c1) AS value FROM t1 WHERE c1 <> 0;
1513 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1514 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1515 DROP TABLE t1,t2,t3,t4;
1516 CREATE TABLE t1(c1 TINYINT NOT NULL);
1517 CREATE TABLE t2(c1 TINYINT NULL);
1518 CREATE TABLE t3(c1 TINYINT NOT NULL);
1519 CREATE TABLE t4(c1 TINYINT NULL);
1520 INSERT INTO t2 (c1) VALUES(NULL);
1521 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1522 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1523 SELECT AVG(c1) AS value FROM t2;
1524 SELECT AVG(c1) AS value FROM t2 WHERE c1 <> 0;
1525 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1526 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1527 DROP TABLE t1,t2,t3,t4;
1528 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1529 CREATE TABLE t2(c1 SMALLINT NULL);
1530 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1531 CREATE TABLE t4(c1 SMALLINT NULL);
1532 INSERT INTO t2 (c1) VALUES(NULL);
1533 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1534 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1535 SELECT AVG(c1) AS value FROM t2;
1536 SELECT AVG(c1) AS value FROM t2 WHERE c1 <> 0;
1537 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1538 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1539 DROP TABLE t1,t2,t3,t4;
1540 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1541 CREATE TABLE t2(c1 MEDIUMINT NULL);
1542 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1543 CREATE TABLE t4(c1 MEDIUMINT NULL);
1544 INSERT INTO t2 (c1) VALUES(NULL);
1545 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1546 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1547 SELECT AVG(c1) AS value FROM t2;
1548 SELECT AVG(c1) AS value FROM t2 WHERE c1 <> 0;
1549 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1550 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1551 DROP TABLE t1,t2,t3,t4;
1552 CREATE TABLE t1(c1 INT NOT NULL);
1553 CREATE TABLE t2(c1 INT NULL);
1554 CREATE TABLE t3(c1 INT NOT NULL);
1555 CREATE TABLE t4(c1 INT NULL);
1556 INSERT INTO t2 (c1) VALUES(NULL);
1557 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1558 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1559 SELECT AVG(c1) AS value FROM t2;
1560 SELECT AVG(c1) AS value FROM t2 WHERE c1 <> 0;
1561 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1562 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1563 DROP TABLE t1,t2,t3,t4;
1564 CREATE TABLE t1(c1 INTEGER NOT NULL);
1565 CREATE TABLE t2(c1 INTEGER NULL);
1566 CREATE TABLE t3(c1 INTEGER NOT NULL);
1567 CREATE TABLE t4(c1 INTEGER NULL);
1568 INSERT INTO t2 (c1) VALUES(NULL);
1569 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1570 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1571 SELECT AVG(c1) AS value FROM t2;
1572 SELECT AVG(c1) AS value FROM t2 WHERE c1 <> 0;
1573 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1574 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1575 DROP TABLE t1,t2,t3,t4;
1576 CREATE TABLE t1(c1 BIGINT NOT NULL);
1577 CREATE TABLE t2(c1 BIGINT NULL);
1578 CREATE TABLE t3(c1 BIGINT NOT NULL);
1579 CREATE TABLE t4(c1 BIGINT NULL);
1580 INSERT INTO t2 (c1) VALUES(NULL);
1581 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1582 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1583 SELECT AVG(c1) AS value FROM t2;
1584 SELECT AVG(c1) AS value FROM t2 WHERE c1 <> 0;
1585 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1586 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1587 DROP TABLE t1,t2,t3,t4;
1588 CREATE TABLE t1(c1 TINYINT NOT NULL);
1589 CREATE TABLE t2(c1 TINYINT NULL);
1590 CREATE TABLE t3(c1 TINYINT NOT NULL);
1591 CREATE TABLE t4(c1 TINYINT NULL);
1592 INSERT INTO t2 (c1) VALUES(NULL);
1593 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1594 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1595 SELECT AVG(c1) AS value FROM t3;
1596 SELECT AVG(c1) AS value FROM t3 WHERE c1 <> 0;
1597 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1598 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1599 DROP TABLE t1,t2,t3,t4;
1600 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1601 CREATE TABLE t2(c1 SMALLINT NULL);
1602 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1603 CREATE TABLE t4(c1 SMALLINT NULL);
1604 INSERT INTO t2 (c1) VALUES(NULL);
1605 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1606 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1607 SELECT AVG(c1) AS value FROM t3;
1608 SELECT AVG(c1) AS value FROM t3 WHERE c1 <> 0;
1609 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1610 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1611 DROP TABLE t1,t2,t3,t4;
1612 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1613 CREATE TABLE t2(c1 MEDIUMINT NULL);
1614 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1615 CREATE TABLE t4(c1 MEDIUMINT NULL);
1616 INSERT INTO t2 (c1) VALUES(NULL);
1617 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1618 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1619 SELECT AVG(c1) AS value FROM t3;
1620 SELECT AVG(c1) AS value FROM t3 WHERE c1 <> 0;
1621 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1622 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1623 DROP TABLE t1,t2,t3,t4;
1624 CREATE TABLE t1(c1 INT NOT NULL);
1625 CREATE TABLE t2(c1 INT NULL);
1626 CREATE TABLE t3(c1 INT NOT NULL);
1627 CREATE TABLE t4(c1 INT NULL);
1628 INSERT INTO t2 (c1) VALUES(NULL);
1629 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1630 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1631 SELECT AVG(c1) AS value FROM t3;
1632 SELECT AVG(c1) AS value FROM t3 WHERE c1 <> 0;
1633 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1634 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1635 DROP TABLE t1,t2,t3,t4;
1636 CREATE TABLE t1(c1 INTEGER NOT NULL);
1637 CREATE TABLE t2(c1 INTEGER NULL);
1638 CREATE TABLE t3(c1 INTEGER NOT NULL);
1639 CREATE TABLE t4(c1 INTEGER NULL);
1640 INSERT INTO t2 (c1) VALUES(NULL);
1641 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1642 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1643 SELECT AVG(c1) AS value FROM t3;
1644 SELECT AVG(c1) AS value FROM t3 WHERE c1 <> 0;
1645 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1646 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1647 DROP TABLE t1,t2,t3,t4;
1648 CREATE TABLE t1(c1 BIGINT NOT NULL);
1649 CREATE TABLE t2(c1 BIGINT NULL);
1650 CREATE TABLE t3(c1 BIGINT NOT NULL);
1651 CREATE TABLE t4(c1 BIGINT NULL);
1652 INSERT INTO t2 (c1) VALUES(NULL);
1653 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1654 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1655 SELECT AVG(c1) AS value FROM t3;
1656 SELECT AVG(c1) AS value FROM t3 WHERE c1 <> 0;
1657 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1658 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1659 DROP TABLE t1,t2,t3,t4;
1660 CREATE TABLE t1(c1 TINYINT NOT NULL);
1661 CREATE TABLE t2(c1 TINYINT NULL);
1662 CREATE TABLE t3(c1 TINYINT NOT NULL);
1663 CREATE TABLE t4(c1 TINYINT NULL);
1664 INSERT INTO t2 (c1) VALUES(NULL);
1665 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1666 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1667 SELECT AVG(c1) AS value FROM t4;
1668 SELECT AVG(c1) AS value FROM t4 WHERE c1 <> 0;
1669 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1670 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1671 DROP TABLE t1,t2,t3,t4;
1672 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1673 CREATE TABLE t2(c1 SMALLINT NULL);
1674 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1675 CREATE TABLE t4(c1 SMALLINT NULL);
1676 INSERT INTO t2 (c1) VALUES(NULL);
1677 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1678 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1679 SELECT AVG(c1) AS value FROM t4;
1680 SELECT AVG(c1) AS value FROM t4 WHERE c1 <> 0;
1681 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1682 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1683 DROP TABLE t1,t2,t3,t4;
1684 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1685 CREATE TABLE t2(c1 MEDIUMINT NULL);
1686 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1687 CREATE TABLE t4(c1 MEDIUMINT NULL);
1688 INSERT INTO t2 (c1) VALUES(NULL);
1689 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1690 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1691 SELECT AVG(c1) AS value FROM t4;
1692 SELECT AVG(c1) AS value FROM t4 WHERE c1 <> 0;
1693 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1694 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1695 DROP TABLE t1,t2,t3,t4;
1696 CREATE TABLE t1(c1 INT NOT NULL);
1697 CREATE TABLE t2(c1 INT NULL);
1698 CREATE TABLE t3(c1 INT NOT NULL);
1699 CREATE TABLE t4(c1 INT NULL);
1700 INSERT INTO t2 (c1) VALUES(NULL);
1701 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1702 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1703 SELECT AVG(c1) AS value FROM t4;
1704 SELECT AVG(c1) AS value FROM t4 WHERE c1 <> 0;
1705 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1706 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1707 DROP TABLE t1,t2,t3,t4;
1708 CREATE TABLE t1(c1 INTEGER NOT NULL);
1709 CREATE TABLE t2(c1 INTEGER NULL);
1710 CREATE TABLE t3(c1 INTEGER NOT NULL);
1711 CREATE TABLE t4(c1 INTEGER NULL);
1712 INSERT INTO t2 (c1) VALUES(NULL);
1713 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1714 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1715 SELECT AVG(c1) AS value FROM t4;
1716 SELECT AVG(c1) AS value FROM t4 WHERE c1 <> 0;
1717 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1718 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1719 DROP TABLE t1,t2,t3,t4;
1720 CREATE TABLE t1(c1 BIGINT NOT NULL);
1721 CREATE TABLE t2(c1 BIGINT NULL);
1722 CREATE TABLE t3(c1 BIGINT NOT NULL);
1723 CREATE TABLE t4(c1 BIGINT NULL);
1724 INSERT INTO t2 (c1) VALUES(NULL);
1725 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1726 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1727 SELECT AVG(c1) AS value FROM t4;
1728 SELECT AVG(c1) AS value FROM t4 WHERE c1 <> 0;
1729 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1730 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1731 DROP TABLE t1,t2,t3,t4;
1732 CREATE TABLE t1(c1 TINYINT NOT NULL);
1733 CREATE TABLE t2(c1 TINYINT NULL);
1734 CREATE TABLE t3(c1 TINYINT NOT NULL);
1735 CREATE TABLE t4(c1 TINYINT NULL);
1736 INSERT INTO t2 (c1) VALUES(NULL);
1737 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1738 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1739 SELECT AVG(c1) AS value FROM t1;
1740 SELECT AVG(c1) AS value FROM t1 WHERE c1 <=> 0;
1741 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1742 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1743 DROP TABLE t1,t2,t3,t4;
1744 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1745 CREATE TABLE t2(c1 SMALLINT NULL);
1746 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1747 CREATE TABLE t4(c1 SMALLINT NULL);
1748 INSERT INTO t2 (c1) VALUES(NULL);
1749 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1750 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1751 SELECT AVG(c1) AS value FROM t1;
1752 SELECT AVG(c1) AS value FROM t1 WHERE c1 <=> 0;
1753 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1754 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1755 DROP TABLE t1,t2,t3,t4;
1756 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1757 CREATE TABLE t2(c1 MEDIUMINT NULL);
1758 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1759 CREATE TABLE t4(c1 MEDIUMINT NULL);
1760 INSERT INTO t2 (c1) VALUES(NULL);
1761 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1762 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1763 SELECT AVG(c1) AS value FROM t1;
1764 SELECT AVG(c1) AS value FROM t1 WHERE c1 <=> 0;
1765 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1766 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1767 DROP TABLE t1,t2,t3,t4;
1768 CREATE TABLE t1(c1 INT NOT NULL);
1769 CREATE TABLE t2(c1 INT NULL);
1770 CREATE TABLE t3(c1 INT NOT NULL);
1771 CREATE TABLE t4(c1 INT NULL);
1772 INSERT INTO t2 (c1) VALUES(NULL);
1773 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1774 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1775 SELECT AVG(c1) AS value FROM t1;
1776 SELECT AVG(c1) AS value FROM t1 WHERE c1 <=> 0;
1777 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1778 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1779 DROP TABLE t1,t2,t3,t4;
1780 CREATE TABLE t1(c1 INTEGER NOT NULL);
1781 CREATE TABLE t2(c1 INTEGER NULL);
1782 CREATE TABLE t3(c1 INTEGER NOT NULL);
1783 CREATE TABLE t4(c1 INTEGER NULL);
1784 INSERT INTO t2 (c1) VALUES(NULL);
1785 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1786 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1787 SELECT AVG(c1) AS value FROM t1;
1788 SELECT AVG(c1) AS value FROM t1 WHERE c1 <=> 0;
1789 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1790 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1791 DROP TABLE t1,t2,t3,t4;
1792 CREATE TABLE t1(c1 BIGINT NOT NULL);
1793 CREATE TABLE t2(c1 BIGINT NULL);
1794 CREATE TABLE t3(c1 BIGINT NOT NULL);
1795 CREATE TABLE t4(c1 BIGINT NULL);
1796 INSERT INTO t2 (c1) VALUES(NULL);
1797 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1798 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1799 SELECT AVG(c1) AS value FROM t1;
1800 SELECT AVG(c1) AS value FROM t1 WHERE c1 <=> 0;
1801 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;
1802 SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NOT NULL;
1803 DROP TABLE t1,t2,t3,t4;
1804 CREATE TABLE t1(c1 TINYINT NOT NULL);
1805 CREATE TABLE t2(c1 TINYINT NULL);
1806 CREATE TABLE t3(c1 TINYINT NOT NULL);
1807 CREATE TABLE t4(c1 TINYINT NULL);
1808 INSERT INTO t2 (c1) VALUES(NULL);
1809 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1810 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1811 SELECT AVG(c1) AS value FROM t2;
1812 SELECT AVG(c1) AS value FROM t2 WHERE c1 <=> 0;
1813 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1814 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1815 DROP TABLE t1,t2,t3,t4;
1816 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1817 CREATE TABLE t2(c1 SMALLINT NULL);
1818 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1819 CREATE TABLE t4(c1 SMALLINT NULL);
1820 INSERT INTO t2 (c1) VALUES(NULL);
1821 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1822 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1823 SELECT AVG(c1) AS value FROM t2;
1824 SELECT AVG(c1) AS value FROM t2 WHERE c1 <=> 0;
1825 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1826 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1827 DROP TABLE t1,t2,t3,t4;
1828 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1829 CREATE TABLE t2(c1 MEDIUMINT NULL);
1830 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1831 CREATE TABLE t4(c1 MEDIUMINT NULL);
1832 INSERT INTO t2 (c1) VALUES(NULL);
1833 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1834 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1835 SELECT AVG(c1) AS value FROM t2;
1836 SELECT AVG(c1) AS value FROM t2 WHERE c1 <=> 0;
1837 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1838 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1839 DROP TABLE t1,t2,t3,t4;
1840 CREATE TABLE t1(c1 INT NOT NULL);
1841 CREATE TABLE t2(c1 INT NULL);
1842 CREATE TABLE t3(c1 INT NOT NULL);
1843 CREATE TABLE t4(c1 INT NULL);
1844 INSERT INTO t2 (c1) VALUES(NULL);
1845 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1846 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1847 SELECT AVG(c1) AS value FROM t2;
1848 SELECT AVG(c1) AS value FROM t2 WHERE c1 <=> 0;
1849 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1850 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1851 DROP TABLE t1,t2,t3,t4;
1852 CREATE TABLE t1(c1 INTEGER NOT NULL);
1853 CREATE TABLE t2(c1 INTEGER NULL);
1854 CREATE TABLE t3(c1 INTEGER NOT NULL);
1855 CREATE TABLE t4(c1 INTEGER NULL);
1856 INSERT INTO t2 (c1) VALUES(NULL);
1857 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1858 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1859 SELECT AVG(c1) AS value FROM t2;
1860 SELECT AVG(c1) AS value FROM t2 WHERE c1 <=> 0;
1861 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1862 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1863 DROP TABLE t1,t2,t3,t4;
1864 CREATE TABLE t1(c1 BIGINT NOT NULL);
1865 CREATE TABLE t2(c1 BIGINT NULL);
1866 CREATE TABLE t3(c1 BIGINT NOT NULL);
1867 CREATE TABLE t4(c1 BIGINT NULL);
1868 INSERT INTO t2 (c1) VALUES(NULL);
1869 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1870 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1871 SELECT AVG(c1) AS value FROM t2;
1872 SELECT AVG(c1) AS value FROM t2 WHERE c1 <=> 0;
1873 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NULL;
1874 SELECT AVG(c1) AS value FROM t2 WHERE c1 IS NOT NULL;
1875 DROP TABLE t1,t2,t3,t4;
1876 CREATE TABLE t1(c1 TINYINT NOT NULL);
1877 CREATE TABLE t2(c1 TINYINT NULL);
1878 CREATE TABLE t3(c1 TINYINT NOT NULL);
1879 CREATE TABLE t4(c1 TINYINT NULL);
1880 INSERT INTO t2 (c1) VALUES(NULL);
1881 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1882 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1883 SELECT AVG(c1) AS value FROM t3;
1884 SELECT AVG(c1) AS value FROM t3 WHERE c1 <=> 0;
1885 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1886 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1887 DROP TABLE t1,t2,t3,t4;
1888 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1889 CREATE TABLE t2(c1 SMALLINT NULL);
1890 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1891 CREATE TABLE t4(c1 SMALLINT NULL);
1892 INSERT INTO t2 (c1) VALUES(NULL);
1893 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1894 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1895 SELECT AVG(c1) AS value FROM t3;
1896 SELECT AVG(c1) AS value FROM t3 WHERE c1 <=> 0;
1897 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1898 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1899 DROP TABLE t1,t2,t3,t4;
1900 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1901 CREATE TABLE t2(c1 MEDIUMINT NULL);
1902 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1903 CREATE TABLE t4(c1 MEDIUMINT NULL);
1904 INSERT INTO t2 (c1) VALUES(NULL);
1905 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1906 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1907 SELECT AVG(c1) AS value FROM t3;
1908 SELECT AVG(c1) AS value FROM t3 WHERE c1 <=> 0;
1909 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1910 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1911 DROP TABLE t1,t2,t3,t4;
1912 CREATE TABLE t1(c1 INT NOT NULL);
1913 CREATE TABLE t2(c1 INT NULL);
1914 CREATE TABLE t3(c1 INT NOT NULL);
1915 CREATE TABLE t4(c1 INT NULL);
1916 INSERT INTO t2 (c1) VALUES(NULL);
1917 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1918 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1919 SELECT AVG(c1) AS value FROM t3;
1920 SELECT AVG(c1) AS value FROM t3 WHERE c1 <=> 0;
1921 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1922 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1923 DROP TABLE t1,t2,t3,t4;
1924 CREATE TABLE t1(c1 INTEGER NOT NULL);
1925 CREATE TABLE t2(c1 INTEGER NULL);
1926 CREATE TABLE t3(c1 INTEGER NOT NULL);
1927 CREATE TABLE t4(c1 INTEGER NULL);
1928 INSERT INTO t2 (c1) VALUES(NULL);
1929 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1930 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1931 SELECT AVG(c1) AS value FROM t3;
1932 SELECT AVG(c1) AS value FROM t3 WHERE c1 <=> 0;
1933 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1934 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1935 DROP TABLE t1,t2,t3,t4;
1936 CREATE TABLE t1(c1 BIGINT NOT NULL);
1937 CREATE TABLE t2(c1 BIGINT NULL);
1938 CREATE TABLE t3(c1 BIGINT NOT NULL);
1939 CREATE TABLE t4(c1 BIGINT NULL);
1940 INSERT INTO t2 (c1) VALUES(NULL);
1941 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1942 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1943 SELECT AVG(c1) AS value FROM t3;
1944 SELECT AVG(c1) AS value FROM t3 WHERE c1 <=> 0;
1945 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NULL;
1946 SELECT AVG(c1) AS value FROM t3 WHERE c1 IS NOT NULL;
1947 DROP TABLE t1,t2,t3,t4;
1948 CREATE TABLE t1(c1 TINYINT NOT NULL);
1949 CREATE TABLE t2(c1 TINYINT NULL);
1950 CREATE TABLE t3(c1 TINYINT NOT NULL);
1951 CREATE TABLE t4(c1 TINYINT NULL);
1952 INSERT INTO t2 (c1) VALUES(NULL);
1953 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1954 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1955 SELECT AVG(c1) AS value FROM t4;
1956 SELECT AVG(c1) AS value FROM t4 WHERE c1 <=> 0;
1957 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1958 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1959 DROP TABLE t1,t2,t3,t4;
1960 CREATE TABLE t1(c1 SMALLINT NOT NULL);
1961 CREATE TABLE t2(c1 SMALLINT NULL);
1962 CREATE TABLE t3(c1 SMALLINT NOT NULL);
1963 CREATE TABLE t4(c1 SMALLINT NULL);
1964 INSERT INTO t2 (c1) VALUES(NULL);
1965 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1966 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1967 SELECT AVG(c1) AS value FROM t4;
1968 SELECT AVG(c1) AS value FROM t4 WHERE c1 <=> 0;
1969 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1970 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1971 DROP TABLE t1,t2,t3,t4;
1972 CREATE TABLE t1(c1 MEDIUMINT NOT NULL);
1973 CREATE TABLE t2(c1 MEDIUMINT NULL);
1974 CREATE TABLE t3(c1 MEDIUMINT NOT NULL);
1975 CREATE TABLE t4(c1 MEDIUMINT NULL);
1976 INSERT INTO t2 (c1) VALUES(NULL);
1977 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1978 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1979 SELECT AVG(c1) AS value FROM t4;
1980 SELECT AVG(c1) AS value FROM t4 WHERE c1 <=> 0;
1981 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1982 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1983 DROP TABLE t1,t2,t3,t4;
1984 CREATE TABLE t1(c1 INT NOT NULL);
1985 CREATE TABLE t2(c1 INT NULL);
1986 CREATE TABLE t3(c1 INT NOT NULL);
1987 CREATE TABLE t4(c1 INT NULL);
1988 INSERT INTO t2 (c1) VALUES(NULL);
1989 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1990 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1991 SELECT AVG(c1) AS value FROM t4;
1992 SELECT AVG(c1) AS value FROM t4 WHERE c1 <=> 0;
1993 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
1994 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
1995 DROP TABLE t1,t2,t3,t4;
1996 CREATE TABLE t1(c1 INTEGER NOT NULL);
1997 CREATE TABLE t2(c1 INTEGER NULL);
1998 CREATE TABLE t3(c1 INTEGER NOT NULL);
1999 CREATE TABLE t4(c1 INTEGER NULL);
2000 INSERT INTO t2 (c1) VALUES(NULL);
2001 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2002 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2003 SELECT AVG(c1) AS value FROM t4;
2004 SELECT AVG(c1) AS value FROM t4 WHERE c1 <=> 0;
2005 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
2006 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
2007 DROP TABLE t1,t2,t3,t4;
2008 CREATE TABLE t1(c1 BIGINT NOT NULL);
2009 CREATE TABLE t2(c1 BIGINT NULL);
2010 CREATE TABLE t3(c1 BIGINT NOT NULL);
2011 CREATE TABLE t4(c1 BIGINT NULL);
2012 INSERT INTO t2 (c1) VALUES(NULL);
2013 INSERT INTO t3 (c1) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2014 INSERT INTO t4 (c1) VALUES(NULL), (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2015 SELECT AVG(c1) AS value FROM t4;
2016 SELECT AVG(c1) AS value FROM t4 WHERE c1 <=> 0;
2017 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NULL;
2018 SELECT AVG(c1) AS value FROM t4 WHERE c1 IS NOT NULL;
2019 DROP TABLE t1,t2,t3,t4;
2020