testsubselect.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

        String sql =
            "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
        String[] expected = new String[] {
            "golden delicious", "macintosh"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * As above, with table aliases.
     */
    public void testWhereClausesCollidingWithAliases() throws SQLException {

        String sql =
            "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
        String[] expected = new String[] {
            "golden delicious", "macintosh"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Inner select with two tables having columns with the same name, one of which is referred to in the
     * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,
     * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).
     */
    public void testHiddenCollision() throws SQLException {

        String sql =
            "select name from fruits where id in (select fruit_id from trees) order by name";
        String[] expected = new String[] {
            "golden delicious", "granny smith", "macintosh", "red delicious"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * As above, with table aliases.
     */
    public void testHiddenCollisionWithAliases() throws SQLException {

        String sql =
            "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
        String[] expected = new String[] {
            "golden delicious", "granny smith", "macintosh", "red delicious"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Inner select with where clause in outer select having column with same name as select clause in inner select
     */
    public void testWhereSelectColliding() throws SQLException {

        // Yes, this is a nonsensical query
        String sql =
            "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
        String[] expected = new String[] {
            "indigo", "orange"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * As above, with aliases.
     */
    public void testWhereSelectCollidingWithAliases() throws SQLException {

        // Yes, this is a nonsensical query
        String sql =
            "select a.val from colors a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.val";
        String[] expected = new String[] {
            "indigo", "orange"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Inner select involving same table
     */
    public void testSameTable() throws SQLException {

        String sql =
            "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
        String[] expected = new String[] {
            "large red delicious tree", "small red delicious tree"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * As above with aliases.
     */
    public void testSameTableWithAliases() throws SQLException {

        String sql =
            "select a.name from trees a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.name";
        String[] expected = new String[] {
            "large red delicious tree", "small red delicious tree"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     *     Inner select involving same table as one of two joined tables in outer select
     */
    public void testSameTableWithJoin() throws SQLException {

        String sql =
            "select sizes.val from trees, sizes where sizes.id = trees.size_id and trees.id in (select id from trees where fruit_id = 3) order by sizes.val";
        String[] expected = new String[] {
            "large", "small"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Tests two subselects, anded.
     */
    public void testAndedSubselects() throws SQLException {

        String sql =
            "select name from trees where size_id in (select id from sizes where val = 'large') and fruit_id in (select id from fruits where color_id = 1) order by name";
        String[] expected = new String[] {
            "large macintosh tree", "large red delicious tree"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Test nested subselects.
     */
    public void testNestedSubselects() throws SQLException {

        String sql =
            "select name from trees where fruit_id in (select id from fruits where color_id in (select id from colors where val = 'red')) order by name";
        String[] expected = new String[] {
            "large macintosh tree", "large red delicious tree",
            "small red delicious tree"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Inner select with "not in" in outer select where clause.
     */
    public void testNotIn() throws SQLException {

        String sql =
            "select name from fruits where id not in (select fruit_id from trees) order by name";
        String[] expected = new String[]{ "tangerine" };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Inner select with "not in" in outer select where clause and same table in inner select where clause.
     */
    public void testNotInSameTableAndColumn() throws SQLException {

        String sql =
            "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
        String[] expected = new String[] {
            "macintosh", "red delicious"
        };

        compareResults(sql, expected, jdbcConnection);
    }

    /**
     * Inner select reusing alias names from outer select, but using them for different tables
     */
    public void testAliasScope() throws SQLException {

        String sql =
            "select a.val, b.name from sizes a, trees b where a.id = b.size_id and b.id in (select a.id from trees a, fruits b where a.fruit_id = b.id and b.name='red delicious') order by a.val";
        String[] expectedSizes = new String[] {
            "large", "small"
        };
        String[] expectedTrees = new String[] {
            "large red delicious tree", "small red delicious tree"
        };

        assertEquals(
            "Programmer error: expected arrays should be of equal length.",
            expectedSizes.length, expectedTrees.length);

        Statement statement = jdbcConnection.createStatement();
        ResultSet results   = statement.executeQuery(sql);
        int       rowCount  = 0;

        while (results.next()) {
            assertTrue("Statement <" + sql + "> returned too many rows.",
                       (rowCount < expectedSizes.length));
            assertEquals("Statement <" + sql + "> returned wrong value.",
                         expectedSizes[rowCount], results.getString(1));
            assertEquals("Statement <" + sql + "> returned wrong value.",
                         expectedTrees[rowCount], results.getString(2));

            rowCount++;
        }

        assertEquals(
            "Statement <" + sql + "> returned wrong number of rows.",
            expectedSizes.length, rowCount);
    }

    //------------------------------------------------------------
    // Main program
    //------------------------------------------------------------
    public static void main(String[] args) throws IOException {
        junit.swingui.TestRunner.run(TestSubselect.class);
    }
}