org.eigenbase.sql.test
Class SqlOperatorTests

java.lang.Object
  junit.framework.Assert
      junit.framework.TestCase
          org.eigenbase.sql.test.SqlOperatorTests

All Implemented Interfaces:

Test

Direct Known Subclasses:

FarragoSqlOperatorsSuite.FarragoVmOperatorTestBase, SqlOperatorTest

public abstract class SqlOperatorTests
extends TestCase

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.eigenbase.sql.SqlOperator, org.eigenbase.sql.test.SqlTester.VmName...) to declare which operator it is testing.

 public void testSubstringFunc() {
     getTester().setFor(SqlStdOperatorTable.substringFunc);
     getTester().checkScalar("sin(0)", "0");
     getTester().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 precsion/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.

Since:

October 1, 2004

Version:

$Id: //open/dev/farrago/src/org/eigenbase/sql/test/SqlOperatorTests.java#87 $

Author:

Julian Hyde

Field Summary

static String	badDatetimeMessage
static Pattern	datePattern Regular expression for a SQL DATE value.
protected static TimeZone	defaultTimeZone
static String	divisionByZeroMessage
private static boolean[]	FalseTrue
protected static TimeZone	gmtTimeZone
static String	invalidCharMessage
static String	literalOutOfRangeMessage
static String[]	maxNumericStrings
static String[]	maxOverflowNumericStrings
static String[]	minNumericStrings
static String[]	minOverflowNumericStrings
static String	NL
static String[]	numericTypeNames
static String	outOfRangeMessage
static String	stringTruncMessage
static Pattern	timePattern Regular expression for a SQL TIME(0) value.
static Pattern	timestampPattern Regular expression for a SQL TIMESTAMP(0) value.
static boolean	todo
private static SqlTester.VmName	VM_EXPAND
private static SqlTester.VmName	VM_FENNEL
private static SqlTester.VmName	VM_JAVA

Constructor Summary

SqlOperatorTests(String testName)

Method Summary

private void	checkCastFails(String value, String targetType, String expectedError, boolean runtime)
private void	checkCastToApproxOkay(String value, String targetType, double expected, double delta)
private void	checkCastToScalarOkay(String value, String targetType)
private void	checkCastToScalarOkay(String value, String targetType, String expected)
private void	checkCastToString(String value, String type, String expected)
private void	checkCastToStringOkay(String value, String targetType, String expected)
protected static String	currentTimeString(TimeZone tz) Returns a time string, in GMT, that will be valid for at least 2 minutes.
protected static 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.
private String	getCastString(String value, String targetType, boolean errorLoc)
protected abstract SqlTester	getTester() Derived class must implement this method to provide a means to validate, execute various statements.
private List<Object>	getValues(BasicSqlType type, boolean inBound)
protected void	setUp()
void	testAbsFunc()
void	testAndOperator()
void	testAvgFunc()
void	testBetween()
void	testCardinalityFunc()
void	testCase()
void	testCaseType()
void	testCastApproxNumericLimits()
void	testCastDateTime()
void	testCastDecimalToDoubleToInteger()
void	testCastExactNumericLimits()
void	testCastNull()
void	testCastToApproxNumeric()
void	testCastToBoolean()
void	testCastToExactNumeric()
void	testCastToString()
void	testCastTruncates()
void	testCastWithRoundingToScalar()
void	testCeilFunc()
void	testCharacterLengthFunc()
void	testCharLengthFunc()
void	testCoalesceFunc()
void	testCollectFunc()
void	testConcatOperator()
void	testConvertFunc()
void	testCountFunc()
void	testCumeDistFunc()
void	testCurrentDateFunc()
void	testCurrentPathFunc()
void	testCurrentRoleFunc()
void	testCurrentTimeFunc()
void	testCurrentTimestampFunc()
void	testCurrentUserFunc()
void	testDenseRankFunc()
void	testDescendingOperator()
void	testDivideOperator()
void	testElementFunc()
void	testEqualsOperator()
void	testEscapeOperator()
void	testExistsOperator()
void	testExpFunc()
void	testExplicitTableOperator()
void	testExtractFunc()
void	testFirstValueFunc()
void	testFloorFunc()
void	testFusionFunc()
void	testGreaterThanOperator()
void	testGreaterThanOrEqualOperator()
void	testInitcapFunc()
void	testInOperator()
void	testIsASetOperator()
void	testIsDistinctFromOperator()
void	testIsFalseOperator()
void	testIsNotDistinctFromOperator()
void	testIsNotFalseOperator()
void	testIsNotNullOperator()
void	testIsNotTrueOperator()
void	testIsNotUnknownOperator()
void	testIsNullOperator()
void	testIsTrueOperator()
void	testIsUnknownOperator()
void	testJdbcFn() Tests support for JDBC functions.
void	testLastValueFunc()
void	testLessThanOperator()
void	testLessThanOrEqualOperator()
void	testLikeOperator()
void	testLiteralAtLimit() Tests that CAST fails when given a value just outside the valid range for that type.
void	testLiteralBeyondLimit() Tests that CAST fails when given a value just outside the valid range for that type.
void	testLiteralChain()
void	testLnFunc()
void	testLocalTimeFunc()
void	testLocalTimestampFunc()
void	testLogFunc()
void	testLowerFunc()
void	testMaxFunc()
void	testMemberOfOperator()
void	testMinFunc()
void	testMinusDateOperator()
void	testMinusIntervalOperator()
void	testMinusOperator()
void	testModFunc()
void	testModFuncDivByZero()
void	testMultiplyOperator()
void	testNotBetween()
void	testNotEqualsOperator()
void	testNotInOperator()
void	testNotLikeOperator()
void	testNotOperator()
void	testNotSimilarToOperator()
void	testNullifFunc()
void	testOrOperator()
void	testOverlapsOperator()
void	testOverlayFunc()
void	testPercentRankFunc()
void	testPlusIntervalOperator()
void	testPlusOperator()
void	testPositionFunc()
void	testPowerFunc()
void	testPrefixMinusOperator()
void	testPrefixPlusOperator()
void	testRankFunc()
void	testRow()
void	testRowNumberFunc()
void	testSelect()
void	testSessionUserFunc()
void	testSimilarToOperator()
void	testSubstringFunction()
void	testSumFunc()
void	testSystemUserFunc()
void	testTranslateFunc()
void	testTrimFunc()
void	testUpperFunc()
void	testUserFunc()
void	testValuesOperator()
void	testWindow()

Methods inherited from class junit.framework.TestCase

countTestCases, createResult, getName, run, run, runBare, runTest, setName, tearDown, toString

Methods inherited from class junit.framework.Assert

assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertFalse, assertFalse, assertNotNull, assertNotNull, assertNotSame, assertNotSame, assertNull, assertNull, assertSame, assertSame, assertTrue, assertTrue, fail, fail, failNotEquals, failNotSame, failSame, format

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

NL

public static final String NL

invalidCharMessage

public static final String invalidCharMessage

outOfRangeMessage

public static final String outOfRangeMessage

divisionByZeroMessage

public static final String divisionByZeroMessage

stringTruncMessage

public static final String stringTruncMessage

badDatetimeMessage

public static final String badDatetimeMessage

literalOutOfRangeMessage

public static final String literalOutOfRangeMessage

See Also:

Constant Field Values

todo

public static final boolean todo

See Also:

Constant Field Values

timePattern

public static final Pattern timePattern

Regular expression for a SQL TIME(0) value.

timestampPattern

public static final Pattern timestampPattern

Regular expression for a SQL TIMESTAMP(0) value.

datePattern

public static final Pattern datePattern

Regular expression for a SQL DATE value.

numericTypeNames

public static final String[] numericTypeNames

minNumericStrings

public static final String[] minNumericStrings

minOverflowNumericStrings

public static final String[] minOverflowNumericStrings

maxNumericStrings

public static final String[] maxNumericStrings

maxOverflowNumericStrings

public static final String[] maxOverflowNumericStrings

FalseTrue

private static final boolean[] FalseTrue

VM_FENNEL

private static final SqlTester.VmName VM_FENNEL

VM_JAVA

private static final SqlTester.VmName VM_JAVA

VM_EXPAND

private static final SqlTester.VmName VM_EXPAND

gmtTimeZone

protected static final TimeZone gmtTimeZone

defaultTimeZone

protected static final TimeZone defaultTimeZone

Constructor Detail

SqlOperatorTests

public SqlOperatorTests(String testName)

Method Detail

getTester

protected abstract SqlTester getTester()

Derived class must implement this method to provide a means to validate, execute various statements.

setUp

protected void setUp()
              throws Exception

Overrides:

setUp in class TestCase

Throws:

Exception

testBetween

public void testBetween()

testNotBetween

public void testNotBetween()

getCastString

private String getCastString(String value,
                             String targetType,
                             boolean errorLoc)

checkCastToApproxOkay

private void checkCastToApproxOkay(String value,
                                   String targetType,
                                   double expected,
                                   double delta)

checkCastToStringOkay

private void checkCastToStringOkay(String value,
                                   String targetType,
                                   String expected)

checkCastToScalarOkay

private void checkCastToScalarOkay(String value,
                                   String targetType,
                                   String expected)

checkCastToScalarOkay

private void checkCastToScalarOkay(String value,
                                   String targetType)

checkCastFails

private void checkCastFails(String value,
                            String targetType,
                            String expectedError,
                            boolean runtime)

checkCastToString

private void checkCastToString(String value,
                               String type,
                               String expected)

testCastToString

public void testCastToString()

testCastExactNumericLimits

public void testCastExactNumericLimits()

testCastToExactNumeric

public void testCastToExactNumeric()

testCastWithRoundingToScalar

public void testCastWithRoundingToScalar()

testCastDecimalToDoubleToInteger

public void testCastDecimalToDoubleToInteger()

testCastApproxNumericLimits

public void testCastApproxNumericLimits()

testCastToApproxNumeric

public void testCastToApproxNumeric()

testCastNull

public void testCastNull()

testCastDateTime

public void testCastDateTime()

getCalendarNotTooNear

protected static 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.

Parameters:

timeUnit - Time unit

Returns:

calendar

testCastToBoolean

public void testCastToBoolean()

testCase

public void testCase()

testCaseType

public void testCaseType()

testJdbcFn

public void testJdbcFn()

Tests support for JDBC functions.

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

testSelect

public void testSelect()

testLiteralChain

public void testLiteralChain()

testRow

public void testRow()

testAndOperator

public void testAndOperator()

testConcatOperator

public void testConcatOperator()

testDivideOperator

public void testDivideOperator()

testEqualsOperator

public void testEqualsOperator()

testGreaterThanOperator

public void testGreaterThanOperator()

testIsDistinctFromOperator

public void testIsDistinctFromOperator()

testIsNotDistinctFromOperator

public void testIsNotDistinctFromOperator()

testGreaterThanOrEqualOperator

public void testGreaterThanOrEqualOperator()

testInOperator

public void testInOperator()

testNotInOperator

public void testNotInOperator()

testOverlapsOperator

public void testOverlapsOperator()

testLessThanOperator

public void testLessThanOperator()

testLessThanOrEqualOperator

public void testLessThanOrEqualOperator()

testMinusOperator

public void testMinusOperator()

testMinusIntervalOperator

public void testMinusIntervalOperator()

testMinusDateOperator

public void testMinusDateOperator()

testMultiplyOperator

public void testMultiplyOperator()

testNotEqualsOperator

public void testNotEqualsOperator()

testOrOperator

public void testOrOperator()

testPlusOperator

public void testPlusOperator()

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()

testExistsOperator

public void testExistsOperator()

testNotOperator

public void testNotOperator()

testPrefixMinusOperator

public void testPrefixMinusOperator()

testPrefixPlusOperator

public void testPrefixPlusOperator()

testExplicitTableOperator

public void testExplicitTableOperator()

testValuesOperator

public void testValuesOperator()

testNotLikeOperator

public void testNotLikeOperator()

testLikeOperator

public void testLikeOperator()

testNotSimilarToOperator

public void testNotSimilarToOperator()

testSimilarToOperator

public void testSimilarToOperator()

testEscapeOperator

public void testEscapeOperator()

testConvertFunc

public void testConvertFunc()

testTranslateFunc

public void testTranslateFunc()

testOverlayFunc

public void testOverlayFunc()

testPositionFunc

public void testPositionFunc()

testCharLengthFunc

public void testCharLengthFunc()

testCharacterLengthFunc

public void testCharacterLengthFunc()

testUpperFunc

public void testUpperFunc()

testLowerFunc

public void testLowerFunc()

testInitcapFunc

public void testInitcapFunc()

testPowerFunc

public void testPowerFunc()

testExpFunc

public void testExpFunc()

testModFunc

public void testModFunc()

testModFuncDivByZero

public void testModFuncDivByZero()

testLnFunc

public void testLnFunc()

testLogFunc

public void testLogFunc()

testAbsFunc

public void testAbsFunc()

testNullifFunc

public void testNullifFunc()

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()

testLocalTimeFunc

public void testLocalTimeFunc()

testLocalTimestampFunc

public void testLocalTimestampFunc()

testCurrentTimeFunc

public void testCurrentTimeFunc()

testCurrentTimestampFunc

public void testCurrentTimestampFunc()

currentTimeString

protected static String currentTimeString(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:".

Parameters:

tz - Time zone

Returns:

Time string

testCurrentDateFunc

public void testCurrentDateFunc()

testSubstringFunction

public void testSubstringFunction()

testTrimFunc

public void testTrimFunc()

testWindow

public void testWindow()

testElementFunc

public void testElementFunc()

testCardinalityFunc

public void testCardinalityFunc()

testMemberOfOperator

public void testMemberOfOperator()

testCollectFunc

public void testCollectFunc()

testFusionFunc

public void testFusionFunc()

testExtractFunc

public void testExtractFunc()

testCeilFunc

public void testCeilFunc()

testFloorFunc

public void testFloorFunc()

testDenseRankFunc

public void testDenseRankFunc()

testPercentRankFunc

public void testPercentRankFunc()

testRankFunc

public void testRankFunc()

testCumeDistFunc

public void testCumeDistFunc()

testRowNumberFunc

public void testRowNumberFunc()

testCountFunc

public void testCountFunc()

testSumFunc

public void testSumFunc()

testAvgFunc

public void testAvgFunc()

testMinFunc

public void testMinFunc()

testMaxFunc

public void testMaxFunc()

testLastValueFunc

public void testLastValueFunc()

testFirstValueFunc

public void testFirstValueFunc()

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()

getValues

private List<Object> getValues(BasicSqlType type,
                               boolean inBound)