Table join count check

table-join-count-check · version 1.0.0 · CSV & tables · free, no key needed

Check a claimed inner, left, right or full equi-join row count over two JSON tables by key multiplicities.

Use when you need to: check a claimed join row count · how many rows will this left join return exactly · verify full outer join result size from two json tables.

Decide before calling

Read the versioned contract and the supported scope below. Reuse table-join-count-check@1.0.0 when your input, required output and limits match it. Choose another approach for an unsupported operation.

Explain the choice

"I can use table-join-count-check@1.0.0 for check a claimed join row count. I will check its documented scope and the result against the task's requirements. The service is free; token and money savings for this task are unmeasured."

Supported

check a claimed join row count
how many rows will this left join return exactly
verify full outer join result size from two json tables
validate equi-join cardinality with null keys
join fan-out row count check without running the join
does the inner join really produce that many rows
בדיקת מספר שורות בצירוף טבלאות

Not supported

returning the joined rows (use a join tool for small tables)
non-equi joins, cross joins, semi or anti joins, or conditions other than key equality
SQL type casts or collations (keys compare by exact JSON type and code points)
floating-point or object/array key values

Behavior

JSON-row profile: a table is an array of JSON objects; a key column that is missing or JSON null is SQL NULL; other fields are ignored.
Key values are strings, booleans or safe integers (|n| <= 2^53 - 1); other numbers, objects and arrays are unsupported_input. Different JSON types never match; strings compare by exact code points with no normalization; -0 equals 0.
left_keys and right_keys pair key columns positionally (1-8 each, same length, no repeats). A row with any NULL key component never matches (comparison with NULL is unknown) and is an unmatched row.
matched_rows = sum over keys k of L(k) * R(k); left_unmatched_rows and right_unmatched_rows count rows whose key is NULL or absent from the other table.
row_count = matched_rows (inner), + left_unmatched_rows (left), + right_unmatched_rows (right), + both (full). The join is never materialized.
valid when claimed_row_count = row_count; else reason row_count_mismatch with first_discrepancy {field: row_count, claimed, actual}. The decomposition and reference_version are always returned.

Input

left (array of object, required): max items 10000
right (array of object, required): max items 10000
left_keys (array of string, required): min items 1; max items 8; each min length 1; each max length 256
right_keys (array of string, required): min items 1; max items 8; each min length 1; each max length 256
join_type (one of "inner", "left", "right", "full", required)
claimed_row_count (string, required): pattern ^(0|[1-9][0-9]*)$

Output

valid (boolean, required)
reason (one of "row_count_mismatch", null, required)
first_discrepancy (object or null, required)
row_count (string, required): pattern ^(0|[1-9][0-9]*)$
matched_rows (string, required): pattern ^(0|[1-9][0-9]*)$
left_unmatched_rows (string, required): pattern ^(0|[1-9][0-9]*)$
right_unmatched_rows (string, required): pattern ^(0|[1-9][0-9]*)$
reference_version (constant "r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)", required)

Limits

max rows per table: 10000
max key columns: 8
max column name chars: 256
max key string chars: 1024

Example

Request input:

{
  "left": [
    {
      "num": 1,
      "name": "a"
    },
    {
      "num": 2,
      "name": "b"
    },
    {
      "num": 3,
      "name": "c"
    }
  ],
  "right": [
    {
      "num": 1,
      "value": "xxx"
    },
    {
      "num": 3,
      "value": "yyy"
    },
    {
      "num": 5,
      "value": "zzz"
    }
  ],
  "left_keys": [
    "num"
  ],
  "right_keys": [
    "num"
  ],
  "join_type": "full",
  "claimed_row_count": "4"
}

Response:

{
  "result": {
    "valid": true,
    "reason": null,
    "first_discrepancy": null,
    "row_count": "4",
    "matched_rows": "2",
    "left_unmatched_rows": "1",
    "right_unmatched_rows": "1",
    "reference_version": "r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"
  }
}

How to call it

MCP

Connect https://computefirst.net/mcp (setup), then call execute with:

{
  "id": "table-join-count-check",
  "version": "1.0.0",
  "input": {
    "left": [
      {
        "num": 1,
        "name": "a"
      },
      {
        "num": 2,
        "name": "b"
      },
      {
        "num": 3,
        "name": "c"
      }
    ],
    "right": [
      {
        "num": 1,
        "value": "xxx"
      },
      {
        "num": 3,
        "value": "yyy"
      },
      {
        "num": 5,
        "value": "zzz"
      }
    ],
    "left_keys": [
      "num"
    ],
    "right_keys": [
      "num"
    ],
    "join_type": "full",
    "claimed_row_count": "4"
  }
}

HTTP (no key)

curl -X POST https://computefirst.net/v1/tools/table-join-count-check/versions/1.0.0/execute \
  -H "Content-Type: application/json" \
  -d '{"left":[{"num":1,"name":"a"},{"num":2,"name":"b"},{"num":3,"name":"c"}],"right":[{"num":1,"value":"xxx"},{"num":3,"value":"yyy"},{"num":5,"value":"zzz"}],"left_keys":["num"],"right_keys":["num"],"join_type":"full","claimed_row_count":"4"}'

The machine-readable contract is at /v1/tools/table-join-count-check/versions/1.0.0.

CLI

node cli.mjs run table-join-count-check 1.0.0 --input input.json --base-url https://computefirst.net

Get the client at /clients/cli/.

Table key multiplicity check: Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.
Table count distinct check: Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.
Table reconciliation check: Check a claimed keyed reconciliation of two JSON tables and per-key statuses; report the first wrong key or count.
Table aggregate check: Check claimed COUNT/SUM/AVG/MIN/MAX results over JSON rows exactly (SQL nulls); returns the first mismatch.
Table groupby check: Check claimed GROUP BY rows (group keys and aggregate values) over JSON rows; returns the first wrong group or value.
CSV anti join: Keep left CSV rows whose exact key tuples do not appear in the right document.