Table key multiplicity check

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

Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.

Use when you need to: verify per-key duplicate counts across two tables · check which join key causes the row explosion · validate key multiplicities claimed for a join.

Decide before calling

Read the versioned contract and the supported scope below. Reuse table-key-multiplicity-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-key-multiplicity-check@1.0.0 for verify per-key duplicate counts across two tables. 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

verify per-key duplicate counts across two tables
check which join key causes the row explosion
validate key multiplicities claimed for a join
per key contribution to a left or full join
are these duplicate key counts correct
check null key row counts before a join
בדיקת ריבוי מפתחות בטבלאות

Not supported

producing the per-key table without a claim (it only verifies)
grouping NULL keys together (NULL keys are counted apart as never-matching rows)
type casts between strings, numbers and booleans
non-equi or cross joins

Behavior

JSON-row profile: a table is an array of JSON objects; a key column that is missing or JSON null is SQL NULL. Key values are strings, booleans or safe integers; other numbers, objects and arrays are unsupported_input; different JSON types never match; -0 equals 0 and is reported as 0.
claims must list every non-null key occurring in either table exactly once (a repeated key is invalid_input); each claim key has one non-null value per key column. null_key_rows gives the claimed left and right counts of rows with a NULL key component.
Per key with L left rows and R right rows, joined_rows is L*R for inner; L*R if R > 0 else L for left; L*R if L > 0 else R for right; L*R if both > 0 else L + R for full.
Check order: claims in array order (presence: key_not_in_tables when L + R = 0; then left_count, right_count, joined_rows), then unclaimed keys in first-appearance order (left rows, then right rows: key_missing_from_claim), then null_key_rows.left, then null_key_rows.right (null_key_rows_mismatch).
first_discrepancy gives claim_index (null for a missing key or null_key_rows), key (null for null_key_rows), field, claimed (null for a missing key) and actual (L + R for presence). distinct_keys counts distinct non-null keys; reference_version is 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)
claims (array of object, required): max items 20000
null_key_rows (object, required)

Output

valid (boolean, required)
reason (one of "key_not_in_tables", "left_count_mismatch", "right_count_mismatch", "joined_rows_mismatch", "key_missing_from_claim", "null_key_rows_mismatch", null, required)
first_discrepancy (object or null, required)
distinct_keys (string, required): pattern ^(0|[1-9][0-9]*)$
reference_version (constant "r59-key-multiplicity-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
max claims: 20000

Example

Request input:

{
  "left": [
    {
      "num": 1
    },
    {
      "num": 2
    },
    {
      "num": 3
    }
  ],
  "right": [
    {
      "num": 1
    },
    {
      "num": 3
    },
    {
      "num": 5
    }
  ],
  "left_keys": [
    "num"
  ],
  "right_keys": [
    "num"
  ],
  "join_type": "left",
  "claims": [
    {
      "key": [
        1
      ],
      "left_count": "1",
      "right_count": "1",
      "joined_rows": "1"
    },
    {
      "key": [
        2
      ],
      "left_count": "1",
      "right_count": "0",
      "joined_rows": "1"
    },
    {
      "key": [
        3
      ],
      "left_count": "1",
      "right_count": "1",
      "joined_rows": "1"
    },
    {
      "key": [
        5
      ],
      "left_count": "0",
      "right_count": "1",
      "joined_rows": "0"
    }
  ],
  "null_key_rows": {
    "left": "0",
    "right": "0"
  }
}

Response:

{
  "result": {
    "valid": true,
    "reason": null,
    "first_discrepancy": null,
    "distinct_keys": "4",
    "reference_version": "r59-key-multiplicity-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-key-multiplicity-check",
  "version": "1.0.0",
  "input": {
    "left": [
      {
        "num": 1
      },
      {
        "num": 2
      },
      {
        "num": 3
      }
    ],
    "right": [
      {
        "num": 1
      },
      {
        "num": 3
      },
      {
        "num": 5
      }
    ],
    "left_keys": [
      "num"
    ],
    "right_keys": [
      "num"
    ],
    "join_type": "left",
    "claims": [
      {
        "key": [
          1
        ],
        "left_count": "1",
        "right_count": "1",
        "joined_rows": "1"
      },
      {
        "key": [
          2
        ],
        "left_count": "1",
        "right_count": "0",
        "joined_rows": "1"
      },
      {
        "key": [
          3
        ],
        "left_count": "1",
        "right_count": "1",
        "joined_rows": "1"
      },
      {
        "key": [
          5
        ],
        "left_count": "0",
        "right_count": "1",
        "joined_rows": "0"
      }
    ],
    "null_key_rows": {
      "left": "0",
      "right": "0"
    }
  }
}

HTTP (no key)

curl -X POST https://computefirst.net/v1/tools/table-key-multiplicity-check/versions/1.0.0/execute \
  -H "Content-Type: application/json" \
  -d '{"left":[{"num":1},{"num":2},{"num":3}],"right":[{"num":1},{"num":3},{"num":5}],"left_keys":["num"],"right_keys":["num"],"join_type":"left","claims":[{"key":[1],"left_count":"1","right_count":"1","joined_rows":"1"},{"key":[2],"left_count":"1","right_count":"0","joined_rows":"1"},{"key":[3],"left_count":"1","right_count":"1","joined_rows":"1"},{"key":[5],"left_count":"0","right_count":"1","joined_rows":"0"}],"null_key_rows":{"left":"0","right":"0"}}'

The machine-readable contract is at /v1/tools/table-key-multiplicity-check/versions/1.0.0.

CLI

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

Get the client at /clients/cli/.

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