phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Swaroopa Kadam (Jira)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-5484) Incorrect startRow in scan when RVC is used in the query
Date Wed, 18 Sep 2019 03:05:00 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-5484?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Swaroopa Kadam updated PHOENIX-5484:
------------------------------------
    Description: 
Saw this during internal testing. 

RVC comparator is not returning the correct results. 

 

Following test is attached to show, expected rows are not returned. 

 
{code:java}
@Test
    public void testQueryMore() throws Exception {
        {
            final Connection conn = DriverManager.getConnection(getUrl());
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE TABLE IF NOT EXISTS TEST.PLINY_TEST_IMMUTABLE_INDEXED
(\n"
                        + "    ORGANIZATION_ID CHAR(3) NOT NULL,\n"
                        + "    CREATED_DATE DATE NOT NULL,\n"
                        + "    TEXT VARCHAR NOT NULL,\n"
                        + "    INT INTEGER NOT NULL,\n"
                        + "    BIGINT BIGINT NOT NULL,\n"
                        + "    DOUBLE DECIMAL NOT NULL,\n"
                        + "    DATE_TIME DATE NOT NULL,\n"
                        + "    RELATIONSHIP_ID CHAR(15) NOT NULL,\n"
                        + "    IS_BOOLEAN BOOLEAN NOT NULL,\n"
                        + "    CURRENCY DECIMAL(22,10) NOT NULL,\n"
                        + "    PLINY_TEST_IMMUTABLE_INDEXED_ID CHAR(15) NOT NULL,\n"
                        + "    TEXT_VALUE VARCHAR,\n"
                        + "    CONSTRAINT PK PRIMARY KEY\n"
                        + "    (\n"
                        + "        ORGANIZATION_ID,\n"
                        + "        CREATED_DATE,\n"
                        + "        TEXT,\n"
                        + "        INT,\n"
                        + "        BIGINT,\n"
                        + "        DOUBLE,\n"
                        + "        DATE_TIME,\n"
                        + "        RELATIONSHIP_ID,\n"
                        + "        IS_BOOLEAN,\n"
                        + "        CURRENCY,\n"
                        + "        PLINY_TEST_IMMUTABLE_INDEXED_ID\n"
                        + "    )\n"
                        + ") VERSIONS=1,MULTI_TENANT=true,REPLICATION_SCOPE=1,IMMUTABLE_ROWS=true");
                stmt.execute(
                        "CREATE INDEX IF NOT EXISTS PLINY_TEST_IMMUTABLE_INDEXED_DATETIME_INDEX\n"
+ "ON TEST.PLINY_TEST_IMMUTABLE_INDEXED (DATE_TIME, PLINY_TEST_IMMUTABLE_INDEXED_ID)\n"
                                + "INCLUDE\n" + "   (TEXT_VALUE)");
            }
        }
        {
            TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
            String tenandId = "xyz";
            final Connection conn = getTenantSpecificConnection(tenandId);
            conn.createStatement().execute("CREATE VIEW TEST.\"0Gl\" AS SELECT * FROM TEST.PLINY_TEST_IMMUTABLE_INDEXED");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES('2016-07-01T00:00:00Z','TextValue1',1000,-1,1.1,'2016-07-01T00:00:00Z','001xx000001UoUD',false,1.1,'0Glxx000000000M','')");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES(TO_DATE('2016-07-02T00:00:00Z'),'TextValue2',1001,-1,1.2,TO_DATE('2016-07-02T00:00:00Z'),'001xx000001UoUE',false,1.2,'0Glxx000000000N','')");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES(TO_DATE('2016-07-03T00:00:00Z'),'TextValue3',1001,-1,1.3,TO_DATE('2016-07-03T00:00:00Z'),'001xx000001UoUF',false,1.3,'0Glxx000000000O','')");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES(TO_DATE('2016-07-04T00:00:00Z'),'TextValue4',1001,-1,1.4,TO_DATE('2016-07-04T00:00:00Z'),'001xx000001UoUG',false,1.4,'0Glxx000000000P','')");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES(TO_DATE('2016-07-05T00:00:00Z'),'TextValue5',1000,-1,1.5,TO_DATE('2016-07-05T00:00:00Z'),'001xx000001UoUH',false,1.5,'0Glxx000000000Q','')");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES(TO_DATE('2016-07-06T00:00:00Z'),'TextValue6',1001,-1,1.6,TO_DATE('2016-07-06T00:00:00Z'),'001xx000001UoUJ',false,1.6,'0Glxx000000000R','')");
            conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
                    + "VALUES(TO_DATE('2016-07-07T00:00:00Z'),'TextValue7',1000,-1,1.7,TO_DATE('2016-07-07T00:00:00Z'),'001xx000001UoUK',false,1.7,'0Glxx000000000S','')");
            conn.commit();
            {
                ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM  TEST.\"0Gl\"");
                int count = 0;
                while(rs.next()){
                    count++;
                    StringBuilder builder = new StringBuilder();
                    for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        Object obj = rs.getObject(i);
                        String value = obj == null ? "null" : obj.toString();
                        builder.append(value+",");
                    }
                    System.out.println("INITTAG: " + builder.toString());
                }
                System.out.println(String.format("Count (all rows) = %d",count));
            }
            String selectSQL = "SELECT CREATED_DATE, TEXT, PLINY_TEST_IMMUTABLE_INDEXED_ID,DATE_TIME,PLINY_TEST_IMMUTABLE_INDEXED_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY
