org.apache.derbyTesting.functionTests.tests.lang

Class ExistsWithSubqueriesTest

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.ExistsWithSubqueriesTest

All Implemented Interfaces:

junit.framework.Test

public class ExistsWithSubqueriesTest
extends BaseJDBCTestCase

This test contains a variety of cases of EXISTS predicates with subqueries. Several tests ensure that an EXISTS predicate which wraps a set operation-- meaning a UNION, INTERSECT, or EXCEPT node--returns the correct results. For example: select * from ( values 'BAD' ) as T where exists ((values 1) intersect (values 2)) should return zero rows. Prompted by DERBY-2370. A somewhat unrelated test verifies the DERBY-3033 behavior, which involves flattening of subqueries with NOT EXISTS predicates. The issue here is that a flattened NOT EXISTS subquery cannot be used to perform equi-join transitive closure, because the implied predicate that results from the flattening is a NOT EQUALS condition.

Field Summary

Fields

Modifier and Type	Field and Description
private static java.lang.String	EXISTS_PREFIX_1
private static java.lang.String	EXISTS_PREFIX_2

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

DEFAULT_DB_DIR, DERBY_LOG, ERRORSTACKTRACEFILE

Constructor Summary

Constructors

Constructor and Description
ExistsWithSubqueriesTest(java.lang.String name) Create a test with the given name.

Method Summary

Modifier and Type	Method and Description
private void	checkQuery(java.sql.Statement st, java.lang.String[][] expRS, java.lang.String query) Simple helper method to assert the results of the received query.
private void	setupDerby3033()
static junit.framework.Test	suite() Return suite with all tests of the class.
void	testCorrelatedSetOps() Test queries where the set operation has subqueries which are correlated to the outer query.
void	testDerby3033() Regression test for Derby-3033.
void	testDerby6408() Some EXISTS subqueries (and IN subqueries transformed to EXISTS) returned NULL instead of TRUE or FALSE before DERBY-6408.
void	testNonCorrelatedSetOps() Test queries where the set operation has subqueries which are not correlated to the outer query.
void	testSetOpsWithVALUES() Test queries where the set operation just involves VALUES expressions.
private void	updateStats(java.sql.Statement st, java.lang.String tName) Ensure that the #rows statistics are updated

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

EXISTS_PREFIX_1

private static final java.lang.String EXISTS_PREFIX_1

See Also:

Constant Field Values

EXISTS_PREFIX_2

private static final java.lang.String EXISTS_PREFIX_2

See Also:

Constant Field Values

Constructor Detail

ExistsWithSubqueriesTest

public ExistsWithSubqueriesTest(java.lang.String name)

Create a test with the given name.

Parameters:

name - name of the test.

Method Detail

suite

public static junit.framework.Test suite()

Return suite with all tests of the class.

testSetOpsWithVALUES

public void testSetOpsWithVALUES()
                          throws java.lang.Exception

Test queries where the set operation just involves VALUES expressions.

Throws:

java.lang.Exception

testNonCorrelatedSetOps

public void testNonCorrelatedSetOps()
                             throws java.lang.Exception

Test queries where the set operation has subqueries which are not correlated to the outer query. It's important to check for cases where we have explicit columns _and_ cases where we have "*" because the binding codepaths differ and we want to verify both.

Throws:

java.lang.Exception

testCorrelatedSetOps

public void testCorrelatedSetOps()
                          throws java.lang.Exception

Test queries where the set operation has subqueries which are correlated to the outer query. Subqueries should still be able reference the outer query table and execute without error.

Throws:

java.lang.Exception

checkQuery

private void checkQuery(java.sql.Statement st,
                        java.lang.String[][] expRS,
                        java.lang.String query)
                 throws java.lang.Exception

Simple helper method to assert the results of the received query. If the array representing expected results is null then we assert that the query returns no rows.

Throws:

java.lang.Exception

testDerby3033

public void testDerby3033()
                   throws java.lang.Exception

Regression test for Derby-3033. This method constructs a query with the property that it: - contains a NOT EXISTS condition against a correlated subquery - such that if that subquery is flattened, the result is 3 tables which all have join predicates on the same key. The point of the test is that it is *not* correct to construct a new equijoin predicate between table d3033_a and d3033_c via transitive closure, because the join condition between d3033_b and d3033_c is NOT EXISTS. In the original bug, the compiler/optimizer erroneously generated the extra equijoin predicate, which caused NPE exceptions at runtime due to attempts to reference the non-existent (NOT EXISTS) row. So this test succeeds if it gets the right results and no NPE.

Throws:

java.lang.Exception

updateStats

private void updateStats(java.sql.Statement st,
                         java.lang.String tName)
                  throws java.lang.Exception

Ensure that the #rows statistics are updated

Throws:

java.lang.Exception

setupDerby3033

private void setupDerby3033()
                     throws java.lang.Exception

Throws:

java.lang.Exception

testDerby6408

public void testDerby6408()
                   throws java.sql.SQLException

Some EXISTS subqueries (and IN subqueries transformed to EXISTS) returned NULL instead of TRUE or FALSE before DERBY-6408. This test case verifies the fix.

Throws:

java.sql.SQLException