Examples with ArrayType com.facebook.presto.common.type.ArrayType used on opensource projects

Example 71 with ArrayType

use of com.facebook.presto.common.type.ArrayType in project presto by prestodb.

the class TestArrayOperators method testSequenceDateTimeYearToMonth.

@Test
public void testSequenceDateTimeYearToMonth() throws ParseException {
    assertFunction("SEQUENCE(date '2016-04-12', date '2016-06-12', interval '1' month)", new ArrayType(DATE), ImmutableList.of(sqlDate("2016-04-12"), sqlDate("2016-05-12"), sqlDate("2016-06-12")));
    assertFunction("SEQUENCE(date '2016-06-12', date '2016-04-12', interval '-1' month)", new ArrayType(DATE), ImmutableList.of(sqlDate("2016-06-12"), sqlDate("2016-05-12"), sqlDate("2016-04-12")));
    assertFunction("SEQUENCE(date '2016-04-12', date '2016-08-12', interval '2' month)", new ArrayType(DATE), ImmutableList.of(sqlDate("2016-04-12"), sqlDate("2016-06-12"), sqlDate("2016-08-12")));
    assertFunction("SEQUENCE(date '2016-08-12', date '2016-04-12', interval '-2' month)", new ArrayType(DATE), ImmutableList.of(sqlDate("2016-08-12"), sqlDate("2016-06-12"), sqlDate("2016-04-12")));
    assertFunction("SEQUENCE(date '2016-04-12', date '2018-04-12', interval '1' year)", new ArrayType(DATE), ImmutableList.of(sqlDate("2016-04-12"), sqlDate("2017-04-12"), sqlDate("2018-04-12")));
    assertFunction("SEQUENCE(date '2018-04-12', date '2016-04-12', interval '-1' year)", new ArrayType(DATE), ImmutableList.of(sqlDate("2018-04-12"), sqlDate("2017-04-12"), sqlDate("2016-04-12")));
    assertFunction("SEQUENCE(timestamp '2016-04-16 01:00:10', timestamp '2016-09-16 01:10:00', interval '2' month)", new ArrayType(TIMESTAMP), ImmutableList.of(sqlTimestampOf(2016, 4, 16, 1, 0, 10, 0, TEST_SESSION), sqlTimestampOf(2016, 6, 16, 1, 0, 10, 0, TEST_SESSION), sqlTimestampOf(2016, 8, 16, 1, 0, 10, 0, TEST_SESSION)));
    assertFunction("SEQUENCE(timestamp '2016-09-16 01:10:10', timestamp '2016-04-16 01:00:00', interval '-2' month)", new ArrayType(TIMESTAMP), ImmutableList.of(sqlTimestampOf(2016, 9, 16, 1, 10, 10, 0, TEST_SESSION), sqlTimestampOf(2016, 7, 16, 1, 10, 10, 0, TEST_SESSION), sqlTimestampOf(2016, 5, 16, 1, 10, 10, 0, TEST_SESSION)));
    assertFunction("SEQUENCE(timestamp '2016-04-16 01:00:10', timestamp '2021-04-16 01:01:00', interval '2' year)", new ArrayType(TIMESTAMP), ImmutableList.of(sqlTimestampOf(2016, 4, 16, 1, 0, 10, 0, TEST_SESSION), sqlTimestampOf(2018, 4, 16, 1, 0, 10, 0, TEST_SESSION), sqlTimestampOf(2020, 4, 16, 1, 0, 10, 0, TEST_SESSION)));
    assertFunction("SEQUENCE(timestamp '2016-04-16 01:01:10', timestamp '2011-04-16 01:00:00', interval '-2' year)", new ArrayType(TIMESTAMP), ImmutableList.of(sqlTimestampOf(2016, 4, 16, 1, 1, 10, 0, TEST_SESSION), sqlTimestampOf(2014, 4, 16, 1, 1, 10, 0, TEST_SESSION), sqlTimestampOf(2012, 4, 16, 1, 1, 10, 0, TEST_SESSION)));
    // failure modes
    assertInvalidFunction("SEQUENCE(date '2016-06-12', date '2016-04-12', interval '1' month)", INVALID_FUNCTION_ARGUMENT, "sequence stop value should be greater than or equal to start value if step is greater than zero otherwise stop should be less than or equal to start");
    assertInvalidFunction("SEQUENCE(date '2016-04-12', date '2016-06-12', interval '-1' month)", INVALID_FUNCTION_ARGUMENT, "sequence stop value should be greater than or equal to start value if step is greater than zero otherwise stop should be less than or equal to start");
    assertInvalidFunction("SEQUENCE(date '2000-04-12', date '3000-06-12', interval '1' month)", INVALID_FUNCTION_ARGUMENT, "result of sequence function must not have more than 10000 entries");
    assertInvalidFunction("SEQUENCE(timestamp '2016-05-16 01:00:10', timestamp '2016-04-16 01:01:00', interval '1' month)", INVALID_FUNCTION_ARGUMENT, "sequence stop value should be greater than or equal to start value if step is greater than zero otherwise stop should be less than or equal to start");
    assertInvalidFunction("SEQUENCE(timestamp '2016-04-16 01:10:10', timestamp '2016-05-16 01:01:00', interval '-1' month)", INVALID_FUNCTION_ARGUMENT, "sequence stop value should be greater than or equal to start value if step is greater than zero otherwise stop should be less than or equal to start");
    assertInvalidFunction("SEQUENCE(timestamp '2016-04-16 01:00:10', timestamp '3000-04-16 09:01:00', interval '1' month)", INVALID_FUNCTION_ARGUMENT, "result of sequence function must not have more than 10000 entries");
}

