MySQL Bugs: #70870: Complex subqueries not handled in FROM clause

Bug #70870	Complex subqueries not handled in FROM clause
Submitted:	10 Nov 2013 20:33	Modified:	12 Nov 2013 15:51
Reporter:	John Viescas	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any
Tags:	FROM Subquery JOIN

Description:
I was pleased to find "subqueries" supported in FROM clauses, but MySQL 5.6 doesn't like this:

SELECT     BowlerTbird.BowlerFullName
FROM         (SELECT DISTINCT Bowlers.BowlerID, (Bowlers.BowlerLastName + ', ' + Bowlers.BowlerFirstName) AS BowlerFullName
                       FROM          ((Bowlers INNER JOIN
                                              Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) INNER JOIN
                                              Tourney_Matches ON Tourney_Matches.MatchID = Bowler_Scores.MatchID) INNER JOIN
                                              Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID
                       WHERE      Tournaments.TourneyLocation = 'Thunderbird Lanes' AND Bowler_Scores.RawScore >= 170) BowlerTbird INNER JOIN
                          (SELECT DISTINCT Bowlers.BowlerID, (Bowlers.BowlerLastName + ', ' + Bowlers.BowlerFirstName) AS BowlerFullName
                            FROM          ((Bowlers INNER JOIN
                                                   Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) INNER JOIN
                                                   Tourney_Matches ON Tourney_Matches.MatchID = Bowler_Scores.MatchID) INNER JOIN
                                                   Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID
                            WHERE      Tournaments.TourneyLocation = 'Bolero Lanes' AND Bowler_Scores.RawScore >= 170) BowlerBolero ON 
                      BowlerTbird.BowlerID = BowlerBolero.BowlerID;

Apparently, it will now accept a simply SELECT .. FROM (single table), but it can't handle a JOIN inside the subquery.  Needs work.

How to repeat:
Structure is:

CREATE DATABASE BowlingLeagueExample;

use BowlingLeagueExample;

CREATE TABLE Bowler_Scores (
	MatchID int NOT NULL ,
	GameNumber smallint NOT NULL ,
	BowlerID int NOT NULL ,
	RawScore smallint NULL ,
	HandiCapScore smallint NULL ,
	WonGame bit NOT NULL 
) ;

CREATE TABLE Bowlers (
	BowlerID int NOT NULL ,
	BowlerLastName nvarchar (50) NULL ,
	BowlerFirstName nvarchar (50) NULL ,
	BowlerMiddleInit nvarchar (1) NULL ,
	BowlerAddress nvarchar (50) NULL ,
	BowlerCity nvarchar (50) NULL ,
	BowlerState nvarchar (2) NULL ,
	BowlerZip nvarchar (10) NULL ,
	BowlerPhoneNumber nvarchar (14) NULL ,
	TeamID int NULL 
) ;

CREATE TABLE Match_Games (
	MatchID int NOT NULL ,
	GameNumber smallint NOT NULL ,
	WinningTeamID int NULL 
) ;

CREATE TABLE Teams (
	TeamID int NOT NULL ,
	TeamName nvarchar (50) NOT NULL ,
	CaptainID int NULL 
) ;

CREATE TABLE Tournaments (
	TourneyID int NOT NULL ,
	TourneyDate datetime NULL ,
	TourneyLocation nvarchar (50) NULL 
) ;

CREATE TABLE Tourney_Matches (
	MatchID int NOT NULL ,
	TourneyID int NULL ,
	Lanes nvarchar (5) NULL ,
	OddLaneTeamID int NULL ,
	EvenLaneTeamID int NULL 
) ;

Attempt to create a View using the above SQL.

Suggested fix:
Either fix this or improve your documentation about subqueries in FROM clauses.

Hello John,

Can you please provide additional information to help us reproduce the problem reported?  Using your table definitions and query (no data) on MySQL 5.6.14 produces no errors, only the expected "Empty set" response.  Do you see an error of any kind?  Or is the query simply not returning the results you expect, given your data set?  If the latter, can you please provide a subset of your data which demonstrates the behavior?

Thanks for your bug report.

Using MySQL Workbench v6, I entered this in a new query window:

Use BowlingLeagueExample;

