[SPARK-17860][SQL] SHOW COLUMN's database conflict check should respect case sensitivity configuration

## What changes were proposed in this pull request?
SHOW COLUMNS command validates the user supplied database
name with database name from qualified table name name to make
sure both of them are consistent. This comparison should respect
case sensitivity.

## How was this patch tested?
Added tests in DDLSuite and existing tests were moved to use new sql based test infrastructure.

Author: Dilip Biswal <dbiswal@us.ibm.com>

Closes #15423 from dilipbiswal/dkb_show_column_fix.

2 files changed, 275 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/inputs/show_columns.sql b/sql/core/src/test/resources/sql-tests/inputs/show_columns.sql
new file mode 100644
index 0000000000..3894082255
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/show_columns.sql
@@ -0,0 +1,58 @@
+CREATE DATABASE showdb;
+
+USE showdb;
+
+CREATE TABLE showcolumn1 (col1 int, `col 2` int);
+CREATE TABLE showcolumn2 (price int, qty int) partitioned by (year int, month int);
+CREATE TEMPORARY VIEW showColumn3 (col3 int, `col 4` int) USING parquet;
+CREATE GLOBAL TEMP VIEW showColumn4 AS SELECT 1 as col1, 'abc' as `col 5`;
+
+
+-- only table name
+SHOW COLUMNS IN showcolumn1;
+
+-- qualified table name
+SHOW COLUMNS IN showdb.showcolumn1;
+
+-- table name and database name
+SHOW COLUMNS IN showcolumn1 FROM showdb;
+
+-- partitioned table
+SHOW COLUMNS IN showcolumn2 IN showdb;
+
+-- Non-existent table. Raise an error in this case
+SHOW COLUMNS IN badtable FROM showdb;
+
+-- database in table identifier and database name in different case
+SHOW COLUMNS IN showdb.showcolumn1 from SHOWDB;
+
+-- different database name in table identifier and database name.
+-- Raise an error in this case.
+SHOW COLUMNS IN showdb.showcolumn1 FROM baddb;
+
+-- show column on temporary view
+SHOW COLUMNS IN showcolumn3;
+
+-- error temp view can't be qualified with a database
+SHOW COLUMNS IN showdb.showcolumn3;
+
+-- error temp view can't be qualified with a database
+SHOW COLUMNS IN showcolumn3 FROM showdb;
+
+-- error global temp view needs to be qualified
+SHOW COLUMNS IN showcolumn4;
+
+-- global temp view qualified with database
+SHOW COLUMNS IN global_temp.showcolumn4;
+
+-- global temp view qualified with database
+SHOW COLUMNS IN showcolumn4 FROM global_temp;
+
+DROP TABLE showcolumn1;
+DROP TABLE showColumn2;
+DROP VIEW  showcolumn3;
+DROP VIEW  global_temp.showcolumn4;
+
+use default;
+
+DROP DATABASE showdb;
diff --git a/sql/core/src/test/resources/sql-tests/results/show_columns.sql.out b/sql/core/src/test/resources/sql-tests/results/show_columns.sql.out
new file mode 100644
index 0000000000..832e6e25bb
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/show_columns.sql.out
@@ -0,0 +1,217 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 25
+
+
+-- !query 0
+CREATE DATABASE showdb
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+USE showdb
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+CREATE TABLE showcolumn1 (col1 int, `col 2` int)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+CREATE TABLE showcolumn2 (price int, qty int) partitioned by (year int, month int)
+-- !query 3 schema
+struct<>
+-- !query 3 output
+
+
+
+-- !query 4
+CREATE TEMPORARY VIEW showColumn3 (col3 int, `col 4` int) USING parquet
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+CREATE GLOBAL TEMP VIEW showColumn4 AS SELECT 1 as col1, 'abc' as `col 5`
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+SHOW COLUMNS IN showcolumn1
+-- !query 6 schema
+struct<col_name:string>
+-- !query 6 output
+col 2
+col1
+
+
+-- !query 7
+SHOW COLUMNS IN showdb.showcolumn1
+-- !query 7 schema
+struct<col_name:string>
+-- !query 7 output
+col 2
+col1
+
+
+-- !query 8
+SHOW COLUMNS IN showcolumn1 FROM showdb
+-- !query 8 schema
+struct<col_name:string>
+-- !query 8 output
+col 2
+col1
+
+
+-- !query 9
+SHOW COLUMNS IN showcolumn2 IN showdb
+-- !query 9 schema
+struct<col_name:string>
+-- !query 9 output
+month
+price
+qty
+year
+
+
+-- !query 10
+SHOW COLUMNS IN badtable FROM showdb
+-- !query 10 schema
+struct<>
+-- !query 10 output
+org.apache.spark.sql.catalyst.analysis.NoSuchTableException
+Table or view 'badtable' not found in database 'showdb';
+
+
+-- !query 11
+SHOW COLUMNS IN showdb.showcolumn1 from SHOWDB
+-- !query 11 schema
+struct<col_name:string>
+-- !query 11 output
+col 2
+col1
+
+
+-- !query 12
+SHOW COLUMNS IN showdb.showcolumn1 FROM baddb
+-- !query 12 schema
+struct<>
+-- !query 12 output
+org.apache.spark.sql.AnalysisException
+SHOW COLUMNS with conflicting databases: 'baddb' != 'showdb';
+
+
+-- !query 13
+SHOW COLUMNS IN showcolumn3
+-- !query 13 schema
+struct<col_name:string>
+-- !query 13 output
+col 4
+col3
+
+
+-- !query 14
+SHOW COLUMNS IN showdb.showcolumn3
+-- !query 14 schema
+struct<>
+-- !query 14 output
+org.apache.spark.sql.catalyst.analysis.NoSuchTableException
+Table or view 'showcolumn3' not found in database 'showdb';
+
+
+-- !query 15
+SHOW COLUMNS IN showcolumn3 FROM showdb
+-- !query 15 schema
+struct<>
+-- !query 15 output
+org.apache.spark.sql.catalyst.analysis.NoSuchTableException
+Table or view 'showcolumn3' not found in database 'showdb';
+
+
+-- !query 16
+SHOW COLUMNS IN showcolumn4
+-- !query 16 schema
+struct<>
+-- !query 16 output
+org.apache.spark.sql.catalyst.analysis.NoSuchTableException
+Table or view 'showcolumn4' not found in database 'showdb';
+
+
+-- !query 17
+SHOW COLUMNS IN global_temp.showcolumn4
+-- !query 17 schema
+struct<col_name:string>
+-- !query 17 output
+col 5
+col1
+
+
+-- !query 18
+SHOW COLUMNS IN showcolumn4 FROM global_temp
+-- !query 18 schema
+struct<col_name:string>
+-- !query 18 output
+col 5
+col1
+
+
+-- !query 19
+DROP TABLE showcolumn1
+-- !query 19 schema
+struct<>
+-- !query 19 output
+
+
+
+-- !query 20
+DROP TABLE showColumn2
+-- !query 20 schema
+struct<>
+-- !query 20 output
+
+
+
+-- !query 21
+DROP VIEW  showcolumn3
+-- !query 21 schema
+struct<>
+-- !query 21 output
+
+
+
+-- !query 22
+DROP VIEW  global_temp.showcolumn4
+-- !query 22 schema
+struct<>
+-- !query 22 output
+
+
+
+-- !query 23
+use default
+-- !query 23 schema
+struct<>
+-- !query 23 output
+
+
+
+-- !query 24
+DROP DATABASE showdb
+-- !query 24 schema
+struct<>
+-- !query 24 output
+