@Test public void testLimit() { assertEquals(computeActual("SELECT * FROM orders LIMIT 10").getRowCount(), 10); }
protected static void assertQueryReturnsEmptyResult(QueryRunner queryRunner, Session session, @Language("SQL") String sql) { try { MaterializedResult results = queryRunner.execute(session, sql).toTestTypes(); assertNotNull(results); assertEquals(results.getRowCount(), 0); } catch (RuntimeException ex) { fail("Execution of query failed: " + sql, ex); } }
@Test public void testNonDeterministicFilter() { MaterializedResult materializedResult = computeActual("SELECT u FROM ( SELECT if(rand() > 0.5, 0, 1) AS u ) WHERE u <> u"); assertEquals(materializedResult.getRowCount(), 0); materializedResult = computeActual("SELECT u, v FROM ( SELECT if(rand() > 0.5, 0, 1) AS u, 4*4 AS v ) WHERE u <> u and v > 10"); assertEquals(materializedResult.getRowCount(), 0); materializedResult = computeActual("SELECT u, v, w FROM ( SELECT if(rand() > 0.5, 0, 1) AS u, 4*4 AS v, 'abc' AS w ) WHERE v > 10"); assertEquals(materializedResult.getRowCount(), 1); }
@Test public void testInnerJoinWithEmptyBuildSide() { MaterializedResult actual = computeActual( noJoinReordering(), "WITH small_part AS (SELECT * FROM part WHERE name = 'a') " + "SELECT lineitem.orderkey FROM lineitem INNER JOIN small_part ON lineitem.partkey = small_part.partkey"); assertEquals(actual.getRowCount(), 0); }
@Test public void testCreateSchema() { assertEquals(queryRunner.execute("SHOW SCHEMAS FROM blackhole").getRowCount(), 2); queryRunner.execute("CREATE SCHEMA blackhole.test"); assertEquals(queryRunner.execute("SHOW SCHEMAS FROM blackhole").getRowCount(), 3); }
@Test public void selectLargeInterval() { MaterializedResult result = computeActual("SELECT INTERVAL '30' DAY"); assertEquals(result.getRowCount(), 1); assertEquals(result.getMaterializedRows().get(0).getField(0), new SqlIntervalDayTime(30, 0, 0, 0, 0)); result = computeActual("SELECT INTERVAL '" + Short.MAX_VALUE + "' YEAR"); assertEquals(result.getRowCount(), 1); assertEquals(result.getMaterializedRows().get(0).getField(0), new SqlIntervalYearMonth(Short.MAX_VALUE, 0)); }
@Test public void testNonDeterministicProjection() { MaterializedResult materializedResult = computeActual("SELECT r, r + 1 FROM (SELECT rand(100) r FROM orders) LIMIT 10"); assertEquals(materializedResult.getRowCount(), 10); for (MaterializedRow materializedRow : materializedResult) { assertEquals(materializedRow.getFieldCount(), 2); assertEquals(((Number) materializedRow.getField(0)).intValue() + 1, materializedRow.getField(1)); } }
@Test public void testClusteringPredicates() { String sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key='key_1' AND clust_one='clust_one'"; assertEquals(execute(sql).getRowCount(), 1); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key IN ('key_1','key_2') AND clust_one='clust_one'"; assertEquals(execute(sql).getRowCount(), 2); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key='key_1' AND clust_one!='clust_one'"; assertEquals(execute(sql).getRowCount(), 0); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key IN ('key_1','key_2','key_3','key_4') AND clust_one='clust_one' AND clust_two>'clust_two_1'"; assertEquals(execute(sql).getRowCount(), 3); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key IN ('key_1','key_2') AND clust_one='clust_one' AND " + "((clust_two='clust_two_1') OR (clust_two='clust_two_2'))"; assertEquals(execute(sql).getRowCount(), 2); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key IN ('key_1','key_2') AND clust_one='clust_one' AND " + "((clust_two='clust_two_1' AND clust_three='clust_three_1') OR (clust_two='clust_two_2' AND clust_three='clust_three_2'))"; assertEquals(execute(sql).getRowCount(), 2); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key IN ('key_1','key_2') AND clust_one='clust_one' AND clust_three='clust_three_1'"; assertEquals(execute(sql).getRowCount(), 1); sql = "SELECT * FROM " + TABLE_CLUSTERING_KEYS + " WHERE key IN ('key_1','key_2') AND clust_one='clust_one' AND clust_two IN ('clust_two_1','clust_two_2')"; assertEquals(execute(sql).getRowCount(), 2); }
@Test public void testQueryCumulativeTable() { computeActual("SELECT * FROM \"*:*\""); computeActual("SELECT * FROM \"java.util.logging:*\""); assertTrue(computeActual("SELECT * FROM \"java.lang:*\"").getRowCount() > 1); assertTrue(computeActual("SELECT * FROM \"jAVA.LANg:*\"").getRowCount() > 1); } }
@Test public void testSelectWithUnenforcedConstraint() { createBlackholeAllTypesTable(); MaterializedResult rows = queryRunner.execute("SELECT * FROM blackhole_all_types where _bigint > 10"); assertEquals(rows.getRowCount(), 0); dropBlackholeAllTypesTable(); }
@Test public void testDefaultDecimalLiteralSwitch() { Session decimalLiteral = Session.builder(getSession()) .setSystemProperty(SystemSessionProperties.PARSE_DECIMAL_LITERALS_AS_DOUBLE, "false") .build(); MaterializedResult decimalColumnResult = computeActual(decimalLiteral, "SELECT 1.0"); assertEquals(decimalColumnResult.getRowCount(), 1); assertEquals(decimalColumnResult.getTypes().get(0), createDecimalType(2, 1)); assertEquals(decimalColumnResult.getMaterializedRows().get(0).getField(0), new BigDecimal("1.0")); Session doubleLiteral = Session.builder(getSession()) .setSystemProperty(SystemSessionProperties.PARSE_DECIMAL_LITERALS_AS_DOUBLE, "true") .build(); MaterializedResult doubleColumnResult = computeActual(doubleLiteral, "SELECT 1.0"); assertEquals(doubleColumnResult.getRowCount(), 1); assertEquals(doubleColumnResult.getTypes().get(0), DOUBLE); assertEquals(doubleColumnResult.getMaterializedRows().get(0).getField(0), 1.0); }
@Test public void testBucketedTableDoubleFloat() throws Exception { try (Transaction transaction = newTransaction()) { ConnectorMetadata metadata = transaction.getMetadata(); ConnectorSession session = newSession(); ConnectorTableHandle tableHandle = getTableHandle(metadata, tableBucketedDoubleFloat); List<ColumnHandle> columnHandles = ImmutableList.copyOf(metadata.getColumnHandles(session, tableHandle).values()); Map<String, Integer> columnIndex = indexColumns(columnHandles); assertTableIsBucketed(tableHandle); ImmutableMap<ColumnHandle, NullableValue> bindings = ImmutableMap.<ColumnHandle, NullableValue>builder() .put(columnHandles.get(columnIndex.get("t_float")), NullableValue.of(REAL, (long) floatToRawIntBits(87.1f))) .put(columnHandles.get(columnIndex.get("t_double")), NullableValue.of(DOUBLE, 88.2)) .build(); // floats and doubles are not supported, so we should see all splits MaterializedResult result = readTable(transaction, tableHandle, columnHandles, session, TupleDomain.fromFixedValues(bindings), OptionalInt.of(32), Optional.empty()); assertEquals(result.getRowCount(), 100); } }
private void doTestCreateTable(String tableName, String createTable) { String dropTable = "DROP TABLE IF EXISTS " + tableName; queryRunner.execute(dropTable); queryRunner.execute(createTable); String insert = "INSERT INTO " + tableName + " VALUES (1, TIMESTAMP '2001-08-22 03:04:05.321', 2.5)"; queryRunner.execute(insert); MaterializedResult result = queryRunner.execute("SELECT id FROM " + tableName); assertEquals(result.getRowCount(), 1); }
private void assertOneNotNullResult(String query) { MaterializedResult results = getQueryRunner().execute(getSession(), query).toTestTypes(); assertEquals(results.getRowCount(), 1); assertEquals(results.getMaterializedRows().get(0).getFieldCount(), 1); assertNotNull(results.getMaterializedRows().get(0).getField(0)); } }
@Test public void testSelectAllTypes() { createBlackholeAllTypesTable(); MaterializedResult rows = queryRunner.execute("SELECT * FROM blackhole_all_types"); assertEquals(rows.getRowCount(), 1); MaterializedRow row = Iterables.getOnlyElement(rows); assertEquals(row.getFieldCount(), 13); assertEquals(row.getField(0), "**********"); assertEquals(row.getField(1), 0L); assertEquals(row.getField(2), 0); assertEquals(row.getField(3), (short) 0); assertEquals(row.getField(4), (byte) 0); assertEquals(row.getField(5), 0.0f); assertEquals(row.getField(6), 0.0); assertEquals(row.getField(7), false); assertEquals(row.getField(8), LocalDate.ofEpochDay(0)); assertEquals(row.getField(9), LocalDateTime.of(1969, 12, 31, 13, 0, 0)); // TODO #7122 should be 1970-01-01 00:00:00 assertEquals(row.getField(10), "****************".getBytes()); assertEquals(row.getField(11), new BigDecimal("0.00")); assertEquals(row.getField(12), new BigDecimal("00000000000000000000.0000000000")); dropBlackholeAllTypesTable(); }
private void assertQueryResult(@Language("SQL") String sql, Object... expected) { MaterializedResult rows = computeActual(sql); assertEquals(rows.getRowCount(), expected.length); for (int i = 0; i < expected.length; i++) { MaterializedRow materializedRow = rows.getMaterializedRows().get(i); int fieldCount = materializedRow.getFieldCount(); assertTrue(fieldCount == 1, format("Expected only one column, but got '%d'", fieldCount)); Object value = materializedRow.getField(0); assertEquals(value, expected[i]); assertTrue(materializedRow.getFieldCount() == 1); } } }
@Test public void testCharTrailingSpace() throws Exception { execute("CREATE TABLE tpch.char_trailing_space (x char(10))"); assertUpdate("INSERT INTO char_trailing_space VALUES ('test')", 1); assertQuery("SELECT * FROM char_trailing_space WHERE x = char 'test'", "VALUES 'test'"); assertQuery("SELECT * FROM char_trailing_space WHERE x = char 'test '", "VALUES 'test'"); assertQuery("SELECT * FROM char_trailing_space WHERE x = char 'test '", "VALUES 'test'"); assertEquals(getQueryRunner().execute("SELECT * FROM char_trailing_space WHERE x = char ' test'").getRowCount(), 0); Map<String, String> properties = ImmutableMap.of("deprecated.legacy-char-to-varchar-coercion", "true"); Map<String, String> connectorProperties = ImmutableMap.of("connection-url", postgreSqlServer.getJdbcUrl()); try (QueryRunner queryRunner = new DistributedQueryRunner(getSession(), 3, properties);) { queryRunner.installPlugin(new PostgreSqlPlugin()); queryRunner.createCatalog("postgresql", "postgresql", connectorProperties); assertEquals(queryRunner.execute("SELECT * FROM char_trailing_space WHERE x = char 'test'").getRowCount(), 0); assertEquals(queryRunner.execute("SELECT * FROM char_trailing_space WHERE x = char 'test '").getRowCount(), 0); assertEquals(queryRunner.execute("SELECT * FROM char_trailing_space WHERE x = char 'test '").getRowCount(), 0); MaterializedResult result = queryRunner.execute("SELECT * FROM char_trailing_space WHERE x = char 'test '"); assertEquals(result.getRowCount(), 1); assertEquals(result.getMaterializedRows().get(0).getField(0), "test "); } assertUpdate("DROP TABLE char_trailing_space"); }
@Test public void testDeleteFromUnpartitionedTable() { assertUpdate("CREATE TABLE test_delete_unpartitioned AS SELECT orderstatus FROM tpch.tiny.orders", "SELECT count(*) from orders"); assertUpdate("DELETE FROM test_delete_unpartitioned"); MaterializedResult result = computeActual("SELECT * from test_delete_unpartitioned"); assertEquals(result.getRowCount(), 0); assertUpdate("DROP TABLE test_delete_unpartitioned"); assertFalse(getQueryRunner().tableExists(getSession(), "test_delete_unpartitioned")); }
private void assertOneNotNullResult(@Language("SQL") String query) { MaterializedResult results = getQueryRunner().execute(getSession(), query).toTestTypes(); assertEquals(results.getRowCount(), 1); assertEquals(results.getMaterializedRows().get(0).getFieldCount(), 1); assertNotNull(results.getMaterializedRows().get(0).getField(0)); }
@Test public void testMySqlTinyint1() throws Exception { execute("CREATE TABLE tpch.mysql_test_tinyint1 (c_tinyint tinyint(1))"); MaterializedResult actual = computeActual("SHOW COLUMNS FROM mysql_test_tinyint1"); MaterializedResult expected = MaterializedResult.resultBuilder(getSession(), VARCHAR, VARCHAR, VARCHAR, VARCHAR) .row("c_tinyint", "tinyint", "", "") .build(); assertEquals(actual, expected); execute("INSERT INTO tpch.mysql_test_tinyint1 VALUES (127), (-128)"); MaterializedResult materializedRows = computeActual("SELECT * FROM tpch.mysql_test_tinyint1 WHERE c_tinyint = 127"); assertEquals(materializedRows.getRowCount(), 1); MaterializedRow row = getOnlyElement(materializedRows); assertEquals(row.getFields().size(), 1); assertEquals(row.getField(0), (byte) 127); assertUpdate("DROP TABLE mysql_test_tinyint1"); }