\n"
                    + "FROM TEST.\"0Gl\" \n"
                    + "WHERE ((DATE_TIME>=? AND DATE_TIME<=?) AND INT=?) \n"
                    + "AND (DATE_TIME,PLINY_TEST_IMMUTABLE_INDEXED_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY)
\n"
                    + "> (?,?,?,?,?,?,?,?,?,?) LIMIT 2";
            PreparedStatement ps = conn.prepareStatement(selectSQL);
            //pop comparison
            ps.setDate(1,Date.valueOf("2016-07-03"));
            ps.setDate(2,Date.valueOf("2016-07-06"));
            //int
            ps.setInt(3,1001);
            //rvc
            ps.setDate(4,Date.valueOf("2016-07-04"));
            ps.setString(5,"0Glxx000000000P");
            ps.setDate(6,Date.valueOf("2016-07-04"));
            ps.setString(7,"TextValue4");
            ps.setInt(8,1001);
            ps.setLong(9,-1);
            ps.setDouble(10,1.4d);
            ps.setString(11,"001xx000001UoUG");
            ps.setBoolean(12,false);
            ps.setDouble(13,1.4d);
            ResultSet rs = ps.executeQuery();
            int count = 0;
            while(rs.next()){
                count++;
                StringBuilder builder = new StringBuilder();
                for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    Object obj = rs.getObject(i);
                    String value = obj == null ? "null" : obj.toString();
                    builder.append(value+",");
                }
                System.out.println("this should return 2016-07-06 but returns 2016-07-04:
" + builder.toString());
            }
            System.out.println(String.format("Count = %d",count));
            selectSQL = "SELECT CREATED_DATE, TEXT, PLINY_TEST_IMMUTABLE_INDEXED_ID,DATE_TIME,PLINY_TEST_IMMUTABLE_INDEXED_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY
\n"
                    + "FROM TEST.\"0Gl\" \n"
                    + "WHERE ((DATE_TIME>=? AND DATE_TIME<=?) AND INT=?) \n";
            ps = conn.prepareStatement(selectSQL);
            //pop comparison
            ps.setDate(1,Date.valueOf("2016-07-03"));
            ps.setDate(2,Date.valueOf("2016-07-06"));
            //int
            ps.setInt(3,1001);
            rs = ps.executeQuery();
            count = 0;
            while(rs.next()){
                count++;
                StringBuilder builder = new StringBuilder();
                for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    Object obj = rs.getObject(i);
                    String value = obj == null ? "null" : obj.toString();
                    builder.append(value+",");
                }
                System.out.println("all qualified rows for base filter: " + builder.toString());
            }
            System.out.println(String.format("Count = %d",count));
        }
    }
{code}

  was:
Saw this during internal testing. 

RVC comparator is not returning the correct results. 


