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.
After my program inserts data into all three tables, I can do a query
on the base tables, however doing a query on the view with a WHERE on
the primary key of the Child table returns no rows. Doing that same
query on the base table returns rows as expected.
Performing a 'flush table Parent' per the example above fixes the
problem. Additionally, using the 'temptable' algorithm works, whereas
explicitly using the 'merge' algorithm, or the 'undefined' default
causes the problem to appear.
All tables are InnoDB, 'select version()' returns '5.0.50-enterprise-
gpl-log'.
Having read the VIEW command documentation, it wouldn't seem that I'm
doing anything against the rules in my query that would cause this.
Any help would be greatly appreciated!
thanks,
-lev
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
