MySQL Forums
Forum List  »  Quality Assurance

Where clause doesn't work! Is this a bug?
Posted by: Han Joosten
Date: January 21, 2018 05:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Where clause doesn't work! Is this a bug?
1477
January 21, 2018 05:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.