Bug #3587 Server crushes after executing query with connecting two subquery on two fields
Submitted: 28 Apr 2004 12:30 Modified: 2 May 2004 18:14
Reporter: Sergey Gurin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.0alpha OS:Microsoft Windows (WinXP)
Assigned to: CPU Architecture:Any

[28 Apr 2004 12:30] Sergey Gurin
Description:
1 step: Create Table "FRUT_T_OBOR".
------------------------------------
CREATE TABLE FRUT_T_OBOR
(
  NAK VARCHAR(20)  NOT NULL,
  KODSKL VARCHAR(20)  NOT NULL,
  KODTOV VARCHAR(20)  NOT NULL,
  KOLVO DECIMAL(13,3)  NOT NULL,
  DATA DATE  NOT NULL,
  KODP VARCHAR(20)  NOT NULL,
  ZSNDS DECIMAL(12,6)  NOT NULL,
  ZBNDS DECIMAL(12,6)  NOT NULL,
  VKEY DECIMAL(13,3)  NOT NULL,
  KEYS1 DECIMAL(13,6)  NOT NULL,
  NODES VARCHAR(7),
  USERS VARCHAR(25),
  USERDATA DATE,
  ZTRANS DECIMAL(12,4),
  PRIMARY KEY (NAK,KODSKL,KODTOV,KOLVO)
);
-- [Create Indexes]
CREATE      INDEX T_OBORFORK_NAKIDX ON FRUT_T_OBOR
(NAK ,KODSKL ) ;

ALTER TABLE FRUT_T_OBOR ADD  FOREIGN KEY (NAK,KODSKL)
REFERENCES FRUT_T_VZPOK (NAK,KODSKL)     ;

CREATE      INDEX T_OBORFORK_TOVIDX ON FRUT_T_OBOR
(KODTOV ) ;

ALTER TABLE FRUT_T_OBOR ADD  FOREIGN KEY (KODTOV)
REFERENCES FRUT_T_TOV (KODTOV)     ;

CREATE      INDEX T_OBORIND_DATATOV ON FRUT_T_OBOR
(DATA ASC,KODTOV ASC,KODSKL ASC) ;

2 step: Create Table "FRUT_T_VZPOK".
------------------------------------
CREATE TABLE FRUT_T_VZPOK
(
  KODP VARCHAR(20)  NOT NULL,
  DATA DATE  NOT NULL,
  NAK VARCHAR(20)  NOT NULL,
  OTPOPL DECIMAL(12,2)  NOT NULL,
  SUMOPL DECIMAL(12,2)  NOT NULL,
  DOLG DECIMAL(12,2)  NOT NULL,
  KODBANK VARCHAR(20)  NOT NULL,
  BNAL VARCHAR(1)  NOT NULL,
  KODEXPED VARCHAR(20),
  DATACLOSE DATE,
  ADR VARCHAR(40),
  NAMEMEN VARCHAR(20),
  NK VARCHAR(1),
  TC VARCHAR(1),
  NG VARCHAR(1),
  KODSKL VARCHAR(20)  NOT NULL,
  NODES VARCHAR(200),
  DOGNO VARCHAR(20),
  USERS VARCHAR(25),
  CHP VARCHAR(10),
  KASKN VARCHAR(5),
  KODPOGR INT,
  USERDATA DATE,
  ALK VARCHAR(1),
  SKID VARCHAR(1),
  NAKCLOSE VARCHAR(20)  NOT NULL,
  NAKPOST VARCHAR(20),
  STAT VARCHAR(1)  NOT NULL,
  KODTIPT VARCHAR(20),
  DATAPOST DATE,
  KODRAUT VARCHAR(20),
  KODADR VARCHAR(3),
  PRIMARY KEY (NAK,KODSKL)
);
-- [Create Indexes]
CREATE      INDEX T_VZPOKFORK_EXPEDIDX ON FRUT_T_VZPOK
(KODEXPED ) ;

ALTER TABLE FRUT_T_VZPOK ADD  FOREIGN KEY (KODEXPED)
REFERENCES FRUT_T_EXPEDIT (KODEXP)     ;

CREATE      INDEX T_VZPOKFORK_KODBANKIDX ON FRUT_T_VZPOK
(KODBANK ) ;

ALTER TABLE FRUT_T_VZPOK ADD  FOREIGN KEY (KODBANK)
REFERENCES FRUT_T_BANK (KODBANK)     ;

CREATE      INDEX T_VZPOKFORK_KODPIDX ON FRUT_T_VZPOK
(KODP ) ;

ALTER TABLE FRUT_T_VZPOK ADD  FOREIGN KEY (KODP)
REFERENCES FRUT_T_POKUP (KODP)     ;

CREATE      INDEX T_VZPOKFORK_RAUTIDX ON FRUT_T_VZPOK
(KODRAUT ) ;

