Refine only-full-group-by checker

[xuyifangreeneyes]

Enhancement

TiDB's only-full-group-by checker is not so compatible with MySQL. The incompatibility brings trouble to users and the current workaround is to remove ONLY_FULL_GROUP_BY from sql_mode. We need to make only-full-group-by checker more compatible with MySQL.

Here are the issues related to only-full-group-by checker:

• SQL Mode only_full_group_by incorrectly errors with DISTINCT + ORDER BY #29418
• planner: full-group checking cannot handle case sensitivity correctly #27723
• The SQL statement that broke the limit of ‘only_full_gropy_by’ did not trigger the error normally #27539
• only_full_group_by check is not compatible with MySQL  #26576
• dml: TiDB rejected valid select statement due to only_full_group_by while MySQL don't #25199
• dml: Missing column name in error message for ERROR 1055 (only_full_group_by) #25196
• Unmatched error code with MySQL for a same error (only_full_group_by) #22694
• reject valid aggregated query in only_full_group_by mode #22301
• infer functional dependencies from filters / join conditions and use them for only_full_group_by check #25089
• planner: completely reject 'ORDER BY contains aggregate function and applies to the result of a non-aggregated query' #21848
• ORDER BY contains aggregate function and applies to the result of a non-aggregated query #30024

One cause for some issues is that only-full-group-by checker is not smart enough to infer some functional dependency(See #14580 for details).

[xuyifangreeneyes]

/label planner

[ti-chi-bot]

@xuyifangreeneyes: The label(s) planner cannot be applied. These labels are supported: challenge-program, compatibility-breaker, first-time-contributor, contribution, require-LGT3, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-3.0, needs-cherry-pick-3.1, needs-cherry-pick-4.0, needs-cherry-pick-5.0, needs-cherry-pick-5.1, needs-cherry-pick-5.2, needs-cherry-pick-5.3, affects-4.0, affects-5.0, affects-5.1, affects-5.2, affects-5.3, backport-4.0.14, backport-4.0.15, backport-5.0.3, backport-5.0.4, backport-5.1.0, backport-5.1.1, backport-5.1.2.

In response to this:

/label planner

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[xuyifangreeneyes]

/planner

[xuyifangreeneyes]

/sig planner

[winoros]

The design doc:
https://pingcap.feishu.cn/docs/doccndyrRWfcGALhjcyVhOrxWYd

[winoros]

Splitted working items

• Introducing the basic structure for the functional dependency. @AilinKid
• Implementing the basic inserting logic of strict functional dependency. @AilinKid
• Implementing the basic inserting logic of strict equiv constraint. @AilinKid
• Implementing the basic inserting logic of lax functional dependency. @AilinKid
• Implementing the maintain for DataSource. @AilinKid
• Implementing the maintain for Selection. @AilinKid
• Implementing the maintain for inner join. @winoros
• Implementing the maintain for outer join. @winoros
• Implementing the maintain for Aggregation. @AilinKid
• Implementing the maintain for Projection. @AilinKid
• Implementing the maintain for Union/Except/Intersect.
• Implementing the maintain for View/CTE.
• Integrated with the plan builder. @AilinKid
• Testing against the SQL mode. @AilinKid@winoros