phoenix-core/src/it/java/org/apache/phoenix/end2end/AggregateIT.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.phoenix.end2end;

import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
import static org.apache.phoenix.util.TestUtil.assertResultSet;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData;
import org.apache.phoenix.schema.AmbiguousColumnException;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.QueryBuilder;
import org.apache.phoenix.util.TestUtil;
import org.junit.Test;

public class AggregateIT extends BaseAggregateIT {

    @Test
    public void testGroupByWithAliasWithSameColumnName() throws SQLException {
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String tableName1 = generateUniqueName();
        String tableName2 = generateUniqueName();
        String tableName3 = generateUniqueName();
        String ddl = "create table " + tableName1 + " (pk integer primary key, col integer)";
        conn.createStatement().execute(ddl);
        ddl = "create table " + tableName2 + " (pk integer primary key, col integer)";
        conn.createStatement().execute(ddl);
        ddl = "create table " + tableName3 + " (notPk integer primary key, col integer)";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("UPSERT INTO " + tableName1 + " VALUES (1,2)");
        conn.createStatement().execute("UPSERT INTO " + tableName2 + " VALUES (1,2)");
        conn.createStatement().execute("UPSERT INTO " + tableName3 + " VALUES (1,2)");
        conn.createStatement().executeQuery("select " + tableName1 + ".pk as pk from " + tableName1 + " group by pk");
        conn.createStatement().executeQuery("select " + tableName1 + ".pk as pk from " + tableName1 + " group by " + tableName1 + ".pk");
        conn.createStatement().executeQuery("select " + tableName1 + ".pk as pk from " + tableName1 + " as t group by t.pk");
        conn.createStatement().executeQuery("select " + tableName1 + ".col as pk from " + tableName1);
        conn.createStatement()
                .executeQuery("select " + tableName1 + ".pk as pk from " + tableName1 + " join " + tableName3 + " on (" + tableName1 + ".pk=" + tableName3 + ".notPk) group by pk");
        try {
            conn.createStatement().executeQuery("select " + tableName1 + ".col as pk from " + tableName1 + " group by pk");
            fail();
        } catch (AmbiguousColumnException e) {}
        try {
            conn.createStatement().executeQuery("select col as pk from " + tableName1 + " group by pk");
            fail();
        } catch (AmbiguousColumnException e) {}
        try {
            conn.createStatement()
                    .executeQuery("select " + tableName1 + ".pk as pk from " + tableName1 + " join " + tableName2 + " on (" + tableName1 + ".pk=" + tableName2 + ".pk) group by pk");
            fail();
        } catch (AmbiguousColumnException e) {}
        conn.close();
    }

    @Test
    public void testGroupByCoerceExpressionBug3453() throws Exception {
        final Connection conn = DriverManager.getConnection(getUrl());
        try {
            //Type is INT
            String intTableName=generateUniqueName();
            String sql="CREATE TABLE "+ intTableName +"("+
                    "ENTITY_ID INTEGER NOT NULL,"+
                    "CONTAINER_ID INTEGER NOT NULL,"+
                    "SCORE INTEGER NOT NULL,"+
                    "CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DESC,SCORE DESC))";

            conn.createStatement().execute(sql);
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (1,1,1)");
            conn.commit();

            sql="select DISTINCT entity_id, score from ( select entity_id, score from "+intTableName+" limit 1)";
            ResultSet rs=conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{1,1}});

            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (2,2,2)");
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (3,3,3)");
            conn.commit();