Example 72 with ArrayType

use of com.facebook.presto.common.type.ArrayType in project presto by prestodb.

the class TestArrayOperators method testArrayMin.

@Test
public void testArrayMin() {
    assertFunction("ARRAY_MIN(ARRAY [])", UNKNOWN, null);
    assertFunction("ARRAY_MIN(ARRAY [NULL])", UNKNOWN, null);
    assertFunction("ARRAY_MIN(ARRAY [NaN()])", DOUBLE, NaN);
    assertFunction("ARRAY_MIN(ARRAY [NULL, NULL, NULL])", UNKNOWN, null);
    assertFunction("ARRAY_MIN(ARRAY [NaN(), NaN(), NaN()])", DOUBLE, NaN);
    assertFunction("ARRAY_MIN(ARRAY [NULL, 2, 3])", INTEGER, null);
    assertFunction("ARRAY_MIN(ARRAY [NaN(), 2, 3])", DOUBLE, NaN);
    assertFunction("ARRAY_MIN(ARRAY [NULL, NaN(), 1])", DOUBLE, NaN);
    assertFunction("ARRAY_MIN(ARRAY [NaN(), NULL, 3.0])", DOUBLE, NaN);
    assertFunction("ARRAY_MIN(ARRAY [1.0E0, NULL, 3])", DOUBLE, null);
    assertFunction("ARRAY_MIN(ARRAY [1.0, NaN(), 3])", DOUBLE, NaN);
    assertFunction("ARRAY_MIN(ARRAY ['1', '2', NULL])", createVarcharType(1), null);
    assertFunction("ARRAY_MIN(ARRAY [3, 2, 1])", INTEGER, 1);
    assertFunction("ARRAY_MIN(ARRAY [1, 2, 3])", INTEGER, 1);
    assertFunction("ARRAY_MIN(ARRAY [BIGINT '3', 2, 1])", BIGINT, 1L);
    assertFunction("ARRAY_MIN(ARRAY [1, 2.0E0, 3])", DOUBLE, 1.0);
    assertFunction("ARRAY_MIN(ARRAY [ARRAY[1, 2], ARRAY[3]])", new ArrayType(INTEGER), ImmutableList.of(1, 2));
    assertFunction("ARRAY_MIN(ARRAY [1.0E0, 2.5E0, 3.0E0])", DOUBLE, 1.0);
    assertFunction("ARRAY_MIN(ARRAY ['puppies', 'kittens'])", createVarcharType(7), "kittens");
    assertFunction("ARRAY_MIN(ARRAY [TRUE, FALSE])", BOOLEAN, false);
    assertFunction("ARRAY_MIN(ARRAY [NULL, FALSE])", BOOLEAN, null);
    assertDecimalFunction("ARRAY_MIN(ARRAY [2.1, 2.2, 2.3])", decimal("2.1"));
    assertDecimalFunction("ARRAY_MIN(ARRAY [2.111111222111111114111, 2.22222222222222222, 2.222222222222223])", decimal("2.111111222111111114111"));
    assertDecimalFunction("ARRAY_MIN(ARRAY [1.9, 2, 2.3])", decimal("0000000001.9"));
    assertDecimalFunction("ARRAY_MIN(ARRAY [2.22222222222222222, 2.3])", decimal("2.22222222222222222"));
}

