@Test public void testParsingError() { assertQueryFails("SELECT foo FROM", "line 1:16: mismatched input '<EOF>'. Expecting: .*"); }
@Test(expectedExceptions = Exception.class, expectedExceptionsMessageRegExp = "line 1:32: Column 'x' cannot be resolved") public void testExplainValidateThrows() { computeActual("EXPLAIN (TYPE VALIDATE) SELECT x"); }
@Test public void testDescribeOutputNonSelect() { assertDescribeOutputRowCount("CREATE TABLE foo AS SELECT * FROM nation"); assertDescribeOutputRowCount("DELETE FROM orders"); assertDescribeOutputEmpty("CALL foo()"); assertDescribeOutputEmpty("SET SESSION optimize_hash_generation=false"); assertDescribeOutputEmpty("RESET SESSION optimize_hash_generation"); assertDescribeOutputEmpty("START TRANSACTION"); assertDescribeOutputEmpty("COMMIT"); assertDescribeOutputEmpty("ROLLBACK"); assertDescribeOutputEmpty("GRANT INSERT ON foo TO bar"); assertDescribeOutputEmpty("REVOKE INSERT ON foo FROM bar"); assertDescribeOutputEmpty("CREATE SCHEMA foo"); assertDescribeOutputEmpty("ALTER SCHEMA foo RENAME TO bar"); assertDescribeOutputEmpty("DROP SCHEMA foo"); assertDescribeOutputEmpty("CREATE TABLE foo (x bigint)"); assertDescribeOutputEmpty("ALTER TABLE foo ADD COLUMN y bigint"); assertDescribeOutputEmpty("ALTER TABLE foo RENAME TO bar"); assertDescribeOutputEmpty("DROP TABLE foo"); assertDescribeOutputEmpty("CREATE VIEW foo AS SELECT * FROM nation"); assertDescribeOutputEmpty("DROP VIEW foo"); assertDescribeOutputEmpty("PREPARE test FROM SELECT * FROM orders"); assertDescribeOutputEmpty("EXECUTE test"); assertDescribeOutputEmpty("DEALLOCATE PREPARE test"); }
@Test public void testOutputInEnforceSingleRow() { assertQuery("SELECT count(*) FROM (SELECT (SELECT 1))"); assertQuery("SELECT * FROM (SELECT (SELECT 1))"); assertQueryFails( "SELECT * FROM (SELECT (SELECT 1, 2))", "line 1:23: Multiple columns returned by subquery are not yet supported. Found 2"); }
@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 testShowCatalogsLike() { MaterializedResult result = computeActual(format("SHOW CATALOGS LIKE '%s'", getSession().getCatalog().get())); assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(getSession().getCatalog().get())); }
@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 testDefaultExplainTextFormat() { String query = "SELECT * FROM orders"; MaterializedResult result = computeActual("EXPLAIN " + query); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL)); }
@Test public void testOrderByLimit() { assertQueryOrdered("SELECT custkey, orderstatus FROM orders ORDER BY orderkey DESC LIMIT 10"); }
@Test public void testIntersectWithAggregation() { assertQuery("SELECT COUNT(*) FROM nation INTERSECT SELECT COUNT(regionkey) FROM nation HAVING SUM(regionkey) IS NOT NULL"); assertQuery("SELECT SUM(nationkey), COUNT(name) FROM (SELECT nationkey,name FROM nation INTERSECT SELECT regionkey, name FROM nation) n"); assertQuery("SELECT COUNT(*) * 2 FROM nation INTERSECT (SELECT SUM(nationkey) FROM nation GROUP BY regionkey ORDER BY 1 LIMIT 2)"); assertQuery("SELECT COUNT(a) FROM (SELECT nationkey AS a FROM (SELECT nationkey FROM nation INTERSECT SELECT regionkey FROM nation) n1 INTERSECT SELECT regionkey FROM nation) n2"); assertQuery("SELECT COUNT(*), SUM(2), regionkey FROM (SELECT nationkey, regionkey FROM nation INTERSECT SELECT regionkey, regionkey FROM nation) n GROUP BY regionkey"); assertQuery("SELECT COUNT(*) FROM (SELECT nationkey FROM nation INTERSECT SELECT 2) n1 INTERSECT SELECT regionkey FROM nation"); }
@Override protected MaterializedResult computeExpected(@Language("SQL") String sql, List<? extends Type> resultTypes) { determinismChecker.checkPlanIsDeterministic(sql); return super.computeExpected(sql, resultTypes); }
@Test public void testAccessControl() { assertAccessDenied("INSERT INTO orders SELECT * FROM orders", "Cannot insert into table .*.orders.*", privilege("orders", INSERT_TABLE)); assertAccessDenied("DELETE FROM orders", "Cannot delete from table .*.orders.*", privilege("orders", DELETE_TABLE)); assertAccessDenied("CREATE TABLE foo AS SELECT * FROM orders", "Cannot create table .*.foo.*", privilege("foo", CREATE_TABLE)); assertAccessDenied("SELECT * FROM nation", "Cannot select from columns \\[nationkey, regionkey, name, comment\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessDenied("SELECT * FROM (SELECT * FROM nation)", "Cannot select from columns \\[nationkey, regionkey, name, comment\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessDenied("SELECT name FROM (SELECT * FROM nation)", "Cannot select from columns \\[nationkey, regionkey, name, comment\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessAllowed("SELECT name FROM nation", privilege("nationkey", SELECT_COLUMN)); assertAccessDenied("SELECT n1.nationkey, n2.regionkey FROM nation n1, nation n2", "Cannot select from columns \\[nationkey, regionkey\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessDenied("SELECT count(name) as c FROM nation where comment > 'abc' GROUP BY regionkey having max(nationkey) > 10", "Cannot select from columns \\[nationkey, regionkey, name, comment\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessDenied("SELECT 1 FROM region, nation where region.regionkey = nation.nationkey", "Cannot select from columns \\[nationkey\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessDenied("SELECT count(*) FROM nation", "Cannot select from columns \\[\\] in table .*.nation.*", privilege("nation", SELECT_COLUMN)); assertAccessDenied("WITH t1 AS (SELECT * FROM nation) SELECT * FROM t1", "Cannot select from columns \\[nationkey, regionkey, name, comment\\] in table .*.nation.*", privilege("nationkey", SELECT_COLUMN)); assertAccessAllowed("SELECT name AS my_alias FROM nation", privilege("my_alias", SELECT_COLUMN)); assertAccessAllowed("SELECT my_alias from (SELECT name AS my_alias FROM nation)", privilege("my_alias", SELECT_COLUMN)); assertAccessDenied("SELECT name AS my_alias FROM nation", "Cannot select from columns \\[name\\] in table .*.nation.*", privilege("name", SELECT_COLUMN)); }
@Test public void testUndistributedOrderBy() { Session undistributedOrderBy = Session.builder(getSession()) .setSystemProperty(DISTRIBUTED_SORT, "false") .build(); assertQueryOrdered(undistributedOrderBy, "SELECT orderstatus FROM orders ORDER BY orderstatus"); }
private void assertExplainDdl(String query) { assertExplainDdl(query, query); }
@Test public void testTopNUnpartitionedWindow() { MaterializedResult actual = computeActual("" + "SELECT * FROM (\n" + " SELECT row_number() OVER (ORDER BY orderkey) rn, orderkey, orderstatus\n" + " FROM orders\n" + ") WHERE rn <= 5"); String sql = "SELECT row_number() OVER (), orderkey, orderstatus FROM orders ORDER BY orderkey LIMIT 5"; MaterializedResult expected = computeExpected(sql, actual.getTypes()); assertEquals(actual, expected); }
@Test public void testWindowFunctionWithGroupBy() { MaterializedResult actual = computeActual("" + "SELECT *, rank() OVER (PARTITION BY x)\n" + "FROM (SELECT 'foo' x)\n" + "GROUP BY 1"); MaterializedResult expected = resultBuilder(getSession(), createVarcharType(3), BIGINT) .row("foo", 1L) .build(); assertEquals(actual, expected); }
@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 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 testOrderByExpressionWithLimit() { assertQueryOrdered("SELECT custkey, orderstatus FROM orders ORDER BY orderkey + 1 DESC LIMIT 10"); }
@Test public void testUnion() { assertQuery("SELECT orderkey FROM orders UNION SELECT custkey FROM orders"); assertQuery("SELECT 123 UNION DISTINCT SELECT 123 UNION ALL SELECT 123"); assertQuery("SELECT NULL UNION SELECT NULL"); assertQuery("SELECT NULL, NULL UNION ALL SELECT NULL, NULL FROM nation"); assertQuery("SELECT 'x', 'y' UNION ALL SELECT name, name FROM nation"); // mixed single-node vs fixed vs source-distributed assertQuery("SELECT orderkey FROM orders UNION ALL SELECT 123 UNION ALL (SELECT custkey FROM orders GROUP BY custkey)"); }