Inconsistent Results on Table Join when Index and TIMESTAMP type involved
Hi All,
I have a use case where I need to join a table with an index on itself in
order to find the latest entry. In doing so, I've come a case in Phoenix
(4.14.1) where I appear to get incorrect results if I do a join with
TIMESTAMPs where indexes are involved.
I have a suspicion that this is to down to the fact that TIMESTAMPs are
stored differently on index tables (at least based on what I see in the
result set meta data it is BigDecimal) and somehow conversion is not done
properly before joining. However, I thought I would reach out to check with
those that have more expertise in this area before I continue.
Attached, you can find a standalone program that you can run which
reproduces this issue. Here are the basic steps:
1) Create a table which effectively has 3 representations of the value - as
a TIMESTAMP, a VARCHAR and as a CHAR(29). Create indexes for each of these
representations:
CREATE TABLE IF NOT EXISTS TIME_TEST
(
ID VARCHARNOT NULL,
ENTRY_NAMEVARCHAR,
TRANSACTION TIMESTAMP ,
TRANSACTION_STR VARCHAR,
TRANSACTION_CHR CHAR(29)
CONSTRAINT pk PRIMARY KEY(ID)
)
IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN,
UPDATE_CACHE_FREQUENCY=90,
COLUMN_ENCODED_BYTES=NONE,
IMMUTABLE_ROWS=true
CREATE LOCAL INDEX TIME_TEST_I01 ON TIME_TEST(ENTRY_NAME, TRANSACTION DESC)
CREATE LOCAL INDEX TIME_TEST_I02 ON TIME_TEST(ENTRY_NAME, TRANSACTION_STR
DESC)
CREATE LOCAL INDEX TIME_TEST_I03 ON TIME_TEST(ENTRY_NAME, TRANSACTION_CHR
DESC)
2) Fill up the table with some values, in the different representations. In
the attached test-case I do 100 entries. Such that you have something like :
IDENTRY_NAME TRANSACTION TRANSACTION_STR
TRANSACTION_CHR
-
001 001 1970-01-01 01:00:00.1 1970-01-01
00:00:00.1 1970-01-01 00:00:00.1
0010001 0010001 1970-01-01 01:00:00.10001 1970-01-01
00:00:00.10001 1970-01-01 00:00:00.10001
0010002 0010002 1970-01-01 01:00:00.10002 1970-01-01
00:00:00.10002 1970-01-01 00:00:00.10002
0010003 0010003 1970-01-01 01:00:00.10003 1970-01-01
00:00:00.10003 1970-01-01 00:00:00.10003
3) Essentially do the same MAX / Join query using each of the different
values (e.g. TIMESTAMP vs. VARCHAR vs. CHAR(29)). They should all return
the same number of rows (e.g. the total number of rows on the table). Only
the one based on VARCHAR does:
SELECT TIME_TEST.ENTRY_NAME FROM TIME_TEST
INNER JOIN
(
SELECT ENTRY_NAME, MAX(TRANSACTION_STR) AS TRANSACTION_STR
FROM TIME_TEST where ENTRY_NAME like '0%' GROUP BY ENTRY_NAME
) sub
ON TIME_TEST.ENTRY_NAME = sub.ENTRY_NAME AND TIME_TEST.TRANSACTION_STR =
sub.TRANSACTION_STR
WHERE TIME_TEST.ENTRY_NAME like '0%'
*TOTAL Rows : 100*
SELECT TIME_TEST.ENTRY_NAME FROM TIME_TEST
INNER JOIN
(
SELECT ENTRY_NAME, MAX(TRANSACTION) AS TRANSACTION
FROM TIME_TEST where ENTRY_NAME like '0%' GROUP BY ENTRY_NAME
) sub
ON TIME_TEST.ENTRY_NAME = sub.ENTRY_NAME AND TIME_TEST.TRANSACTION =
sub.TRANSACTION
WHERE TIME_TEST.ENTRY_NAME like '0%'
*TOTAL Rows : 1*
SELECT TIME_TEST.ENTRY_NAME FROM TIME_TEST
INNER JOIN
(
SELECT ENTRY_NAME, MAX(TRANSACTION_CHR) AS TRANSACTION_CHR
FROM TIME_TEST where ENTRY_NAME like '0%' GROUP BY ENTRY_NAME
) sub
ON TIME_TEST.ENTRY_NAME = sub.ENTRY_NAME AND TIME_TEST.TRANSACTION_CHR=
sub.TRANSACTION_CHR
WHERE TIME_TEST.ENTRY_NAME like '0%'
*TOTAL Rows : 0*
Interestingly enough - in the final query, if I change the inner join
expression to be 'CAST( MAX(TRANSACTION_CHR) AS VARCHAR) AS TRANSACTION_CHR'
it works as expected. Hence, leading me to believe this is some sort of
column type conversion issue.
Either way, would appreciate it if anyone could give me any pointers on the
above.
Thanks,
package com.jsteenkamp.phoenix;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.time.Instant;
import java.time.format.DateTimeFormatter;
import java.util.TimeZone;
public class PhoenixTimestampEncodingTest
{
public static final String JDBC_URL = "jdbc:phoenix:localhost:63214";
private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("-MM-dd HH:mm:ss.n");
public static final int ITEMS_TO_INSERT = 100;
public static final DecimalFormat decf = new DecimalFormat("000");
public static final long STARTING_NANO = 1L;
static
{
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}
public static void main(String... args) throws Exception
{
final java.sql.Connection conn = DriverManager.getConnection(JDBC_URL, "sa", "");
final Statement stmt = conn.createStatement();
stmt.execute("DROP TABLE IF EXISTS TIME_TEST ");
String cre
Re: Inner Join Cursor Query fails with NullPointerException - JoinCompiler.java:187
Thank you for having a look. I have created : https://issues.apache.org/jira/browse/PHOENIX-5087 <https://issues.apache.org/jira/browse/PHOENIX-5087> Regards, On Wed, 2 Jan 2019 at 17:57, Thomas D'Silva wrote: > This looks like a bug, please file a JIRA with your test case. > > On Sat, Dec 29, 2018 at 7:42 AM Jack Steenkamp > wrote: > >> Hi All, >> >> Using Phoenix 4.14.1, I have come across an inner join query in my >> application that fails with the NullPointerException if executed as part of >> a Cursor, but executes fine if done without it. >> >> To reproduce this issue, you can run the attached program (assuming you >> update the JDBC_URL to point to an instance you have running) or you can >> follow the steps below: >> >> Create the Table: >> >> CREATE TABLE IF NOT EXISTS MY_STATS >> ( >>ID VARCHARNOT NULL, >>ENTRY_NAME VARCHAR, >>ENTRY_VALUE DOUBLE , >>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 >> >> Execute a normal query (this works fine): >> >> SELECT * FROM MY_STATS >>INNER JOIN >>( >> SELECT ENTRY_NAME, MAX(TRANSACTION_TIME) AS TRANSACTION_TIME >> FROM MY_STATS >> GROUP BY ENTRY_NAME >>) sub >>ON MY_STATS.ENTRY_NAME = sub.ENTRY_NAME AND MY_STATS.TRANSACTION_TIME >> = sub.TRANSACTION_TIME >> ORDER BY MY_STATS.TRANSACTION_TIME DESC >> >> Now if you execute the same query, but with the cursor declaration at the >> top - >> >> DECLARE MyCursor CURSOR FOR >> >> It produces the following exception: >> >> Exception in thread "main" java.lang.NullPointerException >> at >> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.resolveTable(JoinCompiler.java:187) >> at >> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:224) >> at >> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:181) >> at >> org.apache.phoenix.parse.DerivedTableNode.accept(DerivedTableNode.java:49) >> at >> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:201) >> at >> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:181) >> at org.apache.phoenix.parse.JoinTableNode.accept(JoinTableNode.java:81) >> at org.apache.phoenix.compile.JoinCompiler.compile(JoinCompiler.java:138) >> at >> org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:190) >> at >> org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:153) >> at >> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:490) >> at >> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeclareCursorStatement.compilePlan(PhoenixStatement.java:950) >> at >> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeclareCursorStatement.compilePlan(PhoenixStatement.java:941) >> at >> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:401) >> at >> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391) >> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) >> at >> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390) >> at >> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378) >> at >> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825) >> at >> com.jsteenkamp.phoenix.PhoenixInnerJoinCursorTest.testCursorQuery(PhoenixInnerJoinCursorTest.java:68) >> at >> com.jsteenkamp.phoenix.PhoenixInnerJoinCursorTest.main(PhoenixInnerJoinCursorTest.java:20) >> >> I think this may be a bug - though perhaps this is something you might >> have come across before? >> >> Thanks, >> >> >> >> >> >>
Inner Join Cursor Query fails with NullPointerException - JoinCompiler.java:187
Hi All,
Using Phoenix 4.14.1, I have come across an inner join query in my
application that fails with the NullPointerException if executed as part of
a Cursor, but executes fine if done without it.
To reproduce this issue, you can run the attached program (assuming you
update the JDBC_URL to point to an instance you have running) or you can
follow the steps below:
Create the Table:
CREATE TABLE IF NOT EXISTS MY_STATS
(
ID VARCHARNOT NULL,
ENTRY_NAME VARCHAR,
ENTRY_VALUE DOUBLE ,
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
Execute a normal query (this works fine):
SELECT * FROM MY_STATS
INNER JOIN
(
SELECT ENTRY_NAME, MAX(TRANSACTION_TIME) AS TRANSACTION_TIME
FROM MY_STATS
GROUP BY ENTRY_NAME
) sub
ON MY_STATS.ENTRY_NAME = sub.ENTRY_NAME AND MY_STATS.TRANSACTION_TIME =
sub.TRANSACTION_TIME
ORDER BY MY_STATS.TRANSACTION_TIME DESC
Now if you execute the same query, but with the cursor declaration at the
top -
DECLARE MyCursor CURSOR FOR
It produces the following exception:
Exception in thread "main" java.lang.NullPointerException
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.resolveTable(JoinCompiler.java:187)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:224)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:181)
at
org.apache.phoenix.parse.DerivedTableNode.accept(DerivedTableNode.java:49)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:201)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:181)
at org.apache.phoenix.parse.JoinTableNode.accept(JoinTableNode.java:81)
at org.apache.phoenix.compile.JoinCompiler.compile(JoinCompiler.java:138)
at
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:190)
at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:153)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:490)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeclareCursorStatement.compilePlan(PhoenixStatement.java:950)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeclareCursorStatement.compilePlan(PhoenixStatement.java:941)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:401)
at
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
at
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
at
com.jsteenkamp.phoenix.PhoenixInnerJoinCursorTest.testCursorQuery(PhoenixInnerJoinCursorTest.java:68)
at
com.jsteenkamp.phoenix.PhoenixInnerJoinCursorTest.main(PhoenixInnerJoinCursorTest.java:20)
I think this may be a bug - though perhaps this is something you might
have come across before?
Thanks,
package com.jsteenkamp.phoenix;
import java.sql.DriverManager;
import java.sql.Statement;
/**
* Reproducing the inner join cursor issue
*/
public class PhoenixInnerJoinCursorTest
{
private final boolean RUN_WITH_CURSOR = true; //With false this works, with true it doesn't
private final String JDBC_URL = "jdbc:phoenix:localhost:63214";
private final java.sql.Connection conn;
private final Statement stmt;
public static void main(String... args) throws Exception
{
PhoenixInnerJoinCursorTest innerJoinTest = new PhoenixInnerJoinCursorTest();
innerJoinTest.testCursorQuery();
System.out.println("ALL GOOD - No Exception");
}
public PhoenixInnerJoinCursorTest() throws Exception
{
System.out.println("Connecting To : " + JDBC_URL);
conn = DriverManager.getConnection(JDBC_URL, "sa", "");
stmt = conn.createStatement();
}
public void testCursorQuery() throws Exception
{
stmt.execute("DROP TABLE IF EXISTS MY_STATS");
String createTable = "CREATE TABLE IF NOT EXISTS MY_STATS\n" +
"( \n" +
" ID VARCHARNOT NULL,\n" +
" ENTRY_NAME VARCHAR,\n" +
" ENTRY_VALUE DOUBLE ,\n" +
" TRANSACTION_TIME TIMESTAMP ,\n" +
" CONSTRAINT pk PRIMARY KEY(ID)\n" +
") " +
"IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN,\n" +
"UPDATE_CACHE_FREQUENCY=90,\n" +
"COLUMN_ENCODED_BYTES=NONE,\n" +
"IMMUTABLE_ROWS=true";
System.out.println(createTable);
stmt.execute(createTable);
StringBuilder sql = new StringBuilder();
if(RUN_WITH_CURSOR == true)
{
sql.append(" DECLARE MyCursor CURSOR FOR \n");
}
sql.append
Re: Cursor Query Loops Eternally with Local Index, Returns Fine Without It
Hi Tomas, Sure - I've created https://issues.apache.org/jira/browse/PHOENIX-5072 with all the details. Thanks, On Tue, 18 Dec 2018 at 01:50, Thomas D'Silva wrote: > Jack, > > Can you please file a JIRA that includes you repro steps? > > On Fri, Dec 14, 2018 at 2:33 AM Jack Steenkamp > wrote: > >> Hi All, >> >> I have come across a curious case with Phoenix (4.14.1) cursors where a >> particular 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. >> >> Please find attached a standalone test case that you should be able to >> run to reproduce this problem (though you may need to modify the JDBC_URL >> constant). You can run it either with CREATE_INDEX = true or CREATE_INDEX = >> false. Below are the outputs >> >> With : CREATE_INDEX = true; >> >> Connecting To : jdbc:phoenix:localhost:63214 >> CREATE TABLE IF NOT EXISTS SOME_NUMBERS >> ( >>ID VARCHARNOT 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 >> >> Creating Index: CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME, >> TRANSACTION_TIME DESC) INCLUDE(ANOTHER_VALUE) >> >> Inserting Some Items >> >> Doing Count SQL : SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS >> TOTAL COUNT : 10 >> >> 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 !! >> >> With : CREATE_INDEX = false; >> >> Connecting To : jdbc:phoenix:localhost:63214 >> CREATE TABLE IF NOT EXISTS SOME_NUMBERS >> ( >>ID VARCHARNOT 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 >> >> Not Creating the Index >> >> Inserting Some Items >> >> Doing Count SQL : SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS >> TOTAL COUNT : 10 >> >> 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 >> >> Any idea what might be going on here? >> >> Regards, >> >
Cursor Query Loops Eternally with Local Index, Returns Fine Without It
Hi All,
I have come across a curious case with Phoenix (4.14.1) cursors where a
particular 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.
Please find attached a standalone test case that you should be able to run
to reproduce this problem (though you may need to modify the JDBC_URL
constant). You can run it either with CREATE_INDEX = true or CREATE_INDEX =
false. Below are the outputs
With : CREATE_INDEX = true;
Connecting To : jdbc:phoenix:localhost:63214
CREATE TABLE IF NOT EXISTS SOME_NUMBERS
(
ID VARCHARNOT 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
Creating Index: CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME,
TRANSACTION_TIME DESC) INCLUDE(ANOTHER_VALUE)
Inserting Some Items
Doing Count SQL : SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS
TOTAL COUNT : 10
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 !!
With : CREATE_INDEX = false;
Connecting To : jdbc:phoenix:localhost:63214
CREATE TABLE IF NOT EXISTS SOME_NUMBERS
(
ID VARCHARNOT 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
Not Creating the Index
Inserting Some Items
Doing Count SQL : SELECT COUNT(1) AS TOTAL_ITEMS FROM SOME_NUMBERS
TOTAL COUNT : 10
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
Any idea what might be going on here?
Regards,
package com.jsteenkamp.phoenix;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.UUID;
/**
* Reproducing the eternal cursor issue.
*/
public class PhoenixEternalCursorTest
{
private final boolean CREATE_INDEX = true; //With false this works, with true it doesn't
private final String JDBC_URL = "jdbc:phoenix:localhost:63214";
private final java.sql.Connection conn;
private final Statement stmt;
public static void main(String... args) throws Exception
{
PhoenixEternalCursorTest ectest = new PhoenixEternalCursorTest();
ectest.testEternalCursor();
System.out.println("ALL GOOD - No Exception");
}
public PhoenixEternalCursorTest() throws Exception
{
System.out.println("Connecting To : " + JDBC_URL);
conn = DriverManager.getConnection(JDBC_URL, "sa", "");
stmt = conn.createStatement();
}
public void testEternalCursor() throws Exception
{
stmt.execute("DROP TABLE IF EXISTS SOME_NUMBERS");
String createTable = "CREATE TABLE IF NOT EXISTS SOME_NUMBERS\n" +
"( \n" +
" ID VARCHARNOT NULL,\n" +
" NAME VARCHAR,\n" +
" ANOTHER_VALUE VARCHAR,\n" +
" TRANSACTION_TIME TIMESTAMP ,\n" +
" CONSTRAINT pk PRIMARY KEY(ID)\n" +
") " +
"IMMUTABLE_STORAGE_SCHEME=ONE_CELL_PER_COLUMN,\n" +
"UPDATE_CACHE_FREQUENCY=90,\n" +
"COLUMN_ENCODED_BYTES=NONE,\n" +
"IMMUTABLE_ROWS=true";
String createIndex = "CREATE LOCAL INDEX index_01 ON SOME_NUMBERS(NAME, TRANSACTION_TIME DESC) INCLUDE(ANOTHER_VALUE)";
System.out.println(createTable);
stmt.execute(createTable);
if(CREATE_INDEX == true)
{
System.out.println("\nCreating Index: " + createIndex + "\n");
stmt.execute(createIndex);
}
else
{
System.out.println("\nNot Creating the Index\n");
}
//Insert Some Items.
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));
Re: CursorUtil -> mapCursorIDQuery
Hi Josh / All,
Just following up on the two patches left on PHOENIX-4860
<https://issues.apache.org/jira/browse/PHOENIX-4860> (the quick, albeit
hacky, fix and the more extensive one). Not sure if anyone has had a chance
to look at these yet? Can appreciate if other things take priority (if so,
will then park this for now and workaround locally).
The inclusion of the more extensive patch would be better (I think), though
even with the quick-fix it would make my app/build process more stable /
thread-safe.
Thanks,
On Tue, 21 Aug 2018 at 17:04, Josh Elser wrote:
> Thanks sir! Will take a look up there and continue.
>
> On 8/21/18 11:48 AM, Jack Steenkamp wrote:
> > Hi Josh,
> >
> > I've created https://issues.apache.org/jira/browse/PHOENIX-4860 for
> this.
> >
> > Thanks,
> > On Tue, 21 Aug 2018 at 16:34, Jack Steenkamp
> wrote:
> >>
> >> Hi Josh,
> >>
> >> Glad I could help. The CursorUtil class it seems has not changed since
> >> it was first created as part of PHOENIX-3572 - and is still the same
> >> in master (I checked a bit earlier).
> >>
> >> Sure - will have a go at creating a JIRA for this.
> >>
> >> Regards,
> >>
> >> On Tue, 21 Aug 2018 at 16:23, Josh Elser wrote:
> >>>
> >>> Hi Jack,
> >>>
> >>> Given your assessment, it sounds like you've stumbled onto a race
> >>> condition! Thanks for bringing it to our attention.
> >>>
> >>> A few questions:
> >>>
> >>> * Have you checked if the same code exist in the latest
> >>> branches/releases (4.x-HBase-1.{2,3,4} or master)?
> >>> * Want to create a Jira issue to track this? Else, I can do this for
> ya.
> >>>
> >>> On 8/21/18 9:48 AM, Jack Steenkamp wrote:
> >>>> Hi All,
> >>>>
> >>>> In my application I make heavy use of Apache Phoenix's Cursors
> >>>> (https://phoenix.apache.org/cursors.html) - and for the majority of
> >>>> cases it works great and makes my life a lot easier (thanks for
> >>>> implementing them). However, in very rare cases (fiendishly rare
> >>>> cases), I get NullpointerExceptions such as the following:
> >>>>
> >>>> java.lang.NullPointerException: null
> >>>> at
> org.apache.phoenix.util.CursorUtil.updateCursor(CursorUtil.java:179)
> >>>> at
> org.apache.phoenix.iterate.CursorResultIterator.next(CursorResultIterator.java:46)
> >>>> at
> org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:779)
> >>>>
> >>>> (This is for 4.13.1 - but seems that
> >>>> org.apache.phoenix.util.CursorUtil has not changed much since it was
> >>>> first created).
> >>>>
> >>>> Upon closer inspection it would seem that on line 124 of CursorUtil, a
> >>>> HashMap is used to keep state which is then exposed via a number
> >>>> static methods, which, one has to assume, can be accessed by many
> >>>> different threads. Using a plain old HashMap in cases like these have
> >>>> (at least in my experience) been to blame for rare issues such as
> >>>> these.
> >>>>
> >>>> Would you perhaps consider changing the implementation of
> >>>> mapCursorIDQuery to a ConcurrentHashMap instead? That should hopefully
> >>>> tighten up the class and prevent any potential inconsistencies.
> >>>>
> >>>> Regards,
> >>>>
> >>>> Jack Steenkamp
> >>>>
>
Re: CursorUtil -> mapCursorIDQuery
Hi Josh,
I've created https://issues.apache.org/jira/browse/PHOENIX-4860 for this.
Thanks,
On Tue, 21 Aug 2018 at 16:34, Jack Steenkamp wrote:
>
> Hi Josh,
>
> Glad I could help. The CursorUtil class it seems has not changed since
> it was first created as part of PHOENIX-3572 - and is still the same
> in master (I checked a bit earlier).
>
> Sure - will have a go at creating a JIRA for this.
>
> Regards,
>
> On Tue, 21 Aug 2018 at 16:23, Josh Elser wrote:
> >
> > Hi Jack,
> >
> > Given your assessment, it sounds like you've stumbled onto a race
> > condition! Thanks for bringing it to our attention.
> >
> > A few questions:
> >
> > * Have you checked if the same code exist in the latest
> > branches/releases (4.x-HBase-1.{2,3,4} or master)?
> > * Want to create a Jira issue to track this? Else, I can do this for ya.
> >
> > On 8/21/18 9:48 AM, Jack Steenkamp wrote:
> > > Hi All,
> > >
> > > In my application I make heavy use of Apache Phoenix's Cursors
> > > (https://phoenix.apache.org/cursors.html) - and for the majority of
> > > cases it works great and makes my life a lot easier (thanks for
> > > implementing them). However, in very rare cases (fiendishly rare
> > > cases), I get NullpointerExceptions such as the following:
> > >
> > > java.lang.NullPointerException: null
> > > at org.apache.phoenix.util.CursorUtil.updateCursor(CursorUtil.java:179)
> > > at
> > > org.apache.phoenix.iterate.CursorResultIterator.next(CursorResultIterator.java:46)
> > > at
> > > org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:779)
> > >
> > > (This is for 4.13.1 - but seems that
> > > org.apache.phoenix.util.CursorUtil has not changed much since it was
> > > first created).
> > >
> > > Upon closer inspection it would seem that on line 124 of CursorUtil, a
> > > HashMap is used to keep state which is then exposed via a number
> > > static methods, which, one has to assume, can be accessed by many
> > > different threads. Using a plain old HashMap in cases like these have
> > > (at least in my experience) been to blame for rare issues such as
> > > these.
> > >
> > > Would you perhaps consider changing the implementation of
> > > mapCursorIDQuery to a ConcurrentHashMap instead? That should hopefully
> > > tighten up the class and prevent any potential inconsistencies.
> > >
> > > Regards,
> > >
> > > Jack Steenkamp
> > >
Re: CursorUtil -> mapCursorIDQuery
Hi Josh,
Glad I could help. The CursorUtil class it seems has not changed since
it was first created as part of PHOENIX-3572 - and is still the same
in master (I checked a bit earlier).
Sure - will have a go at creating a JIRA for this.
Regards,
On Tue, 21 Aug 2018 at 16:23, Josh Elser wrote:
>
> Hi Jack,
>
> Given your assessment, it sounds like you've stumbled onto a race
> condition! Thanks for bringing it to our attention.
>
> A few questions:
>
> * Have you checked if the same code exist in the latest
> branches/releases (4.x-HBase-1.{2,3,4} or master)?
> * Want to create a Jira issue to track this? Else, I can do this for ya.
>
> On 8/21/18 9:48 AM, Jack Steenkamp wrote:
> > Hi All,
> >
> > In my application I make heavy use of Apache Phoenix's Cursors
> > (https://phoenix.apache.org/cursors.html) - and for the majority of
> > cases it works great and makes my life a lot easier (thanks for
> > implementing them). However, in very rare cases (fiendishly rare
> > cases), I get NullpointerExceptions such as the following:
> >
> > java.lang.NullPointerException: null
> > at org.apache.phoenix.util.CursorUtil.updateCursor(CursorUtil.java:179)
> > at
> > org.apache.phoenix.iterate.CursorResultIterator.next(CursorResultIterator.java:46)
> > at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:779)
> >
> > (This is for 4.13.1 - but seems that
> > org.apache.phoenix.util.CursorUtil has not changed much since it was
> > first created).
> >
> > Upon closer inspection it would seem that on line 124 of CursorUtil, a
> > HashMap is used to keep state which is then exposed via a number
> > static methods, which, one has to assume, can be accessed by many
> > different threads. Using a plain old HashMap in cases like these have
> > (at least in my experience) been to blame for rare issues such as
> > these.
> >
> > Would you perhaps consider changing the implementation of
> > mapCursorIDQuery to a ConcurrentHashMap instead? That should hopefully
> > tighten up the class and prevent any potential inconsistencies.
> >
> > Regards,
> >
> > Jack Steenkamp
> >
CursorUtil -> mapCursorIDQuery
Hi All, In my application I make heavy use of Apache Phoenix's Cursors (https://phoenix.apache.org/cursors.html) - and for the majority of cases it works great and makes my life a lot easier (thanks for implementing them). However, in very rare cases (fiendishly rare cases), I get NullpointerExceptions such as the following: java.lang.NullPointerException: null at org.apache.phoenix.util.CursorUtil.updateCursor(CursorUtil.java:179) at org.apache.phoenix.iterate.CursorResultIterator.next(CursorResultIterator.java:46) at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:779) (This is for 4.13.1 - but seems that org.apache.phoenix.util.CursorUtil has not changed much since it was first created). Upon closer inspection it would seem that on line 124 of CursorUtil, a HashMap is used to keep state which is then exposed via a number static methods, which, one has to assume, can be accessed by many different threads. Using a plain old HashMap in cases like these have (at least in my experience) been to blame for rare issues such as these. Would you perhaps consider changing the implementation of mapCursorIDQuery to a ConcurrentHashMap instead? That should hopefully tighten up the class and prevent any potential inconsistencies. Regards, Jack Steenkamp
