Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16873

Compare two FLOAT expressions as floats rather than doubles

    XMLWordPrintable

Details

    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.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.