[MDEV-16873] Compare two FLOAT expressions as floats rather than doubles Created: 2018-08-01  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data types
Fix Version/s: 10.11

Type: Task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-13995 MAX(timestamp) returns a wrong result... Closed
Relates
relates to MDEV-16872 Add CAST(expr AS FLOAT) Closed

 Description   

This script returns empty set:

CREATE OR REPLACE TABLE t1 (f float);
 
INSERT INTO t1 VALUES (0.671437);
 
SELECT * FROM t1 WHERE f = 0.671437;

This happens because:

  • The first decimal value 0.671437 (in INSERT) gets converted to float. Then, the value of the float field 'f' gets converted to double on comparison.
  • The second decimal value 0.671437 (which is on the right side of the comparison) gets converted to double directly, without float
    in the middle.

So the two sides of the comparison represent results of different type conversion sequences:

Left side:  DECIMAL -> FLOAT -> DOUBLE
 
Right side: DECIMAL -> DOUBLE

Note, all three type conversions involved in this script:

DECIMAL -> FLOAT
 
FLOAT   -> DOUBLE
 
DECIMAL -> DOUBLE

are lossy.

We'll fix the float type handler to compare two float values as floats rather than as doubles.

Note, comparison between FLOAT and non-FLOAT will still be performed as DOUBLE.

After this change, it will be possible to rewrite the above script as follows:

CREATE OR REPLACE TABLE t1 (id int,f float);
 
INSERT INTO t1 VALUES (1, 0.671437), (2, -1.5);
 
SELECT id, f FROM t1 WHERE f = CAST(0.671437 AS FLOAT);

so both sides of the comparison will be results of DECIMAL->FLOAT conversion, without any double representation on the way. The script will return the inserted value.

 Comments   
Comment by Alexander Barkov [ 2018-08-01 ]

This task is waiting for MDEV-13995, which adds new methods Field::val_raw) and Item::val_raw()

Comment by Sergei Golubchik [ 2018-08-10 ]

This is questionable. The script does not return the inserted value, because the inserted values was truncated during insertion. Such a truncation at INSERT time can happen for many types, not only for FLOAT. And always when a truncation happens — for any type — the value won't later be found in the table, this is natural and expected.

There's one important difference, though. Normally one gets a warning when INSERT truncates a value. This immediately sets the expectations correctly — the value was modified during insertion, it will not be found in the table.

But for FLOAT there is no warning, so the expectation is that the value should be found. FLOAT type is quite unique in this regard.

Comment by Alexander Barkov [ 2021-05-11 ]

FLOAT->DOUBLE->FLOAT conversion is actually not lossy. serg is right. The only reason for SELECT to return empty set is that the value gets truncated during INSERT.

So the only thing we can do here is to add the warning on INSERT.

Generated at Thu Feb 08 08:32:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.