Column *** is ambiguous

🪐 TiDB 技术问题 性能调优
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：v3.0.1
  • 【问题描述】：
SELECT    IF(t1.merchant_id="",t1.agent_id,t1.merchant_id)     id,
          '2019-12-04'                                         AS DATE,
          t1.agent_id                                          AS agentId,
          t1.merchant_id                                       AS merchantId,
          t1.platform_user_type                                AS platformUserType ,
          Ifnull(Round(t1.balance_amount   / 100, 2), 0)       AS balance ,
          Ifnull(Round(t2.available_amount / 100, 2), 0)       AS availableAdvanceAmount ,
          Ifnull(Round(t2.used_amount      / 100, 2), 0)       AS usedAdvance ,
          Ifnull(Round(t2.returned_amount  / 100, 2), 0)       AS returnedAdvance ,
          Ifnull(Round(t4.cle_amount       / 100, 2), 0)       AS cleAmount ,
          IF(t1.platform_user_type=1, t5.merchant_name, '')    AS merchantName ,
          IF(t1.platform_user_type=1, t5.merchant_type, -1)    AS merchantType ,
          IF(t1.agent_id='0','技术服务商', t6.agent_name) AS agentName
FROM      tc_account_balance t1
LEFT JOIN tc_account_advance t2
ON        t2.mechanism_id = t1.mechanism_id
AND       t1.agent_id = t2.agent_id
AND       t1.merchant_id = t2.merchant_id
AND       t2.date='2019-12-04'
LEFT JOIN tc_cle_bill t4
ON        t4.mechanism_id = t1.mechanism_id
AND       t1.agent_id=t4.agent_id
AND       t1.merchant_id=t4.merchant_id
AND       t4.date='2019-12-04'
LEFT JOIN tc_mch_merchant t5
ON        t1.merchant_id = t5.merchant_id
AND       t1.agent_id = t5.agent_id
LEFT JOIN tc_mch_agents t6
ON        t1.agent_id = t6.agent_id
WHERE     t1.mechanism_id = '1188'
AND       (
                    t5.is_independent_settle IS NULL
          OR        t5.is_independent_settle = 1)
AND       t1.platform_user_type IN (1,2)
ORDER BY  balance DESC
LIMIT     0 , 10;

执行正常，但是加上一个条件 AND t1.balance_amount > 0

SELECT    IF(t1.merchant_id="",t1.agent_id,t1.merchant_id)     id,
          '2019-12-04'                                         AS DATE,
          t1.agent_id                                          AS agentId,
          t1.merchant_id                                       AS merchantId,
          t1.platform_user_type                                AS platformUserType ,
          Ifnull(Round(t1.balance_amount   / 100, 2), 0)       AS balance ,
          Ifnull(Round(t2.available_amount / 100, 2), 0)       AS availableAdvanceAmount ,
          Ifnull(Round(t2.used_amount      / 100, 2), 0)       AS usedAdvance ,
          Ifnull(Round(t2.returned_amount  / 100, 2), 0)       AS returnedAdvance ,
          Ifnull(Round(t4.cle_amount       / 100, 2), 0)       AS cleAmount ,
          IF(t1.platform_user_type=1, t5.merchant_name, '')    AS merchantName ,
          IF(t1.platform_user_type=1, t5.merchant_type, -1)    AS merchantType ,
          IF(t1.agent_id='0','技术服务商', t6.agent_name) AS agentName
FROM      tc_account_balance t1
LEFT JOIN tc_account_advance t2
ON        t2.mechanism_id = t1.mechanism_id
AND       t1.agent_id = t2.agent_id
AND       t1.merchant_id = t2.merchant_id
AND       t2.date='2019-12-04'
LEFT JOIN tc_cle_bill t4
ON        t4.mechanism_id = t1.mechanism_id
AND       t1.agent_id=t4.agent_id
AND       t1.merchant_id=t4.merchant_id
AND       t4.date='2019-12-04'
LEFT JOIN tc_mch_merchant t5
ON        t1.merchant_id = t5.merchant_id
AND       t1.agent_id = t5.agent_id
LEFT JOIN tc_mch_agents t6
ON        t1.agent_id = t6.agent_id
WHERE     t1.mechanism_id = '1188'
AND       (
                    t5.is_independent_settle IS NULL
          OR        t5.is_independent_settle = 1)
AND       t1.platform_user_type IN (1,2) 
AND       t1.balance_amount > 0
ORDER BY  balance DESC,
          t1.id DESC
LIMIT     0 , 10;

就会报

t4.date is ambiguous

烦请大佬帮忙看一下，能否在不升级版本的情况下解决这个问题

2

您好： 方便把建表语句给我测试一下吗？

3

我已经发现问题了，order by字段(balance)相同value的记录跨页分布时,分页查询产生重复数据的bug,直接order by balance desc, t1.id desc会报t4.date is ambiguous,如果把t1.id在projection中查询出来并且取上别名tid，然后再order by balance DESC,
tid DESC

改为下面这样就可以了:

SELECT    
           t1.id as tid,
          IF(t1.merchant_id="",t1.agent_id,t1.merchant_id)     id,
          '2019-12-04'                                         AS DATE,
          t1.agent_id                                          AS agentId,
          t1.merchant_id                                       AS merchantId,
          t1.platform_user_type                                AS platformUserType ,
          Ifnull(Round(t1.balance_amount   / 100, 2), 0)       AS balance ,
          Ifnull(Round(t2.available_amount / 100, 2), 0)       AS availableAdvanceAmount ,
          Ifnull(Round(t2.used_amount      / 100, 2), 0)       AS usedAdvance ,
          Ifnull(Round(t2.returned_amount  / 100, 2), 0)       AS returnedAdvance ,
          Ifnull(Round(t4.cle_amount       / 100, 2), 0)       AS cleAmount ,
          IF(t1.platform_user_type=1, t5.merchant_name, '')    AS merchantName ,
          IF(t1.platform_user_type=1, t5.merchant_type, -1)    AS merchantType ,
          IF(t1.agent_id='0','技术服务商', t6.agent_name) AS agentName
FROM      tc_account_balance t1
LEFT JOIN tc_account_advance t2
ON        t2.mechanism_id = t1.mechanism_id
AND       t1.agent_id = t2.agent_id
AND       t1.merchant_id = t2.merchant_id
AND       t2.date='2019-12-04'
LEFT JOIN tc_cle_bill t4
ON        t4.mechanism_id = t1.mechanism_id
AND       t1.agent_id=t4.agent_id
AND       t1.merchant_id=t4.merchant_id
AND       t4.date='2019-12-04'
LEFT JOIN tc_mch_merchant t5
ON        t1.merchant_id = t5.merchant_id
AND       t1.agent_id = t5.agent_id
LEFT JOIN tc_mch_agents t6
ON        t1.agent_id = t6.agent_id
WHERE     t1.mechanism_id = '1188'
AND       (
                    t5.is_independent_settle IS NULL
          OR        t5.is_independent_settle = 1)
AND       t1.platform_user_type IN (1,2) 
AND       t1.balance_amount > 0
ORDER BY  balance DESC,
          tid DESC
LIMIT     0 , 10;

谢谢大佬

4

好的，厉害

5

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。