You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Kadir OZDEMIR (Jira)" <ji...@apache.org> on 2019/09/18 04:53:00 UTC
[jira] [Commented] (PHOENIX-5484) Incorrect startRow in scan when
RVC is used in the query
[ https://issues.apache.org/jira/browse/PHOENIX-5484?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16932057#comment-16932057 ]
Kadir OZDEMIR commented on PHOENIX-5484:
----------------------------------------
[~swaroopa], Thank you for finding this issue and writing the test to reproduce it. I run the test without the create index statement and it still fails, i.e., it prints :
this should return 2016-07-06 but returns 2016-07-04: 2016-07-06,TextValue6,0Glxx000000000R,2016-07-06,0Glxx000000000R,2016-07-06,TextValue6,1001,-1,1.6,001xx000001UoUJ,false,1.6,
Count = 1
> 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)