[Bug]: restore database contains multi view report table does not exist

[YANGGMM]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main 1.2-dev

Commit ID

newest

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Expected Behavior

restore success

Steps to Reproduce

drop database if exists test01;
create database test01;
use test01;
drop table if exists employees;
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    status VARCHAR(10)
);

drop table if exists departments;
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100)
);

drop table if exists orders;
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    order_date DATE,
    order_amount DECIMAL(10, 2)
);

drop table if exists sales_summary;
CREATE TABLE sales_summary (
    department_name VARCHAR(100),
    total_sales DECIMAL(10, 2)
);

INSERT INTO departments (department_name) VALUES ('Sales'), ('Engineering'), ('Human Resources');

INSERT INTO employees (first_name, last_name, department_id, status) VALUES
('John', 'Doe', 1, 'active'),
('Jane', 'Smith', 2, 'active'),
('Jim', 'Beam', 3, 'inactive');

INSERT INTO orders (employee_id, order_date, order_amount) VALUES
(1, '2024-01-15', 1500.00),
(1, '2024-02-10', 1200.00),
(2, '2024-03-01', 2000.00),
(3, '2024-04-01', 1000.00);

INSERT INTO sales_summary (department_name, total_sales) VALUES
('Sales', 2700.00),
('Engineering', 2000.00),
('Human Resources', 0.00);

select * from employees;
select * from departments;
select * from orders;
select * from sales_summary;

CREATE VIEW department_sales AS
SELECT 
    d.department_name,
    e.first_name,
    e.last_name,
    SUM(o.order_amount) AS total_sales
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
JOIN 
    orders o ON e.employee_id = o.employee_id
GROUP BY 
    d.department_name, e.first_name, e.last_name;

SELECT * FROM department_sales;

CREATE VIEW total_department_sales AS
SELECT 
    department_name,
    SUM(total_sales) AS department_total_sales
FROM 
    department_sales
GROUP BY 
    department_name;

SELECT * FROM total_department_sales;

CREATE VIEW combined_sales_view AS
SELECT 
    COALESCE(ds.department_name, ss.department_name) AS department_name,
    ds.total_sales AS individual_sales,
    ss.total_sales AS department_summary_sales
FROM 
    sales_summary ss
LEFT JOIN 
    department_sales ds ON ss.department_name = ds.department_name;

SELECT * FROM combined_sales_view;

drop snapshot if exists sp06;
create snapshot sp06 for account sys;

drop database test01;

restore account sys from snapshot sp06;

Additional information

No response

[YANGGMM]

{"level":"INFO","time":"2024/05/22 11:41:45.345755 +0800","name":"log-service.frontend","caller":"frontend/snapshot.go:591","msg":"[sp06] start to restore view: combined_sales_view","uuid":"7c4dccb4-4d3c-41f8-b482-5251dc7a41bf"}

{"level":"ERROR","time":"2024/05/22 11:41:45.346424 +0800","caller":"plan/query_builder.go:3501","msg":"error: SQL parser error: table \"department_sales\" does not exist","span":{"trace_id":"4cfab61f-20d4-caec-917d-f5345aeb976c","span_id":"5353a5b7eaa7d87d"}}

恢复视图的顺序有问题

[Ariznawlll]

view涉及到多个db，restore也有问题

create database test03;
create database test04;
create database test05;

use test03;
drop table if exists departments;
create table departments (
                             department_id INT PRIMARY KEY,
                             department_name VARCHAR(100)
);

insert into departments (department_id, department_name)
values (1, 'HR'),
       (2, 'Engineering');

use test04;
drop table if exists employees;
create table employees (
                           employee_id INT PRIMARY KEY,
                           first_name VARCHAR(50),
                           last_name VARCHAR(50),
                           department_id INT,
                           FOREIGN KEY (department_id) REFERENCES test03.departments(department_id)
);

insert into employees values
                          (1, 'John', 'Doe', 1),
                          (2, 'Jane', 'Smith', 2),
                          (3, 'Bob', 'Johnson', 1);

use test04;
drop view if exists employee_view;
create view employee_view as select employee_id, first_name, last_name, department_id from test04.employees;
select * from employee_view;

use test03;
drop view if exists department_view;
create view department_view as select department_id, department_name from test03.departments;
select * from department_view;

use test05;
drop view if exists employee_with_department_view;
create view employee_with_department_view as
select e.employee_id, e.first_name, e.last_name, d.department_name
from test04.employee_view e join test03.department_view d on e.department_id = d.department_id;
select * from employee_with_department_view;

drop snapshot if exists sp100;
create snapshot sp100 for account sys;

drop database test04;
drop database test03;
drop database test05;

restore account sys from snapshot sp100; -->出错位置

[YANGGMM]

view涉及到多个db，restore也有问题

create database test03;
create database test04;
create database test05;

use test03;
drop table if exists departments;
create table departments (
                             department_id INT PRIMARY KEY,
                             department_name VARCHAR(100)
);

insert into departments (department_id, department_name)
values (1, 'HR'),
       (2, 'Engineering');

use test04;
drop table if exists employees;
create table employees (
                           employee_id INT PRIMARY KEY,
                           first_name VARCHAR(50),
                           last_name VARCHAR(50),
                           department_id INT,
                           FOREIGN KEY (department_id) REFERENCES test03.departments(department_id)
);

insert into employees values
                          (1, 'John', 'Doe', 1),
                          (2, 'Jane', 'Smith', 2),
                          (3, 'Bob', 'Johnson', 1);

use test04;
drop view if exists employee_view;
create view employee_view as select employee_id, first_name, last_name, department_id from test04.employees;
select * from employee_view;

use test03;
drop view if exists department_view;
create view department_view as select department_id, department_name from test03.departments;
select * from department_view;

use test05;
drop view if exists employee_with_department_view;
create view employee_with_department_view as
select e.employee_id, e.first_name, e.last_name, d.department_name
from test04.employee_view e join test03.department_view d on e.department_id = d.department_id;
select * from employee_with_department_view;

drop snapshot if exists sp100;
create snapshot sp100 for account sys;

drop database test04;
drop database test03;
drop database test05;

restore account sys from snapshot sp100; -->出错位置

跟外键表有关，已经建了issue #16311 跟踪