            sql="select DISTINCT entity_id, score from ( select entity_id, score from "+intTableName+" limit 3) order by entity_id";
            rs=conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{1,1},{2,2},{3,3}});

            sql="select DISTINCT entity_id, score from ( select entity_id, score from "+intTableName+" limit 3) order by entity_id desc";
            rs=conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{3,3},{2,2},{1,1}});

            //Type is CHAR
            String charTableName=generateUniqueName();
            sql="CREATE TABLE "+ charTableName +"("+
                    "ENTITY_ID CHAR(15) NOT NULL,"+
                    "CONTAINER_ID INTEGER NOT NULL,"+
                    "SCORE INTEGER NOT NULL,"+
                    "CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DESC,SCORE DESC))";

            conn.createStatement().execute(sql);
            conn.createStatement().execute("UPSERT INTO "+charTableName+" VALUES ('entity1',1,1)");
            conn.createStatement().execute("UPSERT INTO "+charTableName+" VALUES ('entity2',2,2)");
            conn.createStatement().execute("UPSERT INTO "+charTableName+" VALUES ('entity3',3,3)");
            conn.commit();

            sql="select DISTINCT entity_id, score from ( select entity_id, score from "+charTableName+" limit 3) order by entity_id";
            rs=conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"entity1",1},{"entity2",2},{"entity3",3}});

            sql="select DISTINCT entity_id, score from ( select entity_id, score from "+charTableName+" limit 3) order by entity_id desc";
            rs=conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"entity3",3},{"entity2",2},{"entity1",1}});
        } finally {
            if(conn!=null) {
                conn.close();
            }
        }
    }

    @Test
    public void testNestedGroupedAggregationWithBigInt() throws Exception {
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        String tableName = generateUniqueName();
        try(Connection conn = DriverManager.getConnection(getUrl(), props);) {
            String createQuery="CREATE TABLE "+tableName+" (a BIGINT NOT NULL,c BIGINT NOT NULL CONSTRAINT PK PRIMARY KEY (a, c))";
            String updateQuery="UPSERT INTO "+tableName+"(a,c) VALUES(4444444444444444444, 5555555555555555555)";
            String query="SELECT a FROM (SELECT a, c FROM "+tableName+" GROUP BY a, c) GROUP BY a, c";
            conn.prepareStatement(createQuery).execute();
            conn.prepareStatement(updateQuery).execute();
            conn.commit();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(4444444444444444444L,rs.getLong(1));
            assertFalse(rs.next());
        }
    }

    @Test
    public void testAvgGroupByOrderPreservingWithStats() throws Exception {
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String tableName = generateUniqueName();
        QueryBuilder queryBuilder = new QueryBuilder()
            .setSelectExpression("COUNT(*)")
            .setFullTableName(PhoenixDatabaseMetaData.SYSTEM_STATS_NAME)
            .setWhereClause(PhoenixDatabaseMetaData.PHYSICAL_NAME + " ='" + tableName + "'");
        ResultSet rs = executeQuery(conn, queryBuilder);
        assertTrue(rs.next());
        assertEquals(0,rs.getInt(1));
        initAvgGroupTable(conn, tableName, PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=20 ");
        testAvgGroupByOrderPreserving(conn, tableName, 13);
        rs = executeQuery(conn, queryBuilder);
        assertTrue(rs.next());
        assertEquals(13,rs.getInt(1));
        conn.setAutoCommit(true);
        conn.createStatement().execute("DELETE FROM " + "\"SYSTEM\".\"STATS\"");
        rs = executeQuery(conn, queryBuilder);
        assertTrue(rs.next());
        assertEquals(0,rs.getInt(1));
        TestUtil.doMajorCompaction(conn, tableName);
        rs = executeQuery(conn, queryBuilder);
        assertTrue(rs.next());
        assertEquals(13,rs.getInt(1));
        testAvgGroupByOrderPreserving(conn, tableName, 13);
        conn.createStatement().execute("ALTER TABLE " + tableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=100");
        testAvgGroupByOrderPreserving(conn, tableName, 6);
        conn.createStatement().execute("ALTER TABLE " + tableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=null");
        testAvgGroupByOrderPreserving(conn, tableName, 4);
    }

    @Override
    protected void testCountNullInNonEmptyKeyValueCF(int columnEncodedBytes) throws Exception {
        try (Connection conn = DriverManager.getConnection(getUrl())) {
            //Type is INT
            String intTableName=generateUniqueName();
            String sql="create table " + intTableName + " (mykey integer not null primary key, A.COLA integer, B.COLB integer) "
                    + "IMMUTABLE_ROWS=true, IMMUTABLE_STORAGE_SCHEME = ONE_CELL_PER_COLUMN, COLUMN_ENCODED_BYTES = " + columnEncodedBytes + ", DISABLE_WAL=true";

            conn.createStatement().execute(sql);
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (1,1)");
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (2,1)");
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (3,1,2)");
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (4,1)");
            conn.createStatement().execute("UPSERT INTO "+intTableName+" VALUES (5,1)");
            conn.commit();

            sql="select count(*) from "+intTableName;
            QueryBuilder queryBuilder = new QueryBuilder()
                .setSelectExpression("COUNT(*)")
                .setFullTableName(intTableName);
            ResultSet rs = executeQuery(conn, queryBuilder);
            assertTrue(rs.next());
            assertEquals(5, rs.getLong(1));

            sql="select count(*) from "+intTableName + " where b.colb is not null";
            queryBuilder.setWhereClause("B.COLB IS NOT NULL");
            rs = executeQuery(conn, queryBuilder);
            assertTrue(rs.next());
            assertEquals(1, rs.getLong(1));

            sql="select count(*) from "+intTableName + " where b.colb is null";
            queryBuilder.setWhereClause("B.COLB IS NULL");
            rs = executeQuery(conn, queryBuilder);
            assertTrue(rs.next());
            assertEquals(4, rs.getLong(1));
        }
    }

    @Test
    public void testOrderByOptimizeForClientAggregatePlanBug4820() throws Exception {
        doTestOrderByOptimizeForClientAggregatePlanBug4820(false,false);
        doTestOrderByOptimizeForClientAggregatePlanBug4820(false,true);
        doTestOrderByOptimizeForClientAggregatePlanBug4820(true,false);
        doTestOrderByOptimizeForClientAggregatePlanBug4820(true,true);
    }

    private void doTestOrderByOptimizeForClientAggregatePlanBug4820(boolean desc ,boolean salted) throws Exception {
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(getUrl(), props);
            String tableName = generateUniqueName();
            String sql = "create table " + tableName + "( "+
                    " pk1 varchar not null , " +
                    " pk2 varchar not null, " +
                    " pk3 varchar not null," +
                    " v1 varchar, " +
                    " v2 varchar, " +
                    " CONSTRAINT TEST_PK PRIMARY KEY ( "+
                    "pk1 "+(desc ? "desc" : "")+", "+
                    "pk2 "+(desc ? "desc" : "")+", "+
                    "pk3 "+(desc ? "desc" : "")+
                    " )) "+(salted ? "SALT_BUCKETS =4" : "split on('b')");
            conn.createStatement().execute(sql);

            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('a11','a12','a13','a14','a15')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('a21','a22','a23','a24','a25')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('a31','a32','a33','a34','a35')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('b11','b12','b13','b14','b15')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('b21','b22','b23','b24','b25')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES ('b31','b32','b33','b34','b35')");
            conn.commit();

            sql = "select a.ak3 "+
                  "from (select pk1 ak1,pk2 ak2,pk3 ak3, substr(v1,1,1) av1,substr(v2,1,1) av2 from "+tableName+" order by pk2,pk3 limit 10) a "+
                  "group by a.ak3,a.av1 order by a.ak3 desc,a.av1";
            ResultSet rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"b33"},{"b23"},{"b13"},{"a33"},{"a23"},{"a13"}});

            sql = "select a.ak3 "+
                  "from (select pk1 ak1,pk2 ak2,pk3 ak3, substr(v1,1,1) av1,substr(v2,1,1) av2 from "+tableName+" order by pk2,pk3 limit 10) a "+
                  "group by a.ak3,a.av1 order by a.ak3,a.av1";
            rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"a13"},{"a23"},{"a33"},{"b13"},{"b23"},{"b33"}});

            sql = "select a.ak3 "+
                  "from (select pk1 ak1,pk2 ak2,pk3 ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from "+tableName+" order by pk2,pk3 limit 10) a "+
                  "where a.av1 = 'a' group by a.av1,a.ak3 order by a.ak3 desc";
            rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"a33"},{"a23"},{"a13"}});

            sql = "select a.ak3 "+
                    "from (select pk1 ak1,pk2 ak2,pk3 ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from "+tableName+" order by pk2,pk3 limit 10) a "+
                    "where a.av1 = 'a' group by a.av1,a.ak3 order by a.ak3";
            rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"a13"},{"a23"},{"a33"}});

            sql = "select a.ak3 "+
                    "from (select pk1 ak1,pk2 ak2,pk3 ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from "+tableName+" order by pk2,pk3 limit 10) a "+
                    "where a.av1 = 'b' and a.av2= 'b' group by CASE WHEN a.av1 > a.av2 THEN a.av1 ELSE a.av2 END,a.ak3,a.ak2 order by a.ak3 desc,a.ak2 desc";
            rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"b33"},{"b23"},{"b13"}});

            sql = "select a.ak3 "+
                    "from (select pk1 ak1,pk2 ak2,pk3 ak3,substr(v1,1,1) av1,substr(v2,1,1) av2 from "+tableName+" order by pk2,pk3 limit 10) a "+
                    "where a.av1 = 'b' and a.av2= 'b' group by CASE WHEN a.av1 > a.av2 THEN a.av1 ELSE a.av2 END,a.ak3,a.ak2 order by a.ak3,a.ak2 desc";
            rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"b13"},{"b23"},{"b33"}});

            tableName = generateUniqueName();
            sql = "create table " + tableName + "( "+
                    " pk1 double not null , " +
                    " pk2 double not null, " +
                    " pk3 double not null," +
                    " v1 varchar, " +
                    " CONSTRAINT TEST_PK PRIMARY KEY ( "+
                    "pk1 "+(desc ? "desc" : "")+", "+
                    "pk2 "+(desc ? "desc" : "")+", "+
                    "pk3 "+(desc ? "desc" : "")+
                    " )) "+(salted ? "SALT_BUCKETS =4" : "split on(2.3)");
            conn.createStatement().execute(sql);
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2.1,2.11,2.12,'e')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2.2,2.21,2.23,'d')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2.3,2.31,2.32,'c')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2.4,2.41,2.42,'b')");
            conn.createStatement().execute("UPSERT INTO "+tableName+" VALUES (2.5,2.51,2.52,'a')");
            conn.commit();

            sql = "select a.av1 "+
                  "from (select pk1 ak1,pk2 ak2,pk3 ak3, substr(v1,1,1) av1 from "+tableName+" order by pk1,pk2 limit 10) a "+
                  "where cast(a.ak1 as integer)=2 group by a.ak1,a.av1 order by a.av1";
            rs = conn.prepareStatement(sql).executeQuery();
            assertResultSet(rs, new Object[][]{{"a"},{"b"},{"c"},{"d"},{"e"}});

        } finally {
            if(conn != null) {
                conn.close();
            }
        }
    }

}