MySQL Bugs: #14026: Using nested LEFT JOIN in a VIEW with Prepared Statements causes crash.

Bug #14026	Using nested LEFT JOIN in a VIEW with Prepared Statements causes crash.
Submitted:	14 Oct 2005 13:09	Modified:	9 Nov 2005 3:20
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.15-BK	OS:	Linux (Linux, Any)
Assigned to:		CPU Architecture:	Any

Description:
Using a nested LEFT JOIN within a view, and then selecting from this view using prepared statements causes the MySQL server to crash after the *second* EXECUTE of the prepared statement. 

There seem to be other details that cause this as well. For instance, removing the "AND ..." from the nested LEFT JOIN allows the statement to run.

Running the SELECT statement from the VIEW directly in the prepared statement also allows the statement to run.

Sinisa notes that this is not a duplicate of bug #13095.

How to repeat:
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;

CREATE TABLE t1 (x int, y int);

CREATE TABLE t2 (x int, y int, z int);

CREATE TABLE t3 (x int, y int, z int);

CREATE TABLE t4 (x int, y int, z int);

DROP VIEW v1;

CREATE VIEW v1
AS
SELECT t1.x
FROM ((t1 JOIN t2 ON ((t1.y = t2.y)))
JOIN (t3
LEFT JOIN t4 ON (t3.y = t4.y) AND (t3.z = t4.z)));

PREPARE debug_stmt_1 FROM
"SELECT COUNT(*) 
FROM v1
WHERE x = ?";

SET @parm1=1;

EXECUTE debug_stmt_1 USING @parm1;
EXECUTE debug_stmt_1 USING @parm1;

Verified on 5.0.15-BK as described (ChangeSet@1.2024.1.20, 2005-10-12 22:44:42-07:00, patg@krsna.patg.net):

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0,00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0,00 sec)

mysql> DROP TABLE t2;
Query OK, 0 rows affected (0,00 sec)

mysql> DROP TABLE t3;
Query OK, 0 rows affected (0,00 sec)

mysql> DROP TABLE t4;
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TABLE t1 (x int, y int);
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TABLE t2 (x int, y int, z int);
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TABLE t3 (x int, y int, z int);
Query OK, 0 rows affected (0,00 sec)

mysql> CREATE TABLE t4 (x int, y int, z int);
Query OK, 0 rows affected (0,00 sec)

mysql> DROP VIEW v1;
ERROR 1051 (42S02): Unknown table 'test.v1'

mysql> CREATE VIEW v1
    -> AS
    -> SELECT t1.x
    -> FROM ((t1 JOIN t2 ON ((t1.y = t2.y)))
    -> JOIN (t3
    -> LEFT JOIN t4 ON (t3.y = t4.y) AND (t3.z = t4.z)));
Query OK, 0 rows affected (0,01 sec)

mysql> PREPARE debug_stmt_1 FROM
    -> "SELECT COUNT(*)
    "> FROM v1
    "> WHERE x = ?";

Query OK, 0 rows affected (0,01 sec)
Statement prepared

mysql> SET @parm1=1;
Query OK, 0 rows affected (0,00 sec)

mysql> EXECUTE debug_stmt_1 USING @parm1;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0,00 sec)

mysql> EXECUTE debug_stmt_1 USING @parm1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
051014 16:12:10  mysqld restarted

But I've got different results in the error log:

051014 16:11:20 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.15-rc'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

pure virtual method called
mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 Kbytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8f37088
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbba60504, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x814c8d1
0x64ef18
(nil)
0xd37467
0xd374a4
0xd379c8
0x810b8f9
0x8188790
0x818f479
0x8192cbd
0x818f29a
0x815f753
0x81aff65
0x81aee4f
0x8164f0b
0x8166dce
0x815e26e
0x815de29
0x815d296
0x64879c
0x49527a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8f83948 = SELECT COUNT(*)
FROM v1
WHERE x = ?
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
051014 16:12:10  mysqld restarted

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/internals/31782

The fix has been pushed into 5.0.16 tree.
Note for the docs team:
The bug was that a prepared statement that uses merge VIEW(*) could crash on second execution. The fix removed this effect.

(*) - a view that is processed with merge algorithm.

Noted in 5.0.16 changelog.

v5.0.16 Selecting from the view (see below) causes server to crash on 2nd, 4th, 6th, etc executions. 

View created from the following sql
SELECT ztblProductFamilyClass.ProductFamilyClassID, ztblProductFamily.ProductFamilyID, tblProduct.ProductID
FROM ztblProductFamilyClass INNER JOIN ((ztblProductFamily INNER JOIN ((ztblProduct INNER JOIN tblProduct ON ztblProduct.ProductID = tblProduct.ProductID) LEFT JOIN ztblProductInStock ON tblProduct.ProductID = ztblProductInStock.ProductID) ON ztblProductFamily.ProductFamilyID = ztblProduct.ProductFamilyID) INNER JOIN ztblProductFamilyToClass ON ztblProductFamily.ProductFamilyID = ztblProductFamilyToClass.ProductFamilyID) ON ztblProductFamilyClass.ProductFamilyClassID = ztblProductFamilyToClass.ProdFamilyClassID
WHERE (((ztblProductFamilyClass.QCPass)<>0) AND ((ztblProductFamilyClass.ChainForSale)<>0) AND ((ztblProductFamily.QCPass)<>0) AND ((tblProduct.IsDropShipped)<>0)) OR (((ztblProductFamilyClass.QCPass)<>0) AND ((ztblProductFamilyClass.ChainForSale)<>0) AND ((ztblProductFamily.QCPass)<>0) AND ((tblProduct.IsDropShipped)=0) 
AND ((If(Not IsNull(ztblProductInStock.ProductID),ztblProductInStock.QInStock-ztblProductInStock.QAllocated,Null)) Is Not Null 
And  (If(Not IsNull(ztblProductInStock.ProductID),ztblProductInStock.QInStock-ztblProductInStock.QAllocated,Null))>0))
ORDER BY ztblProductFamilyClass.ProductFamilyClassID, ztblProductFamily.ProductFamilyID, tblProduct.ProductID;