MySQL Bugs: #9636: INSERT INTO table from a complex View

Bug #9636	INSERT INTO table from a complex View
Submitted:	5 Apr 2005 6:45	Modified:	15 May 2005 23:05
Reporter:	David Hammink	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.3 rpm	OS:	Linux (Linux Fedore Core 2)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
We are seting up a new Database and need Views and stored procedures. We need to transform old data to the new database (since the "old" data is still currently maintained)
TP5Prod is the new Database 
StatsGermany the old Database

The following insert cause the server to crash

INSERT INTO TP5Prod.tblServiceNumber
 (PrefixID,MachineID,DDI,PremiumRateNumber,GeographicNumber,TerminationID,SecurityCode,LanguageID)
		SELECT PrefixID,MachineID,DDI,PremiumRateNumber,IF(GeographicNumber IS NULL,'N/A',GeographicNumber) AS GeographicNumber,TerminationID,SecurityCode,LanguageID
		FROM vwServiceNumbers
		WHERE ServiceID IS NULL;

However if I do the insert from the "source" code of the View (so no view) it work ... (That means that views cannot always be trusted yet)
I tried to simulate it with a very simple view but that did not crah the server

Below is the view and the table creates
The view is 
CREATE ALGORITHM=UNDEFINED VIEW `StatsGermany`.`vwServiceNumbers` AS select `SN`.`ServiceID` AS `ServiceID`,(case `CP`.`CountryID` when (1 and (`NUM`.`TarifGruppe` = _latin1'INT_Cayman')) then 21 when (1 and (`NUM`.`TarifGruppe` like _latin1'Int_Naur%')) then 18 when (1 and (`NUM`.`TarifGruppe` = _latin1'INT_Sao T%')) then 20 when (1 and (`NUM`.`TarifGruppe` = _latin1'INT_SLEONE')) then 19 when (1 and (`NUM`.`TarifGruppe` = _latin1'INT_Solomon')) then 17 when (1 and (`NUM`.`TarifGruppe` = _latin1'DEKiribati')) then 8 when (1 and (`NUM`.`TarifGruppe` = _latin1'DENauru')) then 18 when (1 and (`NUM`.`TarifGruppe` like _latin1'0900%')) then 3 when (1 and (`NUM`.`TarifGruppe` like _latin1'TLOCAL') and (`TS`.`Vorwahl` = 167)) then 23 when (1 and (`NUM`.`TarifGruppe` like _latin1'TLOCAL') and (`TS`.`Vorwahl` = 169)) then 24 when (1 and (`NUM`.`TarifGruppe` like _latin1'TLOCAL') and (`TS`.`Vorwahl` = 199)) then 25 when (1 and (`NUM`.`TarifGruppe` like _latin1'TLOCAL') and (`TS`.`Vorwahl` = 546)) then 26 when (1 and (`NUM`.`TarifGruppe` like _latin1'T%')) then 16 when (1 and (`NUM`.`TarifGruppe` = _latin1'11848')) then 9 when (1 and (`NUM`.`TarifGruppe` = _latin1'11896')) then 9 when 1 then 16 when 2 then 4 when 3 then 5 else 0 end) AS `PrefixID`,(case `MP`.`Machine` when 1 then 8 when 2 then 2 when 6 then 6 else 1 end) AS `MachineID`,`DET`.`ProviderNr` AS `DDI`,concat(if((`DET`.`RufNr` = 0),if(((`NUM`.`TarifGruppe` = _latin1'11896') or (`NUM`.`TarifGruppe` = _latin1'11848')),`NUM`.`TarifGruppe`,_latin1'N/A'),if((`DET`.`RufNr` like _latin1'190%'),substr(`DET`.`RufNr`,4),`DET`.`RufNr`)),if(isnull(`NUM`.`PostFix`),_latin1'',`NUM`.`PostFix`)) AS `PremiumRateNumber`,concat(_latin1'0',`TS`.`Vorwahl`,_latin1'-',`TS`.`ProviderNr`) AS `GeographicNumber`,1 AS `TerminationID`,_latin1'' AS `SecurityCode`,2 AS `LanguageID` 
from (((((`StatsGermany`.`tblNummernDetails` `DET` 
left join `StatsGermany`.`tblNummern` `NUM` on((`DET`.`StammNR` = `NUM`.`StammNR`))) 
left join `StatsGermany`.`tblTalklineService` `TS` on((`DET`.`ProviderNr` = `TS`.`ProviderNr`))) 
left join `StatsGermany`.`tblCountry_ProviderNr` `CP` on((`DET`.`ProviderNr` = `CP`.`ProviderNr`))) 
left join `StatsGermany`.`tblMachine_ProviderNr` `MP` on((`DET`.`ProviderNr` = `MP`.`ProviderNr`))) 
left join `TP5Prod`.`tblServiceNumber` `SN` on((`DET`.`ProviderNr` = `SN`.`DDI`))) where (`DET`.`ActiveTalkline` = -(1));

