Bug Report: Unknown column '...' in 'having clause'

[arthurschreiber]

Overview of the Issue

HAVING clauses inside of derived tables fail with Unknown column errors when combined with LIMIT/OFFSET.

This seems to be a regression from v17 -> v18/v19.

Reproduction Steps

• issues is a sharded table
• issues.repository_id is a hash vindex
• issues.id is a lookup vindex

SELECT id
FROM (SELECT `issues`.`id`
      FROM `issues`
      GROUP BY `issues`.`id`
      HAVING (COUNT(issues.id) = 2)
      LIMIT 2 OFFSET 0) subquery_for_limit

{
  "OperatorType": "Limit",
  "Count": ":vtg3",
  "Offset": ":vtg2",
  "Inputs": [
    {
      "OperatorType": "Route",
      "Variant": "Scatter",
      "Keyspace": {
        "Name": "...",
        "Sharded": true
      },
      "FieldQuery": "select id from (select issues.id from issues where 1 != 1 group by issues.id) as subquery_for_limit where 1 != 1",
      "Query": "select id from (select issues.id from issues group by issues.id) as subquery_for_limit having count(issues.id) = :vtg1 /* INT64 */ limit :__upper_limit",
      "Table": "issues"
    }
  ]
}

This error seems to be specific to grouping on a vindex column:

SELECT id
FROM (SELECT `issues`.`id`
      FROM `issues`
      GROUP BY `issues`.`user_id`
      HAVING (COUNT(issues.id) = 2)
      LIMIT 2 OFFSET 0) subquery_for_limit

{
  "OperatorType": "Limit",
  "Count": ":vtg3",
  "Offset": ":vtg2",
  "Inputs": [
    {
      "OperatorType": "Filter",
      "Predicate": "count(issues.id) = :vtg1 /* INT64 */",
      "ResultColumns": 1,
      "Inputs": [
        {
          "OperatorType": "Aggregate",
          "Variant": "Ordered",
          "Aggregates": "any_value(0) AS id, sum_count(1) AS count(issues.id)",
          "GroupBy": "2",
          "Inputs": [
            {
              "OperatorType": "Route",
              "Variant": "Scatter",
              "Keyspace": {
                "Name": "github_development_issues_pull_requests",
                "Sharded": true
              },
              "FieldQuery": "select issues.id, count(issues.id), issues.user_id from issues where 1 != 1 group by issues.user_id",
              "OrderBy": "2 ASC",
              "Query": "select issues.id, count(issues.id), issues.user_id from issues group by issues.user_id order by issues.user_id asc",
              "Table": "issues"
            }
          ]
        }
      ]
    }
  ]
}

Binary Version

v19 (probably present in v18 up to main)

Operating System and Environment details

N/A

Log Fragments

N/A

[systay]

Closed by #16976