SELECT on view with multi-join GROUP BY raises ERROR 1142: INSERT command denied when a joined table has NEXTVAL in column DEFAULT

– Setup (run as root/admin)
CREATE DATABASE test_seq;
USE test_seq;

CREATE SEQUENCE seq_branch INCREMENT BY 1 START 1;

CREATE TABLE t_branch (
org_id CHAR(11) NOT NULL,
code VARCHAR(15) NOT NULL DEFAULT LPAD(NEXTVAL(seq_branch), 3, '0'),
name VARCHAR(50) NOT NULL,
PRIMARY KEY (org_id, code)
) ENGINE=InnoDB;

CREATE TABLE t_bind (
org_id CHAR(11) NOT NULL,
branch_id VARCHAR(15) NOT NULL,
location VARCHAR(15) NOT NULL,
PRIMARY KEY (org_id, branch_id, location)
) ENGINE=InnoDB;

INSERT INTO t_branch (org_id, code, name) VALUES ('12345678901', '001', 'Alpha');
INSERT INTO t_bind VALUES ('12345678901', '001', 'LOC1'), ('12345678901', '001', 'LOC2');

CREATE USER 'test_ro'@'localhost' IDENTIFIED BY 'test';
GRANT SELECT ON test_seq.* TO 'test_ro'@'localhost';
FLUSH PRIVILEGES;

– Reproduce (run as test_ro@localhost)
USE test_seq;

– This works (GROUP BY on t_branch PK prefix → index scan, no temp table):
SELECT b.org_id, b.name
FROM t_branch b

Expected: SELECT succeeds — NEXTVAL in a DEFAULT expression should not be evaluated or privilege-checked when no INSERT is occurring.

Actual: ERROR 1142 (42000): INSERT command denied to user 'test_ro'@'localhost' for table 'seq_branch'

Root cause hypothesis: When the optimizer materializes an internal temp table from a multi-join query, it copies column definitions including DEFAULT expressions. In 10.11.16 a privilege check on the objects referenced in DEFAULT expressions is evaluated at this stage, even though no INSERT is being performed and the DEFAULT will never be invoked. It worked on 10.6 serie.

JOIN t_bind bd ON b.org_id = bd.org_id AND b.code = bd.branch_id
GROUP BY b.org_id, b.code;

– This fails with ERROR 1142: INSERT command denied for table `seq_branch`
– (GROUP BY on non-PK columns forces temp table materialization,
– which copies column metadata including the NEXTVAL DEFAULT expression,
– triggering privilege check for INSERT on the sequence):
SELECT bd.location, COUNT AS cnt
FROM t_branch b
JOIN t_bind bd ON b.org_id = bd.org_id AND b.code = bd.branch_id
GROUP BY bd.org_id, bd.location;

– Cleanup (run as root/admin)
DROP DATABASE test_seq;
DROP USER 'test_ro'@'localhost';