Quering system versioned table doesn't return results

Question

I have some system_versioned tables. After upgrading to version 11.8.2, I no longer get results when querying without explicit AS OF-syntax.

SELECT
    m.*
FROM
    mydb.mytable m

doesn't get any results, while

SELECT
    m.*
FROM
    mydb.mytable FOR SYSTEM_TIME AS OF NOW() m

gives the correct result. Similarly

SELECT
    m.*
FROM
    mydb.mytable FOR SYSTEM_TIME ALL m

gives the expected result.

If I SELECT the implicit columns row_start and row_end, they hold the correct values (start sometime in the past, end 2038).

SELECT @@timestamp, NOW(), system_time() all also give the expected results.

Note:

• I don't recall the version we upgraded from
• our developement environment does not suffer from this strange behaviour, while on the same version of mariadb and debian.
• when I insert new data, it gets returned by the normal query syntax

Answer 1

I have reason to believe that this strange behaviour resulted from a broken or otherwise incorrect index because luckily OPTIMIZE TABLE did the trick.

Additionally, since we're dealing with system versioned tables, I needed to change the 'alter history' vairable:

SET @@system_versioning_alter_history = KEEP;

After that SELECT [....] gave the same result as SELECT [...] FOR SYSTEM_TIME AS OF NOW() while SELECT [...] FOR SYSTEM_TIME ALL still contained the historical data.