CREATE TABLE `tblNummernDetails` (
  `ProviderNr` int(11) NOT NULL default '0',
  `StammNR` int(11) NOT NULL default '0',
  `PIANr` int(11) NOT NULL default '0',
  `RufNr` int(11) NOT NULL default '0',
  `Name` char(50) default NULL,
  `ActiveTalkline` tinyint(4) default '-1',
  PRIMARY KEY  (`ProviderNr`),
  KEY `RufNr` (`RufNr`),
  KEY `Name` (`Name`),
  KEY `StammNR` (`StammNR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `tblNummern` (
  `StammNR` int(11) NOT NULL default '0',
  `PIANr` bigint(20) default NULL,
  `Durchwahlbreite` tinyint(4) default NULL,
  `DurchwahlVon` int(11) default NULL,
  `DurchwahlBis` int(11) default NULL,
  `TarifGruppe` char(20) default NULL,
  `GoldeneNr` bigint(20) default NULL,
  `Bezeichnung` char(25) default NULL,
  `PostFix` char(10) default NULL,
  `Overflow` int(11) default NULL,
  `ServiceID` char(20) default NULL,
  `Turkey` int(11) default NULL,
  `Aktiv` smallint(6) default '-1',
  PRIMARY KEY  (`StammNR`),
  KEY `TarifGrupp` (`TarifGruppe`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `tblTalklineService` (
  `Vorwahl` char(3) NOT NULL,
  `ProviderNr` int(6) NOT NULL default '0',
  `Dienst` int(11) default NULL,
  `Style` int(11) default NULL,
  `Version` int(11) default NULL,
  PRIMARY KEY  (`ProviderNr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

CREATE TABLE `tblCountry_ProviderNr` (
  `CountryID` int(11) NOT NULL default '0',
  `ProviderNr` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ProviderNr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `tblMachine_ProviderNr` (
  `Machine` int(11) NOT NULL default '0',
  `ProviderNr` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Machine`,`ProviderNr`),
  KEY `ProviderNr` (`ProviderNr`),
  KEY `Machine` (`Machine`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

USE TP5Prod;
CREATE TABLE `tblServiceNumber` (
  `ServiceID` int(11) NOT NULL auto_increment,
  `PrefixID` tinyint(4) default NULL,
  `MachineID` tinyint(4) default NULL,
  `DDI` int(11) NOT NULL,
  `PremiumRateNumber` varchar(30) default NULL,
  `GeographicNumber` varchar(30) default NULL,
  `TerminationID` tinyint(4) default NULL,
  `SecurityCode` varchar(10) default NULL,
  `LanguageID` tinyint(4) default NULL,
  PRIMARY KEY  (`ServiceID`),
  KEY `tblPrefix_tblServiceNumber_FK1` (`PrefixID`),
  KEY `tblMachine_tblServiceNumber_FK1` (`MachineID`),
  KEY `tblTermination_tblServiceNumber_FK1` (`TerminationID`),
  KEY `tblLanguage_tblServiceNumber_FK1` (`LanguageID`),
  KEY `DDI` (`DDI`),
  KEY `GeographicNumber` (`GeographicNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Stack :
0x809ac27
0x8342518
0x80cbab8
0x80cbbc5
0x80f13c0
0x80d592d
0x80d8b08
0x80d525f
0x80ad8b4
0x80b1b19
0x80aa624
0x80a9f70
0x80a9514
0x833fccc
0x836961a

How to repeat:
Just do the insert again and again

Having changed the ALGORITHM to TEMPTABLE has solved the problem
CREATE ALGORITHM=TEMPTABLE VIEW `StatsGermany`.`vwServiceNumbers` AS 
.....

Verified with latest 5.0 BK tree.

(gdb) bt
#0  0x081e5876 in find_table_in_list (table=0x8efa9a8, offset=4, db_name=0x8ee12b0 "test",
    table_name=0x8ef98e8 "tblServiceNumber") at sql_base.cc:707
#1  0x081cbba8 in find_table_in_global_list (table=0x8efa9a8, db_name=0x8ee12b0 "test",
    table_name=0x8ef98e8 "tblServiceNumber") at mysql_priv.h:942
#2  0x081e5ab8 in unique_table (table=0x8ef9930, table_list=0x8efa9a8) at sql_base.cc:761
#3  0x08219def in select_insert::prepare (this=0x8f1b568, values=@0x8edd5bc, u=0x8edd374) at sql_insert.cc:1804
#4  0x081f40ca in JOIN::prepare (this=0x8ef5748, rref_pointer_array=0x8edd674, tables_init=0x8efa9a8,
    wild_num=0, conds_init=0x8f1b4a8, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0,
    proc_param_init=0x0, select_lex_arg=0x8edd550, unit_arg=0x8edd374) at sql_select.cc:462
#5  0x081f86f9 in mysql_select (thd=0x8edd328, rref_pointer_array=0x8edd674, tables=0x8efa9a8, wild_num=0,
    fields=@0x8edd5bc, conds=0x8f1b4a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=3498330624, result=0x8f1b568, unit=0x8edd374, select_lex=0x8edd550) at sql_select.cc:2031
