Bug #67014 View evaluation is incorrect when joining to view that selects a literal
Submitted: 28 Sep 2012 22:10 Modified: 13 Jul 2015 16:51
Reporter: Luke Stevens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.27, 5.5.67, 5.0.97, 5.1.67, 5.7.1 OS:Microsoft Windows (7, 64-bit)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[28 Sep 2012 22:10] Luke Stevens
Description:
I have a SELECT via a view that is getting incorrect results. It seems to be when using a LEFT JOIN to a view whose SELECT includes a literal expression; the ON clause seems to be ignored, and the literal expression appears in all results. 

How to repeat:
CREATE TABLE TableA (
  Id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB;

CREATE TABLE TableB (
  Id int NOT NULL,
  IsCool tinyint,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB;

CREATE VIEW ViewB AS 
SELECT TableB.Id, 1 AS IsCool FROM TableB WHERE TableB.IsCool;

INSERT INTO TableA (Id) VALUES (1);
INSERT INTO TableA (Id) VALUES (2);
INSERT INTO TableB (Id, IsCool) VALUES (1, 1);

# This is OK
SELECT TableA.Id, ExprB.IsCool
FROM TableA LEFT JOIN (SELECT TableB.Id, 1 AS IsCool FROM TableB WHERE TableB.IsCool) AS ExprB ON ExprB.Id = TableA.Id;

# This is wrong
SELECT TableA.Id, ViewB.IsCool
FROM TableA LEFT JOIN ViewB ON ViewB.Id = TableA.Id;

Expected result (from first select, not using the view):
1, 1
2, NULL

Actual result (from last select, via the view);
1, 1
2, 1

Suggested fix:
Workaround:

In this example, the view SQL can be inlined as shown in the first case, or the select can be modified to read from a table column (SELECT TableB.Id, TableB.IsCool...)
[6 Dec 2012 19:55] Sveta Smirnova
Thank you for the report.

Verified as described. Table engine does not matter.
[13 Jul 2015 16:51] Paul Dubois
Noted in 5.7.8, 5.8.0 changelogs.

Incorrect results could be produced tor views and derived tables on
the inner side of an outer join and from which non-nullable
expressions such as literals were selected.