sqllogictest

Check-in [1ab2242b4f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Evidence tests for aggregate functions.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1ab2242b4f2e1d5b6c46ad5955b8dfbe6eb95d7f
User & Date: shaneh 2010-09-25 04:03:19.000
Context
2010-11-30
09:50
Update some evidence marks in slt_lang_dropview.test to account for updated documentation text. check-in: 18a6b7f09c user: dan tags: trunk
2010-09-25
04:03
Evidence tests for aggregate functions. check-in: 1ab2242b4f user: shaneh tags: trunk
2010-09-24
18:59
Update to SLT evidence marks. check-in: 04ea7ef2df user: shaneh tags: trunk
Changes
Unified Diff Ignore Whitespace Patch
Added test/evidence/slt_lang_aggfunc.test.


































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)

skipif sqlite
halt

# count(x), avg(x), sum(x), total(x), min(x), max(x)
# group_concat(x), group_concat(x,y)

# EVIDENCE-OF: R-00466-56349 In any aggregate function that takes a
# single argument, that argument can be preceded by the keyword
# DISTINCT.

query I nosort
SELECT count(DISTINCT x) FROM t1
----
2

query I nosort
SELECT avg(DISTINCT x) FROM t1
----
0

query I nosort
SELECT sum(DISTINCT x) FROM t1
----
1

query I nosort
SELECT total(DISTINCT x) FROM t1
----
1

query I nosort
SELECT min(DISTINCT x) FROM t1
----
0

query I nosort
SELECT max(DISTINCT x) FROM t1
----
1

query T nosort
SELECT group_concat(DISTINCT x) FROM t1
----
1,0


# EVIDENCE-OF: R-00171-59428 In such cases, duplicate elements are
# filtered before being passed into the aggregate function.

# EVIDENCE-OF: R-31453-41389 For example, the function "count(distinct
# X)" will return the number of distinct values of column X instead of
# the total number of non-null values in column X.

statement ok
INSERT INTO t1 VALUES(2,'true')

statement ok
INSERT INTO t1 VALUES(2,'true')

query I nosort
SELECT count(DISTINCT x) FROM t1
----
3


# EVIDENCE-OF: R-20409-33051 The avg() function returns the average
# value of all non-NULL X within a group.

query I nosort
SELECT avg(x) FROM t1
----
1


# EVIDENCE-OF: R-29052-00975 String and BLOB values that do not look
# like numbers are interpreted as 0.

query I nosort
SELECT count(y) FROM t1
----
5

query I nosort
SELECT avg(y) FROM t1
----
0

query I nosort
SELECT sum(y) FROM t1
----
0

query I nosort
SELECT total(y) FROM t1
----
0

query I nosort
SELECT min(y) FROM t1
----
0

query I nosort
SELECT max(y) FROM t1
----
0

query T nosort
SELECT group_concat(y) FROM t1
----
true,false,NULL,true,true

# repeat with DISTINCT

query I nosort
SELECT count(DISTINCT y) FROM t1
----
3

query I nosort
SELECT avg(DISTINCT y) FROM t1
----
0

query I nosort
SELECT sum(DISTINCT y) FROM t1
----
0

query I nosort
SELECT total(DISTINCT y) FROM t1
----
0

query I nosort
SELECT min(DISTINCT y) FROM t1
----
0

query I nosort
SELECT max(DISTINCT y) FROM t1
----
0

query T nosort
SELECT group_concat(DISTINCT y) FROM t1
----
true,false,NULL


# EVIDENCE-OF: R-07535-34995 The result of avg() is always a floating
# point value as long as at there is at least one non-NULL input even if
# all inputs are integers.

query R nosort
SELECT avg(x) FROM t1
----
1.250

query R nosort
SELECT avg(DISTINCT x) FROM t1
----
1.000


# EVIDENCE-OF: R-40597-22164 The result of avg() is NULL if and only if
# there are no non-NULL inputs.

query I nosort label-NULL
SELECT avg(x) FROM t1 WHERE y='null'
----
NULL

query I nosort label-NULL
SELECT avg(DISTINCT x) FROM t1 WHERE y='null'
----
NULL


# EVIDENCE-OF: R-34280-42283 The count(X) function returns a count of
# the number of times that X is not NULL in a group.

query I nosort
SELECT count(x) FROM t1 WHERE y='null'
----
0

query I nosort
SELECT count(DISTINCT x) FROM t1 WHERE y='null'
----
0

query I nosort
SELECT count(x) FROM t1 WHERE y='false'
----
1

query I nosort
SELECT count(DISTINCT x) FROM t1 WHERE y='false'
----
1


# EVIDENCE-OF: R-13776-21310 The count(*) function (with no arguments)
# returns the total number of rows in the group.

query I nosort
SELECT count(*) FROM t1 WHERE y='false'
----
1

# TBD: can DISTINCT be used with *?

statement error
SELECT count(DISTINCT *) FROM t1 WHERE y='false'


# EVIDENCE-OF: R-56088-25150 The group_concat() function returns a
# string which is the concatenation of all non-NULL values of X.

query T nosort
SELECT group_concat(x) FROM t1
----
1,0,2,2

query T nosort
SELECT group_concat(DISTINCT x) FROM t1
----
1,0,2


