Bug #36949 SQL-syntax error when using FORCE INDEX(index,PRIMARY)
Submitted: 26 May 2008 9:10 Modified: 15 Jul 2008 9:46
Reporter: Yair dfwerfw Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: FORCE INDEX syntax error
Triage: D3 (Medium)

[26 May 2008 9:10] Yair dfwerfw
Description:
CREATE TABLE IF NOT EXISTS my_table2 ( id int not null auto_increment, aaa varchar(255) null, PRIMARY KEY (id), INDEX (aaa) ) engine=innodb;

This select fails with a syntax error:
SELECT * FROM sbv_my_table2 FORCE INDEX(aaa,PRIMARY) ;

This select succeeds:
SELECT * FROM sbv_my_table2 FORCE INDEX(PRIMARY,aaa) ;

How to repeat:
.
[26 May 2008 9:11] Yair dfwerfw
I meant this select fails with a syntax error:
SELECT * FROM my_table2 FORCE INDEX(aaa,PRIMARY) ;

This select succeeds:
SELECT * FROM my_table2 FORCE INDEX(PRIMARY,aaa) ;
[26 May 2008 12:22] Miguel Solorzano
Thank you for the bug report. This bug doesn't affect 5.1:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.25-rc-debug Source distribution

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

mysql> CREATE TABLE IF NOT EXISTS my_table2 ( id int not null auto_increment, aaa varchar(255)
    -> null, PRIMARY KEY (id), INDEX (aaa) ) engine=innodb;
Query OK, 0 rows affected (0.38 sec)

mysql> SELECT * FROM my_table2 FORCE INDEX(aaa,PRIMARY) ;
Empty set (0.00 sec)

mysql> SELECT * FROM my_table2 FORCE INDEX(PRIMARY,aaa) ;
Empty set (0.00 sec)

mysql>
[6 Jun 2008 13:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47536

2636 Georgi Kodinov	2008-06-06
      Bug#36949: SQL-syntax error when using FORCE INDEX(index,PRIMARY)
      
      The list rule for the key list in the parser was written in an 
      incomplete way.
      Fixed by using a designated production for the list elements.
[15 Jul 2008 9:46] Georgi Kodinov
Since the problem is fixed in 5.1 and the workaround for 5.0 given I'm closing the bug. If you absolutely need the functionality, please apply the attached fix.