/*
 * 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.dbsp.sqlCompiler.compiler.frontend.calciteCompiler;

import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeFactoryImpl;
import org.apache.calcite.rel.type.RelDataTypeFamily;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rel.type.TimeFrame;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexCallBinding;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexRangeRef;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlBinaryOperator;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlIntervalLiteral;
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlJdbcFunctionCall;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlTableFunction;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWindowTableFunction;
import org.apache.calcite.sql.fun.SqlArrayValueConstructor;
import org.apache.calcite.sql.fun.SqlBetweenOperator;
import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlCastFunction;
import org.apache.calcite.sql.fun.SqlDatetimeSubtractionOperator;
import org.apache.calcite.sql.fun.SqlExtractFunction;
import org.apache.calcite.sql.fun.SqlInternalOperators;
import org.apache.calcite.sql.fun.SqlJsonQueryFunction;
import org.apache.calcite.sql.fun.SqlJsonValueFunction;
import org.apache.calcite.sql.fun.SqlLibrary;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlLiteralChainOperator;
import org.apache.calcite.sql.fun.SqlMapValueConstructor;
import org.apache.calcite.sql.fun.SqlMultisetQueryConstructor;
import org.apache.calcite.sql.fun.SqlMultisetValueConstructor;
import org.apache.calcite.sql.fun.SqlOverlapsOperator;
import org.apache.calcite.sql.fun.SqlRowOperator;
import org.apache.calcite.sql.fun.SqlSequenceValueOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.fun.SqlSubstringFunction;
import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlOperandTypeChecker;
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql2rel.InitializerContext;
import org.apache.calcite.sql2rel.ReflectiveConvertletTable;
import org.apache.calcite.sql2rel.SqlRexContext;
import org.apache.calcite.sql2rel.SqlRexConvertlet;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;

import com.google.common.collect.ImmutableList;

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

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.function.UnaryOperator;
import java.util.stream.Collectors;

import static com.google.common.base.Preconditions.checkArgument;

import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CAST;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.QUANTIFY_OPERATORS;
import static org.apache.calcite.sql.type.NonNullableAccessors.getComponentTypeOrThrow;
import static org.apache.calcite.util.Util.first;

import static java.util.Objects.requireNonNull;

/**
 * We don't want everything from the Calcite {@link ConvertletTable},
 * so here we remove some stuff.  This file is a slightly modified copy of the
 * corresponding file from Calcite.  See comments for the actual changes.
 */
@SuppressWarnings("ALL")
public class ConvertletTable extends ReflectiveConvertletTable {

    /** Singleton instance. */
    public static ConvertletTable INSTANCE = new ConvertletTable();

    //~ Constructors -----------------------------------------------------------

