[Panic] - on HAVING query

[kuba--]

I came across the panic when testing https://github.com/src-d/sourced-ce
First I deleted all charts, after that I tried to create a new one based on blobs table.
It ran following query:

SELECT blob_content AS blob_content, COUNT(*) AS count,  AVG(blob_size) AS `AVG(blob_size)` 
FROM  (
  select * from blobs
) AS expr_qry
GROUP BY blob_content
HAVING ((AVG(blob_size) > 0))
ORDER BY count DESC
LIMIT 10000

and crashed with following error:

time="2019-06-21T14:35:19Z" level=error msg="mysql_server caught panic:\nunresolved column is a placeholder node, but Type was called\n/usr/local/go/src/runtime/panic.go:513 (0x42bdc8)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/expression/unresolved.go:46 (0x9b8d48)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/convert_dates.go:161 (0xccab4e)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/convert_dates.go:52 (0xcdcc1d)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/expression/unresolved.go:70 (0x9b8f7b)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/expression/function/aggregation/avg.go:58 (0xc9c5c0)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/convert_dates.go:51 (0xcdd8f8)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/plan/group_by.go:102 (0x9ce087)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/plan/project.go:74 (0x9db310)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/plan/having.go:54 (0x9d0b30)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/plan/project.go:74 (0x9db310)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/plan/sort.go:96 (0x9e4990)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/plan/limit.go:45 (0x9d6be0)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/convert_dates.go:22 (0xcca9ca)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/batch.go:67 (0xcca737)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/batch.go:38 (0xcca3de)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/sql/analyzer/analyzer.go:171 (0xcc2aeb)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/engine.go:143 (0xd0338c)\n/go/pkg/mod/github.com/src-d/go-mysql-server@v0.4.1-0.20190620142646-9722f31e0f85/server/handler.go:90 (0xd05028)\n/go/pkg/mod/vitess.io/vitess@v3.0.0-rc.3.0.20190602171040-12bfde34629c+incompatible/go/mysql/conn.go:819 (0xc079a7)\n/go/pkg/mod/vitess.io/vitess@v3.0.0-rc.3.0.20190602171040-12bfde34629c+incompatible/go/mysql/conn.go:749 (0xc073b9)\n/go/pkg/mod/vitess.io/vitess@v3.0.0-rc.3.0.20190602171040-12bfde34629c+incompatible/go/mysql/server.go:441 (0xc173eb)\n/usr/local/go/src/runtime/asm_amd64.s:1333 (0x459690)\n"

Full log in attachment.

gitbase.log

[kuba--]

Additional queries which does not work:

select 1 from blobs having blob_size > 0
select avg(blob_size) from blobs having avg(blob_size) > 0

[erizocosmico]

A little bit of a progress report here:

Last queries posted by kuba should not actually work, because having requires a group by in order to work in MySQL.

Aside from that, they still don't work if you add GROUP BY whatever, so I've come up with a solution, which is basically rewrite the resolve_having rule completely. It's still far from done and far from debugged but from a theoretical point of view it sounds better.

The solution is pretty much:

• Transfer all aggregations that appear on the HAVING to the underlying GROUP BY.
• Detect which columns are missing in the GROUP BY in order to compute the having and push them up from the group by child to the group by (somewhat similar to how we resolve order bys).
• If the aggregate of the group by was modified, wrap the having node in a project with what was originally projected.

If you see something that seems missing or wrong please tell me. So far I have rewritten the rule but queries are still failing so have to debug further and then write tests before PRing this.