Bug #100203 TEXT field with AND NULL wrongly evaluated to FALSE
Submitted: 13 Jul 2020 14:41 Modified: 14 Jul 2020 3:59
Reporter: Yushan ZHANG Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.21, 5.7.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: Logic, null, text

[13 Jul 2020 14:41] Yushan ZHANG
Description:
TEXT field with non-empty and non-null data is evaluated to FLASE when use AND operator.

How to repeat:
drop table if exists t2;
create table t2 (col0 CHAR(74), col1 DOUBLE NOT NULL, col2 TEXT);
insert into t2 values ('385d3104-1a51-4b85-a774-1095bba75a52', '498.40643610657617', 'x');
insert into t2 values ('a4c8b07c-88dc-4a91-b0fb-2dfd2278d862', '-785.8119347627421', 'x');
insert into t2 values ('14648816-5fab-4247-a36b-4cdf983cb147', '-38.19581534807992', 'e');

-- correct
SELECT `col2` <> NULL FROM t2;
-- correct
SELECT NULL != `col2` FROM t2;
-- incorrect
SELECT NULL AND `col2` FROM t2;
-- incorrect
SELECT (NULL != `col2`) && `col2` FROM t2;
-- incorrect
SELECT (NULL != `col2`) AND `col2` FROM t2;
-- following are all incorrect
SELECT `col2` FROM t2 WHERE (( NULL != `col2` ) && `col2`) IS NULL ORDER BY `col2`;
-- it should be null
SELECT `col2` FROM t2 WHERE (( NULL != `col2` ) && `col2`) IS FALSE ORDER BY `col2`;
SELECT `col2` FROM t2 WHERE (( NULL != `col2` ) AND `col2`) IS NULL ORDER BY `col2`;
-- ite should be null
SELECT `col2` FROM t2 WHERE (( NULL != `col2` ) AND `col2`) IS FALSE ORDER BY `col2`;
[14 Jul 2020 3:59] Yushan ZHANG
Realized the string conversion problem, not a bug.
[14 Jul 2020 12:43] MySQL Verification Team
Thank you, Mr. ZHANG.