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 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18# Basic test of the percentile() function. 19# 20do_test percentile-1.0 { 21 load_static_extension db percentile 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} 28foreach {in out} { 29 100 11.0 30 50 8.0 31 12.5 4.0 32 15 4.4 33 20 5.2 34 80 11.0 35 89 11.0 36} { 37 do_test percentile-1.1.$in { 38 execsql {SELECT percentile(x,$in) FROM t1} 39 } $out 40} 41 42# Add some NULL values. 43# 44do_test percentile-1.2 { 45 execsql {INSERT INTO t1 VALUES(NULL),(NULL);} 46} {} 47foreach {in out} { 48 100 11.0 49 50 8.0 50 12.5 4.0 51 15 4.4 52 20 5.2 53 80 11.0 54 89 11.0 55} { 56 do_test percentile-1.3.$in { 57 execsql {SELECT percentile(x,$in) FROM t1} 58 } $out 59} 60 61# The second argument to percentile can change some, but not much. 62# 63do_test percentile-1.4 { 64 catchsql {SELECT round(percentile(x, 15+0.000001*rowid),1) FROM t1} 65} {0 4.4} 66do_test percentile-1.5 { 67 catchsql {SELECT round(percentile(x, 15+0.1*rowid),1) FROM t1} 68} {1 {2nd argument to percentile() is not the same for all input rows}} 69 70# Input values in a random order 71# 72do_test percentile-1.6 { 73 execsql { 74 CREATE TABLE t2(x); 75 INSERT INTO t2 SELECT x+0.0 FROM t1 ORDER BY random(); 76 } 77} {} 78foreach {in out} { 79 100 11.0 80 50 8.0 81 12.5 4.0 82 15 4.4 83 20 5.2 84 80 11.0 85 89 11.0 86} { 87 do_test percentile-1.7.$in { 88 execsql {SELECT percentile(x,$in) FROM t2} 89 } $out 90} 91 92# Wrong number of arguments 93# 94do_test percentile-1.8 { 95 catchsql {SELECT percentile(x,0,1) FROM t1} 96} {1 {wrong number of arguments to function percentile()}} 97do_test percentile-1.9 { 98 catchsql {SELECT percentile(x) FROM t1} 99} {1 {wrong number of arguments to function percentile()}} 100 101# Second argument must be numeric 102# 103do_test percentile-1.10 { 104 catchsql {SELECT percentile(x,null) FROM t1} 105} {1 {2nd argument to percentile() is not a number between 0.0 and 100.0}} 106do_test percentile-1.11 { 107 catchsql {SELECT percentile(x,'fifty') FROM t1} 108} {1 {2nd argument to percentile() is not a number between 0.0 and 100.0}} 109do_test percentile-1.12 { 110 catchsql {SELECT percentile(x,x'3530') FROM t1} 111} {1 {2nd argument to percentile() is not a number between 0.0 and 100.0}} 112 113# Second argument is out of range 114# 115do_test percentile-1.13 { 116 catchsql {SELECT percentile(x,-0.0000001) FROM t1} 117} {1 {2nd argument to percentile() is not a number between 0.0 and 100.0}} 118do_test percentile-1.14 { 119 catchsql {SELECT percentile(x,100.0000001) FROM t1} 120} {1 {2nd argument to percentile() is not a number between 0.0 and 100.0}} 121 122# First argument is not NULL and is not NUMERIC 123# 124do_test percentile-1.15 { 125 catchsql { 126 BEGIN; 127 UPDATE t1 SET x='50' WHERE x IS NULL; 128 SELECT percentile(x, 50) FROM t1; 129 } 130} {1 {1st argument to percentile() is not numeric}} 131do_test percentile-1.16 { 132 catchsql { 133 ROLLBACK; 134 BEGIN; 135 UPDATE t1 SET x=x'3530' WHERE x IS NULL; 136 SELECT percentile(x, 50) FROM t1; 137 } 138} {1 {1st argument to percentile() is not numeric}} 139do_test percentile-1.17 { 140 catchsql { 141 ROLLBACK; 142 SELECT percentile(x, 50) FROM t1; 143 } 144} {0 8.0} 145 146# No non-NULL entries. 147# 148do_test percentile-1.18 { 149 execsql { 150 UPDATE t1 SET x=NULL; 151 SELECT ifnull(percentile(x, 50),'NULL') FROM t1 152 } 153} {NULL} 154 155# Exactly one non-NULL entry 156# 157do_test percentile-1.19 { 158 execsql { 159 UPDATE t1 SET x=12345 WHERE rowid=5; 160 SELECT percentile(x, 0), percentile(x, 50), percentile(x,100) FROM t1 161 } 162} {12345.0 12345.0 12345.0} 163 164# Infinity as an input 165# 166do_test percentile-1.20 { 167 catchsql { 168 DELETE FROM t1; 169 INSERT INTO t1 SELECT x+0.0 FROM t2; 170 UPDATE t1 SET x=1.0e300*1.0e300 WHERE rowid=5; 171 SELECT percentile(x,50) from t1; 172 } 173} {1 {Inf input to percentile()}} 174do_test percentile-1.21 { 175 catchsql { 176 UPDATE t1 SET x=-1.0e300*1.0e300 WHERE rowid=5; 177 SELECT percentile(x,50) from t1; 178 } 179} {1 {Inf input to percentile()}} 180 181# Million-row Inputs 182# 183ifcapable vtab { 184 do_test percentile-2.0 { 185 load_static_extension db wholenumber 186 execsql { 187 CREATE VIRTUAL TABLE nums USING wholenumber; 188 CREATE TABLE t3(x); 189 INSERT INTO t3 SELECT value-1 FROM nums WHERE value BETWEEN 1 AND 500000; 190 INSERT INTO t3 SELECT value*10 FROM nums 191 WHERE value BETWEEN 500000 AND 999999; 192 SELECT count(*) FROM t3; 193 } 194 } {1000000} 195 foreach {in out} { 196 0 0.0 197 100 9999990.0 198 50 2749999.5 199 10 99999.9 200 } { 201 do_test percentile-2.1.$in { 202 execsql { 203 SELECT round(percentile(x, $in),1) from t3; 204 } 205 } $out 206 } 207} 208 209finish_test 210