I think this is a bug..

Bottom line is that I've a view called view_2.

Select * from view_2 gives me 6 records.
Select * from view_2 where TheString is not null gives me 2 records
select * from view_2 where TheString is null gives me _6_ records.

It's the last one that I believe is wrong.

(I can duplicate the structure in Access, say, and get the results I
expect i.e. 4 records for the final select.)

Database structure below:


SET NAMES UNICODE_FSS;

CREATE TABLE TABLE_1 ( MAINID INTEGER NOT NULL );

CREATE TABLE TABLE_2 (
SUB_ID INTEGER NOT NULL,
MAIN_ID INTEGER,
THESTRING VARCHAR(50)
);

CREATE TABLE TABLE_3 ( SUBID INTEGER );

CREATE VIEW VIEW_1(
MAIN_ID,
THESTRING)
AS
select Table_2.main_id, Table_2.thestring
from
Table_2 inner join Table_3 on
Table_2.sub_id = Table_3.subid
;

CREATE VIEW VIEW_2(
MAINID,
THESTRING)
AS
select Table_1.mainid, View_1.thestring
from Table_1 left join View_1 on
Table_1.mainid = View_1.main_id
;


INSERT INTO TABLE_1 (MAINID) VALUES (1);
INSERT INTO TABLE_1 (MAINID) VALUES (2);
INSERT INTO TABLE_1 (MAINID) VALUES (3);
INSERT INTO TABLE_1 (MAINID) VALUES (4);
INSERT INTO TABLE_1 (MAINID) VALUES (5);
INSERT INTO TABLE_1 (MAINID) VALUES (6);

COMMIT WORK;

INSERT INTO TABLE_2 (SUB_ID, MAIN_ID, THESTRING) VALUES (1, 2, 'A
string value');
INSERT INTO TABLE_2 (SUB_ID, MAIN_ID, THESTRING) VALUES (2,
2, 'Another string');
INSERT INTO TABLE_2 (SUB_ID, MAIN_ID, THESTRING) VALUES (3,
4, 'Something else');

COMMIT WORK;

INSERT INTO TABLE_3 (SUBID) VALUES (2);
INSERT INTO TABLE_3 (SUBID) VALUES (3);

COMMIT WORK;



/********************************************************************
**********/
/**** Primary
Keys ****/
/********************************************************************
**********/

ALTER TABLE TABLE_1 ADD CONSTRAINT PK_TABLE_1 PRIMARY KEY (MAINID);
ALTER TABLE TABLE_2 ADD CONSTRAINT PK_TABLE_2 PRIMARY KEY (SUB_ID);


/********************************************************************
**********/
/**** Foreign
Keys ****/
/********************************************************************
**********/

ALTER TABLE TABLE_2 ADD CONSTRAINT FK_TABLE_2_1 FOREIGN KEY
(MAIN_ID) REFERENCES TABLE_1 (MAINID);
ALTER TABLE TABLE_3 ADD CONSTRAINT FK_TABLE_3_1 FOREIGN KEY (SUBID)
REFERENCES TABLE_2 (SUB_ID);