Example 73 with ArrayType

use of com.facebook.presto.common.type.ArrayType in project presto by prestodb.

the class TestArrayOperators method testArrayUnion.

@Test
public void testArrayUnion() {
    assertFunction("ARRAY_UNION(ARRAY [cast(10 as bigint), NULL, cast(12 as bigint), NULL], ARRAY [NULL, cast(10 as bigint), NULL, NULL])", new ArrayType(BIGINT), asList(10L, null, 12L));
    assertFunction("ARRAY_UNION(ARRAY [12], ARRAY [10])", new ArrayType(INTEGER), ImmutableList.of(12, 10));
    assertFunction("ARRAY_UNION(ARRAY ['foo', 'bar', 'baz'], ARRAY ['foo', 'test', 'bar'])", new ArrayType(createVarcharType(4)), ImmutableList.of("foo", "bar", "baz", "test"));
    assertFunction("ARRAY_UNION(ARRAY [NULL], ARRAY [NULL, NULL])", new ArrayType(UNKNOWN), asList((Object) null));
    assertFunction("ARRAY_UNION(ARRAY ['abc', NULL, 'xyz', NULL], ARRAY [NULL, 'abc', NULL, NULL])", new ArrayType(createVarcharType(3)), asList("abc", null, "xyz"));
    assertFunction("ARRAY_UNION(ARRAY [1, 5], ARRAY [1])", new ArrayType(INTEGER), ImmutableList.of(1, 5));
    assertFunction("ARRAY_UNION(ARRAY [1, 1, 2, 4], ARRAY [1, 1, 4, 4])", new ArrayType(INTEGER), ImmutableList.of(1, 2, 4));
    assertFunction("ARRAY_UNION(ARRAY [2, 8], ARRAY [8, 3])", new ArrayType(INTEGER), ImmutableList.of(2, 8, 3));
    assertFunction("ARRAY_UNION(ARRAY [IF (RAND() < 1.0E0, 7, 1) , 2], ARRAY [7])", new ArrayType(INTEGER), ImmutableList.of(7, 2));
    assertFunction("ARRAY_UNION(ARRAY [1, 5], ARRAY [1.0E0])", new ArrayType(DOUBLE), ImmutableList.of(1.0, 5.0));
    assertFunction("ARRAY_UNION(ARRAY [8.3E0, 1.6E0, 4.1E0, 5.2E0], ARRAY [4.0E0, 5.2E0, 8.3E0, 9.7E0, 3.5E0])", new ArrayType(DOUBLE), ImmutableList.of(8.3, 1.6, 4.1, 5.2, 4.0, 9.7, 3.5));
    assertFunction("ARRAY_UNION(ARRAY [5.1E0, 7, 3.0E0, 4.8E0, 10], ARRAY [6.5E0, 10.0E0, 1.9E0, 5.1E0, 3.9E0, 4.8E0])", new ArrayType(DOUBLE), ImmutableList.of(5.1, 7.0, 3.0, 4.8, 10.0, 6.5, 1.9, 3.9));
    assertFunction("ARRAY_UNION(ARRAY [ARRAY [4, 5], ARRAY [6, 7]], ARRAY [ARRAY [4, 5], ARRAY [6, 8]])", new ArrayType(new ArrayType(INTEGER)), ImmutableList.of(ImmutableList.of(4, 5), ImmutableList.of(6, 7), ImmutableList.of(6, 8)));
}

