我正在经历Redshift的异常行为,其中带有内部联接的基本查询刚刚按预期停止工作。
在下面提供查询
SELECT D.asin,D.client_account_id
FROM ams.t_asin_segmentation_detail A_0
INNER JOIN ams.t_asin_segmentation_value B
ON A_0.ASIN_SEGMENTATION_VALUE_ID = B.ID AND A_0.IS_DELETED = 'N' AND A_0.IS_actIVE = 'Y' AND
B.IS_DELETED = 'N' AND B.IS_actIVE = 'Y' AND B.ID IN (900)
INNER JOIN ams.t_asin_segmentation_type C
ON B.ASIN_SEGMENTATION_TYPE_ID = C.ID AND C.IS_DELETED = 'N' AND C.IS_actIVE = 'Y' AND
C.ID = 1687 AND C.BUSInesS_UNIT_ID = 15
INNER JOIN ams.t_asin D ON A_0.asin_id = D.ID AND D.is_active = 'Y' and D.is_deleted = 'N';
此查询仅返回不正确的一行。它应该返回超过1000行。
当我用不同的方式编写相同的查询时,只需删除表别名为D的最后一个JOIN并以这种方式编写,
SELECT asin,client_account_id
FROM ams.t_asin
WHERE id IN (SELECT DISTINCT A_0.asin_id
FROM ams.t_asin_segmentation_detail A_0
INNER JOIN ams.t_asin_segmentation_value B
ON A_0.ASIN_SEGMENTATION_VALUE_ID = B.ID AND A_0.IS_DELETED = 'N' AND
A_0.IS_actIVE = 'Y' AND
B.IS_DELETED = 'N' AND B.IS_actIVE = 'Y' AND B.ID IN (900)
INNER JOIN ams.t_asin_segmentation_type C
ON B.ASIN_SEGMENTATION_TYPE_ID = C.ID AND C.IS_DELETED = 'N' AND C.IS_actIVE = 'Y' AND
C.ID = 1687 AND C.BUSInesS_UNIT_ID = 15)
AND is_active = 'Y'
AND is_deleted = 'N';
这将开始获取正确的行数,而查询本身没有任何逻辑更改。
我进一步调整了原始查询,只是用别名为D的表将INNER JOIN替换为LEFT JOIN,令人惊讶的是,它开始提供正确的行数。重要的是要注意,即使使用LEFT JOIN,SELECT语句仍从别名为D的表中获取列,如下所示,
SELECT D.asin,D.client_account_id
FROM ams.t_asin_segmentation_detail A_0
INNER JOIN ams.t_asin_segmentation_value B
ON A_0.ASIN_SEGMENTATION_VALUE_ID = B.ID AND A_0.IS_DELETED = 'N' AND A_0.IS_actIVE = 'Y' AND
B.IS_DELETED = 'N' AND B.IS_actIVE = 'Y' AND B.ID IN (900)
INNER JOIN ams.t_asin_segmentation_type C
ON B.ASIN_SEGMENTATION_TYPE_ID = C.ID AND C.IS_DELETED = 'N' AND C.IS_actIVE = 'Y' AND
C.ID = 1687 AND C.BUSInesS_UNIT_ID = 15
LEFT JOIN ams.t_asin D ON A_0.asin_id = D.ID AND D.is_active = 'Y' and D.is_deleted = 'N';
如果有人能提供任何理由,将不胜感激。