3

According to the definition given here at: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user.

To my understanding, unix_timestamp() will always give the same result for the same input.

However, when I am trying to create a generated column using UNIX_TIMESTAMP(epochtime), it is giving an error as disallowed function.

ALTER TABLE `test_table` 
ADD COLUMN epoch_updated_at BIGINT(15)
AS  (UNIX_TIMESTAMP(updated_at)) STORED

enter image description here

Update: I found that MYSQL does consider unix_timestamp as non-deterministic at https://dev.mysql.com/doc/refman/8.0/en/replication-rbr-safe-unsafe.html#replication-rbr-safe-unsafe-not. I am not sure, why, though?

The-Proton-Resurgence
  • 808
  • 1
  • 13
  • 28
  • 1
    The answer to your title (where you don't pass an argument) is no, it's not deterministic. The fact that when you do pass an argument it is deterministic is seemingly not considered. – Alex K. May 31 '19 at 11:34
  • Updated the title of the question. @AlexK. – The-Proton-Resurgence May 31 '19 at 11:50
  • 1
    This is IMHO a bug - Even when documented. – Paul Spiegel May 31 '19 at 11:50
  • @PaulSpiegel has the right idea. This is indefensible. I'm trying to partition by datetime range, and this is absolutely *killing me* ... – Giffyguy Nov 13 '19 at 16:20
  • 2
    @Giffyguy - I'm not sure any more. The problem is: "The server interprets date as a value in the session time zone". Thus the function doesn't return "*the same result, independently of the connected user*". Though the result is still deterministic, if the parameter is a TIMESTAMP column. But if the parameter is a (hardcoded) DATETIME value, then different users with different timezone settings will get different results. – Paul Spiegel Nov 14 '19 at 10:06

0 Answers0