Problem combining inner and right joins

[Sameh El-Ansary]

Hi there,

For bother mysql 4.1.7 and 4.0.18 on windows.
I had the following behavior when combining inner joins and right joins: 

 -- (1) I create a table A and insert some values in it
CREATE TABLE A (
  Id smallint,
  Name varchar(20)
) ;

INSERT INTO A VALUES (1,'Hello');
INSERT INTO A VALUES (2,'Good bye');
INSERT INTO A VALUES (3,'Foo');
INSERT INTO A VALUES (4,'Good Morning');

-- (2)  I create another table B
CREATE TABLE B ( 
  ID smallint 
) ;

-- (3) Naturally, a right join succeeds returning all 4 records in A
SELECT A.Id,A.Name 
FROM B RIGHT JOIN A ON A.Id=B.ID;

-- (4) I create another table C (Note that both B and C are empty)
CREATE TABLE C (
 ID smallint 
 ) ;

-- (5) I inner join B and C and then right join the result with A
SELECT A.Id,A.Name 
FROM   (B INNER JOIN C ON C.Id=B.Id)
       RIGHT JOIN A ON A.Id=B.Id;
--     PROBLEM: the result is an empty set though I should get
--     the 4 records in A


-- (6) Yet more weird is that I insert a value in B which
--     should not change the result of inner joining B and C
--     but....
INSERT INTO B VALUES (17);
SELECT A.Id,A.Name 
FROM   B INNER JOIN C ON C.Id=B.Id
       RIGHT JOIN A ON A.Id=B.Id;
--     PROBLEM: the result is the 4 records !!

Thanks for help
Sameh