# EVIDENCE-OF: R-08600-21007 If parameter Y is present then it is used
# as the separator between instances of X.

query T nosort
SELECT group_concat(x,':') FROM t1
----
1:0:2:2

# TBD: DISTINCT can only be used with single parameters
statement error
SELECT group_concat(DISTINCT x,':') FROM t1


# EVIDENCE-OF: R-39910-14723 A comma (",") is used as the separator if Y
# is omitted.

query T nosort
SELECT group_concat(x) FROM t1
----
1,0,2,2

query T nosort
SELECT group_concat(DISTINCT x) FROM t1
----
1,0,2


# EVIDENCE-OF: R-52585-35928 The max() aggregate function returns the
# maximum value of all values in the group.

query I nosort
SELECT max(x) FROM t1
----
2

query I nosort
SELECT max(DISTINCT x) FROM t1
----
2


# TBD: last non-NULL value
# EVIDENCE-OF: R-13053-11096 The maximum value is the value that would
# be returned last in an ORDER BY on the same column.

query I nosort
SELECT x FROM t1 WHERE x NOT NULL ORDER BY x
----
0
1
2
2

query I nosort
SELECT DISTINCT x FROM t1 WHERE x NOT NULL ORDER BY x
----
0
1
2


# EVIDENCE-OF: R-50775-16353 Aggregate max() returns NULL if and only if
# there are no non-NULL values in the group.

query I nosort label-NULL
SELECT max(x) FROM t1 WHERE y='null'
----
NULL

query I nosort label-NULL
SELECT max(DISTINCT x) FROM t1 WHERE y='null'
----
NULL


# EVIDENCE-OF: R-16028-39081 The min() aggregate function returns the
# minimum non-NULL value of all values in the group.

query I nosort
SELECT min(x) FROM t1
----
0

query I nosort
SELECT min(DISTINCT x) FROM t1
----
0


# EVIDENCE-OF: R-30311-39793 The minimum value is the first non-NULL
# value that would appear in an ORDER BY of the column.

query I nosort
SELECT x FROM t1 WHERE x NOT NULL ORDER BY x
----
0
1
2
2

query I nosort
SELECT DISTINCT x FROM t1 WHERE x NOT NULL ORDER BY x
----
0
1
2


# EVIDENCE-OF: R-10396-30188 Aggregate min() returns NULL if and only if
# there are no non-NULL values in the group.

query I nosort label-NULL
SELECT min(x) FROM t1 WHERE y='null'
----
NULL

query I nosort label-NULL
SELECT min(DISTINCT x) FROM t1 WHERE y='null'
----
NULL


# EVIDENCE-OF: R-58261-63259 The sum() and total() aggregate functions
# return sum of all non-NULL values in the group.

query I nosort label-sum
SELECT sum(x) FROM t1
----
5

query I nosort label-sum
SELECT total(x) FROM t1
----
5

query I nosort label-sum-distinct
SELECT sum(DISTINCT x) FROM t1
----
3

query I nosort label-sum-distinct
SELECT total(DISTINCT x) FROM t1
----
3


# EVIDENCE-OF: R-44223-43966 If there are no non-NULL input rows then
# sum() returns NULL but total() returns 0.

query I nosort label-NULL
SELECT sum(x) FROM t1 WHERE y='null'
----
NULL

query I nosort label-NULL
SELECT sum(DISTINCT x) FROM t1 WHERE y='null'
----
NULL

query I nosort label-zero
SELECT total(x) FROM t1 WHERE y='null'
----
0

query I nosort label-zero
SELECT total(DISTINCT x) FROM t1 WHERE y='null'
----
0


# EVIDENCE-OF: R-07734-01023 The result of total() is always a floating
# point value.

query R nosort
SELECT total(x) FROM t1
----
5.000

query R nosort
SELECT total(DISTINCT x) FROM t1
----
3.000


# EVIDENCE-OF: R-19660-56479 The result of sum() is an integer value if
# all non-NULL inputs are integers.

query I nosort label-sum
SELECT sum(x) FROM t1
----
5

query I nosort label-sum-distinct
SELECT sum(DISTINCT x) FROM t1
----
3


# EVIDENCE-OF: R-47496-23053 If any input to sum() is neither an integer
# or a NULL then sum() returns a floating point value which might be an
# approximation to the true sum.

statement ok
INSERT INTO t1 VALUES(4.0,'true')

query R nosort
SELECT sum(x) FROM t1
----
9.000

query R nosort
SELECT sum(DISTINCT x) FROM t1
----
7.000


# TBD-EVIDENCE-OF: R-08904-24719 Sum() will throw an "integer overflow"
# exception if all inputs are integers or NULL and an integer overflow
# occurs at any point during the computation.

statement ok
INSERT INTO t1 VALUES(1<<63,'true');

statement ok
INSERT INTO t1 VALUES(1<<63,'true');

statement ok
INSERT INTO t1 VALUES(-1,'true');

query R nosort
SELECT sum(x) FROM t1
----

query R nosort
SELECT sum(DISTINCT x) FROM t1
----


# TBD-EVIDENCE-OF: R-19553-64528 Total() never throws an integer overflow.

query R nosort
SELECT total(x) FROM t1
----

query R nosort
SELECT total(DISTINCT x) FROM t1
----