[jira] [Assigned] (PHOENIX-5072) Cursor Query Loops Eternally with Local Index, Returns Fine Without It
[
https://issues.apache.org/jira/browse/PHOENIX-5072?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Swaroopa Kadam reassigned PHOENIX-5072:
---
Assignee: (was: Swaroopa Kadam)
> Cursor Query Loops Eternally with Local Index, Returns Fine Without It
> --
>
> Key: PHOENIX-5072
> URL: https://issues.apache.org/jira/browse/PHOENIX-5072
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.14.1
>Reporter: Jack Steenkamp
>Priority: Major
> Attachments: PhoenixEternalCursorTest.java
>
>
>
> I have come across a case where a particular cursor query would carry on
> looping forever if executed when a local index is present. If however, I
> execute the same query without a local index on the table, then it works as
> expected.
> You can reproduce this by executing the attached standalone test case. You
> only need to modify the JDBC_URL constant (by default it tries to connect to
> localhost) and then you compare the outputs between hving CREATE_INDEX = true
> or CREATE_INDEX = false.
> Here is an example of the output:
> *1) Connect to an environment and create a simple table:*
> {code:java}
> Connecting To : jdbc:phoenix:localhost:63214{code}
> {code:java}
> CREATE TABLE IF NOT EXISTS SOME_NUMBERS
> (
> ID VARCHAR NOT NULL,
> NAME VARCHAR ,
> ANOTHER_VALUE VARCHAR ,
> TRANSACTION_TIME TIMESTAMP ,
> CONSTRAINT pk PRIMARY KEY(ID)
> ) IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN,
> UPDATE_CACHE_FREQUENCY=90,
> COLUMN_ENCODED_BYTES=NONE,
> IMMUTABLE_ROWS=true{code}
> *2) Optionally create a local index:*
>
> If you want to reproduce the failure, create an index:
> {code:java}
> CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME, TRANSACTION_TIME DESC)
> INCLUDE(ANOTHER_VALUE){code}
> Otherwise, skip this.
> *3) Insert a number of objects and verify their count*
> {code:java}
> System.out.println("\nInserting Some Items");
> DecimalFormat dmf = new DecimalFormat("");
> final String prefix = "ReferenceData.Country/";
> for (int i = 0; i < 5; i++)
> {
> for (int j = 0; j < 2; j++)
> {
> PreparedStatement prstmt = conn.prepareStatement("UPSERT INTO
> SOME_NUMBERS VALUES(?,?,?,?)");
> prstmt.setString(1,UUID.randomUUID().toString());
> prstmt.setString(2,prefix + dmf.format(i));
> prstmt.setString(3,UUID.randomUUID().toString());
> prstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
> prstmt.execute();
> conn.commit();3) Insert a number of objects and verify their count_
> prstmt.close();
> }
> }{code}
> Verify the count afterwards with:
> {code:java}
> SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS {code}
> *5) Run a Cursor Query*
> Run a cursor using the standard sequence of commands as appropriate:
> {code:java}
> DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM SOME_NUMBERS where
> NAME like 'ReferenceData.Country/%' ORDER BY TRANSACTION_TIME DESC{code}
> {code:java}
> OPEN MyCursor{code}
> {code:java}
> FETCH NEXT 10 ROWS FROM MyCursor{code}
> * Without an index it will return the correct number of rows
> {code:java}
> Cursor SQL : DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM
> SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY
> TRANSACTION_TIME DESC
> CLOSING THE CURSOR
> Result : 0
> ITEMS returned by count : 10 | Items Returned by Cursor : 10
> ALL GOOD - No Exception{code}
> * With an index it will return far more than the number of rows (it appears
> to be erroneously looping for ever - hence the test-case terminates it).
> {code:java}
> Cursor SQL : DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM
> SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY
> TRANSACTION_TIME DESC
> ITEMS returned by count : 10 | Items Returned by Cursor : 40
> Aborting the Cursor, as it is more than the count!
> Exception in thread "main" java.lang.RuntimeException: The cursor returned a
> different number of rows from the count !! {code}
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Assigned] (PHOENIX-5072) Cursor Query Loops Eternally with Local Index, Returns Fine Without It
[
https://issues.apache.org/jira/browse/PHOENIX-5072?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Swaroopa Kadam reassigned PHOENIX-5072:
---
Assignee: Swaroopa Kadam
> Cursor Query Loops Eternally with Local Index, Returns Fine Without It
> --
>
> Key: PHOENIX-5072
> URL: https://issues.apache.org/jira/browse/PHOENIX-5072
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.14.1
>Reporter: Jack Steenkamp
>Assignee: Swaroopa Kadam
>Priority: Major
> Attachments: PhoenixEternalCursorTest.java
>
>
>
> I have come across a case where a particular cursor query would carry on
> looping forever if executed when a local index is present. If however, I
> execute the same query without a local index on the table, then it works as
> expected.
> You can reproduce this by executing the attached standalone test case. You
> only need to modify the JDBC_URL constant (by default it tries to connect to
> localhost) and then you compare the outputs between hving CREATE_INDEX = true
> or CREATE_INDEX = false.
> Here is an example of the output:
> *1) Connect to an environment and create a simple table:*
> {code:java}
> Connecting To : jdbc:phoenix:localhost:63214{code}
> {code:java}
> CREATE TABLE IF NOT EXISTS SOME_NUMBERS
> (
> ID VARCHAR NOT NULL,
> NAME VARCHAR ,
> ANOTHER_VALUE VARCHAR ,
> TRANSACTION_TIME TIMESTAMP ,
> CONSTRAINT pk PRIMARY KEY(ID)
> ) IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN,
> UPDATE_CACHE_FREQUENCY=90,
> COLUMN_ENCODED_BYTES=NONE,
> IMMUTABLE_ROWS=true{code}
> *2) Optionally create a local index:*
>
> If you want to reproduce the failure, create an index:
> {code:java}
> CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME, TRANSACTION_TIME DESC)
> INCLUDE(ANOTHER_VALUE){code}
> Otherwise, skip this.
> *3) Insert a number of objects and verify their count*
> {code:java}
> System.out.println("\nInserting Some Items");
> DecimalFormat dmf = new DecimalFormat("");
> final String prefix = "ReferenceData.Country/";
> for (int i = 0; i < 5; i++)
> {
> for (int j = 0; j < 2; j++)
> {
> PreparedStatement prstmt = conn.prepareStatement("UPSERT INTO
> SOME_NUMBERS VALUES(?,?,?,?)");
> prstmt.setString(1,UUID.randomUUID().toString());
> prstmt.setString(2,prefix + dmf.format(i));
> prstmt.setString(3,UUID.randomUUID().toString());
> prstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
> prstmt.execute();
> conn.commit();3) Insert a number of objects and verify their count_
> prstmt.close();
> }
> }{code}
> Verify the count afterwards with:
> {code:java}
> SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS {code}
> *5) Run a Cursor Query*
> Run a cursor using the standard sequence of commands as appropriate:
> {code:java}
> DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM SOME_NUMBERS where
> NAME like 'ReferenceData.Country/%' ORDER BY TRANSACTION_TIME DESC{code}
> {code:java}
> OPEN MyCursor{code}
> {code:java}
> FETCH NEXT 10 ROWS FROM MyCursor{code}
> * Without an index it will return the correct number of rows
> {code:java}
> Cursor SQL : DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM
> SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY
> TRANSACTION_TIME DESC
> CLOSING THE CURSOR
> Result : 0
> ITEMS returned by count : 10 | Items Returned by Cursor : 10
> ALL GOOD - No Exception{code}
> * With an index it will return far more than the number of rows (it appears
> to be erroneously looping for ever - hence the test-case terminates it).
> {code:java}
> Cursor SQL : DECLARE MyCursor CURSOR FOR SELECT NAME,ANOTHER_VALUE FROM
> SOME_NUMBERS where NAME like 'ReferenceData.Country/%' ORDER BY
> TRANSACTION_TIME DESC
> ITEMS returned by count : 10 | Items Returned by Cursor : 40
> Aborting the Cursor, as it is more than the count!
> Exception in thread "main" java.lang.RuntimeException: The cursor returned a
> different number of rows from the count !! {code}
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
