Miscellaneous SQL bugs

[rnbrady]

Summary

Reviewing the SQL after investigating the block ingestion issue turned up several independent SQL bugs/edge cases. These should be fixed one at a time with failing tests followed by fixes.

1. block.encoded_hex produces incorrect output

block.encoded_hex encodes the block transaction count using COUNT(cursor), which is not the transaction count. On PostgreSQL 14 this tends to emit 01; on newer PostgreSQL versions it can emit 00. The rest of the block encoding appears correct in recent live samples.

2. recordNodeValidation builds invalid SQL

src/db.ts builds an INSERT INTO node_transaction ... SELECT ... FROM (VALUES ...) INNER JOIN ... query that is syntactically invalid. This path is used when a node announces a transaction that is already known to be in the database, so affected node validation rows may be missing and the agent logs an async DB error.

3. transaction_data_carrier_outputs fails on empty locking bytecode

The function evaluates get_byte(locking_bytecode, 0) without first checking that the bytecode is non-empty.

Impact: querying transaction.data_carrier_outputs can fail for transactions with a nonzero-value output whose locking_bytecode = '\x'.

Example query shape:

query EmptyLockingBytecodeDataCarrierBug {
  transaction(
    where: {
      outputs: {
        locking_bytecode: { _eq: "\\x" }
        value_satoshis: { _neq: "0" }
      }
    }
    limit: 1
  ) {
    hash
    data_carrier_outputs {
      output_index
      value_satoshis
      locking_bytecode
    }
  }
}

Observed on pc3: index 0 out of valid range, 0..-1.

4. Value aggregates return NULL for coinbase-only blocks

Functions based on SUM(...) return NULL when every input value is NULL. For coinbase-only blocks, fields like block.input_value_satoshis, block.fee_satoshis, and block.generated_value_satoshis can therefore be NULL when callers expect numeric values.

Example observed on both pc3 and Pat for block 950314:

• transaction_count: 1
• output_value_satoshis: 312500000
• input_value_satoshis: null
• fee_satoshis: null
• generated_value_satoshis: null

fee_satoshis should be 0; generated_value_satoshis should be 312500000.

5. node_transaction_history has no primary key

The table has an internal_id sequence but no primary key/unique constraint, unlike node_block_history. A local constraint check showed only the two foreign keys.

6. parse_bytecode_pattern_with_pushdata_lengths mishandles zero-length PUSHDATA

Valid zero-length pushes lose their length bytes in the returned pattern:

• 4c00 -> 4c
• 4d0000 -> 4d
• 4e00000000 -> 4e

Expected patterns should preserve the length bytes.

7. parse_bytecode_pattern_redeem can throw on malformed pushes

Malformed push opcodes like 4c can throw instead of returning a partial/null result because the function reads length bytes without bounds checks. The function also uses maybe_redeem = NULL, which is never true in SQL.