Class SqlOperatorBaseTest

  • Direct Known Subclasses:
    CalciteSqlOperatorTest, SqlOperatorTest

    public abstract class SqlOperatorBaseTest
    extends java.lang.Object
    Contains unit tests for all operators. Each of the methods is named after an operator.

    The class is abstract. It contains a test for every operator, but does not provide a mechanism to execute the tests: parse, validate, and execute expressions on the operators. This is left to a SqlTester object which the derived class must provide.

    Different implementations of SqlTester are possible, such as:

    • Execute against a real farrago database
    • Execute in pure java (parsing and validation can be done, but expression evaluation is not possible)
    • Generate a SQL script.
    • Analyze which operators are adequately tested.

    A typical method will be named after the operator it is testing (say testSubstringFunc). It first calls SqlTester.setFor(org.apache.calcite.sql.SqlOperator, org.apache.calcite.sql.test.SqlTester.VmName...) to declare which operator it is testing.

     public void testSubstringFunc() {
         tester.checkScalar("sin(0)", "0");
         tester.checkScalar("sin(1.5707)", "1");

    The rest of the method contains calls to the various checkXxx methods in the SqlTester interface. For an operator to be adequately tested, there need to be tests for:

    • Parsing all of its the syntactic variants.
    • Deriving the type of in all combinations of arguments.
      • Pay particular attention to nullability. For example, the result of the "+" operator is NOT NULL if and only if both of its arguments are NOT NULL.
      • Also pay attention to precision/scale/length. For example, the maximum length of the "||" operator is the sum of the maximum lengths of its arguments.
    • Executing the function. Pay particular attention to corner cases such as null arguments or null results.
    • Field Detail


        public static final java.lang.String INVALID_CHAR_MESSAGE

        public static final java.lang.String OUT_OF_RANGE_MESSAGE

        public static final java.lang.String DIVISION_BY_ZERO_MESSAGE

        public static final java.lang.String STRING_TRUNC_MESSAGE

        public static final java.lang.String BAD_DATETIME_MESSAGE

        public static final java.lang.String INVALID_EXTRACT_UNIT_CONVERTLET_ERROR
        See Also:
        Constant Field Values

        public static final java.lang.String INVALID_EXTRACT_UNIT_VALIDATION_ERROR
        See Also:
        Constant Field Values

        public static final java.lang.String LITERAL_OUT_OF_RANGE_MESSAGE
        See Also:
        Constant Field Values

        public static final java.util.regex.Pattern TIME_PATTERN
        Regular expression for a SQL TIME(0) value.

        public static final java.util.regex.Pattern TIMESTAMP_PATTERN
        Regular expression for a SQL TIMESTAMP(0) value.

        public static final java.util.regex.Pattern DATE_PATTERN
        Regular expression for a SQL DATE value.

        public static final java.lang.String[] NUMERIC_TYPE_NAMES

        public static final java.lang.String[] MIN_NUMERIC_STRINGS

        public static final java.lang.String[] MIN_OVERFLOW_NUMERIC_STRINGS

        public static final java.lang.String[] MAX_NUMERIC_STRINGS

        public static final java.lang.String[] MAX_OVERFLOW_NUMERIC_STRINGS
      • FALSE_TRUE

        private static final boolean[] FALSE_TRUE
      • UTC_TZ

        protected static final java.util.TimeZone UTC_TZ
      • LOCAL_TZ

        protected static final java.util.TimeZone LOCAL_TZ
      • CURRENT_TZ

        protected static final java.util.TimeZone CURRENT_TZ

        private static final java.util.regex.Pattern INVALID_ARG_FOR_POWER
      • CODE_2201F

        private static final java.util.regex.Pattern CODE_2201F
      • DECIMAL

        public static final boolean DECIMAL
        Whether DECIMAL type is implemented.
        See Also:
        Constant Field Values
      • enable

        private final boolean enable
      • strictTester

        protected final SqlTester strictTester
    • Constructor Detail

      • SqlOperatorBaseTest

        protected SqlOperatorBaseTest​(boolean enable,
                                      SqlTester tester)
        Creates a SqlOperatorBaseTest.
        enable - Whether to run "failing" tests.
        tester - Means to validate, execute various statements.
    • Method Detail

      • setUp

        public void setUp()
                   throws java.lang.Exception
      • oracleTester

        protected SqlTester oracleTester()
      • oracleTester

        protected SqlTester oracleTester​(org.apache.calcite.sql.validate.SqlConformance conformance)
      • tester

        protected SqlTester tester​( library)
        Creates a tester with special sql library.
      • testDummy

        public void testDummy()
        For development. Put any old code in here.
      • testSqlOperatorOverloading

        public void testSqlOperatorOverloading()
      • testBetween

        public void testBetween()
      • testNotBetween

        public void testNotBetween()
      • getCastString

        private java.lang.String getCastString​(java.lang.String value,
                                               java.lang.String targetType,
                                               boolean errorLoc)
      • checkCastToApproxOkay

        private void checkCastToApproxOkay​(java.lang.String value,
                                           java.lang.String targetType,
                                           double expected,
                                           double delta)
      • checkCastToStringOkay

        private void checkCastToStringOkay​(java.lang.String value,
                                           java.lang.String targetType,
                                           java.lang.String expected)
      • checkCastToScalarOkay

        private void checkCastToScalarOkay​(java.lang.String value,
                                           java.lang.String targetType,
                                           java.lang.String expected)
      • checkCastToScalarOkay

        private void checkCastToScalarOkay​(java.lang.String value,
                                           java.lang.String targetType)
      • checkCastFails

        private void checkCastFails​(java.lang.String value,
                                    java.lang.String targetType,
                                    java.lang.String expectedError,
                                    boolean runtime)
      • checkCastToString

        private void checkCastToString​(java.lang.String value,
                                       java.lang.String type,
                                       java.lang.String expected)
      • testCastToString

        public void testCastToString()
      • testCastExactNumericLimits

        public void testCastExactNumericLimits()
      • testCastToExactNumeric

        public void testCastToExactNumeric()
      • testCastStringToDecimal

        public void testCastStringToDecimal()
      • testCastIntervalToNumeric

        public void testCastIntervalToNumeric()
      • testCastToInterval

        public void testCastToInterval()
      • testCastIntervalToInterval

        public void testCastIntervalToInterval()
      • testCastWithRoundingToScalar

        public void testCastWithRoundingToScalar()
      • testCastDecimalToDoubleToInteger

        public void testCastDecimalToDoubleToInteger()
      • testCastApproxNumericLimits

        public void testCastApproxNumericLimits()
      • testCastToApproxNumeric

        public void testCastToApproxNumeric()
      • testCastNull

        public void testCastNull()
      • testCastDateTime

        public void testCastDateTime()
      • testCastStringToDateTime

        public void testCastStringToDateTime()
      • getCalendarNotTooNear

        protected static java.util.Calendar getCalendarNotTooNear​(int timeUnit)
        Returns a Calendar that is the current time, pausing if we are within 2 minutes of midnight or the top of the hour.
        timeUnit - Time unit
      • testCastToBoolean

        public void testCastToBoolean()
      • testCase

        public void testCase()
      • testCaseNull

        public void testCaseNull()
      • testCaseType

        public void testCaseType()
      • testJdbcFn

        public void testJdbcFn()
        Tests support for JDBC functions.

        See FRG-97 "Support for JDBC escape syntax is incomplete".

      • testChr

        public void testChr()
      • testSelect

        public void testSelect()
      • testLiteralChain

        public void testLiteralChain()
      • testComplexLiteral

        public void testComplexLiteral()
      • testRow

        public void testRow()
      • testAndOperator

        public void testAndOperator()
      • testAndOperator2

        public void testAndOperator2()
      • testAndOperatorLazy

        public void testAndOperatorLazy()
      • testConcatOperator

        public void testConcatOperator()
      • testModOperator

        public void testModOperator()
      • testModPrecedence

        public void testModPrecedence()
      • testModOperatorNull

        public void testModOperatorNull()
      • testModOperatorDivByZero

        public void testModOperatorDivByZero()
      • testDivideOperator

        public void testDivideOperator()
      • testDivideOperatorIntervals

        public void testDivideOperatorIntervals()
      • testEqualsOperator

        public void testEqualsOperator()
      • testEqualsOperatorInterval

        public void testEqualsOperatorInterval()
      • testGreaterThanOperator

        public void testGreaterThanOperator()
      • testGreaterThanOperatorIntervals

        public void testGreaterThanOperatorIntervals()
      • testIsDistinctFromOperator

        public void testIsDistinctFromOperator()
      • testIsNotDistinctFromOperator

        public void testIsNotDistinctFromOperator()
      • testGreaterThanOrEqualOperator

        public void testGreaterThanOrEqualOperator()
      • testGreaterThanOrEqualOperatorIntervals

        public void testGreaterThanOrEqualOperatorIntervals()
      • testInOperator

        public void testInOperator()
      • testNotInOperator

        public void testNotInOperator()
      • testOverlapsOperator

        public void testOverlapsOperator()
      • testLessThanOperator

        public void testLessThanOperator()
      • testLessThanOperatorInterval

        public void testLessThanOperatorInterval()
      • testLessThanOrEqualOperator

        public void testLessThanOrEqualOperator()
      • testLessThanOrEqualOperatorInterval

        public void testLessThanOrEqualOperatorInterval()
      • testMinusOperator

        public void testMinusOperator()
      • testMinusIntervalOperator

        public void testMinusIntervalOperator()
      • testMinusDateOperator

        public void testMinusDateOperator()
      • testMultiplyOperator

        public void testMultiplyOperator()
      • testMultiplyIntervals

        public void testMultiplyIntervals()
      • testDatePlusInterval

        public void testDatePlusInterval()
      • checkNullOperand

        private void checkNullOperand​(SqlTester tester,
                                      java.lang.String op)
      • testNotEqualsOperator

        public void testNotEqualsOperator()
      • testNotEqualsOperatorIntervals

        public void testNotEqualsOperatorIntervals()
      • testOrOperator

        public void testOrOperator()
      • testOrOperatorLazy

        public void testOrOperatorLazy()
      • testPlusOperator

        public void testPlusOperator()
      • testPlusOperatorAny

        public void testPlusOperatorAny()
      • testPlusIntervalOperator

        public void testPlusIntervalOperator()
      • testDescendingOperator

        public void testDescendingOperator()
      • testIsNotNullOperator

        public void testIsNotNullOperator()
      • testIsNullOperator

        public void testIsNullOperator()
      • testIsNotTrueOperator

        public void testIsNotTrueOperator()
      • testIsTrueOperator

        public void testIsTrueOperator()
      • testIsNotFalseOperator

        public void testIsNotFalseOperator()
      • testIsFalseOperator

        public void testIsFalseOperator()
      • testIsNotUnknownOperator

        public void testIsNotUnknownOperator()
      • testIsUnknownOperator

        public void testIsUnknownOperator()
      • testIsASetOperator

        public void testIsASetOperator()
      • testIsNotASetOperator

        public void testIsNotASetOperator()
      • testIntersectOperator

        public void testIntersectOperator()
      • testExceptOperator

        public void testExceptOperator()
      • testIsEmptyOperator

        public void testIsEmptyOperator()
      • testIsNotEmptyOperator

        public void testIsNotEmptyOperator()
      • testExistsOperator

        public void testExistsOperator()
      • testNotOperator

        public void testNotOperator()
      • testPrefixMinusOperator

        public void testPrefixMinusOperator()
      • testPrefixMinusOperatorIntervals

        public void testPrefixMinusOperatorIntervals()
      • testPrefixPlusOperator

        public void testPrefixPlusOperator()
      • testPrefixPlusOperatorIntervals

        public void testPrefixPlusOperatorIntervals()
      • testExplicitTableOperator

        public void testExplicitTableOperator()
      • testValuesOperator

        public void testValuesOperator()
      • testNotLikeOperator

        public void testNotLikeOperator()
      • testLikeEscape

        public void testLikeEscape()
      • testLikeEscape2

        public void testLikeEscape2()
      • testLikeOperator

        public void testLikeOperator()
      • testNotSimilarToOperator

        public void testNotSimilarToOperator()
      • testSimilarToOperator

        public void testSimilarToOperator()
      • testEscapeOperator

        public void testEscapeOperator()
      • testConvertFunc

        public void testConvertFunc()
      • testTranslateFunc

        public void testTranslateFunc()
      • testTranslate3Func

        public void testTranslate3Func()
      • testOverlayFunc

        public void testOverlayFunc()
      • testPositionFunc

        public void testPositionFunc()
      • testReplaceFunc

        public void testReplaceFunc()
      • testCharLengthFunc

        public void testCharLengthFunc()
      • testCharacterLengthFunc

        public void testCharacterLengthFunc()
      • testAsciiFunc

        public void testAsciiFunc()
      • testToBase64

        public void testToBase64()
      • testFromBase64

        public void testFromBase64()
      • testMd5

        public void testMd5()
      • testSha1

        public void testSha1()
      • testRepeatFunc

        public void testRepeatFunc()
      • testSpaceFunc

        public void testSpaceFunc()
      • testSoundexFunc

        public void testSoundexFunc()
      • testDifferenceFunc

        public void testDifferenceFunc()
      • testReverseFunc

        public void testReverseFunc()
      • testUpperFunc

        public void testUpperFunc()
      • testLeftFunc

        public void testLeftFunc()
      • testRightFunc

        public void testRightFunc()
      • testRegexpReplaceFunc

        public void testRegexpReplaceFunc()
      • testJsonExists

        public void testJsonExists()
      • testJsonValue

        public void testJsonValue()
      • testJsonQuery

        public void testJsonQuery()
      • testJsonPretty

        public void testJsonPretty()
      • testJsonStorageSize

        public void testJsonStorageSize()
      • testJsonType

        public void testJsonType()
      • testJsonDepth

        public void testJsonDepth()
      • testJsonLength

        public void testJsonLength()
      • testJsonKeys

        public void testJsonKeys()
      • testJsonRemove

        public void testJsonRemove()
      • testJsonObject

        public void testJsonObject()
      • testJsonObjectAgg

        public void testJsonObjectAgg()
      • testJsonValueExpressionOperator

        public void testJsonValueExpressionOperator()
      • testJsonArray

        public void testJsonArray()
      • testJsonArrayAgg

        public void testJsonArrayAgg()
      • testJsonPredicate

        public void testJsonPredicate()
      • testLowerFunc

        public void testLowerFunc()
      • testInitcapFunc

        public void testInitcapFunc()
      • testPowerFunc

        public void testPowerFunc()
      • testSqrtFunc

        public void testSqrtFunc()
      • testExpFunc

        public void testExpFunc()
      • testModFunc

        public void testModFunc()
      • testModFuncNull

        public void testModFuncNull()
      • testModFuncDivByZero

        public void testModFuncDivByZero()
      • testLnFunc

        public void testLnFunc()
      • testLogFunc

        public void testLogFunc()
      • testRandFunc

        public void testRandFunc()
      • testRandSeedFunc

        public void testRandSeedFunc()
      • testRandIntegerFunc

        public void testRandIntegerFunc()
      • testRandIntegerSeedFunc

        public void testRandIntegerSeedFunc()
      • testAbsFunc

        public void testAbsFunc()
      • testAbsFuncIntervals

        public void testAbsFuncIntervals()
      • testAcosFunc

        public void testAcosFunc()
      • testAsinFunc

        public void testAsinFunc()
      • testAtanFunc

        public void testAtanFunc()
      • testAtan2Func

        public void testAtan2Func()
      • testCosFunc

        public void testCosFunc()
      • testCotFunc

        public void testCotFunc()
      • testDegreesFunc

        public void testDegreesFunc()
      • testPiFunc

        public void testPiFunc()
      • testRadiansFunc

        public void testRadiansFunc()
      • testRoundFunc

        public void testRoundFunc()
      • testSignFunc

        public void testSignFunc()
      • testSinFunc

        public void testSinFunc()
      • testTanFunc

        public void testTanFunc()
      • testTruncateFunc

        public void testTruncateFunc()
      • testNullifFunc

        public void testNullifFunc()
      • testNullIfOperatorIntervals

        public void testNullIfOperatorIntervals()
      • testCoalesceFunc

        public void testCoalesceFunc()
      • testUserFunc

        public void testUserFunc()
      • testCurrentUserFunc

        public void testCurrentUserFunc()
      • testSessionUserFunc

        public void testSessionUserFunc()
      • testSystemUserFunc

        public void testSystemUserFunc()
      • testCurrentPathFunc

        public void testCurrentPathFunc()
      • testCurrentRoleFunc

        public void testCurrentRoleFunc()
      • testCurrentCatalogFunc

        public void testCurrentCatalogFunc()
      • testLocalTimeFunc

        public void testLocalTimeFunc()
      • testLocalTimestampFunc

        public void testLocalTimestampFunc()
      • testCurrentTimeFunc

        public void testCurrentTimeFunc()
      • testCurrentTimestampFunc

        public void testCurrentTimestampFunc()
      • currentTimeString

        protected static org.apache.calcite.util.Pair<java.lang.String,​org.apache.calcite.runtime.Hook.Closeable> currentTimeString​(java.util.TimeZone tz)
        Returns a time string, in GMT, that will be valid for at least 2 minutes.

        For example, at "2005-01-01 12:34:56 PST", returns "2005-01-01 20:". At "2005-01-01 12:34:59 PST", waits a minute, then returns "2005-01-01 21:".

        tz - Time zone
        Time string
      • testCurrentDateFunc

        public void testCurrentDateFunc()
      • testLastDayFunc

        public void testLastDayFunc()
      • testSubstringFunction

        public void testSubstringFunction()
      • testTrimFunc

        public void testTrimFunc()
      • testRtrimFunc

        public void testRtrimFunc()
      • testLtrimFunc

        public void testLtrimFunc()
      • testGreatestFunc

        public void testGreatestFunc()
      • testLeastFunc

        public void testLeastFunc()
      • testNvlFunc

        public void testNvlFunc()
      • testDecodeFunc

        public void testDecodeFunc()
      • testWindow

        public void testWindow()
      • testElementFunc

        public void testElementFunc()
      • testCardinalityFunc

        public void testCardinalityFunc()
      • testMemberOfOperator

        public void testMemberOfOperator()
      • testMultisetUnionOperator

        public void testMultisetUnionOperator()
      • testMultisetUnionAllOperator

        public void testMultisetUnionAllOperator()
      • testSubMultisetOfOperator

        public void testSubMultisetOfOperator()
      • testNotSubMultisetOfOperator

        public void testNotSubMultisetOfOperator()
      • testCollectFunc

        public void testCollectFunc()
      • testListAggFunc

        public void testListAggFunc()
      • testFusionFunc

        public void testFusionFunc()
      • testYear

        public void testYear()
      • testQuarter

        public void testQuarter()
      • testMonth

        public void testMonth()
      • testWeek

        public void testWeek()
      • testDayOfYear

        public void testDayOfYear()
      • testDayOfMonth

        public void testDayOfMonth()
      • testDayOfWeek

        public void testDayOfWeek()
      • testHour

        public void testHour()
      • testMinute

        public void testMinute()
      • testSecond

        public void testSecond()
      • testExtractIntervalYearMonth

        public void testExtractIntervalYearMonth()
      • testExtractIntervalDayTime

        public void testExtractIntervalDayTime()
      • testExtractDate

        public void testExtractDate()
      • testExtractTimestamp

        public void testExtractTimestamp()
      • testExtractFunc

        public void testExtractFunc()
      • testExtractFuncFromDateTime

        public void testExtractFuncFromDateTime()
      • testExtractWithDatesBeforeUnixEpoch

        public void testExtractWithDatesBeforeUnixEpoch()
      • testArrayValueConstructor

        public void testArrayValueConstructor()
      • testItemOp

        public void testItemOp()
      • testMapValueConstructor

        public void testMapValueConstructor()
      • testCeilFunc

        public void testCeilFunc()
      • testCeilFuncInterval

        public void testCeilFuncInterval()
      • testFloorFunc

        public void testFloorFunc()
      • testFloorFuncDateTime

        public void testFloorFuncDateTime()
      • testCeilFuncDateTime

        public void testCeilFuncDateTime()
      • testFloorFuncInterval

        public void testFloorFuncInterval()
      • testTimestampAdd

        public void testTimestampAdd()
      • testTimestampAddFractionalSeconds

        public void testTimestampAddFractionalSeconds()
      • testTimestampDiff

        public void testTimestampDiff()
      • testDenseRankFunc

        public void testDenseRankFunc()
      • testPercentRankFunc

        public void testPercentRankFunc()
      • testRankFunc

        public void testRankFunc()
      • testCumeDistFunc

        public void testCumeDistFunc()
      • testRowNumberFunc

        public void testRowNumberFunc()
      • testCountFunc

        public void testCountFunc()
      • testApproxCountDistinctFunc

        public void testApproxCountDistinctFunc()
      • testSumFunc

        public void testSumFunc()
      • checkAggType

        protected void checkAggType​(SqlTester tester,
                                    java.lang.String expr,
                                    java.lang.String type)
        Very similar to tester.checkType, but generates inside a SELECT with a non-empty GROUP BY. Aggregate functions may be nullable if executed in a SELECT with an empty GROUP BY.

        Viz: SELECT sum(1) FROM emp has type "INTEGER", SELECT sum(1) FROM emp GROUP BY deptno has type "INTEGER NOT NULL",

      • testAvgFunc

        public void testAvgFunc()
      • testCovarPopFunc

        public void testCovarPopFunc()
      • testCovarSampFunc

        public void testCovarSampFunc()
      • testRegrSxxFunc

        public void testRegrSxxFunc()
      • testRegrSyyFunc

        public void testRegrSyyFunc()
      • testStddevPopFunc

        public void testStddevPopFunc()
      • testStddevSampFunc

        public void testStddevSampFunc()
      • testStddevFunc

        public void testStddevFunc()
      • testVarPopFunc

        public void testVarPopFunc()
      • testVarSampFunc

        public void testVarSampFunc()
      • testVarFunc

        public void testVarFunc()
      • testMinFunc

        public void testMinFunc()
      • testMaxFunc

        public void testMaxFunc()
      • testLastValueFunc

        public void testLastValueFunc()
      • testFirstValueFunc

        public void testFirstValueFunc()
      • testAnyValueFunc

        public void testAnyValueFunc()
      • testBitAndFunc

        public void testBitAndFunc()
      • testBitOrFunc

        public void testBitOrFunc()
      • testLiteralAtLimit

        public void testLiteralAtLimit()
        Tests that CAST fails when given a value just outside the valid range for that type. For example,
        • CAST(-200 AS TINYINT) fails because the value is less than -128;
        • CAST(1E-999 AS FLOAT) fails because the value underflows;
        • CAST(123.4567891234567 AS FLOAT) fails because the value loses precision.
      • testLiteralBeyondLimit

        public void testLiteralBeyondLimit()
        Tests that CAST fails when given a value just outside the valid range for that type. For example,
        • CAST(-200 AS TINYINT) fails because the value is less than -128;
        • CAST(1E-999 AS FLOAT) fails because the value underflows;
        • CAST(123.4567891234567 AS FLOAT) fails because the value loses precision.
      • testCastTruncates

        public void testCastTruncates()
      • testArgumentBounds

        public void testArgumentBounds()
        Test that calls all operators with all possible argument types, and for each type, with a set of tricky values.
      • getValues

        private java.util.List<java.lang.Object> getValues​(org.apache.calcite.sql.type.BasicSqlType type,
                                                           boolean inBound)