MySQL Bugs: #7413: VIEW with sub query can cause the MySQL server to crash

Bug #7413	VIEW with sub query can cause the MySQL server to crash
Submitted:	18 Dec 2004 20:17	Modified:	20 Dec 2004 7:14
Reporter:	Rob Booker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2 alpha	OS:	Windows (XP Pro SP2)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
A View that contains a subquery that returns multiple rows causes the MySQL server to crash

 

How to repeat:
OK, I'll demo this using the mysql.user table. All this is run from the command prompt on a fresh and vanilla installation of v5.0.2. 

When I do a 'SELECT user, host FROM mysql.user' I get:-

user    host
-----------
root  | %
root  | localhost

So there's 2 rows for user 'root'.  If I run the following SQL:-

SELECT (SELECT a.user FROM mysql.user a WHERE a.user = b.user) FROM mysql.user b

then an error is thrown:-

ERROR 1242 (21000): Subquery returns more than 1 row.

So far so good.  Now turn this into a View:-

CREATE VIEW test.myview AS SELECT (SELECT a.user FROM mysql.user a WHERE a.user = b.user) FROM mysql.user b

Now do a select on the view:-

SELECT* FROM test.myview

At this point the server crashes and the NT service ends.  If I put a DISTINCT in the subquery then the View executes ok, eg:-

CREATE VIEW test.myview AS SELECT (SELECT DISTINCT a.user FROM mysql.user a WHERE a.user = b.user) FROM mysql.user b

Thanks

verified on linux

Hi, Rob!

It turns out that I have discovered and fixed this bug around the same time as you reported it. Patch fixing this bug was pushed in source repository as :

ChangeSet
  1.1755 04/12/19 12:51:40 dlenev@brandersnatch.localdomain +1 -0
  Small cleanup of derived tables handling.

So fix will be included in the next 5.0 release.

BTW thank you for your nice test-case which gave me idea how I can cover my bugfix in our test-suite!!!