sqle
sqle copied to clipboard
actiontech
规则问题优化
版本信息（Version）
v4.25.02
问题描述（Describe）
进行AI测试时发现的规则问题，以下一并列出 其他相关问题 https://github.com/actiontech/sqle/issues/2989 https://github.com/actiontech/sqle/issues/2966 https://github.com/actiontech/sqle/issues/2961 https://github.com/actiontech/sqle/issues/2960 https://github.com/actiontech/sqle/issues/2959 知识库遗留问题： http://10.186.18.21/sqle/rule-refactor/-/issues/10 http://10.186.18.21/sqle/rule-refactor/-/issues/11 http://10.186.18.21/sqle/rule-refactor/-/issues/13 http://10.186.18.21/sqle/rule-refactor/-/issues/14
- [ ] MySQL规则00176：期望以下sql触发规则，实际没有
SELECT /*+ FORCE INDEX (idx_totalSnatched) */ groupName FROM torrents ORDER BY totalSnatched LIMIT 1 - [ ] MySQL规则00092：期望以下sql不触发规则，实际触发了
DELETE u FROM Users u WHERE u.Id IN (\n SELECT p.OwnerUserId\n FROM Posts p\n INNER JOIN Votes v ON p.Id = v.PostId\n WHERE v.CreationDate BETWEEN '2012-01-01' AND '2012-01-31' AND v.VoteTypeId = 3\n LIMIT 1000\n) - [ ] MySQL规则00091: 期望以下sql不触发规则，实际触发了
SELECT ( (SELECT Sum(ie.amount) FROM inputevents_cv ie JOIN icustays cs ON ie.icustay_id = cs.icustay_id JOIN admissions ad ON cs.icustay_id = ad.icustay_id WHERE ad.subject_id = 4401 AND Year(ie.charttime) = Year(Curdate()) AND Month(ie.charttime) = Month(Curdate()) AND Date_format(ie.charttime, '%d') = '30') - (SELECT Sum(oe.value) FROM outputevents oe JOIN icustays cs ON oe.icustay_id = cs.icustay_id JOIN admissions ad ON cs.icustay_id = ad.icustay_id WHERE ad.subject_id = 4401 AND Year(oe.charttime) = Year(Curdate()) AND Month(oe.charttime) = Month(Curdate()) AND Date_format(oe.charttime, '%d') = '30') ) - [ ] MySQL规则00128: 期望以下sql不触发规则，实际触发了。期望修改规则的判断逻辑，对于having中涉及本sql新产生的聚合判断如以下SQL中的count()，由于无法移动到where条件中，不应该触发规则。
SELECT COUNT(*) AS QuestCount, P.OwnerUserId, U.DisplayName, U.Reputation, U.LastAccessDate FROM Posts P INNER JOIN Users U ON P.OwnerUserId = U.Id WHERE P.ParentId IS NULL AND (P.Body LIKE '%plz%' OR P.Body LIKE '%pls%' OR P.Body LIKE '%thx%' OR P.Body LIKE '%thnx%' OR P.Body LIKE '%smth%' OR P.Body LIKE '%sth%') AND U.DisplayName = 't3' GROUP BY P.OwnerUserId, U.DisplayName, U.Reputation, U.LastAccessDate HAVING COUNT(*) > 40 ORDER BY COUNT(*) DESC LIMIT 20
