Java Code Examples for java.sql.Connection#close()

The following examples show how to use java.sql.Connection#close() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: phoenix   File: ProductMetricsIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testMaxGroupedAggregation() throws Exception {
    String tablename=generateUniqueName();
    String tenantId = getOrganizationId();
    String query = "SELECT feature,max(transactions) FROM "+tablename+" WHERE organization_id=? GROUP BY feature";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        initTableValues(tablename, tenantId, getSplits(tenantId));
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(F1,rs.getString(1));
        assertEquals(600,rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(F2,rs.getString(1));
        assertEquals(400,rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(F3,rs.getString(1));
        assertEquals(500,rs.getInt(2));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 2
Source Project: reladomo   File: TestDatedNonAudited.java    License: Apache License 2.0 6 votes vote down vote up
public void checkDatedNonAuditedInfinityRow(int balanceId, double quantity, Timestamp businessDate) throws SQLException
{
    Connection con = this.getConnection();
    String sql = "select POS_QUANTITY_M, FROM_Z from NON_AUDITED_BALANCE where BALANCE_ID = ? and " +
            "THRU_Z = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, balanceId);
    ps.setTimestamp(2, InfinityTimestamp.getParaInfinity());
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());
    double resultQuantity = rs.getDouble(1);
    Timestamp resultBusinessDate = rs.getTimestamp(2);
    boolean hasMoreResults = rs.next();
    rs.close();
    ps.close();
    con.close();
    assertTrue(quantity == resultQuantity);
    assertEquals(businessDate, resultBusinessDate);
    assertFalse(hasMoreResults);
}
 
Example 3
Source Project: phoenix   File: AlterTableIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testAddColumnsUsingNewConnection() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    String ddl = "CREATE TABLE " + dataTableFullName + " (\n"
            +"ID1 VARCHAR(15) NOT NULL,\n"
            +"ID2 VARCHAR(15) NOT NULL,\n"
            +"CREATED_DATE DATE,\n"
            +"CREATION_TIME BIGINT,\n"
            +"LAST_USED DATE,\n"
            +"CONSTRAINT PK PRIMARY KEY (ID1, ID2)) "  + tableDDLOptions;
    Connection conn1 = DriverManager.getConnection(getUrl(), props);
    conn1.createStatement().execute(ddl);
    ddl = "ALTER TABLE " + dataTableFullName + " ADD STRING VARCHAR, STRING_DATA_TYPES VARCHAR";
    conn1.createStatement().execute(ddl);
    ddl = "ALTER TABLE " + dataTableFullName + " DROP COLUMN STRING, STRING_DATA_TYPES";
    conn1.createStatement().execute(ddl);
    ddl = "ALTER TABLE " + dataTableFullName + " ADD STRING_ARRAY1 VARCHAR[]";
    conn1.createStatement().execute(ddl);
    conn1.close();
}
 
Example 4
@Test
public void testLikeFunctionOnRowKeyInWhere() throws Exception {
    long ts = nextTimestamp();
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts);
    Connection conn = DriverManager.getConnection(url);
    conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
    conn.close();

    url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 2);
    conn = DriverManager.getConnection(url);
    conn.createStatement().execute("UPSERT INTO substr_test VALUES('abc','a')");
    conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd','b')");
    conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd-','c')");
    conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd-1','c')");
    conn.createStatement().execute("UPSERT INTO substr_test VALUES('abce','d')");
    conn.commit();
    conn.close();

    url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5);
    conn = DriverManager.getConnection(url);
    ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from substr_test where s1 like 'abcd%1'");
    assertTrue(rs.next());
    assertEquals("abcd-1",rs.getString(1));
    assertFalse(rs.next());
}
 
Example 5
@edu.umd.cs.findbugs.annotations.SuppressWarnings(
        value="RV_RETURN_VALUE_IGNORED",
        justification="Test code.")
@Test
public void testValidArithmetic() throws Exception {
    String[] queries = new String[] { 
            "SELECT entity_id,organization_id FROM atable where (A_DATE - A_DATE) * 5 < 0",
            "SELECT entity_id,organization_id FROM atable where 1 + A_DATE  < A_DATE",
            "SELECT entity_id,organization_id FROM atable where A_DATE - 1 < A_DATE",
            "SELECT entity_id,organization_id FROM atable where A_INTEGER - 45 < 0",
            "SELECT entity_id,organization_id FROM atable where X_DECIMAL / 45 < 0", };

    for (String query : queries) {
        Properties props = new Properties();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.executeQuery();
        }
        finally {
            conn.close();
        }
    }
}
 