> Incorrect startRow in scan when RVC is used in the query
> --------------------------------------------------------
>
>                 Key: PHOENIX-5484
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5484
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: Swaroopa Kadam
>            Priority: Major
>
> Saw this during internal testing. 
> RVC comparator is not returning the correct results. 
>  
> Following test is attached to show, expected rows are not returned. 
>  
> {code:java}
> @Test
>     public void testQueryMore() throws Exception {
>         {
>             final Connection conn = DriverManager.getConnection(getUrl());
>             try (Statement stmt = conn.createStatement()) {
>                 stmt.execute("CREATE TABLE IF NOT EXISTS TEST.PLINY_TEST_IMMUTABLE_INDEXED
(\n"
>                         + "    ORGANIZATION_ID CHAR(3) NOT NULL,\n"
>                         + "    CREATED_DATE DATE NOT NULL,\n"
>                         + "    TEXT VARCHAR NOT NULL,\n"
>                         + "    INT INTEGER NOT NULL,\n"
>                         + "    BIGINT BIGINT NOT NULL,\n"
>                         + "    DOUBLE DECIMAL NOT NULL,\n"
>                         + "    DATE_TIME DATE NOT NULL,\n"
>                         + "    RELATIONSHIP_ID CHAR(15) NOT NULL,\n"
>                         + "    IS_BOOLEAN BOOLEAN NOT NULL,\n"
>                         + "    CURRENCY DECIMAL(22,10) NOT NULL,\n"
>                         + "    PLINY_TEST_IMMUTABLE_INDEXED_ID CHAR(15) NOT NULL,\n"
>                         + "    TEXT_VALUE VARCHAR,\n"
>                         + "    CONSTRAINT PK PRIMARY KEY\n"
>                         + "    (\n"
>                         + "        ORGANIZATION_ID,\n"
>                         + "        CREATED_DATE,\n"
>                         + "        TEXT,\n"
>                         + "        INT,\n"
>                         + "        BIGINT,\n"
>                         + "        DOUBLE,\n"
>                         + "        DATE_TIME,\n"
>                         + "        RELATIONSHIP_ID,\n"
>                         + "        IS_BOOLEAN,\n"
>                         + "        CURRENCY,\n"
>                         + "        PLINY_TEST_IMMUTABLE_INDEXED_ID\n"
>                         + "    )\n"
>                         + ") VERSIONS=1,MULTI_TENANT=true,REPLICATION_SCOPE=1,IMMUTABLE_ROWS=true");
>                 stmt.execute(
>                         "CREATE INDEX IF NOT EXISTS PLINY_TEST_IMMUTABLE_INDEXED_DATETIME_INDEX\n"
+ "ON TEST.PLINY_TEST_IMMUTABLE_INDEXED (DATE_TIME, PLINY_TEST_IMMUTABLE_INDEXED_ID)\n"
>                                 + "INCLUDE\n" + "   (TEXT_VALUE)");
>             }
>         }
>         {
>             TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
>             String tenandId = "xyz";
>             final Connection conn = getTenantSpecificConnection(tenandId);
>             conn.createStatement().execute("CREATE VIEW TEST.\"0Gl\" AS SELECT * FROM
TEST.PLINY_TEST_IMMUTABLE_INDEXED");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES('2016-07-01T00:00:00Z','TextValue1',1000,-1,1.1,'2016-07-01T00:00:00Z','001xx000001UoUD',false,1.1,'0Glxx000000000M','')");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES(TO_DATE('2016-07-02T00:00:00Z'),'TextValue2',1001,-1,1.2,TO_DATE('2016-07-02T00:00:00Z'),'001xx000001UoUE',false,1.2,'0Glxx000000000N','')");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES(TO_DATE('2016-07-03T00:00:00Z'),'TextValue3',1001,-1,1.3,TO_DATE('2016-07-03T00:00:00Z'),'001xx000001UoUF',false,1.3,'0Glxx000000000O','')");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES(TO_DATE('2016-07-04T00:00:00Z'),'TextValue4',1001,-1,1.4,TO_DATE('2016-07-04T00:00:00Z'),'001xx000001UoUG',false,1.4,'0Glxx000000000P','')");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES(TO_DATE('2016-07-05T00:00:00Z'),'TextValue5',1000,-1,1.5,TO_DATE('2016-07-05T00:00:00Z'),'001xx000001UoUH',false,1.5,'0Glxx000000000Q','')");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES(TO_DATE('2016-07-06T00:00:00Z'),'TextValue6',1001,-1,1.6,TO_DATE('2016-07-06T00:00:00Z'),'001xx000001UoUJ',false,1.6,'0Glxx000000000R','')");
>             conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\" (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,PLINY_TEST_IMMUTABLE_INDEXED_ID,TEXT_VALUE)
\n"
>                     + "VALUES(TO_DATE('2016-07-07T00:00:00Z'),'TextValue7',1000,-1,1.7,TO_DATE('2016-07-07T00:00:00Z'),'001xx000001UoUK',false,1.7,'0Glxx000000000S','')");
>             conn.commit();
>             {
>                 ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM  TEST.\"0Gl\"");
>                 int count = 0;
>                 while(rs.next()){
>                     count++;
>                     StringBuilder builder = new StringBuilder();
>                     for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
>                         Object obj = rs.getObject(i);
>                         String value = obj == null ? "null" : obj.toString();
>                         builder.append(value+",");
>                     }
>                     System.out.println("INITTAG: " + builder.toString());
>                 }
>                 System.out.println(String.format("Count (all rows) = %d",count));
>             }
>             String selectSQL = "SELECT CREATED_DATE, TEXT, PLINY_TEST_IMMUTABLE_INDEXED_ID,DATE_TIME,PLINY_TEST_IMMUTABLE_INDEXED_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY
\n"
>                     + "FROM TEST.\"0Gl\" \n"
>                     + "WHERE ((DATE_TIME>=? AND DATE_TIME<=?) AND INT=?) \n"
>                     + "AND (DATE_TIME,PLINY_TEST_IMMUTABLE_INDEXED_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY)
\n"
>                     + "> (?,?,?,?,?,?,?,?,?,?) LIMIT 2";
>             PreparedStatement ps = conn.prepareStatement(selectSQL);
>             //pop comparison
>             ps.setDate(1,Date.valueOf("2016-07-03"));
>             ps.setDate(2,Date.valueOf("2016-07-06"));
>             //int
>             ps.setInt(3,1001);
>             //rvc
>             ps.setDate(4,Date.valueOf("2016-07-04"));
>             ps.setString(5,"0Glxx000000000P");
>             ps.setDate(6,Date.valueOf("2016-07-04"));
>             ps.setString(7,"TextValue4");
>             ps.setInt(8,1001);
>             ps.setLong(9,-1);
>             ps.setDouble(10,1.4d);
>             ps.setString(11,"001xx000001UoUG");
>             ps.setBoolean(12,false);
>             ps.setDouble(13,1.4d);
>             ResultSet rs = ps.executeQuery();
>             int count = 0;
>             while(rs.next()){
>                 count++;
>                 StringBuilder builder = new StringBuilder();
>                 for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
>                     Object obj = rs.getObject(i);
>                     String value = obj == null ? "null" : obj.toString();
>                     builder.append(value+",");
>                 }
>                 System.out.println("this should return 2016-07-06 but returns 2016-07-04:
" + builder.toString());
>             }
>             System.out.println(String.format("Count = %d",count));
>             selectSQL = "SELECT CREATED_DATE, TEXT, PLINY_TEST_IMMUTABLE_INDEXED_ID,DATE_TIME,PLINY_TEST_IMMUTABLE_INDEXED_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY
\n"
>                     + "FROM TEST.\"0Gl\" \n"
>                     + "WHERE ((DATE_TIME>=? AND DATE_TIME<=?) AND INT=?) \n";
>             ps = conn.prepareStatement(selectSQL);
>             //pop comparison
>             ps.setDate(1,Date.valueOf("2016-07-03"));
>             ps.setDate(2,Date.valueOf("2016-07-06"));
>             //int
>             ps.setInt(3,1001);
>             rs = ps.executeQuery();
>             count = 0;
>             while(rs.next()){
>                 count++;
>                 StringBuilder builder = new StringBuilder();
>                 for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
>                     Object obj = rs.getObject(i);
>                     String value = obj == null ? "null" : obj.toString();
>                     builder.append(value+",");
>                 }
>                 System.out.println("all qualified rows for base filter: " + builder.toString());
>             }
>             System.out.println(String.format("Count = %d",count));
>         }
>     }
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message