Bug #822 error executing a procedure
Submitted: 9 Jul 2003 17:28 Modified: 3 Oct 2003 3:46
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Linux (linux Redhat 7.3)
Assigned to: Bugs System CPU Architecture:Any

[9 Jul 2003 17:28] [ name withheld ]
Description:
this error appear when execute a procedure:

Error 1110: Column 'tipo' specified twice

when one "blank record" is inserted the error disappears...
changing:
 insert into folios (anio,tipo,descri) values (cAnio, cTipo,cDescri);
by:
 insert into folios (id_folio) values (default);
in the procedure sp_ins_folio

How to repeat:
delimiter |;
CREATE TABLE folios (
  id_folio   int(11)       NOT NULL auto_increment,
  anio       varchar(4)    NOT NULL default '',
  tipo       char(1)       NOT NULL default '',
  descri     varchar(30)   NOT NULL default '',
  folio      decimal(5,0)  NOT NULL default '0',
  PRIMARY KEY (id_folio),
  UNIQUE KEY tip_pol (anio,tipo)
) TYPE=InnoDB
|

CREATE PROCEDURE sp_ins_folio(cAnio varchar(4), cTipo char(1), cDescri varchar(30))
begin
   Declare n int default 0;
   Select count(*) into n from folios where anio=cAnio and tipo = cTipo;
   if n = 0 then
      insert into folios (anio,tipo,descri) values (cAnio, cTipo,cDescri);
      commit;
   end if;
end
|

call sp_ins_folio('2003','1','some description')
|
[19 Aug 2003 2:45] Per-Erik Martin
This turns out to be due to a problem with certain optimizations
during execution of selects that breaks when executed from a stored procedure.
It will in fact cause crashes (intermittently) with different builds or
slightly different circumstances.
The problem is that some field items (e.g. aggregate fields like count()) are
reallocated during execution and then ends up in the wrong memory block
(one that's freed efter the statement has finished).

The problem is already known and is the TODO, to be fixed asap.
[1 Oct 2003 8:13] Michael Widenius
Fix exists and is verified. Will be pushed soon.
[3 Oct 2003 3:46] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html