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

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: