in phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseOrderByWithRegionMovesIT.java [899:1308]
private void doTestOrderByReverseOptimizationWithNullsLast(boolean salted, boolean desc1,
boolean desc2, boolean desc3)
throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
String tableName = generateUniqueName();
TABLE_NAMES.add(tableName);
String sql = "CREATE TABLE " + tableName + " ( " +
"ORGANIZATION_ID VARCHAR," +
"CONTAINER_ID VARCHAR," +
"SCORE VARCHAR," +
"ENTITY_ID VARCHAR NOT NULL," +
"CONSTRAINT TEST_PK PRIMARY KEY ( " +
"ORGANIZATION_ID" + (desc1 ? " DESC" : "") + "," +
"CONTAINER_ID" + (desc2 ? " DESC" : "") + "," +
"SCORE" + (desc3 ? " DESC" : "") + "," +
"ENTITY_ID" +
")) " + (salted ? "SALT_BUCKETS =4" : "split on('4')");
conn.createStatement().execute(sql);
for (int i = 1; i <= 6; i++) {
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES (null,'" + i + "','" + i + "','" + i +
"')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES (null,'" + i + "',null,'" + i + "')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES (null,null,'" + i + "','" + i + "')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES (null,null,null,'" + i + "')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES ('" + i + "','" + i + "','" + i +
"','" + i + "')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES ('" + i + "','" + i + "',null,'" + i +
"')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES ('" + i + "',null,'" + i + "','" + i +
"')");
conn.createStatement().execute(
"UPSERT INTO " + tableName + " VALUES ('" + i + "',null,null,'" + i + "')");
}
conn.createStatement()
.execute("UPSERT INTO " + tableName + " VALUES (null,null,null,'66')");
conn.commit();
//groupBy orderPreserving orderBy asc asc
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
QueryBuilder queryBuilder = new QueryBuilder()
.setSelectColumns(
Lists.newArrayList("ORGANIZATION_ID", "CONTAINER_ID"))
.setFullTableName(tableName)
.setGroupByClause("ORGANIZATION_ID, CONTAINER_ID")
.setOrderByClause(
"ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST");
ResultSet rs = executeQuery(conn, queryBuilder);
assertResultSetWithRegionMoves(rs,
new Object[][]{{null, null}, {null, "1"}, {null, "2"}, {null, "3"}, {null, "4"},
{null, "5"}, {null, "6"}, {"1", null}, {"1", "1"}, {"2", null},
{"2", "2"}, {"3", null}, {"3", "3"}, {"4", null}, {"4", "4"},
{"5", null}, {"5", "5"}, {"6", null}, {"6", "6"}}, tableName);
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "1"}, {null, "2"}, {null, "3"}, {null, "4"}, {null, "5"},
{null, "6"}, {null, null}, {"1", "1"}, {"1", null}, {"2", "2"},
{"2", null}, {"3", "3"}, {"3", null}, {"4", "4"}, {"4", null},
{"5", "5"}, {"5", null}, {"6", "6"}, {"6", null}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "1"}, {null, "2"}, {null, "3"}, {null, "4"}, {null, "5"},
{null, "6"}, {null, null}, {"1", "1"}, {"1", null}, {"2", "2"},
{"2", null}, {"3", "3"}, {"3", null}, {"4", "4"}, {"4", null},
{"5", "5"}, {"5", null}, {"6", "6"}, {"6", null}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", "1"}, {"1", null}, {"2", "2"}, {"2", null}, {"3", "3"},
{"3", null}, {"4", "4"}, {"4", null}, {"5", "5"}, {"5", null},
{"6", "6"}, {"6", null}, {null, "1"}, {null, "2"}, {null, "3"},
{null, "4"}, {null, "5"}, {null, "6"}, {null, null}});
//groupBy orderPreserving orderBy asc desc
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "6"}, {null, "5"}, {null, "4"}, {null, "3"},
{null, "2"}, {null, "1"}, {"1", null}, {"1", "1"}, {"2", null},
{"2", "2"}, {"3", null}, {"3", "3"}, {"4", null}, {"4", "4"},
{"5", null}, {"5", "5"}, {"6", null}, {"6", "6"}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS FIRST, CONTAINER_ID DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSetWithRegionMoves(rs,
new Object[][]{{null, "6"}, {null, "5"}, {null, "4"}, {null, "3"}, {null, "2"},
{null, "1"}, {null, null}, {"1", "1"}, {"1", null}, {"2", "2"},
{"2", null}, {"3", "3"}, {"3", null}, {"4", "4"}, {"4", null},
{"5", "5"}, {"5", null}, {"6", "6"}, {"6", null}}, tableName);
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", null}, {"1", "1"}, {"2", null}, {"2", "2"}, {"3", null},
{"3", "3"}, {"4", null}, {"4", "4"}, {"5", null}, {"5", "5"},
{"6", null}, {"6", "6"}, {null, null}, {null, "6"}, {null, "5"},
{null, "4"}, {null, "3"}, {null, "2"}, {null, "1"}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS LAST, CONTAINER_ID DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", "1"}, {"1", null}, {"2", "2"}, {"2", null}, {"3", "3"},
{"3", null}, {"4", "4"}, {"4", null}, {"5", "5"}, {"5", null},
{"6", "6"}, {"6", null}, {null, "6"}, {null, "5"}, {null, "4"},
{null, "3"}, {null, "2"}, {null, "1"}, {null, null}});
//groupBy orderPreserving orderBy desc asc
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "1"}, {null, "2"}, {null, "3"}, {null, "4"},
{null, "5"}, {null, "6"}, {"6", null}, {"6", "6"}, {"5", null},
{"5", "5"}, {"4", null}, {"4", "4"}, {"3", null}, {"3", "3"},
{"2", null}, {"2", "2"}, {"1", null}, {"1", "1"}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "1"}, {null, "2"}, {null, "3"}, {null, "4"}, {null, "5"},
{null, "6"}, {null, null}, {"6", "6"}, {"6", null}, {"5", "5"},
{"5", null}, {"4", "4"}, {"4", null}, {"3", "3"}, {"3", null},
{"2", "2"}, {"2", null}, {"1", "1"}, {"1", null}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", null}, {"6", "6"}, {"5", null}, {"5", "5"}, {"4", null},
{"4", "4"}, {"3", null}, {"3", "3"}, {"2", null}, {"2", "2"},
{"1", null}, {"1", "1"}, {null, null}, {null, "1"}, {null, "2"},
{null, "3"}, {null, "4"}, {null, "5"}, {null, "6"}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", "6"}, {"6", null}, {"5", "5"}, {"5", null}, {"4", "4"},
{"4", null}, {"3", "3"}, {"3", null}, {"2", "2"}, {"2", null},
{"1", "1"}, {"1", null}, {null, "1"}, {null, "2"}, {null, "3"},
{null, "4"}, {null, "5"}, {null, "6"}, {null, null}});
//groupBy orderPreserving orderBy desc desc
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "6"}, {null, "5"}, {null, "4"}, {null, "3"},
{null, "2"}, {null, "1"}, {"6", null}, {"6", "6"}, {"5", null},
{"5", "5"}, {"4", null}, {"4", "4"}, {"3", null}, {"3", "3"},
{"2", null}, {"2", "2"}, {"1", null}, {"1", "1"}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, CONTAINER_ID DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "6"}, {null, "5"}, {null, "4"}, {null, "3"}, {null, "2"},
{null, "1"}, {null, null}, {"6", "6"}, {"6", null}, {"5", "5"},
{"5", null}, {"4", "4"}, {"4", null}, {"3", "3"}, {"3", null},
{"2", "2"}, {"2", null}, {"1", "1"}, {"1", null}});
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSetWithRegionMoves(rs,
new Object[][]{{"6", null}, {"6", "6"}, {"5", null}, {"5", "5"}, {"4", null},
{"4", "4"}, {"3", null}, {"3", "3"}, {"2", null}, {"2", "2"},
{"1", null}, {"1", "1"}, {null, null}, {null, "6"}, {null, "5"},
{null, "4"}, {null, "3"}, {null, "2"}, {null, "1"}}, tableName);
// sql="SELECT ORGANIZATION_ID,CONTAINER_ID FROM "+tableName+" group by ORGANIZATION_ID, CONTAINER_ID ORDER BY ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS LAST, CONTAINER_ID DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", "6"}, {"6", null}, {"5", "5"}, {"5", null}, {"4", "4"},
{"4", null}, {"3", "3"}, {"3", null}, {"2", "2"}, {"2", null},
{"1", "1"}, {"1", null}, {null, "6"}, {null, "5"}, {null, "4"},
{null, "3"}, {null, "2"}, {null, "1"}, {null, null}});
//-----groupBy not orderPreserving--
//groupBy not orderPreserving orderBy asc asc
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST";
queryBuilder.setSelectColumns(
Lists.newArrayList("ORGANIZATION_ID", "SCORE"))
.setFullTableName(tableName)
.setGroupByClause("ORGANIZATION_ID, SCORE")
.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "1"}, {null, "2"}, {null, "3"}, {null, "4"},
{null, "5"}, {null, "6"}, {"1", null}, {"1", "1"}, {"2", null},
{"2", "2"}, {"3", null}, {"3", "3"}, {"4", null}, {"4", "4"},
{"5", null}, {"5", "5"}, {"6", null}, {"6", "6"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "1"}, {null, "2"}, {null, "3"}, {null, "4"}, {null, "5"},
{null, "6"}, {null, null}, {"1", "1"}, {"1", null}, {"2", "2"},
{"2", null}, {"3", "3"}, {"3", null}, {"4", "4"}, {"4", null},
{"5", "5"}, {"5", null}, {"6", "6"}, {"6", null}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST";
queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", null}, {"1", "1"}, {"2", null}, {"2", "2"}, {"3", null},
{"3", "3"}, {"4", null}, {"4", "4"}, {"5", null}, {"5", "5"},
{"6", null}, {"6", "6"}, {null, null}, {null, "1"}, {null, "2"},
{null, "3"}, {null, "4"}, {null, "5"}, {null, "6"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", "1"}, {"1", null}, {"2", "2"}, {"2", null}, {"3", "3"},
{"3", null}, {"4", "4"}, {"4", null}, {"5", "5"}, {"5", null},
{"6", "6"}, {"6", null}, {null, "1"}, {null, "2"}, {null, "3"},
{null, "4"}, {null, "5"}, {null, "6"}, {null, null}});
//groupBy not orderPreserving orderBy asc desc
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "6"}, {null, "5"}, {null, "4"}, {null, "3"},
{null, "2"}, {null, "1"}, {"1", null}, {"1", "1"}, {"2", null},
{"2", "2"}, {"3", null}, {"3", "3"}, {"4", null}, {"4", "4"},
{"5", null}, {"5", "5"}, {"6", null}, {"6", "6"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST, SCORE DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "6"}, {null, "5"}, {null, "4"}, {null, "3"}, {null, "2"},
{null, "1"}, {null, null}, {"1", "1"}, {"1", null}, {"2", "2"},
{"2", null}, {"3", "3"}, {"3", null}, {"4", "4"}, {"4", null},
{"5", "5"}, {"5", null}, {"6", "6"}, {"6", null}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST";
queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", null}, {"1", "1"}, {"2", null}, {"2", "2"}, {"3", null},
{"3", "3"}, {"4", null}, {"4", "4"}, {"5", null}, {"5", "5"},
{"6", null}, {"6", "6"}, {null, null}, {null, "6"}, {null, "5"},
{null, "4"}, {null, "3"}, {null, "2"}, {null, "1"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST, SCORE DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"1", "1"}, {"1", null}, {"2", "2"}, {"2", null}, {"3", "3"},
{"3", null}, {"4", "4"}, {"4", null}, {"5", "5"}, {"5", null},
{"6", "6"}, {"6", null}, {null, "6"}, {null, "5"}, {null, "4"},
{null, "3"}, {null, "2"}, {null, "1"}, {null, null}});
//groupBy not orderPreserving orderBy desc asc
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "1"}, {null, "2"}, {null, "3"}, {null, "4"},
{null, "5"}, {null, "6"}, {"6", null}, {"6", "6"}, {"5", null},
{"5", "5"}, {"4", null}, {"4", "4"}, {"3", null}, {"3", "3"},
{"2", null}, {"2", "2"}, {"1", null}, {"1", "1"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST, SCORE ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSetWithRegionMoves(rs,
new Object[][]{{null, "1"}, {null, "2"}, {null, "3"}, {null, "4"}, {null, "5"},
{null, "6"}, {null, null}, {"6", "6"}, {"6", null}, {"5", "5"},
{"5", null}, {"4", "4"}, {"4", null}, {"3", "3"}, {"3", null},
{"2", "2"}, {"2", null}, {"1", "1"}, {"1", null}}, tableName);
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST";
queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", null}, {"6", "6"}, {"5", null}, {"5", "5"}, {"4", null},
{"4", "4"}, {"3", null}, {"3", "3"}, {"2", null}, {"2", "2"},
{"1", null}, {"1", "1"}, {null, null}, {null, "1"}, {null, "2"},
{null, "3"}, {null, "4"}, {null, "5"}, {null, "6"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", "6"}, {"6", null}, {"5", "5"}, {"5", null}, {"4", "4"},
{"4", null}, {"3", "3"}, {"3", null}, {"2", "2"}, {"2", null},
{"1", "1"}, {"1", null}, {null, "1"}, {null, "2"}, {null, "3"},
{null, "4"}, {null, "5"}, {null, "6"}, {null, null}});
//groupBy not orderPreserving orderBy desc desc
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, null}, {null, "6"}, {null, "5"}, {null, "4"}, {null, "3"},
{null, "2"}, {null, "1"}, {"6", null}, {"6", "6"}, {"5", null},
{"5", "5"}, {"4", null}, {"4", "4"}, {"3", null}, {"3", "3"},
{"2", null}, {"2", "2"}, {"1", null}, {"1", "1"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS FIRST, SCORE DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{null, "6"}, {null, "5"}, {null, "4"}, {null, "3"}, {null, "2"},
{null, "1"}, {null, null}, {"6", "6"}, {"6", null}, {"5", "5"},
{"5", null}, {"4", "4"}, {"4", null}, {"3", "3"}, {"3", null},
{"2", "2"}, {"2", null}, {"1", "1"}, {"1", null}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST";
queryBuilder.setOrderByClause(
"ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", null}, {"6", "6"}, {"5", null}, {"5", "5"}, {"4", null},
{"4", "4"}, {"3", null}, {"3", "3"}, {"2", null}, {"2", "2"},
{"1", null}, {"1", "1"}, {null, null}, {null, "6"}, {null, "5"},
{null, "4"}, {null, "3"}, {null, "2"}, {null, "1"}});
// sql="SELECT ORGANIZATION_ID,SCORE FROM "+tableName+" group by ORGANIZATION_ID, SCORE ORDER BY ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST";
queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST, SCORE DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs,
new Object[][]{{"6", "6"}, {"6", null}, {"5", "5"}, {"5", null}, {"4", "4"},
{"4", null}, {"3", "3"}, {"3", null}, {"2", "2"}, {"2", null},
{"1", "1"}, {"1", null}, {null, "6"}, {null, "5"}, {null, "4"},
{null, "3"}, {null, "2"}, {null, "1"}, {null, null}});
//-------test only one return column----------------------------------
// sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS FIRST";
queryBuilder.setSelectColumns(
Lists.newArrayList("SCORE"))
.setFullTableName(tableName)
.setGroupByClause("SCORE")
.setOrderByClause("SCORE ASC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs, new Object[][]{{null}, {"1"}, {"2"}, {"3"}, {"4"}, {"5"}, {"6"}});
// sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE ASC NULLS LAST";
queryBuilder.setOrderByClause("SCORE ASC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSetWithRegionMoves(rs, new Object[][]{{"1"}, {"2"}, {"3"}, {"4"}, {"5"},
{"6"}, {null}}, tableName);
// sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS FIRST";
queryBuilder.setOrderByClause("SCORE DESC NULLS FIRST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs, new Object[][]{{null}, {"6"}, {"5"}, {"4"}, {"3"}, {"2"}, {"1"}});
// sql="SELECT SCORE FROM "+tableName+" group by SCORE ORDER BY SCORE DESC NULLS LAST";
queryBuilder.setOrderByClause("SCORE DESC NULLS LAST");
rs = executeQuery(conn, queryBuilder);
assertResultSet(rs, new Object[][]{{"6"}, {"5"}, {"4"}, {"3"}, {"2"}, {"1"}, {null}});
}
}