org.apache.derbyTesting.functionTests.tests.lang

Class JoinTest

java.lang.Object

junit.framework.Assert

junit.framework.TestCase

org.apache.derbyTesting.junit.BaseTestCase

org.apache.derbyTesting.junit.BaseJDBCTestCase

org.apache.derbyTesting.functionTests.tests.lang.JoinTest

All Implemented Interfaces:

junit.framework.Test

public class JoinTest
extends BaseJDBCTestCase

Test cases for JOINs.

Field Summary

Fields

Modifier and Type	Field and Description
private static java.lang.String	AMBIGUOUS_COLNAME
private static java.lang.String	COLUMN_NOT_IN_SCOPE
private static java.lang.String	NO_COLUMNS
private static java.lang.String	NON_COMPARABLE
private static java.lang.String	SYNTAX_ERROR
private static java.lang.String	TABLE_NAME_NOT_IN_SCOPE
private static java.lang.String	VALUES_WITH_NULL

Fields inherited from class org.apache.derbyTesting.junit.BaseTestCase

DEFAULT_DB_DIR, DERBY_LOG, ERRORSTACKTRACEFILE

Constructor Summary

Constructors

Constructor and Description
JoinTest(java.lang.String name)

Method Summary

Modifier and Type	Method and Description
private static java.lang.String[][]	cross(java.lang.String[][] t1, java.lang.String[][] t2) Calculate the Cartesian product of two tables.
private void	fillTable(java.lang.String sql, java.lang.String[][] data) Fill a table with rows.
(package private) static void	insertTourRow(java.sql.PreparedStatement ps, int a, java.lang.String b, java.lang.String c)
(package private) static void	insertTourRow(java.sql.PreparedStatement ps, java.lang.String a, java.lang.String b)
private static java.lang.String[][]	project(int[] cols, java.lang.String[][] rows) Project columns from a table.
private static java.lang.String[][]	reverse(java.lang.String[][] rows) Reverse the order of rows in a table.
static junit.framework.Test	suite()
void	testCrossJoins() Test the CROSS JOIN syntax that was added in DERBY-4355.
void	testDerby_4405() Derby-4405 improve rewrite of OUTER JOIN to INNER JOIN in presence of null intolerant predicate.
void	testDerby_4679() DERBY-4679.
void	testDerby_4695() This test works prior to applying the patch for DERBY-4695, but the corrected (internal, intermediate) behavior can be observed by applying the patch trace-remapping.diff attached to this issue in JIRA and copmparing the results before and after the rest of the patch is applied.
void	testDerby_5933() DERBY-5933.
void	testDerby4372() DERBY-4372: Some joins used to miss some rows after an index was created, because the start and stop keys passed to the index scan were wrong if the IN list in the JOIN condition contained a NULL.
void	testDerby4387() Test that computation of transitive closure of equi-join does not give rise to eternal loop in a case where a predicate of type T1.x = T1.y is added to the closure.
void	testNaturalJoin() Tests for the NATURAL JOIN syntax added in DERBY-4495.
void	testNullabilityInLeftOrRightOuterJoin() Test that the columns returned by a left or right outer join have the correct nullability.
void	testNullabilityInValues() DERBY-4365 Test that the NULL values are caught in VALUES clause when it is part of a non-INSERT statement.
void	testSubqueryInON() Test that ON clauses can contain subqueries (DERBY-4380).
void	testUsingClause() Tests for the USING clause added in DERBY-4370.

Methods inherited from class org.apache.derbyTesting.junit.BaseJDBCTestCase

assertCallError, assertCheckTable, assertCompileError, assertEquals, assertEquals, assertEquals, assertEquivalentDataType, assertErrorCode, assertGetIntError, assertNextError, assertPreparedStatementError, assertSQLExceptionEquals, assertSQLState, assertSQLState, assertStatementError, assertStatementError, assertStatementError, assertStatementError, assertStatementErrorUnordered, assertTableRowCount, assertUpdateCount, assertUpdateCount, assertWarning, checkAllConsistency, checkEstimatedRowCount, closeStatement, commit, createStatement, createStatement, createStatement, dropTable, dropTable, dropView, dropView, emptyStatementCache, getClientTransactionID, getConnection, getDatabaseProperty, getLastSQLException, initializeConnection, openConnection, openDefaultConnection, openDefaultConnection, openDefaultConnection, openUserConnection, prepareCall, prepareCall, prepareCall, prepareStatement, prepareStatement, prepareStatement, prepareStatement, prepareStatement, prepareStatement, rollback, runBare, runBareOverridable, runScript, runScript, runSQLCommands, setAutoCommit, tearDown, usingDB2Client, usingDerbyNetClient, usingEmbedded

