MySQL Bugs: #3030: INSERT into myTable select ... from ... where ... IN (select ... from myTable..

Bug #3030	INSERT into myTable select ... from ... where ... IN (select ... from myTable..
Submitted:	1 Mar 2004 23:29	Modified:	9 Mar 2004 10:51
Reporter:	Mickael Besson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	5.0.0	OS:	Windows (Windows NT4)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Hello,

I was happy to see the version 4.1.1 integrate the INSERT into SELECT request.

But, when I use this type of request, it doesn't function :

INSERT into myTable SELECT ... from ... where ... IN (SELECT... from myTable..)

Please, can you look at this bug.
Thank you

How to repeat:

INSERT into myTable SELECT ... from ... where ... IN (SELECT... from myTable..)
bugs

I forgot to give you the mysql server version : 5.0.0

We need a test case with the SQL statements to create your tables and demonstrate this behavior.  In my own very simple test I was unable to repeat this, so please add a small test case.

There's my request (I can't give you structure of tables) :

INSERT INTO BAE_DOCPA (APP_COD, PRJA_COD, DDOC_NUM, DOCF_REF, VDOCF_NUM, DOCPA_DTMAJ, DOCPA_UTI) 
SELECT 'GER', 'GER-P1', 0, BAE_DOCPA.DOCF_REF, VDOCF_NUM, DOCPA_DTMAJ, DOCPA_UTI
FROM BAE_DOCPA, BAE_DOCF 
WHERE (BAE_DOCPA.DOCF_REF=BAE_DOCF.DOCF_REF) 
AND (APP_COD='GER') 
AND (PRJA_COD='GER-POP') 
AND (DDOC_NUM=0)
AND (CONCAT(BAE_DOCPA.DOCF_REF,'$',BAE_DOCPA.VDOCF_NUM) 
NOT IN (SELECT CONCAT(DOCF_REF,'$',VDOCF_NUM) FROM BAE_DOCPA WHERE (APP_COD='GER') AND (PRJA_COD='GER-P1'))) 
AND (NOT ((BAE_DOCF.TDOC_COD='FDA') 
OR (BAE_DOCF.TDOC_COD='SYN') 
OR (BAE_DOCF.TDOC_COD='MCC') 
OR (BAE_DOCF.TDOC_COD='FAS')))

I was obliged to replace :
‘SELECT CONCAT(DOCF_REF,'$',VDOCF_NUM) FROM BAE_DOCPA WHERE (APP_COD='GER') AND (PRJA_COD='GER-P1'))’
 by a variable which contains the result of this CONCAT (ex : '40$04','47$1','5$104'...)

I cannot repeat this against the current development branches, so it is possible you encountered some bug that has already been fixed.  It is impossible to say without a complete test case, as I may very easily not test something particular to your situation...  If you can provide a complete test case which demonstrates the behavior I will re-test.

There are structures of tables bae_docpa and bae_docf

CREATE TABLE `bae_docpa` (
  `APP_COD` char(3) NOT NULL default '',
  `PRJA_COD` varchar(8) NOT NULL default '',
  `DOCF_REF` varchar(64) NOT NULL default '',
  `VDOCF_NUM` varchar(5) NOT NULL default '',
  `DDOC_NUM` tinyint(4) NOT NULL default '0',
  `DOCPA_DTMAJ` date NOT NULL default '0000-00-00',
  `DOCPA_UTI` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`APP_COD`,`PRJA_COD`,`DOCF_REF`,`VDOCF_NUM`),
  KEY `BAE_DOCPA_DDOC_FKIDX` (`APP_COD`,`PRJA_COD`,`DDOC_NUM`),
  KEY `BAE_DOCPA_PER_FKIDX` (`DOCPA_UTI`),
  KEY `BAE_DOCPA_PRJA_FKIDX` (`APP_COD`,`PRJA_COD`),
  KEY `BAE_DOCPA_VDOCF_FKIDX` (`DOCF_REF`,`VDOCF_NUM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `bae_docf` (
  `DOCF_REF` varchar(64) NOT NULL default '',
  `DOCF_TIT` varchar(240) NOT NULL default '',
  `TDOC_COD` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`DOCF_REF`),
  KEY `BAE_DOCF_TDOC_FKIDX` (`TDOC_COD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

request now : 

INSERT INTO BAE_DOCPA (APP_COD, PRJA_COD, DDOC_NUM, DOCF_REF, VDOCF_NUM,
DOCPA_DTMAJ, DOCPA_UTI)
SELECT 'GER', 'GER-P1', 0, BAE_DOCPA.DOCF_REF, VDOCF_NUM, DOCPA_DTMAJ,
DOCPA_UTI
FROM BAE_DOCPA, BAE_DOCF
WHERE (BAE_DOCPA.DOCF_REF=BAE_DOCF.DOCF_REF)
AND (APP_COD='GER')
AND (PRJA_COD='GER-POP')
AND (DDOC_NUM=0)
AND (('jfh$igu')
NOT IN (SELECT CONCAT(DOCF_REF,'$',VDOCF_NUM) FROM BAE_DOCPA WHERE
(APP_COD='GER') AND (PRJA_COD='GER-P1')))
AND (NOT((BAE_DOCF.TDOC_COD='FDA')
OR (BAE_DOCF.TDOC_COD='SYN')
OR (BAE_DOCF.TDOC_COD='MCC')
OR (BAE_DOCF.TDOC_COD='FAS')))

bug that appears : 

Column 'DOCPA_UTI' specified twice