mysql> select version(); +------------------------+ | version() | +------------------------+ | 5.0.54a-enterprise-gpl | +------------------------+ 1 row in set (0.00 sec) mysql> DROP VIEW IF EXISTS view_parent; Query OK, 0 rows affected (0.00 sec) mysql> DROP VIEW IF EXISTS view_child2; Query OK, 0 rows affected (0.00 sec) mysql> DROP VIEW IF EXISTS view_child2b; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS child1; Query OK, 0 rows affected (0.04 sec) mysql> DROP TABLE IF EXISTS child2; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS parent; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS common; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE common ( -> common_id INT UNSIGNED NOT NULL PRIMARY KEY -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE parent ( -> parent_id INT UNSIGNED NOT NULL, -> common_id INT UNSIGNED NOT NULL, -> PRIMARY KEY (parent_id, common_id), -> FOREIGN KEY (common_id) REFERENCES common(common_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> mysql> 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 (common_id) REFERENCES common(common_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> 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 (common_id) REFERENCES common(common_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE VIEW view_parent AS -> SELECT parent.* FROM parent; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE ALGORITHM=MERGE VIEW view_child2 AS -> SELECT child2.* -> FROM child2 -> LEFT JOIN view_parent USING(parent_id) -> WHERE child2.common_id = view_parent.common_id; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE ALGORITHM=TEMPTABLE VIEW view_child2b AS -> SELECT child2.* -> FROM child2 -> LEFT JOIN view_parent USING(parent_id) -> WHERE child2.common_id = view_parent.common_id; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO common SET common_id = 1; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO child1 SET common_id = 1, parent_id = 1, child_id = 1; Query OK, 1 row affected (0.00 sec) -- For some reason, this EXPLAIN must be executed *here* for the problem to occur?! mysql> mysql> explain extended SELECT -> 1 -> FROM child2 -> LEFT JOIN child1 ON child1.child_id = child2.child_id AND child1.common_id = child2.common_id -> LEFT JOIN parent ON parent.parent_id = child2.parent_id AND parent.common_id = child2.common_id -> WHERE -> ( child1.child_id IS NOT NULL ) AND ( child2.child_id = 1 )\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE noticed after reading const tables 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select 1 AS `1` from `test`.`child2` join `test`.`child1` left join `test`.`parent` on((multiple equal(`test`.`parent`.`parent_id`, '0') and multiple equal('1', `test`.`parent`.`common_id`))) where 0 1 row in set (0.00 sec) mysql> mysql> INSERT INTO parent SET parent_id = 1, common_id = 1; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO child2 SET child_id = 1, parent_id = 1, common_id = 1; Query OK, 1 row affected (0.00 sec) mysql> mysql> explain extended SELECT count(*) FROM child2 WHERE child_id=1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: child2 type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `test`.`child2` where 1 1 row in set (0.00 sec) -- Direct select from the table returns one record, as expected. mysql> SELECT count(*) FROM child2 WHERE child_id=1; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> mysql> explain extended SELECT count(*) FROM view_child2b WHERE child_id=1\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: *************************** 2. row *************************** id: 2 select_type: DERIVED table: child2 type: ALL possible_keys: common_id key: NULL key_len: NULL ref: NULL rows: 1 Extra: *************************** 3. row *************************** id: 2 select_type: DERIVED table: parent type: eq_ref possible_keys: PRIMARY,common_id key: PRIMARY key_len: 8 ref: test.child2.parent_id,test.child2.common_id rows: 1 Extra: Using where; Using index 3 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `test`.`view_child2b` where 1 1 row in set (0.00 sec) -- Select from the TEMPTABLE view returns one record, as expected. mysql> SELECT count(*) FROM view_child2b WHERE child_id=1; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> mysql> explain extended SELECT count(*) FROM view_child2 WHERE child_id=1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE noticed after reading const tables 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `test`.`child2` left join (`test`.`parent`) on((`test`.`parent`.`parent_id` = '1')) where ((`test`.`parent`.`common_id` = '1')) 1 row in set (0.00 sec) -- Select from the MERGE view returns no records? Um.. mysql> SELECT count(*) FROM view_child2 WHERE child_id=1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> mysql> explain extended SELECT count(*) FROM view_child2 WHERE child_id=1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: child2 type: const possible_keys: PRIMARY,common_id key: PRIMARY key_len: 4 ref: const rows: 1 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: parent type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using where; Using index 2 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `test`.`child2` left join (`test`.`parent`) on((`test`.`parent`.`parent_id` = '1')) where ((`test`.`parent`.`common_id` = '1')) 1 row in set (0.00 sec) -- After FLUSH TABLES, the MERGE view then returns one record as expected. mysql> SELECT count(*) FROM view_child2 WHERE child_id=1; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)