Methods inherited from class org.apache.derbyTesting.junit.BaseTestCase

alarm, assertDirectoryDeleted, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertExecJavaCmdAsExpected, assertLaunchedJUnitTestMethod, assertLaunchedJUnitTestMethod, assertSecurityManager, assertThrowableEquals, currentDirectory, execJavaCmd, execJavaCmd, fail, getClassVersionMajor, getEmmaJar, getFailureFolder, getFilesWith, getJavaExecutableName, getSystemProperty, getTestConfiguration, getTestResource, hasInterruptibleIO, isCVM, isIBMJVM, isJ9Platform, isJava5, isJava7, isJava8, isPhoneME, isPlatform, isSunJVM, isWindowsPlatform, openTestResource, println, printStackTrace, readProcessOutput, removeDirectory, removeDirectory, removeFiles, removeSystemProperty, runsWithEmma, runsWithJaCoCo, setSystemProperty, sleep, sleepAtLeastOneTick, traceit

Methods inherited from class junit.framework.TestCase

assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertEquals, assertFalse, assertFalse, assertNotNull, assertNotNull, assertNotSame, assertNotSame, assertNull, assertNull, assertSame, assertSame, assertTrue, assertTrue, countTestCases, createResult, fail, fail, failNotEquals, failNotSame, failSame, format, getName, run, run, runTest, setName, setUp, toString

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

SYNTAX_ERROR

private static final java.lang.String SYNTAX_ERROR

See Also:

Constant Field Values

AMBIGUOUS_COLNAME

private static final java.lang.String AMBIGUOUS_COLNAME

See Also:

Constant Field Values

COLUMN_NOT_IN_SCOPE

private static final java.lang.String COLUMN_NOT_IN_SCOPE

See Also:

Constant Field Values

NON_COMPARABLE

private static final java.lang.String NON_COMPARABLE

See Also:

Constant Field Values

NO_COLUMNS

private static final java.lang.String NO_COLUMNS

See Also:

Constant Field Values

TABLE_NAME_NOT_IN_SCOPE

private static final java.lang.String TABLE_NAME_NOT_IN_SCOPE

See Also:

Constant Field Values

VALUES_WITH_NULL

private static final java.lang.String VALUES_WITH_NULL

See Also:

Constant Field Values

Constructor Detail

JoinTest

public JoinTest(java.lang.String name)

Method Detail

suite

public static junit.framework.Test suite()

testNullabilityInValues

public void testNullabilityInValues()
                             throws java.sql.SQLException

DERBY-4365 Test that the NULL values are caught in VALUES clause when it is part of a non-INSERT statement. Throw exception 42X07 for such a case.

Throws:

java.sql.SQLException

testNullabilityInLeftOrRightOuterJoin

public void testNullabilityInLeftOrRightOuterJoin()
                                           throws java.sql.SQLException

Test that the columns returned by a left or right outer join have the correct nullability. In a left outer join, the columns from the left side of the join should have their original nullability, and all the columns from the right side of the join should be nullable. In a right outer join, all the columns from the left side should be nullable, and the columns from the right side should preserve their original nullability. DERBY-4284.

Throws:

java.sql.SQLException

testDerby4372

public void testDerby4372()
                   throws java.sql.SQLException

DERBY-4372: Some joins used to miss some rows after an index was created, because the start and stop keys passed to the index scan were wrong if the IN list in the JOIN condition contained a NULL.

Throws:

java.sql.SQLException

testCrossJoins

public void testCrossJoins()
                    throws java.sql.SQLException

Test the CROSS JOIN syntax that was added in DERBY-4355.

Throws:

java.sql.SQLException

fillTable

private void fillTable(java.lang.String sql,
                       java.lang.String[][] data)
                throws java.sql.SQLException

Fill a table with rows.

Parameters:

sql - the insert statement used to populate the table

data - the rows to insert into the table

Throws:

java.sql.SQLException

cross

private static java.lang.String[][] cross(java.lang.String[][] t1,
                                          java.lang.String[][] t2)

Calculate the Cartesian product of two tables.

Parameters:

t1 - the rows in the table on the left side

t2 - the rows in the table on the right side

Returns:

a two-dimensional array containing the Cartesian product of the two tables (primary ordering same as t1, secondary ordering same as t2)

project

private static java.lang.String[][] project(int[] cols,
                                            java.lang.String[][] rows)

Project columns from a table.

Parameters:

cols - the column indexes (0-based) to project

rows - the rows in the table

Returns:

the projected result

reverse

private static java.lang.String[][] reverse(java.lang.String[][] rows)

Reverse the order of rows in a table.

Parameters:

rows - the rows in the table

Returns:

the rows in reverse order

testUsingClause

public void testUsingClause()
                     throws java.sql.SQLException

Tests for the USING clause added in DERBY-4370.

Throws:

java.sql.SQLException

testNaturalJoin

public void testNaturalJoin()
                     throws java.sql.SQLException

Tests for the NATURAL JOIN syntax added in DERBY-4495.

Throws:

java.sql.SQLException

testSubqueryInON

public void testSubqueryInON()
                      throws java.sql.SQLException

Test that ON clauses can contain subqueries (DERBY-4380).

Throws:

java.sql.SQLException

testDerby4387

public void testDerby4387()
                   throws java.sql.SQLException

Test that computation of transitive closure of equi-join does not give rise to eternal loop in a case where a predicate of type T1.x = T1.y is added to the closure.

Throws:

java.sql.SQLException

testDerby_4405

public void testDerby_4405()
                    throws java.sql.SQLException

Derby-4405 improve rewrite of OUTER JOIN to INNER JOIN in presence of null intolerant predicate. It uses a slimmed down toursdb dataset.

Throws:

java.sql.SQLException

insertTourRow

static void insertTourRow(java.sql.PreparedStatement ps,
                          java.lang.String a,
                          java.lang.String b)
                   throws java.sql.SQLException

Throws:

java.sql.SQLException

insertTourRow

static void insertTourRow(java.sql.PreparedStatement ps,
                          int a,
                          java.lang.String b,
                          java.lang.String c)
                   throws java.sql.SQLException

Throws:

java.sql.SQLException

testDerby_4679

public void testDerby_4679()
                    throws java.sql.SQLException

DERBY-4679. Verify that when transitive closure generates new criteria into the query, it isn't confused by situations where the same column name appears in a result column list multiple times due to flattening of sub-queries.

Flattening requires remapping of (table, column) numbers in column references. In cases where the same column name appears in a result column list multiple times, this might earlier lead to remapping (reassigning) wrong (table, column) numbers to column references in join predicates transformed to where clauses as a result of the flattening.

See also DERBY-2526 and DERBY-3023 whose fixes which were partial solutions to the problem of wrong column number remappings confusing the transitive closure of search predicates performed by the preprocessing step of the optimizer.

Throws:

java.sql.SQLException

testDerby_4695

public void testDerby_4695()
                    throws java.sql.SQLException

This test works prior to applying the patch for DERBY-4695, but the corrected (internal, intermediate) behavior can be observed by applying the patch trace-remapping.diff attached to this issue in JIRA and copmparing the results before and after the rest of the patch is applied.

Throws:

java.sql.SQLException

testDerby_5933

public void testDerby_5933()
                    throws java.sql.SQLException

DERBY-5933. Error in column reference remapping in connection with flattening of a left outer join with a base table "d", cf test case below. In the example, the predicate b1=1 is pushed into the LOJ node, and in that connection the column reference to "b1" is remapped. The generated table number of the LOJ node is 4, and the correct column number of "b1" should be 5, i.e. [a1: 1, a2: 2, a3: 3, a4: 4, b1:5, c1:6]. However, the remapping logic erroneously picked b1's column number from its base table, 1, which really is the position of a1. Now, since b1 is constant, the column reference b1 "alias" a1 gets marked as such. Since we are ordering on a1, the sort avoidance logic is led to believe a1 is constant, and hence sorting is skipped, hence the wrong result. For related issues, see DERBY-4679, DERBY-4695, DERBY-3023, DERBY-2526.

Throws:

java.sql.SQLException