Debian Codegraph @

test/percentile.test

raw
1 # 2013-05-28
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is percentile.c extension. This also tests
13 # the SQLITE_ENABLE_ORDERED_SET_AGGREGATES compile-time option.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 # Basic test of the percentile() function.
20 #
21 do_test percentile-1.0 {
22 execsql {
23 CREATE TABLE t1(x);
24 INSERT INTO t1 VALUES(1),(4),(6),(7),(8),(9),(11),(11),(11);
25 }
26 execsql {SELECT percentile(x,0) FROM t1}
27 } {1.0}
28 foreach {in out disc} {
29 100 11.0 11.0
30 50 8.0 8.0
31 12.5 4.0 4.0
32 15 4.4 4.0
33 20 5.2 4.0
34 80 11.0 11.0
35 89 11.0 11.0
36 } {
37 do_test percentile-1.1.$in.1 {
38 execsql {SELECT percentile(x,$in) FROM t1}
39 } $out
40 do_test percentile-1.1.$in.2 {
41 execsql {SELECT percentile_cont(x,$in*0.01) FROM t1}
42 } $out
43 do_test percentile-1.1.$in.3 {
44 execsql {SELECT percentile_disc(x,$in*0.01) FROM t1}
45 } $disc
46 if {$in==50} {
47 do_test percentile-1.1.$in.4 {
48 execsql {SELECT median(x) FROM t1}
49 } $out
50 }
51 ifcapable ordered_set_aggregates {
52 do_test percentile-1.1.$in.5 {
53 execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t1}
54 } $out
55 do_test percentile-1.1.$in.6 {
56 execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)
57 FROM t1}
58 } $out
59 do_test percentile-1.1.$in.7 {
60 execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)
61 FROM t1}
62 } $disc
63 if {$in==50} {
64 do_test percentile-1.1.$in.8 {
65 execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t1}
66 } $out
67 }
68 }
69 }
70 do_execsql_test percentile-1.1.median {
71 SELECT median(x) FROM t1;
72 } 8.0
73 ifcapable ordered_set_aggregates {
74 do_execsql_test percentile-1.1.median {
75 SELECT median() WITHIN GROUP (ORDER BY x) FROM t1;
76 } 8.0
77 do_execsql_test percentile-1.1.distinct.1 {
78 SELECT median(DISTINCT x) FROM t1;
79 } 7.0
80 do_catchsql_test percentile-1.1.distinct.2 {
81 SELECT percentile(DISTINCT 50) WITHIN GROUP (ORDER BY x) FROM t1;
82 } {1 {DISTINCT not allowed on ordered-set aggregate percentile()}}
83 } else {
84 do_catchsql_test percentile-1.1.median {
85 SELECT median() WITHIN GROUP (ORDER BY x) FROM t1;
86 } {1 {near "(": syntax error}}
87 }
88
89 # Add some NULL values.
90 #
91 do_test percentile-1.2 {
92 execsql {INSERT INTO t1 VALUES(NULL),(NULL);}
93 } {}
94 foreach {in out disc} {
95 100 11.0 11.0
96 50 8.0 8.0
97 12.5 4.0 4.0
98 15 4.4 4.0
99 20 5.2 4.0
100 80 11.0 11.0
101 89 11.0 11.0
102 } {
103 do_test percentile-1.3.$in.1 {
104 execsql {SELECT percentile(x,$in) FROM t1}
105 } $out
106 do_test percentile-1.3.$in.2 {
107 execsql {SELECT percentile_cont(x,$in*0.01) FROM t1}
108 } $out
109 do_test percentile-1.3.$in.3 {
110 execsql {SELECT percentile_disc(x,$in*0.01) FROM t1}
111 } $disc
112 if {$in==50} {
113 do_test percentile-1.3.$in.4 {
114 execsql {SELECT median(x) FROM t1}
115 } $out
116 }
117 ifcapable ordered_set_aggregates {
118 do_test percentile-1.3.$in.5 {
119 execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t1}
120 } $out
121 do_test percentile-1.3.$in.6 {
122 execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)
123 FROM t1}
124 } $out
125 do_test percentile-1.3.$in.7 {
126 execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)
127 FROM t1}
128 } $disc
129 if {$in==50} {
130 do_test percentile-1.3.$in.8 {
131 execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t1}
132 } $out
133 }
134 }
135 }
136
137 # The second argument to percentile can change some, but not much.
138 #
139 do_test percentile-1.4.1 {
140 catchsql {SELECT round(percentile(x, 15+0.000001*rowid),1) FROM t1}
141 } {0 4.4}
142 do_test percentile-1.4.2 {
143 catchsql {SELECT round(percentile_cont(x,(15+0.000001*rowid)*0.01),1) FROM t1}
144 } {0 4.4}
145 do_test percentile-1.4.3 {
146 catchsql {SELECT percentile_disc(x, (15+0.000001*rowid)*0.01) FROM t1}
147 } {0 4.0}
148 do_test percentile-1.5.1 {
149 catchsql {SELECT percentile(x, 15+0.1*rowid) FROM t1}
150 } {1 {the fraction argument to percentile() is not the same for all input rows}}
151 do_test percentile-1.5.2 {
152 catchsql {SELECT percentile_cont(x, (15+0.1*rowid)*0.01) FROM t1}
153 } {1 {the fraction argument to percentile_cont() is not the same for all input rows}}
154 do_test percentile-1.5.3 {
155 catchsql {SELECT percentile_disc(x, (15+0.1*rowid)*0.01) FROM t1}
156 } {1 {the fraction argument to percentile_disc() is not the same for all input rows}}
157
158 # Input values in a random order
159 #
160 do_test percentile-1.6 {
161 execsql {
162 CREATE TABLE t2(x);
163 INSERT INTO t2 SELECT x+0.0 FROM t1 ORDER BY random();
164 }
165 } {}
166 foreach {in out disc} {
167 100 11.0 11.0
168 50 8.0 8.0
169 12.5 4.0 4.0
170 15 4.4 4.0
171 20 5.2 4.0
172 80 11.0 11.0
173 89 11.0 11.0
174 } {
175 do_test percentile-1.7.$in.1 {
176 execsql {SELECT percentile(x,$in) FROM t2}
177 } $out
178 do_test percentile-1.7.$in.2 {
179 execsql {SELECT percentile_cont(x,$in*0.01) FROM t2}
180 } $out
181 do_test percentile-1.7.$in.3 {
182 execsql {SELECT percentile_disc(x,$in*0.01) FROM t2}
183 } $disc
184 if {$in==50} {
185 do_test percentile-1.7.$in.4 {
186 execsql {SELECT median(x) FROM t2}
187 } $out
188 }
189 ifcapable ordered_set_aggregates {
190 do_test percentile-1.7.$in.5 {
191 execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t2}
192 } $out
193 do_test percentile-1.7.$in.6 {
194 execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)
195 FROM t2}
196 } $out
197 do_test percentile-1.7.$in.7 {
198 execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)
199 FROM t2}
200 } $disc
201 if {$in==50} {
202 do_test percentile-1.7.$in.8 {
203 execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t2}
204 } $out
205 }
206 }
207 }
208
209 # Wrong number of arguments
210 #
211 do_test percentile-1.8.1 {
212 catchsql {SELECT percentile(x,0,1) FROM t1}
213 } {1 {wrong number of arguments to function percentile()}}
214 do_test percentile-1.8.2 {
215 catchsql {SELECT percentile_cont(x,0,1) FROM t1}
216 } {1 {wrong number of arguments to function percentile_cont()}}
217 do_test percentile-1.8.3 {
218 catchsql {SELECT percentile_disc(x,0,1) FROM t1}
219 } {1 {wrong number of arguments to function percentile_disc()}}
220 do_test percentile-1.8.4 {
221 catchsql {SELECT median(x,0) FROM t1}
222 } {1 {wrong number of arguments to function median()}}
223 ifcapable ordered_set_aggregates {
224 do_test percentile-1.8.5 {
225 catchsql {SELECT percentile(0,1) WITHIN GROUP(ORDER BY x) FROM t1}
226 } {1 {wrong number of arguments to function percentile()}}
227 do_test percentile-1.8.2 {
228 catchsql {SELECT percentile_cont(0,1)WITHIN GROUP (ORDER BY x) FROM t1}
229 } {1 {wrong number of arguments to function percentile_cont()}}
230 do_test percentile-1.8.3 {
231 catchsql {SELECT percentile_disc(0,1)WITHIN GROUP (ORDER BY x) FROM t1}
232 } {1 {wrong number of arguments to function percentile_disc()}}
233 do_test percentile-1.8.4 {
234 catchsql {SELECT median(x) WITHIN GROUP (ORDER BY x) FROM t1}
235 } {1 {wrong number of arguments to function median()}}
236 }
237 do_test percentile-1.9.1 {
238 catchsql {SELECT percentile(x) FROM t1}
239 } {1 {wrong number of arguments to function percentile()}}
240 do_test percentile-1.9.2 {
241 catchsql {SELECT percentile_cont(x) FROM t1}
242 } {1 {wrong number of arguments to function percentile_cont()}}
243 do_test percentile-1.9.3 {
244 catchsql {SELECT percentile_disc(x) FROM t1}
245 } {1 {wrong number of arguments to function percentile_disc()}}
246 do_test percentile-1.9.4 {
247 catchsql {SELECT median() FROM t1}
248 } {1 {wrong number of arguments to function median()}}
249 ifcapable ordered_set_aggregates {
250 do_test percentile-1.9.5 {
251 catchsql {SELECT percentile() WITHIN GROUP(ORDER BY x) FROM t1}
252 } {1 {wrong number of arguments to function percentile()}}
253 do_test percentile-1.9.6 {
254 catchsql {SELECT percentile_cont()WITHIN GROUP (ORDER BY x) FROM t1}
255 } {1 {wrong number of arguments to function percentile_cont()}}
256 do_test percentile-1.9.7 {
257 catchsql {SELECT percentile_disc()WITHIN GROUP (ORDER BY x) FROM t1}
258 } {1 {wrong number of arguments to function percentile_disc()}}
259 }
260
261 # Second argument must be numeric
262 #
263 do_test percentile-1.10 {
264 catchsql {SELECT percentile(x,null) FROM t1}
265 } {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
266 do_test percentile-1.11 {
267 catchsql {SELECT percentile(x,'fifty') FROM t1}
268 } {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
269 do_test percentile-1.12 {
270 catchsql {SELECT percentile(x,x'3530') FROM t1}
271 } {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
272
273 # Second argument is out of range
274 #
275 do_test percentile-1.13 {
276 catchsql {SELECT percentile(x,-0.0000001) FROM t1}
277 } {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
278 do_test percentile-1.14 {
279 catchsql {SELECT percentile(x,100.0000001) FROM t1}
280 } {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}
281 do_test percentile-1.14.2 {
282 catchsql {SELECT percentile_cont(x,1.0000001) FROM t1}
283 } {1 {the fraction argument to percentile_cont() is not between 0.0 and 1.0}}
284 do_test percentile-1.14.3 {
285 catchsql {SELECT percentile_disc(x,1.0000001) FROM t1}
286 } {1 {the fraction argument to percentile_disc() is not between 0.0 and 1.0}}
287
288 # First argument is not NULL and is not NUMERIC
289 #
290 do_test percentile-1.15.1 {
291 catchsql {
292 BEGIN;
293 UPDATE t1 SET x='50' WHERE x IS NULL;
294 SELECT percentile(x, 50) FROM t1;
295 }
296 } {1 {input to percentile() is not numeric}}
297 do_test percentile-1.15.2 {
298 catchsql {
299 SELECT percentile_cont(x, 0.50) FROM t1;
300 }
301 } {1 {input to percentile_cont() is not numeric}}
302 do_test percentile-1.15.3 {
303 catchsql {
304 SELECT percentile_disc(x, 0.50) FROM t1;
305 }
306 } {1 {input to percentile_disc() is not numeric}}
307 do_test percentile-1.15.4 {
308 catchsql {
309 SELECT median(x) FROM t1;
310 }
311 } {1 {input to median() is not numeric}}
312 do_test percentile-1.16 {
313 catchsql {
314 ROLLBACK;
315 BEGIN;
316 UPDATE t1 SET x=x'3530' WHERE x IS NULL;
317 SELECT percentile(x, 50) FROM t1;
318 }
319 } {1 {input to percentile() is not numeric}}
320 do_test percentile-1.17 {
321 catchsql {
322 ROLLBACK;
323 SELECT percentile(x, 50) FROM t1;
324 }
325 } {0 8.0}
326
327 # No non-NULL entries.
328 #
329 do_test percentile-1.18 {
330 execsql {
331 UPDATE t1 SET x=NULL;
332 SELECT ifnull(percentile(x, 50),'NULL') FROM t1
333 }
334 } {NULL}
335
336 # Exactly one non-NULL entry
337 #
338 do_test percentile-1.19 {
339 execsql {
340 UPDATE t1 SET x=12345 WHERE rowid=5;
341 SELECT percentile(x, 0), percentile(x, 50), percentile(x,100) FROM t1
342 }
343 } {12345.0 12345.0 12345.0}
344
345 # Infinity as an input
346 #
347 do_test percentile-1.20.1 {
348 catchsql {
349 DELETE FROM t1;
350 INSERT INTO t1 SELECT x+0.0 FROM t2;
351 UPDATE t1 SET x=1.0e300*1.0e300 WHERE rowid=5;
352 SELECT percentile(x,50) from t1;
353 }
354 } {1 {Inf input to percentile()}}
355 do_test percentile-1.20.2 {
356 catchsql {
357 SELECT percentile_cont(x,0.50) from t1;
358 }
359 } {1 {Inf input to percentile_cont()}}
360 do_test percentile-1.20.3 {
361 catchsql {
362 SELECT percentile_disc(x,0.50) from t1;
363 }
364 } {1 {Inf input to percentile_disc()}}
365 do_test percentile-1.20.4 {
366 catchsql {
367 SELECT median(x) from t1;
368 }
369 } {1 {Inf input to median()}}
370 ifcapable ordered_set_aggregates {
371 do_test percentile-1.20.5 {
372 catchsql {
373 SELECT percentile(50) WITHIN GROUP (ORDER BY x) from t1;
374 }
375 } {1 {Inf input to percentile()}}
376 do_test percentile-1.20.6 {
377 catchsql {
378 SELECT percentile_cont(0.50) WITHIN GROUP (ORDER BY x) from t1;
379 }
380 } {1 {Inf input to percentile_cont()}}
381 do_test percentile-1.20.7 {
382 catchsql {
383 SELECT percentile_disc(0.50) WITHIN GROUP(ORDER BY X) from t1;
384 }
385 } {1 {Inf input to percentile_disc()}}
386 do_test percentile-1.20.8 {
387 catchsql {
388 SELECT median() WITHIN GROUP (ORDER BY x) from t1;
389 }
390 } {1 {Inf input to median()}}
391 }
392 do_test percentile-1.21 {
393 catchsql {
394 UPDATE t1 SET x=-1.0e300*1.0e300 WHERE rowid=5;
395 SELECT percentile(x,50) from t1;
396 }
397 } {1 {Inf input to percentile()}}
398
399 # Million-row Inputs
400 #
401 ifcapable vtab {
402 do_test percentile-2.0 {
403 load_static_extension db wholenumber
404 execsql {
405 CREATE VIRTUAL TABLE nums USING wholenumber;
406 CREATE TABLE t3(x);
407 INSERT INTO t3 SELECT value-1 FROM nums WHERE value BETWEEN 1 AND 500000;
408 INSERT INTO t3 SELECT value*10 FROM nums
409 WHERE value BETWEEN 500000 AND 999999;
410 SELECT count(*) FROM t3;
411 }
412 } {1000000}
413 foreach {in out} {
414 0 0.0
415 100 9999990.0
416 50 2749999.5
417 10 99999.9
418 } {
419 do_test percentile-2.1.$in {
420 execsql {
421 SELECT round(percentile(x, $in),1) from t3;
422 }
423 } $out
424 }
425 }
426
427 # median() as a window function. (2024-08-31)
428 #
429 do_execsql_test percentile-3.0 {
430 DROP TABLE IF EXISTS t1;
431 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
432 INSERT INTO t1 VALUES (1, 'A', 'one', 8.4),
433 (2, 'B', 'two', 7.1),
434 (3, 'C', 'three', 5.9),
435 (4, 'D', 'one', 11.0),
436 (5, 'E', 'two', 12.5),
437 (6, 'F', 'three', 0.0),
438 (7, 'G', 'one', 2.7);
439 }
440 foreach {id oba expr} {
441 1 0 "median(d)"
442 2 0 "percentile(d,50)"
443 3 0 "percentile_cont(d,0.5)"
444 4 1 "median() WITHIN GROUP (ORDER BY d)"
445 5 1 "percentile(50) WITHIN GROUP (ORDER BY d)"
446 6 1 "percentile_cont(0.5) WITHIN GROUP (ORDER BY d)"
447 } {
448 if {$oba} {
449 ifcapable !ordered_set_aggregates break
450 }
451 set sql "SELECT a, b, c, d, \
452 group_concat(b,'.') OVER w1 AS 'elements', \
453 $expr OVER w1 AS 'median' \
454 FROM t1 \
455 WINDOW w1 AS (ORDER BY c, a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)"
456 do_execsql_test percentile-3.$id.1 $sql {
457 1 A one 8.4 A.D 9.7
458 4 D one 11.0 A.D.G 8.4
459 7 G one 2.7 D.G.C 5.9
460 3 C three 5.9 G.C.F 2.7
461 6 F three 0.0 C.F.B 5.9
462 2 B two 7.1 F.B.E 7.1
463 5 E two 12.5 B.E 9.8
464 }
465
466 set sql "SELECT a, b, c, d, \
467 group_concat(b,'.') OVER w1 AS 'elements', \
468 $expr OVER w1 AS 'median' \
469 FROM t1 \
470 WINDOW w1 AS (ORDER BY c, a \
471 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)"
472 do_execsql_test percentile-3.$id.2 $sql {
473 1 A one 8.4 A.D 9.7
474 4 D one 11.0 A.D.G 8.4
475 7 G one 2.7 A.D.G.C 7.15
476 3 C three 5.9 A.D.G.C.F 5.9
477 6 F three 0.0 A.D.G.C.F.B 6.5
478 2 B two 7.1 A.D.G.C.F.B.E 7.1
479 5 E two 12.5 A.D.G.C.F.B.E 7.1
480 }
481
482 set sql "SELECT a, b, c, d, \
483 group_concat(b,'.') OVER w1 AS 'elements', \
484 $expr OVER w1 AS 'median' \
485 FROM t1 \
486 WINDOW w1 AS (ORDER BY c, a \
487 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)"
488 do_execsql_test percentile-3.$id.3 $sql {
489 1 A one 8.4 A.D.G.C.F.B.E 7.1
490 4 D one 11.0 A.D.G.C.F.B.E 7.1
491 7 G one 2.7 D.G.C.F.B.E 6.5
492 3 C three 5.9 G.C.F.B.E 5.9
493 6 F three 0.0 C.F.B.E 6.5
494 2 B two 7.1 F.B.E 7.1
495 5 E two 12.5 B.E 9.8
496 }
497 }
498
499 # Test case adapted from examples shown at
500 # https://database.guide/3-functions-to-calculate-the-median-in-sql/
501 #
502 do_execsql_test percential-4.0 {
503 CREATE TABLE products(
504 vendorId INT,
505 productId INTEGER PRIMARY KEY,
506 productName REAL,
507 price REAL
508 );
509 INSERT INTO products VALUES
510 (1001, 17, 'Left-handed screwdriver', 25.99),
511 (1001, 49, 'Right-handed screwdriver', 25.99),
512 (1001, 216, 'Long weight (blue)', 14.75),
513 (1001, 31, 'Long weight (green)', 11.99),
514 (1002, 37, 'Sledge hammer', 33.49),
515 (1003, 7, 'Chainsaw', 245.00),
516 (1003, 8, 'Straw dog box', 55.99),
517 (1003, 12, 'Hammock', 11.01),
518 (1004, 113, 'Teapot', 12.45),
519 (1004, 117, 'Bottomless coffee mug', 9.99);
520 }
521 do_execsql_test percentile-4.1 {
522 SELECT VendorId, ProductId, /* ProductName,*/ Price,
523 avg(price) OVER (PARTITION BY vendorId) AS "Average",
524 median(price) OVER (PARTITION BY vendorId) AS "Median"
525 FROM products
526 ORDER BY vendorId, productId;
527 } {
528 1001 17 25.99 19.68 20.37
529 1001 31 11.99 19.68 20.37
530 1001 49 25.99 19.68 20.37
531 1001 216 14.75 19.68 20.37
532 1002 37 33.49 33.49 33.49
533 1003 7 245.0 104.0 55.99
534 1003 8 55.99 104.0 55.99
535 1003 12 11.01 104.0 55.99
536 1004 113 12.45 11.22 11.22
537 1004 117 9.99 11.22 11.22
538 }
539 do_execsql_test percentile-4.2 {
540 SELECT vendorId, median(price) FROM products
541 GROUP BY 1 ORDER BY 1;
542 } {1001 20.37 1002 33.49 1003 55.99 1004 11.22}
543
544 do_execsql_test percentile-5.0 {
545 CREATE TABLE user(name TEXT, class TEXT, cost REAL);
546 INSERT INTO user VALUES
547 ('Alice', 'Y', 3578.27),
548 ('Bob', 'X', 3399.99),
549 ('Cindy', 'Z', 699.10),
550 ('Dave', 'Y', 3078.27),
551 ('Emma', 'Z', 2319.99),
552 ('Fred', 'Y', 539.99),
553 ('Gina', 'X', 2320.49),
554 ('Hank', 'W', 24.99),
555 ('Irma', 'W', 24.99),
556 ('Jake', 'X', 2234.99),
557 ('Kim', 'Y', 4319.99),
558 ('Liam', 'X', 4968.59),
559 ('Mia', 'W', 59.53),
560 ('Nate', 'W', 23.50);
561 }
562 do_execsql_test percentile-5.1 {
563 SELECT name, class, cost,
564 percentile(cost, 0) OVER w1 AS 'P0',
565 percentile(cost, 25) OVER w1 AS 'P1',
566 percentile(cost, 50) OVER w1 AS 'P2',
567 percentile(cost, 75) OVER w1 AS 'P3',
568 percentile(cost, 100) OVER w1 AS 'P4'
569 FROM user
570 WINDOW w1 AS (PARTITION BY class)
571 ORDER BY class, cost;
572 } {
573 Nate W 23.5 23.5 24.6175 24.99 33.625 59.53
574 Hank W 24.99 23.5 24.6175 24.99 33.625 59.53
575 Irma W 24.99 23.5 24.6175 24.99 33.625 59.53
576 Mia W 59.53 23.5 24.6175 24.99 33.625 59.53
577 Jake X 2234.99 2234.99 2299.115 2860.24 3792.14 4968.59
578 Gina X 2320.49 2234.99 2299.115 2860.24 3792.14 4968.59
579 Bob X 3399.99 2234.99 2299.115 2860.24 3792.14 4968.59
580 Liam X 4968.59 2234.99 2299.115 2860.24 3792.14 4968.59
581 Fred Y 539.99 539.99 2443.7 3328.27 3763.7 4319.99
582 Dave Y 3078.27 539.99 2443.7 3328.27 3763.7 4319.99
583 Alice Y 3578.27 539.99 2443.7 3328.27 3763.7 4319.99
584 Kim Y 4319.99 539.99 2443.7 3328.27 3763.7 4319.99
585 Cindy Z 699.1 699.1 1104.3225 1509.545 1914.7675 2319.99
586 Emma Z 2319.99 699.1 1104.3225 1509.545 1914.7675 2319.99
587 }
588
589 # Fuzzer find.
590 do_execsql_test percentile-6.0 {
591 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)
592 SELECT median(iif(n%2,0.1,1.0)) FROM c;
593 } 0.55
594
595 # Bug 2026-06-03T07:26:42Z
596 # Use tail recursion in percentSort() to prevent stack overflow
597 #
598 do_execsql_test percentile-7.0 {
599 WITH RECURSIVE t1(n,x) AS (
600 VALUES(1,1.0)
601 UNION ALL
602 SELECT n+1, if(n%2,n*2.0,999998.0-2.0*n) FROM t1 WHERE n<1000000
603 ) SELECT median(x) FROM t1;
604 } 499998.0
605
606 finish_test