@Test public void testExampleSystemTable() { assertQuery("SELECT name FROM sys.example", "SELECT 'test' AS name"); MaterializedResult result = computeActual("SHOW SCHEMAS"); assertTrue(result.getOnlyColumnAsSet().containsAll(ImmutableSet.of("sf100", "tiny", "sys"))); result = computeActual("SHOW TABLES FROM sys"); assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of("example")); }
@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 testShowCatalogs() { MaterializedResult result = computeActual("SHOW CATALOGS"); assertTrue(result.getOnlyColumnAsSet().contains(getSession().getCatalog().get())); }
private void assertExplainDdl(String query, String expected) { MaterializedResult result = computeActual("EXPLAIN " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), expected); }
@Test public void testShowTablesLike() { assertThat(computeActual("SHOW TABLES LIKE 'or%'").getOnlyColumnAsSet()) .contains("orders") .allMatch(tableName -> ((String) tableName).startsWith("or")); }
@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 testDefaultExplainTextFormat() { String query = "SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL)); }
@Test public void testDefaultExplainGraphvizFormat() { String query = "SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN (FORMAT GRAPHVIZ) " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getGraphvizExplainPlan(query, LOGICAL)); }
@Test public void testExplainOfExplain() { String query = "EXPLAIN SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL)); }
@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 testLogicalExplainTextFormat() { String query = "SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL, FORMAT TEXT) " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL)); }
@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 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 testShowCatalogsLike() { MaterializedResult result = computeActual(format("SHOW CATALOGS LIKE '%s'", getSession().getCatalog().get())); assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(getSession().getCatalog().get())); }
@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 testShowSchemas() { MaterializedResult result = computeActual("SHOW SCHEMAS"); assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(INFORMATION_SCHEMA, JMX_SCHEMA_NAME, HISTORY_SCHEMA_NAME)); }
@Test public void testShowTables() { Set<String> standardNamesLower = STANDARD_NAMES.stream() .map(name -> name.toLowerCase(Locale.ENGLISH)) .collect(toImmutableSet()); MaterializedResult result = computeActual("SHOW TABLES"); assertTrue(result.getOnlyColumnAsSet().containsAll(standardNamesLower)); }
@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 testExplainOfCreateTableAs() { String query = "CREATE TABLE copy_orders AS SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL)); }
@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)); }