Bug #100746 user variable in select causes view to be non-insertable
Submitted: 4 Sep 2020 19:40 Modified: 4 Sep 2020 21:05
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.31, 8.0 OS:Any
Assigned to: CPU Architecture:x86

[4 Sep 2020 19:40] Dave Pullin
This insert works:
  insert into bug.insertable_view (date) select  ('2020-09-11');

this insert fails: 
  insert into bug.insertable_view (date) select  @thisDate:=('2020-09-12');

ERROR 1471 (HY000): The target table insertable_view of the INSERT is not insertable-into

There is no change to the view or the underlying table, but only to the select that is the source of the data.

How to repeat:
create database if not exists bug;
  drop table if exists bug.items;
  drop view if exists bug.insertable_view;
  CREATE TABLE bug.items (date date NOT NULL DEFAULT '0000-00-00') ENGINE=MyISAM;
  CREATE or replace VIEW bug.insertable_view AS  select date from bug.items;
  insert into bug.insertable_view (date) select  ('2020-09-11');
  insert into bug.insertable_view (date) select  @thisDate:=('2020-09-12');

Suggested fix:
use of a user variable should not change whether the target view is insertable-into.
[4 Sep 2020 19:41] Dave Pullin
[4 Sep 2020 21:05] MySQL Verification Team
Thank you for the bug report.