MySQL Bugs: #71434: DECIMAL `0.1` evaluates to TRUE in project list, FALSE in where/having clauses

Bug #71434	DECIMAL `0.1` evaluates to TRUE in project list, FALSE in where/having clauses
Submitted:	20 Jan 2014 21:24	Modified:	12 Nov 2019 23:00
Reporter:	Arthur O'Dwyer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.31, 5.5.35, 5.6.15, 5.1.73	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The implicit conversion from DECIMAL to BOOLEAN implied by the "AND", "OR", and "NOT" operators (and "IS TRUE" and "IS FALSE" and "IS NULL") give correct results when in the projection list (SELECT ...), but they give wrong and inconsistent results when in a WHERE... or HAVING... filter.

SELECT * FROM t WHERE 0.1;  -- bad behavior
SELECT * FROM t WHERE 0.1 IS TRUE;  -- correct behavior

How to repeat:
CREATE TABLE t (d DECIMAL(10,4));
INSERT INTO t VALUES (0.1), (0.9);

SELECT d, NOT d, NOT NOT d FROM t WHERE d;  -- matches only 0.9
SELECT d, NOT d, NOT NOT d FROM t WHERE NOT d;  -- Empty set
SELECT d, NOT d, NOT NOT d FROM t WHERE NOT NOT d;  -- matches only 0.9
SELECT d, NOT d, NOT NOT d FROM t WHERE NOT NOT NOT d;  -- Empty set

SELECT d, (d AND d) FROM t WHERE d=0.1 AND (d AND d); -- matches 0.1
SELECT d, (d AND d) FROM t WHERE d=0.1 AND (0.1 AND 0.1); -- Empty set

Notice in the first bunch of queries that the row "0.1" never matches any number of NOTs; i.e., it is neither true nor false. Notice that substituting the value "0.1" for "d" in the final query changes the query's behavior, even though "0.1" and "d" are exactly equivalent (same type, same value).

The same bug also applies to FLOAT and DOUBLE columns. Consider:

CREATE TABLE foo (id <<<MYTYPE>>>);
INSERT INTO foo VALUES (0.1);
SELECT * FROM foo `a` JOIN foo `b`;  -- yields one row
SELECT * FROM foo `a` JOIN foo `b` WHERE a.id AND a.id;  -- yields one row
SELECT * FROM foo `a` JOIN foo `b` WHERE a.id AND b.id;  -- yields empty set

This gives inconsistent results whether <<<MYTYPE>>> is FLOAT, DOUBLE, or DECIMAL(2,1).

Hello Arthur,

Thank you for the report.
Verified as described.

Thanks,
Umesh

Hi Arthur,

there is a good workaround for this problem: Replace the column references with complete logical conditions, like this:

SELECT d, NOT (d<>0), NOT NOT (d<>0) FROM t WHERE d<>0;
SELECT d, NOT (d<>0), NOT NOT (d<>0) FROM t WHERE NOT (d<>0);
SELECT d, NOT (d<>0), NOT NOT (d<>0) FROM t WHERE NOT NOT (d<>0);
SELECT d, NOT (d<>0), NOT NOT (d<>0) FROM t WHERE NOT NOT NOT (d<>0);

This substitution would also be the best internal fix, but unsure if it can be done easily.

Posted by developer:
 
Problem has been fixed in 8.0 by worklog 12358 "Ensure that all predicates in SQL conditions are complete"