MySQL Bugs: #16260: VIEW MERGE fails (connection lost) for some select

Bug #16260	VIEW MERGE fails (connection lost) for some select
Submitted:	6 Jan 2006 18:36	Modified:	21 Feb 2006 22:16
Reporter:	haroon chaudhry	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.17/5.0.19 BK	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
MySQL server drops clinet connection for certain select statements against view defined using algo MERGE, however it works fine when algo is changed to temo tables. Please see "how to repeat" for an example

How to repeat:

Create table prod (
       prodid MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
       prodtype varchar(10), 
       prodname varchar(20),
       PRIMARY KEY (prodid)
)ENGINE=InnoDB;

Create table prodclass (
       prodclassid MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
       ptype varchar(10), 
       classname varchar(20),
       PRIMARY KEY (prodclassid)
)ENGINE=InnoDB;

Create table classes (
       classesid MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
       prodid MEDIUMINT(9) NOT NULL,
       prodclassid MEDIUMINT(9) NOT NULL,
       PRIMARY KEY (classesid),
       index(prodid),
       CONSTRAINT FK_class_1 FOREIGN KEY (prodid) REFERENCES prod (prodid) ON DELETE NO ACTION ON UPDATE NO ACTION,
       index(prodclassid),
       CONSTRAINT FK_class_2 FOREIGN KEY (prodclassid) REFERENCES prodclass (prodclassid) ON DELETE NO ACTION ON UPDATE NO ACTION 
)ENGINE=InnoDB;

 CREATE OR REPLACE VIEW classaa AS 
 SELECT  c.* 
 from classes c join prodclass pc on pc.prodclassid=c.prodclassid and pc.ptype='A'
 join prod p on p.prodid=c.prodid and p.prodtype='A';

 CREATE OR REPLACE VIEW classbb AS 
 SELECT  c.* 
 from classes c join prodclass pc on pc.prodclassid=c.prodclassid and pc.ptype='B'
 join prod p on p.prodid=c.prodid and p.prodtype='B';

insert into prod values(1,'A','a1');

insert into prodclass values(1,'A','a1');

insert into classes values(1,1,1);

select * from prod p left outer join classbb cbb on p.prodid=cbb.prodid left outer join prodclass pc on cbb.prodclassid=pc.prodclassid  where p.prodid=1;

select * from prod p left outer join classaa cbb on p.prodid=cbb.prodid left outer join prodclass pc on cbb.prodclassid=pc.prodclassid  where p.prodid=1;

Suggested fix:
currently we are using TEMPTABLE algo to create views (that works) but it causes performance loss as temp table does not use indices

Thank you for the bug report I was able to repeat the behavior reported.

mysql> select * from prod p left outer join classbb cbb on p.prodid=cbb.prodid left
    -> outer join prodclass pc on cbb.prodclassid=pc.prodclassid  where p.prodid=1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

miguel@hegel:~/dbs/5.0> libexec/mysqld
030408  0:56:32  InnoDB: Started; log sequence number 0 259212368
030408  0:56:32 [Note] libexec/mysqld: ready for connections.
Version: '5.0.19-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld got signal 11;

<cut>

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e792c0 = select * from prod p left outer join classbb cbb on p.prodid=cbb.prodid left
outer join prodclass pc on cbb.prodclassid=pc.prodclassid  where p.prodid=1
thd->thread_id=2

This problem manifests itself in the cases when we have a nested outer join for which it
can be inferred that one of the inner tables is a single row table.

The problem can be demonstrated with a simpler test:

CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
                 id int NOT NULL,
                 pid int NOT NULL);

INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);

SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
                     ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
                   LEFT JOIN t2 ON (t3.pid=t2.pid)
  WHERE p.id=1;

Instead of the above query that causes a crash we can use the following sequence: 

CREATE VIEW v1 AS
  SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';

SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
                   LEFT JOIN t2 ON v1.pid=t2.pid
  WHERE p.id=1;

These commands crash the server by same reason.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1768

ChangeSet
  1.2010 06/01/27 21:20:28 igor@rurik.mysql.com +3 -0
  Fixed bug #16260.
  The problem has manifested itself in the cases when we have a nested outer join
  for which it can be inferred that one of the inner tables is a single row table.

  sql/sql_select.cc
    1.389 06/01/27 21:20:09 igor@rurik.mysql.com +2 -1
    Fixed bug #16260.
    The problem has manifested itself in the cases when we have a nested outer join
    for which it can be inferred that one of the inner tables is a single row table.
    A table is never considered as a const table if it is used in a nested join 
    that serves as an inner operand of an outer join.  

  mysql-test/t/join_nested.test
    1.18 06/01/27 21:20:09 igor@rurik.mysql.com +28 -0
    Added a test case for bug #16260.

  mysql-test/r/join_nested.result
    1.22 06/01/27 21:20:09 igor@rurik.mysql.com +23 -0
    Added a test case for bug #16260.

The fix will appear in 5.0.19 and 5.1.7

Documented in 5.0.19 and 5.1.7 changelogs:

      <listitem>
        <para>
          MySQL server dropped client connection for certain SELECT
          statements against views
          defined that used <literal>MERGE</literal> algorithm. (Bug #16260)
        </para>
      </listitem>