Lev Lvovsky wrote:
I'm running into a difficult to reproduce problem with a view which is
similar to the following:
----
CREATE TABLE Common (
COMMON_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
COMMON_NAME VARCHAR(50),
UNIQUE(COMMON_NAME)
) ENGINE = InnoDB;
CREATE TABLE Parent (
PARENT_ID VARCHAR(50) NOT NULL,
PARENT_NAME VARCHAR(50) NOT NULL,
PARENT_COMMON_ID INT UNSIGNED,
PRIMARY KEY (PARENT_ID, PARENT_COMMON_ID),
FOREIGN KEY (PARENT_COMMON_ID) REFERENCES Common(COMMON_ID)
) ENGINE = InnoDB;
CREATE TABLE Child (
CHILD_ID BINARY(20) NOT NULL PRIMARY KEY,
PARENT_ID VARCHAR(50) NOT NULL,
CHILD_NAME VARCHAR(50),
CHILD_COMMON_ID INT UNSIGNED,
FOREIGN KEY (PARENT_ID) REFERENCES Parent(PARENT_ID),
FOREIGN KEY (CHILD_COMMON_ID) REFERENCES Common(COMMON_ID)
) ENGINE = InnoDB;
DROP VIEW IF EXISTS BrokenView;
CREATE VIEW BrokenView AS
SELECT Child.*
FROM Child
LEFT JOIN Parent USING(PARENT_ID)
WHERE Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID;
DROP VIEW IF EXISTS WorkingView;
CREATE VIEW WorkingView AS
SELECT Child.*,
Parent.PARENT_NAME,
Parent.PARENT_COMMON_ID
FROM Child
LEFT JOIN Parent ON (Child.PARENT_ID = Parent.PARENT_ID AND
Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID);
----
Though the example cited above does not cause the problems that I'm
running into, the table structure is similar. Specifically the fact
that I'm doing a "WHERE ..." in the BrokenView vs WorkingView is
seemingly the difference between getting rows returned and not.
And this is exactly the cause of your problems. When you build a query
that optionally includes a table, you get your results back in stages.
The one stage of the query takes your LEFT JOIN and builds a list of
matching rows. A later stage evaluates the terms in your WHERE clause.
In this case in order to evaluate the WHERE clause, you force the
existence of the rows from the `Parent` table just as if you had written
an INNER JOIN. If there were no rows (no values) from the `Parent`
table then the WHERE clause will evaluate as FALSE and those rows will
not be returned. By putting both terms into the ON clause of your LEFT
JOIN, you make it possible to have non-matched rows in your final result.
--
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/
Join the Quality Contribution Program Today!
http://dev.mysql.com/qualitycontribution.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
