testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java

/*
 * 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.sql.test;

import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.fun.SqlLibrary;
import org.apache.calcite.sql.fun.SqlLibraryOperatorTableFactory;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.StringAndPos;
import org.apache.calcite.sql.test.SqlTester.ResultChecker;
import org.apache.calcite.sql.test.SqlTester.TypeChecker;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.test.CalciteAssert;
import org.apache.calcite.test.ConnectionFactories;
import org.apache.calcite.test.ConnectionFactory;
import org.apache.calcite.test.Matchers;
import org.apache.calcite.util.Bug;

import org.checkerframework.checker.nullness.qual.Nullable;

import java.util.function.Consumer;
import java.util.function.UnaryOperator;

import static org.apache.calcite.rel.type.RelDataTypeImpl.NON_NULLABLE_SUFFIX;
import static org.apache.calcite.sql.test.ResultCheckers.isSingle;

/**
 * A fixture for testing the SQL operators.
 *
 * <p>It provides a fluent API so that you can write tests by chaining method
 * calls.
 *
 * <p>It is immutable. If you have two test cases that require a similar set up
 * (for example, the same SQL expression and parser configuration), it is safe
 * to use the same fixture object as a starting point for both tests.
 *
 * <p>The idea is that when you define an operator (or another piece of SQL
 * functionality), you can define the logical behavior of that operator once, as
 * part of that operator. Later you can define one or more physical
 * implementations of that operator, and test them all using the same set of
 * tests.
 *
 * <p>Depending on the implementation of {@link SqlTester} used
 * (see {@link #withTester(UnaryOperator)}), the fixture may or may not
 * evaluate expressions and check their results.
 */
public interface SqlOperatorFixture extends AutoCloseable {
  //~ Enums ------------------------------------------------------------------

  // TODO: Change message
  String INVALID_CHAR_MESSAGE = "(?s).*";

  String OUT_OF_RANGE_MESSAGE = ".* out of range.*";

  String WRONG_FORMAT_MESSAGE = "Number has wrong format.*";

  // TODO: Change message
  String DIVISION_BY_ZERO_MESSAGE = "(?s).*";

  // TODO: Change message
  String STRING_TRUNC_MESSAGE = "(?s).*";

  // TODO: Change message
  String BAD_DATETIME_MESSAGE = "(?s).*";

  String LITERAL_OUT_OF_RANGE_MESSAGE =
      "(?s).*Numeric literal.*out of range.*";

  String INVALID_ARGUMENTS_NUMBER =
      "Invalid number of arguments to function .* Was expecting .* arguments";

  //~ Enums ------------------------------------------------------------------

  /**
   * Name of a virtual machine that can potentially implement an operator.
   */
  enum VmName {
    FENNEL, JAVA, EXPAND
  }

  //~ Methods ----------------------------------------------------------------

  /** Returns the test factory. */
  SqlTestFactory getFactory();

  /** Creates a copy of this fixture with a new test factory. */
  SqlOperatorFixture withFactory(UnaryOperator<SqlTestFactory> transform);

  /** Returns the tester. */
  SqlTester getTester();

  /** Creates a copy of this fixture with a new tester. */
  SqlOperatorFixture withTester(UnaryOperator<SqlTester> transform);

  /** Creates a copy of this fixture with a new parser configuration. */
  default SqlOperatorFixture withParserConfig(
      UnaryOperator<SqlParser.Config> transform) {
    return withFactory(f -> f.withParserConfig(transform));
  }

  /** Returns a fixture that tests a given SQL quoting style. */
  default SqlOperatorFixture withQuoting(Quoting quoting) {
    return withParserConfig(c -> c.withQuoting(quoting));
  }

  /** Returns a fixture that applies a given casing policy to quoted
   * identifiers. */
  default SqlOperatorFixture withQuotedCasing(Casing casing) {
    return withParserConfig(c -> c.withQuotedCasing(casing));
  }

  /** Returns a fixture that applies a given casing policy to unquoted
   * identifiers. */
  default SqlOperatorFixture withUnquotedCasing(Casing casing) {
    return withParserConfig(c -> c.withUnquotedCasing(casing));
  }

  /** Returns a fixture that matches identifiers by case-sensitive or
   * case-insensitive. */
  default SqlOperatorFixture withCaseSensitive(boolean sensitive) {
    return withParserConfig(c -> c.withCaseSensitive(sensitive));
  }

