@Test public void testRowNumberFilterAndLimit() { MaterializedResult actual = computeActual("" + "SELECT * FROM (" + "SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" + "FROM (VALUES (1), (2), (1), (2)) t (a)) t WHERE rn < 2 LIMIT 2"); MaterializedResult expected = resultBuilder(getSession(), BIGINT, BIGINT) .row(1, 1L) .row(2, 1L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); actual = computeActual("" + "SELECT * FROM (" + "SELECT a, row_number() OVER (PARTITION BY a) rn\n" + "FROM (VALUES (1), (2), (1), (2), (1)) t (a)) t WHERE rn < 3 LIMIT 2"); expected = resultBuilder(getSession(), BIGINT, BIGINT) .row(1, 1L) .row(1, 2L) .row(2, 1L) .row(2, 2L) .build(); assertEquals(actual.getMaterializedRows().size(), 2); assertContains(expected, actual); }
protected static void assertQueryFails(QueryRunner queryRunner, Session session, @Language("SQL") String sql, @Language("RegExp") String expectedMessageRegExp) { try { queryRunner.execute(session, sql); fail(format("Expected query to fail: %s", sql)); } catch (RuntimeException ex) { assertExceptionMessage(sql, ex, expectedMessageRegExp); } }
@Test public void testLimit() { MaterializedResult actual = computeActual("SELECT orderkey FROM orders LIMIT 10"); MaterializedResult all = computeExpected("SELECT orderkey FROM orders", actual.getTypes()); assertEquals(actual.getMaterializedRows().size(), 10); assertContains(all, actual); }
@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 testColumnNameAmbiguity() { session.execute("CREATE KEYSPACE keyspace_5 WITH REPLICATION = {'class':'SimpleStrategy', 'replication_factor': 1}"); assertContainsEventually(() -> execute("SHOW SCHEMAS FROM cassandra"), resultBuilder(getSession(), createUnboundedVarcharType()) .row("keyspace_5") .build(), new Duration(1, MINUTES)); session.execute("CREATE TABLE keyspace_5.table_5 (\"CoLuMn_5\" bigint PRIMARY KEY, \"cOlUmN_5\" bigint)"); assertContainsEventually(() -> execute("SHOW TABLES FROM cassandra.keyspace_5"), resultBuilder(getSession(), createUnboundedVarcharType()) .row("table_5") .build(), new Duration(1, MINUTES)); assertQueryFailsEventually( "SHOW COLUMNS FROM cassandra.keyspace_5.table_5", "More than one column has been found for the case insensitive column name: column_5 -> \\(CoLuMn_5, cOlUmN_5\\)", new Duration(1, MINUTES)); assertQueryFailsEventually( "SELECT * FROM cassandra.keyspace_5.table_5", "More than one column has been found for the case insensitive column name: column_5 -> \\(CoLuMn_5, cOlUmN_5\\)", new Duration(1, MINUTES)); session.execute("DROP KEYSPACE keyspace_5"); }
@Test public void testShowSchemas() { MaterializedResult actualSchemas = computeActual("SHOW SCHEMAS").toTestTypes(); MaterializedResult.Builder resultBuilder = MaterializedResult.resultBuilder(getQueryRunner().getDefaultSession(), VARCHAR) .row(getQueryRunner().getDefaultSession().getSchema().orElse("tpch")); assertContains(actualSchemas, resultBuilder.build()); }
@Test public void testUppercaseNameEscaped() { /* * If an identifier is escaped with double quotes it is stored verbatim * * http://docs.datastax.com/en/cql/3.1/cql/cql_reference/ucase-lcase_r.html */ session.execute("CREATE KEYSPACE \"KEYSPACE_2\" WITH REPLICATION = {'class':'SimpleStrategy', 'replication_factor': 1}"); assertContainsEventually(() -> execute("SHOW SCHEMAS FROM cassandra"), resultBuilder(getSession(), createUnboundedVarcharType()) .row("keyspace_2") .build(), new Duration(1, MINUTES)); session.execute("CREATE TABLE \"KEYSPACE_2\".\"TABLE_2\" (\"COLUMN_2\" bigint PRIMARY KEY)"); assertContainsEventually(() -> execute("SHOW TABLES FROM cassandra.keyspace_2"), resultBuilder(getSession(), createUnboundedVarcharType()) .row("table_2") .build(), new Duration(1, MINUTES)); assertContains(execute("SHOW COLUMNS FROM cassandra.keyspace_2.table_2"), resultBuilder(getSession(), createUnboundedVarcharType(), createUnboundedVarcharType(), createUnboundedVarcharType(), createUnboundedVarcharType()) .row("column_2", "bigint", "", "") .build()); execute("INSERT INTO \"KEYSPACE_2\".\"TABLE_2\" (\"COLUMN_2\") VALUES (1)"); assertEquals(execute("SELECT column_2 FROM cassandra.keyspace_2.table_2").getRowCount(), 1); assertUpdate("DROP TABLE cassandra.keyspace_2.table_2"); // when an identifier is unquoted the lowercase and uppercase spelling may be used interchangeable session.execute("DROP KEYSPACE \"KEYSPACE_2\""); }
@Test public void testNodeCount() { String name = STANDARD_NAMES.iterator().next(); MaterializedResult actual = computeActual("SELECT node_id FROM system.runtime.nodes"); MaterializedResult expected = computeActual(format("SELECT DISTINCT node FROM \"%s\"", name)); assertEqualsIgnoreOrder(actual, expected); }
@Test public void testViewMetadata() @Language("SQL") String query = "SELECT BIGINT '123' x, 'foo' y"; assertUpdate("CREATE VIEW meta_test_view AS " + query); getSession().getSchema().get())); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("customer", "BASE TABLE") .row("lineitem", "BASE TABLE") .build(); assertContains(actual, expected); assertContains(actual, expected); getSession().getSchema().get())); expected = resultBuilder(getSession(), actual.getTypes()) .build(); assertContains(actual, expected);
@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); }
@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 testArrayShuffle() { List<Integer> expected = IntStream.rangeClosed(1, 500).boxed().collect(toList()); Set<List<Integer>> distinctResults = new HashSet<>(); distinctResults.add(expected); for (int i = 0; i < 3; i++) { MaterializedResult results = computeActual(format("SELECT shuffle(ARRAY %s) FROM orders LIMIT 10", expected)); List<MaterializedRow> rows = results.getMaterializedRows(); assertEquals(rows.size(), 10); for (MaterializedRow row : rows) { List<Integer> actual = (List<Integer>) row.getField(0); // check if the result is a correct permutation assertEqualsIgnoreOrder(actual, expected); distinctResults.add(actual); } } assertTrue(distinctResults.size() >= 24, "shuffle must produce at least 24 distinct results"); }
@Test public void testDuplicateColumnsInWindowOrderByClause() { MaterializedResult actual = computeActual("SELECT a, row_number() OVER (ORDER BY a ASC, a DESC) FROM (VALUES 3, 2, 1) t(a)"); MaterializedResult expected = resultBuilder(getSession(), BIGINT, BIGINT) .row(1, 1L) .row(2, 2L) .row(3, 3L) .build(); assertEqualsIgnoreOrder(actual, expected); }
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); }
private static void assertQuery( QueryRunner actualQueryRunner, Session session, @Language("SQL") String actual, H2QueryRunner h2QueryRunner, @Language("SQL") String expected, boolean ensureOrdering, boolean compareUpdate, MaterializedResultWithPlan resultWithPlan = actualQueryRunner.executeWithPlan(session, actual, WarningCollector.NOOP); queryPlan = resultWithPlan.getQueryPlan(); actualResults = resultWithPlan.getMaterializedResult().toTestTypes(); actualResults = actualQueryRunner.execute(session, actual).toTestTypes(); MaterializedResult expectedResults = null; try { expectedResults = h2QueryRunner.execute(session, expected, actualResults.getTypes()); assertEqualsIgnoreOrder(actualRows, expectedRows, "For query: \n " + actual);
private void verifyPartitionedBucketedTableAsFewRows(HiveStorageFormat storageFormat, String tableName) { TableMetadata tableMetadata = getTableMetadata(catalog, TPCH_SCHEMA, tableName); assertEquals(tableMetadata.getMetadata().getProperties().get(STORAGE_FORMAT_PROPERTY), storageFormat); assertEquals(tableMetadata.getMetadata().getProperties().get(PARTITIONED_BY_PROPERTY), ImmutableList.of("partition_key")); assertEquals(tableMetadata.getMetadata().getProperties().get(BUCKETED_BY_PROPERTY), ImmutableList.of("bucket_key")); assertEquals(tableMetadata.getMetadata().getProperties().get(BUCKET_COUNT_PROPERTY), 11); List<?> partitions = getPartitions(tableName); assertEquals(partitions.size(), 3); MaterializedResult actual = computeActual("SELECT * from " + tableName); MaterializedResult expected = resultBuilder(getSession(), canonicalizeType(createUnboundedVarcharType()), canonicalizeType(createUnboundedVarcharType()), canonicalizeType(createUnboundedVarcharType())) .row("a", "b", "c") .row("aa", "bb", "cc") .row("aaa", "bbb", "ccc") .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
queryRunner.createCatalog(HIVE_BUCKETED_CATALOG, HIVE_CATALOG, hiveBucketedProperties); copyTpchTables(queryRunner, "tpch", TINY_SCHEMA_NAME, createSession(), tables); copyTpchTablesBucketed(queryRunner, "tpch", TINY_SCHEMA_NAME, createBucketedSession(), tables);
public static void assertContainsEventually(Supplier<MaterializedResult> all, MaterializedResult expectedSubset, Duration timeout) { long start = System.nanoTime(); while (!Thread.currentThread().isInterrupted()) { try { assertContains(all.get(), expectedSubset); return; } catch (AssertionError e) { if (nanosSince(start).compareTo(timeout) > 0) { throw e; } } sleepUninterruptibly(50, MILLISECONDS); } }
public static void assertEqualsIgnoreOrder(Iterable<?> actual, Iterable<?> expected) { assertEqualsIgnoreOrder(actual, expected, null); }
@Test public void testRowNumberMultipleFilters() { MaterializedResult actual = computeActual("" + "SELECT * FROM (" + " SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" + " FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " + "WHERE rn < 3 AND rn % 2 = 0 AND a = 2 LIMIT 2"); MaterializedResult expected = resultBuilder(getSession(), BIGINT, BIGINT) .row(2, 2L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }