github.com/matrixorigin/matrixone@v1.2.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 SELECT median(1); 92 median(1) 93 1.0 94 SELECT median(-6372.2); 95 median(-6372.2) 96 -6372.2 97 SELECT median(NULL); 98 median(null) 99 null 100 SELECT median(ABS(-99)); 101 median(abs(-99)) 102 99.0 103 SELECT median(COS(0) + 2); 104 median(cos(0) + 2) 105 3.0 106 SELECT median(1,2,3); 107 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);"; 108 SELECT median(fekwelwfew); 109 invalid input: column fekwelwfew does not exist 110 SELECT median(3hewh32ioj); 111 invalid input: column 3hewh32ioj does not exist 112 SELECT median("ejwjlvd23232r43f"); 113 invalid argument aggregate function median, bad value [VARCHAR] 114 SELECT median("4"); 115 invalid argument aggregate function median, bad value [VARCHAR] 116 SELECT median(''); 117 invalid argument aggregate function median, bad value [CHAR] 118 DROP TABLE IF EXISTS median_01; 119 CREATE TABLE median_01(id int, d1 tinyint, d2 smallint unsigned, d3 bigint); 120 INSERT INTO median_01 VALUES(1, -128, 65534, 5554584122); 121 INSERT INTO median_01 VALUES(2, 0, 68, -7855122); 122 INSERT INTO median_01 VALUES(3, 45, 0, 67432648932); 123 INSERT INTO median_01 VALUES(4, 45, 5789, 0); 124 INSERT INTO median_01 VALUES(5, NULL, 3782, NULL); 125 INSERT INTO median_01 VALUES(6, -129, 65534, 5554584122); 126 Data truncation: data out of range: data type int8, value '-129' 127 INSERT INTO median_01 VALUES(7, -123, 89555, 5554584122); 128 Data truncation: data out of range: data type uint16, value '89555' 129 INSERT INTO median_01 VALUES(8, -62, 33, 9223372036854775808); 130 Data truncation: data out of range: data type int64, value '9223372036854775808' 131 INSERT INTO median_01 VALUES(2147483648, -62, 33, 9223372036854775808); 132 Data truncation: data out of range: data type int32, value '2147483648' 133 SELECT median(d2) from median_01; 134 median(d2) 135 3782.0 136 SELECT median(d1),median(d2),median(d3),median(id) from median_01; 137 median(d1) median(d2) median(d3) median(id) 138 22.5 3782.0 2.777292061E9 3.0 139 SELECT median(d1) + median(d2) as he, median(d2) * median(d3) as pr from median_01 where id = 2; 140 he pr 141 68.0 -5.34148296E8 142 SELECT median(id) / 4 from median_01; 143 median(id) / 4 144 0.75 145 SELECT median(d3) FROM median_01 WHERE id BETWEEN 1 AND 4; 146 median(d3) 147 2.777292061E9 148 SELECT median(d1),median(d2),median(d3) from median_01 GROUP by d1; 149 median(d1) median(d2) median(d3) 150 -128.0 65534.0 5.554584122E9 151 0.0 68.0 -7855122.0 152 45.0 2894.5 3.3716324466E10 153 null 3782.0 null 154 SELECT d1, d2 FROM median_01 group by median(d1); 155 invalid input: GROUP BY clause cannot contain aggregate functions 156 SELECT median(d1) FROM median_01 WHERE id = ABS(-1) + TAN(45); 157 median(d1) 158 null 159 SELECT ABS(median(d2)), FLOOR(median(id) * 3) from median_01; 160 abs(median(d2)) floor(median(id) * 3) 161 3782.0 9.0 162 SELECT SIN(median(d1)), COS(median(d2)), TAN(median(d2)) FROM median_01; 163 sin(median(d1)) cos(median(d2)) tan(median(d2)) 164 -0.4871745124605095 0.8881213562994406 -0.5175070078184374 165 SELECT TAN(median(d2)), cot(median(d2) * 2), ACOS(median(d1)) FROM median_01; 166 invalid argument acos, bad value 22.5 167 SELECT ATAN(median(d2)), SINH(median(id)) FROM median_01; 168 atan(median(d2)) sinh(median(id)) 169 1.5705319164361722 10.017874927409903 170 SELECT ROUND(median(id) / 2) from median_01; 171 round(median(id) / 2) 172 2.0 173 SELECT CEIL(median(d1)) FROM median_01 WHERE id = 1; 174 ceil(median(d1)) 175 -128.0 176 SELECT power(median(id),3) FROM median_01; 177 power(median(id), 3) 178 27.0 179 SELECT LOG(median(id)) AS a,LN(median(id)) AS b FROM median_01; 180 a b 181 1.0986122886681096 1.0986122886681096 182 SELECT EXP(median(id)) FROM median_01; 183 exp(median(id)) 184 20.085536923187668 185 DROP TABLE IF EXISTS median_02; 186 CREATE TABLE median_02(id int PRIMARY KEY, d1 FLOAT, d2 DOUBLE NOT NULL); 187 INSERT INTO median_02 VALUES(1, 645545.11, 65534.5554584122); 188 INSERT INTO median_02 VALUES(2, NULL, 638239.1); 189 INSERT INTO median_02 VALUES(3, -32783, -56323298.8327382); 190 INSERT INTO median_02 VALUES(4, 0, 389283920.1); 191 INSERT INTO median_02 VALUES(5, 382, 0); 192 INSERT INTO median_02 VALUES(6, 0, -1.8976931348623157E+308); 193 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);"; 194 INSERT INTO median_02 VALUES(7, 4.402823466351E+38, 5554584122); 195 Data truncation: data out of range: data type float32, value '4.402823466351e+38' 196 INSERT INTO median_02 VALUES(8, -55845.0, NULL); 197 constraint violation: Column 'd2' cannot be null 198 SELECT median(d1), median(d2) from median_02; 199 median(d1) median(d2) 200 191.0 65534.5554584122 201 SELECT median(d2) from median_02 group by d2; 202 median(d2) 203 65534.5554584122 204 638239.1 205 -5.63232988327382E7 206 3.892839201E8 207 0.0 208 SELECT median(d2) from median_02 WHERE id BETWEEN 2 AND 4; 209 median(d2) 210 638239.1 211 SELECT median(d1) from median_02 WHERE id = (SELECT id from median_02 where d2 = 65534.5554584122); 212 median(d1) 213 645545.125 214 SELECT ACOS(median(d2)) from median_02; 215 invalid argument acos, bad value 65534.5554584122 216 SELECT CEIL(median(d2)), FLOOR(median(d2)) from median_02; 217 ceil(median(d2)) floor(median(d2)) 218 65535.0 65534.0 219 SELECT power(median(d2),2) from median_02; 220 power(median(d2), 2) 221 4.2947779591317043E9 222 DROP TABLE IF EXISTS median_03; 223 DROP TABLE IF EXISTS median_04; 224 CREATE TABLE median_03(id int, ch smallint NOT NULL, ma bigint unsigned NOT NULL, en FLOAT, ph double, 225 PRIMARY KEY(id)); 226 INSERT INTO median_03 VALUES(1, 88, 99999, -99.98, 88.99); 227 INSERT INTO median_03 VALUES(2, 65, 744515, 0, 78.789); 228 INSERT INTO median_03 VALUES(3, 76, 21, 893293.1, NULL); 229 INSERT INTO median_03 VALUES(4, -367, 3298, NULL, 0); 230 INSERT INTO median_03 VALUES(5, 674, 432, 8767687.0, 0.1); 231 CREATE TABLE median_04(id int, name VARCHAR(10), ch smallint, ma bigint, en FLOAT not NULL, 232 PRIMARY KEY(id)); 233 INSERT INTO median_04 VALUES(1, 'Alice', 327, 45451, 3232.1); 234 INSERT INTO median_04 VALUES(2, 'Bob', 3728, -8889, 899); 235 INSERT INTO median_04 VALUES(3, 'Grace', 0, NULL, 0.1); 236 INSERT INTO median_04 VALUES(4, 'Vicky', 88, 99, 88888.0); 237 INSERT INTO median_04 VALUES(5, 'John', 10, 23211, -78); 238 SELECT median(median_04.ch) from median_03, median_04 where median_03.id = median_04.id; 239 median(median_04.ch) 240 88.0 241 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; 242 a b 243 null null 244 SELECT median(median_03.ch),median(median_03.en) from median_03 WHERE id % 2 =1; 245 median(median_03.ch) median(median_03.en) 246 88.0 893293.125 247 SELECT median(median_03.ch) from median_04 WHERE left(name,2) = 'Al'; 248 invalid input: missing FROM-clause entry for table 'median_03'