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

Bắt đầu với các test xfail test_write_statement_reports_affected_rows_without_result_set và test_reused_statement_clears_result_state_after_write trong diff của coverage PR tại tests/, sau đó so sánh hành vi dự kiến với ODBC PR tham chiếu. Kiểm tra cả các trường hợp Thrift và kernel/SEA, bao gồm các SELECT có dạng counter phải tiếp tục là result sets. Hoàn tất khi các thao tác ghi cung cấp số lượng hàng bị ảnh hưởng mà không có kết quả có thể fetch, và các statement không có kết quả xóa metadata của cursor.

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