[
https://issues.apache.org/jira/browse/PHOENIX-5484?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Priyank Porwal reassigned PHOENIX-5484:
---------------------------------------
Assignee: Daniel Wong
> 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
> Assignee: Daniel Wong
> Priority: Major
>
> Saw this during 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_index() throws Exception {
> {
> final Connection conn = DriverManager.getConnection(getUrl());
> try (Statement stmt = conn.createStatement()) {
> stmt.execute("CREATE TABLE IF NOT EXISTS
> TEST.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"
> + " ANOTHER_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"
> + " ANOTHER_ID\n"
> + " )\n"
> + ")
> VERSIONS=1,MULTI_TENANT=true,REPLICATION_SCOPE=1,IMMUTABLE_ROWS=true");
> stmt.execute(
> "CREATE INDEX IF NOT EXISTS
> TEST_IMMUTABLE_INDEXED_DATETIME_INDEX\n" + "ON TEST.TEST_IMMUTABLE_INDEXED
> (DATE_TIME, ANOTHER_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.TEST_IMMUTABLE_INDEXED");
> conn.createStatement().execute("UPSERT INTO TEST.\"0Gl\"
> (CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,DATE_TIME,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY,ANOTHER_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,ANOTHER_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,ANOTHER_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,ANOTHER_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,ANOTHER_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,ANOTHER_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,ANOTHER_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,
> ANOTHER_ID,DATE_TIME,ANOTHER_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";
> 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);
> 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("all qualified rows for base filter: " +
> builder.toString());
> }
> System.out.println(String.format("Count = %d",count));
> selectSQL = "SELECT CREATED_DATE, TEXT,
> ANOTHER_ID,DATE_TIME,ANOTHER_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,ANOTHER_ID,CREATED_DATE,TEXT,INT,BIGINT,DOUBLE,RELATIONSHIP_ID,IS_BOOLEAN,CURRENCY)
> \n"
> + "> (?,?,?,?,?,?,?,?,?,?) LIMIT 2";
> 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);
> rs = ps.executeQuery();
> count = 0;
> //only one row with RVC
> StringBuilder builder = new StringBuilder();
> if(rs.next()){
> count++;
> for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
> Object obj = rs.getObject(i);
> String value = obj == null ? "null" : obj.toString();
> builder.append(value+",");
> }
> }
> Assert.assertEquals(1, count);
>
> Assert.assertEquals("2016-07-06,TextValue6,0Glxx000000000R,2016-07-06,0Glxx000000000R,2016-07-06,TextValue6,1001,-1,1.6,001xx000001UoUJ,false,1.6,",
> builder.toString());
> }
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
