{"id":"table-key-multiplicity-check","version":"1.0.0","description":"Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.","supported_operations":["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","בדיקת ריבוי מפתחות בטבלאות"],"unsupported_operations":["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"],"semantics":["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."],"limits":{"max_rows_per_table":10000,"max_key_columns":8,"max_column_name_chars":256,"max_key_string_chars":1024,"max_claims":20000},"pricing":{"status":"unpriced","charge_usd":null},"input_schema":{"type":"object","additionalProperties":false,"required":["left","right","left_keys","right_keys","join_type","claims","null_key_rows"],"properties":{"left":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":{"description":"any JSON value"}}},"right":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":{"description":"any JSON value"}}},"left_keys":{"type":"array","minItems":1,"maxItems":8,"items":{"type":"string","minLength":1,"maxLength":256}},"right_keys":{"type":"array","minItems":1,"maxItems":8,"items":{"type":"string","minLength":1,"maxLength":256}},"join_type":{"type":"string","enum":["inner","left","right","full"]},"claims":{"type":"array","maxItems":20000,"items":{"type":"object","additionalProperties":false,"required":["key","left_count","right_count","joined_rows"],"properties":{"key":{"type":"array","minItems":1,"maxItems":8,"items":{"type":["string","integer","boolean"]}},"left_count":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right_count":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"joined_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}}},"null_key_rows":{"type":"object","additionalProperties":false,"required":["left","right"],"properties":{"left":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}}}},"output_schema":{"type":"object","additionalProperties":false,"required":["valid","reason","first_discrepancy","distinct_keys","reference_version"],"properties":{"valid":{"type":"boolean"},"reason":{"type":["string","null"],"enum":["key_not_in_tables","left_count_mismatch","right_count_mismatch","joined_rows_mismatch","key_missing_from_claim","null_key_rows_mismatch",null]},"first_discrepancy":{"type":["object","null"],"additionalProperties":false,"required":["claim_index","key","field","claimed","actual"],"properties":{"claim_index":{"type":["integer","null"],"minimum":0},"key":{"type":["array","null"],"items":{"type":["string","integer","boolean"]}},"field":{"type":"string","enum":["presence","left_count","right_count","joined_rows","null_key_rows_left","null_key_rows_right"]},"claimed":{"type":["string","null"],"pattern":"^(0|[1-9][0-9]*)$"},"actual":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}},"distinct_keys":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"reference_version":{"type":"string","const":"r59-key-multiplicity-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"}}},"examples":[{"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"}},"output":{"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)"}},{"input":{"left":[{"k":7},{"k":7},{"k":7}],"right":[{"k":7},{"k":7},{"k":7},{"k":7}],"left_keys":["k"],"right_keys":["k"],"join_type":"inner","claims":[{"key":[7],"left_count":"3","right_count":"4","joined_rows":"7"}],"null_key_rows":{"left":"0","right":"0"}},"output":{"valid":false,"reason":"joined_rows_mismatch","first_discrepancy":{"claim_index":0,"key":[7],"field":"joined_rows","claimed":"7","actual":"12"},"distinct_keys":"1","reference_version":"r59-key-multiplicity-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"}}],"execute_url":"/v1/tools/table-key-multiplicity-check/versions/1.0.0/execute"}