Incorrect Result with WHERE/HAVING Clause on REAL UNIQUE Column Due to '-0.0' and 0 Comparison

CREATE TABLE t0(c0 REAL UNIQUE,PRIMARY KEY(c0));

CREATE TABLE t1 LIKE t0;

INSERT INTO t0 SELECT seq FROM seq_0_to_10;

UPDATE IGNORE t0 SET c0='-0.0';

INSERT INTO t1 VALUES (0);

-- Expected: Empty result set

-- Actual: Returns one row with value '-0.0'

SELECT * FROM t0 

WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0) 

HAVING EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0); --{-0.0}

import pymysql

import sys

def test_mysql():

    # Database configuration

    host = "localhost"

    database = "mysql"

    user = "root"

    password = ""

    port = 3307

    connection = None

    cursor = None

    try:

        # 1. Establish database connection

        connection = pymysql.connect(

            host=host,

            user=user,

            password=password,

            database=database,

            port=port,

            charset='utf8mb4',

            cursorclass=pymysql.cursors.DictCursor

        )

        print("Successfully connected to database!")

        # 2. Create cursor object

        cursor = connection.cursor()

        # 3. Execute all SQL statements

        sql_statements = [

            "DROP TABLE IF EXISTS t0",

            "DROP TABLE IF EXISTS t1",

            "CREATE TABLE t0(c0 REAL UNIQUE,PRIMARY KEY(c0))",

            "CREATE TABLE t1 LIKE t0",

            "INSERT INTO t0 SELECT seq FROM seq_0_to_10",

            "UPDATE IGNORE t0 SET c0='-0.0'",

            "INSERT INTO t1 VALUES (0)"

        ]

        for sql in sql_statements:

            try:

                cursor.execute(sql)

                connection.commit()

                print(f"Execution successful: {sql[:50]}..." if len(sql) > 50 else f"Execution successful: {sql}")

            except pymysql.Error as e:

                print(f"Execution failed: {sql[:50]}..." if len(sql) > 50 else f"Execution failed: {sql}")

                print(f"Error message: {e}")

        # 4. Execute query statement

        query_sql = "SELECT * FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0) HAVING EXISTS (SELECT 1 FROM t1 WHERE t0.c0 = t1.c0)"

        print(f"\nExecuting query: {query_sql}")

        cursor.execute(query_sql)

        # 5. Process query results

        result = cursor.fetchall()

        # Print column names

        if result:

            print("Query results:")

            columns = [desc[0] for desc in cursor.description]

            print("\t".join(columns))

            print("----------------------------------------")

            # Print data

            row_count = 0

            for row in result:

                values = [str(value) for value in row.values()]

                print("\t".join(values))

                row_count += 1

            print(f"Total rows returned: {row_count}")

            # Add assertion to verify test results

            assert row_count >= 0, "Query should return zero or more rows of data"

        else:

            print("No results returned")

    except pymysql.Error as e:

        print(f"Database operation error: {e}")

        sys.exit(1)

    except Exception as e:

        print(f"Unexpected error: {e}")

        sys.exit(1)

    finally:

        # 6. Close resources

        if cursor:

            cursor.close()

        if connection:

            connection.close()

            print("\nDatabase connection closed")

if __name__ == "__main__":

    test_mysql()