#6  0x081f3829 in handle_select (thd=0x8edd328, lex=0x8edd368, result=0x8f1b568,
    setup_tables_done_option=1073741824) at sql_select.cc:239
#7  0x081c2970 in mysql_execute_command (thd=0x8edd328) at sql_parse.cc:3176
#8  0x081c8225 in mysql_parse (thd=0x8edd328,
    inBuf=0x8ef9770 "INSERT INTO tblServiceNumber\n (PrefixID,MachineID,DDI,PremiumRateNumber,GeographicNumber,TerminationID,\nSecurityCode,LanguageID)\nSELECT PrefixID,MachineID,DDI,PremiumRateNumber,IF(GeographicNumber IS\n"..., length=332) at sql_parse.cc:5156
#9  0x081be8ce in dispatch_command (command=COM_QUERY, thd=0x8edd328,
    packet=0x8ef1711 "INSERT INTO tblServiceNumber\n (PrefixID,MachineID,DDI,PremiumRateNumber,GeographicNumber,TerminationID,\nSecurityCode,LanguageID)\nSELECT PrefixID,MachineID,DDI,PremiumRateNumber,IF(GeographicNumber IS\n"..., packet_length=333) at sql_parse.cc:1647
#10 0x081be17d in do_command (thd=0x8edd328) at sql_parse.cc:1453
#11 0x081bd308 in handle_one_connection (arg=0x8edd328) at sql_parse.cc:1110
#12 0xb7e4514b in pthread_start_thread () from /lib/libpthread.so.0
#13 0xb7e451df in pthread_start_thread_event () from /lib/libpthread.so.0
#14 0xb7d7850a in clone () from /lib/libc.so.6

Thank you for bugreport!
I can't repeat this bug on last bk repository, so it was fixed by some pervious bugfixes.