Bug #35555 SQL-syntax error when using FORCE INDEX()

• Submitted: 25 Mar 2008 15:32
• Modified: 9 Jun 2009 21:48
• Reporter: Armin Schöffmann (OCA)
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.0.60, 5.1, 6.0
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any
• Tags: FORCE INDEX() syntax error

[25 Mar 2008 15:32] Armin Schöffmann

Description:
On server 6.0.4 this query fails with syntax-error - with < 5.0.60 it doesn't:

"CREATE TEMPORARY TABLE`CT`(PRIMARY KEY(`serial_id`))ENGINE=MEMORY SELECT`serial_id`FROM`production`FORCE INDEX()WHERE(`lot_ident`= 'test_100_backthinning') ORDER BY`production`.`epd_time`DESC,`serial_id`DESC"	

The engine-type of 'production' seems not to matter.

How to repeat:
with 6.0.4:

mysql> CREATE TABLE t1 (serial_id integer);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE ct ENGINE=MEMORY SELECT serial_id from t1 FORCE IN
DEX() WHERE (serial_id=0);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ') WHE
RE (serial_id=0)' at line 1

mysql> CREATE TEMPORARY TABLE ct ENGINE=MEMORY SELECT serial_id from t1 WHERE (s
erial_id=0);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

with 5.0.54a

mysql> CREATE TABLE t1 (serial_id integer);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TEMPORARY TABLE ct ENGINE=MEMORY SELECT serial_id from t1 FORCE IN
DEX() WHERE (serial_id=0);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

[25 Mar 2008 15:57] Susanne Ebrecht

Verified as described with:
6.0.5-alpha-debug and
5.0.60-debug

[9 Jun 2009 21:49] Omer Barnir

See http://dev.mysql.com/doc/refman/5.1/en/index-hints.html