[coverage] Conformance findings: STATEMENT-025,STATEMENT-026

Start with the xfail tests test_write_statement_reports_affected_rows_without_result_set and test_reused_statement_clears_result_state_after_write in the coverage PR diff under tests/, then compare the expected behavior with the reference ODBC PR. Check both Thrift and kernel/SEA cases, including counter-shaped SELECTs that must remain result sets. Done means writes expose affected-row counts without fetchable results, and resultless statements clear cursor metadata.

Summary

Surfaced by the multi-language coverage fan-out while conformance-testing these SPEC-IDs against databricks/databricks-sql-python. Each finding is committed as an expected-failure (xfail) test in the coverage PR — the test asserts the CORRECT (post-fix) behavior and stays red until THIS driver (databricks/databricks-sql-python) is fixed, then flips green as a tripwire.

Findings

• STATEMENT-025 [thrift]: A write statement publishes the DML counter record as a fetchable result set: cursor.description reports ['num_affected_rows','num_inserted_rows'] for INSERT, 4 counter columns for MERGE, and ['Result'] for DDL — so an INSERT presents a phantom one-row grid. cursor.rowcount is correct; the result KIND is not.
  • failing test: test_write_statement_reports_affected_rows_without_result_set (see the coverage PR diff under tests/)
• STATEMENT-025 [sea]: On the kernel/SEA backend a DML statement publishes the counter record as a fetchable result set: cursor.description reports ['num_affected_rows','num_inserted_rows'] for INSERT (4 counter columns for MERGE), so an INSERT presents a phantom one-row grid. rowcount is correct; the result KIND is not. DDL correctly reports zero columns here.
  • failing test: test_write_statement_reports_affected_rows_without_result_set (see the coverage PR diff under tests/)
• STATEMENT-026 [thrift]: A resultless DML/DDL statement on a reused Cursor does not report zero result columns: it reports the write's phantom counter grid (['num_affected_rows','num_inserted_rows']; ['Result'] for DDL) instead of clearing result state on the no-result-set branch. The affected-row count and the subsequent fresh SELECT's own metadata are correct.
  • failing test: test_reused_statement_clears_result_state_after_write (see the coverage PR diff under tests/)
• STATEMENT-026 [sea]: On the kernel/SEA backend a reused Cursor's DML statement does not report zero result columns: it reports the write's phantom counter grid ['num_affected_rows','num_inserted_rows'] instead of clearing result state on the no-result-set branch. The affected-row count and the subsequent fresh SELECT's own metadata are correct; the DDL phase is correct on this backend.
  • failing test: test_reused_statement_clears_result_state_after_write (see the coverage PR diff under tests/)
• STATEMENT-025: A write statement publishes the protocol's DML counter record as a fetchable application result set: cursor.description reports ['num_affected_rows','num_inserted_rows'] for INSERT (4 counter columns for MERGE, ['Result'] for DDL on Thrift), so an INSERT looks like a one-row SELECT to a caller branching on "did this return rows?". cursor.rowcount is correct; the result KIND is not. The classifier must be positive on the SQL form — counter-shaped SELECTs currently work and must keep working.
• STATEMENT-026: A resultless DML/DDL statement on a reused Cursor that previously ran a row-returning query does not report zero result columns: it reports the write's phantom counter grid (['num_affected_rows','num_inserted_rows']; ['Result'] for DDL on Thrift) instead of clearing result state on the no-result-set branch. Upstream of STATEMENT-025's phantom-grid defect; the affected-row count and the subsequent fresh SELECT's own metadata are correct.

Reproduce & Expected

STATEMENT-025 — Validates the RESULT KIND of write statements: a DML statement exposes its affected-row count and NO fetchable result set, and a DDL statement exposes neither.

Reproduce:

INSERT INTO {tableName} VALUES (1, 'a'), (2, 'b')
UPDATE {tableName} SET name = 'c' WHERE id = 2
DELETE FROM {tableName} WHERE id = 1
DELETE FROM {tableName} WHERE id = -12345
MERGE INTO {tableName} AS t
USING (SELECT 2 AS id, 'merged' AS name) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name)
WITH src AS (SELECT 3 AS id, 'd' AS name) INSERT INTO {tableName} SELECT id, name FROM src
ALTER TABLE {tableName} ADD COLUMN extra STRING
TRUNCATE TABLE {tableName}
SELECT CAST(7 AS BIGINT) AS num_affected_rows
WITH c AS (SELECT CAST(7 AS BIGINT) AS num_affected_rows) SELECT * FROM c
TABLE {counterViewName}

Expected (per the shared spec):