ALTER TABLE FRUT_T_VZPOK ADD  FOREIGN KEY (KODRAUT)
REFERENCES FRUT_T_RAUT (KODRAUT)     ;

CREATE      INDEX T_VZPOKFORK_SKLIDX ON FRUT_T_VZPOK
(KODSKL ) ;

ALTER TABLE FRUT_T_VZPOK ADD  FOREIGN KEY (KODSKL)
REFERENCES FRUT_T_SKL (KODSKL)     ;

CREATE      INDEX T_VZPOKFORK_TIPTIDX ON FRUT_T_VZPOK
(KODTIPT ) ;

ALTER TABLE FRUT_T_VZPOK ADD  FOREIGN KEY (KODTIPT)
REFERENCES FRUT_T_TIP (KODTIPT)     ;

CREATE      INDEX T_VZPOKIND_BNAL ON FRUT_T_VZPOK
(BNAL ASC) ;

CREATE      INDEX T_VZPOKIND_DATBANK ON FRUT_T_VZPOK
(DATA ASC,KODBANK ASC,KODP ASC,NAK ASC,KODSKL ASC) ;

CREATE      INDEX T_VZPOKIND_NAKPOST ON FRUT_T_VZPOK
(NAKPOST ASC) ;

CREATE      INDEX T_VZPOKIND_PSTAT ON FRUT_T_VZPOK
(KODP ASC,STAT ASC) ;

3 step: Create Table "FRUT_V_TOV".
------------------------------------
CREATE TABLE FRUT_V_TOV
(
  KODGR VARCHAR(20)  NOT NULL,
  NAMEGRR VARCHAR(50)  NOT NULL,
  NAMEGRA VARCHAR(50),
  GRUPGL VARCHAR(25),
  SORTGR INT  NOT NULL,
  COMPL VARCHAR(1),
  KODTOV VARCHAR(20)  NOT NULL,
  NAMER VARCHAR(255)  NOT NULL,
  NAMEA VARCHAR(35),
  VKEY DECIMAL(12,3)  NOT NULL,
  NDS SMALLINT  NOT NULL,
  KODKASS VARCHAR(20),
  HMEL VARCHAR(5),
  KODPOS VARCHAR(20),
  KODTARA VARCHAR(20),
  KODTYP VARCHAR(20),
  POSTARA VARCHAR(20)  NOT NULL,
  EMK DECIMAL(12,2),
  OB DECIMAL(12,2),
  SHOW1 VARCHAR(1)  NOT NULL,
  KODTOVGL VARCHAR(20)  NOT NULL,
  NOTES VARCHAR(35),
  TARA VARCHAR(1)  NOT NULL,
  PROD10 DECIMAL(12,5),
  PROD11 DECIMAL(12,5),
  VKEY2 DECIMAL(12,3)  NOT NULL,
  SORTTOV TINYINT,
  PRIMARY KEY (KODTOV)
);
-- [Create Indexes]
CREATE      INDEX T_TOVFORK_GRIDX ON FRUT_V_TOV
(KODGR ) ;

ALTER TABLE FRUT_V_TOV ADD  FOREIGN KEY (KODGR)
REFERENCES FRUT_T_GRUP (KODGR)     ;

CREATE      INDEX T_TOVFORK_POSIDX ON FRUT_V_TOV
(KODPOS ) ;

ALTER TABLE FRUT_V_TOV ADD  FOREIGN KEY (KODPOS)
REFERENCES FRUT_V_TOV (KODTOV)     ;

CREATE      INDEX T_TOVFORK_TARAIDX ON FRUT_V_TOV
(KODTARA ) ;

ALTER TABLE FRUT_V_TOV ADD  FOREIGN KEY (KODTARA)
REFERENCES FRUT_V_TOV (KODTOV)     ;

CREATE      INDEX T_TOVFORK_TYPIDX ON FRUT_V_TOV
(KODTYP ) ;

ALTER TABLE FRUT_V_TOV ADD  FOREIGN KEY (KODTYP)
REFERENCES FRUT_T_TOVTYP (KODTYP)     ;

CREATE      INDEX T_TOVIND_NAMER ON FRUT_V_TOV
(NAMER ASC) ;

4 step: Create Table "FRUT_V_RAUT".
------------------------------------
CREATE TABLE FRUT_V_RAUT
(
  KODPODR VARCHAR(20)  NOT NULL,
  NAMEPODR VARCHAR(20)  NOT NULL,
  KODDISTR VARCHAR(20)  NOT NULL,
  NAMEDISTR VARCHAR(20)  NOT NULL,
  KODRAUT VARCHAR(20)  NOT NULL,
  NAMERAUT VARCHAR(20)  NOT NULL,
  NAMETORG VARCHAR(20),
  PRIMARY KEY (KODRAUT)
);
-- [Create Indexes]
CREATE      INDEX T_RAUTIND_MEN ON FRUT_V_RAUT
(NAMERAUT ASC) ;