  /** Returns a fixture that follows a given lexical policy. */
  default SqlOperatorFixture withLex(Lex lex) {
    return withParserConfig(c -> c.withLex(lex));
  }

  /** Returns a fixture that tests conformance to a particular SQL language
   * version. */
  default SqlOperatorFixture withConformance(SqlConformance conformance) {
    return withParserConfig(c -> c.withConformance(conformance))
        .withValidatorConfig(c -> c.withConformance(conformance))
        .withConnectionFactory(cf -> cf.with("conformance", conformance));
  }

  /** Returns the conformance. */
  default SqlConformance conformance() {
    return getFactory().parserConfig().conformance();
  }

  /** Returns a fixture with a given validator configuration. */
  default SqlOperatorFixture withValidatorConfig(
      UnaryOperator<SqlValidator.Config> transform) {
    return withFactory(f -> f.withValidatorConfig(transform));
  }

  /** Returns a fixture that tests with implicit type coercion on/off. */
  default SqlOperatorFixture enableTypeCoercion(boolean enabled) {
    return withValidatorConfig(c -> c.withTypeCoercionEnabled(enabled));
  }

  /** Returns a fixture that does not fail validation if it encounters an
   * unknown function. */
  default SqlOperatorFixture withLenientOperatorLookup(boolean lenient) {
    return withValidatorConfig(c -> c.withLenientOperatorLookup(lenient));
  }

  /** Returns a fixture that gets connections from a given factory. */
  default SqlOperatorFixture withConnectionFactory(
      UnaryOperator<ConnectionFactory> transform) {
    return withFactory(f -> f.withConnectionFactory(transform));
  }

  /** Returns a fixture that uses a given operator table. */
  default SqlOperatorFixture withOperatorTable(
      SqlOperatorTable operatorTable) {
    return withFactory(f -> f.withOperatorTable(o -> operatorTable));
  }

  /** Returns whether to run tests that are considered 'broken'.
   * Returns false by default, but it is useful to temporarily enable the
   * 'broken' tests to see whether they are still broken. */
  boolean brokenTestsEnabled();

  /** Sets {@link #brokenTestsEnabled()}. */
  SqlOperatorFixture withBrokenTestsEnabled(boolean enableBrokenTests);

  void checkScalar(String expression,
      TypeChecker typeChecker,
      ResultChecker resultChecker);

  /**
   * Tests that a scalar SQL expression returns the expected result and the
   * expected type. For example,
   *
   * <blockquote>
   * <pre>checkScalar("1.1 + 2.9", "4.0", "DECIMAL(2, 1) NOT NULL");</pre>
   * </blockquote>
   *
   * @param expression Scalar expression
   * @param result     Expected result
   * @param resultType Expected result type
   */
  default void checkScalar(
      String expression,
      Object result,
      String resultType) {
    checkType(expression, resultType);
    checkScalar(expression, SqlTests.ANY_TYPE_CHECKER,
        ResultCheckers.createChecker(result));
  }

  /**
   * Tests that a scalar SQL expression returns the expected exact numeric
   * result as an integer. For example,
   *
   * <blockquote>
   * <pre>checkScalarExact("1 + 2", 3);</pre>
   * </blockquote>
   *
   * @param expression Scalar expression
   * @param result     Expected result
   */
  default void checkScalarExact(String expression, int result) {
    checkScalar(expression, SqlTests.INTEGER_TYPE_CHECKER, isSingle(result));
  }

  /**
   * Tests that a scalar SQL expression returns the expected exact numeric
   * result. For example,
   *
   * <blockquote>
   * <pre>checkScalarExact("1 + 2", "3");</pre>
   * </blockquote>
   *
   * @param expression   Scalar expression
   * @param expectedType Type we expect the result to have, including
   *                     nullability, precision and scale, for example
   *                     <code>DECIMAL(2, 1) NOT NULL</code>.
   * @param result       Expected result
   */
  default void checkScalarExact(
      String expression,
      String expectedType,
      String result) {
    checkScalarExact(expression, expectedType, isSingle(result));
  }

  void checkScalarExact(
      String expression,
      String expectedType,
      ResultChecker resultChecker);

