Examples with MaterializedResult io.prestosql.testing.MaterializedResult used on opensource projects

Example 16 with MaterializedResult

use of io.prestosql.testing.MaterializedResult in project hetu-core by openlookeng.

the class AbstractTestDistributedQueries method testCommentTable.

@Test
public void testCommentTable() {
    assertUpdate("CREATE TABLE test_comment(id integer)");
    assertUpdate("COMMENT ON TABLE test_comment IS 'new comment'");
    MaterializedResult materializedRows = computeActual("SHOW CREATE TABLE test_comment");
    assertTrue(materializedRows.getMaterializedRows().get(0).getField(0).toString().contains("COMMENT 'new comment'"));
    assertUpdate("COMMENT ON TABLE test_comment IS ''");
    materializedRows = computeActual("SHOW CREATE TABLE test_comment");
    assertTrue(materializedRows.getMaterializedRows().get(0).getField(0).toString().contains("COMMENT ''"));
    assertUpdate("COMMENT ON TABLE test_comment IS NULL");
    materializedRows = computeActual("SHOW CREATE TABLE test_comment");
    assertFalse(materializedRows.getMaterializedRows().get(0).getField(0).toString().contains("COMMENT"));
    assertUpdate("DROP TABLE test_comment");
}

Example 17 with MaterializedResult

use of io.prestosql.testing.MaterializedResult in project hetu-core by openlookeng.

the class AbstractTestDistributedQueries method testAddColumn.

@Test
public void testAddColumn() {
    assertUpdate("CREATE TABLE test_add_column AS SELECT 123 x", 1);
    assertUpdate("CREATE TABLE test_add_column_a AS SELECT 234 x, 111 a", 1);
    assertUpdate("CREATE TABLE test_add_column_ab AS SELECT 345 x, 222 a, 33.3E0 b", 1);
    assertQueryFails("ALTER TABLE test_add_column ADD COLUMN x bigint", ".* Column 'x' already exists");
    assertQueryFails("ALTER TABLE test_add_column ADD COLUMN X bigint", ".* Column 'X' already exists");
    assertQueryFails("ALTER TABLE test_add_column ADD COLUMN q bad_type", ".* Unknown type 'bad_type' for column 'q'");
    assertUpdate("ALTER TABLE test_add_column ADD COLUMN a bigint");
    assertUpdate("INSERT INTO test_add_column SELECT * FROM test_add_column_a", 1);
    MaterializedResult materializedRows = computeActual("SELECT x, a FROM test_add_column ORDER BY x");
    assertEquals(materializedRows.getMaterializedRows().get(0).getField(0), 123);
    assertNull(materializedRows.getMaterializedRows().get(0).getField(1));
    assertEquals(materializedRows.getMaterializedRows().get(1).getField(0), 234);
    assertEquals(materializedRows.getMaterializedRows().get(1).getField(1), 111L);
    if (supportsPushdown()) {
        Session session1 = Session.builder(getSession()).setCatalogSessionProperty(getSession().getCatalog().get(), "orc_predicate_pushdown_enabled", "true").build();
        MaterializedResult materializedRows1 = computeActual(session1, "SELECT x, a FROM test_add_column ORDER BY x");
        assertEquals(materializedRows1.getMaterializedRows().get(0).getField(0), 123);
        assertNull(materializedRows1.getMaterializedRows().get(0).getField(1));
        assertEquals(materializedRows1.getMaterializedRows().get(1).getField(0), 234);
        assertEquals(materializedRows1.getMaterializedRows().get(1).getField(1), 111L);
    }
    assertUpdate("ALTER TABLE test_add_column ADD COLUMN b double");
    assertUpdate("INSERT INTO test_add_column SELECT * FROM test_add_column_ab", 1);
    materializedRows = computeActual("SELECT x, a, b FROM test_add_column ORDER BY x");
    assertEquals(materializedRows.getMaterializedRows().get(0).getField(0), 123);
    assertNull(materializedRows.getMaterializedRows().get(0).getField(1));
    assertNull(materializedRows.getMaterializedRows().get(0).getField(2));
    assertEquals(materializedRows.getMaterializedRows().get(1).getField(0), 234);
    assertEquals(materializedRows.getMaterializedRows().get(1).getField(1), 111L);
    assertNull(materializedRows.getMaterializedRows().get(1).getField(2));
    assertEquals(materializedRows.getMaterializedRows().get(2).getField(0), 345);
    assertEquals(materializedRows.getMaterializedRows().get(2).getField(1), 222L);
    assertEquals(materializedRows.getMaterializedRows().get(2).getField(2), 33.3);
    assertUpdate("DROP TABLE test_add_column");
    assertUpdate("DROP TABLE test_add_column_a");
    assertUpdate("DROP TABLE test_add_column_ab");
    assertFalse(getQueryRunner().tableExists(getSession(), "test_add_column"));
    assertFalse(getQueryRunner().tableExists(getSession(), "test_add_column_a"));
    assertFalse(getQueryRunner().tableExists(getSession(), "test_add_column_ab"));
}

