Support Questions
Find answers, ask questions, and share your expertise

How do I fix Phoenix defect involving incorrect display of CURRENT_TIMESTAMP() when querying from salted table with USE_SORT_MERGE_JOIN

Highlighted

How do I fix Phoenix defect involving incorrect display of CURRENT_TIMESTAMP() when querying from salted table with USE_SORT_MERGE_JOIN

The issue I uncovered is that the value generated by using “TO_CHAR(CURRENT_TIMESTAMP())” differs in some cases depending upon whether the USE_SORT_MERGE_JOIN hint is used in the query. Specifically, I am joining two salted tables that I have created and populated that share a common key. The following output shows the logic I was using to uncover this issue.

Note that I have informed HortonWorks of this issue outside of this post. They researched it and confirmed that it is a bug in version 4.4 of Phoenix (the version we are using here at Mercy). They are currently working on a patch for it. I will post the patch number once it is identified to me.

Interestingly enough, I ran this same scenario against a cluster containing version 4.7 of Phoenix and found that the issue exists in that release, too.

Here again is the log output that illustrates this defect. First, the 2 salted source tables are created and populated. The result table is then created and populated using the UPSERT that joins the 2 salted tables. Note that in the first instance of producing the results, the USE_SORT_MERGE_JOIN hint is applied to the UPSERT command.

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> CREATE TABLE MED_DOC_DM.MY_DIM

. . . . . . . . . . . . . . . . . . . . . . .> (

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_ID BIGINT NOT NULL,

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_DESCR VARCHAR,

. . . . . . . . . . . . . . . . . . . . . . .> ROW_INSERT_TSP VARCHAR

. . . . . . . . . . . . . . . . . . . . . . .> CONSTRAINT PK_MY_DESCR PRIMARY KEY( MY_DIM_ID ) )

. . . . . . . . . . . . . . . . . . . . . . .> IMMUTABLE_ROWS=true, COMPRESSION='SNAPPY', SALT_BUCKETS = 12;

