[YSQL] DELETE doesn't work when specifying a float key via JDBC

[komamitsu]

Jira Link: DB-7435

Description

I noticed DELETE statement in YugabyteDB can't delete records when specifying a float key via JDBC.

I think you can reproduce this issue using the following code.

package org.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Supplier;

public class Main {

  public static void main(String[] args) throws SQLException {
//    Supplier<Connection> connFactory = ConnectionUtils.forPg();
    Supplier<Connection> connFactory = ConnectionUtils.forYugabyte();

    float key = 3.141592f;
    try (Connection conn = connFactory.get();
        Statement stmt = conn.createStatement()) {
      stmt.execute("CREATE TABLE IF NOT EXISTS float_tbl (pk double precision PRIMARY KEY)");
    }

    try (Connection conn = connFactory.get();
        PreparedStatement ps = conn.prepareStatement("INSERT INTO float_tbl (pk) VALUES (?)")) {
      ps.setFloat(1, key);
      System.out.println("inserted: " + ps.executeUpdate());
    }

    try (Connection conn = connFactory.get();
        PreparedStatement ps = conn.prepareStatement("DELETE FROM float_tbl WHERE pk = ?")) {
      ps.setFloat(1, key);
      System.out.println("deleted: " + ps.executeUpdate());
    }

    try (Connection conn = connFactory.get();
        PreparedStatement ps = conn.prepareStatement("SELECT pk FROM float_tbl WHERE pk = ?")) {
      ps.setFloat(1, key);
      ResultSet resultSet = ps.executeQuery();
      if (resultSet.next()) {
        System.out.println("selected value: " + resultSet.getFloat(1));
      }
      else {
        System.out.println("no selected value");
      }
    }
  }
}

This code creates a table that has a float primary key column, inserts a record and tries to delete the record. But the DELETE statement doesn't delete the record, while it works with PostgreSQL.

• With YugabyteDB

> Task :Main.main()
inserted: 1
deleted: 0
selected value: 3.141592

• With PostgreSQL

> Task :Main.main()
inserted: 1
deleted: 1
no selected value

I'm using YugabyteDB v2.19.0.0-b190.

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[ddorian]

@komamitsu what version of postgresql did you use to test? Did you try with 11.x ?

[komamitsu]

@ddorian I used PostgreSQL 15.1.

[komamitsu]

I don't have a permission to the JIRA ticket, so could you tell me if there is any updates? Thanks.

[ddorian]

@komamitsu

The JIRA ticket is created automatically.

I've asked a developer to take a look at this.

I couldn't reproduce on python though.
yuga_float_check.py.zip

[komamitsu]

@ddorian Thanks for taking care of this issue.

BTW, I used Java 11 (Temurin) and PostgreSQL JDBC org.postgresql:postgresql:42.6.0 when I executed the above code.

[andrei-mart]

Due to internal precision errors in rounding up floating-point numbers it is always incorrect to test floats for exact equality.
It may fail quite randomly.
Use fixed point data type like numeric, or "almost equal" comparison.
Not an issue.

[komamitsu]

@andrei-mart Thanks for replying on this issue.

What I'm wondering is as follow

• This issue doesn't occur in PostgreSQL and even in MySQL
• The SELECT clause sees the key. But the DELETE clause can't find it. This is an inconsistent behavior
• The above code reproduces this issue 100%

And I didn't get what you mean by "randomly" while this issue doesn't happen at all in PostgreSQL.

I'm evaluating YugabyteDB for some reasons as a scalable PostgreSQL compatible database. But are you saying it's not compatible...?

[ddorian]

@komamitsu

This is a typical computer science problem.

And I didn't get what you mean by "randomly" while this issue doesn't happen at all in PostgreSQL.

Read https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-FLOAT , specifically this section:::

Comparing two floating-point values for equality might not always work as expected.

I can find the same thing in MYSQL docs https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

---

So this is compatible with PostgreSQL. You just didn't hit it yet in PostgreSQL with your exact float-number above, but you will hit it, guaranteed, randomly.

[komamitsu]

@ddorian Thanks for pointing the documents. I agree with the difficulty in handling floating point types.

The SELECT clause sees the key. But the DELETE clause can't find it. This is an inconsistent behavior

So I guess YugabyteDB uses different ways to look up with float type key in SELECT and DELETE. But I think we don't need to discuss on this issue anymore.

[ddorian]

So I guess YugabyteDB uses different ways to look up with float type key in SELECT and DELETE

Are you guaranteed this won't happen in postgresql/mysql?

[komamitsu]

@ddorian No. I don't guarantee that as I've not looked into the implementations yet.

https://gist.github.com/komamitsu/6df9fd53312c33b46b4bb0c9018652b9

I tried to reproduce this issue with PostgreSQL, MySQL and YugabyteDB some times (n = 10000) using the code on the Gist, but the results were significantly different.

• No failure with PostgreSQL and MySQL
• No success with YugabyteDB
• 2 duplicated key exceptions out of 10000 attempts with YugabyteDB. But the conflict rate was too high...?
  (Actually, I executed the code several times, but the results were same)

So this issue might happen randomly in PostgreSQL and MySQL as well, but I would conclude that how often the issue occurs is significantly different with YugabyteDB.

[ddorian]

I still don't understand how you'd expect for this to work in production. Can you explain more your use-case? Along with the failure modes of float-keys not working correctly?

[komamitsu]

Yeah. We've developed some products that works with underlying databases (e.g., PostgreSQL, MySQL, Oracle, SQL Server.) Our users can perform CRUD operations including floating point type key lookup through them. As you said, using float key may fail randomly, but we've not seen this issue over a lot of tests and our users might expect their float key lookup works with "supported database". We're interested in this scalable PostgreSQL compatible database YugabyteDB supporting multi-region replication as a candidate. We can say that our products support YugabyteDB as an underlying database once this issue is resolved. But I don't want to push you on this issue so hard.