[#MDEV-16857] system-invisible row_end is displayed in SHOW INDEX

There is no easy way to check the column information of the auto-generated row_start and row_end columns when a table is created WITH SYSTEM VERSIONING. Due to this, it is causing a bug with phpMyAdmin and other similar tools/libraries (including my own PHP Universal Database Library). These utilities usually do a SELECT * query on their (Browse) tab. In phpMyAdmin, most features, such as editing row values, is disabled because they assume they don't have the full PRIMARY KEY in the results set. We already know that row_end in the PRIMARY KEY is GENERATED, but there is no way to query this so the application software can be aware of this and handle it properly. ~~MDEV-16804~~ is adding the GENERATED flag to these columns, however if the columns are automatically created rather than manually created, they don't even show up via DESC / SHOW COLUMNS, so there is no way to add the needed conditional code to handle these cases at the application layer.

The only other work around I can think of at the moment for the application layer would be to assume any column listed in the PRIMARY KEY that is NOT listed in SHOW COLUMNS is a system GENERATED column. But there may be other applications that want to know the data type information of the PRIMARY KEY, so this would still need addressed.

As a note, according to documentation, user created INVISIBLE columns show up in SHOW COLUMNS, so system created INVISIBLE columns probably should too. https://mariadb.com/kb/en/library/invisible-columns/

CREATE TABLE vertest (x INT PRIMARY KEY) WITH SYSTEM VERSIONING;

SHOW COLUMNS FROM vertest;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| x     | int(11) | NO   | PRI | NULL    |       |

+-------+---------+------+-----+---------+-------+

SHOW FULL COLUMNS FROM vertest;

+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+

| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |

+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+

| x     | int(11) | NULL      | NO   | PRI | NULL    |       | select,insert,update,references |         |

+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+

SHOW INDEX FROM vertest;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| vertest |          0 | PRIMARY  |            1 | x           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

| vertest |          0 | PRIMARY  |            2 | row_end     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

[MDEV-16857] system-invisible row_end is displayed in SHOW INDEX Created: 2018-07-30 Updated: 2021-07-06 Resolved: 2021-07-06
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Create Table, Information Schema, Versioned Tables
Affects Version/s:	10.3.8
Fix Version/s:	10.3.31, 10.4.21, 10.5.12, 10.6.4

[MDEV-16857] system-invisible row_end is displayed in SHOW INDEX Created: 2018-07-30 Updated: 2021-07-06 Resolved: 2021-07-06