[jira] [Created] (HIVE-27948) Wrong results when using materialized views with non-deterministic/dynamic functions

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

Stamatis Zampetakis created HIVE-27948:
------------------------------------------

             Summary: Wrong results when using materialized views with non-deterministic/dynamic functions
                 Key: HIVE-27948
                 URL: https://issues.apache.org/jira/browse/HIVE-27948
             Project: Hive
          Issue Type: Bug
          Components: CBO
    Affects Versions: 4.0.0-beta-1
            Reporter: Stamatis Zampetakis


There are certain SQL functions that return different results across different executions. Usually we refer to these functions as non-deterministic or dynamic. Some examples are: UNIX_TIMESTAMP(), CURRENT_TIMESTAMP, CURRENT_DATE, etc.

When a materialized view definition contains such functions the queries that are using this view may return wrong results.

Consider the following scenario where we populate the employee table with timestamps representing the future. For making this easily reproable in self-contained test the timestamps are only a few seconds apart.
{code:sql}
CREATE TABLE EMPS (ENAME STRING, BIRTH_EPOCH_SECS INT) STORED AS ORC TBLPROPERTIES ('transactional'='true');

INSERT INTO EMPS
VALUES ('Victor', UNIX_TIMESTAMP()),
       ('Alex', UNIX_TIMESTAMP() + 2),
       ('Bob', UNIX_TIMESTAMP() + 5),
       ('Alice', UNIX_TIMESTAMP() + 10);

CREATE MATERIALIZED VIEW v_emp AS SELECT * FROM EMPS WHERE BIRTH_EPOCH_SECS <= UNIX_TIMESTAMP();
{code}
When the materialized view is created it is populated with only the rows that match the timestamp at the given time.

To demonstrate the problem run the following queries with view based rewritting disabled and enabled.
{code:sql}
set hive.materializedview.rewriting.sql=false;
SELECT * FROM EMPS WHERE BIRTH_EPOCH_SECS <= UNIX_TIMESTAMP();
{code}
{noformat}
Victor	1702302786
Alex	1702302788
Bob	1702302791
{noformat}
{code:sql}
set hive.materializedview.rewriting.sql=true;
SELECT * FROM EMPS WHERE BIRTH_EPOCH_SECS <= UNIX_TIMESTAMP();
{code}
{noformat}
Victor	1702302786
Alex	1702302788
{noformat}
Naturally the second query should return more rows than the first one since UNIX_TIMESTAMP is constantly growing. However, when view based rewritting is in use the second query will use the results from the materialized view which are by now obsolete (missing Bob entry).



--
This message was sent by Atlassian Jira
(v8.20.10#820010)