github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/query_result/query_result.result (about) 1 set global save_query_result = on; 2 drop table if exists tt; 3 create table tt (a int); 4 insert into tt values(1), (2); 5 /* save_result */select * from tt; 6 a 7 1 8 2 9 select * from result_scan(last_query_id()) as u; 10 a 11 1 12 2 13 /* save_result */select * from tt; 14 a 15 1 16 2 17 select count(*) from meta_scan(last_query_id()) as u; 18 count(*) 19 1 20 set global save_query_result = off; 21 select * from tt; 22 a 23 1 24 2 25 select * from result_scan(last_query_id()) as u; 26 no configure: save query result 27 set global save_query_result = on; 28 drop table if exists t2; 29 create table t2 (a int, b int, c int); 30 insert into t2 values(1, 2, 3), (1, 2, 3); 31 /* save_result */select c from tt, t2 where tt.a = t2.a; 32 c 33 3 34 3 35 select * from result_scan(last_query_id()) as u; 36 c 37 3 38 3 39 /* save_result */select c from tt, t2 where tt.a = t2.a; 40 c 41 3 42 3 43 /* save_result */select t2.b from result_scan(last_query_id()) as u, t2 where u.c = t2.c; 44 b 45 2 46 2 47 2 48 2 49 select * from result_scan(last_query_id()) as u; 50 b 51 2 52 2 53 2 54 2 55 /* save_result */select c from tt, t2 where tt.a = t2.a; 56 c 57 3 58 3 59 select * from result_scan(last_query_id()) as u, result_scan(last_query_id()) as v limit 1; 60 c c 61 3 3 62 set global save_query_result = off; 63 set global save_query_result = on; 64 /* save_result */select tt.a from tt, t2; 65 a 66 1 67 2 68 1 69 2 70 select tables from meta_scan(last_query_id()) as u; 71 tables 72 tt, t2 73 set global query_result_maxsize = 0; 74 /* save_result */select tt.a from tt, t2; 75 a 76 1 77 2 78 1 79 2 80 select char_length(result_path) from meta_scan(last_query_id()) as u; 81 char_length(result_path) 82 0 83 /* save_result */select tt.a from tt, t2; 84 a 85 1 86 2 87 1 88 2 89 select result_size = 0 from meta_scan(last_query_id()) as u; 90 result_size = 0 91 true 92 set global save_query_result = off; 93 set global save_query_result = on; 94 set global query_result_maxsize = 100; 95 create role rrrqqq; 96 grant rrrqqq to dump; 97 /* save_result */select * from tt; 98 a 99 1 100 2 101 set role rrrqqq; 102 select * from meta_scan(last_query_id(-2)) as u; 103 internal error: do not have privilege to execute the statement 104 set role moadmin; 105 create database db111; 106 create table db111.tt1 (a int); 107 insert into db111.tt1 values(1), (2); 108 create table db111.tt2 (a int); 109 insert into db111.tt2 values(1), (2); 110 grant select on table db111.tt1 to rrrqqq; 111 /* save_result */select * from db111.tt1; 112 a 113 1 114 2 115 /* save_result */select * from db111.tt2; 116 a 117 1 118 2 119 set role rrrqqq; 120 select * from result_scan(last_query_id(-3)) as u; 121 a 122 1 123 2 124 select * from meta_scan(last_query_id(-3)) as u; 125 internal error: do not have privilege to execute the statement 126 set role moadmin; 127 drop role rrrqqq; 128 select * from result_scan('d8fb97e7-e30e-11ed-8d80-d6aeb943c8b4') as u; 129 result file query_result_meta/sys_d8fb97e7-e30e-11ed-8d80-d6aeb943c8b4.blk not found 130 drop database if exists db111; 131 set global save_query_result = off; 132 create account abc ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 133 set global save_query_result = on; 134 create database test; 135 /* save_result */show databases; 136 Database 137 system 138 system_metrics 139 information_schema 140 mysql 141 test 142 mo_catalog 143 select * from result_scan(last_query_id()) as u; 144 Database 145 system 146 system_metrics 147 information_schema 148 mysql 149 test 150 mo_catalog 151 use test; 152 drop table if exists tt; 153 create table tt (a int); 154 insert into tt values(1), (2); 155 /* save_result */select * from tt; 156 a 157 1 158 2 159 select * from result_scan(last_query_id()) as u; 160 a 161 1 162 2 163 /* save_result */select * from tt; 164 a 165 1 166 2 167 select count(*) from meta_scan(last_query_id()) as u; 168 count(*) 169 1 170 /* save_result */show tables; 171 Tables_in_test 172 tt 173 select * from result_scan(last_query_id()) as u; 174 Tables_in_test 175 tt 176 /* save_result */show variables like 'tx_isolation'; 177 Variable_name Value 178 tx_isolation REPEATABLE-READ 179 select * from result_scan(last_query_id()) as u; 180 result file query_result_meta/abc_6056d76c-aa29-11ee-8d46-96f0c1a980fd.blk not found 181 /* save_result */show columns from tt; 182 Field Type Null Key Default Extra Comment 183 a INT(32) YES null 184 select * from result_scan(last_query_id()) as u; 185 Field Type Null Key Default Extra Comment 186 a INT(32) YES null 187 /* save_result */show grants; 188 Grants for admin@localhost 189 GRANT create user ON account `admin`@`localhost` 190 GRANT drop user ON account `admin`@`localhost` 191 GRANT alter user ON account `admin`@`localhost` 192 GRANT create role ON account `admin`@`localhost` 193 GRANT drop role ON account `admin`@`localhost` 194 GRANT create database ON account `admin`@`localhost` 195 GRANT drop database ON account `admin`@`localhost` 196 GRANT show databases ON account `admin`@`localhost` 197 GRANT connect ON account `admin`@`localhost` 198 GRANT manage grants ON account `admin`@`localhost` 199 GRANT account all ON account `admin`@`localhost` 200 GRANT show tables ON database * `admin`@`localhost` 201 GRANT create table ON database * `admin`@`localhost` 202 GRANT drop table ON database * `admin`@`localhost` 203 GRANT alter table ON database * `admin`@`localhost` 204 GRANT create view ON database * `admin`@`localhost` 205 GRANT drop view ON database * `admin`@`localhost` 206 GRANT alter view ON database * `admin`@`localhost` 207 GRANT database all ON database * `admin`@`localhost` 208 GRANT database ownership ON database * `admin`@`localhost` 209 GRANT select ON table *.* `admin`@`localhost` 210 GRANT insert ON table *.* `admin`@`localhost` 211 GRANT update ON table *.* `admin`@`localhost` 212 GRANT truncate ON table *.* `admin`@`localhost` 213 GRANT delete ON table *.* `admin`@`localhost` 214 GRANT reference ON table *.* `admin`@`localhost` 215 GRANT index ON table *.* `admin`@`localhost` 216 GRANT table all ON table *.* `admin`@`localhost` 217 GRANT table ownership ON table *.* `admin`@`localhost` 218 GRANT values ON table *.* `admin`@`localhost` 219 GRANT connect ON account `admin`@`localhost` 220 select * from result_scan(last_query_id()) as u; 221 Grants for admin@localhost 222 GRANT create user ON account `admin`@`localhost` 223 GRANT drop user ON account `admin`@`localhost` 224 GRANT alter user ON account `admin`@`localhost` 225 GRANT create role ON account `admin`@`localhost` 226 GRANT drop role ON account `admin`@`localhost` 227 GRANT create database ON account `admin`@`localhost` 228 GRANT drop database ON account `admin`@`localhost` 229 GRANT show databases ON account `admin`@`localhost` 230 GRANT connect ON account `admin`@`localhost` 231 GRANT manage grants ON account `admin`@`localhost` 232 GRANT account all ON account `admin`@`localhost` 233 GRANT show tables ON database * `admin`@`localhost` 234 GRANT create table ON database * `admin`@`localhost` 235 GRANT drop table ON database * `admin`@`localhost` 236 GRANT alter table ON database * `admin`@`localhost` 237 GRANT create view ON database * `admin`@`localhost` 238 GRANT drop view ON database * `admin`@`localhost` 239 GRANT alter view ON database * `admin`@`localhost` 240 GRANT database all ON database * `admin`@`localhost` 241 GRANT database ownership ON database * `admin`@`localhost` 242 GRANT select ON table *.* `admin`@`localhost` 243 GRANT insert ON table *.* `admin`@`localhost` 244 GRANT update ON table *.* `admin`@`localhost` 245 GRANT truncate ON table *.* `admin`@`localhost` 246 GRANT delete ON table *.* `admin`@`localhost` 247 GRANT reference ON table *.* `admin`@`localhost` 248 GRANT index ON table *.* `admin`@`localhost` 249 GRANT table all ON table *.* `admin`@`localhost` 250 GRANT table ownership ON table *.* `admin`@`localhost` 251 GRANT values ON table *.* `admin`@`localhost` 252 GRANT connect ON account `admin`@`localhost` 253 /* save_result */show create table tt; 254 Table Create Table 255 tt CREATE TABLE `tt` (\n`a` INT DEFAULT NULL\n) 256 select * from result_scan(last_query_id()) as u; 257 Table Create Table 258 tt CREATE TABLE `tt` (\n`a` INT DEFAULT NULL\n) 259 alter table tt add unique index id(a); 260 /* save_result */show index from tt; 261 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 262 tt 0 id 1 a A 0 NULL NULL YES YES NULL 263 select * from result_scan(last_query_id()) as u; 264 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 265 tt 0 id 1 a A 0 NULL NULL YES YES NULL 266 /* save_result */show node list; 267 1 268 select * from result_scan(last_query_id()) as u; 269 invalid input: empty query result 270 create sequence seq_an_03 increment 10 start with 1 no cycle; 271 /* save_result */show sequences; 272 Names Data Type 273 seq_an_03 BIGINT 274 select * from result_scan(last_query_id()) as u; 275 Names Data Type 276 seq_an_03 BIGINT 277 CREATE TABLE t1 (S1 INT); 278 CREATE TABLE t2 (S1 INT); 279 INSERT INTO t1 VALUES (1),(3),(4),(6); 280 INSERT INTO t2 VALUES (2),(4),(5); 281 /* save_result */SELECT * FROM t1 JOIN t2 on t1.S1=t2.S1; 282 s1 s1 283 4 4 284 select * from result_scan(last_query_id()) as u; 285 s1_0 s1_1 286 4 4 287 /* save_result */select t2.S1 from t2 left join t1 on t1.S1 =t2.S1; 288 s1 289 2 290 4 291 5 292 select * from result_scan(last_query_id()) as u; 293 s1 294 2 295 4 296 5 297 /* save_result */select t2.S1 from t2 right join t1 on t1.S1 =t2.S1; 298 s1 299 null 300 null 301 4 302 null 303 select * from result_scan(last_query_id()) as u; 304 s1 305 null 306 null 307 4 308 null 309 /* save_result */(select s1 from t1 union select s1 from t2) order by s1 desc; 310 s1 311 6 312 5 313 4 314 3 315 2 316 1 317 select * from result_scan(last_query_id()) as u; 318 s1 319 6 320 5 321 4 322 3 323 2 324 1 325 /* save_result */(select s1 from t1 union all select s1 from t2) order by s1 desc; 326 s1 327 6 328 5 329 4 330 4 331 3 332 2 333 1 334 select * from result_scan(last_query_id()) as u; 335 s1 336 6 337 5 338 4 339 4 340 3 341 2 342 1 343 /* save_result */select * from t1 where t1.s1 > (select t2.s1 from t2 where s1<3); 344 s1 345 3 346 4 347 6 348 select * from result_scan(last_query_id()) as u; 349 s1 350 3 351 4 352 6 353 /* save_result */select * from t1 where s1 <> any (select s1 from t2); 354 s1 355 1 356 3 357 4 358 6 359 select * from result_scan(last_query_id()) as u; 360 s1 361 1 362 3 363 4 364 6 365 /* save_result */select * from t1 where s1 = some (select s1 from t2); 366 s1 367 4 368 select * from result_scan(last_query_id()) as u; 369 s1 370 4 371 drop table if exists time_window01; 372 create table time_window01 (ts timestamp primary key, col2 int); 373 insert into time_window01 values ('2021-01-12 00:00:00.000', 12); 374 insert into time_window01 values ('2020-01-12 12:00:12.000', 24); 375 insert into time_window01 values ('2021-01-12 01:00:00.000', 34); 376 insert into time_window01 values ('2020-01-12 12:01:12.000', 20); 377 select * from time_window01; 378 ts col2 379 2021-01-12 00:00:00 12 380 2020-01-12 12:00:12 24 381 2021-01-12 01:00:00 34 382 2020-01-12 12:01:12 20 383 /* save_result */select _wstart, _wend, max(col2), min(col2) from time_window01 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 384 _wstart _wend max(col2) min(col2) 385 2019-12-08 00:00:00 2020-03-17 00:00:00 24 20 386 2021-01-11 00:00:00 2021-04-21 00:00:00 34 12 387 select * from result_scan(last_query_id()) as u; 388 _wstart _wend max(col2) min(col2) 389 2019-12-08 00:00:00 2020-03-17 00:00:00 24 20 390 2021-01-11 00:00:00 2021-04-21 00:00:00 34 12 391 create view v1 as SELECT 1 IN (SELECT 1); 392 /* save_result */select * from v1; 393 1 in (select 1) 394 true 395 select * from result_scan(last_query_id()) as u; 396 1 in (select 1) 397 true 398 drop table time_window01; 399 set global save_query_result = off; 400 drop account abc; 401 create account abc ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 402 set global save_query_result = off; 403 create database test; 404 /* save_result */show databases; 405 Database 406 system 407 system_metrics 408 information_schema 409 mysql 410 test 411 mo_catalog 412 select * from result_scan(last_query_id()) as u; 413 no configure: save query result 414 use test; 415 drop table if exists tt; 416 create table tt (a int); 417 insert into tt values(1), (2); 418 /* save_result */select * from tt; 419 a 420 1 421 2 422 select * from result_scan(last_query_id()) as u; 423 no configure: save query result 424 /* save_result */select * from tt; 425 a 426 1 427 2 428 select count(*) from meta_scan(last_query_id()) as u; 429 no configure: save query result 430 /* save_result */show tables; 431 Tables_in_test 432 tt 433 select * from result_scan(last_query_id()) as u; 434 no configure: save query result 435 /* save_result */show variables like 'tx_isolation'; 436 Variable_name Value 437 tx_isolation REPEATABLE-READ 438 select * from result_scan(last_query_id()) as u; 439 no configure: save query result 440 /* save_result */show columns from tt; 441 Field Type Null Key Default Extra Comment 442 a INT(32) YES null 443 select * from result_scan(last_query_id()) as u; 444 no configure: save query result 445 /* save_result */show grants; 446 Grants for admin@localhost 447 GRANT create user ON account `admin`@`localhost` 448 GRANT drop user ON account `admin`@`localhost` 449 GRANT alter user ON account `admin`@`localhost` 450 GRANT create role ON account `admin`@`localhost` 451 GRANT drop role ON account `admin`@`localhost` 452 GRANT create database ON account `admin`@`localhost` 453 GRANT drop database ON account `admin`@`localhost` 454 GRANT show databases ON account `admin`@`localhost` 455 GRANT connect ON account `admin`@`localhost` 456 GRANT manage grants ON account `admin`@`localhost` 457 GRANT account all ON account `admin`@`localhost` 458 GRANT show tables ON database * `admin`@`localhost` 459 GRANT create table ON database * `admin`@`localhost` 460 GRANT drop table ON database * `admin`@`localhost` 461 GRANT alter table ON database * `admin`@`localhost` 462 GRANT create view ON database * `admin`@`localhost` 463 GRANT drop view ON database * `admin`@`localhost` 464 GRANT alter view ON database * `admin`@`localhost` 465 GRANT database all ON database * `admin`@`localhost` 466 GRANT database ownership ON database * `admin`@`localhost` 467 GRANT select ON table *.* `admin`@`localhost` 468 GRANT insert ON table *.* `admin`@`localhost` 469 GRANT update ON table *.* `admin`@`localhost` 470 GRANT truncate ON table *.* `admin`@`localhost` 471 GRANT delete ON table *.* `admin`@`localhost` 472 GRANT reference ON table *.* `admin`@`localhost` 473 GRANT index ON table *.* `admin`@`localhost` 474 GRANT table all ON table *.* `admin`@`localhost` 475 GRANT table ownership ON table *.* `admin`@`localhost` 476 GRANT values ON table *.* `admin`@`localhost` 477 GRANT connect ON account `admin`@`localhost` 478 select * from result_scan(last_query_id()) as u; 479 no configure: save query result 480 /* save_result */show create table tt; 481 Table Create Table 482 tt CREATE TABLE `tt` (\n`a` INT DEFAULT NULL\n) 483 select * from result_scan(last_query_id()) as u; 484 no configure: save query result 485 alter table tt add unique index id(a); 486 /* save_result */show index from tt; 487 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 488 tt 0 id 1 a A 0 NULL NULL YES YES NULL 489 select * from result_scan(last_query_id()) as u; 490 no configure: save query result 491 /* save_result */show node list; 492 1 493 select * from result_scan(last_query_id()) as u; 494 no configure: save query result 495 create sequence seq_an_03 increment 10 start with 1 no cycle; 496 /* save_result */show sequences; 497 Names Data Type 498 seq_an_03 BIGINT 499 select * from result_scan(last_query_id()) as u; 500 no configure: save query result 501 CREATE TABLE t1 (S1 INT); 502 CREATE TABLE t2 (S1 INT); 503 INSERT INTO t1 VALUES (1),(3),(4),(6); 504 INSERT INTO t2 VALUES (2),(4),(5); 505 /* save_result */SELECT * FROM t1 JOIN t2 on t1.S1=t2.S1; 506 s1 s1 507 4 4 508 select * from result_scan(last_query_id()) as u; 509 no configure: save query result 510 /* save_result */select t2.S1 from t2 left join t1 on t1.S1 =t2.S1; 511 s1 512 2 513 4 514 5 515 select * from result_scan(last_query_id()) as u; 516 no configure: save query result 517 /* save_result */select t2.S1 from t2 right join t1 on t1.S1 =t2.S1; 518 s1 519 null 520 null 521 4 522 null 523 select * from result_scan(last_query_id()) as u; 524 no configure: save query result 525 /* save_result */(select s1 from t1 union select s1 from t2) order by s1 desc; 526 s1 527 6 528 5 529 4 530 3 531 2 532 1 533 select * from result_scan(last_query_id()) as u; 534 no configure: save query result 535 /* save_result */(select s1 from t1 union all select s1 from t2) order by s1 desc; 536 s1 537 6 538 5 539 4 540 4 541 3 542 2 543 1 544 select * from result_scan(last_query_id()) as u; 545 no configure: save query result 546 /* save_result */select * from t1 where t1.s1 > (select t2.s1 from t2 where s1<3); 547 s1 548 3 549 4 550 6 551 select * from result_scan(last_query_id()) as u; 552 no configure: save query result 553 /* save_result */select * from t1 where s1 <> any (select s1 from t2); 554 s1 555 1 556 3 557 4 558 6 559 select * from result_scan(last_query_id()) as u; 560 no configure: save query result 561 /* save_result */select * from t1 where s1 = some (select s1 from t2); 562 s1 563 4 564 select * from result_scan(last_query_id()) as u; 565 no configure: save query result 566 drop table if exists time_window01; 567 create table time_window01 (ts timestamp primary key, col2 int); 568 insert into time_window01 values ('2021-01-12 00:00:00.000', 12); 569 insert into time_window01 values ('2020-01-12 12:00:12.000', 24); 570 insert into time_window01 values ('2021-01-12 01:00:00.000', 34); 571 insert into time_window01 values ('2020-01-12 12:01:12.000', 20); 572 select * from time_window01; 573 ts col2 574 2021-01-12 00:00:00 12 575 2020-01-12 12:00:12 24 576 2021-01-12 01:00:00 34 577 2020-01-12 12:01:12 20 578 /* save_result */select _wstart, _wend, max(col2), min(col2) from time_window01 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 579 _wstart _wend max(col2) min(col2) 580 2019-12-08 00:00:00 2020-03-17 00:00:00 24 20 581 2021-01-11 00:00:00 2021-04-21 00:00:00 34 12 582 select * from result_scan(last_query_id()) as u; 583 no configure: save query result 584 create view v1 as SELECT 1 IN (SELECT 1); 585 /* save_result */select * from v1; 586 1 in (select 1) 587 true 588 select * from result_scan(last_query_id()) as u; 589 no configure: save query result 590 drop table time_window01; 591 drop account abc;