Bug #20236 incorrect INSERT syntax is allowed and produces confusing results

• Submitted: 2 Jun 2006 20:34
• Modified: 2 Jun 2006 20:40
• Reporter: Matthew Lord
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.20, 5.0.22
• OS: Any (All)
• CPU Architecture: Any

[2 Jun 2006 20:34] Matthew Lord

Description:
INSERT INTO foo set col1=2 *AND* col2=3; inserts 0's for col1 and col2.  

How to repeat:
use test;

CREATE TABLE `avail` (
`id_prod` int(33) NOT NULL default '0',
`mois` int(11) NOT NULL default '0',
`pkid` int(11) NOT NULL auto_increment,
PRIMARY KEY (`pkid`),
UNIQUE KEY `kim` (`id_prod`,`mois`),
KEY `kmi` (`mois`,`id_prod`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into avail set id_prod=132431 and mois=200606;
select * from avail where id_prod=132431 and mois=200606;
insert into avail set id_prod=132431 and mois=200606;

insert into avail set id_prod=132431, mois=200606;
select * from avail where id_prod=132431 and mois=200606;
insert into avail set id_prod=132431, mois=200606; 

Suggested fix:
We should either document this, return a syntax error or insert the specified values.

[2 Jun 2006 20:36] Matthew Lord

I also tested this with a MyISAM table and the results are the same.

[2 Jun 2006 20:48] 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

Additional info:

This is not a bug because the expression in SET is evaluated as follows:

INSERT INTO avail SET id_prod=132431 AND mois=200606;

Due to precedence of operators it becomes:

INSERT INTO avail SET id_prod = (132431 AND (mois=200606));

It is matter of different discussion if it makes sense to allow column name to appearin such expression.

The correct syntax for INSERT with values for multiple columns is:

INSERT INTO avail SET id_prod=132431,  mois=200606;