sql: incompatible COALESCE expressions: unsupported binary operator: <int> * <int> (desired <decimal>)

[timgraham]

To Reproduce

> SELECT COALESCE(AVG(5), (5*7));
ERROR: incompatible COALESCE expressions: unsupported binary operator: <int> * <int> (desired <decimal>)
SQLSTATE: 22023

Expected behavior
No error.

Environment:

• CockroachDB version 21.2.2

Additional context

This comes from Django's test_aggregation_default_expression which makes the following queries:

SELECT COALESCE(AVG("aggregation_author"."age"), (5 * 7)) AS "value" FROM "aggregation_author" WHERE "aggregation_author"."age" > 100;
SELECT COALESCE(MAX("aggregation_author"."age"), (5 * 7)) AS "value" FROM "aggregation_author" WHERE "aggregation_author"."age" > 100;
SELECT COALESCE(MIN("aggregation_author"."age"), (5 * 7)) AS "value" FROM "aggregation_author" WHERE "aggregation_author"."age" > 100;
SELECT COALESCE(STDDEV_POP("aggregation_author"."age"), (5 * 7)) AS "value" FROM "aggregation_author" WHERE "aggregation_author"."age" > 100;
SELECT COALESCE(SUM("aggregation_author"."age"), (5 * 7)) AS "value" FROM "aggregation_author" WHERE "aggregation_author"."age" > 100;
SELECT COALESCE(VAR_POP("aggregation_author"."age"), (5 * 7)) AS "value" FROM "aggregation_author" WHERE "aggregation_author"."age" > 100;

The error happens for all queries except MAX and MIN.

Jira issue: CRDB-11653

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: Django)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[timgraham]

A similar error is:

> SELECT COALESCE(SUM(5), PI());
ERROR: incompatible COALESCE expressions: expected pi() to be of type decimal, found type float
SQLSTATE: 2202

Let me know if it should be ticketed separately.

[otan]

pi is probably a separate issue

what's the type of "aggregation_author"."age"?

[timgraham]

age is an integer.

[otan]

yeah this is implicit casts rearing it's ugly head again, unfortunately tricky to change atm.
(the pi issue is similar but we can work around it right now)

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[timgraham]

Still a valid issue.