content: /*
   * 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.adapter.java.ReflectiveSchema;
  import org.apache.calcite.avatica.util.DateTimeUtils;
  import org.apache.calcite.config.Lex;
  import org.apache.calcite.jdbc.CalciteConnection;
  import org.apache.calcite.jdbc.Driver;
  import org.apache.calcite.linq4j.Enumerable;
  import org.apache.calcite.linq4j.Linq4j;
  import org.apache.calcite.linq4j.QueryProvider;
  import org.apache.calcite.linq4j.function.Function1;
  import org.apache.calcite.linq4j.tree.Expressions;
  import org.apache.calcite.linq4j.tree.ParameterExpression;
  import org.apache.calcite.linq4j.tree.Types;
  import org.apache.calcite.schema.SchemaPlus;
  import org.apache.calcite.schema.Statistic;
  import org.apache.calcite.schema.Table;
  import org.apache.calcite.schema.impl.AbstractSchema;
  import org.apache.calcite.schema.impl.TableMacroImpl;
  import org.apache.calcite.schema.impl.ViewTable;
  import org.apache.calcite.test.schemata.catchall.CatchallSchema;
  import org.apache.calcite.test.schemata.catchall.CatchallSchema.EveryType;
  import org.apache.calcite.test.schemata.hr.Dependent;
  import org.apache.calcite.test.schemata.hr.Employee;
  import org.apache.calcite.test.schemata.hr.HrSchema;
  import org.apache.calcite.util.Smalls;
  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.lang.reflect.Field;
  import java.lang.reflect.Method;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.Time;
  import java.sql.Timestamp;
  import java.util.Arrays;
  import java.util.BitSet;
  import java.util.List;
  import java.util.Properties;
  
  import static org.apache.calcite.test.Matchers.isListOf;
  
  import static org.hamcrest.CoreMatchers.equalTo;
  import static org.hamcrest.CoreMatchers.is;
  import static org.hamcrest.MatcherAssert.assertThat;
  import static org.hamcrest.Matchers.arrayWithSize;
  import static org.hamcrest.Matchers.hasSize;
  import static org.hamcrest.Matchers.hasToString;
  import static org.junit.jupiter.api.Assertions.assertNotNull;
  import static org.junit.jupiter.api.Assertions.assertTrue;
  import static org.junit.jupiter.api.Assertions.fail;
  
  /**
   * Unit tests for {@link ReflectiveSchema}.
   */
  public class ReflectiveSchemaTest {
    public static final Method LINQ4J_AS_ENUMERABLE_METHOD =
        Types.lookupMethod(
            Linq4j.class, "asEnumerable", Object[].class);
  
    private static final ReflectiveSchema CATCHALL =
        new ReflectiveSchema(new CatchallSchema());
  
    /**
     * Extension of HrSchema with a dummy field for testing a row count from a Collection.
     */
    public static class HrSchemaPlus extends HrSchema {
      public final ImmutableList<Dependent> xlocations =
          ImmutableList.of(new Dependent(10, "San Francisco"),
              new Dependent(20, "San Diego"));
    }
  
    /**
     * Test that uses a JDBC connection as a linq4j
     * {@link org.apache.calcite.linq4j.QueryProvider}.
     *
     * @throws Exception on error
     */
    @Test void testQueryProvider() throws Exception {
      Connection connection = CalciteAssert
          .that(CalciteAssert.Config.REGULAR).connect();
      QueryProvider queryProvider = connection.unwrap(QueryProvider.class);
      ParameterExpression e = Expressions.parameter(Employee.class, "e");
  
      // "Enumerable<T> asEnumerable(final T[] ts)"
      List<Object[]> list =
          queryProvider.createQuery(
              Expressions.call(
                  Expressions.call(
                      Types.of(Enumerable.class, Employee.class),
                      null,
                      LINQ4J_AS_ENUMERABLE_METHOD,
                      Expressions.constant(
                          new HrSchema().emps)),
                  "asQueryable"),
              Employee.class)
              .where(
                  Expressions.lambda(
                      Expressions.lessThan(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(160)),
                      e))
              .where(
                  Expressions.lambda(
                      Expressions.greaterThan(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(140)),
                      e))
              .select(
                  Expressions.<Function1<Employee, Object[]>>lambda(
                      Expressions.new_(
                          Object[].class,
                          Expressions.field(
                              e, "empid"),
                          Expressions.call(
                              Expressions.field(
                                  e, "name"),
                              "toUpperCase")),
                      e))
              .toList();
      assertThat(list, hasSize(1));
      assertThat(list.get(0), arrayWithSize(2));
      assertThat(list.get(0)[0], is(150));
      assertThat(list.get(0)[1], is("SEBASTIAN"));
    }
  
    @Test void testQueryProviderSingleColumn() throws Exception {
      Connection connection = CalciteAssert
          .that(CalciteAssert.Config.REGULAR).connect();
      QueryProvider queryProvider = connection.unwrap(QueryProvider.class);
      ParameterExpression e = Expressions.parameter(Employee.class, "e");
  
      // "Enumerable<T> asEnumerable(final T[] ts)"
      List<Integer> list =
          queryProvider.createQuery(
              Expressions.call(
                  Expressions.call(
                      Types.of(Enumerable.class, Employee.class),
                      null,
                      LINQ4J_AS_ENUMERABLE_METHOD,
                      Expressions.constant(new HrSchema().emps)),
                  "asQueryable"),
              Employee.class)
              .select(
                  Expressions.<Function1<Employee, Integer>>lambda(
                      Expressions.field(e, "empid"),
                      e))
              .toList();
      assertThat(list, isListOf(100, 200, 150, 110));
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-7014">[CALCITE-7014]
     * Support EQUAL/GreaterThanOrEqual/LessThanOrEqual expressions to RexNode
     * in CalcitePrepare</a>. */
    @Test void testQueryProviderWithFilter() throws Exception {
      Connection connection = CalciteAssert
          .that(CalciteAssert.Config.REGULAR).connect();
      QueryProvider queryProvider = connection.unwrap(QueryProvider.class);
      ParameterExpression e = Expressions.parameter(Employee.class, "e");
  
      // test where empid>150 and empid<=160, result size would be 0
      List<Object[]> list =
          queryProvider.createQuery(
                  Expressions.call(
                      Expressions.call(
                          Types.of(Enumerable.class, Employee.class),
                          null,
                          LINQ4J_AS_ENUMERABLE_METHOD,
                          Expressions.constant(
                              new HrSchema().emps)),
                      "asQueryable"),
                  Employee.class)
              .where(
                  Expressions.lambda(
                      Expressions.lessThanOrEqual(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(160)),
                      e))
              .where(
                  Expressions.lambda(
                      Expressions.greaterThan(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(150)),
                      e))
              .select(
                  Expressions.<Function1<Employee, Object[]>>lambda(
                      Expressions.new_(
                          Object[].class,
                          Expressions.field(
                              e, "empid"),
                          Expressions.call(
                              Expressions.field(
                                  e, "name"),
                              "toUpperCase")),
                      e))
              .toList();
      assertThat(list, hasSize(0));
  
      // test where empid>=150 and empid<=160, result size would be 1
      list =
          queryProvider.createQuery(
                  Expressions.call(
                      Expressions.call(
                          Types.of(Enumerable.class, Employee.class),
                          null,
                          LINQ4J_AS_ENUMERABLE_METHOD,
                          Expressions.constant(
                              new HrSchema().emps)),
                      "asQueryable"),
                  Employee.class)
              .where(
                  Expressions.lambda(
                      Expressions.lessThanOrEqual(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(160)),
                      e))
              .where(
                  Expressions.lambda(
                      Expressions.greaterThanOrEqual(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(150)),
                      e))
              .select(
                  Expressions.<Function1<Employee, Object[]>>lambda(
                      Expressions.new_(
                          Object[].class,
                          Expressions.field(
                              e, "empid"),
                          Expressions.call(
                              Expressions.field(
                                  e, "name"),
                              "toUpperCase")),
                      e))
              .toList();
      assertThat(list, hasSize(1));
      assertThat(list.get(0), arrayWithSize(2));
      assertThat(list.get(0)[0], is(150));
      assertThat(list.get(0)[1], is("SEBASTIAN"));
  
      // test where empid=150, result size would be 1
      list =
          queryProvider.createQuery(
                  Expressions.call(
                      Expressions.call(
                          Types.of(Enumerable.class, Employee.class),
                          null,
                          LINQ4J_AS_ENUMERABLE_METHOD,
                          Expressions.constant(
                              new HrSchema().emps)),
                      "asQueryable"),
                  Employee.class)
              .where(
                  Expressions.lambda(
                      Expressions.equal(
                          Expressions.field(
                              e, "empid"),
                          Expressions.constant(150)),
                      e))
              .select(
                  Expressions.<Function1<Employee, Object[]>>lambda(
                      Expressions.new_(
                          Object[].class,
                          Expressions.field(
                              e, "empid"),
                          Expressions.call(
                              Expressions.field(
                                  e, "name"),
                              "toUpperCase")),
                      e))
              .toList();
      assertThat(list, hasSize(1));
      assertThat(list.get(0), arrayWithSize(2));
      assertThat(list.get(0)[0], is(150));
      assertThat(list.get(0)[1], is("SEBASTIAN"));
    }
  
    /**
     * Tests a relation that is accessed via method syntax.
     * The function returns a {@link org.apache.calcite.linq4j.Queryable}.
     */
    @Disabled
    @Test void testOperator() throws SQLException {
      Connection connection =
          DriverManager.getConnection("jdbc:calcite:");
      CalciteConnection calciteConnection =
          connection.unwrap(CalciteConnection.class);
      SchemaPlus rootSchema = calciteConnection.getRootSchema();
      SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
      schema.add("GenerateStrings",
          TableMacroImpl.create(Smalls.GENERATE_STRINGS_METHOD));
      schema.add("StringUnion",
          TableMacroImpl.create(Smalls.STRING_UNION_METHOD));
      rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
      final String sql = "select *\n"
          + "from table(s.StringUnion(\n"
          + "  GenerateStrings(5),\n"
          + "  cursor (select name from emps)))\n"
          + "where char_length(s) > 3";
      ResultSet resultSet = connection.createStatement().executeQuery(sql);
      assertTrue(resultSet.next());
    }
  
    /**
     * Tests a view.
     */
    @Test void testView() throws SQLException {
      Connection connection =
          DriverManager.getConnection("jdbc:calcite:");
      CalciteConnection calciteConnection =
          connection.unwrap(CalciteConnection.class);
      SchemaPlus rootSchema = calciteConnection.getRootSchema();
      SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
      schema.add("emps_view",
          ViewTable.viewMacro(schema,
              "select * from \"hr\".\"emps\" where \"deptno\" = 10",
              null, Arrays.asList("s", "emps_view"), null));
      rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
      final String sql = "select *\n"
          + "from \"s\".\"emps_view\"\n"
          + "where \"empid\" < 120";
      ResultSet resultSet = connection.createStatement().executeQuery(sql);
      assertThat(CalciteAssert.toString(resultSet),
          is("empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000\n"
              + "empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250\n"));
    }
  
    /**
     * Tests a view with a path.
     */
    @Test void testViewPath() throws SQLException {
      Connection connection =
          DriverManager.getConnection("jdbc:calcite:");
      CalciteConnection calciteConnection =
          connection.unwrap(CalciteConnection.class);
      SchemaPlus rootSchema = calciteConnection.getRootSchema();
      SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
      // create a view s.emps based on hr.emps. uses explicit schema path "hr".
      schema.add("emps",
          ViewTable.viewMacro(schema,
              "select * from \"emps\" where \"deptno\" = 10",
              ImmutableList.of("hr"), ImmutableList.of("s", "emps"), null));
      schema.add("hr_emps",
          ViewTable.viewMacro(schema,
              "select * from \"emps\"",
              ImmutableList.of("hr"), ImmutableList.of("s", "hr_emps"), null));
      schema.add("s_emps",
          ViewTable.viewMacro(schema,
              "select * from \"emps\"",
              ImmutableList.of("s"), ImmutableList.of("s", "s_emps"), null));
      schema.add("null_emps",
          ViewTable.viewMacro(schema, "select * from \"emps\"", null,
              ImmutableList.of("s", "null_emps"), null));
      rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
      final Statement statement = connection.createStatement();
  
      // "hr_emps" -> "hr"."emps", 4 rows
      ResultSet resultSet =
          statement.executeQuery("select * from \"s\".\"hr_emps\"");
      assertThat(count(resultSet), is(4));
  
      // "s_emps" -> "s"."emps", 3 rows
      resultSet =
          statement.executeQuery("select * from \"s\".\"s_emps\"");
      assertThat(count(resultSet), is(3));
  
      // "null_emps" -> "s"."emps", 3
      resultSet = statement.executeQuery("select * from \"s\".\"null_emps\"");
      assertThat(count(resultSet), is(3));
      statement.close();
    }
  
    private int count(ResultSet resultSet) throws SQLException {
      int i = 0;
      while (resultSet.next()) {
        ++i;
      }
      resultSet.close();
      return i;
    }
  
    /** Tests column based on java.sql.Date field. */
    @Test void testDateColumn() {
      CalciteAssert.that()
          .withSchema("s", new ReflectiveSchema(new DateColumnSchema()))
          .query("select * from \"s\".\"emps\"")
          .returns(""
              + "hireDate=1970-01-01; empid=10; deptno=20; name=fred; salary=0.0; commission=null\n"
              + "hireDate=1970-04-11; empid=10; deptno=20; name=bill; salary=0.0; commission=null\n");
    }
  
    /** Tests querying an object that has no public fields. */
    @Test void testNoPublicFields() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select 1 from \"s\".\"allPrivates\"")
          .returns("EXPR$0=1\n");
      with.query("select \"x\" from \"s\".\"allPrivates\"")
          .throws_("Column 'x' not found in any table");
    }
  
    /** Tests columns based on types such as java.sql.Date and java.util.Date.
     *
     * @see CatchallSchema#everyTypes */
    @Test void testColumnTypes() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select \"primitiveBoolean\" from \"s\".\"everyTypes\"")
          .returns("primitiveBoolean=false\n"
              + "primitiveBoolean=true\n");
      with.query("select * from \"s\".\"everyTypes\"")
          .returns(""
              + "primitiveBoolean=false; primitiveByte=0; primitiveChar=\u0000; "
              + "primitiveShort=0; primitiveInt=0; primitiveLong=0; primitiveFloat=0.0; "
              + "primitiveDouble=0.0; wrapperBoolean=false; wrapperByte=0; wrapperCharacter=\u0000; "
              + "wrapperShort=0; wrapperInteger=0; wrapperLong=0; wrapperFloat=0.0; "
              + "wrapperDouble=0.0; sqlDate=1970-01-01; sqlTime=00:00:00; "
              + "sqlTimestamp=1970-01-01 00:00:00; utilDate=1970-01-01 00:00:00; "
              + "string=1; bigDecimal=0; list=[]\n"
              + "primitiveBoolean=true; primitiveByte=127; primitiveChar=\uffff; "
              + "primitiveShort=32767; primitiveInt=2147483647; primitiveLong=9223372036854775807; "
              + "primitiveFloat=3.4028235E38; primitiveDouble=1.7976931348623157E308; "
              + "wrapperBoolean=null; wrapperByte=null; wrapperCharacter=null; wrapperShort=null; "
              + "wrapperInteger=null; wrapperLong=null; wrapperFloat=null; wrapperDouble=null; "
              + "sqlDate=null; sqlTime=null; sqlTimestamp=null; utilDate=null; string=null; "
              + "bigDecimal=null; list=null\n");
    }
  
    /** Tests NOT for nullable columns.
     *
     * @see CatchallSchema#everyTypes */
    @Test void testWhereNot() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query(
          "select \"wrapperByte\" from \"s\".\"everyTypes\" where NOT (\"wrapperByte\" is null)")
          .returnsUnordered("wrapperByte=0");
    }
  
    /** Tests NOT for nullable columns.
     *
     * @see CatchallSchema#everyTypes */
    @Test void testSelectNot() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query(
          "select NOT \"wrapperBoolean\" \"value\" from \"s\".\"everyTypes\"")
          .returnsUnordered(
              "value=null",
              "value=true");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testSelectWithFieldAccessOnFirstLevelRecordType() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"birthPlace\".\"city\" as city from \"bookstore\".\"authors\" au\n")
          .returnsUnordered("CITY=Heraklion", "CITY=Besançon", "CITY=Ionia");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-5157">[CALCITE-5157]
     * ClassCastException in checkRollUp with DOT operator</a>. */
    @Test void testSelectWithFieldAccessOnFirstLevelRecordTypeWithParentheses() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select (\"birthPlace\").\"city\" as city from \"bookstore\".\"authors\"\n")
          .returnsUnordered("CITY=Heraklion", "CITY=Besançon", "CITY=Ionia");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testSelectWithFieldAccessOnSecondLevelRecordType() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"birthPlace\".\"coords\".\"latitude\" as lat\n"
              + "from \"bookstore\".\"authors\" au\n")
          .returnsUnordered("LAT=47.24", "LAT=35.3387", "LAT=null");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testWhereWithFieldAccessOnFirstLevelRecordType() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"aid\" as aid from \"bookstore\".\"authors\" au\n"
              + "where au.\"birthPlace\".\"city\"='Heraklion'")
          .returnsUnordered("AID=2");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testWhereWithFieldAccessOnSecondLevelRecordType() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"aid\" as aid from \"bookstore\".\"authors\" au\n"
              + "where au.\"birthPlace\".\"coords\".\"latitude\"=35.3387")
          .returnsUnordered("AID=2");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testSelectWithFieldAccessOnFirstLevelRecordTypeArray() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"books\"[1].\"title\" as title from \"bookstore\".\"authors\" au\n")
          .returnsUnordered("TITLE=Les Misérables", "TITLE=Zorba the Greek", "TITLE=null");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testSelectWithFieldAccessOnSecondLevelRecordTypeArray() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"books\"[1].\"pages\"[1].\"pageNo\" as pno\n"
              + "from \"bookstore\".\"authors\" au\n")
          .returnsUnordered("PNO=1", "PNO=1", "PNO=null");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testWhereWithFieldAccessOnFirstLevelRecordTypeArray() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"aid\" as aid from \"bookstore\".\"authors\" au\n"
              + "where au.\"books\"[1].\"title\"='Les Misérables'")
          .returnsUnordered("AID=1");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2404">[CALCITE-2404]
     * Accessing structured-types is not implemented by the runtime</a>. */
    @Test void testWhereWithFieldAccessOnSecondLevelRecordTypeArray() {
      CalciteAssert.that()
          .with(CalciteAssert.SchemaSpec.BOOKSTORE)
          .query("select au.\"aid\" as aid from \"bookstore\".\"authors\" au\n"
              + "where au.\"books\"[1].\"pages\"[2].\"contentType\"='Acknowledgements'")
          .returnsUnordered("AID=2");
    }
  
    /** Tests columns based on types such as java.sql.Date and java.util.Date.
     *
     * @see CatchallSchema#everyTypes */
    @Test void testAggregateFunctions() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      checkAgg(with, "min");
      checkAgg(with, "max");
      checkAgg(with, "avg");
      checkAgg(with, "count");
    }
  
    private void checkAgg(CalciteAssert.AssertThat with, String fn) {
      for (final Field field
          : fn.equals("avg") ? EveryType.numericFields() : EveryType.fields()) {
        with.query(
            "select " + fn + "(\"" + field.getName() + "\") as c\n"
                + "from \"s\".\"everyTypes\"")
            .returns(
                input -> {
                  int n = 0;
                  try {
                    while (input.next()) {
                      final Object o = get(input);
                      Util.discard(o);
                      ++n;
                    }
                  } catch (SQLException e) {
                    throw TestUtil.rethrow(e);
                  }
                  assertThat(n, equalTo(1));
                });
      }
    }
  
    private Object get(ResultSet input) throws SQLException {
      final int type = input.getMetaData().getColumnType(1);
      switch (type) {
      case java.sql.Types.BOOLEAN:
        return input.getBoolean(1);
      case java.sql.Types.TINYINT:
        return input.getByte(1);
      case java.sql.Types.SMALLINT:
        return input.getShort(1);
      case java.sql.Types.INTEGER:
        return input.getInt(1);
      case java.sql.Types.BIGINT:
        return input.getLong(1);
      case java.sql.Types.REAL:
        return input.getFloat(1);
      case java.sql.Types.DOUBLE:
        return input.getDouble(1);
      case java.sql.Types.CHAR:
      case java.sql.Types.VARCHAR:
        return input.getString(1);
      case java.sql.Types.DATE:
        return input.getDate(1);
      case java.sql.Types.TIME:
        return input.getTime(1);
      case java.sql.Types.TIMESTAMP:
        return input.getTimestamp(1);
      case java.sql.Types.DECIMAL:
        return input.getBigDecimal(1);
      case java.sql.Types.ARRAY:
        return input.getArray(1);
      default:
        throw new AssertionError(type);
      }
    }
  
    @Test void testClassNames() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL).query("select * from \"s\".\"everyTypes\"")
          .returns(
              resultSet -> {
                try {
                  final ResultSetMetaData metaData = resultSet.getMetaData();
                  check(metaData, "primitiveBoolean", Boolean.class);
                  check(metaData, "primitiveByte", Byte.class);
                  check(metaData, "primitiveChar", String.class);
                  check(metaData, "primitiveShort", Short.class);
                  check(metaData, "primitiveInt", Integer.class);
                  check(metaData, "primitiveLong", Long.class);
                  check(metaData, "primitiveFloat", Float.class);
                  check(metaData, "primitiveDouble", Double.class);
                  check(metaData, "wrapperBoolean", Boolean.class);
                  check(metaData, "wrapperByte", Byte.class);
                  check(metaData, "wrapperCharacter", String.class);
                  check(metaData, "wrapperShort", Short.class);
                  check(metaData, "wrapperInteger", Integer.class);
                  check(metaData, "wrapperLong", Long.class);
                  check(metaData, "wrapperFloat", Float.class);
                  check(metaData, "wrapperDouble", Double.class);
                  check(metaData, "sqlDate", java.sql.Date.class);
                  check(metaData, "sqlTime", Time.class);
                  check(metaData, "sqlTimestamp", Timestamp.class);
                  check(metaData, "utilDate", Timestamp.class);
                  check(metaData, "string", String.class);
                } catch (SQLException e) {
                  throw TestUtil.rethrow(e);
                }
              });
    }
  
    private void check(ResultSetMetaData metaData, String columnName,
        Class expectedType) throws SQLException {
      for (int i = 1; i <= metaData.getColumnCount(); i++) {
        if (metaData.getColumnName(i).equals(columnName)) {
          assertThat(metaData.getColumnClassName(i),
              equalTo(expectedType.getName()));
          return;
        }
      }
      fail("column not found: " + columnName);
    }
  
    @Test void testJavaBoolean() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"primitiveBoolean\"")
          .returns("C=1\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\"")
          .returns("C=0\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\" is true")
          .returns("C=0\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\" is not true")
          .returns("C=2\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\" is false")
          .returns("C=1\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\" is not false")
          .returns("C=1\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\" is null")
          .returns("C=1\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"wrapperBoolean\" is not null")
          .returns("C=1\n");
      with.query("select count(*) as c from \"s\".\"everyTypes\"\n"
          + "where \"primitiveInt\" > 0")
          .returns("C=1\n");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-119">[CALCITE-119]
     * Comparing a Java type long with a SQL type INTEGER gives wrong
     * answer</a>. */
    @Test void testCompareJavaAndSqlTypes() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      // With CALCITE-119, returned 0 rows. The problem was that when comparing
      // a Java type (long) and a SQL type (INTEGER), the SQL type was deemed
      // "less restrictive". So, the long value got truncated to an int value.
      with.query("select \"primitiveLong\" as c from \"s\".\"everyTypes\"\n"
          + "where \"primitiveLong\" > 0")
          .returns("C=9223372036854775807\n");
  
      // count(nullif(b, false)) counts how many times b is true
      with.query("select count(\"primitiveBoolean\") as p,\n"
          + "  count(\"wrapperBoolean\") as w,\n"
          + "  count(nullif(\"primitiveShort\" >= 0, false)) as sp,\n"
          + "  count(nullif(\"wrapperShort\" >= 0, false)) as sw,\n"
          + "  count(nullif(\"primitiveInt\" >= 0, false)) as ip,\n"
          + "  count(nullif(\"wrapperInteger\" >= 0, false)) as iw,\n"
          + "  count(nullif(\"primitiveLong\" >= 0, false)) as lp,\n"
          + "  count(nullif(\"wrapperLong\" >= 0, false)) as lw\n"
          + "from \"s\".\"everyTypes\"")
          .returns("P=2; W=1; SP=2; SW=1; IP=2; IW=1; LP=2; LW=1\n");
    }
  
    @Test void testDivideWraperPrimitive() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select \"wrapperLong\" / \"primitiveLong\" as c\n"
          + " from \"s\".\"everyTypes\" where \"primitiveLong\" <> 0")
          .planContains(
              "final Long input_value = current.wrapperLong;")
          .planContains(
              "return input_value == null ? null : Long.valueOf(input_value.longValue() / current.primitiveLong);")
          .returns("C=null\n");
    }
  
    @Test void testDivideDoubleBigDecimal() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select \"wrapperDouble\" / \"bigDecimal\" as c\n"
          + " from \"s\".\"everyTypes\"")
          .runs();
    }
  
    @Test void testDivideWraperWrapper() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select \"wrapperLong\" / \"wrapperLong\" as c\n"
          + " from \"s\".\"everyTypes\" where \"primitiveLong\" <> 0")
          .planContains(
              "final Long input_value = ((org.apache.calcite.test.schemata.catchall.CatchallSchema.EveryType) inputEnumerator.current()).wrapperLong;")
          .planContains(
              "return input_value == null ? null : Long.valueOf(input_value.longValue() / input_value.longValue());")
          .returns("C=null\n");
    }
  
    @Test void testDivideWraperWrapperMultipleTimes() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select \"wrapperLong\" / \"wrapperLong\"\n"
          + "+ \"wrapperLong\" / \"wrapperLong\" as c\n"
          + " from \"s\".\"everyTypes\" where \"primitiveLong\" <> 0")
          .planContains(
              "final Long input_value = ((org.apache.calcite.test.schemata.catchall.CatchallSchema.EveryType) inputEnumerator.current()).wrapperLong;")
          .planContains(
              "final Long binary_call_value = input_value == null ? null : Long.valueOf(input_value.longValue() / input_value.longValue());")
          .planContains(
              "return binary_call_value == null ? null : Long.valueOf(binary_call_value.longValue() + binary_call_value.longValue());")
          .returns("C=null\n");
    }
  
    @Test void testOp() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that()
              .withSchema("s", CATCHALL);
      checkOp(with, "+");
      checkOp(with, "-");
      checkOp(with, "*");
      checkOp(with, "/");
    }
  
    private void checkOp(CalciteAssert.AssertThat with, String fn) {
      for (Field field : EveryType.numericFields()) {
        for (Field field2 : EveryType.numericFields()) {
          final String name = "\"" + field.getName() + "\"";
          final String name2 = "\"" + field2.getName() + "\"";
          with.query("select " + name + "\n"
              + " " + fn + " " + name2 + " as c\n"
              + "from \"s\".\"everyTypes\"\n"
              + "where " + name + " <> 0")
              .returns(resultSet -> { });
        }
      }
    }
  
    @Test void testCastFromString() {
      CalciteAssert.that().withSchema("s", CATCHALL)
          .query("select cast(\"string\" as int) as c from \"s\".\"everyTypes\"")
          .returns("C=1\n"
              + "C=null\n");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-580">[CALCITE-580]
     * Average aggregation on an Integer column throws ClassCastException</a>. */
    @Test void testAvgInt() {
      CalciteAssert.that().withSchema("s", CATCHALL).with(Lex.JAVA)
          .query("select primitiveLong, avg(primitiveInt)\n"
              + "from s.everyTypes\n"
              + "group by primitiveLong order by primitiveLong")
          .returns(input -> {
            StringBuilder buf = new StringBuilder();
            try {
              while (input.next()) {
                buf.append(input.getInt(2)).append("\n");
              }
            } catch (SQLException e) {
              throw TestUtil.rethrow(e);
            }
            assertThat(buf, hasToString("0\n2147483647\n"));
          });
    }
  
    /** Tests that if a field of a relation has an unrecognized type (in this
     * case a {@link BitSet}) then it is treated as an object.
     *
     * @see CatchallSchema#badTypes */
    @Test void testTableFieldHasBadType() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query("select * from \"s\".\"badTypes\"")
          .returns("integer=0; bitSet={}\n");
    }
  
    /** Tests that a schema with a field whose type cannot be recognized
     * throws an informative exception.
     *
     * @see CatchallSchema#enumerable
     * @see CatchallSchema#list */
    @Test void testSchemaFieldHasBadType() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      // BitSet is not a valid relation type. It's as if "bitSet" field does
      // not exist.
      with.query("select * from \"s\".\"bitSet\"")
          .throws_("Object 'bitSet' not found within 's'");
      // Enumerable field returns 3 records with 0 fields
      with.query("select * from \"s\".\"enumerable\"")
          .returns("\n"
              + "\n"
              + "\n"
              + "\n");
      // List is implicitly converted to Enumerable
      with.query("select * from \"s\".\"list\"")
          .returns("\n"
              + "\n"
              + "\n"
              + "\n");
    }
  
    /** Test case for a bug where a Java string 'Abc' compared to a char 'Ab'
     * would be truncated to the char precision and falsely match. */
    @Test void testPrefix() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query(
              "select * from \"s\".\"prefixEmps\" where \"name\" in ('Ab', 'Abd')")
          .returns("empid=2; deptno=10; name=Ab; salary=0.0; commission=null\n"
              + "empid=4; deptno=10; name=Abd; salary=0.0; commission=null\n");
    }
  
    /** If a method returns a
     * {@link ViewTable}.{@code ViewTableMacro}, then it
     * should be expanded. */
    @Disabled
    @Test void testTableMacroIsView() {
      CalciteAssert.that()
          .withSchema("s", new ReflectiveSchema(new HrSchema()))
          .query("select * from table(\"s\".\"view\"('abc'))")
          .returns(
              "empid=2; deptno=10; name=Ab; salary=0.0; commission=null\n"
                  + "empid=4; deptno=10; name=Abd; salary=0.0; commission=null\n");
    }
  
    /** Finds a table-macro using reflection. */
    @Disabled
    @Test void testTableMacro() {
      CalciteAssert.that()
          .withSchema("s", new ReflectiveSchema(new HrSchema()))
          .query("select * from table(\"s\".\"foo\"(3))")
          .returns(
              "empid=2; deptno=10; name=Ab; salary=0.0; commission=null\n"
                  + "empid=4; deptno=10; name=Abd; salary=0.0; commission=null\n");
    }
  
    /** Table with single field as Integer[]. */
    @Disabled(
        "java.lang.AssertionError RelDataTypeImpl.getFieldList(RelDataTypeImpl.java:99)")
    @Test void testArrayOfBoxedPrimitives() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query("select * from \"s\".\"primesBoxed\"")
          .returnsUnordered("value=1", "value=3", "value=7");
    }
  
    /** Table with single field as int[]. */
    @Disabled(
        "java.lang.AssertionError RelDataTypeImpl.getFieldList(RelDataTypeImpl.java:99)")
    @Test void testArrayOfPrimitives() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query("select * from \"s\".\"primes\"")
          .returnsUnordered("value=1", "value=3", "value=7");
    }
  
    @Test void testCustomBoxedScalar() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query("select \"value\" from \"s\".\"primesCustomBoxed\"")
          .returnsUnordered("value=1", "value=3", "value=5");
    }
  
    @Test void testCustomBoxedSalarCalc() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query("select \"value\"*2 \"value\" from \"s\".\"primesCustomBoxed\"")
          .returnsUnordered("value=2", "value=6", "value=10");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-1569">[CALCITE-1569]
     * Date condition can generates Integer == Integer, which is always
     * false</a>. */
    @Test void testDateCanCompare() {
      final String sql = "select a.v\n"
          + "from (select \"sqlDate\" v\n"
          + "  from \"s\".\"everyTypes\" "
          + "  group by \"sqlDate\") a,"
          + "    (select \"sqlDate\" v\n"
          + "  from \"s\".\"everyTypes\"\n"
          + "  group by \"sqlDate\") b\n"
          + "where a.v >= b.v\n"
          + "group by a.v";
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query(sql)
          .returnsUnordered("V=1970-01-01");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-3512">[CALCITE-3512]
     * Query fails when comparing Time/TimeStamp types</a>. */
    @Test void testTimeCanCompare() {
      final String sql = "select a.v\n"
          + "from (select \"sqlTime\" v\n"
          + "  from \"s\".\"everyTypes\" "
          + "  group by \"sqlTime\") a,"
          + "    (select \"sqlTime\" v\n"
          + "  from \"s\".\"everyTypes\"\n"
          + "  group by \"sqlTime\") b\n"
          + "where a.v >= b.v\n"
          + "group by a.v";
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query(sql)
          .returnsUnordered("V=00:00:00");
    }
  
    @Test void testTimestampCanCompare() {
      final String sql = "select a.v\n"
          + "from (select \"sqlTimestamp\" v\n"
          + "  from \"s\".\"everyTypes\" "
          + "  group by \"sqlTimestamp\") a,"
          + "    (select \"sqlTimestamp\" v\n"
          + "  from \"s\".\"everyTypes\"\n"
          + "  group by \"sqlTimestamp\") b\n"
          + "where a.v >= b.v\n"
          + "group by a.v";
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query(sql)
          .returnsUnordered("V=1970-01-01 00:00:00");
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-1919">[CALCITE-1919]
     * NPE when target in ReflectiveSchema belongs to the unnamed package</a>. */
    @Test void testReflectiveSchemaInUnnamedPackage() throws Exception {
      final Driver driver = new Driver();
      try (CalciteConnection connection = (CalciteConnection)
          driver.connect("jdbc:calcite:", new Properties())) {
        SchemaPlus rootSchema = connection.getRootSchema();
        final Class<?> c = Class.forName("RootHr");
        final Object o = c.getDeclaredConstructor().newInstance();
        rootSchema.add("hr", new ReflectiveSchema(o));
        connection.setSchema("hr");
        final Statement statement = connection.createStatement();
        final String sql = "select * from \"emps\"";
        final ResultSet resultSet = statement.executeQuery(sql);
        final String expected = "empid=100; name=Bill\n"
            + "empid=200; name=Eric\n"
            + "empid=150; name=Sebastian\n";
        assertThat(CalciteAssert.toString(resultSet), is(expected));
      }
    }
  
    /** Extension to {@link Employee} with a {@code hireDate} column. */
    public static class EmployeeWithHireDate extends Employee {
      public final java.sql.Date hireDate;
  
      public EmployeeWithHireDate(
          int empid, int deptno, String name, float salary, Integer commission,
          java.sql.Date hireDate) {
        super(empid, deptno, name, salary, commission);
        this.hireDate = hireDate;
      }
    }
  
    /** Schema that contains a table with a date column. */
    public static class DateColumnSchema {
      public final EmployeeWithHireDate[] emps = {
          new EmployeeWithHireDate(
              10, 20, "fred", 0f, null, new java.sql.Date(0)), // 1970-1-1
          new EmployeeWithHireDate(
              10, 20, "bill", 0f, null,
              new java.sql.Date(100 * DateTimeUtils.MILLIS_PER_DAY)) // 1970-04-11
      };
    }
  
    /** Tests
     * <a href="https://issues.apache.org/jira/browse/CALCITE-2611">[CALCITE-2611]
     * UNKNOWN on one side of an OR may lead to uncompilable code</a>. */
    @Test void testUnknownInOr() {
      CalciteAssert.that()
          .withSchema("s", CATCHALL)
          .query("select (\"value\" = 3 and unknown) or ( \"value\"  = 3 ) "
              + "from \"s\".\"primesCustomBoxed\"")
          .returnsUnordered("EXPR$0=false",
              "EXPR$0=false",
              "EXPR$0=true");
    }
  
    @Test void testDecimalNegate() {
      final CalciteAssert.AssertThat with =
          CalciteAssert.that().withSchema("s", CATCHALL);
      with.query("select - \"bigDecimal\" from \"s\".\"everyTypes\"")
          .planContains("negate()")
          .returnsUnordered(
              "EXPR$0=0",
              "EXPR$0=null");
    }
  
    /**
     * Test that the row count statistic can be retrieved from a ReflectiveSchema.
     *
     * @see <a href="https://issues.apache.org/jira/browse/CALCITE-5649">[CALCITE-5649]</a>
     */
    @Test void testArrayFieldTableHasRowCount() {
      ReflectiveSchema schema = new ReflectiveSchema(new HrSchema());
      Table table = schema.tables().get("emps");
      assertNotNull(table);
      Statistic statistic = table.getStatistic();
      assertNotNull(statistic);
      assertThat(statistic.getRowCount(), is(4D));
    }
  
    @Test void testCollectionFieldTableHasRowCount() {
      ReflectiveSchema schema = new ReflectiveSchema(new HrSchemaPlus());
      Table table = schema.tables().get("xlocations");
      assertNotNull(table);
      Statistic statistic = table.getStatistic();
      assertNotNull(statistic);
      assertThat(statistic.getRowCount(), is(2D));
    }
  }
  