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

"Function or expression is not allowed for column 'fd2'" on VIRTUAL column

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Not a Bug
        • Affects Version/s: 10.0.10
        • Fix Version/s: None
        • Component/s: None
        • Labels:
          None
        • Environment:
          Darwin Mattui-MacBook-Pro.local 13.2.0 Darwin Kernel Version 13.2.0: Thu Apr 17 23:03:13 PDT 2014; root:xnu-2422.100.13~1/RELEASE_X86_64 x86_64

          Description

          MariaDB knowledge said "User-defined functions and stored functions cannot be used (built-in functions can be used)" on https://mariadb.com/kb/en/virtual-columns/

          So I think we can use any built-in function on VIRTUAL column.
          But, MariaDB server complain about the function I used, Especially "NOW()". Without NOW() table created successfully.

          Is it expected ?
          And are there any document about what function we can use on VIRTUAL column ? 

          MariaDB [test]> create table t_virtual (fd1 datetime, fd2 bigint as (DATEDIFF(now(), fd1)) VIRTUAL);
ERROR 1901 (HY000): Function or expression is not allowed for column 'fd2'

MariaDB [test]> create table t_virtual (fd1 datetime, fd2 bigint as (DATEDIFF(fd1, fd1)) VIRTUAL);
Query OK, 0 rows affected (0.03 sec)

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                The documentation says that only deterministic expressions are permitted. But NOW() is not deterministic — if you call it twice, you may get different results.

                Show
                serg Sergei Golubchik added a comment - The documentation says that only deterministic expressions are permitted. But NOW() is not deterministic — if you call it twice, you may get different results.
                Hide
                Permalink
                Matt74 Seunguck Lee added a comment -

                Thanks Sergei Golubchik.

                I didn't know that NOW() is not deterministic.
                Statement based binary log don't complain deterministic property of NOW() and also on searching index.
                In one statement, NOW() return the same value. so I thought it's kind of determinstic. ^^;

                And even if it's not deterministic,
                If MariaDB can process NOW() like statement based binary log,
                It would be good (I think).

                Anyway thanks.

                Show
                Matt74 Seunguck Lee added a comment - Thanks Sergei Golubchik. I didn't know that NOW() is not deterministic. Statement based binary log don't complain deterministic property of NOW() and also on searching index. In one statement, NOW() return the same value. so I thought it's kind of determinstic. ^^; And even if it's not deterministic, If MariaDB can process NOW() like statement based binary log, It would be good (I think). Anyway thanks.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    Matt74 Seunguck Lee
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: