MySQL Bugs: #88098: _rowid does not work when the table contains both PRIMARY and UNIQUE keys

Bug #88098	_rowid does not work when the table contains both PRIMARY and UNIQUE keys
Submitted:	15 Oct 2017 3:41	Modified:	28 Jul 2018 15:39
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The documentation states that _rowid can be used to refer to the single integer column in a PRIMARY KEY or to the single integer column in a UNIQUE index but attempting to use _rowid to refer to the single integer column in a UNIQUE index fails if the table contains a PRIMARY KEY.

The documentation for CREATE TABLE states that _rowid can be used to refer to the PRIMARY KEY single column or to the UNIQUE index single column. The documentation for CREATE INDEX states that _rowid can be used to refer to the UNIQUE index single column but does not mention the PRIMARY KEY option.

https://dev.mysql.com/doc/refman/5.7/en/create-table.html

If a PRIMARY KEY consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.

...

If a UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.

The documentation for CREATE INDEX discusses _rowid only for UNIQUE indexes

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

If a UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.

How to repeat:
Create a table with a single integer column in the PRIMARY KEY and in the UNIQUE index.

CREATE TABLE `foo` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO foo SET a=1, b=2;

Observe that _rowid refers to the single integer column in the PRIMARY KEY.

SELECT _rowid FROM foo\G
*************************** 1. row ***************************
_rowid: 1
1 row in set (0.00 sec)

Change the PRIMARY KEY to have two columns.

ALTER TABLE foo DROP PRIMARY KEY, ADD PRIMARY KEY(a,b);

Observe the error when trying to use _rowid to refer to the single integer column in the UNIQUE index.

SELECT _rowid FROM foo\G
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'

Drop the PRIMARY KEY.

ALTER TABLE foo DROP PRIMARY KEY;

Observe that _rowid refers to the single integer column in the UNIQUE index.

mysql> SELECT _rowid FROM foo\G
*************************** 1. row ***************************
_rowid: 2
1 row in set (0.00 sec)

Suggested fix:
Fix the server so _rowid refers to the single column in the UNQUE index when the PRIMARY KEY is not eligible or fix the documentation.

In either case, update the CREATE INDEX documentation to discuss using _rowid for the PRIMARY KEY.

I think the docs should be understood in such a way that row_id refers to an integer PK (if exists), else an non-Nullable Integer Unique Index (if exists). If both exist it will refer to the PK value. And if there are no integer PK and more than one non-Nullable Integer Unique Index, the server decides on its own which Unique Index it will refer to (probably the one listed first in SHOW CREATE TABLE). 

-- Peter
-- not a MySQL/Oracle person

Hi!

What Peter Laursen wrote is correct desciption. However, since different chapters in our manual seem to be out of sync, I can consider this as a documentation bug.

Verified as a documentation bug.

Posted by developer:
 
Changed the explanation in [create-index] to:

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists
of a single column that has an integer type, you can use _rowid to
refer to the indexed column in SELECT statements, as follows:

* _rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY
  consisting of a single integer column. If there is a PRIMARY KEY but
  it does not consist of a single integer column, _rowid cannot be
  used.

* Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL
  index if that index consists of a single integer column. If the first
  UNIQUE NOT NULL index does not consist of a single integer column,
  _rowid cannot be used.

Changed [create-table] to cross reference that explanation so that it's
consistent.