gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/percentile.test (about) 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 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # Basic test of the percentile() function. 19 # 20 do_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} 28 foreach {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 # 44 do_test percentile-1.2 { 45 execsql {INSERT INTO t1 VALUES(NULL),(NULL);} 46 } {} 47 foreach {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 # 63 do_test percentile-1.4 { 64 catchsql {SELECT round(percentile(x, 15+0.000001*rowid),1) FROM t1} 65 } {0 4.4} 66 do_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 # 72 do_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 } {} 78 foreach {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 # 94 do_test percentile-1.8 { 95 catchsql {SELECT percentile(x,0,1) FROM t1} 96 } {1 {wrong number of arguments to function percentile()}} 97 do_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 # 103 do_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}} 106 do_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}} 109 do_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 # 115 do_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}} 118 do_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 # 124 do_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}} 131 do_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}} 139 do_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 # 148 do_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 # 157 do_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 # 166 do_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()}} 174 do_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 # 183 ifcapable 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 209 finish_test