sql-tests/inputs/query_regex_column.sql

0001 set spark.sql.parser.quotedRegexColumnNames=false;
0002
0003 CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
0004 (1, "1", "11"), (2, "2", "22"), (3, "3", "33"), (4, "4", "44"), (5, "5", "55"), (6, "6", "66")
0005 AS testData(key, value1, value2);
0006
0007 CREATE OR REPLACE TEMPORARY VIEW testData2 AS SELECT * FROM VALUES
0008 (1, 1, 1, 2), (1, 2, 1, 2), (2, 1, 2, 3), (2, 2, 2, 3), (3, 1, 3, 4), (3, 2, 3, 4)
0009 AS testData2(A, B, c, d);
0010
0011 -- AnalysisException
0012 SELECT `(a)?+.+` FROM testData2 WHERE a = 1;
0013 SELECT t.`(a)?+.+` FROM testData2 t WHERE a = 1;
0014 SELECT `(a|b)` FROM testData2 WHERE a = 2;
0015 SELECT `(a|b)?+.+` FROM testData2 WHERE a = 2;
0016 SELECT SUM(`(a|b)?+.+`) FROM testData2;
0017 SELECT SUM(`(a)`) FROM testData2;
0018
0019 set spark.sql.parser.quotedRegexColumnNames=true;
0020
0021 -- Regex columns
0022 SELECT `(a)?+.+` FROM testData2 WHERE a = 1;
0023 SELECT `(A)?+.+` FROM testData2 WHERE a = 1;
0024 SELECT t.`(a)?+.+` FROM testData2 t WHERE a = 1;
0025 SELECT t.`(A)?+.+` FROM testData2 t WHERE a = 1;
0026 SELECT `(a|B)` FROM testData2 WHERE a = 2;
0027 SELECT `(A|b)` FROM testData2 WHERE a = 2;
0028 SELECT `(a|B)?+.+` FROM testData2 WHERE a = 2;
0029 SELECT `(A|b)?+.+` FROM testData2 WHERE a = 2;
0030 SELECT `(e|f)` FROM testData2;
0031 SELECT t.`(e|f)` FROM testData2 t;
0032 SELECT p.`(KEY)?+.+`, b, testdata2.`(b)?+.+` FROM testData p join testData2 ON p.key = testData2.a WHERE key < 3;
0033 SELECT p.`(key)?+.+`, b, testdata2.`(b)?+.+` FROM testData p join testData2 ON p.key = testData2.a WHERE key < 3;
0034
0035 set spark.sql.caseSensitive=true;
0036
0037 CREATE OR REPLACE TEMPORARY VIEW testdata3 AS SELECT * FROM VALUES
0038 (0, 1), (1, 2), (2, 3), (3, 4)
0039 AS testdata3(a, b);
0040
0041 -- Regex columns
0042 SELECT `(A)?+.+` FROM testdata3;
0043 SELECT `(a)?+.+` FROM testdata3;
0044 SELECT `(A)?+.+` FROM testdata3 WHERE a > 1;
0045 SELECT `(a)?+.+` FROM testdata3 where `a` > 1;
0046 SELECT SUM(`a`) FROM testdata3;
0047 SELECT SUM(`(a)`) FROM testdata3;
0048 SELECT SUM(`(a)?+.+`) FROM testdata3;
0049 SELECT SUM(a) FROM testdata3 GROUP BY `a`;
0050 -- AnalysisException
0051 SELECT SUM(a) FROM testdata3 GROUP BY `(a)`;
0052 SELECT SUM(a) FROM testdata3 GROUP BY `(a)?+.+`;