public class GroupByTest extends BaseJDBCTestCase
Modifier and Type | Field and Description |
---|---|
private static char[] |
chars |
(package private) java.lang.String[] |
expColNames |
(package private) java.lang.String[][] |
expRS |
DEFAULT_DB_DIR, DERBY_LOG, ERRORSTACKTRACEFILE
Constructor and Description |
---|
GroupByTest(java.lang.String name) |
Modifier and Type | Method and Description |
---|---|
private static void |
assertSetOpErrors(java.lang.String sqlState,
java.sql.Statement s,
java.lang.String[] queries1,
java.lang.String[] queries2)
Try all set operations with queries from
queries1 in the left
operand and queries from queries2 in the right operand. |
private static void |
createSchemaObjects(java.sql.Statement st)
Creates a variety of tables used by the various GroupBy tests
|
private static void |
doAllSetOperations(java.sql.Statement s,
java.lang.String[] queries,
java.lang.String[][][] expectedResults)
Try all set operations (UNION [ALL], EXCEPT [ALL], INTERSECT [ALL]) on
all combinations of the specified queries.
|
private static java.lang.String[][] |
except(java.util.Collection<java.util.List<java.lang.String>> rows1,
java.util.Collection<java.util.List<java.lang.String>> rows2,
boolean all)
Find the difference between two collections of rows (each row is a list
of strings).
|
private static java.lang.String |
genString(int len) |
private static java.lang.String[][] |
intersect(java.util.Collection<java.util.List<java.lang.String>> rows1,
java.util.Collection<java.util.List<java.lang.String>> rows2,
boolean all)
Find the intersection between two collections of rows (each row is a
list of strings).
|
private void |
loadRows()
Load enough rows into the table to get some externalized
MaxMinAggregator instances.
|
private static java.util.Collection<java.util.List<java.lang.String>> |
newBagOrSet(boolean bag)
Create a
Collection that can be used as a bag or a set. |
private static java.util.List<java.util.List<java.lang.String>> |
resultArrayToList(java.lang.String[][] results)
Return a list of lists containing the same values as the specified array
of string arrays.
|
static junit.framework.Test |
suite() |
void |
testColumnAliasInGroupByAndHaving()
DERBY-2457: Derby does not support column aliases in the
GROUP BY and HAVING clauses.
|
void |
testCorrelatedSubqueryInHavingClause()
correlated subquery in having clause.
|
void |
testDERBY2937()
DERBY-2397 showed incorrect typing of aggregate nodes
that lead to a SUBSTR throwing an exception that its
position/length were out of range.
|
void |
testDerby3631AggregateInGroupByQuery()
Test aggregate used in group by query.
|
void |
testDerby3904MinMaxOptimization()
DERBY-3904: Min/Max optimization needs to be aware of joins.
|
void |
testDerby4071AggregateOnGroupByColumnInHaving()
Test aggregate function on a GROUP BY column also present in a HAVING
clause.
|
void |
testDerby4450()
GROUP BY in an IN-subquery inside HAVING clause whose select list is
subset of group by columns.
|
void |
testDerby4698()
HAVING with an aggregate function in presence of join flattening
DERBY-4698.
|
void |
testDerby5313()
GROUP BY on an expression in a JOIN used to trigger an assert failure.
|
void |
testDerby5584()
DISTINCT aggregates in result sets which are opened multiple times.
|
void |
testDerbyOrderByOnAggregate() |
void |
testDistinctGroupBy()
DERBY-3613 check combinations of DISTINCT and GROUP BY
|
void |
testGroupByColumnsNotInSelectList()
additional columns in group by list not in select list.
|
void |
testGroupByCorrelatedSubquery()
Test using GROUP BY in a correlated subquery.
|
void |
testGroupByDistinct()
Test GROUP BY and DISTINCT.
|
void |
testGroupByErrors()
Test various invalid GROUP BY statements.
|
void |
testGroupByExpression()
Test the use of a subquery to group by an expression.
|
void |
testGroupByInSubquery()
group by without having in from subquery.
|
void |
testGroupByMaxWithEmptyString()
DERBY-3219: Check that MaxMinAggregator's external format works
properly with a string of length 0.
|
void |
testGroupByOrderBy()
Test combinations of GROUP BY and ORDER BY.
|
void |
testGroupByWithAdditionalColumns()
additional columns in group by list not in select list.
|
void |
testGroupByWithAliasToSameName()
DERBY-280: Wrong result from select when aliasing to same name as used
in group by
|
void |
testGroupByWithTempTable()
DERBY-578: select with group by on a temp table caused NPE
|
void |
testGroupByWithUsingClause()
DERBY-5613: Queries with group by column not included in the column list
for JOIN(INNER or OUTER) with NATURAL or USING does not fail
Derby does not replace join columns in the select list with coalesce as
suggested by SQL spec, instead it binds the join column with the left
table when working with natural left outer join or inner join and it
binds the join column with the right table when working with natural
right outer join.
|
void |
testGroupByWithVariousDatatypes()
Verify the correct behavior of GROUP BY with various datatypes.
|
void |
testHavingClauseColumnRef()
column references in having clause match columns in group by list.
|
void |
testHavingClauseInSubquery()
group by with having in from subquery.
|
void |
testHavingClauseRestrictions5653()
Tests for Bug 5653 restrictions.
|
void |
testHavingWithInnerJoinDerby3880() |
void |
testHavingWithoutGroupBy5920()
bug 5920 test that HAVING without GROUPBY makes one group.
|
void |
testInvalidHavingClauses()
negative tests for selects with a having clause without a group by.
|
void |
testMulticolumnGrouping()
Test multicolumn grouping.
|
void |
testOrderByNonGroupedColumn()
DERBY-2085 check message on order by of non-grouped column
|
void |
testOrNodeInHavingClause()
DERBY-3257 check for correct number of rows returned with
or in having clause.
|
void |
testParameterMarkersInHavingClause()
Test parameter markers in the having clause.
|
void |
testSetOperationsAndGroupBy()
Test that GROUP BY can be used in the sub-expressions of set operations
(DERBY-3764).
|
private static java.lang.String[][] |
toResultArray(java.util.Collection<java.util.List<java.lang.String>> rows)
Convert a
Collection of rows to an array of string arrays that
can be passed as an argument with expected results to
JDBC.assertUnorderedResultSet(ResultSet,String[][]) . |
private static java.lang.String[][] |
union(java.util.Collection<java.util.List<java.lang.String>> rows1,
java.util.Collection<java.util.List<java.lang.String>> rows2,
boolean all)
Find the union between two collections of rows (each row is a list of
strings).
|
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
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, isPhoneME, isPlatform, isSunJVM, isWindowsPlatform, newAssertionFailedError, openTestResource, println, printStackTrace, readProcessOutput, removeDirectory, removeDirectory, removeFiles, removeSystemProperty, runsWithEmma, runsWithJaCoCo, setSystemErr, setSystemOut, setSystemProperty, sleep, sleepAtLeastOneTick, traceit, vmAtLeast
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
java.lang.String[][] expRS
java.lang.String[] expColNames
private static char[] chars
public static junit.framework.Test suite()
private static void createSchemaObjects(java.sql.Statement st) throws java.sql.SQLException
java.sql.SQLException
public void testGroupByErrors() throws java.lang.Exception
java.lang.Exception
public void testGroupByWithVariousDatatypes() throws java.lang.Exception
java.lang.Exception
public void testMulticolumnGrouping() throws java.lang.Exception
java.lang.Exception
public void testGroupByExpression() throws java.lang.Exception
java.lang.Exception
public void testGroupByCorrelatedSubquery() throws java.lang.Exception
java.lang.Exception
public void testGroupByDistinct() throws java.lang.Exception
java.lang.Exception
public void testGroupByOrderBy() throws java.lang.Exception
java.lang.Exception
public void testGroupByInSubquery() throws java.lang.Exception
java.lang.Exception
public void testGroupByWithAdditionalColumns() throws java.lang.Exception
java.lang.Exception
public void testParameterMarkersInHavingClause() throws java.lang.Exception
java.lang.Exception
public void testHavingClauseInSubquery() throws java.lang.Exception
java.lang.Exception
public void testCorrelatedSubqueryInHavingClause() throws java.lang.Exception
java.lang.Exception
public void testHavingClauseColumnRef() throws java.lang.Exception
java.lang.Exception
public void testGroupByColumnsNotInSelectList() throws java.lang.Exception
java.lang.Exception
public void testInvalidHavingClauses() throws java.lang.Exception
java.lang.Exception
public void testHavingClauseRestrictions5653() throws java.lang.Exception
java.lang.Exception
public void testHavingWithoutGroupBy5920() throws java.lang.Exception
java.lang.Exception
public void testGroupByWithUsingClause() throws java.sql.SQLException
java.sql.SQLException
public void testGroupByWithTempTable() throws java.sql.SQLException
java.sql.SQLException
public void testHavingWithInnerJoinDerby3880() throws java.sql.SQLException
java.sql.SQLException
public void testGroupByWithAliasToSameName() throws java.sql.SQLException
java.sql.SQLException
public void testDERBY2937() throws java.sql.SQLException
java.sql.SQLException
public void testDerbyOrderByOnAggregate() throws java.sql.SQLException
java.sql.SQLException
public void testOrNodeInHavingClause() throws java.sql.SQLException
java.sql.SQLException
public void testDistinctGroupBy() throws java.sql.SQLException
java.sql.SQLException
public void testOrderByNonGroupedColumn() throws java.sql.SQLException
java.sql.SQLException
public void testGroupByMaxWithEmptyString() throws java.sql.SQLException
java.sql.SQLException
private void loadRows() throws java.sql.SQLException
java.sql.SQLException
private static java.lang.String genString(int len)
public void testColumnAliasInGroupByAndHaving() throws java.sql.SQLException
java.sql.SQLException
public void testSetOperationsAndGroupBy() throws java.sql.SQLException
java.sql.SQLException
private static void doAllSetOperations(java.sql.Statement s, java.lang.String[] queries, java.lang.String[][][] expectedResults) throws java.sql.SQLException
s
- the statement used to execute the queriesqueries
- the different queries to use, all of which must be union
compatibleexpectedResults
- the expected results from the different queriesjava.sql.SQLException
private static void assertSetOpErrors(java.lang.String sqlState, java.sql.Statement s, java.lang.String[] queries1, java.lang.String[] queries2) throws java.sql.SQLException
queries1
in the left
operand and queries from queries2
in the right operand. All the
set operations are expected to fail with the same SQLState.sqlState
- the expected SQLStates
- the statement used to execute the queriesqueries1
- queries to use as the left operandqueries2
- queries to use as the right operandjava.sql.SQLException
private static java.lang.String[][] union(java.util.Collection<java.util.List<java.lang.String>> rows1, java.util.Collection<java.util.List<java.lang.String>> rows2, boolean all)
rows1
- the first collection of rowsrows2
- the second collection of rowsall
- whether or not bag semantics (as in UNION ALL) should be used
instead of set semanticsrows1
and rows2
, as a String[][]
private static java.lang.String[][] except(java.util.Collection<java.util.List<java.lang.String>> rows1, java.util.Collection<java.util.List<java.lang.String>> rows2, boolean all)
rows1
- the first operand to the set difference operatorrows2
- the second operand to the set difference operatorall
- whether or not bag semantics (as in EXCEPT ALL) should be
used instead of set semanticsrows1
and rows2
, as a
String[][]
private static java.lang.String[][] intersect(java.util.Collection<java.util.List<java.lang.String>> rows1, java.util.Collection<java.util.List<java.lang.String>> rows2, boolean all)
rows1
- the first collection of rowsrows2
- the second collection of rowsall
- whether or not bag semantics (as in INTERSECT ALL) should be
used instead of set semanticsrows1
and rows2
, as a
String[][]
private static java.util.Collection<java.util.List<java.lang.String>> newBagOrSet(boolean bag)
Collection
that can be used as a bag or a set.bag
- tells whether or not the collection should be a bagList
if a bag is requested, or a Set
otherwiseprivate static java.lang.String[][] toResultArray(java.util.Collection<java.util.List<java.lang.String>> rows)
Collection
of rows to an array of string arrays that
can be passed as an argument with expected results to
JDBC.assertUnorderedResultSet(ResultSet,String[][])
.rows
- a collection of rows, where each row is a list of stringsString[][]
containing the same values as rows
private static java.util.List<java.util.List<java.lang.String>> resultArrayToList(java.lang.String[][] results)
results
- a two dimensional array of strings (typically expected
results from a query}results
in a list of listspublic void testDerby3904MinMaxOptimization() throws java.sql.SQLException
java.sql.SQLException
public void testDerby3631AggregateInGroupByQuery() throws java.sql.SQLException
java.sql.SQLException
public void testDerby4071AggregateOnGroupByColumnInHaving() throws java.sql.SQLException
java.sql.SQLException
public void testDerby4450() throws java.sql.SQLException
java.sql.SQLException
public void testDerby4698() throws java.sql.SQLException
java.sql.SQLException
public void testDerby5584() throws java.sql.SQLException
java.sql.SQLException
public void testDerby5313() throws java.sql.SQLException
java.sql.SQLException
Apache Derby V10.13 Internals - Copyright © 2004,2016 The Apache Software Foundation. All Rights Reserved.