Repository: phoenix Updated Branches: refs/heads/calcite 07739579a -> f9c661680
http://git-wip-us.apache.org/repos/asf/phoenix/blob/f9c66168/phoenix-core/src/test/java/org/apache/phoenix/calcite/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/calcite/SqlOperatorBaseTest.java b/phoenix-core/src/test/java/org/apache/phoenix/calcite/SqlOperatorBaseTest.java new file mode 100644 index 0000000..696a2e1 --- /dev/null +++ b/phoenix-core/src/test/java/org/apache/phoenix/calcite/SqlOperatorBaseTest.java @@ -0,0 +1,5540 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.phoenix.calcite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.fail; + +import java.math.BigDecimal; +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.text.SimpleDateFormat; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Calendar; +import java.util.Collections; +import java.util.List; +import java.util.TimeZone; +import java.util.regex.Pattern; + +import org.apache.calcite.avatica.util.DateTimeUtils; +import org.apache.calcite.linq4j.Linq4j; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.runtime.Hook; +import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlCallBinding; +import org.apache.calcite.sql.SqlDataTypeSpec; +import org.apache.calcite.sql.SqlDialect; +import org.apache.calcite.sql.SqlIdentifier; +import org.apache.calcite.sql.SqlJdbcFunctionCall; +import org.apache.calcite.sql.SqlLiteral; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlNodeList; +import org.apache.calcite.sql.SqlOperandCountRange; +import org.apache.calcite.sql.SqlOperator; +import org.apache.calcite.sql.fun.SqlStdOperatorTable; +import org.apache.calcite.sql.parser.SqlParserPos; +import org.apache.calcite.sql.pretty.SqlPrettyWriter; +import org.apache.calcite.sql.test.DefaultSqlTestFactory; +import org.apache.calcite.sql.test.SqlTester; +import org.apache.calcite.sql.test.SqlTesterImpl; +import org.apache.calcite.sql.test.SqlTests; +import org.apache.calcite.sql.type.BasicSqlType; +import org.apache.calcite.sql.type.SqlOperandTypeChecker; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.sql.util.SqlString; +import org.apache.calcite.sql.validate.SqlValidatorImpl; +import org.apache.calcite.sql.validate.SqlValidatorScope; +import org.apache.calcite.test.CalciteAssert; +import org.apache.calcite.test.SqlLimitsTest; +import org.apache.calcite.util.Bug; +import org.apache.calcite.util.Holder; +import org.apache.calcite.util.Pair; +import org.apache.calcite.util.Util; +import org.junit.Before; +import org.junit.Ignore; +import org.junit.Test; + +import com.google.common.base.Function; +import com.google.common.collect.Lists; + +/** + * Contains unit tests for all operators. Each of the methods is named after an + * operator. + * + * <p>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 {@link SqlTester} object + * which the derived class must provide.</p> + * + * <p>Different implementations of {@link SqlTester} are possible, such as:</p> + * + * <ul> + * <li>Execute against a real farrago database + * <li>Execute in pure java (parsing and validation can be done, but expression + * evaluation is not possible) + * <li>Generate a SQL script. + * <li>Analyze which operators are adequately tested. + * </ul> + * + * <p>A typical method will be named after the operator it is testing (say + * <code>testSubstringFunc</code>). It first calls + * {@link SqlTester#setFor(org.apache.calcite.sql.SqlOperator, org.apache.calcite.sql.test.SqlTester.VmName...)} + * to declare which operator it is testing. + * + * <blockquote> + * <pre><code> + * public void testSubstringFunc() { + * tester.setFor(SqlStdOperatorTable.substringFunc); + * tester.checkScalar("sin(0)", "0"); + * tester.checkScalar("sin(1.5707)", "1"); + * }</code></pre> + * </blockquote> + * + * <p>The rest of the method contains calls to the various {@code checkXxx} + * methods in the {@link SqlTester} interface. For an operator + * to be adequately tested, there need to be tests for: + * + * <ul> + * <li>Parsing all of its the syntactic variants. + * <li>Deriving the type of in all combinations of arguments. + * + * <ul> + * <li>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.</li> + * <li>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.</li> + * </ul> + * </li> + * <li>Executing the function. Pay particular attention to corner cases such as + * null arguments or null results.</li> + * </ul> + */ +public abstract class SqlOperatorBaseTest { + //~ Static fields/initializers --------------------------------------------- + + // TODO: Change message when Fnl3Fixed to something like + // "Invalid character for cast: PC=0 Code=22018" + public static final String INVALID_CHAR_MESSAGE = + Bug.FNL3_FIXED ? null : "(?s).*"; + + // TODO: Change message when Fnl3Fixed to something like + // "Overflow during calculation or cast: PC=0 Code=22003" + public static final String OUT_OF_RANGE_MESSAGE = + Bug.FNL3_FIXED ? null : "(?s).*"; + + // TODO: Change message when Fnl3Fixed to something like + // "Division by zero: PC=0 Code=22012" + public static final String DIVISION_BY_ZERO_MESSAGE = + Bug.FNL3_FIXED ? null : "(?s).*"; + + // TODO: Change message when Fnl3Fixed to something like + // "String right truncation: PC=0 Code=22001" + public static final String STRING_TRUNC_MESSAGE = + Bug.FNL3_FIXED ? null : "(?s).*"; + + // TODO: Change message when Fnl3Fixed to something like + // "Invalid datetime format: PC=0 Code=22007" + public static final String BAD_DATETIME_MESSAGE = + Bug.FNL3_FIXED ? null : "(?s).*"; + + public static final String LITERAL_OUT_OF_RANGE_MESSAGE = + "(?s).*Numeric literal.*out of range.*"; + + public static final boolean TODO = false; + + /** + * Regular expression for a SQL TIME(0) value. + */ + public static final Pattern TIME_PATTERN = + Pattern.compile( + "[0-9][0-9]:[0-9][0-9]:[0-9][0-9]"); + + /** + * Regular expression for a SQL TIMESTAMP(0) value. + */ + public static final Pattern TIMESTAMP_PATTERN = + Pattern.compile( + "[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] " + + "[0-9][0-9]:[0-9][0-9]:[0-9][0-9]"); + + /** + * Regular expression for a SQL DATE value. + */ + public static final Pattern DATE_PATTERN = + Pattern.compile( + "[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]"); + + public static final String[] NUMERIC_TYPE_NAMES = { + "TINYINT", "SMALLINT", "INTEGER", "BIGINT", + "DECIMAL(5, 2)", "REAL", "FLOAT", "DOUBLE" + }; + + // REVIEW jvs 27-Apr-2006: for Float and Double, MIN_VALUE + // is the smallest positive value, not the smallest negative value + public static final String[] MIN_NUMERIC_STRINGS = { + Long.toString(Byte.MIN_VALUE), + Long.toString(Short.MIN_VALUE), + Long.toString(Integer.MIN_VALUE), + Long.toString(Long.MIN_VALUE), + "-999.99", + + // NOTE jvs 26-Apr-2006: Win32 takes smaller values from win32_values.h + "1E-37", /*Float.toString(Float.MIN_VALUE)*/ + "2E-307", /*Double.toString(Double.MIN_VALUE)*/ + "2E-307" /*Double.toString(Double.MIN_VALUE)*/, + }; + + public static final String[] MIN_OVERFLOW_NUMERIC_STRINGS = { + Long.toString(Byte.MIN_VALUE - 1), + Long.toString(Short.MIN_VALUE - 1), + Long.toString((long) Integer.MIN_VALUE - 1), + new BigDecimal(Long.MIN_VALUE).subtract(BigDecimal.ONE).toString(), + "-1000.00", + "1e-46", + "1e-324", + "1e-324" + }; + + public static final String[] MAX_NUMERIC_STRINGS = { + Long.toString(Byte.MAX_VALUE), + Long.toString(Short.MAX_VALUE), + Long.toString(Integer.MAX_VALUE), + Long.toString(Long.MAX_VALUE), "999.99", + + // NOTE jvs 26-Apr-2006: use something slightly less than MAX_VALUE + // because roundtripping string to approx to string doesn't preserve + // MAX_VALUE on win32 + "3.4028234E38", /*Float.toString(Float.MAX_VALUE)*/ + "1.79769313486231E308", /*Double.toString(Double.MAX_VALUE)*/ + "1.79769313486231E308" /*Double.toString(Double.MAX_VALUE)*/ + }; + + public static final String[] MAX_OVERFLOW_NUMERIC_STRINGS = { + Long.toString(Byte.MAX_VALUE + 1), + Long.toString(Short.MAX_VALUE + 1), + Long.toString((long) Integer.MAX_VALUE + 1), + (new BigDecimal(Long.MAX_VALUE)).add(BigDecimal.ONE).toString(), + "1000.00", + "1e39", + "-1e309", + "1e309" + }; + private static final boolean[] FALSE_TRUE = {false, true}; + private static final SqlTester.VmName VM_FENNEL = SqlTester.VmName.FENNEL; + private static final SqlTester.VmName VM_JAVA = SqlTester.VmName.JAVA; + private static final SqlTester.VmName VM_EXPAND = SqlTester.VmName.EXPAND; + protected static final TimeZone UTC_TZ = TimeZone.getTimeZone("GMT"); + // time zone for the LOCAL_{DATE,TIME,TIMESTAMP} functions + protected static final TimeZone LOCAL_TZ = TimeZone.getDefault(); + // time zone for the CURRENT{DATE,TIME,TIMESTAMP} functions + protected static final TimeZone CURRENT_TZ = LOCAL_TZ; + + private static final Pattern INVALID_ARG_FOR_POWER = Pattern.compile( + "(?s).*Invalid argument\\(s\\) for 'POWER' function.*"); + + private static final Pattern CODE_2201F = Pattern.compile( + "(?s).*could not calculate results for the following row.*PC=5 Code=2201F.*"); + + /** + * Whether DECIMAL type is implemented. + */ + public static final boolean DECIMAL = false; + + /** + * Whether INTERVAL type is implemented. + */ + public static final boolean INTERVAL = false; + + private final boolean enable; + + protected final SqlTester tester; + + //~ Constructors ----------------------------------------------------------- + + /** + * Creates a SqlOperatorBaseTest. + * + * @param enable Whether to run "failing" tests. + * @param tester Means to validate, execute various statements. + */ + protected SqlOperatorBaseTest(boolean enable, SqlTester tester) { + this.enable = enable; + this.tester = tester; + assert tester != null; + } + + //~ Methods ---------------------------------------------------------------- + + @Before + public void setUp() throws Exception { + tester.setFor(null); + } + + //--- Tests ----------------------------------------------------------- + + /** + * For development. Put any old code in here. + */ + @Test public void testDummy() { + } + + @Test public void testBetween() { + tester.setFor( + SqlStdOperatorTable.BETWEEN, + SqlTester.VmName.EXPAND); + tester.checkBoolean("2 between 1 and 3", Boolean.TRUE); + tester.checkBoolean("2 between 3 and 2", Boolean.FALSE); + tester.checkBoolean("2 between symmetric 3 and 2", Boolean.TRUE); + tester.checkBoolean("3 between 1 and 3", Boolean.TRUE); + tester.checkBoolean("4 between 1 and 3", Boolean.FALSE); + tester.checkBoolean("1 between 4 and -3", Boolean.FALSE); + tester.checkBoolean("1 between -1 and -3", Boolean.FALSE); + tester.checkBoolean("1 between -1 and 3", Boolean.TRUE); + tester.checkBoolean("1 between 1 and 1", Boolean.TRUE); + tester.checkBoolean("1.5 between 1 and 3", Boolean.TRUE); + tester.checkBoolean("1.2 between 1.1 and 1.3", Boolean.TRUE); + tester.checkBoolean("1.5 between 2 and 3", Boolean.FALSE); + tester.checkBoolean("1.5 between 1.6 and 1.7", Boolean.FALSE); + tester.checkBoolean("1.2e1 between 1.1 and 1.3", Boolean.FALSE); + tester.checkBoolean("1.2e0 between 1.1 and 1.3", Boolean.TRUE); + tester.checkBoolean("1.5e0 between 2 and 3", Boolean.FALSE); + tester.checkBoolean("1.5e0 between 2e0 and 3e0", Boolean.FALSE); + tester.checkBoolean( + "1.5e1 between 1.6e1 and 1.7e1", + Boolean.FALSE); + if (ENABLE_NULL_STRING_TEST) tester.checkBoolean("x'' between x'' and x''", Boolean.TRUE); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as integer) between -1 and 2"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("1 between -1 and cast(null as integer)"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull( + "1 between cast(null as integer) and cast(null as integer)"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("1 between cast(null as integer) and 1"); + tester.checkBoolean("x'0A00015A' between x'0A000130' and x'0A0001B0'", Boolean.TRUE); + tester.checkBoolean("x'0A00015A' between x'0A0001A0' and x'0A0001B0'", Boolean.FALSE); + } + + @Test public void testNotBetween() { + tester.setFor(SqlStdOperatorTable.NOT_BETWEEN, VM_EXPAND); + tester.checkBoolean("2 not between 1 and 3", Boolean.FALSE); + tester.checkBoolean("3 not between 1 and 3", Boolean.FALSE); + tester.checkBoolean("4 not between 1 and 3", Boolean.TRUE); + tester.checkBoolean( + "1.2e0 not between 1.1 and 1.3", + Boolean.FALSE); + tester.checkBoolean("1.2e1 not between 1.1 and 1.3", Boolean.TRUE); + tester.checkBoolean("1.5e0 not between 2 and 3", Boolean.TRUE); + tester.checkBoolean("1.5e0 not between 2e0 and 3e0", Boolean.TRUE); + tester.checkBoolean("x'0A00015A' not between x'0A000130' and x'0A0001B0'", Boolean.FALSE); + tester.checkBoolean("x'0A00015A' not between x'0A0001A0' and x'0A0001B0'", Boolean.TRUE); + } + + private String getCastString( + String value, + String targetType, + boolean errorLoc) { + if (errorLoc) { + value = "^" + value + "^"; + } + return "cast(" + value + " as " + targetType + ")"; + } + + private void checkCastToApproxOkay( + String value, + String targetType, + double expected, + double delta) { + tester.checkScalarApprox( + getCastString(value, targetType, false), + targetType + " NOT NULL", + expected, + delta); + } + + private void checkCastToStringOkay( + String value, + String targetType, + String expected) { + tester.checkString( + getCastString(value, targetType, false), + expected, + targetType + " NOT NULL"); + } + + private void checkCastToScalarOkay( + String value, + String targetType, + String expected) { + tester.checkScalarExact( + getCastString(value, targetType, false), + targetType + " NOT NULL", + expected); + } + + private void checkCastToScalarOkay(String value, String targetType) { + checkCastToScalarOkay(value, targetType, value); + } + + private void checkCastFails( + String value, + String targetType, + String expectedError, + boolean runtime) { + tester.checkFails( + getCastString(value, targetType, !runtime), + expectedError, + runtime); + } + + private void checkCastToString(String value, String type, String expected) { + String spaces = " "; + if (expected == null) { + expected = value.trim(); + } + int len = expected.length(); + if (type != null) { + value = getCastString(value, type, false); + } + + // currently no exception thrown for truncation + if (Bug.DT239_FIXED) { + checkCastFails( + value, + "VARCHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE, + true); + } + + checkCastToStringOkay(value, "VARCHAR(" + len + ")", expected); + checkCastToStringOkay(value, "VARCHAR(" + (len + 5) + ")", expected); + + // currently no exception thrown for truncation + if (Bug.DT239_FIXED) { + checkCastFails( + value, + "CHAR(" + (len - 1) + ")", STRING_TRUNC_MESSAGE, + true); + } + + checkCastToStringOkay( + value, + "CHAR(" + len + ")", + expected); + checkCastToStringOkay( + value, + "CHAR(" + (len + 5) + ")", + expected + spaces); + } + + @Test public void testCastToString() { + if (!ENABLE_CAST_NUMBERIC_TO_STRING_TEST) return; + tester.setFor(SqlStdOperatorTable.CAST); + + // integer + checkCastToString("123", "CHAR(3)", "123"); + checkCastToString("0", "CHAR", "0"); + checkCastToString("-123", "CHAR(4)", "-123"); + + // decimal + checkCastToString("123.4", "CHAR(5)", "123.4"); + checkCastToString("-0.0", "CHAR(2)", ".0"); + checkCastToString("-123.4", "CHAR(6)", "-123.4"); + + tester.checkString( + "cast(1.29 as varchar(10))", + "1.29", + "VARCHAR(10) NOT NULL"); + tester.checkString( + "cast(.48 as varchar(10))", + ".48", + "VARCHAR(10) NOT NULL"); + tester.checkFails( + "cast(2.523 as char(2))", STRING_TRUNC_MESSAGE, + true); + + tester.checkString( + "cast(-0.29 as varchar(10))", + "-.29", + "VARCHAR(10) NOT NULL"); + tester.checkString( + "cast(-1.29 as varchar(10))", + "-1.29", + "VARCHAR(10) NOT NULL"); + + // approximate + checkCastToString("1.23E45", "CHAR(7)", "1.23E45"); + checkCastToString("CAST(0 AS DOUBLE)", "CHAR(3)", "0E0"); + checkCastToString("-1.20e-07", "CHAR(7)", "-1.2E-7"); + checkCastToString("cast(0e0 as varchar(5))", "CHAR(3)", "0E0"); + if (TODO) { + checkCastToString( + "cast(-45e-2 as varchar(17))", "CHAR(7)", + "-4.5E-1"); + } + if (TODO) { + checkCastToString( + "cast(4683442.3432498375e0 as varchar(20))", + "CHAR(19)", + "4.683442343249838E6"); + } + if (TODO) { + checkCastToString("cast(-0.1 as real)", "CHAR(5)", "-1E-1"); + } + + tester.checkFails( + "cast(1.3243232e0 as varchar(4))", STRING_TRUNC_MESSAGE, + true); + tester.checkFails( + "cast(1.9e5 as char(4))", STRING_TRUNC_MESSAGE, + true); + + // string + checkCastToString("'abc'", "CHAR(1)", "a"); + checkCastToString("'abc'", "CHAR(3)", "abc"); + checkCastToString("cast('abc' as varchar(6))", "CHAR(3)", "abc"); + + // date & time + checkCastToString("date '2008-01-01'", "CHAR(10)", "2008-01-01"); + checkCastToString("time '1:2:3'", "CHAR(8)", "01:02:03"); + checkCastToString( + "timestamp '2008-1-1 1:2:3'", + "CHAR(19)", + "2008-01-01 01:02:03"); + checkCastToString( + "timestamp '2008-1-1 1:2:3'", + "VARCHAR(30)", + "2008-01-01 01:02:03"); + + checkCastToString( + "interval '3-2' year to month", + "CHAR(5)", + "+3-02"); + checkCastToString( + "interval '32' month", + "CHAR(3)", + "+32"); + checkCastToString( + "interval '1 2:3:4' day to second", + "CHAR(11)", + "+1 02:03:04"); + checkCastToString( + "interval '1234.56' second(4,2)", + "CHAR(8)", + "+1234.56"); + checkCastToString( + "interval '60' day", + "CHAR(8)", + "+60"); + + // boolean + checkCastToString("True", "CHAR(4)", "TRUE"); + checkCastToString("False", "CHAR(5)", "FALSE"); + tester.checkFails( + "cast(true as char(3))", INVALID_CHAR_MESSAGE, + true); + tester.checkFails( + "cast(false as char(4))", INVALID_CHAR_MESSAGE, + true); + tester.checkFails( + "cast(true as varchar(3))", INVALID_CHAR_MESSAGE, + true); + tester.checkFails( + "cast(false as varchar(4))", INVALID_CHAR_MESSAGE, + true); + } + + @Test public void testCastExactNumericLimits() { + tester.setFor(SqlStdOperatorTable.CAST); + + // Test casting for min,max, out of range for exact numeric types + for (int i = 0; i < NUMERIC_TYPE_NAMES.length; i++) { + String type = NUMERIC_TYPE_NAMES[i]; + + if (type.equalsIgnoreCase("DOUBLE") + || type.equalsIgnoreCase("FLOAT") + || type.equalsIgnoreCase("REAL")) { + // Skip approx types + continue; + } + + // Convert from literal to type + checkCastToScalarOkay(MAX_NUMERIC_STRINGS[i], type); + checkCastToScalarOkay(MIN_NUMERIC_STRINGS[i], type); + + // Overflow test + if (type.equalsIgnoreCase("BIGINT")) { + // Literal of range + checkCastFails( + MAX_OVERFLOW_NUMERIC_STRINGS[i], + type, LITERAL_OUT_OF_RANGE_MESSAGE, + false); + checkCastFails( + MIN_OVERFLOW_NUMERIC_STRINGS[i], + type, LITERAL_OUT_OF_RANGE_MESSAGE, + false); + } else { + checkCastFails( + MAX_OVERFLOW_NUMERIC_STRINGS[i], + type, OUT_OF_RANGE_MESSAGE, + true); + checkCastFails( + MIN_OVERFLOW_NUMERIC_STRINGS[i], + type, OUT_OF_RANGE_MESSAGE, + true); + } + + if (!enable) { + return; + } + // Convert from string to type + checkCastToScalarOkay( + "'" + MAX_NUMERIC_STRINGS[i] + "'", + type, + MAX_NUMERIC_STRINGS[i]); + checkCastToScalarOkay( + "'" + MIN_NUMERIC_STRINGS[i] + "'", + type, + MIN_NUMERIC_STRINGS[i]); + + checkCastFails( + "'" + MAX_OVERFLOW_NUMERIC_STRINGS[i] + "'", + type, OUT_OF_RANGE_MESSAGE, + true); + checkCastFails( + "'" + MIN_OVERFLOW_NUMERIC_STRINGS[i] + "'", + type, + OUT_OF_RANGE_MESSAGE, + true); + + // Convert from type to string + checkCastToString(MAX_NUMERIC_STRINGS[i], null, null); + checkCastToString(MAX_NUMERIC_STRINGS[i], type, null); + + checkCastToString(MIN_NUMERIC_STRINGS[i], null, null); + checkCastToString(MIN_NUMERIC_STRINGS[i], type, null); + + checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true); + } + } + + @Test public void testCastToExactNumeric() { + tester.setFor(SqlStdOperatorTable.CAST); + + checkCastToScalarOkay("1", "BIGINT"); + checkCastToScalarOkay("1", "INTEGER"); + checkCastToScalarOkay("1", "SMALLINT"); + checkCastToScalarOkay("1", "TINYINT"); + checkCastToScalarOkay("1", "DECIMAL(4, 0)"); + checkCastToScalarOkay("-1", "BIGINT"); + checkCastToScalarOkay("-1", "INTEGER"); + checkCastToScalarOkay("-1", "SMALLINT"); + checkCastToScalarOkay("-1", "TINYINT"); + checkCastToScalarOkay("-1", "DECIMAL(4, 0)"); + + checkCastToScalarOkay("1.234E3", "INTEGER", "1234"); + checkCastToScalarOkay("-9.99E2", "INTEGER", "-999"); + if (ENABLE_CAST_STRING_TO_NUMERIC_TEST) { + checkCastToScalarOkay("'1'", "INTEGER", "1"); + checkCastToScalarOkay("' 01 '", "INTEGER", "1"); + checkCastToScalarOkay("'-1'", "INTEGER", "-1"); + checkCastToScalarOkay("' -00 '", "INTEGER", "0"); + + // string to integer + tester.checkScalarExact("cast('6543' as integer)", "6543"); + tester.checkScalarExact("cast(' -123 ' as int)", "-123"); + tester.checkScalarExact( + "cast('654342432412312' as bigint)", + "BIGINT NOT NULL", + "654342432412312"); + } + } + + @Test public void testCastStringToDecimal() { + tester.setFor(SqlStdOperatorTable.CAST); + if (!DECIMAL) { + return; + } + // string to decimal + tester.checkScalarExact( + "cast('1.29' as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "1.3"); + tester.checkScalarExact( + "cast(' 1.25 ' as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "1.3"); + tester.checkScalarExact( + "cast('1.21' as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "1.2"); + tester.checkScalarExact( + "cast(' -1.29 ' as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-1.3"); + tester.checkScalarExact( + "cast('-1.25' as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-1.3"); + tester.checkScalarExact( + "cast(' -1.21 ' as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-1.2"); + tester.checkFails( + "cast(' -1.21e' as decimal(2,1))", INVALID_CHAR_MESSAGE, + true); + } + + @Test public void testCastIntervalToNumeric() { + if (!ENABLE_INTERVAL_TEST) return; + tester.setFor(SqlStdOperatorTable.CAST); + + // interval to decimal + if (DECIMAL) { + tester.checkScalarExact( + "cast(INTERVAL '1.29' second(1,2) as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "1.3"); + tester.checkScalarExact( + "cast(INTERVAL '1.25' second as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "1.3"); + tester.checkScalarExact( + "cast(INTERVAL '-1.29' second as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-1.3"); + tester.checkScalarExact( + "cast(INTERVAL '-1.25' second as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-1.3"); + tester.checkScalarExact( + "cast(INTERVAL '-1.21' second as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-1.2"); + tester.checkScalarExact( + "cast(INTERVAL '5' minute as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "5.0"); + tester.checkScalarExact( + "cast(INTERVAL '5' hour as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "5.0"); + tester.checkScalarExact( + "cast(INTERVAL '5' day as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "5.0"); + tester.checkScalarExact( + "cast(INTERVAL '5' month as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "5.0"); + tester.checkScalarExact( + "cast(INTERVAL '5' year as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "5.0"); + tester.checkScalarExact( + "cast(INTERVAL '-5' day as decimal(2,1))", + "DECIMAL(2, 1) NOT NULL", + "-5.0"); + } + + if (!INTERVAL) { + return; + } + + // Interval to bigint + tester.checkScalarExact( + "cast(INTERVAL '1.25' second as bigint)", + "BIGINT NOT NULL", + "1"); + tester.checkScalarExact( + "cast(INTERVAL '-1.29' second(1,2) as bigint)", + "BIGINT NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '5' day as bigint)", + "BIGINT NOT NULL", + "5"); + + // Interval to integer + tester.checkScalarExact( + "cast(INTERVAL '1.25' second as integer)", + "INTEGER NOT NULL", + "1"); + tester.checkScalarExact( + "cast(INTERVAL '-1.29' second(1,2) as integer)", + "INTEGER NOT NULL", + "-1"); + tester.checkScalarExact( + "cast(INTERVAL '5' day as integer)", + "INTEGER NOT NULL", + "5"); + } + + @Test public void testCastToInterval() { + if (!ENABLE_INTERVAL_TEST) return; + tester.setFor(SqlStdOperatorTable.CAST); + if (!INTERVAL) { + return; + } + tester.checkScalar( + "cast(5 as interval second)", + "+5.000000", + "INTERVAL SECOND NOT NULL"); + tester.checkScalar( + "cast(5 as interval minute)", + "+5", + "INTERVAL MINUTE NOT NULL"); + tester.checkScalar( + "cast(5 as interval hour)", + "+5", + "INTERVAL HOUR NOT NULL"); + tester.checkScalar( + "cast(5 as interval day)", + "+5", + "INTERVAL DAY NOT NULL"); + tester.checkScalar( + "cast(5 as interval month)", + "+5", + "INTERVAL MONTH NOT NULL"); + tester.checkScalar( + "cast(5 as interval year)", + "+5", + "INTERVAL YEAR NOT NULL"); + tester.checkScalar( + "cast(5.7 as interval day)", + "+6", + "INTERVAL DAY NOT NULL"); + tester.checkScalar( + "cast(-5.7 as interval day)", + "-6", + "INTERVAL DAY NOT NULL"); + tester.checkScalar( + "cast(3456 as interval month(4))", + "+3456", + "INTERVAL MONTH(4) NOT NULL"); + tester.checkScalar( + "cast(-5723 as interval minute(4))", + "-5723", + "INTERVAL MINUTE(4) NOT NULL"); + } + + @Test public void testCastIntervalToInterval() { + if (!ENABLE_INTERVAL_TEST) return; + if (!INTERVAL) { + return; + } + tester.checkScalar( + "cast(interval '2 5' day to hour as interval hour to minute)", + "+29:00", + "INTERVAL HOUR TO MINUTE NOT NULL"); + } + + @Test public void testCastWithRoundingToScalar() { + if (!BUG_CHECKER_CAST_FIXED) return; + tester.setFor(SqlStdOperatorTable.CAST); + checkCastToScalarOkay("1.25", "INTEGER", "1"); + checkCastToScalarOkay("1.25E0", "INTEGER", "1"); + if (!enable) { + return; + } + checkCastToScalarOkay("1.5", "INTEGER", "2"); + checkCastToScalarOkay("5E-1", "INTEGER", "1"); + checkCastToScalarOkay("1.75", "INTEGER", "2"); + checkCastToScalarOkay("1.75E0", "INTEGER", "2"); + + checkCastToScalarOkay("-1.25", "INTEGER", "-1"); + checkCastToScalarOkay("-1.25E0", "INTEGER", "-1"); + checkCastToScalarOkay("-1.5", "INTEGER", "-2"); + checkCastToScalarOkay("-5E-1", "INTEGER", "-1"); + checkCastToScalarOkay("-1.75", "INTEGER", "-2"); + checkCastToScalarOkay("-1.75E0", "INTEGER", "-2"); + + checkCastToScalarOkay("1.23454", "DECIMAL(8, 4)", "1.2345"); + checkCastToScalarOkay("1.23454E0", "DECIMAL(8, 4)", "1.2345"); + checkCastToScalarOkay("1.23455", "DECIMAL(8, 4)", "1.2346"); + checkCastToScalarOkay("5E-5", "DECIMAL(8, 4)", "0.0001"); + checkCastToScalarOkay("1.99995", "DECIMAL(8, 4)", "2.0000"); + checkCastToScalarOkay("1.99995E0", "DECIMAL(8, 4)", "2.0000"); + + checkCastToScalarOkay("-1.23454", "DECIMAL(8, 4)", "-1.2345"); + checkCastToScalarOkay("-1.23454E0", "DECIMAL(8, 4)", "-1.2345"); + checkCastToScalarOkay("-1.23455", "DECIMAL(8, 4)", "-1.2346"); + checkCastToScalarOkay("-5E-5", "DECIMAL(8, 4)", "-0.0001"); + checkCastToScalarOkay("-1.99995", "DECIMAL(8, 4)", "-2.0000"); + checkCastToScalarOkay("-1.99995E0", "DECIMAL(8, 4)", "-2.0000"); + + // 9.99 round to 10.0, should give out of range error + tester.checkFails( + "cast(9.99 as decimal(2,1))", OUT_OF_RANGE_MESSAGE, + true); + } + + @Test public void testCastDecimalToDoubleToInteger() { + tester.setFor(SqlStdOperatorTable.CAST); + if (!TODO_CAST_DOUBLE_TO_INTEGER_TEST_FINISHED) return; + tester.checkScalarExact( + "cast( cast(1.25 as double) as integer)", + "1"); + tester.checkScalarExact( + "cast( cast(-1.25 as double) as integer)", + "-1"); + if (!enable) { + return; + } + tester.checkScalarExact( + "cast( cast(1.75 as double) as integer)", + "2"); + tester.checkScalarExact( + "cast( cast(-1.75 as double) as integer)", + "-2"); + tester.checkScalarExact( + "cast( cast(1.5 as double) as integer)", + "2"); + tester.checkScalarExact( + "cast( cast(-1.5 as double) as integer)", + "-2"); + } + + @Test public void testCastApproxNumericLimits() { + tester.setFor(SqlStdOperatorTable.CAST); + + // Test casting for min,max, out of range for approx numeric types + for (int i = 0; i < NUMERIC_TYPE_NAMES.length; i++) { + String type = NUMERIC_TYPE_NAMES[i]; + boolean isFloat; + + if (type.equalsIgnoreCase("DOUBLE") + || type.equalsIgnoreCase("FLOAT")) { + isFloat = false; + } else if (type.equalsIgnoreCase("REAL")) { + isFloat = true; + } else { + // Skip non-approx types + continue; + } + + if (!enable) { + return; + } + + // Convert from literal to type + checkCastToApproxOkay( + MAX_NUMERIC_STRINGS[i], + type, + Double.parseDouble(MAX_NUMERIC_STRINGS[i]), + isFloat ? 1E32 : 0); + checkCastToApproxOkay( + MIN_NUMERIC_STRINGS[i], + type, + Double.parseDouble(MIN_NUMERIC_STRINGS[i]), + 0); + + if (isFloat) { + checkCastFails( + MAX_OVERFLOW_NUMERIC_STRINGS[i], + type, OUT_OF_RANGE_MESSAGE, + true); + } else { + // Double: Literal out of range + checkCastFails( + MAX_OVERFLOW_NUMERIC_STRINGS[i], + type, LITERAL_OUT_OF_RANGE_MESSAGE, + false); + } + + // Underflow: goes to 0 + checkCastToApproxOkay(MIN_OVERFLOW_NUMERIC_STRINGS[i], type, 0, 0); + + // Convert from string to type + checkCastToApproxOkay( + "'" + MAX_NUMERIC_STRINGS[i] + "'", + type, + Double.parseDouble(MAX_NUMERIC_STRINGS[i]), + isFloat ? 1E32 : 0); + checkCastToApproxOkay( + "'" + MIN_NUMERIC_STRINGS[i] + "'", + type, + Double.parseDouble(MIN_NUMERIC_STRINGS[i]), + 0); + + checkCastFails( + "'" + MAX_OVERFLOW_NUMERIC_STRINGS[i] + "'", + type, + OUT_OF_RANGE_MESSAGE, + true); + + // Underflow: goes to 0 + checkCastToApproxOkay( + "'" + MIN_OVERFLOW_NUMERIC_STRINGS[i] + "'", + type, + 0, + 0); + + // Convert from type to string + + // Treated as DOUBLE + checkCastToString( + MAX_NUMERIC_STRINGS[i], null, + isFloat ? null : "1.79769313486231E308"); + + // TODO: The following tests are slightly different depending on + // whether the java or fennel calc are used. + // Try to make them the same + if (false /* fennel calc*/) { // Treated as FLOAT or DOUBLE + checkCastToString( + MAX_NUMERIC_STRINGS[i], + type, + // Treated as DOUBLE + isFloat ? "3.402824E38" : "1.797693134862316E308"); + checkCastToString( + MIN_NUMERIC_STRINGS[i], + null, + // Treated as FLOAT or DOUBLE + isFloat ? null : "4.940656458412465E-324"); + checkCastToString( + MIN_NUMERIC_STRINGS[i], + type, + isFloat ? "1.401299E-45" : "4.940656458412465E-324"); + } else if (false /* JavaCalc */) { + // Treated as FLOAT or DOUBLE + checkCastToString( + MAX_NUMERIC_STRINGS[i], + type, + // Treated as DOUBLE + isFloat ? "3.402823E38" : "1.797693134862316E308"); + checkCastToString( + MIN_NUMERIC_STRINGS[i], + null, + isFloat ? null : null); // Treated as FLOAT or DOUBLE + checkCastToString( + MIN_NUMERIC_STRINGS[i], + type, + isFloat ? "1.401298E-45" : null); + } + + checkCastFails("'notnumeric'", type, INVALID_CHAR_MESSAGE, true); + } + } + + @Test public void testCastToApproxNumeric() { + tester.setFor(SqlStdOperatorTable.CAST); + + checkCastToApproxOkay("1", "DOUBLE", 1, 0); + checkCastToApproxOkay("1.0", "DOUBLE", 1, 0); + if (BUG_CHECKER_CAST_FIXED) checkCastToApproxOkay("-2.3", "FLOAT", -2.3, 0.000001); + if (ENABLE_CAST_STRING_TO_NUMERIC_TEST) { + checkCastToApproxOkay("'1'", "DOUBLE", 1, 0); + checkCastToApproxOkay("' -1e-37 '", "DOUBLE", -1e-37, 0); + } + checkCastToApproxOkay("1e0", "DOUBLE", 1, 0); + if (ENABLE_TYPE_REAL_TEST) checkCastToApproxOkay("0e0", "REAL", 0, 0); + } + + @Test public void testCastNull() { + tester.setFor(SqlStdOperatorTable.CAST); + + // null + tester.checkNull("cast(null as integer)"); + if (DECIMAL) { + tester.checkNull("cast(null as decimal(4,3))"); + } + tester.checkNull("cast(null as double)"); + tester.checkNull("cast(null as varchar(10))"); + tester.checkNull("cast(null as char(10))"); + tester.checkNull("cast(null as date)"); + tester.checkNull("cast(null as time)"); + tester.checkNull("cast(null as timestamp)"); + if (ENABLE_INTERVAL_TEST) { + tester.checkNull("cast(null as interval year to month)"); + tester.checkNull("cast(null as interval day to second(3))"); + } + tester.checkNull("cast(null as boolean)"); + } + + @Test public void testCastDateTime() { + if (!ENABLE_CAST_STRING_TO_TIME_TEST) return; + // Test cast for date/time/timestamp + tester.setFor(SqlStdOperatorTable.CAST); + + tester.checkScalar( + "cast(TIMESTAMP '1945-02-24 12:42:25.34' as TIMESTAMP)", + "1945-02-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + + tester.checkScalar( + "cast(TIME '12:42:25.34' as TIME)", + "12:42:25", + "TIME(0) NOT NULL"); + + // test rounding + if (enable) { + tester.checkScalar( + "cast(TIME '12:42:25.9' as TIME)", + "12:42:26", + "TIME(0) NOT NULL"); + } + + if (Bug.FRG282_FIXED) { + // test precision + tester.checkScalar( + "cast(TIME '12:42:25.34' as TIME(2))", + "12:42:25.34", + "TIME(2) NOT NULL"); + } + + tester.checkScalar( + "cast(DATE '1945-02-24' as DATE)", + "1945-02-24", + "DATE NOT NULL"); + + // timestamp <-> time + tester.checkScalar( + "cast(TIMESTAMP '1945-02-24 12:42:25.34' as TIME)", + "12:42:25", + "TIME(0) NOT NULL"); + + // time <-> string + checkCastToString("TIME '12:42:25'", null, "12:42:25"); + if (TODO) { + checkCastToString("TIME '12:42:25.34'", null, "12:42:25.34"); + } + + // Generate the current date as a string, e.g. "2007-04-18". The value + // is guaranteed to be good for at least 2 minutes, which should give + // us time to run the rest of the tests. + final String today = + new SimpleDateFormat("yyyy-MM-dd").format( + getCalendarNotTooNear(Calendar.DAY_OF_MONTH).getTime()); + + tester.checkScalar( + "cast(DATE '1945-02-24' as TIMESTAMP)", + "1945-02-24 00:00:00", + "TIMESTAMP(0) NOT NULL"); + + // Note: Casting to time(0) should lose date info and fractional + // seconds, then casting back to timestamp should initialize to + // current_date. + tester.checkScalar( + "cast(cast(TIMESTAMP '1945-02-24 12:42:25.34' as TIME) as TIMESTAMP)", + today + " 12:42:25", + "TIMESTAMP(0) NOT NULL"); + + tester.checkScalar( + "cast(TIME '12:42:25.34' as TIMESTAMP)", + today + " 12:42:25", + "TIMESTAMP(0) NOT NULL"); + + // timestamp <-> date + tester.checkScalar( + "cast(TIMESTAMP '1945-02-24 12:42:25.34' as DATE)", + "1945-02-24", + "DATE NOT NULL"); + + // Note: casting to Date discards Time fields + tester.checkScalar( + "cast(cast(TIMESTAMP '1945-02-24 12:42:25.34' as DATE) as TIMESTAMP)", + "1945-02-24 00:00:00", + "TIMESTAMP(0) NOT NULL"); + } + + @Test public void testCastStringToDateTime() { + if (!ENABLE_CAST_STRING_TO_TIME_TEST) return; + tester.checkScalar( + "cast('12:42:25' as TIME)", + "12:42:25", + "TIME(0) NOT NULL"); + tester.checkScalar( + "cast('1:42:25' as TIME)", + "01:42:25", + "TIME(0) NOT NULL"); + tester.checkScalar( + "cast('1:2:25' as TIME)", + "01:02:25", + "TIME(0) NOT NULL"); + tester.checkScalar( + "cast(' 12:42:25 ' as TIME)", + "12:42:25", + "TIME(0) NOT NULL"); + tester.checkScalar( + "cast('12:42:25.34' as TIME)", + "12:42:25", + "TIME(0) NOT NULL"); + + if (Bug.FRG282_FIXED) { + tester.checkScalar( + "cast('12:42:25.34' as TIME(2))", + "12:42:25.34", + "TIME(2) NOT NULL"); + } + + tester.checkFails( + "cast('nottime' as TIME)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('1241241' as TIME)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('12:54:78' as TIME)", BAD_DATETIME_MESSAGE, + true); + + // timestamp <-> string + checkCastToString( + "TIMESTAMP '1945-02-24 12:42:25'", + null, + "1945-02-24 12:42:25"); + + if (TODO) { + // TODO: casting allows one to discard precision without error + checkCastToString( + "TIMESTAMP '1945-02-24 12:42:25.34'", + null, + "1945-02-24 12:42:25.34"); + } + + tester.checkScalar( + "cast('1945-02-24 12:42:25' as TIMESTAMP)", + "1945-02-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + tester.checkScalar( + "cast('1945-2-2 12:2:5' as TIMESTAMP)", + "1945-02-02 12:02:05", + "TIMESTAMP(0) NOT NULL"); + tester.checkScalar( + "cast(' 1945-02-24 12:42:25 ' as TIMESTAMP)", + "1945-02-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + tester.checkScalar( + "cast('1945-02-24 12:42:25.34' as TIMESTAMP)", + "1945-02-24 12:42:25", + "TIMESTAMP(0) NOT NULL"); + + if (Bug.FRG282_FIXED) { + tester.checkScalar( + "cast('1945-02-24 12:42:25.34' as TIMESTAMP(2))", + "1945-02-24 12:42:25.34", + "TIMESTAMP(2) NOT NULL"); + } + tester.checkFails( + "cast('nottime' as TIMESTAMP)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('1241241' as TIMESTAMP)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('1945-20-24 12:42:25.34' as TIMESTAMP)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('1945-01-24 25:42:25.34' as TIMESTAMP)", BAD_DATETIME_MESSAGE, + true); + + // date <-> string + checkCastToString("DATE '1945-02-24'", null, "1945-02-24"); + checkCastToString("DATE '1945-2-24'", null, "1945-02-24"); + + tester.checkScalar( + "cast('1945-02-24' as DATE)", + "1945-02-24", + "DATE NOT NULL"); + tester.checkScalar( + "cast(' 1945-02-24 ' as DATE)", + "1945-02-24", + "DATE NOT NULL"); + tester.checkFails( + "cast('notdate' as DATE)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('52534253' as DATE)", BAD_DATETIME_MESSAGE, + true); + tester.checkFails( + "cast('1945-30-24' as DATE)", BAD_DATETIME_MESSAGE, + true); + + // cast null + tester.checkNull("cast(null as date)"); + tester.checkNull("cast(null as timestamp)"); + tester.checkNull("cast(null as time)"); + tester.checkNull("cast(cast(null as varchar(10)) as time)"); + tester.checkNull("cast(cast(null as varchar(10)) as date)"); + tester.checkNull("cast(cast(null as varchar(10)) as timestamp)"); + tester.checkNull("cast(cast(null as date) as timestamp)"); + tester.checkNull("cast(cast(null as time) as timestamp)"); + tester.checkNull("cast(cast(null as timestamp) as date)"); + tester.checkNull("cast(cast(null as timestamp) as time)"); + } + + /** + * Returns a Calendar that is the current time, pausing if we are within 2 + * minutes of midnight or the top of the hour. + * + * @param timeUnit Time unit + * @return calendar + */ + protected static Calendar getCalendarNotTooNear(int timeUnit) { + final Calendar cal = Calendar.getInstance(); + while (true) { + cal.setTimeInMillis(System.currentTimeMillis()); + try { + switch (timeUnit) { + case Calendar.DAY_OF_MONTH: + // Within two minutes of the end of the day. Wait in 10s + // increments until calendar moves into the next next day. + if ((cal.get(Calendar.HOUR_OF_DAY) == 23) + && (cal.get(Calendar.MINUTE) >= 58)) { + Thread.sleep(10 * 1000); + continue; + } + return cal; + + case Calendar.HOUR_OF_DAY: + // Within two minutes of the top of the hour. Wait in 10s + // increments until calendar moves into the next next day. + if (cal.get(Calendar.MINUTE) >= 58) { + Thread.sleep(10 * 1000); + continue; + } + return cal; + + default: + throw Util.newInternal("unexpected time unit " + timeUnit); + } + } catch (InterruptedException e) { + throw Util.newInternal(e); + } + } + } + + @Test public void testCastToBoolean() { + if (!ENABLE_CAST_STRING_TO_BOOLEAN_TEST) return; + tester.setFor(SqlStdOperatorTable.CAST); + + // string to boolean + tester.checkBoolean("cast('true' as boolean)", Boolean.TRUE); + tester.checkBoolean("cast('false' as boolean)", Boolean.FALSE); + tester.checkBoolean("cast(' trUe' as boolean)", Boolean.TRUE); + tester.checkBoolean("cast(' fALse' as boolean)", Boolean.FALSE); + tester.checkFails( + "cast('unknown' as boolean)", INVALID_CHAR_MESSAGE, + true); + + tester.checkBoolean( + "cast(cast('true' as varchar(10)) as boolean)", + Boolean.TRUE); + tester.checkBoolean( + "cast(cast('false' as varchar(10)) as boolean)", + Boolean.FALSE); + tester.checkFails( + "cast(cast('blah' as varchar(10)) as boolean)", INVALID_CHAR_MESSAGE, + true); + } + + @Test public void testCase() { + if (!TODO_CASE_RELATED_TEST_FINISHED) return; + tester.setFor(SqlStdOperatorTable.CASE); + //tester.checkScalarExact("case when 'a'='a' then 1 end", "1"); + + tester.checkString( + "case 2 when 1 then 'a' when 2 then 'bcd' end", + "bcd", + "CHAR(3)"); + tester.checkString( + "case 1 when 1 then 'a' when 2 then 'bcd' end", + "a ", + "CHAR(3)"); + tester.checkString( + "case 1 when 1 then cast('a' as varchar(1)) " + + "when 2 then cast('bcd' as varchar(3)) end", + "a", + "VARCHAR(3)"); + if (DECIMAL) { + tester.checkScalarExact( + "case 2 when 1 then 11.2 when 2 then 4.543 else null end", + "DECIMAL(5, 3)", + "4.543"); + tester.checkScalarExact( + "case 1 when 1 then 11.2 when 2 then 4.543 else null end", + "DECIMAL(5, 3)", + "11.200"); + } + tester.checkScalarExact("case 'a' when 'a' then 1 end", "1"); + tester.checkScalarApprox( + "case 1 when 1 then 11.2e0 when 2 then cast(4 as bigint) else 3 end", + "DOUBLE NOT NULL", + 11.2, + 0); + tester.checkScalarApprox( + "case 1 when 1 then 11.2e0 when 2 then 4 else null end", + "DOUBLE", + 11.2, + 0); + tester.checkScalarApprox( + "case 2 when 1 then 11.2e0 when 2 then 4 else null end", + "DOUBLE", + 4, + 0); + tester.checkScalarApprox( + "case 1 when 1 then 11.2e0 when 2 then 4.543 else null end", + "DOUBLE", + 11.2, + 0); + tester.checkScalarApprox( + "case 2 when 1 then 11.2e0 when 2 then 4.543 else null end", + "DOUBLE", + 4.543, + 0); + tester.checkNull("case 'a' when 'b' then 1 end"); + + // Per spec, 'case x when y then ...' + // translates to 'case when x = y then ...' + // so nulls do not match. + // (Unlike Oracle's 'decode(null, null, ...)', by the way.) + tester.checkString( + "case cast(null as int) when cast(null as int) then 'nulls match' else 'nulls do not match' end", + "nulls do not match", + "CHAR(18) NOT NULL"); + + tester.checkScalarExact( + "case when 'a'=cast(null as varchar(1)) then 1 else 2 end", + "2"); + + // equivalent to "nullif('a',cast(null as varchar(1)))" + tester.checkString( + "case when 'a' = cast(null as varchar(1)) then null else 'a' end", + "a", + "CHAR(1)"); + + if (TODO) { + tester.checkScalar( + "case 1 when 1 then row(1,2) when 2 then row(2,3) end", + "ROW(INTEGER NOT NULL, INTEGER NOT NULL)", + "row(1,2)"); + tester.checkScalar( + "case 1 when 1 then row('a','b') when 2 then row('ab','cd') end", + "ROW(CHAR(2) NOT NULL, CHAR(2) NOT NULL)", + "row('a ','b ')"); + } + + // multiple values in some cases (introduced in SQL:2011) + tester.checkString( + "case 1 " + + "when 1, 2 then '1 or 2' " + + "when 2 then 'not possible' " + + "when 3, 2 then '3' " + + "else 'none of the above' " + + "end", + "1 or 2 ", + "CHAR(17) NOT NULL"); + tester.checkString( + "case 2 " + + "when 1, 2 then '1 or 2' " + + "when 2 then 'not possible' " + + "when 3, 2 then '3' " + + "else 'none of the above' " + + "end", + "1 or 2 ", + "CHAR(17) NOT NULL"); + tester.checkString( + "case 3 " + + "when 1, 2 then '1 or 2' " + + "when 2 then 'not possible' " + + "when 3, 2 then '3' " + + "else 'none of the above' " + + "end", + "3 ", + "CHAR(17) NOT NULL"); + tester.checkString( + "case 4 " + + "when 1, 2 then '1 or 2' " + + "when 2 then 'not possible' " + + "when 3, 2 then '3' " + + "else 'none of the above' " + + "end", + "none of the above", + "CHAR(17) NOT NULL"); + + // TODO: Check case with multisets + } + + @Test public void testCaseNull() { + if (!TODO_CASE_RELATED_TEST_FINISHED) return; + tester.setFor(SqlStdOperatorTable.CASE); + tester.checkScalarExact("case when 1 = 1 then 10 else null end", "10"); + tester.checkNull("case when 1 = 2 then 10 else null end"); + } + + @Test public void testCaseType() { + tester.setFor(SqlStdOperatorTable.CASE); + tester.checkType( + "case 1 when 1 then current_timestamp else null end", + "TIMESTAMP(0)"); + tester.checkType( + "case 1 when 1 then current_timestamp else current_timestamp end", + "TIMESTAMP(0) NOT NULL"); + tester.checkType( + "case when true then current_timestamp else null end", + "TIMESTAMP(0)"); + tester.checkType( + "case when true then current_timestamp end", + "TIMESTAMP(0)"); + tester.checkType( + "case 'x' when 'a' then 3 when 'b' then null else 4.5 end", + "DECIMAL(11, 1)"); + } + + /** + * Tests support for JDBC functions. + * + * <p>See FRG-97 "Support for JDBC escape syntax is incomplete". + */ + @Test public void testJdbcFn() { + tester.setFor(new SqlJdbcFunctionCall("dummy")); + + // There follows one test for each function in appendix C of the JDBC + // 3.0 specification. The test is 'if-false'd out if the function is + // not implemented or is broken. + + if (!ENABLE_NOT_SUPPORT_FUNCTION) return; + // Numeric Functions + if (!enable) { +// return; + } + tester.checkScalar("{fn ABS(-3)}", 3, "INTEGER NOT NULL"); + if (false) { + tester.checkScalar("{fn ACOS(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn ASIN(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn ATAN(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn ATAN2(float1, float2)}", null, ""); + } + if (false) { + tester.checkScalar("{fn CEILING(-2.6)}", 2, ""); + } + if (false) { + tester.checkScalar("{fn COS(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn COT(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn DEGREES(number)}", null, ""); + } + tester.checkScalarApprox( + "{fn EXP(2)}", + "DOUBLE NOT NULL", + 7.389, + 0.001); + if (false) { + tester.checkScalar("{fn FLOOR(2.6)}", 2, "DOUBLE NOT NULL"); + } + tester.checkScalarApprox( + "{fn LOG(10)}", + "DOUBLE NOT NULL", + 2.30258, + 0.001); + if (ENABLE_NOT_SUPPORT_FUNCTION) tester.checkScalarApprox( + "{fn LOG10(100)}", + "DOUBLE NOT NULL", + 2, + 0); + if (ENABLE_NOT_SUPPORT_FUNCTION) tester.checkScalar("{fn MOD(19, 4)}", 3, "INTEGER NOT NULL"); + if (false) { + tester.checkScalar("{fn PI()}", null, ""); + } + tester.checkScalar("{fn POWER(2, 3)}", 8.0, "DOUBLE NOT NULL"); + if (false) { + tester.checkScalar("{fn RADIANS(number)}", null, ""); + } + if (false) { + tester.checkScalar("{fn RAND(integer)}", null, ""); + } + if (false) { + tester.checkScalar("{fn ROUND(number, places)}", null, ""); + } + if (false) { + tester.checkScalar("{fn SIGN(number)}", null, ""); + } + if (false) { + tester.checkScalar("{fn SIN(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn SQRT(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn TAN(float)}", null, ""); + } + if (false) { + tester.checkScalar("{fn TRUNCATE(number, places)}", null, ""); + } + + // String Functions + if (false) { + tester.checkScalar("{fn ASCII(string)}", null, ""); + } + if (false) { + tester.checkScalar("{fn CHAR(code)}", null, ""); + } + if (ENABLE_NOT_SUPPORT_FUNCTION) tester.checkScalar( + "{fn CONCAT('foo', 'bar')}", + "foobar", + "CHAR(6) NOT NULL"); + if (false) { + tester.checkScalar( + "{fn DIFFERENCE(string1, string2)}", + null, + ""); + } + + // REVIEW: is this result correct? I think it should be "abcCdef" + tester.checkScalar( + "{fn INSERT('abc', 1, 2, 'ABCdef')}", + "ABCdefc", + "VARCHAR(9) NOT NULL"); + tester.checkScalar( + "{fn LCASE('foo' || 'bar')}", + "foobar", + "CHAR(6) NOT NULL"); + if (false) { + tester.checkScalar("{fn LEFT(string, count)}", null, ""); + } + if (false) { + tester.checkScalar("{fn LENGTH(string)}", null, ""); + } + tester.checkScalar( + "{fn LOCATE('ha', 'alphabet')}", + 4, + "INTEGER NOT NULL"); + + // only the 2 arg version of locate is implemented + if (false) { + tester.checkScalar( + "{fn LOCATE(string1, string2[, start])}", + null, + ""); + } + + // ltrim is implemented but has a bug in arg checking + if (false) { + tester.checkScalar( + "{fn LTRIM(' xxx ')}", + "xxx", + "VARCHAR(6)"); + } + if (false) { + tester.checkScalar("{fn REPEAT(string, count)}", null, ""); + } + if (false) { + tester.checkScalar( + "{fn REPLACE(string1, string2, string3)}", + null, + ""); + } + if (false) { + tester.checkScalar("{fn RIGHT(string, count)}", null, ""); + } + + // rtrim is implemented but has a bug in arg checking + if (false) { + tester.checkScalar( + "{fn RTRIM(' xxx ')}", + "xxx", + "VARCHAR(6)"); + } + if (false) { + tester.checkScalar("{fn SOUNDEX(string)}", null, ""); + } + if (false) { + tester.checkScalar("{fn SPACE(count)}", null, ""); + } + tester.checkScalar( + "{fn SUBSTRING('abcdef', 2, 3)}", + "bcd", + "VARCHAR(6) NOT NULL"); + tester.checkScalar("{fn UCASE('xxx')}", "XXX", "CHAR(3) NOT NULL"); + + // Time and Date Functions + tester.checkType("{fn CURDATE()}", "DATE NOT NULL"); + tester.checkType("{fn CURTIME()}", "TIME(0) NOT NULL"); + if (false) { + tester.checkScalar("{fn DAYNAME(date)}", null, ""); + } + if (false) { + tester.checkScalar("{fn DAYOFMONTH(date)}", null, ""); + } + if (false) { + tester.checkScalar("{fn DAYOFWEEK(date)}", null, ""); + } + if (false) { + tester.checkScalar("{fn DAYOFYEAR(date)}", null, ""); + } + if (false) { + tester.checkScalar("{fn HOUR(time)}", null, ""); + } + if (false) { + tester.checkScalar("{fn MINUTE(time)}", null, ""); + } + if (false) { + tester.checkScalar("{fn MONTH(date)}", null, ""); + } + if (false) { + tester.checkScalar("{fn MONTHNAME(date)}", null, ""); + } + tester.checkType("{fn NOW()}", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("{fn QUARTER(DATE '2014-12-10')}", "4", + "BIGINT NOT NULL"); + if (false) { + tester.checkScalar("{fn SECOND(time)}", null, ""); + } + if (false) { + tester.checkScalar( + "{fn TIMESTAMPADD(interval, count, timestamp)}", + null, + ""); + } + if (false) { + tester.checkScalar( + "{fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)}", + null, + ""); + } + if (false) { + tester.checkScalar("{fn WEEK(date)}", null, ""); + } + if (false) { + tester.checkScalar("{fn YEAR(date)}", null, ""); + } + + // System Functions + if (false) { + tester.checkScalar("{fn DATABASE()}", null, ""); + } + if (false) { + tester.checkScalar("{fn IFNULL(expression, value)}", null, ""); + } + if (false) { + tester.checkScalar("{fn USER()}", null, ""); + } + + // Conversion Functions + if (false) { + tester.checkScalar("{fn CONVERT(value, SQLtype)}", null, ""); + } + } + + @Test public void testSelect() { + tester.check( + "select * from (values(1))", + SqlTests.INTEGER_TYPE_CHECKER, + "1", + 0); + + // Check return type on scalar subquery in select list. Note return + // type is always nullable even if subquery select value is NOT NULL. + // Bug FRG-189 causes this test to fail only in SqlOperatorTest; not + // in subtypes. + if (Bug.FRG189_FIXED || Bug.TODO_FIXED) { + tester.checkType( + "SELECT *,(SELECT * FROM (VALUES(1))) FROM (VALUES(2))", + "RecordType(INTEGER NOT NULL EXPR$0, INTEGER EXPR$1) NOT NULL"); + tester.checkType( + "SELECT *,(SELECT * FROM (VALUES(CAST(10 as BIGINT)))) " + + "FROM (VALUES(CAST(10 as bigint)))", + "RecordType(BIGINT NOT NULL EXPR$0, BIGINT EXPR$1) NOT NULL"); + tester.checkType( + " SELECT *,(SELECT * FROM (VALUES(10.5))) FROM (VALUES(10.5))", + "RecordType(DECIMAL(3, 1) NOT NULL EXPR$0, DECIMAL(3, 1) EXPR$1) NOT NULL"); + tester.checkType( + "SELECT *,(SELECT * FROM (VALUES('this is a char'))) " + + "FROM (VALUES('this is a char too'))", + "RecordType(CHAR(18) NOT NULL EXPR$0, CHAR(14) EXPR$1) NOT NULL"); + tester.checkType( + "SELECT *,(SELECT * FROM (VALUES(true))) FROM (values(false))", + "RecordType(BOOLEAN NOT NULL EXPR$0, BOOLEAN EXPR$1) NOT NULL"); + tester.checkType( + " SELECT *,(SELECT * FROM (VALUES(cast('abcd' as varchar(10))))) " + + "FROM (VALUES(CAST('abcd' as varchar(10))))", + "RecordType(VARCHAR(10) NOT NULL EXPR$0, VARCHAR(10) EXPR$1) NOT NULL"); + tester.checkType( + "SELECT *," + + " (SELECT * FROM (VALUES(TIMESTAMP '2006-01-01 12:00:05'))) " + + "FROM (VALUES(TIMESTAMP '2006-01-01 12:00:05'))", + "RecordType(TIMESTAMP(0) NOT NULL EXPR$0, TIMESTAMP(0) EXPR$1) NOT NULL"); + } + } + + @Test public void testLiteralChain() { + tester.setFor(SqlStdOperatorTable.LITERAL_CHAIN, VM_EXPAND); + tester.checkString( + "'buttered'\n' toast'", + "buttered toast", + "CHAR(14) NOT NULL"); + tester.checkString( + "'corned'\n' beef'\n' on'\n' rye'", + "corned beef on rye", + "CHAR(18) NOT NULL"); + tester.checkString( + "_latin1'Spaghetti'\n' all''Amatriciana'", + "Spaghetti all'Amatriciana", + "CHAR(25) NOT NULL"); + tester.checkBoolean("x'1234'\n'abcd' = x'1234abcd'", Boolean.TRUE); + tester.checkBoolean("x'1234'\n'' = x'1234'", Boolean.TRUE); + tester.checkBoolean("x''\n'ab' = x'ab'", Boolean.TRUE); + } + + @Test public void testRow() { + tester.setFor(SqlStdOperatorTable.ROW, VM_FENNEL); + } + + @Test public void testAndOperator() { + tester.setFor(SqlStdOperatorTable.AND); + tester.checkBoolean("true and false", Boolean.FALSE); + tester.checkBoolean("true and true", Boolean.TRUE); + tester.checkBoolean( + "cast(null as boolean) and false", + Boolean.FALSE); + tester.checkBoolean( + "false and cast(null as boolean)", + Boolean.FALSE); + tester.checkNull("cast(null as boolean) and true"); + tester.checkBoolean("true and (not false)", Boolean.TRUE); + } + + @Test public void testAndOperator2() { + if (!TODO_CASE_RELATED_TEST_FINISHED) return; + tester.checkBoolean( + "case when false then unknown else true end and true", + Boolean.TRUE); + tester.checkBoolean( + "case when false then cast(null as boolean) else true end and true", + Boolean.TRUE); + tester.checkBoolean( + "case when false then null else true end and true", + Boolean.TRUE); + } + + @Test public void testAndOperatorLazy() { + tester.setFor(SqlStdOperatorTable.AND); + + // lazy eval returns FALSE; + // eager eval executes RHS of AND and throws; + // both are valid + tester.check( + "values 1 > 2 and sqrt(-4) = -2", + SqlTests.BOOLEAN_TYPE_CHECKER, + new ValueOrExceptionResultChecker( + Boolean.FALSE, INVALID_ARG_FOR_POWER, CODE_2201F)); + } + + @Test public void testConcatOperator() { + if (!ENABLE_OPERATOR_FOR_STR) return; + tester.setFor(SqlStdOperatorTable.CONCAT); + tester.checkString(" 'a'||'b' ", "ab", "CHAR(2) NOT NULL"); + tester.checkNull(" 'a' || cast(null as char(2)) "); + tester.checkNull(" cast(null as char(2)) || 'b' "); + tester.checkNull( + " cast(null as char(1)) || cast(null as char(2)) "); + + tester.checkString( + " x'fe'||x'df' ", + "fedf", + "BINARY(2) NOT NULL"); + tester.checkNull("x'ff' || cast(null as varbinary)"); + tester.checkNull(" cast(null as ANY) || cast(null as ANY) "); + } + + @Test public void testDivideOperator() { + tester.setFor(SqlStdOperatorTable.DIVIDE); + tester.checkScalarExact("10 / 5", "2"); + tester.checkScalarExact("-10 / 5", "-2"); + tester.checkScalarExact("1 / 3", "0"); + tester.checkScalarApprox( + " cast(10.0 as double) / 5", + "DOUBLE NOT NULL", + 2.0, + 0); + if (ENABLE_TYPE_REAL_TEST) tester.checkScalarApprox( + " cast(10.0 as real) / 5", + "REAL NOT NULL", + 2.0, + 0); + if (ENABLE_TYPE_REAL_TEST) tester.checkScalarApprox( + " 6.0 / cast(10.0 as real) ", + "DOUBLE NOT NULL", + 0.6, + 0); + tester.checkScalarExact( + "10.0 / 5.0", + "DECIMAL(9, 6) NOT NULL", + "2"); + if (DECIMAL) { + tester.checkScalarExact( + "1.0 / 3.0", + "DECIMAL(8, 6) NOT NULL", + "0.333333"); + tester.checkScalarExact( + "100.1 / 0.0001", + "DECIMAL(14, 7) NOT NULL", + "1001000.0000000"); + tester.checkScalarExact( + "100.1 / 0.00000001", + "DECIMAL(19, 8) NOT NULL", + "10010000000.00000000"); + } + tester.checkNull("1e1 / cast(null as float)"); + + tester.checkFails( + "100.1 / 0.00000000000000001", OUT_OF_RANGE_MESSAGE, + true); + } + + @Test public void testDivideOperatorIntervals() { + if (!ENABLE_INTERVAL_TEST) return; + tester.checkScalar( + "interval '-2:2' hour to minute / 3", + "-0:41", + "INTERVAL HOUR TO MINUTE NOT NULL"); + tester.checkScalar( + "interval '2:5:12' hour to second / 2 / -3", + "-0:20:52.000000", + "INTERVAL HOUR TO SECOND NOT NULL"); + tester.checkNull( + "interval '2' day / cast(null as bigint)"); + tester.checkNull( + "cast(null as interval month) / 2"); + if (!INTERVAL) { + return; + } + tester.checkScalar( + "interval '3-3' year to month / 15e-1", + "+02-02", + "INTERVAL YEAR TO MONTH NOT NULL"); + tester.checkScalar( + "interval '3-4' year to month / 4.5", + "+00-08", + "INTERVAL YEAR TO MONTH NOT NULL"); + } + + @Test public void testEqualsOperator() { + tester.setFor(SqlStdOperatorTable.EQUALS); + tester.checkBoolean("1=1", Boolean.TRUE); + tester.checkBoolean("1=1.0", Boolean.TRUE); + tester.checkBoolean("1.34=1.34", Boolean.TRUE); + tester.checkBoolean("1=1.34", Boolean.FALSE); + tester.checkBoolean("1e2=100e0", Boolean.TRUE); + tester.checkBoolean("1e2=101", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) + tester.checkBoolean( + "cast(1e2 as real)=cast(101 as bigint)", + Boolean.FALSE); + tester.checkBoolean("'a'='b'", Boolean.FALSE); + tester.checkBoolean("true = true", Boolean.TRUE); + tester.checkBoolean("true = false", Boolean.FALSE); + tester.checkBoolean("false = true", Boolean.FALSE); + tester.checkBoolean("false = false", Boolean.TRUE); + tester.checkBoolean( + "cast('a' as varchar(30))=cast('a' as varchar(30))", + Boolean.TRUE); + if (ENABLE_CAST_STRING_TO_VARSTRING_IGNORE_SPACE_TEST) + tester.checkBoolean("cast('a ' as varchar(30))=cast('a' as varchar(30))", Boolean.TRUE); + tester.checkBoolean("cast('a' as varchar(30))=cast('b' as varchar(30))", Boolean.FALSE); + tester.checkBoolean("cast('a' as varchar(30))=cast('a' as varchar(15))", Boolean.TRUE); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as boolean)=cast(null as boolean)"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as integer)=1"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as varchar(10))='a'"); + } + + @Test public void testEqualsOperatorInterval() { + if (!ENABLE_INTERVAL_TEST) return; + tester.checkBoolean( + "interval '2' day = interval '1' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day = interval '2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2:2:2' hour to second = interval '2' hour", + Boolean.FALSE); + tester.checkNull( + "cast(null as interval hour) = interval '2' minute"); + } + + @Test public void testGreaterThanOperator() { + tester.setFor(SqlStdOperatorTable.GREATER_THAN); + tester.checkBoolean("1>2", Boolean.FALSE); + tester.checkBoolean( + "cast(-1 as TINYINT)>cast(1 as TINYINT)", + Boolean.FALSE); + tester.checkBoolean( + "cast(1 as SMALLINT)>cast(1 as SMALLINT)", + Boolean.FALSE); + tester.checkBoolean("2>1", Boolean.TRUE); + tester.checkBoolean("1.1>1.2", Boolean.FALSE); + tester.checkBoolean("-1.1>-1.2", Boolean.TRUE); + tester.checkBoolean("1.1>1.1", Boolean.FALSE); + tester.checkBoolean("1.2>1", Boolean.TRUE); + tester.checkBoolean("1.1e1>1.2e1", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean( + "cast(-1.1 as real) > cast(-1.2 as real)", + Boolean.TRUE); + tester.checkBoolean("1.1e2>1.1e2", Boolean.FALSE); + if (TODO_COMPARE_BETWEEN_REAL_INTEGER_FINISHED) tester.checkBoolean("1.2e0>1", Boolean.TRUE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("cast(1.2e0 as real)>1", Boolean.TRUE); + tester.checkBoolean("true>false", Boolean.TRUE); + tester.checkBoolean("true>true", Boolean.FALSE); + tester.checkBoolean("false>false", Boolean.FALSE); + tester.checkBoolean("false>true", Boolean.FALSE); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("3.0>cast(null as double)"); + + if (ENABLE_CAST_STRING_TO_TIME_TEST) { + tester.checkBoolean( + "DATE '2013-02-23' > DATE '1945-02-24'", Boolean.TRUE); + tester.checkBoolean( + "DATE '2013-02-23' > CAST(NULL AS DATE)", null); + } + tester.checkBoolean("x'0A000130'>x'0A0001B0'", Boolean.FALSE); + } + + @Test public void testGreaterThanOperatorIntervals() { + if (!ENABLE_INTERVAL_TEST) return; + tester.checkBoolean( + "interval '2' day > interval '1' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day > interval '5' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2 2:2:2' day to second > interval '2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day > interval '2' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day > interval '-2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day > interval '2' hour", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' minute > interval '2' hour", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' second > interval '2' minute", + Boolean.FALSE); + tester.checkNull( + "cast(null as interval hour) > interval '2' minute"); + tester.checkNull( + "interval '2:2' hour to minute > cast(null as interval second)"); + } + + @Test public void testIsDistinctFromOperator() { + if (!ENABLE_IS_DISTINCT_FROM_TEST) return; + tester.setFor( + SqlStdOperatorTable.IS_DISTINCT_FROM, + VM_EXPAND); + tester.checkBoolean("1 is distinct from 1", Boolean.FALSE); + tester.checkBoolean("1 is distinct from 1.0", Boolean.FALSE); + tester.checkBoolean("1 is distinct from 2", Boolean.TRUE); + tester.checkBoolean( + "cast(null as integer) is distinct from 2", + Boolean.TRUE); + tester.checkBoolean( + "cast(null as integer) is distinct from cast(null as integer)", + Boolean.FALSE); + tester.checkBoolean("1.23 is distinct from 1.23", Boolean.FALSE); + tester.checkBoolean("1.23 is distinct from 5.23", Boolean.TRUE); + tester.checkBoolean( + "-23e0 is distinct from -2.3e1", + Boolean.FALSE); + + // IS DISTINCT FROM not implemented for ROW yet + if (false) { + tester.checkBoolean( + "row(1,1) is distinct from row(1,1)", + true); + tester.checkBoolean( + "row(1,1) is distinct from row(1,2)", + false); + } + + // Intervals + tester.checkBoolean( + "interval '2' day is distinct from interval '1' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '10' hour is distinct from interval '10' hour", + Boolean.FALSE); + } + + @Test public void testIsNotDistinctFromOperator() { + if (!ENABLE_IS_DISTINCT_FROM_TEST) return; + tester.setFor( + SqlStdOperatorTable.IS_NOT_DISTINCT_FROM, + VM_EXPAND); + tester.checkBoolean("1 is not distinct from 1", Boolean.TRUE); + tester.checkBoolean("1 is not distinct from 1.0", Boolean.TRUE); + tester.checkBoolean("1 is not distinct from 2", Boolean.FALSE); + tester.checkBoolean( + "cast(null as integer) is not distinct from 2", + Boolean.FALSE); + tester.checkBoolean( + "cast(null as integer) is not distinct from cast(null as integer)", + Boolean.TRUE); + tester.checkBoolean( + "1.23 is not distinct from 1.23", + Boolean.TRUE); + tester.checkBoolean( + "1.23 is not distinct from 5.23", + Boolean.FALSE); + tester.checkBoolean( + "-23e0 is not distinct from -2.3e1", + Boolean.TRUE); + + // IS NOT DISTINCT FROM not implemented for ROW yet + if (false) { + tester.checkBoolean( + "row(1,1) is not distinct from row(1,1)", + false); + tester.checkBoolean( + "row(1,1) is not distinct from row(1,2)", + true); + } + + // Intervals + tester.checkBoolean( + "interval '2' day is not distinct from interval '1' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '10' hour is not distinct from interval '10' hour", + Boolean.TRUE); + } + + @Test public void testGreaterThanOrEqualOperator() { + tester.setFor(SqlStdOperatorTable.GREATER_THAN_OR_EQUAL); + tester.checkBoolean("1>=2", Boolean.FALSE); + tester.checkBoolean("-1>=1", Boolean.FALSE); + tester.checkBoolean("1>=1", Boolean.TRUE); + tester.checkBoolean("2>=1", Boolean.TRUE); + tester.checkBoolean("1.1>=1.2", Boolean.FALSE); + tester.checkBoolean("-1.1>=-1.2", Boolean.TRUE); + tester.checkBoolean("1.1>=1.1", Boolean.TRUE); + tester.checkBoolean("1.2>=1", Boolean.TRUE); + tester.checkBoolean("1.2e4>=1e5", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("1.2e4>=cast(1e5 as real)", Boolean.FALSE); + tester.checkBoolean("1.2>=cast(1e5 as double)", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("120000>=cast(1e5 as real)", Boolean.TRUE); + tester.checkBoolean("true>=false", Boolean.TRUE); + tester.checkBoolean("true>=true", Boolean.TRUE); + tester.checkBoolean("false>=false", Boolean.TRUE); + tester.checkBoolean("false>=true", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkNull("cast(null as real)>=999"); + tester.checkBoolean("x'0A000130'>=x'0A0001B0'", Boolean.FALSE); + tester.checkBoolean("x'0A0001B0'>=x'0A0001B0'", Boolean.TRUE); + } + + @Test public void testGreaterThanOrEqualOperatorIntervals() { + if (!ENABLE_INTERVAL_TEST) return; + tester.checkBoolean( + "interval '2' day >= interval '1' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day >= interval '5' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2 2:2:2' day to second >= interval '2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day >= interval '2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day >= interval '-2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day >= interval '2' hour", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' minute >= interval '2' hour", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' second >= interval '2' minute", + Boolean.FALSE); + tester.checkNull( + "cast(null as interval hour) >= interval '2' minute"); + tester.checkNull( + "interval '2:2' hour to minute >= cast(null as interval second)"); + } + + @Test public void testInOperator() { + tester.setFor(SqlStdOperatorTable.IN, VM_EXPAND); + tester.checkBoolean("1 in (0, 1, 2)", true); + tester.checkBoolean("3 in (0, 1, 2)", false); + if (ENABLE_CAST_NULL_TEST) tester.checkBoolean("cast(null as integer) in (0, 1, 2)", null); + if (ENABLE_CAST_NULL_TEST) tester.checkBoolean( + "cast(null as integer) in (0, cast(null as integer), 2)", + null); + if (ENABLE_CAST_NULL_TEST) + if (Bug.FRG327_FIXED) { + tester.checkBoolean( + "cast(null as integer) in (0, null, 2)", + null); + tester.checkBoolean("1 in (0, null, 2)", null); + } + + if (!enable) { + return; + } + // AND has lower precedence than IN + tester.checkBoolean("false and true in (false, false)", false); + + if (!Bug.TODO_FIXED) { + return; + } + tester.checkFails( + "'foo' in (^)^", + "(?s).*Encountered \"\\)\" at .*", + false); + } + + @Test public void testNotInOperator() { + tester.setFor(SqlStdOperatorTable.NOT_IN, VM_EXPAND); + tester.checkBoolean("1 not in (0, 1, 2)", false); + tester.checkBoolean("3 not in (0, 1, 2)", true); + if (!enable) { + return; + } + tester.checkBoolean( + "cast(null as integer) not in (0, 1, 2)", + null); + tester.checkBoolean( + "cast(null as integer) not in (0, cast(null as integer), 2)", + null); + if (Bug.FRG327_FIXED) { + tester.checkBoolean( + "cast(null as integer) not in (0, null, 2)", + null); + tester.checkBoolean("1 not in (0, null, 2)", null); + } + + // AND has lower precedence than NOT IN + tester.checkBoolean("true and false not in (true, true)", true); + + if (!Bug.TODO_FIXED) { + return; + } + tester.checkFails( + "'foo' not in (^)^", + "(?s).*Encountered \"\\)\" at .*", + false); + } + + @Test public void testOverlapsOperator() { + tester.setFor(SqlStdOperatorTable.OVERLAPS, VM_EXPAND); + if (Bug.FRG187_FIXED) { + tester.checkBoolean( + "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', interval '1' year)", + Boolean.TRUE); + tester.checkBoolean( + "(date '1-2-3', date '1-2-3') overlaps (date '4-5-6', interval '1' year)", + Boolean.FALSE); + tester.checkBoolean( + "(date '1-2-3', date '4-5-6') overlaps (date '2-2-3', date '3-4-5')", + Boolean.TRUE); + tester.checkNull( + "(cast(null as date), date '1-2-3') overlaps (date '1-2-3', interval '1' year)"); + tester.checkNull( + "(date '1-2-3', date '1-2-3') overlaps (date '1-2-3', cast(null as date))"); + + tester.checkBoolean( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', time '1:2:3')", + Boolean.TRUE); + tester.checkBoolean( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', time '1:2:2')", + Boolean.FALSE); + tester.checkBoolean( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', interval '2' hour)", + Boolean.TRUE); + tester.checkNull( + "(time '1:2:3', cast(null as time)) overlaps (time '23:59:59', time '1:2:3')"); + tester.checkNull( + "(time '1:2:3', interval '1' second) overlaps (time '23:59:59', cast(null as interval hour))"); + + tester.checkBoolean( + "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)", + Boolean.TRUE); + tester.checkBoolean( + "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (timestamp '2-2-3 4:5:6', interval '1 2:3:4.5' day to second)", + Boolean.FALSE); + tester.checkNull( + "(timestamp '1-2-3 4:5:6', cast(null as interval day) ) overlaps (timestamp '1-2-3 4:5:6', interval '1 2:3:4.5' day to second)"); + tester.checkNull( + "(timestamp '1-2-3 4:5:6', timestamp '1-2-3 4:5:6' ) overlaps (cast(null as timestamp), interval '1 2:3:4.5' day to second)"); + } + } + + @Test public void testLessThanOperator() { + tester.setFor(SqlStdOperatorTable.LESS_THAN); + tester.checkBoolean("1<2", Boolean.TRUE); + tester.checkBoolean("-1<1", Boolean.TRUE); + tester.checkBoolean("1<1", Boolean.FALSE); + tester.checkBoolean("2<1", Boolean.FALSE); + tester.checkBoolean("1.1<1.2", Boolean.TRUE); + tester.checkBoolean("-1.1<-1.2", Boolean.FALSE); + tester.checkBoolean("1.1<1.1", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("cast(1.1 as real)<1", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("cast(1.1 as real)<1.1", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean( + "cast(1.1 as real)<cast(1.2 as real)", + Boolean.TRUE); + tester.checkBoolean("-1.1e-1<-1.2e-1", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean( + "cast(1.1 as real)<cast(1.1 as double)", + Boolean.FALSE); + tester.checkBoolean("true<false", Boolean.FALSE); + tester.checkBoolean("true<true", Boolean.FALSE); + tester.checkBoolean("false<false", Boolean.FALSE); + tester.checkBoolean("false<true", Boolean.TRUE); + if (ENABLE_CAST_NULL_TEST) { + tester.checkNull("123<cast(null as bigint)"); + tester.checkNull("cast(null as tinyint)<123"); + tester.checkNull("cast(null as integer)<1.32"); + } + tester.checkBoolean("x'0A000130'<x'0A0001B0'", Boolean.TRUE); + } + + @Test public void testLessThanOperatorInterval() { + if (!DECIMAL) { + return; + } + tester.checkBoolean( + "interval '2' day < interval '1' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day < interval '5' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2 2:2:2' day to second < interval '2' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day < interval '2' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day < interval '-2' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day < interval '2' hour", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' minute < interval '2' hour", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' second < interval '2' minute", + Boolean.TRUE); + tester.checkNull( + "cast(null as interval hour) < interval '2' minute"); + tester.checkNull( + "interval '2:2' hour to minute < cast(null as interval second)"); + } + + @Test public void testLessThanOrEqualOperator() { + tester.setFor(SqlStdOperatorTable.LESS_THAN_OR_EQUAL); + tester.checkBoolean("1<=2", Boolean.TRUE); + tester.checkBoolean("1<=1", Boolean.TRUE); + tester.checkBoolean("-1<=1", Boolean.TRUE); + tester.checkBoolean("2<=1", Boolean.FALSE); + tester.checkBoolean("1.1<=1.2", Boolean.TRUE); + tester.checkBoolean("-1.1<=-1.2", Boolean.FALSE); + tester.checkBoolean("1.1<=1.1", Boolean.TRUE); + tester.checkBoolean("1.2<=1", Boolean.FALSE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("1<=cast(1e2 as real)", Boolean.TRUE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("1000<=cast(1e2 as real)", Boolean.FALSE); + tester.checkBoolean("1.2e1<=1e2", Boolean.TRUE); + if (ENABLE_TYPE_REAL_TEST) tester.checkBoolean("1.2e1<=cast(1e2 as real)", Boolean.TRUE); + tester.checkBoolean("true<=false", Boolean.FALSE); + tester.checkBoolean("true<=true", Boolean.TRUE); + tester.checkBoolean("false<=false", Boolean.TRUE); + tester.checkBoolean("false<=true", Boolean.TRUE); + if (ENABLE_TYPE_REAL_TEST && ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as real)<=cast(1 as real)"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as integer)<=3"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("3<=cast(null as smallint)"); + if (ENABLE_CAST_NULL_TEST) tester.checkNull("cast(null as integer)<=1.32"); + tester.checkBoolean("x'0A000130'<=x'0A0001B0'", Boolean.TRUE); + tester.checkBoolean("x'0A0001B0'<=x'0A0001B0'", Boolean.TRUE); + } + + @Test public void testLessThanOrEqualOperatorInterval() { + if (!ENABLE_INTERVAL_TEST) return; + tester.checkBoolean( + "interval '2' day <= interval '1' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day <= interval '5' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2 2:2:2' day to second <= interval '2' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day <= interval '2' day", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' day <= interval '-2' day", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' day <= interval '2' hour", + Boolean.FALSE); + tester.checkBoolean( + "interval '2' minute <= interval '2' hour", + Boolean.TRUE); + tester.checkBoolean( + "interval '2' second <= interval '2' minute", + Boolean.TRUE); + tester.checkNull( + "cast(null as interval hour) <= interval '2' minute"); + tester.checkNull( + "interval '2:2' hour to minute <= cast(null as interval second)"); + } + + @Test public void testMinusOperator() { + tester.setFor(SqlStdOperatorTable.MINUS); + tester.checkScalarExact("-2-1", "-3"); + tester.checkScalarExact("-2-1-5", "-8"); + tester.checkScalarExact("2-1", "1"); + tester.checkScalarApprox( + "cast(2.0 as double) -1", + "DOUBLE NOT NULL", + 1, + 0); + if (ENABLE_TYPE_REAL_TEST) tester.checkScalarApprox( + "cast(1 as smallint)-cast(2.0 as real)", + "REAL NOT NULL", + -1, + 0); + if (ENABLE_TYPE_REAL_TEST) tester.checkScalarApprox( + "2.4-cast(2.0 as real)", + "DOUBLE NOT NULL", + 0.4, + 0.00000001); + tester.checkScalarExact("1-2", "-1"); + if (BUG_CHECKER_EXACT_REAL_FIXED) + tester.checkScalarExact( + "10.0 - 5.0", + "DECIMAL(4, 1) NOT NULL", + "5.0"); + tester.checkScalarExact( + "19.68 - 4.2", + "DECIMAL(5, 2) NOT NULL", + "15.48"); + tester.checkNull("1e1-cast(null as double)"); + tester.checkNull("cast(null as tinyint) - cast(null as smallint)"); + + // TODO: Fix bug + if (Bug.FNL25_FIXED) { + // Should throw out of range error + tester.checkFails( + "cast(100 as tinyint) - cast(-100 as tinyint)", OUT_OF_RANGE_MESSAGE, + true); + tester.checkFails( + "cast(-20000 as smallint) - cast(20000 as smallint)", + OUT_OF_RANGE_MESSAGE, + true); + tester.checkFails( + "cast(1.5e9 as integer) - cast(-1.5e9 as integer)", + OUT_OF_RANGE_MESSAGE, + true); + tester.checkFails( + "cast(-5e18 as bigint) - cast(5e18 as bigint)", OUT_OF_RANGE_MESSAGE, + true); + tester.checkFails( + "cast(5e18 as decimal(19,0)) - cast(-5e18 as decimal(19,0))", + OUT_OF_RANGE_MESSAGE, + true); + tester.checkFails( + "cast(-5e8 as decimal(19,10)) - cast(5e8 as decimal(19,10))", + OUT_OF_RANGE_MESSAGE, + true); + } + } + + @Test public void testMinusIntervalOperator() { + if (!ENABLE_INTERVAL_TEST) return; + tester.setFor(SqlStdOperatorTable.MINUS); + tester.checkScalar( + "interval '2' day - interval '1' day", + "+1", + "INTERVAL DAY NOT NULL"); + tester.checkScalar( + "interval '2' day - interval '1' minute", + "+1 23:59", + "INTERVAL DAY TO MINUTE NOT NULL"); + tester.checkScalar( + "interval '2' year - interval '1' month", + "+1-11", + "INTERVAL YEAR TO MONTH NOT NULL"); + tester.checkScalar( + "interval '2' year - interval '1' month - interval '3' year", + "-1-01", + "INTERVAL YEAR TO MONTH NOT NULL"); + tester.checkNull( + "cast(null as interval day) + interval '2' hour"); + + // Datetime minus interval + tester.checkScalar( + "time '12:03:01' - interval '1:1' hour to minute", + "11:02:01", + "TIME(0) NOT NULL"); + if (!INTERVAL) { + return; + } + tester.checkScalar( + "date '2005-03-02' - interval '5' day", + "2005-02-25", + "DATE NOT NULL"); + tester.checkScalar( + "timestamp '2003-08-02 12:54:01' - interval '-4 2:4' day to minute", + "2003-08-06 14:58:01", + "TIMESTAMP(0) NOT NULL"); + + // TODO: Tests with interval year months (not supported) + } + + @Test public void testMinusDateOperator() { + tester.setFor(SqlStdOperatorTable.MINUS_DATE); + if (!enable) { + return; + } + tester.checkScalar( + "(time '12:03:34' - time '11:57:23') minute to second", + "+6:11", + "INTERVAL MINUTE TO SECOND NOT NULL"); + tester.checkScalar( + "(time '12:03:23' - time '11:57:23') minute", + "+6", + "INTERVAL MINUTE NOT NULL"); + tester.checkScalar( + "(time '12:03:34' - time '11:57:23') minute", + "+6", + "INTERVAL MINUTE NOT NULL"); + tester.checkScalar( + "(timestamp '2004-05-01 12:03:34' - timestamp '2004-04-29 11:57:23') day to second", + "+2 00:06:11", + "INTERVAL DAY TO SECOND NOT NULL"); + tester.checkScalar( + "(timestamp '2004-05-01 12:03:34' - timestamp '2004-04-29 11:57:23') day to hour", + "+2 00", + "INTERVAL DAY TO HOUR NOT NULL"); + tester.checkScalar( + "(date '2004-12-02' - date '2003-12-01') day", + "+367", + "INTERVAL DAY NOT NULL"); + tester.checkNull( + "(cast(null as date) - date '2003-12-01') day"); + + // combine '<datetime> + <interval>' with '<datetime> - <datetime>' + tester.checkScalar( + "timestamp '1969-04-29 0:0:0' +" + + " (timestamp '2008-07-15 15:28:00' - " + + " timestamp '1969-04-29 0:0:0') day to second / 2", + "1988-12-06 07:44:00", + "TIMESTAMP(0) NOT NULL"); + + <TRUNCATED>