Example 74 with ArrayType

use of com.facebook.presto.common.type.ArrayType in project presto by prestodb.

the class TestArrayOperators method testRepeat.

@Test
public void testRepeat() {
    // concrete values
    assertFunction("REPEAT(1, 5)", new ArrayType(INTEGER), ImmutableList.of(1, 1, 1, 1, 1));
    assertFunction("REPEAT('varchar', 3)", new ArrayType(createVarcharType(7)), ImmutableList.of("varchar", "varchar", "varchar"));
    assertFunction("REPEAT(true, 1)", new ArrayType(BOOLEAN), ImmutableList.of(true));
    assertFunction("REPEAT(0.5E0, 4)", new ArrayType(DOUBLE), ImmutableList.of(0.5, 0.5, 0.5, 0.5));
    assertFunction("REPEAT(array[1], 4)", new ArrayType(new ArrayType(INTEGER)), ImmutableList.of(ImmutableList.of(1), ImmutableList.of(1), ImmutableList.of(1), ImmutableList.of(1)));
    // null values
    assertFunction("REPEAT(null, 4)", new ArrayType(UNKNOWN), asList(null, null, null, null));
    assertFunction("REPEAT(cast(null as bigint), 4)", new ArrayType(BIGINT), asList(null, null, null, null));
    assertFunction("REPEAT(cast(null as double), 4)", new ArrayType(DOUBLE), asList(null, null, null, null));
    assertFunction("REPEAT(cast(null as varchar), 4)", new ArrayType(VARCHAR), asList(null, null, null, null));
    assertFunction("REPEAT(cast(null as boolean), 4)", new ArrayType(BOOLEAN), asList(null, null, null, null));
    assertFunction("REPEAT(cast(null as array(boolean)), 4)", new ArrayType(new ArrayType(BOOLEAN)), asList(null, null, null, null));
    // 0 counts
    assertFunction("REPEAT(cast(null as bigint), 0)", new ArrayType(BIGINT), ImmutableList.of());
    assertFunction("REPEAT(1, 0)", new ArrayType(INTEGER), ImmutableList.of());
    assertFunction("REPEAT('varchar', 0)", new ArrayType(createVarcharType(7)), ImmutableList.of());
    assertFunction("REPEAT(true, 0)", new ArrayType(BOOLEAN), ImmutableList.of());
    assertFunction("REPEAT(0.5E0, 0)", new ArrayType(DOUBLE), ImmutableList.of());
    assertFunction("REPEAT(array[1], 0)", new ArrayType(new ArrayType(INTEGER)), ImmutableList.of());
    // illegal inputs
    assertInvalidFunction("REPEAT(2, -1)", INVALID_FUNCTION_ARGUMENT);
    assertInvalidFunction("REPEAT(1, 1000000)", INVALID_FUNCTION_ARGUMENT);
    assertInvalidFunction("REPEAT('loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooongvarchar', 9999)", INVALID_FUNCTION_ARGUMENT);
    assertInvalidFunction("REPEAT(array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20], 9999)", INVALID_FUNCTION_ARGUMENT);
}

Example 75 with ArrayType

use of com.facebook.presto.common.type.ArrayType in project presto by prestodb.

the class TestArrayOperators method testSort.

