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)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.31, 8.0
• OS: Any
• CPU Architecture: x86

[4 Sep 2020 19:40] Dave Pullin

Description:
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

typo

[4 Sep 2020 21:05] MySQL Verification Team

Thank you for the bug report.