org.apache.derbyTesting.functionTests.tests.lang

Class OrderByAndOffsetFetchInSubqueries

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

All Implemented Interfaces:

junit.framework.Test

public class OrderByAndOffsetFetchInSubqueries
extends BaseJDBCTestCase

Tests for DERBY-4397 Allow ORDER BY in subqueries and DERBY-4398 Allow OFFSET/FETCH in subqueries.

Field Summary

Fields

Modifier and Type	Field and Description
(package private) static java.lang.String	COLUMN_NOT_FOUND
(package private) static java.lang.String	COLUMN_OUT_OF_RANGE
(package private) static java.lang.String	ORDER_BY_COLUMN_NOT_FOUND
(package private) static java.lang.String	SYNTAX_ERROR

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

DEFAULT_DB_DIR, DERBY_LOG, ERRORSTACKTRACEFILE

Constructor Summary

Constructors

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

Method Summary

Modifier and Type	Method and Description
private static junit.framework.Test	makeSuite() Construct suite of tests
private void	queryAndCheck(java.sql.Statement stm, java.lang.String queryText, java.lang.String[][] expectedRows)
static junit.framework.Test	suite() Construct top level suite in this JUnit test
void	testDerby6378() Nested query expression body, with each level contributing to the set of ORDER BY and/or OFFSET/FETCH FIRST clauses.
void	testInsertSelectOrderBy() Test {@code INSERT INTO t SELECT ..
void	testInsertSelectOrderByOffsetFetch() Same test as testInsertSelectOrderBy but with use of OFFSET/FETCH FIRST.
void	testJoinsWithOffsetFetch() Test JOIN with delimited subqueries
void	testNestingInsideSetOperation() Test nesting inside set operands, cf. this production in SQL 2011, section 7.12: ::= | [ ] [ ] [ ] The corresponding production in sqlgrammar.jj is nonJoinQueryPrimary.
void	testPushAvoidance() Prevent pushing of where predicates into selects with fetch and/or offset (DERBY-5911).
void	testSelectSubqueriesOrderBy() SELECT subqueries with ORDER BY
void	testSelectSubqueriesOrderByAndOffsetFetch() SELECT subqueries with ORDER BY and OFFSET/FETCH.
void	testSelectSubqueriesOrderByNegative() SELECT subqueries with ORDER BY - negative tests
void	testSelectSubqueriesSortAvoidance() SELECT subqueries with ORDER BY - check sort avoidance
void	testView() Test ORDER BY in a view definition
void	testViewFetchOffset() Test ORDER BY + FETCH/OFFSET in a view definition This test is a variant made my modifying testView with suitable OFFSET/FETCH FIRST clauses.

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

assertCallError, assertCheckTable, assertCommitError, assertCompileError, assertEquals, assertEquals, assertEquals, assertEquivalentDataType, assertErrorCode, assertGetIntError, assertNextError, assertPreparedStatementError, assertResults, assertResults, assertSQLExceptionEquals, assertSQLState, assertSQLState, assertStatementError, assertStatementError, assertStatementError, assertStatementError, assertStatementErrorUnordered, assertTableRowCount, assertUpdateCount, assertUpdateCount, assertWarning, chattyPrepare, chattyPrepareCall, checkAllConsistency, checkEstimatedRowCount, closeStatement, commit, createStatement, createStatement, createStatement, dropTable, dropTable, dropView, dropView, dumpRs, dumpRs, emptyStatementCache, executeQuery, expectCompilationError, expectCompilationError, expectExecutionError, getClientTransactionID, getConnection, getDatabaseProperty, getLastSQLException, goodStatement, goodUpdate, 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, assertEquals, assertExecJavaCmdAsExpected, assertLaunchedJUnitTestMethod, assertLaunchedJUnitTestMethod, assertSameNullness, assertSecurityManager, assertThrowableEquals, currentDirectory, execJavaCmd, execJavaCmd, execJavaCmd, fail, getClassVersionMajor, getEmmaJar, getFailureFolder, getFilesWith, getJavaExecutableName, getSystemProperty, getTestConfiguration, getTestResource, hasInterruptibleIO, isCVM, isIBMJVM, isJ9Platform, isJava5, isJava7, isJava8, isPhoneME, isPlatform, isSunJVM, isWindowsPlatform, newAssertionFailedError, openTestResource, println, printStackTrace, readProcessOutput, removeDirectory, removeDirectory, removeFiles, removeSystemProperty, runsWithEmma, runsWithJaCoCo, setSystemErr, setSystemOut, setSystemProperty, sleep, sleepAtLeastOneTick, traceit, vmAtLeast

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

static final java.lang.String SYNTAX_ERROR

See Also:

Constant Field Values

COLUMN_NOT_FOUND

static final java.lang.String COLUMN_NOT_FOUND

See Also:

Constant Field Values

COLUMN_OUT_OF_RANGE

static final java.lang.String COLUMN_OUT_OF_RANGE

See Also:

Constant Field Values

ORDER_BY_COLUMN_NOT_FOUND

static final java.lang.String ORDER_BY_COLUMN_NOT_FOUND

See Also:

Constant Field Values

Constructor Detail

OrderByAndOffsetFetchInSubqueries

public OrderByAndOffsetFetchInSubqueries(java.lang.String name)

Method Detail

suite

public static junit.framework.Test suite()

Construct top level suite in this JUnit test

Returns:

A suite containing embedded and client suites.

makeSuite

private static junit.framework.Test makeSuite()

Construct suite of tests

Returns:

A suite containing the test cases.

testInsertSelectOrderBy

public void testInsertSelectOrderBy()
                             throws java.sql.SQLException

Test INSERT INTO t SELECT .. FROM .. ORDER BY.

Throws:

java.sql.SQLException

testInsertSelectOrderByOffsetFetch

public void testInsertSelectOrderByOffsetFetch()
                                        throws java.sql.SQLException

Same test as testInsertSelectOrderBy but with use of OFFSET/FETCH FIRST.

Test INSERT INTO t SELECT .. FROM .. ORDER BY + OFFSET FETCH

This test is a variant made my modifying testInsertSelectOrderBy with suitable OFFSET/FETCH FIRST clauses.

Throws:

java.sql.SQLException

testSelectSubqueriesOrderBy

public void testSelectSubqueriesOrderBy()
                                 throws java.sql.SQLException

SELECT subqueries with ORDER BY

Throws:

java.sql.SQLException

testSelectSubqueriesOrderByAndOffsetFetch

public void testSelectSubqueriesOrderByAndOffsetFetch()
                                               throws java.sql.SQLException

SELECT subqueries with ORDER BY and OFFSET/FETCH.

This test is a variant made my modifying testSelectSubqueriesOrderBy with suitable OFFSET/FETCH FIRST clauses.

Throws:

java.sql.SQLException

testJoinsWithOffsetFetch

public void testJoinsWithOffsetFetch()
                              throws java.sql.SQLException

Test JOIN with delimited subqueries

Throws:

java.sql.SQLException

testView

public void testView()
              throws java.sql.SQLException

Test ORDER BY in a view definition

Throws:

java.sql.SQLException

testViewFetchOffset

public void testViewFetchOffset()
                         throws java.sql.SQLException

Test ORDER BY + FETCH/OFFSET in a view definition

This test is a variant made my modifying testView with suitable OFFSET/FETCH FIRST clauses.

Throws:

java.sql.SQLException

testSelectSubqueriesOrderByNegative

public void testSelectSubqueriesOrderByNegative()
                                         throws java.sql.SQLException

SELECT subqueries with ORDER BY - negative tests

Throws:

java.sql.SQLException

testSelectSubqueriesSortAvoidance

public void testSelectSubqueriesSortAvoidance()
                                       throws java.sql.SQLException

SELECT subqueries with ORDER BY - check sort avoidance

Throws:

java.sql.SQLException

testPushAvoidance

public void testPushAvoidance()
                       throws java.sql.SQLException

Prevent pushing of where predicates into selects with fetch and/or offset (DERBY-5911). Similarly, for windowed selects.

Throws:

java.sql.SQLException

testNestingInsideSetOperation

public void testNestingInsideSetOperation()
                                   throws java.sql.SQLException

Test nesting inside set operands, cf. this production in SQL 2011, section 7.12:

  ::=
      
   |   
      [  ] [  ]
      [  ] 
 

The corresponding production in sqlgrammar.jj is nonJoinQueryPrimary. Cf. DERBY-6008.

Throws:

java.sql.SQLException

testDerby6378

public void testDerby6378()
                   throws java.lang.Exception

Nested query expression body, with each level contributing to the set of ORDER BY and/or OFFSET/FETCH FIRST clauses. Cf. these productions in SQL 2011, section 7.11:

  ::=
    [  ] 
    [  ] [  ]
    [  ]

  ::=
      ...
 

One of the productions of <query expression body>, is

    <left paren> <query expression body>
    [  ] [  ]
    [  ] 
 

so our clauses nests to arbitrary depth given enough parentheses, including ORDER BY and OFFSET/FETCH FIRST clauses. This nesting did not work correctly, cf. DERBY-6378. The corresponding productions in sqlgrammar.jj is queryExpression and nonJoinQueryPrimary.

Throws:

java.lang.Exception

queryAndCheck

private void queryAndCheck(java.sql.Statement stm,
                           java.lang.String queryText,
                           java.lang.String[][] expectedRows)
                    throws java.sql.SQLException

Throws:

java.sql.SQLException