Bug #44904 LIMIT IN INSERT ... DUPLICATE KEY UPDATE DIFFER IN THE SMPLE LIMIT
Submitted: 15 May 2009 14:19 Modified: 10 Feb 2018 17:27
Reporter: giovanni silva Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Windows (XP SP3)
Assigned to: CPU Architecture:Any

[15 May 2009 14:19] giovanni silva
Description:
A LIMIT clause works differently for these two types of update:

-- update row with pk = 3
update cep set cep = 'XXX' where  codigo = 4 or cep = 'AAA' limit 1

--update row with pk = 4
insert into cep(codigo, cep) values(4, 'AAA') on duplicate key update cep = 'XXX';

SQL

CREATE DATABASE IF NOT EXISTS codebasicplusbd_graffi;
USE codebasicplusbd_graffi;

--
-- Definition of table `cep`
--

DROP TABLE IF EXISTS `cep`;
CREATE TABLE `cep` (
  `codigo` int(11) NOT NULL auto_increment,
  `tipo` varchar(50) default NULL,
  `logradouro` varchar(100) default NULL,
  `cep` varchar(10) default NULL,
  `estado` varchar(2) default NULL,
  `bairro_inicial` varchar(100) default NULL,
  `bairro_final` varchar(100) default NULL,
  `cidade` varchar(100) default NULL,
  `ativo` tinyint(1) default '1',
  `empresa` int(11) default NULL,
  `ultima_alteracao` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `current_machine` varchar(100) default NULL,
  PRIMARY KEY  (`codigo`),
  UNIQUE KEY `index_cep` USING BTREE (`cep`),
  KEY `index_ativo` (`ativo`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `cep`
--

INSERT INTO `cep` (`codigo`,`tipo`,`logradouro`,`cep`,`estado`,`bairro_inicial`,`bairro_final`,`cidade`,`ativo`,`empresa`,`ultima_alteracao`,`current_machine`) VALUES 
 (1,'Rua','FRANCISCO ABDON, QD 38, LT 01 - SETOR SULESTE','75560000','GO','','','CACHOEIRA DOURADA-GO',1,NULL,'2008-07-16 15:39:44',NULL),
 (2,'Rua','BR 452 KM 0 - SETOR INDUSTRIAL','75901970','GO','RODOVIA','RODOVIA','RIO VERDE',1,NULL,'2008-08-11 15:09:48',NULL),
 (3,NULL,NULL,'AAA',NULL,NULL,NULL,NULL,1,NULL,'2009-05-15 10:22:14',NULL),
 (4,NULL,NULL,'CCC',NULL,NULL,NULL,NULL,1,NULL,'2009-05-15 10:23:11',NULL);

How to repeat:
Create DB and execute SQLs
[17 May 2009 11:48] Sveta Smirnova
Thank you for the report.

You said:

----<Q>----
A LIMIT clause works differently for these two types of update:

-- update row with pk = 3
update cep set cep = 'XXX' where  codigo = 4 or cep = 'AAA' limit 1

--update row with pk = 4
insert into cep(codigo, cep) values(4, 'AAA') on duplicate key update cep = 'XXX';
----</Q>----

But second query does not have LIMIT clause. Please explain what you mean.
[17 Jun 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[10 Feb 2018 17:27] Roy Lyseng
Posted by developer:
 
Closing bug since it has been suspended for 9 years.