    private ConvertletTable() {
        super();

        // Register aliases (operators which have a different name but
        // identical behavior to other operators).
        addAlias(SqlLibraryOperators.LEN,
                SqlStdOperatorTable.CHAR_LENGTH);
        addAlias(SqlLibraryOperators.LENGTH,
                SqlStdOperatorTable.CHAR_LENGTH);
        addAlias(SqlStdOperatorTable.CHARACTER_LENGTH,
                SqlStdOperatorTable.CHAR_LENGTH);
        addAlias(SqlStdOperatorTable.IS_UNKNOWN,
                SqlStdOperatorTable.IS_NULL);
        addAlias(SqlStdOperatorTable.IS_NOT_UNKNOWN,
                SqlStdOperatorTable.IS_NOT_NULL);
        addAlias(SqlLibraryOperators.NULL_SAFE_EQUAL,
                SqlStdOperatorTable.IS_NOT_DISTINCT_FROM);
        addAlias(SqlStdOperatorTable.PERCENT_REMAINDER, SqlStdOperatorTable.MOD);
        addAlias(SqlLibraryOperators.IFNULL, SqlLibraryOperators.NVL);
        addAlias(SqlLibraryOperators.REGEXP_SUBSTR, SqlLibraryOperators.REGEXP_EXTRACT);
        addAlias(SqlLibraryOperators.ENDSWITH, SqlLibraryOperators.ENDS_WITH);
        addAlias(SqlLibraryOperators.STARTSWITH, SqlLibraryOperators.STARTS_WITH);
        addAlias(SqlLibraryOperators.BITAND_AGG, SqlStdOperatorTable.BIT_AND);
        addAlias(SqlLibraryOperators.BITOR_AGG, SqlStdOperatorTable.BIT_OR);

        // Register convertlets for specific objects.
        registerOp(CAST, this::convertCast);
        registerOp(SqlLibraryOperators.SAFE_CAST, this::convertCast);
        registerOp(SqlLibraryOperators.TRY_CAST, this::convertCast);
        registerOp(SqlLibraryOperators.INFIX_CAST, this::convertCast);
        /*
        The following two are deliberately removed.
        registerOp(SqlStdOperatorTable.IS_DISTINCT_FROM,
                (cx, call) -> convertIsDistinctFrom(cx, call, false));
        registerOp(SqlStdOperatorTable.IS_NOT_DISTINCT_FROM,
                (cx, call) -> convertIsDistinctFrom(cx, call, true));
         */

        registerOp(SqlStdOperatorTable.PLUS, this::convertPlus);

        registerOp(SqlStdOperatorTable.MINUS,
                (cx, call) -> {
                    final RexCall e =
                            (RexCall) ConvertletTable.this.convertCall(cx, call);
                    switch (e.getOperands().get(0).getType().getSqlTypeName()) {
                        case DATE:
                        case TIME:
                        case TIMESTAMP:
                            return convertDatetimeMinus(cx, SqlStdOperatorTable.MINUS_DATE,
                                    call);
                        default:
                            return e;
                    }
                });

        // DATE(string) is equivalent to CAST(string AS DATE),
        // but other DATE variants are treated as regular functions.
        registerOp(SqlLibraryOperators.DATE,
                (cx, call) -> {
                    final RexCall e =
                            (RexCall) ConvertletTable.this.convertCall(cx, call);
                    if (e.getOperands().size() == 1
                            && SqlTypeUtil.isString(e.getOperands().get(0).getType())) {
                        return cx.getRexBuilder().makeCast(
                                e.getParserPosition(), e.type, e.getOperands().get(0));
                    }
                    return e;
                });

        registerOp(SqlLibraryOperators.DATETIME_TRUNC,
                new ConvertletTable.TruncConvertlet());
        registerOp(SqlLibraryOperators.TIMESTAMP_TRUNC,
                new ConvertletTable.TruncConvertlet());

        registerOp(SqlLibraryOperators.LTRIM,
                new ConvertletTable.TrimConvertlet(SqlTrimFunction.Flag.LEADING));
        registerOp(SqlLibraryOperators.RTRIM,
                new ConvertletTable.TrimConvertlet(SqlTrimFunction.Flag.TRAILING));

        // Removed GREATEST and LEAST - they are inefficient for many arguments
        // registerOp(SqlLibraryOperators.GREATEST, new ConvertletTable.GreatestConvertlet());
        // registerOp(SqlLibraryOperators.GREATEST_PG, new ConvertletTable.GreatestPgConvertlet());
        // registerOp(SqlLibraryOperators.LEAST, new ConvertletTable.GreatestConvertlet());
        // registerOp(SqlLibraryOperators.LEAST_PG, new ConvertletTable.GreatestPgConvertlet());
        registerOp(SqlLibraryOperators.SUBSTR_BIG_QUERY,
                new ConvertletTable.SubstrConvertlet(SqlLibrary.BIG_QUERY));
        registerOp(SqlLibraryOperators.SUBSTR_MYSQL,
                new ConvertletTable.SubstrConvertlet(SqlLibrary.MYSQL));
        registerOp(SqlLibraryOperators.SUBSTR_ORACLE,
                new ConvertletTable.SubstrConvertlet(SqlLibrary.ORACLE));
        registerOp(SqlLibraryOperators.SUBSTR_POSTGRESQL,
                new ConvertletTable.SubstrConvertlet(SqlLibrary.POSTGRESQL));

        registerOp(SqlLibraryOperators.DATE_ADD,
                new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlLibraryOperators.ADD_MONTHS,
                new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlLibraryOperators.DATE_ADD_SPARK,
                new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlLibraryOperators.DATE_DIFF,
                new ConvertletTable.TimestampDiffConvertlet());
        registerOp(SqlLibraryOperators.DATE_SUB,
                new ConvertletTable.TimestampSubConvertlet());
        registerOp(SqlLibraryOperators.DATE_SUB_SPARK,
                new ConvertletTable.TimestampSubConvertlet());
        registerOp(SqlLibraryOperators.DATETIME_ADD,
                new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlLibraryOperators.DATETIME_DIFF,
                new ConvertletTable.TimestampDiffConvertlet());
        registerOp(SqlLibraryOperators.DATETIME_SUB,
                new ConvertletTable.TimestampSubConvertlet());
        registerOp(SqlLibraryOperators.TIME_ADD,
                new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlLibraryOperators.TIME_DIFF,
                new ConvertletTable.TimestampDiffConvertlet());
        registerOp(SqlLibraryOperators.TIME_SUB,
                new ConvertletTable.TimestampSubConvertlet());
        registerOp(SqlLibraryOperators.TIMESTAMP_ADD2,
                new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlLibraryOperators.TIMESTAMP_DIFF3,
                new ConvertletTable.TimestampDiffConvertlet());
        registerOp(SqlLibraryOperators.TIMESTAMP_SUB,
                new ConvertletTable.TimestampSubConvertlet());

        QUANTIFY_OPERATORS.forEach(operator ->
                registerOp(operator, ConvertletTable::convertQuantifyOperator));

        registerOp(SqlLibraryOperators.NVL, ConvertletTable::convertNvl);
        registerOp(SqlLibraryOperators.NVL2, ConvertletTable::convertNvl2);
        registerOp(SqlLibraryOperators.DECODE,
                ConvertletTable::convertDecode);
        registerOp(SqlLibraryOperators.IF, ConvertletTable::convertIf);

        // Expand "x NOT LIKE y" into "NOT (x LIKE y)"
        registerOp(SqlStdOperatorTable.NOT_LIKE,
                (cx, call) -> cx.convertExpression(
                        SqlStdOperatorTable.NOT.createCall(call.getParserPosition(),
                                SqlStdOperatorTable.LIKE.createCall(call.getParserPosition(),
                                        call.getOperandList()))));

        // Expand "x NOT ILIKE y" into "NOT (x ILIKE y)"
        registerOp(SqlLibraryOperators.NOT_ILIKE,
                (cx, call) -> cx.convertExpression(
                        SqlStdOperatorTable.NOT.createCall(call.getParserPosition(),
                                SqlLibraryOperators.ILIKE.createCall(call.getParserPosition(),
                                        call.getOperandList()))));

        // Expand "x NOT RLIKE y" into "NOT (x RLIKE y)"
        registerOp(SqlLibraryOperators.NOT_RLIKE,
                (cx, call) -> cx.convertExpression(
                        SqlStdOperatorTable.NOT.createCall(call.getParserPosition(),
                                SqlLibraryOperators.RLIKE.createCall(call.getParserPosition(),
                                        call.getOperandList()))));

        // Expand "x NOT SIMILAR y" into "NOT (x SIMILAR y)"
        registerOp(SqlStdOperatorTable.NOT_SIMILAR_TO,
                (cx, call) -> cx.convertExpression(
                        SqlStdOperatorTable.NOT.createCall(call.getParserPosition(),
                                SqlStdOperatorTable.SIMILAR_TO.createCall(call.getParserPosition(),
                                        call.getOperandList()))));

        // Unary "+" has no effect, so expand "+ x" into "x".
        registerOp(SqlStdOperatorTable.UNARY_PLUS,
                (cx, call) -> cx.convertExpression(call.operand(0)));

        // "DOT"
        registerOp(SqlStdOperatorTable.DOT,
                (cx, call) -> cx.getRexBuilder().makeFieldAccess(
                        cx.convertExpression(call.operand(0)),
                        call.operand(1).toString(), false));
        // "ITEM"
        registerOp(SqlStdOperatorTable.ITEM, this::convertItem);
        // "AS" has no effect, so expand "x AS id" into "x".
        registerOp(SqlStdOperatorTable.AS,
                (cx, call) -> cx.convertExpression(call.operand(0)));

        // "MEASURE" has no effect, so expand "x AS MEASURE id" into "x".
        registerOp(SqlInternalOperators.MEASURE,
                (cx, call) -> cx.convertExpression(call.operand(0)));

        // Expand "CAST NOT NULL(x)" into "CAST(x AS INTEGER NOT NULL)"
        registerOp(SqlInternalOperators.CAST_NOT_NULL,
                (cx, call) ->
                        cx.getRexBuilder().makeCast(
                                cx.getTypeFactory().createTypeWithNullability(
                                        cx.getValidator().getValidatedNodeType(call), false),
                                cx.convertExpression(call.operand(0))));

        registerOp(SqlStdOperatorTable.CONVERT, this::convertCharset);
        registerOp(SqlLibraryOperators.CONVERT_ORACLE, this::convertCharset);
        registerOp(SqlStdOperatorTable.TRANSLATE, this::translateCharset);
        /*
        Deliberately removed.
        // "SQRT(x)" is equivalent to "POWER(x, .5)"
        registerOp(SqlStdOperatorTable.SQRT,
                (cx, call) -> cx.convertExpression(
                        SqlStdOperatorTable.POWER.createCall(pos,
                                call.operand(0),
                                SqlLiteral.createExactNumeric("0.5", pos))));
         */

        // "STRPOS(string, substring) is equivalent to
        // "POSITION(substring IN string)"
        registerOp(SqlLibraryOperators.STRPOS,
                (cx, call) -> cx.convertExpression(
                        SqlStdOperatorTable.POSITION.createCall(call.getParserPosition(),
                                call.operand(1), call.operand(0))));

        // "INSTR(string, substring, position, occurrence) is equivalent to
        // "POSITION(substring, string, position, occurrence)"
        registerOp(SqlLibraryOperators.INSTR, ConvertletTable::convertInstr);

        // REVIEW jvs 24-Apr-2006: This only seems to be working from within a
        // windowed agg.  I have added an optimizer rule
        // org.apache.calcite.rel.rules.AggregateReduceFunctionsRule which handles
        // other cases post-translation.  The reason I did that was to defer the
        // implementation decision; e.g. we may want to push it down to a foreign
        // server directly rather than decomposed; decomposition is easier than
        // recognition.

        // Convert "avg(<expr>)" to "cast(sum(<expr>) / count(<expr>) as
        // <type>)". We don't need to handle the empty set specially, because
        // the SUM is already supposed to come out as NULL in cases where the
        // COUNT is zero, so the null check should take place first and prevent
        // division by zero. We need the cast because SUM and COUNT may use
        // different types, say BIGINT.
        //
        // Similarly STDDEV_POP and STDDEV_SAMP, VAR_POP and VAR_SAMP.
        registerOp(SqlStdOperatorTable.AVG,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.AVG));
        registerOp(SqlStdOperatorTable.STDDEV_POP,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.STDDEV_POP));
        registerOp(SqlStdOperatorTable.STDDEV_SAMP,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.STDDEV_SAMP));
        registerOp(SqlStdOperatorTable.STDDEV,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.STDDEV_SAMP));
        registerOp(SqlStdOperatorTable.VAR_POP,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.VAR_POP));
        registerOp(SqlStdOperatorTable.VAR_SAMP,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.VAR_SAMP));
        registerOp(SqlStdOperatorTable.VARIANCE,
                new ConvertletTable.AvgVarianceConvertlet(SqlKind.VAR_SAMP));
        registerOp(SqlStdOperatorTable.COVAR_POP,
                new ConvertletTable.RegrCovarianceConvertlet(SqlKind.COVAR_POP));
        registerOp(SqlStdOperatorTable.COVAR_SAMP,
                new ConvertletTable.RegrCovarianceConvertlet(SqlKind.COVAR_SAMP));
        registerOp(SqlStdOperatorTable.REGR_SXX,
                new ConvertletTable.RegrCovarianceConvertlet(SqlKind.REGR_SXX));
        registerOp(SqlStdOperatorTable.REGR_SYY,
                new ConvertletTable.RegrCovarianceConvertlet(SqlKind.REGR_SYY));

        final SqlRexConvertlet floorCeilConvertlet = new ConvertletTable.FloorCeilConvertlet();
        registerOp(SqlStdOperatorTable.FLOOR, floorCeilConvertlet);
        registerOp(SqlStdOperatorTable.CEIL, floorCeilConvertlet);
        registerOp(SqlStdOperatorTable.TIMESTAMP_ADD, new ConvertletTable.TimestampAddConvertlet());
        registerOp(SqlStdOperatorTable.TIMESTAMP_DIFF,
                new ConvertletTable.TimestampDiffConvertlet());

        registerOp(SqlStdOperatorTable.INTERVAL,
                ConvertletTable::convertInterval);

        // Convert "element(<expr>)" to "$element_slice(<expr>)", if the
        // expression is a multiset of scalars.
        if (false) {
            registerOp(SqlStdOperatorTable.ELEMENT,
                    (cx, call) -> {
                        assert call.operandCount() == 1;
                        final SqlNode operand = call.operand(0);
                        final RelDataType type =
                                cx.getValidator().getValidatedNodeType(operand);
                        if (!getComponentTypeOrThrow(type).isStruct()) {
                            return cx.convertExpression(
                                    SqlStdOperatorTable.ELEMENT_SLICE.createCall(
                                            call.getParserPosition(), operand));
                        }

                        // fallback on default behavior
                        return ConvertletTable.this.convertCall(cx, call);
                    });
        }

        // Convert "$element_slice(<expr>)" to "element(<expr>).field#0"
        if (false) {
            registerOp(SqlStdOperatorTable.ELEMENT_SLICE,
                    (cx, call) -> {
                        assert call.operandCount() == 1;
                        final SqlNode operand = call.operand(0);
                        final RexNode expr =
                                cx.convertExpression(
                                        SqlStdOperatorTable.ELEMENT.createCall(call.getParserPosition(),
                                                operand));
                        return cx.getRexBuilder().makeFieldAccess(expr, 0);
                    });
        }
    }

    /** Converts ALL or SOME operators. */
    private static RexNode convertQuantifyOperator(SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final RexNode left = cx.convertExpression(call.getOperandList().get(0));
        assert call.getOperandList().get(1) instanceof SqlNodeList;
        final RexNode right = cx.convertExpression(((SqlNodeList) call.getOperandList().get(1)).get(0));
        final RelDataType rightComponentType = requireNonNull(right.getType().getComponentType());
        final RelDataType returnType =
                cx.getTypeFactory().createTypeWithNullability(
                        cx.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN), right.getType().isNullable()
                                || left.getType().isNullable() || rightComponentType.isNullable());
        return rexBuilder.makeCall(call.getParserPosition(), returnType,
                call.getOperator(), ImmutableList.of(left, right));
    }

    /** Converts a call to the {@code NVL} function (and also its synonym,
     * {@code IFNULL}). */
    private static RexNode convertNvl(SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final RexNode operand0 =
                cx.convertExpression(call.getOperandList().get(0));
        final RexNode operand1 =
                cx.convertExpression(call.getOperandList().get(1));
        final RelDataType type =
                cx.getValidator().getValidatedNodeType(call);
        // Preserve Operand Nullability
        return rexBuilder.makeCall(call.getParserPosition(), type, SqlStdOperatorTable.CASE,
                ImmutableList.of(
                        rexBuilder.makeCall(call.getParserPosition(), SqlStdOperatorTable.IS_NOT_NULL,
                                operand0),
                        rexBuilder.makeCast(call.getParserPosition(),
                                cx.getTypeFactory()
                                        .createTypeWithNullability(type, operand0.getType().isNullable()),
                                operand0),
                        rexBuilder.makeCast(call.getParserPosition(),
                                cx.getTypeFactory()
                                        .createTypeWithNullability(type, operand1.getType().isNullable()),
                                operand1)));
    }

    /** Converts a call to the {@code NVL2} function. */
    private static RexNode convertNvl2(SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> operands =
                convertOperands(cx, call, call.getOperandList(), SqlOperandTypeChecker.Consistency.NONE);
        final RelDataType type = cx.getValidator().getValidatedNodeType(call);

        // Create a CASE expression equivalent to the NVL2 function
        // NVL2(x, y, z) is equivalent to CASE WHEN x IS NOT NULL THEN y ELSE z END
        return rexBuilder.makeCall(type, SqlStdOperatorTable.CASE,
                ImmutableList.of(
                        rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL,
                                operands.get(0)),
                        rexBuilder.makeCast(
                                cx.getTypeFactory()
                                        .createTypeWithNullability(type, operands.get(1).getType().isNullable()),
                                operands.get(1)),
                        rexBuilder.makeCast(
                                cx.getTypeFactory()
                                        .createTypeWithNullability(type, operands.get(2).getType().isNullable()),
                                operands.get(2))));
    }

    /** Converts a call to the INSTR function.
     * INSTR(string, substring, position, occurrence) is equivalent to
     * POSITION(substring, string, position, occurrence) */
    private static RexNode convertInstr(SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> operands =
                convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
        final RelDataType type =
                cx.getValidator().getValidatedNodeType(call);
        final List<RexNode> exprs = new ArrayList<>();
        switch (call.operandCount()) {
            // Must reverse order of first 2 operands.
            case 2:
                exprs.add(operands.get(1)); // Substring
                exprs.add(operands.get(0)); // String
                break;
            case 3:
                exprs.add(operands.get(1)); // Substring
                exprs.add(operands.get(0)); // String
                exprs.add(operands.get(2)); // Position
                break;
            case 4:
                exprs.add(operands.get(1)); // Substring
                exprs.add(operands.get(0)); // String
                exprs.add(operands.get(2)); // Position
                exprs.add(operands.get(3)); // Occurrence
                break;
            default:
                throw new UnsupportedOperationException("Position does not accept "
                        + call.operandCount() + " operands");
        }
        return rexBuilder.makeCall(call.getParserPosition(), type, SqlStdOperatorTable.POSITION, exprs);
    }

    /** Converts a call to the DECODE function. */
    private static RexNode convertDecode(SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> operands =
                convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
        final RelDataType type =
                cx.getValidator().getValidatedNodeType(call);
        final List<RexNode> exprs = new ArrayList<>();
        for (int i = 1; i < operands.size() - 1; i += 2) {
            exprs.add(
                    RelOptUtil.isDistinctFrom(rexBuilder, operands.get(0),
                            operands.get(i), true));
            exprs.add(operands.get(i + 1));
        }
        if (operands.size() % 2 == 0) {
            exprs.add(Util.last(operands));
        } else {
            exprs.add(rexBuilder.makeNullLiteral(type));
        }
        return rexBuilder.makeCall(call.getParserPosition(), type, SqlStdOperatorTable.CASE, exprs);
    }

    /** Converts a call to the IF function.
     *
     * <p>{@code IF(b, x, y)} &rarr; {@code CASE WHEN b THEN x ELSE y END}. */
    private static RexNode convertIf(SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> operands =
                convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
        final RelDataType type =
                cx.getValidator().getValidatedNodeType(call);
        return rexBuilder.makeCall(call.getParserPosition(), type, SqlStdOperatorTable.CASE, operands);
    }

    /** Converts an interval expression to a numeric multiplied by an interval
     * literal. */
    private static RexNode convertInterval(SqlRexContext cx, SqlCall call) {
        // "INTERVAL n HOUR" becomes "n * INTERVAL '1' HOUR"
        final SqlNode n = call.operand(0);
        final SqlIntervalQualifier intervalQualifier = call.operand(1);
        final SqlIntervalLiteral literal =
                SqlLiteral.createInterval(1, "1", intervalQualifier,
                        call.getParserPosition());
        final SqlCall multiply =
                SqlStdOperatorTable.MULTIPLY.createCall(call.getParserPosition(), n,
                        literal);
        return cx.convertExpression(multiply);
    }

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

    private static RexNode or(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.OR, a0, a1);
    }

    private static RexNode eq(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.EQUALS, a0, a1);
    }

    private static RexNode ge(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, a0,
                a1);
    }

    private static RexNode le(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN_OR_EQUAL, a0, a1);
    }

    private static RexNode and(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.AND, a0, a1);
    }

    private static RexNode divideInt(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0,
                                     RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.DIVIDE_INTEGER, a0, a1);
    }

    private static RexNode divide(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0,
                                  RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.DIVIDE, a0, a1);
    }

    private static RexNode plus(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.PLUS, a0, a1);
    }

    private static RexNode minus(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0, RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.MINUS, a0, a1);
    }

    private static RexNode multiply(SqlParserPos pos, RexBuilder rexBuilder, RexNode a0,
                                    RexNode a1) {
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.MULTIPLY, a0, a1);
    }

    private static RexNode case_(RexBuilder rexBuilder, RexNode... args) {
        return rexBuilder.makeCall(SqlStdOperatorTable.CASE, args);
    }

    // SqlNode helpers

    private static SqlCall plus(SqlParserPos pos, SqlNode a0, SqlNode a1) {
        return SqlStdOperatorTable.PLUS.createCall(pos, a0, a1);
    }

    /**
     * Converts a CASE expression.
     */
    public RexNode convertCase(
            SqlRexContext cx,
            SqlCase call) {
        SqlNodeList whenList = call.getWhenOperands();
        SqlNodeList thenList = call.getThenOperands();
        assert whenList.size() == thenList.size();

        RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> exprList = new ArrayList<>();
        final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
        final RexLiteral unknownLiteral =
                rexBuilder.makeNullLiteral(typeFactory.createSqlType(SqlTypeName.BOOLEAN));
        final RexLiteral nullLiteral =
                rexBuilder.makeNullLiteral(typeFactory.createSqlType(SqlTypeName.NULL));
        for (int i = 0; i < whenList.size(); i++) {
            if (SqlUtil.isNullLiteral(whenList.get(i), false)) {
                exprList.add(unknownLiteral);
            } else {
                exprList.add(cx.convertExpression(whenList.get(i)));
            }
            if (SqlUtil.isNullLiteral(thenList.get(i), false)) {
                exprList.add(nullLiteral);
            } else {
                exprList.add(cx.convertExpression(thenList.get(i)));
            }
        }
        SqlNode elseOperand = call.getElseOperand();
        if (SqlUtil.isNullLiteral(elseOperand, false)) {
            exprList.add(nullLiteral);
        } else {
            exprList.add(cx.convertExpression(requireNonNull(elseOperand, "elseOperand")));
        }

        RelDataType type =
                rexBuilder.deriveReturnType(call.getOperator(), exprList);
        for (int i : elseArgs(exprList.size())) {
            exprList.set(i,
                    rexBuilder.ensureType(
                            call.getParserPosition(), type, exprList.get(i), false));
        }
        return rexBuilder.makeCall(call.getParserPosition(), type, SqlStdOperatorTable.CASE, exprList);
    }

    public RexNode convertMultiset(
            SqlRexContext cx,
            SqlMultisetValueConstructor op,
            SqlCall call) {
        final RelDataType originalType =
                cx.getValidator().getValidatedNodeType(call);
        RexRangeRef rr = requireNonNull(cx.getSubQueryExpr(call));
        RelDataType msType = rr.getType().getFieldList().get(0).getType();
        RexNode expr =
                cx.getRexBuilder().makeInputRef(
                        msType,
                        rr.getOffset());
        if (msType.getComponentType() == null
                || !msType.getComponentType().isStruct()) {
            throw new AssertionError("componentType of " + msType
                    + " must be struct");
        }
        if (originalType.getComponentType() == null) {
            throw new AssertionError("componentType of " + originalType
                    + " must be struct");
        }
        if (!originalType.getComponentType().isStruct()) {
            // If the type is not a struct, the multiset operator will have
            // wrapped the type as a record. Add a call to the $SLICE operator
            // to compensate. For example,
            // if '<ms>' has type 'RECORD (INTEGER x) MULTISET',
            // then '$SLICE(<ms>) has type 'INTEGER MULTISET'.
            // This will be removed as the expression is translated.
            expr =
                    cx.getRexBuilder().makeCall(
                            call.getParserPosition(), originalType, SqlStdOperatorTable.SLICE,
                            ImmutableList.of(expr));
        }
        return expr;
    }

    public RexNode convertArray(
            SqlRexContext cx,
            SqlArrayValueConstructor op,
            SqlCall call) {
        return convertCall(cx, call);
    }

    public RexNode convertMap(
            SqlRexContext cx,
            SqlMapValueConstructor op,
            SqlCall call) {
        return convertCall(cx, call);
    }

    public RexNode convertMultisetQuery(
            SqlRexContext cx,
            SqlMultisetQueryConstructor op,
            SqlCall call) {
        final RelDataType originalType =
                cx.getValidator().getValidatedNodeType(call);
        RexRangeRef rr = requireNonNull(cx.getSubQueryExpr(call));
        RelDataType msType = rr.getType().getFieldList().get(0).getType();
        RexNode expr =
                cx.getRexBuilder().makeInputRef(
                        msType,
                        rr.getOffset());
        if (msType.getComponentType() == null) {
            throw new AssertionError("componentType of " + msType
                    + " must not be null");
        }
        if (originalType.getComponentType() == null) {
            throw new AssertionError("componentType of " + originalType
                    + " must not be null");
        }
        return expr;
    }

    public RexNode convertJdbc(
            SqlRexContext cx,
            SqlJdbcFunctionCall op,
            SqlCall call) {
        // Yuck!! The function definition contains arguments!
        // TODO: adopt a more conventional definition/instance structure
        final SqlCall convertedCall = op.getLookupCall();
        return cx.convertExpression(convertedCall);
    }

    protected RexNode convertCast(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx,
            final SqlCall call) {
        RelDataTypeFactory typeFactory = cx.getTypeFactory();
        final SqlValidator validator = cx.getValidator();
        final SqlKind kind = call.getKind();
        checkArgument(kind == SqlKind.CAST || kind == SqlKind.SAFE_CAST, kind);
        final boolean safe = kind == SqlKind.SAFE_CAST;
        final SqlNode left = call.operand(0);
        final SqlNode right = call.operand(1);
        final SqlLiteral format = call.getOperandList().size() > 2
                ? call.operand(2) : SqlLiteral.createNull(call.getParserPosition());

        final RexBuilder rexBuilder = cx.getRexBuilder();
        final RexNode arg = cx.convertExpression(left);
        final RexLiteral formatArg = (RexLiteral) cx.convertLiteral(format);

        if (right instanceof SqlIntervalQualifier) {
            final SqlIntervalQualifier intervalQualifier =
                    (SqlIntervalQualifier) right;
            if (left instanceof SqlIntervalLiteral) {
                RexLiteral sourceInterval =
                        (RexLiteral) cx.convertExpression(left);
                BigDecimal sourceValue =
                        (BigDecimal) sourceInterval.getValue();
                RexLiteral castedInterval =
                        rexBuilder.makeIntervalLiteral(sourceValue,
                                intervalQualifier);
                return castToValidatedType(
                        call.getParserPosition(), call, castedInterval, validator, rexBuilder, safe);
            } else if (left instanceof SqlNumericLiteral) {
                RexLiteral sourceInterval =
                        (RexLiteral) cx.convertExpression(left);
                BigDecimal sourceValue =
                        requireNonNull(sourceInterval.getValueAs(BigDecimal.class),
                                "sourceValue");
                final BigDecimal multiplier = intervalQualifier.getUnit().multiplier;
                RexLiteral castedInterval =
                        rexBuilder.makeIntervalLiteral(
                                SqlFunctions.multiply(sourceValue, multiplier),
                                intervalQualifier);
                return castToValidatedType(
                        call.getParserPosition(), call, castedInterval, validator, rexBuilder, safe);
            }
            RexNode value = cx.convertExpression(left);
            return castToValidatedType(
                    call.getParserPosition(), call, value, validator, rexBuilder, safe);
        }

        if (SqlUtil.isNullLiteral(left, false)) {
            validator.setValidatedNodeType(left, arg.getType());
            return arg;
        }
        final SqlDataTypeSpec dataType = (SqlDataTypeSpec) right;
        RelDataType type =
                SqlCastFunction.deriveType(cx.getTypeFactory(), arg.getType(),
                        dataType.deriveType(validator), safe);
        if (null != dataType.getCollectionsTypeName()) {
            RelDataType argComponentType = arg.getType().getComponentType();

            // arg.getType() may be ANY
            if (argComponentType == null) {
                argComponentType = dataType.getComponentTypeSpec().deriveType(validator);
            }

            requireNonNull(argComponentType, () -> "componentType of " + arg);

            RelDataType typeFinal = type;
            final RelDataType componentType =
                    requireNonNull(type.getComponentType(),
                            () -> "componentType of " + typeFinal);
            if (argComponentType.isStruct()
                    && !componentType.isStruct()) {
                RelDataType tt =
                        typeFactory.builder()
                                .add(argComponentType.getFieldList().get(0).getName(),
                                        componentType)
                                .build();
                tt = typeFactory.createTypeWithNullability(tt, componentType.isNullable());
                boolean isn = type.isNullable();
                type = typeFactory.createMultisetType(tt, -1);
                type = typeFactory.createTypeWithNullability(type, isn);
            }
        }
        return rexBuilder.makeCast(call.getParserPosition(), type, arg, safe, safe, formatArg);
    }

    protected RexNode convertFloorCeil(SqlRexContext cx, SqlCall call) {
        final boolean floor = call.getKind() == SqlKind.FLOOR;
        final SqlParserPos pos = call.getParserPosition();
        // Rewrite floor, ceil of interval
        if (call.operandCount() == 1
                && call.operand(0) instanceof SqlIntervalLiteral) {
            final SqlIntervalLiteral literal = call.operand(0);
            SqlIntervalLiteral.IntervalValue interval =
                    literal.getValueAs(SqlIntervalLiteral.IntervalValue.class);
            BigDecimal val =
                    interval.getIntervalQualifier().getStartUnit().multiplier;
            RexNode rexInterval = cx.convertExpression(literal);

            final RexBuilder rexBuilder = cx.getRexBuilder();
            RexNode zero = rexBuilder.makeExactLiteral(BigDecimal.valueOf(0));
            RexNode cond = ge(pos, rexBuilder, rexInterval, zero);

            RexNode pad =
                    rexBuilder.makeExactLiteral(val.subtract(BigDecimal.ONE));
            RexNode cast =
                    rexBuilder.makeReinterpretCast(pos, rexInterval.getType(), pad,
                            rexBuilder.makeLiteral(false));
            RexNode sum =
                    floor ? minus(pos, rexBuilder, rexInterval, cast)
                            : plus(pos, rexBuilder, rexInterval, cast);

            RexNode kase = floor
                    ? case_(rexBuilder, rexInterval, cond, sum)
                    : case_(rexBuilder, sum, cond, rexInterval);

            RexNode factor = rexBuilder.makeExactLiteral(val);
            RexNode div = divideInt(pos, rexBuilder, kase, factor);
            return multiply(pos, rexBuilder, div, factor);
        }

        // normal floor, ceil function
        return convertFunction(cx, (SqlFunction) call.getOperator(), call);
    }

    protected RexNode convertCharset(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx, SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final SqlParserPos pos = call.getParserPosition();
        final SqlNode expr = call.operand(0);
        final SqlOperator op = call.getOperator();
        final String srcCharset;
        final String destCharset;
        switch (op.getKind()) {
            case CONVERT:
                srcCharset = call.operand(1).toString();
                destCharset = call.operand(2).toString();
                return rexBuilder.makeCall(pos, op,
                        cx.convertExpression(expr),
                        rexBuilder.makeLiteral(srcCharset),
                        rexBuilder.makeLiteral(destCharset));
            case CONVERT_ORACLE:
                destCharset = call.operand(1).toString();
                switch (call.operandCount()) {
                    case 2:
                        // when srcCharsetName is not specified
                        return rexBuilder.makeCall(pos, op,
                                cx.convertExpression(expr),
                                rexBuilder.makeLiteral(destCharset));
                    default:
                        srcCharset = call.operand(2).toString();
                        return rexBuilder.makeCall(pos, op,
                                cx.convertExpression(expr),
                                rexBuilder.makeLiteral(destCharset),
                                rexBuilder.makeLiteral(srcCharset));
                }
            default:
                throw Util.unexpected(op.getKind());
        }
    }

    protected RexNode translateCharset(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx, SqlCall call) {
        final SqlParserPos pos = call.getParserPosition();
        final SqlNode expr = call.operand(0);
        final String transcodingName = call.operand(1).toString();
        final RexBuilder rexBuilder = cx.getRexBuilder();
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.TRANSLATE,
                cx.convertExpression(expr),
                rexBuilder.makeLiteral(transcodingName));
    }

    /**
     * Converts a call to the {@code EXTRACT} function.
     *
     * <p>Called automatically via reflection.
     */
    public RexNode convertExtract(
            SqlRexContext cx,
            SqlExtractFunction op,
            SqlCall call) {
        return convertFunction(cx, (SqlFunction) call.getOperator(), call);
    }

    @SuppressWarnings("unused")
    private static RexNode mod(SqlParserPos pos, RexBuilder rexBuilder,
                               RelDataType resType, RexNode res,
                               BigDecimal val) {
        if (val.equals(BigDecimal.ONE)) {
            return res;
        }
        return rexBuilder.makeCall(pos, SqlStdOperatorTable.MOD, res,
                rexBuilder.makeExactLiteral(val, resType));
    }

    private static RexNode divide(SqlParserPos pos, RexBuilder rexBuilder, RexNode res,
                                  BigDecimal val) {
        if (val.equals(BigDecimal.ONE)) {
            return res;
        }
        // If val is between 0 and 1, rather than divide by val, multiply by its
        // reciprocal. For example, rather than divide by 0.001 multiply by 1000.
        if (val.compareTo(BigDecimal.ONE) < 0
                && val.signum() == 1) {
            try {
                final BigDecimal reciprocal =
                        BigDecimal.ONE.divide(val, RoundingMode.UNNECESSARY);
                return multiply(pos, rexBuilder, res,
                        rexBuilder.makeExactLiteral(reciprocal));
            } catch (ArithmeticException e) {
                // ignore - reciprocal is not an integer
            }
        }
        return divide(pos, rexBuilder, res, rexBuilder.makeExactLiteral(val));
    }

    public RexNode convertDatetimeMinus(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx,
            SqlDatetimeSubtractionOperator op,
            SqlCall call) {
        // Rewrite datetime minus
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> exprs =
                convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);

        final RelDataType resType =
                cx.getValidator().getValidatedNodeType(call);
        return rexBuilder.makeCall(call.getParserPosition(), resType, op, exprs.subList(0, 2));
    }

    public RexNode convertFunction(
            SqlRexContext cx,
            SqlFunction fun,
            SqlCall call) {
        final List<RexNode> exprs =
                convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
        if (fun.getFunctionType() == SqlFunctionCategory.USER_DEFINED_CONSTRUCTOR) {
            return makeConstructorCall(cx, fun, exprs);
        }
        RelDataType returnType =
                cx.getValidator().getValidatedNodeTypeIfKnown(call);
        if (returnType == null) {
            returnType = cx.getRexBuilder().deriveReturnType(fun, exprs);
        }
        return cx.getRexBuilder().makeCall(call.getParserPosition(), returnType, fun, exprs);
    }

    public RexNode convertWindowFunction(
            SqlRexContext cx,
            SqlWindowTableFunction fun,
            SqlCall call) {
        // The first operand of window function is actually a query, skip that.
        final List<SqlNode> operands = Util.skip(call.getOperandList());
        final List<RexNode> exprs =
                convertOperands(cx, call, operands,
                        SqlOperandTypeChecker.Consistency.NONE);
        RelDataType returnType =
                cx.getValidator().getValidatedNodeTypeIfKnown(call);
        if (returnType == null) {
            returnType = cx.getRexBuilder().deriveReturnType(fun, exprs);
        }
        return cx.getRexBuilder().makeCall(call.getParserPosition(), returnType, fun, exprs);
    }

    public RexNode convertJsonValueFunction(
            SqlRexContext cx, SqlJsonValueFunction fun, SqlCall call) {
        return convertJsonReturningFunction(
                cx,
                fun,
                call,
                SqlJsonValueFunction::hasExplicitTypeSpec,
                SqlJsonValueFunction::removeTypeSpecOperands);
    }

    public RexNode convertJsonQueryFunction(
            SqlRexContext cx, SqlJsonQueryFunction fun, SqlCall call) {
        return convertJsonReturningFunction(
                cx,
                fun,
                call,
                SqlJsonQueryFunction::hasExplicitTypeSpec,
                SqlJsonQueryFunction::removeTypeSpecOperands);
    }

    public RexNode convertJsonReturningFunction(
            SqlRexContext cx,
            SqlFunction fun,
            SqlCall call,
            Predicate<List<SqlNode>> hasExplicitTypeSpec,
            Function<SqlCall, List<SqlNode>> removeTypeSpecOperands) {
        // For Expression with explicit return type:
        // i.e. json_query('{"foo":"bar"}', 'lax $.foo', returning varchar(2000))
        // use the specified type as the return type.
        List<SqlNode> operands = call.getOperandList();
        boolean hasExplicitReturningType = hasExplicitTypeSpec.test(operands);
        if (hasExplicitReturningType) {
            operands = removeTypeSpecOperands.apply(call);
        }
        final List<RexNode> exprs =
                convertOperands(cx, call, operands, SqlOperandTypeChecker.Consistency.NONE);
        RelDataType returnType = cx.getValidator().getValidatedNodeTypeIfKnown(call);
        requireNonNull(returnType, () -> "Unable to get type of " + call);
        return cx.getRexBuilder().makeCall(call.getParserPosition(), returnType, fun, exprs);
    }

    public RexNode convertSequenceValue(
            SqlRexContext cx,
            SqlSequenceValueOperator fun,
            SqlCall call) {
        final List<SqlNode> operands = call.getOperandList();
        assert operands.size() == 1;
        assert operands.get(0) instanceof SqlIdentifier;
        final SqlIdentifier id = (SqlIdentifier) operands.get(0);
        final String key = Util.listToString(id.names);
        RelDataType returnType =
                cx.getValidator().getValidatedNodeType(call);
        return cx.getRexBuilder().makeCall(call.getParserPosition(), returnType, fun,
                ImmutableList.of(cx.getRexBuilder().makeLiteral(key)));
    }

    public RexNode convertAggregateFunction(
            SqlRexContext cx,
            SqlAggFunction fun,
            SqlCall call) {
        final List<RexNode> exprs;
        if (call.isCountStar()) {
            exprs = ImmutableList.of();
        } else {
            exprs = convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
        }
        RelDataType returnType =
                cx.getValidator().getValidatedNodeTypeIfKnown(call);
        final boolean hasEmptyGroup = cx.hasEmptyGroup();
        if (returnType == null) {
            RexCallBinding binding =
                    new RexCallBinding(cx.getTypeFactory(), fun, exprs,
                            ImmutableList.of()) {
                        @Override public boolean hasEmptyGroup() {
                            return hasEmptyGroup;
                        }
                    };
            returnType = fun.inferReturnType(binding);
        }
        return cx.getRexBuilder().makeCall(call.getParserPosition(), returnType, fun, exprs);
    }

    private static RexNode makeConstructorCall(
            SqlRexContext cx,
            SqlFunction constructor,
            List<RexNode> exprs) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        RelDataType type = rexBuilder.deriveReturnType(constructor, exprs);

        int n = type.getFieldCount();
        ImmutableList.Builder<RexNode> initializationExprs =
                ImmutableList.builder();
        final InitializerContext initializerContext = new InitializerContext() {
            @Override public RexBuilder getRexBuilder() {
                return rexBuilder;
            }

            @Override public SqlNode validateExpression(RelDataType rowType, SqlNode expr) {
                throw new UnsupportedOperationException();
            }

            @Override public RexNode convertExpression(SqlNode e) {
                throw new UnsupportedOperationException();
            }
        };
        for (int i = 0; i < n; ++i) {
            initializationExprs.add(
                    cx.getInitializerExpressionFactory().newAttributeInitializer(
                            type, constructor, i, exprs, initializerContext));
        }

        List<RexNode> defaultCasts =
                RexUtil.generateCastExpressions(
                        rexBuilder,
                        type,
                        initializationExprs.build());

        return rexBuilder.makeNewInvocation(type, defaultCasts);
    }

    private RexNode convertItem(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx,
            SqlCall call) {
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final SqlOperator op = call.getOperator();
        SqlOperandTypeChecker operandTypeChecker = op.getOperandTypeChecker();
        final SqlOperandTypeChecker.Consistency consistency =
                operandTypeChecker == null
                        ? SqlOperandTypeChecker.Consistency.NONE
                        : operandTypeChecker.getConsistency();
        final List<RexNode> exprs = convertOperands(cx, call, consistency);

        final RelDataType collectionType = exprs.get(0).getType();
        final boolean isRowTypeField = SqlTypeUtil.isRow(collectionType);
        final boolean isNumericIndex = SqlTypeUtil.isIntType(exprs.get(1).getType());

        if (isRowTypeField && isNumericIndex) {
            final SqlOperatorBinding opBinding =
                    new RexCallBinding(cx.getTypeFactory(), op, exprs, ImmutableList.of());
            final RelDataType operandType = opBinding.getOperandType(0);

            final Integer index = opBinding.getOperandLiteralValue(1, Integer.class);
            if (index == null || index < 1 || index > operandType.getFieldCount()) {
                throw new AssertionError("Cannot access field at position "
                        + index + " within ROW type: " + operandType);
            } else {
                RelDataTypeField relDataTypeField = collectionType.getFieldList().get(index - 1);
                return rexBuilder.makeFieldAccess(
                        exprs.get(0), relDataTypeField.getName(), false);
            }
        }
        RelDataType type = rexBuilder.deriveReturnType(op, exprs);
        return rexBuilder.makeCall(call.getParserPosition(), type, op, RexUtil.flatten(exprs, op));
    }

    /**
     * Converts a call to an operator into a {@link RexCall} to the same
     * operator.
     *
     * <p>Called automatically via reflection.
     *
     * @param cx   Context
     * @param call Call
     * @return Rex call
     */
    public RexNode convertCall(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx,
            SqlCall call) {
        final SqlOperator op = call.getOperator();
        final RexBuilder rexBuilder = cx.getRexBuilder();
        SqlOperandTypeChecker operandTypeChecker = op.getOperandTypeChecker();
        final SqlOperandTypeChecker.Consistency consistency =
                operandTypeChecker == null
                        ? SqlOperandTypeChecker.Consistency.NONE
                        : operandTypeChecker.getConsistency();
        final List<RexNode> exprs = convertOperands(cx, call, consistency);
        RelDataType type = rexBuilder.deriveReturnType(op, exprs);

        // Expand 'ROW (x0, x1, ...) = ROW (y0, y1, ...)'
        // to 'x0 = y0 AND x1 = y1 AND ...'
        // If there are casts to ROW, apply them fieldwise:
        // 'CAST(ROW(x0, x1) AS (t0, t1)) = ROW(y0, y1)' becomes
        // 'CAST(x0 as t0) = y0 AND CAST(x1 as t1) = y1'
        if (op.kind == SqlKind.EQUALS) {
            // For every cast one list with the types of all fields
            ArrayDeque<List<RelDataTypeField>> leftTypes = new ArrayDeque<>();
            ArrayDeque<List<RelDataTypeField>> rightTypes = new ArrayDeque<>();
            RexNode expr0 = exprs.get(0);
            RexNode expr1 = exprs.get(1);
            while (expr0.getKind() == SqlKind.CAST) {
                RexCall cast = (RexCall) expr0;
                if (cast.type.getSqlTypeName() == SqlTypeName.ROW) {
                    expr0 = ((RexCall) expr0).operands.get(0);
                    leftTypes.add(cast.type.getFieldList());
                } else {
                    break;
                }
            }
            while (expr1.getKind() == SqlKind.CAST) {
                RexCall cast = (RexCall) expr1;
                if (cast.type.getSqlTypeName() == SqlTypeName.ROW) {
                    expr1 = ((RexCall) expr1).operands.get(0);
                    rightTypes.add(cast.type.getFieldList());
                } else {
                    break;
                }
            }
            if (expr0.getKind() == SqlKind.ROW && expr1.getKind() == SqlKind.ROW) {
                final RexCall call0 = (RexCall) expr0;
                final RexCall call1 = (RexCall) expr1;

                List<RexNode> expr0Operands = call0.getOperands();
                // Insert the casts in reverse order
                while (!leftTypes.isEmpty()) {
                    List<RexNode> converted = new ArrayList<>();
                    List<RelDataTypeField> types = leftTypes.removeLast();
                    Pair.forEach(types, expr0Operands, (x, y) ->
                            converted.add(
                                    rexBuilder.makeAbstractCast(
                                            call.getParserPosition(), x.getType(), y, false)));
                    expr0Operands = converted;
                }
                List<RexNode> expr1Operands = call1.getOperands();
                while (!rightTypes.isEmpty()) {
                    List<RexNode> converted = new ArrayList<>();
                    List<RelDataTypeField> types = rightTypes.removeLast();
                    Pair.forEach(types, expr1Operands, (x, y) ->
                            converted.add(
                                    rexBuilder.makeAbstractCast(
                                            call.getParserPosition(), x.getType(), y, false)));
                    expr1Operands = converted;
                }

                final List<RexNode> eqList = new ArrayList<>();
                Pair.forEach(expr0Operands, expr1Operands, (x, y) ->
                        eqList.add(rexBuilder.makeCall(call.getParserPosition(), op, x, y)));
                return RexUtil.composeConjunction(rexBuilder, eqList);
            }
        }
        return rexBuilder.makeCall(call.getParserPosition(), type, op, RexUtil.flatten(exprs, op));
    }

    private static List<Integer> elseArgs(int count) {
        // If list is odd, e.g. [0, 1, 2, 3, 4] we get [1, 3, 4]
        // If list is even, e.g. [0, 1, 2, 3, 4, 5] we get [2, 4, 5]
        final List<Integer> list = new ArrayList<>();
        for (int i = count % 2;;) {
            list.add(i);
            i += 2;
            if (i >= count) {
                list.add(i - 1);
                break;
            }
        }
        return list;
    }

    private static List<RexNode> convertOperands(SqlRexContext cx,
                                                 SqlCall call, SqlOperandTypeChecker.Consistency consistency) {
        List<SqlNode> operandList;
        if (call.getOperator() instanceof SqlTableFunction) {
            // skip set semantic table node of table function
            operandList =
                    call.getOperandList().stream().filter(
                                    operand -> operand.getKind() != SqlKind.SET_SEMANTICS_TABLE)
                            .collect(Collectors.toList());
        } else {
            operandList = call.getOperandList();
        }
        return convertOperands(cx, call, operandList, consistency);
    }

    private static List<RexNode> convertOperands(SqlRexContext cx,
                                                 SqlCall call, List<SqlNode> nodes,
                                                 SqlOperandTypeChecker.Consistency consistency) {
        final List<RexNode> exprs = new ArrayList<>();
        for (SqlNode node : nodes) {
            exprs.add(cx.convertExpression(node));
        }
        final List<RelDataType> operandTypes =
                cx.getValidator().getValidatedOperandTypes(call);
        if (operandTypes != null) {
            final List<RexNode> oldExprs = new ArrayList<>(exprs);
            exprs.clear();
            Pair.forEach(oldExprs, operandTypes, (expr, type) ->
                    exprs.add(cx.getRexBuilder().ensureType(call.getParserPosition(), type, expr, true)));
        }
        if (exprs.size() > 1) {
            final RelDataType type =
                    consistentType(cx, consistency, RexUtil.types(exprs));
            if (type != null) {
                final List<RexNode> oldExprs = new ArrayList<>(exprs);
                exprs.clear();
                for (RexNode expr : oldExprs) {
                    exprs.add(cx.getRexBuilder().ensureType(call.getParserPosition(), type, expr, true));
                }
            }
        }
        return exprs;
    }

    private static @Nullable RelDataType consistentType(SqlRexContext cx,
                                                        SqlOperandTypeChecker.Consistency consistency, List<RelDataType> types) {
        switch (consistency) {
            case COMPARE:
                if (SqlTypeUtil.areSameFamily(types)
                        && (types.stream().allMatch(RelDataTypeFactoryImpl::isJavaType)
                        || types.stream().noneMatch(RelDataTypeFactoryImpl::isJavaType))) {
                    // All arguments are of same family. No need for explicit casts.
                    return null;
                }
                final List<RelDataType> nonCharacterTypes = new ArrayList<>();
                for (RelDataType type : types) {
                    if (type.getFamily() != SqlTypeFamily.CHARACTER) {
                        nonCharacterTypes.add(type);
                    }
                }
                if (!nonCharacterTypes.isEmpty()) {
                    final int typeCount = types.size();
                    types = nonCharacterTypes;
                    if (nonCharacterTypes.size() < typeCount) {
                        final RelDataTypeFamily family =
                                nonCharacterTypes.get(0).getFamily();
                        if (family instanceof SqlTypeFamily) {
                            // The character arguments might be larger than the numeric
                            // argument. Give ourselves some headroom.
                            switch ((SqlTypeFamily) family) {
                                case INTEGER:
                                case NUMERIC:
                                    nonCharacterTypes.add(
                                            cx.getTypeFactory().createSqlType(SqlTypeName.BIGINT));
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
                // fall through
            case LEAST_RESTRICTIVE:
                return cx.getTypeFactory().leastRestrictive(types);
            default:
                return null;
        }
    }

    private RexNode convertPlus(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx, SqlCall call) {
        final RexNode rex = convertCall(cx, call);
        switch (rex.getType().getSqlTypeName()) {
            case DATE:
            case TIME:
            case TIMESTAMP:
                // Use special "+" operator for datetime + interval.
                // Re-order operands, if necessary, so that interval is second.
                final RexBuilder rexBuilder = cx.getRexBuilder();
                List<RexNode> operands = ((RexCall) rex).getOperands();
                if (operands.size() == 2) {
                    final SqlTypeName sqlTypeName = operands.get(0).getType().getSqlTypeName();
                    switch (sqlTypeName) {
                        case INTERVAL_YEAR:
                        case INTERVAL_YEAR_MONTH:
                        case INTERVAL_MONTH:
                        case INTERVAL_DAY:
                        case INTERVAL_DAY_HOUR:
                        case INTERVAL_DAY_MINUTE:
                        case INTERVAL_DAY_SECOND:
                        case INTERVAL_HOUR:
                        case INTERVAL_HOUR_MINUTE:
                        case INTERVAL_HOUR_SECOND:
                        case INTERVAL_MINUTE:
                        case INTERVAL_MINUTE_SECOND:
                        case INTERVAL_SECOND:
                            operands = ImmutableList.of(operands.get(1), operands.get(0));
                            break;
                        default:
                            break;
                    }
                }
                return rexBuilder.makeCall(call.getParserPosition(), rex.getType(),
                        SqlStdOperatorTable.DATETIME_PLUS, operands);
            default:
                return rex;
        }
    }

    private RexNode convertIsDistinctFrom(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx,
            SqlCall call,
            boolean neg) {
        RexNode op0 = cx.convertExpression(call.operand(0));
        RexNode op1 = cx.convertExpression(call.operand(1));
        return RelOptUtil.isDistinctFrom(
                cx.getRexBuilder(), op0, op1, neg);
    }

    /**
     * Converts a BETWEEN expression.
     *
     * <p>Called automatically via reflection.
     */
    public RexNode convertBetween(
            SqlRexContext cx,
            SqlBetweenOperator op,
            SqlCall call) {
        SqlOperandTypeChecker operandTypeChecker = op.getOperandTypeChecker();
        final SqlOperandTypeChecker.Consistency consistency =
                operandTypeChecker == null
                        ? SqlOperandTypeChecker.Consistency.NONE
                        : operandTypeChecker.getConsistency();
        final SqlParserPos pos = call.getParserPosition();
        final List<RexNode> list =
                convertOperands(cx, call,
                        consistency);
        final RexNode x = list.get(SqlBetweenOperator.VALUE_OPERAND);
        final RexNode y = list.get(SqlBetweenOperator.LOWER_OPERAND);
        final RexNode z = list.get(SqlBetweenOperator.UPPER_OPERAND);

        final RexBuilder rexBuilder = cx.getRexBuilder();
        RexNode ge1 = ge(pos, rexBuilder, x, y);
        RexNode le1 = le(pos, rexBuilder, x, z);
        RexNode and1 = and(pos, rexBuilder, ge1, le1);

        RexNode res;
        final SqlBetweenOperator.Flag symmetric = op.flag;
        switch (symmetric) {
            case ASYMMETRIC:
                res = and1;
                break;
            case SYMMETRIC:
                RexNode ge2 = ge(pos, rexBuilder, x, z);
                RexNode le2 = le(pos, rexBuilder, x, y);
                RexNode and2 = and(pos, rexBuilder, ge2, le2);
                res = or(pos, rexBuilder, and1, and2);
                break;
            default:
                throw Util.unexpected(symmetric);
        }
        final SqlBetweenOperator betweenOp =
                (SqlBetweenOperator) call.getOperator();
        if (betweenOp.isNegated()) {
            res = rexBuilder.makeCall(call.getParserPosition(), SqlStdOperatorTable.NOT, res);
        }
        return res;
    }

    /**
     * Converts a SUBSTRING expression.
     *
     * <p>Called automatically via reflection.
     */
    public RexNode convertSubstring(
            SqlRexContext cx,
            SqlSubstringFunction op,
            SqlCall call) {
        final SqlLibrary library =
                cx.getValidator().config().conformance().semantics();
        final SqlBasicCall basicCall = (SqlBasicCall) call;
        switch (library) {
            case BIG_QUERY:
                return toRex(cx, basicCall, SqlLibraryOperators.SUBSTR_BIG_QUERY);
            case MYSQL:
                return toRex(cx, basicCall, SqlLibraryOperators.SUBSTR_MYSQL);
            case ORACLE:
                return toRex(cx, basicCall, SqlLibraryOperators.SUBSTR_ORACLE);
            case POSTGRESQL:
            default:
                return convertFunction(cx, op, call);
        }
    }

    private RexNode toRex(SqlRexContext cx, SqlBasicCall call, SqlFunction f) {
        final SqlCall call2 =
                new SqlBasicCall(f, call.getOperandList(), call.getParserPosition());
        final SqlRexConvertlet convertlet = requireNonNull(get(call2));
        return convertlet.convertCall(cx, call2);
    }

    /**
     * Converts a LiteralChain expression: that is, concatenates the operands
     * immediately, to produce a single literal string.
     *
     * <p>Called automatically via reflection.
     */
    public RexNode convertLiteralChain(
            SqlRexContext cx,
            SqlLiteralChainOperator op,
            SqlCall call) {
        Util.discard(cx);

        SqlLiteral sum = SqlLiteralChainOperator.concatenateOperands(call);
        return cx.convertLiteral(sum);
    }

    /**
     * Converts a ROW.
     *
     * <p>Called automatically via reflection.
     */
    public RexNode convertRow(
            SqlRexContext cx,
            SqlRowOperator op,
            SqlCall call) {
        if (cx.getValidator().getValidatedNodeType(call).getSqlTypeName()
                != SqlTypeName.COLUMN_LIST) {
            return convertCall(cx, call);
        }
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final List<RexNode> columns = new ArrayList<>();
        for (String operand : SqlIdentifier.simpleNames(call.getOperandList())) {
            columns.add(rexBuilder.makeLiteral(operand));
        }
        final RelDataType type =
                rexBuilder.deriveReturnType(SqlStdOperatorTable.COLUMN_LIST, columns);
        return rexBuilder.makeCall(
                call.getParserPosition(), type, SqlStdOperatorTable.COLUMN_LIST, columns);
    }

    /**
     * Converts a call to OVERLAPS.
     *
     * <p>Called automatically via reflection.
     */
    public RexNode convertOverlaps(
            SqlRexContext cx,
            SqlOverlapsOperator op,
            SqlCall call) {
        // for intervals [t0, t1] overlaps [t2, t3], we can find if the
        // intervals overlaps by: ~(t1 < t2 or t3 < t0)
        assert call.getOperandList().size() == 2;

        final Pair<RexNode, RexNode> left =
                convertOverlapsOperand(cx, call.getParserPosition(), call.operand(0));
        final RexNode r0 = left.left;
        final RexNode r1 = left.right;
        final Pair<RexNode, RexNode> right =
                convertOverlapsOperand(cx, call.getParserPosition(), call.operand(1));
        final RexNode r2 = right.left;
        final RexNode r3 = right.right;

        // Sort end points into start and end, such that (s0 <= e0) and (s1 <= e1).
        final RexBuilder rexBuilder = cx.getRexBuilder();
        final SqlParserPos pos = call.getParserPosition();
        RexNode leftSwap = le(pos, rexBuilder, r0, r1);
        final RexNode s0 = case_(rexBuilder, leftSwap, r0, r1);
        final RexNode e0 = case_(rexBuilder, leftSwap, r1, r0);
        RexNode rightSwap = le(pos, rexBuilder, r2, r3);
        final RexNode s1 = case_(rexBuilder, rightSwap, r2, r3);
        final RexNode e1 = case_(rexBuilder, rightSwap, r3, r2);
        // (e0 >= s1) AND (e1 >= s0)
        switch (op.kind) {
            case OVERLAPS:
                return and(pos, rexBuilder,
                        ge(pos, rexBuilder, e0, s1),
                        ge(pos, rexBuilder, e1, s0));
            case CONTAINS:
                return and(pos, rexBuilder,
                        le(pos, rexBuilder, s0, s1),
                        ge(pos, rexBuilder, e0, e1));
            case PERIOD_EQUALS:
                return and(pos, rexBuilder,
                        eq(pos, rexBuilder, s0, s1),
                        eq(pos, rexBuilder, e0, e1));
            case PRECEDES:
                return le(pos, rexBuilder, e0, s1);
            case IMMEDIATELY_PRECEDES:
                return eq(pos, rexBuilder, e0, s1);
            case SUCCEEDS:
                return ge(pos, rexBuilder, s0, e1);
            case IMMEDIATELY_SUCCEEDS:
                return eq(pos, rexBuilder, s0, e1);
            default:
                throw new AssertionError(op);
        }
    }

    private static Pair<RexNode, RexNode> convertOverlapsOperand(SqlRexContext cx,
                                                                 SqlParserPos pos, SqlNode operand) {
        final SqlNode a0;
        final SqlNode a1;
        switch (operand.getKind()) {
            case ROW:
                a0 = ((SqlCall) operand).operand(0);
                final SqlNode a10 = ((SqlCall) operand).operand(1);
                final RelDataType t1 = cx.getValidator().getValidatedNodeType(a10);
                if (SqlTypeUtil.isInterval(t1)) {
                    // make t1 = t0 + t1 when t1 is an interval.
                    a1 = plus(pos, a0, a10);
                } else {
                    a1 = a10;
                }
                break;
            default:
                a0 = operand;
                a1 = operand;
        }

        final RexNode r0 = cx.convertExpression(a0);
        final RexNode r1 = cx.convertExpression(a1);
        return Pair.of(r0, r1);
    }

    @Deprecated // to be removed before 2.0
    public RexNode castToValidatedType(
            @UnknownInitialization ConvertletTable this,
            SqlRexContext cx,
            SqlCall call,
            RexNode value) {
        return castToValidatedType(call.getParserPosition(), call, value, cx.getValidator(),
                cx.getRexBuilder(), false);
    }

    @Deprecated // to be removed before 2.0
    public static RexNode castToValidatedType(SqlNode node, RexNode e,
                                              SqlValidator validator, RexBuilder rexBuilder) {
        return castToValidatedType(node.getParserPosition(), node, e, validator, rexBuilder, false);
    }

    /**
     * Casts a RexNode value to the validated type of a SqlCall. If the value
     * was already of the validated type, then the value is returned without an
     * additional cast.
     */
    public static RexNode castToValidatedType(SqlParserPos pos, SqlNode node, RexNode e,
                                              SqlValidator validator, RexBuilder rexBuilder, boolean safe) {
        final RelDataType type = validator.getValidatedNodeType(node);
        if (e.getType() == type) {
            return e;
        }
        return rexBuilder.makeCast(pos, type, e, safe, safe);
    }

    /** Convertlet that handles {@code COVAR_POP}, {@code COVAR_SAMP},
     * {@code REGR_SXX}, {@code REGR_SYY} windowed aggregate functions.
     */
    private static class RegrCovarianceConvertlet implements SqlRexConvertlet {
        private final SqlKind kind;

        RegrCovarianceConvertlet(SqlKind kind) {
            this.kind = kind;
        }

        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            assert call.operandCount() == 2;
            final SqlNode arg1 = call.operand(0);
            final SqlNode arg2 = call.operand(1);
            final SqlParserPos pos = call.getParserPosition();
            final SqlNode expr;
            final RelDataType type =
                    cx.getValidator().getValidatedNodeType(call);
            switch (kind) {
                case COVAR_POP:
                    expr = expandCovariance(pos, arg1, arg2, null, type, cx, true);
                    break;
                case COVAR_SAMP:
                    expr = expandCovariance(pos, arg1, arg2, null, type, cx, false);
                    break;
                case REGR_SXX:
                    expr = expandRegrSzz(pos, arg2, arg1, type, cx, true);
                    break;
                case REGR_SYY:
                    expr = expandRegrSzz(pos, arg1, arg2, type, cx, true);
                    break;
                default:
                    throw Util.unexpected(kind);
            }
            RexNode rex = cx.convertExpression(expr);
            return cx.getRexBuilder().ensureType(call.getParserPosition(), type, rex, true);
        }

        private static SqlNode expandRegrSzz(
                final SqlParserPos pos,
                final SqlNode arg1, final SqlNode arg2,
                final RelDataType avgType, final SqlRexContext cx, boolean variance) {
            final SqlNode count =
                    SqlStdOperatorTable.REGR_COUNT.createCall(pos, arg1, arg2);
            final SqlNode varPop =
                    expandCovariance(pos, arg1, variance ? arg1 : arg2, arg2, avgType, cx, true);
            final RexNode varPopRex = cx.convertExpression(varPop);
            final SqlNode varPopCast;
            varPopCast = getCastedSqlNode(varPop, avgType, pos, varPopRex);
            return SqlStdOperatorTable.MULTIPLY.createCall(pos, varPopCast, count);
        }

        private static SqlNode expandCovariance(
                final SqlParserPos pos,
                final SqlNode arg0Input,
                final SqlNode arg1Input,
                final @Nullable SqlNode dependent,
                final RelDataType varType,
                final SqlRexContext cx,
                boolean biased) {
            // covar_pop(x1, x2) ==>
            //     (sum(x1 * x2) - sum(x2) * sum(x1) / count(x1, x2))
            //     / count(x1, x2)
            //
            // covar_samp(x1, x2) ==>
            //     (sum(x1 * x2) - sum(x1) * sum(x2) / count(x1, x2))
            //     / (count(x1, x2) - 1)
            final SqlLiteral nullLiteral = SqlLiteral.createNull(pos);
            final RelDataType highPrecision = ConvertletTable.AvgVarianceConvertlet.highPrecision(cx, varType);

            final RexNode arg0Rex = cx.convertExpression(arg0Input);
            final RexNode arg1Rex = cx.convertExpression(arg1Input);

            final SqlNode arg0 = getCastedSqlNode(arg0Input, highPrecision, pos, arg0Rex);
            final SqlNode arg1 = getCastedSqlNode(arg1Input, highPrecision, pos, arg1Rex);
            final SqlNode argSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg0, arg1);
            final SqlNode sumArgSquared;
            final SqlNode sum0;
            final SqlNode sum1;
            final SqlNode count;
            if (dependent == null) {
                sumArgSquared = SqlStdOperatorTable.SUM.createCall(pos, argSquared);
                sum0 = SqlStdOperatorTable.SUM.createCall(pos, arg0, arg1);
                sum1 = SqlStdOperatorTable.SUM.createCall(pos, arg1, arg0);
                count = SqlStdOperatorTable.REGR_COUNT.createCall(pos, arg0, arg1);
            } else {
                sumArgSquared = SqlStdOperatorTable.SUM.createCall(pos, argSquared, dependent);
                sum0 =
                        SqlStdOperatorTable.SUM.createCall(pos, arg0,
                                Objects.equals(dependent, arg0Input) ? arg1 : dependent);
                sum1 =
                        SqlStdOperatorTable.SUM.createCall(pos, arg1,
                                Objects.equals(dependent, arg1Input) ? arg0 : dependent);
                count =
                        SqlStdOperatorTable.REGR_COUNT.createCall(pos, arg0,
                                Objects.equals(dependent, arg0Input) ? arg1 : dependent);
            }

            final SqlNode sumSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, sum0, sum1);
            final SqlNode countCasted =
                    getCastedSqlNode(count, highPrecision, pos, cx.convertExpression(count));

            final SqlNode avgSumSquared =
                    SqlStdOperatorTable.DIVIDE.createCall(pos, sumSquared, countCasted);
            final SqlNode diff =
                    SqlStdOperatorTable.MINUS.createCall(pos, sumArgSquared, avgSumSquared);
            SqlNode denominator;
            if (biased) {
                denominator = countCasted;
            } else {
                final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
                denominator =
                        new SqlCase(pos, countCasted,
                                SqlNodeList.of(
                                        SqlStdOperatorTable.EQUALS.createCall(pos, countCasted, one)),
                                SqlNodeList.of(getCastedSqlNode(nullLiteral, highPrecision, pos, null)),
                                SqlStdOperatorTable.MINUS.createCall(pos, countCasted, one));
            }

            return SqlStdOperatorTable.DIVIDE.createCall(pos, diff, denominator);
        }

        private static SqlNode getCastedSqlNode(SqlNode argInput,
                                                RelDataType varType, SqlParserPos pos, @Nullable RexNode argRex) {
            if (argRex == null || argRex.getType().equals(varType)) {
                return argInput;
            }
            return CAST.createCall(pos, argInput,
                    SqlTypeUtil.convertTypeToSpec(varType));
        }
    }

    /** Convertlet that handles {@code AVG} and {@code VARIANCE}
     * windowed aggregate functions. */
    private static class AvgVarianceConvertlet implements SqlRexConvertlet {
        private final SqlKind kind;

        AvgVarianceConvertlet(SqlKind kind) {
            this.kind = kind;
        }

        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            assert call.operandCount() == 1;
            final SqlNode arg = call.operand(0);
            final SqlNode expr;
            final SqlParserPos pos = call.getParserPosition();
            final RelDataType type =
                    cx.getValidator().getValidatedNodeType(call);
            switch (kind) {
                case AVG:
                    expr = expandAvg(arg, type, cx);
                    break;
                case STDDEV_POP:
                    expr = expandVariance(pos, arg, type, cx, true, true);
                    break;
                case STDDEV_SAMP:
                    expr = expandVariance(pos, arg, type, cx, false, true);
                    break;
                case VAR_POP:
                    expr = expandVariance(pos, arg, type, cx, true, false);
                    break;
                case VAR_SAMP:
                    expr = expandVariance(pos, arg, type, cx, false, false);
                    break;
                default:
                    throw Util.unexpected(kind);
            }
            RexNode rex = cx.convertExpression(expr);
            return cx.getRexBuilder().ensureType(pos, type, rex, true);
        }

        private static SqlNode expandAvg(
                final SqlNode arg, final RelDataType avgType, final SqlRexContext cx) {
            final SqlParserPos pos = SqlParserPos.ZERO;
            final SqlNode sum =
                    SqlStdOperatorTable.SUM.createCall(pos, arg);
            final RexNode sumRex = cx.convertExpression(sum);
            final SqlNode sumCast;
            sumCast = getCastedSqlNode(sum, avgType, pos, sumRex);
            final SqlNode count =
                    SqlStdOperatorTable.COUNT.createCall(pos, arg);
            return SqlStdOperatorTable.DIVIDE.createCall(
                    pos, sumCast, count);
        }

        /**
         * Compute a higher precision version of a type.
         *
         * @return If type is a DECIMAL type, return a type with double the precision and scale
         * if possible.  Otherwise, return the type unchanged. */
        private static RelDataType highPrecision(final SqlRexContext cx, final RelDataType type) {
            if (type.getSqlTypeName() == SqlTypeName.DECIMAL) {
                RelDataTypeFactory typeFactory = cx.getValidator().getTypeFactory();
                return typeFactory.createSqlType(
                        type.getSqlTypeName(),
                        Math.min(
                                type.getPrecision() * 2,
                                typeFactory.getTypeSystem().getMaxPrecision(SqlTypeName.DECIMAL)),
                        Math.min(
                                type.getScale() * 2,
                                typeFactory.getTypeSystem().getMaxScale(SqlTypeName.DECIMAL)));
            }
            return type;
        }

        private static SqlNode expandVariance(
                final SqlParserPos pos,
                final SqlNode argInput,
                final RelDataType varType,
                final SqlRexContext cx,
                boolean biased,
                boolean sqrt) {
            // stddev_pop(x) ==>
            //   power(
            //     (sum(x * x) - sum(x) * sum(x) / count(x))
            //     / count(x),
            //     .5)
            //
            // stddev_samp(x) ==>
            //   power(
            //     (sum(x * x) - sum(x) * sum(x) / count(x))
            //     / (count(x) - 1),
            //     .5)
            //
            // var_pop(x) ==>
            //     (sum(x * x) - sum(x) * sum(x) / count(x))
            //     / count(x)
            //
            // var_samp(x) ==>
            //     (sum(x * x) - sum(x) * sum(x) / count(x))
            //     / (count(x) - 1)
            final RelDataType highPrecision = highPrecision(cx, varType);

            final SqlNode arg =
                    getCastedSqlNode(argInput, highPrecision, pos,
                            cx.convertExpression(argInput));

            final SqlNode argSquared =
                    SqlStdOperatorTable.MULTIPLY.createCall(pos, arg, arg);
            final SqlNode argSquaredCasted =
                    getCastedSqlNode(argSquared, highPrecision, pos,
                            cx.convertExpression(argSquared));
            final SqlNode sumArgSquared =
                    SqlStdOperatorTable.SUM.createCall(pos, argSquaredCasted);
            final SqlNode sumArgSquaredCasted =
                    getCastedSqlNode(sumArgSquared, highPrecision, pos,
                            cx.convertExpression(sumArgSquared));
            final SqlNode sum = SqlStdOperatorTable.SUM.createCall(pos, arg);
            final SqlNode sumCasted =
                    getCastedSqlNode(sum, highPrecision, pos, cx.convertExpression(sum));
            final SqlNode sumSquared =
                    SqlStdOperatorTable.MULTIPLY.createCall(pos, sumCasted, sumCasted);
            final SqlNode sumSquaredCasted =
                    getCastedSqlNode(sumSquared, highPrecision, pos,
                            cx.convertExpression(sumSquared));
            final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, arg);
            final SqlNode countCasted =
                    getCastedSqlNode(count, varType, pos, cx.convertExpression(count));
            final SqlNode avgSumSquared =
                    SqlStdOperatorTable.DIVIDE.createCall(pos, sumSquaredCasted,
                            countCasted);
            final SqlNode avgSumSquaredCasted =
                    getCastedSqlNode(avgSumSquared, highPrecision, pos,
                            cx.convertExpression(avgSumSquared));
            final SqlNode diff =
                    SqlStdOperatorTable.MINUS.createCall(pos, sumArgSquaredCasted,
                            avgSumSquaredCasted);
            final SqlNode diffCasted =
                    getCastedSqlNode(diff, highPrecision, pos, cx.convertExpression(diff));
            final SqlNode denominator;
            if (biased) {
                denominator = countCasted;
            } else {
                final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
                final SqlLiteral nullLiteral = SqlLiteral.createNull(pos);
                denominator =
                        new SqlCase(pos, count,
                                SqlNodeList.of(
                                        SqlStdOperatorTable.EQUALS.createCall(pos, count, one)),
                                SqlNodeList.of(getCastedSqlNode(nullLiteral, highPrecision, pos, null)),
                                SqlStdOperatorTable.MINUS.createCall(pos, count, one));
            }
            final SqlNode div =
                    SqlStdOperatorTable.DIVIDE.createCall(pos, diffCasted, denominator);
            final SqlNode divCasted =
                    getCastedSqlNode(div, highPrecision, pos, cx.convertExpression(div));

            SqlNode result = div;
            if (sqrt) {
                final SqlNumericLiteral half = SqlLiteral.createExactNumeric("0.5", pos);
                result = SqlStdOperatorTable.POWER.createCall(pos, divCasted, half);
            }
            return result;
        }

        private static SqlNode getCastedSqlNode(SqlNode argInput,
                                                RelDataType varType, SqlParserPos pos, @Nullable RexNode argRex) {
            if (argRex == null || argRex.getType().equals(varType)) {
                return argInput;
            }
            return CAST.createCall(pos, argInput,
                    SqlTypeUtil.convertTypeToSpec(varType));
        }
    }

    /** Convertlet that converts {@code LTRIM} and {@code RTRIM} to
     * {@code TRIM}. */
    private static class TrimConvertlet implements SqlRexConvertlet {
        private final SqlTrimFunction.Flag flag;

        TrimConvertlet(SqlTrimFunction.Flag flag) {
            this.flag = flag;
        }

        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final RexNode operand =
                    cx.convertExpression(call.getOperandList().get(0));
            return rexBuilder.makeCall(call.getParserPosition(), SqlStdOperatorTable.TRIM,
                    rexBuilder.makeFlag(flag), rexBuilder.makeLiteral(" "), operand);
        }
    }

    /** Convertlet that converts {@code GREATEST} and {@code LEAST}. */
    private static class GreatestConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            // Translate
            //   GREATEST(a, b, c, d)
            // to
            //   CASE
            //   WHEN a IS NULL OR b IS NULL OR c IS NULL OR d IS NULL
            //   THEN NULL
            //   WHEN a > b AND a > c AND a > d
            //   THEN a
            //   WHEN b > c AND b > d
            //   THEN b
            //   WHEN c > d
            //   THEN c
            //   ELSE d
            //   END
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final RelDataType type =
                    cx.getValidator().getValidatedNodeType(call);
            final SqlBinaryOperator op;
            switch (call.getKind()) {
                case GREATEST:
                    op = SqlStdOperatorTable.GREATER_THAN;
                    break;
                case LEAST:
                    op = SqlStdOperatorTable.LESS_THAN;
                    break;
                default:
                    throw new AssertionError();
            }
            final List<RexNode> exprs =
                    convertOperands(cx, call, SqlOperandTypeChecker.Consistency.LEAST_RESTRICTIVE);
            final List<RexNode> list = new ArrayList<>();
            final List<RexNode> orList = new ArrayList<>();
            for (RexNode expr : exprs) {
                orList.add(
                        rexBuilder.makeCall(call.getParserPosition(), SqlStdOperatorTable.IS_NULL, expr));
            }
            list.add(RexUtil.composeDisjunction(rexBuilder, orList));
            list.add(rexBuilder.makeNullLiteral(type));
            for (int i = 0; i < exprs.size() - 1; i++) {
                RexNode expr = exprs.get(i);
                final List<RexNode> andList = new ArrayList<>();
                for (int j = i + 1; j < exprs.size(); j++) {
                    final RexNode expr2 = exprs.get(j);
                    andList.add(rexBuilder.makeCall(call.getParserPosition(), op, expr, expr2));
                }
                list.add(RexUtil.composeConjunction(rexBuilder, andList));
                list.add(expr);
            }
            list.add(exprs.get(exprs.size() - 1));
            return rexBuilder.makeCall(call.getParserPosition(), type, SqlStdOperatorTable.CASE, list);
        }
    }

    /** Convertlet that converts {@code GREATEST} and {@code LEAST}. */
    private static class GreatestPgConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            // Translate
            //   GREATEST(a, b, c, d)
            // to
            //   CASE
            //   WHEN a IS NOT NULL AND (b IS NULL OR a > b) AND (c IS NULL OR a > c) AND
            //        (d IS NULL OR a > d)
            //   THEN a
            //   WHEN b IS NOT NULL AND (c IS NULL OR b > c) AND (d IS NULL OR b > d)
            //   THEN b
            //   WHEN C IS NOT NULL AND (d IS NULL OR c > d)
            //   THEN c
            //   WHEN d IS NOT NULL
            //   THEN d
            //   ELSE NULL
            //   END
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final RelDataType type =
                    cx.getValidator().getValidatedNodeType(call);
            final SqlBinaryOperator op;
            switch (call.getKind()) {
                case GREATEST_PG:
                    op = SqlStdOperatorTable.GREATER_THAN;
                    break;
                case LEAST_PG:
                    op = SqlStdOperatorTable.LESS_THAN;
                    break;
                default:
                    throw new AssertionError();
            }
            final List<RexNode> exprs =
                    convertOperands(cx, call, SqlOperandTypeChecker.Consistency.LEAST_RESTRICTIVE);
            final List<RexNode> list = new ArrayList<>();
            for (int i = 0; i < exprs.size(); i++) {
                RexNode expr = exprs.get(i);
                final List<RexNode> andList = new ArrayList<>();
                andList.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, expr));
                for (int j = i + 1; j < exprs.size(); j++) {
                    final RexNode expr2 = exprs.get(j);
                    final List<RexNode> orList = new ArrayList<>();
                    orList.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, expr2));
                    orList.add(rexBuilder.makeCall(op, expr, expr2));
                    andList.add(RexUtil.composeDisjunction(rexBuilder, orList));
                }
                list.add(RexUtil.composeConjunction(rexBuilder, andList));
                list.add(expr);
            }
            list.add(rexBuilder.makeNullLiteral(type));
            return rexBuilder.makeCall(type, SqlStdOperatorTable.CASE, list);
        }
    }

    /** Convertlet that handles {@code FLOOR} and {@code CEIL} functions. */
    private class FloorCeilConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            return convertFloorCeil(cx, call);
        }
    }

    /** Convertlet that handles the {@code SUBSTR} function; various dialects
     * have slightly different specifications. PostgreSQL seems to comply with
     * the ISO standard for the {@code SUBSTRING} function, and therefore
     * Calcite's default behavior matches PostgreSQL. */
    private static class SubstrConvertlet implements SqlRexConvertlet {
        private final SqlLibrary library;

        SubstrConvertlet(SqlLibrary library) {
            this.library = library;
            checkArgument(library == SqlLibrary.ORACLE
                    || library == SqlLibrary.MYSQL
                    || library == SqlLibrary.BIG_QUERY
                    || library == SqlLibrary.POSTGRESQL);
        }

        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            // Translate
            //   SUBSTR(value, start, length)
            //
            // to the following if we want PostgreSQL semantics:
            //   SUBSTRING(value, start, length)
            //
            // to the following if we want Oracle semantics:
            //   SUBSTRING(
            //     value
            //     FROM CASE
            //          WHEN start = 0
            //          THEN 1
            //          WHEN start + (length(value) + 1) < 1
            //          THEN length(value) + 1
            //          WHEN start < 0
            //          THEN start + (length(value) + 1)
            //          ELSE start)
            //     FOR CASE WHEN length < 0 THEN 0 ELSE length END)
            //
            // to the following in MySQL:
            //   SUBSTRING(
            //     value
            //     FROM CASE
            //          WHEN start = 0
            //          THEN length(value) + 1    -- different from Oracle
            //          WHEN start + (length(value) + 1) < 1
            //          THEN length(value) + 1
            //          WHEN start < 0
            //          THEN start + length(value) + 1
            //          ELSE start)
            //     FOR CASE WHEN length < 0 THEN 0 ELSE length END)
            //
            // to the following if we want BigQuery semantics:
            //   CASE
            //   WHEN start + (length(value) + 1) < 1
            //   THEN value
            //   ELSE SUBSTRING(
            //       value
            //       FROM CASE
            //            WHEN start = 0
            //            THEN 1
            //            WHEN start < 0
            //            THEN start + length(value) + 1
            //            ELSE start)
            //       FOR CASE WHEN length < 0 THEN 0 ELSE length END)

            final RexBuilder rexBuilder = cx.getRexBuilder();
            final SqlParserPos pos = call.getParserPosition();
            final List<RexNode> exprs =
                    convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
            final RexNode value = exprs.get(0);
            final RexNode start = exprs.get(1);
            final RelDataType startType = start.getType();
            final RexLiteral zeroLiteral = rexBuilder.makeLiteral(0, startType);
            final RexLiteral oneLiteral = rexBuilder.makeLiteral(1, startType);
            final RexNode valueLength =
                    SqlTypeUtil.isBinary(value.getType())
                            ? rexBuilder.makeCall(pos, SqlStdOperatorTable.OCTET_LENGTH, value)
                            : rexBuilder.makeCall(pos, SqlStdOperatorTable.CHAR_LENGTH, value);
            final RexNode valueLengthPlusOne =
                    rexBuilder.makeCall(pos, SqlStdOperatorTable.PLUS, valueLength,
                            oneLiteral);

            final RexNode newStart;
            switch (library) {
                case POSTGRESQL:
                    if (call.operandCount() == 2) {
                        newStart =
                                rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE,
                                        rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN, start,
                                                oneLiteral),
                                        oneLiteral, start);
                    } else {
                        newStart = start;
                    }
                    break;
                case BIG_QUERY:
                    newStart =
                            rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.EQUALS, start,
                                            zeroLiteral),
                                    oneLiteral,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN, start,
                                            zeroLiteral),
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.PLUS, start,
                                            valueLengthPlusOne),
                                    start);
                    break;
                default:
                    newStart =
                            rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.EQUALS, start,
                                            zeroLiteral),
                                    library == SqlLibrary.MYSQL ? valueLengthPlusOne : oneLiteral,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN,
                                            rexBuilder.makeCall(pos, SqlStdOperatorTable.PLUS, start,
                                                    valueLengthPlusOne),
                                            oneLiteral),
                                    valueLengthPlusOne,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN, start,
                                            zeroLiteral),
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.PLUS, start,
                                            valueLengthPlusOne),
                                    start);
                    break;
            }

            if (call.operandCount() == 2) {
                return rexBuilder.makeCall(pos, SqlStdOperatorTable.SUBSTRING, value,
                        newStart);
            }

            assert call.operandCount() == 3;
            final RexNode length = exprs.get(2);
            final RexNode newLength;
            switch (library) {
                case POSTGRESQL:
                    newLength = length;
                    break;
                default:
                    newLength =
                            rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN, length,
                                            zeroLiteral),
                                    zeroLiteral, length);
            }
            final RexNode substringCall =
                    rexBuilder.makeCall(pos, SqlStdOperatorTable.SUBSTRING, value, newStart,
                            newLength);
            switch (library) {
                case BIG_QUERY:
                    return rexBuilder.makeCall(pos, SqlStdOperatorTable.CASE,
                            rexBuilder.makeCall(pos, SqlStdOperatorTable.LESS_THAN,
                                    rexBuilder.makeCall(pos, SqlStdOperatorTable.PLUS, start,
                                            valueLengthPlusOne), oneLiteral),
                            value, substringCall);
                default:
                    return substringCall;
            }
        }
    }

    /**
     * Handles the first parameter of the SQL call.
     * Converts the first operand to a RexNode and casts it to DATE type if it is a TIMESTAMP.
     *
     * @param cx The SQL to Rex conversion context.
     * @param rexBuilder The RexBuilder to create RexNode instances.
     * @param call The SQL call containing the operands.
     * @return The converted RexNode for the first parameter.
     */
    private static RexNode handleFirstParameter(SqlRexContext cx, RexBuilder rexBuilder,
                                                SqlCall call) {
        RexNode opfirstparameter = cx.convertExpression(call.operand(0));
        if (opfirstparameter.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP) {
            RelDataType timestampType = cx.getTypeFactory().createSqlType(SqlTypeName.DATE);
            return rexBuilder.makeCast(timestampType, opfirstparameter);
        } else {
            return cx.convertExpression(call.operand(0));
        }
    }


    /**
     * Handles the second parameter of the SQL call.
     * Converts the second operand to a RexNode and casts it to INTEGER type if it is a CHAR.
     *
     * @param cx The SQL to Rex conversion context.
     * @param rexBuilder The RexBuilder to create RexNode instances.
     * @param call The SQL call containing the operands.
     * @return The converted RexNode for the second parameter.
     */
    private static RexNode handleSecondParameter(SqlRexContext cx, RexBuilder rexBuilder,
                                                 SqlCall call) {
        RexNode opsecondparameter = cx.convertExpression(call.operand(1));
        RelDataTypeFactory typeFactory = cx.getTypeFactory();

        // In Calcite, cast('1.2' as integer) is invalid.
        // For details, see https://issues.apache.org/jira/browse/CALCITE-1439
        // When trying to cast a string value to an integer type, Calcite may
        // encounter errors if the string value cannot be successfully converted.
        // To handle this, the string needs to be first converted to a double type,
        // and then the double value can be converted to an integer type.
        // Since the final target type is integer, converting the string to double first
        // will not lose precision for the add_months operation.
        if (opsecondparameter.getType().getSqlTypeName() == SqlTypeName.CHAR) {
            RelDataType doubleType = typeFactory.createSqlType(SqlTypeName.DOUBLE);
            opsecondparameter = rexBuilder.makeCast(doubleType, opsecondparameter);
        }
        RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
        return rexBuilder.makeCast(intType, opsecondparameter);
    }

    /** Convertlet that handles the 3-argument {@code TIMESTAMPADD} function
     * and the 2-argument BigQuery-style {@code TIMESTAMP_ADD} function. */
    private static class TimestampAddConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            // TIMESTAMPADD(unit, count, timestamp)
            //  => timestamp + count * INTERVAL '1' UNIT
            // TIMESTAMP_ADD(timestamp, interval)
            //  => timestamp + interval
            // "timestamp" may be of type TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE.
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final SqlParserPos pos = call.getParserPosition();
            SqlIntervalQualifier qualifier;
            final RexNode op1;
            final RexNode op2;
            switch (call.operandCount()) {
                case 2:
                    if (call.getOperator() == SqlLibraryOperators.ADD_MONTHS) {
                        // Oracle-style 'ADD_MONTHS(date, integer months)'
                        qualifier = new SqlIntervalQualifier(TimeUnit.MONTH, null, call.getParserPosition());
                        op2 = handleFirstParameter(cx, rexBuilder, call);
                        op1 = handleSecondParameter(cx, rexBuilder, call);
                    } else if (call.getOperator() == SqlLibraryOperators.DATE_ADD_SPARK) {
                        // Spark-style 'DATE_ADD(date, integer days)'
                        qualifier = new SqlIntervalQualifier(TimeUnit.DAY, null, call.getParserPosition());
                        op2 = handleFirstParameter(cx, rexBuilder, call);
                        op1 = handleSecondParameter(cx, rexBuilder, call);
                    } else {
                        // BigQuery-style 'TIMESTAMP_ADD(timestamp, interval)'
                        final SqlBasicCall operandCall = call.operand(1);
                        qualifier = operandCall.operand(1);
                        op1 = cx.convertExpression(operandCall.operand(0));
                        op2 = cx.convertExpression(call.operand(0));
                    }
                    break;
                default:
                    // JDBC-style 'TIMESTAMPADD(unit, count, timestamp)'
                    qualifier = call.operand(0);
                    op1 = cx.convertExpression(call.operand(1));
                    op2 = cx.convertExpression(call.operand(2));
            }

            final TimeFrame timeFrame = cx.getValidator().validateTimeFrame(qualifier);
            final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
            final RelDataType type = cx.getValidator().getValidatedNodeType(call);
            if (unit == TimeUnit.EPOCH && qualifier.timeFrameName != null) {
                // Custom time frames have a different path. They are kept as names,
                // and then handled by Java functions such as
                // SqlFunctions.customTimestampAdd.
                final RexLiteral timeFrameName =
                        rexBuilder.makeLiteral(qualifier.timeFrameName);
                // If the TIMESTAMPADD call has type TIMESTAMP and op2 has type DATE
                // (which can happen for sub-day time frames such as HOUR), cast op2 to
                // TIMESTAMP.
                final RexNode op2b = rexBuilder.makeCast(pos, type, op2);
                return rexBuilder.makeCall(pos, type, SqlStdOperatorTable.TIMESTAMP_ADD,
                        ImmutableList.of(timeFrameName, op1, op2b));
            }

            if (qualifier.getUnit() != unit) {
                qualifier =
                        new SqlIntervalQualifier(unit, null,
                                qualifier.getParserPosition());
            }

            RexNode interval2Add;
            BigDecimal multiplier = unit.multiplier;
            if (multiplier == null) {
                throw new IllegalArgumentException("Impossible conversion to " + unit);
            }
            switch (unit) {
                case MICROSECOND:
                case NANOSECOND:
                    interval2Add =
                            divide(pos, rexBuilder,
                                    multiply(pos, rexBuilder,
                                            rexBuilder.makeIntervalLiteral(BigDecimal.ONE, qualifier), op1),
                                    BigDecimal.ONE.divide(multiplier, RoundingMode.UNNECESSARY));
                    break;
                default:
                    interval2Add =
                            multiply(pos, rexBuilder,
                                    rexBuilder.makeIntervalLiteral(multiplier, qualifier), op1);
            }

            return rexBuilder.makeCall(pos, SqlStdOperatorTable.DATETIME_PLUS,
                    op2, interval2Add);
        }
    }

    /** Convertlet that handles the BigQuery {@code DATETIME_TRUNC} and
     * {@code TIMESTAMP_TRUNC} functions. Ensures that DATE operands are
     * cast to TIMESTAMPs to match the expected return type for BigQuery. */
    private static class TruncConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final SqlParserPos pos = call.getParserPosition();
            RexNode op1 = cx.convertExpression(call.operand(0));
            RexNode op2 = cx.convertExpression(call.operand(1));
            if (op1.getType().getSqlTypeName() == SqlTypeName.DATE) {
                RelDataType type = cx.getValidator().getValidatedNodeType(call);
                op1 = cx.getRexBuilder().makeCast(pos, type, op1);
            }
            return rexBuilder.makeCall(pos, call.getOperator(), op1, op2);
        }
    }

    /** Convertlet that handles the BigQuery {@code TIMESTAMP_SUB} function. */
    private static class TimestampSubConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            // TIMESTAMP_SUB(timestamp, interval)
            //  => timestamp - count * INTERVAL '1' UNIT
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final SqlParserPos pos = call.getParserPosition();
            SqlIntervalQualifier qualifier;
            final RexNode op1;
            final RexNode op2;
            if (call.getOperator() == SqlLibraryOperators.DATE_SUB_SPARK) {
                // Spark-style 'DATE_SUB(date, integer days)'
                qualifier = new SqlIntervalQualifier(TimeUnit.DAY, null, call.getParserPosition());
                op2 = handleFirstParameter(cx, rexBuilder, call);
                op1 = handleSecondParameter(cx, rexBuilder, call);
            } else {
                final SqlBasicCall operandCall = call.operand(1);
                qualifier = operandCall.operand(1);
                op1 = cx.convertExpression(operandCall.operand(0));
                op2 = cx.convertExpression(call.operand(0));
            }
            final TimeFrame timeFrame = cx.getValidator().validateTimeFrame(qualifier);
            final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
            final RexNode interval2Sub;
            switch (unit) {
                // Fractional second units are converted to seconds using their
                // associated multiplier.
                case MICROSECOND:
                case NANOSECOND:
                    interval2Sub =
                            divide(pos, rexBuilder,
                                    multiply(pos, rexBuilder,
                                            rexBuilder.makeIntervalLiteral(BigDecimal.ONE, qualifier), op1),
                                    BigDecimal.ONE.divide(unit.multiplier,
                                            RoundingMode.UNNECESSARY));
                    break;
                default:
                    interval2Sub =
                            multiply(pos, rexBuilder,
                                    rexBuilder.makeIntervalLiteral(unit.multiplier, qualifier), op1);
            }

            return rexBuilder.makeCall(pos, SqlInternalOperators.MINUS_DATE2,
                    op2, interval2Sub);
        }
    }

    /** Convertlet that handles the {@code TIMESTAMPDIFF} function. */
    private static class TimestampDiffConvertlet implements SqlRexConvertlet {
        @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
            // The standard TIMESTAMPDIFF and BigQuery's TIMESTAMP_DIFF have two key
            // differences. The first being the order of the subtraction, outlined
            // below. The second is that BigQuery truncates each timestamp to the
            // specified time unit before the difference is computed.
            //
            // In fact, all BigQuery functions (TIMESTAMP_DIFF, DATETIME_DIFF,
            // DATE_DIFF) truncate before subtracting when applied to date intervals
            // (DAY, WEEK, ISOWEEK, MONTH, YEAR, etc.)
            //
            // For example, if computing the number of weeks between two timestamps,
            // one occurring on a Saturday and the other occurring the next day on
            // Sunday, their week difference is 1. This is because the first timestamp
            // is truncated to the previous Sunday. This is done by making calls to
            // TIMESTAMP_TRUNC and the difference is then computed using their
            // results.
            //
            // TIMESTAMPDIFF(unit, t1, t2)
            //    => (t2 - t1) UNIT
            // TIMESTAMP_DIFF(t1, t2, unit)
            //    => (t1 - t2) UNIT
            SqlIntervalQualifier qualifier;
            final boolean preTruncate;
            final RexNode op1;
            final RexNode op2;
            final SqlParserPos pos = call.getParserPosition();
            if (call.operand(0).getKind() == SqlKind.INTERVAL_QUALIFIER) {
                qualifier = call.operand(0);
                preTruncate = false;
                op1 = cx.convertExpression(call.operand(1));
                op2 = cx.convertExpression(call.operand(2));
            } else {
                qualifier = call.operand(2);
                preTruncate = qualifier.isDate();
                op1 = cx.convertExpression(call.operand(1));
                op2 = cx.convertExpression(call.operand(0));
            }
            final RexBuilder rexBuilder = cx.getRexBuilder();
            final RelDataTypeFactory typeFactory = cx.getTypeFactory();
            final TimeFrame timeFrame = cx.getValidator().validateTimeFrame(qualifier);
            final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
            UnaryOperator<RexNode> truncateFn = UnaryOperator.identity();

            if (unit == TimeUnit.EPOCH && qualifier.timeFrameName != null) {
                // Custom time frames have a different path. They are kept as names, and
                // then handled by Java functions.
                final RexLiteral timeFrameName =
                        rexBuilder.makeLiteral(qualifier.timeFrameName);
                // This additional logic accounts for BigQuery truncating prior to
                // computing the difference.
                if (preTruncate) {
                    truncateFn = e ->
                            rexBuilder.makeCall(pos, e.getType(),
                                    SqlLibraryOperators.TIMESTAMP_TRUNC,
                                    ImmutableList.of(e, timeFrameName));
                }
                return rexBuilder.makeCall(pos, cx.getValidator().getValidatedNodeType(call),
                        SqlStdOperatorTable.TIMESTAMP_DIFF,
                        ImmutableList.of(timeFrameName, truncateFn.apply(op1),
                                truncateFn.apply(op2)));
            }

            if (preTruncate) {
                // The timestamps should be truncated unless the time unit is HOUR, in
                // which case only the whole number of hours between the timestamps
                // should be returned.
                final RexNode timeUnit = cx.convertExpression(qualifier);
                truncateFn = e ->
                        rexBuilder.makeCall(call.getParserPosition(), e.getType(),
                                SqlLibraryOperators.TIMESTAMP_TRUNC,
                                ImmutableList.of(e, timeUnit));
            }

            BigDecimal multiplier = BigDecimal.ONE;
            BigDecimal divider = BigDecimal.ONE;
            SqlTypeName sqlTypeName = unit == TimeUnit.NANOSECOND
                    ? SqlTypeName.BIGINT
                    : SqlTypeName.INTEGER;
            switch (unit) {
                case MICROSECOND:
                case MILLISECOND:
                case NANOSECOND:
                case WEEK:
                    multiplier = BigDecimal.valueOf(DateTimeUtils.MILLIS_PER_SECOND);
                    divider = unit.multiplier;
                    qualifier =
                            new SqlIntervalQualifier(TimeUnit.SECOND, null,
                                    qualifier.getParserPosition());
                    break;
                case QUARTER:
                case CENTURY:
                case MILLENNIUM:
                    divider = unit.multiplier;
                    qualifier =
                            new SqlIntervalQualifier(TimeUnit.MONTH, null,
                                    qualifier.getParserPosition());
                    break;
                default:
                    qualifier =
                            new SqlIntervalQualifier(unit, null,
                                    qualifier.getParserPosition());
                    break;
            }

            final RelDataType intervalType =
                    typeFactory.createTypeWithNullability(
                            typeFactory.createSqlIntervalType(qualifier),
                            op1.getType().isNullable() || op2.getType().isNullable());
            final RexNode call2 =
                    rexBuilder.makeCall(pos, intervalType,
                            SqlStdOperatorTable.MINUS_DATE,
                            ImmutableList.of(truncateFn.apply(op2), truncateFn.apply(op1)));
            final RelDataType intType =
                    typeFactory.createTypeWithNullability(
                            typeFactory.createSqlType(sqlTypeName),
                            SqlTypeUtil.containsNullable(call2.getType()));
            RexNode e = rexBuilder.makeCast(pos, intType, call2);
            return rexBuilder.multiplyDivide(pos, e, multiplier, divider);
        }
    }
}
