sql-tests/inputs/show-tables.sql

0001 -- Test data.
0002 CREATE DATABASE showdb;
0003 USE showdb;
0004 CREATE TABLE show_t1(a String, b Int, c String, d String) USING parquet PARTITIONED BY (c, d);
0005 ALTER TABLE show_t1 ADD PARTITION (c='Us', d=1);
0006 CREATE TABLE show_t2(b String, d Int) USING parquet;
0007 CREATE TEMPORARY VIEW show_t3(e int) USING parquet;
0008 CREATE GLOBAL TEMP VIEW show_t4 AS SELECT 1 as col1;
0009
0010 -- SHOW TABLES
0011 SHOW TABLES;
0012 SHOW TABLES IN showdb;
0013
0014 -- SHOW TABLES WITH wildcard match
0015 SHOW TABLES 'show_t*';
0016 SHOW TABLES LIKE 'show_t1*|show_t2*';
0017 SHOW TABLES IN showdb 'show_t*';
0018 SHOW TABLES IN showdb LIKE 'show_t*';
0019
0020 -- SHOW TABLE EXTENDED
0021 SHOW TABLE EXTENDED LIKE 'show_t*';
0022 SHOW TABLE EXTENDED;
0023
0024 -- SHOW TABLE EXTENDED ... PARTITION
0025 SHOW TABLE EXTENDED LIKE 'show_t1' PARTITION(c='Us', d=1);
0026 -- Throw a ParseException if table name is not specified.
0027 SHOW TABLE EXTENDED PARTITION(c='Us', d=1);
0028 -- Don't support regular expression for table name if a partition specification is present.
0029 SHOW TABLE EXTENDED LIKE 'show_t*' PARTITION(c='Us', d=1);
0030 -- Partition specification is not complete.
0031 SHOW TABLE EXTENDED LIKE 'show_t1' PARTITION(c='Us');
0032 -- Partition specification is invalid.
0033 SHOW TABLE EXTENDED LIKE 'show_t1' PARTITION(a='Us', d=1);
0034 -- Partition specification doesn't exist.
0035 SHOW TABLE EXTENDED LIKE 'show_t1' PARTITION(c='Ch', d=1);
0036
0037 -- Clean Up
0038 DROP TABLE show_t1;
0039 DROP TABLE show_t2;
0040 DROP VIEW  show_t3;
0041 DROP VIEW  global_temp.show_t4;
0042 USE default;
0043 DROP DATABASE showdb;