Bug #80116 Outer reference to aggregate incorrectly 0 or NULL in subquery

• Submitted: 22 Jan 2016 13:56
• Modified: 1 Oct 2020 13:56
• Reporter: Knut Anders Hatlen
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[22 Jan 2016 13:56] Knut Anders Hatlen

Description:
If a subquery in the select list or the order by clause has a reference to an aggregate in the outer query, that reference seems to evaluate to 0 or NULL instead of the correct value.

How to repeat:
create table t(a int);
insert into t values (1), (2), (3);

# returns (6, NULL) instead of the expected (6, 1)
select sum(a) as s, (select 1 having s) from t;

# returns (6, 1) instead of the expected (6, NULL)
select sum(a) as s, (select 1 having s is null) from t;

# returns (3, NULL) instead of the expected (3, 1)
select count(a) as c, (select 1 having c) from t;

# returns (3, 1) instead of the expected (3, NULL)
select count(a) as c, (select 1 having c = 0) from t;

# returns the results in the wrong order.
# expected result: (3, 1), (2, 1), (1, 1)
# actual result: varies, have seen both
#       (1, 1), (2, 1), (3, 1)
# and   (3, 1), (1, 1), (2, 1)
select a, count(*) as c from t group by a order by (select -a having c > 0);

[1 Oct 2020 13:56] Jon Stephens

Fixed in MySQL 8.0.23 by the fix for BUG#99048. See same for doc info.

Closed.