MySQL Bugs: #3984: Subquery w/foreign keys crash server

Bug #3984	Subquery w/foreign keys crash server
Submitted:	3 Jun 2004 16:19	Modified:	24 Jun 2004 20:59
Reporter:	Christopher Jeffus	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.2	OS:	Linux (Red Hat AS 2.1)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
A query containing a subquery crashes the server.  If you remove the foreign key reference, it will run; or, if you remove the subquery, it will run.  Both combinations together are fatal.  The trace file that I produced just ends, not much info.  I am ftp'ing a tar ball with the table data, create scripts, query, my.cnf, trace, resolved stack, and err log.

How to repeat:
Create the 3 tables, load the data, run the query.

Suggested fix:
None

Resolved Stack trace

Attachment: mysqld.stack (text/plain), 1.05 KiB.

Error log

Attachment: hvcwy0918.corp.acxiom.net.err (text/plain), 4.24 KiB.

Table create script

Attachment: create_tables.sql (application/octet-stream, text), 937 bytes.

Problematic query

Attachment: prob_query.sql (application/octet-stream, text), 814 bytes.

My.cnf

Attachment: my.cnf (application/octet-stream, text), 2.54 KiB.

compressed trace file (had to gut it in order to get under the 200K upload limit)

Attachment: mysqld.zip (application/x-zip-compressed, text), 22.23 KiB.

Any suggestions on how to get the data uploaded?  Compressed, it totals about 30 MB.

Hi!

You can upload your tables via ftp to support.mysql.com:/pub/mysql/secret (if I recall the directory correctly).

I wonder if the FOREIGN KEY really has anything to do with this assertion failure, except that the index on the foreign key may change the query execution plan?

The query that causes the assertion failure is a plain SELECT. I do not see how it would be affected by FOREIGN KEYs.

This looks like yet another case where MySQL releases the intention locks on tables too early, while it is still processing the SQL statement. Sanja should look into this.

Regards,

Heikki

select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        PARTSUPP,
        SUPPLIER,
        NATION
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'JORDAN'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.0001000000
                        from
                                PARTSUPP,
                                SUPPLIER,
                                NATION
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and n_name = 'JORDAN');

[mysql@hvcwy0918 mysql]$ InnoDB: Error: MySQL is trying to perform a SELECT
InnoDB: but it has not locked any tables in ::external_lock()!
TRANSACTION 0 16128, not started, process no 4497, OS thread id 36874, thread declared inside InnoDB 500
MySQL thread id 1, query id 4 localhost root Sending data
select         ps_partkey,         sum(ps_supplycost * ps_availqty) as value from         
PARTSUPP,         SUPPLIER,         NATION where        
ps_suppkey = s_suppkey         and s_nationkey = n_nationkey         and n_name = 'JORDAN' group by         
ps_partkey having                 sum(ps_supp

040603 15:36:18InnoDB: Assertion failure in thread 36874 in file row0sel.c line 2779
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

Three files are being uploaded to ftp://support.mysql.com/pub/mysql/secret
nation.tbl.Z
supplier.tbl.Z
partsupp.tbl.Z

This bug can be repeated only after LOAD DATA procedure 
or dump restoring without disable/enable keys. If restart server after data 
loading bug can't be repeated, too

following is more simple test of crashing part of bug: 
 
create table t1 (a int, b int) engine=innodb; 
insert into t1 values (1,2), (1,3), (2,3), (2,4), (2,5), (3,4), (4,5), 
(4,100); 
create table t2 (a int) engine=innodb; 
insert into t2 values (1),(2),(3),(4); 
select a, sum(b) as b from t1 group by a having b > (select max(a) from t2); 
drop table t1, t2; 
 
other part is unused indexes for grouping but only if data just loded (see 
previous note)

ChangeSet 
  1.1911 04/06/09 23:32:20 bell@sanja.is.com.ua +6 -0 
  do not unlock tables early if we have subquery in HAVING clause (BUG#3984)

Thank you for bug report! Patch pushed in our internal source repository and 
will be present in next server release.