/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to you under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.calcite.test;

import org.apache.calcite.config.CalciteConnectionConfigImpl;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.config.NullCollation;
import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.RelTrait;
import org.apache.calcite.plan.RelTraitDef;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.plan.hep.HepPlanner;
import org.apache.calcite.plan.hep.HepProgram;
import org.apache.calcite.plan.hep.HepProgramBuilder;
import org.apache.calcite.rel.RelCollationTraitDef;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.rel.RelShuttleImpl;
import org.apache.calcite.rel.externalize.RelDotWriter;
import org.apache.calcite.rel.externalize.RelXmlWriter;
import org.apache.calcite.rel.logical.LogicalCalc;
import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalSort;
import org.apache.calcite.rel.logical.LogicalTableModify;
import org.apache.calcite.rel.rules.CoreRules;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlExplainLevel;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlDelegatingConformance;
import org.apache.calcite.util.Bug;
import org.apache.calcite.util.TestUtil;
import org.apache.calcite.util.Util;

import com.google.common.collect.ImmutableList;

import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.function.Consumer;

import static org.hamcrest.CoreMatchers.notNullValue;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.core.Is.is;
import static org.hamcrest.core.Is.isA;

/**
 * Unit test for {@link org.apache.calcite.sql2rel.SqlToRelConverter}.
 */
class SqlToRelConverterTest extends SqlToRelTestBase {

  private static final SqlToRelFixture LOCAL_FIXTURE =
      SqlToRelFixture.DEFAULT
          .withDiffRepos(DiffRepository.lookup(SqlToRelConverterTest.class));

  @Override public SqlToRelFixture fixture() {
    return LOCAL_FIXTURE;
  }

  @Test void testDotLiteralAfterNestedRow() {
    final String sql = "select ((1,2),(3,4,5)).\"EXPR$1\".\"EXPR$2\" from emp";
    sql(sql).ok();
  }

  @Test void testDotLiteralAfterRow() {
    final String sql = "select row(1,2).\"EXPR$1\" from emp";
    sql(sql).ok();
  }

  @Test void testDotAfterParenthesizedIdentifier() {
    final String sql = "select (home_address).city from emp_address";
    sql(sql).ok();
  }

  @Test void testRowValueConstructorWithSubquery() {
    final String sql = "select ROW("
        + "(select deptno\n"
        + "from dept\n"
        + "where dept.deptno = emp.deptno), emp.ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testIntegerLiteral() {
    final String sql = "select 1 from emp";
    sql(sql).ok();
  }

  @Test void testIntervalLiteralYearToMonth() {
    final String sql = "select\n"
        + "  cast(empno as Integer) * (INTERVAL '1-1' YEAR TO MONTH)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testIntervalLiteralHourToMinute() {
    final String sql = "select\n"
        + " cast(empno as Integer) * (INTERVAL '1:1' HOUR TO MINUTE)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testIntervalExpression() {
    sql("select interval mgr hour as h from emp").ok();
  }

  @Test void testAliasList() {
    final String sql = "select a + b from (\n"
        + "  select deptno, 1 as uno, name from dept\n"
        + ") as d(a, b, c)\n"
        + "where c like 'X%'";
    sql(sql).ok();
  }

