MySQL Bugs: #1076: sub select with a join clause crashes mysql

Bug #1076	sub select with a join clause crashes mysql
Submitted:	18 Aug 2003 9:08	Modified:	18 Sep 2003 13:06
Reporter:	laurent chades	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.0	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Let's assume we have 3 tables joined by the 'login' column. Doing the following leads to a crash of mysql :
SELECT * FROM table1 WHERE login NOT IN (
	SELECT login FROM table2 WHERE firstname LIKE 'foo'
UNION
	SELECT login FROM table3 WHERE lastname LIKE 'doe'
);

No log is generated, the only error message comes from windows :
'mysqld-nt.exe has generated errors and will be closed by windows'

How to repeat:
build 3 tables joined by the same key and execute the previous query.

I tried, but was not able to repeat this. Can you try the following and see
if that crashes MySQL for you? If not, can you provide the exact tables
(schema + data) and query that crashes your server?

mysql> create table table1 (login varchar(32) not null primary key);
Query OK, 0 rows affected (0.10 sec)

mysql> create table table2 (login varchar(32) not null primary key,
    -> firstname varchar(64) not null, lastname varchar(64) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> create table table3 (login varchar(32) not null primary key,
    -> firstname varchar(64) not null, lastname varchar(64) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM table1 WHERE login NOT IN (
    ->         SELECT login FROM table2 WHERE firstname LIKE 'foo'
    -> UNION
    ->         SELECT login FROM table3 WHERE lastname LIKE 'doe'
    -> );
Empty set (0.17 sec)

mysql> insert into table1 values ('item');
Query OK, 1 row affected (0.00 sec)

mysql> insert into table1 values ('foobar');
Query OK, 1 row affected (0.13 sec)

mysql> insert into table1 values ('johndoe');
Query OK, 1 row affected (0.00 sec)

mysql> insert into table2 values ('foobar','foo','bar');
Query OK, 1 row affected (0.00 sec)

mysql> insert into table3 values ('johndoe','john','doe');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM table1 WHERE login NOT IN (
    ->         SELECT login FROM table2 WHERE firstname LIKE 'foo'
    -> UNION
    ->         SELECT login FROM table3 WHERE lastname LIKE 'doe'
    -> );
+-------+
| login |
+-------+
| item  |
+-------+
1 row in set (0.04 sec)

The table definition you gave me works with the queries you provided.
Something I forgot when submitting my bug report was that I use InnoDb tables (for transaction management). And I tried the table definition you gave me but I created InnoDB tables instead and the query who works perfectly on MyISAM tables crashes on InnoDB tables. Try it and tell me if you get a mysql crash.

I don't know if it helps but when I use Mysqlfront (the GUI I use to work whith MySQL) the query crashes the mysql server and when I pass the query in my java code I get the following exception (but no server crash) :

java.lang.ArrayIndexOutOfBoundsException: 0
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:1955)
	at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:1120)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:217)
	at com.kweeze.ibisa.user.UserFactory.getUsersNotInGroup(UserFactory.java:808)
	at com.kweeze.ibisa.group.servlets.GroupServlet.doGet(GroupServlet.java:105)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:527)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
	at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2396)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
	at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
	at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
	at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
	at org.apache.ajp.tomcat4.Ajp13Processor.process(Ajp13Processor.java:458)
	at org.apache.ajp.tomcat4.Ajp13Processor.run(Ajp13Processor.java:551)
	at java.lang.Thread.run(Thread.java:536)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".