[DRILL-8388] CTAS sent over JDBC may be cancelled if query results are not fetched

• Type: Task
• Status: Resolved
• Priority: Minor
• Resolution: Information Provided
• Affects Version/s: 1.20.3
• Fix Version/s: Future
• Component/s: Client - JDBC, Storage - Writer
• Assignee: James Turton
• Reporter: James Turton
• Created: 17/Jan/23 06:16
• Updated: 30/Jan/23 12:32
• Resolved: 21/Jan/23 10:16

Description

When a JDBC client issues a CTAS statement then Drill will return a record for each completed writer fragment containing the number of records that fragment wrote. These records are returned in the usual streaming fashion as writer fragments complete, their order being unknowable in advance. If the client application immediately closes its clientside JDBC resources after its call to Statement.executeQuery has returned as follows

Statement ctasStatement = conn.createStatement();
ResultSet ctasResults = ctasStatement.executeQuery(ctasQueryText);
ctasResults.close();
ctasStatement.close();

it may be that the CTAS statement is still executing, and that is then unintentionally cancelled depending on good or bad luck with respect to timing.

The cancellation of the CTAS statement is usually benign if it spawned only one writer fragment, but if it spawned more than one then the chances increase that at least one writer will be interrupted before it has finished writing, resulting in incomplete or even corrupted output. Even in the benign case, such queries conclude in the CANCELLED state rather than the COMPLETED state.

To have CTAS queries reliably run to completion, the JDBC client can wait for all of the writer fragments to complete before it closes its JDBC resources by scrolling through the ResultSet before closing it. Using try-with-resources syntax,

try (
  Statement ctasStatement = conn.createStatement();
  ResultSet ctasResults = ctasStatement.executeQuery(ctasQueryText);
) {
  while (ctasResults.next());
}