  @Test void testAliasList2() {
    final String sql = "select * from (\n"
        + "  select a, b, c from (values (1, 2, 3)) as t (c, b, a)\n"
        + ") join dept on dept.deptno = c\n"
        + "order by c + a";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2468">[CALCITE-2468]
   * struct type alias should not cause IndexOutOfBoundsException</a>.
   */
  @Test void testStructTypeAlias() {
    final String sql = "select t.r AS myRow\n"
        + "from (select row(row(1)) r from dept) t";
    sql(sql).ok();
  }

  @Test void testJoinUsingDynamicTable() {
    final String sql = "select * from SALES.NATION t1\n"
        + "join SALES.NATION t2\n"
        + "using (n_nationkey)";
    sql(sql).withDynamicTable().ok();
  }

  /**
   * Tests that AND(x, AND(y, z)) gets flattened to AND(x, y, z).
   */
  @Test void testMultiAnd() {
    final String sql = "select * from emp\n"
        + "where deptno < 10\n"
        + "and deptno > 5\n"
        + "and (deptno = 8 or empno < 100)";
    sql(sql).ok();
  }

  @Test void testJoinOn() {
    final String sql = "SELECT * FROM emp\n"
        + "JOIN dept on emp.deptno = dept.deptno";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-245">[CALCITE-245]
   * Off-by-one translation of ON clause of JOIN</a>. */
  @Test void testConditionOffByOne() {
    // Bug causes the plan to contain
    //   LogicalJoin(condition=[=($9, $9)], joinType=[inner])
    final String sql = "SELECT * FROM emp\n"
        + "JOIN dept on emp.deptno + 0 = dept.deptno";
    sql(sql).ok();
  }

  @Test void testConditionOffByOneReversed() {
    final String sql = "SELECT * FROM emp\n"
        + "JOIN dept on dept.deptno = emp.deptno + 0";
    sql(sql).ok();
  }

  @Test void testJoinOnExpression() {
    final String sql = "SELECT * FROM emp\n"
        + "JOIN dept on emp.deptno + 1 = dept.deptno - 2";
    sql(sql).ok();
  }

  @Test void testJoinOnIn() {
    final String sql = "select * from emp join dept\n"
        + " on emp.deptno = dept.deptno and emp.empno in (1, 3)";
    sql(sql).ok();
  }

  @Test void testJoinOnInSubQuery() {
    final String sql = "select * from emp left join dept\n"
        + "on emp.empno = 1\n"
        + "or dept.deptno in (select deptno from emp where empno > 5)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testJoinOnExists() {
    final String sql = "select * from emp left join dept\n"
        + "on emp.empno = 1\n"
        + "or exists (select deptno from emp where empno > dept.deptno + 5)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testJoinUsing() {
    sql("SELECT * FROM emp JOIN dept USING (deptno)").ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-74">[CALCITE-74]
   * JOIN ... USING fails in 3-way join with
   * UnsupportedOperationException</a>. */
  @Test void testJoinUsingThreeWay() {
    final String sql = "select *\n"
        + "from emp as e\n"
        + "join dept as d using (deptno)\n"
        + "join emp as e2 using (empno)";
    sql(sql).ok();
  }

  @Test void testJoinUsingCompound() {
    final String sql = "SELECT * FROM emp LEFT JOIN ("
        + "SELECT *, deptno * 5 as empno FROM dept) "
        + "USING (deptno,empno)";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-801">[CALCITE-801]
   * NullPointerException using USING on table alias with column aliases</a>. */
  @Test void testValuesUsing() {
    final String sql = "select d.deptno, min(e.empid) as empid\n"
        + "from (values (100, 'Bill', 1)) as e(empid, name, deptno)\n"
        + "join (values (1, 'LeaderShip')) as d(deptno, name)\n"
        + "  using (deptno)\n"
        + "group by d.deptno";
    sql(sql).ok();
  }

  @Test void testJoinNatural() {
    sql("SELECT * FROM emp NATURAL JOIN dept").ok();
  }

  @Test void testJoinNaturalNoCommonColumn() {
    final String sql = "SELECT *\n"
        + "FROM emp NATURAL JOIN (SELECT deptno AS foo, name FROM dept) AS d";
    sql(sql).ok();
  }

  @Test void testJoinNaturalMultipleCommonColumn() {
    final String sql = "SELECT *\n"
        + "FROM emp\n"
        + "NATURAL JOIN (SELECT deptno, name AS ename FROM dept) AS d";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3387">[CALCITE-3387]
   * Query with GROUP BY and JOIN ... USING wrongly fails with
   * "Column 'DEPTNO' is ambiguous"</a>. */
  @Test void testJoinUsingWithUnqualifiedCommonColumn() {
    final String sql = "SELECT deptno, name\n"
        + "FROM emp JOIN dept using (deptno)";
    sql(sql).ok();
  }

  /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
   * but with nested common column. */
  @Test void testJoinUsingWithUnqualifiedNestedCommonColumn() {
    final String sql =
        "select (coord).x from\n"
            + "customer.contact_peek t1\n"
            + "join customer.contact_peek t2\n"
            + "using (coord)";
    sql(sql).ok();
  }

  /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
   * but with aggregate. */
  @Test void testJoinUsingWithAggregate() {
    final String sql = "select deptno, count(*)\n"
        + "from emp\n"
        + "full join dept using (deptno)\n"
        + "group by deptno";
    sql(sql).ok();
  }

  /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
   * but with grouping sets. */
  @Test void testJoinUsingWithGroupingSets() {
    final String sql = "select deptno, grouping(deptno),\n"
        + "grouping(deptno, job), count(*)\n"
        + "from emp\n"
        + "join dept using (deptno)\n"
        + "group by grouping sets ((deptno), (deptno, job))";
    sql(sql).ok();
  }

  /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
   * but with multiple join. */
  @Test void testJoinUsingWithMultipleJoin() {
    final String sql = "SELECT deptno, ename\n"
        + "FROM emp "
        + "JOIN dept using (deptno)\n"
        + "JOIN (values ('Calcite', 200)) as s(ename, salary) using (ename)";
    sql(sql).ok();
  }

  @Test void testJoinWithUnion() {
    final String sql = "select grade\n"
        + "from (select empno from emp union select deptno from dept),\n"
        + "  salgrade";
    sql(sql).ok();
  }

  @Test void testGroup() {
    sql("select deptno from emp group by deptno").ok();
  }

  @Test void testGroupByAlias() {
    sql("select empno as d from emp group by d")
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testGroupByAliasOfSubExpressionsInProject() {
    final String sql = "select deptno+empno as d, deptno+empno+mgr\n"
        + "from emp group by d,mgr";
    sql(sql)
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testGroupByAliasEqualToColumnName() {
    sql("select empno, ename as deptno from emp group by empno, deptno")
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testGroupByOrdinal() {
    sql("select empno from emp group by 1")
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testGroupByContainsLiterals() {
    final String sql = "select count(*) from (\n"
        + "  select 1 from emp group by substring(ename from 2 for 3))";
    sql(sql)
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testAliasInHaving() {
    sql("select count(empno) as e from emp having e > 1")
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testGroupJustOneAgg() {
    // just one agg
    final String sql =
        "select deptno, sum(sal) as sum_sal from emp group by deptno";
    sql(sql).ok();
  }

  @Test void testGroupExpressionsInsideAndOut() {
    // Expressions inside and outside aggs. Common sub-expressions should be
    // eliminated: 'sal' always translates to expression #2.
    final String sql = "select\n"
        + "  deptno + 4, sum(sal), sum(3 + sal), 2 * count(sal)\n"
        + "from emp group by deptno";
    sql(sql).ok();
  }

  @Test void testAggregateNoGroup() {
    sql("select sum(deptno) from emp").ok();
  }

  @Test void testGroupEmpty() {
    sql("select sum(deptno) from emp group by ()").ok();
  }

  // Same effect as writing "GROUP BY deptno"
  @Test void testSingletonGroupingSet() {
    sql("select sum(sal) from emp group by grouping sets (deptno)").ok();
  }

  @Test void testGroupingSets() {
    final String sql = "select deptno, ename, sum(sal) from emp\n"
        + "group by grouping sets ((deptno), (ename, deptno))\n"
        + "order by 2";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2147">[CALCITE-2147]
   * Incorrect plan in with with ROLLUP inside GROUPING SETS</a>.
   *
   * <p>Equivalence example:
   * <blockquote>GROUP BY GROUPING SETS (ROLLUP(A, B), CUBE(C,D))</blockquote>
   * <p>is equal to
   * <blockquote>GROUP BY GROUPING SETS ((A,B), (A), (),
   * (C,D), (C), (D) )</blockquote>
   */
  @Test void testGroupingSetsWithRollup() {
    final String sql = "select deptno, ename, sum(sal) from emp\n"
        + "group by grouping sets ( rollup(deptno), (ename, deptno))\n"
        + "order by 2";
    sql(sql).ok();
  }

  @Test void testGroupingSetsWithCube() {
    final String sql = "select deptno, ename, sum(sal) from emp\n"
        + "group by grouping sets ( (deptno), CUBE(ename, deptno))\n"
        + "order by 2";
    sql(sql).ok();
  }

  @Test void testGroupingSetsWithRollupCube() {
    final String sql = "select deptno, ename, sum(sal) from emp\n"
        + "group by grouping sets ( CUBE(deptno), ROLLUP(ename, deptno))\n"
        + "order by 2";
    sql(sql).ok();
  }

  @Test void testGroupingSetsProduct() {
    // Example in SQL:2011:
    //   GROUP BY GROUPING SETS ((A, B), (C)), GROUPING SETS ((X, Y), ())
    // is transformed to
    //   GROUP BY GROUPING SETS ((A, B, X, Y), (A, B), (C, X, Y), (C))
    final String sql = "select 1\n"
        + "from (values (0, 1, 2, 3, 4)) as t(a, b, c, x, y)\n"
        + "group by grouping sets ((a, b), c), grouping sets ((x, y), ())";
    sql(sql).ok();
  }

  /** When the GROUPING function occurs with GROUP BY (effectively just one
   * grouping set), we can translate it directly to 1. */
  @Test void testGroupingFunctionWithGroupBy() {
    final String sql = "select\n"
        + "  deptno, grouping(deptno), count(*), grouping(empno)\n"
        + "from emp\n"
        + "group by empno, deptno\n"
        + "order by 2";
    sql(sql).ok();
  }

  @Test void testGroupingFunction() {
    final String sql = "select\n"
        + "  deptno, grouping(deptno), count(*), grouping(empno)\n"
        + "from emp\n"
        + "group by rollup(empno, deptno)";
    sql(sql).ok();
  }

  /**
   * GROUP BY with duplicates.
   *
   * <p>From SQL spec:
   * <blockquote>NOTE 190 &mdash; That is, a simple <em>group by clause</em>
   * that is not primitive may be transformed into a primitive <em>group by
   * clause</em> by deleting all parentheses, and deleting extra commas as
   * necessary for correct syntax. If there are no grouping columns at all (for
   * example, GROUP BY (), ()), this is transformed to the canonical form GROUP
   * BY ().
   * </blockquote> */
  // Same effect as writing "GROUP BY ()"
  @Test void testGroupByWithDuplicates() {
    sql("select sum(sal) from emp group by (), ()").ok();
  }

  /** GROUP BY with duplicate (and heavily nested) GROUPING SETS. */
  @Test void testDuplicateGroupingSets() {
    final String sql = "select sum(sal) from emp\n"
        + "group by sal,\n"
        + "  grouping sets (deptno,\n"
        + "    grouping sets ((deptno, ename), ename),\n"
        + "      (ename)),\n"
        + "  ()";
    sql(sql).ok();
  }

  @Test void testGroupingSetsCartesianProduct() {
    // Equivalent to (a, c), (a, d), (b, c), (b, d)
    final String sql = "select 1\n"
        + "from (values (1, 2, 3, 4)) as t(a, b, c, d)\n"
        + "group by grouping sets (a, b), grouping sets (c, d)";
    sql(sql).ok();
  }

  @Test void testGroupingSetsCartesianProduct2() {
    final String sql = "select 1\n"
        + "from (values (1, 2, 3, 4)) as t(a, b, c, d)\n"
        + "group by grouping sets (a, (a, b)), grouping sets (c), d";
    sql(sql).ok();
  }

  @Test void testRollupSimple() {
    // a is nullable so is translated as just "a"
    // b is not null, so is represented as 0 inside Aggregate, then
    // using "CASE WHEN i$b THEN NULL ELSE b END"
    final String sql = "select a, b, count(*) as c\n"
        + "from (values (cast(null as integer), 2)) as t(a, b)\n"
        + "group by rollup(a, b)";
    sql(sql).ok();
  }

  @Test void testRollup() {
    // Equivalent to {(a, b), (a), ()}  * {(c, d), (c), ()}
    final String sql = "select 1\n"
        + "from (values (1, 2, 3, 4)) as t(a, b, c, d)\n"
        + "group by rollup(a, b), rollup(c, d)";
    sql(sql).ok();
  }

  @Test void testRollupTuples() {
    // rollup(b, (a, d)) is (b, a, d), (b), ()
    final String sql = "select 1\n"
        + "from (values (1, 2, 3, 4)) as t(a, b, c, d)\n"
        + "group by rollup(b, (a, d))";
    sql(sql).ok();
  }

  @Test void testCube() {
    // cube(a, b) is {(a, b), (a), (b), ()}
    final String sql = "select 1\n"
        + "from (values (1, 2, 3, 4)) as t(a, b, c, d)\n"
        + "group by cube(a, b)";
    sql(sql).ok();
  }

  @Test void testGroupingSetsRepeated() {
    final String sql = "select deptno, group_id()\n"
        + "from emp\n"
        + "group by grouping sets (deptno, (), job, (deptno, job), deptno,\n"
        + "  job, deptno)";
    sql(sql).ok();
  }

  /** As {@link #testGroupingSetsRepeated()} but with no {@code GROUP_ID}
   * function. (We still need the plan to contain a Union.) */
  @Test void testGroupingSetsRepeatedNoGroupId() {
    final String sql = "select deptno, job\n"
        + "from emp\n"
        + "group by grouping sets (deptno, (), job, (deptno, job), deptno,\n"
        + "  job, deptno)";
    sql(sql).ok();
  }

  /** As {@link #testGroupingSetsRepeated()} but grouping sets are distinct.
   * The {@code GROUP_ID} is replaced by 0.*/
  @Test void testGroupingSetsWithGroupId() {
    final String sql = "select deptno, group_id()\n"
        + "from emp\n"
        + "group by grouping sets (deptno, (), job)";
    sql(sql).ok();
  }

  @Test void testGroupingSetsWith() {
    final String sql = "with t(a, b, c, d) as (values (1, 2, 3, 4))\n"
        + "select 1 from t\n"
        + "group by rollup(a, b), rollup(c, d)";
    sql(sql).ok();
  }

  @Test void testHaving() {
    // empty group-by clause, having
    final String sql = "select sum(sal + sal) from emp having sum(sal) > 10";
    sql(sql).ok();
  }

  @Test void testGroupBug281() {
    // Dtbug 281 gives:
    //   Internal error:
    //   Type 'RecordType(VARCHAR(128) $f0)' has no field 'NAME'
    final String sql =
        "select name from (select name from dept group by name)";
    sql(sql).ok();
  }

  @Test void testGroupBug281b() {
    // Try to confuse it with spurious columns.
    final String sql = "select name, foo from (\n"
        + "select deptno, name, count(deptno) as foo\n"
        + "from dept\n"
        + "group by name, deptno, name)";
    sql(sql).ok();
  }

  @Test void testGroupByExpression() {
    // This used to cause an infinite loop,
    // SqlValidatorImpl.getValidatedNodeType
    // calling getValidatedNodeTypeIfKnown
    // calling getValidatedNodeType.
    final String sql = "select count(*)\n"
        + "from emp\n"
        + "group by substring(ename FROM 1 FOR 1)";
    sql(sql).ok();
  }

  @Test void testAggDistinct() {
    final String sql = "select deptno, sum(sal), sum(distinct sal), count(*)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testAggFilter() {
    final String sql = "select\n"
        + "  deptno, sum(sal * 2) filter (where empno < 10), count(*)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testAggFilterWithIn() {
    final String sql = "select\n"
        + "  deptno, sum(sal * 2) filter (where empno not in (1, 2)), count(*)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testFakeStar() {
    sql("SELECT * FROM (VALUES (0, 0)) AS T(A, \"*\")").ok();
  }

  @Test void testSelectNull() {
    sql("select null from emp").ok();
  }

  @Test void testSelectNullWithAlias() {
    sql("select null as dummy from emp").ok();
  }

  @Test void testSelectNullWithCast() {
    sql("select cast(null as timestamp) dummy from emp").ok();
  }

  @Test void testSelectDistinct() {
    sql("select distinct sal + 5 from emp").ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-476">[CALCITE-476]
   * DISTINCT flag in windowed aggregates</a>. */
  @Test void testSelectOverDistinct() {
    // Checks to see if <aggregate>(DISTINCT x) is set and preserved
    // as a flag for the aggregate call.
    final String sql = "select SUM(DISTINCT deptno)\n"
        + "over (ORDER BY empno ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)\n"
        + "from emp\n";
    sql(sql).ok();
  }

  /** As {@link #testSelectOverDistinct()} but for streaming queries. */
  @Test void testSelectStreamPartitionDistinct() {
    final String sql = "select stream\n"
        + "  count(distinct orderId) over (partition by productId\n"
        + "    order by rowtime\n"
        + "    range interval '1' second preceding) as c,\n"
        + "  count(distinct orderId) over w as c2,\n"
        + "  count(orderId) over w as c3\n"
        + "from orders\n"
        + "window w as (partition by productId)";
    sql(sql).ok();
  }

  @Test void testSelectDistinctGroup() {
    sql("select distinct sum(sal) from emp group by deptno").ok();
  }

  /**
   * Tests that if the clause of SELECT DISTINCT contains duplicate
   * expressions, they are only aggregated once.
   */
  @Test void testSelectDistinctDup() {
    final String sql =
        "select distinct sal + 5, deptno, sal + 5 from emp where deptno < 10";
    sql(sql).ok();
  }

  @Test void testSelectWithoutFrom() {
    final String sql = "select 2+2";
    sql(sql).ok();
  }

  /** Tests referencing columns from a sub-query that has duplicate column
   * names. I think the standard says that this is illegal. We roll with it,
   * and rename the second column to "e0". */
  @Test void testDuplicateColumnsInSubQuery() {
    String sql = "select \"e\" from (\n"
        + "select empno as \"e\", deptno as d, 1 as \"e0\" from EMP)";
    sql(sql).ok();
  }

  @Test void testOrder() {
    final String sql = "select empno from emp order by empno";
    sql(sql).ok();

    // duplicate field is dropped, so plan is same
    final String sql2 = "select empno from emp order by empno, empno asc";
    sql(sql2).ok();

    // ditto
    final String sql3 = "select empno from emp order by empno, empno desc";
    sql(sql3).ok();
  }

  /** Tests that if a column occurs twice in ORDER BY, only the first key is
   * kept. */
  @Test void testOrderBasedRepeatFields() {
    final String sql = "select empno from emp order by empno DESC, empno ASC";
    sql(sql).ok();
  }

  @Test void testOrderDescNullsLast() {
    final String sql = "select empno from emp order by empno desc nulls last";
    sql(sql).ok();
  }

  @Test void testOrderByOrdinalDesc() {
    // This test requires a conformance that sorts by ordinal
    final SqlToRelFixture f = fixture()
        .ensuring(f2 -> f2.getConformance().isSortByOrdinal(),
            f2 -> f2.withConformance(SqlConformanceEnum.ORACLE_10));
    final String sql =
        "select empno + 1, deptno, empno from emp order by 2 desc";
    f.withSql(sql).ok();

    // ordinals rounded down, so 2.5 should have the same effect as 2, and
    // generate identical plan
    final String sql2 =
        "select empno + 1, deptno, empno from emp order by 2.5 desc";
    f.withSql(sql2).ok();
  }

  @Test void testOrderDistinct() {
    // The relexp aggregates by 3 expressions - the 2 select expressions
    // plus the one to sort on. A little inefficient, but acceptable.
    final String sql = "select distinct empno, deptno + 1\n"
            + "from emp order by deptno + 1 + empno";
    sql(sql).ok();
  }

  @Test void testOrderByNegativeOrdinal() {
    // Regardless of whether sort-by-ordinals is enabled, negative ordinals
    // are treated like ordinary numbers.
    final String sql =
        "select empno + 1, deptno, empno from emp order by -1 desc";
    sql(sql).ok();
  }

  @Test void testOrderByOrdinalInExpr() {
    // Regardless of whether sort-by-ordinals is enabled, ordinals
    // inside expressions are treated like integers.
    final String sql =
        "select empno + 1, deptno, empno from emp order by 1 + 2 desc";
    sql(sql).ok();
  }

  @Test void testOrderByIdenticalExpr() {
    // Expression in ORDER BY clause is identical to expression in SELECT
    // clause, so plan should not need an extra project.
    final String sql =
        "select empno + 1 from emp order by deptno asc, empno + 1 desc";
    sql(sql).ok();
  }

  @Test void testOrderByAlias() {
    final String sql =
        "select empno + 1 as x, empno - 2 as y from emp order by y";
    sql(sql).ok();
  }

  @Test void testOrderByAliasInExpr() {
    final String sql = "select empno + 1 as x, empno - 2 as y\n"
        + "from emp order by y + 3";
    sql(sql).ok();
  }

  @Test void testOrderByAliasOverrides() {
    // This test requires a conformance that sorts by alias
    final SqlToRelFixture f = fixture()
        .ensuring(f2 -> f2.getConformance().isSortByAlias(),
            f2 -> f2.withConformance(SqlConformanceEnum.ORACLE_10));

    // plan should contain '(empno + 1) + 3'
    final String sql = "select empno + 1 as empno, empno - 2 as y\n"
        + "from emp order by empno + 3";
    f.withSql(sql).ok();
  }

  @Test void testOrderByAliasDoesNotOverride() {
    // This test requires a conformance that does not sort by alias
    final SqlToRelFixture f = fixture()
        .ensuring(f2 -> !f2.getConformance().isSortByAlias(),
            f2 -> f2.withConformance(SqlConformanceEnum.PRAGMATIC_2003));

    // plan should contain 'empno + 3', not '(empno + 1) + 3'
    final String sql = "select empno + 1 as empno, empno - 2 as y\n"
        + "from emp order by empno + 3";
    f.withSql(sql).ok();
  }

  @Test void testOrderBySameExpr() {
    final String sql = "select empno from emp, dept\n"
        + "order by sal + empno desc, sal * empno, sal + empno desc";
    sql(sql).ok();
  }

  @Test void testOrderUnion() {
    final String sql = "select empno, sal from emp\n"
        + "union all\n"
        + "select deptno, deptno from dept\n"
        + "order by sal desc, empno asc";
    sql(sql).ok();
  }

  @Test void testOrderUnionOrdinal() {
    // This test requires a conformance that sorts by ordinal
    final SqlToRelFixture f = fixture()
        .ensuring(f2 -> f2.getConformance().isSortByOrdinal(),
            f2 -> f2.withConformance(SqlConformanceEnum.ORACLE_10));
    final String sql = "select empno, sal from emp\n"
        + "union all\n"
        + "select deptno, deptno from dept\n"
        + "order by 2";
    f.withSql(sql).ok();
  }

  @Test void testOrderUnionExprs() {
    final String sql = "select empno, sal from emp\n"
        + "union all\n"
        + "select deptno, deptno from dept\n"
        + "order by empno * sal + 2";
    sql(sql).ok();
  }

  @Test void testOrderOffsetFetch() {
    final String sql = "select empno from emp\n"
        + "order by empno offset 10 rows fetch next 5 rows only";
    sql(sql).ok();
  }

  @Test void testOrderOffsetFetchWithDynamicParameter() {
    final String sql = "select empno from emp\n"
        + "order by empno offset ? rows fetch next ? rows only";
    sql(sql).ok();
  }

  @Test void testOffsetFetch() {
    final String sql = "select empno from emp\n"
        + "offset 10 rows fetch next 5 rows only";
    sql(sql).ok();
  }

  @Test void testOffsetFetchWithDynamicParameter() {
    final String sql = "select empno from emp\n"
        + "offset ? rows fetch next ? rows only";
    sql(sql).ok();
  }

  @Test void testOffset() {
    final String sql = "select empno from emp offset 10 rows";
    sql(sql).ok();
  }

  @Test void testOffsetWithDynamicParameter() {
    final String sql = "select empno from emp offset ? rows";
    sql(sql).ok();
  }

  @Test void testFetch() {
    final String sql = "select empno from emp fetch next 5 rows only";
    sql(sql).ok();
  }

  @Test void testFetchWithDynamicParameter() {
    final String sql = "select empno from emp fetch next ? rows only";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-439">[CALCITE-439]
   * SqlValidatorUtil.uniquify() may not terminate under some conditions</a>. */
  @Test void testGroupAlias() {
    final String sql = "select \"$f2\", max(x), max(x + 1)\n"
        + "from (values (1, 2)) as t(\"$f2\", x)\n"
        + "group by \"$f2\"";
    sql(sql).ok();
  }

  @Test void testOrderGroup() {
    final String sql = "select deptno, count(*)\n"
        + "from emp\n"
        + "group by deptno\n"
        + "order by deptno * sum(sal) desc, min(empno)";
    sql(sql).ok();
  }

  @Test void testCountNoGroup() {
    final String sql = "select count(*), sum(sal)\n"
        + "from emp\n"
        + "where empno > 10";
    sql(sql).ok();
  }

  @Test void testWith() {
    final String sql = "with emp2 as (select * from emp)\n"
        + "select * from emp2";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-309">[CALCITE-309]
   * WITH ... ORDER BY query gives AssertionError</a>. */
  @Test void testWithOrder() {
    final String sql = "with emp2 as (select * from emp)\n"
        + "select * from emp2 order by deptno";
    sql(sql).ok();
  }

  @Test void testWithUnionOrder() {
    final String sql = "with emp2 as (select empno, deptno as x from emp)\n"
        + "select * from emp2\n"
        + "union all\n"
        + "select * from emp2\n"
        + "order by empno + x";
    sql(sql).ok();
  }

  @Test void testWithUnion() {
    final String sql = "with emp2 as (select * from emp where deptno > 10)\n"
        + "select empno from emp2 where deptno < 30\n"
        + "union all\n"
        + "select deptno from emp";
    sql(sql).ok();
  }

  @Test void testWithAlias() {
    final String sql = "with w(x, y) as\n"
        + "  (select * from dept where deptno > 10)\n"
        + "select x from w where x < 30 union all select deptno from dept";
    sql(sql).ok();
  }

  @Test void testWithInsideWhereExists() {
    final String sql = "select * from emp\n"
        + "where exists (\n"
        + "  with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
        + "  select 1 from dept2 where deptno <= emp.deptno)";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testWithInsideWhereExistsRex() {
    final String sql = "select * from emp\n"
        + "where exists (\n"
        + "  with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
        + "  select 1 from dept2 where deptno <= emp.deptno)";
    sql(sql).withDecorrelate(false).withExpand(false).ok();
  }

  @Test void testWithInsideWhereExistsDecorrelate() {
    final String sql = "select * from emp\n"
        + "where exists (\n"
        + "  with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
        + "  select 1 from dept2 where deptno <= emp.deptno)";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testWithInsideWhereExistsDecorrelateRex() {
    final String sql = "select * from emp\n"
        + "where exists (\n"
        + "  with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
        + "  select 1 from dept2 where deptno <= emp.deptno)";
    sql(sql).withDecorrelate(true).withExpand(false).ok();
  }

  @Test void testWithInsideScalarSubQuery() {
    final String sql = "select (\n"
        + " with dept2 as (select * from dept where deptno > 10)"
        + " select count(*) from dept2) as c\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testWithInsideScalarSubQueryRex() {
    final String sql = "select (\n"
        + " with dept2 as (select * from dept where deptno > 10)"
        + " select count(*) from dept2) as c\n"
        + "from emp";
    sql(sql).withExpand(false).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-365">[CALCITE-365]
   * AssertionError while translating query with WITH and correlated
   * sub-query</a>. */
  @Test void testWithExists() {
    final String sql = "with t (a, b) as (select * from (values (1, 2)))\n"
        + "select * from t where exists (\n"
        + "  select 1 from emp where deptno = t.a)";
    sql(sql).ok();
  }

  @Test void testTableSubset() {
    final String sql = "select deptno, name from dept";
    sql(sql).ok();
  }

  @Test void testTableExpression() {
    final String sql = "select deptno + deptno from dept";
    sql(sql).ok();
  }

  @Test void testTableExtend() {
    final String sql = "select * from dept extend (x varchar(5) not null)";
    sql(sql).ok();
  }

  @Test void testTableExtendSubset() {
    final String sql = "select deptno, x from dept extend (x int)";
    sql(sql).ok();
  }

  @Test void testTableExtendExpression() {
    final String sql = "select deptno + x from dept extend (x int not null)";
    sql(sql).ok();
  }

  @Test void testModifiableViewExtend() {
    final String sql = "select *\n"
        + "from EMP_MODIFIABLEVIEW extend (x varchar(5) not null)";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testModifiableViewExtendSubset() {
    final String sql = "select x, empno\n"
        + "from EMP_MODIFIABLEVIEW extend (x varchar(5) not null)";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testModifiableViewExtendExpression() {
    final String sql = "select empno + x\n"
        + "from EMP_MODIFIABLEVIEW extend (x int not null)";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testSelectViewExtendedColumnCollision() {
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR\n"
        + " from EMP_MODIFIABLEVIEW3\n"
        + " where SAL = 20").withExtendedTester().ok();
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR\n"
        + " from EMP_MODIFIABLEVIEW3 extend (SAL int)\n"
        + " where SAL = 20").withExtendedTester().ok();
  }

  @Test void testSelectViewExtendedColumnCaseSensitiveCollision() {
    sql("select ENAME, EMPNO, JOB, SLACKER, \"sal\", HIREDATE, MGR\n"
        + " from EMP_MODIFIABLEVIEW3 extend (\"sal\" boolean)\n"
        + " where \"sal\" = true").withExtendedTester().ok();
  }

  @Test void testSelectViewExtendedColumnExtendedCollision() {
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, EXTRA\n"
        + " from EMP_MODIFIABLEVIEW2\n"
        + " where SAL = 20").withExtendedTester().ok();
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, EXTRA\n"
        + " from EMP_MODIFIABLEVIEW2 extend (EXTRA boolean)\n"
        + " where SAL = 20").withExtendedTester().ok();
  }

  @Test void testSelectViewExtendedColumnCaseSensitiveExtendedCollision() {
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, \"extra\"\n"
        + " from EMP_MODIFIABLEVIEW2 extend (\"extra\" boolean)\n"
        + " where \"extra\" = false").withExtendedTester().ok();
  }

  @Test void testSelectViewExtendedColumnUnderlyingCollision() {
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, COMM\n"
        + " from EMP_MODIFIABLEVIEW3 extend (COMM int)\n"
        + " where SAL = 20").withExtendedTester().ok();
  }

  @Test void testSelectViewExtendedColumnCaseSensitiveUnderlyingCollision() {
    sql("select ENAME, EMPNO, JOB, SLACKER, SAL, HIREDATE, MGR, \"comm\"\n"
        + " from EMP_MODIFIABLEVIEW3 extend (\"comm\" int)\n"
        + " where \"comm\" = 20").withExtendedTester().ok();
  }

  @Test void testUpdateExtendedColumnCollision() {
    sql("update empdefaults(empno INTEGER NOT NULL, deptno INTEGER)"
        + " set deptno = 1, empno = 20, ename = 'Bob'"
        + " where deptno = 10").ok();
  }

  @Test void testUpdateExtendedColumnCaseSensitiveCollision() {
    sql("update empdefaults(\"slacker\" INTEGER, deptno INTEGER)"
        + " set deptno = 1, \"slacker\" = 100"
        + " where ename = 'Bob'").ok();
  }

  @Test void testUpdateExtendedColumnModifiableViewCollision() {
    sql("update EMP_MODIFIABLEVIEW3(empno INTEGER NOT NULL, deptno INTEGER)"
        + " set deptno = 20, empno = 20, ename = 'Bob'"
        + " where empno = 10").withExtendedTester().ok();
  }

  @Test void testUpdateExtendedColumnModifiableViewCaseSensitiveCollision() {
    sql("update EMP_MODIFIABLEVIEW2(\"slacker\" INTEGER, deptno INTEGER)"
        + " set deptno = 20, \"slacker\" = 100"
        + " where ename = 'Bob'").withExtendedTester().ok();
  }

  @Test void testUpdateExtendedColumnModifiableViewExtendedCollision() {
    sql("update EMP_MODIFIABLEVIEW2(\"slacker\" INTEGER, extra BOOLEAN)"
        + " set deptno = 20, \"slacker\" = 100, extra = true"
        + " where ename = 'Bob'").withExtendedTester().ok();
  }

  @Test void testUpdateExtendedColumnModifiableViewExtendedCaseSensitiveCollision() {
    sql("update EMP_MODIFIABLEVIEW2(\"extra\" INTEGER, extra BOOLEAN)"
        + " set deptno = 20, \"extra\" = 100, extra = true"
        + " where ename = 'Bob'").withExtendedTester().ok();
  }

  @Test void testUpdateExtendedColumnModifiableViewUnderlyingCollision() {
    sql("update EMP_MODIFIABLEVIEW3(extra BOOLEAN, comm INTEGER)"
        + " set empno = 20, comm = 123, extra = true"
        + " where ename = 'Bob'").withExtendedTester().ok();
  }

  @Test void testSelectModifiableViewConstraint() {
    final String sql = "select deptno from EMP_MODIFIABLEVIEW2\n"
        + "where deptno = ?";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testModifiableViewDdlExtend() {
    final String sql = "select extra from EMP_MODIFIABLEVIEW2";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testExplicitTable() {
    sql("table emp").ok();
  }

  @Test void testCollectionTable() {
    sql("select * from table(ramp(3))").ok();
  }

  @Test void testCollectionTableWithLateral() {
    sql("select * from dept, lateral table(ramp(dept.deptno))").ok();
  }

  @Test void testCollectionTableWithLateral2() {
    sql("select * from dept, lateral table(ramp(deptno))").ok();
  }

  @Test void testSnapshotOnTemporalTable1() {
    final String sql = "select * from products_temporal "
        + "for system_time as of TIMESTAMP '2011-01-02 00:00:00'";
    sql(sql).ok();
  }

  @Test void testSnapshotOnTemporalTable2() {
    // Test temporal table with virtual columns.
    final String sql = "select * from VIRTUALCOLUMNS.VC_T1 "
        + "for system_time as of TIMESTAMP '2011-01-02 00:00:00'";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testJoinTemporalTableOnSpecificTime1() {
    final String sql = "select stream *\n"
        + "from orders,\n"
        + "  products_temporal for system_time as of\n"
        + "    TIMESTAMP '2011-01-02 00:00:00'";
    sql(sql).ok();
  }

  @Test void testJoinTemporalTableOnSpecificTime2() {
    // Test temporal table with virtual columns.
    final String sql = "select stream *\n"
        + "from orders,\n"
        + "  VIRTUALCOLUMNS.VC_T1 for system_time as of\n"
        + "    TIMESTAMP '2011-01-02 00:00:00'";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testJoinTemporalTableOnColumnReference1() {
    final String sql = "select stream *\n"
        + "from orders\n"
        + "join products_temporal for system_time as of orders.rowtime\n"
        + "on orders.productid = products_temporal.productid";
    sql(sql).ok();
  }

  @Test void testJoinTemporalTableOnColumnReference2() {
    // Test temporal table with virtual columns.
    final String sql = "select stream *\n"
        + "from orders\n"
        + "join VIRTUALCOLUMNS.VC_T1 for system_time as of orders.rowtime\n"
        + "on orders.productid = VIRTUALCOLUMNS.VC_T1.a";
    sql(sql).withExtendedTester().ok();
  }

  /**
   * Lateral join with temporal table, both snapshot's input scan
   * and snapshot's period reference outer columns. Should not
   * decorrelate join.
   */
  @Test void testCrossJoinTemporalTable1() {
    final String sql = "select stream *\n"
        + "from orders\n"
        + "cross join lateral (\n"
        + "  select * from products_temporal for system_time\n"
        + "  as of orders.rowtime\n"
        + "  where orders.productid = products_temporal.productid)\n";
    sql(sql).ok();
  }

  /**
   * Lateral join with temporal table, snapshot's input scan
   * reference outer columns, but snapshot's period is static.
   * Should be able to decorrelate join.
   */
  @Test void testCrossJoinTemporalTable2() {
    final String sql = "select stream *\n"
        + "from orders\n"
        + "cross join lateral (\n"
        + "  select * from products_temporal for system_time\n"
        + "  as of TIMESTAMP '2011-01-02 00:00:00'\n"
        + "  where orders.productid = products_temporal.productid)\n";
    sql(sql).ok();
  }

  /**
   * Lateral join with temporal table, snapshot's period reference
   * outer columns. Should not decorrelate join.
   */
  @Test void testCrossJoinTemporalTable3() {
    final String sql = "select stream *\n"
        + "from orders\n"
        + "cross join lateral (\n"
        + "  select * from products_temporal for system_time\n"
        + "  as of orders.rowtime\n"
        + "  where products_temporal.productid > 1)\n";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1732">[CALCITE-1732]
   * IndexOutOfBoundsException when using LATERAL TABLE with more than one
   * field</a>. */
  @Test void testCollectionTableWithLateral3() {
    sql("select * from dept, lateral table(DEDUP(dept.deptno, dept.name))").ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4673">[CALCITE-4673]
   * If arguments to a table function use correlation variables,
   * SqlToRelConverter should eliminate duplicate variables</a>.
   *
   * <p>The {@code LogicalTableFunctionScan} should have two identical
   * correlation variables like "{@code $cor0.DEPTNO}", but before this bug was
   * fixed, we have different ones: "{@code $cor0.DEPTNO}" and
   * "{@code $cor1.DEPTNO}". */
  @Test void testCorrelationCollectionTableInSubQuery() {
    Consumer<String> fn = sql -> {
      sql(sql).withExpand(true).withDecorrelate(true)
          .convertsTo("${planExpanded}");
      sql(sql).withExpand(false).withDecorrelate(false)
          .convertsTo("${planNotExpanded}");
    };
    fn.accept("select e.deptno,\n"
        + "  (select * from lateral table(DEDUP(e.deptno, e.deptno)))\n"
        + "from emp e");
    // same effect without LATERAL
    fn.accept("select e.deptno,\n"
        + "  (select * from table(DEDUP(e.deptno, e.deptno)))\n"
        + "from emp e");
  }

  @Test void testCorrelatedScalarSubQueryInSelectList() {
    Consumer<String> fn = sql -> {
      sql(sql).withExpand(true).withDecorrelate(false)
          .convertsTo("${planExpanded}");
      sql(sql).withExpand(false).withDecorrelate(false)
          .convertsTo("${planNotExpanded}");
    };
    fn.accept("select deptno,\n"
        + "  (select min(1) from emp where empno > d.deptno) as i0,\n"
        + "  (select min(0) from emp where deptno = d.deptno "
        + "                            and ename = 'SMITH'"
        + "                            and d.deptno > 0) as i1\n"
        + "from dept as d");
  }

  @Test void testCorrelationLateralSubQuery() {
    String sql = "SELECT deptno, ename\n"
        + "FROM\n"
        + "  (SELECT DISTINCT deptno FROM emp) t1,\n"
        + "  LATERAL (\n"
        + "    SELECT ename, sal\n"
        + "    FROM emp\n"
        + "    WHERE deptno IN (t1.deptno, t1.deptno)\n"
        + "    AND   deptno = t1.deptno\n"
        + "    ORDER BY sal\n"
        + "    DESC LIMIT 3)";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  @Test void testCorrelationExistsWithSubQuery() {
    String sql = "select emp.deptno, dept.deptno\n"
        + "from emp, dept\n"
        + "where exists (select * from emp\n"
        + "  where emp.deptno = dept.deptno\n"
        + "  and emp.deptno = dept.deptno\n"
        + "  and emp.deptno in (dept.deptno, dept.deptno))";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  @Test void testCorrelationInWithSubQuery() {
    String sql = "select deptno\n"
        + "from emp\n"
        + "where deptno in (select deptno\n"
        + "    from dept\n"
        + "    where emp.deptno = dept.deptno\n"
        + "    and emp.deptno = dept.deptno)";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3847">[CALCITE-3847]
   * Decorrelation for join with lateral table outputs wrong plan if the join
   * condition contains correlation variables</a>. */
  @Test void testJoinLateralTableWithConditionCorrelated() {
    final String sql = "select deptno, r.num from dept join\n"
        + " lateral table(ramp(dept.deptno)) as r(num)\n"
        + " on deptno=num";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4206">[CALCITE-4206]
   * RelDecorrelator outputs wrong plan for correlate sort with fetch
   * limit</a>. */
  @Test void testCorrelateSortWithLimit() {
    final String sql = "SELECT deptno, ename\n"
        + "FROM\n"
        + "  (SELECT DISTINCT deptno FROM emp) t1,\n"
        + "  LATERAL (\n"
        + "    SELECT ename, sal\n"
        + "    FROM emp\n"
        + "    WHERE deptno = t1.deptno\n"
        + "    ORDER BY sal\n"
        + "    DESC LIMIT 3\n"
        + "  )";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4333">[CALCITE-4333]
   * The Sort rel should be decorrelated even though it has fetch or limit
   * when its parent is not a Correlate</a>. */
  @Test void testSortLimitWithCorrelateInput() {
    final String sql = ""
        + "SELECT deptno, ename\n"
        + "    FROM\n"
        + "        (SELECT DISTINCT deptno FROM emp) t1,\n"
        + "          LATERAL (\n"
        + "            SELECT ename, sal\n"
        + "            FROM emp\n"
        + "            WHERE deptno = t1.deptno)\n"
        + "    ORDER BY ename DESC\n"
        + "    LIMIT 3";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4437">[CALCITE-4437]
   * The Sort rel should be decorrelated even though it has fetch or limit
   * when it is not inside a Correlate</a>.
   */
  @Test void testProjectSortLimitWithCorrelateInput() {
    final String sql = ""
        + "SELECT ename||deptno FROM\n"
        + "    (SELECT deptno, ename\n"
        + "    FROM\n"
        + "        (SELECT DISTINCT deptno FROM emp) t1,\n"
        + "          LATERAL (\n"
        + "            SELECT ename, sal\n"
        + "            FROM emp\n"
        + "            WHERE deptno = t1.deptno)\n"
        + "    ORDER BY ename DESC\n"
        + "    LIMIT 3)";
    sql(sql).ok();
  }

  @Test void testSample() {
    final String sql =
        "select * from emp tablesample substitute('DATASET1') where empno > 5";
    sql(sql).ok();
  }

  @Test void testSampleQuery() {
    final String sql = "select * from (\n"
        + " select * from emp as e tablesample substitute('DATASET1')\n"
        + " join dept on e.deptno = dept.deptno\n"
        + ") tablesample substitute('DATASET2')\n"
        + "where empno > 5";
    sql(sql).ok();
  }

  @Test void testSampleBernoulli() {
    final String sql =
        "select * from emp tablesample bernoulli(50) where empno > 5";
    sql(sql).ok();
  }

  @Test void testSampleBernoulliQuery() {
    final String sql = "select * from (\n"
        + " select * from emp as e tablesample bernoulli(10) repeatable(1)\n"
        + " join dept on e.deptno = dept.deptno\n"
        + ") tablesample bernoulli(50) repeatable(99)\n"
        + "where empno > 5";
    sql(sql).ok();
  }

  @Test void testSampleSystem() {
    final String sql =
        "select * from emp tablesample system(50) where empno > 5";
    sql(sql).ok();
  }

  @Test void testSampleSystemQuery() {
    final String sql = "select * from (\n"
        + " select * from emp as e tablesample system(10) repeatable(1)\n"
        + " join dept on e.deptno = dept.deptno\n"
        + ") tablesample system(50) repeatable(99)\n"
        + "where empno > 5";
    sql(sql).ok();
  }

  @Test void testCollectionTableWithCursorParam() {
    final String sql = "select * from table(dedup("
        + "cursor(select ename from emp),"
        + " cursor(select name from dept), 'NAME'))";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testUnnest() {
    final String sql = "select*from unnest(multiset[1,2])";
    sql(sql).ok();
  }

  @Test void testUnnestSubQuery() {
    final String sql = "select*from unnest(multiset(select*from dept))";
    sql(sql).ok();
  }

  @Test void testUnnestArrayAggPlan() {
    final String sql = "select d.deptno, e2.empno_avg\n"
        + "from dept_nested as d outer apply\n"
        + " (select avg(e.empno) as empno_avg from UNNEST(d.employees) as e) e2";
    sql(sql).withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  @Test void testUnnestArrayPlan() {
    final String sql = "select d.deptno, e2.empno\n"
        + "from dept_nested as d,\n"
        + " UNNEST(d.employees) e2";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testUnnestArrayPlanAs() {
    final String sql = "select d.deptno, e2.empno\n"
        + "from dept_nested as d,\n"
        + " UNNEST(d.employees) as e2(empno, y, z)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3789">[CALCITE-3789]
   * Support validation of UNNEST multiple array columns like Presto</a>.
   */
  @Test void testAliasUnnestArrayPlanWithSingleColumn() {
    final String sql = "select d.deptno, employee.empno\n"
        + "from dept_nested_expanded as d,\n"
        + " UNNEST(d.employees) as t(employee)";
    sql(sql).withConformance(SqlConformanceEnum.PRESTO).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3789">[CALCITE-3789]
   * Support validation of UNNEST multiple array columns like Presto</a>.
   */
  @Test void testAliasUnnestArrayPlanWithDoubleColumn() {
    final String sql = "select d.deptno, e, k.empno\n"
        + "from dept_nested_expanded as d CROSS JOIN\n"
        + " UNNEST(d.admins, d.employees) as t(e, k)";
    sql(sql).withConformance(SqlConformanceEnum.PRESTO).ok();
  }

  @Test void testArrayOfRecord() {
    sql("select employees[1].detail.skills[2+3].desc from dept_nested").ok();
  }

  @Test void testFlattenRecords() {
    sql("select employees[1] from dept_nested").ok();
  }

  @Test void testUnnestArray() {
    sql("select*from unnest(array(select*from dept))").ok();
  }

  @Test void testUnnestArrayNoExpand() {
    final String sql = "select name,\n"
        + "    array (select *\n"
        + "        from emp\n"
        + "        where deptno = dept.deptno) as emp_array,\n"
        + "    multiset (select *\n"
        + "        from emp\n"
        + "        where deptno = dept.deptno) as emp_multiset,\n"
        + "    map (select empno, job\n"
        + "        from emp\n"
        + "        where deptno = dept.deptno) as job_map\n"
        + "from dept";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUnnestWithOrdinality() {
    final String sql =
        "select*from unnest(array(select*from dept)) with ordinality";
    sql(sql).ok();
  }

  @Test void testMultisetSubQuery() {
    final String sql =
        "select multiset(select deptno from dept) from (values(true))";
    sql(sql).ok();
  }

  @Test void testMultiset() {
    final String sql = "select 'a',multiset[10] from dept";
    sql(sql).ok();
  }

  @Test void testMultisetOfColumns() {
    final String sql = "select 'abc',multiset[deptno,sal] from emp";
    sql(sql).withExpand(true).ok();
  }

  @Test void testMultisetOfColumnsRex() {
    sql("select 'abc',multiset[deptno,sal] from emp").ok();
  }

  @Test void testCorrelationJoin() {
    checkCorrelationJoin(true);
  }

  @Test void testCorrelationJoinRex() {
    checkCorrelationJoin(false);
  }

  void checkCorrelationJoin(boolean expand) {
    final String sql = "select *,\n"
        + "  multiset(select * from emp where deptno=dept.deptno) as empset\n"
        + "from dept";
    sql(sql).withExpand(expand).ok();
  }

  @Test void testCorrelatedArraySubQuery() {
    checkCorrelatedArraySubQuery(true);
  }

  @Test void testCorrelatedArraySubQueryRex() {
    checkCorrelatedArraySubQuery(false);
  }

  void checkCorrelatedArraySubQuery(boolean expand) {
    final String sql = "select *,\n"
        + "    array (select * from emp\n"
        + "        where deptno = dept.deptno) as empset\n"
        + "from dept";
    sql(sql).withExpand(expand).ok();
  }

  @Test void testCorrelatedMapSubQuery() {
    checkCorrelatedMapSubQuery(true);
  }

  @Test void testCorrelatedMapSubQueryRex() {
    checkCorrelatedMapSubQuery(false);
  }

  void checkCorrelatedMapSubQuery(boolean expand) {
    final String sql = "select *,\n"
        + "  map (select empno, job\n"
        + "       from emp where deptno = dept.deptno) as jobMap\n"
        + "from dept";
    sql(sql).withExpand(expand).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-864">[CALCITE-864]
   * Correlation variable has incorrect row type if it is populated by right
   * side of a Join</a>. */
  @Test void testCorrelatedSubQueryInJoin() {
    final String sql = "select *\n"
        + "from emp as e\n"
        + "join dept as d using (deptno)\n"
        + "where d.name = (\n"
        + "  select max(name)\n"
        + "  from dept as d2\n"
        + "  where d2.deptno = d.deptno)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testExists() {
    final String sql = "select*from emp\n"
        + "where exists (select 1 from dept where deptno=55)";
    sql(sql).ok();
  }

  @Test void testExistsCorrelated() {
    final String sql = "select*from emp where exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testNotExistsCorrelated() {
    final String sql = "select * from emp where not exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testExistsCorrelatedDecorrelate() {
    final String sql = "select*from emp where exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(true).ok();
  }

  /**
   * Test case for <a href="https://issues.apache.org/jira/browse/CALCITE-4560">[CALCITE-4560]
   * Wrong plan when decorrelating EXISTS subquery with COALESCE in the predicate</a>. */
  @Test void testExistsDecorrelateComplexCorrelationPredicate() {
    final String sql = "select e1.empno from empnullables e1 where exists (\n"
        + "  select 1 from empnullables e2 where COALESCE(e1.ename,'M')=COALESCE(e2.ename,'M'))";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testExistsCorrelatedDecorrelateRex() {
    final String sql = "select*from emp where exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(true).withExpand(false).ok();
  }

  @Test void testExistsCorrelatedLimit() {
    final String sql = "select*from emp where exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno limit 1)";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testExistsCorrelatedLimitDecorrelate() {
    final String sql = "select*from emp where exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno limit 1)";
    sql(sql).withDecorrelate(true).withExpand(true).ok();
  }

  @Test void testExistsCorrelatedLimitDecorrelateRex() {
    final String sql = "select*from emp where exists (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno limit 1)";
    sql(sql).withDecorrelate(true).withExpand(false).ok();
  }

  @Test void testUniqueWithExpand() {
    final String sql = "select * from emp\n"
        + "where unique (select 1 from dept where deptno=55)";
    sql(sql).withExpand(true).throws_("UNIQUE is only supported if expand = false");
  }

  @Test void testUniqueWithProjectLateral() {
    final String sql = "select * from emp\n"
        + "where unique (select 1 from dept where deptno=55)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUniqueWithOneProject() {
    final String sql = "select * from emp\n"
        + "where unique (select name from dept where deptno=55)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUniqueWithManyProject() {
    final String sql = "select * from emp\n"
        + "where unique (select * from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotUnique() {
    final String sql = "select * from emp\n"
        + "where not unique (select 1 from dept where deptno=55)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotUniqueCorrelated() {
    final String sql = "select * from emp where not unique (\n"
        + "  select 1 from dept where emp.deptno=dept.deptno)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testInValueListShort() {
    final String sql = "select empno from emp where deptno in (10, 20)";
    sql(sql).ok();
    sql(sql).withExpand(false).ok();
  }

  @Test void testInValueListLong() {
    // Go over the default threshold of 20 to force a sub-query.
    final String sql = "select empno from emp where deptno in"
        + " (10, 20, 30, 40, 50, 60, 70, 80, 90, 100"
        + ", 110, 120, 130, 140, 150, 160, 170, 180, 190"
        + ", 200, 210, 220, 230)";
    sql(sql).ok();
  }

  @Test void testInUncorrelatedSubQuery() {
    final String sql = "select empno from emp where deptno in"
        + " (select deptno from dept)";
    sql(sql).ok();
  }

  @Test void testInUncorrelatedSubQueryRex() {
    final String sql = "select empno from emp where deptno in"
        + " (select deptno from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testCompositeInUncorrelatedSubQueryRex() {
    final String sql = "select empno from emp where (empno, deptno) in"
        + " (select deptno - 10, deptno from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotInUncorrelatedSubQuery() {
    final String sql = "select empno from emp where deptno not in"
        + " (select deptno from dept)";
    sql(sql).ok();
  }

  @Test void testAllValueList() {
    final String sql = "select empno from emp where deptno > all (10, 20)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testSomeValueList() {
    final String sql = "select empno from emp where deptno > some (10, 20)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testSome() {
    final String sql = "select empno from emp where deptno > some (\n"
        + "  select deptno from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testSomeWithEquality() {
    final String sql = "select empno from emp where deptno = some (\n"
        + "  select deptno from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testSomeWithNotEquality() {
    final String sql = "select empno from emp where deptno <> some (\n"
        + "  select deptno from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotInUncorrelatedSubQueryRex() {
    final String sql = "select empno from emp where deptno not in"
        + " (select deptno from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotCaseInThreeClause() {
    final String sql = "select empno from emp where not case when "
        + "true then deptno in (10,20) else true end";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotCaseInMoreClause() {
    final String sql = "select empno from emp where not case when "
        + "true then deptno in (10,20) when false then false else deptno in (30,40) end";
    sql(sql).withExpand(false).ok();
  }

  @Test void testNotCaseInWithoutElse() {
    final String sql = "select empno from emp where not case when "
        + "true then deptno in (10,20)  end";
    sql(sql).withExpand(false).ok();
  }

  @Test void testWhereInCorrelated() {
    final String sql = "select empno from emp as e\n"
        + "join dept as d using (deptno)\n"
        + "where e.sal in (\n"
        + "  select e2.sal from emp as e2 where e2.deptno > e.deptno)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testInUncorrelatedSubQueryInSelect() {
    // In the SELECT clause, the value of IN remains in 3-valued logic
    // -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
    // WHERE clause -- so the translation is more complicated.
    final String sql = "select name, deptno in (\n"
        + "  select case when true then deptno else null end from emp)\n"
        + "from dept";
    sql(sql).ok();
  }

  @Test void testInUncorrelatedSubQueryInSelectRex() {
    // In the SELECT clause, the value of IN remains in 3-valued logic
    // -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
    // WHERE clause -- so the translation is more complicated.
    final String sql = "select name, deptno in (\n"
        + "  select case when true then deptno else null end from emp)\n"
        + "from dept";
    sql(sql).withExpand(false).ok();
  }

  @Test void testInUncorrelatedSubQueryInHavingRex() {
    final String sql = "select sum(sal) as s\n"
        + "from emp\n"
        + "group by deptno\n"
        + "having count(*) > 2\n"
        + "and deptno in (\n"
        + "  select case when true then deptno else null end from emp)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUncorrelatedScalarSubQueryInOrderRex() {
    final String sql = "select ename\n"
        + "from emp\n"
        + "order by (select case when true then deptno else null end from emp) desc,\n"
        + "  ename";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUncorrelatedScalarSubQueryInGroupOrderRex() {
    final String sql = "select sum(sal) as s\n"
        + "from emp\n"
        + "group by deptno\n"
        + "order by (select case when true then deptno else null end from emp) desc,\n"
        + "  count(*)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUncorrelatedScalarSubQueryInAggregateRex() {
    final String sql = "select sum((select min(deptno) from emp)) as s\n"
        + "from emp\n"
        + "group by deptno\n";
    sql(sql).withExpand(false).ok();
  }

  /** Plan should be as {@link #testInUncorrelatedSubQueryInSelect}, but with
   * an extra NOT. Both queries require 3-valued logic. */
  @Test void testNotInUncorrelatedSubQueryInSelect() {
    final String sql = "select empno, deptno not in (\n"
        + "  select case when true then deptno else null end from dept)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testNotInUncorrelatedSubQueryInSelectRex() {
    final String sql = "select empno, deptno not in (\n"
        + "  select case when true then deptno else null end from dept)\n"
        + "from emp";
    sql(sql).withExpand(false).ok();
  }

  /** Since 'deptno NOT IN (SELECT deptno FROM dept)' can not be null, we
   * generate a simpler plan. */
  @Test void testNotInUncorrelatedSubQueryInSelectNotNull() {
    final String sql = "select empno, deptno not in (\n"
        + "  select deptno from dept)\n"
        + "from emp";
    sql(sql).ok();
  }

  /** Since 'deptno NOT IN (SELECT mgr FROM emp)' can be null, we need a more
   * complex plan, including counts of null and not-null keys. */
  @Test void testNotInUncorrelatedSubQueryInSelectMayBeNull() {
    final String sql = "select empno, deptno not in (\n"
        + "  select mgr from emp)\n"
        + "from emp";
    sql(sql).ok();
  }

  /** Even though "mgr" allows nulls, we can deduce from the WHERE clause that
   * it will never be null. Therefore we can generate a simpler plan. */
  @Test void testNotInUncorrelatedSubQueryInSelectDeduceNotNull() {
    final String sql = "select empno, deptno not in (\n"
        + "  select mgr from emp where mgr > 5)\n"
        + "from emp";
    sql(sql).ok();
  }

  /** Similar to {@link #testNotInUncorrelatedSubQueryInSelectDeduceNotNull()},
   * using {@code IS NOT NULL}. */
  @Test void testNotInUncorrelatedSubQueryInSelectDeduceNotNull2() {
    final String sql = "select empno, deptno not in (\n"
        + "  select mgr from emp where mgr is not null)\n"
        + "from emp";
    sql(sql).ok();
  }

  /** Similar to {@link #testNotInUncorrelatedSubQueryInSelectDeduceNotNull()},
   * using {@code IN}. */
  @Test void testNotInUncorrelatedSubQueryInSelectDeduceNotNull3() {
    final String sql = "select empno, deptno not in (\n"
        + "  select mgr from emp where mgr in (\n"
        + "    select mgr from emp where deptno = 10))\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testNotInUncorrelatedSubQueryInSelectNotNullRex() {
    final String sql = "select empno, deptno not in (\n"
        + "  select deptno from dept)\n"
        + "from emp";
    sql(sql).withExpand(false).ok();
  }

  @Test void testUnnestSelect() {
    final String sql = "select*from unnest(select multiset[deptno] from dept)";
    sql(sql).withExpand(true).ok();
  }

  @Test void testUnnestSelectRex() {
    final String sql = "select*from unnest(select multiset[deptno] from dept)";
    sql(sql).withExpand(false).ok();
  }

  @Test void testJoinUnnest() {
    final String sql = "select*from dept as d, unnest(multiset[d.deptno * 2])";
    sql(sql).ok();
  }

  @Test void testJoinUnnestRex() {
    final String sql = "select*from dept as d, unnest(multiset[d.deptno * 2])";
    sql(sql).withExpand(false).ok();
  }

  @Test void testLateral() {
    final String sql = "select * from emp,\n"
        + "  LATERAL (select * from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testLateralDecorrelate() {
    final String sql = "select * from emp,\n"
        + " LATERAL (select * from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(true).withExpand(true).ok();
  }

  @Test void testLateralDecorrelateRex() {
    final String sql = "select * from emp,\n"
        + " LATERAL (select * from dept where emp.deptno=dept.deptno)";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testLateralDecorrelateThetaRex() {
    final String sql = "select * from emp,\n"
        + " LATERAL (select * from dept where emp.deptno < dept.deptno)";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testNestedCorrelations() {
    final String sql = "select *\n"
        + "from (select 2+deptno d2, 3+deptno d3 from emp) e\n"
        + " where exists (select 1 from (select deptno+1 d1 from dept) d\n"
        + " where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from dept)\n"
        + " where d4=d.d1 and d5=d.d1 and d6=e.d3))";
    sql(sql).withDecorrelate(false).ok();
  }

  @Test void testNestedCorrelationsDecorrelated() {
    final String sql = "select *\n"
        + "from (select 2+deptno d2, 3+deptno d3 from emp) e\n"
        + " where exists (select 1 from (select deptno+1 d1 from dept) d\n"
        + " where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from dept)\n"
        + " where d4=d.d1 and d5=d.d1 and d6=e.d3))";
    sql(sql).withDecorrelate(true).withExpand(true).ok();
  }

  @Test void testNestedCorrelationsDecorrelatedRex() {
    final String sql = "select *\n"
        + "from (select 2+deptno d2, 3+deptno d3 from emp) e\n"
        + " where exists (select 1 from (select deptno+1 d1 from dept) d\n"
        + " where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from dept)\n"
        + " where d4=d.d1 and d5=d.d1 and d6=e.d3))";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testElement() {
    sql("select element(multiset[5]) from emp").ok();
  }

  @Test void testElementInValues() {
    sql("values element(multiset[5])").ok();
  }

  @Test void testUnionAll() {
    final String sql =
        "select empno from emp union all select deptno from dept";
    sql(sql).ok();
  }

  @Test void testUnion() {
    final String sql =
        "select empno from emp union select deptno from dept";
    sql(sql).ok();
  }

  @Test void testUnionValues() {
    // union with values
    final String sql = "values (10), (20)\n"
        + "union all\n"
        + "select 34 from emp\n"
        + "union all values (30), (45 + 10)";
    sql(sql).ok();
  }

  @Test void testUnionSubQuery() {
    // union of sub-query, inside from list, also values
    final String sql = "select deptno from emp as emp0 cross join\n"
        + " (select empno from emp union all\n"
        + "  select deptno from dept where deptno > 20 union all\n"
        + "  values (45), (67))";
    sql(sql).ok();
  }

  @Test void testIsDistinctFrom() {
    final String sql = "select empno is distinct from deptno\n"
        + "from (values (cast(null as int), 1),\n"
        + "             (2, cast(null as int))) as emp(empno, deptno)";
    sql(sql).ok();
  }

  @Test void testIsNotDistinctFrom() {
    final String sql = "select empno is not distinct from deptno\n"
        + "from (values (cast(null as int), 1),\n"
        + "             (2, cast(null as int))) as emp(empno, deptno)";
    sql(sql).ok();
  }

  @Test void testNotLike() {
    // note that 'x not like y' becomes 'not(x like y)'
    final String sql = "values ('a' not like 'b' escape 'c')";
    sql(sql).ok();
  }

  @Test void testTumble() {
    final String sql = "select STREAM\n"
        + "  TUMBLE_START(rowtime, INTERVAL '1' MINUTE) AS s,\n"
        + "  TUMBLE_END(rowtime, INTERVAL '1' MINUTE) AS e\n"
        + "from Shipments\n"
        + "GROUP BY TUMBLE(rowtime, INTERVAL '1' MINUTE)";
    sql(sql).ok();
  }

  @Test void testTableFunctionTumble() {
    final String sql = "select *\n"
        + "from table(tumble(table Shipments, descriptor(rowtime), INTERVAL '1' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionTumbleWithParamNames() {
    final String sql = "select *\n"
        + "from table(\n"
        + "tumble(\n"
        + "  DATA => table Shipments,\n"
        + "  TIMECOL => descriptor(rowtime),\n"
        + "  SIZE => INTERVAL '1' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionTumbleWithParamReordered() {
    final String sql = "select *\n"
        + "from table(\n"
        + "tumble(\n"
        + "  DATA => table Shipments,\n"
        + "  SIZE => INTERVAL '1' MINUTE,\n"
        + "  TIMECOL => descriptor(rowtime)))";
    sql(sql).ok();
  }

  @Test void testTableFunctionTumbleWithInnerJoin() {
    final String sql = "select *\n"
        + "from table(tumble(table Shipments, descriptor(rowtime), INTERVAL '1' MINUTE)) a\n"
        + "join table(tumble(table Shipments, descriptor(rowtime), INTERVAL '1' MINUTE)) b\n"
        + "on a.orderid = b.orderid";
    sql(sql).ok();
  }

  @Test void testTableFunctionTumbleWithOffset() {
    final String sql = "select *\n"
        + "from table(tumble(table Shipments, descriptor(rowtime),\n"
        + "  INTERVAL '10' MINUTE, INTERVAL '1' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionHop() {
    final String sql = "select *\n"
        + "from table(hop(table Shipments, descriptor(rowtime), "
        + "INTERVAL '1' MINUTE, INTERVAL '2' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionHopWithOffset() {
    final String sql = "select *\n"
        + "from table(hop(table Shipments, descriptor(rowtime), "
        + "INTERVAL '1' MINUTE, INTERVAL '5' MINUTE, INTERVAL '3' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionHopWithParamNames() {
    final String sql = "select *\n"
        + "from table(\n"
        + "hop(\n"
        + "  DATA => table Shipments,\n"
        + "  TIMECOL => descriptor(rowtime),\n"
        + "  SLIDE => INTERVAL '1' MINUTE,\n"
        + "  SIZE => INTERVAL '2' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionHopWithParamReordered() {
    final String sql = "select *\n"
        + "from table(\n"
        + "hop(\n"
        + "  DATA => table Shipments,\n"
        + "  SLIDE => INTERVAL '1' MINUTE,\n"
        + "  TIMECOL => descriptor(rowtime),\n"
        + "  SIZE => INTERVAL '2' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionSession() {
    final String sql = "select *\n"
        + "from table(session(table Shipments, descriptor(rowtime), "
        + "descriptor(orderId), INTERVAL '10' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionSessionWithParamNames() {
    final String sql = "select *\n"
        + "from table(\n"
        + "session(\n"
        + "  DATA => table Shipments,\n"
        + "  TIMECOL => descriptor(rowtime),\n"
        + "  KEY => descriptor(orderId),\n"
        + "  SIZE => INTERVAL '10' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionSessionWithParamReordered() {
    final String sql = "select *\n"
        + "from table(\n"
        + "session(\n"
        + "  DATA => table Shipments,\n"
        + "  KEY => descriptor(orderId),\n"
        + "  TIMECOL => descriptor(rowtime),\n"
        + "  SIZE => INTERVAL '10' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionTumbleWithSubQueryParam() {
    final String sql = "select *\n"
        + "from table(tumble((select * from Shipments), descriptor(rowtime), INTERVAL '1' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionHopWithSubQueryParam() {
    final String sql = "select *\n"
        + "from table(hop((select * from Shipments), descriptor(rowtime), "
        + "INTERVAL '1' MINUTE, INTERVAL '2' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionSessionWithSubQueryParam() {
    final String sql = "select *\n"
        + "from table(session((select * from Shipments), descriptor(rowtime), "
        + "descriptor(orderId), INTERVAL '10' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionSessionCompoundSessionKey() {
    final String sql = "select *\n"
        + "from table(session(table Orders, descriptor(rowtime), "
        + "descriptor(orderId, productId), INTERVAL '10' MINUTE))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithPartitionKey() {
    final String sql = "select *\n"
        + "from table(topn(table orders partition by productid, 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithMultiplePartitionKeys() {
    final String sql = "select *\n"
        + "from table(topn(table orders partition by (orderId, productid), 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithOrderKey() {
    final String sql = "select *\n"
        + "from table(topn(table orders order by orderId, 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithMultipleOrderKeys() {
    final String sql = "select *\n"
        + "from table(topn(table orders order by (orderId, productid), 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithComplexOrderBy() {
    final String sql = "select *\n"
        + "from table(topn(table orders order by (orderId desc, productid desc nulls last), 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithOrderByWithNullLast() {
    final String sql = "select *\n"
        + "from table(topn(table orders order by orderId desc nulls last, 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithPartitionKeyAndOrderKey() {
    final String sql = "select *\n"
        + "from table(topn(table orders partition by productid order by orderId, 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithParamNames() {
    final String sql = "select *\n"
        + "from table(\n"
        + "topn(\n"
        + "  DATA => table orders partition by productid order by orderId,\n"
        + "  COL => 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithSubQuery() {
    final String sql = "select *\n"
        + "from table(topn("
        + "select * from orders partition by productid order by orderId desc nulls last, 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithSubQueryWithParamNames() {
    final String sql = "select *\n"
        + "from table(\n"
        + "topn(\n"
        + "  DATA => select * from orders partition by productid order by orderId nulls first,\n"
        + "  COL => 3))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithMultipleInputTables() {
    final String sql = "select *\n"
        + "from table(\n"
        + "similarlity(\n"
        + "  table emp partition by deptno order by empno nulls first,\n"
        + "  table emp_b partition by deptno order by empno nulls first))";
    sql(sql).ok();
  }

  @Test void testTableFunctionWithMultipleInputTablesWithParamNames() {
    final String sql = "select *\n"
        + "from table(\n"
        + "similarlity(\n"
        + "  LTABLE => table emp partition by deptno order by empno nulls first,\n"
        + "  RTABLE => table emp_b partition by deptno order by empno nulls first))";
    sql(sql).ok();
  }

  @Test void testNotNotIn() {
    final String sql = "select * from EMP where not (ename not in ('Fred') )";
    sql(sql).ok();
  }

  @Test void testOverMultiple() {
    final String sql = "select sum(sal) over w1,\n"
        + "  sum(deptno) over w1,\n"
        + "  sum(deptno) over w2\n"
        + "from emp\n"
        + "where deptno - sal > 999\n"
        + "window w1 as (partition by job order by hiredate rows 2 preceding),\n"
        + "  w2 as (partition by job order by hiredate rows 3 preceding disallow partial),\n"
        + "  w3 as (partition by job order by hiredate range interval '1' second preceding)";
    sql(sql).ok();
  }

  @Test void testOverDefaultBracket() {
    // c2 and c3 are equivalent to c1;
    // c5 is equivalent to c4;
    // c7 is equivalent to c6.
    final String sql = "select\n"
        + "  count(*) over (order by deptno) c1,\n"
        + "  count(*) over (order by deptno\n"
        + "    range unbounded preceding) c2,\n"
        + "  count(*) over (order by deptno\n"
        + "    range between unbounded preceding and current row) c3,\n"
        + "  count(*) over (order by deptno\n"
        + "    rows unbounded preceding) c4,\n"
        + "  count(*) over (order by deptno\n"
        + "    rows between unbounded preceding and current row) c5,\n"
        + "  count(*) over (order by deptno\n"
        + "    range between unbounded preceding and unbounded following) c6,\n"
        + " count(*) over (order by deptno\n"
        + "    rows between unbounded preceding and unbounded following) c7\n"
        + "from emp";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-750">[CALCITE-750]
   * Allow windowed aggregate on top of regular aggregate</a>. */
  @Test void testNestedAggregates() {
    final String sql = "SELECT\n"
        + "  avg(sum(sal) + 2 * min(empno) + 3 * avg(empno))\n"
        + "  over (partition by deptno)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  /**
   * Test one of the custom conversions which is recognized by the class of the
   * operator (in this case,
   * {@link org.apache.calcite.sql.fun.SqlCaseOperator}).
   */
  @Test void testCase() {
    sql("values (case 'a' when 'a' then 1 end)").ok();
  }

  /**
   * Tests one of the custom conversions which is recognized by the identity
   * of the operator (in this case,
   * {@link org.apache.calcite.sql.fun.SqlStdOperatorTable#CHARACTER_LENGTH}).
   */
  @Test void testCharLength() {
    // Note that CHARACTER_LENGTH becomes CHAR_LENGTH.
    sql("values (character_length('foo'))").ok();
  }

  @Test void testOverAvg() {
    // AVG(x) gets translated to SUM(x)/COUNT(x).  Because COUNT controls
    // the return type there usually needs to be a final CAST to get the
    // result back to match the type of x.
    final String sql = "select sum(sal) over w1,\n"
        + "  avg(sal) over w1\n"
        + "from emp\n"
        + "window w1 as (partition by job order by hiredate rows 2 preceding)";
    sql(sql).ok();
  }

  @Test void testOverAvg2() {
    // Check to see if extra CAST is present.  Because CAST is nested
    // inside AVG it passed to both SUM and COUNT so the outer final CAST
    // isn't needed.
    final String sql = "select sum(sal) over w1,\n"
        + "  avg(CAST(sal as real)) over w1\n"
        + "from emp\n"
        + "window w1 as (partition by job order by hiredate rows 2 preceding)";
    sql(sql).ok();
  }

  @Test void testOverCountStar() {
    final String sql = "select count(sal) over w1,\n"
        + "  count(*) over w1\n"
        + "from emp\n"
        + "window w1 as (partition by job order by hiredate rows 2 preceding)";
    sql(sql).ok();
  }

  /**
   * Tests that a window containing only ORDER BY is implicitly CURRENT ROW.
   */
  @Test void testOverOrderWindow() {
    final String sql = "select last_value(deptno) over w\n"
        + "from emp\n"
        + "window w as (order by empno)";
    sql(sql).ok();

    // Same query using inline window
    final String sql2 = "select last_value(deptno) over (order by empno)\n"
        + "from emp\n";
    sql(sql2).ok();
  }

  /**
   * Tests that a window with specifying null treatment.
   */
  @Test void testOverNullTreatmentWindow() {
    final String sql = "select\n"
        + "lead(deptno, 1) over w,\n "
        + "lead(deptno, 2) ignore nulls over w,\n"
        + "lead(deptno, 3) respect nulls over w,\n"
        + "lead(deptno, 1) over w,\n"
        + "lag(deptno, 2) ignore nulls over w,\n"
        + "lag(deptno, 2) respect nulls over w,\n"
        + "first_value(deptno) over w,\n"
        + "first_value(deptno) ignore nulls over w,\n"
        + "first_value(deptno) respect nulls over w,\n"
        + "last_value(deptno) over w,\n"
        + "last_value(deptno) ignore nulls over w,\n"
        + "last_value(deptno) respect nulls over w\n"
        + " from emp\n"
        + "window w as (order by empno)";
    sql(sql).ok();
  }

  /**
   * Tests that a window with a FOLLOWING bound becomes BETWEEN CURRENT ROW
   * AND FOLLOWING.
   */
  @Test void testOverOrderFollowingWindow() {
    // Window contains only ORDER BY (implicitly CURRENT ROW).
    final String sql = "select last_value(deptno) over w\n"
        + "from emp\n"
        + "window w as (order by empno rows 2 following)";
    sql(sql).ok();

    // Same query using inline window
    final String sql2 = "select\n"
        + "  last_value(deptno) over (order by empno rows 2 following)\n"
        + "from emp\n";
    sql(sql2).ok();
  }

  @Test void testTumbleTable() {
    final String sql = "select stream"
        + " tumble_end(rowtime, interval '2' hour) as rowtime, productId\n"
        + "from orders\n"
        + "group by tumble(rowtime, interval '2' hour), productId";
    sql(sql).ok();
  }

  /** As {@link #testTumbleTable()} but on a table where "rowtime" is at
   * position 1 not 0. */
  @Test void testTumbleTableRowtimeNotFirstColumn() {
    final String sql = "select stream\n"
        + "   tumble_end(rowtime, interval '2' hour) as rowtime, orderId\n"
        + "from shipments\n"
        + "group by tumble(rowtime, interval '2' hour), orderId";
    sql(sql).ok();
  }

  @Test void testHopTable() {
    final String sql = "select stream hop_start(rowtime, interval '1' hour,"
        + " interval '3' hour) as rowtime,\n"
        + "  count(*) as c\n"
        + "from orders\n"
        + "group by hop(rowtime, interval '1' hour, interval '3' hour)";
    sql(sql).ok();
  }

  @Test void testSessionTable() {
    final String sql = "select stream session_start(rowtime, interval '1' hour)"
        + " as rowtime,\n"
        + "  session_end(rowtime, interval '1' hour),\n"
        + "  count(*) as c\n"
        + "from orders\n"
        + "group by session(rowtime, interval '1' hour)";
    sql(sql).ok();
  }

  @Test void testInterval() {
    // temporarily disabled per DTbug 1212
    if (!Bug.DT785_FIXED) {
      return;
    }
    final String sql =
        "values(cast(interval '1' hour as interval hour to second))";
    sql(sql).ok();
  }

  @Test void testStream() {
    final String sql =
        "select stream productId from orders where productId = 10";
    sql(sql).ok();
  }

  @Test void testStreamGroupBy() {
    final String sql = "select stream\n"
        + " floor(rowtime to second) as rowtime, count(*) as c\n"
        + "from orders\n"
        + "group by floor(rowtime to second)";
    sql(sql).ok();
  }

  @Test void testStreamWindowedAggregation() {
    final String sql = "select stream *,\n"
        + "  count(*) over (partition by productId\n"
        + "    order by rowtime\n"
        + "    range interval '1' second preceding) as c\n"
        + "from orders";
    sql(sql).ok();
  }

  @Test void testExplainAsXml() {
    String sql = "select 1 + 2, 3 from (values (true))";
    final RelNode rel = sql(sql).toRel();
    StringWriter sw = new StringWriter();
    PrintWriter pw = new PrintWriter(sw);
    RelXmlWriter planWriter =
        new RelXmlWriter(pw, SqlExplainLevel.EXPPLAN_ATTRIBUTES);
    rel.explain(planWriter);
    pw.flush();
    TestUtil.assertEqualsVerbose(
        "<RelNode type=\"LogicalProject\">\n"
            + "\t<Property name=\"EXPR$0\">\n"
            + "\t\t+(1, 2)\n"
            + "\t</Property>\n"
            + "\t<Property name=\"EXPR$1\">\n"
            + "\t\t3\n"
            + "\t</Property>\n"
            + "\t<Inputs>\n"
            + "\t\t<RelNode type=\"LogicalValues\">\n"
            + "\t\t\t<Property name=\"tuples\">\n"
            + "\t\t\t\t[{ true }]\n"
            + "\t\t\t</Property>\n"
            + "\t\t\t<Inputs/>\n"
            + "\t\t</RelNode>\n"
            + "\t</Inputs>\n"
            + "</RelNode>\n",
        Util.toLinux(sw.toString()));
  }

  @Test void testExplainAsDot() {
    String sql = "select 1 + 2, 3 from (values (true))";
    final RelNode rel = sql(sql).toRel();
    StringWriter sw = new StringWriter();
    PrintWriter pw = new PrintWriter(sw);
    RelDotWriter planWriter =
        new RelDotWriter(pw, SqlExplainLevel.EXPPLAN_ATTRIBUTES, false);
    rel.explain(planWriter);
    pw.flush();
    TestUtil.assertEqualsVerbose(
        "digraph {\n"
            + "\"LogicalValues\\ntuples = [{ true }]\\n\" -> \"LogicalProject\\nEXPR$0 = +(1, 2)"
            + "\\nEXPR$1 = 3\\n\" [label=\"0\"]\n"
            + "}\n",
        Util.toLinux(sw.toString()));
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-412">[CALCITE-412]
   * RelFieldTrimmer: when trimming Sort, the collation and trait set don't
   * match</a>. */
  @Test void testSortWithTrim() {
    final String sql = "select ename from (select * from emp order by sal) a";
    sql(sql).withTrim(true).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3183">[CALCITE-3183]
   * Trimming method for Filter rel uses wrong traitSet</a>. */
  @SuppressWarnings("rawtypes")
  @Test void testFilterAndSortWithTrim() {
    // Run query and save plan after trimming
    final String sql = "select count(a.EMPNO)\n"
        + "from (select * from emp order by sal limit 3) a\n"
        + "where a.EMPNO > 10 group by 2";
    RelNode afterTrim = sql(sql)
        .withDecorrelate(false)
        .withFactory(t ->
            // Create a customized test with RelCollation trait in the test
            // cluster.
            t.withPlannerFactory(context ->
                new MockRelOptPlanner(Contexts.empty()) {
                  @Override public List<RelTraitDef> getRelTraitDefs() {
                    return ImmutableList.of(RelCollationTraitDef.INSTANCE);
                  }
                  @Override public RelTraitSet emptyTraitSet() {
                    return RelTraitSet.createEmpty().plus(
                        RelCollationTraitDef.INSTANCE.getDefault());
                  }
                }))
        .toRel();

    // Get Sort and Filter operators
    final List<RelNode> rels = new ArrayList<>();
    final RelShuttleImpl visitor = new RelShuttleImpl() {
      @Override public RelNode visit(LogicalSort sort) {
        rels.add(sort);
        return super.visit(sort);
      }
      @Override public RelNode visit(LogicalFilter filter) {
        rels.add(filter);
        return super.visit(filter);
      }
    };
    visitor.visit(afterTrim);

    // Ensure sort and filter operators have consistent traitSet after trimming
    assertThat(rels.size(), is(2));
    RelTrait filterCollation = rels.get(0).getTraitSet()
        .getTrait(RelCollationTraitDef.INSTANCE);
    RelTrait sortCollation = rels.get(1).getTraitSet()
        .getTrait(RelCollationTraitDef.INSTANCE);
    assertThat(filterCollation, notNullValue());
    assertThat(sortCollation, notNullValue());
    assertThat(filterCollation.satisfies(sortCollation), is(true));
  }

  @Test void testRelShuttleForLogicalCalc() {
    final String sql = "select ename from emp";
    final RelNode rel = sql(sql).toRel();
    final HepProgramBuilder programBuilder = HepProgram.builder();
    programBuilder.addRuleInstance(CoreRules.PROJECT_TO_CALC);
    final HepPlanner planner = new HepPlanner(programBuilder.build());
    planner.setRoot(rel);
    final RelNode calc = planner.findBestExp();
    final List<RelNode> rels = new ArrayList<>();
    final RelShuttleImpl visitor = new RelShuttleImpl() {
      @Override public RelNode visit(LogicalCalc calc) {
        RelNode visitedRel = super.visit(calc);
        rels.add(visitedRel);
        return visitedRel;
      }
    };
    calc.accept(visitor);
    assertThat(rels.size(), is(1));
    assertThat(rels.get(0), isA(LogicalCalc.class));
  }

  @Test void testRelShuttleForLogicalTableModify() {
    final String sql = "insert into emp select * from emp";
    final RelNode rel = sql(sql).toRel();
    final List<RelNode> rels = new ArrayList<>();
    final RelShuttleImpl visitor = new RelShuttleImpl() {
      @Override public RelNode visit(LogicalTableModify modify) {
        RelNode visitedRel = super.visit(modify);
        rels.add(visitedRel);
        return visitedRel;
      }
    };
    rel.accept(visitor);
    assertThat(rels.size(), is(1));
    assertThat(rels.get(0), isA(LogicalTableModify.class));
  }

  @Test void testOffset0() {
    final String sql = "select * from emp offset 0";
    sql(sql).ok();
  }

  /** Tests group-by CASE expression involving a non-query IN. */
  @Test void testGroupByCaseSubQuery() {
    final String sql = "SELECT CASE WHEN emp.empno IN (3) THEN 0 ELSE 1 END\n"
        + "FROM emp\n"
        + "GROUP BY (CASE WHEN emp.empno IN (3) THEN 0 ELSE 1 END)";
    sql(sql).ok();
  }

  /** Tests an aggregate function on a CASE expression involving a non-query
   * IN. */
  @Test void testAggCaseSubQuery() {
    final String sql =
        "SELECT SUM(CASE WHEN empno IN (3) THEN 0 ELSE 1 END) FROM emp";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-753">[CALCITE-753]
   * Test aggregate operators do not derive row types with duplicate column
   * names</a>. */
  @Test void testAggNoDuplicateColumnNames() {
    final String sql = "SELECT  empno, EXPR$2, COUNT(empno) FROM (\n"
        + "    SELECT empno, deptno AS EXPR$2\n"
        + "    FROM emp)\n"
        + "GROUP BY empno, EXPR$2";
    sql(sql).ok();
  }

  @Test void testAggScalarSubQuery() {
    final String sql = "SELECT SUM(SELECT min(deptno) FROM dept) FROM emp";
    sql(sql).ok();
  }

  /** Test aggregate function on a CASE expression involving IN with a
   * sub-query.
   *
   * <p>Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-551">[CALCITE-551]
   * Sub-query inside aggregate function</a>. */
  @Test void testAggCaseInSubQuery() {
    final String sql = "SELECT SUM(\n"
        + "  CASE WHEN deptno IN (SELECT deptno FROM dept) THEN 1 ELSE 0 END)\n"
        + "FROM emp";
    sql(sql).withExpand(false).ok();
  }

  @Test void testCorrelatedSubQueryInAggregate() {
    final String sql = "SELECT SUM(\n"
        + "  (select char_length(name) from dept\n"
        + "   where dept.deptno = emp.empno))\n"
        + "FROM emp";
    sql(sql).withExpand(false).ok();
  }

  @Test void testCorrelatedForOuterFields() {
    final String sql = "SELECT ARRAY(SELECT dept.deptno)\n"
        + "FROM emp\n"
        + "LEFT OUTER JOIN dept\n"
        + "ON emp.empno = dept.deptno";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-614">[CALCITE-614]
   * IN within CASE within GROUP BY gives AssertionError</a>.
   */
  @Test void testGroupByCaseIn() {
    final String sql = "select\n"
        + " (CASE WHEN (deptno IN (10, 20)) THEN 0 ELSE deptno END),\n"
        + " min(empno) from EMP\n"
        + "group by (CASE WHEN (deptno IN (10, 20)) THEN 0 ELSE deptno END)";
    sql(sql).ok();
  }

  @Test void testInsert() {
    final String sql = "insert into empnullables (deptno, empno, ename)\n"
        + "values (10, 150, 'Fred')";
    sql(sql).ok();
  }

  @Test void testInsertSubset() {
    final String sql = "insert into empnullables\n"
        + "values (50, 'Fred')";
    sql(sql).withConformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
  }

  @Test void testInsertWithCustomInitializerExpressionFactory() {
    final String sql = "insert into empdefaults (deptno) values (300)";
    sql(sql).ok();
  }

  @Test void testInsertSubsetWithCustomInitializerExpressionFactory() {
    final String sql = "insert into empdefaults values (100)";
    sql(sql).withConformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
  }

  @Test void testInsertBind() {
    final String sql = "insert into empnullables (deptno, empno, ename)\n"
        + "values (?, ?, ?)";
    sql(sql).ok();
  }

  @Test void testInsertBindSubset() {
    final String sql = "insert into empnullables\n"
        + "values (?, ?)";
    sql(sql).withConformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
  }

  @Test void testInsertBindWithCustomInitializerExpressionFactory() {
    final String sql = "insert into empdefaults (deptno) values (?)";
    sql(sql).ok();
  }

  @Test void testInsertBindSubsetWithCustomInitializerExpressionFactory() {
    final String sql = "insert into empdefaults values (?)";
    sql(sql).withConformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
  }

  @Test void testInsertSubsetView() {
    final String sql = "insert into empnullables_20\n"
        + "values (10, 'Fred')";
    sql(sql).withConformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
  }

  @Test void testInsertExtendedColumn() {
    final String sql = "insert into empdefaults(updated TIMESTAMP)\n"
        + " (ename, deptno, empno, updated, sal)\n"
        + " values ('Fred', 456, 44, timestamp '2017-03-12 13:03:05', 999999)";
    sql(sql).ok();
  }

  @Test void testInsertBindExtendedColumn() {
    final String sql = "insert into empdefaults(updated TIMESTAMP)\n"
        + " (ename, deptno, empno, updated, sal)\n"
        + " values ('Fred', 456, 44, ?, 999999)";
    sql(sql).ok();
  }

  @Test void testInsertExtendedColumnModifiableView() {
    final String sql = "insert into EMP_MODIFIABLEVIEW2(updated TIMESTAMP)\n"
        + " (ename, deptno, empno, updated, sal)\n"
        + " values ('Fred', 20, 44, timestamp '2017-03-12 13:03:05', 999999)";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testInsertBindExtendedColumnModifiableView() {
    final String sql = "insert into EMP_MODIFIABLEVIEW2(updated TIMESTAMP)\n"
        + " (ename, deptno, empno, updated, sal)\n"
        + " values ('Fred', 20, 44, ?, 999999)";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testInsertWithSort() {
    final String sql = "insert into empnullables (empno, ename)\n"
        + "select deptno, ename from emp order by ename";
    sql(sql).ok();
  }

  @Test void testInsertWithLimit() {
    final String sql = "insert into empnullables (empno, ename)\n"
        + "select deptno, ename from emp order by ename limit 10";
    sql(sql).ok();
  }

  @Test void testDelete() {
    final String sql = "delete from emp";
    sql(sql).ok();
  }

  @Test void testDeleteWhere() {
    final String sql = "delete from emp where deptno = 10";
    sql(sql).ok();
  }

  @Test void testDeleteBind() {
    final String sql = "delete from emp where deptno = ?";
    sql(sql).ok();
  }

  @Test void testDeleteBindExtendedColumn() {
    final String sql = "delete from emp(enddate TIMESTAMP) where enddate < ?";
    sql(sql).ok();
  }

  @Test void testDeleteBindModifiableView() {
    final String sql = "delete from EMP_MODIFIABLEVIEW2 where empno = ?";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testDeleteBindExtendedColumnModifiableView() {
    final String sql = "delete from EMP_MODIFIABLEVIEW2(note VARCHAR)\n"
        + "where note = ?";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testUpdate() {
    final String sql = "update emp set empno = empno + 1";
    sql(sql).ok();
  }

  @Test void testUpdateSubQuery() {
    final String sql = "update emp\n"
        + "set empno = (\n"
        + "  select min(empno) from emp as e where e.deptno = emp.deptno)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3229">[CALCITE-3229]
   * UnsupportedOperationException for UPDATE with IN query</a>.
   */
  @Test void testUpdateSubQueryWithIn() {
    final String sql = "update emp\n"
            + "set empno = 1 where empno in (\n"
            + "  select empno from emp where empno=2)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3292">[CALCITE-3292]
   * NPE for UPDATE with IN query</a>.
   */
  @Test void testUpdateSubQueryWithIn1() {
    final String sql = "update emp\n"
            + "set empno = 1 where emp.empno in (\n"
            + "  select emp.empno from emp where emp.empno=2)";
    sql(sql).ok();
  }

  /** Similar to {@link #testUpdateSubQueryWithIn()} but with not in instead of in. */
  @Test void testUpdateSubQueryWithNotIn() {
    final String sql = "update emp\n"
            + "set empno = 1 where empno not in (\n"
            + "  select empno from emp where empno=2)";
    sql(sql).ok();
  }

  @Test void testUpdateWhere() {
    final String sql = "update emp set empno = empno + 1 where deptno = 10";
    sql(sql).ok();
  }

  @Test void testUpdateModifiableView() {
    final String sql = "update EMP_MODIFIABLEVIEW2\n"
        + "set sal = sal + 5000 where slacker = false";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testUpdateExtendedColumn() {
    final String sql = "update empdefaults(updated TIMESTAMP)"
        + " set deptno = 1, updated = timestamp '2017-03-12 13:03:05', empno = 20, ename = 'Bob'"
        + " where deptno = 10";
    sql(sql).ok();
  }

  @Test void testUpdateExtendedColumnModifiableView() {
    final String sql = "update EMP_MODIFIABLEVIEW2(updated TIMESTAMP)\n"
        + "set updated = timestamp '2017-03-12 13:03:05', sal = sal + 5000\n"
        + "where slacker = false";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testUpdateBind() {
    final String sql = "update emp"
        + " set sal = sal + ? where slacker = false";
    sql(sql).ok();
  }

  @Test void testUpdateBind2() {
    final String sql = "update emp"
        + " set sal = ? where slacker = false";
    sql(sql).ok();
  }

  @Disabled("CALCITE-1708")
  @Test void testUpdateBindExtendedColumn() {
    final String sql = "update emp(test INT)"
        + " set test = ?, sal = sal + 5000 where slacker = false";
    sql(sql).ok();
  }

  @Disabled("CALCITE-1708")
  @Test void testUpdateBindExtendedColumnModifiableView() {
    final String sql = "update EMP_MODIFIABLEVIEW2(test INT)"
        + " set test = ?, sal = sal + 5000 where slacker = false";
    sql(sql).ok();
  }

  @Disabled("CALCITE-985")
  @Test void testMerge() {
    final String sql = "merge into emp as target\n"
        + "using (select * from emp where deptno = 30) as source\n"
        + "on target.empno = source.empno\n"
        + "when matched then\n"
        + "  update set sal = sal + source.sal\n"
        + "when not matched then\n"
        + "  insert (empno, deptno, sal)\n"
        + "  values (source.empno, source.deptno, source.sal)";
    sql(sql).ok();
  }

  @Test void testSelectView() {
    // translated condition: deptno = 20 and sal > 1000 and empno > 100
    final String sql = "select * from emp_20 where empno > 100";
    sql(sql).ok();
  }

  @Test void testInsertView() {
    final String sql = "insert into empnullables_20 (empno, ename)\n"
        + "values (150, 'Fred')";
    sql(sql).ok();
  }

  @Test void testInsertModifiableView() {
    final String sql = "insert into EMP_MODIFIABLEVIEW (EMPNO, ENAME, JOB)"
        + " values (34625, 'nom', 'accountant')";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testInsertSubsetModifiableView() {
    final String sql = "insert into EMP_MODIFIABLEVIEW "
        + "values (10, 'Fred')";
    sql(sql).withExtendedTester()
        .withConformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
  }

  @Test void testInsertBindModifiableView() {
    final String sql = "insert into EMP_MODIFIABLEVIEW (empno, job)"
        + " values (?, ?)";
    sql(sql).withExtendedTester().ok();
  }

  @Test void testInsertBindSubsetModifiableView() {
    final String sql = "insert into EMP_MODIFIABLEVIEW"
        + " values (?, ?)";
    sql(sql).withConformance(SqlConformanceEnum.PRAGMATIC_2003)
        .withExtendedTester().ok();
  }

  @Test void testInsertWithCustomColumnResolving() {
    final String sql = "insert into struct.t values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    sql(sql).ok();
  }

  @Test void testInsertWithCustomColumnResolving2() {
    final String sql = "insert into struct.t_nullables (f0.c0, f1.c2, c1)\n"
        + "values (?, ?, ?)";
    sql(sql).ok();
  }

  @Test void testInsertViewWithCustomColumnResolving() {
    final String sql = "insert into struct.t_10 (f0.c0, f1.c2, c1, k0,\n"
        + "  f1.a0, f2.a0, f0.c1, f2.c3)\n"
        + "values (?, ?, ?, ?, ?, ?, ?, ?)";
    sql(sql).ok();
  }

  @Test void testUpdateWithCustomColumnResolving() {
    final String sql = "update struct.t set c0 = c0 + 1";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2936">[CALCITE-2936]
   * Existential sub-query that has aggregate without grouping key
   * should be simplified to constant boolean expression</a>.
   */
  @Test void testSimplifyExistsAggregateSubQuery() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1 where exists\n"
        + "(select avg(sal) from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testSimplifyNotExistsAggregateSubQuery() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1 where not exists\n"
        + "(select avg(sal) from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2936">[CALCITE-2936]
   * Existential sub-query that has Values with at least 1 tuple
   * should be simplified to constant boolean expression</a>.
   */
  @Test void testSimplifyExistsValuesSubQuery() {
    final String sql = "select deptno\n"
        + "from EMP\n"
        + "where exists (values 10)";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testSimplifyNotExistsValuesSubQuery() {
    final String sql = "select deptno\n"
        + "from EMP\n"
        + "where not exists (values 10)";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testReduceConstExpr() {
    final String sql = "select sum(case when 'y' = 'n' then ename else 0.1 end) from emp";
    sql(sql).ok();
  }

  @Test void testSubQueryNoExpand() {
    final String sql = "select (select empno from EMP where 1 = 0)";
    sql(sql).withExpand(false).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-695">[CALCITE-695]
   * SqlSingleValueAggFunction is created when it may not be needed</a>.
   */
  @Test void testSubQueryAggregateFunctionFollowedBySimpleOperation() {
    final String sql = "select deptno\n"
        + "from EMP\n"
        + "where deptno > (select min(deptno) * 2 + 10 from EMP)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1799">[CALCITE-1799]
   * "OR .. IN" sub-query conversion wrong</a>.
   *
   * <p>The problem is only fixed if you have {@code expand = false}.
   */
  @Test void testSubQueryOr() {
    final String sql = "select * from emp where deptno = 10 or deptno in (\n"
        + "    select dept.deptno from dept where deptno < 5)\n";
    sql(sql).withExpand(false).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-695">[CALCITE-695]
   * SqlSingleValueAggFunction is created when it may not be needed</a>.
   */
  @Test void testSubQueryValues() {
    final String sql = "select deptno\n"
        + "from EMP\n"
        + "where deptno > (values 10)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-695">[CALCITE-695]
   * SqlSingleValueAggFunction is created when it may not be needed</a>.
   */
  @Test void testSubQueryLimitOne() {
    final String sql = "select deptno\n"
        + "from EMP\n"
        + "where deptno > (select deptno\n"
        + "from EMP order by deptno limit 1)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-710">[CALCITE-710]
   * When look up sub-queries, perform the same logic as the way when ones were
   * registered</a>.
   */
  @Test void testIdenticalExpressionInSubQuery() {
    final String sql = "select deptno\n"
        + "from EMP\n"
        + "where deptno in (1, 2) or deptno in (1, 2)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-694">[CALCITE-694]
   * Scan HAVING clause for sub-queries and IN-lists</a> relating to IN.
   */
  @Test void testHavingAggrFunctionIn() {
    final String sql = "select deptno\n"
        + "from emp\n"
        + "group by deptno\n"
        + "having sum(case when deptno in (1, 2) then 0 else 1 end) +\n"
        + "sum(case when deptno in (3, 4) then 0 else 1 end) > 10";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-694">[CALCITE-694]
   * Scan HAVING clause for sub-queries and IN-lists</a>, with a sub-query in
   * the HAVING clause.
   */
  @Test void testHavingInSubQueryWithAggrFunction() {
    final String sql = "select sal\n"
        + "from emp\n"
        + "group by sal\n"
        + "having sal in (\n"
        + "  select deptno\n"
        + "  from dept\n"
        + "  group by deptno\n"
        + "  having sum(deptno) > 0)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-716">[CALCITE-716]
   * Scalar sub-query and aggregate function in SELECT or HAVING clause gives
   * AssertionError</a>; variant involving HAVING clause.
   */
  @Test void testAggregateAndScalarSubQueryInHaving() {
    final String sql = "select deptno\n"
        + "from emp\n"
        + "group by deptno\n"
        + "having max(emp.empno) > (SELECT min(emp.empno) FROM emp)\n";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-716">[CALCITE-716]
   * Scalar sub-query and aggregate function in SELECT or HAVING clause gives
   * AssertionError</a>; variant involving SELECT clause.
   */
  @Test void testAggregateAndScalarSubQueryInSelect() {
    final String sql = "select deptno,\n"
        + "  max(emp.empno) > (SELECT min(emp.empno) FROM emp) as b\n"
        + "from emp\n"
        + "group by deptno\n";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
   * window aggregate and ranking functions with grouped aggregates</a>.
   */
  @Test void testWindowAggWithGroupBy() {
    final String sql = "select min(deptno), rank() over (order by empno),\n"
        + "max(empno) over (partition by deptno)\n"
        + "from emp group by deptno, empno\n";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-847">[CALCITE-847]
   * AVG window function in GROUP BY gives AssertionError</a>.
   */
  @Test void testWindowAverageWithGroupBy() {
    final String sql = "select avg(deptno) over ()\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
   * variant involving joins</a>.
   */
  @Test void testWindowAggWithGroupByAndJoin() {
    final String sql = "select min(d.deptno), rank() over (order by e.empno),\n"
        + " max(e.empno) over (partition by e.deptno)\n"
        + "from emp e, dept d\n"
        + "where e.deptno = d.deptno\n"
        + "group by d.deptno, e.empno, e.deptno\n";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
   * variant involving HAVING clause</a>.
   */
  @Test void testWindowAggWithGroupByAndHaving() {
    final String sql = "select min(deptno), rank() over (order by empno),\n"
        + "max(empno) over (partition by deptno)\n"
        + "from emp group by deptno, empno\n"
        + "having empno < 10 and min(deptno) < 20\n";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-770">[CALCITE-770]
   * variant involving join with sub-query that contains window function and
   * GROUP BY</a>.
   */
  @Test void testWindowAggInSubQueryJoin() {
    final String sql = "select T.x, T.y, T.z, emp.empno\n"
        + "from (select min(deptno) as x,\n"
        + "   rank() over (order by empno) as y,\n"
        + "   max(empno) over (partition by deptno) as z\n"
        + "   from emp group by deptno, empno) as T\n"
        + " inner join emp on T.x = emp.deptno\n"
        + " and T.y = emp.empno\n";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1313">[CALCITE-1313]
   * Validator should derive type of expression in ORDER BY</a>.
   */
  @Test void testOrderByOver() {
    String sql = "select deptno, rank() over(partition by empno order by deptno)\n"
        + "from emp order by row_number() over(partition by empno order by deptno)";
    sql(sql).ok();
  }

  /**
   * Test case (correlated scalar aggregate sub-query) for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
   * When de-correlating, push join condition into sub-query</a>.
   */
  @Test void testCorrelationScalarAggAndFilter() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
        + "and e1.deptno < 10 and d1.deptno < 15\n"
        + "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).withExpand(true).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1543">[CALCITE-1543]
   * Correlated scalar sub-query with multiple aggregates gives
   * AssertionError</a>. */
  @Test void testCorrelationMultiScalarAggregate() {
    final String sql = "select sum(e1.empno)\n"
        + "from emp e1, dept d1\n"
        + "where e1.deptno = d1.deptno\n"
        + "and e1.sal > (select avg(e2.sal) from emp e2\n"
        + "  where e2.deptno = d1.deptno)";
    sql(sql).withDecorrelate(true).withExpand(true).ok();
  }

  @Test void testCorrelationScalarAggAndFilterRex() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
        + "and e1.deptno < 10 and d1.deptno < 15\n"
        + "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).withExpand(false).ok();
  }

  /**
   * Test case (correlated EXISTS sub-query) for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
   * When de-correlating, push join condition into sub-query</a>.
   */
  @Test void testCorrelationExistsAndFilter() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
        + "and e1.deptno < 10 and d1.deptno < 15\n"
        + "and exists (select * from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).withExpand(true).ok();
  }

  @Test void testCorrelationExistsAndFilterRex() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
        + "and e1.deptno < 10 and d1.deptno < 15\n"
        + "and exists (select * from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).ok();
  }

  /** A theta join condition, unlike the equi-join condition in
   * {@link #testCorrelationExistsAndFilterRex()}, requires a value
   * generator. */
  @Test void testCorrelationExistsAndFilterThetaRex() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
        + "and e1.deptno < 10 and d1.deptno < 15\n"
        + "and exists (select * from emp e2 where e1.empno < e2.empno)";
    sql(sql).withDecorrelate(true).ok();
  }

  /**
   * Test case (correlated NOT EXISTS sub-query) for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-714">[CALCITE-714]
   * When de-correlating, push join condition into sub-query</a>.
   */
  @Test void testCorrelationNotExistsAndFilter() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n"
        + "and e1.deptno < 10 and d1.deptno < 15\n"
        + "and not exists (select * from emp e2 where e1.empno = e2.empno)";
    sql(sql).withDecorrelate(true).ok();
  }

  /**
   * Test case for decorrelating sub-query that has aggregate with
   * grouping sets.
   */
  @Test void testCorrelationAggregateGroupSets() {
    final String sql = "select sum(e1.empno)\n"
        + "from emp e1, dept d1\n"
        + "where e1.deptno = d1.deptno\n"
        + "and e1.sal > (select avg(e2.sal) from emp e2\n"
        + "  where e2.deptno = d1.deptno group by cube(comm, mgr))";
    sql(sql).withDecorrelate(true).ok();
  }

  @Test void testCorrelationInProjectionWithScan() {
    final String sql = "select array(select e.deptno) from emp e";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  @Test void testCorrelationInProjectionWithProjection() {
    final String sql = "select array(select e.deptno)\n"
        + "from (select deptno, ename from emp) e";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  @Test void testMultiCorrelationInProjectionWithProjection() {
    final String sql = "select cardinality(array(select e.deptno)), array(select e.ename)[0]\n"
        + "from (select deptno, ename from emp) e";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  @Test void testCorrelationInProjectionWithCorrelatedProjection() {
    final String sql = "select cardinality(arr) from"
        + "(select array(select e.deptno) arr\n"
        + "from (select deptno, ename from emp) e)";
    sql(sql).withExpand(false).withDecorrelate(false).ok();
  }

  @Test void testCustomColumnResolving() {
    final String sql = "select k0 from struct.t";
    sql(sql).ok();
  }

  @Test void testCustomColumnResolving2() {
    final String sql = "select c2 from struct.t";
    sql(sql).ok();
  }

  @Test void testCustomColumnResolving3() {
    final String sql = "select f1.c2 from struct.t";
    sql(sql).ok();
  }

  @Test void testCustomColumnResolving4() {
    final String sql = "select c1 from struct.t order by f0.c1";
    sql(sql).ok();
  }

  @Test void testCustomColumnResolving5() {
    final String sql = "select count(c1) from struct.t group by f0.c1";
    sql(sql)
        .withConfig(c ->
            // Don't prune the Project. We want to see columns "FO"."C1" & "C1".
            c.addRelBuilderConfigTransform(c2 ->
                c2.withPruneInputOfAggregate(false)))
        .ok();
  }

  @Test void testCustomColumnResolvingWithSelectStar() {
    final String sql = "select * from struct.t";
    sql(sql).ok();
  }

  @Test void testCustomColumnResolvingWithSelectFieldNameDotStar() {
    final String sql = "select f1.* from struct.t";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1150">[CALCITE-1150]
   * Dynamic Table / Dynamic Star support</a>. */
  @Test void testSelectFromDynamicTable() {
    final String sql = "select n_nationkey, n_name from SALES.NATION";
    sql(sql).withDynamicTable().ok();
  }

  /** As {@link #testSelectFromDynamicTable} but "SELECT *". */
  @Test void testSelectStarFromDynamicTable() {
    final String sql = "select * from SALES.NATION";
    sql(sql).withDynamicTable().ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2080">[CALCITE-2080]
   * Query with NOT IN operator and literal fails throws AssertionError: 'Cast
   * for just nullability not allowed'</a>. */
  @Test void testNotInWithLiteral() {
    final String sql = "SELECT *\n"
        + "FROM SALES.NATION\n"
        + "WHERE n_name NOT IN\n"
        + "    (SELECT ''\n"
        + "     FROM SALES.NATION)";
    sql(sql).withDynamicTable().ok();
  }

  /** As {@link #testSelectFromDynamicTable} but with ORDER BY. */
  @Test void testReferDynamicStarInSelectOB() {
    final String sql = "select n_nationkey, n_name\n"
        + "from (select * from SALES.NATION)\n"
        + "order by n_regionkey";
    sql(sql).withDynamicTable().ok();
  }

  /** As {@link #testSelectFromDynamicTable} but with join. */
  @Test void testDynamicStarInTableJoin() {
    final String sql = "select * from "
        + " (select * from SALES.NATION) T1, "
        + " (SELECT * from SALES.CUSTOMER) T2 "
        + " where T1.n_nationkey = T2.c_nationkey";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testDynamicNestedColumn() {
    final String sql = "select t3.fake_q1['fake_col2'] as fake2\n"
        + "from (\n"
        + "  select t2.fake_col as fake_q1\n"
        + "  from SALES.CUSTOMER as t2) as t3";
    sql(sql).withDynamicTable().ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2900">[CALCITE-2900]
   * RelStructuredTypeFlattener generates wrong types on nested columns</a>. */
  @Test void testNestedColumnType() {
    final String sql = "select empa.home_address.zip\n"
        + "from sales.emp_address empa\n"
        + "where empa.home_address.city = 'abc'";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2962">[CALCITE-2962]
   * RelStructuredTypeFlattener generates wrong types for nested column when
   * flattenProjection</a>.
   */
  @Test void testSelectNestedColumnType() {
    final String sql = "select\n"
        + "  char_length(coord.\"unit\") as unit_length\n"
        + "from\n"
        + "  (\n"
        + "    select\n"
        + "      fname,\n"
        + "      coord\n"
        + "    from\n"
        + "      customer.contact_peek\n"
        + "    where\n"
        + "      coord.x > 1\n"
        + "      and coord.y > 1\n"
        + "  ) as view\n"
        + "where\n"
        + "  fname = 'john'";
    sql(sql).ok();
  }

  @Test void testNestedStructFieldAccess() {
    final String sql = "select dn.skill['others']\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  @Test void testNestedStructPrimitiveFieldAccess() {
    final String sql = "select dn.skill['others']['a']\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  @Test void testFunctionWithStructInput() {
    final String sql = "select json_type(skill)\n"
        + "from sales.dept_nested";
    sql(sql).ok();
  }

  @Test void testAggregateFunctionForStructInput() {
    final String sql = "select collect(skill) as collect_skill,\n"
        + "  count(skill) as count_skill, count(*) as count_star,\n"
        + "  approx_count_distinct(skill) as approx_count_distinct_skill,\n"
        + "  max(skill) as max_skill, min(skill) as min_skill,\n"
        + "  any_value(skill) as any_value_skill\n"
        + "from sales.dept_nested";
    sql(sql).ok();
  }

  @Test void testAggregateFunctionForStructInputByName() {
    final String sql = "select collect(skill) as collect_skill,\n"
        + "  count(skill) as count_skill, count(*) as count_star,\n"
        + "  approx_count_distinct(skill) as approx_count_distinct_skill,\n"
        + "  max(skill) as max_skill, min(skill) as min_skill,\n"
        + "  any_value(skill) as any_value_skill\n"
        + "from sales.dept_nested group by name";
    sql(sql).ok();
  }

  @Test void testNestedPrimitiveFieldAccess() {
    final String sql = "select dn.skill['desc']\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  @Test void testArrayElementNestedPrimitive() {
    final String sql = "select dn.employees[0]['empno']\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  @Test void testArrayElementDoublyNestedPrimitive() {
    final String sql = "select dn.employees[0]['detail']['skills'][0]['type']\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  @Test void testArrayElementDoublyNestedStruct() {
    final String sql = "select dn.employees[0]['detail']['skills'][0]\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  @Test void testArrayElementThreeTimesNestedStruct() {
    final String sql = ""
        + "select dn.employees[0]['detail']['skills'][0]['others']\n"
        + "from sales.dept_nested dn";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3003">[CALCITE-3003]
   * AssertionError when GROUP BY nested field</a>.
   */
  @Test void testGroupByNestedColumn() {
    final String sql =
        "select\n"
            + "  coord.x,\n"
            + "  coord_ne.sub.a,\n"
            + "  avg(coord.y)\n"
            + "from\n"
            + "  customer.contact_peek\n"
            + "group by\n"
            + "  coord_ne.sub.a,\n"
            + "  coord.x";
    sql(sql).ok();
  }

  /**
   * Similar to {@link #testGroupByNestedColumn()},
   * but with grouping sets.
   */
  @Test void testGroupingSetsWithNestedColumn() {
    final String sql =
        "select\n"
            + "  coord.x,\n"
            + "  coord.\"unit\",\n"
            + "  coord_ne.sub.a,\n"
            + "  avg(coord.y)\n"
            + "from\n"
            + "  customer.contact_peek\n"
            + "group by\n"
            + "  grouping sets (\n"
            + "    (coord_ne.sub.a, coord.x, coord.\"unit\"),\n"
            + "    (coord.x, coord.\"unit\")\n"
            + "  )";
    sql(sql).ok();
  }

  /**
   * Similar to {@link #testGroupByNestedColumn()},
   * but with cube.
   */
  @Test void testGroupByCubeWithNestedColumn() {
    final String sql =
        "select\n"
            + "  coord.x,\n"
            + "  coord.\"unit\",\n"
            + "  coord_ne.sub.a,\n"
            + "  avg(coord.y)\n"
            + "from\n"
            + "  customer.contact_peek\n"
            + "group by\n"
            + "  cube (coord_ne.sub.a, coord.x, coord.\"unit\")";
    sql(sql).ok();
  }

  @Test void testDynamicSchemaUnnest() {
    final String sql = "select t1.c_nationkey, t3.fake_col3\n"
        + "from SALES.CUSTOMER as t1,\n"
        + "lateral (select t2 as fake_col3\n"
        + "         from unnest(t1.fake_col) as t2) as t3";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testStarDynamicSchemaUnnest() {
    final String sql = "select *\n"
        + "from SALES.CUSTOMER as t1,\n"
        + "lateral (select t2 as fake_col3\n"
        + "         from unnest(t1.fake_col) as t2) as t3";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testStarDynamicSchemaUnnest2() {
    final String sql = "select *\n"
        + "from SALES.CUSTOMER as t1,\n"
        + "unnest(t1.fake_col) as t2";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testStarDynamicSchemaUnnestNestedSubQuery() {
    String sql = "select t2.c1\n"
        + "from (select * from SALES.CUSTOMER) as t1,\n"
        + "unnest(t1.fake_col) as t2(c1)";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testReferDynamicStarInSelectWhereGB() {
    final String sql = "select n_regionkey, count(*) as cnt from "
        + "(select * from SALES.NATION) where n_nationkey > 5 "
        + "group by n_regionkey";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testDynamicStarInJoinAndSubQ() {
    final String sql = "select * from "
        + " (select * from SALES.NATION T1, "
        + " SALES.CUSTOMER T2 where T1.n_nationkey = T2.c_nationkey)";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testStarJoinStaticDynTable() {
    final String sql = "select * from SALES.NATION N, SALES.REGION as R "
        + "where N.n_regionkey = R.r_regionkey";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testGrpByColFromStarInSubQuery() {
    final String sql = "SELECT n.n_nationkey AS col "
        + " from (SELECT * FROM SALES.NATION) as n "
        + " group by n.n_nationkey";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testDynStarInExistSubQ() {
    final String sql = "select *\n"
        + "from SALES.REGION where exists (select * from SALES.NATION)";
    sql(sql).withDynamicTable().ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1150">[CALCITE-1150]
   * Create the a new DynamicRecordType, avoiding star expansion when working
   * with this type</a>. */
  @Test void testSelectDynamicStarOrderBy() {
    final String sql = "SELECT * from SALES.NATION order by n_nationkey";
    sql(sql).withDynamicTable().ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1321">[CALCITE-1321]
   * Configurable IN list size when converting IN clause to join</a>. */
  @Test void testInToSemiJoin() {
    final String sql = "SELECT empno\n"
        + "FROM emp AS e\n"
        + "WHERE cast(e.empno as bigint) in (130, 131, 132, 133, 134)";
    // No conversion to join since less than IN-list size threshold 10
    sql(sql).withConfig(b -> b.withInSubQueryThreshold(10))
        .convertsTo("${planNotConverted}");
    // Conversion to join since greater than IN-list size threshold 2
    sql(sql).withConfig(b -> b.withInSubQueryThreshold(2))
        .convertsTo("${planConverted}");
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4683">[CALCITE-4683]
   * IN-list converted to JOIN throws type mismatch exception</a>. */
  @Test void testInToSemiJoinWithNewProject() {
    final String sql = "SELECT * FROM (\n"
        + "SELECT '20210101' AS dt, deptno\n"
        + "FROM emp\n"
        + "GROUP BY deptno\n"
        + ") t\n"
        + "WHERE cast(deptno as varchar) in ('1')";
    sql(sql).withConfig(c -> c.withInSubQueryThreshold(0)).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1944">[CALCITE-1944]
   * Window function applied to sub-query with dynamic star gets wrong
   * plan</a>. */
  @Test void testWindowOnDynamicStar() {
    final String sql = "SELECT SUM(n_nationkey) OVER w\n"
        + "FROM (SELECT * FROM SALES.NATION) subQry\n"
        + "WINDOW w AS (PARTITION BY REGION ORDER BY n_nationkey)";
    sql(sql).withDynamicTable().ok();
  }

  @Test void testWindowAndGroupByWithDynamicStar() {
    final String sql = "SELECT\n"
        + "n_regionkey,\n"
        + "MAX(MIN(n_nationkey)) OVER (PARTITION BY n_regionkey)\n"
        + "FROM (SELECT * FROM SALES.NATION)\n"
        + "GROUP BY n_regionkey";
    final SqlConformance conformance =
        new SqlDelegatingConformance(SqlConformanceEnum.DEFAULT) {
      @Override public boolean isGroupByAlias() {
        return true;
      }
    };
    sql(sql).withConformance(conformance).withDynamicTable().ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2366">[CALCITE-2366]
   * Add support for ANY_VALUE aggregate function</a>. */
  @Test void testAnyValueAggregateFunctionNoGroupBy() {
    final String sql = "SELECT any_value(empno) as anyempno FROM emp AS e";
    sql(sql).ok();
  }

  @Test void testAnyValueAggregateFunctionGroupBy() {
    final String sql = "SELECT any_value(empno) as anyempno FROM emp AS e group by e.sal";
    sql(sql).ok();
  }

  @Test void testSomeAndEveryAggregateFunctions() {
    final String sql = "SELECT some(empno = 130) as someempnoexists,\n"
        + " every(empno > 0) as everyempnogtzero\n"
        + " FROM emp AS e group by e.sal";
    sql(sql).ok();
  }

  @Test void testLarge() {
    // Size factor used to be 400, but lambdas use a lot of stack
    final int x = 300;
    final SqlToRelFixture fixture = fixture();
    SqlValidatorTest.checkLarge(x, input -> {
      final RelRoot root = fixture.withSql(input).toRoot();
      final String s = RelOptUtil.toString(root.project());
      assertThat(s, notNullValue());
    });
  }

  @Test void testUnionInFrom() {
    final String sql = "select x0, x1 from (\n"
        + "  select 'a' as x0, 'a' as x1, 'a' as x2 from emp\n"
        + "  union all\n"
        + "  select 'bb' as x0, 'bb' as x1, 'bb' as x2 from dept)";
    sql(sql).ok();
  }

  @Test void testPivot() {
    final String sql = "SELECT *\n"
        + "FROM (SELECT mgr, deptno, job, sal FROM emp)\n"
        + "PIVOT (SUM(sal) AS ss, COUNT(*)\n"
        + "    FOR (job, deptno)\n"
        + "    IN (('CLERK', 10) AS c10, ('MANAGER', 20) AS m20))";
    sql(sql).ok();
  }

  @Test void testPivot2() {
    final String sql = "SELECT *\n"
        + "FROM   (SELECT deptno, job, sal\n"
        + "        FROM   emp)\n"
        + "PIVOT  (SUM(sal) AS sum_sal, COUNT(*) AS \"COUNT\"\n"
        + "        FOR (job) IN ('CLERK', 'MANAGER' mgr, 'ANALYST' AS \"a\"))\n"
        + "ORDER BY deptno";
    sql(sql).ok();
  }

  @Test void testUnpivot() {
    final String sql = "SELECT * FROM emp\n"
        + "UNPIVOT INCLUDE NULLS (remuneration\n"
        + "  FOR remuneration_type IN (comm AS 'commission',\n"
        + "                            sal as 'salary'))";
    sql(sql).ok();
  }

  @Test void testMatchRecognize1() {
    final String sql = "select *\n"
        + "  from emp match_recognize\n"
        + "  (\n"
        + "    partition by job, sal\n"
        + "    order by job asc, sal desc, empno\n"
        + "    pattern (strt down+ up+)\n"
        + "    define\n"
        + "      down as down.mgr < PREV(down.mgr),\n"
        + "      up as up.mgr > prev(up.mgr)) as mr";
    sql(sql).ok();
  }

  @Test void testMatchRecognizeMeasures1() {
    final String sql = "select *\n"
        + "from emp match_recognize (\n"
        + "  partition by job, sal\n"
        + "  order by job asc, sal desc\n"
        + "  measures MATCH_NUMBER() as match_num,\n"
        + "    CLASSIFIER() as var_match,\n"
        + "    STRT.mgr as start_nw,\n"
        + "    LAST(DOWN.mgr) as bottom_nw,\n"
        + "    LAST(up.mgr) as end_nw\n"
        + "  pattern (strt down+ up+)\n"
        + "  define\n"
        + "    down as down.mgr < PREV(down.mgr),\n"
        + "    up as up.mgr > prev(up.mgr)) as mr";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-1909">[CALCITE-1909]
   * Output rowType of Match should include PARTITION BY and ORDER BY
   * columns</a>. */
  @Test void testMatchRecognizeMeasures2() {
    final String sql = "select *\n"
        + "from emp match_recognize (\n"
        + "  partition by job\n"
        + "  order by sal\n"
        + "  measures MATCH_NUMBER() as match_num,\n"
        + "    CLASSIFIER() as var_match,\n"
        + "    STRT.mgr as start_nw,\n"
        + "    LAST(DOWN.mgr) as bottom_nw,\n"
        + "    LAST(up.mgr) as end_nw\n"
        + "  pattern (strt down+ up+)\n"
        + "  define\n"
        + "    down as down.mgr < PREV(down.mgr),\n"
        + "    up as up.mgr > prev(up.mgr)) as mr";
    sql(sql).ok();
  }

  @Test void testMatchRecognizeMeasures3() {
    final String sql = "select *\n"
        + "from emp match_recognize (\n"
        + "  partition by job\n"
        + "  order by sal\n"
        + "  measures MATCH_NUMBER() as match_num,\n"
        + "    CLASSIFIER() as var_match,\n"
        + "    STRT.mgr as start_nw,\n"
        + "    LAST(DOWN.mgr) as bottom_nw,\n"
        + "    LAST(up.mgr) as end_nw\n"
        + "  ALL ROWS PER MATCH\n"
        + "  pattern (strt down+ up+)\n"
        + "  define\n"
        + "    down as down.mgr < PREV(down.mgr),\n"
        + "    up as up.mgr > prev(up.mgr)) as mr";
    sql(sql).ok();
  }

  @Test void testMatchRecognizePatternSkip1() {
    final String sql = "select *\n"
        + "  from emp match_recognize\n"
        + "  (\n"
        + "    after match skip to next row\n"
        + "    pattern (strt down+ up+)\n"
        + "    define\n"
        + "      down as down.mgr < PREV(down.mgr),\n"
        + "      up as up.mgr > NEXT(up.mgr)\n"
        + "  ) mr";
    sql(sql).ok();
  }

  @Test void testMatchRecognizeSubset1() {
    final String sql = "select *\n"
        + "  from emp match_recognize\n"
        + "  (\n"
        + "    after match skip to down\n"
        + "    pattern (strt down+ up+)\n"
        + "    subset stdn = (strt, down)\n"
        + "    define\n"
        + "      down as down.mgr < PREV(down.mgr),\n"
        + "      up as up.mgr > NEXT(up.mgr)\n"
        + "  ) mr";
    sql(sql).ok();
  }

  @Test void testMatchRecognizePrevLast() {
    final String sql = "SELECT *\n"
        + "FROM emp\n"
        + "MATCH_RECOGNIZE (\n"
        + "  MEASURES\n"
        + "    STRT.mgr AS start_mgr,\n"
        + "    LAST(DOWN.mgr) AS bottom_mgr,\n"
        + "    LAST(UP.mgr) AS end_mgr\n"
        + "  ONE ROW PER MATCH\n"
        + "  PATTERN (STRT DOWN+ UP+)\n"
        + "  DEFINE\n"
        + "    DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n"
        + "    UP AS UP.mgr > PREV(LAST(DOWN.mgr, 1), 1)\n"
        + ") AS T";
    sql(sql).ok();
  }

  @Test void testMatchRecognizePrevDown() {
    final String sql = "SELECT *\n"
        + "FROM emp\n"
        + "MATCH_RECOGNIZE (\n"
        + "  MEASURES\n"
        + "    STRT.mgr AS start_mgr,\n"
        + "    LAST(DOWN.mgr) AS up_days,\n"
        + "    LAST(UP.mgr) AS total_days\n"
        + "  PATTERN (STRT DOWN+ UP+)\n"
        + "  DEFINE\n"
        + "    DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n"
        + "    UP AS UP.mgr > PREV(DOWN.mgr)\n"
        + ") AS T";
    sql(sql).ok();
  }

  @Test void testPrevClassifier() {
    final String sql = "SELECT *\n"
        + "FROM emp\n"
        + "MATCH_RECOGNIZE (\n"
        + "  MEASURES\n"
        + "    STRT.mgr AS start_mgr,\n"
        + "    LAST(DOWN.mgr) AS up_days,\n"
        + "    LAST(UP.mgr) AS total_days\n"
        + "  PATTERN (STRT DOWN? UP+)\n"
        + "  DEFINE\n"
        + "    DOWN AS DOWN.mgr < PREV(DOWN.mgr),\n"
        + "    UP AS CASE\n"
        + "            WHEN PREV(CLASSIFIER()) = 'STRT'\n"
        + "              THEN UP.mgr > 15\n"
        + "            ELSE\n"
        + "              UP.mgr > 20\n"
        + "            END\n"
        + ") AS T";
    sql(sql).ok();
  }

  @Test void testMatchRecognizeIn() {
    final String sql = "select *\n"
        + "  from emp match_recognize\n"
        + "  (\n"
        + "    partition by job, sal\n"
        + "    order by job asc, sal desc, empno\n"
        + "    pattern (strt down+ up+)\n"
        + "    define\n"
        + "      down as down.mgr in (0, 1),\n"
        + "      up as up.mgr > prev(up.mgr)) as mr";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2323">[CALCITE-2323]
   * Validator should allow alternative nullCollations for ORDER BY in
   * OVER</a>. */
  @Test void testUserDefinedOrderByOver() {
    String sql = "select deptno,\n"
        + "  rank() over(partition by empno order by deptno)\n"
        + "from emp\n"
        + "order by row_number() over(partition by empno order by deptno)";
    Properties properties = new Properties();
    properties.setProperty(
        CalciteConnectionProperty.DEFAULT_NULL_COLLATION.camelName(),
        NullCollation.LOW.name());
    CalciteConnectionConfigImpl connectionConfig =
        new CalciteConnectionConfigImpl(properties);
    sql(sql)
        .withDecorrelate(false)
        .withTrim(false)
        .withFactory(f ->
                f.withValidatorConfig(c ->
                    c.withDefaultNullCollation(
                        connectionConfig.defaultNullCollation())))
        .ok();
  }

  @Test void testJsonValueExpressionOperator() {
    final String sql = "select ename format json,\n"
        + "ename format json encoding utf8,\n"
        + "ename format json encoding utf16,\n"
        + "ename format json encoding utf32\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonExists() {
    final String sql = "select json_exists(ename, 'lax $')\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonValue() {
    final String sql = "select json_value(ename, 'lax $')\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonQuery() {
    final String sql = "select json_query(ename, 'lax $')\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonType() {
    final String sql = "select json_type(ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonPretty() {
    final String sql = "select json_pretty(ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonDepth() {
    final String sql = "select json_depth(ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonLength() {
    final String sql = "select json_length(ename, 'strict $')\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonKeys() {
    final String sql = "select json_keys(ename, 'strict $')\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonArray() {
    final String sql = "select json_array(ename, ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonArrayAgg1() {
    final String sql = "select json_arrayagg(ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonArrayAgg2() {
    final String sql = "select json_arrayagg(ename order by ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonArrayAgg3() {
    final String sql = "select json_arrayagg(ename order by ename null on null)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonArrayAgg4() {
    final String sql = "select json_arrayagg(ename null on null) within group (order by ename)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonObject() {
    final String sql = "select json_object(ename: deptno, ename: deptno)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonObjectAgg() {
    final String sql = "select json_objectagg(ename: deptno)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonPredicate() {
    final String sql = "select\n"
        + "ename is json,\n"
        + "ename is json value,\n"
        + "ename is json object,\n"
        + "ename is json array,\n"
        + "ename is json scalar,\n"
        + "ename is not json,\n"
        + "ename is not json value,\n"
        + "ename is not json object,\n"
        + "ename is not json array,\n"
        + "ename is not json scalar\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonNestedJsonObjectConstructor() {
    final String sql = "select\n"
        + "json_object(\n"
        + "  'key1' :\n"
        + "  json_object(\n"
        + "    'key2' :\n"
        + "    ename)),\n"
        + "  json_object(\n"
        + "    'key3' :\n"
        + "    json_array(12, 'hello', deptno))\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonNestedJsonArrayConstructor() {
    final String sql = "select\n"
        + "json_array(\n"
        + "  json_object(\n"
        + "    'key1' :\n"
        + "    json_object(\n"
        + "      'key2' :\n"
        + "       ename)),\n"
        + "  json_array(12, 'hello', deptno))\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonNestedJsonObjectAggConstructor() {
    final String sql = "select\n"
        + "json_object(\n"
        + "  'k2' :\n"
        + "  json_objectagg(\n"
        + "    ename :\n"
        + "    json_object(\n"
        + "      'k1' :\n"
        + "      deptno)))\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testJsonNestedJsonArrayAggConstructor() {
    final String sql = "select\n"
        + "json_object(\n"
        + "  'k2' :\n"
        + "  json_arrayagg(\n"
        + "    json_object(\n"
        + "      ename :\n"
        + "      deptno)))\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testWithinGroup1() {
    final String sql = "select deptno,\n"
        + " collect(empno) within group (order by deptno, hiredate desc)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testWithinGroup2() {
    final String sql = "select dept.deptno,\n"
        + " collect(sal) within group (order by sal desc) as s,\n"
        + " collect(sal) within group (order by 1)as s1,\n"
        + " collect(sal) within group (order by sal)\n"
        + "  filter (where sal > 2000) as s2\n"
        + "from emp\n"
        + "join dept using (deptno)\n"
        + "group by dept.deptno";
    sql(sql).ok();
  }

  @Test void testWithinGroup3() {
    final String sql = "select deptno,\n"
        + " collect(empno) within group (order by empno not in (1, 2)), count(*)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testModeFunction() {
    final String sql = "select mode(deptno)\n"
        + "from emp";
    sql(sql).withTrim(true).ok();
  }

  @Test void testModeFunctionWithWinAgg() {
    final String sql = "select deptno, ename,\n"
        + "  mode(job) over (partition by deptno order by ename)\n"
        + "from emp";
    sql(sql).withTrim(true).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4644">[CALCITE-4644]
   * Add PERCENTILE_CONT and PERCENTILE_DISC aggregate functions</a>. */
  @Test void testPercentileCont() {
    final String sql = "select\n"
        + " percentile_cont(0.25) within group (order by deptno)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testPercentileContWithGroupBy() {
    final String sql = "select deptno,\n"
        + " percentile_cont(0.25) within group (order by empno desc)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testPercentileDisc() {
    final String sql = "select\n"
        + " percentile_disc(0.25) within group (order by deptno)\n"
        + "from emp";
    sql(sql).ok();
  }

  @Test void testPercentileDiscWithGroupBy() {
    final String sql = "select deptno,\n"
        + " percentile_disc(0.25) within group (order by empno)\n"
        + "from emp\n"
        + "group by deptno";
    sql(sql).ok();
  }

  @Test void testOrderByRemoval1() {
    final String sql = "select * from (\n"
        + "  select empno from emp order by deptno offset 0) t\n"
        + "order by empno desc";
    sql(sql).ok();
  }

  @Test void testOrderByRemoval2() {
    final String sql = "select * from (\n"
        + "  select empno from emp order by deptno offset 1) t\n"
        + "order by empno desc";
    sql(sql).ok();
  }

  @Test void testOrderByRemoval3() {
    final String sql = "select * from (\n"
        + "  select empno from emp order by deptno limit 10) t\n"
        + "order by empno";
    sql(sql).ok();
  }

  /** Tests LEFT JOIN LATERAL with USING. */
  @Test void testLeftJoinLateral1() {
    final String sql = "select * from (values 4) as t(c)\n"
        + " left join lateral\n"
        + " (select c,a*c from (values 2) as s(a)) as r(d,c)\n"
        + " using(c)";
    sql(sql).ok();
  }

  /** Tests LEFT JOIN LATERAL with NATURAL JOIN. */
  @Test void testLeftJoinLateral2() {
    final String sql = "select * from (values 4) as t(c)\n"
        + " natural left join lateral\n"
        + " (select c,a*c from (values 2) as s(a)) as r(d,c)";
    sql(sql).ok();
  }

  /** Tests LEFT JOIN LATERAL with ON condition. */
  @Test void testLeftJoinLateral3() {
    final String sql = "select * from (values 4) as t(c)\n"
        + " left join lateral\n"
        + " (select c,a*c from (values 2) as s(a)) as r(d,c)\n"
        + " on t.c=r.c";
    sql(sql).ok();
  }

  /** Tests LEFT JOIN LATERAL with multiple columns from outer. */
  @Test void testLeftJoinLateral4() {
    final String sql = "select * from (values (4,5)) as t(c,d)\n"
        + " left join lateral\n"
        + " (select c,a*c from (values 2) as s(a)) as r(d,c)\n"
        + " on t.c+t.d=r.c";
    sql(sql).ok();
  }

  /** Tests LEFT JOIN LATERAL with correlating variable coming
   * from one level up join scope. */
  @Test void testLeftJoinLateral5() {
    final String sql = "select * from (values 4) as t (c)\n"
        + "left join lateral\n"
        + "  (select f1+b1 from (values 2) as foo(f1)\n"
        + "    join\n"
        + "  (select c+1 from (values 3)) as bar(b1)\n"
        + "  on f1=b1)\n"
        + "as r(n) on c=n";
    sql(sql).ok();
  }

  /** Tests CROSS JOIN LATERAL with multiple columns from outer. */
  @Test void testCrossJoinLateral1() {
    final String sql = "select * from (values (4,5)) as t(c,d)\n"
        + " cross join lateral\n"
        + " (select c,a*c as f from (values 2) as s(a)\n"
        + " where c+d=a*c)";
    sql(sql).ok();
  }

  /** Tests CROSS JOIN LATERAL with correlating variable coming
   * from one level up join scope. */
  @Test void testCrossJoinLateral2() {
    final String sql = "select * from (values 4) as t (c)\n"
        + "cross join lateral\n"
        + "(select * from (\n"
        + "  select f1+b1 from (values 2) as foo(f1)\n"
        + "    join\n"
        + "  (select c+1 from (values 3)) as bar(b1)\n"
        + "  on f1=b1\n"
        + ") as r(n) where c=n)";
    sql(sql).ok();
  }

  @Test void testWithinDistinct1() {
    final String sql = "select avg(empno) within distinct (deptno)\n"
        + "from emp";
    sql(sql).ok();
  }

  /** Test case for:
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3310">[CALCITE-3310]
   * Approximate and exact aggregate calls are recognized as the same
   * during sql-to-rel conversion</a>.
   */
  @Test void testProjectApproximateAndExactAggregates() {
    final String sql = "SELECT empno, count(distinct ename),\n"
            + "approx_count_distinct(ename)\n"
            + "FROM emp\n"
            + "GROUP BY empno";
    sql(sql).ok();
  }

  @Test void testProjectAggregatesIgnoreNullsAndNot() {
    final String sql = "select lead(sal, 4) IGNORE NULLS, lead(sal, 4) over (w)\n"
        + "from emp window w as (order by empno)";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3456">[CALCITE-3456]
   * AssertionError throws when aggregation same digest in sub-query in same
   * scope</a>.
   */
  @Test void testAggregateWithSameDigestInSubQueries() {
    final String sql = "select\n"
        + "  CASE WHEN job IN ('810000', '820000') THEN job\n"
        + "  ELSE 'error'\n"
        + "  END AS job_name,\n"
        + "  count(empno)\n"
        + "FROM emp\n"
        + "where job <> '' or job IN ('810000', '820000')\n"
        + "GROUP by deptno, job";
    sql(sql)
        .withConfig(c ->
            c.addRelBuilderConfigTransform(c2 ->
                c2.withPruneInputOfAggregate(false)))
        .ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3575">[CALCITE-3575]
   * IndexOutOfBoundsException when converting SQL to rel</a>. */
  @Test void testPushDownJoinConditionWithProjectMerge() {
    final String sql = "select * from\n"
        + " (select empno, deptno from emp) a\n"
        + " join dept b\n"
        + "on a.deptno + 20 = b.deptno";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-2997">[CALCITE-2997]
   * Avoid pushing down join condition in SqlToRelConverter</a>. */
  @Test void testDoNotPushDownJoinCondition() {
    final String sql = "select *\n"
        + "from emp as e\n"
        + "join dept as d on e.deptno + 20 = d.deptno / 2";
    sql(sql).withConfig(c ->
        c.addRelBuilderConfigTransform(b ->
            b.withPushJoinCondition(false)))
        .ok();
  }

  /** As {@link #testDoNotPushDownJoinCondition()}. */
  @Test void testPushDownJoinCondition() {
    final String sql = "select *\n"
        + "from emp as e\n"
        + "join dept as d on e.deptno + 20 = d.deptno / 2";
    sql(sql).ok();
  }

  @Test void testCoalesceOnNullableField() {
    final String sql = "select coalesce(mgr, 0) from emp";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4145">[CALCITE-4145]
   * Exception when query from UDF field with structured type</a>.
   */
  @Test void testUdfWithStructuredReturnType() {
    final String sql = "SELECT deptno, tmp.r.f0, tmp.r.f1 FROM\n"
        + "(SELECT deptno, STRUCTURED_FUNC() AS r from dept)tmp";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-3826">[CALCITE-3826]
   * UPDATE assigns wrong type to bind variables</a>.
   */
  @Test void testDynamicParamTypesInUpdate() {
    RelNode rel =
        sql("update emp set sal = ?, ename = ? where empno = ?").toRel();
    LogicalTableModify modify = (LogicalTableModify) rel;
    List<RexNode> parameters = modify.getSourceExpressionList();
    assertThat(parameters, notNullValue());
    assertThat(parameters.size(), is(2));
    assertThat(parameters.get(0).getType().getSqlTypeName(), is(SqlTypeName.INTEGER));
    assertThat(parameters.get(1).getType().getSqlTypeName(), is(SqlTypeName.VARCHAR));
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4167">[CALCITE-4167]
   * Group by COALESCE IN throws NullPointerException</a>.
   */
  @Test void testGroupByCoalesceIn() {
    final String sql = "select case when coalesce(ename, 'a') in ('1', '2')\n"
        + "then 'CKA' else 'QT' END, count(distinct deptno) from emp\n"
        + "group by case when coalesce(ename, 'a') in ('1', '2') then 'CKA' else 'QT' END";
    sql(sql).ok();
  }

  @Test void testSortInSubQuery() {
    final String sql = "select * from (select empno from emp order by empno)";
    sql(sql).convertsTo("${planRemoveSort}");
    sql(sql).withConfig(c -> c.withRemoveSortInSubQuery(false)).convertsTo("${planKeepSort}");
  }

  @Test void testTrimUnionAll() {
    final String sql = ""
        + "select deptno from\n"
        + "(select ename, deptno from emp\n"
        + "union all\n"
        + "select name, deptno from dept)";
    sql(sql).withTrim(true).ok();
  }

  @Test void testTrimUnionDistinct() {
    final String sql = ""
        + "select deptno from\n"
        + "(select ename, deptno from emp\n"
        + "union\n"
        + "select name, deptno from dept)";
    sql(sql).withTrim(true).ok();
  }

  @Test void testTrimIntersectAll() {
    final String sql = ""
        + "select deptno from\n"
        + "(select ename, deptno from emp\n"
        + "intersect all\n"
        + "select name, deptno from dept)";
    sql(sql).withTrim(true).ok();
  }

  @Test void testTrimIntersectDistinct() {
    final String sql = ""
        + "select deptno from\n"
        + "(select ename, deptno from emp\n"
        + "intersect\n"
        + "select name, deptno from dept)";
    sql(sql).withTrim(true).ok();
  }

  @Test void testTrimExceptAll() {
    final String sql = ""
        + "select deptno from\n"
        + "(select ename, deptno from emp\n"
        + "except all\n"
        + "select name, deptno from dept)";
    sql(sql).withTrim(true).ok();
  }

  @Test void testTrimExceptDistinct() {
    final String sql = ""
        + "select deptno from\n"
        + "(select ename, deptno from emp\n"
        + "except\n"
        + "select name, deptno from dept)";
    sql(sql).withTrim(true).ok();
  }

  @Test void testJoinWithOnConditionQuery() {
    String sql = ""
        + "SELECT emp.deptno, emp.sal\n"
        + "FROM dept\n"
        + "JOIN emp\n"
        + "ON (SELECT AVG(emp.sal) > 0 FROM emp)";
    sql(sql).ok();
  }

  @Test void testJoinExpandAndDecorrelation() {
    String sql = ""
        + "SELECT emp.deptno, emp.sal\n"
        + "FROM dept\n"
        + "JOIN emp ON emp.deptno = dept.deptno AND emp.sal < (\n"
        + "  SELECT AVG(emp.sal)\n"
        + "  FROM emp\n"
        + "  WHERE  emp.deptno = dept.deptno\n"
        + ")";
    sql(sql)
        .withConfig(configBuilder -> configBuilder
            .withExpand(true)
            .withDecorrelationEnabled(true))
        .convertsTo("${planExpanded}");
    sql(sql)
        .withConfig(configBuilder -> configBuilder
            .withExpand(false)
            .withDecorrelationEnabled(false))
        .convertsTo("${planNotExpanded}");
  }

  @Test void testImplicitJoinExpandAndDecorrelation() {
    String sql = ""
        + "SELECT emp.deptno, emp.sal\n"
        + "FROM dept, emp "
        + "WHERE emp.deptno = dept.deptno AND emp.sal < (\n"
        + "  SELECT AVG(emp.sal)\n"
        + "  FROM emp\n"
        + "  WHERE  emp.deptno = dept.deptno\n"
        + ")";
    sql(sql).withExpand(true).withDecorrelate(true)
        .convertsTo("${planExpanded}");
    sql(sql).withExpand(false).withDecorrelate(false)
        .convertsTo("${planNotExpanded}");
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-4295">[CALCITE-4295]
   * Composite of two checker with SqlOperandCountRange throws IllegalArgumentException</a>.
   */
  @Test void testCompositeOfCountRange() {
    final String sql = ""
        + "select COMPOSITE(deptno)\n"
        + "from dept";
    sql(sql).withTrim(true).ok();
  }

  @Test void testInWithConstantList() {
    String expr = "1 in (1,2,3)";
    expr(expr).ok();
  }

  @Test void testFunctionExprInOver() {
    String sql = "select ename, row_number() over(partition by char_length(ename)\n"
        + " order by deptno desc) as rn\n"
        + "from emp\n"
        + "where deptno = 10";
    sql(sql)
        .withFactory(t ->
            t.withValidatorConfig(config ->
                config.withIdentifierExpansion(false)))
        .withTrim(false)
        .ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-5089">[CALCITE-5089]
   * Allow GROUP BY ALL or DISTINCT set quantifier on GROUPING SETS</a>. */
  @Test void testGroupByDistinct() {
    final String sql = "SELECT deptno, job, count(*)\n"
        + "FROM emp\n"
        + "GROUP BY DISTINCT\n"
        + "CUBE (deptno, job),\n"
        + "ROLLUP (deptno, job)";
    sql(sql).ok();
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-5089">[CALCITE-5089]
   * Allow GROUP BY ALL or DISTINCT set quantifier on GROUPING SETS</a>. */
  @Test void testGroupByAll() {
    final String sql = "SELECT deptno, job, count(*)\n"
        + "FROM emp\n"
        + "GROUP BY ALL\n"
        + "CUBE (deptno, job),\n"
        + "ROLLUP (deptno, job)";
    sql(sql).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-5045">[CALCITE-5045]
   * Alias within GroupingSets throws type mis-match exception</a>.
   */
  @Test void testAliasWithinGroupingSets() {
    final String sql = "SELECT empno / 2 AS x\n"
        + "FROM emp\n"
        + "GROUP BY ROLLUP(x)";
    sql(sql)
        .withConformance(SqlConformanceEnum.LENIENT)
        .ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-5145">[CALCITE-5145]
   * CASE statement within GROUPING SETS throws type mis-match exception</a>.
   */
  @Test public void testCaseAliasWithinGroupingSets() {
    sql("SELECT empno,\n"
        + "CASE\n"
        + "WHEN ename in ('Fred','Eric') THEN 'CEO'\n"
        + "ELSE 'Other'\n"
        + "END AS derived_col\n"
        + "FROM emp\n"
        + "GROUP BY GROUPING SETS ((empno, derived_col),(empno))")
        .withConformance(SqlConformanceEnum.LENIENT).ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-5145">[CALCITE-5145]
   * CASE statement within GROUPING SETS throws type mis-match exception</a>.
   */
  @Test void testCaseWithinGroupingSets() {
    String sql = "SELECT empno,\n"
        + "CASE WHEN ename IN ('Fred','Eric') THEN 'Manager' ELSE 'Other' END\n"
        + "FROM emp\n"
        + "GROUP BY GROUPING SETS (\n"
        + "(empno, CASE WHEN ename IN ('Fred','Eric') THEN 'Manager' ELSE 'Other' END),\n"
        + "(empno)\n"
        + ")";
    sql(sql)
        .withConformance(SqlConformanceEnum.LENIENT)
        .ok();
  }

  /**
   * Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-5297">[CALCITE-5297]
   * Casting dynamic variable twice throws exception</a>.
   */
  @Test void testDynamicParameterDoubleCast() {
    String sql = "SELECT CAST(CAST(? AS INTEGER) AS CHAR)";
    sql(sql).ok();
  }
}