MySQL Bugs: #112275: "Extra" field has NULL value if table is temporary

Bug #112275	"Extra" field has NULL value if table is temporary
Submitted:	6 Sep 2023 13:41	Modified:	7 Sep 2023 8:10
Reporter:	David Eliezer Kesselmann	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	8.0.34, 8.0.11	OS:	Windows (Windows 10 Pro Build: 19045.3393)
Assigned to:		CPU Architecture:	x86 (12th Gen Intel(R) Core(TM) i5-1235U 2.50 GHz)
Tags:	columns extra, inconsistency, MYSQL SERVER, Mysql server 8.0.34, SHOW COLUMNS, temporary table, Temporary Table Behaviour

Description:
The "Extra" field should only have the following possible values:

- auto_increment for columns that have the AUTO_INCREMENT attribute.
- on update CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.
- VIRTUAL GENERATED or STORED GENERATED for generated columns.
- DEFAULT_GENERATED for columns that have an expression default value.

If none of these extras are present, then the field returns an empty varchar, but if you check the extra field on a temporary table, then it will return null if none of these extras are present.

This is an inconsistency and is not documented anywhere.

How to repeat:
1. Create an Database
2. Create a temporary table
3. Create a normal table
4. Execute the SHOW COLUMNS query on both tables
5. You'll see that the normal table will return an empty varchar and the temporary table will return a NULL value

SQL Queries:
1. CREATE DATABASE db;
2. CREATE TEMPORARY TABLE temp (`id` INTEGER, `name` VARCHAR(100));
3. CREATE TABLE normal (`id` INTEGER, `name` VARCHAR(100));
4. SHOW COLUMNS FROM temp FROM db;
4. SHOW COLUMNS FROM normal FROM db;

Suggested fix:
The temporary table should also return an empty varchar like its documented.

Hello David,

Thank you for the report and feedback.
IMHO this behavior is seen at least since 8.0.11(first GA release in 8.0). I couldn't locate any document to confirm if this was known change. 

- 8.0.11+

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 db;
Query OK, 1 row affected (0.05 sec)

mysql> use db;
Database changed
mysql> CREATE TEMPORARY TABLE temp (`id` INTEGER, `name` VARCHAR(100));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE normal (`id` INTEGER, `name` VARCHAR(100));
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW COLUMNS FROM temp FROM db;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    | NULL  |
| name  | varchar(100) | YES  |     | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM normal FROM db;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

- 5.7.42 seems to be fine(as documented)

[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-5.7.43]$ bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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 db;
Query OK, 1 row affected (0.00 sec)

mysql> use db;
Database changed
mysql> CREATE TEMPORARY TABLE temp (`id` INTEGER, `name` VARCHAR(100));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE normal (`id` INTEGER, `name` VARCHAR(100));
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW COLUMNS FROM temp FROM db;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM normal FROM db;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

regards,
Umesh Shastry