  /**
   * Tests that a scalar SQL expression returns expected approximate numeric
   * result. For example,
   *
   * <blockquote>
   * <pre>checkScalarApprox("1.0 + 2.1", "3.1");</pre>
   * </blockquote>
   *
   * @param expression     Scalar expression
   * @param expectedType   Type we expect the result to have, including
   *                       nullability, precision and scale, for example
   *                       <code>DECIMAL(2, 1) NOT NULL</code>.
   * @param result         Expected result, or a matcher
   *
   * @see Matchers#within(Number, double)
   */
  void checkScalarApprox(
      String expression,
      String expectedType,
      Object result);

  /**
   * Tests that a scalar SQL expression returns the expected boolean result.
   * For example,
   *
   * <blockquote>
   * <pre>checkScalarExact("TRUE AND FALSE", Boolean.TRUE);</pre>
   * </blockquote>
   *
   * <p>The expected result can be null:
   *
   * <blockquote>
   * <pre>checkScalarExact("NOT UNKNOWN", null);</pre>
   * </blockquote>
   *
   * @param expression Scalar expression
   * @param result     Expected result (null signifies NULL).
   */
  void checkBoolean(
      String expression,
      @Nullable Boolean result);

  /**
   * Tests that a scalar SQL expression returns the expected string result.
   * For example,
   *
   * <blockquote>
   * <pre>checkScalarExact("'ab' || 'c'", "abc");</pre>
   * </blockquote>
   *
   * @param expression Scalar expression
   * @param result     Expected result
   * @param resultType Expected result type
   */
  void checkString(
      String expression,
      String result,
      String resultType);

  /**
   * Tests that a SQL expression returns the SQL NULL value. For example,
   *
   * <blockquote>
   * <pre>checkNull("CHAR_LENGTH(CAST(NULL AS VARCHAR(3))");</pre>
   * </blockquote>
   *
   * @param expression Scalar expression
   */
  void checkNull(String expression);

  /**
   * Tests that a SQL expression has a given type. For example,
   *
   * <blockquote>
   * <code>checkType("SUBSTR('hello' FROM 1 FOR 3)",
   * "VARCHAR(3) NOT NULL");</code>
   * </blockquote>
   *
   * <p>This method checks length/precision, scale, and whether the type allows
   * NULL values, so is more precise than the type-checking done by methods
   * such as {@link #checkScalarExact}.
   *
   * @param expression Scalar expression
   * @param type       Type string
   */
  void checkType(
      String expression,
      String type);

  /** Very similar to {@link #checkType}, but generates inside a SELECT
   * with a non-empty GROUP BY. Aggregate functions may be nullable if executed
   * in a SELECT with an empty GROUP BY.
   *
   * <p>Viz: {@code SELECT sum(1) FROM emp} has type "INTEGER",
   * {@code SELECT sum(1) FROM emp GROUP BY deptno} has type "INTEGER NOT NULL",
   */
  default SqlOperatorFixture checkAggType(String expr, String type) {
    checkColumnType(AbstractSqlTester.buildQueryAgg(expr), type);
    return this;
  }

  /**
   * Checks that a query returns one column of an expected type. For example,
   * <code>checkType("VALUES (1 + 2)", "INTEGER NOT NULL")</code>.
   *
   * @param sql  Query expression
   * @param type Type string
   */
  void checkColumnType(
      String sql,
      String type);

  /**
   * Tests that a SQL query returns a single column with the given type. For
   * example,
   *
   * <blockquote>
   * <pre>check("VALUES (1 + 2)", "3", SqlTypeName.Integer);</pre>
   * </blockquote>
   *
   * <p>If <code>result</code> is null, the expression must yield the SQL NULL
   * value. If <code>result</code> is a {@link java.util.regex.Pattern}, the
   * result must match that pattern.
   *
   * @param query       SQL query
   * @param typeChecker Checks whether the result is the expected type; must
   *                    not be null
   * @param result      Expected result, or matcher
   */
  default void check(String query,
      TypeChecker typeChecker,
      Object result) {
    check(query, typeChecker, SqlTests.ANY_PARAMETER_CHECKER,
        ResultCheckers.createChecker(result));
  }

  default void check(String query, String expectedType, Object result) {
    check(query, new SqlTests.StringTypeChecker(expectedType), result);
  }

