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

public class BugTest {

  public static void main(String[] args) {
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      String connUrl = "jdbc:mysql://ci16:2209/test";
      String username= "bug";
      String password= "bug";
      
      System.out.println("--- Working test ---");
      Connection conn = DriverManager.getConnection(connUrl, username, password);
      executeTest(523842, conn);
      conn.close();

      System.out.println("\n\n--- Buggish test ---");
      conn = DriverManager.getConnection(connUrl, username, password);
      executeTest(523843, conn);

      conn.close();
    } catch (Exception ex) {
      System.err.println(ex);
    }
  }
  
  static private void executeTest(int rowCount, Connection conn) throws SQLException {

    Statement stmt = conn.createStatement();
    stmt.executeUpdate("drop table if exists table_a");
    stmt.close();
    stmt = conn.createStatement();
    stmt.executeUpdate("drop table if exists table_b");
    stmt.close();
    stmt = conn.createStatement();
    stmt.executeUpdate("create table table_a (id int(10) unsigned default null, a integer, b integer, INDEX idx (a,b))");
    stmt.close();
    stmt = conn.createStatement();
    stmt.executeUpdate("create table table_b (id int(10) unsigned auto_increment, a integer, b integer, primary key table_a (id))");
    stmt.close();

    StringBuilder insertStmt = new StringBuilder();
    insertStmt.append("insert into table_a (a,b) values ");
    for(int i = 0; i < rowCount; i++) {
      insertStmt.append("(");
      insertStmt.append(i);
      insertStmt.append(",");
      insertStmt.append(i);
      insertStmt.append(")");
      if(i+1 < rowCount) {
        insertStmt.append(",");
      }
    }
    stmt = conn.createStatement();
    System.out.println("Insert into table_a: " + stmt.executeUpdate(insertStmt.toString()));
    stmt.close();
    stmt = conn.createStatement();
    System.out.println("Insert into table_b: " + stmt.executeUpdate("insert into table_b (a,b) select a,b from table_a"));
    stmt.close();

    System.out.println("Is not null        : " + countNotNull(conn));
    System.out.println("Is null            : " + countNull(conn));

    System.out.println("Rows joined        : " + countMatches(conn));

    stmt = conn.createStatement();
    System.out.println("Update table_a     : " + stmt.executeUpdate("update table_a as t join table_b as t2 on (t.a=t2.a AND t.b=t2.b) set t.id=t2.id"));
    stmt.close();

    System.out.println("Is not null        : " + countNotNull(conn));
    System.out.println("Is null            : " + countNull(conn));
    
    conn.close();  }

  static private int countMatches(Connection conn) throws SQLException {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select count(*) from table_a as t join table_b as t2 on (t.a=t2.a AND t.b=t2.b)");
    rs.next();
    int c = rs.getInt(1);
    rs.close();
    stmt.close();
    return c;
  }

  static private int countNull(Connection conn) throws SQLException {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select count(*) from table_a where id IS NULL");
    rs.next();
    int c = rs.getInt(1);
    rs.close();
    stmt.close();
    return c;
  }

  static private int countNotNull(Connection conn) throws SQLException {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select count(*) from table_a where id IS NOT NULL");
    rs.next();
    int c = rs.getInt(1);
    rs.close();
    stmt.close();
    return c;
  }
}