[MDEV-13132] Information Schema does not show whether column default is expression or literal Created: 2017-06-20  Updated: 2018-06-29  Resolved: 2017-07-05

Status: Closed
Project: MariaDB Server
Component/s: Admin statements
Affects Version/s: 10.2.6
Fix Version/s: 10.2.7

Type: Bug Priority: Major
Reporter: Will Bryant Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: default, expression

Issue Links:
Problem/Incident
causes MDEV-13341 information_schema.columns Column_def... Closed
causes MDEV-13750 Document recent changes to informatio... Closed
Relates
relates to MDEV-14053 Unquoted NULL default values in infor... Closed
relates to MDEV-16622 NULL is ambiguous in the DESC Default... Closed

 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.

 Comments   
Comment by Will Bryant [ 2017-06-20 ]

I propose adding a column to INFORMATION_SCHEMA.COLUMNS to indicate if the COLUMN_DEFAULT is an expression.

Comment by Sergei Golubchik [ 2017-06-20 ]

I don't understand how the standard resolves this. DEFAULT clause is defined as

<default clause> ::=
 
        DEFAULT <default option>
 
<default option> ::=
 
        <literal>
 
        | <datetime value function>
 
        | USER
 
        | CURRENT_USER
 
        | CURRENT_ROLE
 
        | SESSION_USER
 
        | SYSTEM_USER
 
        | CURRENT_CATALOG
 
        | CURRENT_SCHEMA
 
        | CURRENT_PATH
 
        | <implicitly typed value specification>

So, while it does not allow arbitrary expressions, it does suppose some generated values. Now, INFORMATION_SCHEMA.COLUMNS is a standard view, the COLUMN_DEFAULT is defined as

7) Let DC be the descriptor of the column being described. If DC includes a <default option>, then let DO be that <default option>. The value of COLUMN_DEFAULT is
Case:
a) If DC does not include a <default option>, then the null value.
b) If CHARACTER_LENGTH(DO) > ML, then "TRUNCATED".
c) Otherwise, DO.

I don't understand what the standard specified value of the COLUMN_DEFAULT should be, if DO is not a <literal>.

Comment by Will Bryant [ 2017-06-20 ]

Yeah, interesting.

The reason I was suggesting an extra column was that I assumed the IS_GENERATED column was added for similar reasons, so it seemed like the most natural way to add more information without changing the existing columns.

Is the IS_GENERATED column in the standard?

Comment by Sergei Golubchik [ 2017-06-21 ]

Yes, IS_GENERATED is the standard column.

Now, I'm not completely sure yet, the standard is unclear, but other databases would quote the literal in the COLUMN_DEFAULT. That is, the result is like

+-------------+-----------------------+
 
| COLUMN_NAME | COLUMN_DEFAULT        |
 
+-------------+-----------------------+
 
| cts         | current_timestamp()   |
 
| sts         | 'current_timestamp()' |
 
+-------------+-----------------------+

So, perhaps, this is how the standard should be interpreted.

Comment by Alexander Barkov [ 2017-06-21 ]

How other databases handle defalt values:

SQL Server uses quotes for literals:

DROP TABLE t1;
 
CREATE TABLE t1 (a VARCHAR(10) DEFAULT CURRENT_USER, b VARCHAR(10) DEFAULT 'CURRENT_USER');
 
SELECT COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1';


COLUMN_NAME    COLUMN_DEFAULT
 
a    (user_name())
 
b    ('CURRENT_USER')

PostgreSQL also uses quotes for literals:

DROP TABLE t1;
 
CREATE TABLE t1 (a VARCHAR(10) DEFAULT CURRENT_USER, b VARCHAR(10) DEFAULT 'CURRENT_USER');
 
SELECT COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1';


 column_name |          column_default           
-------------+-----------------------------------
 
 a           | "current_user"()
 
 b           | 'CURRENT_USER'::character varying
 
(2 rows)

Oracle has a table ALL_TAB_COLUMNS, which is equivalent for INFORMATION_SCHEMA.COLUMNS. It also uses quotes for literals:

DROP TABLE t1;
 
CREATE TABLE t1 (a VARCHAR(10) DEFAULT USER, b VARCHAR(10) DEFAULT 'USER');
 
SELECT COLUMN_NAME,DEFAULT_LENGTH,DATA_DEFAULT FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='T1';


COLUMN_NAME		       DEFAULT_LENGTH
 
------------------------------ --------------
 
DATA_DEFAULT
 
--------------------------------------------------------------------------------
 
A					    4
 
USER
 
 
 
B					    6
 
'USER'

Comment by Will Bryant [ 2017-06-25 ]

OK, so we could easily add an extra flag in the Extra column returned by SHOW FULL FIELDS, so I think this maybe the quickest win.

But if we want to add it to INFORMATION_SCHEMA.COLUMNS as well, which I would prefer personally, we will have to break compatibility and introduce quotes like the other databases (or add a non-standard column).

Comment by Sergei Golubchik [ 2017-06-26 ]

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.

Comment by Sébastien Vanvelthem [ 2017-08-28 ]

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

Comment by Toshko Andreev [ 2017-09-08 ]

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

Comment by Sergei Golubchik [ 2017-09-08 ]

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).

Comment by Toshko Andreev [ 2017-09-08 ]

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.

Generated at Thu Feb 08 08:03:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.