@Test public void testShowTables() { Set<String> expectedTables = ImmutableSet.copyOf(transform(TpchTable.getTables(), TpchTable::getTableName)); MaterializedResult result = computeActual("SHOW TABLES"); assertTrue(result.getOnlyColumnAsSet().containsAll(expectedTables)); }
@Test public void testNonDeterministicFilter() { MaterializedResult materializedResult = computeActual("SELECT u FROM ( SELECT if(rand() > 0.5, 0, 1) AS u ) WHERE u <> u"); assertEquals(materializedResult.getRowCount(), 0); materializedResult = computeActual("SELECT u, v FROM ( SELECT if(rand() > 0.5, 0, 1) AS u, 4*4 AS v ) WHERE u <> u and v > 10"); assertEquals(materializedResult.getRowCount(), 0); materializedResult = computeActual("SELECT u, v, w FROM ( SELECT if(rand() > 0.5, 0, 1) AS u, 4*4 AS v, 'abc' AS w ) WHERE v > 10"); assertEquals(materializedResult.getRowCount(), 1); }
@Test public void testShowSchemasFrom() { MaterializedResult result = computeActual(format("SHOW SCHEMAS FROM %s", getSession().getCatalog().get())); assertTrue(result.getOnlyColumnAsSet().containsAll(ImmutableSet.of(getSession().getSchema().get(), INFORMATION_SCHEMA))); }
@Test public void testNodeRoster() { List<MaterializedRow> result = computeActual("SELECT * FROM system.runtime.nodes").getMaterializedRows(); assertEquals(result.size(), getNodeCount()); }
@Test public void testDistributedExplainGraphvizFormat() { String query = "SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED, FORMAT GRAPHVIZ) " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getGraphvizExplainPlan(query, DISTRIBUTED)); }
@Test public void testDefaultExplainTextFormat() { String query = "SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL)); }
@Test public void testShowTablesLikeWithEscape() { assertQueryFails("SHOW TABLES IN a LIKE '%$_%' ESCAPE", "line 1:36: mismatched input '<EOF>'. Expecting: <string>"); assertQueryFails("SHOW TABLES LIKE 't$_%' ESCAPE ''", "Escape string must be a single character"); assertQueryFails("SHOW TABLES LIKE 't$_%' ESCAPE '$$'", "Escape string must be a single character"); Set<Object> allTables = computeActual("SHOW TABLES FROM information_schema").getOnlyColumnAsSet(); assertEquals(allTables, computeActual("SHOW TABLES FROM information_schema LIKE '%_%'").getOnlyColumnAsSet()); Set<Object> result = computeActual("SHOW TABLES FROM information_schema LIKE '%$_%' ESCAPE '$'").getOnlyColumnAsSet(); assertNotEquals(allTables, result); assertThat(result).contains("table_privileges").allMatch(schemaName -> ((String) schemaName).contains("_")); }
@Test public void testShowSchemasLikeWithEscape() { assertQueryFails("SHOW SCHEMAS IN foo LIKE '%$_%' ESCAPE", "line 1:39: mismatched input '<EOF>'. Expecting: <string>"); assertQueryFails("SHOW SCHEMAS LIKE 't$_%' ESCAPE ''", "Escape string must be a single character"); assertQueryFails("SHOW SCHEMAS LIKE 't$_%' ESCAPE '$$'", "Escape string must be a single character"); Set<Object> allSchemas = computeActual("SHOW SCHEMAS").getOnlyColumnAsSet(); assertEquals(allSchemas, computeActual("SHOW SCHEMAS LIKE '%_%'").getOnlyColumnAsSet()); Set<Object> result = computeActual("SHOW SCHEMAS LIKE '%$_%' ESCAPE '$'").getOnlyColumnAsSet(); assertNotEquals(allSchemas, result); assertThat(result).contains("information_schema").allMatch(schemaName -> ((String) schemaName).contains("_")); }
@Test public void testInnerJoinWithEmptyBuildSide() { MaterializedResult actual = computeActual( noJoinReordering(), "WITH small_part AS (SELECT * FROM part WHERE name = 'a') " + "SELECT lineitem.orderkey FROM lineitem INNER JOIN small_part ON lineitem.partkey = small_part.partkey"); assertEquals(actual.getRowCount(), 0); }
@Test public void testTopNUnpartitionedLargeWindow() { MaterializedResult actual = computeActual("" + "SELECT * FROM (\n" + " SELECT row_number() OVER (ORDER BY orderkey) rn, orderkey, orderstatus\n" + " FROM orders\n" + ") WHERE rn <= 10000"); String sql = "SELECT row_number() OVER (), orderkey, orderstatus FROM orders ORDER BY orderkey LIMIT 10000"; MaterializedResult expected = computeExpected(sql, actual.getTypes()); assertEqualsIgnoreOrder(actual, expected); }
@Test public void testShowSchemasLike() { MaterializedResult result = computeActual(format("SHOW SCHEMAS LIKE '%s'", getSession().getSchema().get())); assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(getSession().getSchema().get())); }
@Test public void testShowCatalogsLike() { MaterializedResult result = computeActual(format("SHOW CATALOGS LIKE '%s'", getSession().getCatalog().get())); assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(getSession().getCatalog().get())); }
@Test public void testExplainSetSessionWithUsing() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SET SESSION foo = ?") .build(); MaterializedResult result = computeActual(session, "EXPLAIN (TYPE LOGICAL) EXECUTE my_query USING 7"); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), "SET SESSION foo = 7"); }
@Test public void testDistinctWindow() { MaterializedResult actual = computeActual( "SELECT RANK() OVER (PARTITION BY orderdate ORDER BY COUNT(DISTINCT clerk)) rnk " + "FROM orders " + "GROUP BY orderdate, custkey " + "ORDER BY rnk " + "LIMIT 1"); MaterializedResult expected = resultBuilder(getSession(), BIGINT).row(1L).build(); assertEquals(actual, expected); }
@Test public void testExplainExecuteWithUsing() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT * FROM orders WHERE orderkey < ?") .build(); MaterializedResult result = computeActual(session, "EXPLAIN (TYPE LOGICAL) EXECUTE my_query USING 7"); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan("SELECT * FROM orders WHERE orderkey < 7", LOGICAL)); }
@Test public void testP4ApproxSetVarchar() { MaterializedResult actual = computeActual("SELECT cardinality(cast(approx_set(CAST(custkey AS VARCHAR)) AS P4HYPERLOGLOG)) FROM orders"); MaterializedResult expected = resultBuilder(getSession(), BIGINT) .row(1024L) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testEmptyApproxSet() { MaterializedResult actual = computeActual("SELECT cardinality(empty_approx_set())"); MaterializedResult expected = resultBuilder(getSession(), BIGINT) .row(0L) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testApproxSetOnlyNulls() { MaterializedResult actual = computeActual("SELECT cardinality(approx_set(null)) FROM orders"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row(new Object[] {null}) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testValuesWithUnusedColumns() { MaterializedResult actual = computeActual("SELECT foo FROM (values (1, 2)) a(foo, bar)"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row(1) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testDescribeInputWithAggregation() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT count(*) + ? FROM nation") .build(); MaterializedResult actual = computeActual(session, "DESCRIBE INPUT my_query"); MaterializedResult expected = resultBuilder(session, BIGINT, VARCHAR) .row(0, "bigint") .build(); assertEqualsIgnoreOrder(actual, expected); }