Description:
Since it is possible to have table/database names with dots in them, the CHECKSUM TABLE command will return ambiguous rows.
This is caused because CHECKSUM TABLE returns a column called table and promptly merges the database and table name into a single string separated by a dot.
Example:
mysql> create database `foo`;
Query OK, 1 row affected (0.00 sec)
mysql> create table `foo`.`bar.baz` (id int);
Query OK, 0 rows affected (0.05 sec)
mysql> create database `foo.bar`;
Query OK, 1 row affected (0.01 sec)
mysql> create table `foo.bar`.`baz` (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> checksum table `foo.bar`.`baz`, `foo`.`bar.baz`;
+-------------+----------+
| Table | Checksum |
+-------------+----------+
| foo.bar.baz | 0 |
| foo.bar.baz | 0 |
+-------------+----------+
2 rows in set (0.01 sec)
Clearly, there's no way to tell which row corresponds to which table for the checksum.
How to repeat:
mysql> create database `foo`;
Query OK, 1 row affected (0.00 sec)
mysql> create table `foo`.`bar.baz` (id int);
Query OK, 0 rows affected (0.05 sec)
mysql> create database `foo.bar`;
Query OK, 1 row affected (0.01 sec)
mysql> create table `foo.bar`.`baz` (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> checksum table `foo.bar`.`baz`, `foo`.`bar.baz`;
+-------------+----------+
| Table | Checksum |
+-------------+----------+
| foo.bar.baz | 0 |
| foo.bar.baz | 0 |
+-------------+----------+
2 rows in set (0.01 sec)
Suggested fix:
Quoting the table name in the return of CHECKSUM TABLE sounds problematic as it would likely break backward compatibility..
Perhaps a new command, CHECKSUM2 TABLE would be good such that it returns 3 columns: Database, Table, Checksum to ensure there are no ambiguity.
