[MDEV-20210] If you have an INVISIBLE VIRTUAL column, SHOW CREATE TABLE doesn't list it as INVISIBLE Created: 2019-07-30  Updated: 2019-08-21  Resolved: 2019-08-21

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.3.16, 10.3, 10.4
Fix Version/s: 10.3.18, 10.4.8

Type: Bug Priority: Major
Reporter: Robert Humphries Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None
Environment:

Tested on Windows 10, 64bit.


 Description   

When you have a table that has INVISIBLE VIRTUAL columns, if you run SHOW CREATE TABLE, the INVISIBLE attribute is not present.

CREATE OR REPLACE TABLE `a` (`i` INT, `v` CHAR(1) GENERATED ALWAYS AS ('a') INVISIBLE);
 
INSERT INTO `a` VALUES (1);
 
SELECT `i`, `v` FROM `a`;
 
SELECT * FROM `a`;
 
SHOW CREATE TABLE `a`;
 
DROP TABLE `a`;

As a side note, I don't know if it is relevant, but If you run CREATE TABLE `b` LIKE `a` then the INVISIBLE columns appear to be defined correctly, although the SHOW CREATE TABLE is still incorrect.

 Comments   
Comment by Alice Sherepa [ 2019-07-30 ]

Thanks! Reproduced as described on 10.3,10.4: 

MariaDB [test]> CREATE TABLE t1 (i INT, v int GENERATED ALWAYS AS (1) INVISIBLE);
 
Query OK, 0 rows affected (0.032 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE t1;
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                         |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `i` int(11) DEFAULT NULL,
 
  `v` int(11) GENERATED ALWAYS AS (1) VIRTUAL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> DESC t1;
 
+-------+---------+------+-----+---------+------------------------------+
 
| Field | Type    | Null | Key | Default | Extra                        |
 
+-------+---------+------+-----+---------+------------------------------+
 
| i     | int(11) | YES  |     | NULL    |                              |
 
| v     | int(11) | YES  |     | NULL    | VIRTUAL GENERATED, INVISIBLE |
 
+-------+---------+------+-----+---------+------------------------------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [test]> CREATE OR REPLACE TABLE t1 (i int, v int INVISIBLE);
 
Query OK, 0 rows affected (0.049 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE t1;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `i` int(11) DEFAULT NULL,
 
  `v` int(11) INVISIBLE DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)

Comment by Aleksey Midenkov [ 2019-08-19 ]

Ok to push.

Comment by Anel Husakovic [ 2019-08-21 ]

Fixed with 888f6852261c67a57f21ae8

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