/** * Tests that IN can be un-parsed. * * <p>This cannot be tested using "sql", because because Calcite's SQL parser * replaces INs with ORs or sub-queries. */ @Test public void testUnparseIn1() { final RelBuilder builder = relBuilder().scan("EMP"); final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), builder.literal(21)); final RelNode root = relBuilder().scan("EMP").filter(condition).build(); final String sql = toSql(root); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE \"DEPTNO\" IN (21)"; assertThat(sql, isLinux(expectedSql)); }
@Test public void testUnparseInStruct1() { final RelBuilder builder = relBuilder().scan("EMP"); final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.call(SqlStdOperatorTable.ROW, builder.field("DEPTNO"), builder.field("JOB")), builder.call(SqlStdOperatorTable.ROW, builder.literal(1), builder.literal("PRESIDENT"))); final RelNode root = relBuilder().scan("EMP").filter(condition).build(); final String sql = toSql(root); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'))"; assertThat(sql, isLinux(expectedSql)); }
@Test public void testUnparseInStruct2() { final RelBuilder builder = relBuilder().scan("EMP"); final RexNode condition = builder.call(SqlStdOperatorTable.IN, builder.call(SqlStdOperatorTable.ROW, builder.field("DEPTNO"), builder.field("JOB")), builder.call(SqlStdOperatorTable.ROW, builder.literal(1), builder.literal("PRESIDENT")), builder.call(SqlStdOperatorTable.ROW, builder.literal(2), builder.literal("PRESIDENT"))); final RelNode root = relBuilder().scan("EMP").filter(condition).build(); final String sql = toSql(root); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'), ROW(2, 'PRESIDENT'))"; assertThat(sql, isLinux(expectedSql)); }
@Test public void testUnparseIn2() { final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .filter( builder.call(SqlStdOperatorTable.IN, builder.field("DEPTNO"), builder.literal(20), builder.literal(21))) .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "WHERE \"DEPTNO\" IN (20, 21)"; assertThat(sql, isLinux(expectedSql)); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-2628">[CALCITE-2628] * JDBC adapter throws NullPointerException while generating GROUP BY query * for MySQL</a>. * * <p>MySQL does not support nested aggregates, so {@link RelToSqlConverter} * performs some extra checks, looking for aggregates in the input * sub-query, and these would fail with {@code NullPointerException} * and {@code ClassCastException} in some cases. */ @Test public void testNestedAggregatesMySqlTable() { final RelBuilder builder = relBuilder(); final RelNode root = builder .scan("EMP") .aggregate(builder.groupKey(), builder.count(false, "c", builder.field(3))) .build(); final SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect(); final String expectedSql = "SELECT COUNT(`MGR`) AS `c`\n" + "FROM `scott`.`EMP`"; assertThat(toSql(root, dialect), isLinux(expectedSql)); }
/** As {@link #testNestedAggregatesMySqlTable()}, but input is a sub-query, * not a table. */ @Test public void testNestedAggregatesMySqlStar() { final RelBuilder builder = relBuilder(); final RelNode root = builder .scan("EMP") .filter(builder.equals(builder.field("DEPTNO"), builder.literal(10))) .aggregate(builder.groupKey(), builder.count(false, "c", builder.field(3))) .build(); final SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect(); final String expectedSql = "SELECT COUNT(`MGR`) AS `c`\n" + "FROM `scott`.`EMP`\n" + "WHERE `DEPTNO` = 10"; assertThat(toSql(root, dialect), isLinux(expectedSql)); }
/** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1174">[CALCITE-1174] * When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)</a>. */ @Test public void testSum0BecomesCoalesce() { final RelBuilder builder = relBuilder(); final RelNode root = builder .scan("EMP") .aggregate(builder.groupKey(), builder.aggregateCall(SqlStdOperatorTable.SUM0, builder.field(3)) .as("s")) .build(); final String expectedMysql = "SELECT COALESCE(SUM(`MGR`), 0) AS `s`\n" + "FROM `scott`.`EMP`"; assertThat(toSql(root, SqlDialect.DatabaseProduct.MYSQL.getDialect()), isLinux(expectedMysql)); final String expectedPostgresql = "SELECT COALESCE(SUM(\"MGR\"), 0) AS \"s\"\n" + "FROM \"scott\".\"EMP\""; assertThat(toSql(root, SqlDialect.DatabaseProduct.POSTGRESQL.getDialect()), isLinux(expectedPostgresql)); }