Where clause doesn't work! Is this a bug?

[Han Joosten]

I am working on an application that programmatically writes queries for mysql. Now I am in the situation that I think is a bug in MySQL. Consider the following:


CREATE TABLE "ONE"
( "ONE" VARCHAR(255) UNIQUE NOT NULL
, PRIMARY KEY ("ONE")
, "ts_insertupdate" TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_BIN
, ROW_FORMAT = DYNAMIC
;

CREATE TABLE "Position"
( "Position" VARCHAR(255) UNIQUE NOT NULL
, PRIMARY KEY ("Position")
, "ts_insertupdate" TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_BIN
, ROW_FORMAT = DYNAMIC
;
INSERT INTO "ONE"
("ONE")
VALUES
(1)
;
INSERT INTO "Position"
("Position")
VALUES
('a')
, ('b')
;
select *
from (select "Position" as src1, "Position" as tgt1
from "Position"
where "Position" is not null
)as t1
left join
(select 'a' as src2, 'a' as tgt2
from "ONE"
)
as t2
on (t1.src1 = t2.src2)
and (t1.tgt1 = t2.tgt2)

where src2 is not null

;


The result of the last query I get two rows, where one of them src2 is null!

I think this is a bug. Why does the where clause not prevent the null value to show?

Any help is very much appreciated!

Han Joosten.