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

Information Schema does not show whether column default is expression or literal

Details

    Description

      MDEV-10134 introduced support for creating a table whose default is an expression.

      SHOW [FULL] COLUMNS/FIELDS and INFORMATION_SCHEMA.COLUMNS show these default expressions, but because these just look like strings, it seems to be impossible for a client program to tell if the default is an expression or a literal string.

      For example:

      MariaDB [test]> create table testtbl (cts varchar(255) default current_timestamp, sts varchar(255) default 'current_timestamp()'); show columns from testtbl; select * from information_schema.columns where table_name='testtbl';
      		 
      Query OK, 0 rows affected (0.13 sec)
      		 
       
      		 
      +-------+--------------+------+-----+---------------------+-------+
      		 
      | Field | Type         | Null | Key | Default             | Extra |
      		 
      +-------+--------------+------+-----+---------------------+-------+
      		 
      | cts   | varchar(255) | YES  |     | current_timestamp() |       |
      		 
      | sts   | varchar(255) | YES  |     | current_timestamp() |       |
      		 
      +-------+--------------+------+-----+---------------------+-------+
      		 
      2 rows in set (0.00 sec)
      		 
       
      		 
      +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+--------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+
      		 
      | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT      | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE  | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT | IS_GENERATED | GENERATION_EXPRESSION |
      		 
      +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+--------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+
      		 
      | def           | test         | testtbl    | cts         |                1 | current_timestamp() | YES         | varchar   |                      255 |                    255 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(255) |            |       | select,insert,update,references |                | NEVER        | NULL                  |
      		 
      | def           | test         | testtbl    | sts         |                2 | current_timestamp() | YES         | varchar   |                      255 |                    255 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(255) |            |       | select,insert,update,references |                | NEVER        | NULL                  |
      		 
      +---------------+--------------+------------+-------------+------------------+---------------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+--------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+
      		 
      2 rows in set (0.00 sec)

      This makes it hard to dump and restore the schema properly in database utilities (eg. my Kitchen Sync).

      Note that the server does know (SHOW CREATE TABLE has quotes for the sts DEFAULT but not for the cts DEFAULT), but frameworks would have to parse this instead of using the SHOW statements or INFORMATION_SCHEMA views provided for giving the other table/column metadata.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13341 information_schema.columns Column_default now return 'null' instead of null

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-13750 Document recent changes to information_schema.COLUMNS table

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14053 Unquoted NULL default values in information schema with views

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16622 NULL is ambiguous in the DESC Default column output

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 6 older comments
            serg Sergei Golubchik added a comment -

            Right. It looks like we've misunderstood the standard when INFORMATION_SCHEMA.COLUMNS was originally implemented. If yes, this is certainly a bug that should be fixed.

            serg Sergei Golubchik added a comment - Right. It looks like we've misunderstood the standard when INFORMATION_SCHEMA.COLUMNS was originally implemented. If yes, this is certainly a bug that should be fixed.
            belgattitude Sébastien Vanvelthem added a comment -

            Sergei, FYI see https://jira.mariadb.org/browse/MDEV-13341?focusedCommentId=99353&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-99353

            belgattitude Sébastien Vanvelthem added a comment - Sergei, FYI see https://jira.mariadb.org/browse/MDEV-13341?focusedCommentId=99353&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-99353
            Ravenheart Toshko Andreev added a comment -

            Is it possible to add an override setting that will return the same values like before 10.2?

            Ravenheart Toshko Andreev added a comment - Is it possible to add an override setting that will return the same values like before 10.2?
            serg Sergei Golubchik added a comment -

            No, but you can use SHOW FIELDS that returns the same values as before (and the same values that INFORMATION_SCHEMA.COLUMNS used to return).

            serg Sergei Golubchik added a comment - No, but you can use SHOW FIELDS that returns the same values as before (and the same values that INFORMATION_SCHEMA.COLUMNS used to return).
            Ravenheart Toshko Andreev added a comment -

            I'm asking because we use dbForge for MySql to do all our database administration/development and currently its broken because if this and we cannot do schema comparisons between different servers.

            They are working on a new version that will have support for 10.2 but it may take them months, and we can't wait that long before we must update our clients.

            Ravenheart Toshko Andreev added a comment - I'm asking because we use dbForge for MySql to do all our database administration/development and currently its broken because if this and we cannot do schema comparisons between different servers. They are working on a new version that will have support for 10.2 but it may take them months, and we can't wait that long before we must update our clients.

            People

              serg Sergei Golubchik
              will.bryant Will Bryant
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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