@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 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 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 testLimitWithAggregation() { MaterializedResult actual = computeActual("SELECT custkey, SUM(CAST(totalprice * 100 AS BIGINT)) FROM orders GROUP BY custkey LIMIT 10"); MaterializedResult all = computeExpected("SELECT custkey, SUM(CAST(totalprice * 100 AS BIGINT)) FROM orders GROUP BY custkey", actual.getTypes()); assertEquals(actual.getMaterializedRows().size(), 10); assertContains(all, actual); }
@Test public void testLimitInInlineView() { MaterializedResult actual = computeActual("SELECT orderkey FROM (SELECT orderkey FROM orders LIMIT 100) T LIMIT 10"); MaterializedResult all = computeExpected("SELECT orderkey FROM orders", actual.getTypes()); assertEquals(actual.getMaterializedRows().size(), 10); assertContains(all, actual); }
private void assertCastWithJsonParse(String json, String castSqlType, Type expectedType, Object expected) { String query = "" + "SELECT CAST(JSON_PARSE(col) AS " + castSqlType + ") " + "FROM (VALUES('" + json + "')) AS t(col)"; // building queries with VALUES to avoid constant folding MaterializedResult result = runner.execute(query); assertEquals(result.getTypes().size(), 1); assertEquals(result.getTypes().get(0), expectedType); assertEquals(result.getOnlyValue(), expected); }
@Test public void testLimitPushDown() { MaterializedResult actual = computeActual( "(TABLE orders ORDER BY orderkey) UNION ALL " + "SELECT * FROM orders WHERE orderstatus = 'F' UNION ALL " + "(TABLE orders ORDER BY orderkey LIMIT 20) UNION ALL " + "(TABLE orders LIMIT 5) UNION ALL " + "TABLE orders LIMIT 10"); MaterializedResult all = computeExpected("SELECT * FROM orders", actual.getTypes()); assertEquals(actual.getMaterializedRows().size(), 10); assertContains(all, actual); }
@Test public void testTableSampleBernoulliBoundaryValues() { MaterializedResult fullSample = computeActual("SELECT orderkey FROM orders TABLESAMPLE BERNOULLI (100)"); MaterializedResult emptySample = computeActual("SELECT orderkey FROM orders TABLESAMPLE BERNOULLI (0)"); MaterializedResult all = computeExpected("SELECT orderkey FROM orders", fullSample.getTypes()); assertContains(all, fullSample); assertEquals(emptySample.getMaterializedRows().size(), 0); }
@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 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 testApproxSetWithNulls() { MaterializedResult actual = computeActual("SELECT cardinality(approx_set(IF(orderstatus = 'O', custkey))) FROM orders"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row(1001L) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testP4ApproxSetOnlyNulls() { MaterializedResult actual = computeActual("SELECT cardinality(cast(approx_set(null) AS P4HYPERLOGLOG)) FROM orders"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row(new Object[] {null}) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testP4ApproxSetWithNulls() { MaterializedResult actual = computeActual("SELECT cardinality(cast(approx_set(IF(orderstatus = 'O', custkey)) AS P4HYPERLOGLOG)) FROM orders"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row(1001L) .build(); assertEquals(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testApproximateCountDistinctGroupBy() { MaterializedResult actual = computeActual("SELECT orderstatus, approx_distinct(custkey) FROM orders GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("O", 990L) .row("F", 990L) .row("P", 303L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testApproximateCountDistinctGroupByWithStandardError() { MaterializedResult actual = computeActual("SELECT orderstatus, approx_distinct(custkey, 0.023) FROM orders GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("O", 990L) .row("F", 990L) .row("P", 303L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testApproxSetBigintGroupBy() { MaterializedResult actual = computeActual("" + "SELECT orderstatus, cardinality(approx_set(custkey)) " + "FROM orders " + "GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("O", 1001L) .row("F", 998L) .row("P", 304L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testApproxSetGroupByWithOnlyNullsInOneGroup() { MaterializedResult actual = computeActual("" + "SELECT orderstatus, cardinality(approx_set(IF(orderstatus != 'O', custkey))) " + "FROM orders " + "GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("O", null) .row("F", 998L) .row("P", 304L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testMergeHyperLogLogGroupByWithNulls() { MaterializedResult actual = computeActual("" + "SELECT orderstatus, cardinality(merge(create_hll(IF(orderstatus != 'O', custkey)))) " + "FROM orders " + "GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("O", null) .row("F", 998L) .row("P", 304L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
@Test public void testP4ApproxSetVarcharGroupBy() { MaterializedResult actual = computeActual("" + "SELECT orderstatus, cardinality(cast(approx_set(CAST(custkey AS VARCHAR)) AS P4HYPERLOGLOG)) " + "FROM orders " + "GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(getSession(), actual.getTypes()) .row("O", 1021L) .row("F", 1019L) .row("P", 302L) .build(); assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows()); }
public static void assertOperatorEquals(OperatorFactory operatorFactory, DriverContext driverContext, List<Page> input, MaterializedResult expected, boolean hashEnabled, List<Integer> hashChannels) { List<Page> pages = toPages(operatorFactory, driverContext, input); if (hashEnabled && !hashChannels.isEmpty()) { // Drop the hashChannel for all pages pages = dropChannel(pages, hashChannels); } MaterializedResult actual = toMaterializedResult(driverContext.getSession(), expected.getTypes(), pages); assertEquals(actual, expected); }