Shawn,
On Feb 13, 2008, at 9:12 AM, Shawn Green wrote:
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.
Below is SQL which demonstrates the bug. Apparently this is an issue
with the optimizer, and the temporary fix is the "ALGORITHM=TEMPTABLE"
as I'd mentioned in my previous email. If this is not a bug, I would
think that the behavior would be consistent before and after a 'flush
table ...' command.
-lev
----
DROP DATABASE TestDB;
CREATE DATABASE TestDB;
USE TestDB;
CREATE TABLE Common (
COMMON_ID INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE Parent1 (
PARENT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
EXT_PARENT_ID INT UNSIGNED NOT NULL,
PARENT_NAME INT UNSIGNED NOT NULL,
COMMON_ID INT UNSIGNED NOT NULL,
FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE Child1 (
CHILD_ID INT UNSIGNED NOT NULL PRIMARY KEY,
PARENT_ID INT UNSIGNED NOT NULL,
COMMON_ID INT UNSIGNED NOT NULL,
FOREIGN KEY (PARENT_ID) REFERENCES Parent1(PARENT_ID) ON DELETE CASCADE,
FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE Parent2 (
PARENT_ID INT UNSIGNED NOT NULL,
PARENT_NAME INT UNSIGNED NOT NULL,
COMMON_ID INT UNSIGNED NOT NULL,
PRIMARY KEY (PARENT_ID, COMMON_ID),
FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE Child2 (
CHILD_ID INT UNSIGNED NOT NULL PRIMARY KEY,
PARENT_ID INT UNSIGNED NOT NULL,
COMMON_ID INT UNSIGNED NOT NULL,
FOREIGN KEY (PARENT_ID, COMMON_ID) REFERENCES Parent2(PARENT_ID,
COMMON_ID) ON DELETE CASCADE,
FOREIGN KEY (COMMON_ID) REFERENCES Common(COMMON_ID) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE VIEW ViewParent2 AS
SELECT Parent2.* FROM Parent2;
CREATE VIEW ViewChild2 AS
SELECT Child2.*
FROM Child2
LEFT JOIN ViewParent2 USING(PARENT_ID)
WHERE Child2.COMMON_ID = ViewParent2.COMMON_ID;
INSERT INTO Common
SET COMMON_ID = 1;
INSERT INTO Parent1
SET COMMON_ID = 1,
EXT_PARENT_ID = 1,
PARENT_NAME = 1;
SELECT LAST_INSERT_ID() INTO @_parent_id;
INSERT INTO Child1
SET COMMON_ID = 1,
PARENT_ID = @_parent_id,
CHILD_ID = 1;
SELECT
1
FROM Child2
LEFT JOIN Child1 ON Child1.CHILD_ID = Child2.CHILD_ID AND
Child1.COMMON_ID = Child2.COMMON_ID
LEFT JOIN Parent2 ON Parent2.PARENT_ID = Child2.PARENT_ID AND
Parent2.COMMON_ID = Child2.COMMON_ID
WHERE
( Child1.CHILD_ID IS NOT NULL ) AND ( Child2.CHILD_ID = 1 );
INSERT INTO Parent2
SET
PARENT_ID = 1,
COMMON_ID = 1,
PARENT_NAME = 1;
INSERT INTO Child2
SET
CHILD_ID = 1,
PARENT_ID = 1,
COMMON_ID = 1;
-- Technically Child2, and ViewChild2 have the same data in them.
However in selecting from ViewChild2
-- on the pkey returns 0 rows, whereas the base table returns the
correct single row that exists which
-- matches the WHERE clause.
-- correct
SELECT IF ( count(*), 'Yes', 'No') AS JOIN_VIEW_WORKS_BEFORE_FLUSH
FROM Child2 WHERE CHILD_ID=1;
-- incorrect
SELECT IF ( count(*), 'Yes', 'No') AS WHERE_VIEW_WORKS_BEFORE_FLUSH
FROM ViewChild2 WHERE CHILD_ID=1;
----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
