MySQL Bugs: #24589: Comparison fails, subtraction works

Bug #24589	Comparison fails, subtraction works
Submitted:	25 Nov 2006 19:22	Modified:	27 Sep 2008 10:40
Reporter:	Markus KARG	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MaxDB	Severity:	S1 (Critical)
Version:	7.6.0.34	OS:	Linux (OpenSUSE 10.1)
Assigned to:		CPU Architecture:	Any

Description:
Under special conditions, a simple comparison of two integers is failing.

How to repeat:
Create a new database.
Run the following program.
The program will print out that table A is empty at the end, what is the expected result.
Then exchange "CREATE TABLE A (X INTEGER)" by "CREATE TABLE A (X INTEGER, PRIMARY KEY (X))". In fact, we expect the program to still work. Actually when you run it, this time it says that table A is NOT empty at the end (which must not happen, actually).
Then exchange "... WHERE TEMP.B.X = A.X)" by "... WHERE TEMP.B.X - A.X = 0)". Run the program once more and the program will tell you that now table A is empty as expected.

It seems that using PRIMARY KEY in conjunction with DELETE and EXISTS and TEMPORARY TABLES is buggy. Unfortunately constructions like this are needed sometimes to do complex DELETE commands, e. g. when you want to DELETE from a JOIN (since MaxDB 7.6 is not supporting JOINs in DELETE -- you'll have to INSERT into a TEMPORARY TABLE from a SELECT JOIN).

This is a severe issue since there is no workaround: You cannot change applications that are trying to do this combination of features (unless they are open source)!

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public final class Foo {

    public static final void main(final String[] args) throws Exception {
        Class.forName("com.sap.dbtech.jdbc.DriverSapDB");
        final Connection c = DriverManager.getConnection("jdbc:sapdb://localhost/SCRUM", "DBA", "SQL");
        c.setAutoCommit(false);
        
        c.createStatement().execute("CREATE TABLE A (X INTEGER, PRIMARY KEY (X))");
        c.createStatement().execute("INSERT INTO A (X) VALUES (1)");
        select("SELECT X FROM A", c); // Expected: Should find one row.
        c.createStatement().execute("CREATE TABLE TEMP.B LIKE A");
        c.createStatement().execute("INSERT INTO TEMP.B (X) SELECT X FROM A");
        select("SELECT X FROM TEMP.B", c); // Expected: Should find one row.
        c.createStatement().execute("DELETE FROM A WHERE EXISTS(SELECT X FROM TEMP.B WHERE TEMP.B.X = A.X)");
        select("SELECT X FROM A", c); // Expected: Should not find anything!
        c.createStatement().execute("DROP TABLE A");
        c.commit();
    }
    
    private static final void select(final String sql, final Connection c) throws SQLException {
        System.out.print(sql + ": ");
        final ResultSet r = c.createStatement().executeQuery(sql);
        while (r.next()) {
            for (int i = 1, j = r.getMetaData().getColumnCount() + 1; i < j; i++)
                System.out.print(r.getString(i) + "\t");
            System.out.println();
        }
    }

}

Suggested fix:
The comparison must work.

There is a bug in the test program posted directly in the bug report.
Please use the test software attached as a separate file instead.
Unchanged, it will run without problem.
To see the bug, THEN add the PRIMARY KEY.
To see the workaround (using subtraction), THEN add the subtraction.
The original bug report directly produced the bug, despite the description in the report. Sorry.

Correctly working software. To reproduce the bug, just add a primary key to table A.

Attachment: Foo.java (text/x-java), 1.51 KiB.

This version of the test software reproduces the bug: It is using a PRIMARY KEY on table A.

Attachment: Foo.java (text/x-java), 1.53 KiB.

This version of the test software shows a possible workaround by using subtraction.

Attachment: Foo.java (text/x-java), 1.53 KiB.

Commands used to create the database

Attachment: dberase.sh (application/x-sh, text), 95 bytes.

Commands used to create the database

Attachment: dbinit.sh (application/x-sh, text), 124 bytes.

Parameters used to create database.

Attachment: dbinit.maxdb (application/octet-stream, text), 211 bytes.

Thank you for a problem report. According to http://www.mysql.com/news-and-events/press-release/release_2007_40.html:

"support of the MaxDB database will revert back to SAP"

MaxDB is not under MySQL umbrella any more.