No rows affected (1.463 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_DIM VALUES (1,'This is the description for record #1.');

1 row affected (0.137 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_DIM VALUES (2,'This is the description for record #2.');

1 row affected (0.022 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_DIM VALUES (3,'This is the description for record #3.');

1 row affected (0.019 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> CREATE INDEX IF NOT EXISTS IX_MY_DIM

. . . . . . . . . . . . . . . . . . . . . . .> ON MED_DOC_DM.MY_DIM ( MY_DIM_ID )

. . . . . . . . . . . . . . . . . . . . . . .> INCLUDE ( MY_DIM_DESCR );

3 rows affected (6.393 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> CREATE TABLE MED_DOC_DM.MY_FACT

. . . . . . . . . . . . . . . . . . . . . . .> (

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_ID BIGINT NOT NULL,

. . . . . . . . . . . . . . . . . . . . . . .> MY_FACT_LINE BIGINT NOT NULL,

. . . . . . . . . . . . . . . . . . . . . . .> MY_FACT_TXT VARCHAR,

. . . . . . . . . . . . . . . . . . . . . . .> ROW_INSERT_TSP VARCHAR

. . . . . . . . . . . . . . . . . . . . . . .> CONSTRAINT PK_MY_DESCR PRIMARY KEY( MY_DIM_ID, MY_FACT_LINE ) )

. . . . . . . . . . . . . . . . . . . . . . .> IMMUTABLE_ROWS=true, COMPRESSION='SNAPPY', SALT_BUCKETS = 12;

No rows affected (1.29 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (1, 1, 'This is an arbitrary text value for DIM #1 and FACT #1.');

1 row affected (0.046 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (1, 2, 'This is an arbitrary text value for DIM #1 and FACT #2.');

1 row affected (0.007 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (1, 3, 'This is an arbitrary text value for DIM #1 and FACT #3.');

1 row affected (0.006 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (2, 1, 'This is an arbitrary text value for DIM #2 and FACT #1.');

1 row affected (0.006 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (2, 2, 'This is an arbitrary text value for DIM #2 and FACT #2.');

1 row affected (0.006 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (2, 3, 'This is an arbitrary text value for DIM #2 and FACT #3.');

1 row affected (0.024 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (3, 1, 'This is an arbitrary text value for DIM #3 and FACT #1.');

1 row affected (0.007 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (3, 2, 'This is an arbitrary text value for DIM #3 and FACT #2.');

1 row affected (0.006 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_FACT VALUES (3, 3, 'This is an arbitrary text value for DIM #3 and FACT #3.');

1 row affected (0.006 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> CREATE INDEX IF NOT EXISTS IX_MY_FACT

. . . . . . . . . . . . . . . . . . . . . . .> ON MED_DOC_DM.MY_FACT ( MY_DIM_ID, MY_FACT_LINE )

. . . . . . . . . . . . . . . . . . . . . . .> INCLUDE ( MY_FACT_TXT );

9 rows affected (7.411 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> CREATE TABLE MED_DOC_DM.MY_RESULT

. . . . . . . . . . . . . . . . . . . . . . .> (

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_ID BIGINT NOT NULL,

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_DESCR VARCHAR,

. . . . . . . . . . . . . . . . . . . . . . .> ROW_INSERT_TSP VARCHAR

. . . . . . . . . . . . . . . . . . . . . . .> CONSTRAINT PK_MY_RESULT PRIMARY KEY( MY_DIM_ID ) )

. . . . . . . . . . . . . . . . . . . . . . .> IMMUTABLE_ROWS=true, COMPRESSION='SNAPPY';

No rows affected (1.256 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT /*+ USE_SORT_MERGE_JOIN */ INTO MED_DOC_DM.MY_RESULT

. . . . . . . . . . . . . . . . . . . . . . .> SELECT op.MY_DIM_ID,

. . . . . . . . . . . . . . . . . . . . . . .> op.MY_DIM_DESCR,

. . . . . . . . . . . . . . . . . . . . . . .> TO_CHAR(CURRENT_TIME())

. . . . . . . . . . . . . . . . . . . . . . .> FROM MED_DOC_DM.MY_DIM op

. . . . . . . . . . . . . . . . . . . . . . .> WHERE op.MY_DIM_ID IN ( SELECT rslt.MY_DIM_ID

. . . . . . . . . . . . . . . . . . . . . . .> FROM MED_DOC_DM.MY_FACT rslt );

3 rows affected (0.284 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> select * from MED_DOC_DM.MY_RESULT;

+------------+-------------------------------------------+--------------------------+

| MY_DIM_ID | MY_DIM_DESCR | ROW_INSERT_TSP |

+------------+-------------------------------------------+--------------------------+

| 1 | This is the description for record #1. | 1970-01-01 00:00:00.000 |

| 2 | This is the description for record #2. | 1970-01-01 00:00:00.000 |

| 3 | This is the description for record #3. | 1970-01-01 00:00:00.000 |

+------------+-------------------------------------------+--------------------------+

3 rows selected (0.059 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> DROP TABLE MED_DOC_DM.MY_RESULT;

No rows affected (3.481 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh>

Notice in the 3 records returned that the ROW_INSERT_TSP value produced is the system default value rather than the current date and time (of which the date today should be 5/3/2016). At this point, I dropped the table, recreated it with exactly the same structure, and re-populated it using the same UPSERT statement EXCEPT that I did not include the USE_SORT_MERGE_JOIN hint. The output follows here. Note that the current timestamp in the 3 records produced this time is what we would expect.

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> CREATE TABLE MED_DOC_DM.MY_RESULT

. . . . . . . . . . . . . . . . . . . . . . .> (

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_ID BIGINT NOT NULL,

. . . . . . . . . . . . . . . . . . . . . . .> MY_DIM_DESCR VARCHAR,

. . . . . . . . . . . . . . . . . . . . . . .> ROW_INSERT_TSP VARCHAR

. . . . . . . . . . . . . . . . . . . . . . .> CONSTRAINT PK_MY_RESULT PRIMARY KEY( MY_DIM_ID ) )

. . . . . . . . . . . . . . . . . . . . . . .> IMMUTABLE_ROWS=true, COMPRESSION='SNAPPY';

No rows affected (1.241 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> UPSERT INTO MED_DOC_DM.MY_RESULT

. . . . . . . . . . . . . . . . . . . . . . .> SELECT op.MY_DIM_ID,

. . . . . . . . . . . . . . . . . . . . . . .> op.MY_DIM_DESCR,

. . . . . . . . . . . . . . . . . . . . . . .> TO_CHAR(CURRENT_TIME())

. . . . . . . . . . . . . . . . . . . . . . .> FROM MED_DOC_DM.MY_DIM op

. . . . . . . . . . . . . . . . . . . . . . .> WHERE op.MY_DIM_ID IN ( SELECT rslt.MY_DIM_ID

. . . . . . . . . . . . . . . . . . . . . . .> FROM MED_DOC_DM.MY_FACT rslt );

3 rows affected (0.205 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh> select * from MED_DOC_DM.MY_RESULT;

+------------+-------------------------------------------+--------------------------+

| MY_DIM_ID | MY_DIM_DESCR | ROW_INSERT_TSP |

+------------+-------------------------------------------+--------------------------+

| 1 | This is the description for record #1. | 2016-05-03 22:56:53.717 |

| 2 | This is the description for record #2. | 2016-05-03 22:56:53.717 |

| 3 | This is the description for record #3. | 2016-05-03 22:56:53.717 |

+------------+-------------------------------------------+--------------------------+

3 rows selected (0.051 seconds)

0: jdbc:phoenix:lnxvmhdpae01.smrcy.com,lnxvmh>

1 REPLY 1
Highlighted

Re: How do I fix Phoenix defect involving incorrect display of CURRENT_TIMESTAMP() when querying from salted table with USE_SORT_MERGE_JOIN

Super Collaborator

Thomas, it's a new bug. I've created a JIRA on this issue. And provided fix as well. https://issues.apache.org/jira/browse/PHOENIX-2878