MySQL Bugs: #324: Metadata problems with Connector/J 3.0.7 and MySQL 4.1

Bug #324	Metadata problems with Connector/J 3.0.7 and MySQL 4.1
Submitted:	25 Apr 2003 5:54	Modified:	26 Aug 2003 23:54
Reporter:	Miguel Lorenzo Turbón	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.14	OS:	Linux (Red Hat 8.0)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:

SELECT MAX(foocolumn) FROM footable

I you ask for the ResultSetMetaData.isNullable() from column 1 you get columnNoNulls but if the footable is empty this query returns one row with
 a null value. 

In 3.0.6 is ok, but in 3.0.6 there are other metadata bugs corrected in 3.0.7.

How to repeat:

Connection conn = datasource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rst =  stmt.executeQuery(
                  "SELECT MAX(foocolumn) AS myvalue FROM footable");
ResultSetMetaData rd = rst.getMetaData();
System.out.println(rd.isNullable())

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I've made a testcase based on the code you've provided, but it works, i.e. I get 'columnNullable' for both the 'max' and straight column selects.

A repeatable testcase would include the table schema, and any data in that table that might cause this problem. Can you please include a full test case so that I can test this with your schema?

Sorry, I was wrong with the driver version. Code fails in 3.0.6 and 3.1. It's OK in 3.0.7.

Can you please try a nightly snapshot of 3.1 from http://mmmysql.sourceforge.net/snapshots/dev/ ???

I just tried it with the latest version of 3.1, and I'm not able to repeat it with that, either.

Ok, a test case. 

I think that the problem is: 

select max(col) from foo 

on empty table returns one row with 1 column, the value
of this column is null (but the driver says that is NOT
nullable because col is NOT NULL in the schema)

see the following code, i've tested with mysql and oracle 9 resulting in:

$ java -classpath oracle9-classes12.jar:oracle9-nls_charset12.jar:. Uno  oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@bicho:1521:dbweb
COL     ISNULL  NULLABLE        VAL
-------------------------------------
ID      false           0       1
NAME    false           1       aaaa
-------------------------------------
ID      false           0       2
NAME    true            1       null

COL     ISNULL  NULLABLE        VAL
-------------------------------------
MAX(ID) true            1       0

$ java -classpath mysql-connector-java-3.1-nightly-20030425/mysql-connector-java-3.1-nightly-20030425-bin.jar:. Uno com.mysql.jdbc.Driver jdbc:mysql://hercules.dicoruna.priv/dweb
COL     ISNULL  NULLABLE        VAL
-------------------------------------
ID      false           0       1
NAME    false           1       aaaa
-------------------------------------
ID      false           0       2
NAME    true            1       null

COL     ISNULL  NULLABLE        VAL
-------------------------------------
MAX(ID) true            0       0

--------------------------------------------------

import java.sql.*;

/*

Oracle
java -classpath oracle9-classes12.jar:oracle9-nls_charset12.jar:. Uno  oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@bicho:1521:dbname

MySQL
java -classpath mysql-connector-java-3.0.7-stable/mysql-connector-java-3.0.7-stable-bin.jar:. Uno com.mysql.jdbc.Driver jdbc:mysql://host/dbname

*/

public class Uno {

    public static void main(String args[]) {
      Uno app = new Uno();
      app.probe0(args[0],args[1]);
    }

    void probe0(String driver, String url) {

      Connection con;
      Statement stmt;
      String tablename = "footable";

      try {
        con = getConnection(driver, url);
        stmt = con.createStatement();

        stmt.executeUpdate("create table "+tablename+" (id int not null, name char(50))");
        stmt.executeUpdate("insert into "+tablename+" values (1, 'aaaa')");
        stmt.executeUpdate("insert into "+tablename+" values (2, null)");

        // Select
        ResultSet result = stmt.executeQuery("select id, name from "+tablename);
        ResultSetMetaData m = result.getMetaData();
        System.out.println("COL\tISNULL\tNULLABLE\tVAL");
        while (result.next()) {
            System.out.println("-------------------------------------");
            int id = result.getInt(1);
            System.out.println("ID\t"+result.wasNull()+"\t\t"+m.isNullable(1)+"\t"+id);
            String name = result.getString(2);
            System.out.println("NAME\t"+result.wasNull()+"\t\t"+m.isNullable(2)+"\t"+name);
        }

        System.out.println("");

        // Select MAX on empty result, returns 1 row with "null" => can't be nullable
        result = stmt.executeQuery("select max(id) as maxvalue from "+tablename+" where 1=2");
        m = result.getMetaData();
        System.out.println("COL\tISNULL\tNULLABLE\tVAL");
        while (result.next()) {
            System.out.println("-------------------------------------");
            int id = result.getInt(1);
            System.out.println("MAX(ID)\t"+result.wasNull()+"\t\t"+m.isNullable(1)+"\t"+id);
        }

        // Cleaning
        stmt.executeUpdate("drop table "+tablename);

        stmt.close();
        con.close();
      } catch(Exception e) {
        System.err.print("Exception: ");
        System.err.println(e.getMessage());
      }
    }

    public Connection getConnection(String driverName, String url)
      throws SQLException, ClassNotFoundException {

      Connection connection = null;
      Class.forName(driverName);
      String username = "user";
      String password = "pass";
      connection = DriverManager.getConnection(url, username, password);
      return connection;
   }
}

MySQL server itself is returning the nullability of the MAX() column in this case, and Connector/J is only reporting what the server is returning.

I'm assigning this to someone in the server team for further analysis.

As a note (for Sinisa), the SQL-99 spec says that MAX() can return NULL in the case where every column value is NULL (which isn't this case), if the SELECT returns no rows (which is this case), or if you use MAX(someFunction()) where someFunction returns NULL.

In any case, this seems to be a 'corner' case, because MySQL is returning the metadata (type, nullability, etc.) of the underlying column ('ID' in this case), rather than the metadata for the aggregate function. The SQL-99 spec seems to be vague on this point. This is probably a question that should be posed to Peter G.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

I have now changed the meta-data information returned by 4.0.15 so that that all MAX() and MIN() results are marked as 'may return NULL'