Bug #611 HANDLER tbl READ 'PRIMARY' ... does not work!

• Submitted: 6 Jun 2003 8:12
• Modified: 6 Jun 2003 9:08
• Reporter: Pascal Drecker
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.13-nt
• OS: Windows (Windows XP Pro)
• CPU Architecture: Any

[6 Jun 2003 8:12] Pascal Drecker

Description:
The following commands return ERROR 1064:

    HANDLER mytable READ 'PRIMARY' FIRST;
    HANDLER mytable READ 'PRIMARY' > (2);

My secondary key has the same layout and everything works fine:

    HANDLER mytable READ skey FIRST;
    HANDLER mytable READ skey > (2);

A workaround: Do not create a PRIMARY KEY and instead create a UNIQUE INDEX pkey. But this is a very bad solution.

How to repeat:
mysql> CREATE TABLE mytable (myid INT, PRIMARY KEY (myid), UNIQUE INDEX skey (my
id));
Query OK, 0 rows affected (0.00 sec)

mysql> show columns from mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| myid  | int(11) |      | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show index from mytable;
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| mytable |          0 | skey     |            1 | myid        | A         |
       3 |     NULL | NULL   |      | BTREE      |         |
| mytable |          0 | PRIMARY  |            1 | myid        | A         |
       3 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM mytable;
+------+
| myid |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> HANDLER mytable OPEN;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER mytable READ skey FIRST;
+------+
| myid |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> HANDLER mytable READ skey > (2);
+------+
| myid |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> HANDLER mytable READ 'PRIMARY' FIRST;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near ''PRIMARY' FI
RST' at line 1

mysql> HANDLER mytable READ 'PRIMARY' > (2);
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near ''PRIMARY' >
(2)' at line 1

[6 Jun 2003 9:08] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

As manual points out in chapter 
6.4.2 HANDLER Syntax

...

Note: If you're using HANDLER interface for PRIMARY KEY you should remember to quote the name: HANDLER tbl READ `PRIMARY` > (...) 

PRIMARY should be quoted with backticks not quotes.

I am adding comment about this into manual. Thank you for noticing this.

mysql> HANDLER mytable READ `PRIMARY` FIRST;
+------+
| myid |
+------+
|    1 |
+------+

[6 Jun 2003 10:20] Pascal Drecker

Oh! )-: Thank you! (-: