github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_aggr_median.result (about) 1 select median(null); 2 median(null) 3 null 4 drop table if exists t1; 5 create table t1 (a int,b int); 6 insert into t1 values (1,null); 7 select median(b) from t1; 8 median(b) 9 null 10 insert into t1 values (1,1); 11 select median(b) from t1; 12 median(b) 13 1.0 14 insert into t1 values (1,2); 15 select median(b) from t1; 16 median(b) 17 1.5 18 select median(b) from t1 group by a order by a; 19 median(b) 20 1.5 21 insert into t1 values (2,1),(2,2),(2,3),(2,4); 22 select median(b) from t1 group by a order by a; 23 median(b) 24 1.5 25 2.5 26 insert into t1 values (2,null); 27 select median(b) from t1 group by a order by a; 28 median(b) 29 1.5 30 2.5 31 drop table if exists t1; 32 create table t1 (a int,b float,c double); 33 insert into t1 values (1,null,null); 34 select median(b),median(c) from t1; 35 median(b) median(c) 36 null null 37 insert into t1 values (1,1.1,1.1); 38 select median(b),median(c) from t1; 39 median(b) median(c) 40 1.100000023841858 1.1 41 insert into t1 values (1,2.2,2.2); 42 select median(b),median(c) from t1; 43 median(b) median(c) 44 1.6500000953674316 1.6500000000000001 45 select median(b),median(c) from t1 group by a order by a; 46 median(b) median(c) 47 1.6500000953674316 1.6500000000000001 48 insert into t1 values (2,1.1,1.1),(2,2.2,2.2),(2,3.3,3.3),(2,4.4,4.4); 49 select median(b),median(c) from t1 group by a order by a; 50 median(b) median(c) 51 1.6500000953674316 1.6500000000000001 52 2.75 2.75 53 insert into t1 values (2,null,null); 54 select median(b),median(c) from t1 group by a order by a; 55 median(b) median(c) 56 1.6500000953674316 1.6500000000000001 57 2.75 2.75 58 drop table if exists t1; 59 create table t1 (a int,b decimal(10,2),c decimal(34,10)); 60 insert into t1 values (1,null,null); 61 select median(b),median(c) from t1; 62 median(b) median(c) 63 null null 64 insert into t1 values (1,'1.1','1.1'); 65 select median(b),median(c) from t1; 66 median(b) median(c) 67 1.10 1.1000000000 68 insert into t1 values (1,'2.2','2.2'); 69 select median(b),median(c) from t1; 70 median(b) median(c) 71 1.65 1.6500000000 72 select median(b),median(c) from t1 group by a order by a; 73 median(b) median(c) 74 1.65 1.6500000000 75 insert into t1 values (2,'1.1','1.1'),('2','2.2','2.2'),('2','3.3','3.3'),('2','4.4','4.4'); 76 select median(b),median(c) from t1 group by a order by a; 77 median(b) median(c) 78 1.65 1.6500000000 79 2.75 2.7500000000 80 insert into t1 values (2,null,null); 81 select median(b),median(c) from t1 group by a order by a; 82 median(b) median(c) 83 1.65 1.6500000000 84 2.75 2.7500000000 85 select median(distinct a) from t1; 86 not supported: median in distinct mode 87 drop table if exists t1; 88 create table t1 (a int,b varchar(10)); 89 select median(b) from t1; 90 invalid argument aggregate function median, bad value [VARCHAR] 91 92 SELECT median(1); 93 median(1) 94 1.0 95 SELECT median(-6372.2); 96 median(-6372.2) 97 -6372.2 98 SELECT median(NULL); 99 median(null) 100 null 101 SELECT median(ABS(-99)); 102 median(abs(-99)) 103 99.0 104 SELECT median(COS(0) + 2); 105 median(cos(0) + 2) 106 3.0 107 SELECT median(1,2,3); 108 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 16 near ",2,3);"; 109 SELECT median(fekwelwfew); 110 invalid input: column fekwelwfew does not exist 111 SELECT median(3hewh32ioj); 112 invalid input: column 3hewh32ioj does not exist 113 SELECT median("ejwjlvd23232r43f"); 114 invalid argument aggregate function median, bad value [VARCHAR] 115 SELECT median("4"); 116 invalid argument aggregate function median, bad value [VARCHAR] 117 SELECT median(''); 118 invalid argument aggregate function median, bad value [VARCHAR] 119 DROP TABLE IF EXISTS median_01; 120 CREATE TABLE median_01(id int, d1 tinyint, d2 smallint unsigned, d3 bigint); 121 INSERT INTO median_01 VALUES(1, -128, 65534, 5554584122); 122 INSERT INTO median_01 VALUES(2, 0, 68, -7855122); 123 INSERT INTO median_01 VALUES(3, 45, 0, 67432648932); 124 INSERT INTO median_01 VALUES(4, 45, 5789, 0); 125 INSERT INTO median_01 VALUES(5, NULL, 3782, NULL); 126 INSERT INTO median_01 VALUES(6, -129, 65534, 5554584122); 127 Data truncation: data out of range: data type int8, value '-129' 128 INSERT INTO median_01 VALUES(7, -123, 89555, 5554584122); 129 Data truncation: data out of range: data type uint16, value '89555' 130 INSERT INTO median_01 VALUES(8, -62, 33, 9223372036854775808); 131 Data truncation: data out of range: data type int64, 132 INSERT INTO median_01 VALUES(2147483648, -62, 33, 9223372036854775808); 133 Data truncation: data out of range: data type int32, value '2147483648' 134 SELECT median(d2) from median_01; 135 median(d2) 136 3782.0 137 SELECT median(d1),median(d2),median(d3),median(id) from median_01; 138 median(d1) median(d2) median(d3) median(id) 139 22.5 3782.0 2.777292061E9 3.0 140 SELECT median(d1) + median(d2) as he, median(d2) * median(d3) as pr from median_01 where id = 2; 141 he pr 142 68.0 -5.34148296E8 143 SELECT median(id) / 4 from median_01; 144 median(id) / 4 145 0.75 146 SELECT median(d3) FROM median_01 WHERE id BETWEEN 1 AND 4; 147 median(d3) 148 2.777292061E9 149 SELECT median(d1),median(d2),median(d3) from median_01 GROUP by d1; 150 median(d1) median(d2) median(d3) 151 -128.0 65534.0 5.554584122E9 152 0.0 68.0 -7855122.0 153 45.0 2894.5 3.3716324466E10 154 null 3782.0 null 155 SELECT d1, d2 FROM median_01 group by median(d1); 156 invalid input: GROUP BY clause cannot contain aggregate functions 157 SELECT median(d1) FROM median_01 WHERE id = ABS(-1) + TAN(45); 158 median(d1) 159 null 160 SELECT ABS(median(d2)), FLOOR(median(id) * 3) from median_01; 161 abs(median(d2)) floor(median(id) * 3) 162 3782.0 9.0 163 SELECT SIN(median(d1)), COS(median(d2)), TAN(median(d2)) FROM median_01; 164 sin(median(d1)) cos(median(d2)) tan(median(d2)) 165 -0.4871745124605095 0.8881213562994406 -0.5175070078184374 166 SELECT TAN(median(d2)), cot(median(d2) * 2), ACOS(median(d1)) FROM median_01; 167 tan(median(d2)) cot(median(d2) * 2) acos(median(d1)) 168 -0.5175070078184374 -0.707416987399939 null 169 SELECT ATAN(median(d2)), SINH(median(id)) FROM median_01; 170 atan(median(d2)) sinh(median(id)) 171 1.5705319164361722 10.017874927409903 172 SELECT ROUND(median(id) / 2) from median_01; 173 round(median(id) / 2) 174 2.0 175 SELECT CEIL(median(d1)) FROM median_01 WHERE id = 1; 176 ceil(median(d1)) 177 -128.0 178 SELECT power(median(id),3) FROM median_01; 179 power(median(id), 3) 180 27.0 181 SELECT LOG(median(id)) AS a,LN(median(id)) AS b FROM median_01; 182 a b 183 1.0986122886681096 1.0986122886681096 184 SELECT EXP(median(id)) FROM median_01; 185 exp(median(id)) 186 20.085536923187668 187 DROP TABLE IF EXISTS median_02; 188 CREATE TABLE median_02(id int PRIMARY KEY, d1 FLOAT, d2 DOUBLE NOT NULL); 189 INSERT INTO median_02 VALUES(1, 645545.11, 65534.5554584122); 190 INSERT INTO median_02 VALUES(2, NULL, 638239.1); 191 INSERT INTO median_02 VALUES(3, -32783, -56323298.8327382); 192 INSERT INTO median_02 VALUES(4, 0, 389283920.1); 193 INSERT INTO median_02 VALUES(5, 382, 0); 194 INSERT INTO median_02 VALUES(6, 0, -1.8976931348623157E+308); 195 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 59 near "1.8976931348623157E+308);"; 196 INSERT INTO median_02 VALUES(7, 4.402823466351E+38, 5554584122); 197 Data truncation: data out of range: data type float32, value '4.402823466351e+38' 198 INSERT INTO median_02 VALUES(8, -55845.0, NULL); 199 constraint violation: Column 'd2' cannot be null 200 SELECT median(d1), median(d2) from median_02; 201 median(d1) median(d2) 202 191.0 65534.5554584122 203 SELECT median(d2) from median_02 group by d2; 204 median(d2) 205 65534.5554584122 206 638239.1 207 -5.63232988327382E7 208 3.892839201E8 209 0.0 210 SELECT median(d2) from median_02 WHERE id BETWEEN 2 AND 4; 211 median(d2) 212 638239.1 213 SELECT median(d1) from median_02 WHERE id = (SELECT id from median_02 where d2 = 65534.5554584122); 214 median(d1) 215 645545.125 216 SELECT ACOS(median(d2)) from median_02; 217 acos(median(d2)) 218 null 219 SELECT CEIL(median(d2)), FLOOR(median(d2)) from median_02; 220 ceil(median(d2)) floor(median(d2)) 221 65535.0 65534.0 222 SELECT power(median(d2),2) from median_02; 223 power(median(d2), 2) 224 4.2947779591317043E9 225 DROP TABLE IF EXISTS median_03; 226 DROP TABLE IF EXISTS median_04; 227 CREATE TABLE median_03(id int, ch smallint NOT NULL, ma bigint unsigned NOT NULL, en FLOAT, ph double, 228 PRIMARY KEY(id)); 229 INSERT INTO median_03 VALUES(1, 88, 99999, -99.98, 88.99); 230 INSERT INTO median_03 VALUES(2, 65, 744515, 0, 78.789); 231 INSERT INTO median_03 VALUES(3, 76, 21, 893293.1, NULL); 232 INSERT INTO median_03 VALUES(4, -367, 3298, NULL, 0); 233 INSERT INTO median_03 VALUES(5, 674, 432, 8767687.0, 0.1); 234 CREATE TABLE median_04(id int, name VARCHAR(10), ch smallint, ma bigint, en FLOAT not NULL, 235 PRIMARY KEY(id)); 236 INSERT INTO median_04 VALUES(1, 'Alice', 327, 45451, 3232.1); 237 INSERT INTO median_04 VALUES(2, 'Bob', 3728, -8889, 899); 238 INSERT INTO median_04 VALUES(3, 'Grace', 0, NULL, 0.1); 239 INSERT INTO median_04 VALUES(4, 'Vicky', 88, 99, 88888.0); 240 INSERT INTO median_04 VALUES(5, 'John', 10, 23211, -78); 241 SELECT median(median_04.ch) from median_03, median_04 where median_03.id = median_04.id; 242 median(median_04.ch) 243 88.0 244 SELECT median(median_03.ma) AS a, median(median_04.ma) AS b from median_03 join median_04 ON median_03.ph = median_04.en; 245 a b 246 null null 247 SELECT median(median_03.ch),median(median_03.en) from median_03 WHERE id % 2 =1; 248 median(median_03.ch) median(median_03.en) 249 88.0 893293.125 250 SELECT median(median_03.ch) from median_04 WHERE left(name,2) = 'Al'; 251 invalid input: missing FROM-clause entry for table 'median_03'