@Test
public void testSort() {
    assertFunction("ARRAY_SORT(ARRAY[2, 3, 4, 1])", new ArrayType(INTEGER), ImmutableList.of(1, 2, 3, 4));
    assertFunction("ARRAY_SORT(ARRAY[2, BIGINT '3', 4, 1])", new ArrayType(BIGINT), ImmutableList.of(1L, 2L, 3L, 4L));
    assertFunction("ARRAY_SORT(ARRAY [2.3, 2.1, 2.2])", new ArrayType(createDecimalType(2, 1)), ImmutableList.of(decimal("2.1"), decimal("2.2"), decimal("2.3")));
    assertFunction("ARRAY_SORT(ARRAY [2, 1.900, 2.330])", new ArrayType(createDecimalType(13, 3)), ImmutableList.of(decimal("0000000001.900"), decimal("0000000002.000"), decimal("0000000002.330")));
    assertFunction("ARRAY_SORT(ARRAY['z', 'f', 's', 'd', 'g'])", new ArrayType(createVarcharType(1)), ImmutableList.of("d", "f", "g", "s", "z"));
    assertFunction("ARRAY_SORT(ARRAY[TRUE, FALSE])", new ArrayType(BOOLEAN), ImmutableList.of(false, true));
    assertFunction("ARRAY_SORT(ARRAY[22.1E0, 11.1E0, 1.1E0, 44.1E0])", new ArrayType(DOUBLE), ImmutableList.of(1.1, 11.1, 22.1, 44.1));
    assertFunction("ARRAY_SORT(ARRAY [TIMESTAMP '1973-07-08 22:00:01', TIMESTAMP '1970-01-01 00:00:01', TIMESTAMP '1989-02-06 12:00:00'])", new ArrayType(TIMESTAMP), ImmutableList.of(sqlTimestampOf(1970, 1, 1, 0, 0, 1, 0, TEST_SESSION), sqlTimestampOf(1973, 7, 8, 22, 0, 1, 0, TEST_SESSION), sqlTimestampOf(1989, 2, 6, 12, 0, 0, 0, TEST_SESSION)));
    assertFunction("ARRAY_SORT(ARRAY [ARRAY [1], ARRAY [2]])", new ArrayType(new ArrayType(INTEGER)), ImmutableList.of(ImmutableList.of(1), ImmutableList.of(2)));
    // with lambda function
    assertFunction("ARRAY_SORT(ARRAY[2, 3, 2, null, null, 4, 1], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN x < y THEN 1 " + "WHEN x = y THEN 0 " + "ELSE -1 END)", new ArrayType(INTEGER), asList(null, null, 4, 3, 2, 2, 1));
    assertFunction("ARRAY_SORT(ARRAY[2, 3, 2, null, null, 4, 1], (x, y) -> CASE " + "WHEN x IS NULL THEN 1 " + "WHEN y IS NULL THEN -1 " + "WHEN x < y THEN 1 " + "WHEN x = y THEN 0 " + "ELSE -1 END)", new ArrayType(INTEGER), asList(4, 3, 2, 2, 1, null, null));
    assertFunction("ARRAY_SORT(ARRAY[2, null, BIGINT '3', 4, null, 1], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN x < y THEN 1 " + "WHEN x = y THEN 0 " + "ELSE -1 END)", new ArrayType(BIGINT), asList(null, null, 4L, 3L, 2L, 1L));
    assertFunction("ARRAY_SORT(ARRAY['bc', null, 'ab', 'dc', null], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN x < y THEN 1 " + "WHEN x = y THEN 0 " + "ELSE -1 END)", new ArrayType(createVarcharType(2)), asList(null, null, "dc", "bc", "ab"));
    assertFunction("ARRAY_SORT(ARRAY['a', null, 'abcd', null, 'abc', 'zx'], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN length(x) < length(y) THEN 1 " + "WHEN length(x) = length(y) THEN 0 " + "ELSE -1 END)", new ArrayType(createVarcharType(4)), asList(null, null, "abcd", "abc", "zx", "a"));
    assertFunction("ARRAY_SORT(ARRAY[TRUE, null, FALSE, TRUE, null, FALSE, TRUE], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN x = y THEN 0 " + "WHEN x THEN -1 " + "ELSE 1 END)", new ArrayType(BOOLEAN), asList(null, null, true, true, true, false, false));
    assertFunction("ARRAY_SORT(ARRAY[22.1E0, null, null, 11.1E0, 1.1E0, 44.1E0], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN x < y THEN 1 " + "WHEN x = y THEN 0 " + "ELSE -1 END)", new ArrayType(DOUBLE), asList(null, null, 44.1, 22.1, 11.1, 1.1));
    assertFunction("ARRAY_SORT(ARRAY[TIMESTAMP '1973-07-08 22:00:01', NULL, TIMESTAMP '1970-01-01 00:00:01', NULL, TIMESTAMP '1989-02-06 12:00:00'], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN date_diff('millisecond', y, x) < 0 THEN 1 " + "WHEN date_diff('millisecond', y, x) = 0 THEN 0 " + "ELSE -1 END)", new ArrayType(TIMESTAMP), asList(null, null, sqlTimestampOf(1989, 2, 6, 12, 0, 0, 0, TEST_SESSION), sqlTimestampOf(1973, 7, 8, 22, 0, 1, 0, TEST_SESSION), sqlTimestampOf(1970, 1, 1, 0, 0, 1, 0, TEST_SESSION)));
    assertFunction("ARRAY_SORT(ARRAY[ARRAY[2, 3, 1], null, ARRAY[4, null, 2, 1, 4], ARRAY[1, 2], null], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN cardinality(x) < cardinality(y) THEN 1 " + "WHEN cardinality(x) = cardinality(y) THEN 0 " + "ELSE -1 END)", new ArrayType(new ArrayType(INTEGER)), asList(null, null, asList(4, null, 2, 1, 4), asList(2, 3, 1), asList(1, 2)));
    assertFunction("ARRAY_SORT(ARRAY[2.3, null, 2.1, null, 2.2], (x, y) -> CASE " + "WHEN x IS NULL THEN -1 " + "WHEN y IS NULL THEN 1 " + "WHEN x < y THEN 1 " + "WHEN x = y THEN 0 " + "ELSE -1 END)", new ArrayType(createDecimalType(2, 1)), asList(null, null, decimal("2.3"), decimal("2.2"), decimal("2.1")));
    // with null in the array, should be in nulls-last order
    List<Integer> expected = asList(-1, 0, 1, null, null);
    assertFunction("ARRAY_SORT(ARRAY[1, null, 0, null, -1])", new ArrayType(INTEGER), expected);
    assertFunction("ARRAY_SORT(ARRAY[1, null, null, -1, 0])", new ArrayType(INTEGER), expected);
    // invalid functions
    assertInvalidFunction("ARRAY_SORT(ARRAY[color('red'), color('blue')])", FUNCTION_NOT_FOUND);
    assertInvalidFunction("ARRAY_SORT(ARRAY[2, 1, 2, 4], (x, y) -> y - x)", INVALID_FUNCTION_ARGUMENT, "Lambda comparator must return either -1, 0, or 1");
    assertInvalidFunction("ARRAY_SORT(ARRAY[1, 2], (x, y) -> x / COALESCE(y, 0))", INVALID_FUNCTION_ARGUMENT, "Lambda comparator must return either -1, 0, or 1");
    assertInvalidFunction("ARRAY_SORT(ARRAY[2, 3, 2, 4, 1], (x, y) -> IF(x > y, NULL, IF(x = y, 0, -1)))", INVALID_FUNCTION_ARGUMENT, "Lambda comparator must return either -1, 0, or 1");
    assertInvalidFunction("ARRAY_SORT(ARRAY[1, null], (x, y) -> x / COALESCE(y, 0))", INVALID_FUNCTION_ARGUMENT, "Lambda comparator must return either -1, 0, or 1");
    assertInvalidFunction("ARRAY_SORT(ARRAY[ARRAY[1], ARRAY[null]])", INVALID_FUNCTION_ARGUMENT, "Array contains elements not supported for comparison");
    assertInvalidFunction("ARRAY_SORT(ARRAY[ROW(1), ROW(null)])", INVALID_FUNCTION_ARGUMENT, "Array contains elements not supported for comparison");
    assertCachedInstanceHasBoundedRetainedSize("ARRAY_SORT(ARRAY[2, 3, 4, 1])");
}