private static SqlTimestamp sqlTimestampOf(DateTime dateTime, ConnectorSession session) { return sqlTimestampOf(dateTime.getMillis(), session); }
private static SqlTimestamp intToSqlTimestamp(Integer input) { if (input == null) { return null; } return sqlTimestampOf(input, SESSION); }
public static SqlTimestamp sqlTimestampOf(DateTime dateTime, Session session) { return sqlTimestampOf(dateTime, session.toConnectorSession()); }
@Test public void testGreatest() { assertFunction("greatest(TIMESTAMP '2013-03-30 01:05', TIMESTAMP '2012-03-30 01:05')", TIMESTAMP, sqlTimestampOf(2013, 3, 30, 1, 5, 0, 0, session)); assertFunction("greatest(TIMESTAMP '2013-03-30 01:05', TIMESTAMP '2012-03-30 01:05', TIMESTAMP '2012-05-01 01:05')", TIMESTAMP, sqlTimestampOf(2013, 3, 30, 1, 5, 0, 0, session)); }
@Test @Override public void testCastToTimestamp() { assertFunction("cast(TIME '03:04:05.321 +07:09' as timestamp)", TIMESTAMP, sqlTimestampOf(1970, 1, 1, 2 /* not 3 */, 4, 5, 321, session)); } }
@Test public void testCastToTimestamp() { assertFunction("cast(TIME '03:04:05.321' as timestamp)", TIMESTAMP, sqlTimestampOf(1970, 1, 1, 3, 4, 5, 321, session)); }
@Test public void testLeast() { assertFunction("least(TIMESTAMP '2013-03-30 01:05', TIMESTAMP '2012-03-30 01:05')", TIMESTAMP, sqlTimestampOf(2012, 3, 30, 1, 5, 0, 0, session)); assertFunction("least(TIMESTAMP '2013-03-30 01:05', TIMESTAMP '2012-03-30 01:05', TIMESTAMP '2012-05-01 01:05')", TIMESTAMP, sqlTimestampOf(2012, 3, 30, 1, 5, 0, 0, session)); }
@Test public void testFromUnixTime() { DateTime dateTime = new DateTime(2001, 1, 22, 3, 4, 5, 0, DATE_TIME_ZONE); double seconds = dateTime.getMillis() / 1000.0; assertFunction("from_unixtime(" + seconds + ")", TimestampType.TIMESTAMP, sqlTimestampOf(dateTime, session)); dateTime = new DateTime(2001, 1, 22, 3, 4, 5, 888, DATE_TIME_ZONE); seconds = dateTime.getMillis() / 1000.0; assertFunction("from_unixtime(" + seconds + ")", TimestampType.TIMESTAMP, sqlTimestampOf(dateTime, session)); }
@Test public void testCastToTimestamp() { assertFunction("cast(DATE '2001-1-22' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 0, 0, 0, 0, session)); }
@Test @Override public void testCastToTimestamp() { assertFunction("cast(TIMESTAMP '2001-1-22 03:04:05.321 +07:09' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 2 /* not 3 */, 4, 5, 321, session)); // This TZ had switch in 2014 assertFunction("cast(TIMESTAMP '2001-1-22 03:04:05.321 Pacific/Bougainville' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 21 /* not 22 */, 23 /* not 3 */, 13, 5, 321, session)); } }
@Test @Override public void testCastToTimestamp() { assertFunction("cast(TIME '03:04:05.321 +07:09' as timestamp)", TIMESTAMP, sqlTimestampOf(1970, 1, 1, 3, 4, 5, 321, session)); } }
@Test @Override public void testCastToTimestamp() { assertFunction("cast(TIMESTAMP '2001-1-22 03:04:05.321 +07:09' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); // This TZ had switch in 2014, so if we test for 2014 and used unpacked value we would use wrong shift assertFunction("cast(TIMESTAMP '2001-1-22 03:04:05.321 Pacific/Bougainville' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); } }
@Test public void testLiteral() { assertFunction("TIMESTAMP '2013-03-30 01:05'", TIMESTAMP, sqlTimestampOf(2013, 3, 30, 1, 5, 0, 0, session)); assertFunction("TIMESTAMP '2013-03-30 02:05'", TIMESTAMP, sqlTimestampOf(2013, 3, 30, 2, 5, 0, 0, session)); assertFunction("TIMESTAMP '2013-03-30 03:05'", TIMESTAMP, sqlTimestampOf(2013, 3, 30, 3, 5, 0, 0, session)); assertFunction("TIMESTAMP '2001-01-22 03:04:05.321'", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); assertFunction("TIMESTAMP '2001-01-22 03:04:05'", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 0, session)); assertFunction("TIMESTAMP '2001-01-22 03:04'", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 0, 0, session)); assertFunction("TIMESTAMP '2001-01-22'", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 0, 0, 0, 0, session)); assertFunction("TIMESTAMP '2001-1-2 3:4:5.321'", TIMESTAMP, sqlTimestampOf(2001, 1, 2, 3, 4, 5, 321, session)); assertFunction("TIMESTAMP '2001-1-2 3:4:5'", TIMESTAMP, sqlTimestampOf(2001, 1, 2, 3, 4, 5, 0, session)); assertFunction("TIMESTAMP '2001-1-2 3:4'", TIMESTAMP, sqlTimestampOf(2001, 1, 2, 3, 4, 0, 0, session)); assertFunction("TIMESTAMP '2001-1-2'", TIMESTAMP, sqlTimestampOf(2001, 1, 2, 0, 0, 0, 0, session)); assertInvalidFunction("TIMESTAMP 'text'", SemanticErrorCode.INVALID_LITERAL, "line 1:1: 'text' is not a valid timestamp literal"); }
@Test public void testCastFromTimestamp() { assertFunction("cast(cast (null as timestamp) as JSON)", JSON, null); assertFunction("CAST(TIMESTAMP '1970-01-01 00:00:01' AS JSON)", JSON, format("\"%s\"", sqlTimestampOf(1970, 1, 1, 0, 0, 1, 0, TEST_SESSION))); }
@Test public void testCastFromSlice() { assertFunction("cast('2001-1-22 03:04:05.321' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); assertFunction("cast('2001-1-22 03:04:05' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 0, session)); assertFunction("cast('2001-1-22 03:04' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 0, 0, session)); assertFunction("cast('2001-1-22' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 0, 0, 0, 0, session)); assertFunction("cast('\n\t 2001-1-22 03:04:05.321' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); assertFunction("cast('2001-1-22 03:04:05.321 \t\n' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); assertFunction("cast('\n\t 2001-1-22 03:04:05.321 \t\n' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 1, 22, 3, 4, 5, 321, session)); }
@Test public void testTimestampMinusInterval() { assertFunction("TIMESTAMP '2001-1-22 03:04:05.321' - INTERVAL '3' day", TIMESTAMP, sqlTimestampOf(2001, 1, 19, 3, 4, 5, 321, session)); assertFunction("TIMESTAMP '2001-1-22 03:04:05.321 +05:09' - INTERVAL '3' day", TIMESTAMP_WITH_TIME_ZONE, new SqlTimestampWithTimeZone(new DateTime(2001, 1, 19, 3, 4, 5, 321, WEIRD_TIME_ZONE).getMillis(), WEIRD_TIME_ZONE_KEY)); assertFunction("TIMESTAMP '2001-1-22 03:04:05.321' - INTERVAL '3' month", TIMESTAMP, sqlTimestampOf(2000, 10, 22, 3, 4, 5, 321, session)); assertFunction("TIMESTAMP '2001-1-22 03:04:05.321 +05:09' - INTERVAL '3' month", TIMESTAMP_WITH_TIME_ZONE, new SqlTimestampWithTimeZone(new DateTime(2000, 10, 22, 3, 4, 5, 321, WEIRD_TIME_ZONE).getMillis(), WEIRD_TIME_ZONE_KEY)); }
@Test public void testAddFieldToTimestamp() { assertFunction("date_add('millisecond', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusMillis(3), session)); assertFunction("date_add('second', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusSeconds(3), session)); assertFunction("date_add('minute', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusMinutes(3), session)); assertFunction("date_add('hour', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusHours(3), session)); assertFunction("date_add('hour', 23, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusHours(23), session)); assertFunction("date_add('hour', -4, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.minusHours(4), session)); assertFunction("date_add('hour', -23, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.minusHours(23), session)); assertFunction("date_add('day', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusDays(3), session)); assertFunction("date_add('week', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusWeeks(3), session)); assertFunction("date_add('month', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusMonths(3), session)); assertFunction("date_add('quarter', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusMonths(3 * 3), session)); assertFunction("date_add('year', 3, " + TIMESTAMP_LITERAL + ")", TimestampType.TIMESTAMP, sqlTimestampOf(TIMESTAMP.plusYears(3), session)); assertFunction("date_add('millisecond', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusMillis(3))); assertFunction("date_add('second', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusSeconds(3))); assertFunction("date_add('minute', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusMinutes(3))); assertFunction("date_add('hour', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusHours(3))); assertFunction("date_add('day', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusDays(3))); assertFunction("date_add('week', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusWeeks(3))); assertFunction("date_add('month', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusMonths(3))); assertFunction("date_add('quarter', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusMonths(3 * 3))); assertFunction("date_add('year', 3, " + WEIRD_TIMESTAMP_LITERAL + ")", TIMESTAMP_WITH_TIME_ZONE, toTimestampWithTimeZone(WEIRD_TIMESTAMP.plusYears(3))); }
@Test public void testMapKeys() { assertFunction("MAP_KEYS(MAP(ARRAY['1', '3'], ARRAY['2', '4']))", new ArrayType(createVarcharType(1)), ImmutableList.of("1", "3")); assertFunction("MAP_KEYS(MAP(ARRAY[1.0E0, 2.0E0], ARRAY[ARRAY[1, 2], ARRAY[3]]))", new ArrayType(DOUBLE), ImmutableList.of(1.0, 2.0)); assertFunction("MAP_KEYS(MAP(ARRAY['puppies'], ARRAY['kittens']))", new ArrayType(createVarcharType(7)), ImmutableList.of("puppies")); assertFunction("MAP_KEYS(MAP(ARRAY[TRUE], ARRAY[2]))", new ArrayType(BOOLEAN), ImmutableList.of(true)); assertFunction( "MAP_KEYS(MAP(ARRAY[TIMESTAMP '1970-01-01 00:00:01'], ARRAY[1.0E0]))", new ArrayType(TIMESTAMP), ImmutableList.of(sqlTimestampOf(1970, 1, 1, 0, 0, 1, 0, TEST_SESSION))); assertFunction("MAP_KEYS(MAP(ARRAY[CAST('puppies' as varbinary)], ARRAY['kittens']))", new ArrayType(VARBINARY), ImmutableList.of(new SqlVarbinary("puppies".getBytes(UTF_8)))); assertFunction("MAP_KEYS(MAP(ARRAY[1,2], ARRAY[ARRAY[1, 2], ARRAY[3]]))", new ArrayType(INTEGER), ImmutableList.of(1, 2)); assertFunction("MAP_KEYS(MAP(ARRAY[1,4], ARRAY[MAP(ARRAY[2], ARRAY[3]), MAP(ARRAY[5], ARRAY[6])]))", new ArrayType(INTEGER), ImmutableList.of(1, 4)); assertFunction("MAP_KEYS(MAP(ARRAY [ARRAY [1], ARRAY [2, 3]], ARRAY [ARRAY [3, 4], ARRAY [5]]))", new ArrayType(new ArrayType(INTEGER)), ImmutableList.of(ImmutableList.of(1), ImmutableList.of(2, 3))); assertFunction( "MAP_KEYS(MAP(ARRAY [1.0, 383838383838383.12324234234234], ARRAY [2.2, 3.3]))", new ArrayType(createDecimalType(29, 14)), ImmutableList.of(decimal("000000000000001.00000000000000"), decimal("383838383838383.12324234234234"))); assertFunction( "MAP_KEYS(MAP(ARRAY [1.0, 2.01], ARRAY [2.2, 3.3]))", new ArrayType(createDecimalType(3, 2)), ImmutableList.of(decimal("1.00"), decimal("2.01"))); }
@Test public void testTryCast() throws Exception { assertExecute("try_cast(null as integer)", INTEGER, null); assertExecute("try_cast('123' as integer)", INTEGER, 123); assertExecute("try_cast(null as bigint)", BIGINT, null); assertExecute("try_cast('123' as bigint)", BIGINT, 123L); assertExecute("try_cast('foo' as varchar)", VARCHAR, "foo"); assertExecute("try_cast('foo' as bigint)", BIGINT, null); assertExecute("try_cast('foo' as integer)", INTEGER, null); assertExecute("try_cast('2001-08-22' as timestamp)", TIMESTAMP, sqlTimestampOf(2001, 8, 22, 0, 0, 0, 0, TEST_SESSION)); assertExecute("try_cast(bound_string as bigint)", BIGINT, null); assertExecute("try_cast(cast(null as varchar) as bigint)", BIGINT, null); assertExecute("try_cast(bound_long / 13 as bigint)", BIGINT, 94L); assertExecute("coalesce(try_cast('123' as bigint), 456)", BIGINT, 123L); assertExecute("coalesce(try_cast('foo' as bigint), 456)", BIGINT, 456L); assertExecute("concat('foo', cast('bar' as varchar))", VARCHAR, "foobar"); assertExecute("try_cast(try_cast(123 as varchar) as bigint)", BIGINT, 123L); assertExecute("try_cast('foo' as varchar) || try_cast('bar' as varchar)", VARCHAR, "foobar"); Futures.allAsList(futures).get(); }
@Test public void testSubscript() { assertFunction("MAP(ARRAY [1], ARRAY [null])[1]", UNKNOWN, null); assertFunction("MAP(ARRAY [1.0E0], ARRAY [null])[1.0E0]", UNKNOWN, null); assertFunction("MAP(ARRAY [TRUE], ARRAY [null])[TRUE]", UNKNOWN, null); assertFunction("MAP(ARRAY['puppies'], ARRAY [null])['puppies']", UNKNOWN, null); assertInvalidFunction("MAP(ARRAY [CAST(null as bigint)], ARRAY [1])", "map key cannot be null"); assertInvalidFunction("MAP(ARRAY [CAST(null as bigint)], ARRAY [CAST(null as bigint)])", "map key cannot be null"); assertInvalidFunction("MAP(ARRAY [1,null], ARRAY [null,2])", "map key cannot be null"); assertFunction("MAP(ARRAY [1, 3], ARRAY [2, 4])[3]", INTEGER, 4); assertFunction("MAP(ARRAY [BIGINT '1', 3], ARRAY [BIGINT '2', 4])[3]", BIGINT, 4L); assertFunction("MAP(ARRAY [1, 3], ARRAY[2, NULL])[3]", INTEGER, null); assertFunction("MAP(ARRAY [BIGINT '1', 3], ARRAY[2, NULL])[3]", INTEGER, null); assertFunction("MAP(ARRAY [1, 3], ARRAY [2.0E0, 4.0E0])[1]", DOUBLE, 2.0); assertFunction("MAP(ARRAY[1.0E0, 2.0E0], ARRAY[ ARRAY[1, 2], ARRAY[3]])[1.0E0]", new ArrayType(INTEGER), ImmutableList.of(1, 2)); assertFunction("MAP(ARRAY['puppies'], ARRAY['kittens'])['puppies']", createVarcharType(7), "kittens"); assertFunction("MAP(ARRAY[TRUE,FALSE],ARRAY[2,4])[TRUE]", INTEGER, 2); assertFunction( "MAP(ARRAY['1', '100'], ARRAY[TIMESTAMP '1970-01-01 00:00:01', TIMESTAMP '1973-07-08 22:00:01'])['1']", TIMESTAMP, sqlTimestampOf(1970, 1, 1, 0, 0, 1, 0, TEST_SESSION)); assertFunction("MAP(ARRAY[from_unixtime(1), from_unixtime(100)], ARRAY[1.0E0, 100.0E0])[from_unixtime(1)]", DOUBLE, 1.0); assertInvalidFunction("MAP(ARRAY [BIGINT '1'], ARRAY [BIGINT '2'])[3]", "Key not present in map: 3"); assertInvalidFunction("MAP(ARRAY ['hi'], ARRAY [2])['missing']", "Key not present in map: missing"); assertFunction("MAP(ARRAY[array[1,1]], ARRAY['a'])[ARRAY[1,1]]", createVarcharType(1), "a"); assertFunction("MAP(ARRAY[('a', 'b')], ARRAY[ARRAY[100, 200]])[('a', 'b')]", new ArrayType(INTEGER), ImmutableList.of(100, 200)); assertFunction("MAP(ARRAY[1.0], ARRAY [2.2])[1.0]", createDecimalType(2, 1), decimal("2.2")); assertFunction("MAP(ARRAY[000000000000001.00000000000000], ARRAY [2.2])[000000000000001.00000000000000]", createDecimalType(2, 1), decimal("2.2")); }