| Bug #98283 | DEFAULT_VALUE in I_S.innodb_columns shows wrong result. | ||
|---|---|---|---|
| Submitted: | 19 Jan 2020 7:51 | Modified: | 20 Jan 2020 10:09 |
| Reporter: | tom wang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.12, 8.0.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Jan 2020 7:51]
tom wang
[20 Jan 2020 9:15]
MySQL Verification Team
Hello tom wang, Thank you for the bug report and test case. Imho this is known and documented behavior which I can confirm from the latest GA build 8.0.19 results. Quoting from manual " DEFAULT_VALUE The initial default value of a column that was added instantly using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT. If the default value is NULL or was not specified, this column reports NULL. An explicitly specified non-NULL default value is shown in an internal binary format. Subsequent modifications of the column default value do not change the value reported by this column. " For more details, please see https://dev.mysql.com/doc/refman/8.0/en/innodb-columns-table.html -- Also, same confirmed from my tests bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database if not exists bug98283; Query OK, 1 row affected (0.00 sec) mysql> use bug98283; Database changed mysql> create table t1 (a int primary key ,b int); Query OK, 0 rows affected (0.01 sec) mysql> select * from information_schema.innodb_tables where name='bug98283/t1'; +----------+-------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-------------+------+--------+-------+------------+---------------+------------+--------------+ | 1059 | bug98283/t1 | 33 | 5 | 2 | Dynamic | 0 | Single | 0 | +----------+-------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.01 sec) mysql> select * from information_schema.innodb_columns where table_id = 1059; +----------+------+-----+-------+--------+-----+-------------+------------------------------+ | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | HAS_DEFAULT | DEFAULT_VALUE | +----------+------+-----+-------+--------+-----+-------------+------------------------------+ | 1059 | a | 0 | 6 | 1283 | 4 | 0 | 0x | | 1059 | b | 1 | 6 | 1027 | 4 | 0 | 0x | +----------+------+-----+-------+--------+-----+-------------+------------------------------+ 2 rows in set (0.27 sec) mysql> alter table t1 add column c int default 2; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_columns where table_id = 1059; +----------+------+-----+-------+--------+-----+-------------+------------------------------+ | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | HAS_DEFAULT | DEFAULT_VALUE | +----------+------+-----+-------+--------+-----+-------------+------------------------------+ | 1059 | a | 0 | 6 | 1283 | 4 | 0 | 0x | | 1059 | b | 1 | 6 | 1027 | 4 | 0 | 0x | | 1059 | c | 2 | 6 | 1027 | 4 | 1 | 0x3830303030303032 | +----------+------+-----+-------+--------+-----+-------------+------------------------------+ 3 rows in set (0.26 sec) mysql> create table t2 (a int primary key , b int); Query OK, 0 rows affected (0.01 sec) mysql> select * from information_schema.innodb_tables where name='bug98283/t2'; +----------+-------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-------------+------+--------+-------+------------+---------------+------------+--------------+ | 1060 | bug98283/t2 | 33 | 5 | 3 | Dynamic | 0 | Single | 0 | +----------+-------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_columns where table_id = 1060; +----------+------+-----+-------+--------+-----+-------------+------------------------------+ | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | HAS_DEFAULT | DEFAULT_VALUE | +----------+------+-----+-------+--------+-----+-------------+------------------------------+ | 1060 | a | 0 | 6 | 1283 | 4 | 0 | 0x3830303030303032 | | 1060 | b | 1 | 6 | 1027 | 4 | 0 | 0x3830303030303032 | +----------+------+-----+-------+--------+-----+-------------+------------------------------+ 2 rows in set (0.27 sec) regards, Umesh
[20 Jan 2020 9:41]
tom wang
Hi Umesh Shastry quoting from manual " If the default value is NULL or was not specified, this column reports NULL. An explicitly specified non-NULL default value is shown in an internal binary format." so DEFAULT_VALUE of column a and b in table 'bug98283/t2' should be NULL like 'bug98283/t1' rather than 0x3830303030303032. another case: master8>create table t3 (a int primary key ,b int default 0); master8>select * from information_schema.innodb_tables where name like '%t3'; +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+ | 1061 | sbtest/t3 | 33 | 5 | 4 | Dynamic | 0 | Single | 0 | +----------+-----------+------+--------+-------+------------+---------------+------------+--------------+ master8>select * from information_schema.innodb_columns where table_id = 1061; +----------+------+-----+-------+--------+-----+-------------+---------------+ | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | HAS_DEFAULT | DEFAULT_VALUE | +----------+------+-----+-------+--------+-----+-------------+---------------+ | 1061 | a | 0 | 6 | 1283 | 4 | 0 | 80000002 | | 1061 | b | 1 | 6 | 1027 | 4 | 0 | 80000002 | +----------+------+-----+-------+--------+-----+-------------+---------------+ DEFAULT_VALUE of column b in table t3 should not be 80000002.
[20 Jan 2020 10:09]
MySQL Verification Team
Agree, thank you for the feedback. If this is intended behavior then doc should be modified to avoid confusion. regards, Umesh