  /**
   * Tests that a SQL query returns a result of expected type and value.
   * Checking of type and value are abstracted using {@link TypeChecker}
   * and {@link ResultChecker} functors.
   *
   * @param query         SQL query
   * @param typeChecker   Checks whether the result is the expected type
   * @param parameterChecker Checks whether the parameters are of expected
   *                      types
   * @param resultChecker Checks whether the result has the expected value
   */
  default void check(String query,
      SqlTester.TypeChecker typeChecker,
      SqlTester.ParameterChecker parameterChecker,
      ResultChecker resultChecker) {
    getTester()
        .check(getFactory(), query, typeChecker, parameterChecker,
            resultChecker);
  }

  /**
   * Declares that this test is for a given operator. So we can check that all
   * operators are tested.
   *
   * @param operator             Operator
   * @param unimplementedVmNames Names of virtual machines for which this
   */
  SqlOperatorFixture setFor(
      SqlOperator operator,
      VmName... unimplementedVmNames);

  /**
   * Checks that an aggregate expression returns the expected result.
   *
   * <p>For example, <code>checkAgg("AVG(DISTINCT x)", new String[] {"2", "3",
   * null, "3" }, new Double(2.5), 0);</code>
   *
   * @param expr        Aggregate expression, e.g. <code>SUM(DISTINCT x)</code>
   * @param inputValues Array of input values, e.g. <code>["1", null,
   *                    "2"]</code>.
   * @param checker     Result checker
   */
  void checkAgg(
      String expr,
      String[] inputValues,
      ResultChecker checker);

  /**
   * Checks that an aggregate expression with multiple args returns the expected
   * result.
   *
   * @param expr        Aggregate expression, e.g. <code>AGG_FUNC(x, x2, x3)</code>
   * @param inputValues Nested array of input values, e.g. <code>[
   *                    ["1", null, "2"]
   *                    ["3", "4", null]
   *                    ]</code>
   * @param resultChecker Checks whether the result has the expected value
   */
  void checkAggWithMultipleArgs(
      String expr,
      String[][] inputValues,
      ResultChecker resultChecker);

  /**
   * Checks that a windowed aggregate expression returns the expected result.
   *
   * <p>For example, <code>checkWinAgg("FIRST_VALUE(x)", new String[] {"2",
   * "3", null, "3" }, "INTEGER NOT NULL", 2, 0d);</code>
   *
   * @param expr          Aggregate expression, e.g. {@code SUM(DISTINCT x)}
   * @param inputValues   Array of input values, e.g. {@code ["1", null, "2"]}
   * @param type          Expected result type
   * @param resultChecker Checks whether the result has the expected value
   */
  void checkWinAgg(
      String expr,
      String[] inputValues,
      String windowSpec,
      String type,
      ResultChecker resultChecker);

  /**
   * Tests that an aggregate expression fails at run time.
   *
   * @param expr An aggregate expression
   * @param inputValues Array of input values
   * @param expectedError Pattern for expected error
   * @param runtime       If true, must fail at runtime; if false, must fail at
   *                      validate time
   */
  void checkAggFails(
      String expr,
      String[] inputValues,
      String expectedError,
      boolean runtime);

  /**
   * Tests that a scalar SQL expression fails at run time.
   *
   * @param expression    SQL scalar expression
   * @param expectedError Pattern for expected error. If !runtime, must
   *                      include an error location.
   * @param runtime       If true, must fail at runtime; if false, must fail at
   *                      validate time
   */
  void checkFails(
      StringAndPos expression,
      String expectedError,
      boolean runtime);

  /** As {@link #checkFails(StringAndPos, String, boolean)}, but with a string
   * that contains carets. */
  default void checkFails(
      String expression,
      String expectedError,
      boolean runtime) {
    checkFails(StringAndPos.of(expression), expectedError, runtime);
  }

  /**
   * Tests that a SQL query fails at prepare time.
   *
   * @param sap           SQL query and error position
   * @param expectedError Pattern for expected error. Must
   *                      include an error location.
   */
  void checkQueryFails(StringAndPos sap, String expectedError);

  /**
   * Tests that a SQL query succeeds at prepare time.
   *
   * @param sql           SQL query
   */
  void checkQuery(String sql);

  default SqlOperatorFixture withLibrary(SqlLibrary library) {
    return withOperatorTable(
        SqlLibraryOperatorTableFactory.INSTANCE
            .getOperatorTable(SqlLibrary.STANDARD, library))
        .withConnectionFactory(cf ->
            cf.with(ConnectionFactories.add(CalciteAssert.SchemaSpec.HR))
                .with(CalciteConnectionProperty.FUN, library.fun));
  }

  /** Applies this fixture to some code for each of the given libraries. */
  default void forEachLibrary(Iterable<? extends SqlLibrary> libraries,
      Consumer<SqlOperatorFixture> consumer) {
    SqlLibrary.expand(libraries).forEach(library -> {
      try {
        consumer.accept(this.withLibrary(library));
      } catch (Exception e) {
        throw new RuntimeException("for library " + library, e);
      }
    });
  }

  /** Applies this fixture to some code for each of the given conformances. */
  default void forEachConformance(Iterable<? extends SqlConformanceEnum> conformances,
      Consumer<SqlOperatorFixture> consumer) {
    conformances.forEach(conformance -> {
      try {
        consumer.accept(this.withConformance(conformance));
      } catch (Exception e) {
        throw new RuntimeException("for conformance " + conformance, e);
      }
    });
  }

  default SqlOperatorFixture forOracle(SqlConformance conformance) {
    return withConformance(conformance)
        .withOperatorTable(
            SqlLibraryOperatorTableFactory.INSTANCE
                .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.ORACLE))
        .withConnectionFactory(cf ->
            cf.with(ConnectionFactories.add(CalciteAssert.SchemaSpec.HR))
                .with("fun", "oracle"));
  }

  /**
   * Types for cast.
   */
  enum CastType {
    CAST("cast"),
    SAFE_CAST("safe_cast"),
    TRY_CAST("try_cast");

    CastType(String name) {
      this.name = name;
    }

    final String name;
  }

  default String getCastString(
      String value,
      String targetType,
      boolean errorLoc,
      CastType castType) {
    if (errorLoc) {
      value = "^" + value + "^";
    }
    String function = castType.name;
    return function + "(" + value + " as " + targetType + ")";
  }

  default void checkCastToApproxOkay(String value, String targetType,
      Object expected, CastType castType) {
    checkScalarApprox(getCastString(value, targetType, false, castType),
        getTargetType(targetType, castType), expected);
  }

  default void checkCastToStringOkay(String value, String targetType,
      String expected, CastType castType) {
    final String castString = getCastString(value, targetType, false, castType);
    checkString(castString, expected, getTargetType(targetType, castType));
  }

  default void checkCastToScalarOkay(String value, String targetType,
      String expected, CastType castType) {
    final String castString = getCastString(value, targetType, false, castType);
    checkScalarExact(castString, getTargetType(targetType, castType), expected);
  }

  default String getTargetType(String targetType, CastType castType) {
    return castType == CastType.CAST ? targetType + NON_NULLABLE_SUFFIX : targetType;
  }

  default void checkCastToScalarOkay(String value, String targetType,
      CastType castType) {
    checkCastToScalarOkay(value, targetType, value, castType);
  }

  default void checkCastFails(String value, String targetType,
      String expectedError, boolean runtime, CastType castType) {
    final String query = getCastString(value, targetType, !runtime, castType);
    if (castType == CastType.CAST || !runtime) {
      checkFails(query, expectedError, runtime);
    } else {
      checkNull(query);
    }
  }

  default void checkCastToString(String value, @Nullable String type,
      @Nullable String expected, CastType castType) {
    String spaces = "     ";
    if (expected == null) {
      expected = value.trim();
    }
    int len = expected.length();
    if (type != null) {
      value = getCastString(value, type, false, castType);
    }

    // currently no exception thrown for truncation
    if (Bug.DT239_FIXED) {
      checkCastFails(value,
          "VARCHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
          true, castType);
    }

    checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected, castType);
    checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected, castType);

    // currently no exception thrown for truncation
    if (Bug.DT239_FIXED) {
      checkCastFails(value,
          "CHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE,
          true, castType);
    }

    checkCastToStringOkay(value, "CHAR(" + len + ")", expected, castType);
    checkCastToStringOkay(value, "CHAR(" + (len + 5) + ")",
        expected + spaces, castType);
  }
}