Example 6
Source Project: phoenix   File: SkipScanQueryIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testVarCharXInQuery() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    conn.setAutoCommit(false);
    String tableName = initVarCharCrossProductInTable(conn,Arrays.asList("d","da","db"),Arrays.asList("m","mc","tt"));
    try {
        String query;
        query = "SELECT s1,s2 FROM " + tableName + " WHERE s1 IN ('a','b','da','db') AND s2 IN ('c','ma','m','mc','ttt','z')";
        ResultSet rs = conn.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals("da", rs.getString(1));
        assertEquals("m", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("da", rs.getString(1));
        assertEquals("mc", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("db", rs.getString(1));
        assertEquals("m", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("db", rs.getString(1));
        assertEquals("mc", rs.getString(2));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 7
Source Project: phoenix   File: CastAndCoerceIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testCoerceLongToDecimal1() throws Exception {
    String query = "SELECT entity_id FROM " + tableName + " WHERE organization_id=? AND x_decimal > x_integer";
    String url = getUrl();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW9, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 8
private static void initDb(Connection conn) throws IOException, SQLException {
  try {
    Reader scriptReader = Resources
        .getResourceAsReader("org/apache/ibatis/submitted/result_handler_type/CreateDB.sql");
    ScriptRunner runner = new ScriptRunner(conn);
    runner.setLogWriter(null);
    runner.setErrorLogWriter(null);
    runner.runScript(scriptReader);
    conn.commit();
    scriptReader.close();
  } finally {
    if (conn != null) {
      conn.close();
    }
  }
}
 
Example 9
@Test
public void testLongMultiplyExpression() throws Exception {
    String query = "SELECT entity_id FROM aTable where X_LONG * 2 * 2 = 20";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(ROW7, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 10
Source Project: phoenix   File: LocalIndexIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testLocalIndexRowsShouldBeDeletedWhenUserTableRowsDeleted() throws Exception {
    createBaseTable(TestUtil.DEFAULT_DATA_TABLE_NAME, null, "('e','i','o')");
    Connection conn1 = DriverManager.getConnection(getUrl());
    try {
        conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('b',1,2,4,'z')");
        conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('f',1,2,3,'a')");
        conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('j',2,4,2,'a')");
        conn1.createStatement().execute("UPSERT INTO " + TestUtil.DEFAULT_DATA_TABLE_NAME + " values('q',3,1,1,'c')");
        conn1.commit();
        conn1.createStatement().execute("CREATE LOCAL INDEX " + TestUtil.DEFAULT_INDEX_TABLE_NAME + " ON " + TestUtil.DEFAULT_DATA_TABLE_NAME + "(v1)");
        conn1.createStatement().execute("DELETE FROM " + TestUtil.DEFAULT_DATA_TABLE_NAME + " where v1='a'");
        conn1.commit();
        conn1 = DriverManager.getConnection(getUrl());
        ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + TestUtil.DEFAULT_INDEX_TABLE_NAME);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
    } finally {
        conn1.close();
    }
}
 
Example 11
Source Project: mybaties   File: ComplexColumnTest.java    License: Apache License 2.0 5 votes vote down vote up
@BeforeClass
public static void initDatabase() throws Exception {
    Connection conn = null;

    try {
        Class.forName("org.hsqldb.jdbcDriver");
        conn = DriverManager.getConnection("jdbc:hsqldb:mem:complex_column", "sa",
                "");

        Reader reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/complex_column/CreateDB.sql");

        ScriptRunner runner = new ScriptRunner(conn);
        runner.setLogWriter(null);
        runner.setErrorLogWriter(null);
        runner.runScript(reader);
        conn.commit();
        reader.close();

        reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/complex_column/ibatisConfig.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        reader.close();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 
Example 12
Source Project: phoenix   File: SetPropertyIT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testSetPropertyAndAddColumnForNewAndExistingColumnFamily() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String ddl = "CREATE TABLE " + dataTableFullName + " "
            +
            "  (a_string varchar not null, col1 integer, CF1.col2 integer" +
            "  CONSTRAINT pk PRIMARY KEY (a_string)) " + tableDDLOptions;
    try {
        conn.createStatement().execute(ddl);
        conn.createStatement()
                .execute(
                        "ALTER TABLE "
                                + dataTableFullName
                                + " ADD col4 integer, CF1.col5 integer, CF2.col6 integer IN_MEMORY=true, REPLICATION_SCOPE=1, CF2.IN_MEMORY=false ");
        try (Admin admin = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin()) {
            ColumnFamilyDescriptor[] columnFamilies = admin.getDescriptor(TableName.valueOf(dataTableFullName))
                    .getColumnFamilies();
            assertEquals(3, columnFamilies.length);
            assertEquals("0", columnFamilies[0].getNameAsString());
            assertTrue(columnFamilies[0].isInMemory());
            assertEquals(1, columnFamilies[0].getScope());
            assertEquals("CF1", columnFamilies[1].getNameAsString());
            assertTrue(columnFamilies[1].isInMemory());
            assertEquals(1, columnFamilies[1].getScope());
            assertEquals("CF2", columnFamilies[2].getNameAsString());
            assertFalse(columnFamilies[2].isInMemory());
            assertEquals(1, columnFamilies[2].getScope());
        }
    } finally {
        conn.close();
    }
}
 
Example 13
/**
 * Test that a connection to JDBCDataSource can be established
 * successfully while creating a database using setDatabaseName()
 * with create=true
 *
 * @throws SQLException
 */

public void testCreateInDatabaseName_DS() throws SQLException
{
    DataSource ds = JDBCDataSource.getDataSource();
    String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName();
    JDBCDataSource.setBeanProperty(ds, "databaseName", dbName +";create=true");
    Connection c = ds.getConnection();
    c.setAutoCommit(false);
    c.close();
}
 
Example 14
Source Project: phoenix   File: Array1IT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testSelectWithArrayWithColumnRefWithVarLengthArrayWithNullValue() throws Exception {

    String tenantId = getOrganizationId();
    String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
    initTablesWithArrays(table, tenantId, null, false, getUrl());
    String query = "SELECT b_string,ARRAY['abc',null,'bcd',null,null,b_string] FROM " + table + " where organization_id =  '"
            + tenantId + "'";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        String val = rs.getString(1);
        assertEquals(val, "b");
        Array array = rs.getArray(2);
        // Need to support primitive
        String[] strArr = new String[6];
        strArr[0] = "abc";
        strArr[1] = null;
        strArr[2] = "bcd";
        strArr[3] = null;
        strArr[4] = null;
        strArr[5] = "b";
        Array resultArr = conn.createArrayOf("VARCHAR", strArr);
        assertEquals(resultArr, array);
        String expectedPrefix = "['abc', null, 'bcd', null, null, 'b";
        assertTrue("Expected to start with " + expectedPrefix,
            rs.getString(2).startsWith(expectedPrefix));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 15
@Test
// 测试正排,倒排且部分表第一次结果为0的case
public void testMultiRouterLimitResult3() throws SQLException {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn
                .prepareStatement("SELECT * FROM test WHERE score > 6 ORDER BY score DESC, NAME ASC LIMIT 5,3");
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        List<TestEntity> popResult = popResult(rs);
        Assert.assertEquals(3, popResult.size());
        Assert.assertEquals("conan2", popResult.get(0).getName());
        Assert.assertEquals(10, popResult.get(0).getScore());
        Assert.assertEquals("conan4", popResult.get(1).getName());
        Assert.assertEquals(10, popResult.get(1).getScore());
        Assert.assertEquals("conan1", popResult.get(2).getName());
        Assert.assertEquals(9, popResult.get(2).getScore());
    } catch (Exception e) {
        System.err.println(e);
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 
Example 16
Source Project: qaf   File: DatabaseUtil.java    License: MIT License 5 votes vote down vote up
/**
 * {@link Connection#close() close connection} if not null ignoring exception if any

 * @param conn
 */
public static void close(Connection conn) {
	try {
		if (conn != null) {
			conn.close();
		}
	} catch (SQLException se) {
		log.error("An error occurred while attempting to close a database connection", se);
	}
}
 
Example 17
/**
 * Test that when a nested routine drops a role/schema, the
 * current value is correctly reset. For roles, the current role
 * is unchanged, since it is lazily checked (and potentially reset
 * to NONE if it no longer exists or it is no longer granted to
 * session user) only when it is attempted used for anything. For
 * schema, the current schema should revert back to the session's
 * default schema. This holds for all frames on the session
 * context stack (see also caller's check).
 */
public static void dropper() throws SQLException
{
    Connection conn1 = null;

    try {
        conn1 = DriverManager.getConnection("jdbc:default:connection");

        // Drop current contexts
        Statement stm = conn1.createStatement();
        stm.executeUpdate("drop role outermost");
        stm.executeUpdate("drop schema outermost restrict");
        stm.close();

        String[] expected = new String[]{null, "TEST_DBO"};

        // check that we revert correctly
        for (int i= 0; i < variableKeywords.length; i++) {
            String curr = currentPrefix[i] + variableKeywords[i];

            PreparedStatement ps =
                conn1.prepareStatement("values " + curr);

            ResultSet rs = ps.executeQuery();
            assertCurrent(variableKeywords[i], rs, expected[i]);
            rs.close();
            ps.close();
        }
    } finally {
        if (conn1 != null) {
            try {
                conn1.close();
            } catch (Exception e) {
            }
        }
    }
}
 
Example 18
public void testChangePasswordAndDatabasePropertiesOnly() 
throws SQLException
{
    String dbName = TestConfiguration.getCurrent().getDefaultDatabaseName();

    // use valid user/pwd to set the full accessusers.
    Connection conn1 = openDefaultConnection(
        "dan", ("dan" + PASSWORD_SUFFIX));
    setDatabaseProperty("gemfirexd.authz-full-access-users", 
        "dan,jeff,system", conn1);
    setDatabaseProperty(
        "gemfirexd.authz-default-connection-mode","NoAccess", conn1);
    setDatabaseProperty(
            "gemfirexd.distributedsystem.requireAuthentication","true", conn1);
    
    conn1.commit();
    
    // check the system wide user
    assertConnectionOK(dbName, "system", "admin"); 
    assertConnectionFail("08004", dbName, "system", "otherSysPwd");
    assertConnectionOK(dbName, "jeff", ("jeff" + PASSWORD_SUFFIX));
    assertConnectionFail("08004", dbName, "jeff", "otherPwd");
    setDatabaseProperty("gemfirexd.user.jeff", "otherPwd", conn1);
    conn1.commit();
    // should have changed ok.
    assertConnectionOK(dbName, "jeff", "otherPwd");

    // note: if we do this:
    //  setDatabaseProperty("gemfirexd.user.system", "scndSysPwd", conn1);
    //  conn1.commit();
    // i.e. adding the same user (but different pwd) at database level,
    // then we cannot connect anymore using that user name, not with
    // either password.

    // force database props only
    setDatabaseProperty(
        "gemfirexd.distributedsystem.propertiesOnly","true", conn1);
    conn1.commit();
    
    // now, should not be able to logon as system user
    assertConnectionFail("08004", dbName, "system", "admin");

    // reset propertiesOnly
    setDatabaseProperty(
        "gemfirexd.distributedsystem.propertiesOnly","false", conn1);
    conn1.commit();
    conn1.close();
    assertConnectionOK(dbName, "system", "admin");
    
    // try changing system's pwd
    setSystemProperty("gemfirexd.user.system", "thrdSysPwd");

    // can we get in as system user with changed pwd
    assertConnectionOK(dbName, "system", "thrdSysPwd");
    
    // reset
    // first change system's pwd back
    setSystemProperty("gemfirexd.user.system", "admin");

    conn1 = openDefaultConnection("dan", ("dan" + PASSWORD_SUFFIX));
    setDatabaseProperty(
        "gemfirexd.authz-default-connection-mode","fullAccess", conn1);
    setDatabaseProperty(
        "gemfirexd.authentication.required","false", conn1);
    setDatabaseProperty(
            "gemfirexd.distributedsystem.propertiesOnly","false", conn1);
    conn1.commit();
    conn1.close();
}
 
Example 19
Source Project: phoenix   File: HashJoinIT.java    License: Apache License 2.0 4 votes vote down vote up
@Test
public void testInnerJoin() throws Exception {
    Connection conn = getConnection();
    String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
    String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
    String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for " + seqName + " FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000001");
        assertEquals(rs.getString(2), "T1");
        assertEquals(rs.getString(3), "0000000001");
        assertEquals(rs.getString(4), "S1");
        assertEquals(1, rs.getInt(5));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000002");
        assertEquals(rs.getString(2), "T2");
        assertEquals(rs.getString(3), "0000000001");
        assertEquals(rs.getString(4), "S1");
        assertEquals(2, rs.getInt(5));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000003");
        assertEquals(rs.getString(2), "T3");
        assertEquals(rs.getString(3), "0000000002");
        assertEquals(rs.getString(4), "S2");
        assertEquals(3, rs.getInt(5));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000004");
        assertEquals(rs.getString(2), "T4");
        assertEquals(rs.getString(3), "0000000002");
        assertEquals(rs.getString(4), "S2");
        assertEquals(4, rs.getInt(5));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000005");
        assertEquals(rs.getString(2), "T5");
        assertEquals(rs.getString(3), "0000000005");
        assertEquals(rs.getString(4), "S5");
        assertEquals(5, rs.getInt(5));
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "0000000006");
        assertEquals(rs.getString(2), "T6");
        assertEquals(rs.getString(3), "0000000006");
        assertEquals(rs.getString(4), "S6");
        assertEquals(6, rs.getInt(5));

        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 20
Source Project: phoenix   File: CostBasedDecisionIT.java    License: Apache License 2.0 4 votes vote down vote up
@Test
public void testCostOverridesStaticPlanOrderingInJoinQuery() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(true);
    try {
        String tableName = BaseTest.generateUniqueName();
        conn.createStatement().execute("CREATE TABLE " + tableName + " (\n" +
                "rowkey VARCHAR PRIMARY KEY,\n" +
                "c1 VARCHAR,\n" +
                "c2 VARCHAR)");
        conn.createStatement().execute("CREATE LOCAL INDEX " + tableName + "_idx ON " + tableName + " (c1)");

        String query = "SELECT t1.rowkey, t1.c1, t1.c2, t2.c1, mc2 FROM " + tableName + " t1 "
                + "JOIN (SELECT c1, max(rowkey) mrk, max(c2) mc2 FROM " + tableName + " where rowkey <= 'z' GROUP BY c1) t2 "
                + "ON t1.rowkey = t2.mrk WHERE t1.c1 LIKE 'X0%' ORDER BY t1.rowkey";
        // Use the default plan when stats are not available.
        verifyQueryPlan(query,
                "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + tableName + "\n" +
                "    SERVER FILTER BY C1 LIKE 'X0%'\n" +
                "    PARALLEL INNER-JOIN TABLE 0\n" +
                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [*] - ['z']\n" +
                "            SERVER AGGREGATE INTO DISTINCT ROWS BY [C1]\n" +
                "        CLIENT MERGE SORT\n" +
                "    DYNAMIC SERVER FILTER BY T1.ROWKEY IN (T2.MRK)");

        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " (rowkey, c1, c2) VALUES (?, ?, ?)");
        for (int i = 0; i < 10000; i++) {
            int c1 = i % 16;
            stmt.setString(1, "k" + i);
            stmt.setString(2, "X" + Integer.toHexString(c1) + c1);
            stmt.setString(3, "c");
            stmt.execute();
        }

        conn.createStatement().execute("UPDATE STATISTICS " + tableName);

        // Use the optimal plan based on cost when stats become available.
        verifyQueryPlan(query,
                "CLIENT PARALLEL 626-WAY RANGE SCAN OVER " + tableName + " [1,'X0'] - [1,'X1']\n" +
                "    SERVER FILTER BY FIRST KEY ONLY\n" +
                "    SERVER SORTED BY [\"T1.:ROWKEY\"]\n" +
                "CLIENT MERGE SORT\n" +
                "    PARALLEL INNER-JOIN TABLE 0\n" +
                "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + tableName + " [1]\n" +
                "            SERVER FILTER BY FIRST KEY ONLY AND \"ROWKEY\" <= 'z'\n" +
                "            SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"C1\"]\n" +
                "        CLIENT MERGE SORT\n" +
                "    DYNAMIC SERVER FILTER BY \"T1.:ROWKEY\" IN (T2.MRK)");
    } finally {
        conn.close();
    }
}