[HIVE-28551] Stale results when executing queries over recreated transactional tables

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.1
• Component/s: HiveServer2
• Labels: pull-request-available
• Assignee: Xiaomin Zhang
• Reporter: Stamatis Zampetakis
• Created: 02/Oct/24 11:40
• Updated: 14/Nov/24 12:22

Description

SQL queries return stale results from the cache when the tables involved in the queries are dropped and then recreated with the same name.

The problem can be reproduced by executing the following sequence of queries.

CREATE TABLE author (fname STRING) STORED AS ORC TBLPROPERTIES('transactional'='true');
INSERT INTO author VALUES ('Victor');
SELECT fname FROM author;

DROP TABLE author;

CREATE TABLE author (fname STRING) STORED AS ORC TBLPROPERTIES('transactional'='true');
INSERT INTO author VALUES ('Alexander');
SELECT fname FROM author;

The first execution of the SELECT query correctly returns "Victor" as a result.
The second execution of the SELECT query incorrectly returns "Victor" while it should return "Alexander".

The problem manifestates only when the hive.query.results.cache.enabled is set to true.

Issue Links

• relates to: HIVE-19154 Poll notification events to invalidate the results cache
• relates to: HIVE-19820 add ACID stats support to background stats updater and fix bunch of edge cases found in SU tests
• relates to: HIVE-18513 Query results caching
• links to: GitHub Pull Request #5482