Example 18 with MaterializedResult

use of io.prestosql.testing.MaterializedResult in project hetu-core by openlookeng.

the class AbstractTestDistributedQueries method testLikePredicateWithPartitionKey.

@Test
public void testLikePredicateWithPartitionKey() {
    if (supportsPushdown()) {
        assertUpdate("DROP TABLE IF EXISTS test_partition_predicate");
        assertUpdate("CREATE TABLE test_partition_predicate (id int, p1 varchar, p2 varchar) WITH (partitioned_by=ARRAY['p2'])");
        assertTrue(getQueryRunner().tableExists(getSession(), "test_partition_predicate"));
        assertTableColumnNames("test_partition_predicate", "id", "p1", "p2");
        assertUpdate("INSERT INTO test_partition_predicate VALUES (1,'aaa','aaa'), (2,'bbb','bbb'), (3,'ccc','ccc')", 3);
        assertUpdate("INSERT INTO test_partition_predicate VALUES (4,'ddd','ddd'), (5,'eee','eee'), (6,'fff','fff')", 3);
        assertUpdate("INSERT INTO test_partition_predicate VALUES (7,'ggg','ggg'), (8,'hhh','hhh'), (9,'iii','iii')", 3);
        Session sessionPushdown = Session.builder(getSession()).setCatalogSessionProperty(getSession().getCatalog().get(), "orc_predicate_pushdown_enabled", "true").setCatalogSessionProperty(getSession().getCatalog().get(), "orc_row_data_cache_enabled", "false").build();
        Session sessionCachePushdown = Session.builder(getSession()).setCatalogSessionProperty(getSession().getCatalog().get(), "orc_predicate_pushdown_enabled", "true").setCatalogSessionProperty(getSession().getCatalog().get(), "orc_row_data_cache_enabled", "true").build();
        assertUpdate("INSERT INTO test_partition_predicate VALUES (10,NULL,'10'), (11,NULL,NULL), (NULL,NULL,NULL)", 3);
        assertUpdate("INSERT INTO test_partition_predicate VALUES (13,'ab',NULL), (14,'aab',NULL), (NULL,'aaab',NULL)", 3);
        assertUpdate("INSERT INTO test_partition_predicate VALUES (15,'ab','aab'), (16,'aab','aab'), (NULL,'aaab','aaab')", 3);
        assertUpdate("INSERT INTO test_partition_predicate VALUES (18,'b',NULL), (19,'abb',NULL), (NULL,'abbb',NULL)", 3);
        assertUpdate("INSERT INTO test_partition_predicate VALUES (21,'b','b'), (22,'abb','abb'), (NULL,'abbb','abbb')", 3);
        MaterializedResult resultNormal;
        MaterializedResult resultPushdown;
        MaterializedResult resultCachePushdown;
        /* Conjuct Like */
        List<String> sqlList = Arrays.asList("SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE 'a%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE 'a%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE 'aa%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE 'ab%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE '%a' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE '%b' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE '%ab' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE '%a%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE '%aa%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p2 LIKE '%aaa%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE 'a%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE 'a%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE 'aa%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE 'ab%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE '%a' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE '%b' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE '%ab' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE '%a%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE '%aa%' ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and p1 LIKE '%aaa%' ORDER BY id");
        for (String sql : sqlList) {
            resultNormal = computeActual(sql);
            resultPushdown = computeActual(sessionPushdown, sql);
            resultCachePushdown = computeActual(sessionCachePushdown, sql);
            assertEquals(resultNormal.getMaterializedRows(), resultPushdown.getMaterializedRows());
            assertEquals(resultNormal.getMaterializedRows(), resultCachePushdown.getMaterializedRows());
        }
        /* disjunct Like */
        sqlList = Arrays.asList("SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'a%'      OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'a%'      OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'aa%'     OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'ab%'     OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%a'      OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%b'      OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%ab'     OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%a%'     OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%aa%'    OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%aaa%'   OR p1 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'a%'      OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'a%'      OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'aa%'     OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'ab%'     OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%a'      OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%b'      OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%ab'     OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%a%'     OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%aa%'    OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%aaa%'   OR p1 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'a%'      OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'a%'      OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'aa%'     OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE 'ab%'     OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%a'      OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%b'      OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%ab'     OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%a%'     OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%aa%'    OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p2 LIKE '%aaa%'   OR p1 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'a%'      OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'a%'      OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'aa%'     OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'ab%'     OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%a'      OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%b'      OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%ab'     OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%a%'     OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%aa%'    OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%aaa%'   OR p2 > 'c') ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'a%'      OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'a%'      OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'aa%'     OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'ab%'     OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%a'      OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%b'      OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%ab'     OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%a%'     OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%aa%'    OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%aaa%'   OR p2 IS NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'a%'      OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'a%'      OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'aa%'     OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE 'ab%'     OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%a'      OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%b'      OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%ab'     OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%a%'     OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%aa%'    OR p2 IS NOT NULL) ORDER BY id", "SELECT id, p1, p2 FROM test_partition_predicate WHERE id > 0 and (p1 LIKE '%aaa%'   OR p2 IS NOT NULL) ORDER BY id");
        for (String sql : sqlList) {
            resultNormal = computeActual(sql);
            resultPushdown = computeActual(sessionPushdown, sql);
            resultCachePushdown = computeActual(sessionCachePushdown, sql);
            if (!resultNormal.getMaterializedRows().equals(resultPushdown.getMaterializedRows()) || !resultNormal.getMaterializedRows().equals(resultCachePushdown.getMaterializedRows())) {
                System.out.println("------------------------------------------------------------------------");
                System.out.println("Failed Query: " + sql);
                System.out.println("------------------------------------------------------------------------");
                System.out.println("Expected        : " + resultNormal.getMaterializedRows());
                System.out.println("Result[Pushdown]: " + resultPushdown.getMaterializedRows());
                System.out.println("Result[Cache]   : " + resultCachePushdown.getMaterializedRows());
                System.out.println("------------------------------------------------------------------------");
                resultNormal = computeActual("EXPLAIN " + sql);
                resultPushdown = computeActual(sessionPushdown, "EXPLAIN " + sql);
                resultCachePushdown = computeActual(sessionCachePushdown, "EXPLAIN " + sql);
                System.out.println("Running query [" + sql + "]");
                System.out.println("Normal Plan: " + resultNormal);
                System.out.println("PushDown Plan: " + resultPushdown);
                System.out.println("CacheDown Plan: " + resultCachePushdown);
                System.out.println("------------------------------------------------------------------------");
            }
            assertEquals(resultNormal.getMaterializedRows(), resultPushdown.getMaterializedRows());
            assertEquals(resultNormal.getMaterializedRows(), resultCachePushdown.getMaterializedRows());
        }
    }
}

