github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/row_constructor.result (about) 1 create database test; 2 use test; 3 select (1,2,3)=(0,null,3); 4 (1, 2, 3) = (0, null, 3) 5 false 6 select (1,2,3)=(1,null,3); 7 (1, 2, 3) = (1, null, 3) 8 null 9 select (1,2,3)=(1,null,0); 10 (1, 2, 3) = (1, null, 0) 11 false 12 select (1,2,3) = (1,null,3); 13 (1, 2, 3) = (1, null, 3) 14 null 15 select (1,2,3) = (1+1, null, 3); 16 (1, 2, 3) = (1 + 1, null, 3) 17 false 18 select (1,2,3) = (1,null,3+1); 19 (1, 2, 3) = (1, null, 3 + 1) 20 false 21 select (1,2,3) <> (1,null,3); 22 (1, 2, 3) != (1, null, 3) 23 null 24 select (1,2,3) <> (1+1,null,3); 25 (1, 2, 3) != (1 + 1, null, 3) 26 true 27 select (1,2,3) <> (1,null,3+1); 28 (1, 2, 3) != (1, null, 3 + 1) 29 true 30 select (1,2) > (2,3); 31 (1, 2) > (2, 3) 32 false 33 select (1.000, 2.323200) = (1,2.3232); 34 (1.000, 2.323200) = (1, 2.3232) 35 true 36 select (-10, 200) < (100,200); 37 (-10, 200) < (100, 200) 38 true 39 select (null,null) = (null,null); 40 (null, null) = (null, null) 41 null 42 select (1,2,2,3) > (2,3,4,5); 43 (1, 2, 2, 3) > (2, 3, 4, 5) 44 false 45 select (78415614.7894,789854.0) = (78415614.7894,789854.0); 46 (78415614.7894, 789854.0) = (78415614.7894, 789854.0) 47 true 48 select (1,null) < (2,null); 49 (1, null) < (2, null) 50 true 51 select (2,3) >= (1,3); 52 (2, 3) >= (1, 3) 53 true 54 select (-2,1,3) >= (-1,2,3); 55 (-2, 1, 3) >= (-1, 2, 3) 56 false 57 select (-387293.324321,32190391.34134,000) <= (-387293.324321, -123, -1); 58 (-387293.324321, 32190391.34134, 0) <= (-387293.324321, -123, -1) 59 false 60 select (1,2,3) > (-1,-3+2,2*3); 61 (1, 2, 3) > (-1, -3 + 2, 2 * 3) 62 true 63 select (128093,341231431,0) < (999*22,328932/0.3,null); 64 (128093, 341231431, 0) < (999 * 22, 328932 / 0.3, null) 65 false 66 select (100 % 3,100) >= (30,100); 67 (100 % 3, 100) >= (30, 100) 68 false 69 select (2739,32,-328392,329) <= (23920000 mod 9999,32,-328392,329); 70 (2739, 32, -328392, 329) <= (23920000 % 9999, 32, -328392, 329) 71 false 72 select ('abc','def') = ('abc','def'); 73 (abc, def) = (abc, def) 74 true 75 select ('a', null) < ('b', null); 76 (a, null) < (b, null) 77 true 78 select ('1234dhyecufjwqv','38293r243f') >= ('1211111','38092i4((('); 79 (1234dhyecufjwqv, 38293r243f) >= (1211111, 38092i4((() 80 true 81 select ('&') > ('1'); 82 (&) > (1) 83 false 84 drop table if exists row01; 85 create table row01(a int, b int, c int); 86 insert into row01 values (1, 2, 3), 87 (null,2,3), (1,null,3), (1,2,null), 88 (null,2,3+1), (1,null,3+1), (1,2+1,null), 89 (null,2,3-1), (1,null, 3-1), (1,2*1,null); 90 select (1,2,3) = (1, null, 3); 91 (1, 2, 3) = (1, null, 3) 92 null 93 select (1,2,3) = (1+1, null, 3); 94 (1, 2, 3) = (1 + 1, null, 3) 95 false 96 select (1,2,3) = (1, null, 3+1); 97 (1, 2, 3) = (1, null, 3 + 1) 98 false 99 select * from row01 where (a,b,c) = (1,2,3); 100 a b c 101 1 2 3 102 select (1,2,3) <> (1,null,3); 103 (1, 2, 3) != (1, null, 3) 104 null 105 select (1,2,3) <> (1+1,null,3); 106 (1, 2, 3) != (1 + 1, null, 3) 107 true 108 select (1,2,3) <> (1,null,3+1); 109 (1, 2, 3) != (1, null, 3 + 1) 110 true 111 select * from row01 where (a,b,c) <> (1,2,3); 112 a b c 113 null 2 4 114 1 null 4 115 1 3 null 116 null 2 2 117 1 null 2 118 select (1,2,3) < (null,2,3); 119 (1, 2, 3) < (null, 2, 3) 120 null 121 select (1,2,3) < (1,null,3); 122 (1, 2, 3) < (1, null, 3) 123 null 124 select (1,2,3) < (1*1,null,3); 125 (1, 2, 3) < (1 * 1, null, 3) 126 null 127 select (1,2,3) < (1+1,null,3); 128 (1, 2, 3) < (1 + 1, null, 3) 129 true 130 select * from row01 where (a,b,c) < (1,2,3); 131 a b c 132 select (1,2,3) <= (null,2,3); 133 (1, 2, 3) <= (null, 2, 3) 134 null 135 select (1,2,3) <= (1,null,3); 136 (1, 2, 3) <= (1, null, 3) 137 null 138 select (1,2,3) <= (1-1,null,3); 139 (1, 2, 3) <= (1 - 1, null, 3) 140 false 141 select (1,2,3) <= (1+1,null,3); 142 (1, 2, 3) <= (1 + 1, null, 3) 143 true 144 select * from row01 where (a,b,c) <= (1,2,3); 145 a b c 146 1 2 3 147 select (1,2,3) > (null,2,3); 148 (1, 2, 3) > (null, 2, 3) 149 null 150 select (1,2,3) > (1,null,3); 151 (1, 2, 3) > (1, null, 3) 152 null 153 select (1,2,3) > (1-1,null,3); 154 (1, 2, 3) > (1 - 1, null, 3) 155 true 156 select (1,2,3) > (1+1,null,3); 157 (1, 2, 3) > (1 + 1, null, 3) 158 false 159 select * from row01 where (a,b,c) > (1,2,3); 160 a b c 161 1 3 null 162 select (1,2,3) >= (null,2,3); 163 (1, 2, 3) >= (null, 2, 3) 164 null 165 select (1,2,3) >= (1,null,3); 166 (1, 2, 3) >= (1, null, 3) 167 null 168 select (1,2,3) >= (1-1,null,3); 169 (1, 2, 3) >= (1 - 1, null, 3) 170 true 171 select (1,2,3) >= (100/11,null,3); 172 (1, 2, 3) >= (100 / 11, null, 3) 173 false 174 select * from row01 where (a,b,c) >= (1,2,3); 175 a b c 176 1 2 3 177 1 3 null 178 drop table row01; 179 select (2930,291201) > (2189,1212,3232); 180 invalid input: two tuples have different length(2,3) 181 select (1,2,4,5) < (1,2,4); 182 invalid input: two tuples have different length(4,3) 183 select (null,null,2893.323) = () 184 drop table if exists row01; 185 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 32 near ") 186 drop table if exists row01;"; 187 create table row01(col1 char, col2 varchar(20)); 188 insert into row01 values('a',' uc32qr3'); 189 insert into row01 values('b',' 09920011'); 190 insert into row01 values('c','3ru82q ijf'); 191 insert into row01 values(null,'e&*(& &&'); 192 select * from row01; 193 col1 col2 194 a uc32qr3 195 b 09920011 196 c 3ru82q ijf 197 null e&*(& && 198 select (col1,col2) >= (col1,col2) from row01; 199 (col1, col2) >= (col1, col2) 200 true 201 true 202 true 203 null 204 select (col1,col2) <= (col1,col2) from row01; 205 (col1, col2) <= (col1, col2) 206 true 207 true 208 true 209 null 210 select (col1,col2) < (col1,0) from row01; 211 invalid argument cast to int, bad value uc32qr3 212 select (col1,col2,0) > (col1,col2,col1) from row01; 213 invalid argument cast to int, bad value a 214 select (col1,col2) = (col1,col2) from row01; 215 (col1, col2) = (col1, col2) 216 true 217 true 218 true 219 null 220 select * from row01 where (col1,col2) between ('a','uc32qr3') and ('c','3ru82qijf'); 221 col1 col2 222 b 09920011 223 c 3ru82q ijf 224 select col1,(col1,col2) <> (col1,0) from row01; 225 invalid argument cast to int, bad value uc32qr3 226 select col1,(col1,col2) != (col1,0) from row01; 227 invalid argument cast to int, bad value uc32qr3 228 select (concat_ws(col1,col2),col2) >= (col1,col2) from row01; 229 (concat_ws(col1, col2), col2) >= (col1, col2) 230 false 231 false 232 false 233 null 234 select (col1,ltrim(col2)) <= (col1,col2) from row01; 235 (col1, ltrim(col2)) <= (col1, col2) 236 false 237 false 238 true 239 null 240 select (col1,rtrim(col2)) < (col1,'382039i2jfie') from row01; 241 (col1, rtrim(col2)) < (col1, 382039i2jfie) 242 true 243 true 244 false 245 null 246 select (col1,trim(col2)) < (col1,'382039i2jfie') from row01; 247 (col1, trim(col2)) < (col1, 382039i2jfie) 248 false 249 true 250 false 251 null 252 select (col1,substring(col2,1,3),col1) > (col1,col2,col1) from row01; 253 (col1, substring(col2, 1, 3), col1) > (col1, col2, col1) 254 false 255 false 256 false 257 null 258 select (col1,lpad(col2,30,'abc')) = (col1,col2) from row01; 259 (col1, lpad(col2, 30, abc)) = (col1, col2) 260 false 261 false 262 false 263 false 264 select * from row01 where (col1,col2) between ('a','uc32qr3') and ('c','3ru82qijf'); 265 col1 col2 266 b 09920011 267 c 3ru82q ijf 268 select (col1),(reverse(col1),col2) <> (col1,0) from row01; 269 invalid argument cast to int, bad value uc32qr3 270 select col1,(reverse(col1),col2) != (col1,0) from row01; 271 invalid argument cast to int, bad value uc32qr3 272 drop table row01; 273 drop table if exists row02; 274 create table row02(col1 int,col2 tinyint unsigned,col3 smallint,col4 decimal(20,10),col5 float); 275 insert into row02 values(1,32,38,3829342.3224141,-0.9883283); 276 insert into row02 values(2,null,-190,-99932.1,9320.32); 277 insert into row02 values(3,3,832,-39203.83280932,null); 278 insert into row02 values(null,0,0,1.2,3801432.3213); 279 select (col1,col2,col3) > (0,0,0) from row02; 280 (col1, col2, col3) > (0, 0, 0) 281 true 282 true 283 true 284 null 285 select (col1,col2,col3,col4,col5) < (100,100,100,378217493,3218941031) from row02; 286 (col1, col2, col3, col4, col5) < (100, 100, 100, 378217493, 3218941031) 287 true 288 true 289 true 290 null 291 select (col1,col2) >= (col2,col1) from row02; 292 (col1, col2) >= (col2, col1) 293 false 294 null 295 true 296 null 297 select (col3,col4) <= (372913412.2143,col4) from row02; 298 (col3, col4) <= (372913412.2143, col4) 299 true 300 true 301 true 302 true 303 select * from row02 where (col1,col2) between (0,10000) and (10,20000); 304 col1 col2 col3 col4 col5 305 1 32 38 3829342.3224141000 -0.9883283 306 2 null -190 -99932.1000000000 9320.32 307 3 3 832 -39203.8328093200 null 308 select (col1,col2) <> (col1,col2) from row02; 309 (col1, col2) != (col1, col2) 310 false 311 null 312 false 313 null 314 select (col1,col2) != (col4,col5) from row02; 315 (col1, col2) != (col4, col5) 316 true 317 true 318 true 319 true 320 select (col1 * col2,col2,col3) > (0,0,0) from row02; 321 (col1 * col2, col2, col3) > (0, 0, 0) 322 true 323 null 324 true 325 null 326 select (col1,col2 / 2,col3,col4,col5) < (100,100,100,378217493,3218941031) from row02; 327 (col1, col2 / 2, col3, col4, col5) < (100, 100, 100, 378217493, 3218941031) 328 true 329 true 330 true 331 null 332 select (col1,col2 + col3) >= (col2,col1) from row02; 333 (col1, col2 + col3) >= (col2, col1) 334 false 335 null 336 true 337 null 338 select (col3,col4 - 329302.32) <= (372913412.2143,col4) from row02; 339 (col3, col4 - 329302.32) <= (372913412.2143, col4) 340 true 341 true 342 true 343 true 344 select * from row02 where (col1 % 100,col2) between (0,10000) and (10,20000); 345 col1 col2 col3 col4 col5 346 1 32 38 3829342.3224141000 -0.9883283 347 2 null -190 -99932.1000000000 9320.32 348 3 3 832 -39203.8328093200 null 349 select (col1,col2) <> (col1 mod 10,col2) from row02; 350 (col1, col2) != (col1 % 10, col2) 351 false 352 null 353 false 354 null 355 select (col1,col2 + 10000) != (col4,col5) from row02; 356 (col1, col2 + 10000) != (col4, col5) 357 true 358 true 359 true 360 true 361 drop table row02; 362 drop table if exists row03; 363 create table row03(col1 int unsigned,col2 bigint,col3 decimal(20,10),col4 double); 364 insert into row03 values(1,39438432094940434,838209302.34094043234,3239283092.23830922); 365 insert into row03 values(2,37394,-93298439024,4830403434.4329043); 366 insert into row03 values(3,null,null,null); 367 select (col1,col2,col3) > (-1,32893,32932) from row03; 368 (col1, col2, col3) > (-1, 32893, 32932) 369 true 370 true 371 true 372 select (col1,col2,col3) < (col1,col2,12345678989) from row03; 373 (col1, col2, col3) < (col1, col2, 12345678989) 374 true 375 true 376 null 377 select (col1,col2,100) >= (col1,col2,50) from row03; 378 (col1, col2, 100) >= (col1, col2, 50) 379 true 380 true 381 null 382 select (col1,col2,col3) <= (col1,col2,col3) from row03; 383 (col1, col2, col3) <= (col1, col2, col3) 384 true 385 true 386 null 387 select (col1,col2,col3) <> (1,39438432094940434,838209302.34094043234) from row03; 388 (col1, col2, col3) != (1, 39438432094940434, 838209302.34094043234) 389 true 390 true 391 true 392 select (col1,col2,col3) != (1,39438432094940434,838209302.34094043234) from row03; 393 (col1, col2, col3) != (1, 39438432094940434, 838209302.34094043234) 394 true 395 true 396 true 397 select (col1 * col2,col2,col3) > (-1,32893,32932) from row03; 398 (col1 * col2, col2, col3) > (-1, 32893, 32932) 399 true 400 true 401 null 402 select (col1,col2 - 10000,col3) < (col1,col2,12345678989) from row03; 403 (col1, col2 - 10000, col3) < (col1, col2, 12345678989) 404 true 405 true 406 null 407 select (col1,col2 + 182901.213123,100) >= (col1,col2,50) from row03; 408 (col1, col2 + 182901.213123, 100) >= (col1, col2, 50) 409 true 410 true 411 null 412 select (col1,col2,col3 / 45) <= (col1,col2,col3) from row03; 413 (col1, col2, col3 / 45) <= (col1, col2, col3) 414 true 415 false 416 null 417 select (col1,col2,col3) <> (1,39438432094940434 + col1,838209302.34094043234) from row03; 418 (col1, col2, col3) != (1, 39438432094940434 + col1, 838209302.34094043234) 419 true 420 true 421 true 422 select (col1,col2,col3) != (1,39438432094940434 % 20,838209302.34094043234) from row03; 423 (col1, col2, col3) != (1, 39438432094940434 % 20, 838209302.34094043234) 424 true 425 true 426 true 427 drop table row03; 428 drop table if exists row04; 429 create table row04(col1 date,col2 datetime,col3 timestamp); 430 insert into row04 values('2017-06-15','2000-01-01 00:00:00','2022-01-02 00:00:01.512345'); 431 insert into row04 values('2023-04-06','2023-04-06 12:12:00','1999-01-02 00:00:09'); 432 insert into row04 values(null,null,null); 433 select (col1,col2,col3) >= ('2017-06-15','2000-01-01 00:00:00','2022-01-02 00:00:01.512345') from row04; 434 (col1, col2, col3) >= (2017-06-15, 2000-01-01 00:00:00, 2022-01-02 00:00:01.512345) 435 true 436 true 437 null 438 select (col1,col2,col3) <= (col1,col2,'2023-01-02 00:00:01.512345')from row04; 439 (col1, col2, col3) <= (col1, col2, 2023-01-02 00:00:01.512345) 440 true 441 true 442 null 443 select (col1,col2) > ('1999-01-01','1999-01-04 00:00:09') from row04; 444 (col1, col2) > (1999-01-01, 1999-01-04 00:00:09) 445 true 446 true 447 null 448 select (col1,col3) < ('1970-01-01','2055-01-01 10:10:10') from row04; 449 (col1, col3) < (1970-01-01, 2055-01-01 10:10:10) 450 false 451 false 452 null 453 select * from row04 where (col1,col2) between ('2017-06-15','1000-01-01 00:00:00') and('2025-06-15','2300-01-01 00:00:00'); 454 col1 col2 col3 455 2017-06-15 2000-01-01 00:00:00 2022-01-02 00:00:02 456 2023-04-06 2023-04-06 12:12:00 1999-01-02 00:00:09 457 select (col1,col3) <> ('2017-06-15','2022-01-02 00:00:01.512345') from row04; 458 (col1, col3) != (2017-06-15, 2022-01-02 00:00:01.512345) 459 false 460 true 461 null 462 select (col1,col3) != ('2017-06-15','2022-01-02 00:00:01.5123-5') from row04; 463 (col1, col3) != (2017-06-15, 2022-01-02 00:00:01.5123-5) 464 false 465 true 466 null 467 select (col1,col2,date(col3)) >= ('2017-06-15','2000-01-01 00:00:00','2022-01-02') from row04; 468 (col1, col2, date(col3)) >= (2017-06-15, 2000-01-01 00:00:00, 2022-01-02) 469 true 470 true 471 null 472 select (col1,col2,to_date(col3,'%Y-%m-%d %H:%i:%s')) <= (col1,col2,'2023-01-02 00:00:01.512345')from row04; 473 (col1, col2, to_date(col3, %Y-%m-%d %H:%i:%s)) <= (col1, col2, 2023-01-02 00:00:01.512345) 474 true 475 true 476 null 477 select (date_add(col1,interval 45 day),col2) > ('1999-01-01','1999-01-04 00:00:09') from row04; 478 (date_add(col1, interval(45, day)), col2) > (1999-01-01, 1999-01-04 00:00:09) 479 true 480 true 481 null 482 select (date_sub(col1,interval 45 day),col3) < ('1970-01-01','2055-01-01 10:10:10') from row04; 483 (date_sub(col1, interval(45, day)), col3) < (1970-01-01, 2055-01-01 10:10:10) 484 false 485 false 486 null 487 select (year(col1),year(col2)) > (1999,2003) from row04; 488 (year(col1), year(col2)) > (1999, 2003) 489 true 490 true 491 null 492 select (month(col1),month(col2)) <= (12,12) from row04; 493 (month(col1), month(col2)) <= (12, 12) 494 true 495 true 496 null 497 select (day(col1),day(col2)) <> (30,30) from row04; 498 (day(col1), day(col2)) != (30, 30) 499 true 500 true 501 null 502 select (weekday(col1),weekday(col2)) != (12,34) from row04; 503 (weekday(col1), weekday(col2)) != (12, 34) 504 true 505 true 506 null 507 drop table row04; 508 drop table if exists row05; 509 create table row05(col1 blob,col2 json,col3 binary(10) not null); 510 insert into row05 values('abcdef','{"t1":"a"}',456); 511 insert into row05 values('_bcdef','{"t1":"c"}',100000); 512 insert into row05 values('__cdef',null,0); 513 select (col1,col2) = ('abcdef','{"t1":"a"}') from row05; 514 (col1, col2) = (abcdef, {"t1":"a"}) 515 true 516 false 517 false 518 select (col1,col2) != ('abcdef','{"ehyiuwqnve":"ashyiujewv"}') from row05; 519 (col1, col2) != (abcdef, {"ehyiuwqnve":"ashyiujewv"}) 520 true 521 true 522 true 523 drop table row05; 524 drop database test;