MySQL Bugs: #23172: Optimizer cant parse correlated subquery using derived table

Bug #23172	Optimizer cant parse correlated subquery using derived table
Submitted:	11 Oct 2006 11:50	Modified:	12 Oct 2006 12:19
Reporter:	Dmitry L	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27-BK, 5.x	OS:	Linux (Linux, win32)
Assigned to:		CPU Architecture:	Any

Description:
Correlated subquery cant be parsed:
I have 3 tables:
 - table containing objects
 - table with object stats for past days ("archive")
 - table with object stats for today ("actual")

Query collects object stats by summing "archive" data and "actual" data;

First (with "+" operator) works fine
Second (with sum, union, etc) doesn't work because the most inner subquery cant get outer query column

How to repeat:
-- objects
create table obj (
 id integer not null,
 primary key("id")
);
-- archive stat
create table obj_stat (
 `id` integer not null,
 `dtime` integer not null,
 `cnt` integer not null,
 primary key (`id`, `dtime`)
);
-- actual stat
create table obj_stat_today (
 `id` integer not null,
 `dtime` integer not null,
 primary key (`id`, `dtime`)
);
-- fill data
insert into obj values(1, 2);

insert into obj_stat
values(1, 2, 2),
      (1, 3, 3),
      (1, 4, 4),
      (1, 5, 5);

insert into obj_stat_today
values(1, 2),
      (1, 3),
      (1, 4),
      (1, 5);

-- this works fine
select (select sum(cnt) from obj_stat where id = obj.id and dtime < 4) +
       (select count(*) from obj_stat_today where id = obj.id)
  from obj

-- error: Unknown column 'obj.id' in 'where clause'
select 
 (select sum(cnt) 
    from (select cnt
            from obj_stat
           where id = obj.id
             and dtime < 4
          union all
          select count(*)
            from obj_stat_today
           where id = obj.id) x) as cnt
 from obj

Suggested fix:
Let optimizer expand such dependent subquery with derived table

I believe this is the similiar or the same as Bug #8019

Thank you for a problem report. Verified just as described (with minor corrections like insert into obj values(1),(2);) with 5.0.27-BK on Linux:

mysql> select
    ->  (select sum(cnt)
    ->     from (select cnt
    ->             from obj_stat
    ->            where id = obj.id
    ->              and dtime < 4
    ->           union all
    ->           select count(*)
    ->             from obj_stat_today
    ->            where id = obj.id) x) as cnt
    ->  from obj;
ERROR 1054 (42S22): Unknown column 'obj.id' in 'where clause'

Not sdure is it really a bug (looks like a feature request for me), but let the developers decide.

Thank you, Valeriy
Seems like #8019 is similar to this one, sorry about dup
Anyway I think this should be fixed, because other databases (Oracle, MSSql) execute such query

Duplicate of bug #8019.