You are viewing a plain text version of this content. The canonical link for it is here.
Posted to gitbox@hive.apache.org by "harshal-16 (via GitHub)" <gi...@apache.org> on 2025/01/21 12:37:46 UTC
[PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
harshal-16 opened a new pull request, #5617:
URL: https://github.com/apache/hive/pull/5617
In Oracle, empty strings ('') are treated as NULL values for VARCHAR2 and CHAR data types. This behavior is unique to Oracle and can be confusing, as an empty string is typically considered distinct from NULL in other databases.
As a result, the TXN_WRITE_NOTIFICATION_LOG table receives duplicate entries for a single Hive ACID transaction involving MERGE statements.
This discrepancy leads to issues: the _files and _dumpmetadata files in a Hive ACID incremental dump will not align if the dump scope includes one or more MERGE statements. Consequently, the Hive ACID incremental LOAD fails at the target (DR), blocking subsequent replication executions.
Solution
* Add additional check for partition being null
Testing:
* Tested on cluster with oracle and mysql as backend database
<!--
Thanks for sending a pull request! Here are some tips for you:
1. If this is your first time, please read our contributor guidelines: https://cwiki.apache.org/confluence/display/Hive/HowToContribute
2. Ensure that you have created an issue on the Hive project JIRA: https://issues.apache.org/jira/projects/HIVE/summary
3. Ensure you have added or run the appropriate tests for your PR:
4. If the PR is unfinished, add '[WIP]' in your PR title, e.g., '[WIP]HIVE-XXXXX: Your PR title ...'.
5. Be sure to keep the PR description updated to reflect all changes.
6. Please write your PR title to summarize what this PR proposes.
7. If possible, provide a concise example to reproduce the issue for a faster review.
-->
### What changes were proposed in this pull request?
<!--
Please clarify what changes you are proposing. The purpose of this section is to outline the changes and how this PR fixes the issue.
If possible, please consider writing useful notes for better and faster reviews in your PR. See the examples below.
1. If you refactor some codes with changing classes, showing the class hierarchy will help reviewers.
2. If you fix some SQL features, you can provide some references of other DBMSes.
3. If there is design documentation, please add the link.
4. If there is a discussion in the mailing list, please add the link.
-->
### Why are the changes needed?
<!--
Please clarify why the changes are needed. For instance,
1. If you propose a new API, clarify the use case for a new API.
2. If you fix a bug, you can clarify why it is a bug.
-->
### Does this PR introduce _any_ user-facing change?
<!--
Note that it means *any* user-facing change including all aspects such as the documentation fix.
If yes, please clarify the previous behavior and the change this PR proposes - provide the console output, description, screenshot and/or a reproducable example to show the behavior difference if possible.
If possible, please also clarify if this is a user-facing change compared to the released Hive versions or within the unreleased branches such as master.
If no, write 'No'.
-->
### Is the change a dependency upgrade?
<!--
If yes, please attach a file with output from mvn dependency:tree to validate a complete upgrade of dependency.
If no, write 'No'.
-->
### How was this patch tested?
<!--
If tests were added, say they were added here. Please make sure to add some test cases that check the changes thoroughly including negative and positive cases if possible.
If it was tested in a way different from regular unit tests, please clarify how you tested step by step, ideally copy and paste-able, so that other reviewers can test and check, and descendants can verify in the future.
If tests were not added, please describe why they were not added and/or why it was difficult to add.
-->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1939358566
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
btw `(:partition IS NULL OR \"CMC_PARTITION\" = :partition)` is not the equivalent to your change
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1943311283
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
approved as query is correct now, but it would be better to refactor this in the follow-up PR
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "harshal-16 (via GitHub)" <gi...@apache.org>.
harshal-16 commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1942420873
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
I checked the code and in most of the places partition is inserted as '' if value is not present, so i don't want to change that behaviour, For your 2nd suggestion, I have updated the condition please have a look
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5617:
URL: https://github.com/apache/hive/pull/5617#issuecomment-2636342114
## [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=5617) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=5617&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=5617&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=5617&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=5617&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=5617&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=5617)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "harshal-16 (via GitHub)" <gi...@apache.org>.
harshal-16 commented on PR #5617:
URL: https://github.com/apache/hive/pull/5617#issuecomment-2638736306
Thanks @deniskuzZ for your review!! I have created a follow-up JIRA [HIVE-28743](https://issues.apache.org/jira/browse/HIVE-28743) Can you please merge this PR as I don't have merge access
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "pudidic (via GitHub)" <gi...@apache.org>.
pudidic commented on PR #5617:
URL: https://github.com/apache/hive/pull/5617#issuecomment-2606145476
+1. Looks good to me.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1938329126
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
1. why would we get an empty string ('') as partition name?
2. what if we have an empty partition and a non-empty, you'll lock and update both?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5617:
URL: https://github.com/apache/hive/pull/5617#issuecomment-2620747975
## [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=5617) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=5617&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=5617&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=5617&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=5617&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=5617&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=5617)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ merged PR #5617:
URL: https://github.com/apache/hive/pull/5617
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1939353120
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
@harshal-16
maybe, better fix the way you populate that column. Insert `NULL` instead of '' for unpartitioned tables.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1938329126
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
what if we have an empty partition and a non-empty, you'll lock and update both?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5617:
URL: https://github.com/apache/hive/pull/5617#issuecomment-2604766975
## [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=5617) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=5617&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=5617&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=5617&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=5617&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=5617&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=5617)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1938329126
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
what if we have an empty partition and a non-empty, you'll lock both?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "harshal-16 (via GitHub)" <gi...@apache.org>.
harshal-16 commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1939286256
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
@deniskuzZ
for 1. you can simply create CREATE TABLE employee (id int, name string, salary int)
STORED AS ORC TBLPROPERTIES ('transactional' = 'true');
This will not have any partition information and in the table we are adding '' for partition
For 2. I am not changing any logic here. I am just covering the way oracle handles '' string
Consider below example in oracle database
```
CREATE TABLE t1 (name VARCHAR2(100));
INSERT INTO t1 (name) VALUES ('harshal');
INSERT INTO t1 (name) VALUES ('');
INSERT INTO t1 (name) VALUES (NULL);
SELECT * FROM t1; -- RETURNS all 3 rows
SELECT * FROM t1 WHERE name = ''; -- RETURNS 0 rows
SELECT * FROM t1 WHERE name IS NULL; --returns 2 rows
```
Because of this quirky behavior of Oracle, if the backend database is Oracle, then the MERGE operation adds 2 rows into TXN_WRITE_NOTIFICATION_LOG instead of 1.
Merge statement ran in Hive shell:
`MERGE INTO employee AS a
USING employee_update AS b ON a.id = b.id
WHEN MATCHED THEN UPDATE SET salary = b.salary
WHEN NOT MATCHED THEN INSERT VALUES (b.id, b.name, b.salary);
`
Rows added in oracle database in TXN_WRITE_NOTIFICATION_LOG table
`"WNL_ID","WNL_TXNID","WNL_WRITEID","WNL_DATABASE","WNL_TABLE","WNL_PARTITION","WNL_TABLE_OBJ","WNL_PARTITION_OBJ","WNL_FILES","WNL_EVENT_TIME"
7,9,3,default,employee,,"{""1"":{""str"":""employee""},""2"":{""str"":""default""},""3"":{""str"":""hive""},""4"":{""i32"":1738583942},""5"":{""i32"":0},""6"":{""i32"":0},""7"":{""rec"":{""1"":{""lst"":[""rec"",3,{""1"":{""str"":""id""},""2"":{""str"":""int""}},{""1"":{""str"":""name""},""2"":{""str"":""string""}},{""1"":{""str"":""salary""},""2"":{""str"":""int""}}]},""2"":{""str"":""hdfs://ccycloud-1.harshal1.root.comops.site:8020/warehouse/tablespace/managed/hive/employee""},""3"":{""str"":""org.apache.hadoop.hive.ql.io.orc.OrcInputFormat""},""4"":{""str"":""org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat""},""5"":{""tf"":0},""6"":{""i32"":-1},""7"":{""rec"":{""2"":{""str"":""org.apache.hadoop.hive.ql.io.orc.OrcSerde""},""3"":{""map"":[""str"",""str"",0,{}]}}},""8"":{""lst"":[""str"",0]},""9"":{""lst"":[""rec"",0]},""10"":{""map"":[""str"",""str"",0,{}]},""11"":{""rec"":{""1"":{""lst"":[""str"",0]},""2"":{""lst"":[""lst"",0]},""3"":{""map"":[""lst"",""str"",0,{}]}}},""12"":{""
tf"":0}}},""8"":{""lst"":[""rec"",0]},""9"":{""map"":[""str"",""str"",10,{""totalSize"":""2391"",""numRows"":""3"",""rawDataSize"":""0"",""transactional_properties"":""default"",""COLUMN_STATS_ACCURATE"":""{\""BASIC_STATS\"":\""true\""}"",""numFiles"":""3"",""transient_lastDdlTime"":""1738584068"",""bucketing_version"":""2"",""numFilesErasureCoded"":""0"",""transactional"":""true""}]},""12"":{""str"":""MANAGED_TABLE""},""15"":{""tf"":0},""17"":{""str"":""hive""},""18"":{""i32"":1},""19"":{""i64"":3},""26"":{""i64"":131}}",[NULL],"hdfs://ccycloud-1.harshal1.root.comops.site:8020/warehouse/tablespace/managed/hive/employee/delta_0000003_0000003_0001/bucket_00000_0###delta_0000003_0000003_0001",1738584069
8,9,3,default,employee,,"{""1"":{""str"":""employee""},""2"":{""str"":""default""},""3"":{""str"":""hive""},""4"":{""i32"":1738583942},""5"":{""i32"":0},""6"":{""i32"":0},""7"":{""rec"":{""1"":{""lst"":[""rec"",3,{""1"":{""str"":""id""},""2"":{""str"":""int""}},{""1"":{""str"":""name""},""2"":{""str"":""string""}},{""1"":{""str"":""salary""},""2"":{""str"":""int""}}]},""2"":{""str"":""hdfs://ccycloud-1.harshal1.root.comops.site:8020/warehouse/tablespace/managed/hive/employee""},""3"":{""str"":""org.apache.hadoop.hive.ql.io.orc.OrcInputFormat""},""4"":{""str"":""org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat""},""5"":{""tf"":0},""6"":{""i32"":-1},""7"":{""rec"":{""2"":{""str"":""org.apache.hadoop.hive.ql.io.orc.OrcSerde""},""3"":{""map"":[""str"",""str"",0,{}]}}},""8"":{""lst"":[""str"",0]},""9"":{""lst"":[""rec"",0]},""10"":{""map"":[""str"",""str"",0,{}]},""11"":{""rec"":{""1"":{""lst"":[""str"",0]},""2"":{""lst"":[""lst"",0]},""3"":{""map"":[""lst"",""str"",0,{}]}}},""12"":{""
tf"":0}}},""8"":{""lst"":[""rec"",0]},""9"":{""map"":[""str"",""str"",10,{""totalSize"":""2391"",""numRows"":""3"",""rawDataSize"":""0"",""transactional_properties"":""default"",""COLUMN_STATS_ACCURATE"":""{\""BASIC_STATS\"":\""true\""}"",""numFiles"":""3"",""transient_lastDdlTime"":""1738584069"",""bucketing_version"":""2"",""numFilesErasureCoded"":""0"",""transactional"":""true""}]},""12"":{""str"":""MANAGED_TABLE""},""15"":{""tf"":0},""17"":{""str"":""hive""},""18"":{""i32"":1},""19"":{""i64"":3},""26"":{""i64"":131}}",[NULL],"hdfs://ccycloud-1.harshal1.root.comops.site:8020/warehouse/tablespace/managed/hive/employee/delta_0000003_0000003_0002/bucket_00000_0###delta_0000003_0000003_0002",1738584070
`
and if you see all other database's DDL statements for table creation, it has primary key on WNL_TXNID, WNL_DATABASE, WNL_TABLE, WNL_PARTITION -So, above thing will not even happen in case of other databases but this is a different topic for discussion
Snippet from hive-schema-4.0.0-beta-1.mssql.sql
`ALTER TABLE TXN_WRITE_NOTIFICATION_LOG ADD CONSTRAINT TXN_WRITE_NOTIFICATION_LOG_PK PRIMARY KEY (WNL_TXNID, WNL_DATABASE, WNL_TABLE, WNL_PARTITION);
`
Apart from this, Similar logic is added other places but missing here
Ref:
1. https://github.com/apache/hive/blob/dee65466b8dcb0a624e65f689e9580425deb34ab/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/jdbc/commands/RemoveCompactionMetricsDataCommand.java#L35
2. https://github.com/apache/hive/blob/dee65466b8dcb0a624e65f689e9580425deb34ab/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/txn/jdbc/commands/RemoveDuplicateCompleteTxnComponentsCommand.java#L56
Let me know if you still have some doubts / suggestion then we can surely discuss in detail
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-28622: Duplicate Entries in TXN_WRITE_NOTIFICATION_LOG Due to Oracle's Handling of Empty Strings [hive]
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on code in PR #5617:
URL: https://github.com/apache/hive/pull/5617#discussion_r1943307736
##########
hcatalog/server-extensions/src/main/java/org/apache/hive/hcatalog/listener/DbNotificationListener.java:
##########
@@ -1205,7 +1205,7 @@ private void addWriteNotificationLog(List<NotificationEvent> eventBatch, List<Ac
String select = sqlGenerator.addForUpdateClause("select \"WNL_ID\", \"WNL_FILES\" from" +
" \"TXN_WRITE_NOTIFICATION_LOG\" " +
"where \"WNL_DATABASE\" = ? " +
- "and \"WNL_TABLE\" = ? " + " and \"WNL_PARTITION\" = ? " +
+ "and \"WNL_TABLE\" = ? " + " and (\"WNL_PARTITION\" = ? OR \"WNL_PARTITION\" IS NULL) " +
Review Comment:
what are those places where partition is inserted as ''? check any of acid or compaction tables - it's always null
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org