Cosmetic
In RelToSqlConverterTest,
merge methods that test the same query in several dialects;
merge '.withXxx().ok(expectedXxx)' into one line,
and sort such lines alphabetically;
normalize 'expectedPostgreSQL' and 'expectedPostgreSql' to
'expectedPostgresql', and so forth.
diff --git a/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java b/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
index 5622817..b909946 100644
--- a/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
@@ -468,7 +468,7 @@
@Test void testTableModify() {
final String sql = "insert into \"employee\" select * from \"employee\"";
- final String expectedPhysial = ""
+ final String expectedPhysical = ""
+ "JdbcToEnumerableConverter\n"
+ " JdbcTableModify(table=[[foodmart, employee]], operation=[INSERT], flattened=[true])\n"
+ " JdbcTableScan(table=[[foodmart, employee]])\n";
@@ -476,7 +476,7 @@
+ "LogicalTableModify(table=[[foodmart, employee]], "
+ "operation=[INSERT], flattened=[true])\n"
+ " LogicalTableScan(table=[[foodmart, employee]])\n";
- verify(rel(sql), expectedPhysial, expectedLogical);
+ verify(rel(sql), expectedPhysical, expectedLogical);
}
}
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 2ef2b39..98f75c9 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
@@ -215,7 +215,9 @@
@Test void testSimpleSelectStarFromProductTable() {
String query = "select * from \"product\"";
- sql(query).ok("SELECT *\nFROM \"foodmart\".\"product\"");
+ String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query).ok(expected);
}
@Test void testAggregateFilterWhereToSqlFromProductTable() {
@@ -392,22 +394,18 @@
final String sql1 = "select count(*) from \"product\"";
final String expected = "SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"product\"";
- final String expectedMySql = "SELECT COUNT(*)\n"
+ final String expectedMysql = "SELECT COUNT(*)\n"
+ "FROM `foodmart`.`product`";
final String expectedPresto = "SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"product\"";
sql(sql0)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withPresto()
- .ok(expectedPresto);
+ .withMysql().ok(expectedMysql)
+ .withPresto().ok(expectedPresto);
sql(sql1)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withPresto()
- .ok(expectedPresto);
+ .withMysql().ok(expectedMysql)
+ .withPresto().ok(expectedPresto);
}
@Test void testSelectQueryWithGroupByEmpty2() {
@@ -415,7 +413,7 @@
final String expected = "SELECT 42 AS \"C\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ()";
- final String expectedMySql = "SELECT 42 AS `C`\n"
+ final String expectedMysql = "SELECT 42 AS `C`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY ()";
final String expectedPresto = "SELECT 42 AS \"C\"\n"
@@ -423,10 +421,8 @@
+ "GROUP BY ()";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withPresto()
- .ok(expectedPresto);
+ .withMysql().ok(expectedMysql)
+ .withPresto().ok(expectedPresto);
}
/** Test case for
@@ -445,8 +441,7 @@
+ " \"product_class_id\")\n"
+ "ORDER BY \"brand_name\", \"product_class_id\"";
sql(query)
- .withPostgresql()
- .ok(expected);
+ .withPostgresql().ok(expected);
}
/** Tests GROUP BY ROLLUP of two columns. The SQL for MySQL has
@@ -460,19 +455,17 @@
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\", \"brand_name\")\n"
+ "ORDER BY \"product_class_id\", \"brand_name\"";
- final String expectedMySql = "SELECT `product_class_id`, `brand_name`\n"
+ final String expectedMysql = "SELECT `product_class_id`, `brand_name`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP";
- final String expectedMySql8 = "SELECT `product_class_id`, `brand_name`\n"
+ final String expectedMysql8 = "SELECT `product_class_id`, `brand_name`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY ROLLUP(`product_class_id`, `brand_name`)\n"
+ "ORDER BY `product_class_id` NULLS LAST, `brand_name` NULLS LAST";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withMysql8()
- .ok(expectedMySql8);
+ .withMysql().ok(expectedMysql)
+ .withMysql8().ok(expectedMysql8);
}
/** As {@link #testSelectQueryWithGroupByRollup()},
@@ -486,13 +479,12 @@
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\", \"brand_name\")\n"
+ "ORDER BY \"brand_name\", \"product_class_id\"";
- final String expectedMySql = "SELECT `product_class_id`, `brand_name`\n"
+ final String expectedMysql = "SELECT `product_class_id`, `brand_name`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `brand_name`, `product_class_id` WITH ROLLUP";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql);
+ .withMysql().ok(expectedMysql);
}
/** Tests a query with GROUP BY and a sub-query which is also with GROUP BY.
@@ -529,8 +521,7 @@
+ "GROUP BY `product_class_id`, `product_id`) AS `t1`";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMysql);
+ .withMysql().ok(expectedMysql);
// Equivalent sub-query that uses SELECT DISTINCT
final String query2 = "select sum(\"product_id\")\n"
@@ -538,8 +529,7 @@
+ " from \"product\") as t";
sql(query2)
.ok(expected)
- .withMysql()
- .ok(expectedMysql);
+ .withMysql().ok(expectedMysql);
}
/** CUBE of one column is equivalent to ROLLUP, and Calcite recognizes
@@ -553,7 +543,7 @@
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\")\n"
+ "ORDER BY \"product_class_id\", COUNT(*)";
- final String expectedMySql = "SELECT `product_class_id`, COUNT(*) AS `C`\n"
+ final String expectedMysql = "SELECT `product_class_id`, COUNT(*) AS `C`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id` WITH ROLLUP\n"
+ "ORDER BY `product_class_id` IS NULL, `product_class_id`,"
@@ -565,10 +555,8 @@
+ "COUNT(*) IS NULL, COUNT(*)";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withPresto()
- .ok(expectedPresto);
+ .withMysql().ok(expectedMysql)
+ .withPresto().ok(expectedPresto);
}
/** As {@link #testSelectQueryWithSingletonCube()}, but no ORDER BY
@@ -580,7 +568,7 @@
final String expected = "SELECT \"product_class_id\", COUNT(*) AS \"C\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\")";
- final String expectedMySql = "SELECT `product_class_id`, COUNT(*) AS `C`\n"
+ final String expectedMysql = "SELECT `product_class_id`, COUNT(*) AS `C`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id` WITH ROLLUP";
final String expectedPresto = "SELECT \"product_class_id\", COUNT(*) AS \"C\"\n"
@@ -588,10 +576,8 @@
+ "GROUP BY ROLLUP(\"product_class_id\")";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withPresto()
- .ok(expectedPresto);
+ .withMysql().ok(expectedMysql)
+ .withPresto().ok(expectedPresto);
}
/** Cannot rewrite if ORDER BY contains a column not in GROUP BY (in this
@@ -607,7 +593,7 @@
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\", \"brand_name\")\n"
+ "ORDER BY \"product_class_id\", \"brand_name\", COUNT(*)";
- final String expectedMySql = "SELECT `product_class_id`, `brand_name`,"
+ final String expectedMysql = "SELECT `product_class_id`, `brand_name`,"
+ " COUNT(*) AS `C`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP\n"
@@ -616,8 +602,7 @@
+ " COUNT(*) IS NULL, COUNT(*)";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql);
+ .withMysql().ok(expectedMysql);
}
/** As {@link #testSelectQueryWithSingletonCube()}, but with LIMIT. */
@@ -632,7 +617,7 @@
+ "FETCH NEXT 5 ROWS ONLY";
// If a MySQL 5 query has GROUP BY ... ROLLUP, you cannot add ORDER BY,
// but you can add LIMIT.
- final String expectedMySql = "SELECT `product_class_id`, COUNT(*) AS `C`\n"
+ final String expectedMysql = "SELECT `product_class_id`, COUNT(*) AS `C`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id` WITH ROLLUP\n"
+ "LIMIT 5";
@@ -642,10 +627,8 @@
+ "LIMIT 5";
sql(query)
.ok(expected)
- .withMysql()
- .ok(expectedMySql)
- .withPresto()
- .ok(expectedPresto);
+ .withMysql().ok(expectedMysql)
+ .withPresto().ok(expectedPresto);
}
@Test void testSelectQueryWithMinAggregateFunction() {
@@ -773,7 +756,7 @@
@Test void testCastLongVarchar1() {
final String query = "select cast(\"store_id\" as VARCHAR(10485761))\n"
+ " from \"expense_fact\"";
- final String expectedPostgreSQL = "SELECT CAST(\"store_id\" AS VARCHAR(256))\n"
+ final String expectedPostgresql = "SELECT CAST(\"store_id\" AS VARCHAR(256))\n"
+ "FROM \"foodmart\".\"expense_fact\"";
final String expectedOracle = "SELECT CAST(\"store_id\" AS VARCHAR(512))\n"
+ "FROM \"foodmart\".\"expense_fact\"";
@@ -781,7 +764,7 @@
+ "FROM \"foodmart\".\"expense_fact\"";
sql(query)
.withPostgresqlModifiedTypeSystem()
- .ok(expectedPostgreSQL)
+ .ok(expectedPostgresql)
.withOracleModifiedTypeSystem()
.ok(expectedOracle)
.withRedshift()
@@ -795,11 +778,11 @@
@Test void testCastLongVarchar2() {
final String query = "select cast(\"store_id\" as VARCHAR(175))\n"
+ " from \"expense_fact\"";
- final String expectedPostgreSQL = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n"
+ final String expectedPostgresql = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n"
+ "FROM \"foodmart\".\"expense_fact\"";
sql(query)
.withPostgresqlModifiedTypeSystem()
- .ok(expectedPostgreSQL);
+ .ok(expectedPostgresql);
final String expectedOracle = "SELECT CAST(\"store_id\" AS VARCHAR(175))\n"
+ "FROM \"foodmart\".\"expense_fact\"";
@@ -823,10 +806,8 @@
final String expectedPostgresql = "SELECT COALESCE(SUM(\"MGR\"), 0) AS \"s\"\n"
+ "FROM \"scott\".\"EMP\"";
relFn(relFn)
- .withPostgresql()
- .ok(expectedPostgresql)
- .withMysql()
- .ok(expectedMysql);
+ .withPostgresql().ok(expectedPostgresql)
+ .withMysql().ok(expectedMysql);
}
/** As {@link #testSum0BecomesCoalesce()} but for windowed aggregates. */
@@ -842,8 +823,7 @@
+ " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT ROW))\n"
+ "FROM \"foodmart\".\"product\"";
sql(query)
- .withPostgresql()
- .ok(expectedPostgresql);
+ .withPostgresql().ok(expectedPostgresql);
}
/** Test case for
@@ -1082,7 +1062,7 @@
final String expectedOracle = "SELECT SUM(SUM(\"net_weight\")) \"net_weight_converted\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY \"product_id\"";
- final String expectedMySQL = "SELECT SUM(`net_weight1`) AS `net_weight_converted`\n"
+ final String expectedMysql = "SELECT SUM(`net_weight1`) AS `net_weight_converted`\n"
+ "FROM (SELECT SUM(`net_weight`) AS `net_weight1`\n"
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_id`) AS `t1`";
@@ -1102,22 +1082,14 @@
final String expectedSpark = expectedHive;
final String expectedExasol = expectedBigQuery;
sql(query)
- .withOracle()
- .ok(expectedOracle)
- .withMysql()
- .ok(expectedMySQL)
- .withVertica()
- .ok(expectedVertica)
- .withPostgresql()
- .ok(expectedPostgresql)
- .withBigQuery()
- .ok(expectedBigQuery)
- .withHive()
- .ok(expectedHive)
- .withSpark()
- .ok(expectedSpark)
- .withExasol()
- .ok(expectedExasol);
+ .withBigQuery().ok(expectedBigQuery)
+ .withExasol().ok(expectedExasol)
+ .withHive().ok(expectedHive)
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql)
+ .withSpark().ok(expectedSpark)
+ .withVertica().ok(expectedVertica);
}
/** Test case for
@@ -1298,9 +1270,9 @@
+ "GROUP BY DEPTNO\n"
+ "HAVING `emps.count` < 2) AS t1";
relFn(b -> root)
+ .withBigQuery().ok(expectedBigQuery)
.withMysql().ok(expectedMysql)
- .withPostgresql().ok(expectedPostgresql)
- .withBigQuery().ok(expectedBigQuery);
+ .withPostgresql().ok(expectedPostgresql);
}
/** Test case for
@@ -1354,9 +1326,9 @@
+ "GROUP BY product_id\n"
+ "HAVING gross_weight < 200) AS t1";
sql(query)
+ .withBigQuery().ok(expectedBigQuery)
.withPostgresql().ok(expectedPostgresql)
- .withMysql().ok(expectedMysql)
- .withBigQuery().ok(expectedBigQuery);
+ .withMysql().ok(expectedMysql);
}
@Test void testHaving4() {
@@ -1521,12 +1493,9 @@
final String expected = "SELECT TRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected)
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
@Test void testHiveSparkAndBqTrimWithBoth() {
@@ -1535,12 +1504,9 @@
final String expected = "SELECT TRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected)
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
@Test void testHiveSparkAndBqTrimWithLeading() {
@@ -1549,27 +1515,20 @@
final String expected = "SELECT LTRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected)
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
-
@Test void testHiveSparkAndBqTrimWithTailing() {
final String query = "SELECT TRIM(TRAILING ' ' from ' str ')\n"
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT RTRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected)
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
/** Test case for
@@ -1583,8 +1542,7 @@
final String expectedHS = "SELECT REGEXP_REPLACE('abcd', '^(a)*', '')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected);
}
/** Test case for
@@ -1597,10 +1555,8 @@
final String expected = "SELECT REGEXP_REPLACE('abcd', '^(a)*', '')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected);
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
@Test void testBqTrimWithBothChar() {
@@ -1609,8 +1565,7 @@
final String expected = "SELECT TRIM('abcda', 'a')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected);
}
@Test void testHiveAndSparkTrimWithBothChar() {
@@ -1619,10 +1574,8 @@
final String expected = "SELECT REGEXP_REPLACE('abcda', '^(a)*|(a)*$', '')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected);
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
@Test void testHiveBqTrimWithTailingChar() {
@@ -1631,8 +1584,7 @@
final String expected = "SELECT RTRIM('abcd', 'a')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected);
}
@Test void testHiveAndSparkTrimWithTailingChar() {
@@ -1641,10 +1593,8 @@
final String expected = "SELECT REGEXP_REPLACE('abcd', '(a)*$', '')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected);
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
@Test void testBqTrimWithBothSpecialCharacter() {
@@ -1664,40 +1614,32 @@
+ " '^(\\$\\@\\*A)*|(\\$\\@\\*A)*$', '')\n"
+ "FROM foodmart.reserve_employee";
sql(query)
- .withHive()
- .ok(expected)
- .withSpark()
- .ok(expected);
+ .withHive().ok(expected)
+ .withSpark().ok(expected);
}
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2715">[CALCITE-2715]
- * MS SQL Server does not support character set as part of data type</a>. */
- @Test void testMssqlCharacterSet() {
- String query = "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).withMssql().ok(expected);
- }
-
- /** Test case for
+ * MS SQL Server does not support character set as part of data type</a>
+ * and
* <a href="https://issues.apache.org/jira/browse/CALCITE-4690">[CALCITE-4690]
* Error when executing query with CHARACTER SET in Redshift</a>. */
- @Test void testRedshiftCharacterSet() {
+ @Test void testCharacterSet() {
String query = "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"
+ final String expectedMssql = "SELECT [hire_date],"
+ + " CAST([hire_date] AS VARCHAR(10))\n"
+ + "FROM [foodmart].[reserve_employee]";
+ final String expectedRedshift = "SELECT \"hire_date\","
+ + " CAST(\"hire_date\" AS VARCHAR(10))\n"
+ "FROM \"foodmart\".\"reserve_employee\"";
- sql(query).withRedshift().ok(expected);
- }
-
- @Test void testExasolCharacterSet() {
- String query = "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"
+ final String expectedExasol = "SELECT hire_date,"
+ + " CAST(hire_date AS VARCHAR(10))\n"
+ "FROM foodmart.reserve_employee";
- sql(query).withExasol().ok(expected);
+ sql(query)
+ .withExasol().ok(expectedExasol)
+ .withMssql().ok(expectedMssql)
+ .withRedshift().ok(expectedRedshift);
}
@Test void testExasolCastToTimestamp() {
@@ -1776,7 +1718,8 @@
String query = "select \"product_id\" from \"product\" limit 100 offset 10";
final String expected = "SELECT product_id\n"
+ "FROM foodmart.product\n"
- + "LIMIT 100\nOFFSET 10";
+ + "LIMIT 100\n"
+ + "OFFSET 10";
sql(query).withHive().ok(expected);
}
@@ -2066,21 +2009,21 @@
@Test void testHiveSelectQueryWithOverDescAndHighNullsWithVersionGreaterThanOrEq21() {
final HiveSqlDialect hive2_1Dialect =
- new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT
- .withDatabaseMajorVersion(2)
- .withDatabaseMinorVersion(1)
- .withNullCollation(NullCollation.LOW));
+ new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT
+ .withDatabaseMajorVersion(2)
+ .withDatabaseMinorVersion(1)
+ .withNullCollation(NullCollation.LOW));
final HiveSqlDialect hive2_2_Dialect =
- new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT
- .withDatabaseMajorVersion(2)
- .withDatabaseMinorVersion(2)
- .withNullCollation(NullCollation.LOW));
+ new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT
+ .withDatabaseMajorVersion(2)
+ .withDatabaseMinorVersion(2)
+ .withNullCollation(NullCollation.LOW));
final String query = "SELECT row_number() over "
- + "(order by \"hire_date\" desc nulls first) FROM \"employee\"";
+ + "(order by \"hire_date\" desc nulls first) FROM \"employee\"";
final String expected = "SELECT ROW_NUMBER() OVER (ORDER BY hire_date DESC NULLS FIRST)\n"
- + "FROM foodmart.employee";
+ + "FROM foodmart.employee";
sql(query).dialect(hive2_1Dialect).ok(expected);
sql(query).dialect(hive2_2_Dialect).ok(expected);
}
@@ -2101,15 +2044,15 @@
@Test void testHiveSelectQueryWithOverDescAndHighNullsWithVersion20() {
final HiveSqlDialect hive2_1_0_Dialect =
- new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT
- .withDatabaseMajorVersion(2)
- .withDatabaseMinorVersion(0)
- .withNullCollation(NullCollation.LOW));
+ new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT
+ .withDatabaseMajorVersion(2)
+ .withDatabaseMinorVersion(0)
+ .withNullCollation(NullCollation.LOW));
final String query = "SELECT row_number() over "
- + "(order by \"hire_date\" desc nulls first) FROM \"employee\"";
+ + "(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";
+ + "(ORDER BY hire_date IS NULL DESC, hire_date DESC)\n"
+ + "FROM foodmart.employee";
sql(query).dialect(hive2_1_0_Dialect).ok(expected);
}
@@ -2214,8 +2157,10 @@
@Test void testMySqlCastToTimestamp() {
final String query = "select * from \"employee\" where \"hire_date\" - "
+ "INTERVAL '19800' SECOND(5) > cast(\"hire_date\" as TIMESTAMP) ";
- final String expected = "SELECT *\nFROM `foodmart`.`employee`"
- + "\nWHERE (`hire_date` - INTERVAL '19800' SECOND) > CAST(`hire_date` AS DATETIME)";
+ final String expected = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` - INTERVAL '19800' SECOND)"
+ + " > CAST(`hire_date` AS DATETIME)";
sql(query).withMysql().ok(expected);
}
@@ -2444,10 +2389,8 @@
final String expectedMysql = "SELECT CAST(`product_id` AS CHAR)\n"
+ "FROM `foodmart`.`product`";
sql(query)
- .withClickHouse()
- .ok(expectedClickHouse)
- .withMysql()
- .ok(expectedMysql);
+ .withClickHouse().ok(expectedClickHouse)
+ .withMysql().ok(expectedMysql);
}
@Test void testSelectQueryWithLimitClauseWithoutOrder() {
@@ -2461,8 +2404,7 @@
+ "LIMIT 10, 100";
sql(query)
.ok(expected)
- .withClickHouse()
- .ok(expectedClickHouse);
+ .withClickHouse().ok(expectedClickHouse);
final String expectedPresto = "SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"product\"\n"
@@ -2470,8 +2412,7 @@
+ "LIMIT 100";
sql(query)
.ok(expected)
- .withPresto()
- .ok(expectedPresto);
+ .withPresto().ok(expectedPresto);
}
@Test void testSelectQueryWithLimitOffsetClause() {
@@ -2752,7 +2693,8 @@
+ "GROUP BY v.g";
final String expected = "SELECT"
+ " CASE WHEN \"t0\".\"G\" IN (0, 1) THEN 0 ELSE 1 END\n"
- + "FROM (SELECT *\nFROM \"foodmart\".\"customer\") AS \"t\",\n"
+ + "FROM (SELECT *\n"
+ + "FROM \"foodmart\".\"customer\") AS \"t\",\n"
+ "(VALUES (0)) AS \"t0\" (\"G\")\n"
+ "GROUP BY \"t0\".\"G\"";
sql(query).ok(expected);
@@ -2765,9 +2707,12 @@
final String expected = "SELECT "
+ "\"department\".\"department_id\", \"department\""
+ ".\"department_description\"\n"
- + "FROM \"foodmart\".\"department\"\nINNER JOIN "
- + "(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\"\n"
- + "WHERE \"store_id\" < 150\nGROUP BY \"department_id\") AS \"t1\" "
+ + "FROM \"foodmart\".\"department\"\n"
+ + "INNER JOIN "
+ + "(SELECT \"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "WHERE \"store_id\" < 150\n"
+ + "GROUP BY \"department_id\") AS \"t1\" "
+ "ON \"department\".\"department_id\" = \"t1\".\"department_id\"";
sql(query).ok(expected);
}
@@ -2982,8 +2927,7 @@
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(expected)
- .withDb2()
- .ok(expectedDb2);
+ .withDb2().ok(expectedDb2);
}
/** Test case for
@@ -3197,10 +3141,10 @@
}
private void checkLiteral2(String expression, String expected) {
+ String expectedHsqldb = "SELECT *\n"
+ + "FROM (VALUES (" + expected + ")) AS t (EXPR$0)";
sql("VALUES " + expression)
- .withHsqldb()
- .ok("SELECT *\n"
- + "FROM (VALUES (" + expected + ")) AS t (EXPR$0)");
+ .withHsqldb().ok(expectedHsqldb);
}
/** Test case for
@@ -3287,9 +3231,9 @@
String query7 = "SELECT "
+ "count(distinct \"employee_id\") over (order by \"hire_date\") FROM \"employee\"";
String expected7 = "SELECT "
- + "COUNT(DISTINCT \"employee_id\") "
- + "OVER (ORDER BY \"hire_date\" RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$0\""
- + "\nFROM \"foodmart\".\"employee\"";
+ + "COUNT(DISTINCT \"employee_id\") OVER (ORDER BY \"hire_date\""
+ + " RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"$0\"\n"
+ + "FROM \"foodmart\".\"employee\"";
String query8 = "SELECT "
+ "sum(distinct \"position_id\") over (order by \"hire_date\") FROM \"employee\"";
@@ -3408,8 +3352,7 @@
+ " ROW_NUMBER() OVER (ORDER BY \"product_id\") AS \"RN\"\n"
+ "FROM \"foodmart\".\"product\") AS \"t\"";
sql(query)
- .withPostgresql()
- .ok(expected);
+ .withPostgresql().ok(expected);
}
/** Test case for
@@ -3417,42 +3360,22 @@
* Generate dialect-specific SQL for FLOOR operator</a>. */
@Test void testFloor() {
String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
- String expected = "SELECT TRUNC(hire_date, 'MI')\nFROM foodmart.employee";
+ String expectedClickHouse = "SELECT toStartOfMinute(`hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
+ String expectedHsqldb = "SELECT TRUNC(hire_date, 'MI')\n"
+ + "FROM foodmart.employee";
+ String expectedOracle = "SELECT TRUNC(\"hire_date\", 'MINUTE')\n"
+ + "FROM \"foodmart\".\"employee\"";
+ String expectedPostgresql = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ String expectedPresto = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
sql(query)
- .withHsqldb()
- .ok(expected);
- }
-
- @Test void testFloorClickHouse() {
- String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
- String expected = "SELECT toStartOfMinute(`hire_date`)\nFROM `foodmart`.`employee`";
- sql(query)
- .withClickHouse()
- .ok(expected);
- }
-
- @Test void testFloorPostgres() {
- String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
- String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM \"foodmart\".\"employee\"";
- sql(query)
- .withPostgresql()
- .ok(expected);
- }
-
- @Test void testFloorOracle() {
- String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
- String expected = "SELECT TRUNC(\"hire_date\", 'MINUTE')\nFROM \"foodmart\".\"employee\"";
- sql(query)
- .withOracle()
- .ok(expected);
- }
-
- @Test void testFloorPresto() {
- String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
- String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM \"foodmart\".\"employee\"";
- sql(query)
- .withPresto()
- .ok(expected);
+ .withClickHouse().ok(expectedClickHouse)
+ .withHsqldb().ok(expectedHsqldb)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql)
+ .withPresto().ok(expectedPresto);
}
@Test void testFloorMssqlWeek() {
@@ -3461,8 +3384,7 @@
+ "DATEADD(day, - (6 + DATEPART(weekday, [hire_date] )) % 7, [hire_date] ), 126))\n"
+ "FROM [foodmart].[employee]";
sql(query)
- .withMssql()
- .ok(expected);
+ .withMssql().ok(expected);
}
@Test void testFloorMssqlMonth() {
@@ -3470,8 +3392,7 @@
String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), [hire_date] , 126)+'-01')\n"
+ "FROM [foodmart].[employee]";
sql(query)
- .withMssql()
- .ok(expected);
+ .withMssql().ok(expected);
}
@Test void testFloorMysqlMonth() {
@@ -3479,8 +3400,7 @@
String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-01')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withMysql()
- .ok(expected);
+ .withMysql().ok(expected);
}
@Test void testFloorWeek() {
@@ -3494,12 +3414,9 @@
+ "'%x%v-%w')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withClickHouse()
- .ok(expectedClickHouse)
- .withMssql()
- .ok(expectedMssql)
- .withMysql()
- .ok(expectedMysql);
+ .withClickHouse().ok(expectedClickHouse)
+ .withMssql().ok(expectedMssql)
+ .withMysql().ok(expectedMysql);
}
@Test void testUnparseSqlIntervalQualifierDb2() {
@@ -3511,8 +3428,7 @@
+ " > TIMESTAMP '2005-10-17 00:00:00'";
sql(queryDatePlus)
- .withDb2()
- .ok(expectedDatePlus);
+ .withDb2().ok(expectedDatePlus);
String queryDateMinus = "select * from \"employee\" where \"hire_date\" - "
+ "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' ";
@@ -3522,8 +3438,7 @@
+ " > TIMESTAMP '2005-10-17 00:00:00'";
sql(queryDateMinus)
- .withDb2()
- .ok(expectedDateMinus);
+ .withDb2().ok(expectedDateMinus);
}
@Test void testUnparseSqlIntervalQualifierMySql() {
@@ -3569,8 +3484,7 @@
+ "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'";
sql(queryDatePlus)
- .withMssql()
- .ok(expectedDatePlus);
+ .withMssql().ok(expectedDatePlus);
String queryDateMinus = "select * from \"employee\" where \"hire_date\" -"
+ "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' ";
@@ -3579,8 +3493,7 @@
+ "WHERE DATEADD(SECOND, -19800, [hire_date]) > '2005-10-17 00:00:00'";
sql(queryDateMinus)
- .withMssql()
- .ok(expectedDateMinus);
+ .withMssql().ok(expectedDateMinus);
String queryDateMinusNegate = "select * from \"employee\" "
+ "where \"hire_date\" -INTERVAL '-19800' SECOND(5)"
@@ -3590,8 +3503,7 @@
+ "WHERE DATEADD(SECOND, 19800, [hire_date]) > '2005-10-17 00:00:00'";
sql(queryDateMinusNegate)
- .withMssql()
- .ok(expectedDateMinusNegate);
+ .withMssql().ok(expectedDateMinusNegate);
}
@Test void testUnparseSqlIntervalQualifierBigQuery() {
@@ -3621,8 +3533,7 @@
String expected = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` , '%x%v-1'), '%x%v-%w')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withMysql()
- .ok(expected);
+ .withMysql().ok(expected);
}
@Test void testFloorMonth() {
@@ -3635,12 +3546,9 @@
final String expectedMysql = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-01')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withClickHouse()
- .ok(expectedClickHouse)
- .withMssql()
- .ok(expectedMssql)
- .withMysql()
- .ok(expectedMysql);
+ .withClickHouse().ok(expectedClickHouse)
+ .withMssql().ok(expectedMssql)
+ .withMysql().ok(expectedMysql);
}
@Test void testFloorMysqlHour() {
@@ -3648,8 +3556,7 @@
String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:00:00')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withMysql()
- .ok(expected);
+ .withMysql().ok(expected);
}
@Test void testFloorMysqlMinute() {
@@ -3657,8 +3564,7 @@
String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withMysql()
- .ok(expected);
+ .withMysql().ok(expected);
}
@Test void testFloorMysqlSecond() {
@@ -3666,8 +3572,7 @@
String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:%s')\n"
+ "FROM `foodmart`.`employee`";
sql(query)
- .withMysql()
- .ok(expected);
+ .withMysql().ok(expected);
}
/** Test case for
@@ -3694,16 +3599,11 @@
+ "FROM `foodmart`.`employee`\n"
+ "GROUP BY DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')";
sql(query)
- .withHsqldb()
- .ok(expected)
- .withClickHouse()
- .ok(expectedClickHouse)
- .withOracle()
- .ok(expectedOracle)
- .withPostgresql()
- .ok(expectedPostgresql)
- .withMysql()
- .ok(expectedMysql);
+ .withClickHouse().ok(expectedClickHouse)
+ .withHsqldb().ok(expected)
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql);
}
@Test void testSubstring() {
@@ -3722,23 +3622,16 @@
final String expectedMysql = "SELECT SUBSTRING(`brand_name` FROM 2)\n"
+ "FROM `foodmart`.`product`";
sql(query)
- .withClickHouse()
- .ok(expectedClickHouse)
- .withOracle()
- .ok(expectedOracle)
- .withPostgresql()
- .ok(expectedPostgresql)
- .withPresto()
- .ok(expectedPresto)
- .withSnowflake()
- .ok(expectedSnowflake)
- .withRedshift()
- .ok(expectedRedshift)
- .withMysql()
- .ok(expectedMysql)
+ .withClickHouse().ok(expectedClickHouse)
.withMssql()
// mssql does not support this syntax and so should fail
- .throws_("MSSQL SUBSTRING requires FROM and FOR arguments");
+ .throws_("MSSQL SUBSTRING requires FROM and FOR arguments")
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql)
+ .withPresto().ok(expectedPresto)
+ .withRedshift().ok(expectedRedshift)
+ .withSnowflake().ok(expectedSnowflake);
}
@Test void testSubstringWithFor() {
@@ -3759,22 +3652,14 @@
final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n"
+ "FROM [foodmart].[product]";
sql(query)
- .withClickHouse()
- .ok(expectedClickHouse)
- .withOracle()
- .ok(expectedOracle)
- .withPostgresql()
- .ok(expectedPostgresql)
- .withPresto()
- .ok(expectedPresto)
- .withSnowflake()
- .ok(expectedSnowflake)
- .withRedshift()
- .ok(expectedRedshift)
- .withMysql()
- .ok(expectedMysql)
- .withMssql()
- .ok(expectedMssql);
+ .withClickHouse().ok(expectedClickHouse)
+ .withMysql().ok(expectedMysql)
+ .withMssql().ok(expectedMssql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql)
+ .withPresto().ok(expectedPresto)
+ .withRedshift().ok(expectedRedshift)
+ .withSnowflake().ok(expectedSnowflake);
}
/** Test case for
@@ -4959,22 +4844,14 @@
final String expectedSnowflake = expectedPostgresql;
final String expectedRedshift = expectedPostgresql;
sql(sql)
- .withHsqldb()
- .ok(expectedHsqldb)
- .withMysql()
- .ok(expectedMysql)
- .withPostgresql()
- .ok(expectedPostgresql)
- .withOracle()
- .ok(expectedOracle)
- .withHive()
- .ok(expectedHive)
- .withBigQuery()
- .ok(expectedBigQuery)
- .withSnowflake()
- .ok(expectedSnowflake)
- .withRedshift()
- .ok(expectedRedshift);
+ .withBigQuery().ok(expectedBigQuery)
+ .withHive().ok(expectedHive)
+ .withHsqldb().ok(expectedHsqldb)
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql)
+ .withRedshift().ok(expectedRedshift)
+ .withSnowflake().ok(expectedSnowflake);
}
@Test void testValuesEmpty() {
@@ -4994,12 +4871,39 @@
+ "WHERE 1 = 0";
sql(sql)
.optimize(rules, null)
- .withMysql()
- .ok(expectedMysql)
- .withOracle()
- .ok(expectedOracle)
- .withPostgresql()
- .ok(expectedPostgresql);
+ .withMysql().ok(expectedMysql)
+ .withOracle().ok(expectedOracle)
+ .withPostgresql().ok(expectedPostgresql);
+ }
+
+ /** Tests SELECT without FROM clause; effectively the same as a VALUES
+ * query. */
+ @Test void testSelectWithoutFrom() {
+ final String query = "select 2 + 2";
+ final String expectedBigQuery = "SELECT 2 + 2";
+ final String expectedHive = expectedBigQuery;
+ final String expectedMysql = "SELECT 2 + 2";
+ final String expectedPostgresql = "SELECT 2 + 2\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ sql(query)
+ .withBigQuery().ok(expectedBigQuery)
+ .withHive().ok(expectedHive)
+ .withMysql().ok(expectedMysql)
+ .withPostgresql().ok(expectedPostgresql);
+ }
+
+ @Test void testSelectOne() {
+ final String query = "select 1";
+ final String expectedBigQuery = "SELECT 1";
+ final String expectedHive = expectedBigQuery;
+ final String expectedMysql = expectedBigQuery;
+ final String expectedPostgresql = "SELECT *\n"
+ + "FROM (VALUES (1)) AS \"t\" (\"EXPR$0\")";
+ sql(query)
+ .withBigQuery().ok(expectedBigQuery)
+ .withHive().ok(expectedHive)
+ .withMysql().ok(expectedMysql)
+ .withPostgresql().ok(expectedPostgresql);
}
/** Test case for
@@ -5059,15 +4963,6 @@
isLinux(expectedSql2));
}
-
- @Test void testSelectWithoutFromEmulationForHiveAndBigQuery() {
- String query = "select 2 + 2";
- final String expected = "SELECT 2 + 2";
- sql(query)
- .withHive().ok(expected)
- .withBigQuery().ok(expected);
- }
-
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2118">[CALCITE-2118]
* RelToSqlConverter should only generate "*" if field names match</a>. */
@@ -5384,8 +5279,7 @@
+ "LEFT JOIN \"tpch\".\"part\" ON \"t\".\"nation_name\" = \"part\".\"p_brand\"";
relFn(relFn)
.schema(CalciteAssert.SchemaSpec.TPCH)
- .withPostgresql()
- .ok(expectedPostgresql);
+ .withPostgresql().ok(expectedPostgresql);
}
/** A cartesian product is unparsed as a CROSS JOIN on Spark,
@@ -5528,10 +5422,8 @@
+ "GROUP BY CUBE(\"product_id\", \"product_class_id\")";
sql(query)
.ok(expected)
- .withSpark()
- .ok(expectedInSpark)
- .withPresto()
- .ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withSpark().ok(expectedInSpark);
}
@Test void testRollupWithGroupBy() {
@@ -5541,7 +5433,7 @@
final String expected = "SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_id\", \"product_class_id\")";
- final String expectedInSpark = "SELECT COUNT(*)\n"
+ final String expectedSpark = "SELECT COUNT(*)\n"
+ "FROM foodmart.product\n"
+ "GROUP BY product_id, product_class_id WITH ROLLUP";
final String expectedPresto = "SELECT COUNT(*)\n"
@@ -5549,10 +5441,8 @@
+ "GROUP BY ROLLUP(\"product_id\", \"product_class_id\")";
sql(query)
.ok(expected)
- .withSpark()
- .ok(expectedInSpark)
- .withPresto()
- .ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withSpark().ok(expectedSpark);
}
@Test void testJsonType() {
@@ -5601,8 +5491,12 @@
final String expected = "SELECT \"employee\".\"department_id\"\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "INNER JOIN (SELECT \"t1\".\"department_id\" \"department_id0\", MIN(\"t1\".\"department_id\") \"EXPR$0\"\n"
- + "FROM (SELECT NULL \"department_id\", NULL \"department_description\"\nFROM \"DUAL\"\nWHERE 1 = 0) \"t\",\n"
- + "(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\"\nGROUP BY \"department_id\") \"t1\"\n"
+ + "FROM (SELECT NULL \"department_id\", NULL \"department_description\"\n"
+ + "FROM \"DUAL\"\n"
+ + "WHERE 1 = 0) \"t\",\n"
+ + "(SELECT \"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "GROUP BY \"department_id\") \"t1\"\n"
+ "GROUP BY \"t1\".\"department_id\") \"t3\" ON \"employee\".\"department_id\" = \"t3\".\"department_id0\""
+ " AND \"employee\".\"department_id\" = \"t3\".\"EXPR$0\"";
sql(query).withOracle().ok(expected);
@@ -5616,12 +5510,14 @@
+ "FROM \"foodmart\".\"employee\"\n"
+ "INNER JOIN (SELECT \"t1\".\"department_id\" AS \"department_id0\","
+ " MIN(\"t1\".\"department_id\") AS \"EXPR$0\"\n"
- + "FROM (SELECT *\nFROM (VALUES (NULL, NULL))"
- + " AS \"t\" (\"department_id\", \"department_description\")"
- + "\nWHERE 1 = 0) AS \"t\","
- + "\n(SELECT \"department_id\"\nFROM \"foodmart\".\"employee\""
- + "\nGROUP BY \"department_id\") AS \"t1\""
- + "\nGROUP BY \"t1\".\"department_id\") AS \"t3\" "
+ + "FROM (SELECT *\n"
+ + "FROM (VALUES (NULL, NULL))"
+ + " AS \"t\" (\"department_id\", \"department_description\")\n"
+ + "WHERE 1 = 0) AS \"t\",\n"
+ + "(SELECT \"department_id\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "GROUP BY \"department_id\") AS \"t1\"\n"
+ + "GROUP BY \"t1\".\"department_id\") AS \"t3\" "
+ "ON \"employee\".\"department_id\" = \"t3\".\"department_id0\""
+ " AND \"employee\".\"department_id\" = \"t3\".\"EXPR$0\"";
sql(query).ok(expected);
@@ -5632,8 +5528,7 @@
String expected = "SELECT CAST(\"department_id\" AS NUMBER(5))\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withOracle()
- .ok(expected);
+ .withOracle().ok(expected);
}
@Test void testBigintOracle() {
@@ -5641,8 +5536,7 @@
String expected = "SELECT CAST(\"department_id\" AS NUMBER(19))\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withOracle()
- .ok(expected);
+ .withOracle().ok(expected);
}
@Test void testDoubleOracle() {
@@ -5650,8 +5544,7 @@
String expected = "SELECT CAST(\"department_id\" AS DOUBLE PRECISION)\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withOracle()
- .ok(expected);
+ .withOracle().ok(expected);
}
@Test void testRedshiftCastToTinyint() {
@@ -5659,8 +5552,7 @@
String expected = "SELECT CAST(\"department_id\" AS \"int2\")\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withRedshift()
- .ok(expected);
+ .withRedshift().ok(expected);
}
@Test void testRedshiftCastToDouble() {
@@ -5668,8 +5560,7 @@
String expected = "SELECT CAST(\"department_id\" AS \"float8\")\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withRedshift()
- .ok(expected);
+ .withRedshift().ok(expected);
}
@Test void testDateLiteralOracle() {
@@ -5677,8 +5568,7 @@
String expected = "SELECT TO_DATE('1978-05-02', 'YYYY-MM-DD')\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withOracle()
- .ok(expected);
+ .withOracle().ok(expected);
}
@Test void testTimestampLiteralOracle() {
@@ -5687,8 +5577,7 @@
+ " 'YYYY-MM-DD HH24:MI:SS.FF')\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withOracle()
- .ok(expected);
+ .withOracle().ok(expected);
}
@Test void testTimeLiteralOracle() {
@@ -5696,8 +5585,7 @@
String expected = "SELECT TO_TIME('12:34:56.78', 'HH24:MI:SS.FF')\n"
+ "FROM \"foodmart\".\"employee\"";
sql(query)
- .withOracle()
- .ok(expected);
+ .withOracle().ok(expected);
}
@Test void testSupportsDataType() {
@@ -5835,8 +5723,8 @@
final String query2 =
"SELECT CAST(1111111 AS DECIMAL(5,2)) AS \"num\" from \"product\"";
- final String expected2 =
- "SELECT CAST(1111111 AS DECIMAL(5, 2)) AS \"num\"\nFROM \"foodmart\".\"product\"";
+ final String expected2 = "SELECT CAST(1111111 AS DECIMAL(5, 2)) AS \"num\"\n"
+ + "FROM \"foodmart\".\"product\"";
sql(query2).ok(expected2);
}
@@ -5855,8 +5743,7 @@
+ "CAST('1996-01-01 ' || '00:00:00' AS TIMESTAMP)";
sql(query)
.ok(expected)
- .withBigQuery()
- .ok(expectedBiqquery);
+ .withBigQuery().ok(expectedBiqquery);
}
@Test void testDialectQuoteStringLiteral() {
@@ -5913,7 +5800,8 @@
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
+ "(2, 'Eric', 'Washington')";
final String expectedMysqlX = "INSERT INTO `SCOTT`.`DEPT`"
- + " (`DEPTNO`, `DNAME`, `LOC`)\nSELECT 1, 'Fred', 'San Francisco'\n"
+ + " (`DEPTNO`, `DNAME`, `LOC`)\n"
+ + "SELECT 1, 'Fred', 'San Francisco'\n"
+ "UNION ALL\n"
+ "SELECT 2, 'Eric', 'Washington'";
final String expectedOracle = "INSERT INTO \"SCOTT\".\"DEPT\""
@@ -6061,8 +5949,7 @@
sql(sql)
.parserConfig(parserConfig)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .withBigQuery()
- .ok(expected);
+ .withBigQuery().ok(expected);
}
/** Fluid interface to run tests. */
