You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ma...@apache.org on 2015/08/18 20:24:33 UTC
[1/2] phoenix git commit: PHOENIX-2163 Measure performance of
Phoenix/Calcite querying
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>
[2/2] phoenix git commit: PHOENIX-2163 Measure performance of
Phoenix/Calcite querying
Posted by ma...@apache.org.
PHOENIX-2163 Measure performance of Phoenix/Calcite querying
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/f9c66168
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/f9c66168
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/f9c66168
Branch: refs/heads/calcite
Commit: f9c66168084bad17fc0741102849663a05d35fbe
Parents: 0773957
Author: maryannxue <we...@intel.com>
Authored: Tue Aug 18 14:24:40 2015 -0400
Committer: maryannxue <we...@intel.com>
Committed: Tue Aug 18 14:24:40 2015 -0400
----------------------------------------------------------------------
.../apache/phoenix/calcite/CalciteUtils.java | 141 +-
.../calcite/rules/PhoenixConverterRules.java | 1 -
.../calcite/ExpressionFactoryValuesTest.java | 2 +-
.../phoenix/calcite/SqlOperatorBaseTest.java | 5540 ++++++++++++++++++
4 files changed, 5681 insertions(+), 3 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/f9c66168/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteUtils.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteUtils.java b/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteUtils.java
index 62a8d2e..3aa2404 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteUtils.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/calcite/CalciteUtils.java
@@ -37,23 +37,40 @@ import org.apache.phoenix.expression.DoubleMultiplyExpression;
import org.apache.phoenix.expression.DoubleSubtractExpression;
import org.apache.phoenix.expression.Expression;
import org.apache.phoenix.expression.ExpressionType;
+import org.apache.phoenix.expression.IsNullExpression;
import org.apache.phoenix.expression.LiteralExpression;
import org.apache.phoenix.expression.LongAddExpression;
import org.apache.phoenix.expression.LongDivideExpression;
import org.apache.phoenix.expression.LongMultiplyExpression;
import org.apache.phoenix.expression.LongSubtractExpression;
+import org.apache.phoenix.expression.NotExpression;
import org.apache.phoenix.expression.OrExpression;
+import org.apache.phoenix.expression.StringBasedLikeExpression;
import org.apache.phoenix.expression.TimestampAddExpression;
import org.apache.phoenix.expression.TimestampSubtractExpression;
+import org.apache.phoenix.expression.function.AbsFunction;
import org.apache.phoenix.expression.function.AggregateFunction;
+import org.apache.phoenix.expression.function.CeilDateExpression;
+import org.apache.phoenix.expression.function.CeilDecimalExpression;
+import org.apache.phoenix.expression.function.CeilTimestampExpression;
+import org.apache.phoenix.expression.function.CoalesceFunction;
import org.apache.phoenix.expression.function.CountAggregateFunction;
+import org.apache.phoenix.expression.function.CurrentDateFunction;
+import org.apache.phoenix.expression.function.CurrentTimeFunction;
+import org.apache.phoenix.expression.function.ExpFunction;
+import org.apache.phoenix.expression.function.FloorDateExpression;
+import org.apache.phoenix.expression.function.FloorDecimalExpression;
import org.apache.phoenix.expression.function.FunctionExpression;
+import org.apache.phoenix.expression.function.LnFunction;
+import org.apache.phoenix.expression.function.LowerFunction;
import org.apache.phoenix.expression.function.MaxAggregateFunction;
import org.apache.phoenix.expression.function.MinAggregateFunction;
import org.apache.phoenix.expression.function.PowerFunction;
import org.apache.phoenix.expression.function.RoundDecimalExpression;
import org.apache.phoenix.expression.function.RoundTimestampExpression;
import org.apache.phoenix.expression.function.SqrtFunction;
+import org.apache.phoenix.expression.function.TrimFunction;
+import org.apache.phoenix.expression.function.UpperFunction;
import org.apache.phoenix.schema.SortOrder;
import org.apache.phoenix.schema.TypeMismatchException;
import org.apache.phoenix.schema.types.PDataType;
@@ -510,7 +527,6 @@ public class CalciteUtils {
throw new RuntimeException(e);
}
}
-
});
EXPRESSION_MAP.put(SqlKind.OTHER_FUNCTION, new ExpressionFactory() {
@Override
@@ -524,6 +540,22 @@ public class CalciteUtils {
return new SqrtFunction(children);
} else if (op == SqlStdOperatorTable.POWER) {
return new PowerFunction(children);
+ } else if (op == SqlStdOperatorTable.LN) {
+ return new LnFunction(children);
+ } else if (op == SqlStdOperatorTable.EXP) {
+ return new ExpFunction(children);
+ } else if (op == SqlStdOperatorTable.ABS) {
+ return new AbsFunction(children);
+ } else if (op == SqlStdOperatorTable.CURRENT_DATE) {
+ return new CurrentDateFunction();
+ } else if (op == SqlStdOperatorTable.CURRENT_TIME) {
+ return new CurrentTimeFunction();
+ } else if (op == SqlStdOperatorTable.LOWER) {
+ return new LowerFunction(children);
+ } else if (op == SqlStdOperatorTable.UPPER) {
+ return new UpperFunction(children);
+ } else if (op == SqlStdOperatorTable.COALESCE) {
+ return new CoalesceFunction(children);
}
} catch (SQLException e) {
throw new RuntimeException(e);
@@ -532,7 +564,114 @@ public class CalciteUtils {
throw new UnsupportedOperationException(
"Unsupported SqlFunction: " + op.getName());
}
+ });
+ EXPRESSION_MAP.put(SqlKind.NOT, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ return new NotExpression(convertChildren((RexCall) node, implementor));
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.IS_TRUE, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ List<Expression> children = convertChildren((RexCall) node, implementor);
+ return children.get(0);
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.IS_NOT_TRUE, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ return new NotExpression(convertChildren((RexCall) node, implementor));
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.IS_FALSE, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ return new NotExpression(convertChildren((RexCall) node, implementor));
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.IS_NOT_FALSE, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ List<Expression> children = convertChildren((RexCall) node, implementor);
+ return children.get(0);
+ }
+ });
+ //TODO different kind of LikeExpression based on configuration
+ EXPRESSION_MAP.put(SqlKind.LIKE, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ List<Expression> children = convertChildren((RexCall) node, implementor);
+ return new StringBasedLikeExpression(children);
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.IS_NULL, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ return new IsNullExpression(convertChildren((RexCall) node, implementor), false);
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.IS_NOT_NULL, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ return new IsNullExpression(convertChildren((RexCall) node, implementor), true);
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.TRIM, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ //TODO Phoenix only support separate arguments.
+ try {
+ return new TrimFunction(convertChildren((RexCall) node, implementor));
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.CEIL, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ //TODO Phoenix only support separate arguments.
+ List<Expression> children = convertChildren((RexCall) node, implementor);
+ final Expression firstChild = children.get(0);
+ final PDataType firstChildDataType = firstChild.getDataType();
+ try {
+ if (firstChildDataType.isCoercibleTo(PDate.INSTANCE)) {
+ return CeilDateExpression.create(children);
+ } else if (firstChildDataType == PTimestamp.INSTANCE
+ || firstChildDataType == PUnsignedTimestamp.INSTANCE) {
+ return CeilTimestampExpression.create(children);
+ } else if (firstChildDataType.isCoercibleTo(PDecimal.INSTANCE)) {
+ return CeilDecimalExpression.create(children);
+ } else {
+ throw TypeMismatchException.newException(firstChildDataType, "1");
+ }
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ });
+ EXPRESSION_MAP.put(SqlKind.FLOOR, new ExpressionFactory() {
+ @Override
+ public Expression newExpression(RexNode node, Implementor implementor) {
+ // TODO Phoenix only support separate arguments.
+ List<Expression> children = convertChildren((RexCall) node, implementor);
+ final Expression firstChild = children.get(0);
+ final PDataType firstChildDataType = firstChild.getDataType();
+ try {
+ if (firstChildDataType.isCoercibleTo(PTimestamp.INSTANCE)) {
+ return FloorDateExpression.create(children);
+ } else if (firstChildDataType.isCoercibleTo(PDecimal.INSTANCE)) {
+ return FloorDecimalExpression.create(children);
+ } else {
+ throw TypeMismatchException.newException(firstChildDataType, "1");
+ }
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
});
+ // TODO: SqlKind.CASE
}
private static final Map<String, FunctionFactory> FUNCTION_MAP = Maps
http://git-wip-us.apache.org/repos/asf/phoenix/blob/f9c66168/phoenix-core/src/main/java/org/apache/phoenix/calcite/rules/PhoenixConverterRules.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/calcite/rules/PhoenixConverterRules.java b/phoenix-core/src/main/java/org/apache/phoenix/calcite/rules/PhoenixConverterRules.java
index 6065d35..072e76c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/calcite/rules/PhoenixConverterRules.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/calcite/rules/PhoenixConverterRules.java
@@ -80,7 +80,6 @@ public class PhoenixConverterRules {
PhoenixClientAggregateRule.INSTANCE,
PhoenixServerAggregateRule.SERVER,
PhoenixServerAggregateRule.SERVERJOIN,
- PhoenixUnionRule.INSTANCE,
PhoenixClientJoinRule.INSTANCE,
PhoenixServerJoinRule.INSTANCE,
PhoenixValuesRule.INSTANCE,
http://git-wip-us.apache.org/repos/asf/phoenix/blob/f9c66168/phoenix-core/src/test/java/org/apache/phoenix/calcite/ExpressionFactoryValuesTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/calcite/ExpressionFactoryValuesTest.java b/phoenix-core/src/test/java/org/apache/phoenix/calcite/ExpressionFactoryValuesTest.java
index 66b2528..e528db4 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/calcite/ExpressionFactoryValuesTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/calcite/ExpressionFactoryValuesTest.java
@@ -2,10 +2,10 @@ package org.apache.phoenix.calcite;
import java.sql.Connection;
import java.sql.DriverManager;
+
import org.apache.calcite.jdbc.CalcitePrepare;
import org.apache.calcite.jdbc.Driver;
import org.apache.calcite.linq4j.function.Function0;
-import org.apache.calcite.sql.test.SqlOperatorBaseTest;
import org.apache.phoenix.calcite.jdbc.PhoenixPrepareImpl;
import org.apache.phoenix.calcite.rules.PhoenixConverterRules;