sql-tests/inputs/columnresolution.sql

0001 -- Tests covering different scenarios with qualified column names
0002 -- Scenario: column resolution scenarios with datasource table
0003 CREATE DATABASE mydb1;
0004 USE mydb1;
0005 CREATE TABLE t1 USING parquet AS SELECT 1 AS i1;
0006
0007 CREATE DATABASE mydb2;
0008 USE mydb2;
0009 CREATE TABLE t1 USING parquet AS SELECT 20 AS i1;
0010
0011 USE mydb1;
0012 SELECT i1 FROM t1;
0013 SELECT i1 FROM mydb1.t1;
0014 SELECT t1.i1 FROM t1;
0015 SELECT t1.i1 FROM mydb1.t1;
0016
0017 SELECT mydb1.t1.i1 FROM t1;
0018 SELECT mydb1.t1.i1 FROM mydb1.t1;
0019
0020 USE mydb2;
0021 SELECT i1 FROM t1;
0022 SELECT i1 FROM mydb1.t1;
0023 SELECT t1.i1 FROM t1;
0024 SELECT t1.i1 FROM mydb1.t1;
0025 SELECT mydb1.t1.i1 FROM mydb1.t1;
0026
0027 -- Scenario: resolve fully qualified table name in star expansion
0028 USE mydb1;
0029 SELECT t1.* FROM t1;
0030 SELECT mydb1.t1.* FROM mydb1.t1;
0031 SELECT t1.* FROM mydb1.t1;
0032 USE mydb2;
0033 SELECT t1.* FROM t1;
0034 SELECT mydb1.t1.* FROM mydb1.t1;
0035 SELECT t1.* FROM mydb1.t1;
0036 SELECT a.* FROM mydb1.t1 AS a;
0037
0038 -- Scenario: resolve in case of subquery
0039
0040 USE mydb1;
0041 CREATE TABLE t3 USING parquet AS SELECT * FROM VALUES (4,1), (3,1) AS t3(c1, c2);
0042 CREATE TABLE t4 USING parquet AS SELECT * FROM VALUES (4,1), (2,1) AS t4(c2, c3);
0043
0044 SELECT * FROM t3 WHERE c1 IN (SELECT c2 FROM t4 WHERE t4.c3 = t3.c2);
0045
0046 SELECT * FROM mydb1.t3 WHERE c1 IN
0047   (SELECT mydb1.t4.c2 FROM mydb1.t4 WHERE mydb1.t4.c3 = mydb1.t3.c2);
0048
0049 -- Scenario: column resolution scenarios in join queries
0050 SET spark.sql.crossJoin.enabled = true;
0051
0052 SELECT mydb1.t1.i1 FROM t1, mydb2.t1;
0053
0054 SELECT mydb1.t1.i1 FROM mydb1.t1, mydb2.t1;
0055
0056 USE mydb2;
0057 SELECT mydb1.t1.i1 FROM t1, mydb1.t1;
0058 SET spark.sql.crossJoin.enabled = false;
0059
0060 -- Scenario: Table with struct column
0061 USE mydb1;
0062 CREATE TABLE t5(i1 INT, t5 STRUCT<i1:INT, i2:INT>) USING parquet;
0063 INSERT INTO t5 VALUES(1, (2, 3));
0064 SELECT t5.i1 FROM t5;
0065 SELECT t5.t5.i1 FROM t5;
0066 SELECT t5.t5.i1 FROM mydb1.t5;
0067 SELECT t5.i1 FROM mydb1.t5;
0068 SELECT t5.* FROM mydb1.t5;
0069 SELECT t5.t5.* FROM mydb1.t5;
0070 SELECT mydb1.t5.t5.i1 FROM mydb1.t5;
0071 SELECT mydb1.t5.t5.i2 FROM mydb1.t5;
0072 SELECT mydb1.t5.* FROM mydb1.t5;
0073 SELECT mydb1.t5.* FROM t5;
0074
0075 -- Cleanup and Reset
0076 USE default;
0077 DROP DATABASE mydb1 CASCADE;
0078 DROP DATABASE mydb2 CASCADE;