[SPARK-19602][SQL][TESTS] Add tests for qualified column names

## What changes were proposed in this pull request?
- Add tests covering different scenarios with qualified column names
- Please see Section 2 in the design doc for the various test scenarios [here](https://issues.apache.org/jira/secure/attachment/12854681/Design_ColResolution_JIRA19602.pdf)
- As part of SPARK-19602, changes are made to support three part column name. In order to aid in the review and to reduce the diff, the test scenarios are separated out into this PR.

## How was this patch tested?
- This is a **test only** change. The individual test suites were run successfully.

Author: Sunitha Kambhampati <skambha@us.ibm.com>

Closes #17067 from skambha/colResolutionTests.

1 files changed, 447 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/results/columnresolution.sql.out b/sql/core/src/test/resources/sql-tests/results/columnresolution.sql.out
new file mode 100644
index 0000000000..764cad0e39
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/columnresolution.sql.out
@@ -0,0 +1,447 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 54
+
+
+-- !query 0
+CREATE DATABASE mydb1
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+USE mydb1
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+CREATE TABLE t1 USING parquet AS SELECT 1 AS i1
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+CREATE DATABASE mydb2
+-- !query 3 schema
+struct<>
+-- !query 3 output
+
+
+
+-- !query 4
+USE mydb2
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+CREATE TABLE t1 USING parquet AS SELECT 20 AS i1
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+USE mydb1
+-- !query 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+SELECT i1 FROM t1
+-- !query 7 schema
+struct<i1:int>
+-- !query 7 output
+1
+
+
+-- !query 8
+SELECT i1 FROM mydb1.t1
+-- !query 8 schema
+struct<i1:int>
+-- !query 8 output
+1
+
+
+-- !query 9
+SELECT t1.i1 FROM t1
+-- !query 9 schema
+struct<i1:int>
+-- !query 9 output
+1
+
+
+-- !query 10
+SELECT t1.i1 FROM mydb1.t1
+-- !query 10 schema
+struct<i1:int>
+-- !query 10 output
+1
+
+
+-- !query 11
+SELECT mydb1.t1.i1 FROM t1
+-- !query 11 schema
+struct<>
+-- !query 11 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t1.i1`' given input columns: [i1]; line 1 pos 7
+
+
+-- !query 12
+SELECT mydb1.t1.i1 FROM mydb1.t1
+-- !query 12 schema
+struct<>
+-- !query 12 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t1.i1`' given input columns: [i1]; line 1 pos 7
+
+
+-- !query 13
+USE mydb2
+-- !query 13 schema
+struct<>
+-- !query 13 output
+
+
+
+-- !query 14
+SELECT i1 FROM t1
+-- !query 14 schema
+struct<i1:int>
+-- !query 14 output
+20
+
+
+-- !query 15
+SELECT i1 FROM mydb1.t1
+-- !query 15 schema
+struct<i1:int>
+-- !query 15 output
+1
+
+
+-- !query 16
+SELECT t1.i1 FROM t1
+-- !query 16 schema
+struct<i1:int>
+-- !query 16 output
+20
+
+
+-- !query 17
+SELECT t1.i1 FROM mydb1.t1
+-- !query 17 schema
+struct<i1:int>
+-- !query 17 output
+1
+
+
+-- !query 18
+SELECT mydb1.t1.i1 FROM mydb1.t1
+-- !query 18 schema
+struct<>
+-- !query 18 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t1.i1`' given input columns: [i1]; line 1 pos 7
+
+
+-- !query 19
+USE mydb1
+-- !query 19 schema
+struct<>
+-- !query 19 output
+
+
+
+-- !query 20
+SELECT t1.* FROM t1
+-- !query 20 schema
+struct<i1:int>
+-- !query 20 output
+1
+
+
+-- !query 21
+SELECT mydb1.t1.* FROM mydb1.t1
+-- !query 21 schema
+struct<>
+-- !query 21 output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'mydb1.t1.*' give input columns 'i1';
+
+
+-- !query 22
+SELECT t1.* FROM mydb1.t1
+-- !query 22 schema
+struct<i1:int>
+-- !query 22 output
+1
+
+
+-- !query 23
+USE mydb2
+-- !query 23 schema
+struct<>
+-- !query 23 output
+
+
+
+-- !query 24
+SELECT t1.* FROM t1
+-- !query 24 schema
+struct<i1:int>
+-- !query 24 output
+20
+
+
+-- !query 25
+SELECT mydb1.t1.* FROM mydb1.t1
+-- !query 25 schema
+struct<>
+-- !query 25 output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'mydb1.t1.*' give input columns 'i1';
+
+
+-- !query 26
+SELECT t1.* FROM mydb1.t1
+-- !query 26 schema
+struct<i1:int>
+-- !query 26 output
+1
+
+
+-- !query 27
+SELECT a.* FROM mydb1.t1 AS a
+-- !query 27 schema
+struct<i1:int>
+-- !query 27 output
+1
+
+
+-- !query 28
+USE mydb1
+-- !query 28 schema
+struct<>
+-- !query 28 output
+
+
+
+-- !query 29
+CREATE TABLE t3 USING parquet AS SELECT * FROM VALUES (4,1), (3,1) AS t3(c1, c2)
+-- !query 29 schema
+struct<>
+-- !query 29 output
+
+
+
+-- !query 30
+CREATE TABLE t4 USING parquet AS SELECT * FROM VALUES (4,1), (2,1) AS t4(c2, c3)
+-- !query 30 schema
+struct<>
+-- !query 30 output
+
+
+
+-- !query 31
+SELECT * FROM t3 WHERE c1 IN (SELECT c2 FROM t4 WHERE t4.c3 = t3.c2)
+-- !query 31 schema
+struct<c1:int,c2:int>
+-- !query 31 output
+4	1
+
+
+-- !query 32
+SELECT * FROM mydb1.t3 WHERE c1 IN
+  (SELECT mydb1.t4.c2 FROM mydb1.t4 WHERE mydb1.t4.c3 = mydb1.t3.c2)
+-- !query 32 schema
+struct<>
+-- !query 32 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t4.c3`' given input columns: [c2, c3]; line 2 pos 42
+
+
+-- !query 33
+SET spark.sql.crossJoin.enabled = true
+-- !query 33 schema
+struct<key:string,value:string>
+-- !query 33 output
+spark.sql.crossJoin.enabled	true
+
+
+-- !query 34
+SELECT mydb1.t1.i1 FROM t1, mydb2.t1
+-- !query 34 schema
+struct<>
+-- !query 34 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t1.i1`' given input columns: [i1, i1]; line 1 pos 7
+
+
+-- !query 35
+SELECT mydb1.t1.i1 FROM mydb1.t1, mydb2.t1
+-- !query 35 schema
+struct<>
+-- !query 35 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t1.i1`' given input columns: [i1, i1]; line 1 pos 7
+
+
+-- !query 36
+USE mydb2
+-- !query 36 schema
+struct<>
+-- !query 36 output
+
+
+
+-- !query 37
+SELECT mydb1.t1.i1 FROM t1, mydb1.t1
+-- !query 37 schema
+struct<>
+-- !query 37 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t1.i1`' given input columns: [i1, i1]; line 1 pos 7
+
+
+-- !query 38
+SET spark.sql.crossJoin.enabled = false
+-- !query 38 schema
+struct<key:string,value:string>
+-- !query 38 output
+spark.sql.crossJoin.enabled	false
+
+
+-- !query 39
+USE mydb1
+-- !query 39 schema
+struct<>
+-- !query 39 output
+
+
+
+-- !query 40
+CREATE TABLE t5(i1 INT, t5 STRUCT<i1:INT, i2:INT>) USING parquet
+-- !query 40 schema
+struct<>
+-- !query 40 output
+
+
+
+-- !query 41
+INSERT INTO t5 VALUES(1, (2, 3))
+-- !query 41 schema
+struct<>
+-- !query 41 output
+
+
+
+-- !query 42
+SELECT t5.i1 FROM t5
+-- !query 42 schema
+struct<i1:int>
+-- !query 42 output
+1
+
+
+-- !query 43
+SELECT t5.t5.i1 FROM t5
+-- !query 43 schema
+struct<i1:int>
+-- !query 43 output
+2
+
+
+-- !query 44
+SELECT t5.t5.i1 FROM mydb1.t5
+-- !query 44 schema
+struct<i1:int>
+-- !query 44 output
+2
+
+
+-- !query 45
+SELECT t5.i1 FROM mydb1.t5
+-- !query 45 schema
+struct<i1:int>
+-- !query 45 output
+1
+
+
+-- !query 46
+SELECT t5.* FROM mydb1.t5
+-- !query 46 schema
+struct<i1:int,t5:struct<i1:int,i2:int>>
+-- !query 46 output
+1	{"i1":2,"i2":3}
+
+
+-- !query 47
+SELECT t5.t5.* FROM mydb1.t5
+-- !query 47 schema
+struct<i1:int,i2:int>
+-- !query 47 output
+2	3
+
+
+-- !query 48
+SELECT mydb1.t5.t5.i1 FROM mydb1.t5
+-- !query 48 schema
+struct<>
+-- !query 48 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t5.t5.i1`' given input columns: [i1, t5]; line 1 pos 7
+
+
+-- !query 49
+SELECT mydb1.t5.t5.i2 FROM mydb1.t5
+-- !query 49 schema
+struct<>
+-- !query 49 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '`mydb1.t5.t5.i2`' given input columns: [i1, t5]; line 1 pos 7
+
+
+-- !query 50
+SELECT mydb1.t5.* FROM mydb1.t5
+-- !query 50 schema
+struct<>
+-- !query 50 output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'mydb1.t5.*' give input columns 'i1, t5';
+
+
+-- !query 51
+USE default
+-- !query 51 schema
+struct<>
+-- !query 51 output
+
+
+
+-- !query 52
+DROP DATABASE mydb1 CASCADE
+-- !query 52 schema
+struct<>
+-- !query 52 output
+
+
+
+-- !query 53
+DROP DATABASE mydb2 CASCADE
+-- !query 53 schema
+struct<>
+-- !query 53 output
+