Bug #15243 Syntax error in a correct SQL setence

• Submitted: 25 Nov 2005 8:52
• Modified: 25 Nov 2005 9:43
• Reporter: Fernando GM
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.15
• OS: Windows (Windows)
• CPU Architecture: Any

[25 Nov 2005 8:52] Fernando GM

Description:
Error of syntax??
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 'Driver(id, Program_id)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
)
TYPE=I' at line 7

How to repeat:
*Example, "imagine the rest of the database is correct":

CREATE TABLE Car (
  idCar INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Driver_id VARCHAR(20) NOT NULL,
  Driver_Program_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(idCar),
  FOREIGN KEY(Driver_id, Driver_Program_id)
    REFERENCES Driver(id, Program_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
TYPE=InnoDB;

*This sentence gives the next error:
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 'Driver(id, Program_id)
      ON DELETE RESTRICT
      ON UPDATE CASCADE
)
TYPE=I' at line 7

*but this sentence where the only difference is a black space in "Driver<->(id"

CREATE TABLE Car (
  idCar INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Driver_id VARCHAR(20) NOT NULL,
  Driver_Program_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(idCar),
  FOREIGN KEY(Driver_id, Driver_Program_id)
    REFERENCES Driver (id, Program_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
TYPE=InnoDB;

*This sentence is correct!!!

Suggested fix:
Revision of SQL parser??

[25 Nov 2005 9:43] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat neither on 5.0.15-nt, nor on 5.0.17-BK on Linux. Here is what I tried:

mysql> create table Driver (id varchar(20) not null, Program_id integer unsigned
 not null, primary key (id, Program_id)) engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE Car (
    ->   idCar INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   Driver_id VARCHAR(20) NOT NULL,
    ->   Driver_Program_id INTEGER UNSIGNED NOT NULL,
    ->   PRIMARY KEY(idCar),
    ->   FOREIGN KEY(Driver_id, Driver_Program_id)
    ->     REFERENCES Driver(id, Program_id)
    ->     ON DELETE RESTRICT
    ->     ON UPDATE CASCADE
    -> )
    -> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message            |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+--------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-nt |
+-----------+
1 row in set (0.01 sec)