github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/dml/show/show.test (about)

     1  
     2  -- @suite
     3  
     4  -- @setup
     5  drop database if exists test01;
     6  create database test01;
     7  use test01;
     8  create table tt(col2 float comment '"%$^&*()_+@!\'');
     9  show create table tt;
    10  drop table tt;
    11  create table t1 (id int not null, str char(10),name varchar(10));
    12  create table t2 (a int);
    13  
    14  -- @case
    15  -- @desc:test for show tables
    16  -- @label:bvt
    17  show tables;
    18  
    19  -- @case
    20  -- @desc:test for show tables from
    21  -- @label:bvt
    22  show tables from test01;
    23  
    24  -- @case
    25  -- @desc:test for show columns from
    26  -- @label:bvt
    27  show columns from t1;
    28  show full columns from t1;
    29  show full columns from t1 like '%';
    30  show full columns from t1 where `Field`='id';
    31  drop database if exists test01;
    32  
    33  -- @case
    34  -- @desc:test for show charset
    35  -- @label:bvt
    36  show charset;
    37  drop database if exists test02;
    38  create database test02;
    39  use test02;
    40  create table t1(a tinyint, b smallint, c int, d bigint);
    41  create database test01;
    42  use test01;
    43  show columns from test02.t1;
    44  create database if not exists db11111111111;
    45  show table status from db11111111111;
    46  drop database db11111111111;
    47  show variables where value = 'MatrixOne';
    48  
    49  -- @case
    50  -- @desc:test for show grants
    51  -- @label:bvt
    52  show grants;
    53  show grants for 'root'@'localhost';
    54  show grants for 'test'@'localhost';
    55  create role role1;
    56  grant all on table *.* to role1;
    57  grant create table, drop table on database *.* to role1;
    58  create user user1 identified by 'pass1';
    59  grant role1 to user1;
    60  show grants for 'user1'@'localhost';
    61  drop user user1;
    62  drop role role1;
    63  
    64  -- @case
    65  -- @desc:test for show full tables
    66  -- @label:bvt
    67  create database if not exists test03;
    68  use test03;
    69  drop view if exists v1;
    70  drop table if exists t1;
    71  create table t1(a int, b int);
    72  insert into t1 values(1,1);
    73  create view v1 as select * from t1;
    74  show full tables;
    75  show full tables from test03;
    76  
    77  -- @case
    78  -- @desc:test for sql injection in show statements
    79  -- @label:bvt
    80  show full tables from `1;drop table t1;`;
    81  show tables like `1;drop table t1;`;
    82  show full tables from "1;drop table t1;";
    83  show create database `1;drop table t1;`;
    84  show create table `1;drop table t1;`;
    85  show columns from `1;drop table t1;`;
    86  show table status from `1;drop table t1;`;
    87  show index from `1;drop table t1;`;
    88  show variables like `1;drop table t1;`;
    89  select * from t1;
    90  
    91  -- @case
    92  -- @desc:test for show collation
    93  -- @label:bvt
    94  show collation;
    95  show collation like '%';
    96  show collation where 'Charset'='utf8mb4';
    97  
    98  -- @case
    99  -- @desc:test for set and show variables
   100  -- @label:bvt
   101  show variables like 'sql_safe_updates';
   102  set sql_safe_updates=1;
   103  show variables like 'sql_safe_updates';
   104  set autocommit = on;
   105  show variables like 'autocommit';
   106  set autocommit = off;
   107  show variables like 'autocommit';
   108  set autocommit = 1;
   109  show variables like 'autocommit';
   110  
   111  -- @case
   112  -- @desc:test for show create table while on update and default is not null
   113  -- @label:bvt
   114  drop table if exists t1;
   115  create table t1(c1 int primary key auto_increment,c2 tinyint not null default 4,c3 smallint,c4 bigint,c5 tinyint unsigned,c6 smallint unsigned,c7 int unsigned,c8 bigint unsigned,c9 float,c10 double,c11 date,c12 datetime,c13 timestamp on update current_timestamp,c14 char,c15 varchar default 'q',c16 json,c17 decimal,c18 text,c19 blob,c20 uuid);
   116  show create table t1;
   117  
   118  SHOW TRIGGERS;
   119  SHOW TRIGGERS like '*%';
   120  
   121  use mo_catalog;
   122  show tables;
   123  
   124  show table_number from mo_catalog;
   125  
   126  show column_number from mo_database;
   127  show column_number from hdjshdj;
   128  
   129  drop database if exists test01;
   130  create database test01;
   131  use test01;
   132  create table t(a int, b int);
   133  insert into t values(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
   134  show table_values from t;
   135  drop table t;
   136  
   137  show node list;
   138  show locks;
   139  
   140  create role role1;
   141  grant all on table *.* to role1;
   142  grant create table, drop table on database *.* to role1;
   143  create user user1 identified by 'pass1';
   144  grant role1 to user1;
   145  show grants for 'user1'@'localhost';
   146  show grants for ROLE role1;
   147  drop user user1;
   148  drop role role1;
   149  
   150  drop database if exists test01;
   151  create database test01;
   152  use test01;
   153  create table t1(a int unique key, b int, c int);
   154  show table_number from test01;
   155  drop table t1;
   156  
   157  drop database if exists test01;
   158  create database test01;
   159  use test01;
   160  create table t(a int, b int, c json);
   161  insert into t values(1, 1, '{"a":1,"b":2,"c":3}'), (2, 2, '{"a":1,"b":2,"c":3}'), (3, 3, '{"a":1,"b":2,"c":3}'), (4, 4, '{"a":1,"b":2,"c":3}'), (5, 5, '{"a":1,"b":2,"c":3}'), (6, 6, '{"a":1,"b":2,"c":3}');
   162  show table_values from t;
   163  drop table t;
   164  
   165  create database `.quote`;
   166  show tables from `.quote`;
   167  drop database if exists `.quote`;
   168  
   169  drop database if exists showDB;
   170  create database showDB;
   171  use showDB;
   172  create table a (a int,b int);
   173  create view va as select a from a;
   174  
   175  begin;
   176  show create table a;
   177  show create table va;
   178  show create view va;
   179  show collation;
   180  show collation like '%';
   181  rollback;
   182  drop database if exists showDb;
   183  
   184  -- SHOW INDEX FROM tbl_name [FROM db_name]
   185  -- @suite
   186  -- @setup
   187  DROP DATABASE IF EXISTS test;
   188  CREATE DATABASE test;
   189  USE test;
   190  DROP TABLE IF EXISTS show_01;
   191  CREATE TABLE show_01(sname varchar(30),id int);
   192  show INDEX FROM show_01;
   193  DROP TABLE show_01;
   194  
   195  
   196  -- @suite
   197  -- @setup
   198  DROP TABLE IF EXISTS show_02;
   199  CREATE TABLE show_02 (
   200    h1 varchar(16) NOT NULL default '',
   201    u1 varchar(16) NOT NULL default '',
   202    PRIMARY KEY  (h1,u1)
   203  );
   204  show INDEX FROM show_02;
   205  DROP TABLE show_02;
   206  
   207  
   208  -- @suite
   209  -- @setup
   210  DROP TABLE IF EXISTS show_03;
   211  CREATE TABLE show_03(a int, b int);
   212  CREATE unique index x ON show_03(a) comment 'x';
   213  show CREATE table show_03;
   214  show INDEX FROM show_03;
   215  DROP TABLE show_03;
   216  
   217  
   218  -- @suite
   219  -- @setup
   220  CREATE TABLE show_04(a int, b int, unique key(a));
   221  show CREATE TABLE show_04;
   222  CREATE INDEX b ON show_04(b);
   223  show INDEX FROM show_04;
   224  DROP TABLE show_04;
   225  
   226  -- SHOW FUNCTION STATUS [like_or_where]
   227  -- @bvt:issue#7367
   228  DROP DATABASE IF EXISTS show_03;
   229  USE show_03;
   230  delimiter $$
   231  CREATE FUNCTION myfun(ia int,ib int) returns int
   232  BEGIN
   233  RETURN ia + ib;
   234  end
   235  $$
   236  delimiter;
   237  
   238  show FUNCTION status;
   239  show FUNCTION status LIKE 'pattern';
   240  show FUNCTION status where db = 'show_03';
   241  -- @bvt:issue
   242  
   243  DROP DATABASE test;
   244  create database test;
   245  use test;
   246  drop table if exists t1;
   247  create table t1(a int, b int, c int, primary key(a));
   248  show columns from t1;
   249  drop table t1;
   250  
   251  drop table if exists t2;
   252  create table t2(a int, b int, c int, primary key(a, b));
   253  show columns from t2;
   254  drop table t2;
   255  
   256  drop table if exists t3;
   257  create table t3(a int, b int, c int, primary key(a, b, c));
   258  show columns from t3;
   259  drop table t3;
   260  
   261  drop database test;