Example 19 with MaterializedResult

use of io.prestosql.testing.MaterializedResult in project hetu-core by openlookeng.

the class AbstractTestDistributedQueries method testTableSampleSystemBoundaryValues.

@Test
public void testTableSampleSystemBoundaryValues() {
    MaterializedResult fullSample = computeActual("SELECT orderkey FROM orders TABLESAMPLE SYSTEM (100)");
    MaterializedResult emptySample = computeActual("SELECT orderkey FROM orders TABLESAMPLE SYSTEM (0)");
    MaterializedResult all = computeActual("SELECT orderkey FROM orders");
    assertContains(all, fullSample);
    assertEquals(emptySample.getMaterializedRows().size(), 0);
}

Example 20 with MaterializedResult

use of io.prestosql.testing.MaterializedResult in project hetu-core by openlookeng.

the class AbstractTestDistributedQueries method testRenameTable.

@Test
public void testRenameTable() {
    assertUpdate("CREATE TABLE test_rename AS SELECT 123 x", 1);
    assertUpdate("ALTER TABLE test_rename RENAME TO test_rename_new");
    MaterializedResult materializedRows = computeActual("SELECT x FROM test_rename_new");
    assertEquals(getOnlyElement(materializedRows.getMaterializedRows()).getField(0), 123);
    // provide new table name in uppercase
    assertUpdate("ALTER TABLE test_rename_new RENAME TO TEST_RENAME");
    materializedRows = computeActual("SELECT x FROM test_rename");
    assertEquals(getOnlyElement(materializedRows.getMaterializedRows()).getField(0), 123);
    assertUpdate("DROP TABLE test_rename");
    assertFalse(getQueryRunner().tableExists(getSession(), "test_rename"));
    assertFalse(getQueryRunner().tableExists(getSession(), "test_rename_new"));
}