This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 05923bf4db [CALCITE-6434] JDBC adapter generates wrong SQL for Spark
and Hive because it fails to quote an identifier containing '$'
05923bf4db is described below
commit 05923bf4dbe1900b3b04939a8d254acad2faeff4
Author: Xiong Duan <nobigo...@gmail.com>
AuthorDate: Sun Jun 9 06:55:02 2024 +0800
[CALCITE-6434] JDBC adapter generates wrong SQL for Spark and Hive because
it fails to quote an identifier containing '$'
---
.../org/apache/calcite/test/BabelParserTest.java | 12 +-
.../apache/calcite/sql/dialect/HiveSqlDialect.java | 1 +
.../calcite/sql/dialect/SparkSqlDialect.java | 1 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 252 +++++++++++----------
.../java/org/apache/calcite/tools/PlannerTest.java | 4 +-
.../apache/calcite/sql/parser/SqlParserTest.java | 30 +--
6 files changed, 156 insertions(+), 144 deletions(-)
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java
b/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java
index 14df0a38a6..8137a6b4c6 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java
@@ -439,12 +439,12 @@ class BabelParserTest extends SqlParserTest {
+ "from geo.area2\n"
+ "where cname = 'cityA') as b on a.cid = b.cid\n"
+ "group by a.cid, a.cname";
- final String expected = "SELECT A.CID, A.CNAME, COUNT(1) AMOUNT\n"
- + "FROM GEO.AREA1 A\n"
- + "LEFT ANTI JOIN (SELECT DISTINCT CID, CNAME\n"
- + "FROM GEO.AREA2\n"
- + "WHERE (CNAME = 'cityA')) B ON (A.CID = B.CID)\n"
- + "GROUP BY A.CID, A.CNAME";
+ final String expected = "SELECT `A`.`CID`, `A`.`CNAME`, COUNT(1)
`AMOUNT`\n"
+ + "FROM `GEO`.`AREA1` `A`\n"
+ + "LEFT ANTI JOIN (SELECT DISTINCT `CID`, `CNAME`\n"
+ + "FROM `GEO`.`AREA2`\n"
+ + "WHERE (`CNAME` = 'cityA')) `B` ON (`A`.`CID` = `B`.`CID`)\n"
+ + "GROUP BY `A`.`CID`, `A`.`CNAME`";
f.sql(sql).ok(expected);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
index cfce5bb25c..a8cee95f3b 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java
@@ -41,6 +41,7 @@ import static
org.apache.calcite.util.RelToSqlConverterUtil.unparseSparkArrayAnd
public class HiveSqlDialect extends SqlDialect {
public static final SqlDialect.Context DEFAULT_CONTEXT =
SqlDialect.EMPTY_CONTEXT
.withDatabaseProduct(SqlDialect.DatabaseProduct.HIVE)
+ .withIdentifierQuoteString("`")
.withNullCollation(NullCollation.LOW);
public static final SqlDialect DEFAULT = new HiveSqlDialect(DEFAULT_CONTEXT);
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
index 85ed75c90f..c2daf0ab67 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
@@ -46,6 +46,7 @@ import static
org.apache.calcite.util.RelToSqlConverterUtil.unparseSparkArrayAnd
public class SparkSqlDialect extends SqlDialect {
public static final SqlDialect.Context DEFAULT_CONTEXT =
SqlDialect.EMPTY_CONTEXT
.withDatabaseProduct(SqlDialect.DatabaseProduct.SPARK)
+ .withIdentifierQuoteString("`")
.withNullCollation(NullCollation.LOW);
public static final SqlDialect DEFAULT = new
SparkSqlDialect(DEFAULT_CONTEXT);
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 5334e2546e..1e8da43a2f 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -532,9 +532,9 @@ class RelToSqlConverterTest {
@Test void testSelectQueryWithHiveCube() {
String query = "select \"product_class_id\", \"product_id\", count(*) "
+ "from \"product\" group by cube(\"product_class_id\",
\"product_id\")";
- String expected = "SELECT product_class_id, product_id, COUNT(*)\n"
- + "FROM foodmart.product\n"
- + "GROUP BY product_class_id, product_id WITH CUBE";
+ String expected = "SELECT `product_class_id`, `product_id`, COUNT(*)\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY `product_class_id`, `product_id` WITH CUBE";
sql(query).withHive().ok(expected);
SqlDialect sqlDialect = sql(query).withHive().dialect;
assertTrue(sqlDialect.supportsGroupByWithCube());
@@ -543,9 +543,9 @@ class RelToSqlConverterTest {
@Test void testSelectQueryWithHiveRollup() {
String query = "select \"product_class_id\", \"product_id\", count(*) "
+ "from \"product\" group by rollup(\"product_class_id\",
\"product_id\")";
- String expected = "SELECT product_class_id, product_id, COUNT(*)\n"
- + "FROM foodmart.product\n"
- + "GROUP BY product_class_id, product_id WITH ROLLUP";
+ String expected = "SELECT `product_class_id`, `product_id`, COUNT(*)\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY `product_class_id`, `product_id` WITH ROLLUP";
sql(query).withHive().ok(expected);
SqlDialect sqlDialect = sql(query).withHive().dialect;
assertTrue(sqlDialect.supportsGroupByWithRollup());
@@ -1767,10 +1767,10 @@ class RelToSqlConverterTest {
+ "FROM (SELECT SUM(net_weight) AS net_weight1\n"
+ "FROM foodmart.product\n"
+ "GROUP BY product_id) AS t1";
- final String expectedHive = "SELECT SUM(net_weight1)
net_weight_converted\n"
- + "FROM (SELECT SUM(net_weight) net_weight1\n"
- + "FROM foodmart.product\n"
- + "GROUP BY product_id) t1";
+ final String expectedHive = "SELECT SUM(`net_weight1`)
`net_weight_converted`\n"
+ + "FROM (SELECT SUM(`net_weight`) `net_weight1`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY `product_id`) `t1`";
final String expectedSpark = expectedHive;
final String expectedExasol = expectedBigQuery;
final String expectedStarRocks = "SELECT SUM(`net_weight1`) AS
`net_weight_converted`\n"
@@ -2241,8 +2241,8 @@ class RelToSqlConverterTest {
@Test void testHiveSelectCharset() {
String query = "select \"hire_date\", cast(\"hire_date\" as varchar(10)) "
+ "from \"foodmart\".\"reserve_employee\"";
- final String expected = "SELECT hire_date, CAST(hire_date AS
VARCHAR(10))\n"
- + "FROM foodmart.reserve_employee";
+ final String expected = "SELECT `hire_date`, CAST(`hire_date` AS
VARCHAR(10))\n"
+ + "FROM `foodmart`.`reserve_employee`";
sql(query).withHive().ok(expected);
}
@@ -2280,8 +2280,8 @@ class RelToSqlConverterTest {
@Test void testHiveCastAsInt() {
String query = "select cast( cast(\"employee_id\" as varchar) as int) "
+ "from \"foodmart\".\"reserve_employee\" ";
- final String expected = "SELECT employee_id\n"
- + "FROM foodmart.reserve_employee";
+ final String expected = "SELECT `employee_id`\n"
+ + "FROM `foodmart`.`reserve_employee`";
sql(query).withHive().ok(expected);
}
@@ -2496,9 +2496,11 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(' str ')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT TRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ final String expectedBigQuery = "SELECT TRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery().ok(expected)
+ .withBigQuery().ok(expectedBigQuery)
.withHive().ok(expected)
.withSpark().ok(expected);
}
@@ -2507,9 +2509,11 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(both ' ' from ' str ')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT TRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ final String expectedBigQuery = "SELECT TRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery().ok(expected)
+ .withBigQuery().ok(expectedBigQuery)
.withHive().ok(expected)
.withSpark().ok(expected);
}
@@ -2518,9 +2522,11 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(LEADING ' ' from ' str ')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT LTRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ final String expectedBigQuery = "SELECT LTRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery().ok(expected)
+ .withBigQuery().ok(expectedBigQuery)
.withHive().ok(expected)
.withSpark().ok(expected);
}
@@ -2529,9 +2535,11 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(TRAILING ' ' from ' str ')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT RTRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ final String expectedBigQuery = "SELECT RTRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery().ok(expected)
+ .withBigQuery().ok(expectedBigQuery)
.withHive().ok(expected)
.withSpark().ok(expected);
}
@@ -2558,7 +2566,7 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(LEADING 'a' from 'abcd')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT REGEXP_REPLACE('abcd', '^(a)*', '')\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query)
.withHive().ok(expected)
.withSpark().ok(expected);
@@ -2577,7 +2585,7 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(both 'a' from 'abcda')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT REGEXP_REPLACE('abcda', '^(a)*|(a)*$',
'')\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query)
.withHive().ok(expected)
.withSpark().ok(expected);
@@ -2596,7 +2604,7 @@ class RelToSqlConverterTest {
final String query = "SELECT TRIM(TRAILING 'a' from 'abcd')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT REGEXP_REPLACE('abcd', '(a)*$', '')\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query)
.withHive().ok(expected)
.withSpark().ok(expected);
@@ -2617,7 +2625,7 @@ class RelToSqlConverterTest {
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT REGEXP_REPLACE('$@*AABC$@*AADCAA$@*A',"
+ " '^(\\$\\@\\*A)*|(\\$\\@\\*A)*$', '')\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query)
.withHive().ok(expected)
.withSpark().ok(expected);
@@ -2654,8 +2662,8 @@ class RelToSqlConverterTest {
final String query = "select * from \"employee\" where \"hire_date\" - "
+ "INTERVAL '19800' SECOND(5) > cast(\"hire_date\" as TIMESTAMP(0))";
final String expectedSpark = "SELECT *\n"
- + "FROM foodmart.employee\n"
- + "WHERE (hire_date - INTERVAL '19800' SECOND(5)) > CAST(hire_date AS
TIMESTAMP)";
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` - INTERVAL '19800' SECOND(5)) > CAST(`hire_date`
AS TIMESTAMP)";
final String expectedPresto = "SELECT *\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "WHERE (\"hire_date\" - INTERVAL '19800' SECOND) >
CAST(\"hire_date\" AS TIMESTAMP)";
@@ -2663,8 +2671,8 @@ class RelToSqlConverterTest {
+ "FROM `foodmart`.`employee`\n"
+ "WHERE (`hire_date` - INTERVAL '19800' SECOND) > CAST(`hire_date` AS
DATETIME)";
final String expectedHive = "SELECT *\n"
- + "FROM foodmart.employee\n"
- + "WHERE (hire_date - INTERVAL '19800' SECOND(5)) > CAST(hire_date AS
TIMESTAMP)";
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` - INTERVAL '19800' SECOND(5)) > CAST(`hire_date`
AS TIMESTAMP)";
sql(query)
.withSpark().ok(expectedSpark)
.withPresto().ok(expectedPresto)
@@ -2771,8 +2779,8 @@ class RelToSqlConverterTest {
@Test void testSelectQueryWithLimitClause() {
String query = "select \"product_id\" from \"product\" limit 100 offset
10";
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ "LIMIT 100\n"
+ "OFFSET 10";
final String expectedStarRocks = "SELECT `product_id`\n"
@@ -2786,7 +2794,7 @@ class RelToSqlConverterTest {
@Test void testPositionFunctionForHive() {
final String query = "select position('A' IN 'ABC') from \"product\"";
final String expected = "SELECT INSTR('ABC', 'A')\n"
- + "FROM foodmart.product";
+ + "FROM `foodmart`.`product`";
sql(query).withHive().ok(expected);
}
@@ -2811,12 +2819,12 @@ class RelToSqlConverterTest {
@Test void testPositionForSpark() {
final String query = "SELECT POSITION('a' IN 'abc')";
final String expected = "SELECT POSITION('a', 'abc')\n"
- + "FROM (VALUES (0)) t (ZERO)";
+ + "FROM (VALUES (0)) `t` (`ZERO`)";
sql(query).withSpark().ok(expected);
final String query2 = "SELECT POSITION('a' IN 'abc' FROM 1)";
final String expected2 = "SELECT POSITION('a', 'abc', 1)\n"
- + "FROM (VALUES (0)) t (ZERO)";
+ + "FROM (VALUES (0)) `t` (`ZERO`)";
sql(query2).withSpark().ok(expected2);
}
@@ -2897,7 +2905,7 @@ class RelToSqlConverterTest {
@Test void testModFunctionForHive() {
final String query = "select mod(11,3) from \"product\"";
final String expected = "SELECT 11 % 3\n"
- + "FROM foodmart.product";
+ + "FROM `foodmart`.`product`";
sql(query).withHive().ok(expected);
}
@@ -2949,9 +2957,9 @@ class RelToSqlConverterTest {
final String query = "select \"product_id\" from \"product\"\n"
+ "order by \"product_id\" desc nulls first";
// Hive and MSSQL do not support NULLS FIRST, so need to emulate
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
- + "ORDER BY product_id IS NULL DESC, product_id DESC";
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `product_id` IS NULL DESC, `product_id` DESC";
final String mssqlExpected = "SELECT [product_id]\n"
+ "FROM [foodmart].[product]\n"
+ "ORDER BY CASE WHEN [product_id] IS NULL THEN 0 ELSE 1 END,
[product_id] DESC";
@@ -2964,9 +2972,9 @@ class RelToSqlConverterTest {
final String query = "select \"product_id\" from \"product\"\n"
+ "order by \"product_id\" nulls last";
// Hive and MSSQL do not support NULLS LAST, so need to emulate
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
- + "ORDER BY product_id IS NULL, product_id";
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `product_id` IS NULL, `product_id`";
final String mssqlExpected = "SELECT [product_id]\n"
+ "FROM [foodmart].[product]\n"
+ "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END,
[product_id]";
@@ -2980,9 +2988,9 @@ class RelToSqlConverterTest {
+ "order by \"product_id\" nulls first";
// Hive and MSSQL do not support NULLS FIRST, but nulls sort low, so no
// need to emulate
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
- + "ORDER BY product_id";
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `product_id`";
final String mssqlExpected = "SELECT [product_id]\n"
+ "FROM [foodmart].[product]\n"
+ "ORDER BY [product_id]";
@@ -2996,14 +3004,14 @@ class RelToSqlConverterTest {
+ "order by \"product_id\" desc nulls last";
// Hive and MSSQL do not support NULLS LAST, but nulls sort low, so no
// need to emulate
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
- + "ORDER BY product_id DESC";
+ final String expectedHive = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `product_id` DESC";
final String mssqlExpected = "SELECT [product_id]\n"
+ "FROM [foodmart].[product]\n"
+ "ORDER BY [product_id] DESC";
sql(query)
- .dialect(HiveSqlDialect.DEFAULT).ok(expected)
+ .dialect(HiveSqlDialect.DEFAULT).ok(expectedHive)
.dialect(MssqlSqlDialect.DEFAULT).ok(mssqlExpected);
}
@@ -3011,24 +3019,24 @@ class RelToSqlConverterTest {
final String query = "SELECT row_number() over "
+ "(order by \"hire_date\" desc nulls first) FROM \"employee\"";
final String expected = "SELECT ROW_NUMBER() "
- + "OVER (ORDER BY hire_date IS NULL DESC, hire_date DESC)\n"
- + "FROM foodmart.employee";
+ + "OVER (ORDER BY `hire_date` IS NULL DESC, `hire_date` DESC)\n"
+ + "FROM `foodmart`.`employee`";
sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected);
}
@Test void testHiveSelectQueryWithOverAscAndNullsLastShouldBeEmulated() {
final String query = "SELECT row_number() over "
+ "(order by \"hire_date\" nulls last) FROM \"employee\"";
- final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY hire_date IS
NULL, hire_date)\n"
- + "FROM foodmart.employee";
+ final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY `hire_date` IS
NULL, `hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected);
}
@Test void
testHiveSelectQueryWithOverAscNullsFirstShouldNotAddNullEmulation() {
final String query = "SELECT row_number() over "
+ "(order by \"hire_date\" nulls first) FROM \"employee\"";
- final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY hire_date)\n"
- + "FROM foodmart.employee";
+ final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY `hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected);
}
@@ -3036,7 +3044,7 @@ class RelToSqlConverterTest {
String query = "SELECT SUBSTRING('ABC', 2)"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT SUBSTRING('ABC', 2)\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query).withHive().ok(expected);
}
@@ -3044,7 +3052,7 @@ class RelToSqlConverterTest {
String query = "SELECT SUBSTRING('ABC', 2, 3)"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT SUBSTRING('ABC', 2, 3)\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query).withHive().ok(expected);
}
@@ -3052,7 +3060,7 @@ class RelToSqlConverterTest {
String query = "SELECT SUBSTRING('ABC' FROM 2)"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT SUBSTRING('ABC', 2)\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query).withHive().ok(expected);
}
@@ -3060,15 +3068,15 @@ class RelToSqlConverterTest {
String query = "SELECT SUBSTRING('ABC' FROM 2 FOR 3)"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT SUBSTRING('ABC', 2, 3)\n"
- + "FROM foodmart.reserve_employee";
+ + "FROM `foodmart`.`reserve_employee`";
sql(query).withHive().ok(expected);
}
@Test void
testHiveSelectQueryWithOverDescNullsLastShouldNotAddNullEmulation() {
final String query = "SELECT row_number() over "
+ "(order by \"hire_date\" desc nulls last) FROM \"employee\"";
- final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY hire_date
DESC)\n"
- + "FROM foodmart.employee";
+ final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY `hire_date`
DESC)\n"
+ + "FROM `foodmart`.`employee`";
sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected);
}
@@ -3127,9 +3135,9 @@ class RelToSqlConverterTest {
final String query = "select \"product_id\" from \"product\"\n"
+ "order by \"product_id\" desc nulls first";
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
- + "ORDER BY product_id DESC NULLS FIRST";
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `product_id` DESC NULLS FIRST";
sql(query).dialect(hive2_1Dialect).ok(expected);
sql(query).dialect(hive2_2_Dialect).ok(expected);
}
@@ -3163,9 +3171,9 @@ class RelToSqlConverterTest {
.withNullCollation(NullCollation.LOW));
final String query = "select \"product_id\" from \"product\"\n"
+ "order by \"product_id\" desc nulls first";
- final String expected = "SELECT product_id\n"
- + "FROM foodmart.product\n"
- + "ORDER BY product_id IS NULL DESC, product_id DESC";
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `product_id` IS NULL DESC, `product_id` DESC";
sql(query).dialect(hive2_1_0_Dialect).ok(expected);
}
@@ -3542,10 +3550,10 @@ class RelToSqlConverterTest {
+ "FROM `foodmart`.`product`";
final String expectedMysql = "SELECT CAST(`product_id` AS CHAR)\n"
+ "FROM `foodmart`.`product`";
- final String expectedHive = "SELECT CAST(product_id AS STRING)\n"
- + "FROM foodmart.product";
- final String expectedSpark = "SELECT CAST(product_id AS STRING)\n"
- + "FROM foodmart.product";
+ final String expectedHive = "SELECT CAST(`product_id` AS STRING)\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedSpark = "SELECT CAST(`product_id` AS STRING)\n"
+ + "FROM `foodmart`.`product`";
sql(query)
.withClickHouse().ok(expectedClickHouse)
.withMysql().ok(expectedMysql)
@@ -3557,10 +3565,10 @@ class RelToSqlConverterTest {
String query = "select cast(\"product_id\" as varchar(5)) from
\"product\"";
final String expectedMysql = "SELECT CAST(`product_id` AS CHAR(5))\n"
+ "FROM `foodmart`.`product`";
- final String expectedHive = "SELECT CAST(product_id AS VARCHAR(5))\n"
- + "FROM foodmart.product";
- final String expectedSpark = "SELECT CAST(product_id AS STRING)\n"
- + "FROM foodmart.product";
+ final String expectedHive = "SELECT CAST(`product_id` AS VARCHAR(5))\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedSpark = "SELECT CAST(`product_id` AS STRING)\n"
+ + "FROM `foodmart`.`product`";
sql(query)
.withMysql().ok(expectedMysql)
.withHive().ok(expectedHive)
@@ -3571,10 +3579,10 @@ class RelToSqlConverterTest {
String query = "select cast(\"product_id\" as char) from \"product\"";
final String expectedMysql = "SELECT CAST(`product_id` AS CHAR)\n"
+ "FROM `foodmart`.`product`";
- final String expectedHive = "SELECT CAST(product_id AS CHAR(1))\n"
- + "FROM foodmart.product";
- final String expectedSpark = "SELECT CAST(product_id AS CHAR(1))\n"
- + "FROM foodmart.product";
+ final String expectedHive = "SELECT CAST(`product_id` AS CHAR(1))\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedSpark = "SELECT CAST(`product_id` AS CHAR(1))\n"
+ + "FROM `foodmart`.`product`";
sql(query)
.withMysql().ok(expectedMysql)
.withHive().ok(expectedHive)
@@ -3585,10 +3593,10 @@ class RelToSqlConverterTest {
String query = "select cast(\"product_id\" as char(5)) from \"product\"";
final String expectedMysql = "SELECT CAST(`product_id` AS CHAR(5))\n"
+ "FROM `foodmart`.`product`";
- final String expectedHive = "SELECT CAST(product_id AS CHAR(5))\n"
- + "FROM foodmart.product";
- final String expectedSpark = "SELECT CAST(product_id AS CHAR(5))\n"
- + "FROM foodmart.product";
+ final String expectedHive = "SELECT CAST(`product_id` AS CHAR(5))\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedSpark = "SELECT CAST(`product_id` AS CHAR(5))\n"
+ + "FROM `foodmart`.`product`";
sql(query)
.withMysql().ok(expectedMysql)
.withHive().ok(expectedHive)
@@ -6299,10 +6307,10 @@ class RelToSqlConverterTest {
+ "UNION ALL\n"
+ "SELECT 2 \"a\", 'yy' \"b\"\n"
+ "FROM \"DUAL\")";
- final String expectedHive = "SELECT a\n"
- + "FROM (SELECT 1 a, 'x ' b\n"
+ final String expectedHive = "SELECT `a`\n"
+ + "FROM (SELECT 1 `a`, 'x ' `b`\n"
+ "UNION ALL\n"
- + "SELECT 2 a, 'yy' b)";
+ + "SELECT 2 `a`, 'yy' `b`)";
final String expectedBigQuery = "SELECT a\n"
+ "FROM (SELECT 1 AS a, 'x ' AS b\n"
+ "UNION ALL\n"
@@ -6830,8 +6838,8 @@ class RelToSqlConverterTest {
*/
@Test void testCrossJoinEmulation() {
final String expectedSpark = "SELECT *\n"
- + "FROM foodmart.employee\n"
- + "CROSS JOIN foodmart.department";
+ + "FROM `foodmart`.`employee`\n"
+ + "CROSS JOIN `foodmart`.`department`";
final String expectedMysql = "SELECT *\n"
+ "FROM `foodmart`.`employee`,\n"
+ "`foodmart`.`department`";
@@ -6858,9 +6866,9 @@ class RelToSqlConverterTest {
+ "`foodmart`.`employee`,\n"
+ "`foodmart`.`department`";
final String expectedSpark = "SELECT *\n"
- + "FROM foodmart.store\n"
- + "CROSS JOIN foodmart.employee\n"
- + "CROSS JOIN foodmart.department";
+ + "FROM `foodmart`.`store`\n"
+ + "CROSS JOIN `foodmart`.`employee`\n"
+ + "CROSS JOIN `foodmart`.`department`";
final String expectedStarRocks = "SELECT *\n"
+ "FROM `foodmart`.`store`,\n"
+ "`foodmart`.`employee`,\n"
@@ -7021,32 +7029,32 @@ class RelToSqlConverterTest {
@Test void testSubstringInSpark() {
final String query = "select substring(\"brand_name\" from 2) "
+ "from \"product\"\n";
- final String expected = "SELECT SUBSTRING(brand_name, 2)\n"
- + "FROM foodmart.product";
+ final String expected = "SELECT SUBSTRING(`brand_name`, 2)\n"
+ + "FROM `foodmart`.`product`";
sql(query).withSpark().ok(expected);
}
@Test void testSubstringWithForInSpark() {
final String query = "select substring(\"brand_name\" from 2 for 3) "
+ "from \"product\"\n";
- final String expected = "SELECT SUBSTRING(brand_name, 2, 3)\n"
- + "FROM foodmart.product";
+ final String expected = "SELECT SUBSTRING(`brand_name`, 2, 3)\n"
+ + "FROM `foodmart`.`product`";
sql(query).withSpark().ok(expected);
}
@Test void testFloorInSpark() {
final String query = "select floor(\"hire_date\" TO MINUTE) "
+ "from \"employee\"";
- final String expected = "SELECT DATE_TRUNC('MINUTE', hire_date)\n"
- + "FROM foodmart.employee";
+ final String expected = "SELECT DATE_TRUNC('MINUTE', `hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
sql(query).withSpark().ok(expected);
}
@Test void testNumericFloorInSpark() {
final String query = "select floor(\"salary\") "
+ "from \"employee\"";
- final String expected = "SELECT FLOOR(salary)\n"
- + "FROM foodmart.employee";
+ final String expected = "SELECT FLOOR(`salary`)\n"
+ + "FROM `foodmart`.`employee`";
sql(query).withSpark().ok(expected);
}
@@ -7065,8 +7073,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY CUBE(\"product_id\", \"product_class_id\")";
final String expectedSpark = "SELECT COUNT(*)\n"
- + "FROM foodmart.product\n"
- + "GROUP BY CUBE(product_id, product_class_id)";
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY CUBE(`product_id`, `product_class_id`)";
sql(query)
.ok(expected)
.withPresto().ok(expected)
@@ -7081,8 +7089,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_id\", \"product_class_id\")";
final String expectedSpark = "SELECT COUNT(*)\n"
- + "FROM foodmart.product\n"
- + "GROUP BY ROLLUP(product_id, product_class_id)";
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_id`, `product_class_id`)";
final String expectedStarRocks = "SELECT COUNT(*)\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY ROLLUP(`product_id`, `product_class_id`)";
@@ -7492,7 +7500,9 @@ class RelToSqlConverterTest {
final String query = "select approx_count_distinct(\"product_id\") from
\"product\"";
final String expectedExact = "SELECT COUNT(DISTINCT \"product_id\")\n"
+ "FROM \"foodmart\".\"product\"";
- final String expectedApprox = "SELECT APPROX_COUNT_DISTINCT(product_id)\n"
+ final String expectedApprox = "SELECT
APPROX_COUNT_DISTINCT(`product_id`)\n"
+ + "FROM `foodmart`.`product`";
+ final String expectedBigQuery = "SELECT
APPROX_COUNT_DISTINCT(product_id)\n"
+ "FROM foodmart.product";
final String expectedApproxQuota = "SELECT
APPROX_COUNT_DISTINCT(\"product_id\")\n"
+ "FROM \"foodmart\".\"product\"";
@@ -7503,7 +7513,7 @@ class RelToSqlConverterTest {
sql(query).ok(expectedExact)
.withHive().ok(expectedApprox)
.withSpark().ok(expectedApprox)
- .withBigQuery().ok(expectedApprox)
+ .withBigQuery().ok(expectedBigQuery)
.withOracle().ok(expectedApproxQuota)
.withSnowflake().ok(expectedApproxQuota)
.withPresto().ok(expectedPrestoSql)
@@ -7525,10 +7535,10 @@ class RelToSqlConverterTest {
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
- final String expectedHive = "INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC)\n"
+ final String expectedHive = "INSERT INTO `SCOTT`.`DEPT` (`DEPTNO`,
`DNAME`, `LOC`)\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
+ "(2, 'Eric', 'Washington')";
- final String expectedHiveX = "INSERT INTO SCOTT.DEPT (DEPTNO, DNAME,
LOC)\n"
+ final String expectedHiveX = "INSERT INTO `SCOTT`.`DEPT` (`DEPTNO`,
`DNAME`, `LOC`)\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'";
@@ -8011,7 +8021,7 @@ class RelToSqlConverterTest {
@Test void testSparkSoundexFunction() {
final String query = "select soundex('Miller') from \"product\"\n";
final String expectedSql = "SELECT SOUNDEX('Miller')\n"
- + "FROM foodmart.product";
+ + "FROM `foodmart`.`product`";
sql(query).withSpark().withLibrary(SqlLibrary.SPARK).ok(expectedSql);
}
@@ -8026,8 +8036,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\"";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name NULLS LAST";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name` NULLS LAST";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8039,8 +8049,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\"";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name NULLS LAST";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name` NULLS LAST";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8052,8 +8062,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\"";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name NULLS LAST";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name` NULLS LAST";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8065,8 +8075,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\" IS NULL DESC, \"brand_name\"";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name`";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8078,8 +8088,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\" IS NULL DESC, \"brand_name\" DESC";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name DESC NULLS FIRST";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name` DESC NULLS FIRST";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8091,8 +8101,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\" DESC";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name DESC";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name` DESC";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8104,8 +8114,8 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "ORDER BY \"brand_name\" IS NULL DESC, \"brand_name\" DESC";
final String sparkExpected = "SELECT *\n"
- + "FROM foodmart.product\n"
- + "ORDER BY brand_name DESC NULLS FIRST";
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `brand_name` DESC NULLS FIRST";
sql(query)
.withPresto().ok(expected)
.withSpark().ok(sparkExpected);
@@ -8120,7 +8130,7 @@ class RelToSqlConverterTest {
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
final String expectedStarRocks = "SELECT MAP { 'k1' : 'v1', 'k2' : 'v2' }";
final String expectedSpark = "SELECT MAP ('k1', 'v1', 'k2', 'v2')\n"
- + "FROM (VALUES (0)) t (ZERO)";
+ + "FROM (VALUES (0)) `t` (`ZERO`)";
final String expectedHive = "SELECT MAP ('k1', 'v1', 'k2', 'v2')";
sql(query)
.withPresto().ok(expectedPresto)
@@ -8134,7 +8144,7 @@ class RelToSqlConverterTest {
final String expectedPresto = "SELECT MAP (ARRAY['k1', 'k2'], ARRAY['v1',
'v2'])\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
final String expectedSpark = "SELECT MAP (ARRAY ('k1', 'k2'), ARRAY ('v1',
'v2'))\n"
- + "FROM (VALUES (0)) t (ZERO)";
+ + "FROM (VALUES (0)) `t` (`ZERO`)";
sql(query)
.withPresto().ok(expectedPresto)
.withSpark().ok(expectedSpark);
@@ -8176,7 +8186,7 @@ class RelToSqlConverterTest {
final String query = "SELECT ARRAY[1, 2, 3]";
final String expectedStarRocks = "SELECT[1, 2, 3]";
final String expectedSpark = "SELECT ARRAY (1, 2, 3)\n"
- + "FROM (VALUES (0)) t (ZERO)";
+ + "FROM (VALUES (0)) `t` (`ZERO`)";
final String expectedHive = "SELECT ARRAY (1, 2, 3)";
sql(query).withStarRocks().ok(expectedStarRocks)
.withSpark().ok(expectedSpark)
diff --git a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
index 92464f6aa3..a57ec7fb2f 100644
--- a/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
+++ b/core/src/test/java/org/apache/calcite/tools/PlannerTest.java
@@ -829,8 +829,8 @@ class PlannerTest {
assertThat(Util.toLinux(parse.toSqlString(hiveDialect).getSql()),
equalTo("SELECT *\n"
+ "FROM (SELECT *\n"
- + "FROM emps) T\n"
- + "WHERE name LIKE '%e%'"));
+ + "FROM `emps`) `T`\n"
+ + "WHERE `name` LIKE '%e%'"));
}
/** Unit test that calls {@link Planner#transform} twice,
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index c0bfb3bbb7..092690cd9b 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -3788,30 +3788,30 @@ public class SqlParserTest {
* dialect, which uses LIMIT and OFFSET rather than OFFSET and FETCH. */
@Test void testLimitSpark() {
final String sql1 = "select a from foo order by b, c limit 2 offset 1";
- final String expected1 = "SELECT A\n"
- + "FROM FOO\n"
- + "ORDER BY B, C\n"
+ final String expected1 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "ORDER BY `B`, `C`\n"
+ "LIMIT 2\n"
+ "OFFSET 1";
sql(sql1).withDialect(SparkSqlDialect.DEFAULT).ok(expected1);
final String sql2 = "select a from foo order by b, c limit 2";
- final String expected2 = "SELECT A\n"
- + "FROM FOO\n"
- + "ORDER BY B, C\n"
+ final String expected2 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "ORDER BY `B`, `C`\n"
+ "LIMIT 2";
sql(sql2).withDialect(SparkSqlDialect.DEFAULT).ok(expected2);
final String sql3 = "select a from foo order by b, c offset 1";
- final String expected3 = "SELECT A\n"
- + "FROM FOO\n"
- + "ORDER BY B, C\n"
+ final String expected3 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "ORDER BY `B`, `C`\n"
+ "OFFSET 1";
sql(sql3).withDialect(SparkSqlDialect.DEFAULT).ok(expected3);
final String sql4 = "select a from foo offset 10";
- final String expected4 = "SELECT A\n"
- + "FROM FOO\n"
+ final String expected4 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ "OFFSET 10";
sql(sql4).withDialect(SparkSqlDialect.DEFAULT).ok(expected4);
@@ -3819,11 +3819,11 @@ public class SqlParserTest {
+ "union\n"
+ "select b from baz\n"
+ "limit 3";
- final String expected5 = "SELECT A\n"
- + "FROM FOO\n"
+ final String expected5 = "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ "UNION\n"
- + "SELECT B\n"
- + "FROM BAZ\n"
+ + "SELECT `B`\n"
+ + "FROM `BAZ`\n"
+ "LIMIT 3";
sql(sql5).withDialect(SparkSqlDialect.DEFAULT).ok(expected5);
}
