MySQL Bugs: #119454: Hash join has wrong result when the join equality involves FLOAT or DECIMAL column

Bug #119454	Hash join has wrong result when the join equality involves FLOAT or DECIMAL column
Submitted:	24 Nov 16:04	Modified:	27 Nov 5:11
Reporter:	Guilhem Bichot	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The query in how-to-repeat returns

SELECT  t0.c2,t2.c5 FROM t0 join t2 ON t2.c5 = t0.c2;
+--------+------+
| c2     | c5   |
+--------+------+
| 0.9999 |   51 |
+--------+------+

and this does not make sense: 0.9999 is really not equal to 51: the result should be "no rows".

There is also a warning:
| Warning | 1264 | Out of range value for column '(null)' at row 1 |

Note that if t0.c2 is declared as plain FLOAT (instead of FLOAT(4,4)) there is no problem.
Note also that if hash join is turned off with a hint, there is no problem.

How to repeat:
CREATE TABLE t0 (
                        c2 float(4,4) NOT NULL,
                        c5 tinyint(1) DEFAULT NULL
                        ) ;
CREATE TABLE t2 (
                        c2 float(4,4) NOT NULL ,
                        c5 tinyint(1) DEFAULT NULL);
insert into t2(c5,c2) values (51, 0.9999);
insert into t0(c5,c2) values (51, 0.9999);
SELECT  t0.c2,t2.c5 FROM t0 join t2 ON t2.c5 = t0.c2;

Suggested fix:
to me the error is in Item_eq_base::create_cast_if_needed called by hash join. The chosen comparison type there is DECIMAL(4,4); and if one cast 51 to such type, the result is 0.9999. However it is an error to choose such type, MySQL's rules say that the chosen type should be DOUBLE. If hash join is turned off, the comparison is done as DOUBLE properly.
Item_eq_base::create_cast_if_needed makes a logic error by thinking it can just take the max of precisions and the max of scales. But it forgets that the scale "eats digits" from precision. Also for floating point types, scale and precision are not reliable; for example, a FLOAT(61,16) actually a Item::decimal_precision() of 22 (which confuses Item_eq_base::create_cast_if_needed too).

I am editing the title. Indeed, while the original testcase is about TINYINT=FLOAT(P,S), I have a second testcase with DECIMAL=DECIMAL:

CREATE TABLE t0 (
                        c2 decimal(4,4)
                        ) ;
CREATE TABLE t2 (
                        c5 decimal(4,0) DEFAULT NULL);
insert into t0 values (0.9999);
insert into t2 values (51);
SELECT  t0.c2,t2.c5 FROM t0 join t2 ON t2.c5 = t0.c2;

the SELECT returns
--------+------+
| c2     | c5   |
+--------+------+
| 0.9999 |   51 |
+--------+------+
which is incorrect.

Again the problem is that create_cast_if_needed() chooses type decimal(4,4) for casting.

Summing up, problem happens with:
- hash join
- and either FLOAT(P,S)=other_numeric_column, or DECIMAL=DECIMAL with carefully chosen precision and scale.

Hi Guilhem,

Thank you for the test case. Verified as described.