Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1064

Regression after 3.0.8 (3.0.9+): Batch UPDATE requests with null parameters work incorrectly

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 3.0.10, 3.1.2
    • 3.2.0
    • batch
    • None

    Description

      You can use the following code to reproduce this regression:

          public static void main(String... args) throws Throwable {
              var reproduceThisBug = true;
              var url = "jdbc:mariadb://localhost:3306/test?useServerPrepStmts&useBulkStmts=" + reproduceThisBug;
              try (var conn = DriverManager.getConnection(url, "root", "password")) {
                  try (var stmt = conn.createStatement()) {
                      stmt.executeUpdate("""
                          CREATE TABLE IF NOT EXISTS testtable(
                              username VARCHAR(100) NOT NULL PRIMARY KEY,
                              timeMs BIGINT NULL DEFAULT NULL
                          )
                          """);
                  }
       
                  var sql = """
                      INSERT INTO testtable(username, timeMs) VALUES(?, ?) ON DUPLICATE KEY
                          UPDATE timeMs = IF(timeMs IS NULL, ?, IF(? IS NULL, timeMs, ?))
                      """;
       
                  try (var stmt = conn.prepareStatement(sql)) {
                      Long timeMs1 = 2140L;
                      setObjects(stmt,
                          "user1", timeMs1,
                          timeMs1, timeMs1, timeMs1
                      );
                      stmt.addBatch();
       
                      Long timeMs2 = 1000L;
                      setObjects(stmt,
                          "user2", timeMs2,
                          timeMs2, timeMs2, timeMs2
                      );
                      stmt.addBatch();
       
                      stmt.executeBatch();
                  }
       
                  try (var stmt = conn.prepareStatement(sql)) {
                      Long timeMs1 = 2030L;
                      setObjects(stmt,
                          "user1", timeMs1,
                          timeMs1, timeMs1, timeMs1
                      );
                      stmt.addBatch();
       
                      Long timeMs2 = null;
                      setObjects(stmt,
                          "user2", timeMs2,
                          timeMs2, timeMs2, timeMs2
                      );
                      stmt.addBatch();
       
                      stmt.executeBatch();
                  }
              }
          }
       
          public static void setObjects(PreparedStatement stmt, Object... args) throws SQLException {
              var offset = 0;
              for (Object arg : args) {
                  stmt.setObject(++offset, arg);
              }
          }
      

      When `useBulkStmts` is set to `false`, this code results in both rows having non-null `timeMs`. However, when `useBulkStmts` is set to `true`, the row for `user2` has `timeMs=NULL`. This issue only occurs in versions after 3.0.8.

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            xocip57585 xocip57585
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.