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:
None 
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 
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.