github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/save_query_result/save_query_result.sql (about)

     1  drop table if exists test_table;
     2  create table test_table(
     3  col1 int,
     4  col2 float,
     5  col3 bool,
     6  col4 Date,
     7  col5 varchar(255),
     8  col6 text
     9  );
    10  
    11  -- @setup
    12  set global save_query_result = on;
    13  
    14  -- show sql result cache
    15  show create table test_table;
    16  select * from result_scan(last_query_id()) as u;
    17  show tables;
    18  select * from result_scan(last_query_id()) as u;
    19  show databases like "mysql";
    20  select * from result_scan(last_query_id()) as u;
    21  
    22  -- test select table result cache
    23  load data infile '$resources/load_data/test_1.csv' into table test_table fields terminated by ',';
    24  /* save_result */select * from test_table;
    25  select * from result_scan(last_query_id()) as u;
    26  /* save_result */select col1 from test_table;
    27  select * from result_scan(last_query_id()) as u;
    28  /* save_result */select * from test_table where col1 > 30;
    29  select * from result_scan(last_query_id()) as u;
    30  /* save_result */select * from test_table where col1 < 10;
    31  select * from result_scan(last_query_id()) as u;
    32  /* save_result */select * from test_table where col1 = 10;
    33  select * from result_scan(last_query_id()) as u;
    34  /* save_result */select * from test_table limit 1;
    35  select * from result_scan(last_query_id()) as u;
    36  /* save_result */select * from test_table order by col1 asc;
    37  select * from result_scan(last_query_id()) as u;
    38  
    39  /* save_result */select t1.col1,t2.col1 from test_table t1  left join test_table t2 on t1.col1=t2.col1;
    40  select * from result_scan(last_query_id()) as u;
    41  /* save_result */select t1.col1,t2.col1 from test_table t1  right join test_table t2 on t1.col1=t2.col1;
    42  select * from result_scan(last_query_id()) as u;
    43  /* save_result */select * from test_table union select * from test_table;
    44  select * from result_scan(last_query_id()) as u;
    45  
    46  /* save_result */SELECT col1 FROM test_table where col1 < 30 MINUS SELECT col1  FROM test_table where col1 < 20;
    47  select * from result_scan(last_query_id()) as u;
    48  
    49  -- test view result cache
    50  create view test_view as select * from test_table;
    51  show tables;
    52  select * from result_scan(last_query_id()) as u;
    53  show create view test_view;
    54  select * from result_scan(last_query_id()) as u;
    55  
    56  /* save_result */select * from test_view;
    57  select * from result_scan(last_query_id()) as u;
    58  /* save_result */select col1 from test_view;
    59  select * from result_scan(last_query_id()) as u;
    60  /* save_result */select * from test_view where col1 > 30;
    61  select * from result_scan(last_query_id()) as u;
    62  /* save_result */select * from test_view where col1 < 10;
    63  select * from result_scan(last_query_id()) as u;
    64  /* save_result */select * from test_view where col1 = 10;
    65  select * from result_scan(last_query_id()) as u;
    66  /* save_result */select * from test_view limit 1;
    67  select * from result_scan(last_query_id()) as u;
    68  /* save_result */select * from test_view order by col1 asc;
    69  select * from result_scan(last_query_id()) as u;
    70  
    71  
    72  /* save_result */select t1.col1,t2.col1 from test_view t1  left join test_view t2 on t1.col1=t2.col1;
    73  select * from result_scan(last_query_id()) as u;
    74  /* save_result */select t1.col1,t2.col1 from test_view t1  right join test_view t2 on t1.col1=t2.col1;
    75  select * from result_scan(last_query_id()) as u;
    76  /* save_result */select * from test_view union select * from test_view;
    77  select * from result_scan(last_query_id()) as u;
    78  
    79  /* save_result */SELECT col1 FROM test_view where col1 < 30 MINUS SELECT col1  FROM test_view where col1 < 20;
    80  select * from result_scan(last_query_id()) as u;
    81  
    82  
    83  -- test transactions result cache
    84  begin;
    85  /* save_result */select * from test_table;
    86  select * from result_scan(last_query_id()) as u;
    87  /* save_result */select col1 from test_table;
    88  select * from result_scan(last_query_id()) as u;
    89  /* save_result */select * from test_table where col1 > 30;
    90  select * from result_scan(last_query_id()) as u;
    91  /* save_result */select * from test_table where col1 < 10;
    92  select * from result_scan(last_query_id()) as u;
    93  /* save_result */select * from test_table where col1 = 10;
    94  select * from result_scan(last_query_id()) as u;
    95  /* save_result */select * from test_table limit 1;
    96  select * from result_scan(last_query_id()) as u;
    97  /* save_result */select * from test_table order by col1 asc;
    98  select * from result_scan(last_query_id()) as u;
    99  
   100  /* save_result */select t1.col1,t2.col1 from test_table t1  left join test_table t2 on t1.col1=t2.col1;
   101  select * from result_scan(last_query_id()) as u;
   102  /* save_result */select t1.col1,t2.col1 from test_table t1  right join test_table t2 on t1.col1=t2.col1;
   103  select * from result_scan(last_query_id()) as u;
   104  /* save_result */select * from test_table union select * from test_table;
   105  select * from result_scan(last_query_id()) as u;
   106  
   107  /* save_result */SELECT col1 FROM test_view where col1 < 30 MINUS SELECT col1  FROM test_view where col1 < 20;
   108  select * from result_scan(last_query_id()) as u;
   109  rollback;
   110  
   111  begin;
   112  /* save_result */select * from test_table;
   113  rollback;
   114  select * from result_scan(last_query_id(-2)) as u;
   115  
   116  -- test prepare select sql
   117  set @xxx=10;
   118  prepare s1 from select * from test_table where col1<?;
   119  execute s1 using @xxx;
   120  select * from result_scan(last_query_id(-1)) as u;
   121  deallocate prepare s1;
   122  
   123  
   124  -- test save_query_result config
   125  set global save_query_result = off;
   126  /* save_result */select * from test_table;
   127  select * from result_scan(last_query_id()) as u;
   128  set global save_query_result = on;
   129  
   130  -- @teardown
   131  set global save_query_result = off;
   132  
   133  set global save_query_result = on;
   134  drop table if exists t1;
   135  create table t1(a int);
   136  show columns from t1;
   137  select * from result_scan(last_query_id()) as t;
   138  set global save_query_result = off;
   139