[Bug]: mo report 'invalid input: missing FROM-clause entry for table 'c'' while mysql can execute successfully.

[Ariznawlll]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

e7771f6

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Expected Behavior

as mysql's behavior

Steps to Reproduce

create table orders (
order_id int primary key ,
customer_id int,
order_date date,
status enum('Pending', 'Shipped', 'Delivered', 'Cancelled')
);

insert into orders (order_id, customer_id, order_date, status) values
(1, 1, '2024-01-01', 'Delivered'),
(2, 2, '2024-01-02', 'Shipped'),
(3, 3, '2024-01-03', 'Delivered'),
(4, 1, '2024-01-04', 'Delivered');
select * from orders;

create table order_items (
item_id int primary key,
order_id int,
product_id int,
quantity int,
price decimal(10, 2)
);

insert into order_items (item_id, order_id, product_id, quantity, price) values
(1, 1, 101, 2, 19.99),
(2, 1, 102, 1, 29.99),
(3, 2, 103, 3, 9.99),
(4, 3, 104, 1, 49.99),
(5, 4, 101, 1, 19.99);
select * from order_items;

create table customers (
customer_id int primary key,
first_name varchar(50),
last_name varchar(50),
city char(50)
);

insert into customers (customer_id, first_name, last_name, city) values
(1, 'John', 'Doe', 'New York'),
(2, 'Jane', 'Smith', 'Los Angeles'),
(3, 'Alice', 'Johnson', 'Chicago');
select * from customers;

create table products (
product_id int primary key,
product_name varchar(100),
category_id int
);

insert into products (product_id, product_name, category_id) values
(101, 'Product A', 1),
(102, 'Product B', 1),
(103, 'Product C', 2),
(104, 'Product D', 2);
select * from products;

create table categories (
category_id int primary key,
category_name varchar(100)
);

insert into categories (category_id, category_name) values
(1, 'Electronics'),
(2, 'Books');
select * from categories;

select
    year(o.order_date) as order_year,
    month(o.order_date) as order_month,
    c.city,
    cat.category_name,
    sum(oi.quantity * oi.price) as total_sales
from
    orders o
    join
    order_items oi on o.order_id = oi.order_id
    join
    customers c on o.customer_id = c.customer_id
    join
    products p on oi.product_id = p.product_id
    join
    categories cat on p.category_id = cat.category_id
where
    o.status = 'Delivered'
group by
    year(o.order_date),
    month(o.order_date),
    cat.category_name,
    c.city,
    c.customer_id,
    cat.category_id with rollup
order by
    order_year,
    order_month,
    c.city,
    cat.category_name;

Additional information

No response