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>
