-- ============================================================================ -- S0 STEP 2A QUALITY · VERIFY -- -- 执行契约（AutoVersionUpdate.RunVerifyScriptIfExists）： -- 每条语句经 SqlQuerySingle 取首行首列并要求 IsTruthy == true。因此： -- ① 禁止 SET @var（返回 null 即判失败）→ 租户一律内联子查询，每条语句自包含 -- ② 每条语句返回「通过为真」的标量（用 = 比较得 1/0），不得返回「错误条数」 -- ③ 一条语句一个断言，不用多行 UNION 汇总 -- ④ 文件必须以最后一条 SQL 的 ; 结束，末尾不得残留 comment-only 块 -- -- ⚠️ 作用域边界（CTO 2026-08-28 修正）： -- 本文件**不得**断言「全物理表不存在 tenant_id IN (0,1,1300000000001)」—— -- 默认租户存在 47 行历史 [AI测试] 质量数据、797 侧 qms_gcjygf 另有 3 行， -- 均属另批 S0-DEFAULT-TENANT-SEED-CONTAMINATION-CLEANUP-1，本批 DO NOT TOUCH。 -- §4 改为 TARGET PAYLOAD SCOPE：只验证本批 payload 在 @dst 精确存在、且未错落他租户。 -- 源租户 / 默认租户 MUTATION = 0 由 PRE/POST snapshot 证明，不进本永久契约。 -- ============================================================================ -- --------------------------------------------------------------------------- -- §1 目标计数（14 条） -- --------------------------------------------------------------------------- SELECT COUNT(*) = 5 FROM qms_inspection_method WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 5 FROM qms_inspectionitems WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 5 FROM qms_inspectioninstru WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 4 FROM qms_inspectionfreq WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 4 FROM qms_sampscheme WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 4 FROM qms_quality_base_type WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM qms_inspectioncrit WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM qms_inspectionstd WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 4 FROM qms_inspectpro WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 7 FROM qms_jygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 5 FROM qms_gcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM qms_fqcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 3 FROM qms_oqcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 4 FROM qms_lymjbmd WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); -- --------------------------------------------------------------------------- -- §2 FK 闭合（6 条） -- --------------------------------------------------------------------------- -- 2.1 items → method 无悬空 SELECT COUNT(*) = 0 FROM qms_inspectionitems i LEFT JOIN qms_inspection_method m ON m.id = i.FCHECKMETHOD AND m.tenant_id = i.tenant_id WHERE i.tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND i.FCHECKMETHOD IS NOT NULL AND m.id IS NULL; -- 2.2 items → method 已映射 5 条，且指向同租户 method SELECT COUNT(*) = 5 FROM qms_inspectionitems i JOIN qms_inspection_method m ON m.id = i.FCHECKMETHOD AND m.tenant_id = i.tenant_id WHERE i.tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); -- 2.3 语义正确：QI-001→QM-003 · QI-002→QM-001 · QI-003→QM-003 · QI-004→QM-005 · QI-005→QM-001 SELECT COUNT(*) = 5 FROM qms_inspectionitems i JOIN qms_inspection_method m ON m.id = i.FCHECKMETHOD AND m.tenant_id = i.tenant_id WHERE i.tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CONCAT(i.FNUMBER,'>',m.FNUMBER) IN ('QI-001>QM-003','QI-002>QM-001','QI-003>QM-003','QI-004>QM-005','QI-005>QM-001'); -- 2.4 jygf / gcjygf / fqcjygf / oqcjygf → ItemMaster 无悬空 SELECT COUNT(*) = 0 FROM (SELECT wlbm, tenant_id FROM qms_jygf UNION ALL SELECT wlbm, tenant_id FROM qms_gcjygf UNION ALL SELECT wlbm, tenant_id FROM qms_fqcjygf UNION ALL SELECT wlbm, tenant_id FROM qms_oqcjygf) q LEFT JOIN ItemMaster i ON i.tenant_id = q.tenant_id AND i.ItemNum = q.wlbm WHERE q.tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND q.wlbm IS NOT NULL AND i.RecID IS NULL; -- 2.5 lymjbmd → SuppMaster 无悬空 SELECT COUNT(*) = 0 FROM qms_lymjbmd l LEFT JOIN SuppMaster s ON s.tenant_id = l.tenant_id AND s.Supp = l.gysbm WHERE l.tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND l.gysbm IS NOT NULL AND s.RecID IS NULL; -- 2.6 lymjbmd → ItemMaster 无悬空 SELECT COUNT(*) = 0 FROM qms_lymjbmd l LEFT JOIN ItemMaster i ON i.tenant_id = l.tenant_id AND i.ItemNum = l.wlbm WHERE l.tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND l.wlbm IS NOT NULL AND i.RecID IS NULL; -- --------------------------------------------------------------------------- -- §3 组织引用改写（3 条）：目标租户内不得残留 AIDOP 根组织 Id -- --------------------------------------------------------------------------- SELECT COUNT(*) = 0 FROM qms_inspectioncrit WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FCREATEORGID = (SELECT t2.OrgId FROM SysTenant t2 JOIN SysOrg o2 ON o2.Id=t2.OrgId WHERE o2.Code='AIDOP' AND t2.Status=1 LIMIT 1); SELECT COUNT(*) = 0 FROM qms_inspectionstd WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FCREATEORGID = (SELECT t2.OrgId FROM SysTenant t2 JOIN SysOrg o2 ON o2.Id=t2.OrgId WHERE o2.Code='AIDOP' AND t2.Status=1 LIMIT 1); SELECT COUNT(*) = 0 FROM qms_inspectpro WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND (FCREATEORGID = (SELECT t2.OrgId FROM SysTenant t2 JOIN SysOrg o2 ON o2.Id=t2.OrgId WHERE o2.Code='AIDOP' AND t2.Status=1 LIMIT 1) OR FUSEORGID = (SELECT t3.OrgId FROM SysTenant t3 JOIN SysOrg o3 ON o3.Id=t3.OrgId WHERE o3.Code='AIDOP' AND t3.Status=1 LIMIT 1)); -- --------------------------------------------------------------------------- -- §4 TARGET PAYLOAD SCOPE（14 条） -- 只验证「本批 payload 在 @dst 精确存在」+「CONSTRUCT payload 未错落他租户」。 -- COPY 类业务键在源租户 797 本就合法存在，故不做「全库唯一」断言。 -- --------------------------------------------------------------------------- SELECT COUNT(*) = 5 FROM qms_inspection_method WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('QM-001','QM-002','QM-003','QM-004','QM-005'); SELECT COUNT(*) = 5 FROM qms_inspectionitems WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('QI-001','QI-002','QI-003','QI-004','QI-005'); SELECT COUNT(*) = 5 FROM qms_inspectioninstru WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('QG-001','QG-002','QG-003','QG-004','QG-005'); SELECT COUNT(*) = 4 FROM qms_inspectionfreq WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('JYPC-001','JYPC-002','JYPC-003','JYPC-004'); SELECT COUNT(*) = 4 FROM qms_sampscheme WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('QS-001','QS-002','QS-003','QS-004'); SELECT COUNT(*) = 4 FROM qms_quality_base_type WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CONCAT(type_category,'|',type_code) IN ('检验类型|IQC','检验类型|PQC','不良分类|制成不良','判定结论|合格'); SELECT COUNT(*) = 3 FROM qms_inspectioncrit WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('JYYJ-001','JYYJ-002','JYYJ-003'); SELECT COUNT(*) = 3 FROM qms_inspectionstd WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('JYBZ-001','JYBZ-002','JYBZ-003'); SELECT COUNT(*) = 4 FROM qms_inspectpro WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND FNUMBER IN ('PLAN-FQC-001','PLAN-IQC-001','PLAN-OQC-001','PLAN-PQC-001'); -- jygf：7 行且 Y101-1-11 的 A.1 / X.1 两版必须共存 SELECT COUNT(*) = 7 FROM qms_jygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND CONCAT(wjbh,'|',bb) IN ('F03-1-21|A.0','F101-1-39|A.0','M05-1-19|A.4','U01-1-63|A.1','Y101-1-11|A.1','Y101-1-11|X.1','Y101-1-36|X.1'); SELECT COUNT(*) = 2 FROM qms_jygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND wjbh = 'Y101-1-11' AND bb IN ('A.1','X.1'); -- CONSTRUCT payload 必须只出现在 @dst SELECT COUNT(*) = 0 FROM qms_gcjygf WHERE wjbh LIKE 'UATD-GC-%' AND tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 0 FROM qms_fqcjygf WHERE wjbh LIKE 'UATD-FQC-%' AND tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 0 FROM qms_oqcjygf WHERE wjbh LIKE 'UATD-OQC-%' AND tenant_id <> (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); -- --------------------------------------------------------------------------- -- §5 逻辑业务键唯一性（14 条） -- DB 层这 14 张表**没有任何业务唯一键**，本节是唯一的重复防线。 -- --------------------------------------------------------------------------- SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspection_method WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) a; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectionitems WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) b; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectioninstru WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) c; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectionfreq WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) d; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_sampscheme WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) e; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectioncrit WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) f; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectionstd WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) g; SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectpro WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY FNUMBER HAVING COUNT(*) > 1) h; SELECT COUNT(*) = 0 FROM (SELECT type_category, type_code FROM qms_quality_base_type WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY type_category, type_code HAVING COUNT(*) > 1) i; SELECT COUNT(*) = 0 FROM (SELECT wjbh, bb FROM qms_jygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY wjbh, bb HAVING COUNT(*) > 1) j; SELECT COUNT(*) = 0 FROM (SELECT wjbh FROM qms_gcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY wjbh HAVING COUNT(*) > 1) k; SELECT COUNT(*) = 0 FROM (SELECT wjbh FROM qms_fqcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY wjbh HAVING COUNT(*) > 1) l; SELECT COUNT(*) = 0 FROM (SELECT wjbh FROM qms_oqcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY wjbh HAVING COUNT(*) > 1) m; SELECT COUNT(*) = 0 FROM (SELECT dim_type, IFNULL(gysbm,'') g2, IFNULL(wlbm,'') w2 FROM qms_lymjbmd WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) GROUP BY dim_type, g2, w2 HAVING COUNT(*) > 1) n; -- --------------------------------------------------------------------------- -- §6 污染隔离（2 条）：目标租户内不得出现历史 [AI测试] 痕迹 -- --------------------------------------------------------------------------- SELECT COUNT(*) = 0 FROM qms_inspectionitems WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND (FNUMBER LIKE 'AI-%' OR FNAME LIKE '%AI测试%'); SELECT COUNT(*) = 0 FROM qms_gcjygf WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1) AND (syxh LIKE '%AI测试%' OR fj LIKE '%AI测试%' OR wlbm LIKE '%AI测试%'); -- --------------------------------------------------------------------------- -- §7 回归（3 条）：本批不得触碰的既有 S0 主数据 -- --------------------------------------------------------------------------- SELECT COUNT(*) = 5 FROM CustMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 7 FROM DepartmentMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1); SELECT COUNT(*) = 56 FROM ItemMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1);