Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21593

from_unixtime can not be longer used in Virtual Column

Details

    Description

      This is no longer accept since the 10.4.12

      CREATE TABLE `expenditure2` (
      		 
        `ts` int(10) unsigned NOT NULL COMMENT 'midnight',
      		 
        `dateUTC` date AS (cast(from_unixtime(`ts`) as date)) PERSISTENT
      		 
      ) ENGINE=InnoDB;

      But if you already have a table with similar schema, is still working.

      I check in the doc / release note and the only possibly related entry I was able to find are:
      https://mariadb.com/kb/en/mariadb-10231-changelog/
      https://github.com/MariaDB/server/commit/8eec2d61fc

      MDEV-21249 MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes

      As a side note also the `

      create table like xxx

      ` no longer work so

      create table newTable like tableWithVirtColum;

      Gives
      Error in query (1901): Function or expression 'from_unixtime()' cannot be used in the GENERATED ALWAYS AS clause of `dateCET`

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21249 MariaDB 10.3.10 When referring to bigint to generate timestamp data in the virtual generated column, the value of the generated column does not change when the time zone changes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 11 older comments
            danblack Daniel Black added a comment -

            RoyBellingan sorry for getting distracted on this. I'm around so start up a zulip topic, ping me and show a ref to your current code and I'll give it a closer look.

            danblack Daniel Black added a comment - RoyBellingan sorry for getting distracted on this. I'm around so start up a zulip topic, ping me and show a ref to your current code and I'll give it a closer look.
            Remirror Patrick Winter added a comment -

            Good to hear this is still worked on. Any progress? Actually, to me it looks more like a mere syntax problem.

            Remirror Patrick Winter added a comment - Good to hear this is still worked on. Any progress? Actually, to me it looks more like a mere syntax problem.
            RoyBellingan Roy Bellingan added a comment -

            @patrick it became much a bit more difficult than expect...
            Ongoing developement is here https://github.com/RoyBellingan/server/pull/1 but at the moment Is stalled due to me beeing a bit busy.

            What needs to be done now is a good numer of test cases, covering many area...

            RoyBellingan Roy Bellingan added a comment - @patrick it became much a bit more difficult than expect... Ongoing developement is here https://github.com/RoyBellingan/server/pull/1 but at the moment Is stalled due to me beeing a bit busy. What needs to be done now is a good numer of test cases, covering many area...
            jakdaw Chris Wilson added a comment -

            I think this is workaround-able. We can simply avoid using from_unixtime() and instead rely upon the fact that date_add ignores timezones (this is a feature, not a bug, right?); so this is an equivalent to from_unixtime always in UTC:-

            drop temporary table if exists test1;
            		 
            create temporary table test1 (
            		 
            ts bigint not null,
            		 
            asdate datetime as (date_add("1970-1-1 00:00:00", interval ts second)) persistent
            		 
            ) engine=innodb;

            Or if you want a different timezone then just convert explicitly:-

            drop temporary table if exists test1;
            		 
            create temporary table test1 (
            		 
            ts bigint not null,
            		 
            asdate datetime as (convert_tz(date_add("1970-1-1 00:00:00", interval ts second), "UTC", "Europe/London")) persistent
            		 
            ) engine=innodb;
            		 
             
            		 
            insert into test1 (ts) values (1), (1606993464), (1603587540), (1603587600);
            		 
            select * from test1;

            jakdaw Chris Wilson added a comment - I think this is workaround-able. We can simply avoid using from_unixtime() and instead rely upon the fact that date_add ignores timezones (this is a feature, not a bug, right?); so this is an equivalent to from_unixtime always in UTC:- drop temporary table if exists test1; create temporary table test1 ( ts bigint not null , asdate datetime as (date_add( "1970-1-1 00:00:00" , interval ts second )) persistent ) engine=innodb; Or if you want a different timezone then just convert explicitly:- drop temporary table if exists test1; create temporary table test1 ( ts bigint not null , asdate datetime as (convert_tz(date_add( "1970-1-1 00:00:00" , interval ts second ), "UTC" , "Europe/London" )) persistent ) engine=innodb;   insert into test1 (ts) values (1), (1606993464), (1603587540), (1603587600); select * from test1;
            RoyBellingan Roy Bellingan added a comment -

            This is a very cool idea!
            Amazing

            RoyBellingan Roy Bellingan added a comment - This is a very cool idea! Amazing

            People

              sanja Oleksandr Byelkin
              RoyBellingan Roy Bellingan
              Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.