sql-tests/inputs/show_columns.sql

0001 CREATE DATABASE showdb;
0002
0003 USE showdb;
0004
0005 CREATE TABLE showcolumn1 (col1 int, `col 2` int) USING json;
0006 CREATE TABLE showcolumn2 (price int, qty int, year int, month int) USING parquet partitioned by (year, month);
0007 CREATE TEMPORARY VIEW showColumn3 (col3 int, `col 4` int) USING json;
0008 CREATE GLOBAL TEMP VIEW showColumn4 AS SELECT 1 as col1, 'abc' as `col 5`;
0009
0010
0011 -- only table name
0012 SHOW COLUMNS IN showcolumn1;
0013
0014 -- qualified table name
0015 SHOW COLUMNS IN showdb.showcolumn1;
0016
0017 -- table name and database name
0018 SHOW COLUMNS IN showcolumn1 FROM showdb;
0019
0020 -- partitioned table
0021 SHOW COLUMNS IN showcolumn2 IN showdb;
0022
0023 -- Non-existent table. Raise an error in this case
0024 SHOW COLUMNS IN badtable FROM showdb;
0025
0026 -- database in table identifier and database name in different case
0027 SHOW COLUMNS IN showdb.showcolumn1 from SHOWDB;
0028
0029 -- different database name in table identifier and database name.
0030 -- Raise an error in this case.
0031 SHOW COLUMNS IN showdb.showcolumn1 FROM baddb;
0032
0033 -- show column on temporary view
0034 SHOW COLUMNS IN showcolumn3;
0035
0036 -- error temp view can't be qualified with a database
0037 SHOW COLUMNS IN showdb.showcolumn3;
0038
0039 -- error temp view can't be qualified with a database
0040 SHOW COLUMNS IN showcolumn3 FROM showdb;
0041
0042 -- error global temp view needs to be qualified
0043 SHOW COLUMNS IN showcolumn4;
0044
0045 -- global temp view qualified with database
0046 SHOW COLUMNS IN global_temp.showcolumn4;
0047
0048 -- global temp view qualified with database
0049 SHOW COLUMNS IN showcolumn4 FROM global_temp;
0050
0051 DROP TABLE showcolumn1;
0052 DROP TABLE showColumn2;
0053 DROP VIEW  showcolumn3;
0054 DROP VIEW  global_temp.showcolumn4;
0055
0056 use default;
0057
0058 DROP DATABASE showdb;