public void testGroupByDescColumnWithNullsLastBug3452()

in phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseAggregateWithRegionMovesIT.java [571:845]
210 lines of code
2 McCabe index (conditional complexity)

    public void testGroupByDescColumnWithNullsLastBug3452() throws Exception {

        Connection conn = null;
        try {
            Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
            conn = DriverManager.getConnection(getUrl(), props);

            String tableName = generateUniqueName();
            TABLE_NAMES.add(tableName);

            String sql = "CREATE TABLE " + tableName + " ( " +
                    "ORGANIZATION_ID VARCHAR," +
                    "CONTAINER_ID VARCHAR," +
                    "ENTITY_ID VARCHAR NOT NULL," +
                    "CONSTRAINT TEST_PK PRIMARY KEY ( " +
                    "ORGANIZATION_ID DESC," +
                    "CONTAINER_ID DESC," +
                    "ENTITY_ID" +
                    "))";
            conn.createStatement().execute(sql);

            conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('a',null,'11')");
            conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES (null,'2','22')");
            conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('c','3','33')");
            conn.commit();

            //-----ORGANIZATION_ID

            QueryBuilder queryBuilder = new QueryBuilder()
                    .setSelectColumns(
                            Lists.newArrayList("CONTAINER_ID", "ORGANIZATION_ID"))
                    .setFullTableName(tableName)
                    .setGroupByClause("ORGANIZATION_ID, CONTAINER_ID")
                    .setOrderByClause("ORGANIZATION_ID ASC NULLS FIRST");
            ResultSet rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {null, "a"}, {"3", "c"},}, tableName);

            queryBuilder.setOrderByClause("ORGANIZATION_ID ASC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"3", "c"}, {"2", null}}, tableName);

            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {"3", "c"}, {null, "a"}}, tableName);

            queryBuilder.setOrderByClause("ORGANIZATION_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {null, "a"}, {"2", null}}, tableName);

            //----CONTAINER_ID

            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"2", null}, {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause("CONTAINER_ID ASC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {"3", "c"}, {null, "a"}}, tableName);

            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"3", "c"}, {"2", null}}, tableName);

            queryBuilder.setOrderByClause("CONTAINER_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {"2", null}, {null, "a"}}, tableName);

            conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES (null,null,'44')");
            conn.commit();

            //-----ORGANIZATION_ID ASC  CONTAINER_ID ASC

            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {"2", null}, {null, "a"},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS FIRST,CONTAINER_ID NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {null, null}, {null, "a"},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"3", "c"}, {null, null},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause("ORGANIZATION_ID NULLS LAST,CONTAINER_ID NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"3", "c"}, {"2", null},
                    {null, null}}, tableName);

            //-----ORGANIZATION_ID ASC  CONTAINER_ID DESC

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {"2", null}, {null, "a"},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID NULLS FIRST,CONTAINER_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {null, null}, {null, "a"},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"3", "c"}, {null, null},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID NULLS LAST,CONTAINER_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {"3", "c"}, {"2", null},
                    {null, null}}, tableName);

            //-----ORGANIZATION_ID DESC  CONTAINER_ID ASC

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {"2", null}, {"3", "c"},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {null, null}, {"3", "c"},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {null, "a"}, {null, null},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {null, "a"}, {"2", null},
                    {null, null}}, tableName);

            //-----ORGANIZATION_ID DESC  CONTAINER_ID DESC

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {"2", null}, {"3", "c"},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS FIRST,CONTAINER_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {null, null}, {"3", "c"},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {null, "a"}, {null, null},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "ORGANIZATION_ID DESC NULLS LAST,CONTAINER_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {null, "a"}, {"2", null},
                    {null, null}}, tableName);

            //-----CONTAINER_ID ASC  ORGANIZATION_ID ASC

            queryBuilder.setOrderByClause("CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {null, "a"}, {"2", null},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause("CONTAINER_ID NULLS FIRST,ORGANIZATION_ID NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {null, null}, {"2", null},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause("CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {"3", "c"}, {null, null},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause("CONTAINER_ID NULLS LAST,ORGANIZATION_ID NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {"3", "c"}, {null, "a"},
                    {null, null}}, tableName);

            //-----CONTAINER_ID ASC  ORGANIZATION_ID DESC

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {null, "a"}, {"2", null},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID ASC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {null, null}, {"2", null},
                    {"3", "c"}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {"3", "c"}, {null, null},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID ASC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"2", null}, {"3", "c"}, {null, "a"},
                    {null, null}}, tableName);

            //-----CONTAINER_ID DESC  ORGANIZATION_ID ASC

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {null, "a"}, {"3", "c"},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID ASC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {null, null}, {"3", "c"},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {"2", null}, {null, null},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID ASC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {"2", null}, {null, "a"},
                    {null, null}}, tableName);

            //-----CONTAINER_ID DESC  ORGANIZATION_ID DESC

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, null}, {null, "a"}, {"3", "c"},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS FIRST,ORGANIZATION_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{null, "a"}, {null, null}, {"3", "c"},
                    {"2", null}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS FIRST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {"2", null}, {null, null},
                    {null, "a"}}, tableName);

            queryBuilder.setOrderByClause(
                    "CONTAINER_ID DESC NULLS LAST,ORGANIZATION_ID DESC NULLS LAST");
            rs = executeQuery(conn, queryBuilder);
            assertResultSet(rs, new String[][]{{"3", "c"}, {"2", null}, {null, "a"},
                    {null, null}}, tableName);
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }