# 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](/v1/tools/table-key-multiplicity-check/versions/1.0.0) 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:

```json
{
  "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:

```json
{
  "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](/docs#connect)), then call `execute` with:

```json
{
  "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)

```sh
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](/v1/tools/table-key-multiplicity-check/versions/1.0.0).

### CLI

```sh
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/](/clients/cli/).

## Related tools

- [Table join count check](/tools/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](/tools/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](/tools/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](/tools/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](/tools/table-count-distinct-check): Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.
- [CSV anti join](/tools/csv-anti-join): Keep left CSV rows whose exact key tuples do not appear in the right document.