Debian Codegraph @

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

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t2;
3 --enable_warnings
4 CREATE TABLE t2(c1 TINYINT NOT NULL);
5 INSERT INTO t2 (c1) VALUES(0);
6 INSERT INTO t2 (c1) VALUES(1);
7 INSERT INTO t2 (c1) VALUES(16);
8 INSERT INTO t2 (c1) VALUES(-4);
9 INSERT INTO t2 (c1) VALUES(-9);
10 SELECT MAX(c1) AS value FROM t2;
11 SELECT MAX(c1) AS postive_value FROM t2 WHERE c1 > 0;
12 SELECT MAX(c1) AS negative_value FROM t2 WHERE c1 < 0;
13 SELECT MAX(c1) AS zero_value FROM t2 WHERE c1 = 0;
14 SELECT MAX(c1) AS no_results FROM t2 WHERE c1 = 2;
15 DROP TABLE t2;
16 CREATE TABLE t2(c1 SMALLINT NOT NULL);
17 INSERT INTO t2 (c1) VALUES(0);
18 INSERT INTO t2 (c1) VALUES(1);
19 INSERT INTO t2 (c1) VALUES(16);
20 INSERT INTO t2 (c1) VALUES(-4);
21 INSERT INTO t2 (c1) VALUES(-9);
22 SELECT MAX(c1) AS value FROM t2;
23 SELECT MAX(c1) AS postive_value FROM t2 WHERE c1 > 0;
24 SELECT MAX(c1) AS negative_value FROM t2 WHERE c1 < 0;
25 SELECT MAX(c1) AS zero_value FROM t2 WHERE c1 = 0;
26 SELECT MAX(c1) AS no_results FROM t2 WHERE c1 = 2;
27 DROP TABLE t2;
28 CREATE TABLE t2(c1 MEDIUMINT NOT NULL);
29 INSERT INTO t2 (c1) VALUES(0);
30 INSERT INTO t2 (c1) VALUES(1);
31 INSERT INTO t2 (c1) VALUES(16);
32 INSERT INTO t2 (c1) VALUES(-4);
33 INSERT INTO t2 (c1) VALUES(-9);
34 SELECT MAX(c1) AS value FROM t2;
35 SELECT MAX(c1) AS postive_value FROM t2 WHERE c1 > 0;
36 SELECT MAX(c1) AS negative_value FROM t2 WHERE c1 < 0;
37 SELECT MAX(c1) AS zero_value FROM t2 WHERE c1 = 0;
38 SELECT MAX(c1) AS no_results FROM t2 WHERE c1 = 2;
39 DROP TABLE t2;
40 CREATE TABLE t2(c1 INT NOT NULL);
41 INSERT INTO t2 (c1) VALUES(0);
42 INSERT INTO t2 (c1) VALUES(1);
43 INSERT INTO t2 (c1) VALUES(16);
44 INSERT INTO t2 (c1) VALUES(-4);
45 INSERT INTO t2 (c1) VALUES(-9);
46 SELECT MAX(c1) AS value FROM t2;
47 SELECT MAX(c1) AS postive_value FROM t2 WHERE c1 > 0;
48 SELECT MAX(c1) AS negative_value FROM t2 WHERE c1 < 0;
49 SELECT MAX(c1) AS zero_value FROM t2 WHERE c1 = 0;
50 SELECT MAX(c1) AS no_results FROM t2 WHERE c1 = 2;
51 DROP TABLE t2;
52 CREATE TABLE t2(c1 INTEGER NOT NULL);
53 INSERT INTO t2 (c1) VALUES(0);
54 INSERT INTO t2 (c1) VALUES(1);
55 INSERT INTO t2 (c1) VALUES(16);
56 INSERT INTO t2 (c1) VALUES(-4);
57 INSERT INTO t2 (c1) VALUES(-9);
58 SELECT MAX(c1) AS value FROM t2;
59 SELECT MAX(c1) AS postive_value FROM t2 WHERE c1 > 0;
60 SELECT MAX(c1) AS negative_value FROM t2 WHERE c1 < 0;
61 SELECT MAX(c1) AS zero_value FROM t2 WHERE c1 = 0;
62 SELECT MAX(c1) AS no_results FROM t2 WHERE c1 = 2;
63 DROP TABLE t2;
64 CREATE TABLE t2(c1 BIGINT NOT NULL);
65 INSERT INTO t2 (c1) VALUES(0);
66 INSERT INTO t2 (c1) VALUES(1);
67 INSERT INTO t2 (c1) VALUES(16);
68 INSERT INTO t2 (c1) VALUES(-4);
69 INSERT INTO t2 (c1) VALUES(-9);
70 SELECT MAX(c1) AS value FROM t2;
71 SELECT MAX(c1) AS postive_value FROM t2 WHERE c1 > 0;
72 SELECT MAX(c1) AS negative_value FROM t2 WHERE c1 < 0;
73 SELECT MAX(c1) AS zero_value FROM t2 WHERE c1 = 0;
74 SELECT MAX(c1) AS no_results FROM t2 WHERE c1 = 2;
75 DROP TABLE t2;
76 CREATE TABLE t2(c1 TINYINT NOT NULL);
77 INSERT INTO t2 (c1) VALUES(0);
78 INSERT INTO t2 (c1) VALUES(1);
79 INSERT INTO t2 (c1) VALUES(16);
80 INSERT INTO t2 (c1) VALUES(-4);
81 INSERT INTO t2 (c1) VALUES(-9);
82 SELECT MIN(c1) AS value FROM t2;
83 SELECT MIN(c1) AS postive_value FROM t2 WHERE c1 > 0;
84 SELECT MIN(c1) AS negative_value FROM t2 WHERE c1 < 0;
85 SELECT MIN(c1) AS zero_value FROM t2 WHERE c1 = 0;
86 SELECT MIN(c1) AS no_results FROM t2 WHERE c1 = 2;
87 DROP TABLE t2;
88 CREATE TABLE t2(c1 SMALLINT NOT NULL);
89 INSERT INTO t2 (c1) VALUES(0);
90 INSERT INTO t2 (c1) VALUES(1);
91 INSERT INTO t2 (c1) VALUES(16);
92 INSERT INTO t2 (c1) VALUES(-4);
93 INSERT INTO t2 (c1) VALUES(-9);
94 SELECT MIN(c1) AS value FROM t2;
95 SELECT MIN(c1) AS postive_value FROM t2 WHERE c1 > 0;
96 SELECT MIN(c1) AS negative_value FROM t2 WHERE c1 < 0;
97 SELECT MIN(c1) AS zero_value FROM t2 WHERE c1 = 0;
98 SELECT MIN(c1) AS no_results FROM t2 WHERE c1 = 2;
99 DROP TABLE t2;
100 CREATE TABLE t2(c1 MEDIUMINT NOT NULL);
101 INSERT INTO t2 (c1) VALUES(0);
102 INSERT INTO t2 (c1) VALUES(1);
103 INSERT INTO t2 (c1) VALUES(16);
104 INSERT INTO t2 (c1) VALUES(-4);
105 INSERT INTO t2 (c1) VALUES(-9);
106 SELECT MIN(c1) AS value FROM t2;
107 SELECT MIN(c1) AS postive_value FROM t2 WHERE c1 > 0;
108 SELECT MIN(c1) AS negative_value FROM t2 WHERE c1 < 0;
109 SELECT MIN(c1) AS zero_value FROM t2 WHERE c1 = 0;
110 SELECT MIN(c1) AS no_results FROM t2 WHERE c1 = 2;
111 DROP TABLE t2;
112 CREATE TABLE t2(c1 INT NOT NULL);
113 INSERT INTO t2 (c1) VALUES(0);
114 INSERT INTO t2 (c1) VALUES(1);
115 INSERT INTO t2 (c1) VALUES(16);
116 INSERT INTO t2 (c1) VALUES(-4);
117 INSERT INTO t2 (c1) VALUES(-9);
118 SELECT MIN(c1) AS value FROM t2;
119 SELECT MIN(c1) AS postive_value FROM t2 WHERE c1 > 0;
120 SELECT MIN(c1) AS negative_value FROM t2 WHERE c1 < 0;
121 SELECT MIN(c1) AS zero_value FROM t2 WHERE c1 = 0;
122 SELECT MIN(c1) AS no_results FROM t2 WHERE c1 = 2;
123 DROP TABLE t2;
124 CREATE TABLE t2(c1 INTEGER NOT NULL);
125 INSERT INTO t2 (c1) VALUES(0);
126 INSERT INTO t2 (c1) VALUES(1);
127 INSERT INTO t2 (c1) VALUES(16);
128 INSERT INTO t2 (c1) VALUES(-4);
129 INSERT INTO t2 (c1) VALUES(-9);
130 SELECT MIN(c1) AS value FROM t2;
131 SELECT MIN(c1) AS postive_value FROM t2 WHERE c1 > 0;
132 SELECT MIN(c1) AS negative_value FROM t2 WHERE c1 < 0;
133 SELECT MIN(c1) AS zero_value FROM t2 WHERE c1 = 0;
134 SELECT MIN(c1) AS no_results FROM t2 WHERE c1 = 2;
135 DROP TABLE t2;
136 CREATE TABLE t2(c1 BIGINT NOT NULL);
137 INSERT INTO t2 (c1) VALUES(0);
138 INSERT INTO t2 (c1) VALUES(1);
139 INSERT INTO t2 (c1) VALUES(16);
140 INSERT INTO t2 (c1) VALUES(-4);
141 INSERT INTO t2 (c1) VALUES(-9);
142 SELECT MIN(c1) AS value FROM t2;
143 SELECT MIN(c1) AS postive_value FROM t2 WHERE c1 > 0;
144 SELECT MIN(c1) AS negative_value FROM t2 WHERE c1 < 0;
145 SELECT MIN(c1) AS zero_value FROM t2 WHERE c1 = 0;
146 SELECT MIN(c1) AS no_results FROM t2 WHERE c1 = 2;
147 DROP TABLE t2;
148 CREATE TABLE t2(c1 TINYINT NOT NULL);
149 INSERT INTO t2 (c1) VALUES(0);
150 INSERT INTO t2 (c1) VALUES(1);
151 INSERT INTO t2 (c1) VALUES(16);
152 INSERT INTO t2 (c1) VALUES(-4);
153 INSERT INTO t2 (c1) VALUES(-9);
154 SELECT AVG(c1) AS value FROM t2;
155 SELECT AVG(c1) AS postive_value FROM t2 WHERE c1 > 0;
156 SELECT AVG(c1) AS negative_value FROM t2 WHERE c1 < 0;
157 SELECT AVG(c1) AS zero_value FROM t2 WHERE c1 = 0;
158 SELECT AVG(c1) AS no_results FROM t2 WHERE c1 = 2;
159 DROP TABLE t2;
160 CREATE TABLE t2(c1 SMALLINT NOT NULL);
161 INSERT INTO t2 (c1) VALUES(0);
162 INSERT INTO t2 (c1) VALUES(1);
163 INSERT INTO t2 (c1) VALUES(16);
164 INSERT INTO t2 (c1) VALUES(-4);
165 INSERT INTO t2 (c1) VALUES(-9);
166 SELECT AVG(c1) AS value FROM t2;
167 SELECT AVG(c1) AS postive_value FROM t2 WHERE c1 > 0;
168 SELECT AVG(c1) AS negative_value FROM t2 WHERE c1 < 0;
169 SELECT AVG(c1) AS zero_value FROM t2 WHERE c1 = 0;
170 SELECT AVG(c1) AS no_results FROM t2 WHERE c1 = 2;
171 DROP TABLE t2;
172 CREATE TABLE t2(c1 MEDIUMINT NOT NULL);
173 INSERT INTO t2 (c1) VALUES(0);
174 INSERT INTO t2 (c1) VALUES(1);
175 INSERT INTO t2 (c1) VALUES(16);
176 INSERT INTO t2 (c1) VALUES(-4);
177 INSERT INTO t2 (c1) VALUES(-9);
178 SELECT AVG(c1) AS value FROM t2;
179 SELECT AVG(c1) AS postive_value FROM t2 WHERE c1 > 0;
180 SELECT AVG(c1) AS negative_value FROM t2 WHERE c1 < 0;
181 SELECT AVG(c1) AS zero_value FROM t2 WHERE c1 = 0;
182 SELECT AVG(c1) AS no_results FROM t2 WHERE c1 = 2;
183 DROP TABLE t2;
184 CREATE TABLE t2(c1 INT NOT NULL);
185 INSERT INTO t2 (c1) VALUES(0);
186 INSERT INTO t2 (c1) VALUES(1);
187 INSERT INTO t2 (c1) VALUES(16);
188 INSERT INTO t2 (c1) VALUES(-4);
189 INSERT INTO t2 (c1) VALUES(-9);
190 SELECT AVG(c1) AS value FROM t2;
191 SELECT AVG(c1) AS postive_value FROM t2 WHERE c1 > 0;
192 SELECT AVG(c1) AS negative_value FROM t2 WHERE c1 < 0;
193 SELECT AVG(c1) AS zero_value FROM t2 WHERE c1 = 0;
194 SELECT AVG(c1) AS no_results FROM t2 WHERE c1 = 2;
195 DROP TABLE t2;
196 CREATE TABLE t2(c1 INTEGER NOT NULL);
197 INSERT INTO t2 (c1) VALUES(0);
198 INSERT INTO t2 (c1) VALUES(1);
199 INSERT INTO t2 (c1) VALUES(16);
200 INSERT INTO t2 (c1) VALUES(-4);
201 INSERT INTO t2 (c1) VALUES(-9);
202 SELECT AVG(c1) AS value FROM t2;
203 SELECT AVG(c1) AS postive_value FROM t2 WHERE c1 > 0;
204 SELECT AVG(c1) AS negative_value FROM t2 WHERE c1 < 0;
205 SELECT AVG(c1) AS zero_value FROM t2 WHERE c1 = 0;
206 SELECT AVG(c1) AS no_results FROM t2 WHERE c1 = 2;
207 DROP TABLE t2;
208 CREATE TABLE t2(c1 BIGINT NOT NULL);
209 INSERT INTO t2 (c1) VALUES(0);
210 INSERT INTO t2 (c1) VALUES(1);
211 INSERT INTO t2 (c1) VALUES(16);
212 INSERT INTO t2 (c1) VALUES(-4);
213 INSERT INTO t2 (c1) VALUES(-9);
214 SELECT AVG(c1) AS value FROM t2;
215 SELECT AVG(c1) AS postive_value FROM t2 WHERE c1 > 0;
216 SELECT AVG(c1) AS negative_value FROM t2 WHERE c1 < 0;
217 SELECT AVG(c1) AS zero_value FROM t2 WHERE c1 = 0;
218 SELECT AVG(c1) AS no_results FROM t2 WHERE c1 = 2;
219 DROP TABLE t2;
220 CREATE TABLE t2(c1 TINYINT NOT NULL);
221 INSERT INTO t2 (c1) VALUES(0);
222 INSERT INTO t2 (c1) VALUES(1);
223 INSERT INTO t2 (c1) VALUES(16);
224 INSERT INTO t2 (c1) VALUES(-4);
225 INSERT INTO t2 (c1) VALUES(-9);
226 SELECT SUM(c1) AS value FROM t2;
227 SELECT SUM(c1) AS postive_value FROM t2 WHERE c1 > 0;
228 SELECT SUM(c1) AS negative_value FROM t2 WHERE c1 < 0;
229 SELECT SUM(c1) AS zero_value FROM t2 WHERE c1 = 0;
230 SELECT SUM(c1) AS no_results FROM t2 WHERE c1 = 2;
231 DROP TABLE t2;
232 CREATE TABLE t2(c1 SMALLINT NOT NULL);
233 INSERT INTO t2 (c1) VALUES(0);
234 INSERT INTO t2 (c1) VALUES(1);
235 INSERT INTO t2 (c1) VALUES(16);
236 INSERT INTO t2 (c1) VALUES(-4);
237 INSERT INTO t2 (c1) VALUES(-9);
238 SELECT SUM(c1) AS value FROM t2;
239 SELECT SUM(c1) AS postive_value FROM t2 WHERE c1 > 0;
240 SELECT SUM(c1) AS negative_value FROM t2 WHERE c1 < 0;
241 SELECT SUM(c1) AS zero_value FROM t2 WHERE c1 = 0;
242 SELECT SUM(c1) AS no_results FROM t2 WHERE c1 = 2;
243 DROP TABLE t2;
244 CREATE TABLE t2(c1 MEDIUMINT NOT NULL);
245 INSERT INTO t2 (c1) VALUES(0);
246 INSERT INTO t2 (c1) VALUES(1);
247 INSERT INTO t2 (c1) VALUES(16);
248 INSERT INTO t2 (c1) VALUES(-4);
249 INSERT INTO t2 (c1) VALUES(-9);
250 SELECT SUM(c1) AS value FROM t2;
251 SELECT SUM(c1) AS postive_value FROM t2 WHERE c1 > 0;
252 SELECT SUM(c1) AS negative_value FROM t2 WHERE c1 < 0;
253 SELECT SUM(c1) AS zero_value FROM t2 WHERE c1 = 0;
254 SELECT SUM(c1) AS no_results FROM t2 WHERE c1 = 2;
255 DROP TABLE t2;
256 CREATE TABLE t2(c1 INT NOT NULL);
257 INSERT INTO t2 (c1) VALUES(0);
258 INSERT INTO t2 (c1) VALUES(1);
259 INSERT INTO t2 (c1) VALUES(16);
260 INSERT INTO t2 (c1) VALUES(-4);
261 INSERT INTO t2 (c1) VALUES(-9);
262 SELECT SUM(c1) AS value FROM t2;
263 SELECT SUM(c1) AS postive_value FROM t2 WHERE c1 > 0;
264 SELECT SUM(c1) AS negative_value FROM t2 WHERE c1 < 0;
265 SELECT SUM(c1) AS zero_value FROM t2 WHERE c1 = 0;
266 SELECT SUM(c1) AS no_results FROM t2 WHERE c1 = 2;
267 DROP TABLE t2;
268 CREATE TABLE t2(c1 INTEGER NOT NULL);
269 INSERT INTO t2 (c1) VALUES(0);
270 INSERT INTO t2 (c1) VALUES(1);
271 INSERT INTO t2 (c1) VALUES(16);
272 INSERT INTO t2 (c1) VALUES(-4);
273 INSERT INTO t2 (c1) VALUES(-9);
274 SELECT SUM(c1) AS value FROM t2;
275 SELECT SUM(c1) AS postive_value FROM t2 WHERE c1 > 0;
276 SELECT SUM(c1) AS negative_value FROM t2 WHERE c1 < 0;
277 SELECT SUM(c1) AS zero_value FROM t2 WHERE c1 = 0;
278 SELECT SUM(c1) AS no_results FROM t2 WHERE c1 = 2;
279 DROP TABLE t2;
280 CREATE TABLE t2(c1 BIGINT NOT NULL);
281 INSERT INTO t2 (c1) VALUES(0);
282 INSERT INTO t2 (c1) VALUES(1);
283 INSERT INTO t2 (c1) VALUES(16);
284 INSERT INTO t2 (c1) VALUES(-4);
285 INSERT INTO t2 (c1) VALUES(-9);
286 SELECT SUM(c1) AS value FROM t2;
287 SELECT SUM(c1) AS postive_value FROM t2 WHERE c1 > 0;
288 SELECT SUM(c1) AS negative_value FROM t2 WHERE c1 < 0;
289 SELECT SUM(c1) AS zero_value FROM t2 WHERE c1 = 0;
290 SELECT SUM(c1) AS no_results FROM t2 WHERE c1 = 2;
291 DROP TABLE t2;
292 CREATE TABLE t2(c1 TINYINT NOT NULL);
293 INSERT INTO t2 (c1) VALUES(0);
294 INSERT INTO t2 (c1) VALUES(1);
295 INSERT INTO t2 (c1) VALUES(16);
296 INSERT INTO t2 (c1) VALUES(-4);
297 INSERT INTO t2 (c1) VALUES(-9);
298 SELECT COUNT(c1) AS value FROM t2;
299 SELECT COUNT(c1) AS postive_value FROM t2 WHERE c1 > 0;
300 SELECT COUNT(c1) AS negative_value FROM t2 WHERE c1 < 0;
301 SELECT COUNT(c1) AS zero_value FROM t2 WHERE c1 = 0;
302 SELECT COUNT(c1) AS no_results FROM t2 WHERE c1 = 2;
303 DROP TABLE t2;
304 CREATE TABLE t2(c1 SMALLINT NOT NULL);
305 INSERT INTO t2 (c1) VALUES(0);
306 INSERT INTO t2 (c1) VALUES(1);
307 INSERT INTO t2 (c1) VALUES(16);
308 INSERT INTO t2 (c1) VALUES(-4);
309 INSERT INTO t2 (c1) VALUES(-9);
310 SELECT COUNT(c1) AS value FROM t2;
311 SELECT COUNT(c1) AS postive_value FROM t2 WHERE c1 > 0;
312 SELECT COUNT(c1) AS negative_value FROM t2 WHERE c1 < 0;
313 SELECT COUNT(c1) AS zero_value FROM t2 WHERE c1 = 0;
314 SELECT COUNT(c1) AS no_results FROM t2 WHERE c1 = 2;
315 DROP TABLE t2;
316 CREATE TABLE t2(c1 MEDIUMINT NOT NULL);
317 INSERT INTO t2 (c1) VALUES(0);
318 INSERT INTO t2 (c1) VALUES(1);
319 INSERT INTO t2 (c1) VALUES(16);
320 INSERT INTO t2 (c1) VALUES(-4);
321 INSERT INTO t2 (c1) VALUES(-9);
322 SELECT COUNT(c1) AS value FROM t2;
323 SELECT COUNT(c1) AS postive_value FROM t2 WHERE c1 > 0;
324 SELECT COUNT(c1) AS negative_value FROM t2 WHERE c1 < 0;
325 SELECT COUNT(c1) AS zero_value FROM t2 WHERE c1 = 0;
326 SELECT COUNT(c1) AS no_results FROM t2 WHERE c1 = 2;
327 DROP TABLE t2;
328 CREATE TABLE t2(c1 INT NOT NULL);
329 INSERT INTO t2 (c1) VALUES(0);
330 INSERT INTO t2 (c1) VALUES(1);
331 INSERT INTO t2 (c1) VALUES(16);
332 INSERT INTO t2 (c1) VALUES(-4);
333 INSERT INTO t2 (c1) VALUES(-9);
334 SELECT COUNT(c1) AS value FROM t2;
335 SELECT COUNT(c1) AS postive_value FROM t2 WHERE c1 > 0;
336 SELECT COUNT(c1) AS negative_value FROM t2 WHERE c1 < 0;
337 SELECT COUNT(c1) AS zero_value FROM t2 WHERE c1 = 0;
338 SELECT COUNT(c1) AS no_results FROM t2 WHERE c1 = 2;
339 DROP TABLE t2;
340 CREATE TABLE t2(c1 INTEGER NOT NULL);
341 INSERT INTO t2 (c1) VALUES(0);
342 INSERT INTO t2 (c1) VALUES(1);
343 INSERT INTO t2 (c1) VALUES(16);
344 INSERT INTO t2 (c1) VALUES(-4);
345 INSERT INTO t2 (c1) VALUES(-9);
346 SELECT COUNT(c1) AS value FROM t2;
347 SELECT COUNT(c1) AS postive_value FROM t2 WHERE c1 > 0;
348 SELECT COUNT(c1) AS negative_value FROM t2 WHERE c1 < 0;
349 SELECT COUNT(c1) AS zero_value FROM t2 WHERE c1 = 0;
350 SELECT COUNT(c1) AS no_results FROM t2 WHERE c1 = 2;
351 DROP TABLE t2;
352 CREATE TABLE t2(c1 BIGINT NOT NULL);
353 INSERT INTO t2 (c1) VALUES(0);
354 INSERT INTO t2 (c1) VALUES(1);
355 INSERT INTO t2 (c1) VALUES(16);
356 INSERT INTO t2 (c1) VALUES(-4);
357 INSERT INTO t2 (c1) VALUES(-9);
358 SELECT COUNT(c1) AS value FROM t2;
359 SELECT COUNT(c1) AS postive_value FROM t2 WHERE c1 > 0;
360 SELECT COUNT(c1) AS negative_value FROM t2 WHERE c1 < 0;
361 SELECT COUNT(c1) AS zero_value FROM t2 WHERE c1 = 0;
362 SELECT COUNT(c1) AS no_results FROM t2 WHERE c1 = 2;
363 DROP TABLE t2;
364