@Test public void testGroupByNan() { MaterializedResult actual = computeActual("SELECT * FROM (VALUES nan(), nan(), nan()) GROUP BY 1"); assertTrue(Double.isNaN((Double) actual.getOnlyValue())); }
@Test public void testDistinctNan() { MaterializedResult actual = computeActual("SELECT DISTINCT a/a FROM (VALUES (0.0e0), (0.0e0)) x (a)"); assertTrue(Double.isNaN((Double) actual.getOnlyValue())); }
@Test public void testGroupByNanMap() { MaterializedResult actual = computeActual("SELECT MAP_KEYS(x)[1] FROM (VALUES MAP(ARRAY[nan()], ARRAY[ARRAY[1]]), MAP(ARRAY[nan()], ARRAY[ARRAY[2]])) t(x) GROUP BY 1"); assertTrue(Double.isNaN((Double) actual.getOnlyValue())); }
private void assertExplainAnalyze(@Language("SQL") String query) { String value = (String) computeActual(query).getOnlyValue(); assertTrue(value.matches("(?s:.*)CPU:.*, Input:.*, Output(?s:.*)"), format("Expected output to contain \"CPU:.*, Input:.*, Output\", but it is %s", value)); // TODO: check that rendered plan is as expected, once stats are collected in a consistent way // assertTrue(value.contains("Cost: "), format("Expected output to contain \"Cost: \", but it is %s", value)); }
@Test public void testExplainValidate() { MaterializedResult result = computeActual("EXPLAIN (TYPE VALIDATE) SELECT 1"); assertEquals(result.getOnlyValue(), true); }
protected Object computeScalar(@Language("SQL") String sql) { return computeActual(sql).getOnlyValue(); }
@Test public void testDateToTimestampCoercion() { // allow running tests with a connector that supports TIMESTAMP but not DATE // ordinary date MaterializedResult rows = h2QueryRunner.execute(TEST_SESSION, "SELECT DATE '2018-01-13'", ImmutableList.of(TIMESTAMP)); assertEquals(rows.getOnlyValue(), LocalDate.of(2018, 1, 13).atStartOfDay()); // date, which midnight was skipped in JVM zone LocalDate forwardOffsetChangeAtMidnightInJvmZone = LocalDate.of(1970, 1, 1); checkState(ZoneId.systemDefault().getRules().getValidOffsets(forwardOffsetChangeAtMidnightInJvmZone.atStartOfDay()).size() == 0, "This test assumes certain JVM time zone"); rows = h2QueryRunner.execute(TEST_SESSION, DateTimeFormatter.ofPattern("'SELECT DATE '''uuuu-MM-dd''").format(forwardOffsetChangeAtMidnightInJvmZone), ImmutableList.of(TIMESTAMP)); assertEquals(rows.getOnlyValue(), forwardOffsetChangeAtMidnightInJvmZone.atStartOfDay()); } }
@Test public void testLegacyLogFunctionEnabled() { try (QueryRunner queryRunner = createQueryRunner(true)) { MaterializedResult result = queryRunner.execute(QUERY); assertEquals(result.getOnlyValue(), 2.0); } }
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(timeOut = 60_000) public void testQuerySystemTableResourceGroup() throws Exception { QueryId firstQuery = createQuery(queryRunner, dashboardSession(), LONG_LASTING_QUERY); waitForQueryState(queryRunner, firstQuery, RUNNING); MaterializedResult result = queryRunner.execute("SELECT resource_group_id FROM system.runtime.queries WHERE source = 'dashboard'"); assertEquals(result.getOnlyValue(), ImmutableList.of("global", "user-user", "dashboard-user")); }
@Test public void testCreateAvroTableWithSchemaUrl() throws Exception { String tableName = "test_create_avro_table_with_schema_url"; File schemaFile = createAvroSchemaFile(); String createTableSql = getAvroCreateTableSql(tableName, schemaFile.getAbsolutePath()); String expectedShowCreateTable = getAvroCreateTableSql(tableName, schemaFile.toURI().toString()); assertUpdate(createTableSql); try { MaterializedResult actual = computeActual(format("SHOW CREATE TABLE %s", tableName)); assertEquals(actual.getOnlyValue(), expectedShowCreateTable); } finally { assertUpdate(format("DROP TABLE %s", tableName)); verify(schemaFile.delete(), "cannot delete temporary file: %s", schemaFile); } }
@Test public void testShowCreateTable() { queryRunner.execute("CREATE TABLE IF NOT EXISTS test_show_create_table (\n" + "id INT WITH (primary_key=true),\n" + "user_name VARCHAR\n" + ") WITH (\n" + " partition_by_hash_columns = ARRAY['id'],\n" + " partition_by_hash_buckets = 2," + " number_of_replicas = 1\n" + ")"); MaterializedResult result = queryRunner.execute("SHOW CREATE TABLE test_show_create_table"); String sqlStatement = (String) result.getOnlyValue(); String tableProperties = sqlStatement.split("\\)\\s*WITH\\s*\\(")[1]; assertTableProperty(tableProperties, "number_of_replicas", "1"); assertTableProperty(tableProperties, "partition_by_hash_columns", Pattern.quote("ARRAY['id']")); assertTableProperty(tableProperties, "partition_by_hash_buckets", "2"); }
@Test public void testCreateExternalTable() throws Exception { File tempDir = createTempDir(); File dataFile = new File(tempDir, "test.txt"); Files.write("hello\nworld\n", dataFile, UTF_8); @Language("SQL") String createTableSql = format("" + "CREATE TABLE %s.%s.test_create_external (\n" + " name varchar\n" + ")\n" + "WITH (\n" + " external_location = '%s',\n" + " format = 'TEXTFILE'\n" + ")", getSession().getCatalog().get(), getSession().getSchema().get(), new Path(tempDir.toURI().toASCIIString()).toString()); assertUpdate(createTableSql); MaterializedResult actual = computeActual("SHOW CREATE TABLE test_create_external"); assertEquals(actual.getOnlyValue(), createTableSql); actual = computeActual("SELECT name FROM test_create_external"); assertEquals(actual.getOnlyColumnAsSet(), ImmutableSet.of("hello", "world")); assertUpdate("DROP TABLE test_create_external"); // file should still exist after drop assertFile(dataFile); deleteRecursively(tempDir.toPath(), ALLOW_INSECURE); }
@Test public void testScaleWriters() { try { // small table that will only have one writer assertUpdate( Session.builder(getSession()) .setSystemProperty("scale_writers", "true") .setSystemProperty("writer_min_size", "32MB") .build(), "CREATE TABLE scale_writers_small AS SELECT * FROM tpch.tiny.orders", (long) computeActual("SELECT count(*) FROM tpch.tiny.orders").getOnlyValue()); assertEquals(computeActual("SELECT count(DISTINCT \"$path\") FROM scale_writers_small").getOnlyValue(), 1L); // large table that will scale writers to multiple machines assertUpdate( Session.builder(getSession()) .setSystemProperty("scale_writers", "true") .setSystemProperty("writer_min_size", "1MB") .build(), "CREATE TABLE scale_writers_large WITH (format = 'RCBINARY') AS SELECT * FROM tpch.sf1.orders", (long) computeActual("SELECT count(*) FROM tpch.sf1.orders").getOnlyValue()); long files = (long) computeScalar("SELECT count(DISTINCT \"$path\") FROM scale_writers_large"); long workers = (long) computeScalar("SELECT count(*) FROM system.runtime.nodes"); assertThat(files).isBetween(2L, workers); } finally { assertUpdate("DROP TABLE IF EXISTS scale_writers_large"); assertUpdate("DROP TABLE IF EXISTS scale_writers_small"); } }
@Test public void testGroupByNanMap() { MaterializedResult actual = computeActual("SELECT MAP_KEYS(x)[1] FROM (VALUES MAP(ARRAY[nan()], ARRAY[ARRAY[1]]), MAP(ARRAY[nan()], ARRAY[ARRAY[2]])) t(x) GROUP BY 1"); assertTrue(Double.isNaN((Double) actual.getOnlyValue())); }
@Test public void testDistinctNan() { MaterializedResult actual = computeActual("SELECT DISTINCT a/a FROM (VALUES (0.0e0), (0.0e0)) x (a)"); assertTrue(Double.isNaN((Double) actual.getOnlyValue())); }
@Test public void testGroupByNan() { MaterializedResult actual = computeActual("SELECT * FROM (VALUES nan(), nan(), nan()) GROUP BY 1"); assertTrue(Double.isNaN((Double) actual.getOnlyValue())); }
private void assertExplainAnalyze(@Language("SQL") String query) { String value = (String) computeActual(query).getOnlyValue(); assertTrue(value.matches("(?s:.*)CPU:.*, Input:.*, Output(?s:.*)"), format("Expected output to contain \"CPU:.*, Input:.*, Output\", but it is %s", value)); // TODO: check that rendered plan is as expected, once stats are collected in a consistent way // assertTrue(value.contains("Cost: "), format("Expected output to contain \"Cost: \", but it is %s", value)); }
@Test public void testLegacyLogFunctionEnabled() { try (QueryRunner queryRunner = createQueryRunner(true)) { MaterializedResult result = queryRunner.execute(QUERY); assertEquals(result.getOnlyValue(), 2.0); } }
@Test(timeOut = 60_000) public void testQuerySystemTableResourceGroup() throws Exception { QueryId firstQuery = createQuery(queryRunner, dashboardSession(), LONG_LASTING_QUERY); waitForQueryState(queryRunner, firstQuery, RUNNING); MaterializedResult result = queryRunner.execute("SELECT resource_group_id FROM system.runtime.queries WHERE source = 'dashboard'"); assertEquals(result.getOnlyValue(), ImmutableList.of("global", "user-user", "dashboard-user")); }