@Test public void testDescribeOutput() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT * FROM nation") .build(); MaterializedResult actual = computeActual(session, "DESCRIBE OUTPUT my_query"); MaterializedResult expected = resultBuilder(session, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, BIGINT, BOOLEAN) .row("nationkey", session.getCatalog().get(), session.getSchema().get(), "nation", "bigint", 8, false) .row("name", session.getCatalog().get(), session.getSchema().get(), "nation", "varchar(25)", 0, false) .row("regionkey", session.getCatalog().get(), session.getSchema().get(), "nation", "bigint", 8, false) .row("comment", session.getCatalog().get(), session.getSchema().get(), "nation", "varchar(152)", 0, false) .build(); assertEqualsIgnoreOrder(actual, expected); }
@Test public void testDescribeOutputNamedAndUnnamed() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT 1, name, regionkey AS my_alias FROM nation") .build(); MaterializedResult actual = computeActual(session, "DESCRIBE OUTPUT my_query"); MaterializedResult expected = resultBuilder(session, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, BIGINT, BOOLEAN) .row("_col0", "", "", "", "integer", 4, false) .row("name", session.getCatalog().get(), session.getSchema().get(), "nation", "varchar(25)", 0, false) .row("my_alias", session.getCatalog().get(), session.getSchema().get(), "nation", "bigint", 8, true) .build(); assertEqualsIgnoreOrder(actual, expected); }
@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 testTooManyParameters() { try { Session session = testSessionBuilder() .addPreparedStatement("my_query", "SELECT * FROM foo where col1 = ?") .build(); QUERY_PREPARER.prepareQuery(session, "EXECUTE my_query USING 1,2"); fail("expected exception"); } catch (SemanticException e) { assertEquals(e.getCode(), INVALID_PARAMETER_USAGE); } }
@Test public void testTooFewParameters() { try { Session session = testSessionBuilder() .addPreparedStatement("my_query", "SELECT ? FROM foo where col1 = ?") .build(); QUERY_PREPARER.prepareQuery(session, "EXECUTE my_query USING 1"); fail("expected exception"); } catch (SemanticException e) { assertEquals(e.getCode(), INVALID_PARAMETER_USAGE); } } }
@Test public void testDescribeInputNoParameters() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT * FROM nation") .build(); MaterializedResult actual = computeActual(session, "DESCRIBE INPUT my_query"); MaterializedResult expected = resultBuilder(session, UNKNOWN, UNKNOWN).build(); assertEquals(actual, expected); }
@Test public void testExecuteUsingWithSubquery() { String query = "SELECT ? in (SELECT orderkey FROM orders)"; Session session = Session.builder(getSession()) .addPreparedStatement("my_query", query) .build(); assertQuery(session, "EXECUTE my_query USING 10", "SELECT 10 in (SELECT orderkey FROM orders)"); }
@Test public void testExplainExecute() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT * FROM orders") .build(); MaterializedResult result = computeActual(session, "EXPLAIN (TYPE LOGICAL) EXECUTE my_query"); assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan("SELECT * FROM orders", LOGICAL)); }
@Test public void testPrepareNameExists() { Session session = testSessionBuilder() .addPreparedStatement("my_query", "SELECT bar, baz from foo") .build(); Query query = simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("foo"))); String sqlString = "PREPARE my_query FROM SELECT * FROM foo"; Map<String, String> statements = executePrepare("my_query", query, sqlString, session); assertEquals(statements, ImmutableMap.of("my_query", "SELECT *\nFROM\n foo\n")); }
@Test public void testDescribeOutputOnAliasedColumnsAndExpressions() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT count(*) AS this_is_aliased, 1 + 2 FROM nation") .build(); MaterializedResult actual = computeActual(session, "DESCRIBE OUTPUT my_query"); MaterializedResult expected = resultBuilder(session, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, BIGINT, BOOLEAN) .row("this_is_aliased", "", "", "", "bigint", 8, true) .row("_col1", "", "", "", "integer", 4, false) .build(); assertEqualsIgnoreOrder(actual, expected); }
@Test public void testExecuteStatement() { Session session = testSessionBuilder() .addPreparedStatement("my_query", "SELECT * FROM foo") .build(); PreparedQuery preparedQuery = QUERY_PREPARER.prepareQuery(session, "EXECUTE my_query"); assertEquals(preparedQuery.getStatement(), simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("foo")))); }
private void assertDescribeOutputEmpty(@Language("SQL") String sql) { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", sql) .build(); MaterializedResult actual = computeActual(session, "DESCRIBE OUTPUT my_query"); MaterializedResult expected = resultBuilder(session, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, BIGINT, BOOLEAN) .build(); assertEqualsIgnoreOrder(actual, expected); }
@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); }
@Test public void testExecuteUsing() { String query = "SELECT a + 1, count(?) FROM (VALUES 1, 2, 3, 2) t1(a) JOIN (VALUES 1, 2, 3, 4) t2(b) ON b < ? WHERE a < ? GROUP BY a + 1 HAVING count(1) > ?"; Session session = Session.builder(getSession()) .addPreparedStatement("my_query", query) .build(); assertQuery(session, "EXECUTE my_query USING 1, 5, 4, 0", "VALUES (2, 4), (3, 8), (4, 4)"); }
@Test public void testDeallocate() { Session session = testSessionBuilder() .addPreparedStatement("my_query", "SELECT bar, baz FROM foo") .build(); Set<String> statements = executeDeallocate("my_query", "DEALLOCATE PREPARE my_query", session); assertEquals(statements, ImmutableSet.of("my_query")); }
private void assertDescribeOutputRowCount(@Language("SQL") String sql) { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", sql) .build(); MaterializedResult actual = computeActual(session, "DESCRIBE OUTPUT my_query"); MaterializedResult expected = resultBuilder(session, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, BIGINT, BOOLEAN) .row("rows", "", "", "", "bigint", 8, false) .build(); assertEqualsIgnoreOrder(actual, expected); }
@Test public void testDescribeOutputShowTables() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SHOW TABLES") .build(); MaterializedResult actual = computeActual(session, "DESCRIBE OUTPUT my_query"); MaterializedResult expected = resultBuilder(session, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, BIGINT, BOOLEAN) .row("Table", session.getCatalog().get(), "information_schema", "tables", "varchar", 0, true) .build(); assertEqualsIgnoreOrder(actual, expected); }
@Test public void testExecuteUsingComplexJoinCriteria() { String query = "SELECT * FROM (VALUES 1) t(a) JOIN (VALUES 2) u(a) ON t.a + u.a < ?"; Session session = Session.builder(getSession()) .addPreparedStatement("my_query", query) .build(); assertQuery(session, "EXECUTE my_query USING 5", "VALUES (1, 2)"); }
@Test public void testExecute() { Session session = Session.builder(getSession()) .addPreparedStatement("my_query", "SELECT 123, 'abc'") .build(); assertQuery(session, "EXECUTE my_query", "SELECT 123, 'abc'"); }
@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)); }