SELECT     BowlerTbird.BowlerFullName
FROM         (SELECT DISTINCT Bowlers.BowlerID, Concat(Bowlers.BowlerLastName, ', ', Bowlers.BowlerFirstName) AS BowlerFullName
                       FROM          ((Bowlers INNER JOIN
                                              Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) INNER JOIN
                                              Tourney_Matches ON Tourney_Matches.MatchID = Bowler_Scores.MatchID) INNER JOIN
                                              Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID
                       WHERE      Tournaments.TourneyLocation =
'Thunderbird Lanes' AND Bowler_Scores.RawScore >= 170) BowlerTbird INNER JOIN
                          (SELECT DISTINCT Bowlers.BowlerID, Concat(Bowlers.BowlerLastName, ', ',Bowlers.BowlerFirstName) AS BowlerFullName
                            FROM          ((Bowlers INNER JOIN
                                                   Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) INNER JOIN
                                                   Tourney_Matches ON Tourney_Matches.MatchID = Bowler_Scores.MatchID) INNER JOIN
                                                   Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID
                            WHERE      Tournaments.TourneyLocation =
'Bolero Lanes' AND Bowler_Scores.RawScore >= 170) BowlerBolero ON 
                      BowlerTbird.BowlerID = BowlerBolero.BowlerID;

It worked and returned the expected 11 rows.

What I failed to mention in my original post is this fails when I do a CREATE VIEW xxx AS followed by the SELECT statement.  The error I get is:

17:43:50	CREATE View ch08_Good_Bowlers_Two_Alleys AS SELECT     BowlerTbird.BowlerFullName FROM         (SELECT DISTINCT Bowlers.BowlerID, Concat(Bowlers.BowlerLastName, ', ', Bowlers.BowlerFirstName) AS BowlerFullName                        FROM          ((Bowlers INNER JOIN                                               Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) INNER JOIN                                               Tourney_Matches ON Tourney_Matches.MatchID = Bowler_Scores.MatchID) INNER JOIN                                               Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID                        WHERE      Tournaments.TourneyLocation = 'Thunderbird Lanes' AND Bowler_Scores.RawScore >= 170) BowlerTbird INNER JOIN                           (SELECT DISTINCT Bowlers.BowlerID, Concat(Bowlers.BowlerLastName, ', ',Bowlers.BowlerFirstName) AS BowlerFullName                             FROM          ((Bowlers INNER JOIN                                                    Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) INNER JOIN                                                    Tourney_Matches ON Tourney_Matches.MatchID = Bowler_Scores.MatchID) INNER JOIN                                                    Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID                             WHERE      Tournaments.TourneyLocation = 'Bolero Lanes' AND Bowler_Scores.RawScore >= 170) BowlerBolero ON                        BowlerTbird.BowlerID = BowlerBolero.BowlerID	

Error Code: 1349. View's SELECT contains a subquery in the FROM clause	0.000 sec

So, it does run as a straight query, but I can't create a VIEW on it.

Here's another one from the same database that works as a simple SELECT but not as a CREATE VIEW:

CREATE VIEW All_Bowlers_And_Scores_Over_180
AS
SELECT   Concat(Bowlers.BowlerLastName, ', ', Bowlers.BowlerFirstName) AS BowlerName, TI.TourneyDate, TI.TourneyLocation, 
                      TI.MatchID, TI.RawScore
FROM         Bowlers LEFT OUTER JOIN
                          (SELECT     Tournaments.TourneyDate, Tournaments.TourneyLocation, Bowler_Scores.MatchID, Bowler_Scores.BowlerID, 
                                                   Bowler_Scores.RawScore
                            FROM          (Bowler_Scores INNER JOIN
                                                   Tourney_Matches ON Bowler_Scores.MatchID = Tourney_Matches.MatchID) INNER JOIN
                                                   Tournaments ON Tournaments.TourneyID = Tourney_Matches.TourneyID
                            WHERE      Bowler_Scores.RawScore > 180) TI ON Bowlers.BowlerID = TI.BowlerID
ORDER BY BowlerName;

Hi John,

Thanks for the further information on your problem.  Your observation is correct:  VIEWs disallow subqueries in the FROM clause of statements:

"Subqueries cannot be used in the FROM clause of a view."

http://dev.mysql.com/doc/refman/5.6/en/view-restrictions.html

There is a long-standing feature request to remove this restriction:

http://bugs.mysql.com/bug.php?id=12755

Thanks for the clarification.  It's silly that you can run a query with FROM clause subqueries but not store the query as a View.  I'm in the process of updating a book that includes MySQL examples, and I would rather distribute the examples as Views.

Hi John,

I wish I could explain the reason this restriction was first put in, but I can't.  As noted in the referenced feature request, others have disabled this restriction without noticeable negative impacts, but we'll need to do some pretty extensive testing to make sure the restriction is safe to remove.