• completes without an exception
• completes without an exception
• completes without an exception
• completes without an exception
• completes without an exception
• completes without an exception
• completes without an exception
• completes without an exception
• completes without an exception
• result has exactly 1 row(s)
• result has 1 column(s)
• col 0, row 0 == 7 (type Int64)
• completes without an exception
• result has exactly 1 row(s)
• col 0, row 0 == 7 (type Int64)
• completes without an exception
• result has exactly 1 row(s)
• col 0, row 0 == 7 (type Int64)
• full assertion contract:

result:
- label: insert
  no_exception: true
- label: insert
  no_result_set: true
- label: insert
  affected_row_count: 2
- label: update
  no_exception: true
- label: update
  no_result_set: true
- label: update
  affected_row_count: 1
- label: delete
  no_exception: true
- label: delete
  no_result_set: true
- label: delete
  affected_row_count: 1
- label: delete_zero_rows
  no_exception: true
- label: delete_zero_rows
  no_result_set: true
- label: delete_zero_rows
  affected_row_count: 0
- label: merge
  no_exception: true
- label: merge
  no_result_set: true
- label: merge
  affected_row_count_min: 1
- label: cte_insert
  no_exception: true
- label: cte_insert
  no_result_set: true
- label: cte_insert
  affected_row_count: 1
- label: ddl_alter
  no_exception: true
- label: ddl_alter
  no_result_set: true
- label: ddl_alter
  affected_row_count_not_positive: true
- label: ddl_truncate
  no_exception: true
- label: ddl_truncate
  no_result_set: true
- label: ddl_truncate
  affected_row_count_not_positive: true
- label: counter_select
  no_exception: true
- label: counter_select
  row_count: 1
- label: counter_select
  column_count: 1
- label: counter_select
  column:
    index: 0
    row: 0
    type: Int64
    equals: 7
- label: counter_cte
  no_exception: true
- label: counter_cte
  row_count: 1
- label: counter_cte
  column:
    index: 0
    row: 0
    type: Int64
    equals: 7
- label: counter_table
  no_exception: true
- label: counter_table
  row_count: 1
- label: counter_table
  column:
    index: 0
    row: 0
    type: Int64
    equals: 7

STATEMENT-026 — Validates that executing a RESULTLESS statement (DML or DDL) on a statement/handle that previously ran a row-returning query REPLACES the statement's result state rather than leaving the earlier quer…

Reproduce:

SELECT 1 AS a, 'x' AS b
INSERT INTO {tableName} VALUES (1, 'a')
SELECT 42 AS only_col
ALTER TABLE {tableName} ADD COLUMN extra STRING
SELECT 1 AS a, 'x' AS b
INSERT INTO {tableName} VALUES (2, 'b')

Expected (per the shared spec):

• completes without an exception
• result has exactly 1 row(s)
• result has 2 column(s)
• completes without an exception
• result has 0 column(s)
• completes without an exception
• result has exactly 1 row(s)
• result has 1 column(s)
• col 0 is named only_col
• completes without an exception
• result has 0 column(s)
• completes without an exception
• result has 2 column(s)
• completes without an exception
• result has 0 column(s)
• completes without an exception
• full assertion contract:

result:
- label: baseline_select
  no_exception: true
- label: baseline_select
  row_count: 1
- label: baseline_select
  column_count: 2
- label: write_after_select
  no_exception: true
- label: write_after_select
  no_result_set: true
- label: write_after_select
  column_count: 0
- label: write_after_select
  affected_row_count: 1
- label: select_after_write
  no_exception: true
- label: select_after_write
  row_count: 1
- label: select_after_write
  column_count: 1
- label: select_after_write
  column:
    index: 0
    name: only_col
- label: ddl_after_select
  no_exception: true
- label: ddl_after_select
  no_result_set: true
- label: ddl_after_select
  column_count: 0
- label: prepared_baseline
  no_exception: true
- label: prepared_baseline
  metadata_not_null: true
- label: prepared_baseline
  column_count: 2
- label: prepared_switch
  no_exception: true
- label: prepared_switch
  metadata_is_null: true
- label: prepared_switch
  column_count: 0
- label: prepared_switch_execute
  no_exception: true
- label: prepared_switch_execute
  no_result_set: true
- label: prepared_switch_execute
  affected_row_count: 1

Context

• The behavior was first fixed in a DIFFERENT driver — reference PR: https://github.com/databricks/databricks-odbc/pull/433 — which seeded the shared language-neutral spec. This issue tracks the same conformance gap in databricks/databricks-sql-python; the reference PR is for cross-referencing the intended behavior, NOT a change to this repo.
• Coverage PR carrying the reproducing xfail test(s): https://github.com/databricks/databricks-driver-test/pull/1629