MySQL Bugs: #11611: syntax error on insert

Bug #11611	syntax error on insert
Submitted:	28 Jun 2005 12:55	Modified:	16 Jul 2005 20:41
Reporter:	Wannes Sels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.x	OS:
Assigned to:		CPU Architecture:	Any

Description:
When inserting a new row without filling all columns using following syntax:
INSERT INTO table(column1,column2) VALUES(value1, value2);
MySQL reports a syntax error.

Workaround is to put a space between table name and ( as such:
INSERT INTO table (column1,column2) VALUES(value1, value2);
MySQL now inserts the data.

How to repeat:
see bug description

Suggested fix:
Do not force the space to be present. Problem occured on both 5.0.4 and 5.0.7 on Windows XP. Problem did not occur on 4.0.20a-nt. If this behaviour is valid SQL-92 (sorry, I did not check), at least allow an option for backward compatibility

I was unable to repeat the behavior reported with my own simple
test:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.7-beta-nt

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

mysql> create table tb10 (col1 int, col2 int, col3 int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into tb10(col1,col2) values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb10(col1,col2) values(2, 2);
Query OK, 1 row affected (0.00 sec)

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

Additional info:

This depends on the name of your table - which you didn't tell. It's not SQL-92, but a MySQL extension - see the manual for IGNORE_SPACE for details (it's the option you were asking for)