MySQL Bugs: #42539: _rowid can be used anywhere as identifier for pkey

Bug #42539	_rowid can be used anywhere as identifier for pkey
Submitted:	2 Feb 2009 11:12	Modified:	7 Oct 2017 13:24
Reporter:	Stewart Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
http://www.flamingspork.com/blog/2009/02/02/row-id-in-mysql-and-drizzle-and-the-engines/

drizzle> create table t1 (a int primary key, b varchar(100));
Query OK, 0 rows affected (0.02 sec)

drizzle> insert into t1 values (1,”foo”);
Query OK, 1 row affected (0.00 sec)

drizzle> update t1 set b=”foobar!” where _rowid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> select * from t1;
+—+———+
| a | b |
+—+———+
| 1 | foobar! |
+—+———+
1 row in set (0.00 sec)

So how is this implemented? In two places: in sql_base.cc find_field_in_table() and in table.cc during FRM parsing (this is how I found it). We can even do things Oracle can’t (insert, update and delete):

drizzle> update t1 set a=2 where _rowid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

drizzle> select * from t1;
+---+---------+
| a | b       |
+---+---------+
| 2 | foobar! |
+---+---------+
1 row in set (0.00 sec)

drizzle> update t1 set _rowid=3 where _rowid=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

drizzle> select * from t1;
+---+---------+
| a | b       |
+---+---------+
| 3 | foobar! |
+---+---------+
1 row in set (0.00 sec)

How to repeat:
see above

Suggested fix:
fix documentation

Need to establish that this is intentionally exposed to user and that we promise that it won't just Go Away.

I've verified that this works in 4.1/5.0/5.1/NDB-6.X/6.0.

Have requested architectural review, to ensure that we do intend to give it official support.

Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table trid(c1 int primary key, c2 int) engine=InnoDB;
Query OK, 0 rows affected (0.66 sec)

mysql> insert into trid values(1, 2);
Query OK, 1 row affected (0.13 sec)

mysql> select * from trid where _rowid=1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    2 |
+----+------+
1 row in set (0.14 sec)

mysql> update trid set c2 = 111 where _rowid = 1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from trid;
+----+------+
| c1 | c2   |
+----+------+
|  1 |  111 |
+----+------+
1 row in set (0.05 sec)

This should be clearly documented, as it is a really nice feature.

No feedback was provided. The bug is being suspended because we assume that you are no longer experiencing the problem. If this is not the case and you are able to provide the information that was requested earlier, please do so and change the status of the bug back to "Open". Thank you.

this probably shouldn't be closed as no feedback - unless there's some comment or request i'm missing.

Posted by developer:
 
Noted _rowid in relevant places: CREATE TABLE, CREATE INDEX.