5 step: Executing query.
-----------------------------------------
mysql> select b.namedistr,b.nameraut,b.nametorg, b.mm, b.kodp, b.adr,a.newkl , b.keys1
from
(select date_format(h.data,'%m.%Y') mm,  h.namedistr,h.nameraut,h.nametorg,sum(h.adr) newkl
from
(SELECT r.namedistr,substr(r.nameraut,1,3) nameraut,r.nametorg,h.kodp,min(h.data) 

data,count(distinct(concat(h.kodp,h.kodadr))) adr
FROM   FRUT_T_OBOR i,  FRUT_T_VZPOK h, FRUT_V_TOV p, FRUT_V_RAUT r
Where i.nak=h.nak and i.kodtov=p.kodtov and r.kodraut=h.kodraut  and 

h.data<=str_to_date('03.01.2002','%d.%m.%Y')
      and p.grupgl='01. Pepsi-Cola Drinks'
GROUP BY  r.namedistr, substr(r.nameraut,1,3), r.nametorg, h.kodp
HAVING min(i.data) between str_to_date('01.01.2002','%d.%m.%Y') and str_to_date('03.01.2002','%d.%m.%Y')) h
group by date_format(h.data,'%m.%Y'),  h.namedistr,h.nameraut,h.nametorg) as a left join
(SELECT r.namedistr,substr(r.nameraut,1,3) nameraut,r.nametorg, date_format(h.data,'%m.%Y') mm,
                count(distinct(concat(h.kodp,h.kodadr))) adr, count(distinct(h.kodp)) kodp, sum(-i.keys1) 

keys1
FROM   FRUT_T_OBOR i,  FRUT_T_VZPOK h, FRUT_V_TOV p, FRUT_V_RAUT r
Where i.nak=h.nak and i.kodtov=p.kodtov and r.kodraut=h.kodraut  and h.data between 

str_to_date('01.01.2002','%d.%m.%Y') and str_to_date('03.01.2002','%d.%m.%Y') and h.kodskl='11'
           and p.grupgl='01. Pepsi-Cola Drinks'
GROUP BY  r.namedistr, substr(r.nameraut,1,3), r.nametorg, date_format(h.data,'%m.%Y')) as b on a.mm=b.mm and a.nameraut=b.nameraut;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Windows show error dialog "error in mysqld-nt.exe..."

6 step: Only with two fields.
If connect tables on two fields (a.mm=b.mm and a.nameraut=b.nameraut) then error, but only on one field (a.mm=b.mm) all Ok!

7 step: query with all fields like

select *
from
(select date_format(h.data,'%m.%Y') mm,  h.namedistr,h.nameraut,h.nametorg,sum(h.adr) newkl
from
(SELECT r.namedistr,substr(r.nameraut,1,3) nameraut,r.nametorg,h.kodp,min(h.data) 

data,count(distinct(concat(h.kodp,h.kodadr))) adr
FROM   FRUT_T_OBOR i,  FRUT_T_VZPOK h, FRUT_V_TOV p, FRUT_V_RAUT r
Where i.nak=h.nak and i.kodtov=p.kodtov and r.kodraut=h.kodraut  and 

h.data<=str_to_date('03.01.2002','%d.%m.%Y')
      and p.grupgl='01. Pepsi-Cola Drinks'
GROUP BY  r.namedistr, substr(r.nameraut,1,3), r.nametorg, h.kodp
HAVING min(i.data) between str_to_date('01.01.2002','%d.%m.%Y') and str_to_date('03.01.2002','%d.%m.%Y')) h
group by date_format(h.data,'%m.%Y'),  h.namedistr,h.nameraut,h.nametorg) as a left join
(SELECT r.namedistr,substr(r.nameraut,1,3) nameraut,r.nametorg, date_format(h.data,'%m.%Y') mm,
                count(distinct(concat(h.kodp,h.kodadr))) adr, count(distinct(h.kodp)) kodp, sum(-i.keys1) 

keys1
FROM   FRUT_T_OBOR i,  FRUT_T_VZPOK h, FRUT_V_TOV p, FRUT_V_RAUT r
Where i.nak=h.nak and i.kodtov=p.kodtov and r.kodraut=h.kodraut  and h.data between 

str_to_date('01.01.2002','%d.%m.%Y') and str_to_date('03.01.2002','%d.%m.%Y') and h.kodskl='11'
           and p.grupgl='01. Pepsi-Cola Drinks'
GROUP BY  r.namedistr, substr(r.nameraut,1,3), r.nametorg, date_format(h.data,'%m.%Y')) as b on a.mm=b.mm and a.nameraut=b.nameraut;

OKEY ALL TIME.

How to repeat:
~10 times all crash, but then - first Ok, second crash and so on.

Suggested fix:
I don't know.
[2 May 2004 18:14] MySQL Verification Team
Thank you for the bug report I was able to repeat with version 5.0.0
however it was already fixed on current version 5.0.1.

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