Bug Report: aggregate column rewritten incorrectly to use WHERE instead of HAVING

[olyazavr]

Overview of the Issue

Vitess rewrites
select a from (select a, count(*) as count from data where ... group by ... order by ...) t1 where t1.count >= :daysLimit
as
select a from (select a, count(*) as count from data where ... and count(*) >= :v2 group by ... order by ...) as t1
which is incorrect: it doesn't understand that count is an aggregate column and cannot be used in WHERE and must be used in HAVING. This results in the error Invalid use of group function

Workaround is to make the query more explicit so as to prevent that edge case:

select a from (select a, count(*) as count from data where ... group by ... having count(*) >= :daysLimit order by ...)

This seems similar to #11311 but we have that fix pulled in already, so this seems like another variant

Reproduction Steps

select t1.portalId, t1.flowId from (select portalId, flowId, count(*) as count from suspect_high_cost_flow_data where localDate > :v1 group by portalId, flowId order by null) t1 where t1.count >= :v2

CREATE TABLE `suspect_high_cost_flow_data` (
  `portalId` int(10) unsigned NOT NULL,
  `flowId` bigint(20) unsigned NOT NULL,
  `localDate` int(10) unsigned NOT NULL,
  `totalObjects` bigint(20) unsigned NOT NULL,
  `enrollmentsOfTheDay` bigint(20) unsigned NOT NULL,
  `executionsOfTheDay` bigint(20) unsigned NOT NULL,
  `uniqueEnrollments` bigint(20) unsigned NOT NULL,
  `totalEnrollments` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`portalId`,`flowId`,`localDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

{
  "sharded": true,
  "vindexes": {
    "hash": {
      "type": "hash"
    }
  },
  "tables": {
    "suspect_high_cost_flow_data": {
      "columnVindexes": [
        {
          "column": "portalId",
          "name": "hash"
        }
      ]
    }
  }
}

Binary Version

Vitess 14, up to this commit: https://github.com/vitessio/vitess/commit/01ec41fee8e0f0feca4d8b4857f11076adf6be4e

Operating System and Environment details

Centos8, Linux 5.4.141-hs22.el8.x86_64

Log Fragments

No response

[systay]

Hi @olyazavr 👋

the query you listed under “Reproduction Steps” already has the count(*) >= :v2 expression in the WHERE clause
I'm guessing that is not the correct query

[olyazavr]

Ah sorry about that, here's the actual query: select t1.portalId, t1.flowId from (select portalId, flowId, count(*) as count from suspect_high_cost_flow_data where localDate > :v1 group by portalId, flowId order by null) t1 where t1.count >= :v2

[frouioui]

Backport Pull Requests open for review:

• [planner bugfix 16] add expressions to HAVING (#12668) #12698
• [planner bugfix 15] add expressions to HAVING (#12668) #12699
• [planner bugfix 14] add expressions to HAVING (#12668) #12700