github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/array/array.result (about) 1 drop database if exists vecdb; 2 create database vecdb; 3 use vecdb; 4 drop table if exists vec_table; 5 create table vec_table(a int, b vecf32(3), c vecf64(3)); 6 desc vec_table; 7 Field Type Null Key Default Extra Comment 8 a INT(32) YES null 9 b VECF32(3) YES null 10 c VECF64(3) YES null 11 insert into vec_table values(1, "[1,2,3]", "[4,5,6]"); 12 select * from vec_table; 13 a b c 14 1 [1, 2, 3] [4, 5, 6] 15 select b+b from vec_table; 16 b + b 17 [2, 4, 6] 18 select b-b from vec_table; 19 b - b 20 [0, 0, 0] 21 select b*b from vec_table; 22 b * b 23 [1, 4, 9] 24 select b/b from vec_table; 25 b / b 26 [1, 1, 1] 27 select * from vec_table where b> "[1,2,3]"; 28 a b c 29 select * from vec_table where b< "[1,2,3]"; 30 a b c 31 select * from vec_table where b>= "[1,2,3]"; 32 a b c 33 1 [1, 2, 3] [4, 5, 6] 34 select * from vec_table where b<= "[1,2,3]"; 35 a b c 36 1 [1, 2, 3] [4, 5, 6] 37 select * from vec_table where b!= "[1,2,3]"; 38 a b c 39 select * from vec_table where b= "[1,2,3]"; 40 a b c 41 1 [1, 2, 3] [4, 5, 6] 42 select * from vec_table where b= cast("[1,2,3]" as vecf32(3)); 43 a b c 44 1 [1, 2, 3] [4, 5, 6] 45 select b + "[1,2,3]" from vec_table; 46 b + [1,2,3] 47 [2, 4, 6] 48 select b + "[1,2]" from vec_table; 49 invalid input: vector ops between different dimensions (3, 2) is not permitted. 50 select b + "[1,2,3,4]" from vec_table; 51 invalid input: vector ops between different dimensions (3, 4) is not permitted. 52 select cast("[1,2,3]" as vecf32(3)); 53 cast([1,2,3] as vecf32(3)) 54 [1, 2, 3] 55 select b + "[1,2,3]" from vec_table; 56 b + [1,2,3] 57 [2, 4, 6] 58 select b + sqrt(b) from vec_table; 59 b + sqrt(b) 60 [2, 3.414213562373095, 4.732050807568877] 61 select b + c from vec_table; 62 b + c 63 [5, 7, 9] 64 select abs(b) from vec_table; 65 abs(b) 66 [1, 2, 3] 67 select abs(cast("[-1,-2,3]" as vecf32(3))); 68 abs(cast([-1,-2,3] as vecf32(3))) 69 [1, 2, 3] 70 select sqrt(b) from vec_table; 71 sqrt(b) 72 [1, 1.4142135623730951, 1.7320508075688772] 73 select summation(b) from vec_table; 74 summation(b) 75 6.0 76 select l1_norm(b) from vec_table; 77 l1_norm(b) 78 6.0 79 select l2_norm(b) from vec_table; 80 l2_norm(b) 81 3.741657386773941 82 select vector_dims(b) from vec_table; 83 vector_dims(b) 84 3 85 select inner_product(b,"[1,2,3]") from vec_table; 86 inner_product(b, [1,2,3]) 87 14.0 88 select cosine_similarity(b,"[1,2,3]") from vec_table; 89 cosine_similarity(b, [1,2,3]) 90 1.0 91 select l2_distance(b,"[1,2,3]") from vec_table; 92 l2_distance(b, [1,2,3]) 93 0.0 94 select cosine_distance(b,"[1,2,3]") from vec_table; 95 cosine_distance(b, [1,2,3]) 96 0.0 97 select normalize_l2(b) from vec_table; 98 normalize_l2(b) 99 [0.26726124, 0.5345225, 0.80178374] 100 select * FROM vec_table ORDER BY cosine_similarity(b, '[3,1,2]') LIMIT 5; 101 a b c 102 1 [1, 2, 3] [4, 5, 6] 103 select * FROM vec_table ORDER BY l2_distance(b, '[3,1,2]') LIMIT 5; 104 a b c 105 1 [1, 2, 3] [4, 5, 6] 106 select * FROM vec_table ORDER BY inner_product(b, '[3,1,2]') LIMIT 5; 107 a b c 108 1 [1, 2, 3] [4, 5, 6] 109 select b + "[1,2,3" from vec_table; 110 internal error: malformed vector input: [1,2,3 111 select b + "1,2,3" from vec_table; 112 internal error: malformed vector input: 1,2,3 113 create table t2(a int, b vecf32(3) primary key); 114 not supported: VECTOR column 'b' cannot be in primary key 115 create unique index t3 on vec_table(b); 116 not supported: VECTOR column 'b' cannot be in index 117 create table t3(a int, b vecf32(65537)); 118 Data truncation: data out of range: data type vecf32, typeLen is over the MaxVectorLen : 65535 119 select sqrt(cast("[1,2,-3]" as vecf32(3))); 120 invalid argument Sqrt, bad value -3 121 select b/(cast("[1,2,0]" as vecf32(3))) from vec_table; 122 Data truncation: division by zero 123 select count(b) from vec_table; 124 count(b) 125 1 126 create table t4(a int, b vecf32(5), c vecf64(5)); 127 insert into t4 values(1, "[1,2,3,4,5]", "[1,2,3,4,5]"); 128 insert into t4 values(1, "[1,2]", "[1,2]"); 129 invalid input: expected vector dimension 5 != actual dimension 2. 130 insert into t4 values(1, "[1,2,3,4,5,6]", "[1,2,3,4,5,6]"); 131 invalid input: expected vector dimension 5 != actual dimension 6. 132 select * from t4; 133 a b c 134 1 [1, 2, 3, 4, 5] [1, 2, 3, 4, 5] 135 create table t5(a int, b vecf32(3)); 136 insert into t5 values(1, cast(unhex('7e98b23e9e10383b2f41133f') as blob)); 137 insert into t5 values(2, cast(unhex('0363733ff13e0b3f7aa39d3e') as blob)); 138 insert into t5 values(3, cast(unhex('be1ac03e485d083ef6bc723f') as blob)); 139 insert into t5 values(4, "[0,2,3]"); 140 insert into t5 values(5, cast(unhex('05486c3f3ee2863e713d503dd58e8e3e7b88743f') as blob)); -- this is float32[5] 141 invalid input: expected vector dimension 3 != actual dimension 5. 142 insert into t5 values(6, cast(unhex('9be2123fcf92de3e') as blob)); -- this is float32[2] 143 invalid input: expected vector dimension 3 != actual dimension 2. 144 select * from t5; 145 a b 146 1 [0.34881967, 0.0028086076, 0.5752134] 147 2 [0.95072955, 0.54392916, 0.30788785] 148 3 [0.37520403, 0.13316834, 0.9481958] 149 4 [0, 2, 3] 150 select * from t5 where t5.b > "[0,0,0]"; 151 a b 152 1 [0.34881967, 0.0028086076, 0.5752134] 153 2 [0.95072955, 0.54392916, 0.30788785] 154 3 [0.37520403, 0.13316834, 0.9481958] 155 4 [0, 2, 3] 156 select hex(b) from t5; 157 hex(b) 158 7e98b23e9e10383b2f41133f 159 0363733ff13e0b3f7aa39d3e 160 be1ac03e485d083ef6bc723f 161 000000000000004000004040 162 create table t6(a int, b vecf32(3)); 163 insert into t6 values(1, null); 164 insert into t6 (a,b) values (1, '[1,2,3]'), (2, '[4,5,6]'), (3, '[2,1,1]'), (4, '[7,8,9]'), (5, '[0,0,0]'), (6, '[3,1,2]'); 165 select * from t6; 166 a b 167 1 null 168 1 [1, 2, 3] 169 2 [4, 5, 6] 170 3 [2, 1, 1] 171 4 [7, 8, 9] 172 5 [0, 0, 0] 173 6 [3, 1, 2] 174 update t6 set b = NULL; 175 select * from t6; 176 a b 177 1 null 178 1 null 179 2 null 180 3 null 181 4 null 182 5 null 183 6 null 184 create table t7(a int, b vecf32(3), c vecf32(5)); 185 insert into t7 values(1, NULL,NULL); 186 insert into t7 values(2, "[0.8166459, 0.66616553, 0.4886152]", NULL); 187 insert into t7 values(3, "[0.1726299, 3.2908857, 30.433094]","[0.45052445, 2.1984527, 9.579752, 123.48039, 4635.894]"); 188 insert into t7 values(4, "[8.560689, 6.790359, 821.9778]", "[0.46323407, 23.498016, 563.923, 56.076736, 8732.958]"); 189 select * from t7; 190 a b c 191 1 null null 192 2 [0.8166459, 0.66616553, 0.4886152] null 193 3 [0.1726299, 3.2908857, 30.433094] [0.45052445, 2.1984527, 9.579752, 123.48039, 4635.894] 194 4 [8.560689, 6.790359, 821.9778] [0.46323407, 23.498016, 563.923, 56.076736, 8732.958] 195 select a, b + b, c + c from t7; 196 a b + b c + c 197 1 null null 198 2 [1.6332918, 1.3323311, 0.9772304] null 199 3 [0.3452598, 6.5817714, 60.866188] [0.9010489, 4.3969054, 19.159504, 246.96078, 9271.788] 200 4 [17.121378, 13.580718, 1643.9556] [0.92646813, 46.996033, 1127.846, 112.15347, 17465.916] 201 select a, b * b, c * c from t7; 202 a b * b c * c 203 1 null null 204 2 [0.6669106, 0.44377652, 0.23874483] null 205 3 [0.02980108, 10.829928, 926.1732] [0.20297228, 4.8331943, 91.771645, 15247.407, 21491514] 206 4 [73.28539, 46.108974, 675647.5] [0.2145858, 552.1568, 318009.12, 3144.6003, 76264550] 207 select l2_norm(b), l2_norm(c) from t7; 208 l2_norm(b) l2_norm(c) 209 null null 210 1.1616505074810406 null 211 30.610993802842444 4637.5486836009195 212 822.0504062713129 8751.35770370157 213 insert into vec_table values(2, "[0,2,3]", "[4,4,6]"); 214 insert into vec_table values(3, "[1,3,3]", "[4,1,6]"); 215 select mo_ctl('dn', 'flush', 'vecdb.vec_table'); 216 mo_ctl(dn, flush, vecdb.vec_table) 217 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 218 select mo_ctl('dn', 'flush', 'vecdb.t6'); 219 mo_ctl(dn, flush, vecdb.t6) 220 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 221 select * from vec_table where b> "[1,2,3]"; 222 a b c 223 3 [1, 3, 3] [4, 1, 6] 224 select * from vec_table where b!= "[1,2,3]"; 225 a b c 226 2 [0, 2, 3] [4, 4, 6] 227 3 [1, 3, 3] [4, 1, 6] 228 select * from vec_table where b= "[1,2,3]"; 229 a b c 230 1 [1, 2, 3] [4, 5, 6] 231 create table vec_table1(a int, b vecf32(3), c vecf64(3)); 232 insert into vec_table1 values(1, "[1,2,3]", "[4,5,6]"); 233 select * from vec_table1; 234 a b c 235 1 [1, 2, 3] [4, 5, 6] 236 create table vec_table2(a int primary key, b vecf32(3), c vecf64(3)); 237 insert into vec_table2 values(1, "[1,2,3]", "[4,5,6]"); 238 select * from vec_table2; 239 a b c 240 1 [1, 2, 3] [4, 5, 6] 241 create table vec_table3(a int unique key, b vecf32(3), c vecf64(3)); 242 insert into vec_table3 values(1, "[1,2,3]", "[4,5,6]"); 243 select * from vec_table3; 244 a b c 245 1 [1, 2, 3] [4, 5, 6] 246 select summation(null); 247 summation(null) 248 null 249 select l1_norm(null); 250 l1_norm(null) 251 null 252 select l2_norm(null); 253 l2_norm(null) 254 null 255 select vector_dims(null); 256 vector_dims(null) 257 null 258 select inner_product(null, "[1,2,3]"); 259 inner_product(null, [1,2,3]) 260 null 261 select cosine_similarity(null, "[1,2,3]"); 262 cosine_similarity(null, [1,2,3]) 263 null 264 select l2_distance(null, "[1,2,3]"); 265 l2_distance(null, [1,2,3]) 266 null 267 select cosine_distance(null, "[1,2,3]"); 268 cosine_distance(null, [1,2,3]) 269 null 270 select normalize_l2(null); 271 normalize_l2(null) 272 null 273 select cast(null as vecf32(3)); 274 cast(null as vecf32(3)) 275 null 276 select cast(null as vecf64(3)); 277 cast(null as vecf64(3)) 278 null 279 create table t8(a int, b vecf32(3), c vecf32(5)); 280 INSERT INTO `t8` VALUES (1,NULL,NULL); 281 INSERT INTO `t8` VALUES(2,'[0.8166459, 0.66616553, 0.4886152]',NULL); 282 INSERT INTO `t8` VALUES(3,'[0.1726299, 3.2908857, 30.433094]','[0.45052445, 2.1984527, 9.579752, 123.48039, 4635.894]'); 283 INSERT INTO `t8` VALUES(4,'[8.560689, 6.790359, 821.9778]','[0.46323407, 23.498016, 563.923, 56.076736, 8732.958]'); 284 select cosine_similarity(b,b), cosine_similarity(c,c) from t8; 285 cosine_similarity(b, b) cosine_similarity(c, c) 286 null null 287 1.0 null 288 1.0 1.0 289 1.0 1.0 290 create table t9(a int, b vecf64(3), c vecf64(5)); 291 INSERT INTO `t9` VALUES (1,NULL,NULL); 292 INSERT INTO `t9` VALUES (2,'[0.8166459, 0.66616553, 0.4886152]',NULL); 293 INSERT INTO `t9` VALUES (3,'[8.5606893, 6.7903588, 821.977768]','[0.46323407, 23.49801546, 563.9229458, 56.07673508, 8732.9583881]'); 294 INSERT INTO `t9` VALUES (4,'[0.9260021, 0.26637346, 0.06567037]','[0.45756745, 65.2996871, 321.623636, 3.60082066, 87.58445764]'); 295 select cosine_similarity(b,b), cosine_similarity(c,c) from t9; 296 cosine_similarity(b, b) cosine_similarity(c, c) 297 null null 298 1.0 null 299 1.0 1.0 300 1.0 1.0 301 create table t10(a int, b vecf32(3), c vecf64(3)); 302 insert into t10 values(1, "[1,2.4,3]", "[4.1,5,6]"); 303 insert into t10 values(2, "[3,4,5]", "[6,7.3,8]"); 304 insert into t10 values(3, "[5,6,7]", "[8,9,10]"); 305 select subvector(b,1) from t10; 306 subvector(b, 1) 307 [1, 2.4, 3] 308 [3, 4, 5] 309 [5, 6, 7] 310 select subvector(b,2) from t10; 311 subvector(b, 2) 312 [2.4, 3] 313 [4, 5] 314 [6, 7] 315 select subvector(b,3) from t10; 316 subvector(b, 3) 317 [3] 318 [5] 319 [7] 320 select subvector(b,4) from t10; 321 subvector(b, 4) 322 [] 323 [] 324 [] 325 select subvector(b,-1) from t10; 326 subvector(b, -1) 327 [3] 328 [5] 329 [7] 330 select subvector(b,-2) from t10; 331 subvector(b, -2) 332 [2.4, 3] 333 [4, 5] 334 [6, 7] 335 select subvector(b,-3) from t10; 336 subvector(b, -3) 337 [1, 2.4, 3] 338 [3, 4, 5] 339 [5, 6, 7] 340 select subvector(b, 1, 1) from t10; 341 subvector(b, 1, 1) 342 [1] 343 [3] 344 [5] 345 select subvector(b, 1, 2) from t10; 346 subvector(b, 1, 2) 347 [1, 2.4] 348 [3, 4] 349 [5, 6] 350 select subvector(b, 1, 3) from t10; 351 subvector(b, 1, 3) 352 [1, 2.4, 3] 353 [3, 4, 5] 354 [5, 6, 7] 355 select subvector(b, 1, 4) from t10; 356 subvector(b, 1, 4) 357 [1, 2.4, 3] 358 [3, 4, 5] 359 [5, 6, 7] 360 select subvector(b, -1, 1) from t10; 361 subvector(b, -1, 1) 362 [3] 363 [5] 364 [7] 365 select subvector(b, -2, 1) from t10; 366 subvector(b, -2, 1) 367 [2.4] 368 [4] 369 [6] 370 select subvector(b, -3, 1) from t10; 371 subvector(b, -3, 1) 372 [1] 373 [3] 374 [5] 375 SELECT SUBVECTOR("[1,2,3]", 2); 376 subvector([1,2,3], 2) 377 [2, 3] 378 SELECT SUBVECTOR("[1,2,3]",2,1); 379 subvector([1,2,3], 2, 1) 380 [2] 381 select b + 2 from t10; 382 b + 2 383 [3, 4.4, 5] 384 [5, 6, 7] 385 [7, 8, 9] 386 select b - 2 from t10; 387 b - 2 388 [-1, 0.4000001, 1] 389 [1, 2, 3] 390 [3, 4, 5] 391 select b * 2 from t10; 392 b * 2 393 [2, 4.8, 6] 394 [6, 8, 10] 395 [10, 12, 14] 396 select b / 2 from t10; 397 b / 2 398 [0.5, 1.2, 1.5] 399 [1.5, 2, 2.5] 400 [2.5, 3, 3.5] 401 select 2 + b from t10; 402 2 + b 403 [3, 4.4, 5] 404 [5, 6, 7] 405 [7, 8, 9] 406 select 2 - b from t10; 407 invalid argument operator -, bad value [BIGINT VECF32] 408 select 2 * b from t10; 409 2 * b 410 [2, 4.8, 6] 411 [6, 8, 10] 412 [10, 12, 14] 413 select 2 / b from t10; 414 invalid argument operator /, bad value [BIGINT VECF32] 415 select b + 2.0 from t10; 416 b + 2.0 417 [3, 4.4, 5] 418 [5, 6, 7] 419 [7, 8, 9] 420 select b - 2.0 from t10; 421 b - 2.0 422 [-1, 0.4000001, 1] 423 [1, 2, 3] 424 [3, 4, 5] 425 select b * 2.0 from t10; 426 b * 2.0 427 [2, 4.8, 6] 428 [6, 8, 10] 429 [10, 12, 14] 430 select b / 2.0 from t10; 431 b / 2.0 432 [0.5, 1.2, 1.5] 433 [1.5, 2, 2.5] 434 [2.5, 3, 3.5] 435 select 2.0 + b from t10; 436 2.0 + b 437 [3, 4.4, 5] 438 [5, 6, 7] 439 [7, 8, 9] 440 select 2.0 - b from t10; 441 invalid argument operator -, bad value [DECIMAL64 VECF32] 442 select 2.0 * b from t10; 443 2.0 * b 444 [2, 4.8, 6] 445 [6, 8, 10] 446 [10, 12, 14] 447 select 2.0 / b from t10; 448 invalid argument operator /, bad value [DECIMAL64 VECF32] 449 select cast("[1,2,3]" as vecf32(3)) + 2; 450 cast([1,2,3] as vecf32(3)) + 2 451 [3, 4, 5] 452 select cast("[1,2,3]" as vecf32(3)) - 2; 453 cast([1,2,3] as vecf32(3)) - 2 454 [-1, 0, 1] 455 select cast("[1,2,3]" as vecf32(3)) * 2; 456 cast([1,2,3] as vecf32(3)) * 2 457 [2, 4, 6] 458 select cast("[1,2,3]" as vecf32(3)) / 2; 459 cast([1,2,3] as vecf32(3)) / 2 460 [0.5, 1, 1.5] 461 select 2 + cast("[1,2,3]" as vecf32(3)); 462 2 + cast([1,2,3] as vecf32(3)) 463 [3, 4, 5] 464 select 2 - cast("[1,2,3]" as vecf32(3)); 465 invalid argument operator -, bad value [BIGINT VECF32] 466 select 2 * cast("[1,2,3]" as vecf32(3)); 467 2 * cast([1,2,3] as vecf32(3)) 468 [2, 4, 6] 469 select 2 / cast("[1,2,3]" as vecf32(3)); 470 invalid argument operator /, bad value [BIGINT VECF32] 471 select cast("[1,2,3]" as vecf32(3)) + 2.0; 472 cast([1,2,3] as vecf32(3)) + 2.0 473 [3, 4, 5] 474 select cast("[1,2,3]" as vecf32(3)) - 2.0; 475 cast([1,2,3] as vecf32(3)) - 2.0 476 [-1, 0, 1] 477 select cast("[1,2,3]" as vecf32(3)) * 2.0; 478 cast([1,2,3] as vecf32(3)) * 2.0 479 [2, 4, 6] 480 select cast("[1,2,3]" as vecf32(3)) / 2.0; 481 cast([1,2,3] as vecf32(3)) / 2.0 482 [0.5, 1, 1.5] 483 select 2.0 + cast("[1,2,3]" as vecf32(3)); 484 2.0 + cast([1,2,3] as vecf32(3)) 485 [3, 4, 5] 486 select 2.0 - cast("[1,2,3]" as vecf32(3)); 487 invalid argument operator -, bad value [DECIMAL64 VECF32] 488 select 2.0 * cast("[1,2,3]" as vecf32(3)); 489 2.0 * cast([1,2,3] as vecf32(3)) 490 [2, 4, 6] 491 select 2.0 / cast("[1,2,3]" as vecf32(3)); 492 invalid argument operator /, bad value [DECIMAL64 VECF32] 493 select cast("[1,2,3]" as vecf32(3)) / 0 ; 494 Data truncation: division by zero 495 select 5 + (-1*cast("[1,2,3]" as vecf32(3))); 496 5 + (-1 * cast([1,2,3] as vecf32(3))) 497 [4, 3, 2] 498 create table t11(a vecf32(2)); 499 insert into t11 values('[1,0]'); 500 insert into t11 values('[1,2]'); 501 select distinct a from t11; 502 a 503 [1, 0] 504 [1, 2] 505 select distinct a,a from t11; 506 a a 507 [1, 0] [1, 0] 508 [1, 2] [1, 2] 509 drop table if exists t1; 510 create table t1(c1 int,c2 vecf32(5),c3 tinyint unsigned,c4 bigint,c5 decimal(4,2),c6 float,c7 double); 511 insert into t1 values(10 ,"[1, 0, 1, 6, 6]",3,10,7.1,0.36,2.10); 512 insert into t1 values(60,"[6, 0, 8, 10,129]",2,5,3.26,4.89,1.26); 513 insert into t1 values(20,"[ 9, 18, 1, 4, 132]",6,1,9.36,6.9,5.6); 514 select c2+c3 from t1; 515 c2 + c3 516 [4, 3, 4, 9, 9] 517 [8, 2, 10, 12, 131] 518 [15, 24, 7, 10, 138] 519 select * from t8 except select * from t9; 520 a b c 521 3 [0.1726299, 3.2908857, 30.433094] [0.45052445, 2.1984527, 9.579752, 123.48039, 4635.894] 522 4 [8.560689, 6.790359, 821.9778] [0.46323407, 23.498016, 563.923, 56.076736, 8732.958] 523 select cast("[76875768584509877574546435800000005,8955885757767774774774774456466]" as vecf32(2)) *623585864455; 524 internal error: vector contains infinity values 525 drop database vecdb;