github.com/matrixorigin/matrixone@v1.2.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  -- @bvt:issue#10164
    46  show table status from db11111111111;
    47  -- @bvt:issue
    48  drop database db11111111111;
    49  show variables where value = 'MatrixOne';
    50  
    51  -- @case
    52  -- @desc:test for show grants
    53  -- @label:bvt
    54  select privilege_name, obj_type, privilege_level from mo_catalog.mo_role_privs where privilege_name = 'values';
    55  show grants;
    56  show grants for 'root'@'localhost';
    57  show grants for 'test'@'localhost';
    58  create role role1;
    59  grant all on table *.* to role1;
    60  grant create table, drop table on database *.* to role1;
    61  create user user1 identified by 'pass1';
    62  grant role1 to user1;
    63  show grants for 'user1'@'localhost';
    64  drop user user1;
    65  drop role role1;
    66  
    67  -- @case
    68  -- @desc:test for show full tables
    69  -- @label:bvt
    70  create database if not exists test03;
    71  use test03;
    72  drop view if exists v1;
    73  drop table if exists t1;
    74  create table t1(a int, b int);
    75  insert into t1 values(1,1);
    76  create view v1 as select * from t1;
    77  show full tables;
    78  show full tables from test03;
    79  
    80  -- @case
    81  -- @desc:test for sql injection in show statements
    82  -- @label:bvt
    83  show full tables from `1;drop table t1;`;
    84  show tables like `1;drop table t1;`;
    85  show full tables from "1;drop table t1;";
    86  show create database `1;drop table t1;`;
    87  show create table `1;drop table t1;`;
    88  show columns from `1;drop table t1;`;
    89  show table status from `1;drop table t1;`;
    90  show index from `1;drop table t1;`;
    91  show variables like `1;drop table t1;`;
    92  select * from t1;
    93  
    94  -- @case
    95  -- @desc:test for show collation
    96  -- @label:bvt
    97  show collation like 'utf8mb4_general_ci';
    98  show collation like 'utf8mb4_general_ci%';
    99  show collation where Charset='utf8mb4'and Collation ='utf8mb4_general_ci';
   100  
   101  -- @case
   102  -- @desc:test for set and show variables
   103  -- @label:bvt
   104  show variables like 'sql_safe_updates';
   105  set sql_safe_updates=1;
   106  show variables like 'sql_safe_updates';
   107  set autocommit = on;
   108  show variables like 'autocommit';
   109  set autocommit = off;
   110  show variables like 'autocommit';
   111  set autocommit = 1;
   112  show variables like 'autocommit';
   113  
   114  -- @case
   115  -- @desc:test for show create table while on update and default is not null
   116  -- @label:bvt
   117  drop table if exists t1;
   118  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);
   119  show create table t1;
   120  
   121  SHOW TRIGGERS;
   122  SHOW TRIGGERS like '*%';
   123  
   124  use mo_catalog;
   125  show tables;
   126  
   127  show table_number from mo_catalog;
   128  
   129  show column_number from mo_database;
   130  show column_number from hdjshdj;
   131  
   132  drop database if exists test01;
   133  create database test01;
   134  use test01;
   135  create table t(a int, b int);
   136  insert into t values(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
   137  show table_values from t;
   138  drop table t;
   139  
   140  show node list;
   141  show locks;
   142  
   143  create role role1;
   144  grant all on table *.* to role1;
   145  grant create table, drop table on database *.* to role1;
   146  create user user1 identified by 'pass1';
   147  grant role1 to user1;
   148  show grants for 'user1'@'localhost';
   149  show grants for ROLE role1;
   150  drop user user1;
   151  drop role role1;
   152  
   153  drop database if exists test01;
   154  create database test01;
   155  use test01;
   156  create table t1(a int unique key, b int, c int);
   157  show table_number from test01;
   158  drop table t1;
   159  
   160  drop database if exists test01;
   161  create database test01;
   162  use test01;
   163  create table t(a int, b int, c json);
   164  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}');
   165  show table_values from t;
   166  drop table t;
   167  
   168  create database `.quote`;
   169  show tables from `.quote`;
   170  drop database if exists `.quote`;
   171  
   172  drop database if exists showDB;
   173  create database showDB;
   174  use showDB;
   175  create table a (a int,b int);
   176  create view va as select a from a;
   177  
   178  begin;
   179  show create table a;
   180  show create table va;
   181  show create view va;
   182  show collation like 'utf8mb4_general_ci';
   183  show collation like 'utf8mb4_general_ci%';
   184  rollback;
   185  drop database if exists showDb;
   186  
   187  -- SHOW INDEX FROM tbl_name [FROM db_name]
   188  -- @suite
   189  -- @setup
   190  DROP DATABASE IF EXISTS test;
   191  CREATE DATABASE test;
   192  USE test;
   193  DROP TABLE IF EXISTS show_01;
   194  CREATE TABLE show_01(sname varchar(30),id int);
   195  show INDEX FROM show_01;
   196  DROP TABLE show_01;
   197  
   198  -- @suite
   199  -- @setup
   200  DROP TABLE IF EXISTS show_02;
   201  CREATE TABLE show_02 (
   202    h1 varchar(16) NOT NULL default '',
   203    u1 varchar(16) NOT NULL default '',
   204    PRIMARY KEY  (h1,u1)
   205  );
   206  show INDEX FROM show_02;
   207  DROP TABLE show_02;
   208  
   209  
   210  -- @suite
   211  -- @setup
   212  DROP TABLE IF EXISTS show_03;
   213  CREATE TABLE show_03(a int, b int);
   214  CREATE unique index x ON show_03(a) comment 'x';
   215  show CREATE table show_03;
   216  show INDEX FROM show_03;
   217  DROP TABLE show_03;
   218  
   219  
   220  -- @suite
   221  -- @setup
   222  CREATE TABLE show_04(a int, b int, unique key(a));
   223  show CREATE TABLE show_04;
   224  CREATE INDEX b ON show_04(b);
   225  show INDEX FROM show_04;
   226  DROP TABLE show_04;
   227  
   228  
   229  DROP DATABASE test;
   230  create database test;
   231  use test;
   232  drop table if exists t1;
   233  create table t1(a int, b int, c int, primary key(a));
   234  show columns from t1;
   235  drop table t1;
   236  
   237  drop table if exists t2;
   238  create table t2(a int, b int, c int, primary key(a, b));
   239  show columns from t2;
   240  drop table t2;
   241  
   242  drop table if exists t3;
   243  create table t3(a int, b int, c int, primary key(a, b, c));
   244  show columns from t3;
   245  drop table t3;
   246  
   247  drop database test;
   248  
   249  
   250  
   251  DROP DATABASE test;
   252  create database test;
   253  use test;
   254  
   255  drop table if exists t1;
   256  CREATE TABLE t1 (
   257  	col1 INT NOT NULL PRIMARY KEY,
   258  	col2 DATE NOT NULL,
   259  	col3 INT NOT NULL,
   260  	col4 INT NOT NULL,
   261  	INDEX (col2,col3)
   262  );
   263  show index from t1;
   264  drop table t1;
   265  
   266  drop table if exists t2;
   267  CREATE TABLE t2 (
   268  	col1 INT NOT NULL PRIMARY KEY,
   269  	col2 DATE NOT NULL UNIQUE KEY,
   270  	col3 INT NOT NULL,
   271  	col4 INT NOT NULL
   272  );
   273  show index from t2;
   274  drop table t2;
   275  
   276  drop table if exists t3;
   277  CREATE TABLE t3 (
   278  	col1 INT NOT NULL PRIMARY KEY,
   279  	col2 DATE NOT NULL,
   280  	col3 INT NOT NULL,
   281  	col4 INT NOT NULL,
   282  	UNIQUE KEY (col1),
   283  	UNIQUE KEY (col1, col3)
   284  );
   285  show index from t3;
   286  drop table t3;
   287  
   288  drop database test;
   289  
   290  drop database if exists test;
   291  create database test;
   292  use test;
   293  SHOW CREATE TABLE information_schema.columns;
   294  drop database test;