Can't find column tonder_test.tmm.check_status in schema Column: [tonder_test.tmci.company_info_id,tonder_test.tmci.mechanism_id] Unique key: [[tonder_test.tmci.company_info_id]]

[AndyHoo]

select count(distinct tmci.company_info_id)
        from tc_mch_company_info tmci
        left join tc_mch_merchant tmm on tmm.company_info_id=tmci.company_info_id
        left join `tc_mch_merchant_report` tmmr ON tmci.`company_info_id` = tmmr.`company_info_id`
        left join (
            select GROUP_CONCAT(tag.name) as tagsName, GROUP_CONCAT(tag.id) as tagsId, tagCompany.`company_info_id` as companyInfoId
            from `tc_mch_tag_company` tagCompany
            left join `tc_mch_tag` tag on tagCompany.`tag_id` = tag.id group by tagCompany.`company_info_id`
            ) companyTag on tmci.`company_info_id` = companyTag.companyInfoId
        left join tc_mch_agents tma on tma.agent_id=tmm.agent_id       
        where tmci.mechanism_id='1188'
        and ((tmm.`check_status` = 1 and tmm.merchant_type != 1) or tmm.`merchant_id` is null)

执行上面那条sql的时候报

Can’t find column tonder_test.tmm.check_status in schema Column: [tonder_test.tmci.company_info_id,tonder_test.tmci.mechanism_id] Unique key: [[tonder_test.tmci.company_info_id]]请大牛们帮忙看看

[不懂就问 (zhouyueyue)]

提供下版本号，看着是一个已知问题。

[AndyHoo]

5.7.25-TiDB-v3.0.1

[不懂就问 (zhouyueyue)]

select count(distinct tmci.company_info_id) ,count( tmm.company_info )from tc_mch_company_info tmci left join tc_mch_merchant tmm on tmm.company_info_id=tmci.company_info_id left join tc_mch_merchant_report tmmr ON tmci.company_info_id = tmmr.company_info_id left join ( select GROUP_CONCAT(tag.name) as tagsName, GROUP_CONCAT(tag.id) as tagsId, tagCompany.company_info_id as companyInfoId from tc_mch_tag_company tagCompany left join tc_mch_tag tag on tagCompany.tag_id = tag.id group by tagCompany.company_info_id ) companyTag on tmci.company_info_id = companyTag.companyInfoId left join tc_mch_agents tma on tma.agent_id=tmm.agent_id where tmci.mechanism_id=‘1188’ and ((tmm.check_status = 1 and tmm.merchant_type != 1) or tmm.merchant_id is null)

使用这种方式试下

[AndyHoo]

我看了下，这种方式是可以的，count( tmm.company_info ) 这个貌似随便改成其他的count(1),或者count(*)都是可以的，为什么这样改就可以呢，请大佬指教

[不懂就问 (zhouyueyue)]

[问题澄清]

TiDB集群版本： v3.0.1

问题描述：执行sql过程中报错Can’t find column tonder_test.tmm.check_status in schema Column: [tonder_test.tmci.company_info_id,tonder_test.tmci.mechanism_id] Unique key: [[tonder_test.tmci.company_info_id]]

[问题分析]

这是版本的一个已知问题，在 3.0.2 修复了

[解决方案]

目前的版本通过以下方式能规避掉问题：

select count(distinct tmci.company_info_id) ,count( tmm.company_info )from tc_mch_company_info tmci left join tc_mch_merchant tmm on tmm.company_info_id=tmci.company_info_id left join tc_mch_merchant_report tmmr ON tmci. company_info_id = tmmr. company_info_id left join ( select GROUP_CONCAT(tag.name) as tagsName, GROUP_CONCAT(tag.id) as tagsId, tagCompany. company_info_id as companyInfoId from tc_mch_tag_company tagCompany left join tc_mch_tag tag on tagCompany. tag_id = tag.id group by tagCompany. company_info_id ) companyTag on tmci. company_info_id = companyTag.companyInfoId left join tc_mch_agents tma on tma.agent_id=tmm.agent_id where tmci.mechanism_id=‘1188’ and ((tmm. check_status = 1 and tmm.merchant_type != 1) or tmm. merchant_id is null)

[问题根因]

TIDB 3.0.1版本bug